毕业

毕业
写一个公用的sql 生成 函数.
首先 了解需要的sql 的 情况 找到共性
{ *********************************************************************** }
{                                                                         }
{ 查询当前某商品或某类商品入库出库情况                                    }
{                                                                         }
{ create by:hua                                                           }
{ date:2004-12-07                                                         }
{                                                                         }
{ *********************************************************************** }
unit uStockQuery;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Mask, ToolEdit, DB, Grids, DBGrids, ADODB, OceanQuery,
  LBCtrls, LBDBScrollBar, DBCtrls, ExtCtrls, dbcgrids, cxStyles,
  cxCustomData, cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit,
  cxDBData, cxGridCustomTableView, cxGridTableView, cxGridDBTableView,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGrid,
  cxGridCardView, cxGridDBCardView, cxDropDownEdit, DFSSplitter, Buttons,
  dxPSGlbl, dxPSUtl, dxPSEngn, dxPrnPg, dxBkgnd, dxWrap, dxPrnDev,
  dxPSCompsProvider, dxPSFillPatterns, dxPSEdgePatterns, dxPSCore, XProcs,
  ComCtrls;

 

type
  TfrmStockQuery = class(TForm)
    StockQueryX: TOceanQuery;
    DataSourceX: TDataSource;
    cxStyleRepository1: TcxStyleRepository;
    cxStyle1: TcxStyle;
    cxStyle3: TcxStyle;
    cxStyle4: TcxStyle;
    Panel1: TPanel;
    dxComponentPrinter1: TdxComponentPrinter;
    Panel2: TPanel;
    Label5: TLabel;
    Label25: TLabel;
    LBSpeecButton4: TLBSpeecButton;
    Label1: TLabel;
    edtBgnRiqi: TDateEdit;
    edtEndRiqi: TDateEdit;
    Panel5: TPanel;
    Panel6: TPanel;
    cxGridX: TcxGrid;
    tvPartsX: TcxGridDBTableView;
    lvPartsX: TcxGridLevel;
    Checkdate: TCheckBox;
    RadioGroup1: TRadioGroup;
    procedure FormShow(Sender: TObject);
    procedure SelectRecord(sSQL: string; OceanQry: TOceanQuery);
    procedure Qclick(Sender: TObject);
    procedure CheckdateClick(Sender: TObject);
    function selectSqltext(iRindex:integer):string;
  private
    { Private declarations }
    FID: string;
    FPartFilter: string;
    FImageIndex: integer;
    Fname: string;
    procedure SetID(value: string);
  public
   { Public declarations }
    //property FId:string read getid write setid ;
    property ID: string read FID write SetID;
    property ImageIndex: integer read FImageIndex write FImageIndex;
    property name: string read Fname write Fname;

  end;

var
  frmStockQuery: TfrmStockQuery;

implementation

uses uStatistic, uPublicvar;

{$R *.dfm}

procedure TfrmStockQuery.SetID(value: string);
begin
  FID := Value;
  FPartFilter := GetPartFilter(FID);
end;

function TfrmStockQuery.selectSqltext(iRindex:integer):string;
var
  sTmp0,sTmp1, sTmp2,sTmp3,sTmp4, stmp: string;
  sTime,sTime2:string;
  stmpsql:string;
