mrp_qry_graphanalyzercapacity.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 266 行

unit Mrp_Qry_GraphAnalyzerCapacity;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Entry_Head, Menus, Db, ActnList, AdODB, Grids, DBGridEh, StdCtrls,
  ExtCtrls, ComCtrls, ToolWin, TeEngine, Series, TeeProcs, ChArt, DBChArt,
  QrTee, QuickRpt, OleCtrls, chArtfx3, DBCtrls;

Type
  TFrm_Mrp_Qry_GraphAnalyzerCapacity = Class(TFrm_Base_Entry_Head)
    Panel1: TPanel;
    DBChArt1:TDBChArt;
    Series1: TBarSeries;
    Series2: TBarSeries;
    Series3: TBarSeries;
    Label1: TLabel;
    Label2: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    AdoQry_HeadFinishMonth: TStringField;
    AdoQry_HeadCountmohours: TFloatField;
    AdoQry_HeadCountwcCapacity: TFloatField;
    AdoQry_HeadOverCapacity: TFloatField;
    AdoQry_HeadOverCapacityrate: TStringField;
    AdoQry_HeadOverCapacity1: TFloatField;
    AdoQry_HeadCountwcCapacity1: TFloatField;
    Series4: TLineSeries;
    Label3: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label11: TLabel;
    Label10: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
       procedure setconditionHint(lbl:Tlabel;cAp:string);
       procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
   { Public declarations }
  end;

var
  Frm_Mrp_Qry_GraphAnalyzerCapacity: TFrm_Mrp_Qry_GraphAnalyzerCapacity;
  wcCodecondition,MpsTypeCondition,DateTypeCondition,stArtdate,timesection:string;

implementation

uses Sys_Global,Mrp_Qry_GraphAnalyzerCapacity_H,Mrp_Qry_GraphAnalyzerCapacity_C, Mrp_Qry_GraphAnalyzerCapacity_D;

{$R *.DFM}

{ TFrm_Mrp_Qry_GraphAnalyzerCapacity }

procedure TFrm_Mrp_Qry_GraphAnalyzerCapacity.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var ModateCondition1,ModateCondition2,sqltext1,sqltext2,sqltext3,convertType:string;
begin
  inherited;
  wcCodecondition:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('ItemCode').asstring;
  MpsTypecondition:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('MpsType').asstring;
  dateTypecondition:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('dateType').asstring;
  stArtdate:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('modate').asstring;
     if dateTypecondition='日' then
           begin
              modatecondition1:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('modate').asstring;
              modatecondition2:='2999.12.31';
              convertType:='convert(varchAr,datepArt(yy,Capacity.modate))+''.''+convert(varchAr,datepArt(mm,Capacity.modate))+''.''+convert(varchAr,datepArt(dd,Capacity.modate)),';
           end;
     if dateTypecondition ='周'  then
           begin
              modatecondition1:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('modate').asstring;
              modatecondition2:='2999.12.31';
          //    convertType:='convert(varchAr,datepArt(wk,Capacity.modate)),';
              convertType:='convert(varchAr,datepArt(yy,Capacity.modate))+''年''+convert(varchAr,datepArt(mm,Capacity.modate))+''月第''+convert(varchAr,datepArt(wk,Capacity.modate)-datepArt(wk,convert(varchAr,datepArt(yy,Capacity.modate))+''.''';
              convertType:=convertType+'+convert(varchAr,datepArt(mm,Capacity.modate))+''.01'')+1)+''周'',';
           end;
     if dateTypecondition='月' then
           begin
              modatecondition1:=Frm_Mrp_Qry_GraphAnalyzerCapacity_H.AdoQry_Head.fieldbyname('modate').asstring;
              modatecondition2:='2999.12.31';
              convertType:='convert(varchAr,datepArt(yy,Capacity.modate))+''.''+convert(varchAr,datepArt(mm,Capacity.modate)),';
           end;

  sqltext1:='select  FinishMonth='+convertType+
                  '  modate,'+
                  ' cmohours=sum(isnull(mohours,0)),'+
                  ' cwcCapacity=sum(isnull(Item.Capacityhours,0)),'+
                  ' OvercAp=sum(isnull(mohours,0))-sum(isnull(Item.Capacityhours,0)),'+
                  ' OvercAprate=convert(varchAr,case sum(isnull(Item.Capacityhours,0))'+
                         ' when 0 then 100'+
                         ' else convert(decimal(14,2),(sum(isnull(mohours,0))-sum(isnull(Item.Capacityhours,0)))*100/sum(isnull(Item.Capacityhours,0)))'+
                       ' end)+''%'''+
                  ' into #tmptab '+
      ' from Capacity(nolock) '+
      '      left outer join Item on Capacity.wcCode=Item.ItemCode '+
      '    where Capacity.wcCode='''+wcCodecondition+''''+
      '      and ((Capacity.MpsType='''+MpsTypecondition+''''+')'+' or ('''+MpsTypecondition+''''+'=''全部''))'+
      '      and modate   between '''+modatecondition1+''''+' and '''+modatecondition2+''''+
      '  group by Capacity.modate';
  with AdoQry_Head do
     begin
       Close;
       sql.clear;
       sql.Add(sqltext1);
       try
       Prepared;
       execsql;
       except
       end;
     end;
     with AdoQry_tmp do
      begin
       Close;
       sql.clear;
       sql.Add('select distinct FinishMonth,modate,cwcCapacity into #tmpQry from #tmptab');
       try
       Prepared;
       execsql;
       except
       end;
     end;

 sqltext2:='select  FinishMonth,'+
                  ' Countmohours=sum(isnull(cmohours,0)),'+
                  ' CountwcCapacity=sum(isnull(cwcCapacity,0)),'+
                  ' CountwcCapacity1=sum(isnull(cwcCapacity,0)),'+
                  ' OverCapacity=abs(sum(isnull(cmohours,0))-sum(isnull(cwcCapacity,0))),'+
                  ' OverCapacity1=sum(isnull(cmohours,0))-sum(isnull(cwcCapacity,0)),'+
                  ' OverCapacityrate=convert(varchAr,case sum(isnull(cwcCapacity,0))'+
                         ' when 0 then 100'+
                         ' else convert(decimal(14,2),(sum(isnull(cmohours,0))-sum(isnull(cwcCapacity,0)))*100/sum(isnull(cwcCapacity,0)))'+
                       ' end)+''%'''+
             ' into #tmpres '+
            ' from   #tmptab'+
             ' group by FinishMonth ';
 sqltext3:=' update #tmpres set CountwcCapacity=(select sum(cwcCapacity) from #tmpQry where FinishMonth=#tmpres.FinishMonth) ';

  selectfromsql:='select * from #tmpres';
  with AdoQry_Head do
     begin
       Close;
       sql.clear;
       sql.Add(sqltext2);
       try
       Prepared;
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext3);
       try
       Prepared;
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add('select * from #tmpres');
       try
       open;
       except
       end;
          if recordCount=0 then
       begin
        DispInfo('无当前工作中心能力需求相关数据！',3);
        self.Close;
       end;
       label12.Caption:=floattostr(fieldbyname('CountwcCapacity').asfloat);
     First;
     while not eof do
       begin
         if  fieldbyname('OverCapacity1').asfloat<0  then
           begin
            AdoQry_Head.Edit;
            fieldbyname('CountwcCapacity').asfloat:=fieldbyname('Countmohours').asfloat;
            AdoQry_Head.Post;
            dbchArt1.Series[0].YValues.ValueSource:='CountwcCapacity';
          dbchArt1.Series[1].ValueColor[recno-1]:=clgreen;
           end
         else
          dbchArt1.Series[0].YValues.ValueSource:='CountwcCapacity';
        next;
       end;

     end;
