毕业

毕业
写一个公用的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.

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值