Example #1
0
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)
Example #2
0
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)
Example #3
0
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)
Example #4
0
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)
Example #5
0
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)
Example #6
0
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)
Example #7
0
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)
Example #8
0
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)
Example #9
0
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)
Example #10
0
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)
Example #11
0
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)