Script Report: Automatic Calculation of Re Order Level

120 views
Skip to first unread message

Addy

unread,
May 26, 2013, 4:13:29 PM5/26/13
to erpnext-dev...@googlegroups.com
Hi,

I am trying to write a script report for getting the re order levels as per auto calculation. But since this is my first script report, I am facing trouble to get things done right maybe some one can help me out with the things I am doing wrong.

What this report would do:
  1. List all the items from item table along with their Re-Order Level (Not warehouse wise though)
  2. DN Qty = Sum of all the DN of an item in a specified date range
  3. No of DN = Total Number of DNs in the specified period.
  4. Stock Entry Quantity = Sum of Qty in Stock Entry in the period.
  5. Average quantity in a month for DN = Item No 2/ (Period in months)
  6. Avg of Stock entry in a month = Item no 4/ (period in months)
  7. Total Average = 5 + 6
Now I am trying to write a report but getting some errors like:

[01:40:35.719] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 154, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 189, in execute_cmd
    ret = call(method, webnotes.form_dict)
  File "../lib/webnotes/handler.py", line 206, in call
    return fn(**newargs)
  File "../lib/webnotes/widgets/query_report.py", line 69, in run
    columns, result = webnotes.get_method(method_name)(filters or {})
  File "../app/stock/report/calculate_rol/calculate_rol.py", line 25, in execute
    data = get_sl_entries(filters)
  File "../app/stock/report/calculate_rol/calculate_rol.py", line 45, in get_sl_entries
    where voucher_type = "Delivery Note" %s group by item_code """ % conditions ,as_dict=True)
  File "../lib/webnotes/db.py", line 130, in sql
    raise e
 ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Entry \n\t\twhere voucher_type = "Delivery Note"  and posting_date >= 2013-05-01 an\' at line 1')


Now the python file I am using is attached. I know there is something wrong with the way I am using the %s but I am not able find the error even after many attempts. Also I would request if the report I am making is correct or not.
calculate_rol.py

Nabin Hait

unread,
May 27, 2013, 12:16:16 AM5/27/13
to erpnext-dev...@googlegroups.com
Use `tabStock Ledger Entry` instead of tabStock Ledger Entry.

On 27-May-2013, at 1:43 AM, Addy wrote:

<calculate_rol.py>

Addy

unread,
May 28, 2013, 1:14:52 AM5/28/13
to erpnext-dev...@googlegroups.com
Hi Nabin,

Thanks for pointing out the omission. I am facing another problem which is that I have 3 queries which I now need to join so that I could get the result in a consolidated format but I am unable to find any help on that, it would be great if you could guide me as to how should I join the 3 queries to get the consolidated result, I am attaching the report py file.
calculate_rol.py

Rushabh Mehta

unread,
May 28, 2013, 2:50:08 AM5/28/13
to erpnext-dev...@googlegroups.com
Aditya,

You will have to join the data manually using loops. Also please share code files using gist / pastebin so people can post edits / suggestions.

- Rushabh


W: https://erpnext.com
T: @rushabh_mehta

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.
To post to this group, send email to erpnext-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e00d7c4e-4abc-49bb-8cba-46ea9bf3e0c3%40googlegroups.com?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
<calculate_rol.py>

Addy

unread,
May 28, 2013, 3:14:29 AM5/28/13
to erpnext-dev...@googlegroups.com
Sorry,

Here is the gist.


On Tuesday, May 28, 2013 12:20:08 PM UTC+5:30, rushabh wrote:
Aditya,

You will have to join the data manually using loops. Also please share code files using gist / pastebin so people can post edits / suggestions.

- Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 28-May-2013, at 10:44 AM, Addy <adi...@rigpl.com> wrote:

Hi Nabin,

Thanks for pointing out the omission. I am facing another problem which is that I have 3 queries which I now need to join so that I could get the result in a consolidated format but I am unable to find any help on that, it would be great if you could guide me as to how should I join the 3 queries to get the consolidated result, I am attaching the report py file.



On Monday, May 27, 2013 9:46:16 AM UTC+5:30, Nabin Hait wrote:
Use `tabStock Ledger Entry` instead of tabStock Ledger Entry.

On 27-May-2013, at 1:43 AM, Addy wrote:

<calculate_rol.py>


--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

Rushabh Mehta

unread,
May 28, 2013, 3:21:04 AM5/28/13
to erpnext-dev...@googlegroups.com
Aditya,

You can just merge the results in Python. Python has some great tools for doing it and is quite easy. For example you can add two lists as data1 + data2

More here.


best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

Addy

unread,
May 28, 2013, 11:04:46 AM5/28/13
to erpnext-dev...@googlegroups.com
Hi Rushabh,

Thanks for the link, really helped me alot with the lists. I am now facing a problem where I need to get the calculated field and I am unable to handle the fields where the value to be calculated is none type. How could I do that since I am getting this error:

[20:29:50.347] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 154, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 189, in execute_cmd
    ret = call(method, webnotes.form_dict)
  File "../lib/webnotes/handler.py", line 206, in call
    return fn(**newargs)
  File "../lib/webnotes/widgets/query_report.py", line 69, in run
    columns, result = webnotes.get_method(method_name)(filters or {})
  File "../app/stock/report/calculate_rol/calculate_rol.py", line 25, in execute
    data = get_sl_entries(filters)
  File "../app/stock/report/calculate_rol/calculate_rol.py", line 100, in get_sl_entries
    data += [(float(d)/diff)*30]
 TypeError: float() argument must be a string or a number


And the problem with the part of the code is mentioned below for ease, rest of the code is at the gist.

i= 0
while i < len(data):
#webnotes.msgprint(i)
if not data[i][3]:
data += [(float(data[i][3])/diff)*30]
else:
data += [None]
i +=1

Rushabh Mehta

unread,
May 29, 2013, 1:08:22 AM5/29/13
to erpnext-dev...@googlegroups.com
Like the trace tells you, the value passed to the float function must be integer or string. Its probably None

so use

data += [(float(data[i][3] or 0)/diff)*30]



To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

Rushabh Mehta

unread,
May 29, 2013, 4:25:36 AM5/29/13
to erpnext-dev...@googlegroups.com
Aditya if this works out, do send us the final version.

Or better, a pull request so it is officially recorded as your contribution :)

For that you must 

- set developer_mode=1
- login as Administrator,
- save the report (files will be created in the code folder)
- commit your changes
- push on your fork
- send us a pull request from GitHub

best,
Rushabh




--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.
To post to this group, send email to erpnext-dev...@googlegroups.com.



--



Twitter: @rushabh_mehta

Aditya Duggal

unread,
May 29, 2013, 6:01:00 AM5/29/13
to erpnext-dev...@googlegroups.com
Rushabh,

I am still stuck on the last error and I am trying to figure out the problem once done I would update my gist since I have no idea about the forking and pushing on github and I would really need an idiot's guide on that....I hope you understand I am not a coder just a coding enthusiast nothing more.


Aditya Duggal

unread,
May 29, 2013, 6:19:07 AM5/29/13
to erpnext-dev...@googlegroups.com
Now the thing is that data[i][3] is a float and some values are also NONE Type so how could I handle the same in the below code since I think the nonetype is creating some problems

Here is a msgprint of the data[i] to ensure that I am not making the same mistake as earlier:

[u'C0BE269E7', u'Carbide NB 0X BNEM \xd83x16x50 Z= 2', None, None, None, 0]
[u'C0BE269F5', u'Carbide NB 0X BNEM \xd84x22x50 Z= 2', None, None, None, 100.0]
[u'C0BE269G3', u'Carbide NB 0X BNEM \xd85x22x50 Z= 2', None, None, None, 150.0]
[u'C0BE269H1', u'Carbide NB 0X BNEM \xd85x35x75 Z= 2', None, None, None, 0]
[u'C0BE269J6', u'Carbide NB 0X BNEM \xd86x22x50 Z= 2', None, None, None, -196.0]
[u'C0BE2A01H5', u'Carbide ANUBIS 0X BNEM \xd83x16x50 Z= 2', None, None, None, 0]
[u'C0BE2A01J0', u'Carbide ANUBIS 0X BNEM \xd84x22x50 Z= 2', None, None, None, 0]
[u'C0BE2A01K8', u'Carbide ANUBIS 0X BNEM \xd85x22x50 Z= 2', None, None, None, 0]
[u'C0BE2A01L6', u'Carbide ANUBIS 0X BNEM \xd85x35x75 Z= 2', None, None, None, 0]
[u'C0BE2A01M4', u'Carbide ANUBIS 0X BNEM \xd86x22x50 Z= 2', None, None, None, 0]
[u'C0BE2A69K7', u'Carbide ANUBIS 0X BNEM \xd82x6x\xd83x38 Z= 2', None, None, None, 0]
[u'C0BE2R6990', u'Carbide ROHIT 0X BNEM \xd83x16x50 Z= 2', None, None, None, 0]
[u'C0BE2R69A2', u'Carbide ROHIT 0X BNEM \xd84x22x50 Z= 2', None, 100.0, 2, -100.0]
[u'C0BE2R69B0', u'Carbide ROHIT 0X BNEM \xd85x22x50 Z= 2', None, 100.0, 1, -150.0]
[u'C0BE2R69C8', u'Carbide ROHIT 0X BNEM \xd85x35x75 Z= 2', None, None, None, 0]
[u'C0BE2R69D5', u'Carbide ROHIT 0X BNEM \xd86x22x50 Z= 2', None, 486.0, 2, -818.0]
[u'C0BE2R6BH8', u'Carbide ROHIT 0X BNEM \xd86x35x75 180\xb0 Z= 2', None, 10.0, 1, -50.0]
[u'C0BE2R6DX0', u'Carbide ROHIT 0X BNEM \xd84x35x75 180\xb0 Z= 2', None, 50.0, 2, -80.0]
[u'C0BE46B77', u'Carbide NB 0X BNEM \xd85x22x50 Z= 4', None, None, None, -52.0]
[u'C0BE46B85', u'Carbide NB 0X BNEM \xd84x22x50 Z= 4', None, None, None, -48.0]
[u'C0SE21A01N2', u'Carbide ANUBIS 0X TiAlN SQEM \xd81x4x\xd83x38 Z= 2', None, None, None, 0]

I am still not able to figure out the problem:

Code used is:

i= 0
while i < len(data):
if not (data[i][3]):
data += [0]
else:
data += [((float(data[i][3])  /diff)*30)]
i +=1

Error:

--

  File "../app/stock/report/calculate_rol/calculate_rol.py", line 98, in get_sl_entries
    if not (data[i][3]):
 TypeError: 'int' object is unsubscriptable

Anand Doshi

unread,
May 29, 2013, 8:03:32 AM5/29/13
to erpnext-dev...@googlegroups.com
Hi Aditya,

At the top of the python file, write:

from webnotes.utils import flt


and use the flt function instead of float

Thanks,
Anand.

Aditya Duggal

unread,
May 29, 2013, 2:31:43 PM5/29/13
to erpnext-dev...@googlegroups.com
Hi Anand,

I have tried to replace the float with flt and I am still getting the same error:
TypeError: 'int' object is unsubscriptable

Maybe I am overlooking something simple or is it something more complex. I did not knew that getting a calculated field out of the result is going to take soo long for me to figure out, I thought this would be the simplest job.



Rushabh Mehta

unread,
May 30, 2013, 1:15:20 AM5/30/13
to erpnext-dev...@googlegroups.com
Aditya,

It means data[i] is an integer not a list as expected in data[i][3]

do a msgprint on data[i]

to find out.

best,
Rushabh

W: https://erpnext.com
T: @rushabh_mehta

Aditya Duggal

unread,
May 30, 2013, 1:24:22 AM5/30/13
to erpnext-dev...@googlegroups.com
Dear Rushabh,

I have already given the msgprint of data[i] in my previous post which clearly shows that data[i] is a list with [4] item being either nonetype or float.


Rushabh Mehta

unread,
May 30, 2013, 1:27:13 AM5/30/13
to erpnext-dev...@googlegroups.com
Aditya,

Chances are that the computer is not wrong ;)

are you printing data instead of data[i]?

- Rushabh


W: https://erpnext.com
T: @rushabh_mehta

Aditya Duggal

unread,
May 30, 2013, 7:00:54 AM5/30/13
to erpnext-dev...@googlegroups.com
Hi,

I finally have found a workaround but still the earlier errors are mystery. Now this report is also complete with some things where it is lacking:
  1. The columns which are based on calculations are showing as strings or something since their sorting is all screwed up.
  2. All zero values cannot be converted to NULL values again in the calculated fields which are column no [5 to 8]
You can check the gist here.

The report is working fine apart from the calculated value fields which not getting sorted and I am also not able to convert the zero to null values.

Let me know if you are able to find the solution to the same.


Rushabh Mehta

unread,
May 31, 2013, 1:14:03 AM5/31/13
to erpnext-dev...@googlegroups.com
Aditya,

You will have to sort the table in python before sending it. Something like:

data = sorted(data, key = lambda d: d[3])

if you want it sorted by the 3rd column

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

Aditya Duggal

unread,
May 31, 2013, 2:47:29 AM5/31/13
to erpnext-dev...@googlegroups.com
Dear Rushabh,

I think you have not understood what I had meant.
I am actually saying that the fields which are calculated are not getting sorted as per the float or Int mentioned in get_columns and those columns are being considered as text only.

The column are the last 3 columns so something is not right as these columns should have been int or float but are being considered as Text only when I click on the column heading to sort, however if I try to search by filters on the column then they act as if they are numbers like 101:106 field would only show items between 101 and 106 only.

So if you could really help me with the sorting part and how to remove zeros from the fields that would be great.


Anand Doshi

unread,
May 31, 2013, 3:12:05 AM5/31/13
to erpnext-dev...@googlegroups.com
Hi Aditya,

To remove zeroes, check if the value is zero when inserting into the array and if it is 0, insert None.

Sorting should work. Check Accounts Receivable report's Age field. Even your gist looks okay. Maybe you changed something and didn't update in the gist.

Thanks,
Anand.

Addy

unread,
Jun 2, 2013, 1:41:38 AM6/2/13
to erpnext-dev...@googlegroups.com
Hi,

I have finally made the report as it should be, that is, up to my satisfaction point. It turns out all the errors regarding floats and also my inability to change Zero values to None was due to one simple mistake I was doing in the code, the mistake was that I was inserting a list instead of the float. The example of the erroneous code is below:

data[i].insert (6,[None])

Now I have changed the above part to 

data[i].insert (6,None)

Notice I am using now None instead of [None] turns out I was inserting a list and hence all the errors were happening even with floats.

I have updated the gist which can be found here.

I would like to mention here that I am using insert since in my custom report I have many other columns which would come at the end of the report and hence insert seems a better option, I hope insert does not create a lot of performance issues.

Finally this report is over after a long time.
Aditya,

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.



--



Twitter: @rushabh_mehta

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.


--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsub...@googlegroups.com.

Nabin Hait

unread,
Jun 3, 2013, 7:05:23 AM6/3/13
to erpnext-dev...@googlegroups.com
Hi Aditya,
Thanks for sharing the code via gist.

We will add the report in erpnext as standard report soon, for now creating an issue in github (https://github.com/webnotes/erpnext/issues/418).

Regards,
Nabin Hait


To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer...@googlegroups.com.

To post to this group, send email to erpnext-dev...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages