/// <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);
}
