Calculations on values extracted from several database tables not displaying some values

[mostwanted]

In my Guest House booking system I function that is intended to do invoice calculations for clients using values from different database tables & display the information in a table in the view.

The problem now is some information is not being displayed in the table in the view when a client has not ordered some services. E.G;

• When a client books in and just orders meals and does not request an extra room the meals information does not show in the invoice table only the room information displays but the total amount charges are not displayed.
  
• When a client books a room and an extra room the total amount charges are not displayed.
  
• Only when a client has booked a room, booked an extra room and ordered meals thats when all the information is displayed in the invoices including calculations.

 I am doing something wrong somewhere in my code but i can not figure it out. I need assistance.

CONTROLLER:

def viewInvoices():
    invoice=db.ClientDetails(request.args(0, cast=int))
    resCompany=db(db.my_company).select()
    room=db((db.book_client2.ClientDetails==invoice.id) & (db.book_client2.determinant==1)).select()
    add_room=db((db.additionalRooms2.book_client==invoice.id) & (db.additionalRooms2.determinant==1)).select()
    room_service=db((db.room_service_orders.customer==invoice.id) &(db.room_service_orders.determinant==1)).select()
    return locals()

VIEW:

<div>
<table>
<tr>
    <th>Quantity</th>
    <th>Description</th>
    <th>Order Price</th>
    <th>Value</th>
</tr>
    <tr>

<!------------------------Information for room service----------->

        {{for invoice in room:}}
    <td>{{=invoice.No_Days}} (days)</td>
        <td>{{=invoice.Room.Room_Number}}</td>
    <td>BWP {{=invoice.roomAmount}}</td>
        <td>BWP {{=invoice.Amount}}</td>
    </tr>
<tr>

<!---------------------------Information for an extra room booking------------->

            {{for add_r in add_room:}}
    <td>{{=add_r.No_Days}} (days)</td>
        <td>{{=add_r.Room.Room_Number}}</td>
    <td>BWP {{=add_r.roomAmount}}</td>
        <td>BWP {{=add_r.Amount}}</td>
    </tr>
    
    <tr>

<!-----------------------------Information for miscellaneous orders------------------------------>

        {{for orders in room_service:}}
        <td>{{=orders.quantity}}</td>
        <td>{{=orders.client_order}}</td>
        <td>BWP {{=orders.price}}</td>
        <td>BWP {{=orders.totalPrice}}</td>
</tr>
{{

#Total Calculations of all services rendered

  tourism_levy=10.00
        total=float(invoice.Amount)+float(add_r.Amount)+float(orders.totalPrice)
    totalPrice=float(invoice.Amount)+float(add_r.Amount)+float(orders.totalPrice)+tourism_levy
    }}
<tr>

<!------------------------------------Display of total calculations------------------------------------>

        <td></td><td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy EXCLUSIVE)</td><td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Sub-Total:</td><td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=total}}</td>
    </tr>
    <tr>
        <td></td><td></td><td style="font-weight: bold; font-size: 14px; color: red;" width: 29px;> Tourism Levy: </td><td style="font-weight: bold; font-size: 14px; color: red;" width: 29px>BWP {{=tourism_levy}}</td>
    </tr>
    <tr>
        <td></td><td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy INCLUSIVE)</td><td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Total: </td><td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=totalPrice}}</td>
    </tr>
                      {{pass}}
                {{pass}}
        {{pass}}
</table>
</div>
<br />

[Jim S]

It looks to me like your nesting in your template is wrong.  Seems that your {{pass}} statements are not where they should be.  Based on how I'm reading it, you'll only get a total if there are orders for room service.  You'll only get room service amounts if there are extra room bookings.

I'd do this:

