private static List <EtSale> GetListByDataReader(MySqlDataReader dr)
{
List <EtSale> sales = new List <EtSale>();
try
{
while (dr.Read())
{
EtSale sale = new EtSale
{
SaleID = dr.GetInt32("saleID"),
Good = new EtGood
{
GoodID = dr.GetInt32("goodID"),
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")
},
ProductionDate = dr["productionDate"] is DBNull ? null : dr.GetString("productionDate"),
PurchaseDate = dr["purchaseDate"] is DBNull ? null : dr.GetString("purchaseDate"),
Cost = dr["cost"] is DBNull ? 0 : dr.GetDouble("cost"),
Price = dr["price"] is DBNull ? 0 : dr.GetDouble("price"),
State = (EState)(dr["state"] is DBNull ? 0 : dr.GetInt32("state"))
},
SaleDate = dr["saleDate"] is DBNull ? null : dr.GetString("saleDate"),
Profit = dr["profit"] is DBNull ? 0 : dr.GetDouble("profit"),
StaffID = dr.GetInt32("staffID")
};
sales.Add(sale);
}
}catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(sales);
}
private void BtnInsert_Click(object sender, EventArgs e)
{
try {
EtSale sale = new EtSale {
SaleID = 0,
Good = GoodDao.QueryByGoodID(int.Parse(CmbGoodID.SelectedItem.ToString().Split(' ')[0]))[0]
};
sale.Profit = int.Parse(TxtPrice.Text) - sale.Good.Cost;
sale.Good.Price = int.Parse(TxtPrice.Text);
sale.Good.State = Enums.EState.已出售;
FrmSale.Sales.Add(sale);
Close();
}
catch (Exception) {
MsgBoxUtil.ErrMsgBox("输入不得为空！");
}
}
private EtSale ParseSale(IRow row)
{
if ("" == row.GetCell(0).ToString())
{
errInfo = "商品不存在";
hasErr = true;
return(null);
}
int goodID = int.Parse(row.GetCell(0).ToString());
List <EtGood> goods = GoodDao.QueryByGoodID(goodID);
if (0 == goods.Count || goods[0].State != Enums.EState.未出售)
{
errInfo = "商品不存在";
hasErr = true;
return(null);
}
if ("" == row.GetCell(1).ToString())
{
errInfo = "销售日期不能为空";
hasErr = true;
return(null);
}
string saleDate = row.GetCell(1).ToString();
if ("" == row.GetCell(2).ToString())
{
errInfo = "售价不能为空";
hasErr = true;
return(null);
}
int price = int.Parse(row.GetCell(2).ToString());
EtSale sale = new EtSale {
SaleID = 0,
Good = goods[0],
SaleDate = saleDate,
Profit = price - goods[0].Cost
};
return(sale);
}
public static int InsertSale(EtSale sale)
{
List <EtSale> sales = QueryBySaleID(sale.SaleID);
if (sales.Count > 0)
{
return(-1);
}
DBHelper helper = new DBHelper();
string sql = "INSERT INTO " +
"sale(saleID,goodID,saleDate,profit,staffID) " +
"VALUE(@saleID,@goodID,@saleDate,@profit,@staffID)";
MySqlParameter[] prams =
{
new MySqlParameter("@saleID", sale.SaleID),
new MySqlParameter("@goodID", sale.Good.GoodID),
new MySqlParameter("@saleDate", sale.SaleDate ?? (object)DBNull.Value),
new MySqlParameter("@profit", sale.Profit),
new MySqlParameter("@staffID", sale.StaffID)
};
return(helper.RunNonQuerySQL(sql, prams));
}
private void BtnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog {
InitialDirectory = Path.GetFullPath("../../Excel模板"),
Title = "选择Excel文件",
Filter = "excel文件(*.xls,*.xlsx)|*.xls;*.xlsx|excel03文件(*.xls)|*.xls|excel07文件(*.xlsx)|*.xlsx",
RestoreDirectory = true
};
string excelPath = null;
if (DialogResult.OK == openFileDialog.ShowDialog())
{
excelPath = openFileDialog.FileName;
TxtExcelPath.Text = excelPath;
}
if (excelPath == null || excelPath == "")
{
MsgBoxUtil.ErrMsgBox("路径不能为空");
return;
}
using (FileStream fs = File.OpenRead(excelPath)) {
IWorkbook workbook = null;
//判断Excel的格式，区分xls与xlsx
if (Path.GetExtension(fs.Name) == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else if (Path.GetExtension(fs.Name) == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
//获取工作表
ISheet sheet = workbook.GetSheetAt(0);
//遍历行
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
EtSale sale = ParseSale(row);
if (sale != null)
{
excelSales.Add(sale);
DgvSaleFromExcel.Rows.Add(new object[] {
"√ " + i,
sale.Good.GoodID,
sale.Good.Category.CategoryName,
sale.Good.Category.Unit,
sale.Good.Price,
""
});
}
else
{
DgvSaleFromExcel.Rows.Add(new object[] { "× " + i, "", "", "", "", errInfo });
DgvSaleFromExcel.Rows[i - 1].DefaultCellStyle.ForeColor = Color.Red;
}
hasUpdated = true;
}
}
DgvSaleFromExcel.RowsDefaultCellStyle.BackColor = Color.LightCyan;
DgvSaleFromExcel.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
}
}