pm_qry_poexecutedetail.pas

一个MRPII系统源代码版本

unit Pm_Qry_PoExecuteDetail;
Interface
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Panel, StdCtrls, ActnList, Db, AdODB, ExtCtrls, ComCtrls, ToolWin,
  Grids, DBGridEh, ExtPrintReport,Pr_PrintReportType, jpeg;
Type
  TFrm_Pm_Qry_PoExecuteDetail = Class(TFrm_Base_Panel)
    Label1: TLabel;
    TlBtn_Exit: TToolButton;
    ToolButton2: TToolButton;
    DBGridEh1: TDBGridEh;
    DataSource1: TDataSource;
    ToolButton1: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ExtPrintReport1: TExtPrintReport;
    AdoQry_Main: TAdoQuery;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
    procedure ToolButton4Click(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
    lc_VendorCode:string;
    lc_Status:integer;
    Lc_beginMonth,lc_EndMonth:string;
    procedure SetReport;
    procedure GetCondition;
    procedure GetData(Lc_VendorCode:string;Lc_Status:integer;Lc_beginMonth:string;endMonth:String);
  public
    { Public declarations }
    procedure Initform(AdoConnection:TAdoConnection);
    procedure SetPnl;

  end;
var
  Frm_Pm_Qry_PoExecuteDetail: TFrm_Pm_Qry_PoExecuteDetail;

implementation

uses Pm_Qry_PoExecuteDetail_Condition;

{$R *.DFM}

{ TFrm_Pm_Qry_PoExecuteDetail }

procedure TFrm_Pm_Qry_PoExecuteDetail.GetData(Lc_VendorCode:string;Lc_Status:integer;Lc_beginMonth:string;endMonth:String);
var
  SqlMainStr:string;
  sqlConditionStr:string;
begin
   SqlMainStr:='';
   sqlConditionStr:='';
   SqlMainStr:='  select   DISTINCT Po.VendorCode+'' ''+Vendor.VendorName as VendorCodeName,'+
                     '  Po.PoNo,'+
                     '  PoLine.PoLineNo,'+
                     '  PoLine.PoLineStatus,'+
                     '  PoLine.ItemCode+'' ''+Item.ItemName as ItemCodeName,'+
                     //'  Item.UomCode+'' ''+Uom.UomName as UomCodeName , '+
                     '  Uom.UomName as UomCodeName , '+
                     '  convert(decimal(12,3),PoLine.PoQty) as PoQty,'+
                     '  PoLine.PoLineDate,'+
                     '  InvInBill.InvBillNo,'+
                     '  OnCheckBillLine.OnCheckQty,'+
                     '  InvInBillLine.InvBillQty,'+
                     '  InvInBill.InvBillDate  '+
                     'from Po left join PoLine '+
                     '  on  Po.PoNo=PoLine.PoNo '+
                     '  left join Vendor on Po.VendorCode=Vendor.VendorCode '+
                     '  left join Item on  PoLine.ItemCode=Item.ItemCode '+
                     '  left join Uom on Item.UomCode=Uom.UomCode '+
                     '  left join InvInBill on InvInBill.PoNo=PoLine.PoNo '+
                     '  Left join InvInBillLine on InvInBill.InvBillId=InvInBillLine.InvBillId '+
                     '  left join OnCheckBillLine on OnCheckBillLine.PoNo=Po.PoNo ';
  sqlConditionStr:='  where  InvInBillLine.InvBillQty>0  and  PoLine.PoLineStatus='''+inttostr(lc_Status)+''' ';
  if Lc_VendorCode<>'' then
    sqlConditionStr:=sqlConditionStr+ ' and  Po.VendorCode='''+Lc_VendorCode+''' ' ;
  if (Lc_beginMonth<>'') and (Lc_EndMonth<>'') then
    if (Lc_beginMonth<>'.') and (Lc_EndMonth<>'.') then
       sqlConditionStr:=sqlConditionStr+'  and  convert(varchAr(7),Poline.POLineDate,102)  between  '''+Lc_beginMonth +'''  and '''+Lc_EndMonth+''' ';
  SqlMainStr:=SqlMainStr+ sqlConditionStr;
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(SqlMainStr);
    open;
    if AdoQry_Main.recordCount>0 then
      Pnl_Hint.Caption:='提示:共有:'+''+inttostr(AdoQry_Main.recordCount)+''+'条记录'
    else
      Pnl_Hint.Caption:='提示: 无符合条件的记录';
  end;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.Initform( AdoConnection: TAdoConnection);
var
  str:string;
begin
  SetDBConnect(AdoConnection);
  AdoQry_Tmp.Connection:=AdoConnection;
  AdoQry_Main.Connection:=AdoConnection;
  str:='';
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select top 1 VendorCode  from  Vendor ') ;
    open;
    str:=fieldbyname('VendorCode').asstring;
    Close
  end;
  lc_VendorCode:=str;
  lc_Status:=6;
  Lc_beginMonth:=formatdatetime ('yyyy.mm',now);
  Lc_EndMonth:=formatdatetime ('yyyy.mm',now);;
  GetData(Lc_VendorCode,Lc_Status,Lc_beginMonth,Lc_EndMonth);
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.FormActivate(Sender: TObject);
begin
  inherited;
  //getdata;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  action:=cafree;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.GetCondition;
begin
  Frm_Pm_Qry_PoExecuteDetail_Condition:=TFrm_Pm_Qry_PoExecuteDetail_Condition.Create(Application);
  with Frm_Pm_Qry_PoExecuteDetail_Condition do
  begin
    SetDBConnect(dbconnect);
    VendorCode:=Lc_VendorCode;
    status:=lc_Status;
    beginMonth:= Lc_beginMonth;
    endMonth:=lc_EndMonth;
    ShowModal;
    if ModalResult = Mrok then
    begin
      Lc_VendorCode:=VendorCode;
      Lc_Status:=status;
      Lc_beginMonth:=beginMonth;
      Lc_EndMonth:=endMonth;
      GetData(Lc_VendorCode,Lc_Status,Lc_beginMonth,endMonth);
      SetPnL;
      Release;
    end;
  end;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.Act_FilterExecute(Sender: TObject);
begin
  inherited;
  Getcondition;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.SetPnl;
begin
 if lc_Status=6 then
   label1.Caption:='筛选条件：行状态为下达';
 if lc_Status=7 then
   label1.Caption:='筛选条件：行状态为关闭';
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.ToolButton4Click(Sender: TObject);
begin
  inherited;
  Getcondition;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.Act_PreviewExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport1.preview;
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.SetReport;
var
  i,j:integer;
begin
  inherited;
  ExtPrintReport1.DataSet :=nil;
  ExtPrintReport1.Headers.clear;
  i:=0;
  with ExtPrintReport1 do
  begin
    for j:=0 to DBGridEH1.Columns.Count-1 do
      if DBGridEH1.Columns[j].Visible then
      begin
        Headers.Add;
        Headers.Items[i].Caption :=DBGridEH1.Columns[j].Title.Caption;
        Headers.Items[i].FieldName :=DBGridEH1.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH1.Columns[j].Width div (DBGridEH1.Columns[j].Font.Size-2);
        Headers.Items[i].Alignment :=DBGridEH1.Columns[j].Alignment;
        inc(i);
      end;
    DataSet:=AdoQry_Main;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select SysParamValueC '+
                         'From SysParam '+
                         'where SysParamCode=''Name0''';//Name0是使用本系统的客户的名称
    AdoQry_Tmp.Open;
    ExtPrintReport1.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
    ExtPrintReport1.Title2:=Pnl_Title.Caption;
    //ExtPrintReport1.Subtitle3:=Lbl_include1.Caption+Lbl_include.Caption;//排序字段中文名;
    ExtPrintReport1.Subtitle3:=label1.Caption//排序字段中文名;
  end;
end;
procedure TFrm_Pm_Qry_PoExecuteDetail.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport1.print(self);
end;

procedure TFrm_Pm_Qry_PoExecuteDetail.Act_ExcelExecute(Sender: TObject);
begin
  inherited;
  DBGridEhToExcel(Dbgrideh1);
end;

end.