Script Report: Find Customers Not Buying Since Long Time

40 views
Skip to first unread message

Addy

unread,
May 29, 2013, 12:58:54 AM5/29/13
to erpnext-dev...@googlegroups.com
Hi,

I had earlier posted on user forums as to how could I find customers not buying since a long time the reference post is here.

I have some how tried to find a make shift solution for the same by making a script report gist for the same is here.

Now the only problem I am facing is that I have last column as number of days since the last Sales Order but I am somehow not able to calculate the same as I am getting the following errors for the mentioned code:

Code used: 

if not i[5]:
i.insert(6,"No SO")
else:
days_last_so = (getdate(filters.get("to_date")) - i[4]).days
i.insert(6,days_last_so)
Error:

  File "../app/selling/report/customers_with_so/customers_with_so.py", line 95, in get_so_data
    days_last_so = (getdate(filters.get("to_date")) - i[4]).days
 TypeError: unsupported operand type(s) for -: 'datetime.date' and 'int'

or 
Code Used:

if not i[5]:
i.insert(6,"No SO")
else:
days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days
i.insert(6,days_last_so)

Error:

  File "../app/selling/report/customers_with_so/customers_with_so.py", line 95, in get_so_data
    days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days
  File "../lib/webnotes/utils/__init__.py", line 144, in getdate
    if " " in string_date:
 TypeError: argument of type 'int' is not iterable

Kindly let me know what is the alternative to calculate the # of days since last SO field.

Thanks
Aditya Duggal

Rushabh Mehta

unread,
May 29, 2013, 1:13:16 AM5/29/13
to erpnext-dev...@googlegroups.com
Wow that is a complex report :)

For your last_so query don't use as_list = 1 (that will convert all date formats to strings).

or use (if you are going to print some values:

days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days



--
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/b316599f-dc6f-4360-aca7-bdf7e88cbb84%40googlegroups.com?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Aditya Duggal

unread,
May 29, 2013, 1:44:32 AM5/29/13
to erpnext-dev...@googlegroups.com
Thanks Rushabh for the commendation :D

But coming back to point, I have tried to remove the as_list=1 from the last query for last so and then I used  

days_last_so = (getdate(filters.get("to_date")) - i[4]).days

But again I get this error:

    days_last_so = (getdate(filters.get("to_date")) - i[4]).days
 TypeError: unsupported operand type(s) for -: 'datetime.date' and 'int'


And if I remove the as_list=1 from the last query and then use:

days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days

I get the below error:

  File "../app/selling/report/customers_with_so/customers_with_so.py", line 95, in get_so_data
    days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days
  File "../lib/webnotes/utils/__init__.py", line 144, in getdate
    if " " in string_date:
 TypeError: argument of type 'int' is not iterable


Basically I want to have a column where the last sales order date difference is printed. Ideally I would want to have a column with this formula:

days_last_so = today() - i[4]

where i[4] is the date of the last of sales order received.

Maybe I have not understood your explanation properly, enlighten me on your suggestion.


To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/060711E0-F3FA-4435-BE3F-86D53DE4C3A7%40erpnext.com?hl=en.

Rushabh Mehta

unread,
May 29, 2013, 1:47:09 AM5/29/13
to erpnext-dev...@googlegroups.com
Did you try keeping as_list = 1 and getdate(i[4]) ?


To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/CAEmKBHxUoD0-w7s3mBfmz7zNajybEO7tAp2CPwfC2w6%3DMQW18g%40mail.gmail.com?hl=en.

For more options, visit https://groups.google.com/groups/opt_out.
 
 



--



Twitter: @rushabh_mehta

Aditya Duggal

unread,
May 29, 2013, 1:54:41 AM5/29/13
to erpnext-dev...@googlegroups.com
Well some progress and new error:

For the record, i have used as_list=1 and getdate(i[4])

[11:22:02.967] 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/selling/report/customers_with_so/customers_with_so.py", line 25, in execute
    data = get_so_data(filters)
  File "../app/selling/report/customers_with_so/customers_with_so.py", line 95, in get_so_data
    days_last_so = (getdate(filters.get("to_date")) - getdate(i[4])).days
  File "../lib/webnotes/utils/__init__.py", line 144, in getdate
    if " " in string_date:
 TypeError: argument of type 'int' is not iterable
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/CANn8SWLMF9DiKgR-otbxz-NoW-bfBa%2BXWM%2B60dvKHpCux-iAvA%40mail.gmail.com?hl=en.

Nabin Hait

unread,
May 29, 2013, 1:57:56 AM5/29/13
to erpnext-dev...@googlegroups.com
i[4] is clearly an integer. Can you please msgprint(i), to check the value of i[4]?

Rushabh Mehta

unread,
May 29, 2013, 2:05:54 AM5/29/13
to erpnext-dev...@googlegroups.com
Aditya,

Check your "insert" logic

i.insert(5, None) will insert at index #5 not 4 as you assume in your code. also if "insert" does not find the index, it will simply append.

instead of "insert", use "append" its clearer (and one less parameter to worry about)

- Rushabh


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

Aditya Duggal

unread,
May 29, 2013, 2:06:02 AM5/29/13
to erpnext-dev...@googlegroups.com
Thanks Nabin,

Turns out I was referencing the wrong data, it should have been i[5] and hence all the hoopla. Sorry for raising such a silly mistake.


To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/CDA0A542-0482-4A88-AD0C-E3995EB02EFF%40gmail.com?hl=en.

Addy

unread,
May 29, 2013, 3:28:44 PM5/29/13
to erpnext-dev...@googlegroups.com
Hi Developers,

I have updated this report to the gist which can be found here.

I think this report is a good tool to have to find leaking sales which I guess is a problem with many small people like us, we keep on adding the new customers without giving heed to the fact that we are loosing our old clients.

It is a simple report with the following columns and take 2.5 secs to load.

The columns are

  1. Customer (All Customers from the Customer Master)
  2. Territory
  3. Total Sales Order Net Value booked in a period.
  4. Total Sales Orders' Value considered as Sales, here the calculation goes like if the SO is submitted it is checked if it is not stopped and if it is stopped then only that part which is delivered is considered as sale.
  5. No of Sales orders for the customer made in the period.
  6. Date of the last sales order.
  7. Time since the last Sales Order in number of days.
Just updated the report and you can use this freely in the standard reports.
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-developer-forum@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-developer-forum@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-developer-forum@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-developer-forum@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-developer-forum@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-developer-forum@googlegroups.com.

Rushabh Mehta

unread,
May 30, 2013, 1:16:35 AM5/30/13
to erpnext-dev...@googlegroups.com
Awesome,

Thanks Aditya! Will surely add the report.

btw if you can figure such complex reports, git will not be too hard for you to figure out, maybe an hour or so... this way there will be a public record of your contributions :)

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/acc2172a-3965-401c-b528-3dbb9cb497e5%40googlegroups.com?hl=en.

Anand Doshi

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

We had written a basic document about working on ERPNext fork.

It might help you get started on sending pull requests.

Thanks,
Anand.

Aditya Duggal

unread,
May 30, 2013, 2:50:00 AM5/30/13
to erpnext-dev...@googlegroups.com
Thanks for the help

Soon I would try to learn github as well.


Reply all
Reply to author
Forward
0 new messages