sfc_monthsumqrybcppj_d.pas

一个MRPII系统源代码版本

unit Sfc_MonthSumQrybcppj_D;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin;

Type
  TFrm_Sfc_MonthSumQrybcppj_D = Class(TFrm_Base_Qry)
    DBGridEh1: TDBGridEh;
    AdoQry_Main2: TAdoQuery;
    DataSource1: TDataSource;
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    Edit2: TEdit;
    CheckBox1: TCheckBox;
    procedure TlBtn_LookClick(Sender: TObject);
    procedure TlBtn_SumClick(Sender: TObject);
    procedure DBGridEh1DblClick(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure AdoQry_Main2AfterOpen(DataSet: TDataSet);
    procedure CheckBox1Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    { Private declarations }
    DeptCode,theMonth:string;
  public
    { Public declarations }
    procedure showfilter(lb,Dept,Month:string);
  end;

var
  Frm_Sfc_MonthSumQrybcppj_D: TFrm_Sfc_MonthSumQrybcppj_D;

implementation

uses Sfc_MonthSumQrybcppj_e;

{$R *.DFM}
procedure TFrm_Sfc_MonthSumQrybcppj_D.Showfilter(lb,Dept,Month:string);
begin
  AmountFields:='InvoutAmount,wipAmount,';
  Lbl_Condition.Caption:=lb;
  DeptCode:=Dept;
  theMonth:=Month;
  AdoQry_Main2.Connection:=AdoQry_Main.Connection;
  TlBtn_Look.Visible:=True;
  TlBtn_Look.ImageIndex:=4;
  TlBtn_Look.Enabled:=True;

  TlBtn_Sum.Visible:=True;
  tlbtn_Sum.ImageIndex:=5;
  tlbtn_Sum.Enabled:=True;
  //  tlbtn_Sum.Action:=Act_DeleteLine;
  with AdoQry_Main do
  begin
    Close;
    {
    sql.text:='select #MonthSumQry2.ItemCode+'' ''+Item.ItemName As ItemCodeName,'+
              '#MonthSumQry2.DeptCode+'' ''+Dept.DeptName As DeptCodeName,'+
              '#MonthSumQry2.InvInqty, '+
              'round(#MonthSumQry2.InvInAmount,2) as InvInAmount from #MonthSumQry2 '+
              'left join Item on Item.ItemCode=#MonthSumQry2.ItemCode '+
              'left join Dept on Dept.DeptCode=#MonthSumQry2.DeptCode '+
              'where #MonthSumQry2.InvInAmount=0 ';
    sql.text:='select #MonthSumQry1.ItemCode,#MonthSumQry1.ItemCode+'' ''+Item.ItemName As ItemCodeName,'+
              '#MonthSumQry1.Invoutqty,Uom.UomName,#MonthSumQry1.InvoutAmount from #MonthSumQry1 '+
              ' left join Item on Item.ItemCode=#MonthSumQry1.ItemCode '+
              ' left join Uom on Uom.UomCode=Item.UomCode '+
              ' where #MonthSumQry1.ItemCode not in (select #MonthSumQry1.ItemCode from #MonthSumQry1 '+
              ' join Bom on Bom.ItemCode=#MonthSumQry1.ItemCode '+
              ' and Bom.ite_ItemCode in (select ItemCode from #MonthSumQry2))';
    }
    sql.text:='select #MonthSumQry1.ItemCode,#MonthSumQry1.ItemCode+'' ''+Item.ItemName As ItemCodeName,'+
              '#MonthSumQry1.Invoutqty,Uom.UomName,#MonthSumQry1.InvoutAmount from #MonthSumQry1 '+
              ' left join Item on Item.ItemCode=#MonthSumQry1.ItemCode '+
              ' left join Uom on Uom.UomCode=Item.UomCode '+
              ' where #MonthSumQry1.ItemCode not in (select #MonthSumQry1.ItemCode from #MonthSumQry1, '+
              ' (select ItemCode,sum(wipAmount) as Amount from wipbcpAmount where wiPMonth='+
              QuotedStr(theMonth)+' and DeptCode='+QuotedStr(DeptCode)+' group by ItemCode) a '+
              ' where #MonthSumQry1.ItemCode=a.ItemCode and #MonthSumQry1.InvoutAmount=a.Amount)';
    open;
    First;
  end;
  with AdoQry_Main2 do
  begin
    Close;
    sql.text:='select wiPMonth,wipbcpAmount.DeptCode,wipbcpAmount.ItemCode,ite_ItemCode,Item.ItemName,wipbcpAmount.wipqty,wipAmount from wipbcpAmount '+
              'left join Item on Item.ItemCode=wipbcpAmount.ite_ItemCode '+
              'where wiPMonth='+QuotedStr(theMonth)+
              ' and wipbcpAmount.DeptCode='+QuotedStr(DeptCode)+
              ' and wipbcpAmount.ItemCode='+QuotedStr(AdoQry_Main.fieldbyname('ItemCode').asstring);
    open;
  end;

end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.TlBtn_LookClick(Sender: TObject);
begin
  Frm_Sfc_MonthSumQrybcppj_e:=TFrm_Sfc_MonthSumQrybcppj_e.Create(self);
  Frm_Sfc_MonthSumQrybcppj_e.Showstatus('Add',theMonth,DeptCode,AdoQry_Main.fieldbyname('ItemCode').asstring,AdoQry_Main2);
  Frm_Sfc_MonthSumQrybcppj_e.Showmodal;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.TlBtn_SumClick(Sender: TObject);
begin
  with AdoQry_tmp do
  begin
    Close;
    sql.text:='delete from wipbcpAmount where wiPMonth='+QuotedStr(AdoQry_Main2.fieldbyname('wiPMonth').AsString)+
              ' and DeptCode='+QuotedStr(AdoQry_Main2.fieldbyname('DeptCode').AsString)+
              ' and ItemCode='+QuotedStr(AdoQry_Main2.fieldbyname('ItemCode').AsString)+
              ' and ite_ItemCode='+QuotedStr(AdoQry_Main2.fieldbyname('ite_ItemCode').AsString);
    ExecSQL;
  end;
  AdoQry_Main2.delete;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.DBGridEh1DblClick(Sender: TObject);
begin
  Frm_Sfc_MonthSumQrybcppj_e:=TFrm_Sfc_MonthSumQrybcppj_e.Create(self);
  Frm_Sfc_MonthSumQrybcppj_e.Showstatus('edit',theMonth,DeptCode,AdoQry_Main.fieldbyname('ItemCode').asstring,AdoQry_Main2);
  Frm_Sfc_MonthSumQrybcppj_e.Showmodal;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.DataSourceDataChange(Sender: TObject;
  Field: TField);
var
  Dept,Month:string;
begin
//  inherited;
  with AdoQry_Main2 do
  begin
    Close;
    sql.text:='select wiPMonth,wipbcpAmount.DeptCode,wipbcpAmount.ItemCode,ite_ItemCode,Item.ItemName,wipqty,wipAmount from wipbcpAmount '+
              'left join Item on Item.ItemCode=wipbcpAmount.ite_ItemCode '+
              'where wiPMonth='+QuotedStr(theMonth)+
              ' and wipbcpAmount.DeptCode='+QuotedStr(DeptCode)+
              ' and wipbcpAmount.ItemCode='+QuotedStr(AdoQry_Main.fieldbyname('ItemCode').asstring);
    open;
  end;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.AdoQry_Main2AfterOpen(
  DataSet: TDataSet);
var
  Amount:real;
begin
  inherited;
  Amount:=0;
  with AdoQry_Main2 do
  begin
    DisableControls;
    First;
    while not eof do
    begin
      Amount:=Amount+fieldbyname('wipAmount').AsFloat;
      next;
    end;
    First;
    enableControls;
  end;
  edit1.Text:=FloatToStr(Amount);
  edit2.Text:=FormatFloat('#.##',AdoQry_Main.fieldbyname('InvoutAmount').asfloat-Amount);
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.CheckBox1Click(Sender: TObject);
begin
  inherited;
  if CheckBox1.Checked then
  begin
      with AdoQry_Main do
      begin
        Close;
        sql.text:='select #MonthSumQry1.ItemCode,#MonthSumQry1.ItemCode+'' ''+Item.ItemName As ItemCodeName,'+
                  '#MonthSumQry1.Invoutqty,Uom.UomName,#MonthSumQry1.InvoutAmount from #MonthSumQry1 '+
                  ' left join Item on Item.ItemCode=#MonthSumQry1.ItemCode '+
                  ' left join Uom on Uom.UomCode=Item.UomCode ';
        open;
        First;
      end;
      with AdoQry_Main2 do
      begin
        Close;
        sql.text:='select wiPMonth,wipbcpAmount.DeptCode,wipbcpAmount.ItemCode,ite_ItemCode,Item.ItemName,wipbcpAmount.wipqty,wipAmount from wipbcpAmount '+
                  'left join Item on Item.ItemCode=wipbcpAmount.ite_ItemCode '+
                  'where wiPMonth='+QuotedStr(theMonth)+
                  ' and wipbcpAmount.DeptCode='+QuotedStr(DeptCode)+
                  ' and wipbcpAmount.ItemCode='+QuotedStr(AdoQry_Main.fieldbyname('ItemCode').asstring);
        open;
      end;
  end
  else
  begin
      with AdoQry_Main do
      begin
        Close;
        sql.text:='select #MonthSumQry1.ItemCode,#MonthSumQry1.ItemCode+'' ''+Item.ItemName As ItemCodeName,'+
                  '#MonthSumQry1.Invoutqty,Uom.UomName,#MonthSumQry1.InvoutAmount from #MonthSumQry1 '+
                  ' left join Item on Item.ItemCode=#MonthSumQry1.ItemCode '+
                  ' left join Uom on Uom.UomCode=Item.UomCode '+
                  ' where #MonthSumQry1.ItemCode not in (select #MonthSumQry1.ItemCode from #MonthSumQry1, '+
                  ' (select ItemCode,sum(wipAmount) as Amount from wipbcpAmount where wiPMonth='+
                  QuotedStr(theMonth)+' and DeptCode='+QuotedStr(DeptCode)+' group by ItemCode) a '+
                  ' where #MonthSumQry1.ItemCode=a.ItemCode and #MonthSumQry1.InvoutAmount=a.Amount)';
        open;
        First;
      end;
      with AdoQry_Main2 do
      begin
        Close;
        sql.text:='select wiPMonth,wipbcpAmount.DeptCode,wipbcpAmount.ItemCode,ite_ItemCode,Item.ItemName,wipbcpAmount.wipqty,wipAmount from wipbcpAmount '+
                  'left join Item on Item.ItemCode=wipbcpAmount.ite_ItemCode '+
                  'where wiPMonth='+QuotedStr(theMonth)+
                  ' and wipbcpAmount.DeptCode='+QuotedStr(DeptCode)+
                  ' and wipbcpAmount.ItemCode='+QuotedStr(AdoQry_Main.fieldbyname('ItemCode').asstring);
        open;
      end;
  end;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_D.FormActivate(Sender: TObject);
var
  Amount:real;
begin
  Amount:=0;
  with AdoQry_Main2 do
  begin
    DisableControls;
    First;
    while not eof do
    begin
      Amount:=Amount+fieldbyname('wipAmount').AsFloat;
      next;
    end;
    First;
    enableControls;
  end;
  edit1.Text:=FloatToStr(Amount);
  edit2.Text:=FormatFloat('#.##',AdoQry_Main.fieldbyname('InvoutAmount').asfloat-Amount);
end;

end.