DELIMITER $$;

DROP PROCEDURE IF EXISTS sp_NewMortgageBusiness$$

CREATE PROCEDURE sp_NewMortgageBusiness ( pDateFrom VARCHAR(10) ,pDateTo VARCHAR(10) ,pProductTypeID INT ,pProductSubTypeID INT ,pProviderID INT ,pIntroducerID INT ,pNPWYN VARCHAR(1),pUserID INT ,pUserTypeID INT,pTeamID INT,pBranchID INT,pDateType VARCHAR(15),pSourceType INT, pSourceName INT, pSourceSection INT)
SELECT 
 e.CaseID
,apd.Surname 	
,concat(um.Forename,' ',um.Surname) AllocatedUser
,1 ProductTypeID
,'Mortgage  ' ProductType
,pst.ProductSubTypeID
,pst.ProductSubType 
,pm.ProductCode 
,pm.ProductName 
,amd.LoanRequired Loanvalue
,coalesce(cpc.FeeExp,0) ProcFee
,coalesce(cc.FeeExp,0)  ClientFee
,coalesce(cc1.FeeExp,0) IntroducerFee
,pvm.ProviderID 
,pvm.ProviderName
,i.IntroducerID 
,i.IntroducerName
,IF(e.CreatedOn='0000-00-00 00:00:00' OR e.CreatedOn IS NULL OR Trim(e.CreatedOn)="" OR e.CreatedOn='0000-00-00','0000-00-00',e.CreatedOn) ApplRecdDate
,IF(amd.ExpectedDt='0000-00-00 00:00:00' OR amd.ExpectedDt IS NULL OR Trim(amd.ExpectedDt)="" OR amd.ExpectedDt='0000-00-00','0000-00-00',amd.ExpectedDt) ProdAddDate
,IF(cpc.ExpectedDt='0000-00-00 00:00:00' OR cpc.ExpectedDt IS NULL OR Trim(cpc.ExpectedDt)="" OR cpc.ExpectedDt='0000-00-00','0000-00-00',cpc.ExpectedDt) PFAD
,IF(amd.OfferDt='0000-00-00 00:00:00' OR amd.OfferDt IS NULL OR Trim(amd.OfferDt)="" OR amd.OfferDt='0000-00-00','0000-00-00',amd.OfferDt) OfferDt
,IF(amd.ExchangeDt='0000-00-00 00:00:00' OR amd.ExchangeDt IS NULL OR Trim(amd.ExchangeDt)="" OR amd.ExchangeDt='0000-00-00','0000-00-00',amd.ExchangeDt) ExchangeDt
,IF(cpd.DateSubmitted='0000-00-00 00:00:00' OR cpd.DateSubmitted IS NULL OR Trim(cpd.DateSubmitted)="" OR cpd.DateSubmitted='0000-00-00','0000-00-00',cpd.DateSubmitted) DateSubmitted
,IF(amd.CompletedDt='0000-00-00 00:00:00' OR amd.CompletedDt IS NULL OR Trim(amd.CompletedDt)="" OR amd.CompletedDt='0000-00-00','0000-00-00',amd.CompletedDt) CompletedDt
,IF(cpc.FeeAct1Dt='0000-00-00 00:00:00' OR cpc.FeeAct1Dt IS NULL OR Trim(cpc.FeeAct1Dt)="" OR cpc.FeeAct1Dt='0000-00-00','0000-00-00',cpc.FeeAct1Dt) CompletionPaid
,IF(cpd.DateUnderwritten='0000-00-00 00:00:00' OR cpd.DateUnderwritten IS NULL OR Trim(cpd.DateUnderwritten)="" OR cpd.DateUnderwritten='0000-00-00','0000-00-00',cpd.DateUnderwritten) UnderwrittenDt
,IF(amd.NPWYNDt='0000-00-00 00:00:00' OR amd.NPWYNDt IS NULL OR Trim(amd.NPWYNDt)="" OR amd.NPWYNDt='0000-00-00','0000-00-00',amd.NPWYNDt) NPWYNDt
,amd.NPWYN 
,tm.TeamID 
,tm.TeamName 
,um.UserID
,um.UserTypeID
,ut.UserType
,bm.BranchID
,bm.BranchName
,mt.MediaTypeID
,mt.MediaType
,mn.MediaNameID
,mn.MediaName
,ms.MediaSourceID
,ms.MediaSection
FROM
(
 tblEnquiries e 
,tblApplicantMortgageDet amd
,tblDynamicTeam dt
,tblUserMaster um
) 
left join tblApplicantPersonalDetails apd on e.CaseID = apd.CaseID
left join tblProductSubTypes pst on amd.productsubtypeid = pst.productsubtypeid
left join tblProductMaster pm on amd.ProductID = pm.ProductID
left join tblProviderMaster pvm on amd.ProviderID = pvm.ProviderID
left join tblCaseProductComission cpc on e.CaseID = cpc.CaseID and amd.ProductID = cpc.ProductID and cpc.CommissionType = 11
left join tblCaseComission cc ON e.CaseID = cc.CaseID and cc.CommissionType=1
LEFT JOIN tblCaseComission cc1 ON e.CaseID = cc1.CaseID and cc1.CommissionType=5
LEFT JOIN tblCaseProductDates cpd on e.CaseID = cpd.CaseID and amd.MortgageDetID = cpd.ProductRecordID
LEFT JOIN tblTeamMaster tm on e.AllocatedTeam = tm.teamid
LEFT JOIN tblIntroducer i on e.introducerid = i.introducerid
LEFT JOIN tblBranchMaster bm ON um.branchid = bm.branchid
LEFT JOIN tblUserTypes ut ON dt.UserTypeID = ut.UserTypeID and ut.ActiveYN='1'
LEFT JOIN tblApplicantSource aps ON e.caseid = aps.caseid and aps.ApplicantType=1
LEFT JOIN tblMediaTypes mt ON aps.MediatypeID = mt.MediaTypeID
LEFT JOIN tblMediaNames mn ON aps.MediaNameID = mn.MediaNameID
LEFT JOIN tblMediaSource ms ON aps.MediaSourceID = ms.MediaSourceID
WHERE 
    apd.ApplicantType='1'
