ss_yp_init_search.pas

省级集邮品管理ERP

{*******************************************************}
{                                                       }
{                        查询选项                       }
{                                                       }
{            中软金马公司版权所有。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 + '''';