{*******************************************************}
{                                                       }
{                        查詢選項                       }
{                                                       }
{            中軟金馬公司版權所有。2002.12前            }
{                                                       }
{            編制：中軟金馬郵資票品項目開發組           }
{                                                       }
{                                                       }
{*******************************************************}
(*
本模塊在省級系統管理模塊里集郵品庫存查詢，分類帳校對調用。

*)
unit SS_YP_Init_Search;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, Spin, FieldComboBox, ExtCtrls, Db, DBTables, ClipBrd;

type
  TFrm_YP_Init_Search = class(TForm)
    pnl_Search: TPanel;
    chk_PPMZ: TCheckBox;
    chk_ND: TCheckBox;
    chk_PPSJ: TCheckBox;
    chk_ZH: TCheckBox;
    chk_PPLB: TCheckBox;
    fcb_PPLB: TFieldComboBox;
    fcb_PPXLB: TFieldComboBox;
    edt_PPMZ: TEdit;
    se_From_ND: TSpinEdit;
    edt_SJ: TEdit;
    edt_ZH: TEdit;
    chk_PPXLB: TCheckBox;
    bbtn_Search: TBitBtn;
    bbtn_Cancel: TBitBtn;
    lbl_PPMZ: TLabel;
    lbl_SJ: TLabel;
    qry_Tmp: TQuery;
    se_To_ND: TSpinEdit;
    lbl_To_ND: TLabel;
    chk_MC: TCheckBox;
    edt_MC: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure fcb_PPLBChange(Sender: TObject);
    procedure chk_PPLBClick(Sender: TObject);
    procedure chk_NDClick(Sender: TObject);
    procedure chk_PPMZClick(Sender: TObject);
    procedure chk_PPSJClick(Sender: TObject);
    procedure chk_ZHClick(Sender: TObject);
    procedure chk_PPXLBClick(Sender: TObject);
    procedure edt_PPMZKeyPress(Sender: TObject; var Key: Char);
    procedure bbtn_SearchClick(Sender: TObject);
    procedure bbtn_CancelClick(Sender: TObject);
    procedure chk_MCClick(Sender: TObject);
  private
    { Private declarations }
    procedure Init_Public_Data;                             {初始化公共數據}
    function Test_Input: Boolean;                           {測試輸入是否合法}
    function Generate_SQL_part: Boolean;                    {生成查詢的部分SQL}
    function Change_Comma(Text: string): string;            {改變成符合SQL的語句}

  public
    { Public declarations }
    procedure Get_Default;                                  {取得默認值}
  end;

var
  Frm_YP_Init_Search: TFrm_YP_Init_Search;

implementation

{$R *.DFM}

{郵品查詢的公共模塊，實際是拼寫SQL的Where后面的條件。使用了公共系統變量
  VG_l_PPLB、VG_l_PPDLB、VG_l_PPXLB、VG_l_From_ND、VG_l_To_ND、VG_l_PPMZ
  VG_l_PPSJ、VG_l_ZH、VG_l_MC、part_SQL、Search_Caption
  這些變量保存在libproc中。
  拼寫完成的SQL保存在part_SQL中，同時生成說明Search_Caption。

  石玉琢 2001.09.26補}

uses
  Pub;                                                  {從中獲得一些初始化數據}

{-------------------------------------------------------------------------------}
{初始化公共數據}
procedure TFrm_YP_Init_Search.Init_Public_Data;
begin
  {顯示默認年度}
  with qry_Tmp do
  begin
    Close;
    SQL.Text := 'Select SysDate from Dual';
    Open;
    se_From_ND.Text := FormatDateTime('yyyy', FieldByName('SysDate').AsDateTime);
    se_To_ND.Text := FormatDateTime('yyyy', FieldByName('SysDate').AsDateTime);
  end;

  with qry_Tmp do
  begin
    Close;
    SQL.Text := 'Select MC,JYPLDM From TG_JYPLDMB Where LB=''大類'' order by JYPLDM';
    Open;

    fcb_PPLB.ClearAll;
    while not Eof do
    begin
      fcb_PPLB.Items.Add(FieldByName('MC').AsString);
      fcb_PPLB.FieldItems.Add(FieldByName('JYPLDM').AsString);
      Next;
    end;
{
    if fcb_PPLB.Items.Count > 0 then
    begin
      fcb_PPLB.ItemIndex := 0;
      fcb_PPLBChange(nil);
    end;
}
  end;
