{*******************************************************}
{                                                       }
{                 集郵商品業(yè)務(wù)收入統(tǒng)計(jì)表                  }
{                                                       }
{            中軟金馬公司版權(quán)所有。2002.12前            }
{                                                       }
{            編制：中軟金馬郵資票品項(xiàng)目開發(fā)組           }
{                                                       }
{                                                       }
{*******************************************************}
(*
省級(jí)集郵品管理  ,地市集郵票管理

*)
unit SY_JYSPNXSTJB;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  EHGrids, DBGridEh, Db, DBTables, StdCtrls, Spin, ExtCtrls, Buttons,
  RXCtrls;

type
  TFrmY_JYSPNXSTJB = class(TForm)
    Panel11: TPanel;
    DWMC: TLabel;
    Panel1: TRxLabel;
    Panel3: TPanel;
    BBt_Quit: TBitBtn;
    BBt_Find: TBitBtn;
    BBt_Print: TBitBtn;
    btn_dj: TBitBtn;
    Pnl_Condition: TPanel;
    lbl_ND: TLabel;
    lbl_YF: TLabel;
    RGr_Method: TRadioGroup;
    SED_Year: TSpinEdit;
    SED_Month: TSpinEdit;
    Qry_Search: TQuery;
    Dso_Search: TDataSource;
    Qry_Any: TQuery;
    DBG_ND: TDBGridEh;
    Qry_SearchDWDM: TStringField;
    Qry_SearchDWMC: TStringField;
    Qry_SearchJYPSR: TFloatField;
    Qry_SearchYPSR: TFloatField;
    Qry_SearchQTSR: TFloatField;
    Qry_SearchML: TFloatField;
    Qry_SearchHJ: TFloatField;
    Qry_SearchJYPSRHJ: TFloatField;
    Qry_SearchYPSRHJ: TFloatField;
    Qry_SearchQTSRHJ: TFloatField;
    Qry_SearchMLHJ: TFloatField;
    Qry_SearchHJHJ: TFloatField;
    Label1: TLabel;
    RG_JB: TRadioGroup;
    Bbt_Send: TBitBtn;
    DBG_YF: TDBGridEh;
    UpdateSQL1: TUpdateSQL;
    procedure BBt_PrintClick(Sender: TObject);
    procedure btn_djClick(Sender: TObject);
    procedure BBt_FindClick(Sender: TObject);
    procedure RGr_MethodClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Qry_SearchJYPSRGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure SED_YearChange(Sender: TObject);
    procedure SED_MonthChange(Sender: TObject);
    procedure RG_JBClick(Sender: TObject);
    procedure Bbt_SendClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmY_JYSPNXSTJB: TFrmY_JYSPNXSTJB;
