search.pas

飞恒进销存(超市批发)管理系统V5.1（含源程序） 语言：Delphi 6/7 相关控件：FastReport 2.4以上, Ehlib 3.4以上 1.数据库为fhe2db_V51.da

unit search;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, StdCtrls, Buttons, Grids, DBGridEh, ComCtrls, Mask, DBCtrlsEh,
  DBCtrls, DBLookupEh, FR_Ctrls, DBGrids;

type
  TschConditionForm = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    cmdOk: TBitBtn;
    cmdReset: TBitBtn;
    cmdExit: TBitBtn;
    GrBobusin: TGroupBox;
    schGrid: TDBGridEh;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    cmdAdd: TBitBtn;
    cmdDel: TBitBtn;
    cboCompare: TComboBox;
    edtValue: TEdit;
    edtBegindate: TDateTimePicker;
    lblBegindate: TLabel;
    lblEndDate: TLabel;
    edtEnddate: TDateTimePicker;
    cboFields: TDBLookupComboBox;
    edtSelValue: TfrComboEdit;
    edtTreeSelValue: TDBEditEh;
    procedure cmdExitClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure cboFieldsExit(Sender: TObject);
    procedure cmdAddClick(Sender: TObject);
    procedure cmdDelClick(Sender: TObject);
    procedure cmdResetClick(Sender: TObject);
    procedure cmdOkClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure edtSelValueButtonClick(Sender: TObject);
    procedure edtTreeSelValueEditButtons0Click(Sender: TObject;
      var Handled: Boolean);
  private
    { Private declarations }
    nDType,nRtype,nTmpId:integer;
    FTableName,FSql:string;
    FQueryCode:string;
    FCondition:string;
    sJoin:string;
    procedure ShowDateValue(Dtype:integer);
    Function  ProActiveSql:string;
    Function  ProFieldStr(DType:integer;sOperator,sField,sValue,sValue1:string):string;
    Function  ProJoinStr(sTablename,sAliasName,sJoinName:string):string;
  public
    { Public declarations }
    property  xTablename:string read FTableName write FTableName;
    property  xSql:string read FSql write FSql;
    property  xCondition:string read FCondition write FCondition;
    property  xQueryCode:string read FQueryCode write FQuerycode;
  end;

var
  schConditionForm: TschConditionForm;

implementation
uses datamodule1, selPaytype, selVC, TreeGoodsType, TreeIEType;
{$R *.DFM}

procedure  TschConditionForm.ShowDateValue(Dtype:integer);
begin
  lblBegindate.visible:= Dtype=3;
  lblEnddate.visible:= Dtype=3;
  edtBegindate.visible:= Dtype=3;
  edtEnddate.visible:= Dtype=3;

  edtSelValue.Visible := Dtype=6;//选择供应商，业务员
  edtTreeSelValue.Visible := (Dtype=7) or (Dtype=8);//选择货品类别
  edtValue.Visible := not ((Dtype=3) or (Dtype=6) or (Dtype=7) or (Dtype=8));
end;

procedure TschConditionForm.cmdExitClick(Sender: TObject);
begin
  with dataE2 do
  begin
    querySum.Close;
    queryCon.Close;
  end;

  ModalResult := mrCancel;
end;

procedure TschConditionForm.FormShow(Sender: TObject);
var
  i:integer;
begin
  cboCompare.ItemIndex:=0;
  edtBegindate.Date:=dataE2.GetLastMonth(date);
  edtEnddate.Date:=date;

  with datae2 do
  begin
   try
    queryDlg.Close;

    QuerySum.close;
    QuerySum.sql.Clear;
    QuerySum.sql.Text:='select * from QueryFields where QueryCode like :QueryCode';
    QuerySum.Parameters[0].Value:=xQueryCode;//'008';//SearchCode;goods
    QuerySum.open;
{   cboFields.Items.Clear;
    i:=1;
    while not eof do
    begin
      cboFields.Items.Add(FieldByName('cName').AsString);
      aValueType[i]:=FieldByName('DType').Asinteger;
      next;
      inc(i);
    end;}
    QuerySum.first;

    //为了清空cbofields中的text,用cfg.billtail为空来实现
    QueryTmp.close;
    QueryTmp.sql.Clear;
    QueryTmp.sql.Text:='select billTail from cfg';
    QueryTmp.open;
   except
    ShowAdoError;
   end
  end;

  cboFields.ListSource:=DataE2.dsSum;
  cboFields.dataSource:=DataE2.dsTmp;
  cboFields.SetFocus ;
  ShowDateValue(1);
