def generate_worksheet(well_id, prod_month, rpba):
    try:
        db = config.get_database()
        product = "OIL"
        well = db.select1('WellRoyaltyMaster', ID=well_id)
        well_lease_link_array = db.select('WellLeaseLink', WellID=well_id)
        if len(well_lease_link_array) == 0:
            raise AppError("There were no well_lease_link records for " + str(well_id) + str(prod_month))
        well_lease_link = well_lease_link_array[0]
        royalty = db.select1('LeaseRoyaltyMaster', ID=well_lease_link.LeaseID)
        royalty.format_gorr = format_gorr(royalty.Gorr)
        lease = db.select1('Lease', ID=well_lease_link.LeaseID)

        if rpba:
            monthly_array = db.select('Monthly', WellID=well_id, prodMonth=prod_month, product=product, RPBA=rpba)
        else:
            monthly_array = db.select('Monthly', WellID=well_id, prodMonth=prod_month, product=product)
        if len(monthly_array) == 0:
            raise AppError("There were no monthly records for " + str(well_id) + str(prod_month) + product)
        monthly = monthly_array[0] # if there are multiple pick the first one

        ba = db.select1('BAInfo',BAid=monthly.RPBA)
        calc = db.select1('Calc', WellID=well_id, ProdMonth=prod_month,RPBA=monthly.RPBA)
        rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=product, Payer=monthly.RPBA,
                                   Date=prod_month_to_date(prod_month))
        # calc = calc_array[0]
        # print(monthly)
        return render_template('worksheet/calc_worksheet.html',
                               well=well, rm=royalty, m=monthly, lease=lease,
                               calc=calc, well_lease_link=well_lease_link, ba=ba, rtp_info=rtp_info)
    except Exception as e:
        print('views.worksheet: ***Error:', e)
        traceback.print_exc(file=sys.stdout)
        return "<h2>Error displaying worksheet for well %s</h2><br>" % well_id + str(e)
Beispiel #2
0
    def test_run(self):

        t = prod_month_to_date(201611)
        self.assertEqual(2016, t.year)
        self.assertEqual(11, t.month)
        self.assertEqual(1, t.day)
        self.assertEqual(12, t.hour)
        self.assertEqual(0, t.minute)
    def process_one(self, well_id, prod_month, product):
        logging.info('**** Processing *** {0:6d} {1:6d} {2:}'.format(well_id, prod_month, product))
        # errorCount = 0
        well = self.db.select1('WellRoyaltyMaster', ID=well_id)
        well_lease_link_array = self.db.select('WellLeaseLink', WellID=well_id)
        if len(well_lease_link_array) == 0:
            raise AppError("There were no well_lease_link records for: " + str(well_id) + str(prod_month))
        well_lease_link = well_lease_link_array[0]
        royalty = self.db.select1('LeaseRoyaltymaster', ID=well_lease_link.LeaseID)
        lease = self.db.select1('Lease', ID=well_lease_link.LeaseID)

        # todo Create a test to ensure there is a RTPInfo for the well. raise an apperror if note
        # todo call the prod_month_to_date in the select and not everytime we use the select.
        rtp_info_array = self.db.select('RTPInfo', WellEvent=well.WellEvent, Product='OIL',
                                        Date=prod_month_to_date(prod_month))
        if len(rtp_info_array) == 0:
            print('--- Well not found in RTPInfo', well.ID, well.WellEvent)
        else:
            print('--- Well found in RTPInfo Payer:', rtp_info_array[0].Payer)

        monthly_list = self.db.select('Monthly', WellID=well_id, ProdMonth=prod_month, Product=product)
        if len(monthly_list) == 0:
            raise AppError("No monthly data found for WellID: " + str(well_id) + " ProdMonth:" + str(prod_month) +
                           " Product:" + product)

        calc_array = self.db.select('Calc', WellID=well_id, ProdMonth=prod_month)
        for calc in calc_array:
            self.db.delete('Calc', calc.ID)

        for monthly in monthly_list:
            # calc_array = self.db.select('Calc', WellID=well_id, ProdMonth=prod_month)
            # if len(calc_array) == 0:
            #     calc = None
            # else:
            #     calc = calc_array[0]
            rtp_info = self.db.select1('RTPInfo', WellEvent=well.WellEvent, Product=product, Payer=monthly.RPBA,
                                       Date=prod_month_to_date(prod_month))

            # calc = None
            calc = self.zero_royalty_calc(prod_month, well_id, product)
            self.calc_royalties(well, royalty, calc, monthly, well_lease_link, rtp_info)
            calc.RPBA = monthly.RPBA
            calc.FNReserveID = lease.FNReserveID
            calc.FNBandID = lease.FNBandID
            # if len(calc_array) == 0:
            self.db.insert(calc)
