Thursday, 22 January 2015

Make A Cumulative Sum Column In MySql

MySql: Select Query- Make A Cumulative Sum Column



 
create database sam;
use sam;
CREATE TABLE account
(
transaction_id varchar(255),
acc_no varchar(255),
amount int
); 

INSERT INTO account (transaction_id, acc_no, amount)
VALUES (1,100,1000); 
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (2,100,50); 
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (3,101,2000); 
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (4,102,211); 

select * from account;
+----------------+--------+--------+
| transaction_id | acc_no | amount |
+----------------+--------+--------+
| 1                     | 100       |   1000 |
| 2                     | 100       |       50 |
| 3                     | 101       |   2000 |
| 4                     | 102       |     211 |
+----------------+--------+--------+
4 rows in set (0.00 sec)

 select
 transaction_id,
 amount,
 (@cum_sum:=@cum_sum+amount) as "cumulative"
from
account
JOIN (select @cum_sum := 0.0) B;

+----------------+--------+------------+
| transaction_id | amount | cumulative |
+----------------+--------+------------+
| 1                    |      1000 |       1000 |
| 2                    |          50 |       1050 |
| 3                    |      2000 |       3050 |
| 4                    |        211 |       3261 |
+----------------+--------+------------+

Wednesday, 21 January 2015

Unit Testing: How to write test cases?

Unit tests for test functions in isolation, i.e. you call a function with mock up-parameters and verify the results. Ideally, you check both success and failure response. A schematic example. Say you have a function like this:
def myfunc(a, b):
    return a / b
Then a unit test could be like this:
def test_myfunc():
    result = myfunc(4, 2)
    if result != 2:
        raise AssertionError("Wrong result: should have been 2, but was %s." % result)
So you're writing another function to call the function that is to be tested, with parameters for which you know the result, in order to prove that the function really returns the expected result.
Typically, you should have at least one test call for each distinct type of case - e.g. for myfunc() this would additionally be the cases where either parameter is 0.

Now, in order to write unit tests for any module, you need to look at each function and understand what results it should deliver for which input - and try to find a way to inspect and verify the result. Then identify the assertions (=expected results) for each type of case, and encode it as a test case

You do not need to test the overall functionality - all we need to know is whether the parts still do after the refactoring what they did before, the results are still correct, and no new bugs have been introduced (=regression testing)

ERPNext: Print Format Of Sales Invoice

{%- macro add_header(page_num, max_pages, doc, letter_head, no_letterhead) -%}
    {% if letter_head and not no_letterhead %}
    <div class="letter-head">{{ letter_head }}</div>
    {% endif %}
    {%- if doc.meta.is_submittable and doc.docstatus==0-%}
    <div class="alert alert-info text-center">
        <h4 style="margin: 0px;">{{ _("DRAFT") }}</h4></div>
    {%- endif -%}
    {%- if doc.meta.is_submittable and doc.docstatus==2-%}
    <div class="alert alert-danger text-center">
        <h4 style="margin: 0px;">{{ _("CANCELLED") }}</h4></div>
    {%- endif -%}
    {% if max_pages > 1 %}
    <p class="text-right">{{ _("Page #{0} of {1}").format(page_num, max_pages) }}</p>
    {% endif %}
{%- endmacro -%}

<small>
{{ add_header(0,1,doc,letter_head, no_letterhead) }}

<p class="text-center"><b>{{ _("Sales Invoice") }}</b></p><br>

<div class="row">       
     <div class="col-xs-6">           
        <div class="row">
            <div class="col-xs-5 text-right"><label>Customer Name:</label></div>
            <div class="col-xs-7 ">{{ doc.customer }} </div>
        </div>
        <div class="row">
            <div class="col-xs-5 text-right"><label>Billing Address</label></div>
            <div class="col-xs-7 ">{{ doc.address_display }}</div>
        </div>
        <div class="row">
            <div class="col-xs-5 text-right"><label>Payment Due Date</div>
            <div class="col-xs-7 ">{{ doc.due_date }}</div>
        </div>         
    </div>

    <div class="col-xs-6">
        <div class="row">
            <div class="col-xs-5 text-right"><label>Invoice No:</label></div>
            <div class="col-xs-7 ">{{ doc.name }} </div>
        </div>
        <div class="row">
            <div class="col-xs-5 text-right"> <label>DATE:</label> </div>
            <div class="col-xs-7 "> {{ doc.get_formatted("posting_date") }}<br> </div>
        </div>   
    </div>
           
