毕业

最新推荐文章于 2014-03-21 11:08:00 发布

xluke2

最新推荐文章于 2014-03-21 11:08:00 发布

阅读量1.5k

点赞数

分类专栏：编程笔记文章标签： string integer join sql function forms

本文链接：https://blog.csdn.net/xluke2/article/details/242278

版权

编程笔记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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