def generate_pdfworksheet_from_calc(calc):
    try:
        db = config.get_database()
        if calc.Entity == 'Well':
            well = db.select1('WellRoyaltyMaster', ID=calc.EntityID)
        else:
            well = None
        entity_lease_link_array = db.select('EntityLeaseLink', Entity=calc.Entity, EntityID=calc.EntityID)
        if len(entity_lease_link_array) == 0:
            raise AppError("There were no well_lease_link records for " + str(calc.WellID) + str(calc.ProdMonth))
        entity_lease_link = entity_lease_link_array[0]
        royalty = db.select1('LeaseRoyaltyMaster', ID=calc.LeaseID)
        lease = db.select1('Lease', ID=calc.LeaseID)

        history = db.select_sql("""SELECT ID, ExtractDate, BaseNetRoyaltyValue, GorrNetRoyaltyValue
                   from Calc
                   WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
                   and Product = "{}" and RPBA = "{}"
                   order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
                                                  calc.Product, calc.RPBA))

        prev_BaseNetRoyaltyValue = 0.0
        prev_GorrNetRoyaltyValue = 0.0
        for h in history:
            h.BookedBaseNetRoyaltyValue = h.BaseNetRoyaltyValue - prev_BaseNetRoyaltyValue
            h.BookedGorrNetRoyaltyValue = h.GorrNetRoyaltyValue - prev_GorrNetRoyaltyValue
            h.Booked = h.BookedBaseNetRoyaltyValue + h.BookedGorrNetRoyaltyValue
            prev_BaseNetRoyaltyValue = h.BaseNetRoyaltyValue
            prev_GorrNetRoyaltyValue = h.GorrNetRoyaltyValue

        monthly_array = db.select('Monthly', ExtractDate=calc.ExtractDate, Entity=calc.Entity, EntityID=calc.EntityID,
                                  prodMonth=calc.ProdMonth, product=calc.Product, RPBA=calc.RPBA)
        if len(monthly_array) == 0:
            raise AppError("There were no monthly records for well: " + str(calc.WellID) + " ProdDate: " +
                           str(calc.ProdMonth) + " Product: " + calc.Product)
        monthly = monthly_array[0]  # if there are multiple pick the first one

        ba = db.select1('BAInfo', BAid=monthly.RPBA, BAType='RTP')

        calc_specific = DataStructure(calc.RoyaltySpecific)
        rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=calc.Product, Payer=monthly.RPBA,
                              Date=prod_month_to_date(calc.ProdMonth))

        royalty.format_gorr = format_gorr(calc.Gorr)

        return render_template('worksheet/pdfcalc_worksheet.html',
                               well=well, rm=royalty, m=monthly, lease=lease,
                               calc=calc, calc_sp=calc_specific, entity_lease_link=entity_lease_link,
                               ba=ba, rtp_info=rtp_info, history=history)
    except Exception as e:
        print('views.worksheet: ***Error:', e)
        traceback.print_exc(file=sys.stdout)
        tb = traceback.format_exc()
        return "<h2>Error displaying worksheet for " + calc.Entity + " %s</h2><br>" % calc.EntityID + str(e) + \
               '<plaintext>' + tb + '</plaintext>'
def generate_worksheet_from_calc(calc, with_js='y'):
    """
    Generate html for the calc row that is passed
    :param calc: a full calc record
    :param with_js: default is 'y' only other option is 'n' and should only be used to work around a
                    pdf generation issue
    :return: html of the worksheet for the calc record
    """
    try:
        db = config.get_database()
        if calc.Entity == 'Well':
            well = db.select1('WellRoyaltyMaster', ID=calc.EntityID)
        else:
            well = None
        entity_lease_link_array = db.select('EntityLeaseLink', Entity=calc.Entity, EntityID=calc.EntityID)
        if len(entity_lease_link_array) == 0:
            raise AppError("There were no well_lease_link records for " + str(calc.WellID) + str(calc.ProdMonth))
        entity_lease_link = entity_lease_link_array[0]
        royalty = db.select1('LeaseRoyaltyMaster', ID=calc.LeaseID)
        lease = db.select1('Lease', ID=calc.LeaseID)

        history = db.select_sql("""SELECT *
                   from Calc
                   WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
                   and Product = "{}" and RPBA = "{}"
                   order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
                                                  calc.Product, calc.RPBA))
        # history = db.select_sql("""SELECT ID, ExtractDate, BaseNetRoyaltyValue, GorrNetRoyaltyValue
        #            from Calc
        #            WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
        #            and Product = "{}" and RPBA = "{}"
        #            order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
        #                                           calc.Product, calc.RPBA))

        prev_BaseNetRoyaltyValue = 0.0
        prev_GorrNetRoyaltyValue = 0.0
        i = 0
        for h in history:
            h.BookedBaseNetRoyaltyValue = h.BaseNetRoyaltyValue - prev_BaseNetRoyaltyValue
            h.BookedGorrNetRoyaltyValue = h.GorrNetRoyaltyValue - prev_GorrNetRoyaltyValue
            h.Booked = h.BookedBaseNetRoyaltyValue + h.BookedGorrNetRoyaltyValue
            prev_BaseNetRoyaltyValue = h.BaseNetRoyaltyValue
            prev_GorrNetRoyaltyValue = h.GorrNetRoyaltyValue
            # Set the original calc record to the history record the one before this one
            if h.ExtractDate == calc.ExtractDate and i > 0:
                calc.original(history[i-1])

            i += 1

        monthly_array = db.select('Monthly', ExtractDate=calc.ExtractDate, Entity=calc.Entity, EntityID=calc.EntityID,
                                  prodMonth=calc.ProdMonth, product=calc.Product, RPBA=calc.RPBA)
        if len(monthly_array) == 0:
            raise AppError("There were no monthly records for well: " + str(calc.WellID) + " ProdDate: " +
                           str(calc.ProdMonth) + " Product: " + calc.Product)
        monthly = monthly_array[0]  # if there are multiple pick the first one

        ba = db.select1('BAInfo', BAid=monthly.RPBA, BAType='RTP')

        calc_specific = DataStructure(calc.RoyaltySpecific)
        rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=calc.Product, Payer=monthly.RPBA,
                              Date=prod_month_to_date(calc.ProdMonth))

        royalty.format_gorr = format_gorr(calc.Gorr)

        return render_template('worksheet/calc_worksheet.html',
                               with_js=with_js,
                               well=well, rm=royalty, m=monthly, lease=lease,
                               calc=calc, calc_sp=calc_specific, entity_lease_link=entity_lease_link,
                               ba=ba, rtp_info=rtp_info, history=history)
    except Exception as e:
        print('views.worksheet: ***Error:', e)
        traceback.print_exc(file=sys.stdout)
        tb = traceback.format_exc()
        return "<h2>Error displaying worksheet for " + calc.Entity + " %s</h2><br>" % calc.EntityID + str(e) + \
               '<plaintext>' + tb + '</plaintext>'