const
  SQLText = ' Select B.DWDM ,B.DWMC ,NVL(A.JYPSR/10000,0) JYPSR,NVL(A.YPSR/10000,0) YPSR,NVL(A.QTSR/10000,0) QTSR,NVL(A.ML/10000,0)ML,NVL(A.HJ/10000,0) HJ'
    + ' ,B.JYPSR/10000 JYPSRHJ,B.YPSR/10000 YPSRHJ,B.QTSR/10000 QTSRHJ,B.ML/10000 MLHJ,B.HJ/10000 HJHJ from '
    + ' (Select B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM AND B.ZJXJ =''1'''
    + ' AND ND=:ND AND YF=:YF'
    + ' GROUP BY B.DWDM,B.DWJC) A ,'
    + ' (Select DWDM ,DWMC ,JYPSR ,YPSR ,QTSR ,ML ,HJ from '
    + ' (Select B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM'
    + ' AND ND=:ND AND to_number(YF)<=:YF AND B.ZJXJ =''1'' '
    + ' GROUP BY B.DWDM,B.DWJC)) B,TGS_GXDWSJB C '
    + ' WHERE A.DWDM(+)=B.DWDM AND A.DWDM=C.DWDM order by C.PXM';

  SQLText2 = ' Select B.DWDM ,B.DWMC ,NVL(A.JYPSR/10000,0) JYPSR,NVL(A.YPSR/10000,0) YPSR,NVL(A.QTSR/10000,0) QTSR,NVL(A.ML/10000,0)ML,NVL(A.HJ/10000,0) HJ'
    + ' ,B.JYPSR/10000 JYPSRHJ,B.YPSR/10000 YPSRHJ,B.QTSR/10000 QTSRHJ,B.ML/10000 MLHJ,B.HJ/10000 HJHJ from '
    + ' (Select B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM AND B.BJ =''1'''
    + ' AND ND=:ND AND YF=:YF'
    + ' GROUP BY B.DWDM,B.DWJC) A ,'
    + ' (Select DWDM ,DWMC ,JYPSR ,YPSR ,QTSR ,ML ,HJ from '
    + ' (Select B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM'
    + ' AND ND=:ND AND to_number(YF)<=:YF AND B.BJ =''1'' '
    + ' GROUP BY B.DWDM,B.DWJC)) B ,TGS_GXDWSJB C '
    + ' WHERE A.DWDM(+)=B.DWDM AND A.DWDM=C.DWDM order by C.PXM';

  SQLText1 = ' Select DWDM ,DWMC ,NVL(JYPSR/10000,0) JYPSR ,NVL(YPSR/10000,0) YPSR,NVL(QTSR/10000,0) QTSR,NVL(ML/10000,0) ML,NVL(HJ/10000,0) HJ'
    + ' ,0 JYPSRHJ,0 YPSRHJ,0 QTSRHJ,0 MLHJ,0 HJHJ from '
    + ' ( Select b.pxm, B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM AND B.ZJXJ=''1'' '
    + ' AND ND=:ND AND :YF <> ''-1'' '
    + ' GROUP BY B.DWDM,B.DWJC, b.pxm) order by pxm ';

  SQLText3 = ' Select DWDM ,DWMC ,NVL(JYPSR/10000,0) JYPSR ,NVL(YPSR/10000,0) YPSR,NVL(QTSR/10000,0) QTSR,NVL(ML/10000,0) ML,NVL(HJ/10000,0) HJ'
    + ' ,0 JYPSRHJ,0 YPSRHJ,0 QTSRHJ,0 MLHJ,0 HJHJ from '
    + ' ( Select B.DWDM,B.DWJC DWMC,sum(A.JYPSR) JYPSR ,sum(A.YPSR) YPSR ,sum(A.QTSR) QTSR,sum(A.ML) ML,sum(nvl(A.JYPSR,0)+nvl(A.YPSR,0)+nvl(A.QTSR,0)) HJ'
    + ' from TYS_JYYWSRTJB A,TGS_GXDWSJB B WHERE A.DWDM=B.DWDM AND B.BJ=''1'' '
    + ' AND ND=:ND AND :YF <> ''-1'' '
    + ' GROUP BY B.DWDM,B.DWJC) ';

implementation
uses datas,pub, SY_DataSend, SY_JYYWSRDJ, SY_JYSPNXSTJB_RPT, SY_JYSPNXSTJB_RPT1  ;

{$R *.DFM}

procedure TFrmY_JYSPNXSTJB.BBt_PrintClick(Sender: TObject);
begin
  if not SED_Month.Visible then //nian
  begin
    JYSPNXSTJB_RPT := TJYSPNXSTJB_RPT.Create(nil);
    try
      JYSPNXSTJB_RPT.ND := SED_Year.Text;
      case RG_JB.ItemIndex of
        0: //bj
          JYSPNXSTJB_RPT.BJXJ := 'B.BJ';
        1: //xj
          JYSPNXSTJB_RPT.BJXJ := 'B.ZJXJ';
      end;
      JYSPNXSTJB_RPT.ReportTitle := SED_Year.Text + '年集郵業(yè)務(wù)收入統(tǒng)計(jì)';
      JYSPNXSTJB_RPT.Preview;
    except
      JYSPNXSTJB_RPT.Free;
    end;
    JYSPNXSTJB_RPT.Free;
  end
  else
  begin
    JYSPNXSTJB_RPT1 := TJYSPNXSTJB_RPT1.Create(nil);
    try
      JYSPNXSTJB_RPT1.ND := IntToStr(SED_Year.Value);
      JYSPNXSTJB_RPT1.YF := IntToStr(SED_Month.Value);
      JYSPNXSTJB_RPT1.Qry_Print.Close;
      JYSPNXSTJB_RPT1.Qry_Print.Sql.Text := Qry_Search.SQL.Text;
      JYSPNXSTJB_RPT1.Qry_Print.Open;
      JYSPNXSTJB_RPT1.ReportTitle := SED_Year.Text + '年' + SED_Month.Text + '月集郵業(yè)務(wù)收入統(tǒng)計(jì)';
      JYSPNXSTJB_RPT1.Preview;
    except
      JYSPNXSTJB_RPT1.Free;
    end;
    JYSPNXSTJB_RPT1.Free;
  end;

