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 Hashtable SearchTopLableDate(string strWhere)
{
//String sql = "select sum(convert(float,case when FLev=1 then P01 else 0 end)) Lev1_P01 ,sum(convert(float,case when FLev=2 then P01 else 0 end)) Lev2_P01 ,sum(convert(float,case when FLev=3 then P01 else 0 end)) Lev3_P01 ,sum(convert(float,case when FLev=1 then P01 else 0 end))-sum(convert(float,case when FLev=2 then P01 else 0 end)) Lev1_Miss,sum(convert(float,case when FLev=2 then P01 else 0 end))-sum(convert(float,case when FLev=3 then P01 else 0 end)) Lev2_Miss,sum(convert(float,case when FLev=1 then P01 else 0 end))-sum(convert(float,case when FLev=3 then P01 else 0 end)) LevZ_Miss ,(sum(convert(float,case when FLev=1 then P01 else 0 end))-sum(convert(float,case when FLev=2 then P01 else 0 end)))/sum(convert(float,case when FLev=1 then P01 else 0 end)) *100.00 Lev1_Miss_P,(sum(convert(float,case when FLev=2 then P01 else 0 end))-sum(convert(float,case when FLev=3 then P01 else 0 end)))/sum(convert(float,case when FLev=2 then P01 else 0 end)) *100.00 Lev2_Miss_P,(sum(convert(float,case when FLev=1 then P01 else 0 end))-sum(convert(float,case when FLev=3 then P01 else 0 end)))/sum(convert(float,case when FLev=1 then P01 else 0 end)) *100.00 LevZ_Miss_P,sum(convert(float,case when FLev=1 then P01 else 0 end))/1000*1.2 Mount,(sum(convert(float,case when FLev=1 then P01 else 0 end))-sum(convert(float,case when FLev=3 then P01 else 0 end)))/1000*1.2 Mount1 from PumpManager a,T_Data2016 b where a.ID=b.FPumpID ";
//sql = sql + strWhere;
String sql = @"with flow1 as(
select 1 id, convert(float,(sum(CONVERT(int,Max_P01))-sum(CONVERT(int,Min_P01)))) as Lev1_P01
from (select b.ID as PumpID,max(CONVERT(int,isnull(P01,0))) as Max_P01,min(CONVERT(int,isnull(P01,0))) as Min_P01
from PumpManager b
left join T_Data2016 c on c.FPumpID=b.ID " + strWhere
+ @" where b.FDeleted=0 and FLev=1 group by b.ID) a)
,flow2 as(
select 1 id, convert(float,(sum(CONVERT(int,Max_P01))-sum(CONVERT(int,Min_P01)))) as Lev2_P01
from (select b.ID as PumpID,max(CONVERT(int,isnull(P01,0))) as Max_P01,min(CONVERT(int,isnull(P01,0))) as Min_P01
from PumpManager b
left join T_Data2016 c on c.FPumpID=b.ID " + strWhere
+ @" where b.FDeleted=0 and FLev=2 group by b.ID) a)
,flow3 as (
select 1 id, convert(float,(sum(CONVERT(int,Max_P01))-sum(CONVERT(int,Min_P01)))) as Lev3_P01
from (select b.ID as PumpID,max(CONVERT(int,isnull(P01,0))) as Max_P01,min(CONVERT(int,isnull(P01,0))) as Min_P01
from PumpManager b
left join T_Data2016 c on c.FPumpID=b.ID " + strWhere
+ @" where b.FDeleted=0 and FLev=3 group by b.ID) a)
select Lev1_P01,Lev2_P01,Lev3_P01,(Lev1_P01-Lev2_P01) as Lev1_Miss,(Lev2_P01-Lev3_P01) as Lev2_Miss
,(Lev1_P01-Lev3_P01) as LevZ_Miss,((Lev1_P01-Lev2_P01)/Lev1_P01)*100.00 as Lev1_Miss_P
,((Lev2_P01-Lev3_P01)/Lev2_P01)*100.00 as Lev2_Miss_P,((Lev1_P01-Lev3_P01)/Lev1_P01)*100.00 as LevZ_Miss_P
,(Lev1_P01/1000)*1.2 as Mount,((Lev1_P01-Lev3_P01)/1000)*1.2 as Mount1
from flow1,flow2,flow3 where flow1.id= flow2.id and flow1.id =flow3.id and flow2.id =flow3.id " ;
ArrayList data = DBUtil.Select(sql);
return(data.Count > 0 ? (Hashtable)data[0] : null);
}
