use ResWare
if (objectProperty(object_id('GetPartnerCompanies'), 'IsProcedure') is not null)
      drop procedure dbo.GetPartnerCompanies
GO

CREATE PROCEDURE dbo.GetPartnerCompanies
	@FileID int,
	@ActionListDefID int = -1,
	@ReturnDisabled bit,
	@ReturnClients bit,
	@ReturnAllNonApproved bit,
	@ReturnExistingPartners bit,
	@ReturnOnlyApproved bit,
	@LastName varchar(80) = NULL,
	@FirstName varchar(80) = NULL,
	@DoingBusinessIn bit,
	@CountyID int = NULL,
	@State varchar(4) = NULL,
	@Override bit,
	@ClientID int,
	@CompanyName varchar(80) = NULL,
	@CompanyNameID int = NULL,
	@CompanyAbbr varchar(20) = NULL,
	@PartnerCompanyID int = NULL,
	@ParentSearch bit,
	@IgnoreDecliningPartner bit,
	@PartnerTypeID int = -1,
	@TitleCompanies varchar(200)		-- Comma delimited list of IDs
AS

DECLARE @OurError INT
/*******************************************************************
* PROCEDURE: GetPartnerCompanies
* PURPOSE:   Search on partner companies
* CALLED FM: PartnerCompanySearchDAO
********************************************************************/

SET NOCOUNT ON

DECLARE @FileActionListDefID int
DECLARE @ActionListTransactionTypeID int

IF @FileID > 0
BEGIN
	SET @ActionListTransactionTypeID = (SELECT ActionListTransactionTypeID
	  FROM FileMain FM
	 WHERE FM.FileID = @FileID)
	SET @FileActionListDefID = (SELECT ActionListOrigID
	  FROM FileMain FM
	 WHERE FM.FileID = @FileID)
END
ELSE
BEGIN
	SET @ActionListTransactionTypeID = NULL
	SET @FileActionListDefID = @ActionListDefID
END

