untrecievefdbill.~pas

是分布式粮库程序,是采用Delphi实现的

      showmessage('请选择库点！');
      DBCmbxSite.SetFocus;
    end;
  if DBEdtDptId.Field.AsInteger<0 then
    begin
      showmessage('请选择库房！');
      DBCmbxDepot.SetFocus;
    end;
end;

procedure TFrmRecieveFdBill.BitBtnAllClick(Sender: TObject);
var
  SQLOrd,sOrdFld1,sOrdFld2,sOrdFld3:string;
  SQLSlt: string;
begin
  //以下取得SQLOrd
  if CmBxSrtFld1.Itemindex=-1 then
    sOrdFld1 := ''
  else
    begin
    if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='unit_name' then
      sOrdFld1 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='site_name' then
        sOrdFld1 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='depot_name' then
          sOrdFld1 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='name' then
            sOrdFld1 := 'ent_fdposition_info.name'
          else
            sOrdFld1 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName;
  end;

  if CmBxSrtFld2.Itemindex=-1 then
    sOrdFld2 := ''
  else
  begin
    if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='unit_name' then
      sOrdFld2 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='site_name' then
        sOrdFld2 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='depot_name' then
          sOrdFld2 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='name' then
            sOrdFld2 := 'ent_fdposition_info.name'
          else
            sOrdFld2 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName;
  end;

  if CmBxSrtFld3.Itemindex=-1 then
    sOrdFld3 := ''
  else
  begin
    if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='unit_name' then
      sOrdFld3 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='site_name' then
        sOrdFld3 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='depot_name' then
          sOrdFld3 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='name' then
            sOrdFld3 := 'ent_fdposition_info.name'
          else
            sOrdFld3 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName;
  end;

  if sOrdFld1<>'' then
    begin
      if RadioGroup1.ItemIndex=1 then
        SQLOrd:= ' order by '+ sOrdFld1 + ' desc'
      else
        SQLOrd:= ' order by '+ sOrdFld1 ;
    end;
  if (sOrdFld2<>'') and (sOrdFld1<>'') then
    begin
      if RadioGroup2.ItemIndex=1 then
        SQLOrd:= SQLOrd + ', ' + sOrdFld2 + ' desc'
      else
        SQLOrd:=  SQLOrd + ', ' + sOrdFld2 ;
    end;
   if (sOrdFld2<>'') and (sOrdFld1<>'') and (sOrdFld3<>'') then
    begin
      if RadioGroup2.ItemIndex=1 then
        SQLOrd := SQLOrd + ', ' + sOrdFld3 + ' desc'
      else
        SQLOrd := SQLOrd + ', ' + sOrdFld3 ;
    end;

    SQLSlt := 'select INSTACK_ID,CASE_ID,ADJUST_ID,NOTY_ITEM_ID,IN_FOOD,STORAGE,'+
            'R_NEW,R_OLD,R_MISC,R_TORN,R_KNIT,ATTACHMENT,ATTRIBUTE,'+
            'FQ_CLASS,FQ_MOISTURE,FQ_IMPURITY,FQ_FAT,FQ_ITEM1,FQ_ITEM2,FQ_ITEM3,'+
            'FQ_ITEM4,FQ_ITEM5,REAP_YEAR,PROD_AREA,FROM_STATION,FREIGHT_NUMBER,'+
            'TO_STATION,FREIGHT,ARRIVAL_TIME,NOTY_FOOD,NOTY_QUANTITY,IN_TIME,'+
            'IN_QUANTITY,INTYPE,PRICE,NEW,OLD,MISC,TORN,KNIT,SUPPLYER,CLERK,'+
            'MANAGER,adjustor,ENT_INSTACK.MEMO,ENT_INSTACK.COM_FLAG,SURPLUS,ENT_INSTACK.Unit_id,'+
            'ENT_INSTACK.site_id,ENT_INSTACK.depot_id,ENT_INSTACK.FOODPOSITION_ID,'+
            'ent_fdposition_info.name,ent_depot_info.depot_name,ent_site.site_name,'+
            'ent_unit_info.unit_name '+
         'from ent_instack,ent_fdposition_info,ent_depot_info,ent_site,ent_unit_info '+
         'where ( ent_instack.FOODPOSITION_ID*=ent_fdposition_info.FOODPOSITION_ID '+
                'and ent_instack.depot_id*=ent_fdposition_info.depot_id '+
                'and ent_instack.site_id*=ent_fdposition_info.site_id '+
                'and ent_instack.unit_id*=ent_fdposition_info.unit_id ) and '+
                '( ent_instack.depot_id*=ent_depot_info.depot_id '+
                'and ent_instack.site_id*=ent_depot_info.site_id '+
                'and ent_instack.unit_id*=ent_depot_info.unit_id ) and '+
                '( ent_instack.site_id*=ent_site.site_id '+
                'and ent_instack.unit_id*=ent_site.unit_id ) '+
                'and ent_instack.unit_id*=ent_unit_info.unit_id '+
                'and INTYPE<>'''+'内移收入''';

  if DBGrid1.DataSource = DataSource1 then
  begin
    QrySQL.Close;
    QrySQL.SQL.Clear ;
    QrySQL.SQL.Add(SQLSlt + SQLOrd);
    QrySQL.Open;
  end;
