ap_qry_vendorinvoice.pas

一个MRPII系统源代码版本

  inherited;

  if AdoQry_Main.RecordCount=0 then
  begin
    Pnl_Hint.Caption:='提示：查到发票数：'+ inttostr(AdoQry_Main.RecordCount);
    if AdoQry_tmp.Active then AdoQry_tmp.Close;
    if AdoQry_Detail.Active then AdoQry_Detail.Close;
    Application.MessageBox('没有符合条件的记录！','提示',MB_OK+MB_IconInfoRMATION);
    abort;
  end;
  InvInBill:='select InvInBillline.InvBillId,InvInBill.WhCode+'' ''+Warehouse.WhName as zhaiyao,InvInBill.InvBillno,'+
                   ' sum( InvInBillLine.InvBillNotaxAmountC) as NoTaxAmount,'+
                   ' sum( InvInBillLine.InvBillAmountC) as Amount'+                   
             ' from InvInBillLine '+
             ' join InvInBill on InvInBillline.InvBillId=InvInBill.InvBillId'+
             ' join ApInvoiceline on InvInBill.InvBillid=ApInvoiceline.InvBillid'+
             ' join ApInvoice on ApInvoiceline.ApInvoiceId=ApInvoice.ApInvoiceid'+
             ' join Warehouse on InvInBill.whCode=Warehouse.whCode'+
             ' where InvInBillLine.InvBillid=InvInBill.InvBillid and'+
             ' InvInBill.InvBillid=InvInBill.InvBillid and'+
             ' InvInBill.InvBillid=ApInvoiceline.InvBillid and'+
             ' ApInvoiceline.ApInvoiceid= '+  AdoQry_Main.fieldbyname('ApInvOICEID').asstring +
             ' group by InvInBillline.InvBillId,InvInBill.InvBillno,InvInBill.WhCode,Warehouse.WhName ';

  AdoQry_Detail.Close;
  AdoQry_Detail.SQL.clear;
  AdoQry_Detail.SQL.Add(InvInBill);
  AdoQry_Detail.Open;

  InvInBillTotal:='select sum(a.NoTaxAmount) as NoTaxAmount from '+'('+ InvInBill+') a';
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(InvInBillTotal);
  AdoQry_tmp.Open;
  Label6.Caption:='未税金额合计：'+AdoQry_tmp.fieldbyname('NoTaxAmount').asstring;

 Pnl_Hint.Caption:='提示：查到发票数：'+ inttostr(AdoQry_Main.RecordCount);
end;

procedure TFrm_Ap_Qry_VendorInvoice.DBGridEh2DblClick(Sender: TObject);
var
  S:string;
begin
  inherited;
  if AdoQry_Detail.RecordCount<=0 then
  begin
    Application.MessageBox('没有符合条件的记录！','提示',MB_OK+MB_IconInfoRMATION);
    abort;
  end;

  Frm_Ap_Qry_InvInBillLine:=TFrm_Ap_Qry_InvInBillLine.Create(Application);
  Frm_Ap_Qry_InvInBillLine.InitForm(Dbconnect,True);

  PriceFields:='InvBillnotaxPriceC,InvBillnotaxAmountC,';

  Frm_Ap_Qry_InvInBillLine.lbl_Wh.Caption:=AdoQry_Detail.fieldbyname('zhaiyao').AsString;
  Frm_Ap_Qry_InvInBillLine.lbl_No.Caption:=AdoQry_Detail.fieldbyname('InvBillno').AsString;
  Frm_Ap_Qry_InvInBillLine.lbl_Amount.Caption:=AdoQry_Detail.fieldbyname('NoTaxAmount').AsString;

  S:='select InvInBillline.InvBillId,'+
       ' InvInBillline.InvBilllineno,'+
       ' InvInBillline.ItemCode+'' ''+Item.ItemName as ItemB,'+
       ' InvInBillline.InvBillQty,'+
       ' InvInBillline.InvBillnotaxPriceC,'+
       ' InvInBillline.InvBillnotaxAmountC'+
    ' from InvInBillLine'+
    ' join Item on InvInBillline.ItemCode=Item.ItemCode';

  S:=S+ ' where InvBillid ='+AdoQry_Detail.fieldbyname('InvBillId').asstring+' Order by InvBilllineno ';
  Frm_Ap_Qry_InvInBillLine.AdoQry_Main.Close;
  Frm_Ap_Qry_InvInBillLine.AdoQry_Main.SQL.clear;
  Frm_Ap_Qry_InvInBillLine.AdoQry_Main.SQL.Add(S);
  Frm_Ap_Qry_InvInBillLine.AdoQry_Main.Open;
end;

procedure TFrm_Ap_Qry_VendorInvoice.FormCreate(Sender: TObject);
begin
  inherited;
  VendorCode:='';
  begindate:=Formatdatetime('yyyy.mm.dd',date-30);
  enddate:=Formatdatetime('yyyy.mm.dd',date);
  begindate1:=Formatdatetime('yyyy.mm.dd',date-30);
  enddate1:=Formatdatetime('yyyy.mm.dd',date);
