public DataTable GetFiledsInfo(string CarId, string TerNo, string InfoType)
{
UserInfo user = new UserInfo();
DataTable dt = new DataTable();
user = (UserInfo)Session["LoginUser"];
if (user != null)
{
StringBuilder sb = new StringBuilder();
UserFields uf = new UserFields();
uf.DeptId = user.EnterId;
uf.InfoType = InfoType;
IList <UserFields> iuf = userFieldsBll.GetUserFieldsPage(uf);
if (iuf.Count > 0)
{
sb.Append("select ");
for (int i = 0; i < iuf.Count; i++)
{
if (i < (iuf.Count - 1))
{
sb.Append("tt." + iuf[i].UfName + ",");
}
else
{
sb.Append("tt." + iuf[i].UfName);
}
}
sb.Append(" from Car_Info ci join dept_info di on ci.businessdivisionid=di.businessdivisionid left join terminal_info ti on ci.car_id=ti.car_id left join");
sb.Append(" (SELECT CAR_ID,");
for (int i = 0; i < iuf.Count; i++)
{
if (i < (iuf.Count - 1))
{
sb.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1},", iuf[i].UfId, iuf[i].UfName));
}
else
{
sb.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1} ", iuf[i].UfId, iuf[i].UfName));
}
}
sb.Append(" FROM field_values GROUP BY CAR_ID) tt on ci.car_id=tt.car_id where 1=1");
sb.Append(string.Format(" and ci.car_id='{0}'", CarId));
sb.Append(string.Format(" and ti.TER_NO='{0}'", TerNo));
DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString());
dt = ds.Tables[0];
}
}
return(dt);
}
public string GetAppSetting(string username)
{
string val = "{\"status\":\"0\",\"message\":\"获取失败\",\"result\":{}}";
if (username != "")
{
string sql = @"
SELECT t.username as UserName,
nvl(t.total_setting,'') as totalSetting,
nvl(t.online_setting,'') as onlineSetting,
nvl(t.offline_setting,'') as offlineSetting,
nvl(t.other_setting,'') as otherSetting,
nvl(t.demolition_alarm,'') as demolitionAlarm,
nvl(t.overspeed_alarm,'') as overspeedAlarm,
nvl(t.zone_alarm,'') as zoneAlarm,
nvl(t.power_off_alarm,'') as poweroffAlarm,
nvl(t.push_setting,'') as pushSetting,
nvl(t.stock_setting,'') as stockSetting,
nvl(t.expired_setting,'') as expiredSetting,
nvl(t.warn_setting,'') as warnSetting
FROM AppSetting t
WHERE t.username='******'";
System.Data.DataSet ds = query.GetColligateQuery("ColligateQuery.ProteanQuery", sql);
if (ds != null &&
ds.Tables.Count > 0 &&
ds.Tables[0].Rows.Count > 0)
{
val = Dtb2Json(ds.Tables[0]);
if (val.Length >= 3)
{
string result = val.Substring(1, val.Length - 2);
val = "{\"status\":\"1\",\"message\":\"获取成功\",\"result\":" + result + "}";
}
}
}
return(val);
}
private IList <UserFields> UpdateCache(string DeptId)
{
List <UserFields> uf_list = new List <UserFields>();
string sql = @" select f.uf_id, f.dept_id, f.uf_name, f.uf_desc, f.field_type, f.info_type
from dept_info d, user_fields f
where f.dept_id = d.businessdivisionid
and f.dept_id = '" + DeptId + @"'
order by f.uf_name
";
System.Data.DataSet ds = query.GetColligateQuery("ColligateQuery.ProteanQuery", sql);
if (ds != null &&
ds.Tables.Count > 0 &&
ds.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
UserFields tmp = new UserFields();
tmp.DeptId = dr["dept_id"].ToString();
tmp.InfoType = dr["info_type"].ToString();
tmp.UfDesc = dr["uf_desc"].ToString();
tmp.UfId = dr["uf_id"].ToString();
tmp.UfName = dr["uf_name"].ToString();
tmp.FieldType = dr["field_type"].ToString();
uf_list.Add(tmp);
}
}
string cache_name = "CurUserFieldsList_" + DeptId;
CacheHelper.Remove(cache_name);
CacheHelper.Insert(cache_name, uf_list, 365 * 24 * 60);
return(uf_list);
}
public void GetYsDC(Hashtable ht)
{
DataTable ysdl_dt = new DataTable();
DataTable yscc_dt = new DataTable();
DataTable ccdt = _iCarReportDao.GetCarReport_BJTJViewPage(ht); //拆除设备表
ColligateQueryService c = new ColligateQueryService();
if (ccdt != null && ccdt.Rows.Count > 0)
{
ysdl_dt = ccdt.Clone();
yscc_dt = ccdt.Clone();
foreach (DataRow dr in ccdt.Rows)
{
if (dr["ter_typeid"].ToString() == "0" || dr["ter_typeid"].ToString() == "1")
{
DateTime rtime = DateTime.Parse(dr["rtime"].ToString());
DateTime ctime = DateTime.Now.AddDays(-5);
if (rtime > ctime)
{
int flag = 1; //默认为疑似拆除
while (rtime > ctime)
{
string Stime = rtime.ToString("yyyy-MM-dd") + " 00:00:00";
string Etime = rtime.ToString("yyyy-MM-dd") + " 23:59:59";
string sql = "select latitude,longitude from historical_data where rtime between to_date('" + Stime + "','yyyy-MM-dd hh24:mi:ss') and to_date('" + Etime + "','yyyy-MM-dd hh24:mi:ss')";
System.Data.DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
double dwLongs = double.Parse(ds.Tables[0].Rows[0]["longitude"].ToString());
double dwLats = double.Parse(ds.Tables[0].Rows[0]["latitude"].ToString());
double dwLonge = double.Parse(ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]["longitude"].ToString());
double dwLate = double.Parse(ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]["latitude"].ToString());
double lengthse = this.GetDistance(dwLongs, dwLats, dwLonge, dwLate);
if (lengthse > 2000)
{
flag = 2;
break;
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (i + 1 < ds.Tables[0].Rows.Count)
{
double dwLong1 = double.Parse(ds.Tables[0].Rows[i]["longitude"].ToString());
double dwLat1 = double.Parse(ds.Tables[0].Rows[i]["latitude"].ToString());
double dwLong2 = double.Parse(ds.Tables[0].Rows[i + 1]["longitude"].ToString());
double dwLat2 = double.Parse(ds.Tables[0].Rows[i + 1]["latitude"].ToString());
double length = this.GetDistance(dwLong1, dwLat1, dwLong2, dwLat2);
if (length > 2000)
{
flag = 2;
break;
}
}
}
if (flag == 2)
{
break;
}
}
}
rtime = rtime.AddDays(-1);
}
if (flag == 1)
{
DataRow newdr = yscc_dt.NewRow();
foreach (DataColumn column in ccdt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
yscc_dt.Rows.Add(newdr);
}
else
{
DataRow newdr = ysdl_dt.NewRow();
foreach (DataColumn column in ccdt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
ysdl_dt.Rows.Add(newdr);
}
}
else
{
DataRow newdr = yscc_dt.NewRow();
foreach (DataColumn column in ccdt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
yscc_dt.Rows.Add(newdr);
}
}
}
}
}
public string Config(string AppType)
{
string val = "[{'err':'参数错误或无值'}]";
if (AppType == null)
{
AppType = "'android','iso'";
}
else
{
AppType = "'" + AppType + "'";
}
string sql = @"
select t.ver_id,
t.ver_name,
t.ver_number,
t.app_type,
t.app_url,
t.publisher,
to_char(t.publish_date, 'yyyy-mm-dd hh24:mi:ss') publish_date,
t.description
from (select t.ver_id,
t.ver_name,
t.ver_number,
t.app_type,
t.app_url,
t.publisher,
t.publish_date,
t.description,
row_number() over(partition by t.app_type order by t.publish_date desc) as rnum
from VERSION_INFO t
where lower(t.app_type) in ( " + AppType.Trim().ToLower() + @" ) ) t
where t.rnum = 1
";
sql = @"
select
t.ver_name,
t.ver_number,
t.app_url
from (select t.ver_id,
t.ver_name,
t.ver_number,
t.app_type,
t.app_url,
t.publisher,
t.publish_date,
t.description,
row_number() over(partition by t.app_type order by t.publish_date desc) as rnum
from VERSION_INFO t
where lower(t.app_type) in ( " + AppType.Trim().ToLower() + @" ) ) t
where t.rnum = 1
";
System.Data.DataSet ds = query.GetColligateQuery("ColligateQuery.ProteanQuery", sql);
if (ds != null &&
ds.Tables.Count > 0 &&
ds.Tables[0].Rows.Count > 0)
{
val = Dtb2Json(ds.Tables[0]);
}
if (val.Trim().Length > 0)
{
val = val.Substring(1, (val.Length - 2));
}
return(val);
}
public string GetCarList_bak(CarInfo carInfo, int rows, int page, string ChildrenSel)
{
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
if (user != null)
{
StringBuilder sb = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
sb.Append("select ci.car_id,ci.car_no,ct.type_name,di.businessdivisionname,ti.ter_no,ci.car_adminname,ci.car_admincardid");
sbCount.Append("select count(*) ");
UserFields uf = new UserFields();
if (carInfo.DeptId != "" && carInfo.DeptId != null)
{
uf.DeptId = carInfo.Businessdivisionid;
}
else
{
uf.DeptId = user.EnterId;
}
IList <UserFields> iuf = userFieldsBll.GetUserFieldsPage(uf);
if (iuf.Count > 0)
{
sb.Append(",");
}
for (int i = 0; i < iuf.Count; i++)
{
if (i < (iuf.Count - 1))
{
sb.Append("tt." + iuf[i].UfName + ",");
}
else
{
sb.Append("tt." + iuf[i].UfName);
}
}
sb.Append(" from Car_Info ci left join car_type ct on ci.type_id=ct.type_id join dept_info di on ci.businessdivisionid=di.businessdivisionid left join terminal_info ti on ci.car_id=ti.car_id left join");
sbCount.Append(" from Car_Info ci left join car_type ct on ci.type_id=ct.type_id join dept_info di on ci.businessdivisionid=di.businessdivisionid left join terminal_info ti on ci.car_id=ti.car_id left join");
sb.Append(" (SELECT CAR_ID ");
sbCount.Append(" (SELECT CAR_ID ");
if (iuf.Count > 0)
{
sb.Append(",");
sbCount.Append(",");
}
for (int i = 0; i < iuf.Count; i++)
{
if (i < (iuf.Count - 1))
{
sb.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1},", iuf[i].UfId, iuf[i].UfName));
sbCount.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1},", iuf[i].UfId, iuf[i].UfName));
}
else
{
sb.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1} ", iuf[i].UfId, iuf[i].UfName));
sbCount.Append(string.Format("max(CASE UF_ID WHEN '{0}' THEN FIELD_VALUE ELSE '' END) as {1} ", iuf[i].UfId, iuf[i].UfName));
}
}
sb.Append(" FROM field_values GROUP BY CAR_ID) tt on ci.car_id=tt.car_id where 1=1");
sbCount.Append(" FROM field_values GROUP BY CAR_ID) tt on ci.car_id=tt.car_id where 1=1");
if (carInfo.CarNo != null && carInfo.CarNo.Trim() != "")
{
sb.Append(string.Format(" and ci.car_no like '%{0}%'", carInfo.CarNo));
sbCount.Append(string.Format(" and ci.car_no like '%{0}%'", carInfo.CarNo));
}
if (carInfo.TypeId != null && carInfo.TypeId.Trim() != "")
{
sb.Append(string.Format(" and ct.type_id='{0}'", carInfo.TypeId));
sbCount.Append(string.Format(" and ct.type_id='{0}'", carInfo.TypeId));
}
if (ChildrenSel != "true")
{
if (carInfo.Businessdivisionid != null && carInfo.Businessdivisionid.Trim() != "")
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", carInfo.Businessdivisionid));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", carInfo.Businessdivisionid));
}
else
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
}
}
else
{
DeptInfo di = null;
if (carInfo.Businessdivisionid != null || carInfo.Businessdivisionid.Trim() != "")
{
di = deptInfoBll.GetDeptInfo(carInfo.Businessdivisionid);
}
else
{
di = deptInfoBll.GetDeptInfo(user.EnterId);
}
sb.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
sbCount.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
}
sb.Append(" order by car_no ");
DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString(), page, rows);
DataSet dsCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString());
if (dsCount != null && ds != null && dsCount.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return(DtbConvertToJson(ds.Tables[0], Convert.ToInt32(dsCount.Tables[0].Rows[0][0].ToString())));
}
else
{
return(DtbConvertToJson(ds.Tables[0], 0));
}
}
else
{
return("");
}
}
public string GetYXTrailList(string CarId, string TerNo, DateTime st, DateTime ed)
{
//得到本车最新上传数据的终端编号
//string NewEastTerNo = "";
//if (CarId != "null" && CarId.Trim() != "")
//{
// DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("SELECT TI.TER_NO FROM REALTIME_DATA RD JOIN TERMINAL_INFO TI ON RD.TER_NO = TI.TER_NO JOIN CAR_INFO CI ON TI.CAR_ID = CI.CAR_ID and ti.car_id='{0}' order by rd.rtime desc", CarId));
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// NewEastTerNo = ds.Tables[0].Rows[0][0].ToString();
// }
//}
//else
//{
// NewEastTerNo = TerNo;
//}
string strTrackName = "";
string StartDate = "";
string EndDate = "";
List <HistoricalData> Listhrd = new List <HistoricalData>();
do
{
if (st.ToString("yyyyMMdd") == ed.ToString("yyyyMMdd"))
{
strTrackName = "ZTRACK" + st.ToString("yyyyMMdd");
StartDate = st.ToString("yyyy-MM-dd HH:mm:ss");
EndDate = ed.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
strTrackName = "ZTRACK" + st.ToString("yyyyMMdd");
StartDate = st.ToString("yyyy-MM-dd HH:mm:ss");
EndDate = st.ToString("yyyy-MM-dd") + " " + " 23:59:59";
}
DataSet dsYXTrailCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select * from user_tab_cols where table_name='" + strTrackName + "' and column_name='TER_NO'"));
if (dsYXTrailCount != null && dsYXTrailCount.Tables[0].Rows.Count > 0)
{
DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select ID AS Id,RTIME AS Rtime,nvl(POSITION,'设备周边无地理信息描述') AS Position,TER_NO AS TerNo,"
+ "REPLYDATANAME as ReplydataName,IFPOSITION AS Ifposition,BAIDU_LATITUDE AS BaiduLatitude,BAIDU_LONGITUDE AS BaiduLongitude,"
+ "GOOGLE_LATITUDE AS GoogleLatitude,GOOGLE_LONGITUDE AS GoogleLongitude from " + strTrackName + " where (GOOGLE_LATITUDE>15 and GOOGLE_LATITUDE<55) and (GOOGLE_LONGITUDE>70 and GOOGLE_LONGITUDE<140)"
+ " and TER_NO='" + TerNo + "' and RTIME BETWEEN to_date('" + StartDate + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate + "','yyyy-mm-dd hh24:mi:ss') order by RTIME desc"));
if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsYXTrail.Tables[0].Rows)
{
HistoricalData hd = new HistoricalData();
hd.Rtime = DateTime.Parse(dr["Rtime"].ToString());
hd.Position = dr["Position"].ToString();
hd.TerNo = dr["TerNo"].ToString();
hd.ReplydataName = dr["ReplydataName"].ToString();
hd.Ifposition = dr["Ifposition"].ToString();
hd.GoogleLatitude = double.Parse(dr["GoogleLatitude"].ToString());
hd.GoogleLongitude = double.Parse(dr["GoogleLongitude"].ToString());
hd.BaiduLatitude = double.Parse(dr["BaiduLatitude"].ToString());
hd.BaiduLongitude = double.Parse(dr["BaiduLongitude"].ToString());
Listhrd.Add(hd);
}
}
}
else
{
Hashtable ht = new Hashtable();
ht.Add("TerNo", TerNo);
ht.Add("EndData", 0);
ht.Add("st", StartDate);
ht.Add("ed", EndDate);
IList <HistoricalData> ihrd = historicalDataBll.GetHistorical(ht);
foreach (HistoricalData hid in ihrd)
{
Listhrd.Add(hid);
}
}
st = st.AddDays(1);
}while (st <= ed);
//string strTrackName = "ZTRACK" + st.ToString("yyyyMMdd");
//string StartDate = st.ToString("yyyy-MM-dd HH:mm:ss");
//string EndDate = ed.ToString("yyyy-MM-dd HH:mm:ss");
return(ConvertToJson(Listhrd));
}
public string GetCarList(int page, int rows)
{
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
if (user != null)
{
string CarNo = Request.Params["CarNo"].ToString();
string TerNo = Request.Params["TerNo"].ToString();
string DeptSelId = Request.Params["DeptSelId"].ToString();
string ChildrenSel = Request.Params["ChildrenSel"].ToString();
StringBuilder sb = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
sb.Append("select ci.car_id,ci.car_no,ct.type_name,di.businessdivisionname,ti.ter_no,ti.TER_SIMCARD");
sb.Append(" from terminal_info ti left join Car_Info ci on ci.car_id=ti.car_id left join car_type ct on ci.type_id=ct.type_id left join dept_info di on ti.dept_id=di.businessdivisionid");
sb.Append(" where 1=1");
sbCount.Append("select count(*) from terminal_info ti left join Car_Info ci on ci.car_id=ti.car_id left join car_type ct on ci.type_id=ct.type_id left join dept_info di on ti.dept_id=di.businessdivisionid where 1=1");
if (TerNo != null && TerNo.Trim() != "")
{
sb.Append(string.Format(" and ti.ter_no like '%{0}%'", TerNo));
sbCount.Append(string.Format(" and ti.ter_no like '%{0}%'", TerNo));
}
if (CarNo != null && CarNo.Trim() != "")
{
sb.Append(string.Format(" and ci.car_no like '%{0}%'", CarNo));
sbCount.Append(string.Format(" and ci.car_no like '%{0}%'", CarNo));
}
if (DeptSelId != null && DeptSelId.Trim() != "")
{
if (ChildrenSel != "true")
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", DeptSelId));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", DeptSelId));
}
else
{
DeptInfo di = null;
di = deptInfoBll.GetDeptInfo(DeptSelId);
sb.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
sbCount.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
}
}
else
{
if (ChildrenSel != "true")
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
}
else
{
DeptInfo di = null;
di = deptInfoBll.GetDeptInfo(user.EnterId);
sb.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
sbCount.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
}
}
sb.Append(" order by car_no ");
DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString(), page, rows);
DataSet dsCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(DtbConvertToJson(ds.Tables[0], int.Parse(dsCount.Tables[0].Rows[0][0].ToString())));
}
else
{
return(DtbConvertToJson(new DataTable(), 0));
}
}
else
{
return(DtbConvertToJson(new DataTable(), 0));
}
}
