protected void bt_AddProduct_Click(object sender, EventArgs e) { int product = 0; if (int.TryParse(select_Product.SelectValue, out product) && product > 0) { PDT_Product pdt = new PDT_ProductBLL(product).Model; if (pdt == null) { return; } int quantity = int.Parse(tbx_Q1.Text) * pdt.ConvertFactor + int.Parse(tbx_Q2.Text); DataTable dt = (DataTable)ViewState["DTDetail"]; DataRow[] drs = dt.Select("ID=" + product.ToString()); if (drs.Length > 0) { drs[0]["Quantity"] = quantity; } else { DataRow dr = dt.NewRow(); decimal factoryprice = 0, price = 0; PDT_ProductPriceBLL.GetPriceByClientAndType((int)ViewState["ClientID"], product, (int)ViewState["Type"], out factoryprice, out price); dr["ID"] = product; dr["Code"] = pdt.Code; dr["FullName"] = pdt.FullName; dr["ShortName"] = pdt.ShortName; dr["Brand"] = pdt.Brand; dr["Spec"] = pdt["Spec"]; dr["Classify"] = pdt.Classify; dr["ConvertFactor"] = pdt.ConvertFactor; dr["FactoryPrice"] = factoryprice; dr["Price"] = price; dr["Quantity"] = quantity; try { dr["BrandName"] = new PDT_BrandBLL(pdt.Brand).Model.Name; dr["ClassifyName"] = new PDT_ClassifyBLL(pdt.Classify).Model.Name; dr["TrafficPackaging"] = DictionaryBLL.GetDicCollections("PDT_Packaging")[pdt.TrafficPackaging.ToString()].Name; dr["Packaging"] = DictionaryBLL.GetDicCollections("PDT_Packaging")[pdt.Packaging.ToString()].Name; } catch { } dt.Rows.Add(dr); } BindGrid(); } }
protected void bt_AddProduct_Click(object sender, EventArgs e) { SVM_InventoryBLL bll = new SVM_InventoryBLL((int)ViewState["InventoryID"]); int product = 0; if (int.TryParse(select_Product.SelectValue, out product) && product > 0) { PDT_Product pdt = new PDT_ProductBLL(product).Model; if (pdt == null) { return; } int quantity = int.Parse(tbx_Q1.Text) * pdt.ConvertFactor + int.Parse(tbx_Q2.Text); if (quantity == 0) { return; } SVM_Inventory_Detail _detail = bll.Items.FirstOrDefault(m => m.Product == product); if (_detail == null) { _detail = new SVM_Inventory_Detail(); decimal factoryprice = 0, price = 0; PDT_ProductPriceBLL.GetPriceByClientAndType((int)ViewState["ClientID"], product, 2, out factoryprice, out price); _detail.FactoryPrice = factoryprice; _detail.Product = pdt.ID; _detail.Quantity = quantity; _detail.InventoryID = (int)ViewState["InventoryID"]; bll.AddDetail(_detail); } else { _detail.Quantity = quantity; bll.UpdateDetail(_detail); } BindGrid(); } }
protected void bt_Import_Click(object sender, EventArgs e) { string ImportInfo = ""; lb_ErrorInfo.Text = ""; System.Data.DataTable dtSellIn = null, dtSellOut = null; if (Session["DataTable-SellIn"] != null && Session["DataTable-SellOut"] != null) { dtSellIn = (System.Data.DataTable)Session["DataTable-SellIn"]; dtSellOut = (System.Data.DataTable)Session["DataTable-SellOut"]; } #region 获取允许最迟的销量日期 int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays"); AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model; DateTime minday = month.BeginDate; DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; #endregion IList <PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code"); ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>"; ImportInfo += "<span style='color: Red'>----批量导入零售商进货销量----</span><br/>"; #region 开始导入零售商进货 foreach (System.Data.DataRow dr in dtSellIn.Rows) { #region 验证数据 int clientid = (int)dr["零售商ID"]; CM_Client client = new CM_ClientBLL(clientid).Model; if (client == null || client.FullName != (string)dr["零售商名称"]) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",零售商ID与零售商名称不匹配!</span><br/>"; continue; } if (dr["归属月份"].ToString() != month.Name) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",归属月份必须为【" + month.Name + "】</span><br/>"; continue; } //DateTime salesdate = (DateTime)dr["进货日期"]; //if (salesdate < minday || salesdate > maxday) //{ // ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",进货日期必须在" + minday.ToString("yyyy-MM-dd") + // "至" + maxday.ToString("yyyy-MM-dd") + "之间!</span><br/>"; // continue; //} #endregion #region 组织销量头 SVM_SalesVolumeBLL bll = null; IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString() + " AND Type=2 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd") + " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' "); if (svmlists.Count > 0) { if (svmlists[0].ApproveFlag == 1) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",该零售商当月的进货已审核,不可再次导入!</span><br/>"; continue; } bll = new SVM_SalesVolumeBLL(svmlists[0].ID); bll.Items.Clear(); } else { 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 = 1; //成品销售 bll.Model["SubmitFlag"] = "1"; bll.Model["IsCXP"] = "N"; bll.Model.InsertStaff = (int)Session["UserID"]; bll.Model.Remark = "Excel批量导入"; } #endregion #region 读取各产品销量 IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>(); foreach (PDT_Product product in productlists) { int quantity = (int)dr["[" + product.ShortName + "]"]; if (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); } } #endregion #region 更新销量至数据库 if (bll.Model.ID > 0) { if (details.Count > 0) { bll.DeleteDetail(); //先清除原先导入的数据 bll.Items = details; bll.AddDetail(); bll.Update(); ImportInfo += "<span style='color: Blue'>序号:" + dr["序号"].ToString() + ",零售商:" + client.FullName + " 的原有日期为:" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的进货单被成功更新!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } } else { //if (details.Count > 0) //没有产品也新增一条空销量头 { bll.Items = details; bll.Add(); ImportInfo += "<span style='color: Black'>序号:" + dr["序号"].ToString() + ",零售商:" + client.FullName + " 的进货单已成功导入!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } } #endregion } #endregion ImportInfo += "<br/><br/>"; ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>"; ImportInfo += "<span style='color: Red'>----批量导入零售商销货销量----</span><br/>"; #region 开始导入零售商销货 foreach (System.Data.DataRow dr in dtSellOut.Rows) { #region 验证数据 int clientid = (int)dr["零售商ID"]; CM_Client client = new CM_ClientBLL(clientid).Model; if (client == null || client.FullName != (string)dr["零售商名称"]) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",零售商ID与零售商名称不匹配!</span><br/>"; continue; } if (dr["归属月份"].ToString() != month.Name) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",归属月份必须为【" + month.Name + "】</span><br/>"; continue; } //DateTime salesdate = (DateTime)dr["销售日期"]; //if (salesdate < minday || salesdate > maxday) //{ // ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",销售日期必须在" + minday.ToString("yyyy-MM-dd") + // "至" + maxday.ToString("yyyy-MM-dd") + "之前!</span><br/>"; // continue; //} int promotorid = (int)dr["导购ID"]; if (promotorid > 0) { if (PM_PromotorInRetailerBLL.GetModelList("Client=" + client.ID.ToString() + " AND Promotor=" + promotorid.ToString()).Count == 0) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",零售商中没有关联该导购员!</span><br/>"; continue; } } #endregion #region 组织销量头 SVM_SalesVolumeBLL bll = null; IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Supplier=" + clientid.ToString() + " AND Type=3 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd") + " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' AND ISNULL(Promotor,0)=" + promotorid.ToString()); if (svmlists.Count > 0) { if (svmlists[0].ApproveFlag == 1) { ImportInfo += "<span style='color: Red'>序号:" + dr["序号"].ToString() + ",该零售商当月的销量已审核,不可再次导入!</span><br/>"; continue; } bll = new SVM_SalesVolumeBLL(svmlists[0].ID); bll.Model["SubmitFlag"] = "1"; bll.Items.Clear(); } else { bll = new SVM_SalesVolumeBLL(); bll.Model.Client = 0; bll.Model.OrganizeCity = client.OrganizeCity; bll.Model.Supplier = client.ID; bll.Model.Promotor = promotorid; bll.Model.AccountMonth = month.ID; bll.Model.SalesDate = maxday; bll.Model.Type = 3; bll.Model.ApproveFlag = 2; bll.Model.Flag = 1; //成品销售 bll.Model["SubmitFlag"] = "1"; bll.Model["IsCXP"] = "N"; bll.Model.InsertStaff = (int)Session["UserID"]; bll.Model.Remark = "Excel批量导入"; } #endregion #region 读取各产品销量 IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>(); foreach (PDT_Product product in productlists) { int quantity = (int)dr["[" + product.ShortName + "]"]; if (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.StdPrice; } SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail(); detail.Product = product.ID; detail.FactoryPrice = factoryprice; detail.SalesPrice = salesprice; detail.Quantity = quantity; details.Add(detail); } } #endregion #region 更新销量至数据库 if (bll.Model.ID > 0) { if (details.Count > 0) { bll.DeleteDetail(); //先清除原先导入的数据 bll.Items = details; bll.AddDetail(); bll.Update(); ImportInfo += "<span style='color: Blue'>序号:" + dr["序号"].ToString() + ",零售商:" + client.FullName + " 的原有日期为:" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的销量单被成功更新!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } } else { //if (details.Count > 0) //没有产品也新增一条空销量头 { bll.Items = details; bll.Add(); ImportInfo += "<span style='color: Black'>序号:" + dr["序号"].ToString() + ",零售商:" + client.FullName + " 的销量单已成功导入!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } } #endregion } #endregion lb_ErrorInfo.Text = ImportInfo; bt_Import.Enabled = false; Session["DataTable-SellIn"] = null; Session["DataTable-SellOut"] = null; }
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; tbx_Msg.AppendText("\r\n"); tbx_Msg.AppendText("-------------------------------------------------------------------------\r\n"); tbx_Msg.AppendText("----批量导入客户进货----\r\n+----批量导入客户销量----\r\nID号 门店类型 门店名 导购员 错误原因\r\n"); #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()) { tbx_Msg.AppendText("ID号:" + clientid.ToString() + (client.ClientType == 3 ? ",零售商ID与零售商名称" : ",分销商ID与分销商名称") + "不匹配!\r\n"); State = 4; continue; } int uplimit = client.ClientType == 3 ? 5000 : 9000; if (((Range)worksheet.Cells[row, 5]).Text.ToString() != month.Name) { tbx_Msg.AppendText("ID号:" + clientid.ToString() + ",归属月份必须为【" + month.Name + "】\r\n"); 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 " + " AND Remark='" + tbx_Remark.Text + "'"; } 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() + " AND Remark='" + tbx_Remark.Text + "'"; } IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList(conditon); if (svmlists.Count > 0) { tbx_Msg.AppendText("行号:" + row.ToString() + ",ID号:" + ((Range)worksheet.Cells[row, 1]).Text.ToString() + client.FullName + " 当月的销量单已导入!\r\n"); continue; } 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 = tbx_Remark.Text; bll.Model["DataSource"] = tbx_DataSource.Text; //6:导购销售拆分 7:补录销量 } #endregion bll.Model["SubmitFlag"] = "1"; #region 读取各产品销量 IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>(); bool wrongflag = false;//判断导入数量是否正常(除空导致的异常) int quantity = 0; foreach (PDT_Product product in productlists) { if (((Range)worksheet.Cells[row, _cloumn]).Value2 != null) { int.TryParse(((Range)worksheet.Cells[row, _cloumn]).Value2.ToString(), out quantity); 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) { tbx_Msg.AppendText("行号:" + row.ToString() + ",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 + "数量填写错误。\r\n"); 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(); // tbx_Msg.AppendText("<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() + "\r\n"); //} } else { if (details.Count > 0) //没有产品也新增一条空销量头 { bll.Items = details; if (bll.Add() > 0) { tbx_Msg.AppendText("行号:" + row.ToString() + ",ID号:" + clientid.ToString() + (client.ClientType == 3 ? ",该零售商:" : ",该分销商:") + client.FullName + (cloumn == 6 ? " 的进货单" : "的销量单") + "已成功导入!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "\r\n"); tbx_Msg.Focus(); tbx_Msg.Select(tbx_Msg.TextLength, 0); tbx_Msg.ScrollToCaret(); } } } #endregion } #endregion return(ImportInfo); }