我在介面上用了一个TcxDateEdit给下面的存储的@startDate和@startend进行赋值的,用一个Button作为开始操作
具体的存储过程为(要把yingshouCash应收现金、shishouCash实收现金、yudingjin预定金分别在用3个Edit来显示出来):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE usp_newDemand_Rpt/*@startDate定义为哪一天的*/
@startDate varchar(10),
@endDate varchar(10)
AS
------day calculation
--declare @startDate varchar(10)
--select @startDate='2008-12-01'
-- exec usp_newDemand_Rpt '2008-12-01','2008-12-02'/*把@startDate转换格式来yyyy-mm-dd*/
set @startDate=rtrim(ltrim(str(year(@startDate))))+ '-' +replace(str(month(@startDate),2),' ','0')+'-' +replace(str(day(@startDate),2),' ','0')
set @endDate=rtrim(ltrim(str(year(@endDate))))+ '-' +replace(str(month(@endDate),2),' ','0')+'-' +replace(str(day(@endDate),2),' ','0')delete T_RPT_New_Demand
insert into T_RPT_New_Demand(DayCheckTemp_Date,StockCheck_NumB,TodayIn_Num,MoveIn_Num, CenterFinish_Num,Sales_Num,MoveOut_Num,Reject_Num,ProductPrice,Rebate_Num,Gift_Num,Free_Num,Cash_Num,TodayOut_Num,Adjust_Num,zdd_num,fdd_num,orderzk) select (a.ShopCheck_Date+1),sum(a.ShopCheck_Num * b.ProductPrice),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from T_ShopCheck a inner join T_ShopCheck as b on a.BM_ID=b.bm_id where a.ShopCheck_Date >= dateadd(day,-1,@startDate) and a.ShopCheck_Date < @endDate and a.ShopCheck_Num > 0 and b.ShopCheck_Date>=a.shopcheck_date+1 and b.ShopCheck_Date < a.shopcheck_date+2 and a.bm_Id =b.bm_Id and a.product_id =b.product_id and a.BM_ID in(select BM_ID from T_STAFFPurview where STAFF_ID = '100070' and a.BM_ID in(select BM_ID from T_BM where BM_Type = '0')) group by a.ShopCheck_Date exec( '
select DayCheckTemp_Date,
sum(Sales_Num)+sum(Rebate_Num)+sum(Gift_Num) yingshouCash应收现金,
sum(Cash_Num) shishouCash实收现金,
sum(Zdd_num) yudingjin预定金,
from T_DayCheckTemp group by DayCheckTemp_Date order by DayCheckTemp_Date
')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
具体的存储过程为(要把yingshouCash应收现金、shishouCash实收现金、yudingjin预定金分别在用3个Edit来显示出来):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE usp_newDemand_Rpt/*@startDate定义为哪一天的*/
@startDate varchar(10),
@endDate varchar(10)
AS
------day calculation
--declare @startDate varchar(10)
--select @startDate='2008-12-01'
-- exec usp_newDemand_Rpt '2008-12-01','2008-12-02'/*把@startDate转换格式来yyyy-mm-dd*/
set @startDate=rtrim(ltrim(str(year(@startDate))))+ '-' +replace(str(month(@startDate),2),' ','0')+'-' +replace(str(day(@startDate),2),' ','0')
set @endDate=rtrim(ltrim(str(year(@endDate))))+ '-' +replace(str(month(@endDate),2),' ','0')+'-' +replace(str(day(@endDate),2),' ','0')delete T_RPT_New_Demand
insert into T_RPT_New_Demand(DayCheckTemp_Date,StockCheck_NumB,TodayIn_Num,MoveIn_Num, CenterFinish_Num,Sales_Num,MoveOut_Num,Reject_Num,ProductPrice,Rebate_Num,Gift_Num,Free_Num,Cash_Num,TodayOut_Num,Adjust_Num,zdd_num,fdd_num,orderzk) select (a.ShopCheck_Date+1),sum(a.ShopCheck_Num * b.ProductPrice),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from T_ShopCheck a inner join T_ShopCheck as b on a.BM_ID=b.bm_id where a.ShopCheck_Date >= dateadd(day,-1,@startDate) and a.ShopCheck_Date < @endDate and a.ShopCheck_Num > 0 and b.ShopCheck_Date>=a.shopcheck_date+1 and b.ShopCheck_Date < a.shopcheck_date+2 and a.bm_Id =b.bm_Id and a.product_id =b.product_id and a.BM_ID in(select BM_ID from T_STAFFPurview where STAFF_ID = '100070' and a.BM_ID in(select BM_ID from T_BM where BM_Type = '0')) group by a.ShopCheck_Date exec( '
select DayCheckTemp_Date,
sum(Sales_Num)+sum(Rebate_Num)+sum(Gift_Num) yingshouCash应收现金,
sum(Cash_Num) shishouCash实收现金,
sum(Zdd_num) yudingjin预定金,
from T_DayCheckTemp group by DayCheckTemp_Date order by DayCheckTemp_Date
')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
var
sStatYear,sStatMonth,sStatDay,sStatShift,sOperator,Where_Sql,tmp: String;
OperatorID,OperatorName: String;
Begingdate,Enddate: String;
BeginTime,EndTime: String;
ADOQ,ADOQ1,ADOQ2: TADOQuery;
Node: TcxTreeListNode;
t: TRMView;
FrmWait: TFrmWait;
begin
try
begin
FrmWait := TFrmWait.Create(SELF);
FrmWait.Show; FrmWait.Update;
application.ProcessMessages;
if TreeList.FocusedNode = nil then
begin
FrmWait.Free;
exit;
end;
Node := TreeList.FocusedNode;
sStatYear := Format('%.4d',[StrToInt(EdtYear.Text)]);
sStatMonth := Format('%.2d',[StrToInt(EdtMonth.Text)]);
sStatDay := Format('%.2d',[StrToInt(EdtDay.Text)]);
sStatShift := IntToStr(ComBoxShift.ItemIndex +1);
BeginTime := DateTimeToStr(EdtBeginDate.Date);
EndTime := DateTimeToStr(EdtEndDate.Date);
OperatorID :=Trim(ComBoxOperator.Text);
OperatorName :=Trim(EdtOperatorName.Text);
Begingdate:=sStatYear+sStatMonth+'01';
Enddate :=sStatYear+sStatMonth+'31';
if TreeList.ColumnByName('StatShift').Values[Node.index] = 1 then
begin
Where_Sql := ' where StatDate = ' + sStatYear+sStatMonth+sStatDay + ' and StatShift = '+sStatShift;
end
else
if TreeList.ColumnByName('StatDay').Values[Node.index] = 1 then
begin
Where_Sql := ' where StatDate = ' + sStatYear+sStatMonth+sStatDay;
end
else
if TreeList.ColumnByName('StatMonth').Values[Node.index] = 1 then
begin
Where_Sql := ' where StatDate = ' + sStatYear+sStatMonth;
end
else
if TreeList.ColumnByName('StatYear').Values[Node.index] = 1 then
begin
Where_Sql := ' where StatDate = ' + sStatYear;
end;
if TreeList.ColumnByName('BeginDate').Values[Node.index] = 1 then
Where_Sql := '';
if(TreeList.ColumnByName('OperatorId').Values[Node.index] = 1)then
Where_Sql :=' where Operator='+OperatorID+' and StatDate >= '+begingdate+' and StatDate <= '+enddate;{ + ' and Operator='+OperatorID; }
ADOQ := TADOQuery.Create(self);
// ADOQ1 := TADOQuery.Create(self);
end;
finally
end;
try
ADOQ.Connection := FrmDataModule.ADOCon;
// ADOQ1.Connection := FrmDataModule.ADOCon;
ADOQ.SQL.Text := 'Exec ' + TreeList.ColumnByName('ProcedureName').Values[Node.index] + ' ''' + sStatYear + ''',''' + sStatMonth + ''',''' + sStatDay + ''','''+BeginTime+''','''+EndTime+''',''' + sStatShift + ''',''' + sStationID +''','''+OperatorID+ '''';
ADOQ.ExecSQL;
{ if(TreeList.ColumnByName('OperatorId').Values[Node.index] = 1)then
begin
ADOQ1.SQL.Text:= 'Select * from input00001 ' +Where_Sql;
ADOQ1.Open;
ADOQ1.First;
ADOQ.SQL.Text := 'select * from '+ TreeList.ColumnByName('DataTable').Values[Node.index] + Where_Sql;
ADOQ.Open;
while not ADOQ1.Eof do
begin
ADOQ.Append;
ADOQ.FieldByName('statdate').AsInteger:=ADOQ1.FieldByName('statdate').AsInteger ;
ADOQ.FieldByName('Operator').AsInteger:=ADOQ1.FieldByName('operatorid').AsInteger ;
ADOQ.FieldByName('OperatorName').AsString:=ADOQ1.FieldByName('OperatorName').AsString ;
ADOQ1.Next;
end;
end;
}
ADOQ.Close;
ADOQ.SQL.Text := 'select * from '+ TreeList.ColumnByName('DataTable').Values[Node.index] + Where_Sql;
ADOQ.Open;
RMDBDataSet.DataSet := ADOQ;
RMReport.LoadFromFile(TreeList.ColumnByName('ReportPathName').Values[Node.index]);
t := RMReport.FindObject('StationName');
if t <> nil then t.Memo.Text := sStationName;
// FrmWait.Free;
// RMReport.ShowReport;
finally
FrmWait.Free;
RMReport.ShowReport;
ADOQ.Free;
end;
end;
看对你有帮助吗?