end;

{大類改變，小類跟著改變}
procedure TFrm_YP_Init_Search.fcb_PPLBChange(Sender: TObject);
begin
  with qry_Tmp do
  begin
    Close;
    SQL.Text := 'Select MC,JYPLDM From TG_JYPLDMB Where LB=''小類'' and SubStr(JYPLDM,1,1)=''' + fcb_PPLB.FieldString + ''' order by JYPLDM';
    Open;
    fcb_PPXLB.ClearAll;
    while not Eof do
    begin
      fcb_PPXLB.Items.Add(FieldByName('MC').AsString);
      fcb_PPXLB.FieldItems.Add(FieldByName('JYPLDM').AsString);
      Next;
    end;
  end;
end;

{------------------------------------------------------------------------------}
{取得默認值}
procedure TFrm_YP_Init_Search.Get_Default;
begin
  {選擇了大類}
  if VG_l_PPLB <> '' then
  begin
    chk_PPLB.Checked := True;
    fcb_PPLB.Enabled := True;
    fcb_PPLB.ItemIndex := fcb_PPLB.FieldItems.IndexOf(VG_l_PPLB);
    chk_PPXLB.Checked := False;
    fcb_PPXLB.Enabled := False;
    fcb_PPLBChange(nil);
  end;

  {選擇了小類}
  if VG_l_PPXLB <> '' then
  begin
    chk_PPLB.Checked := True;
    fcb_PPLB.Enabled := True;
    fcb_PPLB.ItemIndex := fcb_PPLB.FieldItems.IndexOf(VG_l_PPLB);
    fcb_PPLBChange(nil);
    chk_PPXLB.Checked := True;
    chk_PPXLB.Enabled := True;
    fcb_PPXLB.Enabled := True;
    fcb_PPXLB.ItemIndex := fcb_PPXLB.FieldItems.IndexOf(VG_l_PPXLB);
  end;
  {選擇了年度}
  if VG_l_From_ND <> 0 then
  begin
    chk_ND.Checked := True;
    se_From_ND.Text := IntToStr(VG_l_From_ND);
  end;
  if VG_l_To_ND <> 0 then
  begin
    chk_ND.Checked := True;
    se_To_ND.Text := IntToStr(VG_l_to_ND);
  end;

  {選擇了票品面值}
  if VG_l_PPMZ <> 0 then
  begin
    chk_PPMZ.Checked := True;
    edt_PPMZ.Enabled := True;
    edt_PPMZ.Text := FloatToStr(VG_l_PPMZ);
  end;
  {選擇了票品售價}
  if VG_l_PPSJ <> 0 then
  begin
    chk_PPSJ.Checked := True;
    edt_SJ.Enabled := True;
    edt_SJ.Text := FloatToStr(VG_l_PPSJ);
  end;
  {填寫默認志號}
  if VG_l_ZH <> '' then
  begin
    chk_ZH.Checked := True;
    edt_ZH.Enabled := True;
    edt_ZH.Text := VG_l_ZH;
  end;
  {填寫名稱}
  if VG_l_MC <> '' then
  begin
    chk_MC.Checked := True;
    edt_MC.Enabled := True;
    edt_MC.Text := VG_l_MC;
  end;
end;

procedure TFrm_YP_Init_Search.FormCreate(Sender: TObject);
begin
  Init_Public_Data;
  Get_Default;
end;

{===============================================================================}
{票品類別，大類}
procedure TFrm_YP_Init_Search.chk_PPLBClick(Sender: TObject);
begin
  if chk_PPLB.Checked = True then
  begin
    fcb_PPLB.Enabled := True;
    chk_PPXLB.Enabled := True;
    chk_PPXLBClick(Sender);
  end
  else
  begin
    fcb_PPLB.Enabled := False;
    chk_PPXLB.Enabled := False;
    fcb_PPXLB.Enabled := False;
  end;
end;
{票品類別，小類}
procedure TFrm_YP_Init_Search.chk_PPXLBClick(Sender: TObject);
begin
  fcb_PPXLB.Enabled := chk_PPXLB.Checked;
end;
{年度}
procedure TFrm_YP_Init_Search.chk_NDClick(Sender: TObject);
begin
  if chk_ND.Checked = True then
  begin
    se_From_ND.Enabled := True;
    se_To_ND.Enabled := True;
  end
  else
  begin
    se_From_ND.Enabled := False;
    se_To_ND.Enabled := False;
  end;
end;
{票品面值}
procedure TFrm_YP_Init_Search.chk_PPMZClick(Sender: TObject);
begin
  edt_PPMZ.Enabled := chk_PPMZ.Checked;
end;
{票品售價}
procedure TFrm_YP_Init_Search.chk_PPSJClick(Sender: TObject);
begin
  edt_SJ.Enabled := chk_PPSJ.Checked;
end;
{志號}
procedure TFrm_YP_Init_Search.chk_ZHClick(Sender: TObject);
begin
  edt_ZH.Enabled := chk_ZH.Checked;
end;
{票品名稱}
procedure TFrm_YP_Init_Search.chk_MCClick(Sender: TObject);
begin
  edt_MC.Enabled := chk_MC.Checked;
end;

{===============================================================================}
procedure TFrm_YP_Init_Search.edt_PPMZKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (Key in ['0'..'9', '.', Chr(VK_BACK)]) then Key := Chr(0);
end;

{生成查詢的部分SQL}
function TFrm_YP_Init_Search.Generate_SQL_part: Boolean;
var
  l_Small_SQL: string;
begin
  {為下次進入顯示上次數據}
  VG_l_PPLB := '';
  VG_l_PPXLB := '';
  VG_l_From_ND := 0;
  VG_l_To_ND := 0;
  VG_l_PPMZ := 0;
  VG_l_PPSJ := 0;
  VG_l_ZH := '';
  VG_l_MC := '';

  Search_Caption := '顯示條件——';
  part_SQL := ' and b.TYPE=''yp''';

  {票品類別}
  if chk_PPLB.Checked = True then
    if chk_PPXLB.Checked = True then
    begin
      part_SQL := part_SQL + ' and b.PPLB=''' + fcb_PPXLB.FieldString + '''';
      Search_Caption := Search_Caption + '票品類別： ' + fcb_PPLB.text + ' - ' + fcb_PPXLB.Text + '  ';
      VG_l_PPLB := fcb_PPLB.FieldString;
      VG_l_PPXLB := fcb_PPXLB.FieldString;
    end
    else
    begin
      part_SQL := part_SQL + ' and SubStr(b.PPLB,1,1)=''' + fcb_PPLB.FieldString + '''';
      Search_Caption := Search_Caption + '票品類別： ' + fcb_PPLB.Text + '  ';
      VG_l_PPLB := fcb_PPLB.FieldString;
    end;

  {發行年度}
  if chk_ND.Checked = True then
  begin
    part_SQL := part_SQL + ' and to_char(b.FXRQ,''yyyy'')>=''' + se_From_ND.text + ''' and to_char(b.FXRQ,''yyyy'')<=''' + se_To_ND.text + '''';
    Search_Caption := Search_Caption + '發行年度： 從' + se_From_ND.text + '年到' + se_To_ND.text + '年  ';
    VG_l_From_ND := StrToInt(se_From_ND.text);
    VG_l_To_ND := StrToInt(se_To_ND.text);
  end;

  {票品面值}
  if chk_PPMZ.Checked = True then
  begin
    part_SQL := part_SQL + ' and b.TPMZ=' + FloatToStr(StrToFloat(edt_PPMZ.Text) * 100);
    Search_Caption := Search_Caption + '票品面值：' + FloatToStr(StrToFloat(edt_PPMZ.Text)) + '元  ';
    VG_l_PPMZ := StrToFloat(edt_PPMZ.Text); {單位：元}      {}
  end;

  {票品售價}
  if chk_PPSJ.Checked = True then
  begin
    part_SQL := part_SQL + ' and b.TPSJ=' + FloatToStr(StrToFloat(edt_SJ.Text) * 100);
    Search_Caption := Search_Caption + '票品售價：' + FloatToStr(StrToFloat(edt_SJ.Text)) + '元  ';
    VG_l_PPSJ := StrToFloat(edt_SJ.Text);
  end;

  {志號}
  if chk_ZH.Checked = True then
  begin
    part_SQL := part_SQL + ' and b.ZH like ''%' + Change_Comma(edt_ZH.Text) + '%''';
    Search_Caption := Search_Caption + '志號中包含：' + edt_ZH.Text + '  ';
    VG_l_ZH := edt_ZH.Text;
  end;

  {票品名稱}
  if chk_MC.Checked = True then
  begin
    part_SQL := part_SQL + ' and b.TMC like ''%' + Change_Comma(edt_MC.Text) + '%''';
    Search_Caption := Search_Caption + '票品名稱中包含：' + edt_MC.Text + '  ';
    VG_l_MC := edt_MC.Text;
  end;

  if part_SQL = ' and b.TYPE=''yp''' then
    Search_Caption := Search_Caption + '所有數據';