end;


procedure TFrm_Mrp_Qry_GraphAnalyzerCapacity.setconditionHint(lbl: Tlabel;
  cAp: string);
begin
  lbl.Caption:=cAp;

end;

procedure TFrm_Mrp_Qry_GraphAnalyzerCapacity.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  with  AdoQry_tmp do
     begin
       Close;
       sql.clear;
       sql.Add('drop table #tmptab,#tmpres,#tmpQry');
       try
       execsql;
       except
       end;
     end;

  inherited;

end;

procedure TFrm_Mrp_Qry_GraphAnalyzerCapacity.Act_LookExecute(
  Sender: TObject);
begin
  inherited;
  Application.CreateForm(TFrm_Mrp_Qry_GraphAnalyzerCapacity_C,Frm_Mrp_Qry_GraphAnalyzerCapacity_C);
 try
    with Frm_Mrp_Qry_GraphAnalyzerCapacity_C  do
      begin
        with AdoQry_Head do
          begin
            First;
            while not eof do
               begin
                 cmb.Items.Add(fieldbyname('FinishMonth').asstring);
                 next;
               end;
          end;
        showmodal;
        if modalResult<>mrok then exit;
        if cmb.text<>'' then
          timesection:=cmb.text
        else timesection:='9999';
      end;
 finally
   Frm_Mrp_Qry_GraphAnalyzerCapacity_C.Free;
 end;
  if Frm_Mrp_Qry_GraphAnalyzerCapacity_D=nil then
   begin
     Frm_Mrp_Qry_GraphAnalyzerCapacity_D:=TFrm_Mrp_Qry_GraphAnalyzerCapacity_D.Create(Self);
     Frm_Mrp_Qry_GraphAnalyzerCapacity_D.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
     Frm_Mrp_Qry_GraphAnalyzerCapacity_D.InitForm(AdoQry_Head.Connection,True);
   end
  else Frm_Mrp_Qry_GraphAnalyzerCapacity_D.Show;
end;

procedure TFrm_Mrp_Qry_GraphAnalyzerCapacity.FormDestroy(Sender: TObject);
begin
  inherited;
Frm_Mrp_Qry_GraphAnalyzerCapacity:=nil;
end;

end.