SELECT PC.Approved,
       ISNULL((SELECT PS2.Name FROM PriorityStatus PS2 WHERE PS2.PriorityStatusID = MIN(PS.PriorityStatusID)), 'None') AS PriorityStatus,
       FM.FileNumber AS [File Number],
       PT.Name AS PartnerType, PC.Name,
       ISNULL(PC.City, '')
          + (CASE WHEN PC.City IS NOT NULL AND PC.State IS NOT NULL THEN ', ' ELSE '' END)
          + ISNULL(PC.State,'') AS Location,
       ISNULL(PC.Street1, '')
          + (CASE WHEN PC.Street1 IS NOT NULL AND PC.Street2 IS NOT NULL THEN ', ' ELSE '' END)
          + ISNULL(PC.Street2,'') AS Address,
       PC.PartnerCompanyID, GU.Enabled, PCPTR.PartnerTypeID,
       ISNULL(MAX(PCountyPTR.Weight), ISNULL(MAX(PCTTR.Weight), ISNULL(MAX(PCDR.Weight), MAX(PCPTR.Weight)))) AS Weight
  FROM PartnerCompany PC
  JOIN GeneralUser GU ON PC.PartnerCompanyID = GU.UserID AND (@ReturnDisabled = 1 OR GU.Enabled = 1)
  JOIN PartnerCompanyPartnerTypeRel PCPTR ON PC.PartnerCompanyID = PCPTR.PartnerCompanyID AND (@ReturnClients = 1 OR PCPTR.PartnerTypeID != 1)
  JOIN PartnerType PT ON PCPTR.PartnerTypeID = PT.PartnerTypeID
  LEFT JOIN (FilePartnerRel FPR JOIN FileMain FM ON FM.FileID = FPR.FileID AND @ReturnAllNonApproved = 1) ON FPR.PartnerCompanyID = PC.PartnerCompanyID AND PC.Approved = 0
  LEFT JOIN PartnerEmployee PE ON PC.PartnerCompanyID = PE.PartnerCompanyID
                              AND (PE.LastName LIKE @LastName OR PE.FirstName LIKE @FirstName)
  LEFT JOIN GeneralUser GU2 ON PE.PartnerEmployeeID = GU2.UserID
  LEFT JOIN ActionListDef ALD ON ALD.ActionListDefID = @ActionListDefID
  LEFT JOIN County C ON C.State = @State AND (C.CountyID = @CountyID OR @CountyID IS NULL)
  LEFT JOIN FileMain FM2 ON FM2.FileID = @FileID
  LEFT JOIN PartnerCountyDefaultRel PCDR ON PCDR.PartnerCompanyID = PC.PartnerCompanyID
                                        AND PCDR.PartnerTypeID = PCPTR.PartnerTypeID
                                        AND PCDR.CountyID = C.CountyID
  LEFT JOIN PartnerCountyTransactionTypeRel PCTTR ON PCTTR.PartnerCompanyID = PC.PartnerCompanyID
                                                 AND PCTTR.PartnerTypeID = PCPTR.PartnerTypeID
                                                 AND (PCTTR.ActionListTransactionTypeID = FM2.ActionListTransactionTypeID OR FM2.ActionListTransactionTypeID IS NULL)
                                                 AND PCTTR.CountyID = C.CountyID
  LEFT JOIN PartnerCountyProductTypeRel PCountyPTR ON PCountyPTR.PartnerCompanyID = PC.PartnerCompanyID
                                                  AND PCountyPTR.PartnerTypeID = PCPTR.PartnerTypeID
                                                  AND (PCountyPTR.ActionListTransactionTypeID = FM2.ActionListTransactionTypeID OR FM2.ActionListTransactionTypeID IS NULL)
                                                  AND (PCountyPTR.ActionListProductTypeID = FM2.ActionListProductTypeID OR FM2.ActionListProductTypeID IS NULL)
                                                  AND PCountyPTR.CountyID = C.CountyID
  LEFT JOIN PriorityStatus PS ON PS.PriorityStatusID = ISNULL(PCountyPTR.PriorityStatusID,ISNULL(PCTTR.PriorityStatusID,ISNULL(PCDR.PriorityStatusID,PCPTR.PriorityStatusID)))
  LEFT JOIN ClientPartnerRel CPR ON CPR.PartnerCompanyID = PC.PartnerCompanyID
                                AND CPR.PartnerTypeID = PCPTR.PartnerTypeID
                                AND ((CPR.ClientID IS NULL
                                        AND CPR.ActionListTransactionTypeID IS NULL)
                                 OR (CPR.ClientID = @ClientID
					AND CPR.ActionListTransactionTypeID IS NULL)
                                 OR (CPR.ClientID = @ClientID
                                        AND CPR.ActionListTransactionTypeID = @ActionListTransactionTypeID))
  LEFT JOIN ClientUnderwriterRel CUR ON CUR.ClientID = @ClientID
  LEFT JOIN TitleCompanyUnderwriterRel TCUR ON CUR.ClientID IS NULL
                                            OR CUR.UnderwriterID = TCUR.UnderwriterID 
  LEFT JOIN FilePartnerRel FPRTC ON FPRTC.PartnerCompanyID = TCUR.TitleCompanyID
                                AND FPRTC.PartnerTypeID = 2
                                AND FPRTC.FileID = @FileID
  LEFT JOIN FilePartnerRel FPR2 ON FPR2.PartnerCompanyID = PCPTR.PartnerCompanyID
						     AND FPR2.PartnerTypeID = PCPTR.PartnerTypeID
						     AND FPR2.FileID = @FileID
 WHERE (@ReturnAllNonApproved = 0 OR ((PC.Approved = 0 AND GU.Enabled = 1)
         OR PC.PartnerCompanyID IN (SELECT PartnerCompanyID
                                      FROM PartnerEmployee PE, GeneralUser GU2
                                     WHERE PE.PartnerEmployeeID = GU2.UserID
                                       AND GU2.Enabled = 1 AND PE.Approved = 0)))
   AND (@ReturnOnlyApproved = 0 OR PC.Approved = 1)
   AND ((@LastName IS NULL AND @FirstName IS NULL)
          OR ((PE.LastName IS NOT NULL
            OR PE.FirstName IS NOT NULL)
           AND GU2.Enabled = 1))
   AND (@DoingBusinessIn = 0 OR ((@CountyID IS NULL OR (PCountyPTR.CountyID = @CountyID AND PCountyPTR.Enabled = 1)
                                OR (PCountyPTR.Enabled IS NULL AND PCTTR.CountyID = @CountyID AND PCTTR.Enabled = 1)
                                OR (PCountyPTR.Enabled IS NULL AND PCTTR.Enabled IS NULL AND PCDR.CountyID = @CountyID AND PCDR.Enabled = 1))
          AND (C.State = @State OR @State IS NULL)))
   AND (@DoingBusinessIn = 1 OR @State IS NULL OR PC.State = @State)
   AND (@CompanyName IS NULL OR PC.Name LIKE @CompanyName OR PC.PartnerCompanyID = @CompanyNameID)
   AND (@CompanyAbbr IS NULL OR PC.Abbreviation LIKE @CompanyAbbr)
   AND (@PartnerCompanyID IS NULL OR PC.PartnerCompanyID = @PartnerCompanyID)
   AND (@ParentSearch = 0 OR PC.ParentPartnerCompanyID IS NULL)
   AND (@ReturnExistingPartners = 1 OR FPR2.PartnerCompanyID IS NULL)
   AND (@IgnoreDecliningPartner = 1 OR @FileID <= 0 OR NOT EXISTS(SELECT FPFAR.PartnerCompanyID
                                                          FROM FilePartnerFileActionRefused FPFAR
                                                          JOIN FileActions FA ON FA.FileActionsID = FPFAR.FileActionsID
                                                         WHERE FPFAR.PartnerCompanyID = PC.PartnerCompanyID
                                                           AND FPFAR.PartnerTypeID = PT.PartnerTypeID
                                                           AND FA.FileID = @FileID))
   AND (@Override = 1 OR (CPR.PartnerCompanyID IS NOT NULL
                         AND EXISTS (SELECT * FROM ClientPartnerRel CPR
                                      WHERE CPR.PartnerCompanyID = PC.PartnerCompanyID
                                        AND CPR.PartnerTypeID = PCPTR.PartnerTypeID
                                        AND ((CPR.ClientID IS NULL AND CPR.ActionListTransactionTypeID IS NULL)
                                         OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID IS NULL
                                         OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID = @ActionListTransactionTypeID))))))
   -- Title Company, Underwriter & Clients Client
   AND (  (@PartnerTypeID = 2 AND PT.PartnerTypeID = 2 AND (TCUR.TitleCompanyID = PC.PartnerCompanyID OR TCUR.TitleCompanyID IS NULL))  
       OR (@PartnerTypeID = 5 AND PT.PartnerTypeID = 5 AND (@FileID = -1 OR @ActionListTransactionTypeID IS NULL
                                       OR PC.PartnerCompanyID IN (SELECT CPR.PartnerCompanyID
                                                                    FROM FileMain FMSub
                                                                    LEFT JOIN Property P
                                                                      ON P.PropertyID = FMSub.PrimaryPropertyID
                                                                    JOIN ClientPartnerRel CPR
                                                                      ON CPR.ClientID = FMSub.ClientID
                                                                     AND CPR.PartnerTypeID = 5
                                                                    LEFT JOIN ClientsClientClientRelInfo CCCRI
                                                                      ON CCCRI.ClientPartnerRelID = CPR.ClientPartnerRelID
                                                                    LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeRel CCCPTR
                                                                      ON CCCPTR.ClientID = FMSub.ClientID
                                                                     AND CCCPTR.ActionListTransactionTypeID = FMSub.ActionListTransactionTypeID
                                                                     AND CCCPTR.ActionListProductTypeID = FMSub.ActionListProductTypeID
                                                                     AND CCCPTR.ClientsClientID = CPR.PartnerCompanyID
                                                                    LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeCountyRel CCCPTCR
                                                                      ON CCCPTCR.ActionListProductTypeID = CCCPTR.ActionListProductTypeID
                                                                     AND CCCPTCR.ActionListTransactionTypeID = CCCPTR.ActionListTransactionTypeID
                                                                     AND CCCPTCR.ClientID = CCCPTR.ClientID
                                                                     AND CCCPTCR.ClientsClientID = CCCPTR.ClientsClientID
                                                                     AND CCCPTR.OverrideCoverage = 1
                                                                     AND P.CountyID = CCCPTCR.CountyID
                                                                   WHERE (((CCCPTR.OverrideCoverage = 1 AND (CCCPTCR.CountyID IS NOT NULL OR P.CountyID IS NULL))
                                                                          OR CCCPTR.OverrideCoverage = 0) OR CCCRI.OverrideProducts = 0 OR CCCRI.OverrideProducts IS NULL)
                                                                     AND FMSub.FileID = @FileID)))
       OR (@PartnerTypeID = 7 AND PT.PartnerTypeID = 7
                AND (@FileID = -1
                   OR ((TCUR.UnderwriterID = PC.PartnerCompanyID OR TCUR.UnderwriterID IS NULL OR FPRTC.PartnerCompanyID IS NULL)
                      AND dbo.GetUnderwriterHasTitleCompaniesFunc(TCUR.UnderwriterID, @TitleCompanies) = 1
                      AND (@ClientID IS NULL OR CUR.ClientID IS NULL OR (CUR.ClientID IS NOT NULL AND CUR.UnderwriterID  = PC.PartnerCompanyID)))))
       OR (@PartnerTypeID = -1 AND ((PT.PartnerTypeID = 2 AND (TCUR.TitleCompanyID = PC.PartnerCompanyID OR TCUR.TitleCompanyID IS NULL))
                                    OR (PT.PartnerTypeID = 7
                                       AND (TCUR.UnderwriterID = PC.PartnerCompanyID OR TCUR.UnderwriterID IS NULL OR FPRTC.PartnerCompanyID IS NULL)
                                       AND dbo.GetUnderwriterHasTitleCompaniesFunc(TCUR.UnderwriterID, @TitleCompanies) = 1
                                       AND (@ClientID IS NULL OR CUR.ClientID IS NULL OR (CUR.ClientID IS NOT NULL AND CUR.UnderwriterID = PC.PartnerCompanyID)))
                                    OR (@FileID = -1 OR @ActionListTransactionTypeID IS NULL
                                       OR PC.PartnerCompanyID IN (SELECT CPR.PartnerCompanyID
                                                                    FROM FileMain FMSub
                                                                    LEFT JOIN Property P
                                                                      ON P.PropertyID = FMSub.PrimaryPropertyID
                                                                    JOIN ClientPartnerRel CPR
                                                                      ON CPR.ClientID = FMSub.ClientID
                                                                     AND CPR.PartnerTypeID = 5
                                                                    LEFT JOIN ClientsClientClientRelInfo CCCRI
                                                                      ON CCCRI.ClientPartnerRelID = CPR.ClientPartnerRelID
                                                                    LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeRel CCCPTR
                                                                      ON CCCPTR.ClientID = FMSub.ClientID
                                                                     AND CCCPTR.ActionListTransactionTypeID = FMSub.ActionListTransactionTypeID
                                                                     AND CCCPTR.ActionListProductTypeID = FMSub.ActionListProductTypeID
                                                                     AND CCCPTR.ClientsClientID = CPR.PartnerCompanyID
                                                                    LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeCountyRel CCCPTCR
                                                                      ON CCCPTCR.ActionListProductTypeID = CCCPTR.ActionListProductTypeID
                                                                     AND CCCPTCR.ActionListTransactionTypeID = CCCPTR.ActionListTransactionTypeID
                                                                     AND CCCPTCR.ClientID = CCCPTR.ClientID
                                                                     AND CCCPTCR.ClientsClientID = CCCPTR.ClientsClientID
                                                                     AND CCCPTR.OverrideCoverage = 1
                                                                     AND P.CountyID = CCCPTCR.CountyID
                                                                   WHERE (((CCCPTR.OverrideCoverage = 1 AND (CCCPTCR.CountyID IS NOT NULL OR P.CountyID IS NULL))
                                                                          OR CCCPTR.OverrideCoverage = 0) OR CCCRI.OverrideProducts = 0 OR CCCRI.OverrideProducts IS NULL)
                                                                     AND FMSub.FileID = @FileID))
                                     OR (PT.PartnerTypeID NOT IN (2,5,7))))
       OR (@PartnerTypeID NOT IN (-1, 2, 5, 7) AND PT.PartnerTypeID = @PartnerTypeID))
 GROUP BY PC.Approved, FM.FileNumber, PT.Name, PC.Name, PC.City, PC.State, PC.Street1, PC.Street2,
          PC.PartnerCompanyID, GU.Enabled, PCPTR.PartnerTypeID
 ORDER BY PriorityStatus, PC.Name, FM.FileNumber

SET @OurError = @@ERROR
IF @OurError <> 0
BEGIN
	EXEC dbo.HandleError
		@ErrorNum = @OurError,
		@ProcID = @@PROCID
END

SET NOCOUNT OFF

GO