protected void bt_Save_Click(object sender, EventArgs e)
{
decimal val;
if (decimal.TryParse(Addr_OrganizeCityParamBLL.GetValueByType(1, 8), out val))
{
SVM_OrganizeTargetBLL _bll = new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]);
decimal oldvalue = _bll.Model.SalesTarget;
dv_detail.GetData(_bll.Model);
decimal changevalue = _bll.Model.SalesTargetAdjust;
if (oldvalue * val >= Math.Abs(changevalue))
{
_bll.Model.SalesTargetAdjust = val;
_bll.Update();
BindData();
}
else
{
MessageBox.Show(this, "系统限定必须在原有目标值基础上浮动调整" + val + "%以内，请重新调整！");
return;
}
}
else
{
MessageBox.Show(this, "系统参数中未维护调整浮动比例，或浮动比例维护有误！");
}
if (sender != null)
{
MessageBox.Show(this, "修改成功!");
}
}
protected void bt_Save_Click(object sender, EventArgs e)
{
decimal val;
if (decimal.TryParse(Addr_OrganizeCityParamBLL.GetValueByType(1, 8), out val))
{
SVM_OrganizeTargetBLL _bll = new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]);
decimal oldvalue = _bll.Model.SalesTarget;
dv_detail.GetData(_bll.Model);
decimal changevalue = _bll.Model.SalesTargetAdjust;
if (oldvalue * val >= Math.Abs(changevalue))
{
_bll.Model.SalesTargetAdjust = val;
_bll.Update();
BindData();
}
else
{
MessageBox.Show(this, "系统限定必须在原有目标值基础上浮动调整" + val + "%以内，请重新调整！");
return;
}
}
else
MessageBox.Show(this, "系统参数中未维护调整浮动比例，或浮动比例维护有误！");
if (sender != null)
MessageBox.Show(this, "修改成功!");
}
protected void bt_Approve_Click(object sender, EventArgs e)
{
SVM_OrganizeTargetBLL target = new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]);
target.Model.ApproveFlag = 1;
target.Model.UpdateStaff = (int)Session["UserID"];
target.Update();
}
protected void bt_Approve_Click(object sender, EventArgs e)
{
SVM_OrganizeTargetBLL target = new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]);
target.Model.ApproveFlag = 1;
target.Model.UpdateStaff = (int)Session["UserID"];
target.Update();
}
protected void bt_UnApprove_Click(object sender, EventArgs e)
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
SVM_OrganizeTargetBLL.UnApprove(month, organizecity);
BindGrid();
}
private void BindData()
{
SVM_OrganizeTarget target = new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]).Model;
dv_detail.BindData(target);
if (target.ApproveFlag == 1 || target.ApproveFlag == 4)
{
bt_Save.Visible = false;
bt_Submit.Visible = false;
bt_Approve.Visible = true;
}
else
{
bt_Approve.Visible = false;
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
PDT_Product product;
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount() - 1;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "营业部" ||
headerRow.GetCell(1).ToString() != "办事处ID" ||
headerRow.GetCell(2).ToString() != "办事处" ||
headerRow.GetCell(3).ToString() != "归属月份" ||
headerRow.GetCell(4).ToString() != "办事处月度费率目标" ||
headerRow.GetCell(5).ToString() != "办事处上月发生费用")
{
MessageBox.Show("工作表表头(1~6列)错误！\r\n");
return;
}
int month = 0;
rows.MoveNext();
while (rows.MoveNext())
{
i++;
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0) == null || row.GetCell(0).ToString() == "")
{
break;
}
int cloumn = 4;
int cityid = 0;
if (!int.TryParse(row.GetCell(1).ToString(), out cityid))
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "ID错误;\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model;
if (_city == null || _city.Name != row.GetCell(2).ToString())
{
errormessage += "办事处ID号：" + cityid.ToString() + "与办事处名称不匹配！\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
ICell cell = row.GetCell(3);
if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份")
{
//ICellStyle cellStyle = cell.CellStyle;
//IDataFormat format = hssfworkbook.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM");
//cell.CellStyle = cellStyle;
IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
}
SVM_OrganizeTargetBLL bll = null;
IList <SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString());
if (_targetlist.Count > 0)
{
if (_targetlist.FirstOrDefault <SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null)
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "当月的重点品项已审核，不可再次导入！\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
if (_targetlist.Count == 1)
{
bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID);
}
}
if (bll == null)
{
bll = new SVM_OrganizeTargetBLL
{
Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 }
};
}
decimal amount = 0M;
if (row.GetCell(cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model.FeeRateTarget = amount;
}
else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK)
{
errormessage += "ID号：" + cityid.ToString() + "，" + _city.Name + "办事处月度费率目标：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(6).SetCellValue(errormessage);
}
amount = 0M;
if (row.GetCell(++cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model["ActFee"] = amount.ToString();
decimal actSales = 0;
if (decimal.TryParse(bll.Model["ActSales"], out actSales) && actSales != 0)
{
bll.Model.FeeYieldRate = amount * 100 / actSales;
}
else
{
bll.Model.FeeYieldRate = 0;
}
}
else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK)
{
errormessage += "ID号：" + cityid.ToString() + "，" + _city.Name + "办事处上月发生费用：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(6).SetCellValue(errormessage);
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
bll.Update();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率被成功更新！\r\n";
}
else
{
bll.Add();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率已成功导入！\r\n";
}
row.GetCell(6).SetCellValue("导入成功");
#endregion
}
}
catch (System.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 backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
PDT_Product product;
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount() - 1;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "营业部" ||
headerRow.GetCell(1).ToString() != "办事处ID" ||
headerRow.GetCell(2).ToString() != "办事处" ||
headerRow.GetCell(3).ToString() != "归属月份" ||
headerRow.GetCell(4).ToString() != "办事处业绩目标额")
{
MessageBox.Show("工作表表头(1~5列)错误！\r\n");
return;
}
int month = 0;
rows.MoveNext();
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
while (rows.MoveNext())
{
i++;
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0).ToString() == "")
{
break;
}
int cloumn = 4;
int cityid = 0;
if (!int.TryParse(row.GetCell(1).ToString(), out cityid))
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "ID错误;\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model;
if (_city == null || _city.Name != row.GetCell(2).ToString())
{
errormessage += "办事处ID号：" + cityid.ToString() + "与办事处名称不匹配！\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
ICell cell = row.GetCell(3);
if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份")
{
//ICellStyle cellStyle = cell.CellStyle;
//IDataFormat format = hssfworkbook.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM");
//cell.CellStyle = cellStyle;
IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
}
SVM_OrganizeTargetBLL bll = null;
IList <SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString());
if (_targetlist.Count > 0)
{
if (_targetlist.FirstOrDefault <SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null)
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "当月的办事处目标已审核，不可再次导入！\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
if (_targetlist.Count == 1)
{
bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID);
}
}
if (bll == null)
{
bll = new SVM_OrganizeTargetBLL
{
Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 }
};
}
decimal amount = 0M;
if (decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model.SalesTarget = amount;
}
else
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "的办事处业绩目标额未能导入，办事处业绩目标额：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
++cloumn;
//IList<SVM_KeyProductTarget_Detail> details = new List<SVM_KeyProductTarget_Detail>();
//bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
//while (true)
//{
// product = null;
// amount = 0M;
// if (headerRow.GetCell(cloumn).CellType == CellType.BLANK || headerRow.GetCell(cloumn).ToString() == string.Empty)
// {
// break;
// }
// IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(cloumn).ToString() + "' AND State=1");
// if (products.Count > 0)
// {
// product = products[0];
// }
// else
// {
// errormessage += "产品名称：" + headerRow.GetCell(cloumn).ToString() + "在产品列表中不存在！\r\n";
// cloumn++;
// continue;
// }
// if ((product != null) && row.GetCell(cloumn).CellType != CellType.BLANK)
// {
// decimal.TryParse(row.GetCell(cloumn).ToString(), out amount);
// if (amount != 0M)
// {
// SVM_KeyProductTarget_Detail detail = new SVM_KeyProductTarget_Detail
// {
// Product = product.ID,
// Amount = amount
// };
// details.Add(detail);
// }
// else if (row.GetCell(cloumn).CellType != CellType.BLANK && (row.GetCell(cloumn).ToString() != "0"))
// {
// wrongflag = true;
// break;
// }
// }
// cloumn++;
//}
//if (wrongflag)
//{
// errormessage += "办事处：" + row.GetCell(2).ToString() + "的重点品项未能导入，品项名称：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
// continue;
//}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
//bll.DeleteDetail(); //先清除原先导入的数据
//bll.Items = details;
//bll.AddDetail();
bll.Update();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处目标被成功更新！\r\n";
}
else
{
//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 += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的重点品项已成功导入！\r\n";
}
//}
}
row.GetCell(5).SetCellValue("导入成功");
#endregion
}
}
catch (System.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 backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
PDT_Product product;
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount() - 1;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "营业部" ||
headerRow.GetCell(1).ToString() != "办事处ID" ||
headerRow.GetCell(2).ToString() != "办事处" ||
headerRow.GetCell(3).ToString() != "归属月份" ||
headerRow.GetCell(4).ToString() != "办事处月度费率目标" ||
headerRow.GetCell(5).ToString() != "办事处上月发生费用")
{
MessageBox.Show("工作表表头(1~6列)错误！\r\n");
return;
}
int month = 0;
rows.MoveNext();
while (rows.MoveNext())
{
i++;
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0) == null || row.GetCell(0).ToString() == "")
{
break;
}
int cloumn = 4;
int cityid = 0;
if (!int.TryParse(row.GetCell(1).ToString(), out cityid))
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "ID错误;\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model;
if (_city == null || _city.Name != row.GetCell(2).ToString())
{
errormessage += "办事处ID号：" + cityid.ToString() + "与办事处名称不匹配！\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
ICell cell = row.GetCell(3);
if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份")
{
//ICellStyle cellStyle = cell.CellStyle;
//IDataFormat format = hssfworkbook.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM");
//cell.CellStyle = cellStyle;
IList<AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
}
SVM_OrganizeTargetBLL bll = null;
IList<SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString());
if (_targetlist.Count > 0)
{
if (_targetlist.FirstOrDefault<SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null)
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "当月的重点品项已审核，不可再次导入！\r\n";
row.GetCell(6).SetCellValue(errormessage);
continue;
}
if (_targetlist.Count == 1)
{
bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID);
}
}
if (bll == null)
{
bll = new SVM_OrganizeTargetBLL
{
Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 }
};
}
decimal amount = 0M;
if (row.GetCell(cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model.FeeRateTarget = amount;
}
else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK)
{
errormessage += "ID号：" + cityid.ToString() + "，" + _city.Name + "办事处月度费率目标：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(6).SetCellValue(errormessage);
}
amount = 0M;
if (row.GetCell(++cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model["ActFee"] = amount.ToString();
decimal actSales=0;
if (decimal.TryParse(bll.Model["ActSales"],out actSales) && actSales != 0)
{
bll.Model.FeeYieldRate = amount * 100 / actSales;
}
else bll.Model.FeeYieldRate = 0;
}
else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK)
{
errormessage += "ID号：" + cityid.ToString() + "，" + _city.Name + "办事处上月发生费用：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(6).SetCellValue(errormessage);
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
bll.Update();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率被成功更新！\r\n";
}
else
{
bll.Add();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处费率已成功导入！\r\n";
}
row.GetCell(6).SetCellValue("导入成功");
#endregion
}
}
catch (System.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 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 + "导入操作成功！"));
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
HSSFWorkbook hssfworkbook;
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
PDT_Product product;
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int i = 0, count = getRowsCount()-1;
try
{
IRow headerRow = sheet.GetRow(0);
if (headerRow.GetCell(0).ToString() != "营业部" ||
headerRow.GetCell(1).ToString() != "办事处ID" ||
headerRow.GetCell(2).ToString() != "办事处" ||
headerRow.GetCell(3).ToString() != "归属月份" ||
headerRow.GetCell(4).ToString() != "办事处业绩目标额")
{
MessageBox.Show("工作表表头(1~5列)错误！\r\n");
return;
}
int month = 0;
rows.MoveNext();
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
while (rows.MoveNext())
{
i++;
((BackgroundWorker)sender).ReportProgress(i * 100 / count, i);
HSSFRow row = (HSSFRow)rows.Current;
if (row.GetCell(0).ToString() == "")
{
break;
}
int cloumn = 4;
int cityid = 0;
if (!int.TryParse(row.GetCell(1).ToString(), out cityid))
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "ID错误;\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model;
if (_city == null || _city.Name != row.GetCell(2).ToString())
{
errormessage += "办事处ID号：" + cityid.ToString() + "与办事处名称不匹配！\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
ICell cell = row.GetCell(3);
if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份")
{
//ICellStyle cellStyle = cell.CellStyle;
//IDataFormat format = hssfworkbook.CreateDataFormat();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM");
//cell.CellStyle = cellStyle;
IList<AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'");
if (_monthlist.Count > 0)
{
month = _monthlist[0].ID;
}
else
{
errormessage += "会计月错误;\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
}
SVM_OrganizeTargetBLL bll = null;
IList<SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString());
if (_targetlist.Count > 0)
{
if (_targetlist.FirstOrDefault<SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null)
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "当月的办事处目标已审核，不可再次导入！\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
if (_targetlist.Count == 1)
{
bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID);
}
}
if (bll == null)
{
bll = new SVM_OrganizeTargetBLL
{
Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 }
};
}
decimal amount = 0M;
if (decimal.TryParse(row.GetCell(cloumn).ToString(), out amount))
{
bll.Model.SalesTarget = amount;
}
else
{
errormessage += "办事处：" + row.GetCell(2).ToString() + "的办事处业绩目标额未能导入，办事处业绩目标额：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
row.GetCell(5).SetCellValue(errormessage);
continue;
}
++cloumn;
//IList<SVM_KeyProductTarget_Detail> details = new List<SVM_KeyProductTarget_Detail>();
//bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
//while (true)
//{
// product = null;
// amount = 0M;
// if (headerRow.GetCell(cloumn).CellType == CellType.BLANK || headerRow.GetCell(cloumn).ToString() == string.Empty)
// {
// break;
// }
// IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(cloumn).ToString() + "' AND State=1");
// if (products.Count > 0)
// {
// product = products[0];
// }
// else
// {
// errormessage += "产品名称：" + headerRow.GetCell(cloumn).ToString() + "在产品列表中不存在！\r\n";
// cloumn++;
// continue;
// }
// if ((product != null) && row.GetCell(cloumn).CellType != CellType.BLANK)
// {
// decimal.TryParse(row.GetCell(cloumn).ToString(), out amount);
// if (amount != 0M)
// {
// SVM_KeyProductTarget_Detail detail = new SVM_KeyProductTarget_Detail
// {
// Product = product.ID,
// Amount = amount
// };
// details.Add(detail);
// }
// else if (row.GetCell(cloumn).CellType != CellType.BLANK && (row.GetCell(cloumn).ToString() != "0"))
// {
// wrongflag = true;
// break;
// }
// }
// cloumn++;
//}
//if (wrongflag)
//{
// errormessage += "办事处：" + row.GetCell(2).ToString() + "的重点品项未能导入，品项名称：" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n";
// continue;
//}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
//bll.DeleteDetail(); //先清除原先导入的数据
//bll.Items = details;
//bll.AddDetail();
bll.Update();
improtmessage += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的办事处目标被成功更新！\r\n";
}
else
{
//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 += "ID号：" + cityid.ToString() + "，" + _city.Name + " 的重点品项已成功导入！\r\n";
}
//}
}
row.GetCell(5).SetCellValue("导入成功");
#endregion
}
}
catch (System.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 BindData()
{
SVM_OrganizeTarget target=new SVM_OrganizeTargetBLL((int)ViewState["TargetID"]).Model;
dv_detail.BindData(target);
if (target.ApproveFlag == 1 || target.ApproveFlag == 4)
{
bt_Save.Visible = false;
bt_Submit.Visible = false;
bt_Approve.Visible = true;
}
else
{
bt_Approve.Visible = false;
}
}
