querypd.pas

晓风财务软件源码,具有财务软基本功能,仅供学习,勿商用,后果与本人无关 1、使用的第三方VCL: FastReport2.6中文版 EHLIB3 sncCurrency SUIPac

unit QueryPD;

interface

uses
  Windows, Graphics, Controls, Forms, SysUtils, Dialogs, ExtFunc,
  SUIDBCtrls, ExtCtrls, SUIButton, StdCtrls, SUIComboBox, SUIEdit,
  ComCtrls, SUISideChannel, Classes, SUIForm, DB, ADODB, Grids, DBGrids,
  DBGridEh, FR_DSet, FR_DBSet, FR_Class, printers;

type
  TFrmQueryPD = class(TForm)
    suiForm1: TsuiForm;
    suiSideChannel1: TsuiSideChannel;
    d1: TDateTimePicker;
    d2: TDateTimePicker;
    Label1: TLabel;
    Label2: TLabel;
    editSummary: TsuiEdit;
    ckSummary: TsuiCheckBox;
    ckSubject: TsuiCheckBox;
    ckMoney: TsuiCheckBox;
    editMoney1: TsuiEdit;
    ckMoney_J: TsuiCheckBox;
    ckMoney_D: TsuiCheckBox;
    editMoney2: TsuiEdit;
    Label3: TLabel;
    ckMakePD: TsuiCheckBox;
    ListMakePD: TsuiComboBox;
    ckAuditingPD: TsuiCheckBox;
    ListAuditingPD: TsuiComboBox;
    btnGO: TsuiButton;
    Splitter1: TSplitter;
    Panel1: TPanel;
    g: TDBGridEh;
    q: TADOQuery;
    DataSource1: TDataSource;
    editSubject: TsuiEdit;
    editNum1: TsuiEdit;
    Label4: TLabel;
    editNum2: TsuiEdit;
    ckNum: TsuiCheckBox;
    btnPrint: TsuiButton;
    fr: TfrReport;
    frDS: TfrDBDataSet;
    q_Report: TADOQuery;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnGOClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure editMoney1KeyPress(Sender: TObject; var Key: Char);
    procedure editSubjectKeyPress(Sender: TObject; var Key: Char);
    procedure gDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
    procedure btnPrintClick(Sender: TObject);
  private
    { Private declarations }
  public
    SelectSubjectID:integer;
  end;

var
  FrmQueryPD: TFrmQueryPD;
implementation

uses EditSubject, PublicFunc, DataModule;

{$R *.dfm}

procedure TFrmQueryPD.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  q.Close;
  q_Report.Close;
end;

procedure TFrmQueryPD.btnGOClick(Sender: TObject);
var
  Condition,SqlCmd:string;
begin
  Condition:=' where (Datetime>='''+DateToStr(d1.date)+''' and Datetime<='''+DateToStr(d2.date)+''')';
  if ckNum.Checked then
    Condition:=Condition+' and (Number>='+editNum1.Text+' and Number<='+editNum2.Text+')';
  if ckSummary.Checked then
    Condition:=Condition+' and (Summary like ''%'+Trim(editSummary.Text)+'%'')';
  if ckSubject.Checked then
    Condition:=Condition+' and (Subject_id='+IntToStr(SelectSubjectID)+')';
  if ckMoney.Checked then
  begin
    if ckMoney_J.Checked then
      Condition:=Condition+' and (J_Money>='+editMoney1.Text+' and J_Money<='+editMoney2.Text+')';
    if ckMoney_D.Checked then
      Condition:=Condition+' and (D_Money>='+editMoney1.Text+' and D_Money<='+editMoney2.Text+')';
  end;
  if ckMakePD.Checked then
    Condition:=Condition+' and (rTrim(LTrim(MakeOperator))='''+ListMakePD.Text+''')';
  if ckAuditingPD.Checked then
    Condition:=Condition+' and (rTrim(LTrim(AuditingOperator))='''+ListAuditingPD.Text+''')';

  SqlCmd:='select * from V_Credence Where ([Year] in (select distinct [Year] from V_Credence '+Condition+')) and ';
  SqlCmd:=SqlCmd+'([Month] in (select distinct [Month] from V_Credence '+Condition+')) and ';
  SqlCmd:=SqlCmd+'(Number in  (select distinct Number  from V_Credence '+Condition+')) order by [Year], [Month], Number, RecNo';

  q.Close;
  q.SQL.Clear;
  q.SQL.Add(SqlCmd);
  q.Open;
  g.SumList.Active:=true;
  btnPrint.Enabled:=(q.RecordCount>0);
end;

procedure TFrmQueryPD.FormShow(Sender: TObject);
begin
  SelectSubjectID:=-1;
  q.Connection:=DM.adoc;
  d1.Date:=StrToDate(IntToStr(DM.AccountYear)+'-'+IntToStr(DM.AccountMonth)+'-1');
  if DM.AccountMonth=12 then d2.Date:=StrToDate(IntToStr(DM.AccountYear+1)+'-1-1')-1
  else d2.Date:=StrToDate(IntToStr(DM.AccountYear)+'-'+IntToStr(DM.AccountMonth+1)+'-1')-1;

  q.Close;
  q.SQL.Clear;
  q.SQL.Add('select * from Operator where Used=1 order by id');
  q.Open;
  while not q.Eof do
  begin
    ListMakePD.Items.Add(Trim(q.FieldByName('Name').AsString));
    ListAuditingPD.Items.Add(Trim(q.FieldByName('Name').AsString));
    q.Next;
  end;
  q.Close;
  btnGo.Click;
end;

procedure TFrmQueryPD.editMoney1KeyPress(Sender: TObject; var Key: Char);
begin
  if not (Key in ['0','1','2','3','4','5','6','7','8','9']) then Key:=#0;
end;

procedure TFrmQueryPD.editSubjectKeyPress(Sender: TObject; var Key: Char);
var
  fes:TFrmEditSubject;
begin
  Key:=#0;
  fes:=TFrmEditSubject.Create(self);
  fes.CanEdit:=false;
  fes.CanSelect:=true;
  fes.ShowModal;
  if fes.SelectID<>-1 then
  begin
    SelectSubjectID:=fes.SelectID;
    editSubject.Text:=fes.SelectCodeLongSubjectName;
  end;
  fes.Destroy;
end;

procedure TFrmQueryPD.gDrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
var
  oldcolor:tcolor;
  oldpm:tpenmode;
  oldFontColor:TColor;
begin
  oldpm:= g.Canvas.pen.mode;
  oldcolor:= g.Canvas.Brush.color;
  oldFontColor:=g.Canvas.Font.Color;

  if (q.FieldByName('RecNo').AsInteger=1) then {设定变色的行的条件}
  begin
    g.Canvas.Brush.color:=$00E8E8E8;
    g.Canvas.pen.mode:=pmmask;
  end;
  if ((column.FieldName='J_Money') and (q.FieldByName('J_Money').AsCurrency<0)) or
       ((column.FieldName='D_Money') and (q.FieldByName('D_Money').AsCurrency<0)) then
    g.Canvas.Font.Color:=RGB(255,0,0);
  if ((column.FieldName='Year') or (column.FieldName='Month')
     or (column.FieldName='Number') or (column.FieldName='DateTime')
     or (column.FieldName='AccessoryNum') or (column.FieldName='MakeOperator')
     or (column.FieldName='AuditingOperator'))
     and (q.FieldByName('RecNo').AsInteger<>1) then
    g.Canvas.Font.Color:=RGB(255,255,255);

  g.DefaultDrawColumnCell (Rect,DataCol, Column, State);
  g.Canvas.Brush.color:=oldcolor;
  g.Canvas.pen.mode:=oldpm;
  g.Canvas.Font.Color:=oldFontColor;
end;

procedure TFrmQueryPD.btnPrintClick(Sender: TObject);
var
  Year,Month,Number:string;
begin
  if not DM.CheckSoftReg then Exit;
  q_Report.Clone(q);
  q.First;
  while not q.Eof do
  begin
    if q.FieldByName('RecNo').AsInteger=1 then
    begin
      Year:=q.FieldByName('Year').AsString;
      Month:=q.FieldByName('Month').AsString;
      Number:=q.FieldByName('Number').AsString;
      q_report.Filter:='Year='+Year+' and Month='+Month+' and Number='+Number;
      q_report.Filtered:=true;
      q_report.First;
      fr.LoadFromFile(FUN_GetCurrentPath^+'Report\Credence.frf');
      fr.Pages[0].ChangePaper(256,DM.PDPageWidth,DM.PDPageHeight,-1,poLandscape);
      frVariables['DeptName']:=DM.DeptName;
      frVariables['ReportDate']:=DateToStr(q_report.FieldByName('Datetime').AsDateTime);
      frVariables['FinanceManager']:=DM.FinanceManager;
      frVariables['MakeOperator']:=q_report.FieldByName('MakeOperator').AsString;
      frVariables['AuditingOperator']:=q_report.FieldByName('AuditingOperator').AsString;
      frVariables['Number']:=Number;
      frVariables['AccessoryNum']:=q_report.FieldByName('AccessoryNum').AsString;
      fr.PrepareReport;
      fr.PrintPreparedReport('',1,true,frAll);
    end;
    q.Next;
  end;
  q_report.Close;
end;

end.