protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
{
#region 获取最迟的销量日期
AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
#endregion
#region 判断有无选择业代
if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0")
{
MessageBox.Show(this, "对不起，请选择责任业代！");
return;
}
#endregion
#region 获取业代负责的零售商及所有产品数据
int staff = int.Parse(select_Staff.SelectValue);
string condtion = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1 " + condtion + "AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
MessageBox.Show(this, "对不起，没有当前人直接负责的终端店！");
return;
}
#endregion
string title = (int)ViewState["ClientType"] == 2 ? "分销商" : "零售商";
string filename = title + ((int)ViewState["IsOpponent"] == 9 ? "赠品进货导入模板-" : "产品进销货量导入模板") + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
if ((int)ViewState["ClientType"] == 2 && (int)ViewState["IsOpponent"] == 1)
{
filename = "分销商产品进货导入模板" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
}
SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
_bll.Model.Name = filename;
_bll.Model.AccountMonth = month.ID;
_bll.Model.State = 1;
_bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
_bll.Model.InsertStaff = (int)Session["UserID"];
_bll.Model.InsertTime = DateTime.Now;
_bll.Model.DownStaff = staff;
if ((int)ViewState["IsOpponent"] == 9)
{
_bll.Model.ProductGifts = ProdutctGifts.ToString().EndsWith(",") ? ProdutctGifts.ToString().Substring(0, ProdutctGifts.Length - 1) : ProdutctGifts.ToString();
_bll.Model.Testers = Testers.ToString().EndsWith(",") ? Testers.ToString().Substring(0, Testers.Length - 1) : Testers.ToString();
_bll.Model.Gifts = Gifts.ToString().EndsWith(",") ? Gifts.ToString().Substring(0, Gifts.Length - 1) : Gifts.ToString();
}
_bll.Model["UserName"] = Session["UserName"].ToString();
_bll.Model["ClientType"] = ViewState["ClientType"].ToString();
_bll.Add();
//StringBuilder builder = new StringBuilder();
//builder.Append("alert('请到该页面下载模版!');");
//builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString()+"&ClientType=" + ViewState["ClientType"].ToString()+"','_blank')");
//MessageBox.ResponseScript(this, builder.ToString());
Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&ClientType=" + ViewState["ClientType"].ToString());
}
protected string PromotorInClient(string RetailerS)
{
if (RetailerS.Equals(""))
{
return("");
}
string clientname = "";
IList <CM_Client> lists = CM_ClientBLL.GetModelList("ID IN (" + RetailerS + ")");
int count = 0;
foreach (CM_Client c in lists)
{
if (count < 2)
{
clientname += "<a href='../CM/RT/RetailerDetail.aspx?ClientID=" + c.ID.ToString() + "' target='_blank' class='listViewTdLinkS1'>"
+ c.FullName + "</a><br/>";
}
else
{
break;
}
count++;
}
if (count > 1)
{
clientname += "共" + lists.Count.ToString() + "个门名";
}
return(clientname);
}
private string GetRleatClient(int id, bool ischangecity)
{
string result = "";
CM_Client client = new CM_ClientBLL(id).Model;
if (ischangecity)
{
tr_OrganizeCity.SelectValue = client.OrganizeCity.ToString();
}
if (client != null && client.ClientType == 2 && client["DIClassify"] == "3")
{
CM_Client supplier = new CM_ClientBLL(client.Supplier).Model;
if (supplier != null && supplier.ClientType == 2 && supplier["DIClassify"] == "1")
{
result = "关联主户头：" + supplier.FullName;
}
}
if (client != null && client.ClientType == 2 && client["DIClassify"] == "1")
{
IList <CM_Client> _listsubclient = CM_ClientBLL.GetModelList(@"Supplier=" + client.ID.ToString() + " AND ClientType=2 AND ActiveFlag=1 AND ApproveFlag=1 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='3'");
result = _listsubclient.Count > 0 ? "关联子户头：" : "";
foreach (CM_Client m in _listsubclient)
{
result += m.FullName + "<br/>                        ";
}
}
return(result);
}
protected void gv_PromotorSalary_DataBound(object sender, EventArgs e)
{
if (gv_PromotorSalary.HeaderRow != null)
{
IList <CM_Client> lists;
string clientname = "";
string RetailerS = "";
gv_PromotorSalary.HeaderRow.Cells[0].Visible = false;
int taskindex = 0;
for (int i = 0; i < gv_PromotorSalary.HeaderRow.Cells.Count; i++)
{
if (gv_PromotorSalary.HeaderRow.Cells[i].Text == "审批工作流")
{
taskindex = i;
}
}
foreach (GridViewRow r in gv_PromotorSalary.Rows)
{
clientname = "";
lists = new List <CM_Client>();
RetailerS = "";
r.Cells[0].Visible = false;
RetailerS = new PM_SalaryBLL().GetDetailModel(int.Parse(gv_PromotorSalary.DataKeys[r.RowIndex].Value.ToString()))["RetailerS"];
if (RetailerS.Equals(""))
{
r.Cells[5].Text = clientname;
continue;
}
lists = CM_ClientBLL.GetModelList("ID IN (" + RetailerS + ")");
int count = 0;
foreach (CM_Client c in lists)
{
if (count < 2)
{
clientname += "<a href='../CM/RT/RetailerDetail.aspx?ClientID=" + c.ID.ToString() + "' target='_blank' class='listViewTdLinkS1'>"
+ c.FullName + "</a><br/>";
}
else
{
break;
}
count++;
}
if (count > 1)
{
clientname += "共" + lists.Count.ToString() + "个门名";
}
r.Cells[6].Text = clientname;
if (taskindex > 0)
{
r.Cells[r.Cells.Count - 1].Text = "<a href='../EWF/TaskDetail.aspx?TaskID=" + r.Cells[taskindex].Text + "' target='_blank' class='listViewTdLinkS1'>"
+ r.Cells[taskindex].Text + "</a>";
}
}
}
}
public void BindDropDown()
{
PM_Promotor p = new PM_PromotorBLL((int)ViewState["PromotorID"]).Model;
ddl_CM.DataTextField = "FullName";
ddl_CM.DataValueField = "ID";
ddl_CM.DataSource = CM_ClientBLL.GetModelList(" OrganizeCity in (" + p.OrganizeCity + ")" + @" AND ClientType=3 AND ActiveFlag=1 AND ApproveFlag=1 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',5)='3'");
ddl_CM.DataBind();
ddl_CM.Items.Insert(0, new ListItem("请选择", "0"));
}
private void BindGrid()
{
int month = 0, organizecity = 0, staff = 0;
if (int.TryParse(ddl_AccountMonth.SelectedValue, out month) && int.TryParse(tr_OrganizeCity.SelectValue, out organizecity))
{
string condition = "AccountMonth = " + month.ToString() + " AND TrackDate IS NULL";
int.TryParse(select_Staff.SelectValue, out staff);
if (staff > 0)
{
string client = string.Empty;
IList <CM_Client> list = CM_ClientBLL.GetModelList(" OrganizeCity =" + organizecity + " AND ClientType = 3 AND ActiveFlag = 1 AND ApproveFlag = 1 AND [MCS_SYS].[dbo].[UF_Spilt](ExtPropertys,'|',27) = '1' AND ClientManager = " + staff.ToString());
if (list != null && list.Count > 0)
{
foreach (CM_Client item in list)
{
client += item.ID.ToString() + ",";
}
client = client.Substring(0, client.Length - 1);
}
if (!string.IsNullOrEmpty(client))
{
condition += " AND (Staff = " + staff.ToString() + " OR Client in(" + client + "))";
}
else
{
condition += " AND Staff = " + staff.ToString();
}
}
if (organizecity > 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 OrganizeCity IN (" + orgcitys + ")";
}
if (ddl_IsSubmit.SelectedValue != "0")
{
condition += " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',5)='" + ddl_IsSubmit.SelectedItem.Text + "'";
}
gv_List.BindGrid(TC_TrackCardBLL.GetModelList(condition));
cbx_CheckAll.Checked = false;
}
}
/// <summary>
/// 同步进销存
/// </summary>
/// <param name="begindate"></param>
/// <param name="endtime"></param>
/// <returns></returns>
private void SyncThread()
{
DateTime begindate = _dtBeginDate, enddate = _dtEndDate;
SendMessage(0, "--------开始同步ERP发货单----------");
try
{
SyncProduct();
if (string.IsNullOrEmpty(_sClientCode))
{
if (begindate.Date == enddate.Date)
{
SyncSellOut(begindate, enddate, "");
}
else
{
IList <CM_Client> lists = CM_ClientBLL.GetModelList("ClientType=2 AND ActiveFlag=1 AND ApproveFlag=1 AND MCS_SYS.dbo.uf_Spilt2('MCS_CM.dbo.CM_Client',ExtPropertys,'DIClassify') IN ('1','3')");
foreach (CM_Client c in lists)
{
if (_bCancelSync)
{
SendMessage(0, "同步进行中时,被用户取消！");
break;
}
if (!string.IsNullOrEmpty(c.Code))
{
SyncSellOut(begindate, enddate, c.Code);
}
}
}
}
else
{
SyncSellOut(begindate, enddate, _sClientCode.Trim());
}
}
catch (Exception err)
{
SendMessage(0, err.StackTrace + err.Source + err.Message);
}
SyncComplete();
}
private bool Check_CMCode()
{
string code = ((TextBox)pl_detail.FindControl("CM_Client_Code")).Text;
if (ViewState["ClientID"] != null && code != "")
{
if (CM_ClientBLL.GetModelList(" Code = '" + code + "' and ID<>" + ViewState["ClientID"].ToString()).Count > 0)
{
return(false);
}
}
else
{
if (CM_ClientBLL.GetModelList(" Code = '" + code + "'").Count > 0)
{
return(false);
}
}
return(true);
}
protected void bt_RevocationApply_Click(object sender, EventArgs e)
{
#region 判断有无下游客户
if (CM_ClientBLL.GetModelList("Supplier=" + ViewState["ClientID"].ToString() + " AND ActiveFlag=1").Count > 0)
{
MessageBox.Show(this, "对不起，该经销商下还有活跃的下游分销商或门店,无法申请撤销!");
return;
}
#endregion
if (new FNA_FeeApplyBLL().GetDetail(@" Flag IN(1,2,4) AND AvailCost>0 AND EXISTS
(SELECT ID FROM MCS_FNA.dbo.FNA_FeeApply WHERE State=3 AND ApproveFlag=1 AND ID=FNA_FeeApplyDetail.ApplyID
AND Client=" + ViewState["ClientID"] + ")").Count > 0)
{
MessageBox.Show(this, "该经销商下还有费用未做核销，请核销后再撤销！");
return;
}
CM_ClientBLL bll = new CM_ClientBLL((int)ViewState["ClientID"]);
NameValueCollection dataobjects = new NameValueCollection();
dataobjects.Add("ID", ViewState["ClientID"].ToString());
dataobjects.Add("OrganizeCity", bll.Model.OrganizeCity.ToString());
dataobjects.Add("ClientName", bll.Model.FullName.ToString());
dataobjects.Add("CloseTime", DateTime.Now.ToShortDateString());
dataobjects.Add("Remark", "经销商终止合作");
dataobjects.Add("OperateClassify", bll.Model["OperateClassify"]);
dataobjects.Add("DIClassify", bll.Model["DIClassify"]);
string Title = TreeTableBLL.GetSuperNameByLevel("MCS_SYS.dbo.Addr_OrganizeCity", "ID", "Name", "SuperID", bll.Model.OrganizeCity, ConfigHelper.GetConfigInt("OrganizeCity-CityLevel")) + "-" + "经销商中止合作流程";//办事处+经分销商名称+中止流程
int TaskID = EWF_TaskBLL.NewTask("Revocation_Distributor", (int)Session["UserID"], Title, "~/SubModule/CM/DI/DistributorDetail.aspx?ClientID=" + ViewState["ClientID"].ToString(), dataobjects);
if (TaskID > 0)
{
bll.Model["TaskID"] = TaskID.ToString();
bll.Model["State"] = "2";
bll.Update();
}
Response.Redirect("~/SubModule/EWF/Apply.aspx?TaskID=" + TaskID.ToString());
}
//为控件ddl_RMSAccountEnabled赋值
//protected void ddl_RMSAccountEnabled_Select()
//{
// DropDownList ddl_IsRMSClient = pl_detail.FindControl("CM_Client_IsRMSClient") as DropDownList;
// DropDownList ddl_RMSAccountEnabled = pl_detail.FindControl("CM_Client_RMSAccountEnabled") as DropDownList;
// TextBox tb_RMSCloseDate = pl_detail.FindControl("CM_Client_RMSCloseDate") as TextBox;
// //请选择和已关闭状态下，积分状态启用否选项都为“否”
// if (ddl_IsRMSClient.SelectedValue == "3")//准备启动
// {
// ddl_RMSAccountEnabled.SelectedValue = "1";
// }
// else if (ddl_IsRMSClient.SelectedValue == "2")
// {
// ddl_RMSAccountEnabled.SelectedValue = "2";
// }
//}
//protected void ddl_IsRMSClient_SelectedIndexChanged(object sender, EventArgs e)
//{
// this.ddl_RMSAccountEnabled_Select();
//}
private void tbx_FullName_TextChanged(object sender, EventArgs e)
{
Org_Staff staff = new Org_StaffBLL((int)Session["UserID"]).Model;
string orgcitys = "";
string ConditionStr = "FullName like '%" + ((TextBox)sender).Text.Trim() + "%'";
string clients = "";
#region 判断当前可查询的范围
if (staff.OrganizeCity != 1)
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(staff.OrganizeCity, true);
orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += staff.OrganizeCity;
if (orgcitys != "")
{
ConditionStr += " AND CM_Client.OrganizeCity IN (" + orgcitys + ")";
}
}
#endregion
if (ViewState["ClientID"] != null)
{
ConditionStr = ConditionStr + " AND ID!=" + ViewState["ClientID"].ToString();
}
IList <CM_Client> clientList = CM_ClientBLL.GetModelList(ConditionStr);
foreach (CM_Client client in clientList)
{
clients += client.FullName + "、";
}
if (!string.IsNullOrEmpty(clients))
{
MessageBox.Show(this, "该门店名称与已有【" + clients.Substring(0, clients.Length - 1) + "】出现相同或相似，请确认是有重复！");
}
}
protected void select_Staff_SelectChange(object sender, MCSControls.MCSWebControls.SelectChangeEventArgs e)
{
ProdutctGifts = new StringBuilder();
Testers = new StringBuilder();
Gifts = new StringBuilder();
#region 获取最迟的销量日期
AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
#endregion
#region 判断有无选择业代
if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0")
{
MessageBox.Show(this, "对不起，请选择责任业代！");
return;
}
#endregion
#region 获取业代负责的零售商及所有产品数据
int staff = int.Parse(select_Staff.SelectValue);
string condtion = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.ToString() +
" AND ActiveFlag=1 AND ApproveFlag=1 " + condtion + "AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code");
if (clientlists.Count == 0)
{
if (sender != null)
{
MessageBox.Show(this, "对不起，没有当前人直接负责的客户！");
}
return;
}
#endregion
if ((int)ViewState["IsOpponent"] == 9)
{
BindProductGrid();
}
}
protected void bt_OK_Click(object sender, EventArgs e)
{
int ApproveCount = 0, UnApproveCount = 0, errcount = 0, LoseApproveCount = 0;
string[] mixed;
if (txt_OpenID.Text.Trim() != "")
{
string[] OpenID = Regex.Split(txt_OpenID.Text.Trim().Replace("\r\n", "\n").Replace(" ", "\n"), "\n", RegexOptions.IgnoreCase);
if (txt_CloseID.Text.Trim() != "")
{
string[] CloseID = Regex.Split(txt_CloseID.Text.Trim().Replace("\r\n", "\n").Replace(" ", "\n"), "\n", RegexOptions.IgnoreCase);
if (!CheckID(OpenID, CloseID, out mixed))
{
MessageBox.Show(this, "客户代码为【" + mixed[0] + "】的客户代码同时出现在了【开通】与【关闭】编辑框中！");
return;
}
}
}
if (txt_OpenID.Text.Trim() != "")
{
string[] OpenID = Regex.Split(txt_OpenID.Text.Trim().Replace("\r\n", "\n").Replace(" ", "\n"), "\n", RegexOptions.IgnoreCase);
CM_ClientBLL _bll;
StringBuilder approvebuild = new StringBuilder("");
for (int i = 0; i < OpenID.Length; i++)
{
if (!OpenID[i].Trim().Equals(""))
{
IList <CM_Client> cmlist = CM_ClientBLL.GetModelList("Code='" + OpenID[i].Trim() + "'");
if (cmlist.Count > 0)
{
_bll = new CM_ClientBLL(cmlist[0].ID);
_bll.Model["IsRMSClient"] = "3";
_bll.Model["RMSAccountEnabled"] = "1";
_bll.Update();
approvebuild.Append("客户代码为【" + OpenID[i].Trim() + "】的门店已开通积分");
ApproveCount++;
}
else
{
approvebuild.Append("<span style='color: Red'>客户代码为【" + OpenID[i].Trim() + "】的门店未能在系统中找到 </span> ");
errcount++;
}
}
else if (!OpenID[i].Trim().Equals(""))
{
approvebuild.Append("<span style='color: Red'>【" + OpenID[i].Trim() + "】不是一个有效的客户代码</span> ");
errcount++;
}
if (i % 5 == 0 && i != 0)
{
approvebuild.Append("<br/>");
}
}
lb_OpenErrorInfo.Text = approvebuild.ToString();
}
if (txt_CloseID.Text.Trim() != "")
{
string[] CloseID = Regex.Split(txt_CloseID.Text.Trim().Replace("\r\n", "\n").Replace(" ", "\n"), "\n", RegexOptions.IgnoreCase);
CM_ClientBLL _bll;
StringBuilder unapprovebuild = new StringBuilder("");
for (int i = 0; i < CloseID.Length; i++)
{
if (!CloseID[i].Trim().Equals(""))
{
IList <CM_Client> cmlist = CM_ClientBLL.GetModelList("Code='" + CloseID[i].Trim() + "'");
if (cmlist.Count > 0)
{
_bll = new CM_ClientBLL(cmlist[0].ID);
_bll.Model["IsRMSClient"] = "2";
_bll.Model["RMSAccountEnabled"] = "2";
_bll.Update();
unapprovebuild.Append("客户代码为【" + CloseID[i].Trim() + "】的门店已关闭积分");
UnApproveCount++;
}
else
{
errcount++;
unapprovebuild.Append("<span style='color: Red'>ID为【" + CloseID[i].Trim() + "】的返利协议未能在系统中找到 </span>");
}
}
else if (!CloseID[i].Trim().Equals(""))
{
unapprovebuild.Append("<span style='color: Red'>【" + CloseID[i].Trim() + "】不是一个有效的客户代码 </span>");
errcount++;
}
if (i % 5 == 0 && i != 0)
{
unapprovebuild.Append("<br/>");
}
}
lb_CloseErrorInfo.Text = unapprovebuild.ToString();
}
MessageBox.Show(this, "开通积分门店个数：" + ApproveCount.ToString() + @"\n关闭积分门店个数：" + UnApproveCount.ToString() +
@"\n未能导入门店个数：" + errcount.ToString());
return;
}
protected void bt_Refresh_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(Select_Client_Begin.SelectValue) || string.IsNullOrEmpty(Select_Client_End.SelectValue))
{
MessageBox.Show(this, "请选择经销商范围");
return;
}
int beginClinetID = int.Parse(Select_Client_Begin.SelectValue);
int endClinetID = int.Parse(Select_Client_End.SelectValue);
DateTime dateBegin = DateTime.ParseExact(ddl_AccountMonth.SelectedItem.Text, "yyyy-MM", null);
DateTime dateEnd = DateTime.ParseExact(ddl_AccountMonthEnd.SelectedItem.Text, "yyyy-MM", null);
if (dateBegin.CompareTo(dateEnd) > 0)
{
DateTime dateTemp = dateEnd;
dateEnd = dateBegin;
dateBegin = dateTemp;
}
StringBuilder organizeCityStr = new StringBuilder();
#region 绑定用户可管辖的管理片区
if ((int)Session["AccountType"] == 1)//账户类型 1：员工，2：商业客户 3:导购
{
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
DataTable dt = staff.GetStaffOrganizeCity();
foreach (DataRow row in dt.Rows)
{
organizeCityStr.Append(row["ID"].ToString() + ",");
}
//获取当前员工的关联经销商
int _relateclient = 0;
if (staff.Model["RelateClient"] != "" && int.TryParse(staff.Model["RelateClient"], out _relateclient))
{
organizeCityStr.Append(_relateclient.ToString() + ",");
}
}
else if ((int)Session["AccountType"] == 2)
{
CM_Client client = new CM_ClientBLL((int)Session["UserID"]).Model;
if (client != null)
{
Addr_OrganizeCityBLL citybll = new Addr_OrganizeCityBLL(client.OrganizeCity);
DataTable dt = citybll.GetAllChildNodeIncludeSelf();
foreach (DataRow row in dt.Rows)
{
organizeCityStr.Append(row["ID"].ToString() + ",");
}
}
}
if (organizeCityStr.Length > 0)//移除最后一处的逗号
{
organizeCityStr = organizeCityStr.Remove(organizeCityStr.Length - 1, 1);
}
#endregion
string beginClinetCode = new CM_ClientBLL(beginClinetID).Model.Code;
string endClinetCode = new CM_ClientBLL(endClinetID).Model.Code;
string connStr = " ApproveFlag=1 AND ActiveFlag=1 AND ClientType=2 AND Code BETWEEN '" + beginClinetCode + "' AND '" + endClinetCode + "' AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)!='2'";
if (!organizeCityStr.ToString().Contains("1,"))
{
connStr += " AND OrganizeCity IN(" + organizeCityStr + ") ";
}
IList <CM_Client> clientList = CM_ClientBLL.GetModelList(connStr);
string folderPath = GetPdfFolder();
foreach (var client in clientList)
{
this.CreateClientPDF(folderPath, "[" + client.Code + "]" + client.FullName + ".pdf", client, dateBegin, dateEnd);
}
string zipPath = folderPath.Remove(folderPath.LastIndexOf("\\")) + ".zip";
ZipDir(folderPath, zipPath, 9);
DownLoadFile(zipPath);
//删除临时文件夹及文件
File.Delete(zipPath);
Directory.Delete(folderPath, true);
}
/// <summary>
/// 同步经销商进货单
/// </summary>
/// <param name="begindate"></param>
/// <param name="enddate"></param>
/// <param name="clientcode"></param>
private void SyncSellOut(DateTime begindate, DateTime enddate, string clientcode)
{
try
{
int success = 0;
//从进销存数据库中读取出货数据
DataTable dtShipHeader = null;
if (string.IsNullOrEmpty(clientcode))
{
dtShipHeader = ERPIF.GetShipHeader(begindate, enddate);
}
else
{
SendMessage(0, "经销商代码:" + clientcode);
dtShipHeader = ERPIF.GetShipHeader(begindate, enddate, clientcode);
}
SendMessage(0, "本次共获取到" + dtShipHeader.Rows.Count.ToString() + "条发货单待同步！" + _sClientCode);
for (int i = 0; i < dtShipHeader.Rows.Count; i++)
{
DataRow dr = dtShipHeader.Rows[i];
if (_bCancelSync)
{
SendMessage(0, "同步进行中时,被用户取消！");
break;
}
#region 逐条同步发货单
try
{
string SheetCode = dr["DELIVERY_ID"].ToString().Trim();
string ClientCode = dr["CUST_NUMBER"].ToString().Trim();
DateTime SalesDate = DateTime.Parse(dr["CARRY_SDATE"].ToString().Trim());
string DJType = dr["ORDER_TYPE"].ToString().Trim();
decimal discount = 0;
decimal.TryParse(dr["DISCOUNT"].ToString().Trim(), out discount);
if (DJType == "R")
{
continue;
}
if (ORD_OrderDeliveryBLL.GetModelList(" SheetCode = '" + SheetCode + "'").Count > 0)
{
continue;
}
#region 判断客户号有效否
IList <CM_Client> clients = CM_ClientBLL.GetModelList(" Code= '" + ClientCode.ToString() + "' AND ActiveFlag=1 AND ApproveFlag=1");
if (clients.Count == 0)
{
SendMessage(-11, "经销商编号为" + ClientCode.ToString() + "的匹配错误！");
continue;
}
CM_Client client = clients[0];
if (client["DIClassify"] == "3")
{
//经销商子户头
client = new CM_ClientBLL(client.Supplier).Model;
if (client == null)
{
SendMessage(-12, "经销商编号为" + ClientCode.ToString() + "对应的经销商为子户头，但未关联正确的主户头！");
continue;
}
}
if (client["DIClassify"] != "1")
{
SendMessage(-13, "经销商编号为" + ClientCode.ToString() + "对应的经销商不是主户头！");
continue;
}
#endregion
#region 生成销量单头
ORD_OrderDeliveryBLL bll = new ORD_OrderDeliveryBLL();
bll.Model.SheetCode = SheetCode;
bll.Model.AccountMonth = AC_AccountMonthBLL.GetMonthByDate(SalesDate);
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.State = 1; //未发货
bll.Model.Client = client.ID;
bll.Model.Store = client.Supplier;
if (dr["CARRY_EDATE"].ToString() != "")
{
bll.Model.PreArrivalDate = DateTime.Parse(dr["CARRY_EDATE"].ToString().Trim());
}
else
{
bll.Model.PreArrivalDate = SalesDate.AddDays(7); //默认7天后到货
}
bll.Model.ApproveFlag = 1;
bll.Model.InsertStaff = 1;
bll.Model["DeliveryTime"] = SalesDate.ToString("yyyy-MM-dd HH:mm");
bll.Model["TruckNo"] = dr["CARRY_CAR"].ToString().Trim();
bll.Model["DeliveryStaff"] = "1";
bll.Model["DeliveryRemark"] = "ERP发货单提取";
bll.Model["Discount"] = discount.ToString("0.###");
bll.Model["CarrySheetCode"] = dr["REQUEST_NUMBER"].ToString().Trim();
#endregion
//获取进销存明细
DataTable dt_detail = ERPIF.GetShipDetail(SheetCode);
#region 生成销量明细
foreach (DataRow dr_detail in dt_detail.Rows)
{
string productcode = dr_detail["ITEM_NO"].ToString();
decimal price = decimal.Parse(dr_detail["PRICE"].ToString());
int quantity = (int)decimal.Parse(dr_detail["SHIP_QTY"].ToString());
string packaging = dr_detail["UOM"].ToString();
if (quantity == 0)
{
continue;
}
#region 验证产品信息
PDT_Product product = null;
if (price == 0)
{
#region 价格为0，表示为赠品,取赠品目录中的物料
IList <PDT_Product> _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent=9)");
if (_products.Count > 0)
{
product = _products[0];
}
else
{
_products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent <> 9)");
if (_products.Count == 0)
{
SendMessage(-21, string.Format("发货单号为{0}内,产品编号{1}未在产品目录库中未找到！", SheetCode, productcode));
continue;
}
else
{
//在成品目录里找到，但未在赠品目录中，自动在赠品目录中增加一条本品赠品目录
PDT_ProductBLL productbll = new PDT_ProductBLL();
productbll.Model = _products[0];
productbll.Model.Brand = 3; //本品赠品
productbll.Model.Classify = 100; //其他品类
productbll.Model.State = 2; //停用
productbll.Model.ApproveFlag = 2; //未审核
int productid = productbll.Add();
product = new PDT_ProductBLL(productid).Model;
SendMessage(0, string.Format("发货单号为{0}内,产品编号{1}未在本品赠品目录库中未找到，自动从产品库中增加该赠品信息！", SheetCode, productcode));
}
}
#endregion
}
else
{
#region 从产品目录中取该产品信息
IList <PDT_Product> _products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent<>9)");
if (_products.Count > 0)
{
product = _products[0];
}
else
{
_products = PDT_ProductBLL.GetModelList("Code='" + productcode + "' AND Brand IN (SELECT ID FROM MCS_Pub.dbo.PDT_Brand WHERE IsOpponent = 9)");
if (_products.Count == 0)
{
SendMessage(-22, string.Format("发货单号为{0}内,产品编号{1}未在产品物料目录库中未找到！", SheetCode, productcode));
continue;
}
else
{
//在赠品目录中找到，则使用赠品目录的产品ID
//不在产品中增加该成品 2012-04-25
product = _products[0];
////在赠品目录里找到，但未在成品目录中，自动在成品目录中增加一条本品成品目录
//PDT_ProductBLL productbll = new PDT_ProductBLL();
//productbll.Model = _products[0];
//productbll.Model.Brand = 1;
//productbll.Model.Classify = 1;
//productbll.Model.State = 2; //停用
//productbll.Model.ApproveFlag = 2; //未审核
//int productid = productbll.Add();
//product = new PDT_ProductBLL(productid).Model;
//SendMessage(0, string.Format("发货单号为{0}内,产品编号{1}未在成品目录库中未找到，自动从赠品库中增加该产品信息！", SheetCode, productcode));
}
}
#endregion
}
if (product.State == 2 && product.ApproveFlag == 1)
{
//确认被停用的产品，不再同步
continue;
}
if (product.State == 3 && !string.IsNullOrEmpty(product["MasterProduct"]) && product["MasterProduct"] != "0")
{
//是子产品，自动归属到主产品
PDT_Product _p = new PDT_ProductBLL(int.Parse(product["MasterProduct"])).Model;
if (_p != null)
{
product = _p;
}
}
if (product == null)
{
SendMessage(-23, productcode + "为无效产品编号！");
continue;
}
#endregion
if (packaging == "Ea" && product.ConvertFactor > 1)
{
//整箱单位，转换为零售单位
quantity = quantity * product.ConvertFactor;
price = price / product.ConvertFactor;
}
ORD_OrderDeliveryDetail item = bll.Items.FirstOrDefault
(p => p.Product == product.ID && p.Client == client.ID);
if (item != null)
{
item.DeliveryQuantity += quantity;
}
else
{
item = new ORD_OrderDeliveryDetail();
item.Client = client.ID;
item.Product = product.ID;
item.Price = price;
item.FactoryPrice = price == 0 ? product.FactoryPrice : price;
item.DeliveryQuantity = quantity;
item.SignInQuantity = 0;
item.BadQuantity = 0;
item.LostQuantity = 0;
bll.Items.Add(item);
}
}
#endregion
if (bll.Items.Count == 0)
{
SendMessage(0, "发货单号" + bll.Model.SheetCode + "，未获取到发货单产品明细");
continue;
}
#region 写入发货单
if (bll.Add() > 0)
{
//bll.Delivery(1); //设为发货状态(20140708修改为数据库作业执行)
SendMessage(0, "成功导入发货数据！单号:" + bll.Model.SheetCode + ",发生日期:" + SalesDate.ToString("yyyy-MM-dd") + ",产品明细条数:" + bll.Items.Count.ToString() + ",同步进度:" + (i + 1).ToString() + "/" + dtShipHeader.Rows.Count.ToString());
}
else
{
SendMessage(-40, "导入发货数据失败！单号" + bll.Model.SheetCode);
continue;
}
#endregion
success += 1;
}
catch (Exception err)
{
SendMessage(0, err.StackTrace + err.Source + err.Message);
continue;
}
#endregion
}
SendMessage(0, "------" + begindate.ToString("yyyy-MM-dd HH:mm") +
"至" + enddate.ToString("yyyy-MM-dd HH:mm") +
"。总共同步完成" + success.ToString() + "条发货单记录！-------");
}
catch (Exception err)
{
SendMessage(0, "SyncSellOut()" + err.StackTrace + err.Source + err.Message);
}
}
protected void bt_Create_Click(object sender, EventArgs e)
{
DateTime begindate = DateTime.Parse(tbx_begindate.Text);
DateTime enddate = DateTime.Parse(tbx_enddate.Text);
#region 规则校验
if (select_PlanStaff.SelectValue == "")
{
MessageBox.Show(this, "请正确选择要填报计划的员工!");
return;
}
if (begindate < DateTime.Today)
{
MessageBox.Show(this, "开始日期不能小于今天！");
return;
}
if (enddate < begindate)
{
MessageBox.Show(this, "截止日期必须大于开始日期！");
return;
}
if ((enddate - begindate).Days > 31)
{
MessageBox.Show(this, "日期范围不能超过一个月！");
return;
}
if (JN_WorkingPlanBLL.GetModelList("Staff = " + select_PlanStaff.SelectValue +
" AND ( (BeginDate BETWEEN '" + begindate.ToString("yyyy-MM-dd") + "' AND '" + enddate.ToString("yyyy-MM-dd") +
"') OR ('" + begindate.ToString("yyyy-MM-dd") + "' BETWEEN BeginDate AND EndDate) )").Count > 0)
{
MessageBox.Show(this, "日期范围与系统中已填报的计划有日期重叠！");
return;
}
#endregion
InitGridView(begindate, enddate);
DataTable dt = GenareateDataTable(begindate, enddate);
#region 页面控件使能控制
tr_adddetail.Visible = true;
tbx_begindate.Enabled = false;
tbx_enddate.Enabled = false;
select_PlanStaff.Enabled = false;
tr_OrganizeCity.Enabled = false;
cbx_GenarateSynergetic.Visible = false;
bt_Create.Visible = false;
bt_Save.Visible = true;
#endregion
#region 载入该操作员所有负责客户，加入客户拜访记录计划中
IList <CM_Client> clients = CM_ClientBLL.GetModelList("ClientManager=" + select_PlanStaff.SelectValue + " AND ActiveFlag=1 AND ApproveFlag=1");
foreach (CM_Client client in clients)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = "1";
dr["WorkingClassifyName"] = DictionaryBLL.GetDicCollections("OA_WorkingClassify")["1"].Name;
dr["RelateClient"] = client.ID;
dr["RelateClientName"] = client.FullName;
dr["RelateStaff"] = 0;
dr["RelateStaffName"] = "";
dr["OfficialCity"] = client.OfficalCity;
if (client.OfficalCity > 0)
{
dr["OfficialCityName"] = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", client.OfficalCity).Replace("->", " ");
}
dt.Rows.Add(dr);
}
#endregion
#region 加入协同拜访工作项，管理片区非总部员工，将所有下级员工加入
if (cbx_GenarateSynergetic.Checked && int.Parse(tr_OrganizeCity.SelectValue) > 1)
{
#region 所有下级职位(不含本级职位)
string positions = "";
int position = new Org_StaffBLL(int.Parse(select_PlanStaff.SelectValue)).Model.Position;
DataTable dtAllChilePosition = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Org_Position", "ID", "SuperID", position.ToString());
for (int i = 0; i < dtAllChilePosition.Rows.Count; i++)
{
positions += dtAllChilePosition.Rows[i]["ID"].ToString();
if (i != dtAllChilePosition.Rows.Count - 1)
{
positions += ",";
}
}
#endregion
#region 所有本级及下级管理片区
string orgcitys = "";
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue), true);
orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
#endregion
if (positions != "" && orgcitys != "")
{
IList <Org_Staff> staffs = Org_StaffBLL.GetStaffList("OrganizeCity IN (" + orgcitys + ") AND Position IN (" + positions + ") AND Dimission=1 AND ApproveFlag=1");
foreach (Org_Staff staff in staffs)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = "2";
dr["WorkingClassifyName"] = DictionaryBLL.GetDicCollections("OA_WorkingClassify")["2"].Name;
dr["RelateClient"] = 0;
dr["RelateClientName"] = "";
dr["RelateStaff"] = staff.ID;
dr["RelateStaffName"] = staff.RealName;
dr["OfficialCity"] = staff.OfficialCity;
if (staff.OfficialCity > 0)
{
dr["OfficialCityName"] = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", staff.OfficialCity).Replace("->", " ");
}
dt.Rows.Add(dr);
}
}
}
#endregion
#region 加入除客户拜访以外的工作项
foreach (ListItem item in ddl_WorkingClassify.Items)
{
if (int.Parse(item.Value) >= 3)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = int.Parse(item.Value);
dr["WorkingClassifyName"] = item.Text;
dr["Description"] = "";
dr["RelateClient"] = 0;
dr["RelateClientName"] = "";
dr["RelateStaff"] = 0;
dr["RelateStaffName"] = "";
dr["OfficialCity"] = (int)ViewState["StaffOfficialCity"];
if (ViewState["StaffOfficialCity"] != null && (int)ViewState["StaffOfficialCity"] != 0)
{
dr["OfficialCityName"] = new Addr_OfficialCityBLL((int)ViewState["StaffOfficialCity"]).Model.Name;
}
else
{
dr["OfficialCity"] = 0;
}
dr["Cost1"] = 0;
dr["Cost2"] = 0;
dt.Rows.Add(dr);
}
}
#endregion
ViewState["WorkingPlanData"] = dt;
ViewState["BeginDate"] = begindate;
ViewState["EndDate"] = enddate;
BindGrid();
}
private void threadStartImport(BackgroundWorker worker, DoWorkEventArgs e)
{
bool bFind = false;
StringBuilder _xhs_success = new StringBuilder("");//成功导入的记录序号（用于定期批量更新导入标志）
string XH = "";
int ClientID = 0, AccountMonth = 0, Brand = 0, Classify = 0, DIClassify = 0;
decimal FeeRate;
try
{
lb_Count.Text = dt_Amount.Rows.Count.ToString();
for (int i = 0; i < dt_Amount.Rows.Count; i++)
{
worker.ReportProgress((i + 1) * 100 / this.dt_Amount.Rows.Count, i);
if (worker.CancellationPending)
{
e.Cancel = true;
break;
}
XH = dt_Amount.Rows[i]["序号"].ToString();
IList <CM_Client> _cmlist = CM_ClientBLL.GetModelList("Code='" + dt_Amount.Rows[i]["经销商代码"].ToString() + "' AND ActiveFlag=1 AND ApproveFlag=1");
if (_cmlist.Count == 1 && _cmlist[0].FullName == dt_Amount.Rows[i]["经销商名称"].ToString() && _cmlist[0]["DIClassify"] == "1")
{
ClientID = _cmlist[0].ID;//经销商为子户头时，取主户头
}
else
{
UpdateImportFlag(i, "导入失败，经销商代码不正确");
continue;
}
if (dt_Amount.Rows[i]["品牌"].ToString() != "")
{
IList <PDT_Brand> _listbrand = PDT_BrandBLL.GetModelList("Name='" + dt_Amount.Rows[i]["品牌"].ToString() + "'");
if (_listbrand.Count == 0)
{
UpdateImportFlag(i, "导入失败，该品牌不存在");
continue;
}
else
{
Brand = _listbrand[0].ID;
}
}
foreach (Dictionary_Data item in DictionaryBLL.Dictionary_Data_GetAlllList("Type IN (SELECT ID FROM MCS_SYS.dbo.Dictionary_Type WHERE TableName='ORD_GiftClassify')"))
{
if (item.Name == dt_Amount.Rows[i]["有导无导标志"].ToString().Trim())
{
Classify = int.Parse(item.Code);
bFind = true;
break;
}
}
if (!bFind)
{
UpdateImportFlag(i, "导入失败，有导无导标志错误！");
continue;
}
IList <PDT_ClassifyGiftCostRate> _listamount = PDT_ClassifyGiftCostRateBLL.GetModelList("Enabled='Y' AND Client=" + ClientID.ToString()
+ " AND PDTBrand=" + Brand.ToString() + " AND GiftCostClassify=" + Classify.ToString());
PDT_ClassifyGiftCostRateBLL _bll = new PDT_ClassifyGiftCostRateBLL();
if (_listamount.Count > 0)
{
_bll = new PDT_ClassifyGiftCostRateBLL(_listamount[0].ID);
}
else
{
_bll.Model.AccountMonth = AccountMonth;
_bll.Model.Client = ClientID;
_bll.Model.PDTBrand = Brand;
_bll.Model.GiftCostClassify = Classify;
_bll.Model.Enabled = "Y";
_bll.Model.OrganizeCity = 1;
}
#region 记录需更新费用
decimal.TryParse(dt_Amount.Rows[i]["赠品费率%"].ToString().Trim(), out FeeRate);
_bll.Model.GiftCostRate = FeeRate;
_bll.Model.Remark = dt_Amount.Rows[i]["备注"].ToString().Trim();
if (_bll.Model.ID > 0)
{
_bll.Update();
}
else
{
_bll.Add();
}
dt_Amount.Rows[i]["导入标志"] = "导入成功";
_xhs_success.Append(XH + ",");
#endregion
}
UpdateSuccess(_xhs_success.ToString());
}
catch (System.Exception err)
{
UpdateSuccess(_xhs_success.ToString());
MessageBox.Show(err.Source + "~r~n" + err.StackTrace, err.Message);
}
return;
}
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 threadStartImport(BackgroundWorker worker, DoWorkEventArgs e)
{
bool bFind = false;
StringBuilder _xhs_success = new StringBuilder("");//成功导入的记录序号（用于定期批量更新导入标志）
string XH = "";
int ClientID = 0, AccountMonth = 0, Brand = 0, Classify = 0, DIClassify = 0;
decimal PreBalance, DeductibleAmount;
try
{
lb_Count.Text = dt_Amount.Rows.Count.ToString();
for (int i = 0; i < dt_Amount.Rows.Count; i++)
{
worker.ReportProgress((i + 1) * 100 / this.dt_Amount.Rows.Count, i);
if (worker.CancellationPending)
{
e.Cancel = true;
break;
}
XH = dt_Amount.Rows[i]["序号"].ToString();
IList <CM_Client> _cmlist = CM_ClientBLL.GetModelList("Code='" + dt_Amount.Rows[i]["经销商代码"].ToString() + "' AND ActiveFlag=1 AND ApproveFlag=1");
if (_cmlist.Count == 1)
{
DIClassify = int.Parse(_cmlist[0]["DIClassify"]);
ClientID = DIClassify == 3?_cmlist[0].Supplier: _cmlist[0].ID;//经销商为子户头时，取主户头
}
else
{
UpdateImportFlag(i, "导入失败，经销商代码不正确");
continue;
}
if (dt_Amount.Rows[i]["品牌"].ToString() != "")
{
IList <PDT_Brand> _listbrand = PDT_BrandBLL.GetModelList("Name='" + dt_Amount.Rows[i]["品牌"].ToString() + "'");
if (_listbrand.Count == 0)
{
UpdateImportFlag(i, "导入失败，该品牌不存在");
continue;
}
else
{
Brand = _listbrand[0].ID;
}
}
if (dt_Amount.Rows[i]["会计月"].ToString() != "")
{
IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + dt_Amount.Rows[i]["会计月"].ToString() + "'");
if (_monthlist.Count == 0)
{
UpdateImportFlag(i, "导入失败，会计月填写错误");
continue;
}
else
{
AccountMonth = _monthlist[0].ID;
}
}
foreach (Dictionary_Data item in DictionaryBLL.Dictionary_Data_GetAlllList("Type IN (SELECT ID FROM MCS_SYS.dbo.Dictionary_Type WHERE TableName='ORD_GiftClassify')"))
{
if (item.Name == dt_Amount.Rows[i]["有导无导标志"].ToString().Trim())
{
Classify = int.Parse(item.Code);
bFind = true;
break;
}
}
if (!bFind)
{
UpdateImportFlag(i, "导入失败，有导无导标志错误！");
continue;
}
IList <ORD_GiftApplyAmount> _listamount = ORD_GiftApplyAmountBLL.GetModelList("AccountMonth=" + AccountMonth.ToString() + " AND Client=" + ClientID.ToString()
+ " AND Brand=" + Brand.ToString() + " AND Classify=" + Classify.ToString());
if (_listamount.Count == 0)
{
UpdateImportFlag(i, "导入失败，没有找到符合该条件的赠品余额记录。");
continue;
}
if (ORD_GiftApplyAmountBLL.GetModelList("AccountMonth=" + (AccountMonth + 1).ToString() + " AND Client=" + ClientID.ToString()
+ " AND Brand=" + Brand.ToString() + " AND Classify=" + Classify.ToString()).Count > 0)
{
UpdateImportFlag(i, "导入失败，不能更新历史记录。");
continue;
}
ORD_GiftApplyAmountBLL _bll = new ORD_GiftApplyAmountBLL(_listamount[0].ID);
#region 记录需更新费用
decimal.TryParse(dt_Amount.Rows[i]["赠品抵扣额"].ToString().Trim(), out DeductibleAmount);
if (decimal.TryParse(dt_Amount.Rows[i]["上月余额"].ToString().Trim(), out PreBalance))
{
if (DIClassify == 1)
{
_bll.Model.PreBalance = PreBalance;
}
else
{
_bll.Model.PreBalance += PreBalance;
}
}
_bll.Model.DeductibleAmount += DeductibleAmount;
_bll.Model.BalanceAmount = _bll.Model.AvailableAmount + _bll.Model.PreBalance - _bll.Model.AppliedAmount - _bll.Model.DeductibleAmount;
_bll.Update();
dt_Amount.Rows[i]["导入标志"] = "导入成功";
_xhs_success.Append(XH + ",");
#endregion
}
UpdateSuccess(_xhs_success.ToString());
}
catch (System.Exception err)
{
UpdateSuccess(_xhs_success.ToString());
MessageBox.Show(err.Source + "~r~n" + err.StackTrace, err.Message);
}
return;
}
public string DoImportSupplier(int TemplateID, int Client, ISheet Sheet, out int State)
{
string ImportInfo = "【供货单位信息】Excel表：";
State = 0;
IPT_UploadTemplateBLL _template = new IPT_UploadTemplateBLL(TemplateID);
List <string> listSupplier = new List <string>()
{
"序号", "客户名称", "地址", "联系人", "电话"
};
DataTable dt = null;
bool flag = VertifySheet(Sheet, listSupplier, out dt, ref ImportInfo);
if (!flag)
{
State = 4; return(ImportInfo);
}
foreach (DataRow dr in dt.Rows)//循环导入数据
{
try
{
string _SupplierName = dr["客户名称"].ToString();
string _SupplierAddress = dr["地址"].ToString();
string _SupplierTeleNum = dr["电话"].ToString();
string _SupplierLinkMan = dr["联系人"].ToString();
if (string.IsNullOrEmpty(_SupplierName))
{
ImportInfo += string.Format("序号为{0}的行客户名称均为空，跳过此行信息\r\n", dr["序号"]);
continue;
}
IList <CM_Client> _listClient = CM_ClientBLL.GetModelList(" ClientType=1 AND OwnerType=3 AND OwnerClient=" + _template.Model.ClientID.ToString() + " AND FullName='" + _SupplierName + "'");
if (_listClient != null && _listClient.Count > 0)
{
ImportInfo += string.Format("序号为{0},名称为{1}的客户已存在，跳过此行信息\r\n", dr["序号"], _SupplierName);
continue;
}
CM_ClientBLL _bllClient = new CM_ClientBLL();
_bllClient.Model.FullName = _SupplierName;
_bllClient.Model.ShortName = _SupplierName;
_bllClient.Model.Address = _SupplierAddress;
_bllClient.Model.TeleNum = _SupplierTeleNum;
_bllClient.Model.Remark = "Excel批量导入";
_bllClient.Model.ClientType = 1;
_bllClient.Model.OwnerType = 3;
_bllClient.Model.OwnerClient = _template.Model.ClientID;
_bllClient.Add();
}
catch (Exception ex)
{
ImportInfo += "导入序列号为" + dr["序号"].ToString() + "的数据行时出现错误，错误说明：" + ex.Message + "\r\n";
State = 5;
continue;
}
}
if (State == 0)
{
State = 3;
}
ImportInfo += (State == 3 ? "导入完成！\r\n" : "");
IPT_UploadTemplateMessageBLL _bllUploadTemplateMessage = new IPT_UploadTemplateMessageBLL();
_bllUploadTemplateMessage.Model.TemplateID = TemplateID;
_bllUploadTemplateMessage.Model.MessageType = State;
_bllUploadTemplateMessage.Model.Content = ImportInfo;
_bllUploadTemplateMessage.Add();
return(ImportInfo);
}
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 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();
}
}
public string DoImportClient(int TemplateID, int Client, ISheet Sheet, out int State)
{
string ImportInfo = "【客户资料】Excel表：";
State = 0;
IPT_UploadTemplateBLL _template = new IPT_UploadTemplateBLL(TemplateID);
List <string> listClient = new List <string>()
{
"序号", "区域", "渠道", "客户编号", "客户名称", "联系人", "地址", "电话", "手机", "销售线路", "备注"
};
DataTable dt = null;
bool flag = VertifySheet(Sheet, listClient, out dt, ref ImportInfo);
if (!flag)
{
State = 4; return(ImportInfo);
}
foreach (DataRow dr in dt.Rows)//循环导入数据
{
try
{
string _ClientCode = dr["客户编号"].ToString();//客户自编码
string _ClientName = dr["客户名称"].ToString();
if (string.IsNullOrEmpty(_ClientCode)) // && string.IsNullOrEmpty(_ClientName)
{
ImportInfo += "导入序列号为" + dr["序号"].ToString() + "的数据行时找不到门店编码，跳过此行记录\r\n"; //门店名称和
continue;
}
IList <CM_Client> _listClient = CM_ClientBLL.GetModelList(" ApproveFlag=1 AND ClientType=3 AND OwnerType=3 AND OwnerClient=" + _template.Model.ClientID + "AND EXISTS(SELECT 1 FROM MCS_CM.dbo.CM_ClientSupplierInfo WHERE Supplier=" + _template.Model.ClientID + " AND Client= CM_Client.id AND Code=" + _ClientCode + " ) ");
if (_listClient != null && _listClient.Count > 0)
{
ImportInfo += "导入序列号为" + dr["序号"].ToString() + "的数据行时找到相同编码的门店，跳过此行记录\r\n";
continue;
}
string _ClientLinkMan = dr["联系人"].ToString();
string _ClientAddress = dr["地址"].ToString();
string _ClientTel = dr["电话"].ToString();
string _ClientMobile = dr["手机"].ToString();
#region 获取线路
string _strClientVisitRoute = dr["销售线路"].ToString(); //业务拜访模板Code
VST_Route _route = null;
if (!string.IsNullOrEmpty(_strClientVisitRoute)) //优先匹配厂商级的拜访记录
{
_route = VST_RouteBLL.GetModelList(" OwnerType IN(1,2) AND Code='" + _strClientVisitRoute + "'").FirstOrDefault(); //IList<VST_Route>_listRoute
if (_route == null) //找不到厂商级的线路找经销商级别的
{
_route = VST_RouteBLL.GetModelList(" OwnerType=3 AND OwnerClient=" + _template.Model.ClientID + " AND Code='" + _strClientVisitRoute + "'").FirstOrDefault();
}
}
/*模板中存在负责业务时获取门店业代和拜访记录代码
* int _ClientSalesMan = 0;//负责业务
* int _ClientVisitRoute = 0;//业务拜访模板ID
* string _strClientSalesMan = dr["负责业务"].ToString();
* if (!string.IsNullOrEmpty(_strClientSalesMan))
* {
* IList<Org_Staff> _listStaff = Org_StaffBLL.GetStaffList(" OwnerClient=" + _template.Model.ClientID + " AND OwnerType=3 AND RealName='" + _strClientSalesMan + "'");
* if (_listStaff != null && _listStaff.Count > 0)
* {
* _ClientSalesMan = _listStaff[0].ID;
* IList<VST_Route> _listRoute = VST_RouteBLL.GetModelList(" OwnerClient=" + _template.Model.ClientID + " AND RelateStaff=" + _ClientSalesMan);
* if (_listRoute != null && _listRoute.Count > 0) _ClientVisitRoute = _listRoute[0].ID;
* }
* else
* {
* Org_StaffBLL _bllStaff = new Org_StaffBLL();
* CM_ClientBLL c = new CM_ClientBLL(_template.Model.ClientID);
* CM_ClientManufactInfo manufactinfo = c.GetManufactInfo();
* if (c != null && manufactinfo != null)
* {
* _bllStaff.Model.OrganizeCity = manufactinfo.OrganizeCity;
* _bllStaff.Model.OfficialCity = c.Model.OfficialCity;
* }
* _bllStaff.Model.RealName = _strClientSalesMan;
* _bllStaff.Model.Position = 1030;//默认为业务员
* _bllStaff.Model.InsertStaff = _template.Model.InsertStaff;
* _bllStaff.Model.OwnerClient = _template.Model.ClientID;
* _bllStaff.Model.OwnerType = 3;
* _bllStaff.Model.Dimission = 1;
* _bllStaff.Model.ApproveFlag = 1;
* _ClientSalesMan = _bllStaff.Add();
* //创建默认员工线路
* if (_ClientSalesMan > 0)
* {
* if (_bllStaff.Model.Position == 1030)
* {
* VST_RouteBLL routebll = new VST_RouteBLL();
* routebll.Model.Code = "R" + _ClientSalesMan.ToString();
* routebll.Model.Name = "线路-" + _bllStaff.Model.RealName;
* routebll.Model.RelateStaff = _ClientSalesMan;
* routebll.Model.OrganizeCity = _bllStaff.Model.OrganizeCity;
* routebll.Model.OwnerClient = _template.Model.ClientID;
* routebll.Model.OwnerType = 3;
* routebll.Model.ApproveFlag = 1;
* routebll.Model.EnableFlag = "Y";
* routebll.Model.InsertStaff = _template.Model.InsertStaff;
* _ClientVisitRoute = routebll.Add();
* }
* }
* }
* }*/
#endregion
string _ClientRemark = dr["备注"].ToString();
int _OwnerClient = _template.Model.ClientID;
#region 获取所在区域
int _SalesArea = 0;//区域
string _strSalesArea = dr["区域"].ToString();
if (!string.IsNullOrEmpty(_strSalesArea))
{
IList <CM_RTSalesArea_TDP> _listSalesArea = CM_RTSalesArea_TDPBLL.GetModelList(" OwnerClient= " + _OwnerClient.ToString() + " AND Name='" + _strSalesArea + "' ");
if (_listSalesArea != null && _listSalesArea.Count > 0)
{
_SalesArea = _listSalesArea[0].ID;
}
else
{
CM_RTSalesArea_TDPBLL _bllRTSalesArea_TDPBLL = new CM_RTSalesArea_TDPBLL();
_bllRTSalesArea_TDPBLL.Model.Name = _strSalesArea;
_bllRTSalesArea_TDPBLL.Model.OwnerClient = _OwnerClient;
_bllRTSalesArea_TDPBLL.Model.Remark = "Excel批量导入";
_bllRTSalesArea_TDPBLL.Model.InsertStaff = _template.Model.InsertStaff;
_bllRTSalesArea_TDPBLL.Model.InsertTime = DateTime.Now;
_SalesArea = _bllRTSalesArea_TDPBLL.Add();
}
}
#endregion
#region 获取所在渠道
int _RTChannel = 0;//渠道
string _strRTChannel = dr["渠道"].ToString();
if (!string.IsNullOrEmpty(_strRTChannel))
{
IList <CM_RTChannel_TDP> _listRTChannel = CM_RTChannel_TDPBLL.GetModelList(" OwnerClient= " + _OwnerClient.ToString() + " AND Name='" + _strRTChannel + "' ");
if (_listRTChannel != null && _listRTChannel.Count > 0)
{
_RTChannel = _listRTChannel[0].ID;
}
else
{
CM_RTChannel_TDPBLL _bllRTSalesArea_TDPBLL = new CM_RTChannel_TDPBLL();
_bllRTSalesArea_TDPBLL.Model.Name = _strRTChannel;
_bllRTSalesArea_TDPBLL.Model.OwnerClient = _OwnerClient;
_bllRTSalesArea_TDPBLL.Model.Remark = "Excel批量导入";
_bllRTSalesArea_TDPBLL.Model.InsertStaff = _template.Model.InsertStaff;
_bllRTSalesArea_TDPBLL.Model.InsertTime = DateTime.Now;
_RTChannel = _bllRTSalesArea_TDPBLL.Add();
}
}
#endregion
CM_ClientBLL _bll = new CM_ClientBLL();//Client表Code字段暂不启用，以FactoryCode作为厂商编码。经销商对门店的编码存于CM_ClientSupplierInfo表的Code字段
_bll.Model.FullName = _ClientName;
_bll.Model.ShortName = _ClientName;
_bll.Model.LinkManName = _ClientLinkMan;
_bll.Model.Address = _ClientAddress;
_bll.Model.TeleNum = _ClientTel;
_bll.Model.Mobile = _ClientMobile;
_bll.Model.Remark = string.IsNullOrEmpty(_ClientRemark) ? "Excel批量导入" : _ClientRemark;
_bll.Model.ClientType = 3;
_bll.Model.ApproveFlag = 1;
_bll.Model.InsertStaff = _template.Model.InsertStaff;
_bll.Model.OwnerType = 3; //所属经销商
_bll.Model.OwnerClient = _OwnerClient;
int _ClientID = _bll.Add();
if (_ClientID > 0)
{
CM_ClientSupplierInfo _Supplierinfo = _bll.GetSupplierInfo(_OwnerClient);
if (_Supplierinfo == null)
{
_Supplierinfo = new CM_ClientSupplierInfo();
_Supplierinfo.Supplier = _OwnerClient;
}
_Supplierinfo.TDPChannel = _RTChannel;
_Supplierinfo.TDPSalesArea = _SalesArea;
_Supplierinfo.Code = _ClientCode;
_Supplierinfo.Remark = _ClientRemark;
CM_ClientBLL s = new CM_ClientBLL(_OwnerClient);
CM_ClientManufactInfo _manufactinfo = _bll.GetManufactInfo(s.Model.OwnerClient);
if (_manufactinfo == null)
{
_manufactinfo = new CM_ClientManufactInfo();
//门店所属区域为经销商对应区域
_manufactinfo.Manufacturer = s.Model.OwnerClient;
_manufactinfo.OrganizeCity = s.GetManufactInfo().OrganizeCity;
}
if (_route != null && _route.OwnerType == 3)//线路为经销商级别的存放在经销商表中
{
_Supplierinfo.VisitRoute = _route.ID;
}
else if (_route != null && (_route.OwnerType == 1 || _route.OwnerType == 2))//线路为厂商级别的存放在经销商表中
{
_manufactinfo.VisitRoute = _route.ID;
}
_bll.SetSupplierInfo(_Supplierinfo);
_bll.SetManufactInfo(_manufactinfo);
}
}
catch (Exception ex)
{
ImportInfo += "导入序列号为" + dr["序号"].ToString() + "的数据行时出现错误，错误说明：" + ex.Message + "\r\n";
State = 5;
continue;
}
}
if (State == 0)
{
State = 3;
}
ImportInfo += (State == 3 ? "导入完成！\r\n" : "");
IPT_UploadTemplateMessageBLL _bllUploadTemplateMessage = new IPT_UploadTemplateMessageBLL();
_bllUploadTemplateMessage.Model.TemplateID = TemplateID;
_bllUploadTemplateMessage.Model.MessageType = State;
_bllUploadTemplateMessage.Model.Content = ImportInfo;
_bllUploadTemplateMessage.Add();
return(ImportInfo);
}
