SQLSERVER bcp【master..xp_cmdshell】

-- =============================================
-- Author: <lxh>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
--EXEC [dbo].[Proc_ToExcel_QuotaFix] '2017-01-01','2017-03-31','测试'
--存储过程: [dbo].[Proc_ToExcel_QuotaFix]
--功能名称: 固定费用审批数按额度审批所属年月
--输入参数: @bdate @edate

CREATE PROCEDURE [dbo].[Proc_ToExcel_QuotaFix]
(
@bdate NVARCHAR(10),
@edate NVARCHAR(10),
@fullFileName NVARCHAR(3000) OUTPUT
) AS
--PRINT @bdate
--PRINT @edate
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @sql VARCHAR(4000)
DECLARE @fileName NVARCHAR(300)


--DECLARE @bdate NVARCHAR(300)
--DECLARE @edate NVARCHAR(300)
SET @fileName= REPLACE(REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 25),':','-'),' ','-'),'.','-')
set @fullFileName='\\192.168.2.1\IIS_Store\TempExcel\固定费用审批数按额度审批所属年月'+@fileName+'.xls'

SET @sql='bcp " with t as ( '
SET @sql=@sql+'select q.Stf_ID ,( '
SET @sql=@sql+'select stf_name from Basic_Staffer where Stf_ID=q.Stf_ID) 人员, '
SET @sql=@sql+' '
SET @sql=@sql+'( '
SET @sql=@sql+'select bpn_Name from Basic_PostName where BPN_ID=( '
SET @sql=@sql+'select top 1 SnPost_PostID from Stat_SnAgnStfPost where SnPost_Year =datepart(yy,DATEADD(q,DATEDIFF(q,0,(q.Qta_FAYear+''-''+q.Qta_FAMonth+''-1''))-1,0)) and '
SET @sql=@sql+'SnPost_Season =datepart(q,DATEADD(q,DATEDIFF(q,0,(q.Qta_FAYear+''-''+q.Qta_FAMonth+''-1''))-1,0)) and Stf_ID=q.Stf_ID '
SET @sql=@sql+')) 上季度定岗, '
SET @sql=@sql+'( '
SET @sql=@sql+'select bpn_Name from Basic_PostName where BPN_ID=( '
SET @sql=@sql+'select top 1 SnPost_PostID from Stat_SnAgnStfPost where SnPost_Year =2016 and '
SET @sql=@sql+'SnPost_Season =DATEPART(q,(q.Qta_FAYear+''-''+q.Qta_FAMonth+''-1'')) and Stf_ID=q.Stf_ID '
SET @sql=@sql+')) [当时季度定岗], '
SET @sql=@sql+'( '
SET @sql=@sql+'select bpn_Name from Basic_PostName where BPN_ID=( '
SET @sql=@sql+'select top 1 SnPost_PostID from Stat_SnAgnStfPost where SnPost_Year =datepart(yy,DATEADD(q,DATEDIFF(q,0,(q.Qta_FAYear+''-''+q.Qta_FAMonth+''-1''))+1,0)) and '
SET @sql=@sql+'SnPost_Season =datepart(q,DATEADD(q,DATEDIFF(q,0,(q.Qta_FAYear+''-''+q.Qta_FAMonth+''-1''))+1,0)) and Stf_ID=q.Stf_ID '
SET @sql=@sql+')) 下季度定岗, '
SET @sql=@sql+' '
SET @sql=@sql+'(select Post_Name from Basic_Post where post_id=s.Post_id and Agn_ID=s.Agn_ID) 现在岗位, '
SET @sql=@sql+'q.FSub_ID , t. fsub_name, CONVERT(NVARCHAR,qta_fayear) as 额度年, CONVERT(NVARCHAR,qta_famonth) as 额度月 , CONVERT(NVARCHAR,YEAR(auditdate))as 审批年 , CONVERT(NVARCHAR,month(auditdate))as 审批月 , '
SET @sql=@sql+'CONVERT(NVARCHAR,QtaAmount) 审批金额 from Quota_Staff q with (nolock) , '
SET @sql=@sql+'Basic_Staffer s, '
SET @sql=@sql+'Basic_FeeSubject t '
SET @sql=@sql+'where q. FSub_ID =t.FSub_ID '
SET @sql=@sql+'and q.Stf_ID=s.Stf_ID '
SET @sql=@sql+'and q.FSub_ID like ''aa%'' and q.FSub_ID not in (''AAABBX'',''AAABCA'') '
SET @sql=@sql+'and ProcStat =''正常'' '
SET @sql=@sql+'and FSub_AlaisName <>''人力成本'' '
--SET @sql=@sql+'and CONVERT(DATE,AuditDate) BETWEEN ''2016-1-1'' AND ''2016-12-31'' '
SET @sql=@sql+'and left(CONVERT(date,AuditDate),10) >= '''+@bdate+''' '
SET @sql=@sql+' AND left(CONVERT(date,AuditDate),10) <= '''+@edate+''' '
SET @sql=@sql+') '

SET @sql=@sql+'select t=0, '
SET @sql=@sql+'Stf_ID=''Stf_ID'',人员=''人员'', '
SET @sql=@sql+'岗位=''岗位'', '
SET @sql=@sql+'FSub_ID=''FSub_ID'', '
SET @sql=@sql+'FSub_Name=''FSub_Name'', '
SET @sql=@sql+'额度年=''额度年'', '
SET @sql=@sql+'额度月=''额度月'', '
SET @sql=@sql+'审批年=''审批年'', '
SET @sql=@sql+'审批月=''审批月'', '
SET @sql=@sql+'审批金额=''审批金额'' '
SET @sql=@sql+'UNION all '
SET @sql=@sql+'select t=1,'
SET @sql=@sql+'Stf_ID,人员, '
SET @sql=@sql+'case when 当时季度定岗 is not null then 当时季度定岗 '
SET @sql=@sql+'when 当时季度定岗 IS null and 上季度定岗 is not null then 上季度定岗 '
SET @sql=@sql+'when 上季度定岗 IS null and 下季度定岗 is not null then 下季度定岗 '
SET @sql=@sql+'else 现在岗位 '
SET @sql=@sql+'end as 岗位, '
SET @sql=@sql+'FSub_ID, '
SET @sql=@sql+'FSub_Name, '
SET @sql=@sql+'额度年, '
SET @sql=@sql+'额度月, '
SET @sql=@sql+'审批年, '
SET @sql=@sql+'审批月, '
SET @sql=@sql+'审批金额 '
SET @sql=@sql+'from t '
SET @sql=@sql+' "'

--PRINT @sql

--DECLARE @a NVARCHAR(4000)

SET @sql=@sql+' queryout '+@fullFileName+' -c -S201.168.2.1 -ULXHToExcel -PLXH@lxh'


EXEC master..xp_cmdshell @sql


END

 

-- =============================================
-- Author: <lxh>
-- Create date: <2017-07-14>
-- Description: <实际收入导出>
-- =============================================
--EXEC [dbo].[Proc_ToExcel_SalarySys_StfSnActIncome] '2016','4','2017','1','实际收入导出'
--参数:@years:开始年，@seasons:开始季度，@yeare:结束年，@seasone:结束季度
CREATE PROCEDURE [dbo].[Proc_ToExcel_SalarySys_StfSnActIncome]
(
--@bdate NVARCHAR(10),
--@edate NVARCHAR(10),
@years INT ,
@seasons INT,
@yeare INT,
@seasone INT,
@fullFileName NVARCHAR(3000) OUTPUT
) AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @sql VARCHAR(4000)
DECLARE @fileName NVARCHAR(300)

SET @fileName= REPLACE(REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 25),':','-'),' ','-'),'.','-')
set @fullFileName='\\192.168.2.1\IIS_Store\TempExcel\实际收入导出'+@fileName+'.xls'


-- DECLARE @years NVARCHAR(10)
-- DECLARE @seasons NVARCHAR(10)
-- DECLARE @yeare NVARCHAR(10)
-- DECLARE @seasone NVARCHAR(10)

--SET @years=2016
--SET @seasons=4
--SET @yeare=2017
--SET @seasone=1

DELETE FROM tiger.toexcel_SalarySys_StfSnActIncome
INSERT INTO tiger.toexcel_SalarySys_StfSnActIncome
SELECT Agn_ID, ActIncm_SmallAgn ,
大区=(select agn_name_lv1 from dbo.View_BasicAgnLevel where agn_id=ActIncm_SmallAgn ),
省区 =(select agn_name from Basic_Agency a where a.Agn_ID = i.agn_id),
地区 =(select agn_name from Basic_Agency a where a.Agn_ID = i.ActIncm_SmallAgn),
ActIncm_Year as 年 , ActIncm_Season as 季度,ActIncm_Month as 月,Stf_ID,
ActIncm_TaskScoreContr as 工作任务分 , ActIncm_FnshScoreContr as 完成任务分,
stf_name =(select stf_name from basic_staffer s where s.Stf_ID =i.stf_id), ActIncm_Post
,ActIncm_PostType, ActIncm_SkillIncome as 技能收入, ActIncm_TaskIncomeQta as 考核收入额度 ,
ActIncm_MktAdjIncomeQta as 市场调整收入额度,ActIncm_FnshProp as 完成率,ActIncm_FnshPropAdj as 考核完成率,
人为考核完成率=(select FPropAdj_AprProp from SalarySys_FnshPropAdjustApp p where p.Stf_ID=i.stf_id
and FPropAdj_Year=i.ActIncm_year and p.FPropAdj_Month=i. ActIncm_Month and FPropAdj_Mark='认可') ,
ActIncm_TaskIncome as 实际任务业绩收入 , ActIncm_MktAdjIncome as 实际调整业绩收入额度 ,
ActIncm_OrpDate,(select sum(isnull(SnAllowDtl_Amount,0)) From SalarySys_SnAllowanceAppDetl d
where SnAllow_ID in(select SnAllow_ID from SalarySys_SnAllowanceApp p where p.Stf_ID=i.stf_Id
and SnAllow_Mark='总部认可')and d.SnAllowDtl_Year=i.ActIncm_Year and
d.SnAllowDtl_Month=i.ActIncm_Month) 特殊补贴额度 ,
(select sum(app.Qta_FESum) from Quota_FixApp app where FSub_ID='AAABBX'
and app.Qta_FAYear=i.ActIncm_Year and app.Qta_FAMonth =i.ActIncm_Month
and ProcMark='认可' and app.Stf_ID=i.Stf_ID ) 业务补贴审批额度 ,(select top 1 FnshP_FnshPropSpAllow from
SalarySys_StfFnshProp where FnshP_Year=i.ActIncm_Year and FnshP_Season =i.ActIncm_Season
and FnshP_Status in ('提交','保存') and Stf_ID=i.Stf_ID )'特殊补贴总完成率来自完成表',
ActIncm_SpAProp '特殊补贴总完成率来自实际收入表',
(select AllowProp_OrgProp from SalarySys_SpAllowAdjustApp where AllowProp_Mark='认可'
and AllowProp_Year=i.ActIncm_Year AND AllowProp_Season=i.ActIncm_Season AND Stf_ID=i.Stf_ID)
as '原始特殊补贴率认可',(select AllowProp_AprProp from SalarySys_SpAllowAdjustApp WHERE
AllowProp_Mark='认可'and AllowProp_Year=i.ActIncm_Year AND AllowProp_Season=i.ActIncm_Season
and Stf_ID=i.Stf_ID) '审批特殊补贴率认可', ActIncm_SpAllowance as 实际特殊补贴,
最低补贴额度=(select top 1 SnIncome_LmtAllowance from SalarySys_StfSnPostIncome s----*******
where s.stf_id =i.Stf_ID and s.SnIncome_Season =i. ActIncm_Season and
i.ActIncm_Year =s.SnIncome_Year AND i.ActIncm_Season =s.SnIncome_Season and SnIncome_Mark='提交' ) , -------
ActIncm_LmtAllowance as 实际最低补贴,
ActIncm_ActTaskIncome as 实际奖金, ActIncm_IncmAdj as 业绩收入超高部分,
(select DISTINCT ActIncm_IncmAdj from SalarySys_StfSnActIncome t where
t.ActIncm_Year =datepart(yy,DATEADD(q,DATEDIFF(q,0,(i.ActIncm_Year+'-'+i.ActIncm_Month +'-1'))-1,0)) and
t.ActIncm_Season =datepart(q,DATEADD(q,DATEDIFF(q,0,(i.ActIncm_Year+'-'+i.ActIncm_Month+'-1'))-1,0)) AND
t.Stf_ID=i.Stf_ID )上季度业绩收入超高部分, ActIncm_IncmAdjDbl 业绩收入翻倍调整数 , ActIncm_ActIncmTotal as 实际工资奖金合计 ,
入职日期 =(select stf_employdate from Basic_Staffer s where s.Stf_ID =i.stf_id ),
离职日期 =(select Stf_DismissDate from Basic_Staffer s where s.Stf_ID =i.stf_id ),
在职状态 =(select s.Stf_Status from Basic_Staffer s where s.Stf_ID =i.stf_id ),
附属状态 =(select s.bas_otherstatus from Basic_Staffer s where s.Stf_ID =i.stf_id ),
任务贡献分=(select SUM(FnshP_TaskScoreContr) from SalarySys_StfFnshProp p where p.Stf_ID =i.Stf_ID and p.FnshP_Season=i.ActIncm_Season
and p.FnshP_Year=i.ActIncm_Year),
完成贡献分=(select SUM(FnshP_FnshScoreContr) from SalarySys_StfFnshProp p where p.Stf_ID =i.Stf_ID and p.FnshP_Season=i.ActIncm_Season
and p.FnshP_Year=i.ActIncm_Year) ,ActIncm_Status
FROM SalarySys_StfSnActIncome i
where ActIncm_Year*12+ActIncm_Season BETWEEN @years*12+@seasons AND @yeare*12+@seasone
AND ActIncm_Status in ('保存','提交')


SET @sql='bcp "select t=0, Agn_ID=''Agn_ID'', ActIncm_SmallAgn=''ActIncm_SmallAgn'',大区=''大区'', 省区=''省区'',地区=''地区'',年=''年'',季度=''季度'', 月=''月'' '
SET @sql=@sql+' , Stf_ID=''Stf_ID'', 工作任务分=''工作任务分'', 完成任务分=''完成任务分'',stf_name=''stf_name'',ActIncm_Post=''ActIncm_Post'',ActIncm_PostType=''ActIncm_PostType'', 技能收入=''技能收入'' '
SET @sql=@sql+' , 考核收入额度=''考核收入额度'', 市场调整收入额度=''市场调整收入额度'', 完成率=''完成率'',考核完成率=''考核完成率'',人为考核完成率=''人为考核完成率'',实际任务业绩收入=''实际任务业绩收入'', 实际调整业绩收入额度=''实际调整业绩收入额度'' '
SET @sql=@sql+' , ActIncm_OrpDate=''ActIncm_OrpDate'', 特殊补贴额度=''特殊补贴额度'', 业务补贴审批额度=''业务补贴审批额度'',特殊补贴总完成率来自完成表=''特殊补贴总完成率来自完成表'',特殊补贴总完成率来自实际收入表=''特殊补贴总完成率来自实际收入表'',原始特殊补贴率认可=''原始特殊补贴率认可'', 审批特殊补贴率认可=''审批特殊补贴率认可'' '
SET @sql=@sql+' , 实际特殊补贴=''实际特殊补贴'', 最低补贴额度=''最低补贴额度'', 实际最低补贴=''实际最低补贴'',实际奖金=''实际奖金'',业绩收入超高部分=''业绩收入超高部分'',上季度业绩收入超高部分=''上季度业绩收入超高部分'', 业绩收入翻倍调整数=''业绩收入翻倍调整数'' '
SET @sql=@sql+' , 实际工资奖金合计=''实际工资奖金合计'', 入职日期=''入职日期'', 离职日期=''离职日期'',在职状态=''在职状态'',附属状态=''附属状态'',任务贡献分=''任务贡献分'', 完成贡献分=''完成贡献分'' ,ActIncm_Status=''ActIncm_Status'' '
SET @sql=@sql+' union all '
SET @sql=@sql+'SELECT '
SET @sql=@sql+' t=1, '
SET @sql=@sql+' Agn_ID,ActIncm_SmallAgn,大区,省区,地区,CONVERT(NVARCHAR,年)年,CONVERT(NVARCHAR,季度)季度,CONVERT(NVARCHAR,月)月,CONVERT(NVARCHAR,Stf_ID)stf_Id,CONVERT(NVARCHAR,工作任务分)工作任务分 '
SET @sql=@sql+' ,CONVERT(NVARCHAR,完成任务分)完成任务分,stf_name,CONVERT(NVARCHAR,ActIncm_Post)ActIncm_Post,ActIncm_PostType,CONVERT(NVARCHAR,技能收入)技能收入,CONVERT(NVARCHAR,考核收入额度)考核收入额度,CONVERT(NVARCHAR,市场调整收入额度)市场调整收入额度 '
SET @sql=@sql+' ,CONVERT(NVARCHAR,完成率)完成率,CONVERT(NVARCHAR,考核完成率)考核完成率,CONVERT(NVARCHAR,人为考核完成率)人为考核完成率,CONVERT(NVARCHAR,实际任务业绩收入)实际任务业绩收入,CONVERT(NVARCHAR,实际调整业绩收入额度)实际调整业绩收入额度,CONVERT(VARCHAR(10), ActIncm_OrpDate, 21) 操作日期,CONVERT(NVARCHAR,特殊补贴额度)特殊补贴额
度 '
SET @sql=@sql+' ,CONVERT(NVARCHAR,业务补贴审批额度)业务补贴审批额度,CONVERT(NVARCHAR,特殊补贴总完成率来自完成表)特殊补贴总完成率来自完成表,CONVERT(NVARCHAR,特殊补贴总完成率来自实际收入表)特殊补贴总完成率来自实际收入表,CONVERT(NVARCHAR,原始特殊补贴率认可) 原始特殊补贴率认可 '
SET @sql=@sql+' ,CONVERT(NVARCHAR,审批特殊补贴率认可)审批特殊补贴率认可,CONVERT(NVARCHAR,实际特殊补贴)实际特殊补贴,CONVERT(NVARCHAR,最低补贴额度)最低补贴额度,CONVERT(NVARCHAR,实际最低补贴)实际最低补贴,CONVERT(NVARCHAR,实际奖金)实际奖金,CONVERT(NVARCHAR,业绩收入超高部分)业绩收入超高部分,CONVERT(NVARCHAR,上季度业绩收入超高部分)上季度业绩收入超高部分 '

SET @sql=@sql+' ,CONVERT(NVARCHAR,业绩收入翻倍调整数)业绩收入翻倍调整数,CONVERT(NVARCHAR,实际工资奖金合计)实际工资奖金合计,convert(varchar(10),入职日期,21)入职日期,convert(varchar(10),离职日期,21)离职日期,在职状态,附属状态,CONVERT(NVARCHAR,任务贡献分)任务贡献分,CONVERT(NVARCHAR,完成贡献分)完成贡献分,ActIncm_Status '
SET @sql=@sql+' FROM tiger.toexcel_SalarySys_StfSnActIncome "'

--SELECT Agn_ID,ActIncm_SmallAgn,大区,省区,地区,年,季度,月,Stf_ID,工作任务分
--,完成任务分,stf_name,ActIncm_Post,ActIncm_PostType,技能收入,考核收入额度,市场调整收入额度
--,完成率,考核完成率,人为考核完成率,实际任务业绩收入,实际调整业绩收入额度,ActIncm_OrpDate,特殊补贴额度
--,业务补贴审批额度,特殊补贴总完成率来自完成表,特殊补贴总完成率来自实际收入表,原始特殊补贴率认可
--,审批特殊补贴率认可,实际特殊补贴,最低补贴额度,实际最低补贴,实际奖金,业绩收入超高部分,上季度业绩收入超高部分
--,业绩收入翻倍调整数,实际工资奖金合计,入职日期,离职日期,在职状态,附属状态,任务贡献分,完成贡献分,ActIncm_Status
-- FROM tiger.toexcel_SalarySys_StfSnActIncome



PRINT @sql

---DECLARE @a NVARCHAR(4000)

SET @sql=@sql+' queryout '+@fullFileName+' -c -S192.168.2.1 -UlxhToExcel -Plxh@lxh'


EXEC master..xp_cmdshell @sql


END