SQL2005存储过程

?delta2???

use dormitory
go
create proc pr_delta2count
@emp_type int,@dept_code nvarchar(8),@delta2_count int output
as
Select @delta2_count = Count(*) From Zs_Person P LEFT JOIN VEmployee V ON P.EMpCode=V.EmpCode
Where P.FactoryCode=@factory_code
AND V.EmpType=@emp_type
AND V.DeptCode IN @dept_code
go

?︽

declare @factory_code nvarchar(6),@emp_type int,@dept_code nvarchar(8),@delta2_count int output
exec pr_delta2count 'delta2','j+1','Group(i)',@delta2_count output
select @delta2_count
go
xlsheet.Cells(i + 7, j + 3) = CNumber(@delta2_count)





???delta2盝?ノ

use dormitory
go
create proc pr_delta2sumcharges
@month char(6),@emp_type int,@dept_code nvarchar(8),@delta2_sumcharges money output
as
Select @delta2_sumcharges=Sum(M.Charges) From Zs_Monthly_Detail M LEFT JOIN VEmployee V ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND V.EmpType=@emp_type
AND V.DeptCode In @dept_code
go

?︽

declare @month char(6),@factory_code nvarchar(6),@emp_type int,@dept_code nvarchar(8),@delta2_sumcharges money output
exec pr_delta2sumcharges 'm','delta2','j+1','Group(i)',@delta2_sumcharges output
select @delta2_sumcharges
go
xlsheet.Cells(i + 7, j + 5 + k) = CNumber(@delta2_sumcharges)





???delta2?

use dormitory
go
create proc pr_delta2sumwatercharges
@month char(6),@emp_type int,@dept_code nvarchar(8),@delta2_sumwatercharges money output
as
select @delta2_sumwatercharges=Select Sum(M.WaterCharge) From Zs_Monthly_Detail M LEFT JOIN VEmployee V ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND V.EmpType=@emp_type
AND V.DeptCode In @dept_code
go

?︽

declare @month char(6),@factory_code nvarchar(6),@emp_type int,@dept_code nvarchar(8),@delta2_sumwatercharges money output
exec pr_delta2sumwatercharges 'm','delta2','j+1','Group(i)',@delta2_sumwatercharges output
select @delta2_sumwatercharges
go
xlsheet.Cells(i + 7, j + 6 + k) = CNumber(@delta2_sumwatercharges)





???delta1?

use dormitory
go
create proc pr_delta1countpeople
@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_countpeople int output
as
if @area_code = 'B' then
Select @delta1_countpeople=Count(*)
From Zs_Person P
LEFT JOIN VEmployee V
ON P.EmpCode=V.EmpCode
LEFT JOIN Zs_Room R
ON P.RoomID=R.RoomID
Where P.FactoryCode=@factory_code
AND R.BuildingCode=@building_code
AND R.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
elseif @area_code = 'C' then
Select @delta1_countpeople=Count(*)
From Zs_Person P
LEFT JOIN VEmployee V
ON P.EmpCode=V.EmpCode
LEFT JOIN Zs_Room R
ON P.RoomID=R.RoomID
Where P.FactoryCode=@factory_code
AND R.BuildingCode=@building_code
AND R.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
go
end
end

?︽

declare @factory_code nvarchar(6),@building_code nvarchar(6),@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_countpeople int output

exec pr_delta1countpeople 'delta1','STAFF','B/C','Group(i)',@delta1_countpeople output
select @delta1_countpeople
go
xlsheet.Cells(i + 7, 15) = CNumber(@delta1_countpeople)
xlsheet.Cells(i + 7, 16) = CNumber(@delta1_countpeople)





???delta1盝?

use dormitory
go
create proc pr_delta1sumcharges
@month char(6),@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_sumcharges money output
as
if @area_code = 'B' then
Select @delta1_sumcharges=Sum(M.Charges)
From Zs_Monthly_Detail M
LEFT JOIN VEmployee V
ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND M.BuildingCode=@building_code
AND M.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
go
else if @area_code = 'C' then
Select @delta1_sumcharges=Sum(M.Charges)
From Zs_Monthly_Detail M
LEFT JOIN VEmployee V
ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND M.BuildingCode=@building_code
AND M.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
go
end
end

?︽

declare @month char(6),@factory_code nvarchar(6),@building_code nvarchar(6),@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_sumcharges money output
exec pr_delta1sumcharges'm','delta1','STAFF','B/C','Group(i)',@delta1_sumcharges output
select @delta1_sumcharges
go
xlsheet.Cells(i + 7, 17) = CNumber(@delta1_sumcharges)
xlsheet.Cells(i + 7, 19) = CNumber(@delta1_sumcharges)





???delta1?

use dormitory
go
create proc pr_delta1sumwatercharges
@month char(6),@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_sumwatercharges money output
as
if @area_code = 'B' then
Select @delta1_sumwatercharges=Sum(M.WaterCharge)
From Zs_Monthly_Detail M
LEFT JOIN VEmployee V
ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND M.BuildingCode=@building_code
AND M.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
go
else if @area_code = 'c' then
Select @delta1_sumwatercharges=Sum(M.WaterCharge)
From Zs_Monthly_Detail M
LEFT JOIN VEmployee V
ON M.EmpCode=V.EmpCode
Where M.Month=@month
AND M.FactoryCode=@factory_code
AND M.BuildingCode=@building_code
AND M.AreaCode=@area_code
AND V.DeptCode In (@dept_code)
go
end
end

?︽

declare @month char(6),@factory_code nvarchar(6),@building_code nvarchar(6),@area_code nvarchar(6),@dept_code nvarchar(8),@delta1_sumwatercharges money output
exec pr_delta1sumwatercharges'm','delta1','STAFF','B/C','Group(i)',@delta1_sumwatercharges output
select @delta1_sumwatercharges
go
xlsheet.Cells(i + 7, 18) = CNumber(@delta1_sumwatercharges)
xlsheet.Cells(i + 7, 20) = CNumber(@delta1_sumwatercharges)