public void BindDropDown()
{
CM_Client m = new CM_ClientBLL((int)ViewState["ClientID"]).Model;
ddl_Promotor.DataTextField = "Name";
ddl_Promotor.DataValueField = "ID";
ddl_Promotor.DataSource = PM_PromotorBLL.GetModelList(" OrganizeCity in (" + m.OrganizeCity + @") AND Dimission=1 AND ApproveFlag=1 AND
(MCS_SYS.dbo.UF_Spilt2('MCS_Promotor.dbo.PM_Promotor',ExtPropertys,'Classfiy')='2' OR ID NOT IN (SELECT Promotor FROM MCS_Promotor.dbo.PM_PromotorInRetailer))");
ddl_Promotor.DataBind();
ddl_Promotor.Items.Insert(0, new ListItem("请选择", "0"));
ddl_state.DataSource = DictionaryBLL.GetDicCollections("CM_ContractState");
ddl_state.DataBind();
ddl_state.Items.Insert(0, new ListItem("所有", "0"));
ddl_state.SelectedValue = "3";
}
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
}
/// <summary>
/// 绑定客户信息
/// </summary>
/// <param name="type">销量类型 1：经销商进货 3：经销商出货（即门店进货） 3：门店出货</param>
/// <param name="sellinclient">进货客户</param>
/// <param name="supplier">出货客户</param>
private void BindClientInfo(int sellinclient, int supplier)
{
if (sellinclient > 0)
{
CM_Client _r = new CM_ClientBLL(sellinclient).Model;
switch (_r.ClientType)
{
case 1: //公司仓库
hy_SellInClient.NavigateUrl = "~/SubModule/CM/Store/StoreDetail.aspx?ClientID=" + sellinclient.ToString();
break;
case 2: //经销商
hy_SellInClient.NavigateUrl = "~/SubModule/CM/DI/DistributorDetail.aspx?ClientID=" + sellinclient.ToString();
break;
case 3: //终端门店
hy_SellInClient.NavigateUrl = "~/SubModule/CM/RT/RetailerDetail.aspx?ClientID=" + sellinclient.ToString();
break;
}
hy_SellInClient.Text = "(" + _r.Code + ")" + _r.FullName;
if (supplier == 0)
{
supplier = _r.Supplier;
#region 根据门店或分销商取供货商
//成品，只取第一供货商
//赠品，先取第二供货商，如果取不到，则取第一供货商
if ((bool)ViewState["IsCXP"])
{
int supplier2 = 0;
int.TryParse(_r["Supplier2"], out supplier2);
if (supplier2 != 0)
{
supplier = supplier2;
}
}
CM_Client _s = new CM_ClientBLL(supplier).Model;
ddl_SellOutClient.Items.Add(new ListItem("(" + _s.Code + ")" + _s.FullName, _s.ID.ToString()));
#endregion
hy_SellOutClient.Visible = false;
//经销商登录时，供货商直接选择该经销商
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
if (!string.IsNullOrEmpty(staff.Model["RelateClient"]))
{
if (ddl_SellOutClient.Items.FindByValue(staff.Model["RelateClient"]) == null)
{
MessageBox.ShowAndRedirect(this, "对不起，您无法向该客户配送" + ((bool)ViewState["IsCXP"] ? "赠品" : "成品") + "！",
"SalesVolumeList.aspx?Type=" + ViewState["Type"].ToString() + "&SellInClientID=" + _r.ID.ToString());
}
ddl_SellOutClient.SelectedValue = staff.Model["RelateClient"];
ddl_SellOutClient.Enabled = false;
}
}
if ((int)ViewState["VolumeID"] == 0 && new CM_ClientBLL(int.Parse(ddl_SellOutClient.SelectedValue)).Model.ClientType == 2 && SVM_InventoryBLL.GetModelList("Client=" + ddl_SellOutClient.SelectedValue + " AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND ApproveFlag=2").Count > 0)
{
MessageBox.ShowAndRedirect(this, "该经销商本月还有未审核库存，请先审核库存再作此操作。", "InventoryList.aspx?ClientID=" + ddl_SellOutClient.SelectedValue);
return;
}
}
else
{
hy_SellInClient.Visible = false;
lb_SellInTitle.Visible = false;
}
if (hy_SellOutClient.Visible)
{
if (supplier > 0)
{
CM_Client _s = new CM_ClientBLL(supplier).Model;
switch (_s.ClientType)
{
case 1: //公司仓库
hy_SellOutClient.NavigateUrl = "~/SubModule/CM/Store/StoreDetail.aspx?ClientID=" + supplier.ToString();
break;
case 2: //经销商
hy_SellOutClient.NavigateUrl = "~/SubModule/CM/DI/DistributorDetail.aspx?ClientID=" + supplier.ToString();
break;
case 3: //终端门店
hy_SellOutClient.NavigateUrl = "~/SubModule/CM/RT/RetailerDetail.aspx?ClientID=" + supplier.ToString();
#region 绑定门店导购员
try
{
lbl_Promotor.Visible = true;
ddl_Promotor.Visible = true;
AC_AccountMonth month = new AC_AccountMonthBLL(int.Parse(ddl_AccountMonth.SelectedValue)).Model;
StringBuilder condition = new StringBuilder(" PM_Promotor.BeginWorkDate<='" + month.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND ISNULL(PM_Promotor.EndWorkDate,GETDATE())>='" + month.BeginDate.ToString("yyyy-MM-dd") + "' AND PM_Promotor.ApproveFlag=1 ");
condition.Append("AND ID in (SELECT Promotor FROM PM_PromotorInRetailer WHERE Client = " + supplier.ToString() + ")");
//if ((int)ViewState["VolumeID"] == 0)
//{
// condition.Append(" AND ID NOT IN (SELECT Promotor FROM [MCS_SVM].[dbo].[SVM_SalesVolume] WHERE Type=3 AND Supplier= " + supplier.ToString() + " AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND Promotor IS NOT NULL)");
//}
ddl_Promotor.DataSource = PM_PromotorBLL.GetModelList(condition.ToString());
ddl_Promotor.DataBind();
ddl_Promotor.Items.Insert(0, new ListItem("请选择..", "0"));
}
catch { }
#endregion
break;
}
hy_SellOutClient.Text = "(" + _s.Code + ")" + _s.FullName;
ddl_SellOutClient.Visible = false;
}
else
{
hy_SellOutClient.Visible = false;
ddl_SellOutClient.Visible = false;
lb_SellOutTitle.Visible = false;
}
}
}