/// <summary> /// 推广--获取用户具有权限的城市过滤条件对象 /// </summary> /// <param name="userid"></param> /// <returns></returns> public BusinessFilter GetCityFilterByPromotion(int userid) { Wicresoft.DataAccess.SQLServer dal = new Wicresoft.DataAccess.SQLServer(); BusinessFilter filter = new BusinessFilter("Dictionary"); string sql = string.Format("select a.FK_Center,b.parent as CityID,b.name from AssistantDataPermission as a left join [Dictionary] as b on a.fk_center = b.pkid where a.fk_center <> -1 and a.fk_Assistant =" + userid); DataTable dt = dal.ExcuteDataTable(new DataTable(), sql, CommandType.Text); //if (temp != null && temp.Rows.Count != 0) //{ // filter.AddCustomerFilter("Dictionary.PKID IN (" + temp.Rows[0]["CityID"].ToString() + ")", AndOr.AND); //} string strCity = string.Empty; for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["CityID"] != null && dt.Rows[i]["CityID"].ToString() != string.Empty) { strCity += "," + dt.Rows[i]["CityID"].ToString(); } } if (strCity != string.Empty) { strCity = strCity.Substring(1, strCity.Length - 1); filter.AddCustomerFilter("Dictionary.PKID IN (" + strCity + ")", AndOr.AND); } return(filter); }
/// <summary> /// 推广--获取用户具有权限的中心过滤条件对象 /// </summary> /// <param name="userid"></param> /// <returns></returns> public BusinessFilter GetCenterFilterByPromotion(string userid) { Wicresoft.DataAccess.SQLServer dal = new Wicresoft.DataAccess.SQLServer(); BusinessFilter filter = new BusinessFilter("Dictionary"); filter.AddCustomerFilter("Dictionary.PKID IN (" + GetCenterFilterByPromotion(int.Parse(userid)) + ")", AndOr.AND); return(filter); }
/// <summary> /// 获取用户具有权限的中心过滤条件对象 /// </summary> /// <param name="userid"></param> /// <returns></returns> public BusinessFilter GetAuthorizedCenterFilter(int userid) { Wicresoft.DataAccess.SQLServer dal = new Wicresoft.DataAccess.SQLServer(); string sql = string.Format(sp_GetUserDataPermissionOnCenter, userid); string centerpkids = dal.ExecuteScalar(sql, CommandType.Text).ToString(); BusinessFilter filter = new BusinessFilter("Dictionary"); filter.AddCustomerFilter("Dictionary.PKID IN (" + centerpkids + ")", AndOr.AND); return(filter); }
//Qick 获取城市列表 2009-02-06 public DataTable GetCityListNew(out int totalCount, int pageSize, int pageIndex, Common.OrderByType obType, BusinessFilter filter) { Common commonRule = new Common(); BusinessFilter flt = new BusinessFilter("DictionaryAD"); flt.AddFilterItem("IsValid", "1", Operation.Equal, FilterType.NumberType, AndOr.AND); flt.AddCustomerFilter("(DictionaryAD.Level =1 OR DictionaryAD.Level =0)", AndOr.AND); flt.AddFilter(filter, AndOr.AND); BusinessObjectCollection boc = new BusinessObjectCollection("DictionaryAD"); boc.SessionInstance = new Session(); boc.AddFilter(flt); DataSet ds = boc.GetPagedRecords(pageIndex, pageSize, "PKID", (obType == Common.OrderByType.ASC) ? true : false); totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); return(ds.Tables[1]); }
/// <summary> /// 获取行政区列表 /// </summary> /// <param name="totalCount">记录总数</param> /// <param name="pageSize">页大小(一次取出的记录数)</param> /// <param name="pageIndex">页码(1开始)</param> /// <param name="obType">排续类型</param> /// <param name="subfilter">筛选条件对象</param> /// <returns>结果集</returns> public DataTable GetDistrictList(out int totalCount, int pageSize, int pageIndex, Common.OrderByType obType, BusinessFilter subfilter) { BusinessRule.BaseData.Region rule = new Region(); Wicresoft.Session.Session session = new Wicresoft.Session.Session(); BusinessObjectCollection boc = new BusinessObjectCollection("District"); boc.SessionInstance = session; BusinessFilter filter = new BusinessFilter("District"); filter.AddFilterItem("IsValid", "1", Operation.Equal, FilterType.NumberType, AndOr.AND); filter.AddCustomerFilter("District.FK_Dictionary IN (" + rule.GetAuthorizedCitiesPKID(GlobalFacade.SystemContext.GetContext().UserID) + ")", AndOr.AND); if (subfilter != null) { filter.AddFilter(subfilter, AndOr.AND); } boc.AddFilter(filter); DataSet ds = boc.GetPagedRecords(pageIndex, pageSize, "PKID", (obType == Common.OrderByType.ASC)?true:false); totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); return(ds.Tables[1]); }
/// <summary> /// Generate filter according to each query item(along with filter description) /// </summary> public BusinessFilter GetBusinessFilter(out string filterDescription) { BusinessFilter queryFilter = new BusinessFilter(this.BusinessObjectView.BusinessObjectName); HtmlTable queryTable = this.queryHolder.FindControl("__queryTable") as HtmlTable; AndOr filterJunction = GetFilterJunction(queryTable); StringBuilder queryDesc = new StringBuilder(); // queryTable的最后一行是queryType(满足全部、满足任一) for (int i = 0; i < queryTable.Rows.Count - 1; i++) { HtmlTableRow queryItem = queryTable.FindControl("__queryitem_" + i.ToString()) as HtmlTableRow; HtmlInputCheckBox checkbox = queryTable.FindControl("__checkbox_" + i.ToString()) as HtmlInputCheckBox; if (checkbox.Checked) { LiteralControl desc = queryTable.FindControl("__desc_" + i.ToString()) as LiteralControl; ViewItemDisplayType itemType = (ViewItemDisplayType)int.Parse(queryItem.Attributes["displayType"]); if (itemType == ViewItemDisplayType.CheckBox) { // Filter HtmlInputCheckBox boolCtl = queryTable.FindControl("__bool_" + i.ToString()) as HtmlInputCheckBox; if (queryItem.Attributes["isVirtual"] != "true") { queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], Convert.ToInt32(boolCtl.Checked).ToString(), Operation.Equal, FilterType.NumberType, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } else { queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + "=" + Convert.ToInt32(boolCtl.Checked).ToString(), queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } // Desc queryDesc.AppendFormat("{0}:{1}.", desc.Text, boolCtl.Checked); } else if (itemType == ViewItemDisplayType.Literal) { // Filter HtmlInputText textbox = queryTable.FindControl("__textbox_" + i.ToString()) as HtmlInputText; if (textbox.Value != string.Empty) { GlobalFacade.PageContext pgCtx = GlobalFacade.SystemContext.GetContext().GetPageContext(this.openerId); pgCtx.Parms.Clear(); RadioButtonList FuzzyEnquiry = queryTable.FindControl("__Fuzzytxt_" + i.ToString()) as RadioButtonList; if (queryItem.Attributes["isVirtual"] != "true") { if (FuzzyEnquiry != null) { if (FuzzyEnquiry.SelectedIndex == 0) { queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], textbox.Value.Replace("'", "''"), Operation.Like, FilterType.StringType, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } else { queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], textbox.Value.Replace("'", "''"), Operation.Equal, FilterType.StringType, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } } else { queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], textbox.Value.Replace("'", "''"), Operation.Like, FilterType.StringType, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } } else { if (FuzzyEnquiry != null) { if (FuzzyEnquiry.SelectedIndex == 0) { queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " LIKE '%" + textbox.Value.Replace("'", "''") + "%'", queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } else { queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " = '" + textbox.Value.Replace("'", "''") + "'", queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } } else { queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " LIKE '%" + textbox.Value.Replace("'", "''") + "%'", queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } } // if(queryItem.Attributes["isVirtual"] != "true") // queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], // textbox.Value.Replace("'", "''"), Operation.Like, FilterType.StringType, filterJunction); // 2007-4-5, Tony, 将单引号替换成两个单引号,避免查询时报错 // else // { // /* Andy Modify 2008-07-30 只有Client的Mobile、TelePhone、ChineseName、EnglishName用前匹配模糊查询 */ // if(this.BusinessObjectView.BusinessObjectName.ToLower() == "client") // { // if( queryItem.Attributes["fieldName"].ToLower().Trim().IndexOf("mobile") == -1 && // queryItem.Attributes["fieldName"].ToLower().Trim().IndexOf("telephone") == -1 && // queryItem.Attributes["fieldName"].ToLower().Trim().IndexOf("chinesename") == -1 && // queryItem.Attributes["fieldName"].ToLower().Trim().IndexOf("englishname") == -1) // { // queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " LIKE '%" + textbox.Value.Replace("'", "''") + "%'", filterJunction);// 2007-4-5, Tony, 将单引号替换成两个单引号,避免查询时报错 // } // else // queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " LIKE '" + textbox.Value.Replace("'", "''") + "%'", filterJunction); // } // else // queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " LIKE '%" + textbox.Value.Replace("'", "''") + "%'", filterJunction);// 2007-4-5, Tony, 将单引号替换成两个单引号,避免查询时报错 // } // Desc queryDesc.AppendFormat("{0}:{1}.", desc.Text, textbox.Value); } } else if (itemType == ViewItemDisplayType.DateTime) { // Filter HtmlInputText beginTime = queryTable.FindControl("__beginTime_" + i.ToString()) as HtmlInputText; HtmlInputText endTime = queryTable.FindControl("__endTime_" + i.ToString()) as HtmlInputText; BusinessFilter subFilter = new BusinessFilter(this.BusinessObjectView.BusinessObjectName); if (queryItem.Attributes["isVirtual"] != "true") { if (beginTime.Value != string.Empty) { subFilter.AddFilterItem(queryItem.Attributes["fieldName"], beginTime.Value, Operation.NotSmaller, FilterType.StringType, AndOr.AND); } if (endTime.Value != string.Empty) { subFilter.AddFilterItem(queryItem.Attributes["fieldName"], endTime.Value, Operation.Smaller, FilterType.StringType, AndOr.AND); } //endTime.Value, Operation.NotLarger, FilterType.StringType, AndOr.AND); } else { if (beginTime.Value != string.Empty) { subFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + ">= '" + beginTime.Value + "'", AndOr.AND); } if (endTime.Value != string.Empty) { subFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + "< '" + endTime.Value + "'", AndOr.AND); } //subFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + "<= '" + endTime.Value + "'", AndOr.AND); } queryFilter.AddFilter(subFilter, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); if (beginTime.Value != string.Empty || endTime.Value != string.Empty) { // Desc queryDesc.AppendFormat("{0}:从{1}至{2}.", desc.Text, beginTime.Value, endTime.Value); } } else if (itemType == ViewItemDisplayType.SingleObject) { // Filter GridPicker ucGridPicker = queryTable.FindControl("__ucGridPicker_" + i.ToString()) as GridPicker; if (ucGridPicker.SelectedValue != string.Empty) { if (queryItem.Attributes["isVirtual"] != "true") { queryFilter.AddFilterItem(queryItem.Attributes["fieldName"], ucGridPicker.SelectedValue, Operation.Equal, FilterType.NumberType, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } else { queryFilter.AddCustomerFilter(queryItem.Attributes["fieldName"] + " = " + ucGridPicker.SelectedValue, queryFilter.Filter != string.Empty ? filterJunction : AndOr.AND); } // Desc queryDesc.AppendFormat("{0}:{1}.", desc.Text, ucGridPicker.SelectedText); } } } } if (queryDesc.Length != 0) { filterDescription = queryDesc.AppendFormat("({0})", (filterJunction == AndOr.AND) ? "满足全部条件" : "满足任一条件").ToString(); } else { filterDescription = string.Empty; } BusinessFilter parentQueryFilter = new BusinessFilter(this.BusinessObjectView.BusinessObjectName); parentQueryFilter.AddCustomerFilter("1=1", AndOr.AND); parentQueryFilter.AddFilter(queryFilter, AndOr.AND); this.SaveQueryStatus(); return(parentQueryFilter); }
//Qick新加方法 public DataTable GetCityListAD(out int totalCount, int pageSize, int pageIndex, Common.OrderByType obType, BusinessFilter filter) { //获取没有中心的城市 string sql = "select pkid from dictionaryAd where pkid in (select pkid from dictionaryAd where [name] not in (select [name] from dictionary where isvalid =1 and ([level]=1 or [level]=0)) and isvalid = 1 and ([level]=1 or [level]=0)) and ([level]=1 or [level]=0)"; Wicresoft.Session.Session session = new Wicresoft.Session.Session(); DataTable dt = session.SqlHelper.ExcuteDataTable(null, sql, CommandType.Text); string NOPKIDs = ""; for (int i = 0; i < dt.Rows.Count; i++) { NOPKIDs += "," + dt.Rows[i][0]; } //获取登陆用户拥有权限的城市 /* Andy Modify 2009-12-17 用DictionaryAD表的PKID和FK_Dictionary过滤不同值 */ BusinessRule.BaseData.Region regionRule = new BusinessRule.BaseData.Region(); string cityPKIDs = regionRule.GetAuthorizedCitiesPKID(GlobalFacade.SystemContext.GetContext().UserID).ToString(); if (cityPKIDs.IndexOf("2,") != -1) { cityPKIDs += ",1"; } if (NOPKIDs != string.Empty) { NOPKIDs = NOPKIDs.Substring(1, NOPKIDs.Length - 1); } // string cityPKIDs = ","+regionRule.GetAuthorizedCitiesPKID(GlobalFacade.SystemContext.GetContext().UserID).ToString()+","; // if(cityPKIDs.IndexOf(",2,")!=-1) // { // cityPKIDs=cityPKIDs.Substring(1,cityPKIDs.Length-2); // cityPKIDs+=",1"; // if(NOPKIDs!=null&&NOPKIDs!=""&&NOPKIDs!=string.Empty) // { // cityPKIDs+=NOPKIDs; // } // } // else // { // cityPKIDs=cityPKIDs.Substring(1,cityPKIDs.Length-2); // } Common commonRule = new Common(); BusinessFilter flt = new BusinessFilter("DictionaryAD"); flt.AddCustomerFilter("(DictionaryAD.PKID IN(" + NOPKIDs + ") OR DictionaryAD.FK_Dictionary IN (" + cityPKIDs + "))", AndOr.AND); // flt.AddCustomerFilter("(DictionaryAD.PKID IN(" + cityPKIDs + ") OR DictionaryAD.PKID IN (" + cityPKIDs + "))", AndOr.AND); flt.AddFilterItem("IsValid", "1", Operation.Equal, FilterType.NumberType, AndOr.AND); flt.AddFilter(filter, AndOr.AND); // Add Data Permission // flt.AddFilter(GetAuthorizedCityFilter(GlobalFacade.SystemContext.GetContext().UserID), AndOr.AND); BusinessObjectCollection boc = new BusinessObjectCollection("DictionaryAD"); boc.SessionInstance = new Session(); boc.AddFilter(flt); DataSet ds = boc.GetPagedRecords(pageIndex, pageSize, "PKID", (obType == Common.OrderByType.ASC) ? true : false); totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); return(ds.Tables[1]); }