/// <summary>
/// 获取记录
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getDT(T_SYS_ORG_LINK_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM T_SYS_ORG_LINK");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.ORGLINK_ID) == false)
{
sb.AppendFormat(" AND ORGLINK_ID = '{0}'", ClsSql.EncodeSql(sw.ORGLINK_ID));
}
if (string.IsNullOrEmpty(sw.ORGNO) == false)
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.ORGNO));
}
if (string.IsNullOrEmpty(sw.ORGLINKTYPE) == false)
{
sb.AppendFormat(" AND ORGLINKTYPE = '{0}'", ClsSql.EncodeSql(sw.ORGLINKTYPE));
}
if (string.IsNullOrEmpty(sw.PHONE) == false)
{
if (sw.PHONE.Split(',').Length > 1)
{
sb.AppendFormat(" AND PHONE in({0})", ClsSql.SwitchStrToSqlIn(sw.PHONE));
}
else
{
sb.AppendFormat(" AND PHONE ='{0}'", ClsSql.EncodeSql(sw.PHONE));
}
}
if (string.IsNullOrEmpty(sw.keys) == false)
{
sb.AppendFormat(" AND (NAME like '%{0}%' or USERJOB like '%{0}%' or PHONE like '%{0}%' or Tell like '%{0}%' or UNITNAME like '%{0}%')", ClsSql.EncodeSql(sw.keys));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取所有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT ORGLINK_ID, BYORGNO, ORGLINKTYPE,UNITNAME, NAME, USERJOB, PHONE, Tell, ORDERBY"
+ sb.ToString()
+ " order by BYORGNO,ORGLINKTYPE,ORDERBY";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 批量更新图层
/// </summary>
/// <param name="m">参见模型</param>
/// <returns></returns>
public static Message PLMdy(T_SYS_LAYER_Model m)
{
List <string> sqllist = new List <string>();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE T_SYS_LAYER SET ");
if (m.PlCZ == "1")
{
sb.AppendFormat(" ISDEFAULTCH = '1' ");
}
if (m.PlCZ == "2")
{
sb.AppendFormat(" ISDEFAULTCH = '0' ");
}
if (m.PlCZ == "3")
{
sb.AppendFormat(" ISACTION = '1' ");
}
if (m.PlCZ == "4")
{
sb.AppendFormat(" ISACTION = '0' ");
}
if (m.PlCZ == "5")
{
sb.AppendFormat(" ISFIREROUNDDEFAULT = '1' ");
}
if (m.PlCZ == "6")
{
sb.AppendFormat(" ISFIREROUNDDEFAULT = '0' ");
}
if (m.PlCZ == "7")
{
sb.AppendFormat(" ISFUROUNDDEFAULT = '1' ");
}
if (m.PlCZ == "8")
{
sb.AppendFormat(" ISFUROUNDDEFAULT = '0' ");
}
sb.AppendFormat(" WHERE LAYERCODE IN ({0})", ClsSql.SwitchStrToSqlIn(m.LAYERCODE));
sqllist.Add(sb.ToString());
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y > 0)
{
return(new Message(true, "批量操作成功!", ""));
}
else
{
return(new Message(false, "批量操作失败,事物回滚机制!", ""));
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Del(PEST_LOCALTREESPECIES_Model m)
{
List <string> sqllist = new List <string>();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("Delete From PEST_LOCALTREESPECIES WHERE 1=1 ");
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(m.BYORGNO));
sb.AppendFormat(" AND TSPCODE IN ({0})", ClsSql.SwitchStrToSqlIn(m.TSPCODE));
sqllist.Add(sb.ToString());
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y > 0)
{
return(new Message(true, "删除成功!", ""));
}
else
{
return(new Message(false, "删除失败,事物回滚!", ""));
}
}
/// <summary>
/// 保存角色拥有的权限
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(T_SYSSEC_ROLE_RIGHT_Model m)
{
if (string.IsNullOrEmpty(m.ROLEID))
{
return(null);
}
//首先删除
DataBaseClass.ExeSql("delete T_SYSSEC_ROLE_RIGHT where ROLEID=" + ClsSql.EncodeSql(m.ROLEID));
if (string.IsNullOrEmpty(m.RIGHTID) == false)
{
//一次性插入该用户对应的角色
DataBaseClass.ExeSql("INSERT INTO T_SYSSEC_ROLE_RIGHT (ROLEID, RIGHTID) SELECT " + m.ROLEID + " , RIGHTID FROM T_SYSSEC_RIGHT WHERE RIGHTID IN (" + ClsSql.SwitchStrToSqlIn(m.RIGHTID) + ")");
}
return(new Message(true, "保存成功！", ""));
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_IPSFR_USER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT a.HID, a.HNAME, a.SN, a.PHONE, a.SEX, a.BIRTH, a.ONSTATE, a.BYORGNO,a.MOBILEPARAMLIST, a.ISENABLE,a.PATROLLENGTH, b.ORGNAME");
sb.AppendFormat(" FROM T_IPSFR_USER AS a LEFT OUTER JOIN T_SYS_ORG AS b ON a.BYORGNO = b.ORGNO");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.HID) == false)
{
if (sw.HID.Split(',').Length > 1)
{
sb.AppendFormat(" AND a.HID in({0})", ClsSql.EncodeSql(sw.HID));
}
else
{
sb.AppendFormat(" AND a.HID ='{0}'", ClsSql.EncodeSql(sw.HID));
}
}
if (string.IsNullOrEmpty(sw.HNAME) == false)
{
sb.AppendFormat(" AND a.HNAME like '%{0}%'", ClsSql.EncodeSql(sw.HNAME));
}
if (string.IsNullOrEmpty(sw.SN) == false)
{
sb.AppendFormat(" AND a.SN like '%{0}%'", ClsSql.EncodeSql(sw.SN));
}
if (string.IsNullOrEmpty(sw.PHONE) == false)
{
if (sw.PHONE.Length == 11)//精确查询
{
sb.AppendFormat(" AND a.PHONE= '{0}'", ClsSql.EncodeSql(sw.PHONE));
}
else //模糊查询
{
sb.AppendFormat(" AND a.PHONE like '%{0}%'", ClsSql.EncodeSql(sw.PHONE));
}
}
if (string.IsNullOrEmpty(sw.PHONELIST) == false)
{
if (sw.PHONELIST.Split(',').Length > 1)
{
sb.AppendFormat(" AND PHONE in({0})", ClsSql.SwitchStrToSqlIn(sw.PHONELIST));
}
else
{
sb.AppendFormat(" AND PHONE ='{0}'", ClsSql.EncodeSql(sw.PHONELIST));
}
}
if (string.IsNullOrEmpty(sw.PATROLLENGTH) == false)
{
sb.AppendFormat(" AND a.PATROLLENGTH = '{0}'", ClsSql.EncodeSql(sw.PATROLLENGTH));
}
if (string.IsNullOrEmpty(sw.SEX) == false)
{
sb.AppendFormat(" AND a.SEX = '{0}'", ClsSql.EncodeSql(sw.SEX));
}
if (string.IsNullOrEmpty(sw.ONSTATE) == false)
{
sb.AppendFormat(" AND a.ONSTATE = '{0}'", ClsSql.EncodeSql(sw.ONSTATE));
}
if (string.IsNullOrEmpty(sw.ISENABLE) == false)//默认取有效用户
{
sb.AppendFormat(" AND a.ISENABLE = '{0}'", ClsSql.EncodeSql(sw.ISENABLE));
}
if (string.IsNullOrEmpty(sw.PhoneHname) == false)
{
sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname));
}
if (string.IsNullOrEmpty(sw.BYORGNO) == false)
{
if (PublicCls.OrgIsShi(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsXian(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsZhen(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsCun(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(sw.BYORGNO));
}
}
if (string.IsNullOrEmpty(sw.Orgs) == false)
{
string[] arr = sw.Orgs.Split(',');
string tmpOrg = "";
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].Length == 15)
{
if (tmpOrg != "")
{
tmpOrg += ",";
}
tmpOrg += arr[i];
}
}
if (tmpOrg != "")
{
string[] arr1 = tmpOrg.Split(',');//循环每个单位
sb.AppendFormat(" and (");
for (int i = 0; i < arr1.Length; i++)
{
if (i > 0)
{
sb.AppendFormat(" or");
}
if (PublicCls.OrgIsShi(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsXian(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsZhen(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsCun(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(arr1[i]));
}
}
sb.AppendFormat(" )");
}
}
sb.AppendFormat(" ORDER BY a.BYORGNO,a.HNAME ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 获取联表查询
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getUnionDT(QueryLayerDataSW sw)
{
#region union all
// select NAME, DISPLAY_X,DISPLAY_Y, FLAG from
//(
// ( select NAME,DISPLAY_X,DISPLAY_Y, 0 as FLAG from XIANGZHENZHUDI a
// where a.DISPLAY_X is not null and a.DISPLAY_Y is not null) UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 1 as FLAG from CUNZHUDI b
// where b.DISPLAY_X is not null and b.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 2 as FLAG from JIAYOUZHAN c
// where c.DISPLAY_X is not null and c.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 3 as FLAG from ZERENLUXIAN d
// where d.DISPLAY_X is not null and d.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 4 as FLAG from ZERENQU e
// where e.DISPLAY_X is not null and e.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 5 as FLAG from XIAOFANGDUIWU f
// where f.DISPLAY_X is not null and f.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPALY_X as DISPLAY_X,DISPLAY_Y, 6 as FLAG from ZHENGFUJIGUAN g
// where g.DISPALY_X is not null and g.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 7 as FLAG from ZIYUAN h
// where h.DISPLAY_X is not null and h.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 8 as FLAG from CANGKU I
// where I.DISPLAY_X is not null and I.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 9 as FLAG from ZHUANGBEI J
// where J.DISPLAY_X is not null and J.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 10 as FLAG from YINGFANG K
// where K.DISPLAY_X is not null and K.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 11 as FLAG from TINGCHECHANG L
// where L.DISPLAY_X is not null and L.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 12 as FLAG from LIAOWANGTAI M
// where M.DISPLAY_X is not null and M.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 13 as FLAG from XUANCHUANBEIPAI N
// where N.DISPLAY_X is not null and N.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 14 as FLAG from ZHONGJIZHAN O
// where O.DISPLAY_X is not null and O.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 15 as FLAG from JIANCEZHAN P
// where P.DISPLAY_X is not null and P.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 16 as FLAG from YINZICAIJIZHAN Q
// where Q.DISPLAY_X is not null and Q.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 17 as FLAG from GELIDAI R
// where R.DISPLAY_X is not null and R.DISPLAY_Y is not null)UNION all
// ( select NAME,DISPLAY_X,DISPLAY_Y, 18 as FLAG from FANGHUOTONGDAO S
// where S.DISPLAY_X is not null and S.DISPLAY_Y is not null)
// ) as c
#endregion
StringBuilder sb = new StringBuilder();
sb.Append(" select ID, NAME, DISPLAY_X,DISPLAY_Y, FLAG,category,LNGLATSTRS,DBTYPE,TYPE,ImageUrl from ( ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 112 as Flag,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE ,'jiayouzhan.png' as ImageUrl from NEW_JIAYOUZHAN b where b.DISPLAY_X is not null and b.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 113 as Flag,null as category,null as LNGLATSTRS ,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from CUNZHUDI C where C.DISPLAY_X is not null and C.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 114 as Flag,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from POINTMARK D where D.DISPLAY_X is not null and D.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 115 as Flag ,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from MOUNTAIN E where E.DISPLAY_X is not null and E.DISPLAY_Y is not null) UNION all ");
//sb.Append(" ( select NAME,DISPLAY_X,DISPLAY_Y, 116 as Flag,null as LNGLATSTRS from XIANZHUDI F where F.DISPLAY_X is not null and F.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 117 as Flag,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from NEW_XIANGZHENZHUDI G where G.DISPLAY_X is not null and G.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPALY_X as DISPLAY_X,DISPLAY_Y, 128 as FLAG,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'zhengfudanwei.png' as ImageUrl from ZHENGFUJIGUAN g where g.DISPALY_X is not null and g.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 119 as Flag,category,null as LNGLATSTRS,3 as DBTYPE,0 as TYPE,case category when '1' then 'yingfang/ganggou.png' when '2' then 'yingfang/zhuanhun.png' when '3' then 'yingfang/ganghun.png' else 'location.png' end as ImageUrl from YINGFANG G where G.DISPLAY_X is not null and G.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 120 as Flag,category,null as LNGLATSTRS,12 as DBTYPE,0 as TYPE,'cangku.png' as ImageUrl from CANGKU where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 121 as Flag,category,null as LNGLATSTRS,5 as DBTYPE,0 as TYPE,case category when '1' then 'liaowangtai/ganggou.png' when '2' then 'liaowangtai/zhuanhun.png' when '3' then 'liaowangtai/ganghun.png' else 'location.png' end as ImageUrl from LIAOWANGTAI where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 122 as Flag,category,null as LNGLATSTRS,6 as DBTYPE,0 as TYPE,case category when '1' then 'xuanchuanbeipai/yongjiu.png' when '2' then 'xuanchuanbeipai/linshi.png' else 'location.png' end as ImageUrl from XUANCHUANBEIPAI where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 123 as Flag,category,null as LNGLATSTRS,7 as DBTYPE,0 as TYPE,case category when '1' then 'zhongjizhan/duanbo.png' when '2' then 'zhongjizhan/chaoduanbo.png' when '3' then 'zhongjizhan/weibo.png' else 'location.png' end as ImageUrl from ZHONGJIZHAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 124 as Flag,category,null as LNGLATSTRS,8 as DBTYPE,0 as TYPE,case category when '1' then 'jiancezhan/youxian.png' when '2' then 'jiancezhan/wuxian.png' else 'location.png' end as ImageUrl from JIANCEZHAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 125 as Flag,category,null as LNGLATSTRS,9 as DBTYPE,0 as TYPE,case category when '1' then 'yinzicaijizhan/youxian.png' when '2' then 'yinzicaijizhan/wuxian.png' else 'location.png' end as ImageUrl from YINZICAIJIZHAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 126 as Flag,category,null as LNGLATSTRS,1 as DBTYPE,0 as TYPE,case category when '1' then 'xiaofangduiwu/zhuanye.png' when '2' then 'xiaofangduiwu/banzhuanye.png' when '3' then 'xiaofangduiwu/yingji.png' when '4' then 'xiaofangduiwu/qunzhong.png' else 'location.png' end as ImageUrl from XIAOFANGDUIWU where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 127 as Flag,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from QITASHESHI where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
//sb.Append(" ( select NAME,DISPALY_X as DISPLAY_X,DISPLAY_Y, 128 as Flag,null as LNGLATSTRS from ZHENGFUJIGUAN where DISPALY_X is not null and DISPLAY_Y is not null) UNION all");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 129 as Flag ,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'tingchechang.png' as ImageUrl from TINGCHECHANG where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 212 as Flag,null as category,[shape] as LNGLATSTRS,null as DBTYPE,1 as TYPE ,'location.png' as ImageUrl from HELIU where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 219 as Flag,category,[shape] as LNGLATSTRS,10 as DBTYPE,1 as TYPE,'location.png'as ImageUrl from GELIDAI where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 220 as Flag,category,[shape] as LNGLATSTRS,11 as DBTYPE,1 as TYPE,'location.png'as ImageUrl from FANGHUOTONGDAO where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 115 as Flag ,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from MOUNTAIN E where E.DISPLAY_X is not null and E.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 115 as Flag ,null as category,null as LNGLATSTRS,null as DBTYPE,0 as TYPE,'location.png' as ImageUrl from MOUNTAIN E where E.DISPLAY_X is not null and E.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 221 as Flag,null as category,[shape] as LNGLATSTRS,null as DBTYPE,1 as TYPE,'location.png' as ImageUrl from ZERENLUXIAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, NAME,DISPLAY_X,DISPLAY_Y, 311 as Flag,null as category,[shape] as LNGLATSTRS,null as DBTYPE,2 as TYPE,'location.png' as ImageUrl from ZERENQU where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 313 as Flag,category,[shape] as LNGLATSTRS,10 as DBTYPE,2 as TYPE,'location.png' as ImageUrl from HUOSHAOMIAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,DISPLAY_X,DISPLAY_Y, 314 as Flag,category,[shape] as LNGLATSTRS,2 as DBTYPE,2 as TYPE ,'location.png' as ImageUrl from ZIYUAN where DISPLAY_X is not null and DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,JD as DISPLAY_X,WD as DISPLAY_Y, 130 as Flag,null as category, null as LNGLATSTRS,null as DBTYPE,0 as TYPE ,'dianzijiankong.png' as ImageUrl from SHIPINGJIANKONG l where l.JD is not null and l.WD is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME,JD as DISPLAY_X,WD as DISPLAY_Y, 131 as Flag,null as category, null as LNGLATSTRS,null as DBTYPE,0 as TYPE ,'hongwaixiangji.png' as ImageUrl from HONGWAIXIANGJI X where X.JD is not null and X.WD is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID,NAME, DISPLAY_X, DISPLAY_Y, 132 as Flag,null as category, null as LNGLATSTRS,101 as DBTYPE,0 as TYPE ,'youhaishengwujcd.png' as ImageUrl from YHSWJCD X where X.DISPLAY_X is not null and X.DISPLAY_Y is not null) UNION all ");
sb.Append(" ( select [OBJECTID] as ID, ([乡]+' 林班:'+[林班]+',小班:'+[小班]) as NAME, DISPLAY_X , DISPLAY_Y, 316 as Flag,null as category,[shape] as LNGLATSTRS,100 as DBTYPE,2 as TYPE, 'location.png' as ImageUrl from GONGYILINVIEW ) ");
//sb.Append(" UNION all ( select [OBJECTID] as ID, ([乡]+[林班]+[小班]) as NAME, shape.STCentroid().STX AS DISPLAY_X ,shape.STCentroid().STY AS DISPLAY_Y, 316 as Flag,[shape] as LNGLATSTRS,100 as DBTYPE,3 as TYPE from GONGYILIN where [横坐标] is not null and [纵坐标] is not null) ");
sb.Append(" ) as ff Where 1=1 ");
if (!string.IsNullOrEmpty(sw.Name))
{
sb.AppendFormat(" And ff.Name like '%{0}%' ", sw.Name.Trim());
}
if (!string.IsNullOrEmpty(sw.FlagStr))
{
sb.AppendFormat(" And ff.FLAG in ({0})", ClsSql.SwitchStrToSqlIn(sw.FlagStr));
}
if (!string.IsNullOrEmpty(sw.AroundValue))//周边距离
{
//AND (dbo.fnGetDistance(LONGITUDE, LATITUDE, 103.2680404, 23.7104433) < 10
//[dbo].[GetDistanceByNative]
// sb.AppendFormat(" AND (dbo.fnGetDistance(DISPLAY_Y, DISPLAY_X,{0},{1}) <= {2})", sw.WD, sw.JD, sw.AroundValue);
sb.AppendFormat(" AND (dbo.GetDistanceByNative(DISPLAY_Y,DISPLAY_X,{0},{1}) <= {2})", sw.WD, sw.JD, sw.AroundValue);
}
DataSet ds = SDEDataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}