-- ***********************************--
--
-- 项目名称:网络端口报名
-- 功能描述:生成报名总表报表
-- 项目开发:Me
-- 时 间:2013年2月20日
-- exec p_ApplyInfoCreate 4
--
-- ***********************************--
ALTER proc [dbo].[p_ApplyInfoCreate]
@StageID Int
as
Set NOCOUNT ON
--获取最多端口个数为列数
Declare @ColumnCount Int
Set @ColumnCount=0
Select @ColumnCount=Max(A.Countx)
From (Select Count(1) Countx From dbo.t_PortApply Where StageID=@StageID and IsDelete=0 Group By OrgCode5,StaffCode) A
--添加端口基本信息
Declare @ColumnStr Nvarchar(Max)
Set @ColumnStr=''
While @ColumnCount>0
Begin
Set @ColumnStr=',网络渠道'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
'版本套餐'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
'端口单价'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
'开通人手机号'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
'端口用户名'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128)'+
+@ColumnStr
Set @ColumnCount=@ColumnCount-1
End
Declare @SqlStr Nvarchar(Max)
Set @SqlStr='
--定义表
If Exists (Select 1 from dbo.sysobjects where ID = object_id(N''[dbo].[t_ApplyTable'+cast(@StageID as varchar(10))+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
Drop Table t_ApplyTable'+cast(@StageID as varchar(10))+'
Create Table t_ApplyTable'+Cast(@StageID As Varchar(10))+' (
序号 Int Identity(1,1),
事业部 Nvarchar(128),
战区 Nvarchar(128),
片区 Nvarchar(128),
分行组别 Nvarchar(128),
姓名 Nvarchar(128),
工号 Nvarchar(128)'+@ColumnStr+')
--插入基本信息
Insert Into t_ApplyTable'+Cast(@StageID As Varchar(10))+'(事业部,战区,片区,分行组别,姓名,工号)
Select Distinct
OrgName1,
OrgName2,
OrgName3,
OrgName5,
StaffName,
StaffCode
From t_PortApply
Where StageID='+Cast(@StageID As Varchar(10))+'
And IsDelete=0
Order By OrgName1,OrgName2,OrgName3,OrgName5
'
exec sp_executesql @SqlStr
--插入端口信息组串
Declare @UpdateStr Nvarchar(Max)
Set @UpdateStr=''
Declare @ApplyTable Table(ID Int Identity(1,1),StaffCode Nvarchar(128))
Insert Into @ApplyTable(StaffCode) Select Distinct StaffCode From t_PortApply WhereStageID=@StageID And IsDelete=0
Declare @RowCount Int
Select @RowCount=count(1) From @ApplyTable
While @RowCount>0
Begin
Declare @StaffCode Nvarchar(128)
Select @StaffCode=StaffCode From @ApplyTable Where ID=@RowCount
--每个人报的产品
Declare @tmpTable Table(ID Int,网络渠道 Nvarchar(128),版本套餐 Nvarchar(128),端口单价 Nvarchar(128),开通人手机号 Nvarchar(128),端口用户名 Nvarchar(128))
Insert Into @tmpTable(ID,网络渠道,版本套餐,端口单价,开通人手机号,端口用户名)
Select ROW_NUMBER()Over(order by ProductName),CanalName,ProductName,RealPrice,PhoneNum,PortName From t_PortApply WhereStaffCode=@StaffCode AndStageID=@StageID And IsDelete=0
--修改端口信息组串
Declare @PortCount Int
Select @PortCount=Count(1) From @tmpTable
While @PortCount>0
Begin
Declare @CanalName Nvarchar(128),@ProductName Nvarchar(128),@PortPrice Nvarchar(128),@PortName Nvarchar(128),@PhoneNum Nvarchar(128)
Select @CanalName=网络渠道,
@ProductName=版本套餐,
@PortPrice=端口单价,
@PhoneNum=开通人手机号,
@PortName=端口用户名
From @tmpTable
Where ID=@PortCount
Set @UpdateStr=' Update t_ApplyTable'+Cast(@StageID As Varchar(10))+' Set 网络渠道'+Cast(@PortCount As Nvarchar(10))+'='''+@CanalName+''',
版本套餐'+Cast(@PortCount As Nvarchar(10))+'='''+@ProductName+''',
端口单价'+Cast(@PortCount As Nvarchar(10))+'='''+@PortPrice+''',
开通人手机号'+Cast(@PortCount As Nvarchar(10))+'='''+@PhoneNum+''',
端口用户名'+Cast(@PortCount As Nvarchar(10))+'='''+@PortName+'''
Where 工号='''+@StaffCode+''''
exec sp_executesql @UpdateStr
Set @PortCount=@PortCount-1
End
Delete From @tmpTable
Set @RowCount=@RowCount-1
End
Declare @SqlStr1 Nvarchar(Max)
Set @SqlStr1='Select * From t_ApplyTable'+cast(@StageID as varchar(10))+''
exec sp_executesql @SqlStr1
SET NOCOUNT OFF