/// <summary>
/// 获取指定部门下的所有子对象
/// </summary>
/// <param name="strOrgValues">要求查询的部门对象(父部门标识,多个之间采用","分隔)</param>
/// <param name="soc">查询要求的查询列名称
/// （ＧＵＩＤ、ＵＳＥＲ＿ＧＵＩＤ、ＬＯＧＯＮ＿ＮＡＭＥ、ＯＲＩＧＩＮＡＬ＿ＳＯＲＴ、ＧＬＯＢＡＬ＿ＳＯＲＴ、ＡＬＬ＿ＰＡＴＨ＿ＮＡＭＥ）</param>
/// <param name="iLot">要求查询的数据对象类型（机构、组、人员、兼职对象）</param>
/// <param name="iLod">是否包含被逻辑删除的成员</param>
/// <param name="iDepth">要求查询的层次（最少一层）（0代表全部子对象）</param>
/// <param name="strOrgRankCodeName">查询中要求的机构对象级别</param>
/// <param name="strUserRankCodeName">查询中要求的人员对象级别</param>
/// <param name="strHideType">查询中要求屏蔽的数据(对应于配置文件HideTypes.xml中的配置)</param>
/// <param name="strAttrs">查询中要求获取数据对象的属性类型</param>
/// <param name="iOrgClass">要求展现机构的类型</param>
/// <param name="iOrgType">要求展现机构的属性</param>
/// <returns>获取指定部门下的所有子对象的查询结果</returns>
public static DataSet GetOrganizationChildren(string strOrgValues,
SearchObjectColumn soc,
int iLot,
int iLod,
int iDepth,
string strOrgRankCodeName,
string strUserRankCodeName,
string strHideType,
string strAttrs,
int iOrgClass,
int iOrgType)
{
strAttrs = OGUCommonDefine.CombinateAttr(strAttrs);
SearchOrgChildrenCondition scc = new SearchOrgChildrenCondition(strOrgValues, soc, strAttrs);
scc.ListObjDelete = (ListObjectDelete)iLod;
scc.ListObjType = (ListObjectType)iLot;
scc.Depth = iDepth;
scc.OrgRankCN = strOrgRankCodeName;
scc.UserRankCN = strUserRankCodeName;
scc.HideType = strHideType;
scc.OrgClass = iOrgClass;
scc.OrgType = iOrgType;
return GetOrganizationChildren(scc);
}
/// <summary>
/// 根据指定的查询条件类，生成系统中对于“人员”的查询SQL语句
/// </summary>
/// <param name="scc">查询条件类对象</param>
/// <param name="rootPath">传入的ORIGINAL_SORT</param>
/// <returns>针对于“人员”的查询SQL语</returns>
///
//2009-05-07删除RANK_DEFINE约束，修改ORIGINAL_SORT约束
private static string GetUsersSqlByScc2(SearchOrgChildrenCondition scc, string rootPath)
{
StringBuilder strB = new StringBuilder(1024);
strB.Append(@" SELECT 'USERS' AS OBJECTCLASS, "
+ DatabaseSchema.Instence.GetTableColumns(scc.ObjAttrs, "USERS", "OU_USERS") + @"
FROM USERS , OU_USERS
WHERE USERS.GUID = OU_USERS.USER_GUID
AND ( " + GetSqlSearchParOriginal2("OU_USERS", scc.Depth, rootPath) + " )");
string strListDelete = GetSqlSearchStatus("OU_USERS", scc.ListObjDelete);
if (strListDelete.Length > 0)
strB.Append(" \n AND ( " + strListDelete + " ) ");
if (scc.HideType != string.Empty)
{
string strHideType = GetHideTypeFromXml(scc.HideType, "OU_USERS");
if (strHideType.Length > 0)
strB.Append(" \n AND OU_USERS.ALL_PATH_NAME NOT IN ( " + strHideType + " ) ");
}
if ((scc.ListObjType & ListObjectType.SIDELINE) == 0)
strB.Append(" \n AND OU_USERS.SIDELINE = 0 ");
return strB.ToString();
}
/// <summary>
/// 根据指定的查询条件类，生成系统中对于“人员”的查询SQL语句
/// </summary>
/// <param name="scc">查询条件类对象</param>
/// <param name="rootPath">传入的ORIGINAL_SORT</param>
/// <returns>针对于“人员”的查询SQL语</returns>
///
//2009-05-11
private static string GetUsersSqlByScc(SearchOrgChildrenCondition scc, string rootPath)
{
StringBuilder strB = new StringBuilder(1024);
strB.Append(@" SELECT 'USERS' AS OBJECTCLASS, "
+ DatabaseSchema.Instence.GetTableColumns(scc.ObjAttrs, "USERS", "OU_USERS", "RANK_DEFINE") + @"
FROM USERS JOIN RANK_DEFINE
ON RANK_DEFINE.CODE_NAME = USERS.RANK_CODE
{0}, OU_USERS
WHERE USERS.GUID = OU_USERS.USER_GUID
AND ( " + GetSqlSearchParOriginal2("OU_USERS", scc.Depth, rootPath) + " )");
string strListDelete = GetSqlSearchStatus("OU_USERS", scc.ListObjDelete);
if (strListDelete.Length > 0)
strB.Append(" \n AND ( " + strListDelete + " ) ");
string strRankLimit = string.Empty;
if (scc.UserRankCN.Length > 0)
strRankLimit = " AND RANK_DEFINE.SORT_ID <= (SELECT SORT_ID FROM RANK_DEFINE WHERE CODE_NAME = " + TSqlBuilder.Instance.CheckQuotationMark(scc.UserRankCN, true) + " ) ";
if (scc.HideType != string.Empty)
{
string strHideType = GetHideTypeFromXml(scc.HideType, "OU_USERS");
if (strHideType.Length > 0)
strB.Append(" \n AND OU_USERS.ALL_PATH_NAME NOT IN ( " + strHideType + " ) ");
}
if ((scc.ListObjType & ListObjectType.SIDELINE) == 0)
strB.Append(" \n AND OU_USERS.SIDELINE = 0 ");
return string.Format(strB.ToString(), strRankLimit);
}
/// <summary>
/// 根据指定的查询条件类，生成系统中对于“人员组”的查询SQL语句
/// </summary>
/// <param name="scc">查询条件类对象</param>
/// <param name="rootPath">传入的ORIGINAL_SORT</param>
/// <returns>针对于“人员组”的查询SQL语句</returns>
///
//2009-05-07删除RANK_DEFINE约束，修改ORIGINAL_SORT约束
private static string GetGroupsSqlByScc2(SearchOrgChildrenCondition scc, string rootPath)
{
StringBuilder strB = new StringBuilder(1024);
strB.Append(@" SELECT 'GROUPS' AS OBJECTCLASS, " + DatabaseSchema.Instence.GetTableColumns(scc.ObjAttrs, "GROUPS") + @"
FROM GROUPS
WHERE ( " + GetSqlSearchParOriginal2("GROUPS", scc.Depth, rootPath) + " )");
string strListDelete = GetSqlSearchStatus("GROUPS", scc.ListObjDelete);
if (strListDelete.Length > 0)
strB.Append(" \n AND ( " + strListDelete + " ) ");
if (scc.HideType != string.Empty)
{
string strHideType = GetHideTypeFromXml(scc.HideType, "GROUPS");
if (strHideType.Length > 0)
strB.Append(" \n AND GROUPS.ALL_PATH_NAME NOT IN ( " + strHideType + " ) ");
}
return strB.ToString();
}
//2009-05-07删除RANK_DEFINE约束，修改ORIGINAL_SORT约束
private static string GetOrganizationsSqlByScc2(SearchOrgChildrenCondition scc, string rootPath)
{
StringBuilder strB = new StringBuilder(1024);
string strRootGuids = TransHashToSqlString(scc.RootGuids);
strB.Append(@" SELECT 'ORGANIZATIONS' AS OBJECTCLASS, "
+ DatabaseSchema.Instence.GetTableColumns(scc.ObjAttrs, "ORGANIZATIONS") + @"
FROM ORGANIZATIONS
WHERE( GUID IN(" + strRootGuids + @")
OR
(
( " + GetSqlSearchParOriginal2("ORGANIZATIONS", scc.Depth, rootPath) + @" )
{0}
)
)");
string strListDelete = GetSqlSearchStatus("ORGANIZATIONS", scc.ListObjDelete);
if (strListDelete.Length > 0)
strListDelete = " AND (" + strListDelete + ")";
if (scc.HideType != string.Empty)
{
string strHideType = GetHideTypeFromXml(scc.HideType, "ORGANIZATIONS");
if (strHideType.Length > 0)
strB.Append(" \n AND ORGANIZATIONS.ALL_PATH_NAME NOT IN ( " + strHideType + " ) ");
}
if (scc.OrgClass != 1024 * 1024 - 1)
strB.Append(" \n AND (ORGANIZATIONS.ORG_CLASS = 0 OR ORGANIZATIONS.ORG_CLASS & " + scc.OrgClass + " <> 0 ) ");
if (scc.OrgType != 1024 * 1024 - 1)
strB.Append(" \n AND (ORGANIZATIONS.ORG_TYPE = 0 OR ORGANIZATIONS.ORG_TYPE & " + scc.OrgType + " <> 0 ) ");
return string.Format(strB.ToString(), strListDelete);
}
///// <summary>
///// 根据数据表名称以及要求获取的数据表中的数据列名称组合成为核数据表相关的数据查询语言
///// </summary>
///// <param name="strAttrs">数据表中的数据列名称组合</param>
///// <param name="da">数据库操作对象</param>
///// <param name="strTables">指定的数据表名称</param>
///// <returns>数据查询语言SQL</returns>
//private static string GetTableColumns(string strAttrs, params string[] strTables)
//{
// StringBuilder strB = new StringBuilder(1024);
// ExceptionHelper.TrueThrow(strAttrs.Length == 0, "对不起,程序没有指定要求查询的列名称！请验证！");
// ExceptionHelper.TrueThrow(strTables.Length == 0, "对不起，没有确定的数据表名称！");
// if (strAttrs.Trim() == "*")
// strB.Append(strTables[0] + "." + strAttrs.Trim());
// else
// {
// string[] strAttrArr = strAttrs.Split(',');
// InitHashTable(da);
// for (int i = 0; i < strAttrArr.Length; i++)
// {
// strAttrArr[i] = strAttrArr[i].Trim();
// if (strB.Length > 0)
// strB.Append(", ");
// bool bComplicated = false;
// for (int j = 0; j < strTables.Length; j++)
// {
// DataTable table = _DataSet_Schema.Tables[strTables[j]];
// DataRow[] drs = table.Select("CNAME=" + TSqlBuilder.Instance.CheckQuotationMark(strAttrArr[i]));
// if (drs.Length > 0)
// {
// strB.Append(XmlHelper.DBValueToString(drs[0]["TNAME"]) + "." + strAttrArr[i]);
// bComplicated = true;
// break;
// }
// }
// if (false==bComplicated)
// strB.Append(" NULL AS " + strAttrArr[i]);
// }
// }
// return strB.ToString();
//}
/// <summary>
/// 根据指定的查询条件类，生成系统中对于“机构”的查询SQL语句
/// </summary>
/// <param name="scc">查询条件类对象</param>
/// <param name="rootPath">传入的ORIGINAL_SORT</param>
/// <returns>针对于“机构”的查询SQL语句</returns>
///
//2009-05-11
private static string GetOrganizationsSqlByScc(SearchOrgChildrenCondition scc, string rootPath)
{
StringBuilder strB = new StringBuilder(1024);
string strRootGuids = TransHashToSqlString(scc.RootGuids);
strB.Append(@" SELECT 'ORGANIZATIONS' AS OBJECTCLASS, "
+ DatabaseSchema.Instence.GetTableColumns(scc.ObjAttrs, "ORGANIZATIONS", "RANK_DEFINE") + @"
FROM ORGANIZATIONS JOIN RANK_DEFINE
ON ORGANIZATIONS.RANK_CODE = RANK_DEFINE.CODE_NAME
{1}
WHERE( GUID IN(" + strRootGuids + @")
OR
(
( " + GetSqlSearchParOriginal2("ORGANIZATIONS", scc.Depth, rootPath) + @" )
{0}
)
)");
string strListDelete = GetSqlSearchStatus("ORGANIZATIONS", scc.ListObjDelete);
if (strListDelete.Length > 0)
strListDelete = " AND (" + strListDelete + ")";
string strRankLimit = string.Empty;
if (scc.OrgRankCN.Length > 0)
strRankLimit = " AND RANK_DEFINE.SORT_ID <= (SELECT SORT_ID FROM RANK_DEFINE WHERE CODE_NAME = "
+ TSqlBuilder.Instance.CheckQuotationMark(scc.OrgRankCN, true) + " ) ";
if (scc.HideType != string.Empty)
{
string strHideType = GetHideTypeFromXml(scc.HideType, "ORGANIZATIONS");
if (strHideType.Length > 0)
strB.Append(" \n AND ORGANIZATIONS.ALL_PATH_NAME NOT IN ( " + strHideType + " ) ");
}
if (scc.OrgClass != 1024 * 1024 - 1)
strB.Append(" \n AND (ORGANIZATIONS.ORG_CLASS = 0 OR ORGANIZATIONS.ORG_CLASS & " + scc.OrgClass + " <> 0 ) ");
if (scc.OrgType != 1024 * 1024 - 1)
strB.Append(" \n AND (ORGANIZATIONS.ORG_TYPE = 0 OR ORGANIZATIONS.ORG_TYPE & " + scc.OrgType + " <> 0 ) ");
return string.Format(strB.ToString(), strListDelete, strRankLimit);
}
/// <summary>
/// 按照一定的查询条件查询系统中的数据对象
/// </summary>
/// <param name="scc">系统的查询条件对象</param>
/// <returns>按照一定的查询条件查询系统中的数据对象</returns>
///
//2009-05-07
public static DataSet GetOrganizationChildren2(SearchOrgChildrenCondition scc)
{
string searchKey = scc.GetHashString();
#if DEBUG
long cast = DateTime.Now.Ticks;
Trace.WriteLine(searchKey);
#endif
DataSet result;
string strRootGuids = TransHashToSqlString(scc.RootGuids).Trim('\'');
//得到All_Path_Name
DataSet dsRootOrg = OGUReader.GetObjectsDetail("ORGANIZATIONS",
strRootGuids,
SearchObjectColumn.SEARCH_GUID,
string.Empty,
SearchObjectColumn.SEARCH_GUID);
ExceptionHelper.FalseThrow(dsRootOrg.Tables[0].Rows.Count > 0, "不能找到ID为{0}的机构", strRootGuids);
string rootPath = dsRootOrg.Tables[0].Rows[0]["ORIGINAL_SORT"].ToString();
if (false == Caching.GetOrganizationChildrenQueue.Instance.TryGetValue(searchKey, out result))
{
using (DbContext context = DbContext.GetContext(CommonResource.AccreditConnAlias))
{
Database database = DatabaseFactory.Create(context);
StringBuilder strB = new StringBuilder(1024);
if ((scc.ListObjType & ListObjectType.ORGANIZATIONS) != ListObjectType.None)
{
strB.Append(" ( " + GetOrganizationsSqlByScc2(scc, rootPath) + " \n )");
}
if ((scc.ListObjType & ListObjectType.GROUPS) != ListObjectType.None)
{
if (strB.Length > 0)
strB.Append(" \n UNION \n ");
strB.Append(" ( " + GetGroupsSqlByScc2(scc, rootPath) + " \n )");
}
if ((scc.ListObjType & ListObjectType.USERS) != ListObjectType.None)
{
if (strB.Length > 0)
strB.Append(" \n UNION \n ");
strB.Append(" ( " + GetUsersSqlByScc2(scc, rootPath) + " \n )");
}
string strSql = "SELECT * FROM ( " + strB.ToString() + " ) RESULT ORDER BY GLOBAL_SORT";
result = database.ExecuteDataSet(CommandType.Text, strSql);
}
Caching.GetOrganizationChildrenQueue.Instance.Add(searchKey, result, InnerCacheHelper.PrepareDependency());
}
// }
//}
#if DEBUG
cast = DateTime.Now.Ticks - cast;
Trace.WriteLine(cast.ToString(), "Cast Time");
#endif
return result;
}
