protected void dl_packaging_SelectedIndexChanged(object sender, EventArgs e) { IList <PDT_Product> _product = PDT_ProductBLL.GetModelList("classify=" + dl_classify.SelectedValue + " and packaging=" + dl_packaging.SelectedValue + "and pdt_product.approveflag=1"); dl_product.DataSource = _product; dl_product.DataBind(); dl_product_SelectedIndexChanged(null, null); }
private void BindTree() { //获取品牌 string condition = ""; if (Request.QueryString["IsOpponent"] != null) { condition = "IsOpponent=" + Request.QueryString["IsOpponent"]; } IList <PDT_Brand> _brands = PDT_BrandBLL.GetModelList(condition); dl_brand.DataSource = _brands; dl_brand.DataBind(); dl_brand.Items.Insert(0, new ListItem("请选择", "0")); dl_brand_SelectedIndexChanged(null, null); foreach (PDT_Brand brand in _brands) { TreeNode tn_brand = new TreeNode(brand.Name, brand.ID.ToString()); tr_Product.Nodes.Add(tn_brand); IList <PDT_Classify> _classifys = new PDT_ClassifyBLL()._GetModelList("PDT_Classify.Brand=" + brand.ID.ToString()); foreach (PDT_Classify classify in _classifys) { TreeNode tn_classify = new TreeNode(classify.Name, classify.ID.ToString()); tn_brand.ChildNodes.Add(tn_classify); string ConditionStr = "PDT_Product.State = 1 AND PDT_Product.Classify=" + classify.ID.ToString() + "and pdt_product.approveflag=1"; if (Request.QueryString["ExtCondition"] != null) { ConditionStr += " AND (" + Request.QueryString["ExtCondition"].Replace("\"", "").Replace('~', '\'') + ")"; } IList <PDT_Product> _products = PDT_ProductBLL.GetModelList(ConditionStr); foreach (PDT_Product product in _products) { TreeNode tn = new TreeNode(); tn.Text = product.Code + " " + product.FullName; tn.Value = product.ID.ToString(); tn_classify.ChildNodes.Add(tn); if (tn.Value == ViewState["ID"].ToString()) { tn_classify.Expand(); tn.Select(); tbx_SelectedProductID.Text = tn.Value; tbx_SelectedProductName.Text = tn.Text; } } } } }
protected void bt_AddAll_Click(object sender, EventArgs e) { string ConditionStr = " PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + ")"; if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1") { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_List.SelectedValue; ConditionStr += " AND PDT_Product.Category IN (" + _categoryids + ")"; } int count = 0; IList <PDT_Product> product = PDT_ProductBLL.GetModelList(ConditionStr); foreach (PDT_Product p in product) { int id = p.ID; PDT_ProductBLL bll = new PDT_ProductBLL(id); PDT_ProductExtInfo extinfo = new PDT_ProductExtInfo(); extinfo.Code = bll.Model.FactoryCode == "" ? bll.Model.Code : bll.Model.FactoryCode; extinfo.Supplier = (int)Session["OwnerClient"]; bll.SetProductExtInfo(extinfo); count++; } if (count > 0) { MessageBox.Show(this, string.Format("成功将{0}个商品加入经营范围!", count)); BindGrid(); } else { MessageBox.Show(this, "请选择要加入经营产商品!"); } }
private void BindGrid() { if (MCSTabControl1.SelectedIndex == 0) { ListTable <ORD_ApplyPublishDetail> _details = ViewState["Details"] as ListTable <ORD_ApplyPublishDetail>; gv_List.BindGrid <ORD_ApplyPublishDetail>(_details.GetListItem().OrderByDescending(p => p.Price).ThenBy(p => p["GiveLevel"]).ToArray()); } else { //获取非价表产品列表 string condition = "ID NOT IN (SELECT Product FROM MCS_Logistics.dbo.ORD_ApplyPublishDetail WHERE PublishID=" + ViewState["ID"].ToString() + ")"; if (ddl_Brand.SelectedValue != "0") { if (ddl_Classify.SelectedValue == "0") { condition += " AND Brand =" + ddl_Brand.SelectedValue; } else { condition += " AND Classify =" + ddl_Classify.SelectedValue; } } condition += " AND State = 1 AND ApproveFlag = 1 AND Brand IN (SELECT ID FROM MCS_PUB.dbo.PDT_Brand WHERE IsOpponent=9)"; if (tbx_ProductText.Text != "") { condition += " AND (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR Code like '%" + tbx_ProductText.Text + "%')"; } IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition); gv_NotInList.BindGrid <PDT_Product>(products); } cb_SelectAll.Checked = false; }
private void BindGrid() { int category = 0; int.TryParse(tr_Category.SelectValue, out category); if (MCSTabControl1.SelectedIndex == 0) { string condition = " 1 = 1 "; #region 获取产品分类 if (category > 1) { condition += " AND PDT_StandardPrice_Detail.Product IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString(); string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_Category.SelectValue; condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")"; condition += ")"; } #endregion if (tbx_ProductText.Text != "") { condition += "AND PDT_StandardPrice_Detail.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE "; condition += " (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR FactoryCode like '%" + tbx_ProductText.Text + "%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))"; condition += ")"; } IList <PDT_StandardPrice_Detail> list = new PDT_StandardPriceBLL((int)ViewState["ID"]).GetDetail(condition); gv_List.BindGrid <PDT_StandardPrice_Detail>(list); } else { //获取非价表产品列表 string condition = "PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_StandardPrice_Detail WHERE PriceID=" + ViewState["ID"].ToString() + ")"; condition += " AND State = 1 AND ApproveFlag = 1"; condition += " AND PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString(); #region 获取产品分类 if (category > 1) { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_Category.SelectValue; condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")"; } #endregion condition += ")"; if (tbx_ProductText.Text != "") { condition += " AND (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR Code like '%" + tbx_ProductText.Text + "%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))"; } IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition); gv_NotInList.BindGrid <PDT_Product>(products); } cb_SelectAll.Checked = false; }
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; }
protected void bt_DownloadTemplate_Click(object sender, EventArgs e) { #region 获取最迟的销量日期 int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays"); AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model; DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; #endregion #region 判断有无选择业代 if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0") { MessageBox.Show(this, "对不起,请选择责任业代!"); return; } #endregion #region 获取业代负责的零售商及所有产品数据 int staff = int.Parse(select_Staff.SelectValue); IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=3 AND ClientManager=" + staff.ToString() + " AND ActiveFlag=1 AND ApproveFlag=1 AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code"); if (clientlists.Count == 0) { MessageBox.Show(this, "对不起,没有当前人直接负责的终端店!"); return; } 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"); #endregion #region 组织文件路径及文件名 string path = ConfigHelper.GetConfigString("AttachmentPath"); if (path.StartsWith("~")) { path = Server.MapPath(path); } if (!path.EndsWith("\\")) { path = path + "\\"; } path += "ImportExcelSVM\\Download\\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string filename = "销量导入模板-" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls"; path += filename; #endregion #region 生成Excel文件 object missing = System.Reflection.Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass(); ExcelApp.Visible = false; ExcelApp.DisplayAlerts = false; Workbook workbook1 = null; Worksheet worksheet1 = null, worksheet2 = null; try { workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"]; worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing); worksheet1.Name = "零售商进货"; worksheet2.Name = "零售商销货"; #region 创建表头 worksheet1.Cells[1, 1] = "零售商ID"; worksheet1.Cells[1, 2] = "零售商编号"; worksheet1.Cells[1, 3] = "零售商名称"; worksheet1.Cells[1, 4] = "零售商分类"; worksheet1.Cells[1, 5] = "归属月份"; worksheet1.get_Range("B2", "B2").ColumnWidth = 15; worksheet1.get_Range("C2", "C2").ColumnWidth = 20; worksheet1.get_Range("D2", "E2").ColumnWidth = 10; worksheet1.get_Range("A1", "A1").RowHeight = 50; worksheet1.get_Range("A1", "CC1").WrapText = true; worksheet1.get_Range("A1", "CC1").Font.Bold = true; worksheet1.get_Range("A1", "CC1000").Font.Size = 9; worksheet1.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter; worksheet2.Cells[1, 1] = "零售商ID"; worksheet2.Cells[1, 2] = "零售商编号"; worksheet2.Cells[1, 3] = "零售商名称"; worksheet2.Cells[1, 4] = "零售商分类"; worksheet2.Cells[1, 5] = "归属月份"; worksheet2.Cells[1, 6] = "导购ID"; worksheet2.Cells[1, 7] = "导购姓名"; worksheet2.get_Range("B2", "B2").ColumnWidth = 15; worksheet2.get_Range("C2", "C2").ColumnWidth = 20; worksheet2.get_Range("D2", "G2").ColumnWidth = 10; worksheet2.get_Range("A1", "A1").RowHeight = 50; worksheet2.get_Range("A1", "CC1").WrapText = true; worksheet2.get_Range("A1", "CC1").Font.Bold = true; worksheet2.get_Range("A1", "CC1000").Font.Size = 9; worksheet2.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter; int bgcolor1 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow); int bgcolor2 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); //将产品写入表头 for (int i = 0; i < productlists.Count; i++) { worksheet1.Cells[1, 6 + i] = productlists[i].ShortName; worksheet2.Cells[1, 8 + i] = productlists[i].ShortName; #region 品牌设置产品列颜色 if (i > 0) { if (productlists[i].Brand == productlists[i - 1].Brand) { worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = worksheet1.get_Range(worksheet1.Cells[1, 6 + i - 1], worksheet1.Cells[1000, 6 + i - 1]).Interior.Color; worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = worksheet2.get_Range(worksheet2.Cells[1, 8 + i - 1], worksheet2.Cells[1000, 8 + i - 1]).Interior.Color; } else { if (int.Parse(worksheet1.get_Range(worksheet1.Cells[1, 6 + i - 1], worksheet1.Cells[1000, 6 + i - 1]).Interior.Color.ToString()) == bgcolor1) { worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor2; } else { worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor1; } if (int.Parse(worksheet2.get_Range(worksheet2.Cells[1, 8 + i - 1], worksheet2.Cells[1000, 8 + i - 1]).Interior.Color.ToString()) == bgcolor1) { worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor2; } else { worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor1; } } } else { worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor1; worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor1; } #endregion } #endregion #region 将零售商信息写入表格内 int sellinrow = 2, selloutrow = 2; foreach (CM_Client client in clientlists) { worksheet1.Cells[sellinrow, 1] = client.ID; worksheet1.Cells[sellinrow, 2] = client.Code; worksheet1.Cells[sellinrow, 3] = client.FullName; worksheet1.Cells[sellinrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name; worksheet1.Cells[sellinrow, 5] = "'" + month.Name; //worksheet1.Cells[sellinrow, 5] = day.ToString("yyyy-MM-dd"); sellinrow++; worksheet2.Cells[selloutrow, 1] = client.ID; worksheet2.Cells[selloutrow, 2] = client.Code; worksheet2.Cells[selloutrow, 3] = client.FullName; worksheet2.Cells[selloutrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name; worksheet2.Cells[selloutrow, 5] = "'" + month.Name; //worksheet2.Cells[selloutrow, 5] = day.ToString("yyyy-MM-dd"); IList <PM_Promotor> promotorlists = PM_PromotorBLL.GetModelList("ID IN (SELECT Promotor FROM dbo.PM_PromotorInRetailer WHERE Client = " + client.ID.ToString() + ") AND Dimission=1 AND ApproveFlag=1"); for (int j = 0; j < promotorlists.Count; j++) { if (j > 0) { worksheet2.Cells[selloutrow, 1] = client.ID; worksheet2.Cells[selloutrow, 2] = client.Code; worksheet2.Cells[selloutrow, 3] = client.FullName; worksheet2.Cells[selloutrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name; worksheet2.Cells[selloutrow, 5] = "'" + month.Name; //worksheet2.Cells[selloutrow, 5] = day.ToString("yyyy-MM-dd"); } worksheet2.Cells[selloutrow, 6] = promotorlists[j].ID; worksheet2.Cells[selloutrow, 7] = promotorlists[j].Name; if (j != promotorlists.Count - 1) { selloutrow++; } } selloutrow++; } #endregion #region 设置表格格式 //设置行高 worksheet1.get_Range(worksheet1.Cells[2, 1], worksheet1.Cells[sellinrow - 1, 1]).RowHeight = 16; worksheet2.get_Range(worksheet2.Cells[2, 1], worksheet2.Cells[selloutrow - 1, 1]).RowHeight = 16; //设置表格单元格格线 worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[sellinrow - 1, 6 + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); worksheet2.get_Range(worksheet2.Cells[1, 1], worksheet2.Cells[selloutrow - 1, 8 + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); #endregion worksheet1.Activate(); ExcelApp.AlertBeforeOverwriting = false; workbook1.SaveAs(path, XlFileFormat.xlExcel8, "", "", false, false, XlSaveAsAccessMode.xlNoChange, 1, false, missing, missing, missing); } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; lb_ErrorInfo.Text = error; MessageBox.Show(this, "系统错误-1!" + err.Message); } finally { if (workbook1 != null) { workbook1.Close(false, missing, missing); } if (worksheet1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1); } if (worksheet2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2); } if (workbook1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); } worksheet1 = null; worksheet2 = null; workbook1 = null; if (File.Exists(path)) { Downloadfile(path, filename); } } } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; lb_ErrorInfo.Text = error; MessageBox.Show(this, "系统错误-2!" + err.Message); } finally { if (ExcelApp != null) { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); //GC.WaitForPendingFinalizers(); } #endregion }
protected void bt_UploadExcel_Click(object sender, EventArgs e) { Session["DataTable-SellIn"] = null; Session["DataTable-SellOut"] = null; UpdatePanel3.Visible = false; bt_Import.Enabled = false; #region 保存文件 if (!FileUpload1.HasFile) { MessageBox.Show(this.Page, "请选择要上传的文件!"); return; } int FileSize = (FileUpload1.PostedFile.ContentLength / 1024); if (FileSize > ConfigHelper.GetConfigInt("MaxAttachmentSize")) { MessageBox.Show(this.Page, "上传的文件不能大于" + ConfigHelper.GetConfigInt("MaxAttachmentSize") + "KB!当前上传文件大小为:" + FileSize.ToString() + "KB"); return; } //判断文件格式 string FileName = FileUpload1.PostedFile.FileName; FileName = FileName.Substring(FileName.LastIndexOf('\\') + 1); FileName = FileName.Substring(0, FileName.LastIndexOf('.')); string FileExtName = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf(".") + 1).ToLowerInvariant(); if (FileExtName != "xls") { MessageBox.Show(this, "对不起,必须上传扩展名为xls的Excel文件!"); return; } string path = ConfigHelper.GetConfigString("AttachmentPath"); if (path.StartsWith("~")) { path = Server.MapPath(path); } if (!path.EndsWith("\\")) { path = path + "\\"; } path += "ImportExcelSVM\\Upload\\" + Session["UserName"].ToString() + "\\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path += FileName + "." + FileExtName; FileUpload1.SaveAs(path); #endregion #region 读取Excel文件 string ErrorInfo = ""; lb_ErrorInfo.Text = ""; object missing = System.Reflection.Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass(); ExcelApp.Visible = false; ExcelApp.DisplayAlerts = false; Workbook workbook1 = null; Worksheet worksheet1 = null, worksheet2 = null; try { workbook1 = ExcelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); #region 验证工作表数据格式 if (workbook1.Worksheets.Count != 2) { ErrorInfo += "Excel表格中必须且只能有2张工作表!"; goto End; } worksheet1 = (Worksheet)workbook1.Worksheets[1]; worksheet2 = (Worksheet)workbook1.Worksheets[2]; if (worksheet1.Name != "零售商进货") { ErrorInfo += "Excel表格中第1个工作表名称必须为【零售商进货】!"; goto End; } if (worksheet2.Name != "零售商销货") { ErrorInfo += "Excel表格中第2个工作表名称必须为【零售商销货】!"; goto End; } if (((Range)worksheet1.Cells[1, 1]).Text.ToString() != "零售商ID" || ((Range)worksheet1.Cells[1, 2]).Text.ToString() != "零售商编号" || ((Range)worksheet1.Cells[1, 3]).Text.ToString() != "零售商名称" || ((Range)worksheet1.Cells[1, 4]).Text.ToString() != "零售商分类" || ((Range)worksheet1.Cells[1, 5]).Text.ToString() != "归属月份") { ErrorInfo += "零售商进货工作表表头(1~5列)错误!"; goto End; } if (((Range)worksheet2.Cells[1, 1]).Text.ToString() != "零售商ID" || ((Range)worksheet2.Cells[1, 2]).Text.ToString() != "零售商编号" || ((Range)worksheet2.Cells[1, 3]).Text.ToString() != "零售商名称" || ((Range)worksheet2.Cells[1, 4]).Text.ToString() != "零售商分类" || ((Range)worksheet2.Cells[1, 5]).Text.ToString() != "归属月份" || ((Range)worksheet2.Cells[1, 6]).Text.ToString() != "导购ID" || ((Range)worksheet2.Cells[1, 7]).Text.ToString() != "导购姓名") { ErrorInfo += "零售商销货工作表表头(1~7列)错误!"; goto End; } string ParamValue = Addr_OrganizeCityParamBLL.GetValueByType(1, 26); ParamValue = string.IsNullOrEmpty(ParamValue) ? "0" : ParamValue; ParamValue = ParamValue.EndsWith(",") ? ParamValue.Remove(ParamValue.Length - 1) : ParamValue; IList <PDT_Product> productlists = PDT_ProductBLL.GetModelList(" Brand NOT IN(" + ParamValue + ") AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code"); for (int i = 0; i < productlists.Count; i++) { if (((Range)worksheet1.Cells[1, 6 + i]).Text.ToString() != productlists[i].ShortName) { ErrorInfo += "零售商进货工作表表头,(" + (6 + i).ToString() + "列)产品名称错误!必须为:" + productlists[i].ShortName; goto End; } if (((Range)worksheet2.Cells[1, 8 + i]).Text.ToString() != productlists[i].ShortName) { ErrorInfo += "零售商进货工作表表头,(" + (8 + i).ToString() + "列)产品名称错误!必须为:" + productlists[i].ShortName; goto End; } } #endregion #region 创建DataTable System.Data.DataTable dtSellIn = new System.Data.DataTable(); dtSellIn.Columns.Add(new System.Data.DataColumn("序号", Type.GetType("System.Int32"))); dtSellIn.Columns.Add(new System.Data.DataColumn("零售商ID", Type.GetType("System.Int32"))); dtSellIn.Columns.Add(new System.Data.DataColumn("零售商编号", Type.GetType("System.String"))); dtSellIn.Columns.Add(new System.Data.DataColumn("零售商名称", Type.GetType("System.String"))); dtSellIn.Columns.Add(new System.Data.DataColumn("零售商分类", Type.GetType("System.String"))); dtSellIn.Columns.Add(new System.Data.DataColumn("归属月份", Type.GetType("System.String"))); System.Data.DataTable dtSellOut = new System.Data.DataTable(); dtSellOut.Columns.Add(new System.Data.DataColumn("序号", Type.GetType("System.Int32"))); dtSellOut.Columns.Add(new System.Data.DataColumn("零售商ID", Type.GetType("System.Int32"))); dtSellOut.Columns.Add(new System.Data.DataColumn("零售商编号", Type.GetType("System.String"))); dtSellOut.Columns.Add(new System.Data.DataColumn("零售商名称", Type.GetType("System.String"))); dtSellOut.Columns.Add(new System.Data.DataColumn("零售商分类", Type.GetType("System.String"))); dtSellOut.Columns.Add(new System.Data.DataColumn("归属月份", Type.GetType("System.String"))); dtSellOut.Columns.Add(new System.Data.DataColumn("导购ID", Type.GetType("System.Int32"))); dtSellOut.Columns.Add(new System.Data.DataColumn("导购姓名", Type.GetType("System.String"))); foreach (PDT_Product product in productlists) { dtSellIn.Columns.Add(new System.Data.DataColumn("[" + product.ShortName + "]", Type.GetType("System.Int32"))); dtSellOut.Columns.Add(new System.Data.DataColumn("[" + product.ShortName + "]", Type.GetType("System.Int32"))); } #endregion #region 读取Excel表格--零售商进货 int sellinrow = 1; int emptyrow = 0; while (true) { sellinrow++; if (((Range)worksheet1.Cells[sellinrow, 1]).Text.ToString() == string.Empty) { emptyrow++; if (emptyrow > 5) { break; } else { continue; } } int clientid = 0; if (!int.TryParse(((Range)worksheet1.Cells[sellinrow, 1]).Text.ToString(), out clientid)) { continue; } //DateTime selldate; //if (!DateTime.TryParse(((Range)worksheet1.Cells[sellinrow, 5]).Text.ToString(), out selldate)) //{ // continue; //} System.Data.DataRow row = dtSellIn.NewRow(); row[0] = sellinrow - 1; row[1] = clientid; row[2] = ((Range)worksheet1.Cells[sellinrow, 2]).Text.ToString(); row[3] = ((Range)worksheet1.Cells[sellinrow, 3]).Text.ToString(); row[4] = ((Range)worksheet1.Cells[sellinrow, 4]).Text.ToString(); row[5] = ((Range)worksheet1.Cells[sellinrow, 5]).Text.ToString(); for (int i = 0; i < productlists.Count; i++) { int quantity = 0; int.TryParse(((Range)worksheet1.Cells[sellinrow, 6 + i]).Text.ToString(), out quantity); row[6 + i] = quantity; } dtSellIn.Rows.Add(row); } Session["DataTable-SellIn"] = dtSellIn; #endregion #region 读取Excel表格--零售商销货 int selloutrow = 1; emptyrow = 0; while (true) { selloutrow++; if (((Range)worksheet2.Cells[selloutrow, 1]).Text.ToString() == string.Empty) { emptyrow++; if (emptyrow > 5) { break; } else { continue; } } int clientid = 0; if (!int.TryParse(((Range)worksheet2.Cells[selloutrow, 1]).Text.ToString(), out clientid)) { continue; } //DateTime selldate; //if (!DateTime.TryParse(((Range)worksheet2.Cells[selloutrow, 5]).Text.ToString(), out selldate)) //{ // continue; //} int promotorid = 0; if (((Range)worksheet2.Cells[selloutrow, 6]).Text.ToString() != "" && !int.TryParse(((Range)worksheet2.Cells[selloutrow, 6]).Text.ToString(), out promotorid)) { continue; } System.Data.DataRow row = dtSellOut.NewRow(); row[0] = selloutrow - 1; row[1] = clientid; row[2] = ((Range)worksheet2.Cells[selloutrow, 2]).Text.ToString(); row[3] = ((Range)worksheet2.Cells[selloutrow, 3]).Text.ToString(); row[4] = ((Range)worksheet2.Cells[selloutrow, 4]).Text.ToString(); row[5] = ((Range)worksheet2.Cells[selloutrow, 5]).Text.ToString(); row[6] = promotorid; row[7] = ((Range)worksheet2.Cells[selloutrow, 7]).Text.ToString(); for (int i = 0; i < productlists.Count; i++) { int quantity = 0; int.TryParse(((Range)worksheet2.Cells[selloutrow, 8 + i]).Text.ToString(), out quantity); row[8 + i] = quantity; } dtSellOut.Rows.Add(row); } Session["DataTable-SellIn"] = dtSellIn; Session["DataTable-SellOut"] = dtSellOut; #endregion End: ; } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; lb_ErrorInfo.Text = error; MessageBox.Show(this, "系统错误-4!" + err.Message); } finally { if (workbook1 != null) { workbook1.Close(false, missing, missing); } if (worksheet1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1); } if (worksheet2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2); } if (workbook1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); } worksheet1 = null; worksheet2 = null; workbook1 = null; } } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; lb_ErrorInfo.Text = error; MessageBox.Show(this, "系统错误-5!" + err.Message); } finally { if (ExcelApp != null) { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); //GC.WaitForPendingFinalizers(); if (ErrorInfo != "") { lb_ErrorInfo.Text = ErrorInfo; MessageBox.Show(this, "对不起,Excel文件打开错误,请确认格式是否正确。错误提示:" + ErrorInfo); bt_Import.Enabled = false; } else { BindGrid(); MessageBox.Show(this, "Excel文件上传并成功读取,请确认表格中的数据,无误后点【确认提交】按钮!"); bt_Import.Enabled = true; } } #endregion }
private void BindGrid() { if (MCSTabControl1.SelectedIndex == 0) { tr1.Visible = false; tr_Product.Visible = true; string condition = " 1 = 1 "; if (ddl_Brand.SelectedValue != "0") { if (ddl_Classify.SelectedValue == "0") { condition = " Brand =" + ddl_Brand.SelectedValue; } else { condition = " Classify =" + ddl_Classify.SelectedValue; } } condition += " ORDER BY PDT_Product.Code"; PDT_ProductPriceBLL bll = new PDT_ProductPriceBLL((int)ViewState["PriceID"]); gv_List.BindGrid(bll.GetDetail(condition)); } else { tr1.Visible = true; tr_Product.Visible = false; //获取非价表产品列表 string condition = "State = 1 AND ID NOT IN (SELECT Product FROM MCS_Pub.dbo.PDT_ProductPrice_Detail WHERE PriceID=" + ViewState["PriceID"].ToString() + ")"; if (ddl_Brand.SelectedValue == "0") { condition += " AND Brand in (SELECT ID FROM PDT_Brand WHERE IsOpponent in ('1'))"; } else { if (ddl_Classify.SelectedValue == "0") { condition += " AND Brand =" + ddl_Brand.SelectedValue; } else { condition += " AND Classify =" + ddl_Classify.SelectedValue; } } if ((int)ViewState["StandardPrice"] != 0) { condition += " AND ID IN (SELECT Product FROM MCS_Pub.dbo.PDT_StandardPrice_Detail WHERE StandardPrice=" + ViewState["StandardPrice"].ToString() + ")"; } IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition); gv_List_FacProd.BindGrid <PDT_Product>(products); } cb_SelectAll.Checked = false; }
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 void BindGrid() { if (MCSTabControl1.SelectedIndex == 0) { tr1.Visible = false; tr_Product.Visible = true; string condition = " 1 = 1 "; if (ddl_Brand.SelectedValue != "0") { if (ddl_Classify.SelectedValue == "0") { condition = " Brand =" + ddl_Brand.SelectedValue; } else { condition = " Classify =" + ddl_Classify.SelectedValue; } } condition += " ORDER BY PDT_Product.Code"; PDT_StandardPriceBLL bll = new PDT_StandardPriceBLL((int)ViewState["PriceID"]); gv_List.BindGrid(bll.GetDetail(condition)); if (!Right_Assign_BLL.GetAccessRight((string)Session["UserName"], 1410, "ModifyFactoryPrice")) { foreach (GridViewRow row in gv_List.Rows) { TextBox tbx = (TextBox)row.FindControl("tbx_FactoryPrice"); if (tbx != null) { tbx.Enabled = false; } } } } else { tr1.Visible = true; tr_Product.Visible = false; //获取非价表产品列表 string condition = "State=1 AND ID NOT IN (SELECT Product FROM PDT_StandardPrice_Detail WHERE StandardPrice=" + ViewState["PriceID"].ToString() + ")"; if (ddl_Brand.SelectedValue == "0") { condition += " AND Brand in (SELECT ID FROM PDT_Brand WHERE IsOpponent in ('1'))"; } else { if (ddl_Classify.SelectedValue == "0") { condition += " AND Brand =" + ddl_Brand.SelectedValue; } else { condition += " AND Classify =" + ddl_Classify.SelectedValue; } } IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition); gv_List_FacProd.BindGrid <PDT_Product>(products); } }
/// <summary> /// 同步经销商进货单 /// </summary> /// <param name="begindate"></param> /// <param name="enddate"></param> /// <param name="clientcode"></param> private void SyncSellOut(DateTime begindate, DateTime enddate, string clientcode) { try { int success = 0; //从进销存数据库中读取出货数据 DataTable dtShipHeader = null; if (string.IsNullOrEmpty(clientcode)) { dtShipHeader = ERPIF.GetShipHeader(begindate, enddate); } else { SendMessage(0, "经销商代码:" + clientcode); dtShipHeader = ERPIF.GetShipHeader(begindate, enddate, clientcode); } SendMessage(0, "本次共获取到" + dtShipHeader.Rows.Count.ToString() + "条发货单待同步!" + _sClientCode); for (int i = 0; i < dtShipHeader.Rows.Count; i++) { DataRow dr = dtShipHeader.Rows[i]; if (_bCancelSync) { SendMessage(0, "同步进行中时,被用户取消!"); break; } #region 逐条同步发货单 try { string SheetCode = dr["DELIVERY_ID"].ToString().Trim(); string ClientCode = dr["CUST_NUMBER"].ToString().Trim(); DateTime SalesDate = DateTime.Parse(dr["CARRY_SDATE"].ToString().Trim()); string DJType = dr["ORDER_TYPE"].ToString().Trim(); decimal discount = 0; decimal.TryParse(dr["DISCOUNT"].ToString().Trim(), out discount); if (DJType == "R") { continue; } if (ORD_OrderDeliveryBLL.GetModelList(" SheetCode = '" + SheetCode + "'").Count > 0) { continue; } #region 判断客户号有效否 IList <CM_Client> clients = CM_ClientBLL.GetModelList(" Code= '" + ClientCode.ToString() + "' AND ActiveFlag=1 AND ApproveFlag=1"); if (clients.Count == 0) { SendMessage(-11, "经销商编号为" + ClientCode.ToString() + "的匹配错误!"); continue; } CM_Client client = clients[0]; if (client["DIClassify"] == "3") { //经销商子户头 client = new CM_ClientBLL(client.Supplier).Model; if (client == null) { SendMessage(-12, "经销商编号为" + ClientCode.ToString() + "对应的经销商为子户头,但未关联正确的主户头!"); continue; } } if (client["DIClassify"] != "1") { SendMessage(-13, "经销商编号为" + ClientCode.ToString() + "对应的经销商不是主户头!"); continue; } #endregion #region 生成销量单头 ORD_OrderDeliveryBLL bll = new ORD_OrderDeliveryBLL(); bll.Model.SheetCode = SheetCode; bll.Model.AccountMonth = AC_AccountMonthBLL.GetMonthByDate(SalesDate); bll.Model.OrganizeCity = client.OrganizeCity; bll.Model.State = 1; //未发货 bll.Model.Client = client.ID; bll.Model.Store = client.Supplier; if (dr["CARRY_EDATE"].ToString() != "") { bll.Model.PreArrivalDate = DateTime.Parse(dr["CARRY_EDATE"].ToString().Trim()); } else { bll.Model.PreArrivalDate = SalesDate.AddDays(7); //默认7天后到货 } bll.Model.ApproveFlag = 1; bll.Model.InsertStaff = 1; bll.Model["DeliveryTime"] = SalesDate.ToString("yyyy-MM-dd HH:mm"); bll.Model["TruckNo"] = dr["CARRY_CAR"].ToString().Trim(); bll.Model["DeliveryStaff"] = "1"; bll.Model["DeliveryRemark"] = "ERP发货单提取"; bll.Model["Discount"] = discount.ToString("0.###"); bll.Model["CarrySheetCode"] = dr["REQUEST_NUMBER"].ToString().Trim(); #endregion //获取进销存明细 DataTable dt_detail = ERPIF.GetShipDetail(SheetCode); #region 生成销量明细 foreach (DataRow dr_detail in dt_detail.Rows) { string productcode = dr_detail["ITEM_NO"].ToString(); decimal price = decimal.Parse(dr_detail["PRICE"].ToString()); int quantity = (int)decimal.Parse(dr_detail["SHIP_QTY"].ToString()); string packaging = dr_detail["UOM"].ToString(); if (quantity == 0) { continue; } #region 验证产品信息 PDT_Product product = null; if (price == 0) { #region 价格为0,表示为赠品,取赠品目录中的物料 IList <PDT_Product> _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent=9)"); if (_products.Count > 0) { product = _products[0]; } else { _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent <> 9)"); if (_products.Count == 0) { SendMessage(-21, string.Format("发货单号为{0}内,产品编号{1}未在产品目录库中未找到!", SheetCode, productcode)); continue; } else { //在成品目录里找到,但未在赠品目录中,自动在赠品目录中增加一条本品赠品目录 PDT_ProductBLL productbll = new PDT_ProductBLL(); productbll.Model = _products[0]; productbll.Model.Brand = 3; //本品赠品 productbll.Model.Classify = 100; //其他品类 productbll.Model.State = 2; //停用 productbll.Model.ApproveFlag = 2; //未审核 int productid = productbll.Add(); product = new PDT_ProductBLL(productid).Model; SendMessage(0, string.Format("发货单号为{0}内,产品编号{1}未在本品赠品目录库中未找到,自动从产品库中增加该赠品信息!", SheetCode, productcode)); } } #endregion } else { #region 从产品目录中取该产品信息 IList <PDT_Product> _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent<>9)"); if (_products.Count > 0) { product = _products[0]; } else { _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent = 9)"); if (_products.Count == 0) { SendMessage(-22, string.Format("发货单号为{0}内,产品编号{1}未在产品物料目录库中未找到!", SheetCode, productcode)); continue; } else { //在赠品目录中找到,则使用赠品目录的产品ID //不在产品中增加该成品 2012-04-25 product = _products[0]; ////在赠品目录里找到,但未在成品目录中,自动在成品目录中增加一条本品成品目录 //PDT_ProductBLL productbll = new PDT_ProductBLL(); //productbll.Model = _products[0]; //productbll.Model.Brand = 1; //productbll.Model.Classify = 1; //productbll.Model.State = 2; //停用 //productbll.Model.ApproveFlag = 2; //未审核 //int productid = productbll.Add(); //product = new PDT_ProductBLL(productid).Model; //SendMessage(0, string.Format("发货单号为{0}内,产品编号{1}未在成品目录库中未找到,自动从赠品库中增加该产品信息!", SheetCode, productcode)); } } #endregion } if (product.State == 2 && product.ApproveFlag == 1) { //确认被停用的产品,不再同步 continue; } if (product.State == 3 && !string.IsNullOrEmpty(product["MasterProduct"]) && product["MasterProduct"] != "0") { //是子产品,自动归属到主产品 PDT_Product _p = new PDT_ProductBLL(int.Parse(product["MasterProduct"])).Model; if (_p != null) { product = _p; } } if (product == null) { SendMessage(-23, productcode + "为无效产品编号!"); continue; } #endregion if (packaging == "Ea" && product.ConvertFactor > 1) { //整箱单位,转换为零售单位 quantity = quantity * product.ConvertFactor; price = price / product.ConvertFactor; } ORD_OrderDeliveryDetail item = bll.Items.FirstOrDefault (p => p.Product == product.ID && p.Client == client.ID); if (item != null) { item.DeliveryQuantity += quantity; } else { item = new ORD_OrderDeliveryDetail(); item.Client = client.ID; item.Product = product.ID; item.Price = price; item.FactoryPrice = price == 0 ? product.FactoryPrice : price; item.DeliveryQuantity = quantity; item.SignInQuantity = 0; item.BadQuantity = 0; item.LostQuantity = 0; bll.Items.Add(item); } } #endregion if (bll.Items.Count == 0) { SendMessage(0, "发货单号" + bll.Model.SheetCode + ",未获取到发货单产品明细"); continue; } #region 写入发货单 if (bll.Add() > 0) { //bll.Delivery(1); //设为发货状态(20140708修改为数据库作业执行) SendMessage(0, "成功导入发货数据!单号:" + bll.Model.SheetCode + ",发生日期:" + SalesDate.ToString("yyyy-MM-dd") + ",产品明细条数:" + bll.Items.Count.ToString() + ",同步进度:" + (i + 1).ToString() + "/" + dtShipHeader.Rows.Count.ToString()); } else { SendMessage(-40, "导入发货数据失败!单号" + bll.Model.SheetCode); continue; } #endregion success += 1; } catch (Exception err) { SendMessage(0, err.StackTrace + err.Source + err.Message); continue; } #endregion } SendMessage(0, "------" + begindate.ToString("yyyy-MM-dd HH:mm") + "至" + enddate.ToString("yyyy-MM-dd HH:mm") + "。总共同步完成" + success.ToString() + "条发货单记录!-------"); } catch (Exception err) { SendMessage(0, "SyncSellOut()" + err.StackTrace + err.Source + err.Message); } }
public void ImportSAPService() { while (bIsRunning) { try { ImportExcelTDP.SAP_ZSD.Z_SD_SD_SHService sd = new ImportExcelTDP.SAP_ZSD.Z_SD_SD_SHService(); NetworkCredential c = new NetworkCredential(); c.UserName = ConfigHelper.GetConfigString("SAP_ZSD_User"); c.Password = ConfigHelper.GetConfigString("SAP_ZSD_Password"); sd.Credentials = c; ImportExcelTDP.SAP_ZSD.Ztdplog[] results1 = new ImportExcelTDP.SAP_ZSD.Ztdplog[1024]; ImportExcelTDP.SAP_ZSD.Ztdplog[] results2 = new ImportExcelTDP.SAP_ZSD.Ztdplog[1024]; sd.ZSdSdSh("1", "5200", DateTime.Today.AddDays(-1).ToString("yyyyMMdd"), ref results1); sd.ZSdSdSh("2", "5200", DateTime.Today.AddDays(-1).ToString("yyyyMMdd"), ref results1); sd.ZSdSdSh("1", "5200", DateTime.Today.ToString("yyyyMMdd"), ref results1); sd.ZSdSdSh("2", "5200", DateTime.Today.ToString("yyyyMMdd"), ref results2); List <ImportExcelTDP.SAP_ZSD.Ztdplog> results = new List <ImportExcelTDP.SAP_ZSD.Ztdplog>(); results.AddRange(results1); results.AddRange(results2); SendMessage("ImportSAPService", "获取到" + results.Count().ToString() + "条发货数量!"); if (results.Count() == 0) { Thread.Sleep(3600 * 1000); continue; } PBM_DeliveryBLL bll = new PBM_DeliveryBLL(); decimal totalamount = 0; foreach (ImportExcelTDP.SAP_ZSD.Ztdplog item in results.OrderBy(p => p.Vgbel)) { if (item.Vgbel != bll.Model.SheetCode) { #region 更新发货单 if (bll.Model.SheetCode != "" && bll.Items.Count > 0) { //计算折扣金额 bll.Model.DiscountAmount = bll.Items.Sum(p => (1 - p.DiscountRate) * Math.Round(p.Price * p.ConvertFactor, 2) * p.DeliveryQuantity / p.ConvertFactor); //明细合计金额 decimal actamount = Math.Round(bll.Items.Sum(p => p.DiscountRate * Math.Round(p.Price * p.ConvertFactor, 2) * p.SignInQuantity / p.ConvertFactor), 2); //优惠金额(明细合计金额与实际采购金额的差额) bll.Model.WipeAmount = actamount - totalamount; //计算实际销售金额 bll.Model.ActAmount = (bll.Model.Classify == 12 ? -1 : 1) * (actamount - bll.Model.WipeAmount); int ret = bll.Add(); if (ret > 0) { SendMessage("ImportSAPService", "单据号" + item.Vgbel + ",正在提交,总金额" + bll.Model.ActAmount.ToString("0.##") + "!"); } else { SendMessage("ImportSAPService", "单据号" + item.Vgbel + ",提交失败!Ret=" + ret.ToString()); } bll = null; totalamount = 0; } #endregion #region 新增采购单据 //判断单号有没有导入过 if (PBM_DeliveryBLL.GetModelList("SheetCode='" + item.Vgbel + "' AND Supplier=1000 AND Classify IN (11,12)").Count > 0) { SendMessage("ImportSAPService", "单据号" + item.Vgbel + ",之前已存在!"); continue; } bll = new PBM_DeliveryBLL(); bll.Model.Classify = 11; #region 判断TDP编码Kunage string tdpcode = item.Kunag.Substring(3); IList <CM_ClientManufactInfo> _clients = CM_ClientManufactInfoBLL.GetModelList("Code='" + tdpcode + "' AND Manufacturer=1000 AND State = 1"); if (_clients.Count == 0) { SendMessage("ImportSAPService", "门店编码无效,单据号" + item.Vgbel + ",客户编号:" + tdpcode); continue; } #endregion bll.Model.SheetCode = item.Vgbel; bll.Model.Supplier = 1000; bll.Model.Client = _clients[0].Client; #region 获取经销商主仓库 IList <CM_WareHouse> _warehouses = CM_WareHouseBLL.GetEnbaledByClient(bll.Model.Client).Where(p => p.Classify == 1).OrderBy(p => p.ID).ToList(); if (_warehouses.Count == 0) { SendMessage("ImportSAPService", "未找到客户主仓库,单据号" + item.Vgbel + ",客户编号:" + tdpcode); continue; } #endregion bll.Model.ClientWareHouse = _warehouses[0].ID; bll.Model.PrepareMode = 1; bll.Model.State = 1; //默认制单状态 bll.Model.WipeAmount = 0; bll.Model.Remark = "SAP导入"; bll.Model.ApproveFlag = 2; bll.Model.InsertStaff = 1; DateTime _prearrivaldate = DateTime.Today; DateTime.TryParse(item.WadatIst, out _prearrivaldate); bll.Model.PreArrivalDate = _prearrivaldate; #endregion } #region 新增商品明细 string productcode = item.Matnr.Substring(item.Matnr.Length - 6); IList <PDT_Product> _products = PDT_ProductBLL.GetModelList("FactoryCode='" + productcode + "' AND State=1"); if (_products.Count == 0) { SendMessage("ImportSAPService", "商品编码无效,单据号" + item.Vgbel + ",商品编号:" + productcode); continue; } int productid = _products[0].ID; PDT_ProductBLL productbll = new PDT_ProductBLL(productid); PBM_DeliveryDetail d = new PBM_DeliveryDetail(); d.Product = productid; d.ConvertFactor = _products[0].ConvertFactor == 0 ? 1 : _products[0].ConvertFactor; d.SalesMode = 1; d.DeliveryQuantity = (int)Math.Round(item.Fkimg * d.ConvertFactor, 0); if (d.DeliveryQuantity < 0) { bll.Model.Classify = 12; //采购退库单 d.DeliveryQuantity = 0 - d.DeliveryQuantity; } d.SignInQuantity = d.DeliveryQuantity; d.CostPrice = Math.Round(item.Kzwi / d.DeliveryQuantity, 4); d.Price = d.CostPrice; d.DiscountRate = Math.Round(item.Kzwi5 / item.Kzwi, 4); d.LotNumber = item.Charg; totalamount += item.Kzwi5; DateTime _productdate = new DateTime(1900, 1, 1); DateTime.TryParse(item.Hsdat, out _productdate); if (_productdate.Year < 1900) { _productdate = new DateTime(1900, 1, 1); } d.ProductDate = _productdate; d.Remark = item.Fkart; bll.Items.Add(d); #endregion #region 判断是否在默认价表中 PDT_ProductExtInfo extinfo = productbll.GetProductExtInfo(bll.Model.Client); if (extinfo == null) { extinfo = new PDT_ProductExtInfo(); extinfo.Supplier = bll.Model.Client; extinfo.Product = productid; extinfo.Category = productbll.Model.Category; extinfo.Code = productbll.Model.FactoryCode; extinfo.SalesState = 1; extinfo.BuyPrice = d.Price; extinfo.SalesPrice = d.Price; new PDT_ProductBLL(productid).SetProductExtInfo(extinfo); } #endregion } #region 更新发货单 if (bll.Model.SheetCode != "" && bll.Items.Count > 0) { //计算折扣金额 bll.Model.DiscountAmount = bll.Items.Sum(p => (1 - p.DiscountRate) * Math.Round(p.Price * p.ConvertFactor, 2) * p.DeliveryQuantity / p.ConvertFactor); //计算实际销售金额 bll.Model.ActAmount = Math.Round((bll.Model.Classify == 12 ? -1 : 1) * bll.Items.Sum(p => p.DiscountRate * Math.Round(p.Price * p.ConvertFactor, 2) * p.SignInQuantity / p.ConvertFactor) - bll.Model.WipeAmount, 2); int ret = bll.Add(); if (ret > 0) { SendMessage("ImportSAPService", "单据号" + bll.Model.SheetCode + ",提交成功,总金额" + bll.Model.ActAmount.ToString("0.##") + "!"); } else { SendMessage("ImportSAPService", "单据号" + bll.Model.SheetCode + ",提交失败!Ret=" + ret.ToString()); } } #endregion Thread.Sleep(3600 * 1000); } catch (ThreadAbortException e) { this.SendMessage("ImportSAPService.ThreadAbortException", e.Message); Thread.Sleep(10000); continue; } catch (Exception ex) { this.SendMessage("ImportSAPService.Exception", ex.Message); Thread.Sleep(10000); continue; } } }
private void ImportProduct(string path) { string message = ""; string improtmessage = ""; AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetCurrentMonth() - 1).Model; DateTime minday = month.BeginDate; DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; int _State = 0; #region 读取Excel文件 string ErrorInfo = ""; object missing = System.Reflection.Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass(); ExcelApp.Visible = false; ExcelApp.DisplayAlerts = false; Workbook workbook1 = null; Worksheet worksheet1 = null; try { workbook1 = ExcelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); #region 验证工作表数据格式 worksheet1 = (Worksheet)workbook1.Worksheets[1]; if (worksheet1.Name != "零售商销货") { MessageBox.Show("Excel表格中第1个工作表名称必须为【零售商销货】!"); goto End; } if (!VerifyWorkSheet(worksheet1, 7)) { MessageBox.Show("零售商销货工作表表头(1~7列)错误!"); goto End; } 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"); for (int i = 0; i < productlists.Count; i++) { if (((Range)worksheet1.Cells[1, 8 + i]).Text.ToString() != productlists[i].ShortName) { MessageBox.Show("零售商进货工作表表头,(" + (8 + i).ToString() + "列)产品名称错误!必须为:" + productlists[i].ShortName); goto End; } } #endregion improtmessage += DoImportProduct(worksheet1, month.ID, 1, 8, productlists, out _State); End: ; } catch (ThreadAbortException exception3) { return; } catch (System.Exception err) { string error = "Message:" + err.Message + "\r\n" + "Source:" + err.Source + "\r\n" + "StackTrace:" + err.StackTrace + "\r\n"; MessageBox.Show(error); } finally { if (workbook1 != null) { workbook1.Close(false, missing, missing); } if (worksheet1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1); } if (workbook1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); } worksheet1 = null; workbook1 = null; } } catch (ThreadAbortException exception3) { return; } catch (System.Exception err) { MessageBox.Show("系统错误-5!" + err.Message); } finally { if (ExcelApp != null) { try { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } catch (System.Exception err) { string error = "Message:" + err.Message + "\r\n" + "Source:" + err.Source + "\r\n" + "StackTrace:" + err.StackTrace + "\r\n"; MessageBox.Show(error + "系统错误-6,Excel宏报错,请确认文件打开不报错再上传!" + err.Message); KillProcess(); } } GC.Collect(); //GC.WaitForPendingFinalizers(); if (ErrorInfo != "") { MessageBox.Show("对不起,Excel文件打开错误,请确认格式是否正确。错误提示:" + ErrorInfo); } } #endregion string filename = path.Substring(path.LastIndexOf('\\') + 1); //MessageBox.Show("导入成品进销", message != "" ? filename + "-" + message : filename + "导入操作成功!"); }
private void BindTree() { //获取品牌 string brandcondition = ""; if (Request.QueryString["IsOpponent"] != null) { if (Request.QueryString["IsOpponent"] == "10") { brandcondition = "IsOpponent IN (1,9)"; } else { brandcondition = "IsOpponent=" + Request.QueryString["IsOpponent"]; } } else { if ((int)Session["OwnerType"] == 2) { brandcondition = "IsOpponent='1'"; } } IList <PDT_Brand> _brands = PDT_BrandBLL.GetModelList(brandcondition); foreach (PDT_Brand brand in _brands) { TreeNode tn_brand = new TreeNode(brand.Name, brand.ID.ToString()); tr_Product.Nodes.Add(tn_brand); string ConditionStr = "PDT_Product.State = 1 AND PDT_Product.ApproveFlag = 1 AND PDT_Product.Brand = " + brand.ID.ToString(); if ((int)Session["OwnerType"] == 2) { ConditionStr += " AND PDT_Product.OwnerType = 2 AND PDT_Product.OwnerClient =" + Session["OwnerClient"].ToString(); } else if ((int)Session["OwnerType"] == 1) { ConditionStr += " AND PDT_Product.OwnerType IN (1, 2) "; } if (Request.QueryString["ExtCondition"] != null) { ConditionStr += " AND (" + Request.QueryString["ExtCondition"].Replace("\"", "").Replace('~', '\'') + ")"; } IList <PDT_Product> _products = PDT_ProductBLL.GetModelList(ConditionStr); foreach (PDT_Product product in _products) { TreeNode tn = new TreeNode(); tn.Text = product.FactoryCode + " " + product.FullName; tn.Value = product.ID.ToString(); tn_brand.ChildNodes.Add(tn); if (tn.Value == ViewState["ID"].ToString()) { tn_brand.Expand(); tn.Select(); tbx_SelectedProductID.Text = tn.Value; tbx_SelectedProductName.Text = tn.Text; } } } }
public string DoImportProduct(int TemplateID, int Client, ISheet Sheet, out int State) { string ImportInfo = "【商品资料】Excel表:"; State = 0; IPT_UploadTemplateBLL _template = new IPT_UploadTemplateBLL(TemplateID); List <string> listPDT = new List <string>() { "序号", "大类", "小类", "商品编码", "产品名称", "规格型号", "大单位", "小单位", "整零换算系数", "整件重量", "销售价", "采购价", "保质期", "整件条码", "零售条码" }; DataTable dt = null; bool flag = VertifySheet(Sheet, listPDT, out dt, ref ImportInfo); if (!flag) { State = 4; return(ImportInfo); } foreach (DataRow dr in dt.Rows)//循环导入数据 { try { decimal _pdtBuyPrice = (!string.IsNullOrEmpty(dr["采购价"].ToString())) ? Convert.ToDecimal(dr["采购价"].ToString()) : 0; decimal _pdtSalesPrice = (!string.IsNullOrEmpty(dr["销售价"].ToString())) ? Convert.ToDecimal(dr["销售价"].ToString()) : 0; if (_pdtSalesPrice == 0) { continue; //销售价格围标表示门店不经营 } int _pdtID = 0; string _pdtCode = dr["商品编码"].ToString(); IList <PDT_ProductExtInfo> _listPDTExtInfo = PDT_ProductExtInfoBLL.GetProductExtInfoList_BySupplier(_template.Model.ClientID).Where(m => m.Code == _pdtCode).ToList(); if (_listPDTExtInfo != null && _listPDTExtInfo.Count > 0) { ImportInfo += string.Format("序号为{0},产品编码为{1}的产品与现有产品编码重复,跳过此行信息\r\n", dr["序号"], _pdtCode); continue; } int _pdtCategory = 0; //类别 IList <PDT_Product> listPdt = PDT_ProductBLL.GetModelList(string.Format(" OwnerType IN (1,2) AND FactoryCode='{0}' ", _pdtCode)); if (listPdt == null || listPdt.Count == 0) //添加新产品 { #region 获取产品类别 IList <PDT_Category> _listCategory = PDT_CategoryBLL.GetModelList(" SuperID=1 AND ApproveFlag=1 AND(OwnerType IN(1,2) OR (OwnerType=3 AND OwnerClient=" + _template.Model.ClientID.ToString() + " )) AND Name='" + dr["大类"].ToString() + "'");//获取大类(大类可能属于平台级和厂商级,也有可能属于经销商自己) if (_listCategory == null || _listCategory.Count == 0) { PDT_CategoryBLL _bllCategory = new PDT_CategoryBLL(); _bllCategory.Model.Name = dr["大类"].ToString(); _bllCategory.Model.SuperID = 1; _bllCategory.Model.EnabledFlag = "Y"; _bllCategory.Model.OwnerType = 3; _bllCategory.Model.OwnerClient = _template.Model.ClientID; _bllCategory.Model.Remark = "Excel批量导入"; _bllCategory.Model.ApproveFlag = 1; _bllCategory.Model.InsertTime = DateTime.Now; _bllCategory.Model.InsertStaff = _template.Model.InsertStaff; _pdtCategory = _bllCategory.Add(); } else { _pdtCategory = _listCategory[0].ID; } IList <PDT_Category> _listCategory2 = PDT_CategoryBLL.GetModelList(" ApproveFlag=1 AND SuperID=" + _pdtCategory.ToString() + " AND (OwnerType IN(1,2) OR (OwnerType=3 AND OwnerClient=" + _template.Model.ClientID.ToString() + ")) AND Name='" + dr["小类"].ToString() + "'");//获取小类 if (_listCategory2 == null || _listCategory2.Count == 0) { PDT_CategoryBLL _bllCategory = new PDT_CategoryBLL(); _bllCategory.Model.Name = dr["小类"].ToString(); _bllCategory.Model.SuperID = _pdtCategory; _bllCategory.Model.EnabledFlag = "Y"; _bllCategory.Model.OwnerType = 3; _bllCategory.Model.OwnerClient = _template.Model.ClientID; _bllCategory.Model.Remark = "Excel批量导入"; _bllCategory.Model.ApproveFlag = 1; _bllCategory.Model.InsertTime = DateTime.Now; _bllCategory.Model.InsertStaff = _template.Model.InsertStaff; _pdtCategory = _bllCategory.Add(); } else { _pdtCategory = _listCategory2[0].ID; } #endregion string _pdtName = dr["产品名称"].ToString(); if (string.IsNullOrEmpty(_pdtCode) && string.IsNullOrEmpty(_pdtName)) { ImportInfo += string.Format("序号为{0}的行产品编码和名称均为空,跳过此行信息\r\n", dr["序号"]); continue; } string _pdtSpec = dr["规格型号"].ToString(); IList <Dictionary_Data> _listData = DictionaryBLL.Dictionary_Data_GetAlllList(" TableName='PDT_Packaging' "); string _strPdtTrafficPackaging = _listData.First(m => m.Name == dr["大单位"].ToString()).Code; int _pdtTrafficPackaging = 0; int.TryParse(_strPdtTrafficPackaging, out _pdtTrafficPackaging); //整件单位 string _strPdtPackaging = _listData.First(m => m.Name == dr["小单位"].ToString()).Code; int _pdtPackaging = 0; int.TryParse(_strPdtPackaging, out _pdtPackaging); //零售包装 int _pdtConvertFactor = (!string.IsNullOrEmpty(dr["整零换算系数"].ToString())) ? Convert.ToInt32(dr["整零换算系数"].ToString()) : 0; //重要信息强制显示,出错时直接跳过此行 decimal _pdtWeight = 0; decimal.TryParse(dr["整件重量"].ToString(), out _pdtWeight); int _pdtExpiry = 0; int.TryParse(dr["保质期"].ToString(), out _pdtExpiry); string _pdtBoxBarCode = dr["整件条码"].ToString(); string _pdtBarCode = dr["零售条码"].ToString(); PDT_ProductBLL _bllProduct = new PDT_ProductBLL(); _bllProduct.Model.FullName = _pdtName; _bllProduct.Model.ShortName = _pdtName; _bllProduct.Model.Spec = _pdtSpec; _bllProduct.Model.TrafficPackaging = _pdtTrafficPackaging; _bllProduct.Model.Packaging = _pdtPackaging; _bllProduct.Model.ConvertFactor = _pdtConvertFactor; _bllProduct.Model.BoxBarCode = _pdtBoxBarCode; _bllProduct.Model.BarCode = _pdtBarCode; _bllProduct.Model.Weight = _pdtWeight; _bllProduct.Model.Expiry = _pdtExpiry; _bllProduct.Model.Category = _pdtCategory; _bllProduct.Model.State = 1; //在用产品 _bllProduct.Model.OwnerClient = _template.Model.ClientID; //所属经销商 _bllProduct.Model.OwnerType = 3; //经销商级 _bllProduct.Model.ApproveFlag = 1; _bllProduct.Model.InsertTime = DateTime.Now; _bllProduct.Model.InsertStaff = _template.Model.InsertStaff; _bllProduct.Model.Remark = "Excel批量导入"; _pdtID = _bllProduct.Add(); } else { _pdtID = listPdt[0].ID; //已存在的厂商或平台级产品 } IList <PDT_ProductExtInfo> _listPdtExtInfo = PDT_ProductExtInfoBLL.GetModelList(" Product=" + _pdtID.ToString() + " AND Supplier=" + _template.Model.ClientID.ToString() + " AND ApproveFlag=1 "); if (_listPdtExtInfo == null || _listPdtExtInfo.Count == 0) { PDT_ProductExtInfoBLL _pdtExtInfoBLL = new PDT_ProductExtInfoBLL(); _pdtExtInfoBLL.Model.Supplier = _template.Model.ClientID; _pdtExtInfoBLL.Model.Product = _pdtID; _pdtExtInfoBLL.Model.Code = _pdtCode; _pdtExtInfoBLL.Model.BuyPrice = _pdtBuyPrice; _pdtExtInfoBLL.Model.SalesPrice = _pdtSalesPrice; _pdtExtInfoBLL.Model.SalesState = 1; _pdtExtInfoBLL.Model.Category = _pdtCategory; _pdtExtInfoBLL.Model.Remark = "Excel批量导入"; _pdtExtInfoBLL.Model.ApproveFlag = 1; _pdtExtInfoBLL.Model.InsertStaff = _template.Model.InsertStaff; _pdtExtInfoBLL.Model.InsertTime = DateTime.Now; _pdtExtInfoBLL.Add(); } else { PDT_ProductExtInfoBLL _pdtExtInfoBLL = new PDT_ProductExtInfoBLL(_listPdtExtInfo[0].ID); _pdtExtInfoBLL.Model.BuyPrice = _pdtBuyPrice; _pdtExtInfoBLL.Model.SalesPrice = _pdtSalesPrice; _pdtExtInfoBLL.Model.Remark = "Excel批量导入时修改"; _pdtExtInfoBLL.Model.UpdateStaff = _template.Model.InsertStaff; _pdtExtInfoBLL.Model.UpdateTime = DateTime.Now; _pdtExtInfoBLL.Update(); } } catch (Exception ex) { ImportInfo += "导入序列号为" + dr["序号"].ToString() + "的数据行时出现错误,错误说明:" + ex.Message + "\r\n"; State = 5; continue; } } if (State == 0) { State = 3; } ImportInfo += (State == 3 ? "导入完成!\r\n" : ""); IPT_UploadTemplateMessageBLL _bllUploadTemplateMessage = new IPT_UploadTemplateMessageBLL(); _bllUploadTemplateMessage.Model.TemplateID = TemplateID; _bllUploadTemplateMessage.Model.MessageType = State; _bllUploadTemplateMessage.Model.Content = ImportInfo; _bllUploadTemplateMessage.Add(); return(ImportInfo); }