and e.caseid = amd.caseid
and dt.caseid = e.caseid
and dt.userid = um.userid
and IF(pDateType='ApplRecd',
  (DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='ProdAddDate',
  (DATE_FORMAT(amd.ExpectedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.ExpectedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='PFAD',
  (DATE_FORMAT(cpc.ExpectedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpc.ExpectedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Offered',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='CompletedPaid',
  (DATE_FORMAT(cpc.FeeAct1Dt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpc.FeeAct1Dt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Submitted',
  (DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Completed',
  (DATE_FORMAT(amd.CompletedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.CompletedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Underwritten',
  (DATE_FORMAT(cpd.DateUnderwritten,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateUnderwritten,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='NPWd',
  (DATE_FORMAT(amd.NPWYNDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.NPWYNDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='ONotComp',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.CompletedDt = '0000-00-00 00:00:00' OR amd.CompletedDt IS NULL)),
 IF(pDateType='AppRdNotComp',
  (DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.CompletedDt = '0000-00-00 00:00:00' OR amd.CompletedDt IS NULL)),
 IF(pDateType='SubNotOffd',
  (DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.OfferDt = '0000-00-00 00:00:00' OR amd.OfferDt IS NULL)),
 IF(pDateType='OffdNotSub',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (cpd.DateSubmitted = '0000-00-00 00:00:00' OR cpd.DateSubmitted IS NULL)),
  1=1)))))))))))))
AND IF(pNPWYN='Y',1=1,amd.NPWYN=pNPWYN)
AND IF(pProviderID=-1,1=1,pvm.ProviderID=pProviderID)
AND IF(pIntroducerID=-1,1=1,i.IntroducerID=pIntroducerID)
AND IF(pProductSubTypeID=-1,1=1,pst.ProductSubTypeID=pProductSubTypeID)
AND IF(pProductTypeID=-1,1=1,pst.ProductTypeID=pProductTypeID)
AND IF(pBranchID=-1,1=1,bm.BranchID=pBranchID)
AND IF(pUserID=-1,1=1,um.UserID=pUserID)
AND IF(pUserTypeID=-1,1=1,dt.UserTypeID=pUserTypeID)
AND IF(pTeamID=-1,1=1,tm.TeamID=pTeamID)
AND IF(pSourceType=-1,1=1,mt.MediaTypeID=pSourceType)
AND IF(pSourceName=-1,1=1,mn.MediaNameID=pSourceName)
AND IF(pSourceSection=-1,1=1,ms.MediaSourceID=pSourceSection)
UNION ALL 
SELECT 
 e.CaseID 
