/// <summary>
/// 根据ID获取对象空间坐标
/// </summary>
/// <param name="boid"></param>
/// <returns></returns>
public List <GeometryModel> GetGeometryByID(string boid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT BOID,GATHERID,NAME,T.GEOMETRY.MakeValid().STAsText() AS GEOMETRY,SOURCEDB FROM GEOMETRY T ");
strSql.Append(" WHERE BOID =@BOID");
SqlParameter[] parameters =
{
new SqlParameter("BOID", SqlDbType.VarChar, 36)
};
parameters[0].Value = boid;
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters);
List <GeometryModel> list = new List <GeometryModel>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
GeometryModel model = new GeometryModel();
model.BOID = item["BOID"].ToString();
model.GATHERID = item["GATHERID"].ToString();
model.GEOMETRY = item["GEOMETRY"].ToString();
model.NAME = item["NAME"].ToString();
model.SOURCEDB = item["SOURCEDB"].ToString();
list.Add(model);
}
}
return(list);
}
public HomeInfo FirstOne(string sql)
{
DataTable dt = SqlServerDBHelper.GetDataSet(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
var data = dt.Select().First();
HomeInfo dal = new HomeInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Name = data["Name"].ToString(),
Value = data["Value"].ToString(),
Type = data["Type"].ToString(),
Url = data["Url"].ToString(),
Color = data["Color"].ToString(),
PluginType = data["PluginType"].ToString(),
Plugin = data["Plugin"].ToString(),
WidthStyle = data["WidthStyle"].ToString(),
HeightClass = data["HeightClass"].ToString(),
GroupID = data["GroupID"].ToString(),
Order = data["Order"].ToString(),
ContentStyle = data["ContentStyle"].ToString(),
Remark = data["Remark"].ToString()
};
return(dal);
}
public FilesInfo FirstImageOne(string sql)
{
DataTable dt = SqlServerDBHelper.GetDataSet(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
var data = dt.Select().First();
FilesInfo dal = new FilesInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Name = data["Name"].ToString(),
State = data["State"].ToString(),
Type = data["Type"].ToString(),
Extension = data["Extension"].ToString(),
CreateDate = (DateTime)data["CreateDate"],
Url = data["Url"].ToString(),
Describe = data["Describe"].ToString(),
Owner = data["Owner"].ToString(),
ParentID = data["ParentID"].ToString(),
IsPrivate = data["IsPrivate"].ToString(),
Custom = data["Custom"].ToString(),
Size = data["Custom"].ToString().Split(';'),
Remark = data["Remark"].ToString(),
CoverUrl = data["CoverUrl"].ToString()
};
return(dal);
}
public List <HomeInfo> GetHomeWhere(string strwhere)
{
List <HomeInfo> dals = new List <HomeInfo>();
string sql = "select * from Ananas_Home where " + strwhere;
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
HomeInfo dal = new HomeInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Name = data["Name"].ToString(),
Value = data["Value"].ToString(),
Type = data["Type"].ToString(),
Url = data["Url"].ToString(),
Color = data["Color"].ToString(),
PluginType = data["PluginType"].ToString(),
Plugin = data["Plugin"].ToString(),
WidthStyle = data["WidthStyle"].ToString(),
HeightClass = data["HeightClass"].ToString(),
GroupID = data["GroupID"].ToString(),
Order = data["Order"].ToString(),
ContentStyle = data["ContentStyle"].ToString(),
Remark = data["Remark"].ToString()
};
dals.Add(dal);
}
return(dals);
}
/// <summary>
/// 获取应用场景已经参数
/// </summary>
/// <returns></returns>
public AppDomainCollection GetAppDomains()
{
AppDomainCollection coll = new AppDomainCollection();
List <Jurassic.PKS.Service.GF.AppDomain> list = new List <Jurassic.PKS.Service.GF.AppDomain>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" WITH TAB AS( ");
strSql.Append(" SELECT T.BOT, T1.NS ");
strSql.Append(" FROM OBJECTTYPE T ");
strSql.Append(" LEFT JOIN OBJTYPEPROPERTY T1 ");
strSql.Append(" ON T.BOTID = T1.BOTID) ");
strSql.Append(" select DISTINCT bot,nss=STUFF((SELECT ','+ ns FROM TAB t WHERE bot=t1.bot FOR XML PATH('')), 1, 1, '') ");
strSql.Append(" from tab t1 order by bot ");
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString());
foreach (DataRow row in dt.Rows)
{
Jurassic.PKS.Service.GF.AppDomain appdomain = new Jurassic.PKS.Service.GF.AppDomain();
appdomain.BOT = row["BOT"].ToString();
if (!string.IsNullOrEmpty(row["NSS"].ToString()))
{
appdomain.Appdomain = row["NSS"].ToString().Split(',').ToList();
}
list.Add(appdomain);
}
coll.AddRange(list);
return(coll);
}
/// <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());
}
public List <UserInfo> GetAll()
{
List <UserInfo> dals = new List <UserInfo>();
string sql = "select * from Ananas_User";
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
UserInfo dal = new UserInfo()
{
ID = data["ID"].ToString(),
Name = data["Name"].ToString(),
sName = data["sName"].ToString(),
Password = data["Password"].ToString(),
Role = (int)data["RoleID"],
Skin = data["Skin"].ToString(),
Email = data["Email"].ToString(),
Introduction = data["Introduction"].ToString(),
ImageUrl = data["ImageUrl"].ToString(),
Projects = data["Projects"].ToString(),
Background = data["Background"].ToString()
};
dals.Add(dal);
}
return(dals);
}
/// <summary>
/// 获取BO的叙词分类。主要用于短语分词的时候识别业务对象是什么类型
/// </summary>
/// <returns></returns>
public TermBOCollection GetCCTermOfBO()
{
TermBOCollection termBolist = new TermBOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" WITH TAB AS( ");
strSql.Append(" SELECT T.BOID,T.NAME,T2.BOT,T1.NAME NAME1 FROM BO T ");
strSql.Append(" LEFT JOIN ALIASNAME T1 ");
strSql.Append(" ON T.BOID=T1.BOID ");
strSql.Append(" LEFT JOIN OBJECTTYPE T2 ");
strSql.Append(" ON T.BOTID=T2.BOTID )");
strSql.Append(" SELECT BOID,NAME,BOT, ");
strSql.Append(" ALIAS= STUFF((SELECT ','+[NAME1] FROM TAB t WHERE BOID=t1.BOID and BOT=t1.BOT and NAME=t1.NAME FOR XML PATH('')), 1, 1, '') ");
strSql.Append(" FROM TAB T1 ");
strSql.Append(" GROUP BY BOID, NAME,BOT ");
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString());
foreach (DataRow row in dt.Rows)
{
TermBO termBo = new TermBO();
termBo.BOID = row["BOID"].ToString();
termBo.BOT = row["BOT"].ToString();
termBo.Name = row["NAME"].ToString();
if (!string.IsNullOrEmpty(row["ALIAS"].ToString()))
{
termBo.Alias = row["ALIAS"].ToString().Split(',').ToList();
}
termBolist.Add(termBo);
}
return(termBolist);
}
public UserInfo FirstOne(string sql)
{
DataTable dt = SqlServerDBHelper.GetDataSet(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
var data = dt.Select().First();
UserInfo dal = new UserInfo()
{
ID = data["ID"].ToString(),
Name = data["Name"].ToString(),
sName = data["sName"].ToString(),
Password = data["Password"].ToString(),
Role = (int)data["RoleID"],
Skin = data["Skin"].ToString(),
Email = data["Email"].ToString(),
Introduction = data["Introduction"].ToString(),
ImageUrl = GetImageUrl(data["ImageUrl"].ToString()),
Projects = data["Projects"].ToString(),
Background = data["Background"].ToString()
};
return(dal);
}
public List <WorkInfo> GetWorksWhere(string strwhere)
{
List <WorkInfo> dals = new List <WorkInfo>();
string sql = "select * from Ananas_Work where " + strwhere;
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
WorkInfo dal = new WorkInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Content = data["Content"].ToString(),
Title = data["Title"].ToString(),
Type = data["Type"].ToString(),
Cover = data["Cover"].ToString(),
Reading = data["Reading"].ToString(),
Zaning = data["Zaning"].ToString(),
CreateDate = ((DateTime)data["CreateDate"]).ToString(@"yyyy-MM-dd HH:mm")
};
dals.Add(dal);
}
return(dals);
}
/// <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 List <WorkInfo> GetWorksWhereWithDetail(string strwhere)
{
List <WorkInfo> dals = new List <WorkInfo>();
string sql = "select * from Ananas_Work where " + strwhere;
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
WorkInfo dal = new WorkInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Content = data["Content"].ToString(),
Title = data["Title"].ToString(),
Type = data["Type"].ToString(),
Cover = data["Cover"].ToString(),
Reading = data["Reading"].ToString(),
Zaning = data["Zaning"].ToString(),
ZanCount = SqlServerDBHelper.GetScalar("select COUNT(*) from Ananas_Comment where WorkID='" + data["ID"].ToString() + "' and Type='" + CommentType.Zan + "'"),
CommentsCount = SqlServerDBHelper.GetScalar("select COUNT(*) from Ananas_Comment where WorkID='" + data["ID"].ToString() + "' and Type='" + CommentType.Commrnt + "'"),
CreateDate = ((DateTime)data["CreateDate"]).ToString(@"yyyy-MM-dd HH:mm")
};
dals.Add(dal);
}
return(dals);
}
//特殊处理图片子表
public List <FilesInfo> GetImageWithDetail(string strwhere)
{
List <FilesInfo> dals = new List <FilesInfo>();
string sql = "select * from Ananas_File where " + strwhere;
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
FilesInfo dal = new FilesInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Name = data["Name"].ToString(),
State = data["State"].ToString(),
Type = data["Type"].ToString(),
Extension = data["Extension"].ToString(),
CreateDate = (DateTime)data["CreateDate"],
Url = data["Url"].ToString(),
Describe = data["Describe"].ToString(),
Owner = data["Owner"].ToString(),
ParentID = data["ParentID"].ToString(),
IsPrivate = data["IsPrivate"].ToString(),
Custom = data["Custom"].ToString(),
FirstSize = this.GetFirstSize(data["Custom"].ToString()),
SizeList = this.GetSizeList(data["Custom"].ToString()),
Remark = data["Remark"].ToString(),
CoverUrl = data["CoverUrl"].ToString()
};
dals.Add(dal);
}
return(dals);
}
/// <summary>
/// 获取FT集合
/// </summary>
/// <returns></returns>
public FTCCollection GetFTCList()
{
FTCCollection list = new FTCCollection();
FTC ftc = new FTC("平面");
ftc.Name = "平面";
//获取全部对象类型
DataTable objTypeDt = SqlServerDBHelper.GetDataTable("SELECT BOTID,FT FROM OBJECTTYPE ");
//DataTable dt;
foreach (DataRow row in objTypeDt.Rows)
{
FT ft = new FT(row["FT"].ToString());
ft.Name = row["FT"].ToString();
SqlParameter[] parameters =
{
new SqlParameter("BOTID", SqlDbType.VarChar, 36)
};
parameters[0].Value = row["BOTID"].ToString();
DataTable dt = new DataTable();
//MD不截取发布服务出错
dt = SqlServerDBHelper.GetDataTable("SELECT MD AS MD,NS FROM OBJTYPEPROPERTY WHERE BOTID = @BOTID ", parameters);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i]["MD"].ToString()))
{
NS ns = new NS(dt.Rows[i]["NS"].ToString());
ns.Name = dt.Rows[i]["NS"].ToString();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(dt.Rows[i]["MD"].ToString());
for (int j = 0; j < xmlDoc.SelectNodes("PropertySet/P").Count; j++)
{
FeatureParameter para = new FeatureParameter(xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[0].Value);
para.Name = xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[0].Value;
if (xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[1].Value.ToUpper() == Jurassic.PKS.Service.PropertyDataType.String.ToString().ToUpper())
{
para.DataType = PropertyDataType.String;
}
else if (xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[1].Value.ToUpper() == Jurassic.PKS.Service.PropertyDataType.Decimal.ToString().ToUpper())
{
para.DataType = PropertyDataType.Decimal;
}
else
{
para.DataType = PropertyDataType.Date;
}
ns.Parameters.Add(para);
}
ft.NSs.Add(ns);
}
}
ftc.FTs.Add(ft);
}
list.Add(ftc);
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>
/// 空间坐标是否存在
/// </summary>
/// <param name="Geometry"></param>
/// <returns></returns>
public bool ExistGeometry(GeometryModel Geometry)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT BOID,NAME,T.GEOMETRY.MakeValid().STAsText() AS GEOMETRY,SOURCEDB FROM GEOMETRY T ");
strSql.Append(" WHERE BOID =@BOID");
SqlParameter[] parameters =
{
new SqlParameter("BOID", SqlDbType.VarChar, 36)
};
parameters[0].Value = Geometry.BOID;
return(SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters).Rows.Count > 0 ? true : false);
}
/// <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="ft"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public List <string> GetDomain(string ft, string parameter)
{
string[] strArr = parameter.Split('.').ToArray();
StringBuilder strSql = new StringBuilder();
List <string> list = new List <string>();
strSql.Append(" SELECT VS=COL.value('text()[1]','VARCHAR(MAX)') ");
strSql.Append(" FROM OBJECTTYPE T2,OBJTYPEPROPERTY X CROSS APPLY MD.nodes('/PropertySet/P') AS TBL(COL) ");
strSql.Append(string.Format(" WHERE X.BOTID=T2.BOTID AND X.NS='{0}' AND T2.FT='{1}' AND COL.value('@n','VARCHAR(MAX)')='{2}' ", strArr[0], ft, strArr[1]));
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString());
if (dt.Rows.Count > 0)
{
list = dt.Rows[0]["VS"].ToString().Split(',').ToList();
}
return(list);
}
/// <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、G|P|B查询对象3GX数据，3GX数据中可包含坐标信息或参数信息或两者都包含
/// </summary>
/// <param name="boid">对象ID</param>
/// <param name="category">枚举值[G|P|B]</param>
/// <returns></returns>
public System.Xml.XmlDocument Get3GXById(string boid, GGGXDataCategory category)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT T.BOID,T.NAME,T1.BOT,T1.FT 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;
DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters);
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);
if (category == GGGXDataCategory.B)
{
ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID);
ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID);
}
else if (category == GGGXDataCategory.P)
{
ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID);
ft.GeometryList = null;
}
else if (category == GGGXDataCategory.G)
{
ft.PropertyList = null;
ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID);
}
ftList.Add(ft);
}
return(GGGXParse.ConvertFT.FeatureToGGGX(ftList));
}
public void BackupFile(string filepath, string filename)
{
var tablename = filename;
DirectoryInfo readlocalfile = new DirectoryInfo(filepath);
//連線SSMS 取得連線字串
IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsetting.json", optional: true, reloadOnChange: true).Build();
string connString = config.GetConnectionString("DefaultConnection");
SqlServerDBHelper sqlHelper = new SqlServerDBHelper(string.Format(connString, "HISDB", "msdba", "1qaz@wsx"));
SqlServerTableHelper sqltablehelper = new SqlServerTableHelper(string.Format(connString, "HISDB", "msdba", "1qaz@wsx"));
List <SqlServerDBColumnInfo> tableList = sqltablehelper.FillTableList($"{tablename}").FillColumnList().GetTableList().First().SqlServerDBColumnList;
for (int x = 0; x <= tableList.Count - 1; x++)
{
if (tableList[x].DataTypeName == "BIT()")
{
tableList[x].DataTypeName = "BIT";
}
}
//Step1.2. 檔案讀取→\\10.1.225.17\d$\csv \\10.1.225.17\d$\CSV_20200721
var host = @"10.1.225.17";
var RDPfile = "CSV";
var username = @"TW-VYIN-207\Administrator";
var password = "******";
string old_path = "";
string new_path = $@"\\{host}\d$\{RDPfile}\" + tablename + ".csv";
using (new RDPCredentials(host, username, password))
{
//Step1.3. 找到相對應File
DirectoryInfo readfile = new DirectoryInfo($@"\\{host}\d$\{RDPfile}\{tablename}.csv");
//Step1.4. 將File中的資料存入var
string LastWriteTime = File.GetLastWriteTime(readfile.ToString()).ToString("yyyyMMdd");
old_path = $@"\\{host}\d$\{RDPfile}\" + tablename + "_" + LastWriteTime + ".csv";
//Step1.5. 修改名稱(原File_修改日期yyyyMMdd)--備份
readfile.MoveTo(old_path);
//Step1.6. 將下載的File 複製到 mstv
File.Copy(readlocalfile.ToString(), new_path);
}
}
/// <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);
}
public List <HomeList> GetHomeWhereWithBlock(string strwhere)
{
List <HomeList> dals = new List <HomeList>();
string sql = "select * from Ananas_Home where " + strwhere;
SqlDataReader data = SqlServerDBHelper.GetReader(sql);
while (data.Read())
{
HomeList dal = new HomeList()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Type = data["Type"].ToString(),
Order = data["Order"].ToString(),
GroupClass = data["Value"].ToString(),
Blocks = this.GetHomeWhere("GroupID ='" + data["ID"].ToString() + "'").OrderBy(d => d.Order).ToList()
};
dals.Add(dal);
}
return(dals);
}
/// <summary>
/// 添加空间坐标
/// </summary>
/// <param name="Geometry"></param>
/// <returns></returns>
public bool InsertGeometry(GeometryModel Geometry)
{
StringBuilder strInsertSql = new StringBuilder();
strInsertSql.Append(" INSERT INTO GEOMETRY( ");
strInsertSql.Append(" BOID,NAME,GATHERID,GEOMETRY,SOURCEDB)");
strInsertSql.Append(" VALUES (@BOID,@NAME,GATHERID,GEOGRAPHY::STGeomFromText(@GEOMETRY, 4326),@SOURCEDB)");
SqlParameter[] parameters =
{
new SqlParameter("BOID", SqlDbType.VarChar, 36),
new SqlParameter("NAME", SqlDbType.VarChar, 50),
new SqlParameter("GATHERID", SqlDbType.VarChar, 36),
new SqlParameter("GEOMETRY", SqlDbType.Binary),
new SqlParameter("SOURCEDB", SqlDbType.VarChar, 50)
};
parameters[0].Value = Geometry.BOID;
parameters[1].Value = Geometry.NAME;
parameters[2].Value = Geometry.GATHERID;
parameters[3].Value = Geometry.SOURCEDB;
return(SqlServerDBHelper.ExecuteCommand(strInsertSql.ToString(), parameters) > 0 ? true : false);
}
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>
/// 获取参数的数据类型
/// </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 CommentInfo FirstOne(string sql)
{
DataTable dt = SqlServerDBHelper.GetDataSet(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
var data = dt.Select().First();
CommentInfo dal = new CommentInfo()
{
ID = data["ID"].ToString(),
WorkID = data["WorkID"].ToString(),
Content = data["Content"].ToString(),
IP = data["IP"].ToString(),
Type = data["Type"].ToString(),
Remark = data["Remark"].ToString(),
UID = data["UID"].ToString(),
CreateDate = ((DateTime)data["CreateDate"]).ToString(@"yyyy-MM-dd HH:mm")
};
return(dal);
}
/// <summary>
/// 修改空间坐标
/// </summary>
/// <param name="Geometry"></param>
/// <returns></returns>
public bool UpdateGeometry(GeometryModel Geometry)
{
StringBuilder strUpdateSql = new StringBuilder();
strUpdateSql.Append(" UPDATE GEOMETRY SET GATHERID=:GATHERID");
strUpdateSql.Append(" SOURCEDB=@SOURCEDB,GEOMETRY=GEOGRAPHY::STGeomFromText(@GEOMETRY, 4326),NAME=@NAME");
strUpdateSql.Append(" WHERE BOID=@BOID ");
SqlParameter[] parameters =
{
new SqlParameter("GATHERID", SqlDbType.VarChar, 50),
new SqlParameter("SOURCEDB", SqlDbType.Binary),
new SqlParameter("GEOMETRY", SqlDbType.VarChar, 36),
new SqlParameter("NAME", SqlDbType.VarChar, 50),
new SqlParameter("BOID", SqlDbType.VarChar, 36)
};
parameters[0].Value = Geometry.GATHERID;
parameters[1].Value = Geometry.SOURCEDB;
parameters[2].Value = Geometry.GEOMETRY;
parameters[3].Value = Geometry.NAME;
parameters[3].Value = Geometry.BOID;
return(SqlServerDBHelper.ExecuteCommand(strUpdateSql.ToString(), parameters) > 0 ? true : false);
}
public WorkInfo FirstOne(string sql)
{
DataTable dt = SqlServerDBHelper.GetDataSet(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
var data = dt.Select().First();
WorkInfo dal = new WorkInfo()
{
ID = data["ID"].ToString(),
UID = data["UID"].ToString(),
Content = data["Content"].ToString(),
Title = data["Title"].ToString(),
Type = data["Type"].ToString(),
Cover = data["Cover"].ToString(),
Reading = data["Reading"].ToString(),
Zaning = data["Zaning"].ToString(),
CreateDate = ((DateTime)data["CreateDate"]).ToString(@"yyyy-MM-dd HH:mm")
};
return(dal);
}
