/// <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>
/// <returns></returns>
public DataTable searchAllBillDesc()
{
string sql = "select id as 序号,* from [" + getExcelSheetNameById(TableId.BillForExcel) + "]";
DataTable dt_bill = new DataTable();
dt_bill = ExcelDataAccess.GetReader(sql, connectionString).Tables[0];
return(dt_bill);
}
/// <summary>
/// 查询商品类别
/// </summary>
/// <returns></returns>
public DataTable searchGoodsType()
{
string sql = "SELECT * FROM [" + getExcelSheetNameById(TableId.GoodsTypeForExcel) + "] ";
DataSet dt = new DataSet();
dt = ExcelDataAccess.GetReader(sql, connectionString);
return(dt.Tables[0]);
}
/// <summary>
/// 账单查询
/// </summary>
/// <param name="goods"></param>
/// <returns></returns>
public DataTable searchBillByCondition(Goods goods)
{
string sql = "select id as 序号,* from [" + getExcelSheetNameById(TableId.BillForExcel) + "]";
//sql += combineBillCondition(goods);
DataTable dt_bill = new DataTable();
dt_bill = ExcelDataAccess.GetReader(sql, connectionString).Tables[0];
return(dt_bill);
}