end;

{改變成符合SQL的語句}
function TFrm_YP_Init_Search.Change_Comma(Text: string): string;
var
  Ret_Text: string;
  i: Integer;
begin
  Ret_Text := '';
  for i := 1 to Length(Text) do
  begin
    Ret_Text := Ret_Text + Copy(Text, i, 1);
    if Copy(Text, i, 1) = '''' then
      Ret_Text := Ret_Text + '''';
  end;
  Result := Ret_Text;
end;
{-------------------------------------------------------------------------------}
{測試輸入是否合法}
function TFrm_YP_Init_Search.Test_Input: Boolean;
begin
  if chk_ND.Checked then
  begin
    try
      StrToInt(se_From_ND.Text);
    except
      CHQMsgBox('選擇了年度，請輸入合法數值！');
      se_From_ND.SetFocus;
      Result := False;
      exit;
    end;

    try
      StrToInt(se_To_ND.Text);
    except
      CHQMsgBox('選擇了年度，請輸入合法數值！');
      se_To_ND.SetFocus;
      Result := False;
      exit;
    end;
    if StrToInt(se_To_ND.Text) < StrToInt(se_From_ND.Text) then
    begin
      CHQMsgBox('所選擇的中止年度小于起始年度！您什么也查不到！');
      se_From_ND.SetFocus;
      Result := False;
      Exit;
    end;
  end;

  if chk_PPMZ.Checked then
  try
    StrToFloat(edt_PPMZ.Text);
  except
    CHQMsgBox('選擇了票品面值，請輸入合法數值！');
    edt_PPMZ.SetFocus;
    Result := False;
    exit;
  end;

  if chk_PPSJ.Checked then
  try
    StrToFloat(edt_SJ.Text);
  except
    CHQMsgBox('選擇了票品售價，請輸入合法數值！');
    edt_SJ.SetFocus;
    Result := False;
    exit;
  end;
  Result := True; {設置返回值為True}                        {}
end;

procedure TFrm_YP_Init_Search.bbtn_SearchClick(Sender: TObject);
begin
  if Test_Input = False then Exit;                          {判斷輸入是否合法}

  Generate_SQL_part; {產生SQL語句}                          {}
  Close;
  ModalResult := mrOk;
end;

procedure TFrm_YP_Init_Search.bbtn_CancelClick(Sender: TObject);
begin
  Close;
  ModalResult := mrCancel;
end;


end.

//    SQL.Text := 'Select MC,JYPLDM From TG_JYPLDMB Where LB=''大類''';
//    SQL.Text := 'Select MC,JYPLDM From TG_JYPLDMB Where LB=''小類'' and SubStr(JYPLDM,1,1)=''' + fcb_PPLB.FieldString + '''';
//      part_SQL := part_SQL + ' and SubStr(b.PPLB,1,1)=''' + fcb_PPLB.FieldString + '''';