sql.dll

仓库管理系统的部分代码！！！！设计时可以参考

  Make_Date, 
  '商品领用' as Summary, 
  -In_Amount as In_Amount, 
  -Sale_Amount as Sale_Amount 
  from UseMst 
  where Attrib = 1
go
------------------------------------------------------------
------------------------------------------------------------
--过程
------------------------------------------------------------
------------------------------------------------------------
--生成供应商每月的流水帐Vyyyymm
create procedure Create_vendor_bill (@pDate datetime) WITH ENCRYPTION as
declare @pppExecuteString nvarchar(2000)
declare @pppTableName char(128)
select @pppTableName = convert(char(10), @pDate, 102)
select @pppTableName = 'V' + subString(@pppTableName,1,4) + subString(@pppTableName,6,2)
if not exists(select * from sysobjects where name = @pppTableName)
begin
  select @pppExecuteString = 'create table ' 
    + RTrim(@pppTableName) 
    + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
    + 'V_No char(6) NULL,'
    + 'T_No char(12) NULL,'
    + ' Make_Date datetime NULL,'
    + 'Summary char(12) NULL,'
    + 'G_No char(13) NULL,'
    + 'In_Price money default 0,'
    + 'Sale_Price money default 0,'
    + 'Number float default 0)'
  exec (@pppExecuteString)
end
go
------------------------------------------------------------
--生成仓库每月的流水帐Syyyymm
create procedure Create_Storage_bill (@pDate datetime) WITH ENCRYPTION as
declare @pppExecuteString nvarchar(2000)
declare @pppTableName char(128)
select @pppTableName = convert(char(10), @pDate, 102)
select @pppTableName = 'T' + subString(@pppTableName,1,4) + subString(@pppTableName,6,2)
if not exists(select * from sysobjects where name = @pppTableName)
begin
  select @pppExecuteString = 'create table ' 
    + RTrim(@pppTableName) 
    + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
    + 'S_No char(6) NULL,T_No char(12) NULL,'
    + 'Make_Date datetime NULL,'
    + 'Summary char(12) NULL,'
    + 'G_No char(13) NULL,'
    + 'In_Price money default 0,'
    + 'Sale_Price money default 0,'
    + 'Number float default 0)'
  exec (@pppExecuteString)
end
go
------------------------------------------------------------
--建数据表Syyyymmdd=商品库存量表
create procedure GetCountStock(@pDate datetime) as
--参数:@pDate  datetime   盘点日期
declare @ExecuteString nvarchar(2000)
declare @TableName char(20)
declare @S_No char(6), @G_No char(13), @Number float

select @TableName = convert(char(10), @pDate, 102)
select @TableName = 'S' 
  + subString(@TableName,1,4) 
  + subString(@TableName,6,2) 
  + subString(@TableName,9,2)
select @ExecuteString = 'create table ' 
  + @TableName 
  + '(R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'S_No char(6) NULL,'
  + 'G_No char(13) NULL,'
  + 'Number Float NULL)'
exec (@ExecuteString)

