protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
#region 获取最迟的销量日期
AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
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);
string condtion = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1 " + condtion + "AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
MessageBox.Show(this, "对不起，没有当前人直接负责的终端店！");
return;
}
#endregion
string title = (int)ViewState["ClientType"] == 2 ? "分销商" : "零售商";
string filename = title + ((int)ViewState["IsOpponent"] == 9 ? "赠品进货导入模板-" : "产品进销货量导入模板") + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
if ((int)ViewState["ClientType"] == 2 && (int)ViewState["IsOpponent"] == 1)
{
filename = "分销商产品进货导入模板" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
}
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = month.ID;
_bll.Model.State = 1;
_bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model.DownStaff = staff;
if ((int)ViewState["IsOpponent"] == 9)
{
_bll.Model.ProductGifts = ProdutctGifts.ToString().EndsWith(",") ? ProdutctGifts.ToString().Substring(0, ProdutctGifts.Length - 1) : ProdutctGifts.ToString();
_bll.Model.Testers = Testers.ToString().EndsWith(",") ? Testers.ToString().Substring(0, Testers.Length - 1) : Testers.ToString();
_bll.Model.Gifts = Gifts.ToString().EndsWith(",") ? Gifts.ToString().Substring(0, Gifts.Length - 1) : Gifts.ToString();
}
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Model["ClientType"] = ViewState["ClientType"].ToString();
_bll.Add();
//StringBuilder builder = new StringBuilder();
//builder.Append("alert('请到该页面下载模版!');");
//builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString()+"&ClientType=" + ViewState["ClientType"].ToString()+"','_blank')");
//MessageBox.ResponseScript(this, builder.ToString());
Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&ClientType=" + ViewState["ClientType"].ToString());
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
Button bt = (Button)sender;
GridViewRow drv = (GridViewRow)bt.NamingContainer;
int rowIndex = drv.RowIndex;
SVM_DownloadTemplateBLL _bll;
int templateid = int.Parse(gv_downtemplate.DataKeys[rowIndex]["SVM_DownloadTemplate_ID"].ToString());
_bll = new SVM_DownloadTemplateBLL(templateid);
if (_bll.Model.State == 1)
{
MessageBox.Show(this, "模版还未生成，目前在生成队列中第" + GetDownWait(templateid.ToString()) + "个");
return;
}
string path = ConfigHelper.GetConfigString("AttachmentPath");
if (path.StartsWith("~")) path = Server.MapPath(path);
if (!path.EndsWith("\\")) path = path + "\\";
if (ViewState["IsOpponent"].ToString() != "2" && ViewState["IsOpponent"].ToString() != "3")
{
path += "ImportExcelSVM\\Download\\" + Session["UserName"].ToString() + "\\" + _bll.Model.Name;
}
else
{
path += "ImportExcelSVM\\Download\\" + _bll.Model.AccountMonth.ToString() + "\\" + _bll.Model.Name;
}
Response.Write(path);
_bll.Model.State = 3;
Downloadfile(path, _bll.Model.Name);
_bll.Update();
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
#region 获取最迟的销量日期
AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
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);
string condtion = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1 " + condtion + "AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
MessageBox.Show(this, "对不起，没有当前人直接负责的终端店！");
return;
}
#endregion
string title = (int)ViewState["ClientType"] == 2 ? "分销商" : "零售商";
string filename = title+((int)ViewState["IsOpponent"] == 9 ? "赠品进货导入模板-" : "产品进销货量导入模板") + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
if ((int)ViewState["ClientType"] == 2 && (int)ViewState["IsOpponent"] == 1)
{
filename = "分销商产品进货导入模板" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
}
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = month.ID;
_bll.Model.State = 1;
_bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model.DownStaff = staff;
if ((int)ViewState["IsOpponent"] == 9)
{
_bll.Model.ProductGifts = ProdutctGifts.ToString().EndsWith(",") ? ProdutctGifts.ToString().Substring(0,ProdutctGifts.Length - 1) : ProdutctGifts.ToString();
_bll.Model.Testers = Testers.ToString().EndsWith(",") ? Testers.ToString().Substring(0,Testers.Length - 1) : Testers.ToString();
_bll.Model.Gifts = Gifts.ToString().EndsWith(",") ? Gifts.ToString().Substring(0,Gifts.Length - 1) : Gifts.ToString();
}
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Model["ClientType"] = ViewState["ClientType"].ToString();
_bll.Add();
//StringBuilder builder = new StringBuilder();
//builder.Append("alert('请到该页面下载模版!');");
//builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString()+"&ClientType=" + ViewState["ClientType"].ToString()+"','_blank')");
//MessageBox.ResponseScript(this, builder.ToString());
Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&ClientType=" + ViewState["ClientType"].ToString());
}
private void CheckOrganizeCity()
{
IList <SVM_DownloadTemplate> _downlist = SVM_DownloadTemplateBLL.GetModelList("IsOpponent=2 AND State IN (1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue);
foreach (SVM_DownloadTemplate _m in _downlist)
{
ListItem item = chk_Citylist.Items.FindByValue(_m["OrganizeCity"]);
if (!item.Text.EndsWith("已操作"))
{
item.Text += "------已操作";
}
}
}
protected void ddl_AccountMonth2_SelectedIndexChanged(object sender, EventArgs e)
{
if ((int)(ViewState["IsOpponent"]) == 3 && SVM_DownloadTemplateBLL.GetModelList("AccountMonth=" + ddl_AccountMonth2.SelectedValue + " AND IsOpponent=3 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',3)='1'").Count == 0)
{
string filename = "办事处月度费率导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = int.Parse(ddl_AccountMonth2.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent = 3;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model["OrganizeCity"] = "1";
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
}
}
protected void ddl_AccountMonth2_SelectedIndexChanged(object sender, EventArgs e)
{
if ((int)(ViewState["IsOpponent"]) == 3 && SVM_DownloadTemplateBLL.GetModelList("AccountMonth=" + ddl_AccountMonth2.SelectedValue + " AND IsOpponent=3 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',3)='1'").Count == 0)
{
string filename = "办事处月度费率导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = int.Parse(ddl_AccountMonth2.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent = 3;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model["OrganizeCity"] = "1";
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
}
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
SVM_DownloadTemplateBLL _bll;
string filename;
if (Produtcts.Length == 1)
{
MessageBox.Show(this, "对不起，请选择品项！");
return;
}
// string filename = ddl_OrganizeCity.SelectedItem.Text.Replace("------已操作", "") + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
StringBuilder organizecitys = new StringBuilder();
StringBuilder citynames = new StringBuilder();
foreach (ListItem item in chk_Citylist.Items)
{
if (item.Selected)
{
organizecitys.Append(item.Value + ",");
citynames.Append(item.Text.Substring(0, item.Text.IndexOf("营业部")) + ",");
}
}
if (organizecitys.ToString().EndsWith(","))
{
organizecitys.Remove(organizecitys.Length - 1, 1);
citynames.Remove(citynames.Length - 1, 1);
}
filename = citynames.ToString() + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
if (filename.Length > 100)
{
filename = ddl_AccountMonth.SelectedItem.Text + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
}
_bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = int.Parse(ddl_AccountMonth.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent = 2;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model["OrganizeCity"] = organizecitys.ToString();
_bll.Model.ProductGifts = Produtcts.ToString().Substring(1, Produtcts.Length - 2);
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
Response.Redirect("ImportHistory.aspx?IsOpponent=2");
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
SVM_DownloadTemplateBLL _bll;
string filename;
if (Produtcts.Length == 1)
{
MessageBox.Show(this, "对不起，请选择品项！");
return;
}
// string filename = ddl_OrganizeCity.SelectedItem.Text.Replace("------已操作", "") + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
StringBuilder organizecitys = new StringBuilder();
StringBuilder citynames = new StringBuilder();
foreach (ListItem item in chk_Citylist.Items)
{
if (item.Selected)
{
organizecitys.Append(item.Value+",");
citynames.Append(item.Text.Substring(0, item.Text.IndexOf("营业部"))+",");
}
}
if (organizecitys.ToString().EndsWith(","))
{
organizecitys.Remove(organizecitys.Length - 1,1);
citynames.Remove(citynames.Length - 1, 1);
}
filename = citynames.ToString() + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
if (filename.Length > 100) filename = ddl_AccountMonth.SelectedItem.Text + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
_bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth =int.Parse(ddl_AccountMonth.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent = 2;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model["OrganizeCity"] = organizecitys.ToString();
_bll.Model.ProductGifts = Produtcts.ToString().Substring(1, Produtcts.Length - 2);
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
Response.Redirect("ImportHistory.aspx?IsOpponent=2");
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
Button bt = (Button)sender;
GridViewRow drv = (GridViewRow)bt.NamingContainer;
int rowIndex = drv.RowIndex;
SVM_DownloadTemplateBLL _bll;
int templateid = int.Parse(gv_downtemplate.DataKeys[rowIndex]["SVM_DownloadTemplate_ID"].ToString());
_bll = new SVM_DownloadTemplateBLL(templateid);
if (_bll.Model.State == 1)
{
MessageBox.Show(this, "模版还未生成，目前在生成队列中第" + GetDownWait(templateid.ToString()) + "个");
return;
}
string path = ConfigHelper.GetConfigString("AttachmentPath");
if (path.StartsWith("~"))
{
path = Server.MapPath(path);
}
if (!path.EndsWith("\\"))
{
path = path + "\\";
}
if (ViewState["IsOpponent"].ToString() != "2" && ViewState["IsOpponent"].ToString() != "3")
{
path += "ImportExcelSVM\\Download\\" + Session["UserName"].ToString() + "\\" + _bll.Model.Name;
}
else
{
path += "ImportExcelSVM\\Download\\" + _bll.Model.AccountMonth.ToString() + "\\" + _bll.Model.Name;
}
Response.Write(path);
_bll.Model.State = 3;
Downloadfile(path, _bll.Model.Name);
_bll.Update();
}
protected string GetDownWait(string templateid)
{
IList <SVM_DownloadTemplate> downlist = SVM_DownloadTemplateBLL.GetModelList("State=1 AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND ID<=" + templateid);
return(downlist.Count > 0 ? (downlist.Count).ToString() : "");
}
private void CreateKeyTargetDownTemplate(int templateid, string path)
{
int cloumn = 8;
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid);
if (_bll.Model.State != 5)
{
Exception err;
string error;
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model;
DateTime day = (DateTime.Today < month.EndDate) ? DateTime.Today : month.EndDate;
string message = ""; IList<Addr_OrganizeCity> _cityList;
if (_bll.Model["OrganizeCity"] != "1")
{
_cityList = Addr_OrganizeCityBLL.GetModelList("Level3_SuperID IN (" + _bll.Model["OrganizeCity"] + ") AND Level=" + ConfigHelper.GetConfigString("OrganizeCity-CityLevel") + " Order By Level3_SuperID");
}
else
{
_cityList = Addr_OrganizeCityBLL.GetModelList("Level=" + ConfigHelper.GetConfigString("OrganizeCity-CityLevel") + " Order By Level3_SuperID");
}
object missing = Missing.Value;
ApplicationClass ExcelApp = null;
try
{
ExcelApp = new ApplicationClass
{
Visible = false,
DisplayAlerts = false
};
Workbook workbook1 = null;
Worksheet worksheet1 = null;
try
{
workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet1.Name = "重点品项";
int bgcolor1 = ColorTranslator.ToOle(Color.LightYellow);
int bgcolor2 = ColorTranslator.ToOle(Color.LightGreen);
worksheet1.Cells[1, 1] = "营业部";
worksheet1.Cells[1, 2] = "办事处ID";
worksheet1.Cells[1, 3] = "办事处";
worksheet1.Cells[1, 4] = "归属月份";
if (_bll.Model.IsOpponent == 2)
{
worksheet1.Cells[1, 5] = "办事处业绩目标额";
cloumn = 6;
}
else
{
worksheet1.Name = "月度费率";
worksheet1.Cells[1, 5] = "办事处月度费率目标";
worksheet1.Cells[1, 6] = "办事处月度费率达成";
}
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;
int sellrow = 2;
foreach (Addr_OrganizeCity city in _cityList)
{
worksheet1.Cells[sellrow, 1] = new Addr_OrganizeCityBLL(city.SuperID).Model.Name;
worksheet1.Cells[sellrow, 2] = city.ID;
worksheet1.Cells[sellrow, 3] = city.Name;
worksheet1.Cells[sellrow, 4] = "'" + month.Name;
#region 按營業部設置顏色
if (sellrow > 0)
{
if (new Addr_OrganizeCityBLL(city.SuperID).Model.Name == ((Range)worksheet1.Cells[sellrow - 1, 1]).Text.ToString())
{
worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color =
worksheet1.get_Range(worksheet1.Cells[sellrow - 1, 1], worksheet1.Cells[sellrow - 1, 4]).Interior.Color;
}
else
{
if (int.Parse(worksheet1.get_Range(worksheet1.Cells[sellrow - 1, 1], worksheet1.Cells[sellrow - 1, 4]).Interior.Color.ToString()) == bgcolor1)
worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor2;
else
worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor1;
}
}
else
{
worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor1;
}
#endregion
sellrow++;
}
if (_bll.Model.IsOpponent == 2)
{
if (!string.IsNullOrEmpty(_bll.Model.ProductGifts))
{
if (_bll.Model.ProductGifts.EndsWith(","))
{
_bll.Model.ProductGifts = _bll.Model.ProductGifts.Substring(0, _bll.Model.ProductGifts.Length - 1);
}
IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList("ID IN (" + _bll.Model.ProductGifts + ") ORDER BY ISNULL(SubUnit,999999),Code");
//将产品写入表头
for (int i = 0; i < productlists.Count; i++)
{
worksheet1.Cells[1, cloumn + i] = productlists[i].ShortName;
#region 按品牌设置产品列颜色
if (i > 0)
{
if (productlists[i].Brand == productlists[i - 1].Brand)
{
worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color =
worksheet1.get_Range(worksheet1.Cells[1, cloumn + i - 1], worksheet1.Cells[1000, cloumn + i - 1]).Interior.Color;
}
else
{
if (int.Parse(worksheet1.get_Range(worksheet1.Cells[1, cloumn + i - 1], worksheet1.Cells[1000, cloumn + i - 1]).Interior.Color.ToString()) == bgcolor1)
worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor2;
else
worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor1;
}
}
else
{
worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor1;
}
#endregion
}
#region 设置表格格式
//设置行高
worksheet1.get_Range(worksheet1.Cells[2, 1], worksheet1.Cells[sellrow - 1, 1]).RowHeight = 16;
//设置表格单元格格线
worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[sellrow - 1, cloumn + 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 (Exception exception1)
{
err = exception1;
error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>";
message = message + "系统错误-1!" + err.Message;
}
finally
{
if (workbook1 != null)
{
workbook1.Close(false, missing, missing);
}
if (worksheet1 != null)
{
Marshal.ReleaseComObject(worksheet1);
}
if (workbook1 != null)
{
Marshal.ReleaseComObject(workbook1);
}
worksheet1 = null;
workbook1 = null;
if (File.Exists(path))
{
_bll.Model.Path = path;
_bll.Model.State = 2;
}
}
}
catch (Exception exception2)
{
err = exception2;
error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>";
message = message + "系统错误-2!" + err.Message;
}
finally
{
if (ExcelApp != null)
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
}
if (message != "")
{
_bll.Model.State = 4;
}
_bll.Model.Remark = message;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
this.SendMessage("生成重点品项导入模版", (message != "") ? (filename + "-" + message) : (filename + "生成操作成功！"));
}
}
private void CreateDownTemplate(int templateid, string path)
{
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid);
if (_bll.Model.State == 5) return;//模版作废
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model;
DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
string message = "";
#region 获取业代负责的零售商及所有产品数据
string condtion = _bll.Model["ClientType"] == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',89) !='1'";
IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + _bll.Model["ClientType"] + " AND ClientManager=" + _bll.Model.DownStaff.ToString() +
" AND ApproveFlag=1" + condtion + " AND OpenTime<'" + month.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND ISNULL(CloseTime,GETDATE())>='" + month.BeginDate.ToString("yyyy-MM-dd") + "' ORDER BY Code");
if (clientlists.Count == 0)
{
message = "对不起，没有当前人直接负责的客户！";
_bll.Model.Remark = message;
_bll.Update();
return;
}
#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"];
if (_bll.Model["ClientType"] == "2")
{
worksheet1.Name = "分销商进货";
WriteSheet(worksheet1, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 6);
}
else
{
worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing);
worksheet1.Name = "零售商进货";
worksheet2.Name = "零售商销货";
string ParamValue = Addr_OrganizeCityParamBLL.GetValueByType(1, 26);
ParamValue = string.IsNullOrEmpty(ParamValue) ? "0" : ParamValue;
ParamValue = ParamValue.EndsWith(",") ? ParamValue.Remove(ParamValue.Length - 1) : ParamValue;
WriteSheet(worksheet2, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand NOT IN(" + ParamValue + ") AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1')AND MCS_SYS.dbo.UF_Spilt2('MCS_PUB.dbo.PDT_Product',ExtPropertys,'IntegralPoints')!=1 AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 8);
WriteSheet(worksheet1, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand NOT IN(" + ParamValue + ") AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1')AND MCS_SYS.dbo.UF_Spilt2('MCS_PUB.dbo.PDT_Product',ExtPropertys,'IntegralPoints')!=1 AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 6);
}
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/>";
message += "系统错误-1!" + err.Message;
}
finally
{
if (workbook1 != null) workbook1.Close(false, missing, missing);
if (worksheet1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1);
if (_bll.Model["ClientType"] != "2" && worksheet2 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2);
if (workbook1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1);
worksheet1 = null;
if (_bll.Model["ClientType"] != "2")
{
worksheet2 = null;
}
workbook1 = null;
if (File.Exists(path))
{
_bll.Model.Path = path;
_bll.Model.State = 2;
}
}
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-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
if (message != "") _bll.Model.State = 4;
_bll.Model.Remark = message;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
SendMessage("生成成品导入模版", message != "" ? filename + "-" + message : filename + "生成操作成功！");
}
private void CrateGiftDownTemplate(int templateid, string path)
{
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid);
if (_bll.Model.State == 5) return;//模版作废
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model;
DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
string message = "";
#region 获取业代负责的零售商及所有产品数据
string condtion = _bll.Model["ClientType"] == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + _bll.Model["ClientType"] + " AND ClientManager=" + _bll.Model.DownStaff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1" + condtion + " AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
message = "对不起，没有当前人直接负责的客户！";
_bll.Model.Remark = message;
_bll.Update();
return;
}
#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, worksheet3 = null;
try
{
workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing);
worksheet3 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet2, 1, missing);
worksheet1.Name = "成品赠品";
worksheet2.Name = "试用装";
worksheet3.Name = "高值促销品";
//将产品写入表头
if (!string.IsNullOrEmpty(_bll.Model.ProductGifts)) WriteSheet(worksheet1, "ID IN(" + _bll.Model.ProductGifts + ")", clientlists, month, 6);
if (!string.IsNullOrEmpty(_bll.Model.Testers)) WriteSheet(worksheet2, "ID IN(" + _bll.Model.Testers + ")", clientlists, month, 6);
if (!string.IsNullOrEmpty(_bll.Model.Gifts)) WriteSheet(worksheet3, "ID IN(" + _bll.Model.Gifts + ")", clientlists, month, 6);
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/>";
message += "系统错误-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 (worksheet3 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet3);
if (workbook1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1);
worksheet1 = null;
worksheet2 = null;
workbook1 = null;
if (File.Exists(path))
{
_bll.Model.Path = path;
_bll.Model.State = 2;
}
}
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-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
if (message != "") _bll.Model.State = 4;
_bll.Model.Remark = message;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
SendMessage("生成赠品导入模版", message != "" ? filename + "-" + message : filename + "生成操作成功！");
}