1 USE [HLT_HIEP_V2]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[master_querySupplierCertificate]    Script Date: 06/08/2013 10:54:22 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER procedure [dbo].[master_querySupplierCertificate]
  9     @OrganizationDomain          NVARCHAR(20),    
 10     @PartnerId                nvarchar(20),
 11     @CertificateId            nvarchar(20),
 12     @CertificateName          nvarchar(80),
 13     @CertificateType          int,
 14     @StartValidDate           datetime,
 15     @EndValidDate             datetime,
 16     @CertificatePhoto         nvarchar(128),    
 17     @Markers                  int,    
 18     @StartCreateDate          datetime,
 19     @EndCreateDate              datetime,
 20     @PageIndex                  int,
 21     @PageSize                  int,
 22     @SortField                  nvarchar(20),
 23     @SortMethod                  nvarchar(20)    
 24 as
 25 begin
 26     DECLARE @PageLowerBound int
 27     DECLARE @PageUpperBound int
 28     DECLARE @TotalRecords   int
 29     SET @PageLowerBound = @PageSize * @PageIndex
 30     SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
 31     --设置排序字段和规则
 32     IF(@SortField IS NULL OR @SortMethod IS NULL OR @SortField='' OR @SortMethod='')
 33     BEGIN
 34         SET @SortField = 'PartnerId'
 35         SET @SortMethod = 'DESC'
 36     END
 37     --拼SQL 按条件排序查询
 38     DECLARE @SqlStr nvarchar(1000)
 39     SET @SqlStr=''
 40     
 41     DECLARE @OrganizationProviderId uniqueidentifier
 42      IF(@OrganizationDomain IS NOT NULL AND @OrganizationDomain<>'' AND @OrganizationDomain<>'%' AND @OrganizationDomain<>'%%')  
 43     BEGIN
 44         SELECT @OrganizationProviderId=OrganizationProviderId FROM system_Organizations WHERE OrganizationDomain = @OrganizationDomain
 45         if(@OrganizationProviderId is null) return -1
 46     END
 47     
 48     IF(@OrganizationProviderId IS NOT NULL)  
 49         SET @SqlStr=@SqlStr+'OrganizationProviderId = '''+CONVERT(nvarchar(50), @OrganizationProviderId, 50) + ''' AND ' 
 50     IF(@PartnerId IS NOT NULL AND @PartnerId<>'' AND @PartnerId<>'%' AND @PartnerId<>'%%')  
 51         SET @SqlStr+='PartnerId like ''%'+@PartnerId + '%'' AND ' 
 52     IF(@CertificateId IS NOT NULL AND @CertificateId<>'' AND @CertificateId<>'%' AND @CertificateId<>'%%')  
 53         SET @SqlStr+='CertificateId like ''%'+@CertificateId + '%'' AND ' 
 54     IF(@CertificateName IS NOT NULL AND @CertificateName<>'' AND @CertificateName<>'%' AND @CertificateName<>'%%')  
 55         SET @SqlStr+='CertificateName like ''%'+@CertificateName + '%'' AND '    
 56     IF(@CertificateType IS NOT NULL )  
 57         SET @SqlStr+='CertificateType = '''+@CertificateType + ''' AND '      
 58     IF(@StartValidDate IS NOT NULL)
 59         SET @SqlStr += 'StartValidDate >= '''+CONVERT(varchar(50), @StartValidDate, 21) + ''' AND '     
 60     IF(@EndValidDate IS NOT NULL)  
 61         SET @SqlStr+='EndValidDate <= '''+CONVERT(varchar(50), @EndValidDate, 21) + ''' AND ' 
 62     IF(@CertificatePhoto IS NOT NULL AND @CertificatePhoto<>'' AND @CertificatePhoto<>'%' AND @CertificatePhoto<>'%%')  
 63         SET @SqlStr+='CertificatePhoto like ''%'+@CertificatePhoto + '%'' AND ' 
 64      IF(@Markers IS NOT NULL AND @Markers<>'' AND @Markers<>'%' AND @Markers<>'%%')  
 65         SET @SqlStr+='Markers = '''+@Markers + ''' AND '          
 66     IF(@StartCreateDate IS NOT NULL)
 67         SET @SqlStr += 'CreateDate >= '''+CONVERT(varchar(50), @StartCreateDate, 21) + ''' AND ' 
 68     IF(@EndCreateDate IS NOT NULL)  
 69         SET @SqlStr+='CreateDate <= '''+CONVERT(varchar(50), @EndCreateDate, 21) + ''' AND '     
 70     
 71     IF(RIGHT(@SqlStr,4)='AND ') SET @SqlStr = LEFT(@SqlStr,LEN(@SqlStr)-4)        
 72     IF(@SqlStr<>'') SET @SqlStr=' WHERE '+@SqlStr
 73     SET @SqlStr= 'SELECT OrganizationProviderId,PartnerId,CertificateId FROM master_SupplierCertificate '+@SqlStr    
 74     SET @SqlStr= @SqlStr + ' ORDER BY '+ @SortField + ' ' + @SortMethod
 75     
 76     CREATE TABLE #sortInformation
 77     (
 78         IndexId int IDENTITY (0, 1) NOT NULL,
 79         [OrganizationProviderId]  uniqueidentifier NOT NULL,
 80         PartnerId                 nvarchar(20),
 81         CertificateId             nvarchar(20)
 82     )
 83     INSERT INTO #sortInformation([OrganizationProviderId],PartnerId,CertificateId) 
 84     EXEC(@SqlStr)    
 85     
 86     SET @TotalRecords = @@ROWCOUNT
 87     
 88     SELECT o.OrganizationProviderId
 89       ,o.[PartnerId]
 90       ,o.[CertificateId]
 91       ,[CertificateName]
 92       ,[CertificateType]
 93       ,[StartValidDate]
 94       ,[EndValidDate]
 95       ,[CertificatePhoto]
 96       ,[Remark]
 97       ,[Markers]
 98   FROM [HLT_HIEP_V2].[dbo].[master_SupplierCertificate] AS o, #sortInformation AS s
 99     WHERE    o.OrganizationProviderId=s.OrganizationProviderId 
100     and o.PartnerId = s.PartnerId 
101     and o.CertificateId = s.CertificateId
102     AND  s.IndexId 
103     BETWEEN convert(varchar(10), @PageLowerBound) 
104     and  convert(varchar(10), @PageUpperBound) 
105    order by  @SortField + ' ' + @SortMethod 
106         
107     RETURN @TotalRecords
108 end