Excel
--if you are running this sp without any parameters then it will perform the export by default for last one month.
--SELECT 'Cost Centre','Dept','Activity Code','JC Analysis Code','Employee Number','Basic Hours/Calls','Basic Amount','Bonus Hours/Calls','Bonus Amount','Holiday Hours/Calls','Holiday Amount','Mileage Value','Expenses Value' UNION ALL SELECT Cost_Centre 'Cost Centre',Dept 'Dept',Activity_Code 'Activity Code',JC_Analysis_Code 'JC Analysis Code',Employee_Number 'Employee Number',Basic_Hours_Calls 'Basic Hours/Calls',Basic_Amount 'Basic Amount',Bonus_Hours_Calls 'Bonus Hours/Calls',Bonus_Amount 'Bonus Amount',Holiday_Hours_Calls 'Holiday Hours/Calls',Holiday_Amount 'Holiday Amount',Mileage_Value 'Mileage Value',Expenses_Value 'Expenses Value' FROM ##TempResults
--DROP TABLE ##TempResults
CREATE PROCEDURE dbo.stpFEPaymentGetList_Excel_ALL_ACTIVITY
(
@fromDate datetime=NULL,
@toDate datetime=NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(8000),
@fileName varchar(50),
@command varchar(8000),
@Script_file_name varchar(150),
@FTP_site varchar(100),
@FTP_user_id varchar(50),
@FTP_password varchar(100),
@FTP_destination varchar(100),
@Source_folder varchar(100),
@Files_to_upload varchar(100),
@finalEmailBody varchar(8000),
@emailServer varchar(100),
@FromEmailID varchar(100),
@ToEmailID varchar(100),
@CcEmailID varchar(100)
CREATE TABLE ##TempResults
(
Cost_Centre varchar(1000),
Dept varchar(1000),
Activity_Code varchar(1000),
JC_Analysis_Code varchar(3),
Employee_Number varchar(1000),
Basic_Hours_Calls varchar(25),
Basic_Amount varchar(25),
Bonus_Hours_Calls varchar(25),
Bonus_Amount varchar(25),
Holiday_Hours_Calls varchar(25),
Holiday_Amount varchar(25),
Mileage_Value varchar(25),
Expenses_Value varchar(25)
)
IF @fromDate IS NULL OR @toDate IS NULL
BEGIN
SET @toDate=GETDATE()
SET @fromDate=DATEADD(Month,-1,@toDate)
END
SELECT @fileName='ActXl'+CAST(DATEPART(millisecond,GETDATE()) AS varchar(4))+'.xls'
SET @Files_to_upload=@fileName
SET @fileName='D:\Websites\Raptor\General\'+@fileName
SET @sql='SELECT CLC.strClientCategory as ''Cost Centre'''
SET @sql=@sql+' ,CLC.strClientCategory as ''Dept'''
SET @sql=@sql+' ,AC.strActivityCode as ''Activity Code'''
SET @sql=@sql+' ,611 as ''JC Analysis Code'''
SET @sql=@sql+' ,tblRPTUser.strUserPMICode AS ''Employee Number'''
SET @sql=@sql+' ,(AC.dcBasicRate-(AC.dcHolidayRate+AC.dcBonus)) as ''Basic Hours/Calls'''
SET @sql=@sql+' ,((AC.dcBasicRate-(AC.dcHolidayRate+AC.dcBonus))* FA.intCompletedCalls) as ''Basic Amount'''
SET @sql=@sql+' ,AC.dcBonus as ''Bonus Hours/Calls'''
SET @sql=@sql+' ,(AC.dcBonus * FA.intCompletedCalls) as ''Bonus Amount'''
SET @sql=@sql+' ,AC.dcHolidayRate as ''Holiday Hours/Calls'''
SET @sql=@sql+' ,(AC.dcHolidayRate * FA.intCompletedCalls) as ''Holiday Amount'''
SET @sql=@sql+' ,cast(round(isnull((FA.dcMiles*AC.dcMilageRate),0),2) as decimal(10,2)) as ''Mileage Value'''
SET @sql=@sql+' ,FA.dcExpenses as ''Expenses Value'''
SET @sql=@sql+' FROM tblrptActivity AC'
SET @sql=@sql+' inner join tblrptcampaign CMP '
SET @sql=@sql+' ON AC.FK_intCampaignID=CMP.PK_intCampaignID '
SET @sql=@sql+' inner JOIN tblrptclient CL'
SET @sql=@sql+' ON CMP.FK_intClientID=CL.PK_intClientID '
SET @sql=@sql+' inner JOIN tblrptmasterclientcategory CLC'
SET @sql=@sql+' ON CL.FK_intClientCategoryID=CLC.PK_intClientCategoryID '
SET @sql=@sql+' INNER JOIN tblRPTFEActivityAllocation FA'
SET @sql=@sql+' on FA.fK_intactivityid = pk_intactivityid'
SET @sql=@sql+' Inner JOIN tblRPTUser'
SET @sql=@sql+' ON tblRPTUser.PK_intUserID = FA.fk_INTFEID'
SET @sql=@sql+' where btClaimApproved=1 AND FA.dtDateOfApproval BETWEEN '''+ CAST(@fromDate AS varchar(12))+''' AND ''' + CAST(@toDate AS varchar(12))+''''
INSERT INTO ##TempResults EXEC(@sql)
SET @sql=''
SET @sql='SELECT ''Cost Centre'',''Dept'',''Activity Code'',''JC Analysis Code'',''Employee Number'',''Basic Hours/Calls'',''Basic Amount'',''Bonus Hours/Calls'',''Bonus Amount'',''Holiday Hours/Calls'',''Holiday Amount'',''Mileage Value'',''Expenses Value'' UNION ALL '
SET @sql=@sql+'SELECT Cost_Centre ''Cost Centre'',Dept ''Dept'',Activity_Code ''Activity Code'',JC_Analysis_Code ''JC Analysis Code'',Employee_Number ''Employee Number'',Basic_Hours_Calls ''Basic Hours/Calls'',Basic_Amount ''Basic Amount'',Bonus_Hours_Calls ''Bonus Hours/Calls'',Bonus_Amount ''Bonus Amount'',Holiday_Hours_Calls ''Holiday Hours/Calls'',Holiday_Amount ''Holiday Amount'',Mileage_Value ''Mileage Value'',Expenses_Value ''Expenses Value'' FROM ##TempResults'
SET @sql='bcp "'+@sql+'" queryout "'+@fileName+'" -UWebAppUser -PPlA123magic -c -CRAW'
SET @sql=@sql+' -S'+@@SERVERNAME
EXEC master..xp_cmdshell @sql
DROP TABLE ##TempResults
--Initializing FTP command variables
SET @Script_file_name='D:\\Websites\Raptor\unilever\FTPScripts\ScriptaCTIVITY.txt'
SET @FTP_site='84.252.241.203'
SET @FTP_user_id='Greenland'
SET @FTP_password='Land12345'
SET @FTP_destination='/webspiders/raptor/Wagesxls'
SET @Source_folder='D:\Websites\Raptor\General'
SET @command='echo OPEN '+@FTP_site+'> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo '+@FTP_user_id+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo '+@FTP_password+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo cd "'+@FTP_destination+'">> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo lcd "'+@Source_folder+'">> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo binary>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo put '+@Files_to_upload+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo quit>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='ftp.exe -i -s:"'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
end
--Send email, notifying that export has been completed
SET @emailServer='192.168.16.12'
SET @FromEmailID='wagesxls@pmifm.co.uk'
SET @ToEmailID='nitin@webspiders.com'
SET @CcEmailID='ritwik.sanyal@webspiders.com;swaty.dalmia@webspiders.com'
SET @finalEmailBody='Dear Administrator,
Activities are exported to excel, please visit the following link to get the file: -
Please click here to get the file'
SET @finalEmailBody=@finalEmailBody+'
Regards,
PMIFM Raptor'
EXEC SendEmail 'Activities exported to excel: Please get it by visiting the following link',@finalEmailBody,
@emailServer,@FromEmailID,@ToEmailID,@CcEmailID
GO
posted by Soumalyade @ 6:32 AM
