private static List <EtPurchase> QueryByPurchase(EtPurchase purchase)
{
DBHelper helper = new DBHelper();
string sql = "SELECT * FROM purchase LEFT JOIN category ON purchase.categoryID = category.categoryID WHERE purchaseID = @purchaseID AND purchase.categoryID = @categoryID";
MySqlParameter[] prams = { new MySqlParameter("@purchaseID", purchase.PurchaseID), new MySqlParameter("@categoryID", purchase.Category.CategoryID) };
MySqlDataReader dr = helper.RunQuerySQL(sql, prams);
return(GetListByDataReader(dr));
}
private bool Save()
{
EtPurchase purchase;
if (-1 == CmbOperator.SelectedIndex)
{
MsgBoxUtil.ErrMsgBox("经办人不能为空！");
return(false);
}
int staffId = StaffDao.QueryByStaffName(CmbOperator.SelectedItem.ToString())[0].StaffID;
int res = 0;
foreach (ClsGood good in Goods)
{
purchase = new EtPurchase {
PurchaseID = int.Parse(LblPurchaseID.Text),
Category = good.Good.Category,
PurchaseDate = DtpPurchaseDate.Value.ToString("yyyyMMdd"),
Quantity = good.Count,
Cost = good.Good.Cost,
StaffID = staffId
};
res += PurchaseDao.InsertPurchase(purchase);
EtGood g = new EtGood {
Category = good.Good.Category,
ProductionDate = good.Good.ProductionDate,
PurchaseDate = purchase.PurchaseDate,
Cost = good.Good.Cost,
Price = good.Good.Price,
State = good.Good.State
};
for (int i = 0; i < good.Count; i++)
{
GoodDao.InsertGood(g);
}
}
if (res == Goods.Count)
{
return(true);
}
else
{
return(false);
}
}
private static List <EtPurchase> GetListByDataReader(MySqlDataReader dr)
{
List <EtPurchase> purchases = new List <EtPurchase>();
try
{
while (dr.Read())
{
EtPurchase purchase = new EtPurchase
{
PurchaseID = dr.GetInt32("purchaseID"),
Category = new EtCategory
{
CategoryID = dr.GetInt32("categoryID"),
CategoryName = dr["categoryName"] is DBNull ? null : dr.GetString("categoryName"),
ParentCategoryID = dr["parentCategoryID"] is DBNull ? ECategory.未定义 : (ECategory)dr.GetInt16("parentCategoryID"),
ParentCategoryName = dr["parentCategoryName"] is DBNull ? null : dr.GetString("parentCategoryName"),
Unit = dr["unit"] is DBNull ? null : dr.GetString("unit"),
Color = dr["color"] is DBNull ? null : dr.GetString("color"),
Firm = dr["firm"] is DBNull ? null : dr.GetString("firm"),
MinStock = dr["minStock"] is DBNull ? 0 : dr.GetInt32("minStock"),
MaxStock = dr["maxStock"] is DBNull ? 0 : dr.GetInt32("maxStock"),
ExpirationDate = dr["expirationDate"] is DBNull ? 0 : dr.GetInt32("expirationDate"),
IsValid = dr["isValid"] is DBNull ? EValid.已删除 : (EValid)dr.GetInt16("isValid")
},
PurchaseDate = dr["purchaseDate"] is DBNull ? null : dr.GetString("purchaseDate"),
Quantity = dr["quantity"] is DBNull ? 0 : dr.GetInt32("quantity"),
Cost = dr["cost"] is DBNull ? 0 : dr.GetInt32("cost"),
StaffID = dr.GetInt32("staffID")
};
purchases.Add(purchase);
}
}catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(purchases);
}
public static int InsertPurchase(EtPurchase purchase)
{
List <EtPurchase> purchases = QueryByPurchase(purchase);
if (purchases.Count > 0)
{
return(-1);
}
DBHelper helper = new DBHelper();
string sql = "INSERT INTO " +
"purchase(purchaseID,categoryID,purchaseDate,quantity,cost,staffID) " +
"VALUE(@purchaseID,@categoryID,@purchaseDate,@quantity,@cost,@staffID)";
MySqlParameter[] prams =
{
new MySqlParameter("@purchaseID", purchase.PurchaseID),
new MySqlParameter("@categoryID", purchase.Category.CategoryID),
new MySqlParameter("@purchaseDate", purchase.PurchaseDate ?? (object)DBNull.Value),
new MySqlParameter("@quantity", purchase.Quantity),
new MySqlParameter("@cost", purchase.Cost),
new MySqlParameter("@staffID", purchase.StaffID)
};
return(helper.RunNonQuerySQL(sql, prams));
}