<div>

    <table>

        <tr>

            <th>Quantity</th>

            <th>Description</th>

            <th>Order Price</th>

            <th>Value</th>

        </tr>

        <!------------------------Information for room service----------->

        {{for invoice in room:}}

            <tr>

                <td>{{=invoice.No_Days}} (days)</td>

                <td>{{=invoice.Room.Room_Number}}</td>

                <td>BWP {{=invoice.roomAmount}}</td>

                <td>BWP {{=invoice.Amount}}</td>

            </tr>

            <!---------------------------Information for an extra room booking------------->

            {{for add_r in add_room:}}

                <tr>

                    <td>{{=add_r.No_Days}} (days)</td>

                    <td>{{=add_r.Room.Room_Number}}</td>

                    <td>BWP {{=add_r.roomAmount}}</td>

                    <td>BWP {{=add_r.Amount}}</td>

                </tr>

            {{pass}}

            <!-----------------------------Information for miscellaneous orders------------------------------>

            {{for orders in room_service:}}

                <tr>

                    <td>{{=orders.quantity}}</td>

                    <td>{{=orders.client_order}}</td>

                    <td>BWP {{=orders.price}}</td>

                    <td>BWP {{=orders.totalPrice}}</td>

                </tr>

            {{pass}}

            {{

            #Total Calculations of all services rendered

            tourism_levy=10.00

            total=float(invoice.Amount)+float(add_r.Amount)+float(orders.totalPrice)

            totalPrice=float(invoice.Amount)+float(add_r.Amount)+float(orders.totalPrice)+tourism_levy

            }}

            <tr>

                <!------------------------------------Display of total calculations------------------------------------>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy EXCLUSIVE)</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Sub-Total:</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=total}}</td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: red;" width: 29px;> Tourism Levy:</td>

                <td style="font-weight: bold; font-size: 14px; color: red;" width: 29px>BWP {{=tourism_levy}}</td>

            </tr>

            <tr>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy INCLUSIVE)</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Total:</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=totalPrice}}</td>

            </tr>

        {{pass}}

    </table>

</div>

<br/>

-Jim

[mostwanted]

With this approach some variables the become undetectable, I get errors that:  <type 'exceptions.NameError'> name 'orders' is not defined  and  <type 'exceptions.NameError'> name 'add_r' is not defined

[Jim S]

Just need to move around where we're getting the variables.

<div>

    <table>

        <tr>

            <th>Quantity</th>

            <th>Description</th>

            <th>Order Price</th>

            <th>Value</th>

        </tr>

        <!------------------------Information for room service----------->

        {{tourism_levy = 10.00}}

        {{for invoice in room:}}

            <tr>

                <td>{{=invoice.No_Days}} (days)</td>

                <td>{{=invoice.Room.Room_Number}}</td>

                <td>BWP {{=invoice.roomAmount}}</td>

                <td>BWP {{=invoice.Amount}}</td>

            </tr>

            {{total = float(invoice.Amount)}}

            <!---------------------------Information for an extra room booking------------->

            {{for add_r in add_room:}}

                <tr>

                    <td>{{=add_r.No_Days}} (days)</td>

                    <td>{{=add_r.Room.Room_Number}}</td>

                    <td>BWP {{=add_r.roomAmount}}</td>

                    <td>BWP {{=add_r.Amount}}</td>

                </tr>

                {{total += float(add_r.Amount)

            {{pass}}

            <!-----------------------------Information for miscellaneous orders------------------------------>

            {{for orders in room_service:}}

                <tr>

                    <td>{{=orders.quantity}}</td>

                    <td>{{=orders.client_order}}</td>

                    <td>BWP {{=orders.price}}</td>

                    <td>BWP {{=orders.totalPrice}}</td>

                </tr>

                {{total += orders.totalPrice}}

            {{pass}}

            {{totalPrice=total + tourism_levy}}

            <tr>

                <!------------------------------------Display of total calculations------------------------------------>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy EXCLUSIVE)</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Sub-Total:</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=total}}</td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: red;" width: 29px;> Tourism Levy:</td>

                <td style="font-weight: bold; font-size: 14px; color: red;" width: 29px>BWP {{=tourism_levy}}</td>

            </tr>

            <tr>

                <td></td>

                <td style="font-weight: bold; font-size: 14px; color: black;" width: 29px;>(Levy INCLUSIVE)</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px;>Total:</td>

                <td style="font-weight: bold; font-size: 14px; color: green;" width: 29px>BWP {{=totalPrice}}</td>

            </tr>

        {{pass}}

    </table>

</div>

<br/>

-Jim