SQLSERVER Stored Procedure Dynamic Query Pivot Table DateTime parameter

 USE [TNG_KMS]  
 GO  
 /****** Object: StoredProcedure [dbo].[SP_S_GetCompletedInjectionReport]  Script Date: 18/7/2016 10:52:36 AM ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 DROP PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]  
 GO  
 CREATE PROCEDURE [dbo].[SP_S_GetCompletedInjectionReport]  
 --(       
 --     -- Add the parameters for the stored procedure here  
      @dt_DateFrom datetime ,   
      @dt_DateTo datetime  
 --)  
 AS  
 BEGIN  
      -- SET NOCOUNT ON added to prevent extra result sets from  
      -- interfering with SELECT statements.  
      SET NOCOUNT ON;  
      SET FMTONLY OFF;  
   -- Insert statements for procedure here  
 DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)  
 DECLARE @ColumnName AS NVARCHAR(MAX)  
 SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(name)  
 FROM (select name from LookupItems where CategoryID=4) AS AppSector  
 ORDER BY name  
 SET @DynamicPivotQuery =  
 'SELECT * FROM (  
   select   
      @@ROWCOUNT No,      FormRefNo,   
      ISNULL(TotalSam,0) BigTotalSam,   
      R.CreatedTime,R.CompletionDate,  
       Datediff(dd,CONVERT(Date,R.CreatedTime),R.CompletionDate) ''DaysCompleted'',  
      LI.Name [AppSector]  
      from   
       [TNG_KMS].[dbo].[Request] R,   
       [TNG_KMS].[dbo].[ProfileGroup] PG,  
       [TNG_KMS].[dbo].[LookupItems] LI  
       where R.ProfileGroupID=PG.ID  
       and PG.AppSectorID=LI.ItemIndex   
       and LI.CategoryID = 4  
       and R.RequestStatusID=5  
       and (R.CompletionDate between @dt_DateFrom and @dt_DateTo)  
 ) as s  
 PIVOT  
 (  
   SUM(BigTotalSam) FOR [AppSector] IN (' +@ColumnName+ ')  
 ) AS pvt'  
 ;  
 --Execute the Dynamic Pivot Query  
 EXEC sp_executesql @DynamicPivotQuery,        
                          N'@dt_DateFrom DATETIME, @dt_DateTo DATETIME',  
                          @dt_DateFrom,   
                          @dt_DateTo;   
 END  
 GO  

Login: SPRING Security 3.0.5 & Stored Procedure

Masalah: login gunakan SPRING Security + Stored Procedure (custom authentication)

Mencabar gak task ni. Sebab dah biasa gunakan config login SPRING Security & LDAP.

Work in progress:

• cuba guna custom beans + custom filter.
• daripada docs suruh guna custom AuthenticationProvider

Kajian & rujukan:

• Beans Go Crazy: Custom Authentication using Spring Security
• http://stackoverflow.com/questions/16104228/how-implement-spring-security-when-login-page-having-more-field-apart-from-user
• melalui jawapan yang diberi, mungkin boleh modify userdetails, cuma ini framework SPRING Security 2.0
• http://www.integratingstuff.com/2011/04/30/migrating-from-spring-security-2-to-spring-security-3/
• Implement custom AuthenticationProvider in Spring Security 2.06
• http://danielkaes.wordpress.com/2013/02/20/custom-authentication-provider-in-spring/, the most relevant info for my problems. What a shame, never found this post before, Google should be blamed for it! ***Working on this solution***
• masalah dgn bean la pulak!!!
• http://www.cleancode.co.nz/blog/937/customization-spring-security-authentication; tak sempat baca lagi.