SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO





ALTER  proc up_CustomerDirectoryReport_Select
	/* Queries the database to build the Custom Directory Report */

	@State nvarchar(500)=null,
	@Counties nvarchar(1000)=null,
	@Specialty nvarchar(200)=null,
	@Zip1 nvarchar(3)=null,
	@Zip2 nvarchar(3)=null

as
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER  OFF
	Declare @SQL1 nvarchar(4000)
	Declare @SQL2 nvarchar(4000)
	Declare @SQL3 nvarchar(4000)
        Declare @SQLWHERE nvarchar(4000)
        Declare @SQLOrderBY nvarchar(2000)

	if @State IS NOT NULL AND Len(@State) <> 0
          if @State <> "'ALL'"
	    Set @SQLWHERE = @SQLWHERE + N'and l.LState in (' + @State + ') '

	if @Counties IS NOT NULL AND Len(@Counties) <> 0
	  if @Counties <> "'ALLCOUNTIES'"
	    Set @SQLWHERE = @SQLWHERE + N'and l.LCounty in (' + @Counties + ') '
	
	if @Specialty IS NOT NULL
	  Set @SQLWHERE = @SQLWHERE + N'and s.SpecCode in (' + @Specialty + ') '

	if @Zip1 IS NOT NULL
	  Set @SQLWHERE = @SQLWHERE + N'and left(l.LZip,3) between ' + @Zip1 + ' and ' + @Zip2 + ' '

	Set @SQLWHERE = @SQLWHERE + N'and plsl.ListInDirectory = 1 and plsl.EffDate <= getdate() and (plsl.DelDate = null or plsl.DelDate > getdate()) '
        Set @SQLOrderBY = N'order by 13,11,1,3,12,4,6,8,9,10,2 '

set @SQL1 = N'Select Distinct 
	s.Description, 
	s.Description2, 
	l.LCounty, 
	p.PLast + space(1) + p.PFirst + space(1) +  p.PTitle AS Name, 
	OfficeName=case 
		when lt.directoryname is not null then lt.directoryname
		when lt.SuppressOfficeName = 0 AND lt.DirectoryName Is NULL then l.LOfficeName
		when lt.SuppressOfficeName = 1 AND lt.DirectoryName Is NULL then '''' end,
	lt.SuppressOfficeName, 
	lt.DirectoryName, 
	l.LAdd1 + space(1) + 
	l.LAdd2 AS Add1, 
	l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3, 
	lt.Phone, 
	s.DisplayOrder AS Sortorder,
	l.LCity,
	tblStateAbb.State
	        FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls 
                      ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN 
                     (tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID) 
                      ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
			where 1=1 ' + @SQLWHERE


/*use a case statement instead of a union of three different queries...
        Set @SQL1 = N'Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +  
		p.PTitle AS Name, lt.DirectoryName AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName, 
		l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3, 
		lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
	        FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls 
                      ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN 
                     (tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID) 
                      ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
			where (lt.DirectoryName Is Not NULL) ' + @SQLWHERE
	        
        Set @SQL2 = N'UNION Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +  
		p.PTitle AS Name, l.LOfficeName AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName, 
		l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3, 
		lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
	        FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls 
                      ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN 
                     (tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID) 
                      ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
	        where lt.SuppressOfficeName = 0 AND lt.DirectoryName Is NULL ' + @SQLWHERE

        Set @SQL3 = N'UNION Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +  
		p.PTitle AS Name, "" AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName, 
		l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3, 
		lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
	        FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls 
                      ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN 
                     (tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID) 
                      ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
	        where lt.SuppressOfficeName = 1 AND lt.DirectoryName Is NULL ' + @SQLWHERE
*/

	

--print @SQL1 + @SQL2 + @SQL3 + @SQLOrderBY
--EXECUTE(@SQL1 + @SQL2 + @SQL3 + @SQLOrderBY)

--print @SQL1 + @SQLOrderBY

	EXECUTE(@SQL1 + @SQLOrderBY)








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO