?? viewandproc.sql
字號:
select @temp=ltrim(rtrim(str(@no)))
select @temp=left('000000',6-len(@temp))+@temp
select @receiptno=@deptno+@temp
end
else
select @receiptno=@deptno+'000001'
go
print 'up_getreceiptno is created already.'
go
if exists(select * from sysobjects where name='up_UpdateVendorPrice' and type='p')
drop procedure up_UpdateVendorPrice
go
Create Procedure up_UpdatevendorPrice (
@VendorNo tVendor,
@GoodsNo TGoods,
@TaxPrice TPrice)
as
Begin
Declare @HighPrice TPrice
Declare @LowPrice TPrice
declare @HighDate TDate
Declare @LowDate Tdate
if (select count(*) from vendorprice where goodsno=@goodsno and vendorno=@vendorno)>0
begin
Select @HighPrice=HighPrice,@HighDate=HighDate,@LowDate=LowDate,@LowPrice=LowPrice from VendorPrice Where VendorNo=@VendorNo and GoodsNo=@GoodsNo
if @TaxPrice > @HighPrice
Select @HighPrice=@TaxPrice,@HighDate=GetDate()
if @TaxPrice < @LowPrice
Select @LowPrice=@Taxprice,@LowDate=GetDate()
update vendorprice
set lastprice=@taxprice,
subprice=lastprice,
HighPrice=@HighPrice,
HighDate=@HighDate,
LowPrice=@LowPrice,
LowDate=@LowDate
where vendorno=@vendorno and goodsno=@goodsno
end
else
insert vendorprice(goodsno,vendorno,lastprice,highprice,
lowprice,subprice,highdate,lowdate)
values(@goodsno,@vendorno,@taxprice,@taxprice,
@taxprice,@taxprice,Getdate(),GetDate())
end
go
print ' Procedure up_UpdateVendorPrice is created'
go
if exists(select * from sysobjects where name='up_UpdateOrders' and type='p')
drop procedure up_UpdateOrders
go
Create Procedure up_UpdateOrders (
@OrderNo tReceipt,
@Goodsno TGoods,
@Qty TQty)
as
Begin
update orders
set receivedate=GetDate()
where orderno=@orderno
update orderdetail
set qty=isnull(qty,0)+@qty
where orderno=@orderno and goodsno=@goodsno
end
go
print "Procedure up_UpdateOrders is created"
go
if exists(select * from sysobjects where name='up_InsertAlloc' and type='p')
drop procedure up_InsertAlloc
go
Create Procedure up_InsertAlloc (
@SrcDept TDept,
@DestDept TDept,
@ImportNo TReceipt,
@GoodsNo TGoods,
@Qty TQty,
@TaxPrice TPrice,
@NTaxPrice TPrice,
@Amt TAmt,
@NTaxAmt TAmt,
@ValidDate TDate,
@VendorNo TVendor,
@BatchNo TBatch,
@RetailPrice TPrice,
@Operator TStaff,
@Employee TStaff,
@BusinessFlag char(1))
as
begin
Declare @AllocNo TReceipt
Declare @Profitratio numeric(5,3)
Declare @Price TPrice
Declare @CostAmt TAmt
Declare @NCostAmt TAmt
Select @ProfitRatio=Isnull(ProfitRatio,0) from Dept where DeptNo=@Destdept
if (select count(*) from alloc where receiptno=@importno and receiptflag='2')>0
select @allocno=allocno from alloc where receiptno=@importno and receiptflag='2'
else
begin
exec up_getreceiptno 'alloc',@destdept,@allocno output
Select @CostAmt=amt,@NCostAmt=NTaxAmt from Import where ImportNo=@ImportNO /*get Import amt*/
insert alloc(allocno,receiptno,operator,employee,allocdate,destdept,srcdept,
inputdept,cost,ntaxamt,amt,receiptflag,businessflag,receivedate,auditflag)
values(@allocno,@importno,@operator,@employee,getdate(),@destdept,@Srcdept,
@SrcDept,@CostAmt,Round(@NCostAmt * (1+@ProfitRatio),2),
Round(@CostAmt * (1 + @ProfitRatio),2),'2',@businessflag,Getdate(),'1')
end
Select @Price = @TaxPrice * (1 + @ProfitRatio)
Select @NTaxPrice = @NTaxPrice * ( 1 + @Profitratio)
Select @Amt = Round(@Amt * (1 + @Profitratio),2)
Select @NTaxAmt = Round(@NTaxAmt * ( 1 + @ProfitRatio),2)
insert allocdetail(allocno,goodsno,qty,auditQty,costprice,price,ntaxprice,amt,
ntaxamt,validdate,vendorno,batchno,RetailPrice)
values(@allocno,@goodsno,@qty,@Qty,@TaxPrice,@Price,@ntaxprice,@amt,
@ntaxamt,@validdate,@vendorno,@batchno,@RetailPrice)
end
go
Print "Procedure up_insertAlloc is Created "
go
/*=====================================*/
/* Process PosRetail and SuspendRetail */
/*=====================================*/
if exists (select * from sysobjects where id = object_id('dbo.ProcessPosAndSuspendRetail') and sysstat & 0xf = 4)
drop procedure dbo.ProcessPosAndSuspendRetail
go
CREATE PROCEDURE ProcessPosAndSuspendRetail AS
Delete from PosRetail Where InputDate < DateAdd(dd,-5,GetDate())
Delete From SusPendRetail Where InputDate < DateAdd(dd,-5,GetDate())
Delete From Retail Where InputDate < DateAdd(dd,-32,GetDate())
Delete From DayStock where date < Convert(char(8),DateAdd(dd,-32,GetDate()),112)
Delete From Req Where InputDate < DateAdd(dd,-62,GetDate())
Delete From ChecksFlow Where InputDate < DateAdd(dd,-62,GetDate())
Delete From pz where bz='1'
go
Print " Procedure Processposandsuspendretail is Created!"
go
if exists ( select * from sysobjects where name='autocreateDayReport' and type='p')
drop proc AutoCreateDayReport
go
Create Proc AutoCreateDayReport (@day as int) as
Declare @deptNo as tDept
Declare @Date tDate
Declare @SDate char(8)
Declare @Count int
Declare @year int
Declare @Month Int
Declare @Flag char(1)
Select @date=DateAdd(dd,@day,GetDate())
Select @SDate=Convert(char(8),@date,112)
Select @year=year,@Month=Month from Months where Convert(char(8),enddate,112)=@SDate
Select @Count=@@RowCount
declare c1 cursor for select deptno from dept where localflag='1'
open c1
Fetch c1 into @deptNo
While @@Fetch_Status=0
Begin
Select @Flag=isnull(Flag,'0') from tally where Convert(char(8),businessdate,112)=@SDate and deptNo=@deptNo
if @@RowCount=0 or @Flag<>'1' /* 沒有登帳*/
begin
if @Count>0
Execute up_ComputeCostPrice /*計算成本家價*/
Execute CreateDayReport @deptNo,@Sdate
if @Count>0
Execute MonthInit @deptNo,@Year,@Month
Insert tally (deptno,tallydate,businessdate,staff,flag)
values (@deptNo,getdate(),@Date,'root','1')
end
Fetch c1 into @DeptNO
end
close c1
deallocate c1
go
print 'Proc AutoCreatedayreport is created!'
go
/****************************************************************************/
/* 結算用過程 */
/****************************************************************************/
/*原結算過程*/
if exists ( select * from sysobjects where name='up_InsertAccountForImport' and type='p')
drop Proc up_InsertAccountForImport
go
CREATE Proc up_InsertAccountForImport
as
Declare @day int
if datepart(hh,getdate())<19
Select @Day=-1
else
Select @Day=0
Insert Account (Subjectno,DeptNo,ReceiptNo,Companyno,Employee,Operator,Credit,balance,occurdate)
Select '204',a.InputDept,a.ImportNo,a.VendorNo,a.Employee,a.Operator,sum(b.Amt),sum(-b.amt),a.AuditDate
From Import a,Importdetail b
where a.ImportNo=b.ImportNo and a.AuditFlag='1' and
Convert(char(8),a.AuditDate,112)=Convert(char(8),dateAdd(dd,@day,GetDate()),112)
group by a.Inputdept,a.ImportNo,a.vendorNo,a.employee,a.Operator,a.Auditdate
go
print 'proc up_InsertAccountForImport is created!'
go
/*新結算過程*/
/* up_importcheck_update_by_retail 銷售劃進貨流水 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_retail') > 0
drop procedure up_importcheck_update_by_retail
go
create procedure up_importcheck_update_by_retail
@GoodsNo TGoods,@RetailQty TQty
as
declare @ImportQty TQty
declare @exportQty TQty /*退貨數量 */
declare @RetailedQty TQty /*--已銷售數量*/
declare @BalanceQty TQty /*剩余數量*/
select @BalanceQty = @RetailQty
if @RetailQty > 0 /*正常銷售，銷售數量為正數 */
begin /*先劃供貨商為88888888的進貨*/
select @RetailedQty = isnull(RetailQty,0) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo
if @RetailedQty < 0
begin
if ( @BalanceQty + @RetailQty ) > 0
update Import_To_Check set RetailQty = 0 where VendorNo ='88888888' and GoodsNo = @GoodsNo
else
begin
update Import_To_Check set RetailQty = isnull(RetailQty,0) + @BalanceQty where VendorNo='88888888' and GoodsNo = @GoodsNo
return
end
select @BalanceQty =@BalanceQty + @RetailEdQty
end
declare my_cursor CURSOR FOR
SELECT isnull(ImportQty,0),isnull(ExportQty,0),isnull(RetailQty,0) from Import_To_Check
where GoodsNo = @GoodsNo and (isnull(ImportQty,0) + isnull(ExportQty,0) > isnull(RetailQty,0) )
order by id
end
else /*退貨或更正時，銷售數量為負數*/
begin
select @RetailEdQty = isnull(RetailQty,0) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo
if @RetailedQty > 0
begin
if (@RetailQty + @RetailedQty) > 0
begin
update Import_To_Check set RetailQty = isnull(RetailQty,0) + @BalanceQty where VendorNo='88888888' and GoodsNo = @GoodsNo
return
end
else
update Import_To_Check set RetailQty = 0 where VendorNo='88888888' and GoodsNo = @GoodsNo
select @BalanceQty = @BalanceQty + @RetailedQty
end
declare my_cursor CURSOR FOR
SELECT isnull(ImportQty,0),isnull(ExportQty,0),isnull(RetailQty,0) from Import_To_Check
where GoodsNo = @GoodsNo and isnull(RetailQty,0) > 0
order by id desc
end
OPEN my_cursor
FETCH NEXT from my_cursor into @ImportQty,@ExportQty,@RetailedQty
if @@fetch_status = 0 /*表示有未劃完進貨的記錄*/
begin
while @@fetch_status = 0
begin
if @RetailQty > 0
BEGIN
if (@ImportQty + @ExportQty) >= (@BalanceQty + @RetailedQty) /*剩余數量+已銷售數量小于進貨數量，表示夠劃*/
begin
UPDATE Import_To_Check SET RetailQty = isnull(RetailQty,0) + @BalanceQty
WHERE current of my_cursor
select @BalanceQty = 0
BREAK /*退出循環*/
end
ELSE
begin
UPDATE Import_To_Check SET RetailQty = (@ImportQty + @ExportQty) WHERE current of my_cursor
select @BalanceQty = @BalanceQty - (@ImportQty + @ExportQty - @RetailedQty)
end
END
Else
BEGIN
if (@RetailedQty + @BalanceQty) > 0
begin
UPDATE Import_To_Check SET RetailQty = isnull(RetailQty,0) + @BalanceQty
WHERE current of my_cursor
select @BalanceQty = 0
BREAK /*退出循環*/
end
ELSE
begin
UPDATE Import_To_Check SET RetailQty = 0 WHERE current of my_cursor
select @BalanceQty = @BalanceQty + @RetailedQty
end
END
FETCH NEXT from my_cursor into @ImportQty,@ExportQty,@RetailedQty
end
if @BalanceQty <> 0 /*銷售數量沒有劃完*/
if (select count(*) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo ) >0
update Import_To_Check set
RetailQty = isnull(RetailQty,0) + @BalanceQty
where VendorNo = '88888888' and GoodsNo = @GoodsNo
else
insert into Import_To_Check(VendorNo,GoodsNo,RetailQty,ImportDate,ImportNo)
values( '88888888',@GoodsNo,@BalanceQty,getdate(),'##########')
end
else /*沒有可劃進貨的記錄，可能是先銷后進的情況*/
if (select count(*) from Import_To_Check where VendorNo='88888888' and GoodsNo = @GoodsNo ) >0
update Import_To_Check set
RetailQty = isnull(RetailQty,0) + @BalanceQty
where VendorNo = '88888888' and GoodsNo = @GoodsNo
else
insert into Import_To_Check(VendorNo,GoodsNo,RetailQty,ImportDate,ImportNo)
values( '88888888',@GoodsNo,@BalanceQty,getdate(),'##########')
close my_cursor
deallocate my_cursor
go
print 'up_importcheck_update_by_retail is created!'
go
/* up_importcheck_update_by_import 進貨調整import_to_check表 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_import') > 0
drop procedure up_importcheck_update_by_import
go
create procedure up_importcheck_update_by_import
@GoodsNo TGoods, @ImportNo TReceipt, @ImportQty TQty, @VendorNo TVendor
as
declare @RetailedQty TQty --已銷售數量
select @RetailedQty = isnull(RetailQty,0) from Import_To_Check
where VendorNo ='88888888' and GoodsNo = @GoodsNo
if @@rowcount = 0 return
if @RetailedQty > 0 /*表示已先銷后進，則要把已銷的數量反映到進貨來*/
if @ImportQty < @RetailedQty
begin
update Import_To_Check set RetailQty = isnull(RetailQty,0) - @ImportQty
where VendorNo = '88888888' and GoodsNo = @GoodsNo
update Import_To_Check set RetailQty = @ImportQty
where VendorNo = @VendorNo and ImportNo = @ImportNo and GoodsNo = @GoodsNo
end
else
begin
update Import_To_Check set RetailQty = 0
where VendorNo = '88888888' and GoodsNo = @GoodsNo
update Import_To_Check set RetailQty = @RetailedQty
where VendorNo = @VendorNo and ImportNo = @ImportNo and GoodsNo = @GoodsNo
end
go
print 'up_importcheck_update_by_import is Created!'
go
/* up_importcheck_update_by_checkviaamt 按金額結算時調整import_to_check表的結算數量及結算金額 */
if (select count(*) from sysobjects where name = 'up_importcheck_update_by_checkviaamt') > 0
drop procedure up_importcheck_update_by_checkviaamt
go
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -