workstatetjfrm.pas

一个电力企业的后台管理程序

  ReplaceTime(EndTime,TimePicker_End.time);
  With DM_WorkStateTJ.Qr_WorkStateTJ do
        begin

          close; sql.Clear;
          sql.Text:='drop view GZBX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YKZX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHTS_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHZX_Num';
          try
            ExecSQL;
          except
          end;


          close; sql.Clear;
          sql.Text:='drop view Temp1';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp2';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp3';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp4';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Total';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='create view GZBX_Num as  select count(slr) GZBX_Num,Slr from ClientElecOut where  zzflsj>=:StartTime and zzflsj<=:EndTime Group By Slr';
          ParamByName('StartTime').asDateTime:=StartTime;
          ParamByName('EndTime').asDateTime:=EndTime;
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YKZX_Num as  select count(slr) YKZX_Num,Slr from YkGcZx where  SlTime>=:StartTime and SlTime<=:EndTime Group By Slr';
          ParamByName('StartTime').asDateTime:=StartTime;
          ParamByName('EndTime').asDateTime:=EndTime;
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHTS_Num as  select count(slr) YHTS_Num,Slr from ClientComp where  Slsj>=:StartTime and Slsj<=:EndTime Group By Slr';
          ParamByName('StartTime').asDateTime:=StartTime;
          ParamByName('EndTime').asDateTime:=EndTime;
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHZX_Num as  select count(slr) YHZX_Num,Slr from ClientAsk where  Slsj>=:StartTime and Slsj<=:EndTime Group By Slr';
          ParamByName('StartTime').asDateTime:=StartTime;
          ParamByName('EndTime').asDateTime:=EndTime;
          ExecSQL;


          close; sql.Clear;
          sql.Text:='create view Temp1 as  select a.WorkId,a.WorkName,b.GZBX_Num from SysLogin a Left Join GZBX_Num b on a.WorkId=b.Slr';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp2 as  select a.WorkId,a.WorkName,a.GZBX_Num,b.YKZX_Num from Temp1 a Left Join YKZX_Num b on a.WorkId=b.Slr';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp3 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,b.YHTS_Num from Temp2 a Left Join YHTS_Num b on a.WorkId=b.Slr';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp4 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,a.YHTS_Num,b.YHZX_Num from Temp3 a Left Join YHZX_Num b on a.WorkId=b.Slr';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Total as  select Sum(GZBX_Num) GZBX_Total,Sum(YKZX_Num) YKZX_Total,Sum(YHTS_Num) YHTS_Total,Sum(YHZX_Num) YHZX_Total from Temp4';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='select * from Temp4';
          Open;

        end;
end;

