private void BindGrid() { string ConditionStr = " PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + ")"; if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1") { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_List.SelectedValue; ConditionStr += " AND PDT_Product.Category IN (" + _categoryids + ")"; } if (tbx_SearchKey.Text != "") { ConditionStr += " AND ("; ConditionStr += " PDT_Product.FullName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR PDT_Product.ShortName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR PDT_Product.BarCode LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR PDT_Product.FactoryName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR PDT_Product.FactoryCode LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += ")"; } gv_List.ConditionString = ConditionStr; gv_List.BindGrid(); }
protected void bt_Add_Click(object sender, EventArgs e) { if (tr_Position.SelectValue != "0") { int position = int.Parse(tr_Position.SelectValue); int beginday = int.Parse(tbx_BeginDay.Text); int endday = int.Parse(tbx_EndDay.Text); EWF_Flow_InitPositionBLL bll = new EWF_Flow_InitPositionBLL(); bll.Model.App = (Guid)ViewState["AppID"]; bll.Model.Position = position; bll.Model.BeginDay = beginday; bll.Model.EndDay = endday; bll.Add(); if (cb_IncludeChild.Checked) { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Org_Position", "ID", "SuperID", tr_Position.SelectValue); foreach (DataRow dr in dt.Rows) { bll.Model.ID = Guid.NewGuid(); bll.Model.Position = (int)dr["ID"]; bll.Add(); } } } BindGrid(); }
private void BindGrid() { if (ViewState["Details"] != null) { ListTable <PBM_DeliveryDetail> Details = (ListTable <PBM_DeliveryDetail>)ViewState["Details"]; int category = 1; int.TryParse(tr_Category.SelectValue, out category); if (category > 1) { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.PDT_Category", "ID", "SuperID", category.ToString()); List <int> categorys = new List <int>(); foreach (DataRow dr in dt.Rows) { categorys.Add((int)dr["ID"]); } categorys.Add(category); gv_List.BindGrid(Details.GetListItem().Where(p => categorys.Contains(new PDT_ProductBLL(p.Product).GetProductExtInfo((int)Session["OwnerClient"]).Category)).ToList()); } else { gv_List.BindGrid(Details.GetListItem()); } if (Details.GetListItem().Count == 0) { bt_Confirm.Visible = false; } } }
protected void tr_OfficialCity_Selected(object sender, MCSControls.MCSWebControls.SelectedEventArgs e) { cbl_OfficialList.Items.Clear(); DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Addr_OfficialCity", "ID", "SuperID", tr_OfficialCity.SelectValue); for (int i = 0; i < dt.Rows.Count; i++) { Addr_OfficialCityBLL city = new Addr_OfficialCityBLL((int)dt.Rows[i]["ID"]); if (city.Model.Level == 3) { string fullname = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", city.Model.ID); ListItem item = new ListItem(fullname, city.Model.ID.ToString()); IList <Addr_OfficialCityInOrganizeCity> organizecitys = Addr_OfficialCityInOrganizeCityBLL.GetModelList("OfficialCity=" + city.Model.ID.ToString()); if (organizecitys.Count > 0) { Addr_OrganizeCity organizecity = new Addr_OrganizeCityBLL(organizecitys[0].OrganizeCity).Model; if (organizecity != null) { item.Text += "; 已归属于片区:【" + organizecity.Name + "】"; } item.Enabled = false; } cbl_OfficialList.Items.Add(item); } } }
protected void gv_ListDetail_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { int id = (int)gv_ListDetail.DataKeys[e.Row.RowIndex][0]; UC_GridView gv_Detail = (UC_GridView)e.Row.FindControl("gv_Detail"); if (gv_Detail != null) { IList <FNA_FeeWriteOffDetail> lists = new FNA_FeeWriteOffBLL(id).Items; //费用类型 int accounttile = 0; int.TryParse(tr_AccountTitle.SelectValue, out accounttile); if (ddl_FeeType.SelectedValue != "0" && !(accounttile > 1)) { IList <AC_AccountTitleInFeeType> titles = AC_AccountTitleInFeeTypeBLL.GetModelList("FeeType=" + ddl_FeeType.SelectedValue); int[] ids = new int[titles.Count]; for (int i = 0; i < titles.Count; i++) { ids[i] = titles[i].AccountTitle; } lists = lists.Where(p => ids.Contains(p.AccountTitle)).ToList(); } //费用科目 if (accounttile > 1) { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString()); int[] ids = new int[dt.Rows.Count + 1]; for (int i = 0; i < dt.Rows.Count; i++) { ids[i] = (int)dt.Rows[i]["ID"]; } ids[ids.Length - 1] = accounttile; lists = lists.Where(p => ids.Contains(p.AccountTitle)).ToList(); } //核销金额判断 decimal _cost = 0; decimal.TryParse(tbx_WriteOffCost.Text, out _cost); if (_cost != 0) { if (ddl_WriteOffCostOP.SelectedValue == ">") { lists = lists.Where(p => p.WriteOffCost + p.AdjustCost > _cost).ToList(); } else if (ddl_WriteOffCostOP.SelectedValue == "<") { lists = lists.Where(p => p.WriteOffCost + p.AdjustCost < _cost).ToList(); } } gv_Detail.BindGrid(lists); } } }
private void BindKBGridView(UC_GridView gv_KB, int Catalog) { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_OA.dbo.KB_Catalog", "ID", "SuperID", Catalog.ToString()); string catalogs = Catalog.ToString(); foreach (DataRow dr in dt.Rows) { catalogs += "," + dr["ID"].ToString(); } string condition = "ID IN(Select TOP 3 ID FROM MCS_OA.dbo.KB_Article WHERE Catalog IN (" + catalogs + ") AND IsDelete = 'N' AND HasApproved = 'Y' ORDER BY ApproveTime DESC)"; gv_KB.BindGrid(KB_ArticleBLL.GetModelList(condition)); gv_KB.Attributes["CatalogID"] = Catalog.ToString(); }
public string GetAllChildPosition() { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_OA.dbo.KB_Catalog", "ID", "SuperID", _m.ID.ToString()); string ids = ""; for (int i = 0; i < dt.Rows.Count; i++) { if (ids != "") { ids += ","; } ids += dt.Rows[i]["ID"]; } return(ids); }
private void BindGrid() { string ConditionStr = "INV_Inventory.Quantity <> 0 "; if (ddl_WareHouse.SelectedValue != "0") { ConditionStr += " AND Inv_Inventory.WareHouse = " + ddl_WareHouse.SelectedValue; } else { ConditionStr += " AND INV_Inventory.WareHouse IN (SELECT ID FROM MCS_CM.dbo.CM_WareHouse WHERE Client=" + Session["OwnerClient"].ToString() + ")"; } if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1") { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_List.SelectedValue; ConditionStr += " AND INV_Inventory.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE Category IN (" + _categoryids + "))"; } if (tbx_SearchKey.Text != "") { ConditionStr += " AND (INV_Inventory.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE "; ConditionStr += " FullName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR ShortName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR BarCode LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR FactoryName LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += "OR FactoryCode LIKE '%" + tbx_SearchKey.Text + "%' "; ConditionStr += ") )"; } IList <INV_Inventory> lists = INV_InventoryBLL.GetModelList(ConditionStr); ViewState["TotalInventory"] = lists.Sum(p => p.Price * p.Quantity); gv_List.BindGrid(lists); gv_List.Columns[gv_List.Columns.Count - 1].Visible = ddl_WareHouse.SelectedValue != "0"; bt_Adjust.Enabled = ddl_WareHouse.SelectedValue != "0"; }
protected void bt_AddAll_Click(object sender, EventArgs e) { string ConditionStr = " PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + ")"; if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1") { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_List.SelectedValue; ConditionStr += " AND PDT_Product.Category IN (" + _categoryids + ")"; } int count = 0; IList <PDT_Product> product = PDT_ProductBLL.GetModelList(ConditionStr); foreach (PDT_Product p in product) { int id = p.ID; PDT_ProductBLL bll = new PDT_ProductBLL(id); PDT_ProductExtInfo extinfo = new PDT_ProductExtInfo(); extinfo.Code = bll.Model.FactoryCode == "" ? bll.Model.Code : bll.Model.FactoryCode; extinfo.Supplier = (int)Session["OwnerClient"]; bll.SetProductExtInfo(extinfo); count++; } if (count > 0) { MessageBox.Show(this, string.Format("成功将{0}个商品加入经营范围!", count)); BindGrid(); } else { MessageBox.Show(this, "请选择要加入经营产商品!"); } }
private void BindGrid() { IList <Right_Assign> list; if (tr_Module.SelectedValue != "") { DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Right_Module", "ID", "SuperID", tr_Module.SelectedValue.Substring(1)); string ids = tr_Module.SelectedValue.Substring(1) + ","; foreach (DataRow row in dt.Rows) { ids += row[0].ToString() + ","; } ids = ids.Substring(0, ids.Length - 1); list = Right_Assign_BLL.GetModelList("RoleName='" + tr_Role.SelectedValue + "' AND Module in (" + ids + ")"); } else { list = (IList <Right_Assign>)ViewState["RightAssign"]; } gv_list.TotalRecordCount = list.Count; gv_list.PageIndex = (int)ViewState["PageIndex"]; gv_list.BindGrid <Right_Assign>(list); }
protected void btn_addsearch_Click(object sender, EventArgs e) { if (ddl_Field.SelectedValue == "0") { return; } //添加条件 ListItem lt = new ListItem(); #region 条件选择 UD_TableList table = new UD_TableListBLL(new Guid(ddl_TableName.SelectedValue), true).Model; if (table == null) { return; } UD_ModelFields modelfield = new UD_ModelFieldsBLL(new Guid(ddl_Field.SelectedValue), true).Model; if (modelfield == null) { return; } //字段全称 string fielddisplayname = table.DisplayName + "." + modelfield.DisplayName; string fieldfullname = ""; if (modelfield.Flag == "Y") { fieldfullname = table.Name + "." + modelfield.FieldName; //实体字段 } else { fieldfullname = "MCS_SYS.dbo.UF_Spilt(" + table.Name + ".ExtPropertys,'|'," + modelfield.Position.ToString() + ")"; } if (rbl_ValueFrom.SelectedValue == "M") { #region 从手工设定中取值 switch (modelfield.RelationType) { case 1: #region 字典关联 if (cbl_SearchValue.SelectedIndex != -1) { lt.Text = fielddisplayname + " 包含 ( "; lt.Value = fieldfullname + " IN ( "; foreach (ListItem item in cbl_SearchValue.Items) { if (item.Selected) { lt.Text += item.Text + ","; lt.Value += "'" + item.Value + "',"; } } lt.Text = lt.Text.Substring(0, lt.Text.Length - 1); lt.Value = lt.Value.Substring(0, lt.Value.Length - 1); lt.Text += ")"; lt.Value += ")"; } break; #endregion case 2: #region 实体表关联 lt.Text = fielddisplayname + " 为 "; lt.Value = fieldfullname + " = "; if (!string.IsNullOrEmpty(modelfield.SearchPageURL)) { //通过查询控件查询 lt.Text += MCSSelectControl1.SelectText; lt.Value += "'" + MCSSelectControl1.SelectValue + "'"; } else if (new UD_TableListBLL(modelfield.RelationTableName).Model.TreeFlag == "Y") { #region 通过树形结构查询 if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity") { if (ddl_TreeLevel.SelectedValue == "0") { lt.Text += MCSTreeControl1.SelectText; lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue; } else if (ddl_TreeLevel.SelectedValue == "100") { //所属于 lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText; lt.Value = "MCS_SYS.dbo.UF_IsChildOrganizeCity(" + MCSTreeControl1.SelectValue + "," + fieldfullname + ") = 0"; } else { //指定级别上级 lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + MCSTreeControl1.SelectText; lt.Value = "MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + MCSTreeControl1.SelectValue; } } else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity") { if (ddl_TreeLevel.SelectedValue == "0") { lt.Text += MCSTreeControl1.SelectText; lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue; } else if (ddl_TreeLevel.SelectedValue == "100") { //所属于 lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText; lt.Value = "MCS_SYS.dbo.UF_IsChildOfficialCityCity(" + MCSTreeControl1.SelectValue + "," + fieldfullname + ") = 0"; } else { //指定级别上级 lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + MCSTreeControl1.SelectText; lt.Value = "MCS_SYS.dbo.UF_GetSuperOfficialCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + MCSTreeControl1.SelectValue; } } else { lt.Text += MCSTreeControl1.SelectText; lt.Value = fieldfullname + " IN ("; DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue); foreach (DataRow dr in dt.Rows) { lt.Value += "'" + dr[0].ToString() + "',"; } lt.Value += "'" + MCSTreeControl1.SelectValue + "')"; } #endregion } else { #region 其他关联方式的实体表 if (cbl_SearchValue.SelectedIndex != -1) { lt.Text = fielddisplayname + " 包含 ( "; lt.Value = fieldfullname + " IN ( "; foreach (ListItem item in cbl_SearchValue.Items) { if (item.Selected) { lt.Text += item.Text + ","; lt.Value += "'" + item.Value + "',"; } } lt.Text = lt.Text.Substring(0, lt.Text.Length - 1); lt.Value = lt.Value.Substring(0, lt.Value.Length - 1); lt.Text += ")"; lt.Value += ")"; } #endregion } break; #endregion default: #region 非关联字段 lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " (" + tbx_searchvalue.Text + ")"; lt.Value = fieldfullname + " = "; if (ddl_op.SelectedValue != "like") { if (tbx_searchvalue.Text.StartsWith("@")) { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + tbx_searchvalue.Text; } else { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'"; } } else { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '%" + tbx_searchvalue.Text + "%'"; } break; #endregion } #endregion } else { #region 从参数中取值 switch (modelfield.RelationType) { case 1: #region 字典关联 lt.Text = fielddisplayname + " = " + ddl_Param.SelectedValue; lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue; #endregion break; case 2: #region 实体表关联 lt.Text = fielddisplayname + " 为 "; lt.Value = fieldfullname + " = "; if (ddl_TreeLevel.Visible) { #region 管理片区及行政城市选项 if (ddl_TreeLevel.SelectedValue == "0") { //当前级 lt.Text += ddl_Param.SelectedValue; lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue; } else if (ddl_TreeLevel.SelectedValue == "100") { //所属于 lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + ddl_Param.SelectedValue; if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity") { lt.Value = "MCS_SYS.dbo.UF_IsChildOrganizeCity(" + ddl_Param.SelectedValue + "," + fieldfullname + ") = 0"; } else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity") { lt.Value = "MCS_SYS.dbo.UF_IsChildOfficialCityCity(" + ddl_Param.SelectedValue + "," + fieldfullname + ") = 0"; } } else { lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + ddl_Param.SelectedValue; //指定级别上级 if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity") { lt.Value = "MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + ddl_Param.SelectedValue; } else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity") { lt.Value = "MCS_SYS.dbo.UF_GetSuperOfficialCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + ddl_Param.SelectedValue; } } #endregion } else { lt.Text += ddl_Param.SelectedValue; lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue; } break; #endregion default: #region 非关联字段 lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " (" + ddl_Param.SelectedValue + ")"; lt.Value = fieldfullname + " = "; if (ddl_op.SelectedValue != "like") { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + ddl_Param.SelectedValue; } else { lt.Value = fieldfullname + " LIKE '%'+" + ddl_Param.SelectedValue + "+'%'"; } break; #endregion } #endregion } #endregion if (!string.IsNullOrEmpty(lt.Text)) { lbx_search.Items.Add(lt); } }
private void BindGrid() { int category = 0; int.TryParse(tr_Category.SelectValue, out category); if (MCSTabControl1.SelectedIndex == 0) { string condition = " 1 = 1 "; #region 获取产品分类 if (category > 1) { condition += " AND PDT_StandardPrice_Detail.Product IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString(); string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_Category.SelectValue; condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")"; condition += ")"; } #endregion if (tbx_ProductText.Text != "") { condition += "AND PDT_StandardPrice_Detail.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE "; condition += " (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR FactoryCode like '%" + tbx_ProductText.Text + "%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))"; condition += ")"; } IList <PDT_StandardPrice_Detail> list = new PDT_StandardPriceBLL((int)ViewState["ID"]).GetDetail(condition); gv_List.BindGrid <PDT_StandardPrice_Detail>(list); } else { //获取非价表产品列表 string condition = "PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_StandardPrice_Detail WHERE PriceID=" + ViewState["ID"].ToString() + ")"; condition += " AND State = 1 AND ApproveFlag = 1"; condition += " AND PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString(); #region 获取产品分类 if (category > 1) { string _categoryids = ""; DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue); for (int i = 0; i < dt.Rows.Count; i++) { _categoryids += dt.Rows[i]["ID"].ToString() + ","; } _categoryids += tr_Category.SelectValue; condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")"; } #endregion condition += ")"; if (tbx_ProductText.Text != "") { condition += " AND (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR Code like '%" + tbx_ProductText.Text + "%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))"; } IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition); gv_NotInList.BindGrid <PDT_Product>(products); } cb_SelectAll.Checked = false; }
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 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() { 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_FeeApply.OrganizeCity IN (" + orgcitys + ")"; } //会计月条件 if ((int)ViewState["AccountTitle"] == 82) { condition += " AND EXISTS (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE FNA_FeeApplyDetail.AccountTitle=82 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID AND BeginMonth=" + ddl_Month.SelectedValue + ")"; } else { condition += " AND FNA_FeeApply.AccountMonth = " + ddl_Month.SelectedValue; } //申请单号 if (tbx_SheetCode.Text != "") { condition += " AND FNA_FeeApply.SheetCode like '%" + tbx_SheetCode.Text + "%'"; } if (!string.IsNullOrEmpty(Select_InsertStaff.SelectValue)) { condition += " AND FNA_FeeApply.InsertStaff=" + Select_InsertStaff.SelectValue; } if (!string.IsNullOrEmpty(select_ApplyClient.SelectValue)) { condition += " AND FNA_FeeApply.Client=" + select_ApplyClient.SelectValue; } //费用类型 if (ddl_FeeType.SelectedValue != "0") { condition += " AND FNA_FeeApply.FeeType = " + ddl_FeeType.SelectedValue; } if (!Right_Assign_BLL.GetAccessRight((string)Session["UserName"], 4703, "Browse")) { //无查看营养教育费用权限 condition += " AND FNA_FeeApply.FeeType <> " + ConfigHelper.GetConfigInt("CSOCostType").ToString(); } //审批状态 if (ddl_State.SelectedValue != "0") { condition += " AND FNA_FeeApply.State = " + ddl_State.SelectedValue; } //核销状态 if (ddl_WriteOffState.SelectedValue == "1") { condition += " AND FNA_FeeApply.State=3 AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AvailCost > 0 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)"; } else if (ddl_WriteOffState.SelectedValue == "2") { condition += " AND FNA_FeeApply.State=3 AND FNA_FeeApply.ID NOT IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AvailCost > 0 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)"; } //会计科目 int accounttile = 0; if (int.TryParse(tr_AccountTitle.SelectValue, out accounttile) && 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(); 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)"; } //标题 if (tbx_Title.Text != "") { condition += " AND MCS_SYS.dbo.UF_Spilt(FNA_FeeApply.ExtPropertys,'|',4) LIKE '%" + tbx_Title.Text + "%'"; } #endregion gv_List.ConditionString = condition; gv_List.BindGrid(); }
protected void btn_addsearch_Click(object sender, EventArgs e) { if (ddl_Field.SelectedValue == "0") { return; } //添加条件 ListItem lt = new ListItem(); #region 条件选择 UD_TableList table = new UD_TableListBLL(new Guid(ddl_TableName.SelectedValue), true).Model; if (table == null) { return; } UD_ModelFields modelfield = null; try { modelfield = new UD_ModelFieldsBLL(new Guid(ddl_Field.SelectedValue), true).Model; } catch { } if (modelfield == null) { //自定义字段 lt.Text = ddl_Field.SelectedItem.Text + " " + ddl_op.SelectedItem.Text + " ('" + tbx_searchvalue.Text + "')"; lt.Value = ddl_Field.SelectedValue + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'"; } else { string fielddisplayname = table.DisplayName + "." + modelfield.DisplayName; string fieldfullname = ""; if (modelfield.Flag == "Y") { fieldfullname = table.Name + "." + modelfield.FieldName; //实体字段 } else { fieldfullname = "MCS_SYS.dbo.UF_Spilt(" + table.Name + ".ExtPropertys,'|'," + modelfield.Position.ToString() + ")"; } if (ddl_op.SelectedValue == "NOTNULL") { lt.Text = fielddisplayname + " 不为空"; lt.Value = " ISNULL(" + fieldfullname + ",'')<>'' "; lbx_search.Items.Add(lt); return; } if (ddl_op.SelectedValue == "ISNULL") { lt.Text = fielddisplayname + " 为空"; lt.Value = " ISNULL(" + fieldfullname + ",'')='' "; lbx_search.Items.Add(lt); return; } switch (modelfield.RelationType) { case 1: #region 字典关联 if (cbl_SearchValue.SelectedIndex != -1) { lt.Text = fielddisplayname + " 包含 ( "; lt.Value = fieldfullname + " IN ( "; foreach (ListItem item in cbl_SearchValue.Items) { if (item.Selected) { lt.Text += item.Text + ","; lt.Value += "'" + item.Value + "',"; } } lt.Text = lt.Text.Substring(0, lt.Text.Length - 1); lt.Value = lt.Value.Substring(0, lt.Value.Length - 1); lt.Text += ")"; lt.Value += ")"; } break; #endregion case 2: #region 实体表关联 lt.Text = fielddisplayname + " 为 "; lt.Value = fieldfullname + " = "; if (!string.IsNullOrEmpty(modelfield.SearchPageURL)) { //通过查询控件查询 lt.Text += MCSSelectControl1.SelectText; lt.Value += "'" + MCSSelectControl1.SelectValue + "'"; } else if (new UD_TableListBLL(modelfield.RelationTableName).Model.TreeFlag == "Y") { #region 通过树形结构查询 if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity" || modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity") { if (ddl_TreeLevel.SelectedValue == "0") { lt.Text += MCSTreeControl1.SelectText; lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue; } else if (ddl_TreeLevel.SelectedValue == "100") { //所属于 lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText; lt.Value = fieldfullname + " IN ("; DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue); foreach (DataRow dr in dt.Rows) { lt.Value += "'" + dr[0].ToString() + "',"; } lt.Value += "'" + MCSTreeControl1.SelectValue + "')"; } } else { lt.Text += MCSTreeControl1.SelectText; lt.Value = fieldfullname + " IN ("; DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue); foreach (DataRow dr in dt.Rows) { lt.Value += "'" + dr[0].ToString() + "',"; } lt.Value += "'" + MCSTreeControl1.SelectValue + "')"; } #endregion } else { #region 其他关联方式的实体表 if (cbl_SearchValue.SelectedIndex != -1) { lt.Text = fielddisplayname + " 包含 ( "; lt.Value = fieldfullname + " IN ( "; foreach (ListItem item in cbl_SearchValue.Items) { if (item.Selected) { lt.Text += item.Text + ","; lt.Value += "'" + item.Value + "',"; } } lt.Text = lt.Text.Substring(0, lt.Text.Length - 1); lt.Value = lt.Value.Substring(0, lt.Value.Length - 1); lt.Text += ")"; lt.Value += ")"; } #endregion } break; #endregion default: #region 非关联字段 lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " ('" + tbx_searchvalue.Text + "')"; lt.Value = fieldfullname + " = "; if (ddl_op.SelectedValue == "StartWith") { lt.Value = fieldfullname + " " + "Like" + " '" + tbx_searchvalue.Text + "%'"; } else if (ddl_op.SelectedValue == "NotStartWith") { lt.Value = fieldfullname + " " + "Not Like" + " '" + tbx_searchvalue.Text + "%'"; } else if (ddl_op.SelectedValue != "like") { if (modelfield.DataType == 4) //日期(datetime) { lt.Value = "CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(DATETIME," + fieldfullname + "),111))" + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'"; } else { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'"; } } else { lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '%" + tbx_searchvalue.Text + "%'"; } break; #endregion } } #endregion if (!string.IsNullOrEmpty(lt.Text)) { lbx_search.Items.Add(lt); } }
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(); } }