public static Hashtable SearchYALI(string strWhere, int index, int size, string sortField, string sortOrder)
{
String sql = @"select BASEID,FCustomerID,a.FDTUCode,FName,FMpaUp,FMpaDown,FCreateDate
,FOnLine,FMpa,FBatt,FUpdateDate,TempTime,Repeat,
isnull((select top 1 1 from Alarm_Timely _c where _c.BaseID=a.id and FMarkerType=7 and FStatus=1),0) as FIsAlarm
from BASE_YALI a,DATA_YALI_MAIN b where a.id=b.baseid ";
sql = sql + strWhere;
if (String.IsNullOrEmpty(sortField) == false)
{
if (sortOrder != "desc")
{
sortOrder = "asc";
}
sql += " order by " + sortField + " " + sortOrder;
}
DataTable dt = DBUtil.SelectDataTablePager(sql, index, size);
int count = Convert.ToInt32(DBUtil.SelectDataTable("select count(*) as countS from BASE_YALI a,DATA_YALI_MAIN b where a.id=b.baseid ").Rows[0][0].ToString());
Hashtable result = new Hashtable();
result["data"] = dt;
result["total"] = count;
return(result);
}
/// <summary>
/// 月用水量
/// </summary>
/// <param name="date">日期（用于提取年月）</param>
/// <param name="pumpid">泵房id</param>
/// <returns></returns>
public static Hashtable SearchWatPumpMonthData(string date, int pumpid)
{
Hashtable has = new Hashtable();
String sql = "select * from dbo.fun_watpumpmonthdata('" + date + "'," + pumpid + ") order by id ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
/// <summary>
/// 年用水量
/// </summary>
/// <param name="num">今年or去年（0或1）</param>
/// <param name="pumpid">泵房id</param>
/// <returns></returns>
public static Hashtable SearchWatPumpYearData(int num, int pumpid)
{
Hashtable has = new Hashtable();
String sql = "select * from dbo.fun_watpumpyeardata(" + num + "," + pumpid + ") ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
/// <summary>
/// 月成本
/// </summary>
/// <param name="lev"></param>
/// <returns></returns>
public static Hashtable GetMonthCostDataBT(int lev)
{
Hashtable has = new Hashtable();
String sql = @"select id,FMonth,FFlow*FPrice as FFlowPrice from T_FlowLeakCost where FLev=" + lev + " and FYear=year(GETDATE()) order by id ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
public static Hashtable SearchCurveData(string pumpId)
{
Hashtable has = new Hashtable();
String sql = "select *,CONVERT(varchar(10),TM,120) as T_Data,CONVERT(varchar(10),TM,108) as T_Time from T_Data2016 where datediff(day,AddTime,getdate())=0 and FPumpID='" + pumpId + "' order by T_Time";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
public static DataTable SearchOverlay(string strWhere)
{
String sql = @"select a.FName,b.* from Map_Area a,Map_Area_Overlay b where a.ID=b.FMapAreaID ";
sql = sql + strWhere + " order by a.FCreateDate";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static Hashtable SearchWatLeakRecMarchData_day3(int lev)
{
Hashtable has = new Hashtable();
String sql = "select * from dbo.fun_watleakrecmarchdata_day3(" + lev + ") ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
public static DataTable Search(string strWhere)
{
String sql = @"select a.id as baseId,FCustomerID,FDTUCode,FName,FSchemeID,b.*
from BASE_TIAOFENG a,DATA_TIAOFENG_MAIN b where a.id=b.BASEID";
sql = sql + strWhere;
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static Hashtable GetMonthCostData(int lev)
{
Hashtable has = new Hashtable();
String sql = @"select a.id, CONVERT(varchar(10), right(a.FYear,2))+'-'+CONVERT(varchar(10),a.FMonth) as yearMonth,a.FFlow*FPrice as FFlowPrice, a.FLeakage*FPrice as FLeakagePrice,a.FTotalLeakage*FPrice as FTotalLeakPrice from(
select top 12 * from T_FlowLeakCost where FLev=" + lev + " order by id desc) a order by id ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
public static DataTable Search(string strWhere)
{
String sql = @"select a.ID as MarkerID,FMapTempID,FName,FCreateDate as MarkerCreateDate,b.ID as MarkerProID
,FMarkerID,FAliasName,FType,FLineID,FParentID,FMarker,FMID
from Map_Marker a,Map_MarkerProperty b where a.ID=b.FMarkerID and a.FIsDelete=0 ";
sql = sql + strWhere + " order by a.FCreateDate";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static DataTable Search(string strWhere)
{
String sql = @"select a.id as TempID,FMapTempName,FCreateDate as TempCreateDate,b.ID as TempProID
,FMapTempID,FAliasName,FMinZoom,FMaxZoom,FCenter,FZoom,FFeatures,FMapType,FStyle
from Map_Template a,Map_TempProperty b where a.id=b.FMapTempID";
sql = sql + strWhere + " order by a.FCreateDate";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static DataTable Search(string strWhere)
{
String sql = @"select a.ID as AreaID,FMapTempID,FName,FCreateDate as AreaCreateDate,b.id as AreaProID,FMapAreaID,FAliasName,FAreaType,FArea
,FStrokeColor,FStrokeOpacity,FStrokeWeight,FStrokeStyle,FAreaColor,FAreaOpacity
from Map_Area a,Map_AreaProperty b where a.ID=b.FMapAreaID";
sql = sql + strWhere + " order by a.FCreateDate";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static DataTable Search(string strWhere)
{
String sql = @"select a.ID as LineID,FMapTempID,FName,FCreateDate as LineCreateDate,b.id as LineProID,FMapLineID,FAliasName,FLineType,FLine
,FPipeSize,FPipeMaterials,FPavDate,FDepth,FLength,FThickness,FStrokeColor
,FStrokeOpacity,FStrokeWeight,FStrokeStyle,FStrokeParentID
from Map_Line a,Map_LineProperty b where a.ID=b.FMapLineID";
sql = sql + strWhere + " order by a.FCreateDate";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}
public static Hashtable SearchHistogramData(string pumpId, string time)
{
Hashtable has = new Hashtable();
String sql = @"select CONVERT(varchar(10),isnull(TM,getdate()),120) as T_Data,T_dayHour as h_TM,(max(isnull(CONVERT(int,P01),0))-min(isnull(CONVERT(int,P01),0))) as data from T_DayHours a
left join T_Data2016 on a.T_dayHour=datepart(hour,TM) and left(CONVERT(varchar(100),AddTime,120),10)='" + time + "' and FPumpID='" + pumpId + "'"
+ @" where T_Lev=1
group by CONVERT(varchar(10),isnull(TM,getdate()),120),T_dayHour
order by h_TM";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
public static DataTable SearchInsertAlarm(string strWhere, string BaseID)
{
string where = strWhere;
string sql = @"insert into Alarm_Timely select id as ParamID,BaseID='" + BaseID + "',FMarkerType,FKey,FMsg,FMsg as FSetMsg,FLev,FStatus=0,FIsPhone=0,FAlarmTime=null "
+ @" from Alarm_Param where 1=1 " + strWhere;
if (!where.Equals(""))
{
sql = sql + where;
}
DataTable dt = DBUtil.SelectDataTable(sql);
return(dt);
}
/// <summary>
/// 季度成本
/// </summary>
/// <param name="lev"></param>
/// <returns></returns>
public static Hashtable GetQuarterCostDataBT(int lev)
{
Hashtable has = new Hashtable();
String sql = @"select '一季度' as 'quarterS', isnull(sum(FFlow*FPrice),0) as FFlowPrice from T_FlowLeakCost where FLev=" + lev + " and FYear=year(GETDATE()) and DATENAME(quarter,FMonth)=1"
+ @"union all
select '二季度' as 'quarterS', isnull(sum(FFlow*FPrice),0) as FFlowPrice from T_FlowLeakCost where FLev=" + lev + " and FYear=year(GETDATE()) and DATENAME(quarter,FMonth)=2"
+ @"union all
select '三季度' as 'quarterS', isnull(sum(FFlow*FPrice),0) as FFlowPrice from T_FlowLeakCost where FLev=" + lev + " and FYear=year(GETDATE()) and DATENAME(quarter,FMonth)=3"
+ @"union all
select '四季度' as 'quarterS', isnull(sum(FFlow*FPrice),0) as FFlowPrice from T_FlowLeakCost where FLev=" + lev + " and FYear=year(GETDATE()) and DATENAME(quarter,FMonth)=4 ";
DataTable data = DBUtil.SelectDataTable(sql);
has["data"] = data;
return(data.Rows.Count > 0 ? has : null);
}
// static String tbname = "T_Alarm";
// public static Hashtable Search(string key, int index, int size, string sortField, string sortOrder)
// {
// string sql = @"SELECT dbo.PumpManager.FName, dbo.T_Alarm.*
// FROM dbo.T_Alarm INNER JOIN
// dbo.PumpManager ON dbo.T_Alarm.FPumpID = dbo.PumpManager.ID
// where T_Alarm.FStatus='1' " + key + " order by FCreateDate desc";
// if (String.IsNullOrEmpty(sortField) == false)
// {
// if (sortOrder != "desc") sortOrder = "asc";
// sql += " order by " + sortField + " " + sortOrder;
// }
// DataTable dt = DBUtil.SelectDataTablePager(sql, index, size);
// ArrayList data = DBUtil.DataTable2ArrayList(dt);
// int count = DBUtil.ExecuteScalar(sql);
// Hashtable result = new Hashtable();
// result["data"] = data;
// result["total"] = count;
// return result;
// }
// public static Hashtable Get(string id)
// {
// string sql = "select * from " + tbname + " where id = '" + id + "'";
// ArrayList data = DBUtil.Select(sql);
// return data.Count > 0 ? (Hashtable)data[0] : null;
// }
// public static string Insert(Hashtable has)
// {
// string id = (has["id"] == null || has["id"].ToString() == "") ? Guid.NewGuid().ToString() : has["id"].ToString();
// has["id"] = id;
// string columns = "";
// string values = "";
// foreach (DictionaryEntry de in has)
// {
// columns += "" + de.Key + ",";
// values += "@" + de.Key + ",";
// }
// string sql = string.Format("insert into " + tbname + " ( {0} ) values( {1} )", columns.Substring(0, columns.Length - 1), values.Substring(0, values.Length - 1));
// DBUtil.Execute(sql, has);
// return id;
// }
// public static void Delete(string id)
// {
// Hashtable has = new Hashtable();
// has["id"] = id;
// DBUtil.Execute("delete from " + tbname + " where id = @id", has);
// }
// public static void Update(Hashtable has)
// {
// string set = "";
// string where = "";
// foreach (DictionaryEntry de in has)
// {
// if (de.Key.ToString() != "id")
// {
// set += "" + de.Key + "= @" + de.Key + ",";
// }
// else
// {
// where += "" + de.Key + "= @" + de.Key + "";
// }
// }
// string sql = string.Format("update " + tbname + " set {0} where {1}", set.Substring(0, set.Length - 1), where);
// DBUtil.Execute(sql, has);
// }
public static DataTable SearchAlarm(string strWhere, int id)
{
String sql = @"select a.id as AlarmID,FSetMsg,a.BaseID,b.FName ,TempTime,
TimeRange=case when DATEDIFF(MINUTE, FAlarmTime,getdate())<5 then '刚刚'
when DATEDIFF(MINUTE, FAlarmTime,getdate()) between 5 and 60 then convert(varchar(20), DATEDIFF(MINUTE, FAlarmTime,getdate()))+'分钟前'
when DATEDIFF(HOUR, FAlarmTime,getdate()) between 1 and 24 then convert(varchar(20), DATEDIFF(HOUR, FAlarmTime,getdate()))+'个小时前'
when DATEDIFF(DAY, FAlarmTime,getdate()) between 1 and 30 then convert(varchar(20), DATEDIFF(DAY, FAlarmTime,getdate()))+'天前'
when DATEDIFF(MONTH, FAlarmTime,getdate()) between 1 and 12 then convert(varchar(20), DATEDIFF(MONTH, FAlarmTime,getdate()))+'个月前'
else 'N年前' end
from Alarm_Timely a,BASE_YALI b,DATA_YALI_MAIN c
where a.BaseID=b.id and b.id=c.BASEID and a.BaseID=c.BASEID and a.FMarkerType=" + id + " and FStatus=1 ";
sql = sql + strWhere + " order by FAlarmTime desc ";
DataTable data = DBUtil.SelectDataTable(sql);
return(data);
}