ap_qry_noinvoiceinbinbill.pas

一个MRPII系统源代码版本

unit Ap_Qry_NoInvoiceInbInBill;
//
Interface

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

Type
  TFrm_Ap_Qry_NoInvoiceInbInBill = Class(TFrm_Base_Panel)
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    AdoQry_Main: TAdoQuery;
    DataSource: TDataSource;
    DBGridEh: TDBGridEh;
    ToolButton3: TToolButton;
    ExtPrintReport: TExtPrintReport;
    ToolButton4: TToolButton;
    Splitter1: TSplitter;
    DBGridEh1: TDBGridEh;
    AdoQuery_Detail: TAdoQuery;
    DataSource1: TDataSource;
    Panel1: TPanel;
    Label1: TLabel;
    Label3: TLabel;
    Lb_tax: TLabel;
    ToolButton5: TToolButton;
    Lb_Notax: TLabel;
    ToolButton6: TToolButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure Act_FilterExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure Act_SetColumnExecute(Sender: TObject);
  private
    { Private declarations }
    Lc_VendorCode:string;
    Lc_begindate,Lc_Enddate:string;
    procedure SetPnl;
    procedure GetCondition;
    procedure GetNoInvoiceData(VendorCode:string);
    procedure SetReport;
  protected
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;
  public
    { Public declarations }
    procedure SetDBConnect(AdOConnection: TAdOConnection);Override;    
  end;

var
  Frm_Ap_Qry_NoInvoiceInbInBill: TFrm_Ap_Qry_NoInvoiceInbInBill;

implementation

uses Ap_Qry_NoInvoiceInbInBill_Condition,Sys_Global, Sys_SetColumn;

{$R *.DFM}
var
  Temp_DBGridEh:TDBGridEh;
procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);
var
  i:integer;
