/// <summary>
/// 添加文件
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Add(INFO_FILEINFO info)
{
bool isSuccess = true;
StringBuilder pInsertText = new StringBuilder();
pInsertText.Append("INSERT INTO INFO_FILEINFO(FILENAME,FILEURL,UPLOADTIME,FILESIZE,UPLOADUSER) VALUES('");
pInsertText.Append(info.FILENAME + "','" + info.FILEURL + "',TO_DATE('" + info.UPLOADTIME.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),'" + info.FILESIZE + "'," + info.UPLOADUSER + ")");
try
{
OracleOperateBLL.ExecuteSql(pInsertText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<INFO_FILEINFO> db = new DbBase<INFO_FILEINFO>();
//db.Insert(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 删除文件
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static bool Delete(int[] ids)
{
bool isSuccess = true;
foreach (var id in ids)
{
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("DELETE FROM INFO_FILEINFO WHERE ID=" + id);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
}
return(isSuccess);
//DbBase<INFO_FILEINFO> db = new DbBase<INFO_FILEINFO>();
//List<INFO_FILEINFO> infoList = new List<INFO_FILEINFO>();
//foreach (var id in ids)
//{
// db.Delete(p => p.ID == id);
//}
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
public static List <GpsData> GetList_User(string telephone, DateTime stTime, DateTime endTime)
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT LON,LAT,CREATE_TIME,ADDRESS FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(telephone) == false)
{
pSBQueryText.Append("AND PHONE='" + telephone + "' ");
}
pSBQueryText.Append(" ORDER BY CREATE_TIME");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <GpsData> gpsDataList = new List <GpsData>();
foreach (DataRow dr in data.Rows)
{
GpsData d = new GpsData();
d.Address = dr["ADDRESS"].ToString();
d.Create_Time = DateTime.Parse(dr["CREATE_TIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
var lonAndLat = GpsTranslate.gcj2bd(Convert.ToDouble(dr["LAT"].ToString()), Convert.ToDouble(dr["LON"].ToString()));
d.Lat = lonAndLat[0].ToString();
d.Lon = lonAndLat[1].ToString();
gpsDataList.Add(d);
}
return(gpsDataList);
}
/// <summary>
/// 添加车辆信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Add(CFG_CARINFO info)
{
bool isSuccess = true;
StringBuilder pInsertText = new StringBuilder();
pInsertText.Append("INSERT INTO CFG_CARINFO(VEHICLENO,VEHICLETYPE,VEHICLEBRAND,BELONGDEPTID,BELONGNETID,REMARK,ISDELETED,OWNER,OWNERTYPE,CLDWZDSBH,WLWKHM) VALUES('");
pInsertText.Append(info.VEHICLENO + "'," + info.VEHICLETYPE + ",'" + info.VEHICLEBRAND + "'," + info.BELONGDEPTID + "," + info.BELONGNETID + ",'" + info.REMARK + "'," + info.ISDELETED + ",'");
pInsertText.Append(info.OWNER + "'," + info.OWNERTYPE + ",'" + info.CLDWZDSBH + "','" + info.WLWKHM + "')");
try
{
OracleOperateBLL.ExecuteSql(pInsertText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_CARINFO> db = new DbBase<CFG_CARINFO>();
//db.Insert(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 修改车辆信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Update(CFG_CARINFO info)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("UPDATE CFG_CARINFO SET VEHICLENO='" + info.VEHICLENO + "',VEHICLETYPE=" + info.VEHICLETYPE + ",VEHICLEBRAND='" + info.VEHICLEBRAND + "',BELONGDEPTID=" + info.BELONGDEPTID + ",BELONGNETID=" + info.BELONGNETID + ",");
pUpdateText.Append("REMARK='" + info.REMARK + "',OWNER='" + info.OWNER + "',OWNERTYPE=" + info.OWNERTYPE + ",CLDWZDSBH='" + info.CLDWZDSBH + "',WLWKHM='" + info.WLWKHM + "' ");
pUpdateText.Append("WHERE ID=" + info.ID);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_CARINFO> db = new DbBase<CFG_CARINFO>();
//db.Update(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool Delete(int id)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("DELETE FROM CFG_USERINFO WHERE ID=" + id);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>();
//db.Delete(p => p.ID == id);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
public static List <GpsData> GetList_Car(string cldwzdsbh, DateTime stTime, DateTime endTime)
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT LONGITUDE_BAIDU,LATITUDE_BAIDU,PASSTIME FROM GPS_VEHICLEDATA A LEFT JOIN CFG_VEHICLEINFO B ON A.VEHID=B.ID WHERE A.PASSTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(cldwzdsbh) == false)
{
pSBQueryText.Append("AND B.CLDWZDSBH='" + cldwzdsbh + "' ");
}
pSBQueryText.Append(" ORDER BY A.PASSTIME DESC");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <GpsData> gpsDataList = new List <GpsData>();
foreach (DataRow dr in data.Rows)
{
GpsData d = new GpsData();
//d.Address = dr["ADDRESS"].ToString();
d.Create_Time = DateTime.Parse(dr["PASSTIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
d.Lat = Convert.ToDecimal(dr["LATITUDE_BAIDU"]).ToString("f6");
d.Lon = Convert.ToDecimal(dr["LONGITUDE_BAIDU"]).ToString("f6");
gpsDataList.Add(d);
}
return(gpsDataList);
}
/// <summary>
/// 添加用户信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Add(CFG_USERINFO info)
{
bool isSuccess = true;
StringBuilder pInsertText = new StringBuilder();
pInsertText.Append("INSERT INTO CFG_USERINFO(NAME,SEX,AGE,TELEPHONE,CERTIFICATETYPE,CERTIFICATENUM,DIRECTION,BELONGDEPTID,BELONGNETID,ADDRESS,REMARK,ISDELETED,USERTYPE,VIRTUALTRUMPET,USERNAME,USERPWD,LOGINTYPE) VALUES('");
pInsertText.Append(info.NAME + "'," + info.SEX + ",'" + info.AGE + "','" + info.TELEPHONE + "'," + info.CERTIFICATETYPE + ",'" + info.CERTIFICATENUM + "','" + info.DIRECTION + "',");
pInsertText.Append(info.BELONGDEPTID + "," + info.BELONGNETID + ",'" + info.ADDRESS + "','" + info.REMARK + "'," + info.ISDELETED + "," + info.USERTYPE + ",'");
pInsertText.Append(info.VIRTUALTRUMPET + "','" + info.USERNAME + "','" + info.USERPWD + "'," + info.LOGINTYPE + ")");
try
{
OracleOperateBLL.ExecuteSql(pInsertText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>();
//db.Insert(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Update(CFG_USERINFO info)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("UPDATE CFG_USERINFO SET NAME='" + info.NAME + "',SEX=" + info.SEX + ",AGE='" + info.AGE + "',TELEPHONE='" + info.TELEPHONE + "',CERTIFICATETYPE=" + info.CERTIFICATETYPE + ",");
pUpdateText.Append("CERTIFICATENUM='" + info.CERTIFICATENUM + "',DIRECTION='" + info.DIRECTION + "',BELONGDEPTID=" + info.BELONGDEPTID + ",BELONGNETID=" + info.BELONGNETID + ",ADDRESS='" + info.ADDRESS + "', ");
pUpdateText.Append("REMARK='" + info.REMARK + "',USERTYPE=" + info.USERTYPE + ",VIRTUALTRUMPET='" + info.VIRTUALTRUMPET + "',USERNAME='******',USERPWD='" + info.USERPWD + "',LOGINTYPE= " + info.LOGINTYPE + " ");
pUpdateText.Append("WHERE ID=" + info.ID);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>();
//db.Update(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 修改网格信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Update(CFG_NETINFO info)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("UPDATE CFG_NETINFO SET NAME='" + info.NAME + "',POPULATIONINFO='" + info.POPULATIONINFO + "',HOUSEINFO='" + info.HOUSEINFO + "',UNITSTOREINFO='" + info.UNITSTOREINFO + "',BELONGAREA='" + info.BELONGAREA + "',");
pUpdateText.Append("BELONGDEPTID=" + info.BELONGDEPTID + ",REMARK='" + info.REMARK + "',NETCOLOR='" + info.NETCOLOR + "',LONANDLAT='" + info.LONANDLAT + "' ");
pUpdateText.Append("WHERE ID=" + info.ID);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>();
//db.Update(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 获取所有
/// </summary>
/// <returns></returns>
public static string GetAllInfo()
{
var info = "";
var now = DateTime.Now;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT A.NAME,A.TELEPHONE AS OBJECTNAME,CASE WHEN B.PASSTIME IS NULL THEN '1753-01-01 00:00:00' ELSE TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') END AS PASSTIME FROM CFG_USERINFO A ");
pSBQueryText.Append("LEFT JOIN GPS_REAL B ON A.TELEPHONE = B.OBJECTNAME WHERE A.ISDELETED = 0 AND A.LOGINTYPE=40 ");
pSBQueryText.Append("UNION ALL ");
pSBQueryText.Append("SELECT A.VEHICLENO AS NAME,TO_CHAR(D.ID) AS OBJECTNAME,CASE WHEN B.PASSTIME IS NULL THEN '1753-01-01 00:00:00' ELSE TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') END AS PASSTIME FROM CFG_CARINFO A ");
pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.CLDWZDSBH = D.CLDWZDSBH LEFT JOIN GPS_REAL B ON A.VEHICLENO = B.OBJECTNAME AND B.OBJECTTYPE=1 WHERE A.ISDELETED = 0 ");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
foreach (DataRow dr in data.Rows)
{
var objectName = dr["OBJECTNAME"].ToString();
var passTime = dr["PASSTIME"].ToString();
if (string.IsNullOrWhiteSpace(objectName) == false)//&& now.Subtract(DateTime.Parse(passTime)).TotalSeconds <= 300)
{
info += objectName + ";";
}
}
return(info);
}
/// <summary>
/// 添加网格信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Add(CFG_NETINFO info)
{
bool isSuccess = true;
StringBuilder pInsertText = new StringBuilder();
pInsertText.Append("INSERT INTO CFG_NETINFO(NAME,POPULATIONINFO,HOUSEINFO,UNITSTOREINFO,BELONGAREA,BELONGDEPTID,REMARK,ISDELETED,NETCOLOR,LONANDLAT) VALUES('");
pInsertText.Append(info.NAME + "','" + info.POPULATIONINFO + "','" + info.HOUSEINFO + "','" + info.UNITSTOREINFO + "','" + info.BELONGAREA + "'," + info.BELONGDEPTID + ",'" + info.REMARK + "',");
pInsertText.Append(info.ISDELETED + ",'" + info.NETCOLOR + "','" + info.LONANDLAT + "')");
try
{
OracleOperateBLL.ExecuteSql(pInsertText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>();
//db.Insert(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 删除网格信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool Delete(int id)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("UPDATE CFG_NETINFO SET ISDELETED=1 WHERE ID=" + id);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>();
//var info = Get(id);
//info.ISDELETED = 1;
//db.Update(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 修改是否已读
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Update(MESSAGE info)
{
bool isSuccess = true;
StringBuilder pUpdateText = new StringBuilder();
pUpdateText.Append("UPDATE MESSAGE SET ISREAD_PLATFORM= " + info.ISREAD_PLATFORM + " ");
pUpdateText.Append("WHERE ID=" + info.ID);
try
{
OracleOperateBLL.ExecuteSql(pUpdateText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<MESSAGE> db = new DbBase<MESSAGE>();
//db.Update(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 添加报警信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public static bool Add(INFO_ALARMINFO info)
{
bool isSuccess = true;
StringBuilder pInsertText = new StringBuilder();
pInsertText.Append("INSERT INTO INFO_ALARMINFO(ALARMINFO,ALARMTIME,ALARMADDRESS,ALARMTYPE,ALARMOBJECTNAME) VALUES('");
pInsertText.Append(info.ALARMINFO + "',TO_DATE('" + info.ALARMTIME.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),'" + info.ALARMADDRESS + "','" + info.ALARMTYPE + "','" + info.ALARMOBJECTNAME + "')");
try
{
OracleOperateBLL.ExecuteSql(pInsertText.ToString());
}
catch
{
isSuccess = false;
}
return(isSuccess);
//DbBase<INFO_ALARMINFO> db = new DbBase<INFO_ALARMINFO>();
//db.Insert(info);
//if (db.SaveChanges() >= 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 获取报警列表
/// </summary>
/// <param name="objectName">对象名称</param>
/// <param name="alarmType">报警类型</param>
/// <param name="stTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <AlarmInfo> GetList(string objectName, int alarmType, DateTime stTime, DateTime endTime, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM INFO_ALARMINFO WHERE ALARMTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(objectName) == false)
{
pSBQueryCount.Append("AND ALARMOBJECTNAME LIKE '%" + objectName + "%'");
}
if (alarmType != -1)
{
pSBQueryCount.Append("AND ALARMTYPE = " + alarmType);
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,ALARMINFO,TO_CHAR(ALARMTIME, 'yyyy-mm-dd hh24:mi:ss') AS ALARMTIME,ALARMADDRESS,ALARMTYPE,ALARMOBJECTNAME FROM (SELECT A.*, rownum r FROM(");
pSBQueryText.Append("SELECT * FROM INFO_ALARMINFO WHERE ALARMTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(objectName) == false)
{
pSBQueryText.Append("AND ALARMOBJECTNAME LIKE '%" + objectName + "%'");
}
if (alarmType != -1)
{
pSBQueryText.Append("AND ALARMTYPE = " + alarmType);
}
pSBQueryText.Append(" ORDER BY ALARMTIME DESC) A ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") B WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <AlarmInfo> alarmInfoList = new List <AlarmInfo>();
foreach (DataRow dr in data.Rows)
{
AlarmInfo a = new AlarmInfo();
a.Id = Convert.ToInt32(dr["ID"].ToString());
a.Info = dr["ALARMINFO"].ToString();
a.AlarmTimeStr = dr["ALARMTIME"].ToString();
var type = Convert.ToInt16(dr["ALARMTYPE"].ToString());
if (type == 1)
{
a.AlarmTypeName = "人员报警";
}
else
{
a.AlarmTypeName = "车辆报警";
}
a.AlarmAddress = dr["ALARMADDRESS"].ToString();
a.AlarmObjectName = dr["ALARMOBJECTNAME"].ToString();
alarmInfoList.Add(a);
}
return(alarmInfoList);
}
/// <summary>
/// 更新消息
/// </summary>
/// <param name="ids"></param>
public static void UpdateMessage(List <int> ids)
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("UPDATE MESSAGE SET ISSHOW_PLATFORM=1 WHERE ID IN(" + string.Join(",", ids) + ")");
try
{
OracleOperateBLL.ExecuteSql(pSBQueryText.ToString());
}
catch
{
}
}
public static void AddLog(LogInfo info)
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("INSERT INTO SYS_LOG(USER_ID,USERNAME,OPERATION,TIME,METHOD,PARAMS,IP,GMT_CREATE) VALUES('" + info.User_Id + "','" + info.UserName + "','" + info.Operation + "','" + 0 + "','" + info.Method + "','" + info.Params + "','" + info.Ip + "'," + "sysdate)");
try
{
OracleOperateBLL.ExecuteSql(pSBQueryText.ToString());
}
catch
{
}
}
/// <summary>
/// 更新定位实时信息
/// </summary>
/// <param name="objectName"></param>
/// <returns></returns>
public static void UpdateGpsInfo(string objectName)
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("UPDATE GPS_REAL SET LASTALARMTIME=TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') WHERE OBJECTNAME='" + objectName + "'");
try
{
OracleOperateBLL.ExecuteSql(pSBQueryText.ToString());
}
catch
{
}
}
public static List <NetInfo> GetAll()
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT A.ID,A.BELONGAREA,A.BELONGDEPTID,B.BMVALUE AS BELONGDEPTNAME,A.NAME,A.POPULATIONINFO,A.HOUSEINFO,A.UNITSTOREINFO,A.REMARK,A.NETCOLOR,A.LONANDLAT FROM CFG_NETINFO A ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE B ON A.BELONGDEPTID=B.BMKEY WHERE A.ISDELETED=0 ");
pSBQueryText.Append(" ORDER BY A.NAME");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <NetInfo> netInfoList = new List <NetInfo>();
foreach (DataRow dr in data.Rows)
{
NetInfo d = new NetInfo();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.BelongArea = dr["BELONGAREA"].ToString();
d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString());
d.BelongDeptName = dr["BELONGDEPTNAME"].ToString();
d.Name = dr["NAME"].ToString();
d.PopulationInfo = dr["POPULATIONINFO"].ToString();
d.HouseInfo = dr["HOUSEINFO"].ToString();
d.UnitStoreInfo = dr["UNITSTOREINFO"].ToString();
d.Remark = dr["REMARK"].ToString();
d.NetColor = dr["NETCOLOR"].ToString();
d.LonAndLat = dr["LONANDLAT"].ToString();
netInfoList.Add(d);
}
return(netInfoList);
//var q = (from n in SlaveDb.Set<CFG_NETINFO>()
// join c in SlaveDb.Set<SYS_BM_CODE>() on n.BELONGDEPTID equals c.BMKEY into cc
// where n.ISDELETED == 0
// from ccc in cc.DefaultIfEmpty()
// select new NetInfo
// {
// Id = n.ID,
// BelongArea = n.BELONGAREA,
// BelongDeptName = ccc.BMVALUE,
// HouseInfo = n.HOUSEINFO,
// LonAndLat = n.LONANDLAT,
// PopulationInfo = n.POPULATIONINFO,
// Remark = n.REMARK,
// UnitStoreInfo = n.UNITSTOREINFO,
// NetColor=n.NETCOLOR,
// Name = n.NAME
// });
//return q.ToList();
}
/// <summary>
/// 修改用户密码
/// </summary>
/// <param name="userId"></param>
/// <param name="password"></param>
public static bool UpdateUserPwd(int userId, string password)
{
bool success = true;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("UPDATE CFG_USERINFO SET USERPWD='" + password + "' WHERE ID=" + userId + "");
try
{
OracleOperateBLL.ExecuteSql(pSBQueryText.ToString());
}
catch
{
success = false;
}
return(success);
}
/// <summary>
/// 登录系统
/// </summary>
/// <param name="userName">用户名</param>
/// <param name="userPwd">密码</param>
/// <returns></returns>
public static int Login(string userName, string userPwd, out UserInfo userInfo)
{
userInfo = null;
int pResult = -1; //-1:不存在该用户 0：密码不正确 1：登录成功
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,USERNAME,USERPWD,NAME,LOGINTYPE FROM CFG_USERINFO WHERE USERNAME='******' AND ISDELETED=0");
try
{
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
if (data.Rows.Count == 0)
{
pResult = -1;
}
else if (data.Rows[0]["USERPWD"].ToString() == userPwd)
{
//看是不是平台用户
if (Convert.ToInt32(data.Rows[0]["LOGINTYPE"]) == 39)
{
pResult = 1;
userInfo = new UserInfo();
userInfo.Id = Convert.ToInt32(data.Rows[0]["ID"]);
userInfo.UserName = data.Rows[0]["USERNAME"].ToString();
userInfo.UserPwd = data.Rows[0]["USERPWD"].ToString();
userInfo.Name = data.Rows[0]["NAME"].ToString();
}
else
{
pResult = -2;
}
}
else
{
pResult = 0;
}
}
catch (Exception error)
{
}
return(pResult);
}
/// <summary>
/// 获取人员轨迹列表
/// </summary>
/// <param name="telephone">电话号码</param>
/// <param name="stTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <GpsData> GetList_User(string telephone, DateTime stTime, DateTime endTime, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(telephone) == false)
{
pSBQueryCount.Append("AND PHONE='" + telephone + "'");
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT LON,LAT,CREATE_TIME,ADDRESS FROM (SELECT A.*, rownum r FROM(");
pSBQueryText.Append("SELECT * FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') ");
if (string.IsNullOrWhiteSpace(telephone) == false)
{
pSBQueryText.Append("AND PHONE='" + telephone + "' ");
}
pSBQueryText.Append(" ORDER BY CREATE_TIME DESC) A ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") B WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <GpsData> gpsDataList = new List <GpsData>();
foreach (DataRow dr in data.Rows)
{
GpsData d = new GpsData();
d.Address = dr["ADDRESS"].ToString();
d.Create_Time = DateTime.Parse(dr["CREATE_TIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
d.Lat = dr["LAT"].ToString();
d.Lon = dr["LON"].ToString();
gpsDataList.Add(d);
}
return(gpsDataList);
}
/// <summary>
/// 获取所有人员
/// </summary>
/// <returns></returns>
public static List <CFG_USERINFO> GetAll()
{
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,NAME,SEX,AGE,TELEPHONE,CERTIFICATETYPE,CERTIFICATENUM,DIRECTION,BELONGDEPTID,BELONGNETID,ADDRESS,REMARK,ISDELETED,USERTYPE,VIRTUALTRUMPET,USERNAME,USERPWD,LOGINTYPE FROM CFG_USERINFO WHERE ISDELETED=0");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <CFG_USERINFO> userInfoList = new List <CFG_USERINFO>();
foreach (DataRow dr in data.Rows)
{
CFG_USERINFO d = new CFG_USERINFO();
d.ID = Convert.ToInt32(dr["ID"]);
d.NAME = Convert.ToString(dr["NAME"]);
d.SEX = Convert.ToInt32(dr["SEX"]);
d.AGE = Convert.ToString(dr["AGE"]);
d.TELEPHONE = Convert.ToString(dr["TELEPHONE"]);
d.CERTIFICATETYPE = Convert.ToInt32(dr["CERTIFICATETYPE"]);
d.CERTIFICATENUM = Convert.ToString(dr["CERTIFICATENUM"]);
d.DIRECTION = Convert.ToString(dr["DIRECTION"]);
d.BELONGDEPTID = Convert.ToInt32(dr["BELONGDEPTID"]);
d.BELONGNETID = Convert.ToInt32(dr["BELONGNETID"]);
d.ADDRESS = Convert.ToString(dr["ADDRESS"]);
d.REMARK = Convert.ToString(dr["REMARK"]);
d.ISDELETED = Convert.ToInt32(dr["ISDELETED"]);
d.USERTYPE = Convert.ToInt32(dr["USERTYPE"]);
d.VIRTUALTRUMPET = Convert.ToString(dr["VIRTUALTRUMPET"]);
d.USERNAME = Convert.ToString(dr["USERNAME"]);
d.USERPWD = Convert.ToString(dr["USERPWD"]);
d.LOGINTYPE = Convert.ToInt32(dr["LOGINTYPE"]);
userInfoList.Add(d);
}
return(userInfoList);
//DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>();
//return db.GetAll(p => p.ISDELETED == 0, "NAME");
}
/// <summary>
/// 获取未显示过的消息
/// </summary>
/// <param name="phone"></param>
/// <returns></returns>
public static List <MessageInfo> GetMessageList(string phone)
{
List <MessageInfo> infoList = new List <MessageInfo>();
List <int> ids = new List <int>();
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,TITLE,ADDRESS,REMARKS FROM MESSAGE WHERE ISSHOW_PLATFORM=0 and PHONE='" + phone + "'");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
foreach (DataRow dr in data.Rows)
{
MessageInfo info = new MessageInfo();
info.Id = Convert.ToInt32(dr["ID"]);
info.Title = dr["TITLE"].ToString();
info.Address = dr["ADDRESS"].ToString();
info.Remarks = dr["REMARKS"].ToString();
ids.Add(info.Id);
infoList.Add(info);
}
UpdateMessage(ids);
return(infoList);
}
/// <summary>
/// 获取用户信息列表
/// </summary>
/// <param name="name"></param>
/// <param name="belongDeptId"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <UserInfo> GetList(string name, string telephone, int belongDeptId, int userType, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM CFG_USERINFO WHERE ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryCount.Append("AND NAME like '%" + name + "%' ");
}
if (string.IsNullOrWhiteSpace(telephone) == false)
{
pSBQueryCount.Append("AND TELEPHONE like '%" + telephone + "%' ");
}
if (userType != -1)
{
pSBQueryCount.Append("AND USERTYPE =" + userType + " ");
}
if (belongDeptId != -1)
{
pSBQueryCount.Append("AND BELONGDEPTID =" + belongDeptId);
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,NAME,SEXNAME,AGE,TELEPHONE,CERTIFICATENUM,ADDRESS,BELONGDEPTID,BELONGDEPTNAME,BELONGNETID,BELONGNETNAME,USERTYPE,USERTYPENAME,VIRTUALTRUMPET FROM (SELECT F.*, rownum r FROM(");
pSBQueryText.Append("SELECT A.ID,A.NAME,C.BMVALUE AS SEXNAME,A.AGE,A.TELEPHONE,A.CERTIFICATENUM,A.ADDRESS,A.BELONGDEPTID,D.BMVALUE AS BELONGDEPTNAME,A.BELONGNETID,B.NAME AS BELONGNETNAME,A.USERTYPE,E.BMVALUE AS USERTYPENAME,A.VIRTUALTRUMPET FROM CFG_USERINFO A ");
pSBQueryText.Append("LEFT JOIN CFG_NETINFO B ON A.BELONGNETID=B.ID ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE C ON A.SEX=C.BMKEY ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE D ON A.BELONGDEPTID=D.BMKEY ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE E ON A.USERTYPE=E.BMKEY WHERE A.ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryText.Append("AND A.NAME like '%" + name + "%' ");
}
if (string.IsNullOrWhiteSpace(telephone) == false)
{
pSBQueryText.Append("AND A.TELEPHONE like '%" + telephone + "%' ");
}
if (userType != -1)
{
pSBQueryText.Append("AND A.USERTYPE =" + userType + " ");
}
if (belongDeptId != -1)
{
pSBQueryText.Append("AND A.BELONGDEPTID =" + belongDeptId);
}
pSBQueryText.Append(" ORDER BY A.USERTYPE DESC,A.BELONGDEPTID DESC,A.BELONGNETID DESC,A.NAME DESC) F ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") G WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <UserInfo> userInfoList = new List <UserInfo>();
foreach (DataRow dr in data.Rows)
{
UserInfo d = new UserInfo();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.Name = dr["NAME"].ToString();
d.SexName = dr["SEXNAME"].ToString();
d.Age = dr["AGE"].ToString();
d.Telephone = dr["TELEPHONE"].ToString();
d.CertificateNum = dr["CERTIFICATENUM"].ToString();
d.Address = dr["ADDRESS"].ToString();
d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString());
d.BelongDeptName = dr["BELONGDEPTNAME"].ToString();
d.BelongNetId = Convert.ToInt32(dr["BELONGNETID"].ToString());
d.BelongNetName = dr["BELONGNETNAME"].ToString();
d.UserType = Convert.ToInt32(dr["USERTYPE"].ToString());
d.UserTypeName = dr["USERTYPENAME"].ToString();
d.VirtualTrumpet = dr["VIRTUALTRUMPET"].ToString();
userInfoList.Add(d);
}
return(userInfoList);
//var q = (from u in SlaveDb.Set<CFG_USERINFO>()
// join n in SlaveDb.Set<CFG_NETINFO>() on u.BELONGNETID equals n.ID into nn
// join s in SlaveDb.Set<SYS_BM_CODE>() on u.SEX equals s.BMKEY
// join d in SlaveDb.Set<SYS_BM_CODE>() on u.BELONGDEPTID equals d.BMKEY
// join t in SlaveDb.Set<SYS_BM_CODE>() on u.USERTYPE equals t.BMKEY
// from nnn in nn.DefaultIfEmpty()
// where ((name == "" || name == null) ? true : u.NAME.Contains(name))
// && ((telephone == "" || telephone == null) ? true : u.TELEPHONE.Contains(telephone))
// && (userType == -1 ? true : u.USERTYPE == userType)
// && (belongDeptId == -1 ? true : u.BELONGDEPTID == belongDeptId) && u.ISDELETED == 0
// select new UserInfo
// {
// Id = u.ID,
// Name = u.NAME,
// SexName = s.BMVALUE,
// Age = u.AGE,
// Telephone = u.TELEPHONE,
// CertificateNum = u.CERTIFICATENUM,
// Address = u.ADDRESS,
// BelongDeptId = u.BELONGDEPTID,
// BelongDeptName = d.BMVALUE,
// BelongNetId = u.BELONGNETID,
// BelongNetName = nnn.NAME,
// UserType = u.USERTYPE,
// UserTypeName = t.BMVALUE,
// VirtualTrumpet = u.VIRTUALTRUMPET
// });
//totalCount = q.Count();
//return q.OrderByDescending(p => p.UserType).ThenByDescending(p => p.BelongDeptId).ThenByDescending(p => p.BelongNetId).ThenBy(p => p.Name).Skip((start - 1) * limit).Take(limit).ToList();
}
/// <summary>
/// 获取最新定位信息
/// </summary>
/// <param name="objectName"></param>
/// <returns></returns>
public static List <GpsRealData> GetNewGpsInfo(string[] objectNames)
{
var now = DateTime.Now;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT A.OBJECTNAME AS OBJECTID,LON,LAT,CASE WHEN B.BELONGNETID IS NULL THEN C.BELONGNETID ELSE B.BELONGNETID END AS BELONGNETID,");
pSBQueryText.Append("CASE WHEN B.NAME IS NULL THEN C.VEHICLENO ELSE B.NAME END AS NAME,CASE WHEN B.NAME IS NULL THEN C.OWNER ELSE B.NAME END AS OWNER,TO_CHAR(A.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,A.ADDRESS,CASE WHEN B.NAME IS NULL THEN 2 ELSE 1 END AS TYPE,CASE WHEN B.NAME IS NULL THEN C.VEHICLETYPE ELSE 0 END AS OBJECTTYPE,TO_CHAR(A.LASTALARMTIME,'yyyy-mm-dd hh24:mi:ss') AS LASTALARMTIME FROM GPS_REAL A ");
pSBQueryText.Append("LEFT JOIN CFG_USERINFO B ON A.OBJECTNAME = B.TELEPHONE ");
pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.OBJECTNAME = D.ID AND A.OBJECTTYPE = 1 LEFT JOIN CFG_CARINFO C ON D.CLDWZDSBH=C.CLDWZDSBH WHERE A.OBJECTNAME IN('" + string.Join("','", objectNames) + "')");
//获取所有需要提醒的用户手机号码
var phone_Show = OracleOperateBLL.FillDataTable("SELECT DISTINCT PHONE FROM MESSAGE WHERE ISSHOW_PLATFORM=0");
List <string> phone_ShowList = new List <string>();
foreach (DataRow dr in phone_Show.Rows)
{
phone_ShowList.Add(dr["PHONE"].ToString());
}
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <GpsRealData> dataList = new List <GpsRealData>();
foreach (DataRow dr in data.Rows)
{
var objectId = dr["OBJECTID"].ToString();
var objectName = dr["NAME"].ToString();
var lon = dr["LON"].ToString();
var lat = dr["LAT"].ToString();
var belongNetId = dr["BELONGNETID"].ToString();
var passTime = dr["PASSTIME"].ToString();
var address = dr["ADDRESS"].ToString();
var type = dr["TYPE"].ToString();
var owner = dr["OWNER"].ToString();
var objectType = dr["OBJECTTYPE"].ToString();
var lastAlarmTime = dr["LASTALARMTIME"].ToString();
GpsRealData d = new GpsRealData();
d.ObjectId = objectId;
d.ObjectType = objectType;
if (string.IsNullOrWhiteSpace(lastAlarmTime))
{
d.IsNeedAlarm = true;
}
else //看最近一次报警时间与当前时间比较，小于一分钟不报警
{
var alarmTime = DateTime.Parse(lastAlarmTime);
if (now.Subtract(alarmTime).TotalSeconds >= 60)
{
d.IsNeedAlarm = true;
}
else
{
d.IsNeedAlarm = false;
}
}
if (type == "1") //人员
{
if (phone_ShowList.Contains(d.ObjectId))
{
d.IsNeedShow = true;
}
else
{
d.IsNeedShow = false;
}
d.ObjectName = objectName;
var lonAndLat = GpsTranslate.gcj2bd(Convert.ToDouble(lat), Convert.ToDouble(lon));
d.Lat = lonAndLat[0].ToString();
d.Lon = lonAndLat[1].ToString();
}
else
{
d.ObjectName = objectName;
d.Lon = lon;
d.Lat = lat;
}
d.BelongNetId = belongNetId;
d.Address = address;
d.Type = type;
//如果时间大于5min，表示离线
if (now.Subtract(DateTime.Parse(passTime)).TotalSeconds >= 300)
{
d.IsOffline = true;
}
else
{
d.IsOffline = false;
}
dataList.Add(d);
}
return(dataList);
}
/// <summary>
/// 获取网格信息列表
/// </summary>
/// <param name="name"></param>
/// <param name="belongDeptId"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <NetInfo> GetList(string name, int belongDeptId, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM CFG_NETINFO WHERE ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryCount.Append("AND NAME like '%" + name + "%' ");
}
if (belongDeptId != -1)
{
pSBQueryCount.Append("AND BELONGDEPTID =" + belongDeptId);
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,BELONGAREA,BELONGDEPTID,BELONGDEPTNAME,NAME,POPULATIONINFO,HOUSEINFO,UNITSTOREINFO,REMARK,NETCOLOR,LONANDLAT FROM (SELECT C.*, rownum r FROM(");
pSBQueryText.Append("SELECT A.ID,A.BELONGAREA,A.BELONGDEPTID,B.BMVALUE AS BELONGDEPTNAME,A.NAME,A.POPULATIONINFO,A.HOUSEINFO,A.UNITSTOREINFO,A.REMARK,A.NETCOLOR,A.LONANDLAT FROM CFG_NETINFO A ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE B ON A.BELONGDEPTID=B.BMKEY WHERE A.ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryText.Append("AND A.NAME like '%" + name + "%' ");
}
if (belongDeptId != -1)
{
pSBQueryText.Append("AND A.BELONGDEPTID =" + belongDeptId);
}
pSBQueryText.Append(" ORDER BY A.NAME) C ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") D WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <NetInfo> netInfoList = new List <NetInfo>();
foreach (DataRow dr in data.Rows)
{
NetInfo d = new NetInfo();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.BelongArea = dr["BELONGAREA"].ToString();
d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString());
d.BelongDeptName = dr["BELONGDEPTNAME"].ToString();
d.Name = dr["NAME"].ToString();
d.PopulationInfo = dr["POPULATIONINFO"].ToString();
d.HouseInfo = dr["HOUSEINFO"].ToString();
d.UnitStoreInfo = dr["UNITSTOREINFO"].ToString();
d.Remark = dr["REMARK"].ToString();
d.NetColor = dr["NETCOLOR"].ToString();
d.LonAndLat = dr["LONANDLAT"].ToString();
netInfoList.Add(d);
}
return(netInfoList);
//var q = (from n in SlaveDb.Set<CFG_NETINFO>()
// join c in SlaveDb.Set<SYS_BM_CODE>() on n.BELONGDEPTID equals c.BMKEY into cc
// where ((name == "" || name == null) ? true : n.NAME.Contains(name))
// && (belongDeptId == -1 ? true : n.BELONGDEPTID == belongDeptId) && n.ISDELETED == 0
// from ccc in cc.DefaultIfEmpty()
// select new NetInfo
// {
// Id = n.ID,
// BelongArea = n.BELONGAREA,
// BelongDeptName = ccc.BMVALUE,
// HouseInfo = n.HOUSEINFO,
// LonAndLat = n.LONANDLAT,
// PopulationInfo = n.POPULATIONINFO,
// Remark = n.REMARK,
// UnitStoreInfo = n.UNITSTOREINFO,
// NetColor=n.NETCOLOR,
// Name = n.NAME
// });
//totalCount = q.Count();
//return q.OrderByDescending(p => p.Name).Skip((start - 1) * limit).Take(limit).ToList();
}
/// <summary>
/// 获取树
/// </summary>
/// <returns></returns>
public static List <TreeNode> GetTree()
{
var now = DateTime.Now;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT * FROM (SELECT A.NAME,A.TELEPHONE AS OBJECTNAME,B.LON,B.LAT,TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,1 AS TYPE,B.ADDRESS,C.BMVALUE AS BELONGDEPTNAME,'' AS OWNER FROM CFG_USERINFO A ");
pSBQueryText.Append("LEFT JOIN GPS_REAL B ON A.TELEPHONE = B.OBJECTNAME LEFT JOIN SYS_BM_CODE C ON A.BELONGDEPTID = C.BMKEY WHERE A.ISDELETED = 0 AND A.LOGINTYPE=40 ORDER BY A.BELONGDEPTID,A.NAME)");
pSBQueryText.Append("UNION ALL ");
pSBQueryText.Append("SELECT * FROM(SELECT A.VEHICLENO AS NAME,TO_CHAR(D.ID) AS OBJECTNAME,B.LON,B.LAT,TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,2 AS TYPE,B.ADDRESS,C.BMVALUE AS BELONGDEPTNAME,A.OWNER FROM CFG_CARINFO A ");
pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.CLDWZDSBH = D.CLDWZDSBH LEFT JOIN GPS_REAL B ON D.ID = B.OBJECTNAME AND B.OBJECTTYPE=1 LEFT JOIN SYS_BM_CODE C ON A.BELONGDEPTID = C.BMKEY WHERE A.ISDELETED = 0 ORDER BY A.BELONGDEPTID,A.VEHICLENO)");
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <TreeInfo> infoList = new List <TreeInfo>();
foreach (DataRow dr in data.Rows)
{
var name = dr["NAME"].ToString();
var objectName = dr["OBJECTNAME"].ToString();
var lon = dr["LON"].ToString();
var lat = dr["LAT"].ToString();
var passTime = dr["PASSTIME"].ToString();
var type = dr["TYPE"].ToString();
var address = dr["ADDRESS"].ToString();
var belongDeptName = dr["BELONGDEPTNAME"].ToString();
var owner = dr["OWNER"].ToString();
TreeInfo info = new TreeInfo();
info.Name = name;
info.ObjectName = objectName;
info.Lon = lon;
info.Lat = lat;
info.PassTime = passTime;
info.Type = type;
info.Address = address;
info.BelongDeptName = belongDeptName;
info.Owner = owner;
infoList.Add(info);
}
List <TreeNode> treeNodeList = new List <TreeNode>();
TreeNode userNode = new TreeNode();
userNode.name = "人员";
userNode.open = true;
userNode.@checked = true;
userNode.id = "1";
int userOffline = 0;
int userCount = 0;
//找到所有人员信息
var userInfoList = infoList.Where(p => p.Type == "1").ToList();
var belongDeptList_User = infoList.Where(p => p.Type == "1").Select(p => p.BelongDeptName).Distinct().OrderBy(p => p).ToList();
foreach (var dept in belongDeptList_User)
{
TreeNode deptNode = new TreeNode();
deptNode.open = false;
deptNode.@checked = true;
var id = "1_" + belongDeptList_User.IndexOf(dept);
deptNode.id = id;
var userList = infoList.Where(p => p.Type == "1" && p.BelongDeptName == dept).ToList();
var userOffline_Dept = 0;
var userCount_Dept = 0;
foreach (var u in userList)
{
var id_child = id + "_" + userList.IndexOf(u);
TreeNode n = new TreeNode();
n.objectName = u.ObjectName;
if (string.IsNullOrWhiteSpace(u.PassTime)) //实时表没有数据，表示离线
{
n.name = u.Name + "(离线)";
n.id = id_child;
n.iconSkin = "dark";
userOffline++;
userOffline_Dept++;
}
else
{
//如果时间大于5min，表示离线
if (now.Subtract(DateTime.Parse(u.PassTime)).TotalSeconds >= 300)
{
n.name = u.Name + "(" + u.PassTime + "后离线)";
n.id = id_child;
n.iconSkin = "dark";
userOffline++;
userOffline_Dept++;
}
else
{
n.name = u.Name;
n.id = id_child;
n.iconSkin = "light";
}
}
n.@checked = true;
deptNode.children.Add(n);
userCount++;
userCount_Dept++;
}
deptNode.name = dept + "(" + (userCount_Dept - userOffline_Dept) + "/" + userCount_Dept + ")";
userNode.children.Add(deptNode);
}
userNode.name = "人员(" + (userCount - userOffline) + "/" + userCount + ")";
TreeNode carNode = new TreeNode();
carNode.name = "车辆";
carNode.open = true;
carNode.@checked = true;
carNode.id = "2";
int carOffline = 0;
int carCount = 0;
//找到所有车辆信息
var carInfoList = infoList.Where(p => p.Type == "2").ToList();
var belongDeptList_Car = infoList.Where(p => p.Type == "2").Select(p => p.BelongDeptName).Distinct().OrderBy(p => p).ToList();
foreach (var dept in belongDeptList_Car)
{
TreeNode deptNode = new TreeNode();
deptNode.open = false;
deptNode.@checked = true;
var id = "1_" + belongDeptList_Car.IndexOf(dept);
deptNode.id = id;
var carList = infoList.Where(p => p.Type == "2" && p.BelongDeptName == dept).ToList();
int carOffline_Dept = 0;
int carCount_Dept = 0;
foreach (var c in carList)
{
var id_child = id + "_" + carList.IndexOf(c);
TreeNode n = new TreeNode();
n.objectName = c.ObjectName;
if (string.IsNullOrWhiteSpace(c.PassTime)) //实时表没有数据，表示离线
{
n.name = c.Name + "(" + c.Owner + ")" + "(离线)";
n.id = id_child;
n.iconSkin = "dark";
carOffline++;
carOffline_Dept++;
}
else
{
//如果时间大于5min，表示离线
if (now.Subtract(DateTime.Parse(c.PassTime)).TotalSeconds >= 300)
{
n.name = c.Name + "(" + c.Owner + ")" + "(" + c.PassTime + "后离线)";
n.id = id_child;
n.iconSkin = "dark";
carOffline++;
carOffline_Dept++;
}
else
{
n.name = c.Name + "(" + c.Owner + ")";
n.id = id_child;
n.iconSkin = "light";
}
}
n.@checked = true;
deptNode.children.Add(n);
carCount++;
carCount_Dept++;
}
deptNode.name = dept + "(" + (carCount_Dept - carOffline_Dept) + "/" + carCount_Dept + ")";
carNode.children.Add(deptNode);
}
carNode.name = "车辆(" + (carCount - carOffline) + "/" + carCount + ")";
if (userNode.children.Count > 0)
{
treeNodeList.Add(userNode);
}
if (carNode.children.Count > 0)
{
treeNodeList.Add(carNode);
}
return(treeNodeList);
}
/// <summary>
/// 获取人员列表
/// </summary>
/// <returns></returns>
public static Dictionary <string, Dictionary <string, string> > GetUserList()
{
Dictionary <string, Dictionary <string, string> > m_dic = new Dictionary <string, Dictionary <string, string> >();
Dictionary <string, bool> m_userDic = new Dictionary <string, bool>();
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT A.TELEPHONE,A.ID,A.NAME,A.CERTIFICATENUM,B.BMVALUE FROM CFG_USERINFO A LEFT JOIN SYS_BM_CODE B ON A.USERTYPE=B.BMKEY WHERE A.ISDELETED = 0 ORDER BY USERTYPE, NAME");
try
{
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
foreach (DataRow dr in data.Rows)
{
var userName = dr["NAME"].ToString();
if (m_userDic.ContainsKey(userName) == false)
{
m_userDic.Add(userName, false);
}
else
{
m_userDic[userName] = true;
}
}
foreach (DataRow dr in data.Rows)
{
var userType = dr["BMVALUE"].ToString();
var userId = dr["ID"].ToString();
var userTelephone = dr["TELEPHONE"].ToString();
var userName = dr["NAME"].ToString();
var userCertificateNum = dr["CERTIFICATENUM"].ToString();
if (string.IsNullOrWhiteSpace(userType))
{
userType = "未分类";
}
//看是不是已经存在用户类型
if (m_dic.ContainsKey(userType))
{
var value = m_dic[userType];
if (value.ContainsKey(userId + ";" + userTelephone) == false)
{
//看是不是同名
if (m_userDic.ContainsKey(userName))
{
var isSame = m_userDic[userName];
if (isSame)
{
value.Add(userId + ";" + userTelephone, userName + "(" + userCertificateNum + ")");
}
else
{
value.Add(userId + ";" + userTelephone, userName);
}
}
else
{
value.Add(userId + ";" + userTelephone, userName);
}
}
}
else
{
m_dic.Add(userType, new Dictionary <string, string>());
var value = m_dic[userType];
if (value.ContainsKey(userId + ";" + userTelephone) == false)
{
//看是不是同名
if (m_userDic.ContainsKey(userName))
{
var isSame = m_userDic[userName];
if (isSame)
{
value.Add(userId + ";" + userTelephone, userName + "(" + userCertificateNum + ")");
}
else
{
value.Add(userId + ";" + userTelephone, userName);
}
}
else
{
value.Add(userId + ";" + userTelephone, userName);
}
}
}
}
}
catch
{
}
return(m_dic);
}