end;

procedure TschConditionForm.cboFieldsExit(Sender: TObject);
begin
  nDType:=dataE2.QuerySum.FieldByName('dType').asinteger;
  nRType:=dataE2.QuerySum.FieldByName('RType').asinteger;
  case  nDType of
    1,4..5,7,8: begin
         cboCompare.Items.clear;
         cboCompare.items.add('包含');
         cboCompare.items.add('等于');
         ShowDateValue(nDType);
       end;
    2: begin
         cboCompare.Items.clear;
         cboCompare.items.add('大于等于');
         cboCompare.items.add('等于');
         cboCompare.items.add('小于');
         ShowDateValue(nDType);
       end;
    3: begin
         cboCompare.Items.clear;
         cboCompare.items.add('属于');
         ShowDateValue(nDType);
       end;
    6: begin
         cboCompare.Items.clear;
         cboCompare.items.add('等于');
         ShowDateValue(nDtype);
       end;
   end; //case
   cbocompare.ItemIndex :=0;
end;

procedure TschConditionForm.cmdAddClick(Sender: TObject);
var
  i:integer;
begin
  if edtTreeSelValue.Visible then
    if edtTreeSelValue.Text='' then
    begin
      Application.MessageBox('请输入条件值！','提示',64);
      exit;
    end;
  if edtValue.Visible then
    if edtValue.Text='' then
    begin
      Application.MessageBox('请输入条件值！','提示',64);
      exit;
    end
    else if cboFields.Text='金额' then
      for i := 1 To Length(edtValue.Text) do
        if not (edtValue.Text[i] in['0'..'9','-','.']) then
        begin
          Application.MessageBox('你请输入的不是数值，请输入数值！','提示',64);
          exit;
        end;

  with dataE2 do
  begin
    if not queryCon.active then queryCon.open;

    QueryCon.Append;
    queryCon.fieldbyname('EName').asstring:=querySum.fieldbyname('eName').asstring;
    queryCon.fieldbyname('cName').asstring:=querySum.fieldbyname('cName').asstring;
    queryCon.fieldbyname('operator').asstring:=cboCompare.text;
    queryCon.fieldbyname('TableName').asstring:=querySum.fieldbyname('TableName').asstring;
    queryCon.fieldbyname('AliasName').asstring:=querySum.fieldbyname('AliasName').asstring;
    queryCon.fieldbyname('JoinName').asstring:=querySum.fieldbyname('JoinName').asstring;
    queryCon.fieldbyname('DType').asInteger:=querySum.fieldbyname('DType').asinteger;
    if nDtype=3 then  //日期范围
    begin
      queryCon.fieldbyname('cValue1').asstring:=DateTostr(edtBeginDate.date);
      queryCon.fieldbyname('cValue2').asstring:=DateTostr(edtEndDate.date);//+' 23:59';
      queryCon.fieldbyname('cValue').asstring:=DateTostr(edtBeginDate.date)+'至'+DateTostr(edtEndDate.date) end
    else if nDtype=6 then
    begin
      queryCon.fieldbyname('cValue1').asstring:=intTostr(nTmpId);
      queryCon.fieldbyname('cValue').asstring:=edtSelValue.text; end
    else if (nDtype=7) or (nDtype=8) then
    begin
      queryCon.fieldbyname('cValue1').asstring:=edtTreeSelValue.text;
      queryCon.fieldbyname('cValue').asstring:=edtTreeSelValue.text;
    end else
    begin
      queryCon.fieldbyname('cValue1').asstring:=edtValue.text;
      queryCon.fieldbyname('cValue').asstring:=edtValue.text;
    end;
    queryCon.post;
  end;

  if not cmdOk.Enabled then
  begin
    cmdOk.Enabled :=true;
    cmdDel.enabled:=true;
  end;