</div>
       
<br>

<table class="table table-condensed table-hover table-bordered">
        <tr>
            <th>Sr</th>
            <th>Description</th>
            <th class="text-right">Qty</th>
            <th class="text-right">Rate</th>
            <th class="text-right">Amount</th>
        </tr>
        {%- for row in doc.entries -%}
        <tr>
            <td style="width: 3%;">{{ row.idx }}</td>
            <td style="width: 57%;">{{ row.description }}</td>
            <td style="width: 10%; text-align: right;">{{ row.qty }} {{ row.uom or row.stock_uom }}</td>
            <td style="width: 15%; text-align: right;">{{
                row.get_formatted("rate", doc) }}</td>
            <td style="width: 15%; text-align: right;">{{
                row.get_formatted("amount", doc) }}</td>
        </tr>
        {%- endfor -%}
    </tbody>
</table>
<table class="table table-condensed table-bordered">
  <tr>
    <td width="50%">
        <p>{% if doc.terms %} <b>{{ _("Auxiliary Information") }}: </b>{{ doc.terms or "" }}
        {%- endif -%}
    </p>
       <p style="font-size:12px">{% if doc.in_words_export %}
        <b>{{ _("Total Amount (In Words)") }}: </b><br>
            {{ doc.in_words_export }}
        {%- endif -%}
    </p><br><br>
    <table class="table table-condensed">
      <tr>
        <td height="100px"; valign="top">
            <b>{{ _("Received By:") }}</b>
        </td>
      </tr>
      <tr>
        <td>
            <b>{{ _("Customer Sign") }}</b>
        </td>
      </tr>
    </table>

    </td>
<td>
        <table class="table table-condensed">
            <tr>
                <td class="text-right" style="width: 30%">
                    {{ _("Net Total") }}
                </td>
                <td class="text-right">
                    {{ doc.get_formatted("net_total_export") }}
                </td>
            </tr>
            {%- for row in doc.other_charges -%}
            {%- if not row.included_in_print_rate -%}
            {%- if row.tax_amount -%}
            <tr height:100%>
                <td class="text-right" style="width: 70%">
                    {{ row.description }}
                </td>
                <td class="text-right">
                    {{ row.get_formatted("tax_amount", doc) }}
                </td>
            </tr>
            {%- endif -%}
            {%- endif -%}
            {%- endfor -%}
            {%- if doc.discount_amount -%}
            <tr>
                <td class="text-right" style="width: 70%">
                    {{ _("Discount") }}
                </td>
                <td class="text-right">
                    {{ doc.get_formatted("discount_amount") }}
                </td>
            </tr>
            {%- endif -%}
            <tr>
                <td class="text-right" style="width: 70%">
                    <big><b>{{ _("Grand Total") }}</b></big>
                </td>
                <td class="text-right">
                    <big><b>{{ doc.get_formatted("grand_total_export") }}</b></big>
                </td>
            </tr>
        </td>

        </table>

    </td>
  </tr>
</table>
{% if doc.get("other_charges", filters={"included_in_print_rate": 1}) %}
<hr>
<p><b>Taxes Included:</b></p>
<table class="table table-condensed no-border">
    <tbody>
        {%- for row in doc.other_charges -%}
        {%- if row.included_in_print_rate -%}
        <tr>
            <td class="text-right" style="width: 70%">

                {{ row.description }}
            </td>
            <td class="text-right">
                {{ row.get_formatted("tax_amount", doc) }}
            </td>
        <tr>
        {%- endif -%}
        {%- endfor -%}
    </tbody>
</table>
{%- endif -%}
<hr>
</small>

Monday, 19 January 2015

Delivery Note Print Format


