/// <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]);
}
