protected string GetUpWaitTime(string templateid)
{
IList <SVM_UploadTemplate> uplist = SVM_UploadTemplateBLL.GetModelList("State=1 AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND ID<=" + templateid);
if (uplist.Count > 0)
{
return(DateTime.Now.AddSeconds(30 * uplist.Count).ToString());
}
return("");
}
protected void bt_ViewRemark_Click(object sender, EventArgs e)
{
Button bt = (Button)sender;
GridViewRow drv = (GridViewRow)bt.NamingContainer;
int rowIndex = drv.RowIndex;
int templateid = int.Parse(gv_uptemplate.DataKeys[rowIndex]["SVM_UploadTemplate_ID"].ToString());
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(templateid);
lb_ErrorInfo.Text = _bll.Model.Remark;
if (_bll.Model.State > 1 && _bll.Model.Remark != "") BindGrid();
}
protected void bt_ViewRemark_Click(object sender, EventArgs e)
{
Button bt = (Button)sender;
GridViewRow drv = (GridViewRow)bt.NamingContainer;
int rowIndex = drv.RowIndex;
int templateid = int.Parse(gv_uptemplate.DataKeys[rowIndex]["SVM_UploadTemplate_ID"].ToString());
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(templateid);
lb_ErrorInfo.Text = _bll.Model.Remark;
if (_bll.Model.State > 1 && _bll.Model.Remark != "")
{
BindGrid();
}
}
protected void bt_UploadExcel_Click(object sender, EventArgs e)
{
#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" && FileExtName != "csv")
{
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\\" + ddl_AccountMonth.SelectedValue + "\\";
if (!Directory.Exists(path)) Directory.CreateDirectory(path);
path += FileName + "." + FileExtName;
FileUpload1.SaveAs(path);
#endregion
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL();
_bll.Model.Name = FileName + "." + FileExtName; ;
_bll.Model.AccountMonth = int.Parse(ddl_AccountMonth.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent =MCSTabControl1.SelectedIndex==1? 2:3;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.UploadTime = DateTime.Now;
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
Response.Redirect("ImportHistory.aspx?IsOpponent=" + (MCSTabControl1.SelectedIndex == 1 ? "2" : "3") + "&State=2");
}
protected void bt_UploadExcel_Click(object sender, EventArgs e)
{
#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" && FileExtName != "csv")
{
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
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL();
_bll.Model.Name = FileName + "." + FileExtName;;
_bll.Model.AccountMonth = ((AC_AccountMonth)ViewState["month"]).ID;
_bll.Model.State = 1;
_bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.UploadTime = DateTime.Now;
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Model["ClientType"] = ViewState["ClientType"].ToString();
_bll.Add();
div_gift.Visible = false;
//StringBuilder builder = new StringBuilder();
//builder.Append("alert('请到该页面查询导入状态!');");
//builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&State=2&ClientType=" + ViewState["ClientType"].ToString() + "','_blank')");
//MessageBox.ResponseScript(this, builder.ToString());
Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&State=2&ClientType=" + ViewState["ClientType"].ToString());
}
protected string GetUpWait(string templateid)
{
IList <SVM_UploadTemplate> uplist = SVM_UploadTemplateBLL.GetModelList("State=1 AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND ID<=" + templateid);
return(uplist.Count > 0 ? (uplist.Count).ToString() : "");
}
private void ImportGiftProduct(int templateid, string path)
{
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(templateid);
if (_bll.Model.State != 1) return;//模版作废
string message = "";
string improtmessage = "";
#region 读取Excel文件
string ErrorInfo = "";
int _State = 0;
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.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
#region 验证工作表数据格式
if (workbook1.Worksheets.Count != 3)
{
message += "Excel表格中必须且只能有3张工作表！";
goto End;
}
worksheet1 = (Worksheet)workbook1.Worksheets[1];
worksheet2 = (Worksheet)workbook1.Worksheets[2];
worksheet3 = (Worksheet)workbook1.Worksheets[3];
if (worksheet1.Name != "成品赠品")
{
ErrorInfo += "Excel表格中第1个工作表名称必须为【成品赠品】！";
goto End;
}
if (worksheet2.Name != "试用装")
{
ErrorInfo += "Excel表格中第2个工作表名称必须为【试用装】！";
goto End;
}
if (worksheet3.Name != "高值促销品")
{
ErrorInfo += "Excel表格中第3个工作表名称必须为【高值促销品】！";
goto End;
}
if (_bll.Model["ClientType"] == "2")
{
if (!VerifyDIWorkSheet(worksheet1))
{
ErrorInfo += "【成品赠品】工作表表头(1~5列)错误!";
goto End;
}
if (!VerifyDIWorkSheet(worksheet2))
{
ErrorInfo += "【试用装】工作表表头(1~5列)错误!";
goto End;
}
if (!VerifyDIWorkSheet(worksheet3))
{
ErrorInfo += "【高值促销品】工作表表头(1~5列)错误!";
goto End;
}
}
else
{
if (!VerifyWorkSheet(worksheet1, 5))
{
ErrorInfo += "【成品赠品】工作表表头(1~5列)错误!";
goto End;
}
if (!VerifyWorkSheet(worksheet2, 5))
{
ErrorInfo += "【试用装】工作表表头(1~5列)错误!";
goto End;
}
if (!VerifyWorkSheet(worksheet3, 5))
{
ErrorInfo += "【高值促销品】工作表表头(1~5列)错误!";
goto End;
}
}
#endregion
#region 读取Excel表格
IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='9') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code");
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model;
improtmessage += ImportGifts(worksheet1, "Excel批量导入本品赠品", month, _bll.Model.InsertStaff, productlists, out _State);
if (_State != 0) _bll.Model.State = _State;
improtmessage += ImportGifts(worksheet2, "Excel批量导入试用装赠品", month, _bll.Model.InsertStaff, productlists, out _State);
if (_State != 0) _bll.Model.State = _State;
improtmessage += ImportGifts(worksheet3, "Excel批量导入高值促销品", month, _bll.Model.InsertStaff, productlists, out _State);
if (_State != 0) _bll.Model.State = _State;
#endregion
End:
;
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-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 (worksheet3 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet3);
if (workbook1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1);
worksheet1 = null;
worksheet2 = null;
worksheet3 = null;
workbook1 = null;
}
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-5!" + err.Message;
}
finally
{
if (ExcelApp != null)
{
try
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-6,Excel宏报错，请确认文件打开不报错再上传!" + err.Message;
KillProcess();
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
if (ErrorInfo != "")
{
message += "对不起，Excel文件打开错误，请确认格式是否正确。错误提示:" + ErrorInfo;
}
}
if (_bll.Model.State == 1)
_bll.Model.State = message != "" ? 3 : 2;
_bll.Model.ImprotTime = DateTime.Now;
_bll.Model.Remark = message != "" ? message : improtmessage;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
SendMessage("导入赠品进货", message != "" ? filename + "-" + message : filename + "导入操作成功！");
#endregion
}
public void UploadExcel()
{
while (bIsRunning)
{
try
{
#region 获取最迟的销量月份
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
#endregion
IList<SVM_UploadTemplate> uplists = SVM_UploadTemplateBLL.GetModelList("AccountMonth=" + month.ID + " AND State=1");
if (uplists.Count == 0)
{
System.Threading.Thread.Sleep(2000);
continue;
}
foreach (SVM_UploadTemplate m in uplists)
{
dtHeartTime = DateTime.Now;
ImportTemplateID = m.ID;
try
{
#region 组织文件路径及文件名
string path = ConfigHelper.GetConfigString("AttachmentPath");
if (!path.EndsWith("\\")) path = path + "\\";
if (m.IsOpponent != 2 && m.IsOpponent != 3)
{
path += "ImportExcelSVM\\Upload\\" + m["UserName"] + "\\";
}
else
{
path += "ImportExcelSVM\\Upload\\" + m.AccountMonth.ToString() + "\\";
}
string filename = m.Name;
path += filename;
#endregion
SendMessage("当前处理记录", m.Name);
switch (m.IsOpponent)
{
case 1:
this.ImportProduct(m.ID, path);
break;
case 2:
case 3:
this.ImportKeyProduct(m.ID, path);
break;
case 9:
this.ImportGiftProduct(m.ID, path);
break;
}
this.ImportTemplateID = 0;
}
catch (System.Exception err)
{
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(m.ID);
_bll.Model.State = 3;
_bll.Model.Remark = "导入出错，异常：" + err.Message;
_bll.Model.ImprotTime = DateTime.Now;
_bll.Update();
this.ImportTemplateID = 0;
}
}
}
catch (ThreadAbortException e)
{
this.SendMessage("ThreadAbortException", this.ImportTemplateID.ToString() + e.ToString());
break;
}
}
}
private void ThreadCheckHeartTime()
{
while (bIsRunning)
{
int TimeLimit = ConfigHelper.GetConfigInt("TimeLimit");
if (dtHeartTime < DateTime.Now.AddSeconds(-TimeLimit) && ImportTemplateID > 0)
{
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(ImportTemplateID);
_bll.Model.State = 3;
_bll.Model.Remark = "导入出错，导入超时，请确认文件没有损坏或病毒再上传!";
_bll.Model.ImprotTime = DateTime.Now;
_bll.Update();
if (th_ImportService != null && th_ImportService.ThreadState == System.Threading.ThreadState.Running)
{
this.ImportTemplateID = 0;
th_ImportService.Abort();
th_ImportService.Join(5000);
KillProcess();
th_ImportService = new Thread(new ThreadStart(UploadExcel));
th_ImportService.Start();
}
}
Thread.Sleep(50000);
}
}
private void ImportProduct(int templateid, string path)
{
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(templateid);
if (_bll.Model.State != 1) return;//模版作废
string message = "";
string improtmessage = "";
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).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, worksheet2 = null;
try
{
workbook1 = ExcelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
#region 验证工作表数据格式
switch (_bll.Model["ClientType"])
{
case "2":
if (workbook1.Worksheets.Count != 1)
{
ErrorInfo += "Excel表格中必须且只能有1张工作表,请并检查宏是否有问题！";
goto End;
}
break;
case "3":
if (workbook1.Worksheets.Count != 2)
{
ErrorInfo += "Excel表格中必须且只能有2张工作表，请并检查宏是否有问题！";
goto End;
}
break;
}
worksheet1 = (Worksheet)workbook1.Worksheets[1];
if (_bll.Model["ClientType"] == "2")
{
if (worksheet1.Name != "分销商进货")
{
ErrorInfo += "Excel表格中第1个工作表名称必须为【分销商进货】！";
goto End;
}
if (!VerifyDIWorkSheet(worksheet1))
{
ErrorInfo += "Excel表格中第1个工作表名称必须为【零售商进货】！";
goto End;
}
}
else
{
worksheet2 = (Worksheet)workbook1.Worksheets[2];
if (worksheet1.Name != "零售商进货")
{
ErrorInfo += "Excel表格中第1个工作表名称必须为【零售商进货】！";
goto End;
}
if (worksheet2.Name != "零售商销货")
{
ErrorInfo += "Excel表格中第2个工作表名称必须为【零售商销货】！";
goto End;
}
if (!VerifyWorkSheet(worksheet1, 5))
{
ErrorInfo += "零售商进货工作表表头(1~5列)错误!";
goto End;
}
if (!VerifyWorkSheet(worksheet2, 7))
{
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("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");
if (_bll.Model["ClientType"] == "3")
{
productlists = PDT_ProductBLL.GetModelList("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')=0" +
" 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 += (_bll.Model["ClientType"] == "3" ? "零售商" : "分销商") + "进货工作表表头，(" + (6 + i).ToString() + "列)产品名称错误！必须为:" + productlists[i].ShortName;
goto End;
}
if (_bll.Model["ClientType"] == "3" && ((Range)worksheet2.Cells[1, 8 + i]).Text.ToString() != productlists[i].ShortName)
{
ErrorInfo += "零售商进货工作表表头，(" + (8 + i).ToString() + "列)产品名称错误！必须为:" + productlists[i].ShortName + ";错误表头为" + ((Range)worksheet2.Cells[1, 8 + i]).Text.ToString();
goto End;
}
}
#endregion
improtmessage += DoImportProduct(worksheet1, _bll.Model.AccountMonth, _bll.Model.InsertStaff, 6, productlists, out _State);
if (_State != 0) _bll.Model.State = _State;
if (_bll.Model["ClientType"] != "2")
{
improtmessage += DoImportProduct(worksheet2, _bll.Model.AccountMonth, _bll.Model.InsertStaff, 8, productlists, out _State);
if (_State != 0) _bll.Model.State = _State;
}
End:
;
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-4!" + 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;
}
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
message += "系统错误-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 + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-6,Excel宏报错，请确认文件打开不报错再上传!" + err.Message;
KillProcess();
}
}
GC.Collect();
//GC.WaitForPendingFinalizers();
if (ErrorInfo != "")
{
message += "对不起，Excel文件打开错误，请确认格式是否正确。错误提示:" + ErrorInfo;
}
}
#endregion
if (_bll.Model.State == 1)
{
_bll.Model.State = message != "" ? 3 : 2;
}
_bll.Model.Remark = message != "" ? message : improtmessage;
_bll.Model.ImprotTime = DateTime.Now;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
SendMessage("导入成品进销", message != "" ? filename + "-" + message : filename + "导入操作成功！");
}
private void ImportKeyProduct(int templateid, string path)
{
ThreadAbortException e;
Exception err;
string error;
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL(templateid);
if (_bll.Model.State != 1)
{
return;
}
string message = "";
string improtmessage = "";
AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model;
string ErrorInfo = "";
object missing = Missing.Value;
ApplicationClass ExcelApp = null;
try
{
ExcelApp = new ApplicationClass
{
Visible = false,
DisplayAlerts = false
};
Workbook workbook1 = null;
Worksheet worksheet1 = null;
try
{
PDT_Product product;
workbook1 = ExcelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet1 = (Worksheet)workbook1.Worksheets[1];
if (workbook1.Worksheets.Count != 1)
{
improtmessage = improtmessage + "Excel表格中必须且只能有1张工作表,请并检查宏是否有问题！";
goto End;
}
switch (_bll.Model.IsOpponent)
{
case 2:
if (worksheet1.Name != "重点品项")
{
improtmessage = improtmessage + "Excel表格中第1个工作表名称必须为【重点品项】！";
goto End;
}
if (!this.VerifyKeyProductSheet(worksheet1))
{
improtmessage = improtmessage + "工作表表头(1~7列)错误！";
goto End;
}
break;
case 3:
if (worksheet1.Name != "月度费率")
{
improtmessage = improtmessage + "Excel表格中第1个工作表名称必须为【月度费率】！";
goto End;
}
if (!this.VerifyFeeRateSheet(worksheet1))
{
improtmessage = improtmessage + "工作表表头(1~6列)错误！";
goto End;
}
break;
}
int cloumn = 5;
int sellinrow = 1;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
improtmessage += "<br/>";
improtmessage += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
improtmessage += "<span style='color: Red'>---------------------------" + (_bll.Model.IsOpponent == 2 ? "办事处重点品项导入" : "办事处费率导入") + "----------------------------</span><br/>";
#region 读取Excel表格
while (true)
{
cloumn = 5;
sellinrow++;
if (((Range)worksheet1.Cells[sellinrow, 2]).Value2 == null)
{
break;
}
int cityid = 0;
if (!int.TryParse(((Range)worksheet1.Cells[sellinrow, 2]).Value2.ToString(), out cityid))
{
continue;
}
#region 验证数据
Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model;
if (_city == null || _city.Name != ((Range)worksheet1.Cells[sellinrow, 3]).Text.ToString())
{
improtmessage += "<span style='color: Red'>办事处ID号：" + ((Range)worksheet1.Cells[sellinrow, 2]).Text.ToString() + "与办事处名称不匹配！</span><br/>";
_bll.Model.State = 4;
continue;
}
if (((Range)worksheet1.Cells[sellinrow, 4]).Text.ToString() != month.Name)
{
improtmessage += "<span style='color: Red'>办事处ID号：" + ((Range)worksheet1.Cells[sellinrow, 2]).Text.ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
_bll.Model.State = 4;
continue;
}
#endregion
#region 办事处目标额
SVM_OrganizeTargetBLL bll = null;
IList<SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ID.ToString());
if (_targetlist.Count > 0)
{
if (_targetlist.FirstOrDefault<SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null)
{
improtmessage = improtmessage + "<span style='color: Red'>ID号：" + ((Range)worksheet1.Cells[sellinrow, 2]).Text.ToString() + "当月的重点品项已审核，不可再次导入！</span><br/>";
continue;
}
if (_targetlist.Count == 1)
{
bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID);
if (_bll.Model.IsOpponent == 2)
{
bll.Items.Clear();
}
}
}
if (bll == null)
{
bll = new SVM_OrganizeTargetBLL
{
Model = { OrganizeCity = cityid, AccountMonth = month.ID, ApproveFlag = 2 }
};
}
decimal amount = 0M;
if (_bll.Model.IsOpponent == 2)
{
decimal.TryParse(((Range)worksheet1.Cells[sellinrow, cloumn]).Value2.ToString(), out amount);
if (amount != 0M)
{
bll.Model.SalesTarget = amount;
}
else if ((this.trim(((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString()) != "") && (((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString() != "0"))
{
improtmessage += "<span style='color: Red'>ID号：" + cityid.ToString() + "，" + _city.Name + "的重点品项未能导入，办事处业绩目标额：" + ((Range)worksheet1.Cells[1, cloumn]).Text + "金额填写错误";
_bll.Model.State = 4;
continue;
}
++cloumn;
amount = 0M;
}
else
{
decimal.TryParse(((Range)worksheet1.Cells[sellinrow, cloumn]).Value2.ToString(), out amount);
if (amount != 0M)
{
bll.Model.FeeRateTarget = amount;
}
else if ((this.trim(((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString()) != "") && (((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString() != "0"))
{
improtmessage += "<span style='color: Red'>ID号：" + cityid.ToString() + "，" + _city.Name + "的重点品项未能导入，办事处月度费率目标：" + ((Range)worksheet1.Cells[1, cloumn]).Text + "金额填写错误";
_bll.Model.State = 4;
continue;
}
amount = 0M;
decimal.TryParse(((Range)worksheet1.Cells[sellinrow, ++cloumn]).Value2.ToString(), out amount);
if (amount != 0M)
{
bll.Model.FeeYieldRate = amount;
}
else if ((this.trim(((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString()) != "") && (((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString() != "0"))
{
improtmessage += "<span style='color: Red'>ID号：" + cityid.ToString() + "，" + _city.Name + "的重点品项未能导入，办事处月度费率达成：" + ((Range)worksheet1.Cells[1, cloumn]).Text + "金额填写错误";
_bll.Model.State = 4;
continue;
}
}
#endregion
IList<SVM_KeyProductTarget_Detail> details = new List<SVM_KeyProductTarget_Detail>();
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
while (true)
{
amount = 0M;
if (((Range)worksheet1.Cells[1, cloumn]).Text.ToString() == string.Empty)
{
break;
}
IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + ((Range)worksheet1.Cells[1, cloumn]).Text + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
improtmessage += "<span style='color: Red'>产品名称：" + ((Range)worksheet1.Cells[1, cloumn]).Text + "在产品列表中不存在！</span><br/>";
_bll.Model.State = 4;
cloumn++;
continue;
}
if ((product != null) && (((Range)worksheet1.Cells[sellinrow, cloumn]).Value2 != null))
{
decimal.TryParse(((Range)worksheet1.Cells[sellinrow, cloumn]).Value2.ToString(), out amount);
if (amount != 0M)
{
SVM_KeyProductTarget_Detail detail = new SVM_KeyProductTarget_Detail
{
Product = product.ID,
Amount = amount
};
details.Add(detail);
}
else if ((this.trim(((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString()) != "") && (((Range)worksheet1.Cells[sellinrow, cloumn]).Text.ToString() != "0"))
{
wrongflag = true;
break;
}
}
}
if (wrongflag && _bll.Model.IsOpponent == 2)
{
improtmessage += "<span style='color: Red'>ID号：" + cityid.ToString() + "，" + _city.Name + "的重点品项未能导入，品项名称：" + ((Range)worksheet1.Cells[1, cloumn]).Text + "金额填写错误";
_bll.Model.State = 4;
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
switch (_bll.Model.IsOpponent)
{
case 2:
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Model.UpdateStaff = _bll.Model.InsertStaff;
bll.Items = details;
bll.AddDetail();
bll.Update();
improtmessage += "<span style='color: Blue'>ID号：" + cityid.ToString() + "，" + _city.Name + " 的重点品项被成功更新！</span><br/>";
}
break;
case 3:
bll.Update();
improtmessage += "<span style='color: Blue'>ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率被成功更新！</span><br/>";
break;
}
}
else
{
switch (_bll.Model.IsOpponent)
{
case 2:
if (details.Count > 0)
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_OrganizeTarget m in _targetlist)
{
bll = new SVM_OrganizeTargetBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
improtmessage += "<span style='color: Black'>ID号：" + cityid.ToString() + "，" + _city.Name + " 的重点品项已成功导入！</span><br/>";
}
break;
case 3:
bll.Add();
improtmessage += "<span style='color: Black'>ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率已成功导入！</span><br/>";
break;
}
}
#endregion
}
#endregion
End:
;
}
catch (ThreadAbortException exception1)
{
e = exception1;
return;
}
catch (Exception exception2)
{
err = exception2;
error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>";
message = message + "系统错误-4!" + 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;
}
}
catch (ThreadAbortException exception3)
{
e = exception3;
return;
}
catch (Exception exception4)
{
err = exception4;
message = message + "系统错误-5!" + err.Message;
}
finally
{
if (ExcelApp != null)
{
try
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
catch (Exception exception5)
{
err = exception5;
error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>";
message = message + "系统错误-6,Excel宏报错，请确认文件打开不报错再上传!" + err.Message;
}
}
GC.Collect();
if (ErrorInfo != "")
{
message = message + "对不起，Excel文件打开错误，请确认格式是否正确。错误提示:" + ErrorInfo;
}
}
if (_bll.Model.State == 1)
{
_bll.Model.State = (message != "") ? 3 : 2;
}
_bll.Model.Remark = (message != "") ? message : improtmessage;
_bll.Model.ImprotTime = DateTime.Now;
_bll.Update();
string filename = path.Substring(path.LastIndexOf('\\') + 1);
this.SendMessage("导入办事处重点品项", (message != "") ? (filename + "-" + message) : (filename + "导入操作成功！"));
}
protected void bt_UploadExcel_Click(object sender, EventArgs e)
{
#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" &&FileExtName != "csv")
{
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
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL();
_bll.Model.Name = FileName + "." + FileExtName; ;
_bll.Model.AccountMonth = ((AC_AccountMonth)ViewState["month"]).ID;
_bll.Model.State = 1;
_bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.UploadTime = DateTime.Now;
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Model["ClientType"] = ViewState["ClientType"].ToString();
_bll.Add();
div_gift.Visible = false;
//StringBuilder builder = new StringBuilder();
//builder.Append("alert('请到该页面查询导入状态!');");
//builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&State=2&ClientType=" + ViewState["ClientType"].ToString() + "','_blank')");
//MessageBox.ResponseScript(this, builder.ToString());
Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&State=2&ClientType=" + ViewState["ClientType"].ToString());
}
protected void bt_UploadExcel_Click(object sender, EventArgs e)
{
#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" && FileExtName != "csv")
{
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\\" + ddl_AccountMonth.SelectedValue + "\\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path += FileName + "." + FileExtName;
FileUpload1.SaveAs(path);
#endregion
SVM_UploadTemplateBLL _bll = new SVM_UploadTemplateBLL();
_bll.Model.Name = FileName + "." + FileExtName;;
_bll.Model.AccountMonth = int.Parse(ddl_AccountMonth.SelectedValue);
_bll.Model.State = 1;
_bll.Model.IsOpponent = MCSTabControl1.SelectedIndex == 1? 2:3;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.UploadTime = DateTime.Now;
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Add();
Response.Redirect("ImportHistory.aspx?IsOpponent=" + (MCSTabControl1.SelectedIndex == 1 ? "2" : "3") + "&State=2");
}
