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)); } }