Graduation writes a public SQL generating function. First understand the case of SQL needs to find commonality {**************************************** **********************************************************************************************************} {} {query the current item or a certain Class merchandise storage warehouse} {} {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
Uption uStatistic, UPUBLICVAR;
{$ R * .dfm}
Procedure tfrmStockQuery.setId (value: string); begin fid: = value; fpartfilter: = getPartfilter (FID);
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; // Query Stock // CheckdEx = 4 THEN CHECKDATE.CHECKED: = true; if not checkdate.checked the station: = '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: = original SQL sql conditions (time) case iRindex of 0: stmpsql: = 'SELECT PARTSINPUTBODY.BILLCODE as Receipt, PARTSINPUTBODY.PARTNAME as item name,' 'PARTSINPUTBODY.PARTCODE as EAN, PARTSINPUTHAND.PROVIDER as suppliers, ' ' PARTSINPUTHAND.BILLDATE as storage date, isnull (PARTSINPUTHAND.TOTALSUM, 0) as the amount, PARTSINPUTBODY.QUANTITY number as ' ' FROM PARTSINPUTHAND INNER JOIN ' ' PARTSINPUTBODY ON ' ' PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE ' sTmp0; 1: stmpsql: =' SELECT PARTSOUTPUTHAND.BILLCODE as storehouse, PARTSOUTPUTBODY.PARTNAME as item name, ' ' PARTSOUTPUTBODY.PARTCODE as EAN, PARTSOUTPUTHAND.CLIENTNAME as recipients unit, ' ' PARTSOUTPUTHAND.BILLDATE as recipients date, isnull (PARTSOUTPUTBODY.TOTALSUM, 0) as the amount, PARTSOUTPUTBODY.QUANTITY number 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 item name, A.Partcode AS Item Coding, SUM (Isnull (isnull (a.quantity, 0)) AS Intrinsically,' 'Sum (ISNULL (B.quantity , 0)) AS Outlet Quantity, SUM (Isnull (A.quantity, 0) ' ' - Isnull (B.quantity, 0)) AS residual number ' ' from (Select PartsInputBody.Partcode, ' ' PartsInputBody. pARTNAME, ' ' SUM (PARTSINPUTBODY.QUANTITY) AS quantity ' ' FROM PARTSINPUTBODY, PARTSINPUTHAND ' ' WHERE partsinputhand. ' sTime2 ' AND ' ' PARTSINPUTBODY.BILLCODE = PARTSINPUTHAND.BILLCODE ' ' GROU P 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: = 'Current product:' fname; panel6.caption: = radiogroup1.items.strings [radiogroup1.itemindex];
S: = SELECTSQLText (Radiogroup1.itemindex); SELECTRECORD (S, stockQueryx); // Execute Query 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; // Reconstruction of columns in cxgrid TvPartsx.datacontroller.createAllItems; for i: = 0 to II-1 do tvartsx.columns [i] .width: = 100; Next; ExcePt 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; Qclick (sender);
Procedure tfrmstockQuery.checkdateClick (sender: TOBJECT); beginedTBGnriqi.enabled: = checkdate.checked; edtendriqi.enabled: = checkdate.checked;
End.