end;

procedure TFrmY_JYSPNXSTJB.btn_djClick(Sender: TObject);
begin
  with TfrmP_JYYWSRDJ.Create(Self) do
  begin
    if self.SED_Month.Visible then
      Init(SED_Year.Value, SED_Month.Value, RG_JB.ItemIndex = 0);
    try
      ShowModal;
    finally
      Free;
    end;
  end;
  BBt_FindClick(nil);
end;

procedure TFrmY_JYSPNXSTJB.BBt_FindClick(Sender: TObject);
begin
  with Qry_Search do
  begin
    Close;
    if RGr_Method.ItemIndex = 1 then
    begin
      if RG_JB.ItemIndex = 1 then
        SQL.Text := SQLText
      else
        SQL.Text := SQLText2;
    end
    else
    begin
      if RG_JB.ItemIndex = 1 then
        SQL.Text := SQLText1
      else
        SQL.Text := SQLText3;
    end;
    Params.ParamByName('ND').AsInteger := SED_Year.Value;
    Params.ParamByName('YF').AsInteger := SED_Month.Value;
    Open;
  end;
end;

procedure TFrmY_JYSPNXSTJB.RGr_MethodClick(Sender: TObject);
begin
  btn_dj.Visible := RGr_Method.ItemIndex = 1;
  if RGr_Method.ItemIndex = 1 then
    DBG_YF.BringToFront
  else
    DBG_ND.BringToFront;
  lbl_YF.Visible := RGr_Method.ItemIndex = 1;
  SED_Month.Visible := RGr_Method.ItemIndex = 1;
  RG_JBClick(nil);
end;

procedure TFrmY_JYSPNXSTJB.FormCreate(Sender: TObject);
begin
  DWMC.Caption := '  使用單位：' + VG_UnitName;
  SED_Year.Value := VG_Year;
  SED_Month.Value := VG_Month;
end;

procedure TFrmY_JYSPNXSTJB.Qry_SearchJYPSRGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  if not Sender.IsNull then
    Text := FormatFloat('#,##0.####', Sender.AsFloat);
end;

procedure TFrmY_JYSPNXSTJB.SED_YearChange(Sender: TObject);
begin
  if (SED_Year.Text = '') or (SED_Month.Text = '') then Exit;
  BBt_FindClick(nil);
end;

procedure TFrmY_JYSPNXSTJB.SED_MonthChange(Sender: TObject);
begin
  BBt_FindClick(nil);
end;

procedure TFrmY_JYSPNXSTJB.RG_JBClick(Sender: TObject);
begin
  BBt_Send.Visible := (RG_JB.ItemIndex = 0) and (RGr_Method.ItemIndex = 1);
  BBt_FindClick(nil);
end;

procedure TFrmY_JYSPNXSTJB.Bbt_SendClick(Sender: TObject);
begin
  if Qry_Search.IsEmpty then
  begin
    CHQMsgBox('請(qǐng)選擇要上報(bào)的集郵業(yè)務(wù)收入統(tǒng)計(jì)表！');
    Exit;
  end;

  VG_SQLWhere := ' select * from TYS_JYYWSRTJB '
    + ' where DWDM =''' + VG_UnitID + ''' '
    + ' and ND =''' + SED_Year.Text + ''' '
    + ' and YF =''' + SED_Month.Text + ''' ';
  if SendData(VG_SQLWhere, 'TYS_JYYWSRTJB', false) = IDOK then
    CHQMsgBox('信息發(fā)送成功！');


end;

end.