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); }