/// <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="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>
/// <param name="cldwzdsbh"></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_Car(string cldwzdsbh, DateTime stTime, DateTime endTime, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) 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)
{
pSBQueryCount.Append("AND B.CLDWZDSBH='" + cldwzdsbh + "'");
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT LONGITUDE_BAIDU,LATITUDE_BAIDU,PASSTIME FROM (SELECT C.*, rownum r FROM(");
pSBQueryText.Append("SELECT * 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) C ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") D 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["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="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="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>
/// <param name="username">用户名称</param>
/// <param name="title">标题</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <MessageInfo> GetList(string username, string title, DateTime startTime, DateTime endTime, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM MESSAGE A LEFT JOIN CFG_USERINFO B ON A.PHONE=B.TELEPHONE WHERE TO_CHAR(A.CREATE_DATE,'yyyymmddhh24miss') BETWEEN '" + startTime.ToString("yyyyMMddHHmmss") + "' AND '" + endTime.ToString("yyyyMMddHHmmss") + "' ");
if (string.IsNullOrWhiteSpace(title) == false)
{
pSBQueryCount.Append("AND A.TITLE like '%" + title + "%' ");
}
if (string.IsNullOrWhiteSpace(username) == false)
{
pSBQueryCount.Append("AND B.NAME like '%" + username + "%' ");
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,TITLE,PHONE,ADDRESS,REMARKS,CREATE_DATE,NAME FROM (SELECT C.*, rownum r FROM(");
pSBQueryText.Append("SELECT A.ID,A.TITLE,A.PHONE,A.ADDRESS,A.REMARKS,TO_CHAR(A.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss') AS CREATE_DATE,B.NAME FROM MESSAGE A ");
pSBQueryText.Append("LEFT JOIN CFG_USERINFO B ON A.PHONE=B.TELEPHONE WHERE TO_CHAR(A.CREATE_DATE,'yyyymmddhh24miss') BETWEEN '" + startTime.ToString("yyyyMMddHHmmss") + "' AND '" + endTime.ToString("yyyyMMddHHmmss") + "' ");
if (string.IsNullOrWhiteSpace(title) == false)
{
pSBQueryText.Append("AND A.TITLE like '%" + title + "%' ");
}
if (string.IsNullOrWhiteSpace(username) == false)
{
pSBQueryText.Append("AND B.NAME like '%" + username + "%' ");
}
pSBQueryText.Append(" ORDER BY A.CREATE_DATE DESC,A.TITLE DESC) C ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") D WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <MessageInfo> messageInfoList = new List <MessageInfo>();
foreach (DataRow dr in data.Rows)
{
MessageInfo d = new MessageInfo();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.Title = dr["TITLE"].ToString();
d.Phone = dr["PHONE"].ToString();
d.Address = dr["ADDRESS"].ToString();
d.Remarks = dr["REMARKS"].ToString();
d.Name = dr["NAME"].ToString();
d.Create_Date = Convert.ToDateTime(dr["CREATE_DATE"].ToString());
d.UploadDate = dr["CREATE_DATE"].ToString();
messageInfoList.Add(d);
}
return(messageInfoList);
//var q = (from m in SlaveDb.Set<MESSAGE>()
// join u in SlaveDb.Set<CFG_USERINFO>() on m.PHONE equals u.TELEPHONE into uu
// from uuu in uu.DefaultIfEmpty()
// where ((title == "" || title == null) ? true : m.TITLE.Contains(title))
// && ((username == "" || username == null) ? true : uuu.NAME.Contains(username))
// && m.CREATE_DATE >= startTime && m.CREATE_DATE <= endTime
// select new MessageInfo
// {
// Id = m.ID,
// Title = m.TITLE,
// Phone = m.PHONE,
// Address = m.ADDRESS,
// Name = uuu.NAME,
// Create_Date = m.CREATE_DATE
// });
//totalCount = q.Count();
//var data = q.OrderByDescending(p => p.Create_Date).ThenByDescending(p => p.Title).Skip((start - 1) * limit).Take(limit).ToList();
//foreach (var d in data)
//{
// d.UploadDate = d.Create_Date.ToString("yyyy-MM-dd HH:mm:ss");
//}
//return data;
}
/// <summary>
/// 获取文件列表
/// </summary>
/// <param name="name"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <FileInfos> GetList(string name, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM INFO_FILEINFO ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryCount.Append("WHERE FILENAME like '%" + name + "%'");
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,FILENAME,FILEURL,UPLOADTIME,FILESIZE,UPLOADUSER,NAME FROM (SELECT C.*, rownum r FROM(");
pSBQueryText.Append("SELECT A.ID,A.FILENAME,A.FILEURL,TO_CHAR(A.UPLOADTIME,'yyyy-mm-dd hh24:mi:ss') AS UPLOADTIME,A.FILESIZE,A.UPLOADUSER,B.NAME FROM INFO_FILEINFO A ");
pSBQueryText.Append("LEFT JOIN CFG_USERINFO B ON A.UPLOADUSER=B.ID ");
if (string.IsNullOrWhiteSpace(name) == false)
{
pSBQueryText.Append("WHERE A.FILENAME like '%" + name + "%' ");
}
pSBQueryText.Append(" ORDER BY A.UPLOADTIME DESC) C ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") D WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <FileInfos> fileInfosList = new List <FileInfos>();
foreach (DataRow dr in data.Rows)
{
FileInfos d = new FileInfos();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.FileName = dr["FILENAME"].ToString();
d.FileUrl = dr["FILEURL"].ToString();
d.UploadTime = DateTime.Parse(dr["UPLOADTIME"].ToString());
d.UploadTimeStr = dr["UPLOADTIME"].ToString();
d.FileSize = dr["FILESIZE"].ToString();
d.UploadUser = dr["NAME"].ToString();
fileInfosList.Add(d);
}
return(fileInfosList);
//var q = (from f in SlaveDb.Set<INFO_FILEINFO>()
// join u in SlaveDb.Set<CFG_USERINFO>() on f.UPLOADUSER equals u.ID into uu
// from uuu in uu.DefaultIfEmpty()
// where ((name == "" || name == null) ? true : f.FILENAME.Contains(name))
// select new FileInfos
// {
// Id = f.ID,
// FileName = f.FILENAME,
// FileUrl = f.FILEURL,
// UploadTime = f.UPLOADTIME,
// FileSize = f.FILESIZE,
// UploadUser = uuu.NAME
// });
//totalCount = q.Count();
//var data = q.OrderByDescending(p => p.UploadTime).Skip((start - 1) * limit).Take(limit).ToList();
//foreach(var d in data)
//{
// d.UploadTimeStr = d.UploadTime.ToString("yyyy-MM-dd HH:mm:ss");
//}
//return data;
}
/// <summary>
/// 获取车辆信息列表
/// </summary>
/// <param name="vehicleNo"></param>
/// <param name="ownerType"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <CarInfo> GetList(string vehicleNo, int ownerType, int start, int limit, out int totalCount)
{
StringBuilder pSBQueryCount = new StringBuilder();
pSBQueryCount.Append("SELECT COUNT(*) FROM CFG_CARINFO WHERE ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(vehicleNo) == false)
{
pSBQueryCount.Append("AND VEHICLENO like '%" + vehicleNo + "%' ");
}
if (ownerType != -1)
{
pSBQueryCount.Append("AND OWNERTYPE =" + ownerType);
}
totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString());
int pStartNum = limit * (start - 1) + 1;
int pEndNum = limit * start;
StringBuilder pSBQueryText = new StringBuilder();
pSBQueryText.Append("SELECT ID,VEHICLENO,VEHICLETYPE,VEHICLETYPENAME,VEHICLEBRAND,BELONGDEPTID,BELONGDEPTNAME,BELONGNETID,BELONGNETNAME,OWNER,OWNERTYPE,OWNERTYPENAME,CLDWZDSBH,WLWKHM FROM (SELECT F.*, rownum r FROM(");
pSBQueryText.Append("SELECT A.ID,A.VEHICLENO,A.VEHICLETYPE,C.BMVALUE AS VEHICLETYPENAME,A.VEHICLEBRAND,A.BELONGDEPTID,D.BMVALUE AS BELONGDEPTNAME,A.BELONGNETID,B.NAME AS BELONGNETNAME,A.OWNER,A.OWNERTYPE,E.BMVALUE AS OWNERTYPENAME,A.CLDWZDSBH,A.WLWKHM FROM CFG_CARINFO A ");
pSBQueryText.Append("LEFT JOIN CFG_NETINFO B ON A.BELONGNETID = B.ID ");
pSBQueryText.Append("LEFT JOIN SYS_BM_CODE C ON A.VEHICLETYPE = 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.OWNERTYPE = E.BMKEY WHERE A.ISDELETED=0 ");
if (string.IsNullOrWhiteSpace(vehicleNo) == false)
{
pSBQueryText.Append("AND A.VEHICLENO like '%" + vehicleNo + "%' ");
}
if (ownerType != -1)
{
pSBQueryText.Append("AND A.OWNERTYPE =" + ownerType);
}
pSBQueryText.Append(" ORDER BY A.OWNERTYPE DESC,A.BELONGDEPTID DESC,A.VEHICLENO DESC) F ");
pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") G WHERE r>=" + pStartNum);
var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString());
List <CarInfo> carInfoList = new List <CarInfo>();
foreach (DataRow dr in data.Rows)
{
CarInfo d = new CarInfo();
d.Id = Convert.ToInt32(dr["ID"].ToString());
d.VehicleNo = dr["VEHICLENO"].ToString();
d.VehicleType = Convert.ToInt32(dr["VEHICLETYPE"].ToString());
d.VehicleTypeName = dr["VEHICLETYPENAME"].ToString();
d.VehicleBrand = dr["VEHICLEBRAND"].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.Owner = dr["OWNER"].ToString();
d.OwnerType = Convert.ToInt32(dr["OWNERTYPE"].ToString());
d.OwnerTypeName = dr["OWNERTYPENAME"].ToString();
d.Cldwzdsbh = dr["CLDWZDSBH"].ToString();
d.Wlwkhm = dr["WLWKHM"].ToString();
carInfoList.Add(d);
}
return(carInfoList);
//var q = (from c in SlaveDb.Set<CFG_CARINFO>()
// join n in SlaveDb.Set<CFG_NETINFO>() on c.BELONGNETID equals n.ID
// join v in SlaveDb.Set<SYS_BM_CODE>() on c.VEHICLETYPE equals v.BMKEY
// join d in SlaveDb.Set<SYS_BM_CODE>() on c.BELONGDEPTID equals d.BMKEY
// join o in SlaveDb.Set<SYS_BM_CODE>() on c.OWNERTYPE equals o.BMKEY into oo
// from ooo in oo.DefaultIfEmpty()
// where ((vehicleNo == "" || vehicleNo == null) ? true : c.VEHICLENO.Contains(vehicleNo))
// && ((ownerType == -1) ? true : c.OWNERTYPE == ownerType) && c.ISDELETED == 0
// select new CarInfo
// {
// Id = c.ID,
// VehicleNo = c.VEHICLENO,
// VehicleType = c.VEHICLETYPE,
// VehicleTypeName = v.BMVALUE,
// VehicleBrand = c.VEHICLEBRAND,
// BelongDeptId = c.BELONGDEPTID,
// BelongDeptName = d.BMVALUE,
// BelongNetName = n.NAME,
// Owner = c.OWNER,
// OwnerType = c.OWNERTYPE,
// OwnerTypeName = ooo.BMVALUE,
// Cldwzdsbh = c.CLDWZDSBH,
// Wlwkhm = c.WLWKHM
// });
//totalCount = q.Count();
//return q.OrderByDescending(p => p.OwnerType).ThenByDescending(p => p.BelongDeptId).ThenBy(p => p.VehicleNo).Skip((start - 1) * limit).Take(limit).ToList();
}