procedure TForm_WorkStateTJ.DoOneAgent_OnlySelectMonth;
begin
      With DM_WorkStateTJ.Qr_WorkStateTJ do
        begin

          close; sql.Clear;
          sql.Text:='drop view GZBX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YKZX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHTS_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHZX_Num';
          try
            ExecSQL;
          except
          end;


          close; sql.Clear;
          sql.Text:='drop view Temp1';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp2';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp3';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp4';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Total';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='create view GZBX_Num as  select count(slr) GZBX_Num from ClientElecOut where  year(zzflsj)='''+inttostr(CurYear)+''' and Month(zzflsj)='''+Edt_OnlyMonthSelect.Text+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YKZX_Num as  select count(slr) YKZX_Num from YkGcZx where  year(SlTime)='''+inttostr(CurYear)+''' and Month(SlTime)='''+Edt_OnlyMonthSelect.Text+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHTS_Num as  select count(slr) YHTS_Num from ClientComp where  year(Slsj)='''+inttostr(CurYear)+''' and Month(Slsj)='''+Edt_OnlyMonthSelect.Text+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHZX_Num as  select count(slr) YHZX_Num from ClientAsk where  year(Slsj)='''+inttostr(CurYear)+''' and Month(Slsj)='''+Edt_OnlyMonthSelect.Text+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;


          close; sql.Clear;
          sql.Text:='create view Temp1 as  select a.WorkId,a.WorkName,b.GZBX_Num from SysLogin a,GZBX_Num b where a.WorkId='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp2 as  select a.WorkId,a.WorkName,a.GZBX_Num,b.YKZX_Num from Temp1 a,YKZX_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp3 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,b.YHTS_Num from Temp2 a,YHTS_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp4 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,a.YHTS_Num,b.YHZX_Num from Temp3 a , YHZX_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Total as  select Sum(GZBX_Num) GZBX_Total,Sum(YKZX_Num) YKZX_Total,Sum(YHTS_Num) YHTS_Total,Sum(YHZX_Num) YHZX_Total from Temp4';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='select * from Temp4';
          Open;

        end;
end;

procedure TForm_WorkStateTJ.DoOneAgent_OnlySelectDay;
var
  Uyear,Umonth,Uday:word;
begin
    DecodeDate(DatePicker.DateTime,Uyear,Umonth,Uday);
    With DM_WorkStateTJ.Qr_WorkStateTJ do
        begin

          close; sql.Clear;
          sql.Text:='drop view GZBX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YKZX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHTS_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHZX_Num';
          try
            ExecSQL;
          except
          end;


          close; sql.Clear;
          sql.Text:='drop view Temp1';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp2';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp3';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp4';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Total';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='create view GZBX_Num as  select count(slr) GZBX_Num from ClientElecOut where  year(zzflsj)='''+inttostr(UYear)+''' and Month(zzflsj)='''+IntToStr(UMonth)+''' and Day(zzflsj)='''+IntToStr(UDay)+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YKZX_Num as  select count(slr) YKZX_Num from YkGcZx where  year(SlTime)='''+inttostr(UYear)+''' and Month(SlTime)='''+IntToStr(UMonth)+''' and Day(SlTime)='''+IntToStr(UDay)+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHTS_Num as  select count(slr) YHTS_Num from ClientComp where  year(Slsj)='''+inttostr(UYear)+''' and Month(Slsj)='''+IntToStr(UMonth)+''' and Day(Slsj)='''+IntToStr(UDay)+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YHZX_Num as  select count(slr) YHZX_Num from ClientAsk where  year(Slsj)='''+inttostr(CurYear)+''' and Month(Slsj)='''+IntToStr(UMonth)+''' and Day(Slsj)='''+IntToStr(UDay)+''' and slr='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;


          close; sql.Clear;
          sql.Text:='create view Temp1 as  select a.WorkId,a.WorkName,b.GZBX_Num from SysLogin a,GZBX_Num b where a.WorkId='''+RxDBLpCb_Agent.Value+'''';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp2 as  select a.WorkId,a.WorkName,a.GZBX_Num,b.YKZX_Num from Temp1 a,YKZX_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp3 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,b.YHTS_Num from Temp2 a,YHTS_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Temp4 as  select a.WorkId,a.WorkName,a.GZBX_Num,a.YKZX_Num,a.YHTS_Num,b.YHZX_Num from Temp3 a , YHZX_Num b ';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view Total as  select Sum(GZBX_Num) GZBX_Total,Sum(YKZX_Num) YKZX_Total,Sum(YHTS_Num) YHTS_Total,Sum(YHZX_Num) YHZX_Total from Temp4';
          ExecSQL;

          close; sql.Clear;
          sql.Text:='select * from Temp4';
          Open;

        end;
end;

procedure TForm_WorkStateTJ.DoOneAgent_BetweenTowDateTime;
var
  StartTime,EndTime:TDateTime;
begin
     StartTime:=DatePicker_Start.Date;
     EndTime:=DatePicker_End.Date;

     ReplaceTime(StartTime,TimePicker_Start.time);
     ReplaceTime(EndTime,TimePicker_End.time);

        With DM_WorkStateTJ.Qr_WorkStateTJ do
        begin

          close; sql.Clear;
          sql.Text:='drop view GZBX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YKZX_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHTS_Num';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view YHZX_Num';
          try
            ExecSQL;
          except
          end;


          close; sql.Clear;
          sql.Text:='drop view Temp1';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp2';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp3';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Temp4';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='drop view Total';
          try
            ExecSQL;
          except
          end;

          close; sql.Clear;
          sql.Text:='create view GZBX_Num as  select count(slr) GZBX_Num from ClientElecOut where  zzflsj>=:StartTime and zzflsj<=:EndTime and slr='''+RxDBLpCb_Agent.Value+'''';
          ParamByName('StartTime').asDateTime:=StartTime;
          ParamByName('EndTime').asDateTime:=EndTime;
          ExecSQL;

          close; sql.Clear;
          sql.Text:='create view YKZX_Num as  select count(slr) YKZX_Num from YkGcZx where  SlTime>=:StartTime and SlTime<=:EndTime and slr='''+RxDBLpCb_Agent.Value+'''';