end;

procedure TschConditionForm.cmdDelClick(Sender: TObject);
begin
  with dataE2 do
  begin
    if not queryCon.eof then
      queryCon.Delete;

    if querycon.eof then
    begin
      cmdOk.Enabled :=false;
      cmdDel.enabled:=false;
    end;
  end;
end;

procedure TschConditionForm.cmdResetClick(Sender: TObject);
begin
  with dataE2 do
    queryCon.close;
    
  cmdOk.Enabled :=false;
  cmdDel.enabled:=false;
end;

//                             参数类型      操作符    字段   值1，2
Function  TschConditionForm.ProFieldStr(DType:integer;sOperator,sField,sValue,sValue1:string):string;
var
  sTmp:string;
begin
  sTmp:='';
  case Dtype of
    1,4..5 :
        if sOperator='等于' then  sTmp:=sField+' = '''+sValue+''''
        else sTmp:=sField+' like '''+'%'+sValue+'%''';
    7..8:  if sOperator='等于' then  sTmp:=sField+' = '''+sValue+''''
        else sTmp:=sField+' like '''+sValue+'%''';
    2:  if sOperator='大于等于' then sTmp:=sField+' >= '+sValue+ '   '
        else if sOperator='等于' then sTmp:=sField+' = '+sValue+ '   '
        else sTmp:=sField+' < '+sValue+'   ';
    6:  sTmp:=sField+' = '+sValue+ '   ';
    3:  sTmp:=sField+' between '''+sValue +'''   and   '''+ sValue1+''''; //and 前后有三个空格，方便反推
  end;
  result:=sTmp;
end;

//                             参数类型      操作符    字段   值1，2
Function  TschConditionForm.ProJoinStr(sTablename,sAliasName,sJoinName:string):string;
var
  sTmp:string;
begin
  sTmp:='';
  //未有连接
  //m is master table's aliasname
  if Pos(sJoinName,sJoin) < 1 then
    sTmp:=' Left Outer join '+sTableName+' '+sAliasName+' on m.'+sJoinName+'='+sAliasName+'.'+sJoinName;
  result:=sTmp;
end;

Function TschConditionForm.ProActiveSql:string;
var
  sSql,sTmp,sValue1,sValue2,sOperator:string;
begin
  with dataE2.QueryCon do
  begin
    First ;
    sSql:='';
    sJoin:='';
    Fcondition:='';
    while not eof do
    begin
      //生成条件，用于显示
      FCondition:=FCondition+' '+trim(FieldByName('cName').asstring)+' '+trim(FieldByName('Operator').asstring)+' '+trim(FieldByName('cValue').asstring);
      //产生条件,where
      sValue1:=trim(FieldByName('cValue1').asstring);
      sValue2:=trim(FieldByName('cValue2').asstring);

      sTmp:=ProFieldStr(FieldByName('dtype').asinteger,FieldByName('Operator').asstring,FieldByName('eName').asstring,sValue1,sValue2);
      //别名为空，不记入
      if FieldByName('AliasName').asstring<>'' then
        sTmp:=FieldByName('AliasName').asstring+'.'+stmp;

      if sSql<> '' then sSql:=sSql+' and ';
      sSql:=sSql+sTmp;

      //产生Join 连接,以join字段不空为准
      if trim(FieldByName('JoinName').asstring)<>'' then
      begin
        sTmp:=ProJoinStr(FieldByName('TableName').asstring,FieldByName('AliasName').asstring,FieldByName('JoinName').asstring);
        sJoin:=sJoin+sTmp;
      end;

      next;
    end;
    //if sJoin<>'' then sJoin:=sJoin+' where ';
  end;
  //result:='select * from '+xTableName+' where '+sSql;
  result:=sJoin+' where ' +sSql;
end;

procedure TschConditionForm.cmdOkClick(Sender: TObject);
begin
  if ( dataE2.hotelspe > 0)  and (DataE2.installdate > now) then  //security use
  with dataE2 do
  begin
    querySum.close;

    //get sql ,from querycon
    FSql:=ProActiveSql;

    queryCon.Close;
  end else close;
  
end;

procedure TschConditionForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
var
  shift:TShiftState;
  k:word;
begin
  cboFields.ListSource:=nil;
  cboFields.DataSource :=nil;
  dataE2.QueryTmp.Close;
  cmdDel.Enabled :=false;
  cmdOk.Enabled :=false;

//  k:=VK_DELETE;
//  cboFieldsKeyDown(cboFields,K,[]);
end;

procedure TschConditionForm.edtSelValueButtonClick(Sender: TObject);
var
  s:string;
  sSql,sParm1,sParm2:string;
begin
  case nRtype of
    11 :begin
          sSql:='select top 200 ClientId as id,ClientNo as Code,ShortName as name from client ';
          sParm1 :='where ClientNo like :ClientNo';
          sParm2 :='where shortname like :shortname';
          dlgSelVC.NewOrderSql:=' Order by ClientNo';
          dlgSelVC.tvType.Visible:=True;
        end;
    12 :begin
          sSql:='select top 200 vendorId as id,vendorNo as Code,ShortName as name from vendor ';
          sParm1 :='where vendorNo like :vendorNo';
          sParm2 :='where shortname like :shortname';
          dlgSelVC.NewOrderSql:=' Order by vendorNo';
          dlgSelVC.tvType.Visible:=True;
        end;
    13 : s:='select StoreId as id,name from  store ';
    14 : s:='select EmployId as id,name from employ ';
  end;

  if (nRtype=13) or (nRtype=14) then
  begin
    dlgSelPaytype.newSql:=s;
    dlgSelpaytype.Top :=self.top+(sender as TComboBox).Top +50;//toolbar1.height+edtInstore.Height+20 ;
    dlgSelpaytype.Left :=self.Left+(sender as TComboBox).left+8 ;
    if dlgselPayType.showmodal=mrok then
    with dataE2 do
    begin
      (sender as TComboBox).Text :=dlgselPayType.xName;
       nTmpId:=dlgSelPaytype.xid;
    end;
  end;
  if (nRtype=11) or (nRtype=12) then
  begin
    dlgSelVC.Newsql:=sSql;
    dlgSelVC.sParams1 :=sParm1;
    dlgSelVC.sParams2 :=sParm2;
    if dlgSelVC.showmodal=mrok then
    with dataE2 do
    begin
      (sender as TComboBox).Text :=dlgSelVC.xName;
      nTmpId:=dlgSelVC.xid;
    end;
  end;
end;

procedure TschConditionForm.edtTreeSelValueEditButtons0Click(
  Sender: TObject; var Handled: Boolean);
begin
  if nDType=7 then
  begin
    TreeGoodsTypeFrm.NewSQL1:='select * from goodsType order by Type';
    TreeGoodsTypeFrm.NewCaption:='货品类别';
    TreeGoodsTypeFrm.NewSQL2:='select * from goodstype1 where type=:Type order by Type,code1';
    TreeGoodsTypeFrm.NewSQL3:='select * from goodstype2 where type=:Type and code1=:code1 order by Type,code1,code2';
    if TreeGoodsTypeFrm.showmodal=mrok then
    with dataE2 do
    begin
      edtTreeSelValue.Text:=TreeGoodsTypeFrm.xName;
    end;
  end;
  if nDType=8 then
  begin
    if TreeIETypefrm.showmodal=mrok then
      edtTreeSelValue.Text:=TreeIETypefrm.xCode
  end;
end;

end.