private void BindGrid() { int month = int.Parse(ddl_AccountMonth.SelectedValue); int organizecity = int.Parse(tr_OrganizeCity.SelectValue); int scheme = int.Parse(ddl_KPI_Scheme.SelectedValue); int staff; int.TryParse(select_Staff.SelectValue, out staff); int approveflag = int.Parse(ddl_ApproveFlag.SelectedValue); int position = int.Parse(ddl_Position.SelectedValue); DataTable dt_detail = KPI_ScoreBLL.KPIGetByAccountMonth(organizecity, month, scheme, staff, approveflag, position); if (dt_detail.Rows.Count == 0) { gv_List.DataBind(); return; } dt_detail = MatrixTable.Matrix(dt_detail, new string[] { "ID", "营业部", "办事处", "会计月", "员工姓名", "职位", "审核标志", "KPI总达成率" }, new string[] { "schemename", "detailname" }, "LastValue", false, false); gv_List.DataSource = dt_detail; gv_List.DataBind(); if (dt_detail.Columns.Count >= 24) { gv_List.Width = new Unit(dt_detail.Columns.Count * 55); } else { gv_List.Width = new Unit(100, UnitType.Percentage); } MatrixTable.GridViewMatric(gv_List); }
private void BindGrid() { DateTime dtbegin, dtend; int salesman, deliveryman; DateTime.TryParse(tbx_begin.Text, out dtbegin); DateTime.TryParse(tbx_end.Text, out dtend); int.TryParse(ddl_SalesMan.SelectedValue, out salesman); int.TryParse(ddl_DeliveryMan.SelectedValue, out deliveryman); if (gv_List_Product.Visible) { DataTable dt = PBM_DeliveryBLL.GetDeliverySummary_ByProduct((int)Session["OwnerClient"], salesman, deliveryman, 0, dtbegin, dtend); int _quantity_t = 0, _quantity_p = 0; decimal _amount = 0; foreach (DataRow dr in dt.Rows) { _quantity_t += (int)dr["Quantity_T"]; _quantity_p += (int)dr["Quantity_P"]; _amount += (decimal)dr["TotalAmount"]; } DataRow row = dt.NewRow(); row["ProductName"] = "合计"; row["Quantity_T"] = _quantity_t; row["Quantity_P"] = _quantity_p; row["TotalAmount"] = _amount; dt.Rows.Add(row); gv_List_Product.DataSource = dt; gv_List_Product.DataBind(); } else if (gv_List_Client.Visible) { DataTable dt = PBM_DeliveryBLL.GetDeliverySummary_ByClient((int)Session["OwnerClient"], salesman, deliveryman, 0, dtbegin, dtend); gv_List_Client.DataSource = dt; gv_List_Client.DataBind(); } else if (gv_PayInfoSummary.Visible) { DataTable dt = PBM_DeliveryBLL.GetPayInfoSummary((int)Session["OwnerClient"], salesman, deliveryman, dtbegin, dtend); gv_PayInfoSummary.DataSource = dt; gv_PayInfoSummary.DataBind(); gv_PayInfoSummary.Visible = true; } else if (gv_PayInfoDetail.Visible) { DataTable dt = PBM_DeliveryBLL.GetPayInfoDetail((int)Session["OwnerClient"], salesman, deliveryman, dtbegin, dtend); dt = MatrixTable.Matrix(dt, new string[] { "SheetCode", "ClientName", "DeliveryManName" }, "PayModeName", "Amount"); dt.Columns["SheetCode"].ColumnName = "单号"; dt.Columns["ClientName"].ColumnName = "客户"; dt.Columns["DeliveryManName"].ColumnName = "送货人"; gv_PayInfoDetail.DataSource = dt; gv_PayInfoDetail.DataBind(); } }
public void BindGrid() { if (tr_OrganizeCity.SelectValue == "0") { return; } DataTable dt = FNA_BudgetBLL.GetAssignInfo(int.Parse(tr_OrganizeCity.SelectValue), int.Parse(ddl_AccountMonth.SelectedValue), int.Parse(ddl_CityLevel.SelectedValue)); DataTable dt_Matrix = MatrixTable.Matrix(dt, new string[] { "OrganizeCity", "区域名称" }, "FeeTypeName", "BudgetAmount", true, true); gv_List.DataSource = dt_Matrix; gv_List.DataBind(); }
private void BindGrid() { int OrganizeCity = int.Parse(tr_OrganizeCity.SelectValue); int Month = int.Parse(ddl_Month.SelectedValue); //会计月条件 int Level = int.Parse(ddl_Level.SelectedValue); DataTable dt = FNA_BudgetBalanceBLL.GetBalance(OrganizeCity, Month, Level); DataTable dt_Matrix = MatrixTable.Matrix(dt, new string[] { "OrganizeCity", "区域名称", "区域级别" }, "FeeTypeName", "CostBalance"); gv_BalanceList.SelectedIndex = -1; gv_BalanceList.DataSource = dt_Matrix; gv_BalanceList.DataBind(); tr_BalanceChangeList.Visible = false; }
private void BindGrid() { int organizecity = 0; if (int.TryParse(tr_OrganizeCity.SelectValue, out organizecity)) { DataTable dt = FNA_BudgetPercentFeeTypeBLL.GetList(organizecity, 0); DataTable dt_matrix = MatrixTable.Matrix(dt, new string[] { "区域ID", "区域名称", "区域级别" }, "FeeTypeName", "BudgetPercent", false, true); //dt_matrix.Columns.Add("机动费用", Type.GetType("System.Decimal"), "100-合计"); gv_List.DataSource = dt_matrix; gv_List.DataBind(); } }
private void BindGrid() { int organizecity, accoutmonth; int.TryParse(tr_OrganizeCity.SelectValue, out organizecity); int.TryParse(ddl_Month.SelectedValue, out accoutmonth); DataTable dtSummary = ORD_GiftApplyAmountBLL.GetUsedInfo(accoutmonth, organizecity); if (dtSummary.Rows.Count == 0) { gv_List.DataBind(); return; } dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "大区", "省区", "办事处", "代码", "经销商ID", "客户全称" }, new string[] { "ORD_GiftApplyAmount_Brand", "Classify" }, new string[] { "实际销量", "赠品费率%", "本月实销生成可申请额度", "上月余额", "赠品抵扣额", "已申请赠品额度", "还可申请赠品额度" }, false, false); dtSummary.Columns.Add("N.合计→-→含赠品抵扣额费率%"); dtSummary.Columns["N.合计→-→含赠品抵扣额费率%"].SetOrdinal(dtSummary.Columns.Count - 6); foreach (DataRow row in dtSummary.Rows) { if ((decimal)row["N.合计→-→实际销量"] != 0) { row["N.合计→-→含赠品抵扣额费率%"] = (((decimal)row["N.合计→-→已申请赠品额度"] + (decimal)row["N.合计→-→赠品抵扣额"]) / (decimal)row["N.合计→-→实际销量"]).ToString("0.00%"); } else { row["N.合计→-→含赠品抵扣额费率%"] = 0; } } 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); }
private void BindJXCSummary() { DataTable dt = OPI_JXCSummary_DIBLL.GetOverview((int)ViewState["OrganizeCity"], 0); if (dt.Rows.Count > 0) { DataTable matrix_dt = MatrixTable.Matrix(dt, new string[] { "Item" }, "MonthName", "Value", false, false); matrix_dt.Columns["Item"].ColumnName = "项目"; gv_JXCSummary_DI.DataSource = matrix_dt; gv_JXCSummary_DI.DataBind(); DataView dv0 = new DataView(dt, "Item='A.期初库存'", "MonthName", DataViewRowState.CurrentRows); chart_JXCSummary.Series[0].Points.DataBind(dv0, "MonthName", "Value", ""); DataView dv1 = new DataView(dt, "Item='B.本期进货'", "MonthName", DataViewRowState.CurrentRows); chart_JXCSummary.Series[1].Points.DataBind(dv1, "MonthName", "Value", ""); DataView dv2 = new DataView(dt, "Item='C.本月销售'", "MonthName", DataViewRowState.CurrentRows); chart_JXCSummary.Series[2].Points.DataBind(dv2, "MonthName", "Value", ""); DataView dv3 = new DataView(dt, "Item='D.期末盘存'", "MonthName", DataViewRowState.CurrentRows); chart_JXCSummary.Series[3].Points.DataBind(dv3, "MonthName", "Value", ""); DataView dv4 = new DataView(dt, "Item='E.预计销售'", "MonthName", DataViewRowState.CurrentRows); chart_JXCSummary.Series[4].Points.DataBind(dv4, "MonthName", "Value", ""); //DataView dv5 = new DataView(dt, "Item='F.实际达成'", "MonthName", DataViewRowState.CurrentRows); //chart_JXCSummary.Series[5].Points.DataBind(dv5, "MonthName", "Value", ""); //DataView dv6 = new DataView(dt, "Item='H.投入费比'", "MonthName", DataViewRowState.CurrentRows); //chart_JXCSummary.Series[6].Points.DataBind(dv6, "MonthName", "Value", ""); } else { MessageBox.Show(this, "对不起,未检索到数据!"); return; } }
private void BindGrid() { int month = int.Parse(ddl_Month.SelectedValue); int organizecity = int.Parse(tr_OrganizeCity.SelectValue); DataTable dtSummary = new DataTable(); int state = int.Parse(ddl_State.SelectedValue); string accountname = ddl_Month.SelectedItem.Text; string preaccountname = DateTime.Parse(accountname + "-01").AddMonths(-3).ToString("yyyy-MM"); switch (MCSTabControl1.SelectedTabItem.Value) { case "0": { dtSummary = FNA_FeeApplyBLL.GetDiaplayFeeSummary(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[] { "责任人员" }, 3, new string[] { "RTChannel", "DisplayFeeType", "Title" }, false); #region 重新计算总计行的费率 if (dtSummary.Rows.Count > 1) { foreach (DataRow dr in dtSummary.Rows) { if (dr[0].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 + "→2.陈列费总计(元/月)→A.我司承担") && dtSummary.Columns.Contains(title + "→3.销量及费率→C." + accountname + "预计销量(元/月)") && (decimal)dr[title + "→3.销量及费率→C." + accountname + "预计销量(元/月)"] != 0) { dr[dc.ColumnName] = Math.Round((decimal)dr[title + "→2.陈列费总计(元/月)→A.我司承担"] / (decimal)dr[title + "→3.销量及费率→C." + accountname + "预计销量(元/月)"] * 100, 1, MidpointRounding.AwayFromZero); } } } if (dc.ColumnName.EndsWith("E.费率(较" + preaccountname + ")%")) { string title = dc.ColumnName; int pos = title.IndexOf('→'); if (pos > 0) { title = title.Substring(0, pos); if (dtSummary.Columns.Contains(title + "→2.陈列费总计(元/月)→A.我司承担") && dtSummary.Columns.Contains(title + "→3.销量及费率→A." + preaccountname + "实际销量(元/月)") && (decimal)dr[title + "→3.销量及费率→A." + preaccountname + "实际销量(元/月)"] != 0) { dr[dc.ColumnName] = Math.Round((decimal)dr[title + "→2.陈列费总计(元/月)→A.我司承担"] / (decimal)dr[title + "→3.销量及费率→A." + preaccountname + "实际销量(元/月)"] * 100, 1, MidpointRounding.AwayFromZero); } } } } } } } #endregion } break; case "1": { dtSummary = FNA_FeeApplyBLL.GetDiaplayFeeByDisplay(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[] { "DisplayMode", "Title" }, "Summary", true, false); #region 重新计算总计行的费率 if (dtSummary.Rows.Count > 1) { foreach (DataRow dr in dtSummary.Rows) { if (dr[0].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 + "→B.我司费用") && dtSummary.Columns.Contains(title + "→D." + accountname + "预计销量(元/月)") && (decimal)dr[title + "→D." + accountname + "预计销量(元/月)"] != 0) { dr[dc.ColumnName] = Math.Round((decimal)dr[title + "→B.我司费用"] / (decimal)dr[title + "→D." + accountname + "预计销量(元/月)"] * 100, 1, MidpointRounding.AwayFromZero); } } } } } } } #endregion } break; case "2": { dtSummary = FNA_FeeApplyBLL.GetByPayMode(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[] { "付款周期", "Title" }, "Summary", true, false); #region 重新计算总计行的费率 if (dtSummary.Rows.Count > 1) { foreach (DataRow dr in dtSummary.Rows) { if (dr[0].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 + "→我司费用") && dtSummary.Columns.Contains("总计→我司费用") && (decimal)dr["总计→我司费用"] != 0) { dr[dc.ColumnName] = Math.Round((decimal)dr[title + "→我司费用"] / (decimal)dr["总计→我司费用"] * 100, 1, MidpointRounding.AwayFromZero); } } } } } } } #endregion } break; } 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); MatrixTable.GridViewMergSampeValueRow(gv_List, 1); #region 是否可以批量审批 if (Right_Assign_BLL.GetAccessRight(Session["UserName"].ToString(), 1509, "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(1, organizecity, month); if (dt.Rows.Count > 0) { bt_Approve.Enabled = false; //bt_UnApprove.Enabled = false; 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=1&StaffID=0&Month=" + ddl_Month.SelectedValue + "&City=" + tr_OrganizeCity.SelectValue + "&tempid='+tempid, window, 'dialogWidth:520px;DialogHeight=600px;status:yes;resizable=no');</script>", false); } } #endregion } else { bt_Approve.Visible = false; bt_UnApprove.Visible = false; } #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(); } }
private void BindGrid() { ViewState["ClientID"] = int.Parse(select_Client.SelectValue); if (MCSTabControl1.SelectedIndex == 0) { DataTable dtSummary = FNA_FeeApplyBLL.GetSummaryTotalByDistributor((int)ViewState["ClientID"], int.Parse(ddl_BeginMonth.SelectedValue), int.Parse(ddl_EndMonth.SelectedValue), int.Parse(ddl_FeeType.SelectedValue)); if (dtSummary.Rows.Count == 0) { gv_List.DataBind(); return; } dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "客户名称" }, new string[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true); dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "ApplyCost" }); gv_List.DataSource = dtSummary; gv_List.DataBind(); MatrixTable.GridViewMatric(gv_List); } else { string condition = " FNA_FeeApply.State =3 "; #region 组织查询条件 //管理片区及所有下属管理片区 CM_Client client = new CM_ClientBLL((int)ViewState["ClientID"]).Model; int city = TreeTableBLL.GetSuperIDByLevel("MCS_SYS.dbo.Addr_OrganizeCity", client.OrganizeCity, 2); if (city != 1) { Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(city); string orgcitys = orgcity.GetAllChildNodeIDs(); if (orgcitys != "") { orgcitys += ","; } orgcitys += city.ToString(); condition += " AND FNA_FeeApply.OrganizeCity IN (" + orgcitys + ")"; } //会计月条件 condition += " AND FNA_FeeApply.AccountMonth BETWEEN " + ddl_BeginMonth.SelectedValue + " AND " + ddl_EndMonth.SelectedValue; //费用类型 if (ddl_FeeType.SelectedValue != "0") { condition += " AND FNA_FeeApply.FeeType = " + ddl_FeeType.SelectedValue; } //指定经销商 condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE MCS_CM.dbo.uf_GetDistributorIDByClient(FNA_FeeApplyDetail.Client) =" + ViewState["ClientID"].ToString() + ")"; #endregion gv_ListDetail.ConditionString = condition; gv_ListDetail.BindGrid(); } }
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() { 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); int flag = int.Parse(ddl_Flag.SelectedValue); if (MCSTabControl1.SelectedIndex == 0) { #region 显示汇总单数据源 Dictionary_Data dicFeeType = null; if (feetype > 0) { dicFeeType = DictionaryBLL.GetDicCollections("FNA_FeeType")[feetype.ToString()]; } DataTable dtSummary_Sub; DataTable dtSummary = FNA_FeeApplyBLL.GetSummaryTotal(month, organizecity, level, feetype, state, flag, int.Parse(Session["UserID"].ToString())); if (dtSummary.Rows.Count == 0) { gv_List.DataBind(); return; } else { dtSummary_Sub = FNA_FeeApplyBLL.GetSummaryTotal_Sub(month, organizecity, level, feetype, state, flag, int.Parse(Session["UserID"].ToString())); } #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" }, "ApplyCost", true, 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[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true); } else if (level == 20) { //按门店查看 #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[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true); } } dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "sales" }); dtSummary.Columns["合计"].ColumnName = "费用合计"; dtSummary.Columns.Add("本月费用合计", Type.GetType("System.Decimal")); dtSummary.Columns.Add("预计销量", Type.GetType("System.Decimal")); dtSummary.Columns.Add("上月销量", Type.GetType("System.Decimal")); dtSummary.Columns.Add("平均销量", Type.GetType("System.Decimal")); dtSummary.Columns.Add("预计费点", Type.GetType("System.String")); 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")); dtSummary.Columns.Add("终结费用", Type.GetType("System.Decimal")); } if (dicFeeType != null && dicFeeType.Description == "FeeRateControl") { dtSummary.Columns.Add("上月余额", Type.GetType("System.Decimal")); } #endregion decimal sumTotalVolume = 0, sumAvgVolume = 0, sumSalesForcast = 0, sumHappenApplyCost = 0; decimal sumTotalBudget = 0, sumExtBudget = 0, sumPreMonthBudgetBalance = 0, sumUsableAmount = 0, sumCancelCost = 0; int premonth = month - 1; if (premonth >= AC_AccountMonthBLL.GetCurrentMonth()) { premonth = AC_AccountMonthBLL.GetCurrentMonth() - 1; } foreach (DataRow row in dtSummary.Rows) { int id = 0; if (int.TryParse(row["ID"].ToString(), out id) && id > 0) { string filter = "ID=" + id; bool includechild = false; if (level < 10 && new Addr_OrganizeCityBLL(id).Model.Level >= level) { includechild = true; } #region 计算销量数据 decimal happenApplyCost = 0, forcast = 0, preSales = 0, aVGSales = 0, feeRate = 0; DataRow[] drows = dtSummary_Sub.Select(filter); if (drows.Length > 0) { decimal.TryParse(drows[0]["HappenApplyCost"].ToString(), out happenApplyCost); decimal.TryParse(drows[0]["Forcast"].ToString(), out forcast); decimal.TryParse(drows[0]["PreSales"].ToString(), out preSales); decimal.TryParse(drows[0]["AVGSales"].ToString(), out aVGSales); decimal.TryParse(drows[0]["FeeRate"].ToString(), out feeRate); } row["本月费用合计"] = happenApplyCost.ToString("0.##"); row["上月销量"] = preSales.ToString("0.##"); row["平均销量"] = aVGSales.ToString("0.##"); row["预计销量"] = forcast.ToString("0.##"); row["预计费点"] = feeRate.ToString("0.##%"); sumHappenApplyCost += (decimal)row["本月费用合计"]; sumTotalVolume += (decimal)row["上月销量"]; sumAvgVolume += (decimal)row["平均销量"]; sumSalesForcast += (decimal)row["预计销量"]; #endregion #region 预算总额及余额 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["预算余额"]; row["终结费用"] = (FNA_FeeApplyBLL.GetCancelCost(month, id, feetype, includechild) + FNA_FeeApplyBLL.GetCancelCost(month, id, 0, includechild)); sumCancelCost += (decimal)row["终结费用"]; } #endregion //#region 计算预计费点 //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["本月费用合计"] = sumHappenApplyCost.ToString("0.##"); row["上月销量"] = sumTotalVolume.ToString("0.##"); row["平均销量"] = sumAvgVolume.ToString("0.##"); row["预计销量"] = sumSalesForcast.ToString("0.##"); #region 预算总额及余额 if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10) { row["预算总额"] = sumTotalBudget.ToString("0.##"); row["其中扩增额"] = sumExtBudget.ToString("0.##"); row["预算余额"] = sumUsableAmount.ToString("0.##"); row["终结费用"] = sumCancelCost.ToString("0.##"); } #endregion #region 预计费点 if (sumSalesForcast != 0) { if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10) { row["上月余额"] = sumPreMonthBudgetBalance; row["预计费点"] = (((decimal)row["费用合计"] - sumPreMonthBudgetBalance) / sumSalesForcast).ToString("0.##%"); } else { row["预计费点"] = ((decimal)row["费用合计"] / sumSalesForcast).ToString("0.##%"); } } #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 #endregion } else { string condition = "1=1"; #region 组织明细记录的查询条件 //管理片区及所有下属管理片区 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 FNA_FeeApply.OrganizeCity IN (" + orgcitys + ")"; } //会计月条件 condition += " AND FNA_FeeApply.AccountMonth = " + ddl_Month.SelectedValue; //费用类型 if (ddl_FeeType.SelectedValue != "0") { condition += " AND FNA_FeeApply.FeeType = " + ddl_FeeType.SelectedValue; } int accounttile = 0; int.TryParse(tr_AccountTitle.SelectValue, out accounttile); decimal _cost = 0; decimal.TryParse(tbx_ApplyCost.Text, out _cost); //费用科目 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_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)"; } else { condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AccountTitle IN(" + ids + ") AND (ApplyCost+AdjustCost)" + ddl_ApplyCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)"; } } else if (_cost != 0)//金额判断 { condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE (ApplyCost+AdjustCost)" + ddl_ApplyCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)"; } //审批状态 if (ddl_State.SelectedValue == "0") { condition += " AND FNA_FeeApply.State IN (2,3) "; } else if (ddl_State.SelectedValue == "1") { condition += @" AND FNA_FeeApply.State = 2 AND FNA_FeeApply.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_FeeApply.State = 3 "; } else if (ddl_State.SelectedValue == "3") { AC_AccountMonth m = new AC_AccountMonthBLL(month).Model; condition += @" AND FNA_FeeApply.State IN (2,3) AND FNA_FeeApply.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") + @"'))"; } #endregion gv_ListDetail.ConditionString = condition; gv_ListDetail.BindGrid(); btn_Approve.Visible = state == 1; btn_UnApprove.Visible = state == 1; gv_ListDetail.Columns[gv_ListDetail.Columns.Count - 1].Visible = state == 1; } }
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 BindGrid() { string ConditionStr = "JN_WorkingPlan.BeginDate Between '" + tbx_begindate.Text + "' AND '" + tbx_enddate.Text + " 23:59' "; switch (MCSTabControl1.SelectedTabItem.Value) { case "0": ConditionStr += " AND JN_WorkingPlan.Staff=" + Session["UserID"].ToString(); gv_List.OrderFields = "JN_WorkingPlan_ID"; gv_List.ConditionString = ConditionStr; gv_List.BindGrid(); break; case "1": if (tbx_StaffName.Text != "") { ConditionStr += "AND Org_Staff.RealName LIKE '%" + tbx_StaffName.Text + "%'"; } #region 只显示当前员工的所有下级职位的员工计划 if (tr_Position.SelectValue != "0") { if (cb_IncludeChild.Checked || tr_Position.SelectValue == tr_Position.RootValue) { Org_PositionBLL p = new Org_PositionBLL(int.Parse(tr_Position.SelectValue)); string positions = p.GetAllChildPosition(); if (tr_Position.SelectValue != tr_Position.RootValue) { if (positions != "") { positions += ","; } positions += tr_Position.SelectValue; } ConditionStr += " AND Org_Staff.Position IN (" + positions + ")"; } else { ConditionStr += " AND Org_Staff.Position = " + tr_Position.SelectValue; } } #endregion #region 判断当前可查询的管理片区范围 if (tr_OrganizeCity.SelectValue != "1") { string orgcitys = ""; Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue), true); orgcitys = orgcity.GetAllChildNodeIDs(); if (orgcitys != "") { orgcitys += ","; } orgcitys += tr_OrganizeCity.SelectValue; if (orgcitys != "") { ConditionStr += " AND JN_WorkingPlan.OrganizeCity IN (" + orgcitys + ") "; } } #endregion gv_List.OrderFields = "JN_WorkingPlan_ID"; gv_List.ConditionString = ConditionStr; gv_List.BindGrid(); break; case "2": DateTime dt_begin = DateTime.Parse(tbx_begindate.Text); DateTime dt_end = DateTime.Parse(tbx_enddate.Text); DataTable dtSummary = JN_WorkingPlanBLL.GetSummary(dt_begin, dt_end, int.Parse(tr_OrganizeCity.SelectValue), int.Parse(tr_Position.SelectValue), tbx_StaffName.Text.Trim(), cb_IncludeChild.Checked ? 1 : 0); if (dtSummary.Rows.Count == 0) { gv_planList.DataBind(); return; } dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "营业部", "办事处", "Staff", "员工姓名", "工号", "职位", "出差天数", "负责客户数", "拜访客户数→计划", "拜访客户数→实际", "差旅费→计划", "差旅费→实际差旅费", "差旅费→实际用车费" }, new string[] { "WorkingClassify" }, "WorkCounts", true, false); dtSummary.Rows[dtSummary.Rows.Count - 1]["出差天数"] = dtSummary.Compute("SUM(出差天数)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["负责客户数"] = dtSummary.Compute("SUM(负责客户数)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["拜访客户数→计划"] = dtSummary.Compute("SUM(拜访客户数→计划)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["拜访客户数→实际"] = dtSummary.Compute("SUM(拜访客户数→实际)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["差旅费→计划"] = dtSummary.Compute("SUM(差旅费→计划)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["差旅费→实际差旅费"] = dtSummary.Compute("SUM(差旅费→实际差旅费)", "true"); dtSummary.Rows[dtSummary.Rows.Count - 1]["差旅费→实际用车费"] = dtSummary.Compute("SUM(差旅费→实际用车费)", "true"); gv_planList.DataSource = dtSummary; gv_planList.DataBind(); MatrixTable.GridViewMatric(gv_planList); gv_planList.Rows[gv_planList.Rows.Count - 1].Cells[0].Text = ""; break; } }
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(); } }
private void BindGrid() { int month = 0, organizecity = 0, staff = 0; if (int.TryParse(tr_OrganizeCity.SelectValue, out organizecity) && int.TryParse(ddl_AccountMonth.SelectedValue, out month)) { int.TryParse(select_Staff.SelectValue, out staff); //员工不选时,查看该片区所有人员跟踪表 DataTable dt = TC_TrackCardBLL.GetListByStaff(month, organizecity, staff, ddl_ISWanYuan.SelectedValue); if (dt.Rows.Count > 0) { #region 矩阵表 dt = MatrixTable.Matrix(dt, new string[] { "CityName1", "CityName2", "CityName3", "CityName4", "CityName5", "StaffName", "ClientName", "ShortName", "IsPromote", "Promotor", "Salesroom", "PreMonthSalesVolume", "TargetDate01", "TargetDate02", "TargetDate04", "TargetDate03" }, new string[] { "D", "TrackDate" }, new string[] { "Data01", "Data02", "Data04", "Data03" }, false, true); dt = MatrixTable.ColumnSummaryTotal(dt, new int[] { 1 }, new string[] { "Data01", "Data02", "Data04", "Data03" }); #endregion #region 加入行小计 MatrixTable.TableAddRowSubTotal_Matric(dt, new string[] { "CityName1", "CityName2", "CityName3", "CityName4", "CityName5", "StaffName" }, 11, new string[] { "Data01", "Data02", "Data04", "Data03" }, true); #endregion #region 统计完成率 dt.Columns.Add(new DataColumn("完成率%→Data01", Type.GetType("System.Int32"), "IIF([TargetDate01]=0,0,[合计→Data01]/[TargetDate01])*100")); dt.Columns.Add(new DataColumn("完成率%→Data02", Type.GetType("System.Int32"), "IIF([TargetDate02]=0,0,[合计→Data02]/[TargetDate02])*100")); dt.Columns.Add(new DataColumn("完成率%→Data04", Type.GetType("System.Int32"), "IIF([TargetDate04]=0,0,[合计→Data04]/[TargetDate04])*100")); dt.Columns.Add(new DataColumn("完成率%→Data03", Type.GetType("System.Int32"), "IIF([TargetDate03]=0,0,[合计→Data03]/[TargetDate03])*100")); #endregion #region 统计排名 dt.Columns.Add(new DataColumn("排名→Data01", Type.GetType("System.Int32"))); dt.Columns.Add(new DataColumn("排名→Data02", Type.GetType("System.Int32"))); dt.Columns.Add(new DataColumn("排名→Data04", Type.GetType("System.Int32"))); dt.Columns.Add(new DataColumn("排名→Data03", Type.GetType("System.Int32"))); DataView dv = new DataView(dt); dv.RowFilter = "CityName1<>'总计' AND CityName2<>'小计' AND CityName3<>'小计' AND CityName4<>'小计' AND CityName5<>'小计' AND StaffName<>'小计' AND ClientName<>'小计'"; int prevalue = -1, presort = 0; #region Data01完成率排名 dv.Sort = "[完成率%→Data01] DESC"; for (int i = 0; i < dv.Count; i++) { if ((int)dv[i]["完成率%→Data01"] == prevalue) { dv[i]["排名→Data01"] = presort; } else { dv[i]["排名→Data01"] = i + 1; prevalue = (int)dv[i]["完成率%→Data01"]; presort = i + 1; } } #endregion #region Data02完成率排名 dv.Sort = "[完成率%→Data02] DESC"; for (int i = 0; i < dv.Count; i++) { if ((int)dv[i]["完成率%→Data02"] == prevalue) { dv[i]["排名→Data02"] = presort; } else { dv[i]["排名→Data02"] = i + 1; prevalue = (int)dv[i]["完成率%→Data02"]; presort = i + 1; } } #endregion #region Data04完成率排名 dv.Sort = "[完成率%→Data04] DESC"; for (int i = 0; i < dv.Count; i++) { if ((int)dv[i]["完成率%→Data04"] == prevalue) { dv[i]["排名→Data04"] = presort; } else { dv[i]["排名→Data04"] = i + 1; prevalue = (int)dv[i]["完成率%→Data04"]; presort = i + 1; } } #endregion #region Data03完成率排名 dv.Sort = "[完成率%→Data03] DESC"; for (int i = 0; i < dv.Count; i++) { if ((int)dv[i]["完成率%→Data03"] == prevalue) { dv[i]["排名→Data03"] = presort; } else { dv[i]["排名→Data03"] = i + 1; prevalue = (int)dv[i]["完成率%→Data03"]; presort = i + 1; } } #endregion #endregion #region 列表字段名称替换 dt.Columns["CityName1"].ColumnName = "部"; dt.Columns["CityName2"].ColumnName = "省区"; dt.Columns["CityName3"].ColumnName = "区域"; dt.Columns["CityName4"].ColumnName = "城市"; dt.Columns["CityName5"].ColumnName = "县城"; dt.Columns["ClientName"].ColumnName = "门店全称"; dt.Columns["ShortName"].ColumnName = "门店简称"; dt.Columns["StaffName"].ColumnName = "员工"; dt.Columns["IsPromote"].ColumnName = "促销店"; dt.Columns["Promotor"].ColumnName = "促销员"; dt.Columns["Salesroom"].ColumnName = "门店容量(元)"; dt.Columns["PreMonthSalesVolume"].ColumnName = "上月销量"; dt.Columns["TargetDate01"].ColumnName = "任务→ →销量(元)"; dt.Columns["TargetDate02"].ColumnName = "任务→ →档案数(自抢档案)"; dt.Columns["TargetDate04"].ColumnName = "任务→ →档案数(NE提供档案)"; dt.Columns["TargetDate03"].ColumnName = "任务→ →送货上门数"; foreach (DataColumn c in dt.Columns) { if (c.ColumnName.EndsWith("→Data01")) { c.ColumnName = c.ColumnName.Replace("→Data01", "→销量(元)"); } if (c.ColumnName.EndsWith("→Data02")) { c.ColumnName = c.ColumnName.Replace("→Data02", "→档案数(自抢档案)"); } if (c.ColumnName.EndsWith("→Data04")) { c.ColumnName = c.ColumnName.Replace("→Data04", "→档案数(NE提供档案)"); } if (c.ColumnName.EndsWith("→Data03")) { c.ColumnName = c.ColumnName.Replace("→Data03", "→送货上门数"); } } #endregion gv_List.DataSource = dt; gv_List.DataBind(); gv_List.Width = new Unit(dt.Columns.Count * 50); MatrixTable.GridViewMatric(gv_List); } else { gv_List.DataBind(); } } }
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.GetFLFeeSummary(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); MatrixTable.GridViewMergSampeValueRow(gv_List, 1); #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 }