def get_contract_length(jobs):
sql = """
select l.InstallationID, l.MonthlyPaymentsQuantity
from Lease l
join dbo.Split(?,',') s on s.Data = l.InstallationID
where l.MonthlyPaymentsQuantity > 0
"""
return sw.readsqljobs(sql, jobs)
def actuals(jobs):
sql = """
select inv.InstallationID, iph.measureddate [Date], iph.energy [Output]
from sc_dw..inverterpowerhistory iph
join Inverters inv on iph.inverterid = inv.InverterID
join dbo.Split(?,',') s on s.Data = inv.InstallationID
"""
return sw.readsqljobs(sql,jobs)
def _amounts(jobs):
sql = """
select ir.InstallationID, ir.Amount
from InstallationRebate ir
join dbo.Split(?,',') s ON ir.InstallationID = s.Data
where ir.PrimaryRebate = 1 and ir.RebateType IN ('Upfront','Utility - Upfront')
"""
return sw.readsqljobs(sql, jobs)
def actuals(jobs):
sql = """
select pd.InstallationID, pd.PTODate Date
from dbo.Split(?,',') s
join ProjectedDate pd ON pd.InstallationID = s.Data
where pd.PTODate is not null
"""
return sw.readsqljobs(sql, jobs)
def get_inspection_dates(jobs):
# Inspection complete date actuals
sql = """
select pd.InstallationID, pd.InspectionCompleteDate AS 'Date'
from dbo.Split(?,',') s
join ProjectedDate pd ON pd.InstallationID = s.Data
where pd.InspectionCompleteDate IS NOT NULL
"""
return sw.readsqljobs(sql, jobs)
def get_submit_dates(jobs):
# Grant submit date actuals
sql = """
select icc.InstallationID, CONVERT(datetime,icc.Value) [Date]
from InstallationCustomColumn icc
join dbo.Split(?,',') s on s.Data = icc.InstallationID
where icc.CustomColumnID = 35 and ISDATE(icc.Value) = 1
"""
return sw.readsqljobs(sql,jobs)
def _amounts(jobs):
# Expected grant amounts based on FMV rates -- also accounts for Treasury override
sql = """
select i.InstallationID, ISNULL(dbo.fGetTreasuryFMVPPW(i.InstallationID),dbo.fGetFMVPPW(i.InstallationID)) * i.SystemSize * 1000 * 0.30 AS 'Amount'
from dbo.Split(?,',') s
join Installations i ON s.Data = i.InstallationID
where dbo.fGetTreasuryGrantFlag(i.InstallationID) = 1
"""
return sw.readsqljobs(sql,jobs)
def actuals(jobs):
sql = """
select ir.InstallationID, ir.Amount, rsh.LastEntryDate [Date]
from InstallationRebate ir
join (
select InstallationRebateID, LastEntryDate
from RebateStatusHistory
where RebateStatusID = 6
) rsh on ir.InstallationRebateID = rsh.InstallationRebateID
join dbo.Split(?,',') s ON ir.InstallationID = s.Data
where ir.PrimaryRebate = 1
and ir.RebateType IN ('Upfront','Utility - Upfront')
"""
return sw.readsqljobs(sql, jobs)
def actuals(jobs):
sql = """
select amt.InstallationID, CONVERT(datetime,dte.Value) [Date], CAST(amt.Value AS FLOAT) Amount
from dbo.Split(?,',') s
join (
select InstallationID, DATEADD(dd,DATEDIFF(dd,0,Value),0) Value
from InstallationCustomColumn
where CustomColumnID = 14 AND ISDATE(Value) = 1
) dte on s.Data = dte.InstallationID
join (
select InstallationID, Value
from InstallationCustomColumn
where CustomColumnID = 12 AND ISNUMERIC(Value) = 1
) amt ON dte.InstallationID = amt.InstallationID
"""
return sw.readsqljobs(sql,jobs)
def get_monthly_projections(jobs):
sql = """
select p.InstallationID, y1.[Month], y1.kWh as PredictedkWh
from (
select ip.InstallationID, vcp.ProposalID, ROW_NUMBER() OVER(PARTITION BY ip.InstallationID
ORDER BY CASE vcp.CreatorRole WHEN 'Engineering' THEN 1 WHEN 'SalesCreatedJob' THEN 2 ELSE 3 END, CASE vcp.Selector WHEN 'Sold' THEN 1 ELSE 2 END, vcp.ProposalID) rownum
from SoleoCustomer..vCustomerProposal vcp
join InstallationsProposals ip on vcp.ProposalID = ip.ProposalID
) p
join (
select ps.ProposalID, po.[Month], SUM(po.kWh) kWh
from SoleoCustomer..PredictedOutput po
join SoleoCustomer..PanelString pstr on pstr.StringID = po.PanelStringID
join SoleoCustomer..InverterSet invs on invs.StringID = pstr.InverterSetID
join SoleoCustomer..PowerSource ps on ps.StringID = invs.PowerSourceID
group by ps.ProposalID, po.[Month]
) y1 on y1.ProposalID = p.ProposalID
join dbo.Split(?,',') s on s.Data = p.InstallationID
where p.rownum = 1
"""
return sw.readsqljobs(sql, jobs)
def actuals(jobs):
# Actual post PTO production
sql = """
select a.InstallationID, a.[Month], a.[Output] - ISNULL(b.[Output],0) [Output]
from dbo.Split(?,',') s
join (
select inv.InstallationID, DATEADD(dd,-1,DATEADD(mm,invo.[Month],DATEADD(yy,invo.[Year]-1900,0))) [Month], SUM(invo.[Output]) [Output]
from InverterOutputs invo (nolock)
join Inverters inv on invo.InverterID = inv.InverterID
where invo.[Hour] IS NULL and invo.[Day] IS NULL and invo.[Month] IS NOT NULL and invo.[Year] IS NOT NULL
group by inv.InstallationID, DATEADD(dd,-1,DATEADD(mm,invo.[Month],DATEADD(yy,invo.[Year]-1900,0)))
) a on s.Data = a.InstallationID
left join (
--Pre-PTO output that needs to be netted out!!
select inv.InstallationID, DATEADD(dd,-1,DATEADD(mm,invo.[Month],DATEADD(yy,invo.[Year]-1900,0))) [Month], SUM(invo.[Output]) [Output]
from InverterOutputs invo (nolock)
join Inverters inv on invo.InverterID = inv.InverterID
join ProjectedDate pd on inv.InstallationID = pd.InstallationID
where invo.[Hour] IS NULL and invo.[Day] IS NOT NULL and invo.[Month] IS NOT NULL and invo.[Year] IS NOT NULL
and DATEADD(dd,invo.[Day]-1,DATEADD(mm,invo.[Month]-1,DATEADD(yy,invo.[Year]-1900,0))) <= pd.PTODate
group by inv.InstallationID, DATEADD(dd,-1,DATEADD(mm,invo.[Month],DATEADD(yy,invo.[Year]-1900,0)))
) b on a.InstallationID = b.InstallationID and a.[Month] = b.[Month]
"""
return sw.readsqljobs(sql, jobs)