up_processstat.sql

这是用PB6开发的一个POS管理系统

/*订货数量*/
CREATE proc up_UpdateOrderQtywithImportQty
as
 Declare @Day int
 Declare @Inputdate char(8)

   if datepart(hh,getdate())<19 
   	Select @Day=-1
   else
   	Select @Day=0

   Select @InputDate=Convert(char(8),Dateadd(dd,@day,getdate()),112)
   
   Update a set a.OrderQty=b.Qty - a.OrderQty 
   from TopandBottom a,Vimport b
   Where a.goodsno=b.goodsno and a.deptNo=b.InputDept and b.auditDate=@Inputdate
   
   Update TopandBottom set OrderQty=0 where OrderQty <=0   
go

/*日终处理过程*/
CREATE PROCEDURE up_ProcessDayEnd AS
Declare @mysql varchar(300)

Declare c1 cursor for select ProcessSql from ProcessDayEnd where ProcessFlag='1'
Open c1
Fetch c1 into @Mysql

While @@Fetch_Status=0
  begin
    Execute @Mysql
    Fetch c1 into @mysql
  end
close c1
deallocate c1

go

/*日终统计过程*/
CREATE PROCEDURE up_ProcessStat
AS

Declare @day int
Declare @Inputdate char(8)

   if datepart(hh,getdate())<19 
   	Select @Day=-1
   else
   	Select @Day=0

Select @InputDate=Convert(char(8),Dateadd(dd,@day,getdate()),112)

/*进货统计*/
insert into importstat(deptno,groupno,classno,importdate,amt,ntaxamt,qty)
select a.inputdept,c.groupno,c.classno,convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by a.inputdept,c.groupno,c.classno,convert(char(8),a.auditdate,112)


/*品类进货统计*/
insert into categoryimportstat(categoryno,importdate,amt,ntaxamt,qty)
select isnull(c.categoryno,'99999999'),convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by c.categoryno,convert(char(8),a.auditdate,112)


/*厂商进货统计*/
insert into vendorimportstat(vendorno,importdate,amt,ntaxamt,qty)
select isnull(c.vendorno,'9999999999'),convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from goods c,import a,importdetail b
where a.importno=b.importno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by c.vendorno,convert(char(8),a.auditdate,112)


/*配送统计*/
insert into allocstat(deptno,groupno,classno,alloctdate,amt,ntaxamt,qty)
select a.destdept,c.groupno,c.classno,convert(char(8),a.auditdate,112),sum(b.amt),sum(b.ntaxamt),sum(b.qty)
from alloc a,allocdetail b,goods c
where a.allocno=b.allocno and b.goodsno=c.goodsno and convert(char(8),a.auditdate,112)=@inputdate and a.AuditFlag='1'
group by a.destdept,c.groupno,c.classno,convert(char(8),a.auditdate,112)



/*品类销售统计*/
insert into categoryretailstat(categoryno,retaildate,retailmode,taxratio,amt,costamt,disamt)
select isnull(c.categoryno,'99999999'),convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),c.taxratio,sum(b.amt),sum(b.costamt),sum(b.disamt)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate 
group by c.categoryno,convert(char(8),b.retaildate,112),c.retailmode,c.taxratio


/*厂商销售统计*/
insert into vendorretailstat(vendorno,retaildate,retailmode,taxratio,amt,costamt,disamt)
select isnull(c.vendorno,'9999999999'),convert(char(8),b.retaildate,112),isnull(c.retailmode,'0'),c.taxratio,sum(b.amt),sum(b.costamt),sum(b.disamt)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate
group by c.vendorno,convert(char(8),b.retaildate,112),c.retailmode,c.taxratio


/*销售统计*/

insert into retailstat(deptno,GroupNo,ClassNO,taxRatio,RetailMode,RetailDate,Amt,CostAmt,DisAmt,Qty) 
select b.deptno,c.groupno,c.classno,c.taxratio,isnull(c.retailmode,'0'),convert(char(8),b.retaildate,112),sum(b.amt),sum(b.costamt),sum(b.disamt),sum(b.qty)
from goods c,deptretail b
where c.goodsno=b.goodsno and convert(char(8),b.retaildate,112)=@inputdate 
group by b.deptno,c.groupno,c.classno,c.taxratio,c.retailmode,convert(char(8),b.retaildate,112)



/*新品销售统计*/
insert into newgoodsretail(goodsno,retaildate,amt,costamt,disamt,qty)
select c.goodsno,convert(char(8),b.retaildate,112),sum(b.amt),sum(b.costamt),sum(b.disamt),sum(b.qty)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate and c.newflag='1'
group by c.goodsno,convert(char(8),b.retaildate,112)



/*必备商品品销售统计*/
insert into importantgoodsretail(goodsno,retaildate,amt,costamt,disamt,qty)
select c.goodsno,convert(char(8),b.retaildate,112),sum(b.amt),sum(b.costamt),sum(b.disamt),sum(b.qty)
from goods c,deptretail b
where b.goodsno=c.goodsno and convert(char(8),b.retaildate,112)=@inputdate and mustflag='1'
group by c.goodsno,convert(char(8),b.retaildate,112)