protected void AddEmptyDetail()
{
SaveGrid();
ListTable<FNA_FeeApplyDetail> _details = ViewState["Details"] as ListTable<FNA_FeeApplyDetail>;
for (int i = 0; i < 5; i++)
{
ViewState["MaxID"] = (int)ViewState["MaxID"] + 1;
FNA_FeeApplyDetail item;
item = new FNA_FeeApplyDetail();
item.AccountTitle = 1;
item.ApplyCost = 0;
item.ID = (int)ViewState["MaxID"];
#region 获取当前会计月的开始及截止日期
int month = (int)ViewState["AccountMonth"];
if (month == 0) month = AC_AccountMonthBLL.GetCurrentMonth();
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
item.BeginDate = m.BeginDate;
item.EndDate = m.EndDate;
item.BeginMonth = month;
item.EndMonth = month;
#endregion
item.Flag = 1; //未报销
item.Remark = "";
_details.Add(item); //新增科目
}
BindGrid();
}
protected void AddEmptyDetail()
{
SaveGrid();
ListTable<FNA_FeeWriteOffDetail> _details = ViewState["Details"] as ListTable<FNA_FeeWriteOffDetail>;
for (int i = 0; i < 5; i++)
{
ViewState["MaxID"] = (int)ViewState["MaxID"] + 1;
FNA_FeeWriteOffDetail item;
item = new FNA_FeeWriteOffDetail();
item.AccountTitle = 1;
item.ApplyCost = 0;
item.WriteOffCost = 0;
item.ID = (int)ViewState["MaxID"];
#region 获取当前会计月的开始及截止日期
int month = AC_AccountMonthBLL.GetMonthByDate(DateTime.Today.AddDays(-7));
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
item.BeginMonth = m.ID;
item.EndMonth = m.ID;
item.BeginDate = m.BeginDate;
item.EndDate = m.EndDate;
#endregion
item.Remark = "";
_details.Add(item); //新增科目
}
BindGrid();
}
protected void ddl_AccountMonth_SelectedIndexChanged(object sender, EventArgs e)
{
int month = 0;
if (int.TryParse(ddl_AccountMonth.SelectedValue, out month) && month > 0)
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
if (m != null)
{
tbx_begin.Text = m.BeginDate.ToString("yyyy-MM-dd");
tbx_end.Text = m.EndDate.ToString("yyyy-MM-dd");
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("季度费率");
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 12 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 16 * 256);
sheet.SetColumnWidth(6, 12 * 256);
int rowcount = 0;
IRow row = sheet.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("营业部");
row.CreateCell(1).SetCellValue("办事处ID");
row.CreateCell(2).SetCellValue("办事处");
row.CreateCell(3).SetCellValue("归属月份");
row.CreateCell(4).SetCellValue("办事处月度费率目标");
row.CreateCell(5).SetCellValue("办事处上月发生费用");
row.CreateCell(6).SetCellValue("导入标志");
IList<Addr_OrganizeCity> _cityList = Addr_OrganizeCityBLL.GetModelList("Level=4 Order By Level3_SuperID");
AC_AccountMonthBLL monthbll = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddMonths(-1)));
foreach (Addr_OrganizeCity city in _cityList)
{
row = sheet.CreateRow(++rowcount);
row.CreateCell(0).SetCellValue(new Addr_OrganizeCityBLL(city.SuperID).Model.Name);
row.CreateCell(1).SetCellValue(city.ID);
row.CreateCell(2).SetCellValue(city.Name);
row.CreateCell(3).SetCellValue(monthbll.Model.Name);
row.CreateCell(4).SetCellValue("");
row.CreateCell(5).SetCellValue("");
row.CreateCell(6).SetCellValue("");
worker.ReportProgress(rowcount * 100 / _cityList.Count, rowcount);
}
//保存
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
book.Write(fs);
}
book = null;
sheet = null;
}
protected void bt_Add_Click(object sender, EventArgs e)
{
AC_AccountMonthBLL _accountmonthbll;
if (ViewState["ID"] != null)
{
//修改
_accountmonthbll = new AC_AccountMonthBLL(int.Parse(ViewState["ID"].ToString()));
}
else
{
//新增
_accountmonthbll = new AC_AccountMonthBLL();
}
#region 获取界面信息
_accountmonthbll.Model.Name = this.tbx_Name.Text;
_accountmonthbll.Model.Year = int.Parse(this.tbx_AccountYear.Text.Trim());
_accountmonthbll.Model.BeginDate = DateTime.Parse(this.tbx_BeginDate.Text.Trim());
_accountmonthbll.Model.EndDate = DateTime.Parse(this.tbx_EndDate.Text.Trim() + " 23:59:59");
_accountmonthbll.Model.Month = int.Parse(this.tbx_AccountMonth.Text.Trim());
#endregion
if (ViewState["ID"] != null)
{
//修改
if (_accountmonthbll.Update()<0)
{
lbl_AlertInfo.Text = "更新记录失败！";
return;
}
}
else
{
//新增
if (_accountmonthbll.Add()<=0)
{
lbl_AlertInfo.Text = "新增记录失败！";
return;
}
}
Response.Redirect("AccountMonth.aspx");
}
protected void btn_Delete_Click(object sender, EventArgs e)
{
if (ViewState["ID"] != null)
{
AC_AccountMonthBLL _accountmonthbll= new AC_AccountMonthBLL(int.Parse(ViewState["ID"].ToString()));
if (_accountmonthbll.Delete() < 0)
{
lbl_AlertInfo.Text = "删除会计月失败";
return;
}
}
else
{
lbl_AlertInfo.Text ="请选择要删除的会计月";
return;
}
Response.Redirect("AccountMonth.aspx");
}
protected void bt_SetEndDate_Click(object sender, EventArgs e)
{
AC_AccountMonth maxendmonth = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetCurrentMonth() + 1).Model;
CM_ContractBLL _bll = new CM_ContractBLL((int)ViewState["ContractID"]);
if (_bll != null && _bll.Model.ApproveFlag == 1 && _bll.Model.State == 3)
{
if (Convert.ToDateTime(txt_EndDate.Text.ToString().Trim()) > _bll.Model.EndDate)
{
MessageBox.Show(this, "设定截止日期不能大于原有截止日期！");
return;
}
if (_bll.Model.EndDate <= _bll.Model.BeginDate)
{
MessageBox.Show(this, "对不起，合同起始日期不能大于截止日期!");
return;
}
_bll.Model.EndDate =Convert.ToDateTime(txt_EndDate.Text.ToString().Trim());
if (_bll.Model.EndDate > maxendmonth.EndDate)
{
MessageBox.Show(this, "对不起，截止日期最大值为" + maxendmonth.EndDate.ToString("yyyy-MM-dd") + "。");
return;
}
//导购工资
if (_bll.Model.Classify == 3)
{
int lastmonth = _bll.CheckPMFeeApplyLastMonth();
int conendmonth =AC_AccountMonthBLL.GetMonthByDate(_bll.Model.EndDate.AddDays(1));
if (conendmonth <= lastmonth)
{
MessageBox.Show(this, "对不起，该协议已生成预付管理费申请单，请重新填写终止日期(截止日期最小值为" + new AC_AccountMonthBLL(lastmonth).Model.EndDate.ToString("yyyy-MM-dd") + ")！");
return;
}
}
_bll.Update();
_bll.Disable((int)Session["UserID"]);
MessageBox.ShowAndClose(this, "协议中止成功！");
}
}
private void BindDropDown()
{
chk_Citylist.DataSource = Addr_OrganizeCityBLL.GetModelList("Level=" + ConfigHelper.GetConfigString("OrganizePartCity-CityLevel"));
chk_Citylist.DataBind();
if ((int)ViewState["Type"] == 2)
{
MCSTabControl1.SelectedIndex = 2;
tb_step1.Visible = MCSTabControl1.SelectedIndex == 0;
}
IList <PDT_Brand> _brandList = PDT_BrandBLL.GetModelList("IsOpponent=1");
ddl_Brand.DataTextField = "Name";
ddl_Brand.DataValueField = "ID";
ddl_Brand.DataSource = _brandList;
ddl_Brand.DataBind();
ddl_Brand.Items.Insert(0, new ListItem("全部", "0"));
//CheckOrganizeCity();
ddl_AccountMonth.DataSource = AC_AccountMonthBLL.GetModelList("");
ddl_AccountMonth.DataBind();
ddl_AccountMonth.SelectedValue = (AC_AccountMonthBLL.GetCurrentMonth() - 1).ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["IsOpponent"] = Request.QueryString["IsOpponent"] != null?int.Parse(Request.QueryString["IsOpponent"]) : 9;
ViewState["ClientType"] = Request.QueryString["ClientType"] != null?int.Parse(Request.QueryString["ClientType"]) : 3;
ViewState["Type"] = Request.QueryString["Type"] != null?int.Parse(Request.QueryString["Type"]) : 0;
#region 获取最迟的销量月份
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
ViewState["month"] = month;
#endregion
ViewState["Produtcts"] = new StringBuilder(",");
BindDropDown();
BindGrid();
}
Produtcts = (StringBuilder)ViewState["Produtcts"];
}
private void BindDropDown()
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
if ((int)ViewState["OrganizeCity"] > 0)
{
tr_OrganizeCity.SelectValue = ViewState["OrganizeCity"].ToString();
}
#endregion
int forwarddays = ConfigHelper.GetConfigInt("FeeApplyForwardDays");
ddl_Month.DataSource = AC_AccountMonthBLL.GetModelList("BeginDate<='" + DateTime.Today.AddDays(forwarddays).ToString("yyyy-MM-dd") +
"' AND Year>=" + (DateTime.Today.Year - 1).ToString());
ddl_Month.DataBind();
ddl_Month.SelectedValue = (int)ViewState["AccountMonth"] == 0 ? AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(forwarddays)).ToString() : ViewState["AccountMonth"].ToString();
ddl_RTChannel.DataSource = DictionaryBLL.GetDicCollections("CM_RT_Channel");
ddl_RTChannel.DataBind();
ddl_RTChannel.Items.Insert(0, new ListItem("所有", "0"));
ddl_RTChannel.SelectedValue = "0";
ddl_RTType.DataSource = DictionaryBLL.GetDicCollections("CM_RT_Classify");
ddl_RTType.DataBind();
ddl_RTType.Items.Insert(0, new ListItem("所有", "0"));
ddl_RTType.SelectedValue = "0";
}
protected void bt_AddDetail_Click(object sender, EventArgs e)
{
IList <CAT_FeeApplyDetail> _details = (IList <CAT_FeeApplyDetail>)ViewState["FeeListDetails"];
CAT_FeeApplyDetail _m = new CAT_FeeApplyDetail();
int client = 0;
int.TryParse(select_Client.SelectValue, out client);
_m.Client = client;
_m.AccountTitle = int.Parse(ddl_AccountTitle.SelectedValue);
if (TreeTableBLL.GetChild("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", _m.AccountTitle).Rows.Count > 0)
{
MessageBox.Show(this, "费用科目必须选择最底级会计科目!" + ddl_AccountTitle.SelectedItem.Text);
return;
}
_m.ApplyCost = decimal.Parse(tbx_ApplyCost.Text.Trim());
_m.DICost = decimal.Parse(tbx_DICost.Text.Trim());
_m.Remark = tbx_Remark.Text.Trim();
if ((int)ViewState["ID"] != 0)
{
_m.BeginMonth = AC_AccountMonthBLL.GetMonthByDate(new CAT_ActivityBLL((int)ViewState["ID"]).Model.PlanBeginDate);
_m.Activity = (int)ViewState["ID"];
}
else
{
_m.BeginMonth = AC_AccountMonthBLL.GetCurrentMonth();
}
if (_details.Where(p => p.Client == client && p.AccountTitle == _m.AccountTitle).Count() == 0)
{
if (_m.Activity > 0)
{
CAT_FeeApplyDetailBLL _bll = new CAT_FeeApplyDetailBLL();
_bll.Model = _m;
_bll.Add();
}
_details.Add(_m);
BindGrid();
}
}
private void BindGrid()
{
AC_AccountMonth month = new AC_AccountMonthBLL(int.Parse(ddl_AccountMonth.SelectedValue)).Model;
int monthdays = month.EndDate.Subtract(month.BeginDate).Days + 1;
ViewState["monthdays"] = monthdays;
string condition = "FNA_StaffSalaryDataObject.AccountMonth=" + ddl_AccountMonth.SelectedValue;
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND MCS_SYS.dbo.Org_Staff.OrganizeCity in(" + orgcitys + ") ";
}
if (select_Staff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.Org_Staff.ID=" + select_Staff.SelectValue;
}
if (rbl_ApproveFlag.SelectedValue != "0")
{
condition += " AND FNA_StaffSalaryDataObject.SubmitFlag=" + rbl_ApproveFlag.SelectedValue;
}
if (MCSTabControl1.SelectedIndex == 0)
{
condition += " AND FNA_StaffSalaryDataObject.Position=210";
}
else
{
condition += " AND FNA_StaffSalaryDataObject.Position!=210";
}
gv_List.ConditionString = condition + " ORDER BY MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(Org_Staff.OrganizeCity,3),MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(Org_Staff.OrganizeCity,4)";
gv_List.BindGrid();
}
/// <summary>
/// 绑定下拉列表框
/// </summary>
private void BindDropDown()
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
#endregion
ddl_AccountMonth.DataSource = AC_AccountMonthBLL.GetModelList("");
ddl_AccountMonth.DataBind();
ddl_AccountMonth.SelectedValue = (AC_AccountMonthBLL.GetCurrentMonth() - 1).ToString();
}
private void BindDropDown()
{
#region 绑定用户可管辖的片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
tr_OrganizeCity_Selected(null, null);
#endregion
ddl_Month.DataSource = AC_AccountMonthBLL.GetModelList("BeginDate<GETDATE() AND Year>=" + (DateTime.Today.Year - 1).ToString());
ddl_Month.DataBind();
ddl_Month.SelectedValue = AC_AccountMonthBLL.GetMonthByDate(DateTime.Now).ToString();
}
private void BindDropDown()
{
#region 绑定用户可管辖的片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
#endregion
ddl_Month.DataSource = AC_AccountMonthBLL.GetModelList("BeginDate Between dateadd(month,-4,getdate()) AND getdate()");
ddl_Month.DataBind();
ddl_Month.SelectedValue = AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddMonths(-1)).ToString();
select_Client.PageUrl = "~/SubModule/CM/PopSearch/Search_SelectClient.aspx?ClientType=2&ExtCondition=\"MCS_SYS.dbo.UF_Spilt(CM_Client.ExtPropertys,~|~,7)=1\"";
}
private void BindDropDown()
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
tr_OrganizeCity_Selected(null, null);
#endregion
int forwarddays = ConfigHelper.GetConfigInt("FeeApplyForwardDays");
ddl_AccountMonth.DataSource = AC_AccountMonthBLL.GetModelList("EndDate>=GETDATE() AND BeginDate<='" + DateTime.Today.AddDays(forwarddays).ToString("yyyy-MM-dd") +
"' AND Year>=" + (DateTime.Today.Year - 1).ToString());
ddl_AccountMonth.DataBind();
ddl_AccountMonth.SelectedValue = AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(forwarddays)).ToString();
ddl_FeeType.DataSource = DictionaryBLL.GetDicCollections("FNA_FeeType").OrderBy(p => p.Value.Name).ToList();
ddl_FeeType.DataBind();
ddl_FeeType.SelectedValue = ConfigHelper.GetConfigInt("CarFeeType").ToString();
ddl_FeeType.Enabled = false;
//foreach (ListItem item in ddl_FeeType.Items)
//{
// if (AC_AccountTitleBLL.GetListByFeeType(int.Parse(item.Value)).Where(p => p.ID > 1).ToList().Count == 0)
// {
// item.Enabled = false;
// }
//}
}
private void BindDropDown()
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
DataTable dt = staff.GetStaffOrganizeCity();
tr_FromOrganizeCity.DataSource = dt;
Org_StaffBLL staff2 = new Org_StaffBLL((int)Session["UserID"]);
DataTable dt2 = staff2.GetStaffOrganizeCity();
tr_ToOrganizeCity.DataSource = dt2;
if (dt.Select("ID = 1").Length > 0)
{
tr_FromOrganizeCity.RootValue = "0";
tr_ToOrganizeCity.RootValue = "0";
}
else
{
tr_FromOrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_FromOrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
//tr_FromOrganizeCity.Enabled = false;
tr_ToOrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff2.Model.OrganizeCity).Model.SuperID.ToString();
tr_ToOrganizeCity.SelectValue = staff2.Model.OrganizeCity.ToString();
}
#endregion
ddl_FromMonth.DataSource = AC_AccountMonthBLL.GetModelList("EndDate>GETDATE() AND EndDate<DateAdd(month,1,GETDATE())");
ddl_FromMonth.DataBind();
//ddl_FromMonth.Enabled = false;
ddl_ToMonth.DataSource = AC_AccountMonthBLL.GetModelList("EndDate>GETDATE() AND EndDate<DateAdd(month,1,GETDATE())");
ddl_ToMonth.DataBind();
//ddl_ToMonth.Enabled = false;
}
protected void select_Client_SelectChange(object sender, MCSControls.MCSWebControls.SelectChangeEventArgs e)
{
ddl_LinkMan.Items.Clear();
int client = 0;
if (int.TryParse(select_Client.SelectValue, out client) && client > 0)
{
IList <CM_LinkMan> lists = CM_LinkManBLL.GetModelList("ClientID = " + client.ToString());
foreach (CM_LinkMan item in lists)
{
ddl_LinkMan.Items.Add(new ListItem(item.Name, item.ID.ToString()));
}
CM_ClientBLL clientbll = new CM_ClientBLL(client);
decimal forcast = clientbll.GetSalesForcast((int)ViewState["AccountMonth"]);
tbx_SalesForcast.Text = forcast.ToString("0.##");
tbx_SalesForcast.ReadOnly = forcast > 0;
lb_PreSalesVolume.Text = clientbll.GetSalesVolume(AC_AccountMonthBLL.GetCurrentMonth() - 1).ToString("0.##元");
lb_AvgSalesVolume.Text = clientbll.GetSalesVolumeAvg().ToString("0.##元");
}
ddl_LinkMan.Items.Insert(0, new ListItem("请选择", "0"));
}
private void BindDropDown()
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
#endregion
int forwarddays = ConfigHelper.GetConfigInt("FeeApplyForwardDays");
ddl_Month.DataSource = AC_AccountMonthBLL.GetModelList("EndDate>=GETDATE() AND BeginDate<='" + DateTime.Today.AddDays(forwarddays).ToString("yyyy-MM-dd") +
"' AND Year>=" + (DateTime.Today.Year - 1).ToString());
ddl_Month.DataBind();
ddl_Month.SelectedValue = AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(forwarddays)).ToString();
}
private void BindDropDown()
{
#region 绑定管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
#endregion
ddl_AccountMonth.DataSource = AC_AccountMonthBLL.GetModelList("BeginDate>DateAdd(month,-4,getdate()) AND EndDate<=Getdate()");
ddl_AccountMonth.DataBind();
ddl_AccountMonth.SelectedValue = (AC_AccountMonthBLL.GetCurrentMonth() - 1).ToString();
ddl_AccountMonth_SelectedIndexChanged(null, null);
}
protected void AddEmptyDetail()
{
SaveGrid();
ListTable <FNA_FeeApplyDetail> _details = ViewState["Details"] as ListTable <FNA_FeeApplyDetail>;
for (int i = 0; i < 5; i++)
{
ViewState["MaxID"] = (int)ViewState["MaxID"] + 1;
FNA_FeeApplyDetail item;
item = new FNA_FeeApplyDetail();
item.AccountTitle = 1;
item.ApplyCost = 0;
item.ID = (int)ViewState["MaxID"];
#region 获取当前会计月的开始及截止日期
int month = (int)ViewState["AccountMonth"];
if (month == 0)
{
month = AC_AccountMonthBLL.GetCurrentMonth();
}
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
item.BeginDate = m.BeginDate;
item.EndDate = m.EndDate;
item.BeginMonth = month;
item.EndMonth = month;
#endregion
item.Flag = 1; //未报销
item.Remark = "";
_details.Add(item); //新增科目
}
BindGrid();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["DataTable-SellIn"] = null;
Session["DataTable-SellOut"] = null;
select_Staff.SelectValue = Session["UserID"].ToString();
select_Staff.SelectText = Session["UserRealName"].ToString();
#region 获取最迟的销量月份
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
lb_MonthTitle.Text = month.Name + "(" + month.BeginDate.ToString("yyyy-MM-dd") + "至" + month.EndDate.ToString("yyyy-MM-dd") + ")";
#endregion
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
#region 获取页面参数
ViewState["TargetID"] = Request.QueryString["TargetID"] == null ? 0 : int.Parse(Request.QueryString["TargetID"]);
ViewState["ClientID"] = Request.QueryString["ClientID"] == null ? 0 : int.Parse(Request.QueryString["ClientID"]);
#endregion
BindDropDown();
if ((int)ViewState["TargetID"] != 0)
{
BindData();
btn_SalesTarget.Visible = false;
}
else
{
if ((int)ViewState["ClientID"] == 0)
{
Response.Redirect("SalesTarget.aspx");
}
#region 新增销量目标
CM_Client c = new CM_ClientBLL((int)ViewState["ClientID"]).Model;
MCSSelectControl select_Client = (MCSSelectControl)UC_DetailView1.FindControl("SVM_SalesTarget_Client");
if (select_Client != null)
{
select_Client.SelectValue = ViewState["ClientID"].ToString();
select_Client.SelectText = c.FullName;
select_Client.Enabled = false;
}
MCSTreeControl tr_OrganizeCity = (MCSTreeControl)UC_DetailView1.FindControl("SVM_SalesTarget_OrganizeCity");
if (tr_OrganizeCity != null)
{
tr_OrganizeCity.SelectValue = c.OrganizeCity.ToString();
tr_OrganizeCity.Enabled = false;
}
DropDownList ddl_Month = (DropDownList)UC_DetailView1.FindControl("SVM_SalesTarget_AccountMonth");
if (ddl_Month != null)
{
ddl_Month.SelectedValue = AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-7).AddMonths(1)).ToString();
}
bt_Del.Visible = false;
bt_Save.Visible = false;
bt_Approve.Visible = false;
#endregion
}
#region 确定页面权限
if ((int)ViewState["ClientID"] != 0)
{
CM_Client _r = new CM_ClientBLL((int)ViewState["ClientID"]).Model;
ViewState["ClientType"] = _r.ClientType;
if (_r.ClientType == 3)
{
Header.Attributes["WebPageSubCode"] += "ClientType=3";
}
else if (_r.ClientType == 2)
{
Header.Attributes["WebPageSubCode"] += "ClientType=2&DIClassify=" + _r["DIClassify"];
}
}
#endregion
}
}
private void BindGrid()
{
int month = int.Parse(ddl_AccountMonth.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
decimal _cost = 0;
decimal.TryParse(tbx_ApplyCost.Text, out _cost);
if (MCSTabControl1.SelectedIndex == 0)
{
DataTable dtSummary = CSO_OfferBalanceBLL.Summary(month, organizecity, level, state, (int)Session["UserID"]);
#region 矩阵化数据表，扩展表数据列
dtSummary.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
if (level < 10)
{
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["OrganizeCity"];
}
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
else if (level == 10)
{
//按经销商查看
#region 将经销商的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["经销商ID"] == DBNull.Value ? 0 : row["经销商ID"];
row["经销商名称"] = row["经销商名称"] == DBNull.Value ? "无" : row["经销商名称"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
else if (level == 20)
{
//按医生查看
#region 将医生的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["VIPID"] == DBNull.Value ? 0 : row["VIPID"];
row["VIP姓名"] = row["VIP姓名"] == DBNull.Value ? "无" : row["VIP姓名"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称", "VIP姓名" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
#endregion
if (dtSummary != null && dtSummary.Rows.Count > 0)
{
dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "有效名单", "支付费用" });
#region 增加本月新客数
string thismonthnewclientcount = "Convert([3.新客标准→小计→→有效名单], 'System.Int32')";
if (dtSummary.Columns.Contains("4.3段新客标准→小计→→有效名单"))
thismonthnewclientcount += " + Convert([4.3段新客标准→小计→→有效名单], 'System.Int32')";
string thismonthnewclientfee = "Convert([3.新客标准→小计→→支付费用], 'System.Decimal')";
if (dtSummary.Columns.Contains("4.3段新客标准→小计→→支付费用"))
thismonthnewclientfee += " + Convert([4.3段新客标准→小计→→支付费用], 'System.Decimal')";
dtSummary.Columns.Add(new DataColumn("本月新客户数", Type.GetType("System.Int32"), thismonthnewclientcount));
dtSummary.Columns.Add(new DataColumn("本月新客户费", Type.GetType("System.Decimal"), thismonthnewclientfee));
#endregion
#region 获取上月新客
dtSummary.Columns.Add(new DataColumn("上月新客数", Type.GetType("System.Int32")));
dtSummary.Columns.Add(new DataColumn("上月新客费", Type.GetType("System.Decimal")));
string ids = "";
foreach (DataRow row in dtSummary.Rows)
{
ids += row["ID"].ToString() + ",";
}
if (ids.EndsWith(",")) ids = ids.Substring(0, ids.Length - 1);
DataTable dtPreMonth = null;
if (level < 10)
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndOrganzieCity(month - 1, ids);
else if (level == 10)
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndDistributors(month - 1, ids);
else if (level == 20)
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndDoctors(month - 1, ids);
if (dtPreMonth != null)
{
foreach (DataRow row in dtSummary.Rows)
{
DataRow[] selectedrows = dtPreMonth.Select("ID=" + row["ID"].ToString());
if (selectedrows.Length > 0)
{
row["上月新客数"] = (int)selectedrows[0]["EffectiveAmount"];
row["上月新客费"] = (decimal)selectedrows[0]["PayFee"];
}
else
{
row["上月新客数"] = 0;
row["上月新客费"] = 0;
}
}
}
#region 增加上月新增增长比较
dtSummary.Columns.Add("较上月新客增长量", Type.GetType("System.Int32"), "CONVERT([本月新客户数],'System.Int32') - [上月新客数]");
dtSummary.Columns.Add("较上月新客增长率(%)", Type.GetType("System.String"),
"IIF([上月新客数]=0,'-',CONVERT(CONVERT((CONVERT([本月新客户数],'System.Int32') - [上月新客数])*100/[上月新客数],'System.Int32'),'System.String'))+'%'");
#endregion
#endregion
#region 按金额条件过滤
if (_cost != 0)
{
switch (ddl_ComparerField.SelectedValue)
{
case "1":
dtSummary.DefaultView.RowFilter = "[1.派样185标准→小计→→有效名单]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "2":
dtSummary.DefaultView.RowFilter = "[2.派样400标准→小计→→有效名单]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "3":
dtSummary.DefaultView.RowFilter = "[本月新客户数]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "4":
dtSummary.DefaultView.RowFilter = "[合计→支付费用]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "5":
dtSummary.DefaultView.RowFilter = "[较上月新客增长量]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "6":
dtSummary.DefaultView.RowFilter = "上月新客数>0 AND (([较上月新客增长量]*100/[上月新客数])" + ddl_ApplyCostOP.SelectedValue + _cost + ")";
break;
default:
break;
}
dtSummary = dtSummary.DefaultView.ToTable();
}
#endregion
#region 增加合计行
List<string> valuecolumns = new List<string>();
valuecolumns.Add("ID");
valuecolumns.Add("上月新客数");
valuecolumns.Add("上月新客费");
foreach (DataColumn c in dtSummary.Columns)
{
if (c.ColumnName.Contains("→")) valuecolumns.Add(c.ColumnName);
}
MatrixTable.TableAddSummaryRow(dtSummary, "管理片区名称", valuecolumns.ToArray());
#endregion
}
gv_List.DataSource = dtSummary;
gv_List.BindGrid();
if (dtSummary.Columns.Count >= 24)
gv_List.Width = new Unit(dtSummary.Columns.Count * 55);
else
gv_List.Width = new Unit(100, UnitType.Percentage);
MatrixTable.GridViewMatric(gv_List);
}
else
{
#region 组织明细记录的查询条件
string condition = "CSO_OfferBalance.AccountMonth=" + month.ToString();
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue == "0")
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
if ((int)ViewState["OrganizeCity"] > 0)
{
tr_OrganizeCity.SelectValue = ViewState["OrganizeCity"].ToString();
}
#endregion
}
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND CSO_OfferBalance.OrganizeCity IN (" + orgcitys + ")";
}
//审批状态
if (ddl_State.SelectedValue == "0")
condition += " AND CSO_OfferBalance.State IN (2,3) ";
else if (ddl_State.SelectedValue == "1")
condition +=
@" AND CSO_OfferBalance.State = 2 AND CSO_OfferBalance.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM [192.168.8.82].MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
[192.168.8.82].MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3)";
else if (ddl_State.SelectedValue == "2")
condition += " AND CSO_OfferBalance.State = 3 ";
else if (ddl_State.SelectedValue == "3")
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND CSO_OfferBalance.State IN (2,3) AND CSO_OfferBalance.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM [192.168.8.82].MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
[192.168.8.82].MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND
EWF_Task_JobDecision.DecisionTime BETWEEN DATEADD(month,-1,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"') AND
DATEADD(month,3,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"'))";
}
#endregion
gv_ListDetail.ConditionString = condition;
gv_ListDetail.BindGrid();
}
if (gv_List.Rows.Count > 0 || gv_ListDetail.Rows.Count > 0)
{
btn_Approve.Enabled = state == 1;
btn_UnApprove.Enabled = state == 1;
}
else
{
btn_Approve.Enabled = false;
btn_UnApprove.Enabled = false;
}
}
private void BindGrid()
{
AC_AccountMonth month = new AC_AccountMonthBLL(int.Parse(ddl_AccountMonth.SelectedValue)).Model;
int monthdays = month.EndDate.Subtract(month.BeginDate).Days + 1;
ViewState["monthdays"] = monthdays;
string condition = "FNA_StaffSalaryDataObjectBeta.AccountMonth=" + ddl_AccountMonth.SelectedValue;
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND FNA_StaffSalaryDataObjectBeta.OrganizeCity in(" + orgcitys + ") ";
}
if (MCSTabControl1.SelectedIndex == 0)
{
condition += " AND FNA_StaffSalaryDataObjectBeta.PositionType=1";
}
else
{
condition += " AND FNA_StaffSalaryDataObjectBeta.PositionType!=1";
}
if (select_Staff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.Org_Staff.ID=" + select_Staff.SelectValue;
}
if (ddl_ApproveFlag.SelectedValue != "0")
{
condition += " AND FNA_StaffSalaryDataObjectBeta.ApproveFlag=" + ddl_ApproveFlag.SelectedValue;
}
gv_List.ConditionString = condition + " Order By FNA_StaffSalaryDataObjectBeta_OrganizeCity1,FNA_StaffSalaryDataObjectBeta_OrganizeCity3,FNA_StaffSalaryDataObjectBeta_OrganizeCity4,Org_Staff_RealName";
gv_List.BindGrid();
if (MCSTabControl1.SelectedIndex == 0)
gv_List.Columns[gv_List.Columns.Count - 2].Visible = gv_List.Columns[gv_List.Columns.Count - 3].Visible = false;
else
gv_List.Columns[gv_List.Columns.Count - 2].Visible = gv_List.Columns[gv_List.Columns.Count - 3].Visible = true;
chk_Header.Checked = false;
ddl_AccountMonth_SelectedIndexChanged(null, null);
}
private void BindData()
{
int id = (int)ViewState["ID"];
ORD_OrderApply apply = new ORD_OrderApplyBLL(id).Model;
if (apply == null) Response.Redirect("FeeApplyList.aspx");
pn_OrderApply.BindData(apply);
ViewState["Type"] = apply.Type;
int month = new AC_AccountMonthBLL(apply.AccountMonth).Model.Month;
ORD_OrderLimitFactorBLL limitbll = new ORD_OrderLimitFactorBLL();
ViewState["Limit"] = limitbll.GetLimitInfo(apply.AccountMonth, apply.Client);
#region 绑定当前申请单的管理片区
Label lb_OrganizeCity = (Label)pn_OrderApply.FindControl("ORD_OrderApply_OrganizeCity");
lb_OrganizeCity.Text = TreeTableBLL.GetFullPathName("MCS_Sys.dbo.Addr_OrganizeCity", apply.OrganizeCity);
#endregion
BindGrid();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["IsOpponent"] = Request.QueryString["IsOpponent"] != null ? int.Parse(Request.QueryString["IsOpponent"]) : 9;
ViewState["ClientType"] = Request.QueryString["ClientType"] != null ? int.Parse(Request.QueryString["ClientType"]) : 3;
ViewState["Type"] = Request.QueryString["Type"] != null ? int.Parse(Request.QueryString["Type"]) : 0;
#region 获取最迟的销量月份
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
ViewState["month"] = month;
#endregion
ViewState["Produtcts"] = new StringBuilder(",");
BindDropDown();
BindGrid();
}
Produtcts = (StringBuilder)ViewState["Produtcts"];
}
private void BindGrid()
{
AC_AccountMonth month = new AC_AccountMonthBLL(int.Parse(ddl_AccountMonth.SelectedValue)).Model;
int monthdays = month.EndDate.Subtract(month.BeginDate).Days + 1;
ViewState["monthdays"] = monthdays;
string condition = "FNA_StaffSalaryDataObject.AccountMonth=" + ddl_AccountMonth.SelectedValue;
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND MCS_SYS.dbo.Org_Staff.OrganizeCity in(" + orgcitys + ") ";
}
if (select_Staff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.Org_Staff.ID=" + select_Staff.SelectValue;
}
if (rbl_ApproveFlag.SelectedValue != "0")
{
condition += " AND FNA_StaffSalaryDataObject.SubmitFlag=" + rbl_ApproveFlag.SelectedValue;
}
if (MCSTabControl1.SelectedIndex == 0)
{
condition += " AND FNA_StaffSalaryDataObject.Position=210";
}
else
{
condition += " AND FNA_StaffSalaryDataObject.Position!=210";
}
gv_List.ConditionString = condition +" ORDER BY MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(Org_Staff.OrganizeCity,3),MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(Org_Staff.OrganizeCity,4)";
gv_List.BindGrid();
}
private void BindGrid()
{
string condition = "SVM_InventoryDifferences_Detail.InventoryID=" + ViewState["InventoryID"].ToString();
if (ddl_Classify.SelectedValue.CompareTo("0") > 0)
{
condition += " AND PDT_Product.Classify=" + ddl_Classify.SelectedValue;
}
else if (ddl_Brand.SelectedValue.CompareTo("0") > 0)
{
condition += "AND PDT_Product.Brand=" + ddl_Brand.SelectedValue;
}
if (select_Product1.SelectValue != "")
{
condition += " AND SVM_InventoryDifferences_Detail.Product=" + select_Product1.SelectValue;
}
if (cb_OnlyDisplayUnZero.Checked)
{
condition += " AND SVM_InventoryDifferences_Detail.Quantity <> 0";
}
DropDownList ddl_Month = (DropDownList)UC_DetailView1.FindControl("SVM_InventoryDifferences_AccountMonth");
AC_AccountMonthBLL prbll = new AC_AccountMonthBLL(int.Parse(ddl_Month.SelectedValue)-1);
//gv_List.Columns[4].HeaderText = ddl_Month.SelectedItem.Text.Substring( ddl_Month.SelectedItem.Text.LastIndexOf("-")+1)+ "月实时库存（实物）";
//gv_List.Columns[5].HeaderText = prbll.Model.Month + "月" + "20日期末实物库存";
gv_List.ConditionString = condition;
gv_List.BindGrid();
if (!bt_Save.Visible)
{
gv_List.SetControlsEnable(false);
}
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int feetype = int.Parse(ddl_FeeType.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
if (MCSTabControl1.SelectedIndex == 0)
{
#region 显示汇总单数据源
Dictionary_Data dicFeeType = null;
if (feetype > 0) dicFeeType = DictionaryBLL.GetDicCollections("FNA_FeeType")[feetype.ToString()];
#region 额外条件
string condition = "";
//费用代垫客户
if (select_Client.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsteadPayClient=" + select_Client.SelectValue;
}
//费用代垫员工
if (Select_InsteadPayStaff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.UF_Spilt2('MCS_FNA.dbo.FNA_FeeWriteOff',FNA_FeeWriteOff.ExtPropertys,'InsteadPayStaff')=" + Select_InsteadPayStaff.SelectValue;
}
//核销申请人
if (Select_InsertStaff.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsertStaff=" + Select_InsertStaff.SelectValue;
}
#endregion
DataTable dtSummary = FNA_FeeWriteOffBLL.GetSummaryTotal(month, organizecity, level, feetype, state, int.Parse(Session["UserID"].ToString()), condition);
if (dtSummary.Rows.Count == 0)
{
gv_List.DataBind();
return;
}
#region 矩阵化数据表，扩展表数据列
dtSummary.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
if (level < 10)
{
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["OrganizeCity"];
}
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
else
{
if (level == 10)
{
//按经销商查看
#region 将经销商的所属管理片区赋至管理片区列
Dictionary<int, string> dicFullOrganizeCityName = new Dictionary<int, string>();
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["经销商ID"] == DBNull.Value ? 0 : row["经销商ID"];
row["经销商名称"] = row["经销商名称"] == DBNull.Value ? "无" : row["经销商名称"];
if ((int)row["ID"] > 0)
{
CM_Client c = new CM_ClientBLL((int)row["ID"]).Model;
if (c != null)
{
if (!dicFullOrganizeCityName.ContainsKey(c.OrganizeCity))
{
dicFullOrganizeCityName.Add(c.OrganizeCity, TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OrganizeCity", c.OrganizeCity));
}
row["管理片区名称"] = dicFullOrganizeCityName[c.OrganizeCity];
}
}
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
else if (level == 20)
{
//按门店查看
#region 将门店的所属管理片区赋至管理片区列
Dictionary<int, string> dicFullOrganizeCityName = new Dictionary<int, string>();
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["客户ID"] == DBNull.Value ? 0 : row["客户ID"];
row["客户名称"] = row["客户名称"] == DBNull.Value ? "无" : row["客户名称"];
if ((int)row["ID"] > 0)
{
CM_Client c = new CM_ClientBLL((int)row["ID"]).Model;
if (c != null)
{
if (!dicFullOrganizeCityName.ContainsKey(c.OrganizeCity))
{
dicFullOrganizeCityName.Add(c.OrganizeCity, TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OrganizeCity", c.OrganizeCity));
}
row["管理片区名称"] = dicFullOrganizeCityName[c.OrganizeCity];
}
}
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "客户名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
}
dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "WriteOffCost" });
#endregion
#region 暂不计算分析数据
//dtSummary.Columns["合计"].ColumnName = "费用合计";
//dtSummary.Columns.Add("预计销量", Type.GetType("System.Decimal"));
//if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
//{
// dtSummary.Columns.Add("预算总额", Type.GetType("System.Decimal"));
// dtSummary.Columns.Add("其中扩增额", Type.GetType("System.Decimal"));
// dtSummary.Columns.Add("预算余额", Type.GetType("System.Decimal"));
//}
//if (dicFeeType != null && dicFeeType.Description == "FeeRateControl")
// dtSummary.Columns.Add("上月余额", Type.GetType("System.Decimal"));
//dtSummary.Columns.Add("费点", Type.GetType("System.String"));
//decimal sumTotalVolume = 0, sumTotalBudget = 0, sumExtBudget = 0, sumPreMonthBudgetBalance = 0, sumUsableAmount = 0;
//foreach (DataRow row in dtSummary.Rows)
//{
// int id = 0;
// if (int.TryParse(row[0].ToString(), out id) && id > 0)
// {
// bool includechild = true;
// if (level < 10 && new Addr_OrganizeCityBLL(id).Model.Level < level) includechild = false;
// #region 计算费点
// if (level < 10)
// row["预计销量"] = SVM_SalesForcastBLL.GetTotalVolume(month, id, includechild);
// else if (level == 10 || level == 20)
// row["预计销量"] = SVM_SalesForcastBLL.GetTotalVolumeByClient(month, id);
// sumTotalVolume += (decimal)row["预计销量"];
// if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
// {
// row["预算总额"] = (FNA_BudgetBLL.GetSumBudgetAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetSumBudgetAmount(month, id, 0, includechild));
// sumTotalBudget += (decimal)row["预算总额"];
// row["其中扩增额"] = (FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, feetype, includechild) + FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, 0, includechild));
// sumExtBudget += (decimal)row["其中扩增额"];
// row["预算余额"] = (FNA_BudgetBLL.GetUsableAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetUsableAmount(month, id, 0, includechild));
// sumUsableAmount += (decimal)row["预算余额"];
// }
// if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
// {
// row["上月余额"] = FNA_BudgetBLL.GetUsableAmount(month - 1, id, feetype, includechild);
// sumPreMonthBudgetBalance += (decimal)row["上月余额"];
// if ((decimal)row["预计销量"] != 0)
// row["费点"] = (((decimal)row["费用合计"] - (decimal)row["上月余额"]) / (decimal)row["预计销量"]).ToString("0.00%");
// }
// else if ((decimal)row["预计销量"] != 0)
// row["费点"] = ((decimal)row["费用合计"] / (decimal)row["预计销量"]).ToString("0.00%");
// #endregion
// }
// #region 求合计行
// if (id == 0)
// {
// row["预计销量"] = sumTotalVolume.ToString("0.00");
// #region 预算总额及余额
// if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
// {
// row["预算总额"] = sumTotalBudget.ToString("0.00");
// row["其中扩增额"] = sumExtBudget.ToString("0.00");
// row["预算余额"] = sumUsableAmount.ToString("0.00");
// }
// #endregion
// #region 费点
// if (sumTotalVolume != 0)
// {
// if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
// {
// row["上月余额"] = sumPreMonthBudgetBalance;
// row["费点"] = (((decimal)row["费用合计"] - sumPreMonthBudgetBalance) / sumTotalVolume).ToString("0.##%");
// }
// else
// row["费点"] = ((decimal)row["费用合计"] / sumTotalVolume).ToString("0.##%");
// }
// #endregion
// }
// #endregion
//}
#endregion
#endregion
gv_List.DataSource = dtSummary;
gv_List.DataBind();
if (dtSummary.Columns.Count >= 24)
gv_List.Width = new Unit(dtSummary.Columns.Count * 55);
else
gv_List.Width = new Unit(100, UnitType.Percentage);
MatrixTable.GridViewMatric(gv_List);
if (level == 20)
{
//按终端门店显示时，将上下行相同管理片区合并
MatrixTable.GridViewMergSampeValueRow(gv_List, 0);
MatrixTable.GridViewMergSampeValueRow(gv_List, 1);
}
#region 是否可以批量审批
if (state != 1 || level >= 10)
{
gv_List.Columns[0].ItemStyle.Width = new Unit(1);
foreach (GridViewRow row in gv_List.Rows)
{
row.FindControl("bt_Approved").Visible = false;
row.FindControl("bt_UnApproved").Visible = false;
}
}
else
{
gv_List.Columns[0].ItemStyle.Width = new Unit(68);
}
#endregion
}
else
{
string condition = "1=1";
#region 组织明细记录的查询条件
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND FNA_FeeWriteOff.OrganizeCity IN (" + orgcitys + ")";
}
//会计月条件
condition += " AND FNA_FeeWriteOff.AccountMonth = " + ddl_Month.SelectedValue;
int accounttile = 0;
int.TryParse(tr_AccountTitle.SelectValue, out accounttile);
decimal _cost = 0;
decimal.TryParse(tbx_WriteOffCost.Text, out _cost);
//费用类型
if (ddl_FeeType.SelectedValue != "0" && !(accounttile > 1))
{
IList<AC_AccountTitleInFeeType> titles = AC_AccountTitleInFeeTypeBLL.GetModelList("FeeType=" + ddl_FeeType.SelectedValue);
string ids = "";
foreach (AC_AccountTitleInFeeType item in titles)
{
ids += item.AccountTitle + ",";
}
if (ids.EndsWith(",")) ids = ids.Substring(0, ids.Length - 1);
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
//费用科目
if (accounttile > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString());
string ids = "";
foreach (DataRow dr in dt.Rows)
{
ids += dr["ID"].ToString() + ",";
}
ids += accounttile.ToString();
if (_cost == 0)
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
else
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND (WriteOffCost+AdjustCost)" + ddl_WriteOffCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
else if (_cost != 0)
{ //核销金额判断
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE (WriteOffCost+AdjustCost)" + ddl_WriteOffCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
//审批状态
if (ddl_State.SelectedValue == "0")
condition += " AND FNA_FeeWriteOff.State IN (2,3) ";
else if (ddl_State.SelectedValue == "1")
condition +=
@" AND FNA_FeeWriteOff.State = 2 AND FNA_FeeWriteOff.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3)";
else if (ddl_State.SelectedValue == "2")
condition += " AND FNA_FeeWriteOff.State = 3 ";
else if (ddl_State.SelectedValue == "3")
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND FNA_FeeWriteOff.State IN (2,3) AND FNA_FeeWriteOff.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND
EWF_Task_JobDecision.DecisionTime BETWEEN DATEADD(month,-1,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"') AND
DATEADD(month,3,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"'))";
}
//费用代垫客户
if (select_Client.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsteadPayClient=" + select_Client.SelectValue;
}
//费用代垫员工
if (Select_InsteadPayStaff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.UF_Spilt2('MCS_FNA.dbo.FNA_FeeWriteOff',FNA_FeeWriteOff.ExtPropertys,'InsteadPayStaff')=" + Select_InsteadPayStaff.SelectValue;
}
//核销申请人
if (Select_InsertStaff.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsertStaff=" + Select_InsertStaff.SelectValue;
}
#endregion
gv_ListDetail.ConditionString = condition;
gv_ListDetail.BindGrid();
}
}
protected void bt_Init_Click(object sender, EventArgs e)
{
int month = 0, organizecity = 0, staff = 0;
DateTime trackdate = DateTime.Today;
if (int.TryParse(tr_OrganizeCity.SelectValue, out organizecity) &&
int.TryParse(select_Staff.SelectValue, out staff) && DateTime.TryParse(tbx_TrackDate.Text, out trackdate))
{
month = AC_AccountMonthBLL.GetMonthByDate(trackdate);
#region 限制填报规则
if (trackdate > DateTime.Today)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报今天之后的数据!");
return;
}
if (trackdate < DateTime.Today.AddDays(-7))
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报7天之前的数据!");
return;
}
if (DateTime.Today.Day > 5 && trackdate.Day <= 5)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报5日之前的数据！");
return;
}
else if (DateTime.Today.Day > 10 && trackdate.Day <= 10)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报10日之前的数据！");
return;
}
else if (DateTime.Today.Day > 15 && trackdate.Day <= 15)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报15日之前的数据！");
return;
}
else if (DateTime.Today.Day > 20 && trackdate.Day <= 20)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报20日之前的数据！");
return;
}
else if (DateTime.Today.Day > 25 && trackdate.Day <= 25)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报25日之前的数据！");
return;
}
else if (DateTime.Today.Month != trackdate.Month)
{
tbx_TrackDate.Text = DateTime.Today.ToString("yyyy-MM-dd");
MessageBox.Show(this, "对不起，不能填报上月的数据！");
return;
}
#endregion
TC_TrackCardBLL.InitTrack(month, organizecity, staff, trackdate, (int)Session["UserID"]);
}
BindGrid();
}
protected void bt_OK_Click(object sender, EventArgs e)
{
HR_SpecialApplyBLL _bll;
if ((int)ViewState["ID"] != 0)
{
//修改
_bll = new HR_SpecialApplyBLL((int)ViewState["ID"]);
}
else
{
//新增
_bll = new HR_SpecialApplyBLL();
_bll.Model.AccountMonth = AC_AccountMonthBLL.GetCurrentMonth();
}
pl_detail.GetData(_bll.Model);
#region 判断必填项
if (_bll.Model.OrganizeCity == 0)
{
MessageBox.Show(this, "必须正确选择管理片区!");
return;
}
if (_bll.Model.AccountMonth == 0)
{
MessageBox.Show(this, "必须正确选择会计月!");
return;
}
if (_bll.Model.AccountTitleType == 0)
{
MessageBox.Show(this, "必须正确选择科目!");
return;
}
//if (_bll.Model["SectorName"]=="0")
//{
// MessageBox.Show(this, "必须正确选择审批部门名称!");
// return;
//}
#endregion
if ((int)ViewState["ID"] != 0)
{
//修改
_bll.Model.UpdateStaff = (int)Session["UserID"];
if (_bll.Update() == 0)
{
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "修改成功!", "HR_SpecialApplyDetail.aspx?ID=" + ViewState["ID"].ToString());
}
}
}
else
{
//新增
_bll.Model.ApproveFlag = 1;//ApproveFlag 状态采用费用申请State审批状态
_bll.Model.InsertStaff = (int)Session["UserID"];
ViewState["ID"] = _bll.Add();
bt_Submit.Visible = true;
if ((int)ViewState["ID"] > 0)
{
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "新增成功!", "HR_SpecialApplyDetail.aspx?ID=" + ViewState["ID"].ToString());
}
}
}
}
private void BindDropDown()
{
ddl_AccountMonth.DataSource = AC_AccountMonthBLL.GetModelList("");
ddl_AccountMonth.DataBind();
ddl_AccountMonth.SelectedValue = AC_AccountMonthBLL.GetCurrentMonth().ToString();
}
protected void bt_Generate_Click(object sender, EventArgs e)
{
decimal giftamountbalance = 0;
int month = 0, city = 0, feetype = 0, client = 0, productbrand = 0, giftclassify = 0;
feetype = (int)ViewState["GiftFeeType"];
int.TryParse(ddl_ApplyMonth.SelectedValue, out month);
int.TryParse(tr_OrganizeCity.SelectValue, out city);
int.TryParse(select_Client.SelectValue, out client);
int.TryParse(ddl_Brand.SelectedValue, out productbrand);
int.TryParse(rbl_GiftClassify.SelectedValue, out giftclassify);
#region 判断有效性
if (city == 0)
{
MessageBox.Show(this, "请正确选择管理片区");
return;
}
if (client == 0)
{
MessageBox.Show(this, "请正确选择经销商");
return;
}
if (productbrand == 0)
{
MessageBox.Show(this, "请正确选择赠品费用归属品牌");
return;
}
giftamountbalance = GetGiftAmountBalance();
if (giftamountbalance == 0)
{
MessageBox.Show(this, "可申请赠品金额为0，不可申请赠品");
return;
}
int AccountTitle = int.Parse(ddl_AccountTitle.SelectedValue);
if (TreeTableBLL.GetChild("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", AccountTitle).Rows.Count > 0)
{
MessageBox.Show(this, "费用科目必须选择最底级会计科目!" + ddl_AccountTitle.SelectedItem.Text);
return;
}
decimal applycost = 0;
decimal.TryParse(tbx_ApplyCost.Text, out applycost);
if (applycost == 0)
{
MessageBox.Show(this, "申请赠品金额不可为0");
return;
}
if (applycost > giftamountbalance)
{
MessageBox.Show(this, "赠品申请金额:" + tbx_ApplyCost.Text + "不能大于可用申请赠品金额:" + lb_GiftAmountBalance.Text);
return;
}
#endregion
#region 创建费用申请单
FNA_FeeApplyBLL bll = new FNA_FeeApplyBLL();
bll.Model.OrganizeCity = int.Parse(tr_OrganizeCity.SelectValue);
bll.Model.AccountMonth = int.Parse(ddl_ApplyMonth.SelectedValue);
bll.Model.Client = client; //费用代垫客户
bll.Model.State = 1; //草稿
bll.Model.ApproveFlag = 2; //未审核
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model["Title"] = ddl_ApplyMonth.SelectedItem.Text + " 赠品费用申请单" + " " + select_Client.SelectText + " " + Session["UserRealName"].ToString();
bll.Model.FeeType = feetype;
bll.Model["GiftFeeClassify"] = giftclassify.ToString();
bll.Model.ProductBrand = productbrand;
AC_AccountMonth accountmonth = new AC_AccountMonthBLL(month).Model;
FNA_FeeApplyDetail detail = new FNA_FeeApplyDetail();
detail.Client = client;
detail.AccountTitle = AccountTitle;
detail.BeginMonth = month;
detail.EndMonth = month;
detail.BeginDate = accountmonth.BeginDate;
detail.EndDate = accountmonth.EndDate;
detail.ApplyCost = applycost * (100 - decimal.Parse(tbx_DIPercent.Text)) / 100;
detail.DICost = applycost * decimal.Parse(tbx_DIPercent.Text) / 100;
detail.SalesForcast = decimal.Parse(tbx_SalesForcast.Text);
detail.Remark = tbx_Remark.Text;
detail.RelateBrands = productbrand.ToString();
detail.LastWriteOffMonth = int.Parse(ddl_LastWriteOffMonth.SelectedValue);
detail.Flag = 1; //未报销
detail["FeeApplyType"] = "1";
bll.Items.Add(detail);
bll.Model.SheetCode = FNA_FeeApplyBLL.GenerateSheetCode(bll.Model.OrganizeCity, bll.Model.AccountMonth);
int id = bll.Add();
#endregion
if (id > 0)
{
Response.Redirect("FeeApplyDetail3.aspx?ID=" + id.ToString());
}
else
{
MessageBox.Show(this, "对不起，赠品费用申请单生成失败！错误码:" + id.ToString());
}
}
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
}
private bool CheckLimitWriteOffDate(int month)
{
bool flag = true;
AC_AccountMonthBLL _bll = new AC_AccountMonthBLL(month);
if (_bll.Model.EndDate.AddDays(-11) > DateTime.Now)
{
flag = false;
MessageBox.Show(this, "对不起，" + _bll.Model.EndDate.AddDays(-11).ToString() + "后才可操作该费用的核销!");
}
return flag;
}
private void BindGrid()
{
int month = int.Parse(ddl_AccountMonth.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
decimal _cost = 0;
decimal.TryParse(tbx_ApplyCost.Text, out _cost);
if (MCSTabControl1.SelectedIndex == 0)
{
DataTable dtSummary = CSO_OfferBalanceBLL.Summary(month, organizecity, level, state, (int)Session["UserID"]);
#region 矩阵化数据表，扩展表数据列
dtSummary.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
if (level < 10)
{
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["OrganizeCity"];
}
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
else if (level == 10)
{
//按经销商查看
#region 将经销商的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["经销商ID"] == DBNull.Value ? 0 : row["经销商ID"];
row["经销商名称"] = row["经销商名称"] == DBNull.Value ? "无" : row["经销商名称"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
else if (level == 20)
{
//按医生查看
#region 将医生的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["VIPID"] == DBNull.Value ? 0 : row["VIPID"];
row["VIP姓名"] = row["VIP姓名"] == DBNull.Value ? "无" : row["VIP姓名"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称", "VIP姓名" },
new string[] { "结算标准", "派发品牌", "派发产品" }, new string[] { "有效名单", "支付费用" }, false, true);
}
#endregion
if (dtSummary != null && dtSummary.Rows.Count > 0)
{
dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "有效名单", "支付费用" });
#region 增加本月新客数
string thismonthnewclientcount = "Convert([3.新客标准→小计→→有效名单], 'System.Int32')";
if (dtSummary.Columns.Contains("4.3段新客标准→小计→→有效名单"))
{
thismonthnewclientcount += " + Convert([4.3段新客标准→小计→→有效名单], 'System.Int32')";
}
string thismonthnewclientfee = "Convert([3.新客标准→小计→→支付费用], 'System.Decimal')";
if (dtSummary.Columns.Contains("4.3段新客标准→小计→→支付费用"))
{
thismonthnewclientfee += " + Convert([4.3段新客标准→小计→→支付费用], 'System.Decimal')";
}
dtSummary.Columns.Add(new DataColumn("本月新客户数", Type.GetType("System.Int32"), thismonthnewclientcount));
dtSummary.Columns.Add(new DataColumn("本月新客户费", Type.GetType("System.Decimal"), thismonthnewclientfee));
#endregion
#region 获取上月新客
dtSummary.Columns.Add(new DataColumn("上月新客数", Type.GetType("System.Int32")));
dtSummary.Columns.Add(new DataColumn("上月新客费", Type.GetType("System.Decimal")));
string ids = "";
foreach (DataRow row in dtSummary.Rows)
{
ids += row["ID"].ToString() + ",";
}
if (ids.EndsWith(","))
{
ids = ids.Substring(0, ids.Length - 1);
}
DataTable dtPreMonth = null;
if (level < 10)
{
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndOrganzieCity(month - 1, ids);
}
else if (level == 10)
{
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndDistributors(month - 1, ids);
}
else if (level == 20)
{
dtPreMonth = CSO_OfferBalanceBLL.GetNewClientAmountAndFee_ByMonthAndDoctors(month - 1, ids);
}
if (dtPreMonth != null)
{
foreach (DataRow row in dtSummary.Rows)
{
DataRow[] selectedrows = dtPreMonth.Select("ID=" + row["ID"].ToString());
if (selectedrows.Length > 0)
{
row["上月新客数"] = (int)selectedrows[0]["EffectiveAmount"];
row["上月新客费"] = (decimal)selectedrows[0]["PayFee"];
}
else
{
row["上月新客数"] = 0;
row["上月新客费"] = 0;
}
}
}
#region 增加上月新增增长比较
dtSummary.Columns.Add("较上月新客增长量", Type.GetType("System.Int32"), "CONVERT([本月新客户数],'System.Int32') - [上月新客数]");
dtSummary.Columns.Add("较上月新客增长率(%)", Type.GetType("System.String"),
"IIF([上月新客数]=0,'-',CONVERT(CONVERT((CONVERT([本月新客户数],'System.Int32') - [上月新客数])*100/[上月新客数],'System.Int32'),'System.String'))+'%'");
#endregion
#endregion
#region 金额条件过滤
if (_cost != 0)
{
switch (ddl_ComparerField.SelectedValue)
{
case "1":
dtSummary.DefaultView.RowFilter = "[1.派样185标准→小计→→有效名单]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "2":
dtSummary.DefaultView.RowFilter = "[2.派样400标准→小计→→有效名单]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "3":
dtSummary.DefaultView.RowFilter = "[本月新客户数]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "4":
dtSummary.DefaultView.RowFilter = "[合计→支付费用]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "5":
dtSummary.DefaultView.RowFilter = "[较上月新客增长量]" + ddl_ApplyCostOP.SelectedValue + _cost;
break;
case "6":
dtSummary.DefaultView.RowFilter = "上月新客数>0 AND (([较上月新客增长量]*100/[上月新客数])" + ddl_ApplyCostOP.SelectedValue + _cost + ")";
break;
default:
break;
}
dtSummary = dtSummary.DefaultView.ToTable();
}
#endregion
#region 增加合计行
List <string> valuecolumns = new List <string>();
valuecolumns.Add("ID");
valuecolumns.Add("上月新客数");
valuecolumns.Add("上月新客费");
foreach (DataColumn c in dtSummary.Columns)
{
if (c.ColumnName.Contains("→"))
{
valuecolumns.Add(c.ColumnName);
}
}
MatrixTable.TableAddSummaryRow(dtSummary, "管理片区名称", valuecolumns.ToArray());
#endregion
}
gv_List.DataSource = dtSummary;
gv_List.BindGrid();
if (dtSummary.Columns.Count >= 24)
{
gv_List.Width = new Unit(dtSummary.Columns.Count * 55);
}
else
{
gv_List.Width = new Unit(100, UnitType.Percentage);
}
MatrixTable.GridViewMatric(gv_List);
}
else
{
#region 组织明细记录的查询条件
string condition = "CSO_OfferBalance.AccountMonth=" + month.ToString();
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue == "0")
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
if ((int)ViewState["OrganizeCity"] > 0)
{
tr_OrganizeCity.SelectValue = ViewState["OrganizeCity"].ToString();
}
#endregion
}
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND CSO_OfferBalance.OrganizeCity IN (" + orgcitys + ")";
}
//审批状态
if (ddl_State.SelectedValue == "0")
{
condition += " AND CSO_OfferBalance.State IN (2,3) ";
}
else if (ddl_State.SelectedValue == "1")
{
condition +=
@" AND CSO_OfferBalance.State = 2 AND CSO_OfferBalance.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM [192.168.8.82].MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
[192.168.8.82].MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3)";
}
else if (ddl_State.SelectedValue == "2")
{
condition += " AND CSO_OfferBalance.State = 3 ";
}
else if (ddl_State.SelectedValue == "3")
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND CSO_OfferBalance.State IN (2,3) AND CSO_OfferBalance.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM [192.168.8.82].MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
[192.168.8.82].MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND
EWF_Task_JobDecision.DecisionTime BETWEEN DATEADD(month,-1,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"') AND
DATEADD(month,3,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"'))";
}
#endregion
gv_ListDetail.ConditionString = condition;
gv_ListDetail.BindGrid();
}
if (gv_List.Rows.Count > 0 || gv_ListDetail.Rows.Count > 0)
{
btn_Approve.Enabled = state == 1;
btn_UnApprove.Enabled = state == 1;
}
else
{
btn_Approve.Enabled = false;
btn_UnApprove.Enabled = false;
}
}
private void BindData()
{
if (ViewState["ID"] != null)
{
AC_AccountMonth _month =new AC_AccountMonthBLL(int.Parse(ViewState["ID"].ToString())).Model;
this.tbx_Name.Text = _month.Name;
this.tbx_AccountMonth.Text = _month.Month.ToString();
this.tbx_AccountYear.Text = _month.Year.ToString();
this.tbx_BeginDate.Text = _month.BeginDate.ToShortDateString();
this.tbx_EndDate.Text = _month.EndDate.ToShortDateString();
this.bt_Add.Text = "修改";
this.bt_Add.ForeColor = System.Drawing.Color.Red;
this.btn_Delete.Visible = true;
MessageBox.ShowConfirm(this.btn_Delete, "确实要删除所选择的会计月吗？");
}
}
protected void bt_OK_Click(object sender, EventArgs e)
{
FNA_BudgetExtraApplyBLL _bll;
if ((int)ViewState["ID"] != 0)
{
//修改
_bll = new FNA_BudgetExtraApplyBLL((int)ViewState["ID"]);
}
else
{
//新增
_bll = new FNA_BudgetExtraApplyBLL();
_bll.Model.AccountMonth = AC_AccountMonthBLL.GetCurrentMonth();
}
pl_detail.GetData(_bll.Model);
//_bll.Model["SheetCode"] = FNA_BudgetExtraApplyBLL.GenerateSheetCode(_bll.Model.OrganizeCity);在存储过程中实现
#region 判断必填项
if (_bll.Model.OrganizeCity == 0)
{
MessageBox.Show(this, "必须正确选择管理片区!");
return;
}
if (_bll.Model.FeeType == 0)
{
MessageBox.Show(this, "必须正确选择费用类型!");
return;
}
if (_bll.Model["ExtraType"] == "0")
{
MessageBox.Show(this, "必须正确选择扩增费用类别!");
return;
}
#endregion
#region 判断上级直至部经理手中有无预算
decimal sumbudget = 0;
Addr_OrganizeCityBLL city = new Addr_OrganizeCityBLL(_bll.Model.OrganizeCity);
DataTable dt = city.GetFullPath();
for (int i = 0; i < dt.Rows.Count; i++)
{
Addr_OrganizeCity c = new Addr_OrganizeCityBLL((int)dt.Rows[i]["ID"]).Model;
if (c == null || c.ID == 1 || c.ID == _bll.Model.OrganizeCity || c.Level == 1)
{
continue; //忽略总部
}
sumbudget += FNA_BudgetBLL.GetUsableAmount(_bll.Model.AccountMonth, c.ID, _bll.Model.FeeType);
}
if (sumbudget > _bll.Model.ExtraAmount)
{
MessageBox.Show(this, "对不起，您的上级领导预算余额大于您要扩增的金额，请与领导沟通申请要求分配预算给您，而无需提交扩增预算流程！");
return;
}
#endregion
if ((int)ViewState["ID"] != 0)
{
//修改
_bll.Model.UpdateStaff = (int)Session["UserID"];
if (_bll.Update() == 0)
{
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "修改成功!", "BudgetExtraApplyDetail.aspx?ID=" + ViewState["ID"].ToString());
}
}
}
else
{
//新增
_bll.Model.ApproveFlag = 2;
_bll.Model.InsertStaff = (int)Session["UserID"];
ViewState["ID"] = _bll.Add();
bt_Submit.Visible = true;
if ((int)ViewState["ID"] > 0)
{
if (sender != null)
{
MessageBox.ShowAndRedirect(this, "新增成功!", "BudgetExtraApplyDetail.aspx?ID=" + ViewState["ID"].ToString());
}
}
}
}
protected void bt_AddTeleFee_Click(object sender, EventArgs e)
{
if (ddl_Tele.SelectedValue == "0") return;
int teleid = int.Parse(ddl_Tele.SelectedValue);
CM_PropertyInTelephone tele = new CM_PropertyInTelephoneBLL(teleid).Model;
decimal writeoffcost = decimal.Parse(tbx_TeleCost.Text);
decimal applycost = tele.TeleCost + tele.NetCost;
if (writeoffcost > applycost)
{
MessageBox.Show(this, "对不起，实际报销金额不能超过申请限额" + lb_TeleApplyCost.Text + "!");
tbx_TeleCost.Text = lb_TeleApplyCost.Text;
return;
}
SaveGrid();
ViewState["MaxID"] = (int)ViewState["MaxID"] + 1;
#region 组织电话费核销明细
FNA_FeeWriteOffDetail item = new FNA_FeeWriteOffDetail();
item.ID = (int)ViewState["MaxID"];
item.AccountTitle = ConfigHelper.GetConfigInt("TeleCostACTitle");
item.ApplyCost = applycost;
item.WriteOffCost = writeoffcost;
item["TeleFeeRelateTelephone"] = tele.ID.ToString();
item.Client = tele.Client;
#region 获取当前会计月的开始及截止日期
int month = int.Parse(ddl_TeleCostMonth.SelectedValue);
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
item.BeginMonth = m.ID;
item.EndMonth = m.ID;
item.BeginDate = m.BeginDate;
item.EndDate = m.EndDate;
#endregion
if (tele.Client > 0)
{
CM_Client client = new CM_ClientBLL(tele.Client).Model;
if (client != null) item.Remark = "物业:" + client.FullName + ";";
}
item.Remark += "电话号码:" + ddl_Tele.SelectedItem.Text + ";月份:" + ddl_TeleCostMonth.SelectedItem.Text +
";限额:" + lb_TeleApplyInfo.Text + ";";
if (tbx_TeleRemark.Text != "")
item.Remark += "说明:" + tbx_TeleRemark.Text;
#endregion
if (FNA_FeeWriteOffBLL.CheckTeleFeeHasWriteOff(item.AccountTitle, item.BeginMonth, tele.ID))
{
MessageBox.Show(this, "该电话指定月的费用，已在其他报销单中报销，请勿重复报销!");
}
else
{
ListTable<FNA_FeeWriteOffDetail> _details = ViewState["Details"] as ListTable<FNA_FeeWriteOffDetail>;
if (_details.GetListItem().FirstOrDefault(p =>
p.AccountTitle == item.AccountTitle &&
p.BeginMonth == item.BeginMonth &&
p["TeleFeeRelateTelephone"] == item["TeleFeeRelateTelephone"]) == null)
{
_details.Add(item); //新增科目
}
else
{
MessageBox.Show(this, "该电话指定月的费用，已在本报销单中，请勿重复报销!");
}
}
BindGrid();
AddEmptyDetail();
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int state = int.Parse(ddl_State.SelectedValue);
if (MCSTabControl1.SelectedTabItem.Value == "0")
{
#region 显示汇总数据
gv_List.Visible = true; gv_DetailList.Visible = false;
DataTable dtSummary = FNA_FeeApplyBLL.GetFLFeeSummary2(month, organizecity, int.Parse(ddl_Level.SelectedValue), state, int.Parse(Session["UserID"].ToString()));
if (dtSummary.Rows.Count == 0)
{
gv_List.DataBind();
return;
}
//dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "管理片区名称", "行政属性" },
// new string[] { "RTChannel", "DisplayFeeType", "Title" }, "Summary", true, false);
//MatrixTable.TableAddRowSubTotal_Matric(dtSummary, new string[] { "管理片区名称" }, 2, new string[] { "RTChannel", "DisplayFeeType", "Title" }, false);
#region 重新计算总计行的费率
//if (dtSummary.Rows.Count > 1)
//{
// foreach (DataRow dr in dtSummary.Rows)
// {
// if (dr[1].ToString().EndsWith("计") || dr[2].ToString().EndsWith("计"))
// {
// foreach (DataColumn dc in dtSummary.Columns)
// {
// if (dc.ColumnName.EndsWith("费率%"))
// {
// string title = dc.ColumnName;
// int pos = title.IndexOf('→');
// if (pos > 0)
// {
// title = title.Substring(0, pos);
// if (dtSummary.Columns.Contains(title + "→1.返利费总计(元/月)→A.我司承担") &&
// dtSummary.Columns.Contains(title + "→2.销量及费率→A.实际进货(元/月)") &&
// (decimal)dr[title + "→2.销量及费率→A.实际进货(元/月)"] != 0)
// {
// dr[dc.ColumnName] = Math.Round((decimal)dr[title + "→1.返利费总计(元/月)→A.我司承担"] / (decimal)dr[title + "→2.销量及费率→A.实际进货(元/月)"] * 100, 1, MidpointRounding.AwayFromZero);
// }
// }
// }
// }
// }
// }
//}
#endregion
gv_List.DataSource = dtSummary;
gv_List.DataBind();
if (dtSummary.Columns.Count >= 24)
gv_List.Width = new Unit(dtSummary.Columns.Count * 65);
else
gv_List.Width = new Unit(100, UnitType.Percentage);
MatrixTable.GridViewMatric(gv_List);
MatrixTable.GridViewMergSampeValueRow(gv_List, 0);
#endregion
}
else
{
gv_List.Visible = false; gv_DetailList.Visible = true;
if (ddl_State.SelectedValue=="1" && Right_Assign_BLL.GetAccessRight(Session["UserName"].ToString(), 1510, "BatApproveFee"))
{
bt_Approve.Visible = false;
bt_UnApprove.Visible = true;
bt_Approve.OnClientClick = "return confirm('是否确认将所选中所有申请单批量设为审批通过？注意该操作可能耗时较长，请耐心等待！')";
bt_UnApprove.OnClientClick = "return confirm('是否确认将所选中所有申请单批量设为审批不通过？注意该操作可能耗时较长，请耐心等待！')";
}
#region 组织明细查询条件
string condition = "";
condition += @"FNA_FeeApply.FeeType=7 AND EXISTS(SELECT 1 FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE FNA_FeeApplyDetail.AccountTitle=82
AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID AND BeginMonth=" + ddl_Month.SelectedValue + ")";
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(organizecity);
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += organizecity;
condition += " AND FNA_FeeApply.OrganizeCity IN (" + orgcitys + ")";
}
if (state == 0)
condition += " AND FNA_FeeApply.State IN (2,3) ";
else if (state == 1)
condition +=
@" AND FNA_FeeApply.State = 2 AND
EXISTS (
SELECT EWF_Task_Job.Task
FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
INNER JOIN MCS_EWF.dbo.EWF_Task ON EWF_Task_Job.Task= EWF_Task.ID AND EWF_Task.App='4eb9e905-3502-4caf-88d0-aadcfec6e4dd'
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3 AND FNA_FeeApply.ApproveTask=EWF_Task_Job.Task)";
else if (state == 2)
condition += " AND FNA_FeeApply.State = 3 ";
else if (state == 3)
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND FNA_FeeApply.State IN (2,3) AND EXISTS
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
INNER JOIN MCS_EWF.dbo.EWF_Task ON=EWF_Task_Job.Task=EWF_Task.ID AND EWF_Task.App='4eb9e905-3502-4caf-88d0-aadcfec6e4dd'
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND FNA_FeeApply.ApproveTask=EWF_Task_Job.Task)";
}
#endregion
gv_DetailList.ConditionString = condition;
gv_DetailList.BindGrid();
}
#region 是否可以批量审批
if (Right_Assign_BLL.GetAccessRight(Session["UserName"].ToString(), 1510, "BatApproveFee"))
{
bt_Approve.Visible = (ddl_State.SelectedValue == "1");
bt_UnApprove.Visible = (ddl_State.SelectedValue == "1");
bt_Approve.Enabled = (ddl_State.SelectedValue == "1");
bt_UnApprove.Enabled = (ddl_State.SelectedValue == "1");
#region 判断费用申请进度
if (ddl_State.SelectedValue == "1")
{
Org_StaffBLL _staff = new Org_StaffBLL((int)Session["UserID"]);
DataTable dt = _staff.GetLowerPositionTask(2, int.Parse(tr_OrganizeCity.SelectValue), month);
if (AC_AccountMonthBLL.GetCurrentMonth() - 1 <= int.Parse(ddl_Month.SelectedValue))
{
string[] allowdays1 = Addr_OrganizeCityParamBLL.GetValueByType(1, 5).Replace(" ", "").Split(new char[] { ',', '，', ';', '；' });
string[] allowdays2 = Addr_OrganizeCityParamBLL.GetValueByType(1, 6).Replace(" ", "").Split(new char[] { ',', '，', ';', '；' });
string date = DateTime.Now.Day.ToString();
if (allowdays1.Contains(date))
bt_Approve.Enabled = false;
else if (allowdays2.Contains(date))
{
DataTable dt2 = new DataTable();
if (_staff.Model.Position == 210)
dt2 = _staff.GetFillProcessDetail(2);
if (dt.Rows.Count > 0 || dt2.Rows.Count > 0)
{
bt_Approve.Enabled = false;
}
}
else
{
bt_UnApprove.Enabled = false;
}
}
else
{
bt_UnApprove.Enabled = false;
}
if (dt.Rows.Count > 0)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "message", "<script language='javascript'>var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("~/SubModule/Pop_ShowLowerPositionTask.aspx") +
"?Type=2&StaffID=0&Month=" + ddl_Month.SelectedValue + "&City=" + tr_OrganizeCity.SelectValue + "&tempid='+tempid, window, 'dialogWidth:520px;DialogHeight=600px;status:yes;resizable=no');</script>", false);
bt_Approve.Enabled = false;
}
}
#endregion
}
else
{
bt_Approve.Visible = false;
bt_UnApprove.Visible = false;
}
#endregion
}
protected void tbx_VolumeDate_TextChanged(object sender, EventArgs e)
{
DateTime volumedate;
if (DateTime.TryParse(tbx_VolumeDate.Text, out volumedate))
{
if (volumedate > DateTime.Now.Date)
{
MessageBox.Show(this, "发生日期不能超出当天日期！");
tbx_VolumeDate.Text = DateTime.Now.ToString("yyyy-MM-dd");
return;
}
if (AC_AccountMonthBLL.GetMonthByDate(volumedate) != int.Parse(ddl_AccountMonth.SelectedValue))
{
AC_AccountMonthBLL _bll = new AC_AccountMonthBLL(int.Parse(ddl_AccountMonth.SelectedValue));
MessageBox.Show(this, "发生日期应在" + _bll.Model.BeginDate.ToShortDateString() + "~" + _bll.Model.EndDate.ToShortDateString() + "之间");
tbx_VolumeDate.Text = _bll.Model.EndDate.ToShortDateString();
return;
}
}
else
{
MessageBox.Show(this, "日期格式不正确!");
tbx_VolumeDate.Text = DateTime.Now.ToString("yyyy-MM-dd");
}
}
private void BindData()
{
int id = (int)ViewState["ID"];
ORD_OrderApply apply = new ORD_OrderApplyBLL(id).Model;
if (apply == null) Response.Redirect("OrderApplyList.aspx");
if (apply["ProductType"] != "1") priceEnable = true;
pn_OrderApply.BindData(apply);
ViewState["Type"] = apply.Type;
int month = new AC_AccountMonthBLL(apply.AccountMonth).Model.Month;
ORD_OrderLimitFactorBLL limitbll = new ORD_OrderLimitFactorBLL();
ViewState["Limit"] = limitbll.GetLimitInfo(apply.AccountMonth, apply.Client);
#region 绑定当前申请单的管理片区
Label lb_OrganizeCity = (Label)pn_OrderApply.FindControl("ORD_OrderApply_OrganizeCity");
lb_OrganizeCity.Text = TreeTableBLL.GetFullPathName("MCS_Sys.dbo.Addr_OrganizeCity", apply.OrganizeCity);
#endregion
#region 根据审批状态控制页面
if (apply.State != 1 && apply.State != 8)
{
//提交 状态
pn_OrderApply.SetControlsEnable(false);
gv_ProductList.Columns[0].Visible = false; //选择 列
gv_ProductList.Columns[gv_ProductList.Columns.Count - 1].Visible = false; //删除 列
bt_Save.Visible = false;
bt_Submit.Visible = false;
bt_Delete.Visible = false;
//可见调整数量及原因
gv_ProductList.Columns[gv_ProductList.Columns.Count - 4].Visible = true; //调整原因
gv_ProductList.Columns[gv_ProductList.Columns.Count - 5].Visible = true; //调整金额
bt_SaveAdjust.Visible = false;
}
if (apply.State == 2)
{
bt_SaveAdjust.Visible = false;
///已提交状态，审批过程中，可以作申请数量调整
if (Request.QueryString["Decision"] != "" && Request.QueryString["Decision"] == "Y")
{
bt_SaveAdjust.Visible = true;
}
}
if (apply.State >= 3)
{
bt_SaveAdjust.Visible = false;
//审批通过
gv_ProductList.Columns[gv_ProductList.Columns.Count - 3].Visible = true; //已发放数量
}
#endregion
BindGrid();
}
protected void bt_WriteOff_Click(object sender, EventArgs e)
{
#region 求选中行的差旅费、住宿费、补贴合计
decimal Cost1 = 0, Cost2 = 0, Cost3 = 0, Cost4 = 0, Cost5 = 0;
decimal Cost11 = 0, Cost12 = 0, Cost13 = 0; //车辆费用
decimal Cost21 = 0, Cost22 = 0;
DateTime minbegindate = DateTime.Parse("2999-1-1"), maxenddate = DateTime.Parse("1900-1-1");
List <int> selectedevectionids = new List <int>();
foreach (GridViewRow row in gv_List.Rows)
{
CheckBox cbx = (CheckBox)row.FindControl("cbx");
if (cbx != null && cbx.Checked)
{
int id = (int)gv_List.DataKeys[row.RowIndex][0];
FNA_EvectionRoute evection = new FNA_EvectionRouteBLL(id).Model;
#region 根据关联的工作日志的类型,判断是否培训费
//获取关联的日志类型
int journalid = 0, workingclassify = 0;
if (int.TryParse(evection["RelateJournal"], out journalid) && journalid > 0)
{
JN_Journal Joural = new JN_JournalBLL(journalid).Model;
if (Joural != null)
{
workingclassify = Joural.WorkingClassify;
}
}
switch (workingclassify)
{
case 4: //总部组织的培训，计入总部培训-差旅费
Cost21 += evection.Cost1 + evection.Cost2 + evection.Cost3 + evection.Cost4 + evection.Cost5;
break;
case 5: //省区组织的培训，计入营业部培训-差旅费
Cost22 += evection.Cost1 + evection.Cost2 + evection.Cost3 + evection.Cost4 + evection.Cost5;
break;
default:
Cost1 += evection.Cost1; //交通费
Cost2 += evection.Cost2; //住宿费
Cost3 += evection.Cost3; //补贴
Cost4 += evection.Cost4; //市内交通费
Cost5 += evection.Cost5; //的士费
break;
}
#endregion
if (minbegindate > evection.BeginDate)
{
minbegindate = evection.BeginDate;
}
if (maxenddate < evection.EndDate)
{
maxenddate = evection.EndDate;
}
int cardispatchid = 0;
if (int.TryParse(gv_List.DataKeys[row.RowIndex]["Car_DispatchRide_ID"].ToString(), out cardispatchid) &&
cardispatchid > 0)
{
Car_DispatchRide dispatch = new Car_DispatchRideBLL(cardispatchid).Model;
if (dispatch != null)
{
Cost11 += dispatch.RoadToll; //过路过桥费
Cost12 += dispatch.FuelFee; //油费
Cost13 += dispatch.ParkingFee; //停车费
Cost13 += dispatch.OtherFee; //其他费
}
}
selectedevectionids.Add(id);
}
}
if (Cost1 + Cost2 + Cost3 + Cost4 + Cost5 + Cost11 + Cost12 + Cost13 + Cost21 + Cost22 == 0)
{
MessageBox.Show(this, "对不起，请勾选要报销的差旅行程记录！");
return;
}
#endregion
#region 新增费用核销单头
Org_Staff staff = new Org_StaffBLL((int)Session["UserID"]).Model;
FNA_FeeWriteOffBLL bll = new FNA_FeeWriteOffBLL();
bll.Model.SheetCode = FNA_FeeWriteOffBLL.GenerateSheetCode(staff.OrganizeCity); //自动产生报销单号
bll.Model.AccountMonth = AC_AccountMonthBLL.GetCurrentMonth();
bll.Model.FeeType = ConfigHelper.GetConfigInt("EvectionFeeType"); //差旅费对应的费用类型
bll.Model.OrganizeCity = staff.OrganizeCity;
bll.Model.ApproveFlag = 1;
bll.Model.State = 1;
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model["HasFeeApply"] = "N"; //无申请单
bll.Model["IsEvectionWriteOff"] = "Y"; //是差旅报销
#endregion
#region 新增费用核销单明细
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost1ACTitle"), Cost1, minbegindate, maxenddate); //交通费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost2ACTitle"), Cost2, minbegindate, maxenddate); //住宿费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost3ACTitle"), Cost3, minbegindate, maxenddate); //补贴
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost4ACTitle"), Cost4, minbegindate, maxenddate); //市内交通费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost5ACTitle"), Cost5, minbegindate, maxenddate); //的士费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost11ACTitle"), Cost11, minbegindate, maxenddate); //车辆费用-路桥费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost12ACTitle"), Cost12, minbegindate, maxenddate); //车辆费用-油费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("EvectionCost13ACTitle"), Cost13, minbegindate, maxenddate); //车辆费用-其他杂费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("TrainingCost1ACTitle"), Cost21, minbegindate, maxenddate); //总部培训费-差旅费
AddWriteOffDetail(bll, ConfigHelper.GetConfigInt("TrainingCost2ACTitle"), Cost22, minbegindate, maxenddate); //营业部培训费-差旅费
#endregion
int writeoff = bll.Add();
#region 置差旅行程的关联报销单ID
foreach (int evectionid in selectedevectionids)
{
FNA_EvectionRouteBLL evectionbll = new FNA_EvectionRouteBLL(evectionid);
evectionbll.Model.WriteOffID = writeoff;
evectionbll.Update();
}
#endregion
Response.Redirect("FeeWriteoffDetail.aspx?ID=" + writeoff.ToString());
}
protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
#region 获取最迟的销量日期
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
#endregion
#region 判断有无选择业代
if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0")
{
MessageBox.Show(this, "对不起，请选择责任业代！");
return;
}
#endregion
#region 获取业代负责的零售商及所有产品数据
int staff = int.Parse(select_Staff.SelectValue);
IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=3 AND ClientManager=" + staff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1 AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
MessageBox.Show(this, "对不起，没有当前人直接负责的终端店！");
return;
}
IList <PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code");
#endregion
#region 组织文件路径及文件名
string path = ConfigHelper.GetConfigString("AttachmentPath");
if (path.StartsWith("~"))
{
path = Server.MapPath(path);
}
if (!path.EndsWith("\\"))
{
path = path + "\\";
}
path += "ImportExcelSVM\\Download\\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string filename = "销量导入模板-" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
path += filename;
#endregion
#region 生成Excel文件
object missing = System.Reflection.Missing.Value;
ApplicationClass ExcelApp = null;
try
{
ExcelApp = new ApplicationClass();
ExcelApp.Visible = false;
ExcelApp.DisplayAlerts = false;
Workbook workbook1 = null;
Worksheet worksheet1 = null, worksheet2 = null;
try
{
workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing);
worksheet1.Name = "零售商进货";
worksheet2.Name = "零售商销货";
#region 创建表头
worksheet1.Cells[1, 1] = "零售商ID";
worksheet1.Cells[1, 2] = "零售商编号";
worksheet1.Cells[1, 3] = "零售商名称";
worksheet1.Cells[1, 4] = "零售商分类";
worksheet1.Cells[1, 5] = "归属月份";
worksheet1.get_Range("B2", "B2").ColumnWidth = 15;
worksheet1.get_Range("C2", "C2").ColumnWidth = 20;
worksheet1.get_Range("D2", "E2").ColumnWidth = 10;
worksheet1.get_Range("A1", "A1").RowHeight = 50;
worksheet1.get_Range("A1", "CC1").WrapText = true;
worksheet1.get_Range("A1", "CC1").Font.Bold = true;
worksheet1.get_Range("A1", "CC1000").Font.Size = 9;
worksheet1.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter;
worksheet2.Cells[1, 1] = "零售商ID";
worksheet2.Cells[1, 2] = "零售商编号";
worksheet2.Cells[1, 3] = "零售商名称";
worksheet2.Cells[1, 4] = "零售商分类";
worksheet2.Cells[1, 5] = "归属月份";
worksheet2.Cells[1, 6] = "导购ID";
worksheet2.Cells[1, 7] = "导购姓名";
worksheet2.get_Range("B2", "B2").ColumnWidth = 15;
worksheet2.get_Range("C2", "C2").ColumnWidth = 20;
worksheet2.get_Range("D2", "G2").ColumnWidth = 10;
worksheet2.get_Range("A1", "A1").RowHeight = 50;
worksheet2.get_Range("A1", "CC1").WrapText = true;
worksheet2.get_Range("A1", "CC1").Font.Bold = true;
worksheet2.get_Range("A1", "CC1000").Font.Size = 9;
worksheet2.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter;
int bgcolor1 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
int bgcolor2 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
//将产品写入表头
for (int i = 0; i < productlists.Count; i++)
{
worksheet1.Cells[1, 6 + i] = productlists[i].ShortName;
worksheet2.Cells[1, 8 + i] = productlists[i].ShortName;
#region 品牌设置产品列颜色
if (i > 0)
{
if (productlists[i].Brand == productlists[i - 1].Brand)
{
worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color =
worksheet1.get_Range(worksheet1.Cells[1, 6 + i - 1], worksheet1.Cells[1000, 6 + i - 1]).Interior.Color;
worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color =
worksheet2.get_Range(worksheet2.Cells[1, 8 + i - 1], worksheet2.Cells[1000, 8 + i - 1]).Interior.Color;
}
else
{
if (int.Parse(worksheet1.get_Range(worksheet1.Cells[1, 6 + i - 1], worksheet1.Cells[1000, 6 + i - 1]).Interior.Color.ToString()) == bgcolor1)
{
worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor2;
}
else
{
worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor1;
}
if (int.Parse(worksheet2.get_Range(worksheet2.Cells[1, 8 + i - 1], worksheet2.Cells[1000, 8 + i - 1]).Interior.Color.ToString()) == bgcolor1)
{
worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor2;
}
else
{
worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor1;
}
}
}
else
{
worksheet1.get_Range(worksheet1.Cells[1, 6 + i], worksheet1.Cells[1000, 6 + i]).Interior.Color = bgcolor1;
worksheet2.get_Range(worksheet2.Cells[1, 8 + i], worksheet2.Cells[1000, 8 + i]).Interior.Color = bgcolor1;
}
#endregion
}
#endregion
#region 将零售商信息写入表格内
int sellinrow = 2, selloutrow = 2;
foreach (CM_Client client in clientlists)
{
worksheet1.Cells[sellinrow, 1] = client.ID;
worksheet1.Cells[sellinrow, 2] = client.Code;
worksheet1.Cells[sellinrow, 3] = client.FullName;
worksheet1.Cells[sellinrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name;
worksheet1.Cells[sellinrow, 5] = "'" + month.Name;
//worksheet1.Cells[sellinrow, 5] = day.ToString("yyyy-MM-dd");
sellinrow++;
worksheet2.Cells[selloutrow, 1] = client.ID;
worksheet2.Cells[selloutrow, 2] = client.Code;
worksheet2.Cells[selloutrow, 3] = client.FullName;
worksheet2.Cells[selloutrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name;
worksheet2.Cells[selloutrow, 5] = "'" + month.Name;
//worksheet2.Cells[selloutrow, 5] = day.ToString("yyyy-MM-dd");
IList <PM_Promotor> promotorlists = PM_PromotorBLL.GetModelList("ID IN (SELECT Promotor FROM dbo.PM_PromotorInRetailer WHERE Client = " + client.ID.ToString() + ") AND Dimission=1 AND ApproveFlag=1");
for (int j = 0; j < promotorlists.Count; j++)
{
if (j > 0)
{
worksheet2.Cells[selloutrow, 1] = client.ID;
worksheet2.Cells[selloutrow, 2] = client.Code;
worksheet2.Cells[selloutrow, 3] = client.FullName;
worksheet2.Cells[selloutrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name;
worksheet2.Cells[selloutrow, 5] = "'" + month.Name;
//worksheet2.Cells[selloutrow, 5] = day.ToString("yyyy-MM-dd");
}
worksheet2.Cells[selloutrow, 6] = promotorlists[j].ID;
worksheet2.Cells[selloutrow, 7] = promotorlists[j].Name;
if (j != promotorlists.Count - 1)
{
selloutrow++;
}
}
selloutrow++;
}
#endregion
#region 设置表格格式
//设置行高
worksheet1.get_Range(worksheet1.Cells[2, 1], worksheet1.Cells[sellinrow - 1, 1]).RowHeight = 16;
worksheet2.get_Range(worksheet2.Cells[2, 1], worksheet2.Cells[selloutrow - 1, 1]).RowHeight = 16;
//设置表格单元格格线
worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[sellinrow - 1, 6 + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
worksheet2.get_Range(worksheet2.Cells[1, 1], worksheet2.Cells[selloutrow - 1, 8 + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
#endregion
worksheet1.Activate();
ExcelApp.AlertBeforeOverwriting = false;
workbook1.SaveAs(path, XlFileFormat.xlExcel8, "", "", false, false, XlSaveAsAccessMode.xlNoChange, 1, false, missing, missing, missing);
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
lb_ErrorInfo.Text = error;
MessageBox.Show(this, "系统错误-1!" + err.Message);
}
finally
{
if (workbook1 != null)
{
workbook1.Close(false, missing, missing);
}
if (worksheet1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1);
}
if (worksheet2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2);
}
if (workbook1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1);
}
worksheet1 = null;
worksheet2 = null;
workbook1 = null;
if (File.Exists(path))
{
Downloadfile(path, filename);
}
}
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
lb_ErrorInfo.Text = error;
MessageBox.Show(this, "系统错误-2!" + err.Message);
}
finally
{
if (ExcelApp != null)
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
//GC.WaitForPendingFinalizers();
}
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
select_Staff.SelectValue = Session["UserID"].ToString();
select_Staff.SelectText = Session["UserRealName"].ToString();
ViewState["IsOpponent"] = Request.QueryString["IsOpponent"] != null?int.Parse(Request.QueryString["IsOpponent"]) : 9;
ViewState["ClientType"] = Request.QueryString["ClientType"] != null?int.Parse(Request.QueryString["ClientType"]) : 3;
#region 获取最迟的销量月份
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
lb_MonthTitle.Text = month.Name + "(" + month.BeginDate.ToString("yyyy-MM-dd") + "至" + month.EndDate.ToString("yyyy-MM-dd") + ")";
ViewState["month"] = month;
#endregion
select_Staff_SelectChange(null, null);
ViewState["ProdutctGifts"] = new StringBuilder();
ViewState["Testers"] = new StringBuilder();
ViewState["Gifts"] = new StringBuilder();
if ((int)ViewState["IsOpponent"] == 1)
{
lb_PageTitle.Text = "从Excel导入零售商进货及销货数量";
lb_setp1.Text = "第一步：下载门店进销量模板";
lb_setp2.Text = "第二步：上传门店进销量EXCEL表格";
if ((int)ViewState["ClientType"] == 2)
{
lb_PageTitle.Text = "从Excel导入分销商进货";
lb_setp1.Text = "第一步：下载分销商进货量模板";
lb_setp2.Text = "第二步：上传分销商进货量EXCEL表格";
diproductprompt.Visible = false;
rtgiftprompt.Visible = false;
diproductprompt.Visible = true;
}
else
{
rtgiftprompt.Visible = false;
rtproductprompt.Visible = true;
digiftprompt.Visible = false;
diproductprompt.Visible = false;
}
Header.Attributes["WebPageSubCode"] = "IsOpponent=1";
}
else
{
Header.Attributes["WebPageSubCode"] = "IsOpponent=9";
if ((int)ViewState["ClientType"] == 2)
{
lb_setp1.Text = "第一步：下载分销商赠品进货模板";
lb_setp2.Text = "第二步：上传分销商赠品进货Excel表格";
diproductprompt.Visible = false;
rtproductprompt.Visible = false;
rtgiftprompt.Visible = false;
digiftprompt.Visible = true;
}
}
}
ProdutctGifts = (StringBuilder)ViewState["ProdutctGifts"];
Testers = (StringBuilder)ViewState["Testers"];
Gifts = (StringBuilder)ViewState["Gifts"];
}
/// <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;
}
}
}
private string GetDefaule(string defaulevalue)
{
if (defaulevalue.StartsWith("$") && defaulevalue.EndsWith("$"))
{
switch (defaulevalue)
{
case "$StaffID$":
if (System.Web.HttpContext.Current.Session["AccountType"] == null ||
(int)System.Web.HttpContext.Current.Session["AccountType"] == 1)
{
defaulevalue = System.Web.HttpContext.Current.Session["UserID"].ToString();
}
else
{
defaulevalue = "0";
}
break;
case "$Today$":
defaulevalue = DateTime.Today.ToString("yyyy-MM-dd");
break;
case "$Now$":
defaulevalue = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
break;
case "$ThisMonthFirstDay$":
defaulevalue = DateTime.Today.ToString("yyyy-MM-01");
break;
case "$ThisYearFirstDay$":
defaulevalue = DateTime.Today.ToString("yyyy-01-01");
break;
case "$CurrentAccountMonth$":
defaulevalue = AC_AccountMonthBLL.GetCurrentMonth().ToString();
break;
case "$StaffOrganizeCity$":
{
if (System.Web.HttpContext.Current.Session["AccountType"] == null ||
(int)System.Web.HttpContext.Current.Session["AccountType"] == 1)
{
Org_StaffBLL staff = new Org_StaffBLL((int)System.Web.HttpContext.Current.Session["UserID"]);
if (staff.Model != null)
{
defaulevalue = staff.Model.OrganizeCity.ToString();
}
else
{
defaulevalue = "1";
}
}
else
{
defaulevalue = "1";
}
}
break;
case "$StaffOfficialCity$":
{
if (System.Web.HttpContext.Current.Session["AccountType"] == null ||
(int)System.Web.HttpContext.Current.Session["AccountType"] == 1)
{
Org_StaffBLL staff = new Org_StaffBLL((int)System.Web.HttpContext.Current.Session["UserID"]);
if (staff.Model != null)
{
defaulevalue = staff.Model.OfficialCity.ToString();
}
}
else
{
defaulevalue = "1";
}
}
break;
case "$TDPClient$":
{
Org_StaffBLL staff = new Org_StaffBLL((int)System.Web.HttpContext.Current.Session["UserID"]);
if (System.Web.HttpContext.Current.Session["OwnerType"] != null &&
(int)System.Web.HttpContext.Current.Session["OwnerType"] == 3)
{
defaulevalue = staff.Model["OwnerClient"];
}
else
{
defaulevalue = "0";
}
break;
}
default:
break;
}
}
return(defaulevalue);
}
protected void Page_Load(object sender, EventArgs e)
{
Page.ClientScript.RegisterClientScriptInclude("meizzDate", Page.ResolveClientUrl("~/App_Themes/basic/meizzDate.js"));
if (!IsPostBack)
{
#region 获取参数
ViewState["OrganizeCity"] = Request.QueryString["OrganizeCity"] == null ? 1 : int.Parse(Request.QueryString["OrganizeCity"]);
ViewState["FeeType"] = Request.QueryString["FeeType"] == null ? 1 : int.Parse(Request.QueryString["FeeType"]);
#endregion
BindDropDown();
#region 获取当前会计月的开始及截止日期
int month = AC_AccountMonthBLL.GetCurrentMonth();
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
BeginDate = m.BeginDate;
EndDate = m.EndDate;
#endregion
#region 传递或创建空的费用明细列表
ListTable<FNA_FeeWriteOffDetail> _details;
if (Session["FeeWriteOffDetails"] != null)
{
_details = (ListTable<FNA_FeeWriteOffDetail>)Session["FeeWriteOffDetails"];
int max = 0;
foreach (FNA_FeeWriteOffDetail item in _details.GetListItem())
{
if (item.ID > max) max = item.ID;
}
ViewState["MaxID"] = max;
ViewState["Details"] = _details;
BindGrid();
AddEmptyDetail();
}
else
{
_details = new ListTable<FNA_FeeWriteOffDetail>(new List<FNA_FeeWriteOffDetail>(), "ID");
ViewState["MaxID"] = 0;
ViewState["Details"] = _details;
AddEmptyDetail();
}
#endregion
if ((int)ViewState["FeeType"] == ConfigHelper.GetConfigInt("ManagementCostType"))
{
tb_TeleFee.Visible = true;
tb_MobileFee.Visible = true;
//绑定显示报销电话费与手机费
BindTeleList();
}
}
}
protected void bt_Import_Click(object sender, EventArgs e)
{
string ImportInfo = "";
lb_ErrorInfo.Text = "";
System.Data.DataTable dtSellIn = null, dtSellOut = null;
if (Session["DataTable-SellIn"] != null && Session["DataTable-SellOut"] != null)
{
dtSellIn = (System.Data.DataTable)Session["DataTable-SellIn"];
dtSellOut = (System.Data.DataTable)Session["DataTable-SellOut"];
}
#region 获取允许最迟的销量日期
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
DateTime minday = month.BeginDate;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
#endregion
IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code");
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----批量导入零售商进货销量----</span><br/>";
#region 开始导入零售商进货
foreach (System.Data.DataRow dr in dtSellIn.Rows)
{
#region 验证数据
int clientid = (int)dr["零售商ID"];
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != (string)dr["零售商名称"])
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商ID与零售商名称不匹配！</span><br/>";
continue;
}
if (dr["归属月份"].ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
continue;
}
//DateTime salesdate = (DateTime)dr["进货日期"];
//if (salesdate < minday || salesdate > maxday)
//{
// ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，进货日期必须在" + minday.ToString("yyyy-MM-dd") +
// "至" + maxday.ToString("yyyy-MM-dd") + "之间！</span><br/>";
// continue;
//}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString()
+ " AND Type=2 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' ");
if (svmlists.Count > 0)
{
if (svmlists[0].ApproveFlag == 1)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，该零售商当月的进货已审核，不可再次导入！</span><br/>";
continue;
}
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
else
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = clientid;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.Supplier;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 2;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["SubmitFlag"] = "1";
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model.Remark = "Excel批量导入";
}
#endregion
#region 读取各产品销量
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
foreach (PDT_Product product in productlists)
{
int quantity = (int)dr["[" + product.ShortName + "]"];
if (quantity != 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 2, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.NetPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
}
#endregion
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的进货单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
else
{
//if (details.Count > 0) //没有产品也新增一条空销量头
{
bll.Items = details;
bll.Add();
ImportInfo += "<span style='color: Black'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的进货单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
ImportInfo += "<br/><br/>";
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----批量导入零售商销货销量----</span><br/>";
#region 开始导入零售商销货
foreach (System.Data.DataRow dr in dtSellOut.Rows)
{
#region 验证数据
int clientid = (int)dr["零售商ID"];
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != (string)dr["零售商名称"])
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商ID与零售商名称不匹配！</span><br/>";
continue;
}
if (dr["归属月份"].ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
continue;
}
//DateTime salesdate = (DateTime)dr["销售日期"];
//if (salesdate < minday || salesdate > maxday)
//{
// ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，销售日期必须在" + minday.ToString("yyyy-MM-dd") +
// "至" + maxday.ToString("yyyy-MM-dd") + "之前！</span><br/>";
// continue;
//}
int promotorid = (int)dr["导购ID"];
if (promotorid > 0)
{
if (PM_PromotorInRetailerBLL.GetModelList("Client=" + client.ID.ToString() +
" AND Promotor=" + promotorid.ToString()).Count == 0)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商中没有关联该导购员！</span><br/>";
continue;
}
}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Supplier=" + clientid.ToString()
+ " AND Type=3 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' AND ISNULL(Promotor,0)=" + promotorid.ToString());
if (svmlists.Count > 0)
{
if (svmlists[0].ApproveFlag == 1)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，该零售商当月的销量已审核，不可再次导入！</span><br/>";
continue;
}
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Model["SubmitFlag"] = "1";
bll.Items.Clear();
}
else
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = 0;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 3;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["SubmitFlag"] = "1";
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model.Remark = "Excel批量导入";
}
#endregion
#region 读取各产品销量
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
foreach (PDT_Product product in productlists)
{
int quantity = (int)dr["[" + product.ShortName + "]"];
if (quantity != 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 3, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.StdPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
}
#endregion
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的销量单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
else
{
//if (details.Count > 0) //没有产品也新增一条空销量头
{
bll.Items = details;
bll.Add();
ImportInfo += "<span style='color: Black'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的销量单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
lb_ErrorInfo.Text = ImportInfo;
bt_Import.Enabled = false;
Session["DataTable-SellIn"] = null;
Session["DataTable-SellOut"] = null;
}
protected void bt_Import_Click(object sender, EventArgs e)
{
string ImportInfo = "";
lb_ErrorInfo.Text = "";
System.Data.DataTable dtSellIn = null, dtSellOut = null;
if (Session["DataTable-SellIn"] != null && Session["DataTable-SellOut"] != null)
{
dtSellIn = (System.Data.DataTable)Session["DataTable-SellIn"];
dtSellOut = (System.Data.DataTable)Session["DataTable-SellOut"];
}
#region 获取允许最迟的销量日期
int JXCDelayDays = ConfigHelper.GetConfigInt("JXCDelayDays");
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(DateTime.Now.AddDays(-JXCDelayDays))).Model;
DateTime minday = month.BeginDate;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
#endregion
IList <PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code");
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----批量导入零售商进货销量----</span><br/>";
#region 开始导入零售商进货
foreach (System.Data.DataRow dr in dtSellIn.Rows)
{
#region 验证数据
int clientid = (int)dr["零售商ID"];
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != (string)dr["零售商名称"])
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商ID与零售商名称不匹配！</span><br/>";
continue;
}
if (dr["归属月份"].ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
continue;
}
//DateTime salesdate = (DateTime)dr["进货日期"];
//if (salesdate < minday || salesdate > maxday)
//{
// ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，进货日期必须在" + minday.ToString("yyyy-MM-dd") +
// "至" + maxday.ToString("yyyy-MM-dd") + "之间！</span><br/>";
// continue;
//}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString()
+ " AND Type=2 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' ");
if (svmlists.Count > 0)
{
if (svmlists[0].ApproveFlag == 1)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，该零售商当月的进货已审核，不可再次导入！</span><br/>";
continue;
}
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
else
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = clientid;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.Supplier;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 2;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["SubmitFlag"] = "1";
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model.Remark = "Excel批量导入";
}
#endregion
#region 读取各产品销量
IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>();
foreach (PDT_Product product in productlists)
{
int quantity = (int)dr["[" + product.ShortName + "]"];
if (quantity != 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 2, out factoryprice, out salesprice);
if (factoryprice == 0)
{
factoryprice = product.FactoryPrice;
}
if (salesprice == 0)
{
salesprice = product.NetPrice;
}
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
}
#endregion
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的进货单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
else
{
//if (details.Count > 0) //没有产品也新增一条空销量头
{
bll.Items = details;
bll.Add();
ImportInfo += "<span style='color: Black'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的进货单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
ImportInfo += "<br/><br/>";
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----批量导入零售商销货销量----</span><br/>";
#region 开始导入零售商销货
foreach (System.Data.DataRow dr in dtSellOut.Rows)
{
#region 验证数据
int clientid = (int)dr["零售商ID"];
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != (string)dr["零售商名称"])
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商ID与零售商名称不匹配！</span><br/>";
continue;
}
if (dr["归属月份"].ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
continue;
}
//DateTime salesdate = (DateTime)dr["销售日期"];
//if (salesdate < minday || salesdate > maxday)
//{
// ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，销售日期必须在" + minday.ToString("yyyy-MM-dd") +
// "至" + maxday.ToString("yyyy-MM-dd") + "之前！</span><br/>";
// continue;
//}
int promotorid = (int)dr["导购ID"];
if (promotorid > 0)
{
if (PM_PromotorInRetailerBLL.GetModelList("Client=" + client.ID.ToString() +
" AND Promotor=" + promotorid.ToString()).Count == 0)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，零售商中没有关联该导购员！</span><br/>";
continue;
}
}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList <SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Supplier=" + clientid.ToString()
+ " AND Type=3 AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',1)='N' AND ISNULL(Promotor,0)=" + promotorid.ToString());
if (svmlists.Count > 0)
{
if (svmlists[0].ApproveFlag == 1)
{
ImportInfo += "<span style='color: Red'>序号：" + dr["序号"].ToString() + "，该零售商当月的销量已审核，不可再次导入！</span><br/>";
continue;
}
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Model["SubmitFlag"] = "1";
bll.Items.Clear();
}
else
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = 0;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.ID;
bll.Model.Promotor = promotorid;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 3;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 1; //成品销售
bll.Model["SubmitFlag"] = "1";
bll.Model["IsCXP"] = "N";
bll.Model.InsertStaff = (int)Session["UserID"];
bll.Model.Remark = "Excel批量导入";
}
#endregion
#region 读取各产品销量
IList <SVM_SalesVolume_Detail> details = new List <SVM_SalesVolume_Detail>();
foreach (PDT_Product product in productlists)
{
int quantity = (int)dr["[" + product.ShortName + "]"];
if (quantity != 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 3, out factoryprice, out salesprice);
if (factoryprice == 0)
{
factoryprice = product.FactoryPrice;
}
if (salesprice == 0)
{
salesprice = product.StdPrice;
}
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
}
#endregion
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的销量单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
else
{
//if (details.Count > 0) //没有产品也新增一条空销量头
{
bll.Items = details;
bll.Add();
ImportInfo += "<span style='color: Black'>序号：" + dr["序号"].ToString() + "，零售商：" + client.FullName
+ " 的销量单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
lb_ErrorInfo.Text = ImportInfo;
bt_Import.Enabled = false;
Session["DataTable-SellIn"] = null;
Session["DataTable-SellOut"] = null;
}
private bool SaveGrid()
{
ListTable<FNA_FeeWriteOffDetail> _details = ViewState["Details"] as ListTable<FNA_FeeWriteOffDetail>;
foreach (GridViewRow gr in gv_List.Rows)
{
int id = (int)gv_List.DataKeys[gr.RowIndex]["ID"];
FNA_FeeWriteOffDetail item = _details.GetListItem().FirstOrDefault(p => p.ID == id);
if (item == null)
{
item = new FNA_FeeWriteOffDetail();
item.ID = id;
}
item.AccountTitle = int.Parse(((DropDownList)gr.FindControl("ddl_AccountTitle")).SelectedValue);
//忽略手工直填的电话费及手机费,必须通过专用面板加入报销信息
if (item.AccountTitle == ConfigHelper.GetConfigInt("TeleCostACTitle") ||
item.AccountTitle == ConfigHelper.GetConfigInt("MobileCostACTitle"))
{
if (((TextBox)gr.FindControl("tbx_WriteOffCost")).Enabled) _details.Remove(item);
continue;
}
if (((TextBox)gr.FindControl("tbx_WriteOffCost")).Text.Trim() != "")
item.WriteOffCost = decimal.Parse(((TextBox)gr.FindControl("tbx_WriteOffCost")).Text.Trim());
if (item.AccountTitle > 1 && item.WriteOffCost != 0)
{
#region 获取选择的会计月
if (gr.FindControl("ddl_BeginMonth") != null &&
item.BeginMonth.ToString() != ((DropDownList)gr.FindControl("ddl_BeginMonth")).SelectedValue)
{
item.BeginMonth = int.Parse(((DropDownList)gr.FindControl("ddl_BeginMonth")).SelectedValue);
item.EndMonth = item.BeginMonth;
AC_AccountMonth month = new AC_AccountMonthBLL(item.BeginMonth).Model;
item.BeginDate = month.BeginDate;
item.EndDate = month.EndDate;
}
#endregion
item.Remark = ((TextBox)gr.FindControl("tbx_Remark")).Text;
if (item.Remark == "")
{
MessageBox.Show(this, "备注必填");
return false;
}
_details.Update(item);
}
else
{
_details.Remove(item);
}
}
return true;
}
protected void bt_OK_Click(object sender, EventArgs e)
{
CM_ContractBLL _bll = null;
#region 判断合同编码是否重复
//合同编码的获取
TextBox tbx_ContractCode = pl_detail.FindControl("CM_Contract_ContractCode") == null ? null : (TextBox)pl_detail.FindControl("CM_Contract_ContractCode");
if ((int)ViewState["ContractID"] == 0)
{
_bll = new CM_ContractBLL();
if (tbx_ContractCode != null && tbx_ContractCode.Text != "" && CM_ContractBLL.GetModelList("ContractCode='" + tbx_ContractCode.Text.Trim() + "'").Count > 0)
{
MessageBox.Show(this, "对不起，合同编码" + tbx_ContractCode.Text.Trim() + "数据库已存在。");
return;
}
}
else
{
_bll = new CM_ContractBLL((int)ViewState["ContractID"]);
if (tbx_ContractCode != null && tbx_ContractCode.Text != "" && CM_ContractBLL.GetModelList("ContractCode='" + tbx_ContractCode.Text.Trim() + "' AND ID !=" + _bll.Model.ID.ToString()).Count > 0)
{
MessageBox.Show(this, "对不起，合同编码" + tbx_ContractCode.Text.Trim() + "数据库已存在。");
return;
}
}
#endregion
pl_detail.GetData(_bll.Model);
if (((int)ViewState["Classify"] == 2 || (int)ViewState["Classify"] == 3) && (int)ViewState["ContractID"] == 0)
{
AC_AccountMonth minbegainmonth = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetCurrentMonth() - 1).Model;
if (_bll.Model.BeginDate < minbegainmonth.BeginDate)
{
MessageBox.Show(this, "对不起，新增协议的合作日期最小值为" + minbegainmonth.BeginDate.ToString("yyyy-MM-dd") + "。");
return;
}
}
if ((int)ViewState["Classify"] == 2 || (int)ViewState["Classify"] == 3)
{
IList<CM_Contract> _listcontract = CM_ContractBLL.GetModelList("ID!=" + ViewState["ContractID"].ToString() + " AND State IN(1,2,3,9) AND Classify=" + ViewState["Classify"].ToString() + " AND Client=" + ViewState["ClientID"].ToString() + " Order By EndDate DESC");
if (_listcontract.Count > 0)
{
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(_listcontract[0].EndDate)).Model;
AC_AccountMonth bemonth = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(_listcontract[0].BeginDate)).Model;
if (month == null)
{
MessageBox.Show(this, "对不起，新增协议的合作日期与原有协议重复");
return;
}
if (_bll.Model.BeginDate <= month.EndDate && _bll.Model.EndDate >= bemonth.BeginDate)
{
MessageBox.Show(this, "对不起，新增协议的合作日期与原有协议重复，请重新确认!建议的开始日期为" + new AC_AccountMonthBLL(month.ID + 1).Model.BeginDate.ToString("yyyy-MM-dd"));
return;
}
}
}
//导购工资
#region 导购工资单独判断 2014-05-07 Jace
if ((int)ViewState["Classify"] == 3)
{
//A、若开始日期范围在过去的最新一个会计月且单月管理费用已随工资流程生成，则该导购协议无法进行保存，并由系统提示：“本协议开始日期需重新填写，导购工资已生成过该笔费用”，若开始日期包含过去的最新一个会计月，但工资流程中并未产生该笔费用，则可以进行保存。
int month = AC_AccountMonthBLL.GetMonthByDate(_bll.Model.BeginDate);
int applyedmonth = _bll.CheckPMFeeApplyLastMonth(int.Parse(ViewState["ClientID"].ToString()));
CM_ClientBLL cm = new CM_ClientBLL(int.Parse(ViewState["ClientID"].ToString()));
if (month <= applyedmonth)
{
MessageBox.Show(this, "本协议开始日期需重新填写，导购工资已生成过该笔费用(最大可提前至" + new AC_AccountMonthBLL(applyedmonth + 1).Model.BeginDate + ")!");
return;
}
//C、导购协议合同周期最多不超过2年。
if (_bll.Model.BeginDate.AddYears(2) < _bll.Model.EndDate)
{
MessageBox.Show(this, "导购协议合同周期最多不超过2年!");
return;
}
//B、导购人数录入的数量 不得超过当时申请时门店实际关联的导购人数，但可以少于门店实际人数，最小的基数为1。
//若门店没有关联导购员或业务员，该门店不得添加导购费用协议，系统提示：“门店没有关联导购员或业务员，请关联后再做申请”。
int conpmcount = 0, actpmcount = 0;
int.TryParse(_bll.Model["PromotorCount"],out conpmcount);
actpmcount = PM_PromotorInRetailerBLL.GetModelList("Client=" + ViewState["ClientID"].ToString()).Count(p => (new PM_PromotorBLL(p.Promotor).Model.Dimission == 1 || new PM_PromotorBLL(p.Promotor).Model.EndWorkDate < new AC_AccountMonthBLL(month).Model.BeginDate));
if (actpmcount > 0)
{
if (actpmcount < conpmcount)
{
MessageBox.Show(this, "导购人数录入的数量不得超过当时申请时门店实际关联的导购人数!");
return;
}
}
else
{
MessageBox.Show(this, "门店没有关联导购员或业务员，请关联后再做申请！");
return;
}
//该门店为LKA或NKA门店，但KA系统没有填写，同样不得添加导购费用协议，系统提示：“门店为KA店，KA系统没有填写，需填写后再做申请”。
if (cm.Model["RTChannel"] == "1" || cm.Model["RTChannel"] == "2")
{
if (cm.Model["VestKA"] == "" || cm.Model["VestKA"] == "0")
{
MessageBox.Show(this, "门店为KA店，KA系统没有填写，需填写后再做申请！");
return;
}
}
//门店为非LKA或NKA门店，但KA系统有填写内容，不得添加导购费用协议，系统提示“门店为非KA店，但KA系统错填写内容，请修改后再做申请”。
else if (cm.Model["VestKA"] != "" && cm.Model["VestKA"] != "0")
{
MessageBox.Show(this, "门店为非KA店，但KA系统错填写内容，请修改后再做申请！");
return;
}
#endregion
}
if (ViewState["ClientID"] != null && (int)ViewState["Classify"] == 2)
{
IList<PM_Promotor> pmlist = PM_PromotorBLL.GetModelList("EXISTS (SELECT 1 FROM MCS_Promotor.dbo.PM_PromotorInRetailer WHERE PM_PromotorInRetailer.Promotor=PM_Promotor.ID AND Client=" + ViewState["ClientID"].ToString()
+ ")AND Dimission=2 AND ApproveFlag=1 ORDER BY EndWorkDate DESC");
if (pmlist.Count > 0 )
{
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetMonthByDate(pmlist[0].EndWorkDate)).Model;
if (month!=null&&_bll.Model.BeginDate <= month.EndDate)
{
MessageBox.Show(this, "对不起，该开始日期期间为导购店，请重新确认!建议的开始日期为" + new AC_AccountMonthBLL(month.ID + 1).Model.BeginDate.ToString("yyyy-MM-dd"));
return;
}
}
}
ListTable<CM_ContractDetail> _details = ViewState["Details"] as ListTable<CM_ContractDetail>;
int cycle = GetContractcycle();
#region 判断必填字段
//if (_bll.Model.BeginDate < DateTime.Today)
//{
// MessageBox.Show(this, "对不起，合同起始日期不能小于今天!");
// return;
//}
if (_bll.Model.EndDate <= _bll.Model.BeginDate)
{
MessageBox.Show(this, "对不起，合同起始日期不能大于截止日期!");
return;
}
if (_bll.Model.EndDate != _bll.Model.BeginDate.AddMonths(cycle).AddDays(-1))
{
MessageBox.Show(this, "提醒，合同截止日期与开始日期不足月匹配，请确认输入是否正确！建议的截止日期为" +
_bll.Model.BeginDate.AddMonths(cycle).AddDays(-1).ToString("yyyy-MM-dd"));
return;
}
if (_bll.Model.Classify==1 && (_bll.Model["IsKA"] == "" || _bll.Model["IsKA"] == "0"))
{
MessageBox.Show(this, "对不起请正确选择是否KA费用！");
return;
}
if (_bll.Model.Classify == 2)
{
if (_bll.Model["RebateLevel"] == "0")
{
MessageBox.Show(this, "对不起请正确选择返利标准！");
return;
}
}
if (_bll.Model.Classify == 3)
{
if (_bll.Model["PromotorCostPayMode"] == "0")
{
MessageBox.Show(this, "对不起，请正确选择【导购管理费付款方式】!");
return;
}
#region 增加导购管理费科目明细
CM_ContractDetail item;
int accounttitle = ConfigHelper.GetConfigInt("ContractAccountTitle-PromotorCost");
item = _details.GetListItem().FirstOrDefault(p => p.AccountTitle == accounttitle);
if (item == null)
{
//新增科目
item = new CM_ContractDetail();
item.AccountTitle = accounttitle;
_details.Add(item);
}
else
{
//修改科目
_details.Update(item);
}
item.ApplyLimit = decimal.Parse(_bll.Model["PromotorCost"]) * int.Parse(_bll.Model["PromotorCount"]);
item.Amount = item.ApplyLimit * cycle;
item.FeeCycle = cycle;
item.BearPercent = 100 - decimal.Parse(_bll.Model["PromotorCostRate"]);
item.PayMode = int.Parse(_bll.Model["PromotorCostPayMode"]);
item.Remark = "导购管理费";
#endregion
}
#endregion
if ((int)ViewState["ContractID"] == 0)
{
_bll.Model.State = 1;
_bll.Model.ApproveFlag = 2;
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.Client = int.Parse(ViewState["ClientID"].ToString());
CM_ClientBLL _cm = new CM_ClientBLL(_bll.Model.Client);
//导购店添加返利协议
if (_cm.Model["RTClassify"] == "3" && (int)ViewState["Classify"] == 2)
{
MessageBox.Show(this, _cm.CheckRealClassifyShowMessage(1));
return;
}
_bll.Items = _details.GetListItem();
ViewState["ContractID"] = _bll.Add();
}
else
{
_bll.Model.UpdateTime = DateTime.Now;
_bll.Model.UpdateStaff = (int)Session["UserID"];
_bll.Update();
#region 修改明细
_bll.Items = _details.GetListItem(ItemState.Added);
_bll.AddDetail();
foreach (CM_ContractDetail _deleted in _details.GetListItem(ItemState.Deleted))
{
_bll.DeleteDetail(_deleted.ID);
}
foreach (CM_ContractDetail d in _details.GetListItem())
{
if (d.PayMode == 20)
{
//一次性支付
if (d.ApplyLimit != d.Amount)
{
d.ApplyLimit = d.Amount;
_details.Update(d);
}
}
else
{
//非一次性支付
if (d.ApplyLimit != 0 && d.Amount / d.ApplyLimit != cycle)
{
d.Amount = d.ApplyLimit * cycle;
_details.Update(d);
}
}
}
_bll.Items = _details.GetListItem(ItemState.Modified);
_bll.UpdateDetail();
#endregion
}
if (sender != null)
{
if (Request.QueryString["Decision"] != "" && Request.QueryString["Decision"] == "Y")
MessageBox.Show(this, "合同编码保存成功！");
else
MessageBox.ShowAndRedirect(this, "保存零售商合同详细信息成功！", "RetailerContractDetail.aspx?ContractID=" + ViewState["ContractID"].ToString());
}
else
ViewState["SaveResult"] = true;
}
protected void bt_AddMobileFee_Click(object sender, EventArgs e)
{
int staffid = 0;
int.TryParse(select_MobileStaff.SelectValue, out staffid);
if (staffid == 0) return;
Org_Staff staff = new Org_StaffBLL(staffid).Model;
if (staff == null) return;
if (string.IsNullOrEmpty(staff["ManageInfo11"])) staff["ManageInfo11"] = "0";
decimal applycost = 0;
decimal.TryParse(staff["ManageInfo11"], out applycost);
if (applycost <= 0) return;
decimal writeoffcost = decimal.Parse(tbx_MobileCost.Text);
if (writeoffcost > applycost)
{
MessageBox.Show(this, "对不起，实际报销金额不能超过申请限额" + lb_MobileApplyCost.Text + "!");
tbx_MobileCost.Text = lb_MobileApplyCost.Text;
return;
}
SaveGrid();
ViewState["MaxID"] = (int)ViewState["MaxID"] + 1;
#region 组织手机费核销明细
FNA_FeeWriteOffDetail item = new FNA_FeeWriteOffDetail();
item.ID = (int)ViewState["MaxID"];
item.AccountTitle = ConfigHelper.GetConfigInt("MobileCostACTitle");
item.ApplyCost = applycost;
item.WriteOffCost = writeoffcost;
item["MobileFeeRelateStaff"] = staff.ID.ToString();
#region 获取当前会计月的开始及截止日期
int month = int.Parse(ddl_MobileCostMonth.SelectedValue);
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
item.BeginMonth = m.ID;
item.EndMonth = m.ID;
item.BeginDate = m.BeginDate;
item.EndDate = m.EndDate;
#endregion
item.Remark = "员工:" + staff.RealName + ";";
item.Remark += "手机号码:" + staff["Mobile"] + ";月份:" + ddl_MobileCostMonth.SelectedItem.Text +
";限额:" + lb_MobileApplyCost.Text + ";";
if (tbx_MobileRemark.Text != "")
item.Remark += "说明:" + tbx_MobileRemark.Text;
#endregion
if (FNA_FeeWriteOffBLL.CheckMobileFeeHasWriteOff(item.AccountTitle, item.BeginMonth, staffid))
{
MessageBox.Show(this, "该员工指定月的手机费，已在其他报销单中报销，请勿重复报销!");
}
else
{
ListTable<FNA_FeeWriteOffDetail> _details = ViewState["Details"] as ListTable<FNA_FeeWriteOffDetail>;
if (_details.GetListItem().FirstOrDefault(p =>
p.AccountTitle == item.AccountTitle &&
p.BeginMonth == item.BeginMonth &&
p["MobileFeeRelateStaff"] == item["MobileFeeRelateStaff"]) == null)
{
_details.Add(item); //新增科目
}
else
{
MessageBox.Show(this, "该员工指定月的手机费，已在本报销单中，请勿重复报销!");
}
}
BindGrid();
AddEmptyDetail();
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
int iscxp = int.Parse(ddl_IsCXP.SelectedValue);
if (new Addr_OrganizeCityBLL(organizecity).Model.Level >= 2 &&
month == AC_AccountMonthBLL.GetMonthByDate(DateTime.Today.AddDays(-10)))
{
if ((int)ViewState["ClientType"] == 3)
{
DataTable dtFillDataProgress = Org_StaffBLL.GetFillDataProgress((int)Session["UserID"], true);
DataRow[] rows;
if (iscxp == 1)
{
rows = dtFillDataProgress.Select("ItemCode='013'");
}
else
{
rows = dtFillDataProgress.Select("ItemCode='007'");
}
if (rows.Length > 0)
{
int ItemTargetCount = (int)rows[0]["ItemTargetCount"];
int ItemCompleteCount = (int)rows[0]["ItemCompleteCount"];
if (ItemTargetCount > ItemCompleteCount)
{
MessageBox.Show(this, "对不起，还有" + (ItemTargetCount - ItemCompleteCount).ToString() +
"家门店库存尚未录入,无法提交或审核!具体请查看桌面填报进度表.");
bt_Approve.Enabled = false;
bt_Submit.Enabled = false;
}
}
}
}
if (MCSTabControl1.SelectedIndex == 0)
{
DataTable dt_summary = SVM_InventoryBLL.GetSummaryTotal(organizecity, month, (int)ViewState["ClientType"], level, state, iscxp, (int)Session["UserID"]);
dt_summary = MatrixTable.Matrix(dt_summary, new string[] { "管理片区名称" }, new string[] { "品牌", "段位" }, "金额", true, true);
gv_Summary.DataSource = dt_summary;
gv_Summary.DataBind();
MatrixTable.GridViewMatric(gv_Summary);
if (dt_summary.Columns.Count >= 24)
{
gv_Summary.Width = new Unit(dt_summary.Columns.Count * 60);
}
else
{
gv_Summary.Width = new Unit(100, UnitType.Percentage);
}
}
else
{
string condition = " 1=1 ";
#region 组织查询条件
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
if (CM_ClientBLL.GetModelList("OrganizeCity IN (" + orgcitys + ") AND ActiveFlag=1 AND CM_Client.ClientType=" + ViewState["ClientType"].ToString()).Count != 0)
{
condition += " AND SVM_Inventory.OrganizeCity IN (" + orgcitys + ")";
}
else
{
condition += " AND CM_Client.ClientManager=" + Session["UserID"].ToString();
}
}
condition += " AND MCS_SYS.dbo.UF_Spilt2('MCS_SVM.dbo.SVM_Inventory',SVM_Inventory.ExtPropertys,'IsCXP')=" + ddl_IsCXP.SelectedValue;
//会计月条件
condition += " AND SVM_Inventory.AccountMonth =" + ddl_Month.SelectedValue;
condition += " AND CM_Client.ClientType=" + ViewState["ClientType"].ToString();
switch (ddl_State.SelectedValue)
{
case "1":
condition += " AND SVM_Inventory.ApproveFlag=2 AND MCS_SYS.dbo.UF_Spilt2('MCS_SVM.dbo.SVM_Inventory',SVM_Inventory.ExtPropertys,'SubmitFlag')='2'";
break;
case "2":
condition += " AND SVM_Inventory.ApproveFlag=2 AND MCS_SYS.dbo.UF_Spilt2('MCS_SVM.dbo.SVM_Inventory',SVM_Inventory.ExtPropertys,'SubmitFlag')='1'";
break;
case "3":
condition += " AND SVM_Inventory.ApproveFlag=1";
break;
}
#endregion
gv_List.ConditionString = condition;
gv_List.BindGrid();
}
}
private void BindGrid()
{
IList<AC_AccountMonth> _accountmonthlist = new AC_AccountMonthBLL()._GetModelList("");
if (ViewState["PageIndex"] != null)
{
gv_List.PageIndex = Int32.Parse(ViewState["PageIndex"].ToString());
}
gv_List.DataSource = _accountmonthlist;
gv_List.DataBind();
lb_rowcount.Text =_accountmonthlist.Count.ToString();
tbx_PageGo.Text = (gv_List.PageIndex + 1).ToString();
}
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;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
#region 判断传入参数是否为SheetCode
if (Request.QueryString["SheetCode"] != null)
{
string code = Request.QueryString["SheetCode"];
IList <FNA_FeeApply> list = FNA_FeeApplyBLL.GetModelList("SheetCode='" + code + "'");
if (list.Count > 0)
{
Response.Redirect("FeeApplyDetail3.aspx?ID=" + list[0].ID.ToString());
}
else
{
Response.Redirect("FeeApplyList.aspx");
}
}
#endregion
#region 获取参数
ViewState["ID"] = Request.QueryString["ID"] == null ? 0 : int.Parse(Request.QueryString["ID"]);
ViewState["OrganizeCity"] = Request.QueryString["OrganizeCity"] == null ? 0 : int.Parse(Request.QueryString["OrganizeCity"]);
ViewState["FeeType"] = Request.QueryString["FeeType"] == null ? 0 : int.Parse(Request.QueryString["FeeType"]);
ViewState["AccountTitle2"] = Request.QueryString["AccountTitle2"] == null ? 0 : int.Parse(Request.QueryString["AccountTitle2"]);
ViewState["AccountMonth"] = Request.QueryString["AccountMonth"] == null ? 0 : int.Parse(Request.QueryString["AccountMonth"]);
ViewState["ActivityID"] = Request.QueryString["ActivityID"] == null ? 0 : int.Parse(Request.QueryString["ActivityID"]);
ViewState["Client"] = Request.QueryString["Client"] == null ? 0 : int.Parse(Request.QueryString["Client"]);
ViewState["Brand"] = Request.QueryString["Brand"] == null ? 0 : int.Parse(Request.QueryString["Brand"]);
ViewState["RelateCar"] = Request.QueryString["RelateCar"] == null ? 0 : int.Parse(Request.QueryString["RelateCar"]);
ViewState["GiftFeeClassify"] = Request.QueryString["GiftFeeClassify"] == null ? 0 : int.Parse(Request.QueryString["GiftFeeClassify"]);
ViewState["FromGeneralFlow"] = Request.QueryString["FromGeneralFlow"] == null ? "N" : Request.QueryString["FromGeneralFlow"];
Session["FeeApplyDetail"] = null;
Session["SuccessFlag"] = null;
#endregion
BindDropDown();
#region 创建费用明细的列表
ListTable <FNA_FeeApplyDetail> _details = new ListTable <FNA_FeeApplyDetail>(new FNA_FeeApplyBLL((int)ViewState["ID"]).Items, "ID");
ViewState["Details"] = _details;
int max = 0;
if (_details.GetListItem().Count > 0)
{
_details.GetListItem().Max(p => p.ID);
}
ViewState["MaxID"] = max;
#endregion
if ((int)ViewState["ID"] == 0)
{
if ((int)ViewState["FeeType"] == 0 || (int)ViewState["OrganizeCity"] == 0)
{
Response.Redirect("FeeApplyDetail0.aspx");
return;
}
ViewState["DicFeeType"] = DictionaryBLL.GetDicCollections("FNA_FeeType")[ViewState["FeeType"].ToString()];
if ((int)ViewState["AccountMonth"] == 0)
{
ViewState["AccountMonth"] = AC_AccountMonthBLL.GetCurrentMonth();
}
#region 新费用申请时，初始化申请信息
Label lb_OrganizeCity = (Label)pn_FeeApply.FindControl("FNA_FeeApply_OrganizeCity");
if (lb_OrganizeCity != null)
{
lb_OrganizeCity.Text = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OrganizeCity", (int)ViewState["OrganizeCity"]);
}
Label lb_month = (Label)pn_FeeApply.FindControl("FNA_FeeApply_AccountMonth");
if (lb_month != null)
{
lb_month.Text = new AC_AccountMonthBLL((int)ViewState["AccountMonth"]).Model.Name;
}
Label lb_FeeType = (Label)pn_FeeApply.FindControl("FNA_FeeApply_FeeType");
if (lb_FeeType != null)
{
lb_FeeType.Text = ((Dictionary_Data)ViewState["DicFeeType"]).Name;
}
Label lb_staff = (Label)pn_FeeApply.FindControl("FNA_FeeApply_InsertStaff");
if (lb_staff != null)
{
lb_staff.Text = new Org_StaffBLL((int)Session["UserID"]).Model.RealName;
}
Label lb_InsertTime = (Label)pn_FeeApply.FindControl("FNA_FeeApply_InsertTime");
if (lb_InsertTime != null)
{
lb_InsertTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
}
TextBox tbx_Title = (TextBox)pn_FeeApply.FindControl("FNA_FeeApply_Title");
if (tbx_Title != null && lb_FeeType != null)
{
tbx_Title.Text = lb_month.Text + " " + lb_FeeType.Text + "申请单" + Session["UserRealName"].ToString();
}
if ((int)ViewState["AccountTitle2"] != 0)
{
Label lb_AccountTitle2 = (Label)pn_FeeApply.FindControl("FNA_FeeApply_AccountTitle2");
if (lb_AccountTitle2 != null)
{
lb_AccountTitle2.Text = new AC_AccountTitleBLL((int)ViewState["AccountTitle2"]).Model.Name;
}
}
DropDownList ddl_Brand = (DropDownList)pn_FeeApply.FindControl("FNA_FeeApply_ProductBrand");
if (ddl_Brand != null)
{
ddl_Brand.SelectedValue = ViewState["Brand"].ToString();
}
if ((int)ViewState["RelateCar"] != 0)
{
Label lb_RelateCar = (Label)pn_FeeApply.FindControl("FNA_FeeApply_RelateCar");
if (lb_RelateCar != null)
{
Car_CarList car = new Car_CarListBLL((int)ViewState["RelateCar"]).Model;
if (car != null)
{
lb_RelateCar.Text = car.CarNo;
}
else
{
ViewState["RelateCar"] = 0;
}
}
}
#endregion
if (((Dictionary_Data)ViewState["DicFeeType"]).Description == "BudgetControl")
{
BindBudgetInfo();
}
else if (((Dictionary_Data)ViewState["DicFeeType"]).Description == "FeeRateControl")
{
BindFeeRateInfo();
}
UploadFile1.Visible = false;
bt_Submit.Visible = false;
bt_ViewReport.Visible = false;
bt_Print.Visible = false;
bt_Copy.Visible = false;
bt_ViewWriteOff.Visible = false;
bt_Cancel.Visible = false;
tbl_Remark.Visible = true;
}
else
{
BindData();
}
bt_AddDetail.OnClientClick =
string.Format("PopAddFeeDetail({0},{1},{2},{3},{4},{5});",
ViewState["FeeType"].ToString(),
ViewState["OrganizeCity"].ToString(),
ViewState["AccountMonth"].ToString(),
ViewState["AccountTitle2"].ToString(),
ViewState["Brand"].ToString(),
ViewState["RelateCar"].ToString()
);
}
#region 注册弹出窗口脚本
string script = "function PopAddFeeDetail(feetype,organizecity,month,accounttitle2,brand,car){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("Pop_AddFeeApplyDetailItem.aspx") +
"?FeeType=' + feetype + '&OrganizeCity=' + organizecity + '&AccountMonth=' + month + '&AccountTitle2=' + accounttitle2 + '&Client=" +
ViewState["Client"].ToString() + "&Brand=' + brand + '&RelateCar=' + car + '&FromGeneralFlow=" + ViewState["FromGeneralFlow"].ToString()
+ "&tempid='+tempid, window, 'dialogWidth:800px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopAddFeeDetail", script, true);
script = "function PopReport(id){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("~/SubModule/ReportViewer/PubReportViewerFeeApp.aspx?ViewFramework=false&ReportPath=/MCS_FNA_Report/Report_FNA_ClientInfoByAppID_001&FeeAppID=' + id ") +
", window, 'dialogWidth:800px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopReport", script, true);
script = "function PopWriteOffListByDetailID(detailid){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("../FeeWriteoff/Pop_FeeWriteOffListByFeeApply.aspx?tempid='+tempid+'&FeeApplyDetailID=' + detailid ") +
", window, 'dialogWidth:800px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopWriteOffListByDetailID", script, true);
script = "function PopWriteOffListByApplyID(applyid){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("../FeeWriteoff/Pop_FeeWriteOffListByFeeApply.aspx?tempid='+tempid+'&FeeApplyID=' + applyid ") +
", window, 'dialogWidth:800px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopWriteOffListByApplyID", script, true);
script = "function PopAdjust(id){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("Pop_AdjustApplyDetail.aspx") +
"?ID=' + id + '&tempid='+tempid, window, 'dialogWidth:500px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopAdjust", script, true);
script = "function PopClientFNAInfo(clientid){\r\n";
script += "var tempid = Math.random() * 10000; \r\n window.showModalDialog('" + Page.ResolveClientUrl("../FeeApplyOrWriteoffByClientList.aspx") +
"?ClientID=' + clientid + '&tempid='+tempid, window, 'dialogWidth:900px;DialogHeight=600px;status:yes;resizable=yes');}";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "PopClientFNAInfo", script, true);
#endregion
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
int flag = int.Parse(ddl_Flag.SelectedValue);
#region 判断是否可以审批通过
string[] allowdays = Addr_OrganizeCityParamBLL.GetValueByType(1, 7).Replace(" ", "").Split(new char[] { ',', '，', ';', '；' });
string date = DateTime.Now.Day.ToString();
if (allowdays.Contains(date))
{
bt_Approve.Enabled = false;
bt_Approve.ToolTip = "每月21-25号不可对进销存审批通过！";
}
#endregion
if (new Addr_OrganizeCityBLL(organizecity).Model.Level >= 2
&& month == AC_AccountMonthBLL.GetMonthByDate(DateTime.Today.AddDays(-10)))
{
if ((int)ViewState["ClientType"] == 3 && (int)ViewState["Type"] == 2)
{
DataTable dtFillDataProgress = Org_StaffBLL.GetFillDataProgress((int)Session["UserID"], true);
DataRow[] rows;
if (flag == 1)
rows = dtFillDataProgress.Select("ItemCode='006'");
else
rows = dtFillDataProgress.Select("ItemCode='012'");
if (rows.Length > 0)
{
int ItemTargetCount = (int)rows[0]["ItemTargetCount"];
int ItemCompleteCount = (int)rows[0]["ItemCompleteCount"];
if (ItemTargetCount > ItemCompleteCount)
{
MessageBox.Show(this, "对不起，还有" + (ItemTargetCount - ItemCompleteCount).ToString() +
"家门店销量尚未录入,无法提交或审核!具体请查看桌面填报进度表.");
bt_Approve.Enabled = false;
bt_Submit.Enabled = false;
}
}
}
}
if (MCSTabControl1.SelectedIndex == 0)
{
DataTable dt_summary = SVM_SalesVolumeBLL.GetSummaryTotal(organizecity, month, (int)ViewState["ClientType"], flag, level, state, (int)ViewState["Type"], (int)Session["UserID"]);
dt_summary = MatrixTable.Matrix(dt_summary, new string[] { "管理片区名称" }, new string[] { "品牌", "段位" }, "金额", true, true);
gv_Summary.DataSource = dt_summary;
gv_Summary.DataBind();
MatrixTable.GridViewMatric(gv_Summary);
if (dt_summary.Columns.Count >= 24)
gv_Summary.Width = new Unit(dt_summary.Columns.Count * 60);
else
gv_Summary.Width = new Unit(100, UnitType.Percentage);
}
else if (MCSTabControl1.SelectedIndex == 1)
{
if (organizecity == 1 || new Addr_OrganizeCityBLL(organizecity).Model.Level < 2)
{
MessageBox.Show(this, "按客户及SKU查询时，不能按总部及大区级别查询!");
return;
}
DataTable dt_summary = SVM_SalesVolumeBLL.GetSummaryTotal2(organizecity, month, (int)ViewState["ClientType"], flag, state, (int)ViewState["Type"], (int)Session["UserID"]);
dt_summary = MatrixTable.Matrix(dt_summary, new string[] { "管理片区名称", "客户名称", "责任业代" }, new string[] { "品牌", "产品名称" }, "数量", true, true);
gv_Summary.DataSource = dt_summary;
gv_Summary.DataBind();
MatrixTable.GridViewMatric(gv_Summary);
if (dt_summary.Columns.Count >= 24)
gv_Summary.Width = new Unit(dt_summary.Columns.Count * 60);
else
gv_Summary.Width = new Unit(100, UnitType.Percentage);
}
else
{
string condition = " SVM_SalesVolume.AccountMonth=" + ddl_Month.SelectedValue;
IList<CM_Client> cmlist = new List<CM_Client>();
if (organizecity > 1)
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(organizecity);
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "") orgcitys += ",";
orgcitys += tr_OrganizeCity.SelectValue;
cmlist = CM_ClientBLL.GetModelList("OrganizeCity IN (" + orgcitys + ") AND ActiveFlag=1 AND CM_Client.ClientType=" + ViewState["ClientType"].ToString());
if (CM_ClientBLL.GetModelList("OrganizeCity IN (" + orgcitys + ") AND ActiveFlag=1 AND CM_Client.ClientType=" + ViewState["ClientType"].ToString()).Count != 0)
{
condition += " AND SVM_SalesVolume.OrganizeCity IN (" + orgcitys + ")";
}
}
condition += ddl_Flag.SelectedValue == "1" ? " AND SVM_SalesVolume.Flag<6" : " AND SVM_SalesVolume.Flag>6";
AC_AccountMonthBLL _monthbll = new AC_AccountMonthBLL(month);
if (ViewState["ClientType"] != null)
{
switch ((int)ViewState["ClientType"])
{
case 2:
if ((int)ViewState["Type"] == 1)
{
condition += " AND SVM_SalesVolume.Client IN( Select ID FROM MCS_CM.dbo.CM_Client Where ClientType=2 AND MCS_SYS.dbo.UF_Spilt2('MCS_CM.dbo.CM_Client',CM_Client.ExtPropertys,'DIClassify')='2'";
}
else
{
condition += " AND SVM_SalesVolume.Supplier IN( Select ID FROM MCS_CM.dbo.CM_Client Where ClientType=2";
}
break;
case 3:
condition += (int)ViewState["Type"] == 1 ? " AND SVM_SalesVolume.Client IN" : " AND SVM_SalesVolume.Supplier IN";
condition += "( Select ID FROM MCS_CM.dbo.CM_Client Where ClientType=3 ";
break;
}
if (cmlist.Count == 0)
{
condition += " AND CM_Client.ClientManager=" + Session["UserID"].ToString();
}
condition += " AND ApproveFlag=1 AND OpenTime<='" + _monthbll.Model.EndDate + "'AND ISNULL(CloseTime,GETDATE())>='" + _monthbll.Model.BeginDate + "')";
}
switch (ddl_State.SelectedValue)
{
case "1":
condition += "AND SVM_SalesVolume.ApproveFlag=2 AND MCS_SYS.dbo.UF_Spilt2('MCS_SVM.dbo.SVM_SalesVolume',SVM_SalesVolume.ExtPropertys,'SubmitFlag')='2'";
break;
case "2":
condition += "AND SVM_SalesVolume.ApproveFlag=2 AND MCS_SYS.dbo.UF_Spilt2('MCS_SVM.dbo.SVM_SalesVolume',SVM_SalesVolume.ExtPropertys,'SubmitFlag')='1'";
break;
case "3":
condition += " AND SVM_SalesVolume.ApproveFlag=1";
break;
}
gv_List.ConditionString = condition;
gv_List.BindGrid();
}
}
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;
}
}