declare Cur_Table Cursor for select S_No, G_No, Number from Stock for read only
open Cur_Table
fetch Cur_Table into @S_No, @G_No, @Number
while @@fetch_status = 0
begin
  select @ExecuteString = 'insert ' 
    + @TableName 
    + ' (S_No, G_No, Number) values (''' 
    + @S_No 
    + ''',''' 
    + @G_No 
    + ''',' 
    + ltrim(Str(@Number)) 
    + ')'
  exec (@ExecuteString)
  fetch Cur_Table into @S_No, @G_No, @Number
end
close Cur_Table
deallocate Cur_Table
go

------------------------------------------------------------
create procedure GetCodeNo(@pItem_Name char(20), @pFlag bit, @Result char(20) OUTPUT) as
--参数:@pItem_Name  char(20)   编码的项目
--参数:@pFlag       bit        是否写数据
declare @FirstCharactor char(10)
declare @Number         int
declare @Length         int
--declare @Result 	char(20)
declare @t              char(20)
declare @even		int
declare @odd		int
declare @i		int
select @FirstCharactor = FirstCharactor,
    @Number = Number,
    @Length = Length 
    from Code 
    where Item_Name = @pItem_Name
select @Result = LTrim(str(@Number+1))
while len(@Result) < @Length
begin
  select @Result = '0' + @Result
end
select @Result = RTrim(@FirstCharactor) + RTrim(@Result)
if @pFlag = 1
begin
 update Code set Number = Number+1 where Item_Name = @pItem_Name
end
if @pItem_name = '商品编号' and len(@Result) = 12
begin
  select @even = 0
  select @odd = 0
  select @i = 2
  while @i <= len(@Result)
  begin
    select @even = @even + convert(int, SubString(@Result, @i, 1))
    select @odd = @odd + convert(int, SubString(@Result, @i - 1, 1))
    select @i = @i + 2
  end
  select @even = @even*3 + @odd
  select @t = lTrim(str(@even))
  select @even = convert(int, SubString(@t, len(@t), 1))
  if @even > 0
    select @even = 10 - @even
  select @Result = lTrim(@Result) + convert(char(1), @even)
end
go
------------------------------------------------------------
--取得仓库的金额帐(根据：起止日期、仓库编号)结果存放在@TableName表中
create procedure Get_StorageMoneyBill 
  (@StartDate datetime, 
   @EndDate datetime, 
   @S_No char(6), 
   @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @Amount money
declare @T_No char(12), @Make_Date datetime, @Summary char(10), @In_Amount money
--select @ExecuteString = 'drop table ' + RTrim(@TableName)
--exec (@ExecuteString)
select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Amount Money,'
  + 'Out_Amount Money,'
  + 'Rest_Amount Money)'
exec (@ExecuteString)
select @Amount = sum(In_Amount) 
  from StorageMoneyBill 
  where S_No = @S_No and Make_Date < @StartDate
if isnull(@Amount, 0) <> 0
begin
  select @ExecuteString = 'insert ' 
    + RTrim(@TableName) 
    + ' (Make_Date, Summary, Rest_Amount) values (''' 
    + convert(char(10), @StartDate, 102) 
    + ''',''期前结余'',' 
    + ltrim(str(@Amount)) + ')'
  exec (@ExecuteString)
end
else
begin
 select @Amount = 0
end
set nocount on
declare Cur_Table Cursor for select T_No, Make_Date, Summary, In_Amount 
  from StorageMoneyBill 
  where Make_Date >= @StartDate and Make_Date <= @EndDate and S_No = @S_No 
  order by Make_Date for read only
open Cur_Table
fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_Amount
while @@fetch_status = 0
begin
  select @Amount = @Amount + @In_Amount
  if @In_Amount >= 0
  begin
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, In_Amount, Rest_Amount) values (''' 
      + @T_No + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  end
  else
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, Out_Amount, Rest_Amount) values (''' 
      + @T_No 
      + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' 
      + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(-@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  exec (@ExecuteString)
  fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_amount
end
close Cur_Table
deallocate Cur_Table
go
------------------------------------------------------------
--取得仓库的商品帐(根据：起止日期、仓库编号、商品编号)结果存放在@TableName表中
create procedure Get_StorageGoodsBill 
  (@StartDate datetime, 
  @EndDate datetime, 
  @S_No char(6), 
  @G_No char(13), 
  @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @tTableName char(128), @startTableName char(128), @endTableName char(128)
declare @Number Float, @tNumber Float
declare @T_No char(12), @Make_Date datetime, @Summary char(10), @In_Number float
declare @ttTableName char(128)

select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Number Float,'
  + 'Out_Number Float,'
  + 'Rest_Number Float)'
exec (@ExecuteString)
select @startTableName = convert(char(10), @StartDate, 102)
select @startTableName = 'T' + subString(@startTableName,1,4) + subString(@startTableName,6,2)
select @endTableName = convert(char(10), @EndDate, 102)
select @endTableName = 'T' + subString(@endTableName,1,4) + subString(@endTableName,6,2)
--取得结余
select @Number = 0
set nocount on
declare Cur_Table Cursor for select name 
  from sysobjects 
  where name >= 'T200001' and Name <= @startTableName for read only
open Cur_Table
fetch Cur_Table into @tTableName
while @@fetch_status = 0
begin
  select @ttTableName = '##' + right(rtrim(convert(char(40), getdate(),121)),3)
  select @ExecuteString = 'select sum(number) as number into ' 
    + @ttTableName 
    + ' from ' 
    + Rtrim(@tTableName) 
    + ' where S_No = ''' 
    + @S_No 
    + ''' and G_No = ''' 
    + @G_No 
    + ''' and Make_Date < ''' 
    + convert(char(10), @StartDate, 102) 
    + ''''
  exec (@ExecuteString)

  select @ExecuteString = 'declare xxxCur_Table Cursor for select number from ' + @ttTableName
  exec (@ExecuteString)
  open xxxCur_Table
  fetch xxxCur_Table into @tNumber
  close xxxCur_Table
  deallocate xxxCur_Table
  
  select @ExecuteString = 'drop table ' + @ttTableName
  exec (@ExecuteString)

  select @Number = @Number + isnull(@tNumber,0)
  fetch Cur_Table into @tTableName
end
close Cur_Table
deallocate Cur_Table
if isnull(@Number, 0) <> 0
begin
  select @ExecuteString = 'insert ' 
    + RTrim(@TableName) 
    + ' (Make_Date, Summary, Rest_Number) values (''' 
    + convert(char(10), @StartDate, 102) 
    + ''',''期前结余'',' 
    + ltrim(str(@Number)) 
    + ')'
  exec (@ExecuteString)
end
else
begin
 select @Number = 0
end
--取得明细
select @ExecuteString = ''
declare Cur_Table Cursor for 
  select name 
  from sysobjects 
  where name >= @startTableName and Name <= @endTableName for read only
open Cur_Table
fetch Cur_Table into @tTableName
while @@fetch_status = 0
begin
  select @ttTableName = '##' + right(rtrim(convert(char(40), getdate(),121)),3)
  select @ExecuteString = 'select T_No, Make_Date, Summary, Number into ' 
    + @ttTableName 
    + ' from ' 
    + Rtrim(@tTableName) 
    + ' where S_No = ''' 
    + @S_No 
    + ''' and G_No = ''' 
    + @G_No 
    + ''''
  exec (@ExecuteString)

  select @ExecuteString = 'declare tCur_Table Cursor for select T_No, Make_Date, Summary, Number from ' 
    + @ttTableName 
    + ' where Make_Date >= ''' 
    + convert(char(10), @StartDate, 120) 
    + ''' and Make_Date <= ''' 
    + convert(char(10), @EndDate, 120) 
    + ''' order by Make_Date for read only'
  exec (@ExecuteString)

  open tCur_Table
  fetch tCur_Table into @T_No, @Make_Date, @Summary, @In_Number
  while @@fetch_status = 0
  begin
    select @Number = @Number + @In_Number
    if @In_Number >= 0
    begin
      select @ExecuteString = 'insert ' 
        + RTrim(@TableName) 
        + ' (T_No, Make_Date, Summary, In_Number, Rest_Number) values (''' 
        + @T_No 
        + ''',''' 
        + convert(char(10), @Make_Date, 102) 
        + ''',''' 
        + RTrim(@Summary) 
        + ''',' 
        + ltrim(str(@In_Number)) 
        + ',' 
        + ltrim(str(@Number)) 
        + ')'
    end
    else
      select @ExecuteString = 'insert ' 
        + RTrim(@TableName) 
        + ' (T_No, Make_Date, Summary, Out_Number, Rest_Number) values (''' 
        + @T_No 
        + ''',''' 
        + convert(char(10), @Make_Date, 102) 
        + ''',''' 
        + RTrim(@Summary) 
        + ''',' 
        + ltrim(str(-@In_Number)) 
        + ',' 
        + ltrim(str(@Number)) 
        + ')'
    exec (@ExecuteString)