{*******************************************************}
{                                                       }
{                    貼票收入查詢統計                   }
{                                                       }
{            中軟金馬公司版權所有。2002.12前            }
{                                                       }
{            編制：中軟金馬郵資票品項目開發組           }
{                                                       }
{                                                       }
{*******************************************************}
(*

省級綜合查詢
*)
unit sz_tpsr;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, ComCtrls, StdCtrls, Buttons, ExtCtrls, Grids, DBGrids, Spin,
  DBCtrls, RXCtrls, ComboBoxDB, Clipbrd;

type
  TFrmz_TPSRCX = class(TForm)
    Panel_Head: TPanel;
    RxLabel_Title: TRxLabel;
    Panel_Waist: TPanel;
    DBGrid_TPSRCX: TDBGrid;
    GroupBox1: TGroupBox;
    Label3: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    ND_Start: TSpinEdit;
    ND_End: TSpinEdit;
    YF_End: TSpinEdit;
    YF_Start: TSpinEdit;
    BBt_Find: TBitBtn;
    BBt_Send: TBitBtn;
    BBt_Print: TBitBtn;
    BBt_Quit: TBitBtn;
    CB_DW: TComboBoxDB;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Qry_tpsr: TQuery;
    Ds_tpsr: TDataSource;
    Qry_tpsrhj: TQuery;
    Ds_tpsrhj: TDataSource;
    Label1: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure BBt_FindClick(Sender: TObject);
    procedure BBt_SendClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frmz_TPSRCX: TFrmz_TPSRCX;

implementation

uses
  pub,sz_txtpsrtjfx;

{$R *.DFM}

procedure TFrmz_TPSRCX.FormCreate(Sender: TObject);
begin
  GetAllColWidth(Self);
  ND_Start.Value := VG_Year;
  ND_End.Value := VG_Year;
  YF_Start.Value := 1;
  YF_End.Value := VG_Month;
//2002-03-08 by jillshao
{  if CB_DW.Open and (CB_DW.Items.Count > 0) then
  begin
    CB_DW.Items.Insert(0, '全部');
    CB_DW.ItemIndex := 0;
  end; }
  CB_DW.Open;
end;

procedure TFrmz_TPSRCX.BBt_FindClick(Sender: TObject);
const
  QuerySQL =
    'select B.DWMC,A.ND,A.YF,A.PPLBMC,A.SQTPSRHZ/10000 SQTPSRHZ,A.SQKCZZHZ/10000 SQKCZZHZ,A.BQSDZZHZ/10000 BQSDZZHZ,A.BQXFZZHZ/10000 BQXFZZHZ,'#13#10 +
    '  A.BQTJZZHZ/10000 BQTJZZHZ,A.BQZXZZHZ/10000 BQZXZZHZ,A.BQKCZZHZ/10000 BQKCZZHZ,A.CE/10000 CE,A.ZBR,A.SPR,A.ZBRQ,A.BZ,A.ZT,A.SDATE'#13#10 +
    'from TYS_TPSRYKCDZB A,TGS_GXDWSJB B'#13#10 +
    'where A.DWDM=B.DWDM';
  TotalSQL =
    'select ''合計'' HJ,sum(A.SQTPSRHZ)/10000 SQTPSRHZ,sum(A.SQKCZZHZ)/10000 SQKCZZHZ,sum(A.BQSDZZHZ)/10000 BQSDZZHZ,sum(A.BQXFZZHZ)/10000 BQXFZZHZ,'#13#10 +
    '  sum(A.BQTJZZHZ)/10000 BQTJZZHZ,sum(A.BQZXZZHZ)/10000 BQZXZZHZ,sum(A.BQKCZZHZ)/10000 BQKCZZHZ,sum(A.CE)/10000 CE'#13#10 +
    'from TYS_TPSRYKCDZB A where 1=1';
  function GetFmtMon(AMon: string): string;
  begin
    if Length(AMon) = 1 then
      Result := '0' + AMon
    else
      Result := AMon;
  end;
begin
  with Qry_tpsr do
  begin
    Close;
    SQL.Clear;
    SQL.Add(QuerySQL);
//2002-03-08 by jillshao
//    if CB_DW.ItemIndex > 0 then
      SQL.Add(Format('and A.DWDM=''%s''', [CB_DW.Values[CB_DW.ItemIndex]]));
    if YF_Start.Value <= 0 then
      SQL.Add(Format('and to_number(A.ND) between %d and %d', [ND_Start.Value, ND_End.Value]))
    else
      SQL.Add(Format('and to_number(A.ND||A.YF) between %s and %s', [ND_Start.Text + GetFmtMon(YF_Start.Text), ND_End.Text + GetFmtMon(YF_End.Text)]));
    Open;
  end;
  with Qry_tpsrhj do
  begin
    Close;
    SQL.Clear;
    SQL.Add(TotalSQL);
//2002-03-08 by jillshao
//    if CB_DW.ItemIndex > 0 then
      SQL.Add(Format('and A.DWDM=''%s''', [CB_DW.Values[CB_DW.ItemIndex]]));
    if YF_Start.Value <= 0 then
      SQL.Add(Format('and to_number(A.ND) between %d and %d', [ND_Start.Value, ND_End.Value]))
    else
      SQL.Add(Format('and to_number(A.ND||A.YF) between %s and %s', [ND_Start.Text + GetFmtMon(YF_Start.Text), ND_End.Text + GetFmtMon(YF_End.Text)]));
    Open;
  end;
end;

procedure TFrmz_TPSRCX.BBt_SendClick(Sender: TObject);
begin
  with TFrmz_txtpsrtjfx.Create(Self) do
  try
    ShowModal;
  finally
    Release;
  end;
end;

procedure TFrmz_TPSRCX.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SaveAllColWidth(Self);
end;

end.