Invoice manipulation
Avatar image
Apr 16, 2013
Ron wrote
Hi, on our invoices we have product descriptions with additional attributes. So if the product is "screw" and the attribute: Pack size is say 10, then the description being shown on the invoice is "Screw Quantity : 10" and that is great.

In a spreadsheet or database I can extract the last few characters of a field using field length and right() so for example i could use right(2) and it will give me 10

Is it possible in the report tool editor to use a function similar to "Right" so that I can extract the last few characters (Which I will then use in a calculation"

So in other works is there anyway to make "Screw Quantity : 10" just say "10"

OR

can I grab just the attribute data?
Reply
7 Answers
Avatar image
Apr 17, 2013
Andrii Yanosh agent wrote
Hello Ron,

Could you please provide us with the screen capture of the invoice, so we could check it more deeply and advise you on this regard.
Avatar image
Apr 17, 2013
Ron wrote
Thanks for the reply

I have attached a screen shot. You will see the customer has bought 1 pack of 5 (the five is a prestashop attribute - most would use coluurs e.g red shoes, but we have them as pack sizes)

We have the invoices barcoded so that we can book out the stock from our database. As this invoice stands it would book out "1" turnbuckle. I want to take the attribute "5" and multiply by the qty sold so that we have the total number bought

Therefore if customer buys 1 pack of 5, we can book out 5, if they buy 2 packs of 5 we can book out 10, if they buy 2 packs of 10 we can book out 30 etc

Hope that makes sense

In essence how can I get at the "5" at the end of the product description
Avatar image
Apr 18, 2013
Andrii Yanosh agent wrote
Thank you for screen capture.

You have to retrieve the total quantity of the product in the invoice.
We assume that you understand that in order to proceed with these actions you must have at least basic MySQL skills:

1. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\SQL\Invoice15.sql"
*use file 13 or 14 if you have older PrestaShop website.

2. Add a line to select:
RIGHT(T2.product_name,1) * T2.product_quantity AS 'final_qty'
*don't forget about comma :-)

3. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\Invoice.fr3"

4. Insert a new column under MasterData1 section

5. In new Memo write Text value:
[frxInvoiceDS."final_qty"]

6. Should look something like this:
http://screencast.com/t/5Gw2x0pvZ

After that you should have additional column in Invoice that contains "final" quantity.
Avatar image
Apr 18, 2013
Ron wrote
That is perfect

Except

I have realised a flaw in my own reasoning as soon as I saw your solution

The attribute quantity could be 10 or even a 100
So for example "M10 Bolts - Quantity : 100"

So I would need
RIGHT(T2.product_name,3) if the attribute was 100

Can I do what I can in a spreadsheet and return all the data AFTER the colon?

Ahh, and another issue, some products don't have attributes so RIGHT(T2.product_name,1) would the last letter of the description which would then cause an error because it is not numeric. So can we don something like

If there is a colon in the description take all the data after the colon (and convert to numeric?), else use "1"?
Avatar image
Apr 22, 2013
Andrii Yanosh agent wrote
1. Yes there is a MySQL function that does what you need. Please use this:
TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity AS 'final_qty'

2. You may also use brief MySQL help yourself:
http://screencast.com/t/K1mD1zNH

or official Oracle MySQL documentation at:
http://dev.mysql.com/doc/
Avatar image
Apr 22, 2013
Ron wrote
Brilliant, thanks for your help

I combined the trim with an If statement. By testing if TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity is true tells me if it is numeric or not. If true, I return that value, if not I keep the original quantity

IF (TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity,TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity, T2.product_quantity) AS final_qty ,
Avatar image
Apr 23, 2013
Andrii Yanosh agent wrote
Thank you for keeping us informed!