end;

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

procedure TFrm_Ap_Qry_VendorInvoice.GetTransValue(FieldName,OldValue:String;var NewValue:String);
var
  i:integer;
begin
  NewValue:=OldValue;
  if DBGridEh<>nil then
    for i:=0 to DBGridEh.Columns.Count-1 do
      if(DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        if(DBGridEh.Columns[i].Field.DataType in [ftFloat])
          and(TFloatField(DBGridEh.Columns[i].Field).DisplayFormat<>'')
          and(OldValue<>'')then
        begin//处理 定义了打印格式的字段
          NewValue:=FormatFloat(TFloatField(DBGridEh.Columns[i].Field).DisplayFormat,StrToFloat(OldValue));
        end
        else if DBGridEh.Columns[i].KeyList.IndexOf(OldValue)<>-1 then
        begin//处理 pickList 与keyList
          NewValue:=DBGridEh.Columns[i].PickList.Strings[DBGridEh.Columns[i].
            KeyList.IndexOf(OldValue)];
        end;
        break;
      end;
end;

procedure TFrm_Ap_Qry_VendorInvoice.SetReport;
var
  i,j,k:integer;
  GroupCaption,ColumnTitle:String;
begin
  if DBGridEh.DataSource.DataSet.Active=False then
    Abort;
  GroupCaption:='';
  ExtPrintReport.DataSet:=nil;
  ExtPrintReport.Headers.clear;
  i:=0;
  k:=-1;
  with ExtPrintReport do                                           
  begin
    for j:=0 to DBGridEH.Columns.Count-1 do
      if DBGridEH.Columns[j].Visible then
      begin//只打印可视的数据
        Headers.Add;
        ColumnTitle:=DBGridEH.Columns[j].Title.Caption;
        Headers.Items[i].Caption :=Copy(ColumnTitle,Pos('|',ColumnTitle)+1,Length(ColumnTitle)-Pos('|',ColumnTitle));
        Headers.Items[i].FieldName :=DBGridEH.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH.Columns[j].Width div (DBGridEH.Canvas.TextWidth(' '));
        Headers.Items[i].Alignment :=DBGridEH.Columns[j].Alignment;
        if((DBGridEh.Columns[j].PickList.Count>0)
          and(DBGridEh.Columns[j].KeyList.Count>0))
          or(DBGridEh.Columns[j].Field.DataType in [ftFloat])then
        begin//定义了PickList或显示格式的传递一个转换过程
//          Headers.Items[i].Style:=dsTransForm;
//          Headers.Items[i].TransformFunction:=GetTransValue;
        end
        else//通知子类
          SetColumnsStyle(i,DBGridEH.Columns[j].FieldName);
        if (DBGridEH.UseMultiTitle)and(Pos('|',DBGridEH.Columns[j].Title.Caption)<>0) then
        begin//多层表头的处理
          ColumnTitle:=Copy(DBGridEH.Columns[j].Title.Caption,1,Pos('|',DBGridEH.Columns[j].Title.Caption)-1);
          if GroupCaption<>ColumnTitle then
          begin
            Inc(k);
            GroupCaption:=ColumnTitle;
            HeaderGroups.Add;
            HeaderGroups.Items[k].Caption:=GroupCaption;
            HeaderGroups.Items[k].StArtCol:=i+1;
            HeaderGroups.Items[k].endCol:=i+1;
          end
          else
          begin
            HeaderGroups.Items[k].endCol:=i+1;
          end;
        end
        else
          GroupCaption:='';
        Inc(i);
      end;
    DataSet:=DBGridEh.DataSource.DataSet;
    InitReport;
  end;
end;

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

procedure TFrm_Ap_Qry_VendorInvoice.InitReport;
begin//定义表头
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select SysParamValueC'
    +' From SysParam'
    +' Where SysParamCode=''Name0''';
  AdoQry_Tmp.Open;
  ExtPrintReport.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select ReportName1,ISOCode1'
    +' From ReportCtrl'
    +' Where SysMenuId='+MenuId;
  AdoQry_Tmp.Open;
  ExtPrintReport.Title2:=AdoQry_Tmp.fieldbyname('ReportName1').AsString;
  ExtPrintReport.SubTitle1:=AdoQry_Tmp.fieldbyname('ISOCode1').AsString;

  ExtPrintReport.Subtitle2:=Label3.Caption+Lbl_begindate.Caption+'至'+Lbl_Enddate.Caption+'      '+
                           Label1.Caption+Lbl_begindate1.Caption+'至'+Lbl_Enddate1.Caption;
end;


procedure TFrm_Ap_Qry_VendorInvoice.Act_PreviewExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.Preview;
end;

procedure TFrm_Ap_Qry_VendorInvoice.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.Print(self);
end;

end.