/// <summary> /// 增加账单 /// </summary> /// <param name="goods"></param> /// <param name="errorMessage"></param> /// <returns></returns> public DataTable addBills(Goods goods, out string errorMessage) { int maxId = getExcelSheetMaxId(TableId.BillForExcel, out errorMessage); DataTable dt_bill = null; if (maxId < 0) { return(dt_bill); } else { string sql = "insert into [" + getExcelSheetNameById(TableId.BillForExcel) + "](id,商品名称,商品价格 ,商品类别,商场,购买时间,备注) values(" + maxId + "," + goods.goodsName + "," + goods.goodsPrice + "," + goods.goodsType + "," + goods.mall + "," + goods.createDate + "," + goods.goodsMark + ")"; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { //errorMessage = "增加失败!请检查excel"; return(dt_bill); } sql = "select * from [" + getExcelSheetNameById(TableId.BillForExcel) + "] "; DataSet ds = ExcelDataAccess.GetReader(sql, connectionString); if (ds != null) { dt_bill = ds.Tables[0]; } return(dt_bill); } }
/// <summary> /// 增加商品类别 /// </summary> /// <param name="goodsType"></param> /// <param name="errorMessage"></param> /// <returns></returns> public DataTable addGoodsType(GoodsType goodsType, out string errorMessage) { int maxId = getExcelSheetMaxId(TableId.GoodsTypeForExcel, out errorMessage); DataTable dt_goodsType = null; if (maxId < 0) { return(dt_goodsType); } else { string sql = "insert into [" + getExcelSheetNameById(TableId.GoodsTypeForExcel) + "] (id,类别代码,商品类别) values(" + maxId + "," + goodsType.goodsTypeCode + "," + goodsType.goodsTypeName + ")"; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { //errorMessage = "增加失败!请检查excel"; return(dt_goodsType); } sql = "select * from [" + getExcelSheetNameById(TableId.GoodsTypeForExcel) + "] "; DataSet ds = ExcelDataAccess.GetReader(sql, connectionString); if (ds != null) { dt_goodsType = ds.Tables[0]; } return(dt_goodsType); } }
/// <summary> /// 删除账单 /// </summary> /// <param name="goods"></param> /// <param name="errorMessage"></param> /// <returns></returns> public bool deleteBill(Goods goods, out string errorMessage) { string sql = "delete from [" + getExcelSheetNameById(TableId.BillForExcel) + "] where id =" + goods.id; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { return(false); } return(true); }
/// <summary> /// 修改账单 /// </summary> /// <param name="goods"></param> /// <param name="errorMessage"></param> /// <returns></returns> public bool updateBills(Goods goods, out string errorMessage) { string sql = "update [" + getExcelSheetNameById(TableId.BillForExcel) + "] set 商品名称 = " + goods.goodsName + ",商品价格=" + goods.goodsPrice + ",商品类别=" + goods.goodsType + ",商场=" + goods.mall + ",购买时间=" + goods.createDate + ",备注=" + goods.goodsMark + " where id= " + goods.id; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { //errorMessage = "修改失败"; return(false); } return(true); }
/// <summary> /// 修改商品类别 /// </summary> /// <param name="goodsType"></param> /// <param name="errorMessage"></param> /// <returns></returns> public bool updateGoodsType(GoodsType goodsType, out string errorMessage) { errorMessage = ""; string sql = "update [" + getExcelSheetNameById(TableId.GoodsTypeForExcel) + "] set 商品类别 = " + goodsType.goodsTypeName + "where 类别代码 =" + goodsType.goodsTypeCode; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { errorMessage = "修改失败,手动删除"; return(false); } return(true); }
/// <summary> /// 删除商品类别 /// </summary> /// <param name="goodsType"></param> /// <param name="errorMessage"></param> /// <returns></returns> public bool deleteGoodsType(GoodsType goodsType, out string errorMessage) { errorMessage = ""; string sql = "delete from [" + getExcelSheetNameById(TableId.GoodsTypeForExcel) + "] where 类别代码=" + goodsType.goodsTypeCode; int execCount = ExcelDataAccess.ExecuteCommand(sql, connectionString, out errorMessage); if (execCount < 1) { //errorMessage += "删除失败,请稍后重试,或手动删除"; return(false); } return(true); }