,apd.Surname 
,concat(um.Forename,' ',um.Surname) AllocatedUser
,2 ProductTypeID
,'Remortgage' ProductType
,pst.ProductSubTypeID
,pst.ProductSubType 
,pm.ProductCode 
,pm.ProductName 
,amd.RequiredLoan 
,coalesce(cpc.FeeExp,0) 
,coalesce(cc.FeeExp,0) 
,coalesce(cc1.FeeExp,0) 
,pvm.ProviderID 
,pvm.ProviderName 
,i.IntroducerID 
,i.IntroducerName 
,IF(e.CreatedOn='0000-00-00 00:00:00' OR e.CreatedOn IS NULL OR Trim(e.CreatedOn)="" OR e.CreatedOn='0000-00-00','0000-00-00',e.CreatedOn) ApplRecdDate
,IF(amd.ExpectedDt='0000-00-00 00:00:00' OR amd.ExpectedDt IS NULL OR Trim(amd.ExpectedDt)="" OR amd.ExpectedDt='0000-00-00','0000-00-00',amd.ExpectedDt) ProdAddDate
,IF(cpc.ExpectedDt='0000-00-00 00:00:00' OR cpc.ExpectedDt IS NULL OR Trim(cpc.ExpectedDt)="" OR cpc.ExpectedDt='0000-00-00','0000-00-00',cpc.ExpectedDt) PFAD
,IF(amd.OfferDt='0000-00-00 00:00:00' OR amd.OfferDt IS NULL OR Trim(amd.OfferDt)="" OR amd.OfferDt='0000-00-00','0000-00-00',amd.OfferDt) OfferDt
,IF(amd.ExchangeDt='0000-00-00 00:00:00' OR amd.ExchangeDt IS NULL OR Trim(amd.ExchangeDt)="" OR amd.ExchangeDt='0000-00-00','0000-00-00',amd.ExchangeDt) ExchangeDt
,IF(cpd.DateSubmitted='0000-00-00 00:00:00' OR cpd.DateSubmitted IS NULL OR Trim(cpd.DateSubmitted)="" OR cpd.DateSubmitted='0000-00-00','0000-00-00',cpd.DateSubmitted) DateSubmitted
,IF(amd.CompletedDt='0000-00-00 00:00:00' OR amd.CompletedDt IS NULL OR Trim(amd.CompletedDt)="" OR amd.CompletedDt='0000-00-00','0000-00-00',amd.CompletedDt) CompletedDt
,IF(cpc.FeeAct1Dt='0000-00-00 00:00:00' OR cpc.FeeAct1Dt IS NULL OR Trim(cpc.FeeAct1Dt)="" OR cpc.FeeAct1Dt='0000-00-00','0000-00-00',cpc.FeeAct1Dt) CompletionPaid
,IF(cpd.DateUnderwritten='0000-00-00 00:00:00' OR cpd.DateUnderwritten IS NULL OR Trim(cpd.DateUnderwritten)="" OR cpd.DateUnderwritten='0000-00-00','0000-00-00',cpd.DateUnderwritten) UnderwrittenDt
,IF(amd.NPWYNDt='0000-00-00 00:00:00' OR amd.NPWYNDt IS NULL OR Trim(amd.NPWYNDt)="" OR amd.NPWYNDt='0000-00-00','0000-00-00',amd.NPWYNDt) NPWYNDt
,amd.NPWYN 
,tm.TeamID 
,tm.TeamName 
,um.UserID
,um.UserTypeID
,ut.UserType
,bm.BranchID
,bm.BranchName
,mt.MediaTypeID
,mt.MediaType
,mn.MediaNameID
,mn.MediaName
,ms.MediaSourceID
,ms.MediaSection
FROM
(
 tblEnquiries e 
,tblApplicantReMortgageDet amd
,tblDynamicTeam dt
,tblUserMaster um
)
left join tblApplicantPersonalDetails apd on e.CaseID = apd.CaseID
left join tblProductSubTypes pst on amd.productsubtypeid = pst.productsubtypeid
left join tblProductMaster pm on amd.ProductID = pm.ProductID
left join tblProviderMaster pvm on amd.ProviderID = pvm.ProviderID 
left join tblCaseProductComission cpc on e.CaseID = cpc.CaseID and amd.ProductID = cpc.ProductID and cpc.CommissionType = 12
left join tblCaseComission cc ON e.CaseID = cc.CaseID and cc.CommissionType=1
left join tblCaseComission cc1 ON e.CaseID = cc1.CaseID and cc1.CommissionType=5
left join tblCaseProductDates cpd on e.CaseID = cpd.CaseID and amd.RemortgageDetID = cpd.ProductRecordID
left join tblTeamMaster tm on e.AllocatedTeam = tm.teamid
left join tblIntroducer i on e.introducerid = i.introducerid
left join tblBranchMaster bm ON um.branchid = bm.branchid
left join tblUserTypes ut ON dt.UserTypeID = ut.UserTypeID and ut.ActiveYN='1'
LEFT JOIN tblApplicantSource aps ON e.caseid = aps.caseid and aps.ApplicantType=1
LEFT JOIN tblMediaTypes mt ON aps.MediatypeID = mt.MediaTypeID
LEFT JOIN tblMediaNames mn ON aps.MediaNameID = mn.MediaNameID
LEFT JOIN tblMediaSource ms ON aps.MediaSourceID = ms.MediaSourceID
where    
apd.ApplicantType='1'
and e.Caseid = amd.caseid
and dt.caseid = e.caseid
and dt.userid = um.userid
and IF(pDateType='ApplRecd',
  (DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='ProdAddDate',
  (DATE_FORMAT(amd.ExpectedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.ExpectedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='PFAD',
  (DATE_FORMAT(cpc.ExpectedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpc.ExpectedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Offered',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='CompletedPaid',
  (DATE_FORMAT(cpc.FeeAct1Dt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpc.FeeAct1Dt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Submitted',
  (DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Completed',
  (DATE_FORMAT(amd.CompletedDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.CompletedDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='Underwritten',
  (DATE_FORMAT(cpd.DateUnderwritten,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateUnderwritten,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='NPWd',
  (DATE_FORMAT(amd.NPWYNDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.NPWYNDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE())),
 IF(pDateType='ONotComp',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.CompletedDt = '0000-00-00 00:00:00' OR amd.CompletedDt IS NULL)),
 IF(pDateType='AppRdNotComp',
  (DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(e.CreatedOn,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.CompletedDt = '0000-00-00 00:00:00' OR amd.CompletedDt IS NULL)),
 IF(pDateType='SubNotOffd',
  (DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(cpd.DateSubmitted,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (amd.OfferDt = '0000-00-00 00:00:00' OR amd.OfferDt IS NULL)),
 IF(pDateType='OffdNotSub',
  (DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') >= COALESCE(pDateFrom,'0000-00-00') AND DATE_FORMAT(amd.OfferDt,'%Y-%m-%d') <= COALESCE(pDateTo,CURDATE()) 
    AND (cpd.DateSubmitted = '0000-00-00 00:00:00' OR cpd.DateSubmitted IS NULL)),
  1=1)))))))))))))
AND IF(pNPWYN='Y',1=1,amd.NPWYN=pNPWYN)
AND IF(pProviderID=-1,1=1,pvm.ProviderID=pProviderID)
AND IF(pIntroducerID=-1,1=1,i.IntroducerID=pIntroducerID)
AND IF(pProductSubTypeID=-1,1=1,pst.ProductSubTypeID=pProductSubTypeID)
AND IF(pProductTypeID=-1,1=1,pst.ProductTypeID=pProductTypeID)
AND IF(pBranchID=-1,1=1,bm.BranchID=pBranchID)
AND IF(pUserID=-1,1=1,um.UserID=pUserID)
AND IF(pUserTypeID=-1,1=1,dt.UserTypeID=pUserTypeID)
AND IF(pTeamID=-1,1=1,tm.TeamID=pTeamID)
AND IF(pSourceType=-1,1=1,mt.MediaTypeID=pSourceType)
AND IF(pSourceName=-1,1=1,mn.MediaNameID=pSourceName)
AND IF(pSourceSection=-1,1=1,ms.MediaSourceID=pSourceSection)$$

DELIMITER ;$$