protected void bt_Save_Click(object sender, EventArgs e)
{
SaveMyViewState();
CM_Client _r = new CM_ClientBLL((int)ViewState["ClientID"]).Model;
#region 更新销量内容
#region 保存销量头信息
SVM_SalesVolumeBLL bll;
//成/赠品进货
IList<SVM_SalesVolume> svmlists = new List<SVM_SalesVolume>();
string conditon = "";
string message = "";
if (!(bool)ViewState["IsCXP"])
{
switch (ViewState["Type"].ToString())
{
case "1":
case "2":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type IN(1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的进货已审核，不可再次录入";
break;
case "3":
conditon = "Supplier=" + _r.ID.ToString()
+ " AND Type=3 AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue + " AND ISNULL(Promotor,0)=" + ddl_Promotor.SelectedValue;
message = "该客户当月的销量已审核，不可再次录入";
break;
}
}
else //判断是否有过门店赠品进货
{
switch (ViewState["Type"].ToString())
{
case "1":
case "2":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type IN(1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的赠品进货已审核，不可再次录入";
break;
case "3":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type=3 AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的赠品销量已审核，不可再次录入";
break;
}
}
svmlists = SVM_SalesVolumeBLL.GetModelList(conditon + " AND InsertStaff!=1");
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
MessageBox.Show(this, message);
return;
}
if (svmlists.Count == 1)
{
ViewState["VolumeID"] = svmlists[0].ID;
}
}
if ((int)ViewState["VolumeID"] == 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
bll = new SVM_SalesVolumeBLL();
bll.Model.Type = (int)ViewState["Type"];
bll.Model.OrganizeCity = _r.OrganizeCity;
bll.Model["DataSource"] = "4";
if (bll.Model.Type == 3)
{
//门店销售
bll.Model.Supplier = _r.ID;
bll.Model.Promotor = int.Parse(ddl_Promotor.SelectedValue);
}
else
{
bll.Model.Client = _r.ID;
if (ddl_SellOutClient.Visible)
bll.Model.Supplier = int.Parse(ddl_SellOutClient.SelectedValue);
else
bll.Model.Supplier = _r.Supplier;
}
bll.Model.InsertStaff = (int)Session["UserID"];
}
else
{
bll = new SVM_SalesVolumeBLL((int)ViewState["VolumeID"]);
bll.Model.UpdateStaff = (int)Session["UserID"];
}
bll.Model.SalesDate = DateTime.Parse(this.tbx_VolumeDate.Text.Trim());
bll.Model.AccountMonth = int.Parse(ddl_AccountMonth.SelectedValue);
bll.Model.Flag = int.Parse(ddl_Flag.SelectedValue);
bll.Model.SheetCode = tbx_sheetCode.Text;
bll.Model.Remark = tbx_Remark.Text;
if (bll.Model.Type == 3)
{
//门店销售时记录导购员
bll.Model.Promotor = int.Parse(ddl_Promotor.SelectedValue);
}
bll.Model["SubmitFlag"] = "2";
if ((int)ViewState["VolumeID"] == 0)
ViewState["VolumeID"] = bll.Add();
else
bll.Update();
#endregion
#region 更新产品明细数据
DataTable dt = (DataTable)ViewState["DTDetail"];
foreach (DataRow dr in dt.Rows)
{
int product = (int)dr["ID"];
int quantity = (int)dr["Quantity"];
if (quantity > 5000 && bll.Model.Type == 3)
{
MessageBox.Show(this, "超过系统设置的销量上限5000");
return;
}
if ((bll.Model.Flag == 2 || bll.Model.Flag == 3 || bll.Model.Flag == 12) && quantity > 0)
quantity = 0 - quantity; //退货时，数量保存为负数 (2:原价退货 3:折价退货 12:赠品退货)
SVM_SalesVolume_Detail _detail = bll.Items.FirstOrDefault(m => m.Product == product);
if (_detail == null)
{
//销量明细里不存在该产品的记录
if (quantity == 0) continue; //新增销量时，数量为0的不保存到数据库中
_detail = new SVM_SalesVolume_Detail();
_detail.Product = product;
_detail.SalesPrice = (decimal)dr["Price"];
_detail.Quantity = quantity;
_detail.FactoryPrice = (decimal)dr["FactoryPrice"];
bll.AddDetail(_detail);
}
else
{
//销量明细里已存在该产品的记录
if (quantity == 0)
{
bll.DeleteDetail(_detail.ID);
continue;
}
else if (_detail.Quantity != quantity || _detail.SalesPrice != (decimal)dr["Price"])
{
_detail.SalesPrice = (decimal)dr["Price"];
_detail.Quantity = quantity;
bll.UpdateDetail(_detail);
}
}
}
#endregion
#endregion
if (ddl_Promotor.Items.Count > 2)
{
MessageBox.Show(this, "本店有" + (ddl_Promotor.Items.Count - 1).ToString() + "个导购员，请确定每个导购员均已录入了销量");
}
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "数据暂存成功，并在所有门店及经分销商的销量全部录入完成后，及时到汇总页面统一提交!",
"SalesVolumeBatchInput.aspx?VolumeID=" + ViewState["VolumeID"].ToString());
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
DateTime minday, maxday = DateTime.Now;
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount() - 1;
bool flag = true;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "零售商ID" ||
headerRow.GetCell(1).ToString() != "零售商编号" ||
headerRow.GetCell(2).ToString() != "零售商名称" ||
headerRow.GetCell(3).ToString() != "零售商分类" ||
headerRow.GetCell(4).ToString() != "归属月份" ||
headerRow.GetCell(5).ToString() != "导购ID" ||
headerRow.GetCell(6).ToString() != "导购姓名")
{
flag = false;
}
if (!flag)
{
MessageBox.Show(this, "表头(1~7列)错误!");
return;
}
int column = getColumnCount(headerRow) + 1;
int month = 0;
rows.MoveNext();
while (rows.MoveNext())
{
int datacolumn = 7;
i++;
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0).ToString() == "")
{
break;
}
int clientid = 0; int promotorid = 0;
if (!int.TryParse(row.GetCell(0).ToString(), out clientid))
{
errormessage += "零售商：" + row.GetCell(2).ToString() + "的ID错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null)
{
errormessage += "ID号：" + clientid.ToString() + "零售商在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (int.TryParse(row.GetCell(5).ToString(), out promotorid))
{
PM_Promotor pm = new PM_PromotorBLL(promotorid).Model;
if (pm == null)
{
errormessage += "导购ID号：" + promotorid.ToString() + "导购在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
if (month == 0 && headerRow.GetCell(4).ToString() == "归属月份")
{
IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + row.GetCell(4).ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
minday = _monthlist[0].BeginDate;
maxday = DateTime.Today < _monthlist[0].EndDate ? DateTime.Today : _monthlist[0].EndDate;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
string conditon = "";
conditon = "Supplier=" + clientid.ToString()
+ "AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',4)='7' AND Type=3 AND AccountMonth=" + month.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=1 AND ISNULL(Promotor,0)=" + promotorid.ToString();
IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList(conditon);
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
errormessage += "ID号：" + row.GetCell(0).ToString() + "," + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的销量单" + "已审核，不可再次导入！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (svmlists.Count == 1)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = 0;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.Type = 3;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.AccountMonth = month;
bll.Model.SalesDate = maxday;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = 1;
bll.Model.Remark = "线下补录导入";
}
#endregion
bll.Model["SubmitFlag"] = "1";
bll.Model["DataSource"] = "7";
IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>();
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
int quantity = 0;
bool isnumber = false;
while (true)
{
PDT_Product product = null;
quantity = 0;
if (headerRow.GetCell(datacolumn) == null || headerRow.GetCell(datacolumn).CellType == CellType.BLANK || headerRow.GetCell(datacolumn).ToString() == string.Empty)
{
break;
}
IList <PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(datacolumn).ToString() + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
errormessage += "产品名称：" + headerRow.GetCell(datacolumn).ToString() + "在产品列表中不存在！\r\n";
datacolumn++;
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if ((product != null) && row.GetCell(datacolumn).CellType != CellType.BLANK)
{
int.TryParse(row.GetCell(datacolumn).ToString(), out quantity);
if (int.TryParse(row.GetCell(datacolumn).ToString(), out quantity) && !isnumber)
{
isnumber = true;
}
if (quantity != 0 && quantity <= 5000 && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 3, out factoryprice, out salesprice);
if (factoryprice == 0)
{
factoryprice = product.FactoryPrice;
}
if (salesprice == 0)
{
salesprice = product.NetPrice;
}
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (row.GetCell(datacolumn).CellType != CellType.BLANK && (row.GetCell(datacolumn).ToString() != "0"))
{
wrongflag = true;
break;
}
else if (quantity < 0)
{
wrongflag = true;
break;
}
}
datacolumn++;
}
if (wrongflag)
{
errormessage += "ID号：" + clientid.ToString() + "，" + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的线下补录销量单"
+ "未能导入！产品名称：" + headerRow.GetCell(datacolumn).ToString() + "数量填写错误。\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.Model.UpdateStaff = 1;
bll.AddDetail();
bll.Update();
string message = " ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + "的销量单" + "被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
string message = "ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ "的销量单" + "已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
}
#endregion
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
FileStream writefile = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook.Write(writefile);
writefile.Close();
sheet = null;
}
}
private string DoImportProduct(Worksheet worksheet, int accountmonth, int insertstaff, int cloumn, IList<PDT_Product> productlists, out int State)
{
string ImportInfo = "";
AC_AccountMonth month = new AC_AccountMonthBLL(accountmonth).Model;
DateTime minday = month.BeginDate;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
State = 0;
ImportInfo += "<br/>";
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += cloumn == 6 ? "<span style='color: Red'>----批量导入客户进货----</span><br/>" : "<span style='color: Red'>----批量导入客户销量----<br/>ID号 门店类型 门店名 导购员 错误原因<br/></span>";
#region 读取Excel表格
int row = 1;
int emptyrow = 0;
int _cloumn = 0;
while (true)
{
_cloumn = cloumn;
row++;
if (((Range)worksheet.Cells[row, 1]).Value2 == null)
{
emptyrow++;
if (emptyrow > 5)
break;
else
continue;
}
int clientid = 0; int promotorid = 0;
if (!int.TryParse(((Range)worksheet.Cells[row, 1]).Value2.ToString(), out clientid))
{
continue;
}
if (cloumn == 8 && ((Range)worksheet.Cells[row, 6]).Value2 != null &&
!int.TryParse(((Range)worksheet.Cells[row, 6]).Value2.ToString(), out promotorid))
{
continue;
}
#region 验证数据
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != ((Range)worksheet.Cells[row, 3]).Text.ToString())
{
ImportInfo += "<span style='color: Red'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商ID与零售商名称" : "，分销商ID与分销商名称") + "不匹配！</span><br/>";
State = 4;
continue;
}
int uplimit = client.ClientType == 3 ? 5000 : 9000;
if (((Range)worksheet.Cells[row, 5]).Text.ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>ID号：" + clientid.ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
State = 4;
continue;
}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
string conditon = "";
if (cloumn == 6)
{
conditon = "Client=" + clientid.ToString()
+ " AND Type IN(1,2) AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=1 ";
}
else
{
conditon = "Supplier=" + clientid.ToString()
+ " AND Type=3 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=1 AND ISNULL(Promotor,0)=" + promotorid.ToString();
}
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList(conditon + " AND InsertStaff!=1");
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[row, 1]).Text.ToString() + (client.ClientType == 3 ? "，" : "，该分销商") + client.FullName
+ (cloumn == 6 ? " 当月的进货单" : "，导购:" + ((Range)worksheet.Cells[row, 7]).Text.ToString() + " 当月的销量单") + "已审核，不可再次导入！</span><br/>";
continue;
}
if (svmlists.Count == 1)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
if (cloumn == 8)
{
bll.Model.Client = 0;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.Type = 3;
}
else
{
bll.Model.Client = clientid;
bll.Model.Supplier = client.Supplier;
bll.Model.Type = 2;
}
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = insertstaff;
bll.Model.Remark = "Excel批量导入";
}
#endregion
bll.Model["SubmitFlag"] = "1";
bll.Model["DataSource"] = "3";
#region 读取各产品销量
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
int quantity = 0;
bool isnumber = false;
foreach (PDT_Product product in productlists)
{
quantity = 0;
if (((Range)worksheet.Cells[row, _cloumn]).Value2 != null)
{
int.TryParse(((Range)worksheet.Cells[row, _cloumn]).Value2.ToString(), out quantity);
if (int.TryParse(((Range)worksheet.Cells[row, _cloumn]).Value2.ToString(), out quantity) && !isnumber)
{
isnumber = true;
}
if (quantity != 0 && quantity <= uplimit && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, cloumn == 8 ? 3 : 2, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.NetPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (trim(((Range)worksheet.Cells[row, _cloumn]).Text.ToString()) != "" && ((Range)worksheet.Cells[row, _cloumn]).Text.ToString() != "0")
{
wrongflag = true;
break;
}
else if (quantity < 0)
{
wrongflag = true;
break;
}
}
_cloumn++;
}
if (wrongflag)
{
ImportInfo += "<span style='color: Red'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，" + ((Range)worksheet.Cells[row, 4]).Text + ":" : "，分销商：") + client.FullName
+ (client.ClientType == 3 ? "" : "，该分销商") + (cloumn == 6 ? "当月的进货单" : "，导购:" + ((Range)worksheet.Cells[row, 7]).Text.ToString() + " 当月的销量单")
+ "未能导入！产品名称：" + ((Range)worksheet.Cells[1, _cloumn]).Text + "数量填写错误。</span><br/>";
State = 4;
continue;
}
#endregion
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.Model.UpdateStaff = insertstaff;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color:Blue'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，该零售商：" : "，该分销商：") + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + (cloumn == 6 ? " 的进货单" : "的销量单") + "被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
ImportInfo += "<span style='color:Black'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，该零售商：" : "，该分销商：") + client.FullName
+ (cloumn == 6 ? " 的进货单" : "的销量单") + "已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
return ImportInfo;
}
private string ImportGifts(Worksheet worksheet, string remark, AC_AccountMonth month, int insertstaff, IList<PDT_Product> productlists, out int State)
{
string ImportInfo = "";
int cloumn = 6;
int sellinrow = 1;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
ImportInfo += "<br/>";
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----" + remark + "----</span><br/>";
State = 0;
PDT_Product product;
#region 读取Excel表格
while (true)
{
cloumn = 6;
sellinrow++;
if (((Range)worksheet.Cells[sellinrow, 1]).Value2 == null)
{
break;
}
int clientid = 0;
if (!int.TryParse(((Range)worksheet.Cells[sellinrow, 1]).Value2.ToString(), out clientid))
{
continue;
}
#region 验证数据
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != ((Range)worksheet.Cells[sellinrow, 3]).Text.ToString())
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? "，零售商ID与零售商名称" : "，分销商ID与分销商名称") + "不匹配！</span><br/>";
State = 4;
continue;
}
int uplimit = client.ClientType == 3 ? 5000 : 9000;
if (((Range)worksheet.Cells[sellinrow, 5]).Text.ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
State = 4;
continue;
}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString() + " AND InsertStaff!=1"
+ " AND Type IN(1,2) AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=11 AND (Remark='" + remark + "' OR Remark NOT IN ('Excel批量导入本品赠品','Excel批量导入试用装赠品','Excel批量导入高值促销品'))");
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? "，该零售商" : "，该分销商") + "当月的本品赠品进货已审核，不可再次导入！</span><br/>";
continue;
}
//仅存在一条符合条件的赠品,进行覆盖
if (svmlists.Count == 1 && svmlists[0].Remark == remark)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = clientid;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.Supplier;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 2;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 11; //赠品进货
bll.Model["IsCXP"] = "Y";
bll.Model.InsertStaff = insertstaff;
bll.Model.Remark = remark;
}
#endregion
bll.Model["SubmitFlag"] = "1";
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
int quantity = 0;
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
bool isnumber = false;
while (true)
{
if (((Range)worksheet.Cells[1, cloumn]).Text.ToString() == string.Empty)
{
break;
}
IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + ((Range)worksheet.Cells[1, cloumn]).Text + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
ImportInfo += "<span style='color: Red'>赠品产品名称：" + ((Range)worksheet.Cells[1, cloumn]).Text + "在赠品列表中不存在！</span><br/>";
State = 4;
cloumn++;
continue;
}
if (product != null && ((Range)worksheet.Cells[sellinrow, cloumn]).Value2 != null)
{
#region 读取各产品销量
if (!isnumber && int.TryParse(((Range)worksheet.Cells[sellinrow, cloumn]).Value2.ToString(), out quantity))
{
isnumber = true;
}
if (quantity != 0 && quantity <= uplimit && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 2, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.NetPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (trim(((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString()) != "" && ((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString() != "0")
{
wrongflag = true;
break;
}
#endregion
}
cloumn++;
}
if (wrongflag)
{
ImportInfo += "<span style='color: Red'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商" : "，分销商：") + client.FullName
+ "的赠品进货单未能导入，赠品名称：" + ((Range)worksheet.Cells[1, cloumn]).Text + "数量填写错误";
State = 4;
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Model.UpdateStaff = insertstaff;
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商：" : "，分销商：") + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的赠品进货单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
ImportInfo += "<span style='color: Black'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商：" : "，分销商：") + client.FullName
+ " 的赠品进货单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
return ImportInfo;
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
DateTime minday, maxday=DateTime.Now;
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount()-1;
bool flag = true;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "零售商ID" ||
headerRow.GetCell(1).ToString() != "零售商编号" ||
headerRow.GetCell(2).ToString() != "零售商名称" ||
headerRow.GetCell(3).ToString() != "零售商分类" ||
headerRow.GetCell(4).ToString() != "归属月份" ||
headerRow.GetCell(5).ToString() != "导购ID" ||
headerRow.GetCell(6).ToString() != "导购姓名")
{
flag = false;
}
if (!flag)
{
MessageBox.Show(this, "表头(1~7列)错误!");
return;
}
int column = getColumnCount(headerRow) + 1;
int month = 0;
rows.MoveNext();
while (rows.MoveNext())
{
int datacolumn = 7;
i++;
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0).ToString() == "")
{
break;
}
int clientid = 0; int promotorid = 0;
if (!int.TryParse(row.GetCell(0).ToString(), out clientid))
{
errormessage += "零售商：" + row.GetCell(2).ToString() + "的ID错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null)
{
errormessage += "ID号：" + clientid.ToString() + "零售商在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (int.TryParse(row.GetCell(5).ToString(), out promotorid))
{
PM_Promotor pm = new PM_PromotorBLL(promotorid).Model;
if (pm == null)
{
errormessage += "导购ID号：" + promotorid.ToString() + "导购在系统中不存在！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
if (month == 0 && headerRow.GetCell(4).ToString() == "归属月份")
{
IList<AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + row.GetCell(4).ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
minday = _monthlist[0].BeginDate;
maxday = DateTime.Today < _monthlist[0].EndDate ? DateTime.Today : _monthlist[0].EndDate;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
}
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
string conditon = "";
conditon = "Supplier=" + clientid.ToString()
+ "AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',4)='7' AND Type=3 AND AccountMonth=" + month.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=1 AND ISNULL(Promotor,0)=" + promotorid.ToString();
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList(conditon);
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
errormessage += "ID号：" + row.GetCell(0).ToString() + "," + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的销量单" + "已审核，不可再次导入！\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if (svmlists.Count == 1)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = 0;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.Type = 3;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.AccountMonth = month;
bll.Model.SalesDate = maxday;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = 1;
bll.Model.Remark = "线下补录导入";
}
#endregion
bll.Model["SubmitFlag"] = "1";
bll.Model["DataSource"] = "7";
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
int quantity = 0;
bool isnumber = false;
while (true)
{
PDT_Product product = null;
quantity = 0;
if (headerRow.GetCell(datacolumn) == null||headerRow.GetCell(datacolumn).CellType == CellType.BLANK || headerRow.GetCell(datacolumn).ToString() == string.Empty)
{
break;
}
IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(datacolumn).ToString() + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
errormessage += "产品名称：" + headerRow.GetCell(datacolumn).ToString() + "在产品列表中不存在！\r\n";
datacolumn++;
row.GetCell(column).SetCellValue(errormessage);
continue;
}
if ((product != null) && row.GetCell(datacolumn).CellType != CellType.BLANK)
{
int.TryParse(row.GetCell(datacolumn).ToString(), out quantity);
if (int.TryParse(row.GetCell(datacolumn).ToString(), out quantity) && !isnumber)
{
isnumber = true;
}
if (quantity != 0 && quantity <= 5000 && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 3, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.NetPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (row.GetCell(datacolumn).CellType != CellType.BLANK && (row.GetCell(datacolumn).ToString() != "0"))
{
wrongflag = true;
break;
}
else if (quantity < 0)
{
wrongflag = true;
break;
}
}
datacolumn++;
}
if (wrongflag)
{
errormessage += "ID号：" + clientid.ToString() + "，" + client.FullName
+ "，导购:" + row.GetCell(6).ToString() + " 当月的线下补录销量单"
+ "未能导入！产品名称：" + headerRow.GetCell(datacolumn).ToString() + "数量填写错误。\r\n";
row.GetCell(column).SetCellValue(errormessage);
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.Model.UpdateStaff = 1;
bll.AddDetail();
bll.Update();
string message = " ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + "的销量单" + "被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
string message = "ID号：" + clientid.ToString() + "，该零售商：" + client.FullName
+ "的销量单" + "已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "\r\n";
improtmessage += message;
row.CreateCell(column).SetCellValue(message);
}
}
#endregion
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
FileStream writefile = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook.Write(writefile);
writefile.Close();
sheet = null;
}
}
protected void bt_Save_Click(object sender, EventArgs e)
{
SaveMyViewState();
CM_Client _r = new CM_ClientBLL((int)ViewState["ClientID"]).Model;
#region 更新销量内容
#region 保存销量头信息
SVM_SalesVolumeBLL bll;
//成/赠品进货
IList <SVM_SalesVolume> svmlists = new List <SVM_SalesVolume>();
string conditon = "";
string message = "";
if (!(bool)ViewState["IsCXP"])
{
switch (ViewState["Type"].ToString())
{
case "1":
case "2":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type IN(1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的进货已审核，不可再次录入";
break;
case "3":
conditon = "Supplier=" + _r.ID.ToString()
+ " AND Type=3 AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue + " AND ISNULL(Promotor,0)=" + ddl_Promotor.SelectedValue;
message = "该客户当月的销量已审核，不可再次录入";
break;
}
}
else //判断是否有过门店赠品进货
{
switch (ViewState["Type"].ToString())
{
case "1":
case "2":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type IN(1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的赠品进货已审核，不可再次录入";
break;
case "3":
conditon = "Client=" + _r.ID.ToString()
+ " AND Type=3 AND AccountMonth=" + ddl_AccountMonth.SelectedValue
+ " AND Flag=" + ddl_Flag.SelectedValue;
message = "该客户当月的赠品销量已审核，不可再次录入";
break;
}
}
svmlists = SVM_SalesVolumeBLL.GetModelList(conditon + " AND InsertStaff!=1");
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
MessageBox.Show(this, message);
return;
}
if (svmlists.Count == 1)
{
ViewState["VolumeID"] = svmlists[0].ID;
}
}
if ((int)ViewState["VolumeID"] == 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
bll = new SVM_SalesVolumeBLL();
bll.Model.Type = (int)ViewState["Type"];
bll.Model.OrganizeCity = _r.OrganizeCity;
bll.Model["DataSource"] = "4";
if (bll.Model.Type == 3)
{
//门店销售
bll.Model.Supplier = _r.ID;
bll.Model.Promotor = int.Parse(ddl_Promotor.SelectedValue);
}
else
{
bll.Model.Client = _r.ID;
if (ddl_SellOutClient.Visible)
{
bll.Model.Supplier = int.Parse(ddl_SellOutClient.SelectedValue);
}
else
{
bll.Model.Supplier = _r.Supplier;
}
}
bll.Model.InsertStaff = (int)Session["UserID"];
}
else
{
bll = new SVM_SalesVolumeBLL((int)ViewState["VolumeID"]);
bll.Model.UpdateStaff = (int)Session["UserID"];
}
bll.Model.SalesDate = DateTime.Parse(this.tbx_VolumeDate.Text.Trim());
bll.Model.AccountMonth = int.Parse(ddl_AccountMonth.SelectedValue);
bll.Model.Flag = int.Parse(ddl_Flag.SelectedValue);
bll.Model.SheetCode = tbx_sheetCode.Text;
bll.Model.Remark = tbx_Remark.Text;
if (bll.Model.Type == 3)
{
//门店销售时记录导购员
bll.Model.Promotor = int.Parse(ddl_Promotor.SelectedValue);
}
bll.Model["SubmitFlag"] = "2";
if ((int)ViewState["VolumeID"] == 0)
{
ViewState["VolumeID"] = bll.Add();
}
else
{
bll.Update();
}
#endregion
#region 更新产品明细数据
DataTable dt = (DataTable)ViewState["DTDetail"];
foreach (DataRow dr in dt.Rows)
{
int product = (int)dr["ID"];
int quantity = (int)dr["Quantity"];
if (quantity > 5000 && bll.Model.Type == 3)
{
MessageBox.Show(this, "超过系统设置的销量上限5000");
return;
}
if ((bll.Model.Flag == 2 || bll.Model.Flag == 3 || bll.Model.Flag == 12) && quantity > 0)
{
quantity = 0 - quantity; //退货时，数量保存为负数 (2:原价退货 3:折价退货 12:赠品退货)
}
SVM_SalesVolume_Detail _detail = bll.Items.FirstOrDefault(m => m.Product == product);
if (_detail == null)
{
//销量明细里不存在该产品的记录
if (quantity == 0)
{
continue; //新增销量时，数量为0的不保存到数据库中
}
_detail = new SVM_SalesVolume_Detail();
_detail.Product = product;
_detail.SalesPrice = (decimal)dr["Price"];
_detail.Quantity = quantity;
_detail.FactoryPrice = (decimal)dr["FactoryPrice"];
bll.AddDetail(_detail);
}
else
{
//销量明细里已存在该产品的记录
if (quantity == 0)
{
bll.DeleteDetail(_detail.ID);
continue;
}
else if (_detail.Quantity != quantity || _detail.SalesPrice != (decimal)dr["Price"])
{
_detail.SalesPrice = (decimal)dr["Price"];
_detail.Quantity = quantity;
bll.UpdateDetail(_detail);
}
}
}
#endregion
#endregion
if (ddl_Promotor.Items.Count > 2)
{
MessageBox.Show(this, "本店有" + (ddl_Promotor.Items.Count - 1).ToString() + "个导购员，请确定每个导购员均已录入了销量");
}
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "数据暂存成功，并在所有门店及经分销商的销量全部录入完成后，及时到汇总页面统一提交!",
"SalesVolumeBatchInput.aspx?VolumeID=" + ViewState["VolumeID"].ToString());
}
}