public async Task <IEnumerable <SfPrioritizedLead> > GetPrioritizedLeadsForLeepNoPostalCodes(PrioLeadInput input, LogCommand logCommand)
{
//Logging Helper String
const string methodName = "GetPrioritizedLeadsForLeepNoPostalCodes";
//Query Used by Dapper
var query =
$" SELECT CL.CustomerGuid " +
" ,CC.FullName " +
" ,CoP.Phone PhoneNumber " +
" ,CPA.Latitude " +
" ,CPA.Longitude " +
" ,CONCAT (CPA.StreetAddress1, ' ', CPA.StreetAddress2, ' ', CPA.City + ', ' , CPA.State, ' ', CPA.Zip) Address " +
" ,CL.SfLeadId " +
" ,CUU.CAPTier " +
" FROM Customer.Lead CL " +
" INNER JOIN [Contact].[Contact] CC ON CC.CustomerGuid = CL.CustomerGuid " +
" INNER JOIN [Customer].[Address] CPA ON CPA.CustomerGuid = CL.CustomerGuid " +
" INNER JOIN [CustomerProgram].[PecoCustomer] CPPC ON CPPC.CustomerGuid = CL.CustomerGuid " +
" INNER JOIN customer.Customer cu on cu.CustomerGuid = cl.CustomerGuid " +
" INNER JOIN program.Subprogram sp on sp.SubProgramGuid = cu.SubProgramGuid " +
" CROSS APPLY(SELECT TOP 1 CUU.Rate, CUU.AverageUsage, CUU.HeatingAverageUsage, lrc.CapTier FROM [Customer].[Usage] CUU " +
" INNER JOIN [UsageRaw].[LkpRateCodes] LRC ON LRC.RateCode = CUU.Rate " +
" AND LRC.ProgramGuid = CU.ProgramGuid " +
" WHERE CUU.CustomerGuid = CL.CustomerGuid) CUU " +
" CROSS APPLY (SELECT TOP 1 CoP.Phone FROM [Contact].[Phone] CoP " +
" WHERE CoP.ContactGuid = CC.ContactGuid " +
" AND CoP.Phone != '9999999999') CoP " +
" OUTER APPLY (Select max (aud.LastModifiedDate) lastModifiedDate FROM [CDCQueue].[AuditLog] aud " +
" WHERE aud.ObjectType = 'Shared.Entities.DTO.Customer.Lead' and aud.RecordID = cl.SFLeadID) aud " +
" WHERE CL.LeadStatusGuid IN ('F06163CC-8196-482C-99DF-0D46185CA0F0','46AB1284-1228-4B12-87AB-776FB7C1583E') " +
" AND CC.ContactTypeGuid = 'BEF01936-5D80-471D-AF77-4CC2AE5161B4' " +
"/*AND sp.SubProgramGuid = '14C2E6A1-1650-47CA-AA37-3B732167FA01' */ " +
" AND CC.IsAnyContactAllowed = 1 " +
" AND CC.IsVoiceContactAllowed = 1 " +
" AND CPA.Latitude IS NOT NULL " +
" AND CPA.Longitude IS NOT NULL " +
" and cl.ReservedDate != convert(date,GETDATE()) " +
" AND CL.LeadStatusReason IS NULL " +
" AND (aud.lastModifiedDate IS NULL or DATEDIFF(day, aud.lastModifiedDate, GETDATE()) != 0) " +
$" AND CL.QualifiedAuditTypeGuid = '{input.WorkType}' ";
//"/* AND CPPC.IncomeLevel = 1 */ " +
//LeadStatusName in ('Not Attempted', 'Attempted Contact')
//ContactTypeName = Primary
//PhoneTypeName = Program PhoneNumber
//Log the input
logCommand.LogMessage = $"{Repository}.{methodName} Starting input parameter: {input} " + Environment.NewLine +
$"Query: {query}";
_logHandler.HandleLog(logCommand);
using (var connection = _connectionFactory.GetConnection)
{
//Await the response
var leadList = await connection.QueryAsync <SfPrioritizedLead>(query);
connection.Close();
//Log the output
logCommand.LogMessage = $"{Repository}.{methodName} completed";
_logHandler.HandleLog(logCommand);
return(leadList);
}
}
public async Task <IEnumerable <SfPrioritizedLead> > GetPrioritizedLeadsByUsage(PrioLeadInput input, LogCommand logCommand)
{
//Logging Helper String
const string methodName = "GetSFPrioritizedLeadsByUsage";
var query = QueryResources.LeadQueries.ResourceManager.GetString("LiurpElectricAndGasAuditQuery");
//Check if we only want to do GasOnly Heating Audits
//If Worktype = Heating (BDA) or Apt Heating
if (input.WorkType == Guid.Parse("4B6CEBE1-E220-4F46-A732-2CCCCDE5F61A") || input.WorkType == Guid.Parse("E08D72D1-A4D5-470E-9558-F1020A774CA9"))
{
if (ConfigurationManager.AppSettings["LiurpGasOnlyHeatingAudit"] != "-1")
{
//Limits leads to those only qualified for Gas heating audits
query = query + " AND LRC.BaseRate in ('G', 'H') ";
}
}
//Log the input
logCommand.LogMessage = $"{Repository}.{methodName} Starting input parameter: { input.WorkType} " + Environment.NewLine +
$"Query: {query}";
_logHandler.HandleLog(logCommand);
using (var connection = _connectionFactory.GetConnection)
{
//Await the response
var leadList = await connection.QueryAsync <SfPrioritizedLead>(query, new { @QualifiedAuditTypeGuid = input.WorkType });
connection.Close();
//Log the output
logCommand.LogMessage = $"{Repository}.{methodName} completed";
_logHandler.HandleLog(logCommand);
return(leadList);
}
}
