//统计报表中的添加商品销售的 记录
public void AddIteam(PutHuo model)
{
if (ID == null)
{
return;
}
string str = "insert into PutHuo" + ID + "(PutNo,PutName,PutType,PutMoney,PutCount,PutCardNo,PutPersonName,PutDate,PutSale,PutDianName) values(@PutNo,@PutName,@PutType,@PutMoney,@PutCount,@PutCardNo,@PutPersonName,@PutDate,@PutSale,@PutDianName)";
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@PutNo", model.PutNo.Trim()),
new SqlParameter("@PutName", model.PutName.Trim()),
new SqlParameter("@PutType", model.PutType.Trim()),
new SqlParameter("@PutMoney", model.PutMoney.Trim()),
new SqlParameter("@PutCardNo", model.PutCardNo.Trim()),
new SqlParameter("@PutCount", model.PutCount.Trim()),
new SqlParameter("@PutPersonName", model.PubPersonName.Trim()),
new SqlParameter("@PutDate", SqlDbType.SmallDateTime)
{
Value = model.PutDate.Trim()
},
new SqlParameter("@PutSale", model.PutSale.Trim()),
new SqlParameter("@PutDianName", model.PutDianName.Trim())
};
SqlHelper.ExecuteNonQuery(str, pms);
}
/// <summary>
/// 商品销售导出
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="sheet"></param>
/// <returns></returns>
private static IWorkbook huoputpirent <T>(List <T> list, ISheet sheet)
{
int rowIndex = 0;
IRow row = sheet.CreateRow(rowIndex);
#region MyRegion//表头
row.CreateCell(0).SetCellValue("库存号");
row.CreateCell(1).SetCellValue("商品名称");
row.CreateCell(2).SetCellValue("商品类型");
row.CreateCell(3).SetCellValue("金额");
row.CreateCell(4).SetCellValue("会员卡号");
row.CreateCell(5).SetCellValue("数量");
row.CreateCell(6).SetCellValue("业务员");
row.CreateCell(7).SetCellValue("客户姓名");
row.CreateCell(8).SetCellValue("日期");
row.CreateCell(9).SetCellValue("店铺名称");
rowIndex++;
#endregion
foreach (T item in list)
{
PutHuo model = item as PutHuo;
//每遍历一条数据创建一行
row = sheet.CreateRow(rowIndex);
//创建行中的单元格
row.CreateCell(0).SetCellValue(model.PutNo);
row.CreateCell(1).SetCellValue(model.PutName);
row.CreateCell(2).SetCellValue(model.PutType);
row.CreateCell(3).SetCellValue(model.PutMoney);
row.CreateCell(4).SetCellValue(model.PutCardNo);
row.CreateCell(5).SetCellValue(model.PutCount);
row.CreateCell(6).SetCellValue(model.PutSale);
row.CreateCell(7).SetCellValue(model.PubPersonName);
row.CreateCell(8).SetCellValue(model.PutDate);
row.CreateCell(9).SetCellValue(model.PutDianName);
rowIndex++;
}
return(sheet.Workbook);
}
//统计报表中的 统计销售商品的统计
public List <PutHuo> SelectListXS(string begindate, string enddate, string yginfo, string dpname)
{
string dianpu = FilterClass.DianPu1.UserName.Trim();
List <PutHuo> list = new List <PutHuo>();
PutHuo model;
string str = "";
//SqlParameter[] pms;
if (dianpu == "admin")
{
if (dpname == "全部")
{
if (yginfo.Trim() == "全部")
{
foreach (KeyValuePair <string, int> iteam in FilterClass.dic)
{
str += "select * from ";
str += "PutHuo" + iteam.Value + "";
str += " where PutDate between '" + begindate + "' and '" + enddate + "'";
str += " union all ";
}
str = str.Substring(0, str.Length - 10);
// str = "select * from PutHuo where PutDate between '" + begindate + "' and '" + enddate + "'";
// pms = new SqlParameter[] {
//};
}
else
{
foreach (KeyValuePair <string, int> iteam in FilterClass.dic)
{
str += "select * from ";
str += "PutHuo" + iteam.Value + "";
str += " where PutDate between '" + begindate + "' and '" + enddate + "' and PutSale='" + yginfo.Trim() + "'";
str += " union all ";
}
str = str.Substring(0, str.Length - 10);
// str = "select * from PutHuo where PutSale=@PutSale";
// pms = new SqlParameter[] {
//new SqlParameter("@PutSale",yginfo.Trim())
//};
}
}
else
{
int id = FilterClass.dic[dpname];
if (yginfo.Trim() == "全部")
{
str = "select * from PutHuo" + id + " where PutDate between '" + begindate + "' and '" + enddate + "'";
// pms = new SqlParameter[] {
// new SqlParameter("@PutDianName",dpname)
//};
}
else
{
str = "select * from PutHuo" + id + " where PutSale='" + yginfo.Trim() + "' and PutDate between '" + begindate + "' and '" + enddate + "'";
// pms = new SqlParameter[] {
//new SqlParameter("@PutSale",yginfo.Trim()),
// new SqlParameter("@PutDianName",dpname)
//};
}
}
}
else
{
if (yginfo.Trim() == "全部")
{
str = "select * from PutHuo" + ID + " where PutDate between '" + begindate + "' and '" + enddate + "'";
//pms = new SqlParameter[] {
//new SqlParameter("@PutDianName",dianpu)
//};
}
else
{
str = "select * from PutHuo" + ID + " where PutSale='" + yginfo.Trim() + "' and PutDate between '" + begindate + "' and '" + enddate + "'";
//pms = new SqlParameter[] {
//new SqlParameter("@PutDianName",dianpu),
//new SqlParameter("@PutSale",yginfo.Trim())
//};
}
}
SqlDataReader read = SqlHelper.ExecuteReader(str);
while (read.Read())
{
if (read.HasRows)
{
model = new PutHuo();
model.PutNo = read["PutNo"].ToString().Trim();
model.PutName = read["PutName"].ToString().Trim();
model.PutType = read["PutType"].ToString().Trim();
model.PutMoney = read["PutMoney"].ToString().Trim();
model.PutCount = read["PutCount"].ToString().Trim();
model.PutCardNo = read["PutCardNo"].ToString().Trim();
model.PubPersonName = read["PutPersonName"].ToString().Trim();
model.PutDate = read["PutDate"].ToString().Trim();
model.PutSale = read["PutSale"].ToString().Trim();
model.PutDianName = read["PutDianName"].ToString().Trim();
list.Add(model);
}
}
return(list);
}
public void AddIteam(PutHuo model)
{
dal.AddIteam(model);
}