begin
  NewValue:=OldValue;
  if Temp_DBGridEh<>nil then
    for i:=0 to Temp_DBGridEh.Columns.Count-1 do
      if(Temp_DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        NewValue:=Temp_DBGridEh.Columns[i].PickList.Strings[Temp_DBGridEh.Columns[i].
          KeyList.IndexOf(OldValue)];
        break;
      end;
end;


procedure TFrm_Ap_Qry_NoInvoiceInbInBill.FormDestroy(Sender: TObject);
begin
  inherited;
  if Frm_Sys_SetColumn<>nil then
    Frm_Sys_SetColumn.Release;
  Frm_Ap_Qry_NoInvoiceInbInBill:=nil;
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action := Cafree; 
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.SetDBConnect(
  AdOConnection: TAdOConnection);
var
  sSQL,sSQL1,twosum:string;
  FirstVendor:string;
begin
  Application.ProcessMessages;
  inherited;
  AdoQry_Main.Connection:=Dbconnect;
  AdoQry_Tmp.Connection:=Dbconnect;
  AdoQuery_Detail.Connection:=Dbconnect;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.clear;
  AdoQry_Tmp.SQL.Add('select top 1 VendorCode  from InvInBill');
  AdoQry_Tmp.Prepared;
  AdoQry_tmp.Open;
  FirstVendor:=  AdoQry_Tmp.fieldbyname('VendorCode').asstring;


  PriceFields:='InvBillAmountC,InvBillnotaxPriceC,InvBillnotaxAmountC,';
  sSQL:='select InvInBill.InvBillId,'+
               ' InvInBill.VendorCode,'+
               ' InvInBill.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
               ' InvInBill.WHCode+'' ''+Warehouse.WHName as WarehouseB,'+
               ' InvInBill.InvBillNo,'+
               ' InvInBill.InvBillDate,'+
               ' InvInBill.CurrencyCode+'' ''+ currency.currencyName as currencyB,'+
               ' sum(InvInBillline.InvBillnotaxAmountC) as InvBillnotaxAmountC,'+
               ' sum(InvInBillline.InvBillAmountC) as InvBillAmountC '+
        ' from InvInBill'+
        ' join Vendor on InvInBill.VendorCode=Vendor.VendorCode'+
        ' join Warehouse on InvInBill.WHCode=Warehouse.WHCode'+
        ' join currency on InvInBill.CurrencyCode=currency.currencyCode'+
        ' join InvInBillline on  InvInBill.InvBillId=InvInBillline.InvBillid'+
        ' where InvInBill.VendorCode= '+quotedstr(FirstVendor)+
        ' and  ((InvInBill.BillTypeCode =''0101'' )' +
        '   or (InvInBill.BillTypeCode=''0103'' )' +
        '   or (InvInBill.BillTypeCode=''0102'' )' +
        '   or ( (BillTypeCode=''0199'' )  and InvInBill.BillType2Code in ( select BillType2Code from BillType2 where ChangeAp=1 )))'+
        '   and InvInBill.InvBillStkChck=1 ' +
        '   and InvInBill.InvBillValuation<>1' +
        '   and InvInBill.InvBillFinChck<>1'+
        ' group by InvInBill.InvBillId,InvInBill.VendorCode,Vendor.VendorName,'+
                 ' Warehouse.WHName,currency.currencyName,'+
                 ' InvInBill.WHCode,InvInBill.InvBillNo,InvInBill.InvBillDate,InvInBill.CurrencyCode';
  sSQL1:=sSQL+' Order by InvInBill.VendorCode ';

  AdoQry_Main.Close;
  AdoQry_Main.SQL.clear;
  AdoQry_Main.SQL.Add(sSQL1);
  AdoQry_Main.Open;

  twosum:='select sum(InvBillnotaxAmountC) as sumFirst,sum(InvBillAmountC) as sumsecond '+
          'from  ('+ sSQL+')A';
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(twosum);
  AdoQry_tmp.Open;
  Lb_Notax.Caption:=AdoQry_tmp.fieldbyname('sumFirst').asstring;
  Lb_tax.Caption:=AdoQry_tmp.fieldbyname('sumsecond').asstring;

  Pnl_Hint.Caption:='提示：查找到'+inttostr(AdoQry_Main.recordCount)+'条记录';
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.FormCreate(Sender: TObject);
begin
  inherited;
  Lc_VendorCode:= '';
  Lb_Notax.Caption:='';
  Lb_tax.Caption:='';
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.SetPnl;
begin
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.GetCondition;
begin
  Frm_Ap_Qry_NoInvoiceInbInBill_Condition := TFrm_Ap_Qry_NoInvoiceInbInBill_Condition.Create(Application);
  Frm_Ap_Qry_NoInvoiceInbInBill_Condition.AdoQry_Tmp.Connection:=dbconnect;
  Frm_Ap_Qry_NoInvoiceInbInBill_Condition.VendorCode:=Lc_VendorCode;
  Frm_Ap_Qry_NoInvoiceInbInBill_Condition.begindate:=Lc_begindate;
  Frm_Ap_Qry_NoInvoiceInbInBill_Condition.enddate:=Lc_Enddate;

  Frm_Ap_Qry_NoInvoiceInbInBill_Condition.ShowModal;

  if Frm_Ap_Qry_NoInvoiceInbInBill_Condition.ModalResult = MrOk then
  begin
    Lc_VendorCode:= Frm_Ap_Qry_NoInvoiceInbInBill_Condition.VendorCode;
    Lc_begindate:=Frm_Ap_Qry_NoInvoiceInbInBill_Condition.begindate;
    Lc_Enddate:=Frm_Ap_Qry_NoInvoiceInbInBill_Condition.enddate;
    GetNoInvoiceData(Lc_VendorCode);
    SetPnl;
  end;
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.GetNoInvoiceData(VendorCode:string);
var
  sSQL,sSQL1:string;
  twosum:string;
begin
  sSQL:='select InvInBill.InvBillId,'+
             ' InvInBill.VendorCode,'+
             ' InvInBill.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
             ' InvInBill.WHCode+'' ''+Warehouse.WHName as WarehouseB,'+
             ' InvInBill.InvBillNo,'+
             ' InvInBill.InvBillDate,'+             
             ' InvInBill.CurrencyCode+'' ''+ currency.currencyName as currencyB,'+
             ' sum(InvInBillline.InvBillnotaxAmountC) as InvBillnotaxAmountC,'+
             ' sum(InvInBillline.InvBillAmountC) as InvBillAmountC '+
      ' from InvInBill'+
      ' join Vendor on InvInBill.VendorCode=Vendor.VendorCode'+
      ' join Warehouse on InvInBill.WHCode=Warehouse.WHCode'+
      ' join currency on InvInBill.CurrencyCode=currency.currencyCode'+
      ' join InvInBillline on  InvInBill.InvBillId=InvInBillline.InvBillid'+
      ' where InvInBill.InvBillFinChck<>1 '+
      ' and ((InvInBill.BillTypeCode =''0101'' )' +
      ' or (InvInBill.BillTypeCode=''0103'' )' +
      ' or (InvInBill.BillTypeCode=''0102'' )' +
      ' or ( (BillTypeCode=''0199'' )  and InvInBill.BillType2Code in ( select BillType2Code from BillType2 where ChangeAp=1 )))'+
      ' and InvInBill.InvBillStkChck=1 ' +
      ' and InvInBill.InvBillValuation<>1' ;
  if Lc_VendorCode<>'' then
    sSQL:=sSQL+ ' and InvInBill.VendorCode =  ' +Quotedstr(Lc_VendorCode) ;
  if Lc_begindate<>'    .  .  ' then
    sSQL:=sSQL+ ' and InvInBill.InvBillDate >= ' +Quotedstr(Lc_begindate) ;
  if Lc_Enddate<>'    .  .  ' then
    sSQL:=sSQL+ ' and InvInBill.InvBillDate <= ' +Quotedstr(Lc_Enddate) ;

  sSQL:=sSQL+ ' group by InvInBill.InvBillId,InvInBill.VendorCode,Vendor.VendorName,'+
         ' Warehouse.WHName,currency.currencyName,'+
         ' InvInBill.WHCode,InvInBill.InvBillNo,InvInBill.InvBillDate,InvInBill.CurrencyCode';
  sSQL1:=sSQL+' Order by InvInBill.VendorCode';

  AdoQry_Main.Close;
  AdoQry_Main.SQL.clear;
  AdoQry_Main.SQL.Add(sSQL1);
  AdoQry_Main.Open;

  twosum:='select sum(InvBillnotaxAmountC) as sumFirst,sum(InvBillAmountC) as sumsecond '+
          'from  ('+ sSQL+')A';
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(twosum);
  AdoQry_tmp.Open;
  Lb_Notax.Caption:=AdoQry_tmp.fieldbyname('sumFirst').asstring;
  Lb_tax.Caption:=AdoQry_tmp.fieldbyname('sumsecond').asstring;
  Pnl_Hint.Caption:='提示：查找到'+inttostr(AdoQry_Main.recordCount)+'条记录';
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.Act_FilterExecute(
  Sender: TObject);
begin
  inherited;
  if AdoQry_Main.Active then
//    AdoQry_Main.Close; 
  Getcondition;
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.SetColumnsStyle(
  ItemIndex: Integer; FieldName: String);
begin

end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.SetReport;
var
  i,j:integer;
begin
  ExtPrintReport.DataSet :=nil;
  ExtPrintReport.Headers.clear;
  i:=0;
  with ExtPrintReport do
  begin
    for j:=0 to DBGridEH.Columns.Count-1 do
      if DBGridEH.Columns[j].Visible then
      begin
        Headers.Add;
        Headers.Items[i].Caption :=DBGridEH.Columns[j].Title.Caption;
        Headers.Items[i].FieldName :=DBGridEH.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH.Columns[j].Width div (DBGridEH.Columns[j].Font.Size-2);
        Headers.Items[i].Alignment :=DBGridEH.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;
    ExtPrintReport.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
    ExtPrintReport.Title2:=Pnl_Title.Caption; 
  end;

end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.print(self);
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.Act_PreviewExecute(
  Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.preview;
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.DataSourceDataChange(
  Sender: TObject; Field: TField);
var
  S:string;
begin
  inherited;
  if AdoQry_Main.RecordCount=0 then
  begin
    Lb_Notax.Caption:='';
    Lb_tax.Caption:='';
    if AdoQuery_Detail.Active then AdoQuery_Detail.Close;
    Pnl_Hint.Caption:='提示：查找到'+inttostr(AdoQry_Main.recordCount)+'条记录';
    Application.MessageBox('没有符合条件的记录','提示',mb_ok+MB_IconWArNING);
    abort;
  end;

  S:='select InvInBillLine.InvBilllineno,'+
             ' InvInBillLine.ItemCode+'' ''+Item.ItemName as ItemB,'+
             ' InvInBillLine.InvBillQTY,'+
             ' InvInBillLine.InvBillnotaxPriceC,'+
             ' InvInBillLine.InvBillnotaxAmountC,'+
             ' InvInBillLine.InvBillAmountC'+
      ' from InvInBillline'+
      ' left join Item on InvInBillLine.ItemCode=Item.ItemCode '+
      ' where InvInBillLine.InvBillid='+ AdoQry_Main.fieldbyname('InvBillId').asstring;
  AdoQuery_Detail.Close;
  AdoQuery_Detail.SQL.clear;
  AdoQuery_Detail.SQL.Add(s);
  AdoQuery_Detail.Open;
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.Act_ExcelExecute(Sender: TObject);
begin
  inherited;
  DBGridEhToExcel(DBGridEh);
end;

procedure TFrm_Ap_Qry_NoInvoiceInbInBill.Act_SetColumnExecute(
  Sender: TObject);
begin
  inherited;
  Frm_Sys_SetColumn:=TFrm_Sys_SetColumn.Create(Application);
  Frm_Sys_SetColumn.SetSrcGrid(DBGridEH,'');
  Frm_Sys_SetColumn.ShowModal;

end;

end.