/*訂貨數量*/
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)