end;

procedure TFrmRecieveFdBill.BitBtnOKClick(Sender: TObject);
var
  SQLSlt,SQLWhr,SQLOrd,SQL1,SQL2,SQL3 : string;
  //i: integer;
  sFld1, sFld2, sFld3,InisFld1, InisFld2, InisFld3 : string;
  sOpr1, sOpr2, sOpr3 : string;
  sVal1, sVal2, sVal3 : string;
  sLog1, sLog2 : string;
  sOrdFld1,sOrdFld2,sOrdFld3: string;
begin
  //inherited;
  SQLSlt := 'select INSTACK_ID,CASE_ID,ADJUST_ID,NOTY_ITEM_ID,IN_FOOD,STORAGE,'+
            'R_NEW,R_OLD,R_MISC,R_TORN,R_KNIT,ATTACHMENT,ATTRIBUTE,'+
            'FQ_CLASS,FQ_MOISTURE,FQ_IMPURITY,FQ_FAT,FQ_ITEM1,FQ_ITEM2,FQ_ITEM3,'+
            'FQ_ITEM4,FQ_ITEM5,REAP_YEAR,PROD_AREA,FROM_STATION,FREIGHT_NUMBER,'+
            'TO_STATION,FREIGHT,ARRIVAL_TIME,NOTY_FOOD,NOTY_QUANTITY,IN_TIME,'+
            'IN_QUANTITY,INTYPE,PRICE,NEW,OLD,MISC,TORN,KNIT,SUPPLYER,CLERK,'+
            'MANAGER,adjustor,ENT_INSTACK.MEMO,ENT_INSTACK.COM_FLAG,SURPLUS,ENT_INSTACK.Unit_id,'+
            'ENT_INSTACK.site_id,ENT_INSTACK.depot_id,ENT_INSTACK.FOODPOSITION_ID,'+
            'ent_fdposition_info.name,ent_depot_info.depot_name,ent_site.site_name,'+
            'ent_unit_info.unit_name '+
         'from ent_instack,ent_fdposition_info,ent_depot_info,ent_site,ent_unit_info '+
         'where ( ent_instack.FOODPOSITION_ID*=ent_fdposition_info.FOODPOSITION_ID '+
                'and ent_instack.depot_id*=ent_fdposition_info.depot_id '+
                'and ent_instack.site_id*=ent_fdposition_info.site_id '+
                'and ent_instack.unit_id*=ent_fdposition_info.unit_id ) and '+
                '( ent_instack.depot_id*=ent_depot_info.depot_id '+
                'and ent_instack.site_id*=ent_depot_info.site_id '+
                'and ent_instack.unit_id*=ent_depot_info.unit_id ) and '+
                '( ent_instack.site_id*=ent_site.site_id '+
                'and ent_instack.unit_id*=ent_site.unit_id ) '+
                'and ent_instack.unit_id*=ent_unit_info.unit_id '+
                'and INTYPE<>'''+'内移收入''';

  { where 子句程序 }
  SQLWhr := '';
  SQLOrd := '';
  if CboxField1.Itemindex=-1 then
    sFld1:=''
  else
  begin
    InisFld1 := Dbgrid1.Columns[CboxField1.Itemindex].FieldName;
    if Dbgrid1.Columns[CboxField1.Itemindex].FieldName= 'unit_name' then
      sFld1 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CboxField1.Itemindex].FieldName= 'site_name' then
        sFld1 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CboxField1.Itemindex].FieldName= 'depot_name' then
          sFld1 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CboxField1.Itemindex].FieldName= 'name' then
            sFld1 := 'ent_fdposition_info.name'
          else
            sFld1 := 'ent_instack.'+Dbgrid1.Columns[CboxField1.Itemindex].FieldName;
  end;

  if CboxField2.Itemindex=-1 then
    sFld2:=''
  else
  begin
    InisFld2 := Dbgrid1.Columns[CboxField2.Itemindex].FieldName;
    if Dbgrid1.Columns[CboxField2.Itemindex].FieldName= 'unit_name' then
      sFld2 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CboxField2.Itemindex].FieldName= 'site_name' then
        sFld2 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CboxField2.Itemindex].FieldName= 'depot_name' then
          sFld2 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CboxField2.Itemindex].FieldName= 'name' then
            sFld2 := 'ent_fdposition_info.name'
          else
            sFld2 := 'ent_instack.'+Dbgrid1.Columns[CboxField2.Itemindex].FieldName;
  end;

  if CboxField3.Itemindex=-1 then
    sFld3:=''
  else
  begin
    InisFld3 := Dbgrid1.Columns[CboxField3.Itemindex].FieldName;
    if Dbgrid1.Columns[CboxField3.Itemindex].FieldName= 'unit_name' then
      sFld3 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CboxField3.Itemindex].FieldName= 'site_name' then
        sFld3 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CboxField3.Itemindex].FieldName= 'depot_name' then
          sFld3 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CboxField3.Itemindex].FieldName= 'name' then
            sFld3 := 'ent_fdposition_info.name'
          else
            sFld3 := 'ent_instack.'+Dbgrid1.Columns[CboxField3.Itemindex].FieldName;
  end;

  sOpr1 := CboxOperator1.Items[CboxOperator1.ItemIndex];
  sOpr2 := CboxOperator2.Items[CboxOperator2.ItemIndex];
  sOpr3 := CboxOperator3.Items[CboxOperator3.ItemIndex];
  sVal1 := EdtFieldValue1.Text;
  sVal2 := EdtFieldValue2.Text;
  sVal3 := EdtFieldValue3.Text;
  sLog1 := CboxLogic1.Items[CboxLogic1.ItemIndex];
  sLog2 := CboxLogic2.Items[CboxLogic2.ItemIndex];

  //一下取得SQLWhr
  if ( InisFld1 <> '' ) and ( sOpr1 <> '' ) then
  begin
    case DBGrid1.DataSource.DataSet.FieldByName(InisFld1).DataType of
      ftString: begin
                  if sOpr1='like' then sVal1:='%'+sVal1+'%';
                  SQL1:=format('  %s %s ''%s''',[ sFld1, sOpr1, sVal1]);
                end;
      ftInteger: begin
                   try
                     StrToInt(sVal1);
                   except
                     showmessage('第一个字段（列）值为无效的整型，请重新输入！');
                     exit;
                   end;
                   SQL1:=format('  %s %s %d', [ sFld1, sOpr1, StrToInt(sVal1)]);
                 end;

      ftFloat:   begin
                   try
                     StrToFloat(sVal1);
                   except
                     showmessage('第一个字段（列）值为无效的浮点数值类型，请重新输入！');
                     exit;
                   end;
                   SQL1:=format('  %s %s %f', [ sFld1, sOpr1, StrToFloat(sVal1)]);
                 end;
      ftDateTime:begin
                   try
                     StrToDate(sVal1);
                   except
                     showmessage('第一个字段（列）值为无效的日期类型，有效的日期类型为：YY-MM-DD！请重新输入！');
                     exit;
                   end;
                   SQL1:=format('  %s %s ''%s''', [ sFld1, sOpr1, sVal1]);
                 end;
    else
      begin
        showmessage('要查询的字段:'+sFld1+'的类型还未定义！');
        exit;
      end;
    end;
    SQLWhr := SQLWhr + SQL1;
  end;
  if ( sLog1 <> '' ) and ( InisFld2 <> '' ) and ( sOpr2 <> '' ) then
  begin
    case DBGrid1.DataSource.DataSet.FieldByName(InisFld2).DataType of
      ftString: begin
                 if sOpr2='like' then sVal2:='%'+sVal2+'%';
                 SQL2:=format(' %s  %s %s ''%s'' ', [sLog1, sFld2, sOpr2, sVal2]);
                end;
      ftInteger: begin
                   try
                     StrToInt(sVal2);
                   except
                     showmessage('第二个字段（列）值为无效的整型，请重新输入！');
                     exit;
                   end;
                   SQL2:=format(' %s  %s %s %d ', [sLog1 , sFld2, sOpr2, StrToInt(sVal2)]);
                 end;
      ftFloat:   begin
                   try
                     StrToFloat(sVal2);
                   except
                     showmessage('第二个字段（列）值为无效的浮点数值类型，请重新输入！');
                     exit;
                   end;
                   SQL2:=format(' %s  %s %s %f ', [sLog1 , sFld2, sOpr2, StrToFloat(sVal2)]);
                 end;
      ftDateTime:begin
                   try
                     StrToDate(sVal2);
                   except
                     showmessage('第二个字段（列）值为无效的日期类型，有效的日期类型为：YY-MM-DD！请重新输入！');
                     exit;
                   end;
                   SQL2:=format(' %s  %s %s ''%s'' ', [sLog1, sFld2, sOpr2, sVal2]);
                 end;
    else
      begin
        showmessage('要查询的字段:'+sFld2+'的类型还未定义！');
        exit;
      end;
    end;
    SQLWhr := SQLWhr + SQL2;
  end;
  if ( sLog2 <> '' ) and ( InisFld3 <> '' ) and ( sOpr3 <> '' ) then
  begin
    case  DBGrid1.DataSource.DataSet.FieldByName(InisFld3).DataType of
      ftString: begin
                  if sOpr3='like' then sVal3:='%'+sVal3+'%';
                  SQL3:=format(' %s  %s %s ''%s'' ', [ sLog2, sFld3, sOpr3, sVal3]);
                end;
      ftInteger: begin
                   try
                     StrToInt(sVal3);
                   except
                     showmessage('第三个字段（列）值为无效的整型，请重新输入！');
                     exit;
                   end;
                   SQL3:=format(' %s  %s %s %d ', [ sLog2, sFld3, sOpr3, StrToInt(sVal3)]);
                 end;
      ftFloat:   begin
                   try
                     StrToFloat(sVal2);
                   except
                     showmessage('第三个字段（列）值为无效的浮点数值类型，请重新输入！');
                     exit;
                   end;
                   SQL3:=format(' %s  %s %s %f ', [ sLog2, sFld3, sOpr3, StrToFloat(sVal3)]);
                 end;
      
      ftDateTime:begin
                   try
                     StrToDate(sVal2);
                   except
                     showmessage('第三个字段（列）值为无效的日期类型，有效的日期类型为：YY-MM-DD！请重新输入！');
                     exit;
                   end;
                   SQL3:=format(' %s  %s %s ''%s'' ', [sLog2, sFld3, sOpr3, sVal3]);
                 end;
    else
      begin
        showmessage('要查询的字段:'+sFld3+'的类型还未定义！');
        exit;
      end;
    end;
    SQLWhr := SQLWhr + SQL3;
  end;

  if (SQLWhr<>'')  then
    SQLWhr := ' and '+ SQLWhr ;

  //以下取得SQLOrd
  if CmBxSrtFld1.Itemindex=-1 then
    sOrdFld1 := ''
  else
  begin
    if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='unit_name' then
      sOrdFld1 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='site_name' then
        sOrdFld1 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='depot_name' then
          sOrdFld1 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName='name' then
            sOrdFld1 := 'ent_fdposition_info.name'
          else
            sOrdFld1 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld1.Itemindex].FieldName;
  end;

  if CmBxSrtFld2.Itemindex=-1 then
    sOrdFld2 := ''
  else
  begin
    if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='unit_name' then
      sOrdFld2 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='site_name' then
        sOrdFld2 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='depot_name' then
          sOrdFld2 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName='name' then
            sOrdFld2 := 'ent_fdposition_info.name'
          else
            sOrdFld2 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld2.Itemindex].FieldName;
  end;

  if CmBxSrtFld3.Itemindex=-1 then
    sOrdFld3 := ''
  else
  begin
    if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='unit_name' then
      sOrdFld3 := 'ent_unit_info.unit_name'
    else
      if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='site_name' then
        sOrdFld3 := 'ent_site.site_name'
      else
        if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='depot_name' then
          sOrdFld3 := 'ent_depot_info.depot_name'
        else
          if Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName='name' then
            sOrdFld3 := 'ent_fdposition_info.name'
          else
            sOrdFld3 := 'ent_instack.'+Dbgrid1.Columns[CmBxSrtFld3.Itemindex].FieldName;
  end;

  if sOrdFld1 <> '' then
    begin
      if RadioGroup1.ItemIndex=1 then
        SQLOrd:= ' order by '+ sOrdFld1 + ' desc'
      else
        SQLOrd:= ' order by '+ sOrdFld1 ;
    end;
  if (sOrdFld2<>'') and (sOrdFld1 <>'') then
    begin
      if RadioGroup2.ItemIndex=1 then
        SQLOrd:= SQLOrd + ', ' + sOrdFld2 + ' desc'
      else
        SQLOrd:=  SQLOrd + ', ' + sOrdFld2 ;
    end;
   if (sOrdFld2<>'') and (sOrdFld1<>'') and (sOrdFld3<>'') then
    begin
      if RadioGroup2.ItemIndex=1 then
        SQLOrd:= SQLOrd + ', ' + sOrdFld3 + ' desc'
      else
        SQLOrd:=  SQLOrd + ', ' + sOrdFld3 ;
    end;
  StrSQL := SQLSlt + SQLWhr + SQLOrd;

  if DBGrid1.DataSource = DataSource1 then
  begin
    QrySQL.Close;
    QrySQL.SQL.Clear ;
    QrySQL.SQL.Add (SQLSlt + SQLWhr + SQLOrd );
    QrySQL.Open;
  end;

end;

end.