cqyfkbb.pas

已经给出使用的每个SQL Server数据库

unit cqyfkbb;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, RpDefine, RpBase, RpSystem, Grids, StdCtrls, ComCtrls,
  Buttons,main;

type
  Tf_cqyfkbb = class(TForm)
    bt: TLabel;
    Panel2: TPanel;
    dy: TBitBtn;
    yl: TBitBtn;
    BitBtn2: TBitBtn;
    cx: TBitBtn;
    Panel1: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    sj: TDateTimePicker;
    czy: TEdit;
    bg: TStringGrid;
    Rprinter: TRvSystem;
    tx: TImage;
    procedure cxClick(Sender: TObject);
    procedure bgClick(Sender: TObject);
    procedure bgDrawCell(Sender: TObject; ACol, ARow: Integer; Rect: TRect;
      State: TGridDrawState);
    procedure bgSelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
    procedure dyClick(Sender: TObject);
    procedure RprinterPrint(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ylClick(Sender: TObject);
  private
    { Private declarations }
  public
    procedure SetGridFieldName;
    { Public declarations }
  end;
  RFont = Record   //设置字体
    RFontName: String;
    RFontSize: Integer;
    RFontColor: Integer;
    RFontStyle: TFontStyles;
  End;
  RContent = ReCord //设置正文
    TitleHeight: Real; //标题高度
    BtHeight: Real;//表头与标题的间距
    Bgjj: Real;//表格与表头的间距
    Czyjj: real;//操作员与其标签的水平距离
    sjjj: real;//时间与其标签的水平距离
    Bgdqfs: integer;//表格内容的对齐方式 0居左,1居右,2居中
  end;
var
  f_cqyfkbb: Tf_cqyfkbb;
  csmc: Array of String;//记录未及时向厂商结款的厂商信息
  yjsj: integer = 25;//假设月结时间为每月的25日
  TempField: String ='';
  Sumzdkd: real=0.0;// 字段总宽度
  Rowzdkd: Real=0.0;//每行字段总宽度
  zbj: real=0.0; //正文左边距
  Content: RContent;
  AZdmc: Array[0..3]of String=('厂商名称','欠厂商款','最近付款金额','最近付款时间');
  AZdkd : Array[0..3]of Real=(1.2,1.2,1.2,1.2); //数组中的元素对应表格中相应字段的宽度
  TitleFont,BtFont,BgtitleFont,BgFont: RFont;//读取或设置标题,表头,表格字体,表格标题字体
  zds : integer = 4;//表格字段数
  Cols: integer = 4;//每行列数
  row,row1: integer;
implementation
  uses DataModal,csyskdysz;
{$R *.dfm}

procedure Tf_cqyfkbb.cxClick(Sender: TObject);
var
  i: Integer;//记录没有按时向厂商结款的厂商数
  m,c: Integer;
  fktj: String;//记录厂商的付款条件,例如月结2天付款
  jkfs: String;//记录厂商结款方式,例如月结,货到,月结月底
  ts: Integer;//记录厂商结款方式中的结款天数
  yjksj: TDateTime;//厂商应结款的时间
  cgdsj: TDateTime;//记录最近的采购单中的时间
  year,month,day,days: Word;  //days记录当前月有多少天
begin
  i := 0;
  cgdsj := Now;
  days := 30;
  DecodeDate(sj.DateTime,year,month,day);
  with Data.Query1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select  csmc from t_csjk'); //厂商结款表中khmc字段为唯一索引
    Open;
  end;
  setlength(csmc,Data.Query1.RecordCount);//设置数组大小为厂商结款表中的记录数
  Data.Query1.First;
  while not Data.Query1.Eof do //利用循环将没有及时向厂商结款的厂商记录到数组中
  begin
    //首先确定厂商的应结款时间
    with Data.Query3 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from t_cszl where csmc = :a');
      ParamByName('a').AsString := Trim(Data.Query1.FieldByName('csmc').AsString);
      Open;
    end;
    fktj := Data.Query3.FieldByName('fktj').AsString;
    with Data.Query3 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from t_fkdm where fktj = :a');
      ParamByName('a').AsString := Trim(fktj);
      Open;
    end;
    jkfs := Data.Query3.FieldByName('fkfs').AsString;
    ts := Data.Query3.FieldByName('ts').AsInteger;
    if Trim(jkfs)='货到' then //以最近的采购单时间为基准计算向厂商应结款时间
    begin
      with Data.Query3 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select max(sj) from t_cgph where csmc = :a');
        ParamByName('a').AsString := Trim(Data.Query1.FieldByName('csmc').AsString);
        Open;
      end;
      cgdsj := Data.Query3.Fields[0].AsDateTime;
      yjksj := cgdsj+ts;//厂商结款时间=最近与厂商的采购单时间+天数
    end
    {todo: 由于还没有确定月结时间,所以假设月结时间为每月的25日}
    else if Trim(jkfs)='月结' then
    begin
      yjksj := Trunc(sj.DateTime)-day+yjsj+ts;
    end
    else //月结月底
    begin
      Case Month of
        1,3,5,7,8,10,12: Days := 31;
        2: begin
             if isLeapYear(Year)=True then
               Days :=29
             else
               Days := 28
           end;
        4,6,9: Days := 30;
      end;
      yjksj := Trunc(sj.DateTime)-day+Days+ts;
    end;
    with Data.Query2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from t_csjkjl where sj >= :a and sj <:b and csmc = :c');
      if Trim(jkfs) = '货到' then
        ParamByName('a').AsDateTime := cgdsj //起始时间
      else if Trim(jkfs) = '月结' then
        ParamByName('a').AsDateTime := Trunc(sj.DateTime)-day+yjsj
      else  //月结月底
        ParamByName('a').AsDateTime := Trunc(sj.DateTime)-day+Days;
      ParamByName('b').AsDateTime := Trunc(yjksj)+1; //截止时间
      ParamByName('c').AsString := Data.Query1.FieldByName('csmc').AsString;
      Open;
    end;
    if Data.Query2.RecordCount<=0 then     //在结款时间内没有收到客户结款,记录客户信息
    begin
      csmc[i]:= Data.Query1.FieldByName('csmc').AsString;
      inc(i); //在循环后i值会比数组中的元素数多一
    end;
    Data.Query1.Next;
  end;
  if  i = 0 then
  begin
    dy.Enabled := False;
    Application.MessageBox('均向所有厂商按期付款.','提示',64);
    Exit;
  end
  else
  begin
    dy.Enabled := True;
    bg.RowCount := i+1;
    For m :=0 to i-1 do
    begin
      with Data.Query3 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select aa.csmc,aa.yfje,bb.czje,max(bb.sj) from t_csjk aa inner join  t_csjkjl bb on aa.csmc = :a and bb.csmc = :a group by aa.csmc ,aa.yfje,bb.czje ');
        ParamByName('a').AsString := Trim(csmc[m]);
        Open;
      end;
      if Data.Query3.RecordCount>0 then
        for c := 1 to bg.ColCount-1 do
          bg.Cells[c,m+1]:= Data.Query3.Fields[c-1].AsString;
    end;
  end;
end;

procedure Tf_cqyfkbb.SetGridFieldName;
var
  rect: TRect;
  zdmc: String;
  zdwidth: integer;
  zdheight,i: Integer;
begin
  zdheight:= 12;
  For i := 1 to bg.ColCount-1 do
  begin
    rect := bg.CellRect(i,0);
    case i of
      1: zdmc := '厂商名称';
      2: zdmc := '欠厂商款';
      3: zdmc := '最近付款金额';
      4: zdmc := '最近付款时间';
    end;
    zdwidth := length(zdmc);
    bg.Canvas.Brush.Color := clBtnFace;
    bg.Canvas.Font.Color := clRed;
    bg.Canvas.TextOut(Rect.Left+Trunc((bg.ColWidths[i]-zdwidth*6.5)/2),Rect.Top+Trunc((bg.RowHeights[0] -zdheight)/2),zdmc );
  end;
end;

procedure Tf_cqyfkbb.bgClick(Sender: TObject);
var
  Rect: TRect;
begin
  if row<>0 then
  begin
    rect := bg.CellRect(0,row);
    bg.Canvas.Brush.Color := clBtnFace;
    bg.Canvas.FillRect(rect);
  end;
  rect := bg.CellRect(0,row1);
  bg.Canvas.Draw(rect.Left+Trunc((rect.Right-rect.Left-tx.Width)/2),rect.Top+Trunc((rect.Bottom-rect.Top-tx.Height)/2),tx.Picture.Graphic);
  row := row1;
  bg.Repaint;
end;

procedure Tf_cqyfkbb.bgDrawCell(Sender: TObject; ACol, ARow: Integer;
  Rect: TRect; State: TGridDrawState);
var
  rect1: TRect;
begin
  SetGridFieldName;
  if Row1<>0 then
  begin
    rect1 := bg.CellRect(0,row1);
      bg.Canvas.Draw(rect1.Left+Trunc((rect1.Right-rect1.Left-tx.Width)/2),rect1.Top+Trunc((rect1.Bottom-rect1.Top-tx.Height)/2),tx.Picture.Graphic);
  end;
  rect1 := bg.CellRect(0,0);
  bg.Canvas.Brush.Color := clBtnFace;
  bg.Canvas.FillRect(rect1);
end;

procedure Tf_cqyfkbb.bgSelectCell(Sender: TObject; ACol, ARow: Integer;
  var CanSelect: Boolean);
begin
  row1 := Arow;
end;

procedure Tf_cqyfkbb.dyClick(Sender: TObject);
begin
  Rprinter.Execute;
end;

procedure Tf_cqyfkbb.RprinterPrint(Sender: TObject);
var
 justify: TPrintJustify;
 i,m,n,l,r: integer;
begin      
    with Sender as TBaseRePort do
    begin
      justify := pjleft;
      zbj := (PageWidth-Rowzdkd)/2;
      SetFont(TitleFont.RFontName,TitleFont.RFontSize);
      FontColor := TitleFont.RFontColor;
      ypos :=Content.TitleHeight; ;
      PrintCenter('超期应付款报表',PageWidth/2);
      setFont(BtFont.RFontName,BtFont.RFontSize);
      FontColor := BtFont.RFontColor;