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