/// <summary>
/// 获取DBINFO基本信息
/// </summary>
/// <returns></returns>
public DbInfo GetDbInfo()
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DBSERNAME,CRS,CSPARAM FROM DBInfo ");
return(SqlServerDBHelper.ExecuteQueryText <DbInfo>(strSql.ToString()).FirstOrDefault());
}
/// <summary>
/// 根据业务对象类型和过滤条件获取对象列表
/// </summary>
/// <param name="bot"></param>
/// <param name="filter"></param>
/// <returns></returns>
public BOCollection GetBOListByType(string bot, string filter)
{
BOCollection list = new BOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT FROM ");
strSql.Append(" BO T,");
strSql.Append(" OBJECTTYPE T1 ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
}
strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot));
strSql.Append(" AND T.BOTID = T1.BOTID ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" AND PROPERTY.BOID = T.BOID ");
}
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")");
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString()));
return(list);
}
public IList <TypeClassTree> GetList()
{
List <TypeClassTree> list = new List <TypeClassTree>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT T1.ID AS ID,T1.NAME AS NAME ,'0' AS PID,'' AS FT,");
strSql.Append(" T1.ISUSERDEFINE ,'' AS SHAPE,'' AS USEGEOMETRY,'TypeClass' AS TYPE,'' AS ENAME FROM OBJTYPECLASS T1 ");
strSql.Append(" UNION ");
strSql.Append(" SELECT O.BOTID AS ID,O.BOT AS NAME,T.ID AS PID,O.FT,O.ISUSERDEFINE,");
strSql.Append(" O.SHAPE,O.USEGEOMETRY,'ObjType' AS TYPE,O.NAME AS ENAME FROM OBJECTTYPE O JOIN OBJTYPECLASS T ON T.ID=O.ID ");
list.AddRange(SqlServerDBHelper.ExecuteQueryText <TypeClassTree>(strSql.ToString()));
return(list);
}
/// <summary>
/// 根据对象ID查询业务对象
/// </summary>
/// <param name="boid">对象ID</param>
/// <returns></returns>
public BO GetBOById(string boid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT T.BOID,T.NAME,T1.BOT FROM BO T ");
strSql.Append(" LEFT JOIN OBJECTTYPE T1 ");
strSql.Append(" ON T.BOTID=T1.BOTID ");
strSql.Append(" WHERE T.BOID =@BOID ");
SqlParameter[] parameters =
{
new SqlParameter("BOID", SqlDbType.VarChar, 36)
};
parameters[0].Value = boid;
return(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString(), parameters).FirstOrDefault());
}
/// <summary>
/// 根据业务对象名称、对象类型查询对象ID
/// </summary>
/// <param name="name">业务对象名称</param>
/// <param name="bot">对象类型</param>
/// <returns></returns>
public BO GetBOByName(string name, string bot)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT * FROM BO B");
strSql.Append(" LEFT JOIN OBJECTTYPE T ");
strSql.Append(" ON B.BOTID=T.BOTID ");
strSql.Append(" WHERE B.NAME =@NAME ");
strSql.Append(" AND T.BOT=@BOT ");
SqlParameter[] parameters =
{
new SqlParameter("NAME", SqlDbType.VarChar, 50),
new SqlParameter("BOT", SqlDbType.VarChar, 50)
};
parameters[0].Value = name;
parameters[1].Value = bot;
return(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString(), parameters).FirstOrDefault());
}
/// <summary>
/// 根据应用场景和过滤条件查询业务对象。通过对象的参数集进行过滤，返回符合条件的对象列表
/// </summary>
/// <param name="bot">业务对象类型</param>
/// <param name="wktBBox">空间范围</param>
/// <param name="filte">过滤条件</param>
/// <returns></returns>
public BOCollection GetBOListByFilter(string bot, string wktBBox, string filte)
{
//sdo_point_type（坐下坐标，右上坐标）。
BOCollection list = new BOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT ");
strSql.Append(" FROM ");
if (!string.IsNullOrEmpty(wktBBox))
{
strSql.Append(" V_GEOMETRY A,");
}
strSql.Append(" BO T,");
strSql.Append(" OBJECTTYPE T1 ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" ,PROPERTY PROPERTY ");
}
strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot));
if (!string.IsNullOrEmpty(wktBBox))
{
//strSql.Append(" GEOMETRY A,");
strSql.Append(" AND A.BOID = T.BOID ");
}
strSql.Append(" AND T.BOTID = T1.BOTID ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" AND PROPERTY.BOID = T.BOID ");
}
//空间范围不为空
if (!string.IsNullOrEmpty(wktBBox))
{
strSql.Append(string.Format(" AND ( A.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", wktBBox));
strSql.Append(string.Format(" A.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", wktBBox));
}
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(filte, bot).ToString() + " )");
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString()));
return(list);
}
/// <summary>
/// 根据业务对象ID和业务域查询业务对象别名
/// </summary>
/// <param name="boid">业务对象ID</param>
/// <param name="appdomains">业务域</param>
/// <returns></returns>
public AliasCollection GetBOAliasByID(string boid, params string[] appdomains)
{
AliasCollection aliasColl = new AliasCollection();
foreach (var item in appdomains)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT * FROM ALIASNAME ");
strSql.Append(" WHERE BOID =@BOID AND APPDOMAIN=@APPDOMAIN");
SqlParameter[] parameters =
{
new SqlParameter("BOID", SqlDbType.VarChar, 36),
new SqlParameter("APPDOMAIN", SqlDbType.VarChar, 50)
};
parameters[0].Value = boid;
parameters[1].Value = item;
List <Alias> list = SqlServerDBHelper.ExecuteQueryText <Alias>(strSql.ToString(), parameters);
aliasColl.AddRange(list);
}
return(aliasColl);
}
/// <summary>
/// 获取参数的数据类型
/// </summary>
/// <param name="ft"></param>
/// <param name="ns"></param>
/// <param name="paraName"></param>
/// <returns></returns>
public string GetPropertyDataType(string ns, string paraName, string bot)
{
string dataType = string.Empty;
StringBuilder strSql = new StringBuilder();
SqlParameter[] parameters;
strSql.Append(" SELECT x.MD.value('(/PropertySet/P[@n=\"" + paraName.Trim() + "\"]/@t)[1]','varchar(10)') as datatype");
strSql.Append(" FROM objtypeproperty X, ");
strSql.Append(" objecttype t ");
strSql.Append(" WHERE t.BOTID=x.BOTID ");
strSql.Append(" AND t.BOT= @BOT ");
strSql.Append(" AND X.NS = @NS ");
parameters = new SqlParameter[] {
new SqlParameter("BOT", SqlDbType.VarChar, 50),
new SqlParameter("NS", SqlDbType.VarChar, 50)
};
parameters[0].Value = bot.Trim();
parameters[1].Value = ns.Trim();
dataType = SqlServerDBHelper.ExecuteQueryText <string>(strSql.ToString(), parameters).FirstOrDefault();
if (string.IsNullOrEmpty(dataType))
{
strSql = new StringBuilder();
strSql.Append(" SELECT x.MD.value('(/PropertySet/P[@n=\"" + paraName.Trim() + "\"]/@t)[1]','varchar(10)') as datatype");
strSql.Append(" FROM property X, ");
strSql.Append(" objecttype t,bo ");
strSql.Append(" WHERE bo.boid=x.boid and bo.BOTID=t.BOTID ");
strSql.Append(" AND t.BOT= @BOT ");
strSql.Append(" AND X.NS = @NS ");
parameters = new SqlParameter[] {
new SqlParameter("BOT", SqlDbType.VarChar, 50),
new SqlParameter("NS", SqlDbType.VarChar, 50),
};
parameters[0].Value = bot.Trim();
parameters[1].Value = ns.Trim();
dataType = SqlServerDBHelper.ExecuteQueryText <string>(strSql.ToString(), parameters).FirstOrDefault();
}
return(dataType);
}
public NearBOCollection GetNearBOByBo(decimal distince, string bot, string filter, string strSqlGetGeo)
{
NearBOCollection list = new NearBOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT B.BOID, T.NAME, O.BOT, SUBSTRING(B.SPACELOCATION,0,3800) AS SPACELOCATION, B.DIST DISTINCE ");
strSql.Append(" FROM (SELECT A.BOID, A.NAME, A.GEOMETRY.STAsText() AS SPACELOCATION, ");
strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY ) AS DIST ", strSqlGetGeo));
strSql.Append(" FROM V_GEOMETRY A ");
//过滤条件
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = A.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")");
strSql.Append(" AND ");
}
else
{
strSql.Append(" WHERE ");
}
strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY )<{1} ", strSqlGetGeo, distince));
strSql.Append(" ) B ");
strSql.Append(" LEFT JOIN BO T ");
strSql.Append(" ON B.BOID = T.BOID ");
strSql.Append(" LEFT JOIN OBJECTTYPE O ");
strSql.Append(" ON O.BOTID = T.BOTID ");
//对象类型
if (!string.IsNullOrEmpty(bot))
{
strSql.Append(string.Format(" WHERE O.BOT = '{0}'", bot));
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <NearBO>(strSql.ToString()));
return(list);
}
/// <summary>
/// 根据对象ID获取属性数据
/// </summary>
/// <param name="boid"></param>
/// <returns></returns>
public static List <Property> GetPropertyByBoid(string boid)
{
return(SqlServerDBHelper.ExecuteQueryText <Property>(string.Format("SELECT NS,T.MD MD FROM PROPERTY T WHERE T.BOID = '{0}'", boid)));
}
/// <summary>
/// 根据对象ID获取空间坐标数据
/// </summary>
/// <param name="boid"></param>
/// <returns></returns>
public static List <Geometry> GetGeometryByBoid(string boid)
{
return(SqlServerDBHelper.ExecuteQueryText <Geometry>(string.Format("SELECT NAME,T.GEOMETRY.STAsText() GEOMETRY,SOURCEDB FROM GEOMETRY T WHERE T.BOID = '{0}'", boid)));
}
/// <summary>
/// 根据对象ID获取别名
/// </summary>
/// <param name="boid"></param>
/// <returns></returns>
public static List <AliasName> GetAliasNameByBoid(string boid)
{
return(SqlServerDBHelper.ExecuteQueryText <AliasName>(string.Format("SELECT APPDOMAIN,NAME FROM ALIASNAME WHERE BOID = '{0}'", boid)));
}
/// <summary>
/// 获取对象类型集合
/// </summary>
/// <returns></returns>
public List <string> GetBOTList()
{
return(SqlServerDBHelper.ExecuteQueryText <string>("SELECT BOT FROM OBJECTTYPE "));
}
/// <summary>
/// 根据条件获取3GX数据
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public XmlDocument GetFeatures(FeatureFilter filter)
{
string sqlWhere = string.Empty;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT DISTINCT BO.*,OBJECTTYPE.BOT,OBJECTTYPE.FT FROM BO,OBJECTTYPE");
if (!string.IsNullOrEmpty(filter.BBox))
{
strSql.Append(",v_Geometry ");
}
if (filter.Filter != null)
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE BO.BOTID = OBJECTTYPE.BOTID ");
strSql.Append(" AND PROPERTY.BOID = BO.BOID ");
strSql.Append(string.Format(" AND OBJECTTYPE.FT='{0}' ", filter.FT));
strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID ");
}
else
{
strSql.Append(string.Format(" WHERE OBJECTTYPE.FT='{0}' ", filter.FT));
strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID ");
}
//对象名称
if (filter.BOs != null && filter.BOs.Count > 0)
{
string bos = string.Empty;
for (int i = 0; i < filter.BOs.Count; i++)
{
if (i == filter.BOs.Count - 1)
{
bos += "'" + filter.BOs[i].Trim() + "'";
}
else
{
bos += "'" + filter.BOs[i].Trim() + "',";
}
}
strSql.Append(string.Format(" AND BO.NAME in {0} ", bos));
}
//空间范围和crs 未启用坐标范围
if (!string.IsNullOrEmpty(filter.BBox))
{
strSql.Append(" AND v_Geometry.BOID=BO.BOID ");
strSql.Append(string.Format(" AND ( v_Geometry.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", filter.BBox));
strSql.Append(string.Format(" v_Geometry.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", filter.BBox));
}
//属性
if (filter.Filter != null)
{
JObject jObj = JObject.Parse(filter.Filter.ToString());
string bot = SqlServerDBHelper.ExecuteQueryText <string>(string.Format("SELECT BOT FROM OBJECTTYPE WHERE FT='{0}'", filter.FT)).FirstOrDefault();
strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(jObj.ToString(), bot).ToString() + " )");
}
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString());
List <GeoFeature> ftList = new List <GeoFeature>();
foreach (DataRow row in dt.Rows)
{
GeoFeature ft = new GeoFeature();
ft.BOID = row["Boid"].ToString();
ft.BOT = row["BOT"].ToString();
ft.FT = row["FT"].ToString();
ft.NAME = row["Name"].ToString();
ft.AliasNameList = Comm.GetAliasNameByBoid(ft.BOID);
ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID);
ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID);
ftList.Add(ft);
}
return(GGGXParse.ConvertFT.FeatureToGGGX(ftList));
}
/// <summary>
/// 递归查询
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private List <TreeBO> GetTreeBONode(BOTreeNodeTemplate nodeTemplate, List <TreeBO> treeBOList, BORelation relation)
{
foreach (var bo in treeBOList)
{
StringBuilder strSql = new StringBuilder();
if (string.IsNullOrEmpty(relation.ToString()) || relation == BORelation.Space)
{
strSql.Append(" SELECT T.BOID, T.NAME, T1.BOT,");
strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID ");
strSql.Append(" FROM BO T, OBJECTTYPE T1, GEOMETRY T3 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(nodeTemplate.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T.BOID ");
JObject jObj = JObject.Parse(nodeTemplate.Filter);
JToken strFilter = jObj["filter"];
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(strFilter.ToString(), bo.BOT).ToString() + ")");
strSql.Append(" AND T.BOTID = T1.BOTID ");
strSql.Append(" AND T.BOID = T3.BOID ");
}
else
{
strSql.Append(" WHERE T.BOTID = T1.BOTID ");
strSql.Append(" AND T.BOID = T3.BOID ");
}
#endregion
//空间包含的子对象
strSql.Append(" AND SDO_FILTER(t3.GEOMETRY, ");
strSql.Append(" (SELECT T2.GEOMETRY ");
strSql.Append(" FROM GEOMETRY T2 ");
strSql.Append(string.Format(" WHERE T2.BOID ='{0}'), ", bo.BOID));
strSql.Append(" 'QUERYTYPE=WINDOW') = 'TRUE' ");
}
else
{
strSql.Append(" SELECT T5.BOID, T5.NAME, T6.BOT, ");
strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID ");
strSql.Append(" FROM RELATION T4, BO T5, OBJECTTYPE T6 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(nodeTemplate.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T5.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(nodeTemplate.Filter, bo.BOT).ToString() + ")");
strSql.Append(" AND T4.RTID = (SELECT T.RTID ");
}
else
{
strSql.Append(" WHERE T4.RTID = (SELECT T.RTID ");
}
#endregion
strSql.Append(" FROM RELTYPE T ");
strSql.Append(" JOIN OBJECTTYPE T1 ");
strSql.Append(" ON T.BOTID1 = T1.BOTID ");
strSql.Append(" JOIN OBJECTTYPE T2 ");
strSql.Append(" ON T.BOTID2 = T2.BOTID ");
strSql.Append(string.Format(" AND T1.BOT = '{0}' ", bo.BOT));
strSql.Append(string.Format(" AND T2.BOT = '{0}') ", nodeTemplate.BOT));
strSql.Append(" AND T4.BOID2 = T5.BOID ");
strSql.Append(string.Format(" AND T4.BOID1 = '{0}' ", bo.BOID));
strSql.Append(" AND T5.BOTID = T6.BOTID ");
}
//过滤条件未添加
if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0)
{
if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0)
{
string strBos = string.Empty;
for (int i = 0; i < nodeTemplate.BOs.Count(); i++)
{
if (i == nodeTemplate.BOs.Count() - 1)
{
strBos += "'" + nodeTemplate.BOs[i].Trim() + "'";
}
else
{
strBos += "'" + nodeTemplate.BOs[i].Trim() + "',";
}
}
strSql.Append(string.Format(" AND T5.NAME IN ({0})", strBos));
strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT));
}
}
else
{
strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT));
}
List <TreeBO> boList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString());
listTemp.AddRange(boList);
if (nodeTemplate.Children != null)
{
foreach (var children in nodeTemplate.Children)
{
GetTreeBONode(children, boList, nodeTemplate.Relation);
}
}
}
return(listTemp);
}
/// <summary>
/// 根据业务对象ID获取指定BO的父节点、下级节点、兄弟节点、相邻节点（父节点、兄弟节点和下级节点）和子树。返回的节点中不包括自己
/// </summary>
/// <param name="template">树模板</param>
/// <returns></returns>
public TreeBOCollection GetBOTree(BOTreeTemplate template)
{
TreeBOCollection list = new TreeBOCollection();
foreach (var item in template)
{
string strBos = string.Empty;
TreeBO treeBo = new TreeBO();
if (item.BOs != null && item.BOs.Count() > 0)
{
for (int i = 0; i < item.BOs.Count(); i++)
{
if (i == item.BOs.Count() - 1)
{
strBos += "'" + item.BOs[i].Trim() + "'";
}
else
{
strBos += "'" + item.BOs[i].Trim() + "',";
}
}
}
StringBuilder strSql = new StringBuilder();
SqlParameter[] parameters;
strSql.Append(" SELECT T.BOID, T1.BOT, T.NAME, '' AS ParentBOID ");
strSql.Append(" FROM BO T, OBJECTTYPE T1 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(item.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(item.Filter, item.BOT).ToString() + ")");
strSql.Append(" AND T.BOTID = T1.BOTID ");
}
else
{
strSql.Append(" WHERE T.BOTID = T1.BOTID ");
}
#endregion
if (item.BOs != null && item.BOs.Count() > 0)
{
strSql.Append(string.Format(" AND T.NAME IN({0}) ", strBos));
strSql.Append(" AND T1.BOT =@BOT ");
}
else
{
strSql.Append(" AND T1.BOT =@BOT ");
}
parameters = new SqlParameter[] {
new SqlParameter("BOT", SqlDbType.VarChar, 100)
};
parameters[0].Value = item.BOT;
List <TreeBO> treeBOList = new List <TreeBO>();
//父节点
treeBOList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString(), parameters);
list.AddRange(treeBOList);
if (item.Children != null)
{
foreach (BOTreeNodeTemplate nodeTemplate in item.Children)
{
list.AddRange(GetTreeBONode(nodeTemplate, treeBOList, item.Relation));
}
}
}
return(list);
}