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);
}