{%- macro add_header(page_num, max_pages, doc, letter_head, no_letterhead) -%}
    {% if letter_head and not no_letterhead %}
    <div class="letter-head">{{ letter_head }}</div>
    {% endif %}
    {% if max_pages > 1 %}
    <p class="text-right">{{ _("Page #{0} of {1}").format(page_num, max_pages) }}</p>
    {% endif %}
{%- endmacro -%}


<p class="text-center"><b>{{ _("PRODUCT RELEASE") }}</b></p><br>

<div class="row">      
        <div class="col-xs-6">          
        <div class="row">
            <div class="col-xs-5 text-right"><label>SHIP TO:</label></div>
            <div class="col-xs-7 ">{{ doc.customer }} </div>
        </div>
        <div class="row">
            <div class="col-xs-5 text-right"></div>
            <div class="col-xs-7 ">{{ doc.shipping_address }}</div>
        </div>
         <div class="row">
            <div class="col-xs-5 text-right"><label>ATTN:</label></div>
            <div class="col-xs-7 "> {{ doc.contact_display }}</div></div>          
        </div>
     
        <div class="col-xs-6">
         <div class="row">
            <div class="col-xs-5 text-right"> <label>RELEASE DATE:</label> </div>
            <div class="col-xs-7 "> {{ doc.get_formatted("posting_date") }}<br>
            </div>
        </div>
         <div class="row">
          <div class="col-xs-5 text-right"> <label>RELEASE:</label></div>
            <div class="col-xs-7 "> {{ doc.name }} </div>
        </div>
         <div class="row">
            <div class="col-xs-5 text-right"> <label>SHIP DATE:</label> </div>
            <div class="col-xs-7 "> {{ doc.get_formatted("lr_date") }}<br> </div>
        </div>
     
         <div class="row">
            <div class="col-xs-5 text-right"><label>BUYER’S P.O.:</label>  </div>
            <div class="col-xs-7 "> {{ doc.po_no }} </div>
        </div>      
        <div class="row">
            <div class="col-xs-5 text-right"><label>SALE REP SM:</label>  </div>
            <div class="col-xs-7 "> </div>
        </div>
         <div class="row">
            <div class="col-xs-5 text-right"><label>SHIPPING TERMS:</label>  </div>
            <div class="col-xs-7 "> {{ doc.terms }}</div>
        </div>    
        </div>
         
        </div>
     
<br>
<table class="table table-condensed table-hover table-bordered">
        <tr>
            <th>Sr</th>
            <th class="text-right">Item</th>
            <th>Description</th>
            <th>Quantity</th>
        </tr>
        {%- for row in doc.delivery_note_details -%}
        <tr>
            <td style="width: 3%; text-align: right;">{{ row.idx }}</td>
            <td style="width: 10%; text-align: right;">{{ row.item_name }}</td>
            <td style="width: 37%; text-align: left;">
<b>Batch No: </b>{{ row.batch_no }}
<br><b>Quantity in Weight: </b>{{ row.quantity_in_weight }}{{ row.weight_uom }}
<br><b>Quantity : </b>{{ row.qty }} {{ row.stock_uom }}
<br><b>Net Weight: </b>{{ row.net_weight }}
</td>
          <td style="width: 20%;"> {{ row.quantity_in_weight}} {{ row.weight_uom }}</td>
        </tr>
        {%- endfor -%}
    </tbody>
</table>


<p class="text-left"><label>COMMENTS:</label></p>
<p class="text-left"><b>To Customer:</b></p>
<p class="text-left">Buyer to arrange trucking</p>
<p class="text-left">Trucker to bring pallet(s) for exchange; otherwise $10/pallet will be assessed on your invoice.</p><br>
<p class="text-left"><b>To Warehouse:  </b> </p>
<p class="text-left">Please inspect condition of packages prior to loading order.  Only release packages with label, seal, exterior, etc. intact.</p>
<p class="text-left"><b>Tracking: </b> </p><br>
<p class="text-left"><h5>We appreciate your business!<h5></p>
<p class="text-left"><h5>Thank you.</h5></p>


Note: Print Format, Don't write any external style