public DataTable getFacility(string where)
{
string mySql = string.Empty;
try
{
mySql = " select id,facilities_id,facilities_name,facilities_mode, facilities_type,department,deviceuse,facilities_even,Intersection_Number,Manufacturer from t_dev_facilities where " + where + " ";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
/// 判断该车辆是否存在
/// </summary>
/// <param name="hphm">号牌号码</param>
/// <param name="hpzl">号牌种类</param>
/// <returns></returns>
public DataTable GetBkbh(string hphm, string hpzl)
{
string mySql = string.Empty;
try
{
mySql = "select * from t_tgs_suspect where hphm ='" + hphm + "' and hpzl= '" + hpzl + "'";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
public DataTable GetEngineroom(string where)
{
string mySql = string.Empty;
try
{
mySql = " select ID,ENGINEROOM_ID,ENGINEROOM_NAME,f_to_name('241011',ENGINEROOM_TYPE) as ENGINEROOM_TYPE,ENGINEROOM_DRAND,ENGINEROOM_IP,to_char(ENGINEROOM_TIME,'yyyy-mm-dd hh24:mi:ss') as ENGINEROOM_TIME,DEPARTMENT,USERNAME,PASSWORD,PROCESSOR,PROCESSORCOUNT,MEMORYSPACE,";
mySql += " MEMORYSLOTS,MEMORYTYPE,NETWORKCONTROLLER,HARDDISK,HARDDISK_TYPE,POWERSUPPLY_TYPE,PROCESSORCACHE,INFRASTRUCTURE_MANAGEMENT,DRIVE_TYPE,SHAPE,WARRANTY ";
mySql += " from T_DEV_ENGINEROOM where 1=1 " + where + " order by ENGINEROOM_TIME asc";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
/// 执行查询datatable操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataTable GetDataTable(string mySql)
{
try
{
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(ex.Message);
return(null);
}
}
/// <summary>
/// 查询方法
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public DataTable GetNoticePic(string where)
{
string mySql = string.Empty;
try
{
mySql = "SELECT id, picName, NAME, picDisc, wfxw, lrr, lrsj, priUrl FROM t_cfg_noticepic WHERE " + where;
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
/// 查询业务日志
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public DataTable GetLogBusiness(string where)
{
string mySql = string.Empty;
try
{
mySql = "select a.* from t_log_Business a where " + where + " order by recordtime desc";;
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
/// 读取字典项
/// </summary>
/// <param name="codeType"></param>
/// <returns></returns>
public DataTable GetCode(string codeType)
{
string mySql = string.Empty;
try
{
mySql = "select * from t_sys_code where codeType='" + codeType + "'";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
public DataTable GetUserStationInfo(string userid, string shjb)
{
string mySql = string.Empty;
try
{
mySql = "select a.* from t_cfg_set_station a, t_tms_user_station b where a.station_id=b.station_id and b.check_times='" + shjb + "' and b.user_id='" + userid + "'";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
/// 获得过车小时流量统计
/// </summary>
/// <param name="datetime"></param>
/// <returns></returns>
public System.Data.DataTable GetPassCarCountHour(string datetime)
{
StringBuilder mySql = new StringBuilder();
try
{
mySql.Append(" select time_id AS hour1, CONVERT(IFNULL(ll,'0'),DECIMAL) as ll from ");
mySql.Append(" (select xs,SUM(ll) AS ll from t_tgs_flow_hour_count t where rq=STR_TO_DATE('" + datetime + "','%Y-%m-%d') GROUP BY xs) a RIGHT JOIN");
mySql.Append(" t_cfg_time b ON a.xs = b.time_id WHERE time_type = '1' ORDER BY hour1 ASC");
ILog.WriteErrorLog(mySql.ToString());
return(dataAccess.Get_DataTable(mySql.ToString()));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql.ToString() + ex.Message);
return(null);
}
}
public DataTable GetUserSystem(string userName)
{
string mySql = string.Empty;
try
{
mySql = "SELECT systemid, a.usercode ,username FROM t_cfg_sysuser a,t_ser_register b WHERE a.usercode=b.usercode AND username='******'";
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public DataTable GetMapLable(string where)
{
string mySql = string.Empty;
try
{
mySql = "select point_id,point_x,point_y,point_type,f_to_names ('240040',point_type) as point_typedesc,point_name,point_tag,point_image,point_button from t_tgs_map_lable where " + where;
return(dataAccess.Get_DataTable(mySql));
}
catch (Exception ex)
{
ILog.WriteErrorLog(mySql + ex.Message);
return(null);
}
}
public DataTable GetRoadSegList(string strWhere)
{
string sql = "select distinct a.id, f_get_value ('dlmc','t_gis_road','roadid', dlbh) as dlmc,ldmc,decode(ldfx,'0','下行','1','上行') as fxmc,gxsj,ldfx,dlbh,f_get_value ('dllx','t_gis_road','roadid', dlbh) as dllx from t_gis_road_seg a where " + strWhere;
return(dataAccess.Get_DataTable(sql));
}
public DataTable GetIllegalDetail(string zqlx, string nd, string kkid)
{
StringBuilder strSql = new StringBuilder();
try
{
string strwhere = "where ", wfsj = "";
switch (dataAccess.DataBaseType.ToUpper())
{
case "MYSQL":
switch (zqlx)
{
case "0": //日
strwhere = strwhere + "rq=STR_TO_DATE('" + nd + "', '%Y%m%d')";
wfsj = " wfsj=STR_TO_DATE('" + nd + "', '%Y%m%d')";
break;
case "1": //月
strwhere = strwhere + " DATE_FORMAT(rq,'%Y%m')='" + nd + "'";
wfsj = " DATE_FORMAT(wfsj,'%Y%m')='" + nd + "'";
break;
case "2": //周
strwhere = strwhere + " WEEK(rq)='" + nd + "'";
wfsj = " WEEK(wfsj)='" + nd + "'";
break;
case "3": //年
strwhere = strwhere + " DATE_FORMAT(rq,'%Y')='" + nd + "'";
wfsj = " DATE_FORMAT(wfsj,'%Y')='" + nd + "'";
break;
}
break;
case "ORACLE":
switch (zqlx)
{
case "0": //日
strwhere = strwhere + "rq=TO_DATE('" + nd + "', 'YYYY-mm-dd')";
wfsj = " wfsj=TO_DATE('" + nd + "', 'YYYY-mm-dd')";
break;
case "1": //月
strwhere = strwhere + " TO_DATE(rq,'%Y%m')='" + nd + "'";
wfsj = " TO_DATE(wfsj,'%Y%m')='" + nd + "'";
break;
case "2": //周
strwhere = strwhere + " WEEK(rq)='" + nd + "'";
wfsj = " WEEK(wfsj)='" + nd + "'";
break;
case "3": //年
strwhere = strwhere + " TO_DATE(rq,'%Y')='" + nd + "'";
wfsj = " TO_DATE(wfsj,'%Y')='" + nd + "'";
break;
}
break;
}
strSql.Append("SELECT a.*,b.STATION_NAME,b.STATION_ID,CONCAT(c.wfxw,'_',c.wfxwjc) wfxwname,d.zs,CONCAT(FORMAT((a.wfzs/zs*100),2),'%') wfbl,xpoint,ypoint FROM ");
strSql.Append("(SELECT xh,wfdd AS kkid,wfxw,SUM(zs) AS wfzs FROM t_tms_peccancy_count WHERE " + wfsj + " GROUP BY wfdd,wfxw) a ");
strSql.Append("INNER JOIN (SELECT sta.*,mark.xpoint,mark.ypoint FROM T_CFG_SET_STATION sta INNER JOIN t_gis_device_mark mark ON sta.station_id=mark.relationid WHERE station_id='" + kkid + "') b ON a.kkid=b.STATION_ID ");
strSql.Append("INNER JOIN t_tms_peccnacy_type c ON a.wfxw=c.wfxw ");
strSql.Append("LEFT JOIN (SELECT kkid ,SUM(zs) zs FROM T_TGS_PASSCAR_COUNT_DAY " + strwhere);
switch (dataAccess.DataBaseType.ToUpper())
{
case "MYSQL":
strSql.Append("GROUP BY kkid ) d ON a.kkid=d.kkid ORDER BY wfzs DESC ");
break;
case "ORACLE":
strSql.Append("GROUP BY kkid ) d ON a.kkid=d.kkid ORDER BY wfzs DESC ROWNUM<10");
break;
}
return(dataAccess.Get_DataTable(strSql.ToString()));
}
catch (Exception ex)
{
ILog.WriteErrorLog(strSql + ex.Message);
return(null);
}
}