X-Cart forums - PO Numbers on Invoices, Searchable in Orders.

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - PO Numbers on Invoices, Searchable in Orders. (https://forum.x-cart.com/showthread.php?t=31653)

PO Numbers on Invoices, Searchable in Orders.

I've been asked to include this modification for people who want to have order-based Purchase Order Numbers, pulling that info from the encrypted field and making it searchable in the admin Orders section. This mod also includes PO Numbers on invoices.

Caveat: In this mod, the new PO Number field is not encrypted. If that's important, then you must modify further to include encryption.

This has been tested on X-Cart 4.1.7. You may wish to modify further for other versions of X-Cart.

Don't forget -- Step One is to create a new field in the database. Steps Two through Ten follow in the next post.

Step 1: Create a new database field with phpMyAdmin or another method in the xcart_orders table directly after "extra," called po_number. (Varchar(50), not null).

see next post for more... :D

Re: PO Numbers on Invoices, Searchable in Orders.

Step 2: Add this to the bottom of xcart_root_folder/include/func/func.db.php: --(See reply #6 for a fix.)

Code:

function getPONum($o_details) {

        if (strpos($o_details, "PO Number") > -1) {

                $po_number_arr = explode(' ',$o_details);

                        for ($int=0; $int<count($po_number_arr); $int++) {

                                if ((strpos($po_number_arr[$int], "PO") > -1)&&(strpos($po_number_arr[$int + 1], "Number") > -1)) {

                                        $po_num = $po_number_arr[$int + 2];

                                        break;

                                }

                        }

                return $po_num;

        } else {

                return "";

        }

}

Step 3: The above will not work if there are spaces in the PO Number, so modify xcart_root_folder/skin1/customer/main/payment_po.tpl:
Add this script after the "required fields" script (just before the table tag):

Code:

{literal}

<script type="text/javascript">

<!--

function ckFld() {

 var fld = document.checkout_form.PO_Number;

 if (fld.value.indexOf(' ') > -1) { 

        alert('There can be no spaces in this field.'); 

        fld.value = fld.value.replace(' ','');

        fld.focus(); 

 }

} 

-->

</script>

{/literal}

and change this line:

Code:

<input type="text" size="32" id="PO_Number" name="PO_Number" />

to this:

Code:

<input type="text" size="32" id="PO_Number" name="PO_Number" onChange="ckFld();" />

Step 4: Find the array in xcart_root_folder/include/func/func_order.php that begins:

Code:

$insert_data = array (

and insert "po_number" at the end of it (around line 579) by replacing this:

Code:

'extra' => addslashes(serialize($_extra)));

with the following, so that the last two lines in the array read:

Code:

        'extra' => addslashes(serialize($_extra)),

        'po_number' => addslashes(getPONum($order_details)));

Step 5: Modify xcart_root_folder/include/orders.php:

Find the array:

Code:

$advanced_options = array("orderid1", "orderid2", "total_max", "payment_method", "shipping_method", "status", "provider", "features", "product_substring", "productcode", "productid", "price_max", "customer", "address_type", "phone", "email");

and replace it with this to add "po_number" so that it reads:

Code:

$advanced_options = array("orderid1", "orderid2", "total_max", "payment_method", "po_number", "shipping_method", "status", "provider", "features", "product_substring", "productcode", "productid", "price_max", "customer", "address_type", "phone", "email");

In the same file, find:

Code:

if (!empty($sort) && in_array($sort, array("orderid","status","customer","date","provider", "total"))) {

and change it to include "po_number" as well, to read:

Code:

        if (!empty($sort) && in_array($sort, array("orderid","status","customer","date","po_number","provider", "total"))) {

Then, find this "if" clause (around line 182):

Code:

        # Search by payment method

        if (!empty($data["payment_method"]))

                $search_condition .= " AND $sql_tbl[orders].payment_method LIKE '".$data["payment_method"]."%'";

keep it there and add another search clause below it, (before "# Search by shipping method"):

Code:

        # Search by po number

        if (!empty($data["po_number"]))

                $search_condition .= " AND $sql_tbl[orders].po_number LIKE '%".$data["po_number"]."%'";

The last mod on this file is to find this case (around line 420):

Code:

        case "date":

                $sort_string = "$sql_tbl[orders].date $direction";

                break;

keep it there and add this case below it:

Code:

        case "po_number":  // added for po number

                $sort_string = "($sql_tbl[orders].po_number + 0) $direction";

                break;

Step 6: In the file xcart_root_folder/admin/order.php, find this line (around line 190):

Code:

$query_data['details'] = func_crypt_order_details($details);

and add this line directly under it:

Code:

$query_data['po_number'] = getPOnum($details);

Step 7: Edit the file xcart_root_folder/skin1/mail/html/order_invoice.tpl:

Find this line:

Code:

<b>{$lng.lbl_payment_method}:</b><br />{$order.payment_method}<br /><b>{$lng.lbl_delivery}:</b><br />{$order.shipping|trademark|default:$lng.txt_not_available}

and change it to:

Code:

<b>{$lng.lbl_payment_method}:</b><br />{$order.payment_method}<br /><b>{$lng.lbl_po_number}:</b> {$order.po_number}<br /><b>{$lng.lbl_delivery}:</b><br />{$order.shipping|trademark|default:$lng.txt_not_available}

Step 8: Edit the file xcart_root_folder/skin1/main/order_info.tpl:

After the table row:

Code:

<tr> 

        <td valign="top">{$lng.lbl_payment_method}</td>

        <td valign="top">{$order.payment_method}</td>

</tr>

add this new table row:

Code:

<tr> 

        <td valign="top">{$lng.lbl_po_number}</td>

        <td valign="top">{$order.po_number}</td>

</tr>

Step 9: Edit the file xcart_root_folder/skin1/main/order_list.tpl:

Find the section:

Code:

<tr class="TableHead">

        <td width="5">&nbsp;</td>

        <td width="5%" nowrap="nowrap">{if $search_prefilled.sort_field eq "orderid"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=orderid">#</a></td>

        <td nowrap="nowrap">{if $search_prefilled.sort_field eq "status"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=status">{$lng.lbl_status}</a></td>

        <td width="30%" nowrap="nowrap">{if $search_prefilled.sort_field eq "customer"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=customer">{$lng.lbl_customer}</a></td>

{if $usertype eq "A" and $single_mode eq ""}

{assign var="colspan" value=7}

        <td width="20%" nowrap="nowrap">{if $search_prefilled.sort_field eq "provider"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=provider">{$lng.lbl_provider}</a></td>

{/if}

        <td width="20%" nowrap="nowrap">{if $search_prefilled.sort_field eq "date"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=date">{$lng.lbl_date}</a></td>

        <td width="20%" align="right" nowrap="nowrap">{if $search_prefilled.sort_field eq "total"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=total">{$lng.lbl_total}</a></td>

</tr>

and replace it with this:

Code:

<tr class="TableHead">

        <td width="5">&nbsp;</td>

        <td width="5%" nowrap="nowrap">{if $search_prefilled.sort_field eq "orderid"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=orderid">#</a></td>

        <td nowrap="nowrap">{if $search_prefilled.sort_field eq "status"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=status">{$lng.lbl_status}</a></td>

        <td width="35%" nowrap="nowrap">{if $search_prefilled.sort_field eq "customer"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=customer">{$lng.lbl_customer}</a></td>

{if $usertype eq "A" and $single_mode eq ""}

{assign var="colspan" value=7}

        <td width="20%" nowrap="nowrap">{if $search_prefilled.sort_field eq "provider"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=provider">{$lng.lbl_provider}</a></td>

{/if}

        <td width="20%" nowrap="nowrap">{if $search_prefilled.sort_field eq "date"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=date">{$lng.lbl_date}</a></td>

        <td width="15%" nowrap="nowrap">{if $search_prefilled.sort_field eq "po_number"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=po_number">{$lng.lbl_po_number}</a></td>

        <td width="10%" align="right" nowrap="nowrap">{if $search_prefilled.sort_field eq "total"}{include file="buttons/sort_pointer.tpl" dir=$search_prefilled.sort_direction}&nbsp;{/if}<a href="orders.php?mode=search&amp;sort=total">{$lng.lbl_total}</a></td>

</tr>

Find this line:

Code:

        <td nowrap="nowrap"><a href="order.php?orderid={$orders[oid].orderid}">{$orders[oid].date|date_format:$config.Appearance.datetime_format}</a></td>

and add this line directly under it:

Code:

<td nowrap="nowrap"><a href="order.php?orderid={$orders[oid].orderid}">{$orders[oid].po_number}</a></td>

Step 10: Edit the file xcart_root_folder/skin1/main/orders.tpl:

Find the line:

Code:

var searchform_def = [

and at the end of that entire array insert:

Code:

['posted_data[po_number]', '']

after the 'posted_data[status]' line.

Now find the table row:

Code:

<tr>

        <td class="FormButton" nowrap="nowrap">{$lng.lbl_payment_method}:</td>

        <td width="10">&nbsp;</td>

        <td>

        <select name="posted_data[payment_method]" style="width:70%">

                <option value=""></option>

{section name=pm loop=$payment_methods}

                <option value="{$payment_methods[pm].payment_method}"{if $search_prefilled.payment_method eq $payment_methods[pm].payment_method} selected="selected"{/if}>{$payment_methods[pm].payment_method}</option>

{/section}

        </select>

        </td>

</tr>

and add this row after it:

Code:

<tr>

        <td class="FormButton" nowrap="nowrap">{$lng.lbl_po_number}:</td>

        <td width="10">&nbsp;</td>

        <td>

        <input type="text" name="posted_data[po_number]" size="30" value="{$search_prefilled.po_number}" style="width:70%" />

        </td>

</tr>

That's it. :D

Re: PO Numbers on Invoices, Searchable in Orders.

Hello ...
This is great :) It's working fine ... apart from just one thing ..

I'm not sure if it's the function getPONum() but as well as attaching the PO Number is also grabbing the first word of the next line (In this case it's Company Name)

So when i look at the database the po_number field is showing "5361 Company"
(5361 is the actual po number i entered)

Any ideas ?

Thanks A Lot ...

Regards

Shafiq :sK

Re: PO Numbers on Invoices, Searchable in Orders.

Oh, yes...

We removed the Company name and Name of purchaser from our po orders, so, no wonder this didn't come up! My function finds spaces, but not line returns. So here's a possible fix:

Try this function instead in your file x-cart_root/include/func/func.db.php:

Code:

function getPONum($o_details) {

        if (strpos($o_details, "PO Number") > -1) {

                $po_number_arr = explode(' ',$o_details);

                        for ($int=0; $int<count($po_number_arr); $int++) {

                                if ((strpos($po_number_arr[$int], "PO") > -1)&&(strpos($po_number_arr[$int + 1], "Number") > -1)) {

                                        $po_arr = explode('\n', $po_number_arr[$int + 2]);

                                        $po_num = $po_arr[0];

                                        break;

                                }

                        }

                return $po_num;

        } else {

                return "";

        }

}

Let me know if that one works! :D

Re: PO Numbers on Invoices, Searchable in Orders.

Hello ... Just tried this ... But no joy :(

Any other ideas ?

Thanks & Regards

Shafiq :sK

Re: PO Numbers on Invoices, Searchable in Orders.

Ugh. :roll:

We've got to find how your system registers line breaks.

So, try this function at the bottom of x-cart_root/include/func/func.db.php instead:

Code:

function getPONum($o_details) {

        if (strpos($o_details, "PO Number") > -1) {

                $o_string = preg_replace("/\r\n|\n|\r/", " ", $o_details);

                $po_number_arr = explode(' ',$o_string);

                for ($int=0; $int<count($po_number_arr); $int++) {

                        if ((strpos($po_number_arr[$int], "PO") > -1)&&(strpos($po_number_arr[$int + 1], "Number") > -1)) {

                                $po_num = $po_number_arr[$int + 2];

                                break;

                        }

                }

                        return $po_num;

        } else {

                return "";

        }

}

Does that do it? I hope so! :D

Re: PO Numbers on Invoices, Searchable in Orders.

Excellent :D !!

That did the trick ... Thanks very much !!!

Regards

Shafiq :sK

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by chamberinternet

Excellent :D !!

That did the trick ... Thanks very much !!!

Regards

Shafiq :sK

Hooray! :D

Now it's a pretty nifty mod for 4.1.x.

Cheers,

Re: PO Numbers on Invoices, Searchable in Orders.

I tried this on 4.2 ... yikes ... all kinds of stuff is broken now ... I think i backed up all the files but one :( So now to try to undo it all...

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by GreatLakesVacuum

I tried this on 4.2 ... yikes ... all kinds of stuff is broken now ... I think i backed up all the files but one :( So now to try to undo it all...

Nevermind, I screwed up a step, it works fine!

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by GreatLakesVacuum

Nevermind, I screwed up a step, it works fine!

WHEW! :-)

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by GreatLakesVacuum

Nevermind, I screwed up a step, it works fine!

Hi....... about to try this on 4.2..... should the routine in #6 be used on this version?

All the best

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by adriant

Hi....... about to try this on 4.2..... should the routine in #6 be used on this version?

All the best

Most likely it should. This code works on 4.17; if 4.2 doesn't use the variable $query_data on order.php, you may have to modify yours to find out the right array to add the po number to.

(We had no luck migrating the whole store to 4.2, so we're sticking to 4.17 for now.)

Re: PO Numbers on Invoices, Searchable in Orders.

Hi Russell

can confirm that this does work on 4.2.2 with the routine from #6

best regards
Adrian

Re: PO Numbers on Invoices, Searchable in Orders.

Works great. Except a small problem. PO Number field appears on every invoice whether the payment method is Purchase order or any other. Need to show the field on invoice, only if payment method is Purchase Order.

Re: PO Numbers on Invoices, Searchable in Orders.

customer/main/order_message.tpl

Code:

{if $order.order.payment_method eq "Purchase Order"}{$order.order.po_number}{/if}

mail/html/order_invoice.tpl (used in Print Invoice on Admin)

Code:

{if $order.payment_method eq "Purchase Order"}{$order.po_number}{/if}

That should give you a general idea of what to do.

Re: PO Numbers on Invoices, Searchable in Orders.

Thanks.

I changed mail/html/order_invoice.tpl ,

Code:

<b>{$lng.lbl_po_number}:</b> {$order.po_number}<br />

Code:

{if $order.payment_method eq "Purchase Order"}

<b>{$lng.lbl_po_number}:</b> {$order.po_number}<br />

{/if}

But this does not display PO Number field even if the payment method is Purchase Order. I don't know what is wrong.

Re: PO Numbers on Invoices, Searchable in Orders.

Never Mind, It is working now. Thanks for your help.

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by hues

Never Mind, It is working now. Thanks for your help.

Hues, did you have to do anything special to get the po_number field to appear?

I don't see the variable anywhere in the debug console on the invoice page, and when I view the order, the PO number, company, etc. are all in just big text blob. I've tried variations on {$order.po_number}, without success.

Is there some setting to allow this to function as you'd expect? 4.3.2 doesn't seem to like it.

Re: PO Numbers on Invoices, Searchable in Orders.

I just added this hack to order_invoice.tpl. It displays the PO number by using regex in the smarty template to remove everything after the first line break in $order.details.

{if $order.payment_method eq "Purchase Order"}{$order.details|regex_replace:"/[\n].*/":""}{/if}

Re: PO Numbers on Invoices, Searchable in Orders.

I finally got this mod to work on v4.4.4. It was not working when the po_number field in the database was set to VARCHAR. But when I changed it to text, it worked. Weird!

But thank you, Russell, for the great modification!

Re: PO Numbers on Invoices, Searchable in Orders.

Having an issue with getting this to work.

I can insert a table entry for po_number manually, and it'll show up just fine in searches, etc. but as soon as I go to create a new order through xcart I get an error.

Warning
403
Access denied !

You are not allowed to access that resource!

Id: 79

This error no longer occurs if I remove:

'po_number' => addslashes(getPONum($order_details)),

from func.order.php

I've tried changing the dbtype to text from varchar, but that didn't help.

Re: PO Numbers on Invoices, Searchable in Orders.

Is this working with 4.5.1 ?

Re: PO Numbers on Invoices, Searchable in Orders.

will this mode work on 4.5.2?

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by fonefun

will this mode work on 4.5.2?

Hasn't been tested higher than 4.4.x (see notgrass' post above) but you could be the first! :-)

Re: PO Numbers on Invoices, Searchable in Orders.

:-) This mod works great in 4.5.4 I'm using it

Re: PO Numbers on Invoices, Searchable in Orders.

Quote:

Originally Posted by kcmastrpc

I hate bumping old threads, but I ran across this thread while trying to determine the reason for an almost identical error while modifying a module, and wanted to provide my solution.

Clear the x-cart cache. It's that easy. I made a change to code that involves inserting data into a modified db table using the array2insert function built into x-cart. Apparently x-cart caches information about that table, and would throw this error when I'd provide 2 extra columns in the array. Clearing the cache solved it.