begin
  sTime := 'billdate between ' + QuotedStr(edtBgnRiqi.Text) + ' and ' + QuotedStr(edtEndRiqi.text) ;
  sTime2:=sTime;  // 查询库存要用时间 sTime2
  if iRindex=4  then checkdate.Checked:=true;
  if not checkdate.checked  then sTime:=' 1=1 ';

  if Fimageindex = 1 then
  begin
    sTmp0 :=' WHERE ' + sTime + ' and PARTSINPUTBODY.PARTID=' + QuotedStr(FId);
    sTmp1 :=' WHERE ' + sTime + ' and PARTSOUTPUTBODY.PARTID=' + QuotedStr(FId);
    sTmp2 :=' WHERE 1=1 ';
    sTmp3 :=' WHERE 1=1 ';
  end
  else if Fimageindex = 0 then
  begin
  stmp:='partid in (select id from partinfo where  imageindex=1 and  '+FPartFilter+')';
    sTmp0 := 'WHERE ' + sTime + 'and '+ stmp;
    sTmp1 := 'WHERE ' + sTime + 'and '+ stmp;
    sTmp2 :=' WHERE 1=1 ';
    sTmp3 :=' WHERE 1=1 ';
  end;

 

  //stmpsql:=原sql+条件sql(时间)
  case iRindex of
  0: stmpsql :='   SELECT PARTSINPUTBODY.BILLCODE as 入库单, PARTSINPUTBODY.PARTNAME as 物品名称,   ' +
    '         PARTSINPUTBODY.PARTCODE as 物品编码, PARTSINPUTHAND.PROVIDER as 供应商,   ' +
    '         PARTSINPUTHAND.BILLDATE as 入库日期, isnull(PARTSINPUTHAND.TOTALSUM,0) as 金额,PARTSINPUTBODY.QUANTITY as 数量' +
    '   FROM PARTSINPUTHAND INNER JOIN  ' +
    '         PARTSINPUTBODY ON   ' +
    '         PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE  '+sTmp0;
  1: stmpsql :='   SELECT PARTSOUTPUTHAND.BILLCODE as 出库单, PARTSOUTPUTBODY.PARTNAME as 物品名称, ' +
    '         PARTSOUTPUTBODY.PARTCODE as 物品编码,PARTSOUTPUTHAND.CLIENTNAME as 领用单位 ,   ' +
    '         PARTSOUTPUTHAND.BILLDATE as 领用日期,isnull(PARTSOUTPUTBODY.TOTALSUM,0) as 金额 ,PARTSOUTPUTBODY.QUANTITY  as 数量 ' +
    '   FROM PARTSOUTPUTBODY INNER JOIN  ' +
    '         PARTSOUTPUTHAND ON   ' +
    '         PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE  '+sTmp1;
  2: stmpsql :='  SELECT PARTSINPUTBODY.BILLCODE, PARTSINPUTBODY.PARTNAME,   ' +
    '         PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER,   ' +
    '         PARTSINPUTHAND.BILLDATE, isnull(PARTSINPUTHAND.TOTALSUM,0) ,PARTSINPUTBODY.QUANTITY ' +
    '   FROM PARTSINPUTHAND INNER JOIN  ' +
    '         PARTSINPUTBODY ON   ' +
    '         PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE  '+sTmp2;
  3: stmpsql :='   SELECT PARTSOUTPUTHAND.BILLCODE, PARTSOUTPUTHAND.CLIENTNAME,   ' +
    '         PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,   ' +
    '         PARTSOUTPUTHAND.BILLDATE, isnull(PARTSOUTPUTHAND.TOTALSUM,0) ,PARTSOUTPUTBODY.QUANTITY ' +
    '   FROM PARTSOUTPUTBODY INNER JOIN  ' +
    '         PARTSOUTPUTHAND ON   ' +
    '         PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE  '+sTmp3;
  4: stmpsql :='   SELECT a.PARTNAME as 物品名称, a.PARTCODE as 物品编码, SUM(ISNULL(a.quantity, 0)) AS 入库数量,   '+
    '                 SUM(ISNULL(b.Quantity, 0)) AS 出库数量, SUM(ISNULL(a.quantity, 0)   '+
    '                 - ISNULL(b.Quantity, 0)) AS 节余数量  '+
    '           FROM (SELECT PARTSINPUTBODY.PARTCODE,   '+
    '                         PARTSINPUTBODY.PARTNAME,   '+
    '                         SUM(PARTSINPUTBODY.QUANTITY) AS quantity  '+
    '                   FROM PARTSINPUTBODY, PARTSINPUTHAND  '+
    '                   WHERE partsinputhand.'+sTime2+'  AND   '+
    '                         PARTSINPUTBODY.BILLCODE = PARTSINPUTHAND.BILLCODE  '+
    '                   GROUP BY PARTSinPUTBODY.partcode, PARTSinPUTBODY.partname)   '+
    '                 a LEFT OUTER JOIN  '+
    '                     (SELECT PARTSOUTPUTBODY.PARTCODE,   '+
    '                          PARTSOUTPUTBODY.PARTNAME,   '+
    '                          SUM(PARTSOUTPUTBODY.QUANTITY) AS Quantity  '+
    '                    FROM PARTSOUTPUTBODY, PARTSOUTPUTHAND  '+
    '                    WHERE partsoutputhand.'+sTime2+' AND   '+
    '                          PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE  '+
    '                    GROUP BY PARTSOUTPUTBODY.partcode, PARTSOUTPUTBODY.partname)   '+
    '                 b ON a.PARTCODE = b.PARTCODE  '+
    '           GROUP BY a.PARTNAME, a.PARTCODE  ' ;

else
  stmpsql := 'select * from partinfo';
end;
result:=stmpsql;

end;
procedure TfrmStockQuery.Qclick(Sender: TObject);
var
  s: string;
begin
  Label1.Caption := '当前商品:' + Fname;
  panel6.Caption :=RadioGroup1.Items.strings[RadioGroup1.ItemIndex] ;

  s:=selectsqltext(RadioGroup1.ItemIndex);
  SelectRecord(s, StockQueryX); //执行查询
end;

procedure TfrmStockQuery.SelectRecord(sSQL: string; OceanQry: TOceanQuery);
var
   i,ii:integer;
begin
  try
    with OceanQry do
    begin
      close;
      sql.Clear;
      sql.Add(sSql);
      Prepared := true;
      open;
      ii:=FieldCount ;
    end;
      tvPartsX.ClearItems ; //重建 CXGRID里面的列
      tvPartsX.DataController.CreateAllItems;
      for i := 0 to ii-1 do
         tvPartsX.Columns[i].Width :=100;
      next;
  except
  end;
end;

procedure TfrmStockQuery.FormShow(Sender: TObject);
var
  lYear, lMonth, lDay: Word;
begin
  Decodedate(Date, lYear, lMonth, lDay);
  edtBgnRiqi.Date := EncodeDate(lYear, lMonth, 1);
  edtEndRiqi.Date := DateEndOfMonth(edtBgnRiqi.Date);
  Qclick(sender);
end;

procedure TfrmStockQuery.CheckdateClick(Sender: TObject);
begin
edtBgnRiqi.Enabled:=checkdate.Checked ;
edtEndRiqi.Enabled:=checkdate.Checked ;
end;

end.