Frappe Chart connectivity to Database

ERPNext
1

I am having issue with getting chart while using dynamic data ( directly to database). With static data its printing but now with direct connectivity to database. Need help in that.

My .py file is

from __future__ import unicode_literals
import frappe
from frappe.model.document import Document


@frappe.whitelist()

def state_wise():
	state=frappe.db.sql("select state from tabLead where state is not null group by state")
	lead=frappe.db.sql("select count(*) from tabLead where state is not null group by state")
	return state,lead

and .js file is

frappe.ui.form.on('graphtesting', {
	refresh: function(frm) {

$.getScript("https://cdn.jsdelivr.net/npm/frappe-charts@1.1.0/dist/frappe-charts.min.iife.js",function(){



frappe.call({
    method: "erpnext.selling.doctype.graphtesting.graphtesting.state_wise",
    args: {"state":cur_frm.doc.state,
    		"lead":cur_frm.doc.lead}
    callback: function(r) {
    if(r.message) {
       alert(r.message.state),
       alert(r.message.lead)
    },
});


let chart = new frappe.Chart( "#chart", { 
    data: {
    	labels:[state]
      

      datasets: [
        {
          name: "Total Lead", chartType: 'bar',
          values:[lead]
          
        },
        
      ],

  
    },

    title: "State-Wise Lead Count",
    type: 'bar',
    height: 350,
    colors: ['blue'],

    tooltipOptions: {
      formatTooltipX: d => (d + '').toUpperCase(),
     formatTooltipY: d => d + ' pts',

}

    


});




});

	}
});
2

@nikzz add async: false to frappe.call function and store the values in variables in the callback so you can call it later

3

adding that also doesn’t worked

4

@nikzz show me your code

5

.js file

frappe.ui.form.on(‘graphtesting’, {
refresh: function(frm) {

$.getScript(“https://cdn.jsdelivr.net/npm/frappe-charts@1.1.0/dist/frappe-charts.min.iife.js”,function(){

frappe.call({
method: “erpnext.selling.doctype.graphtesting.graphtesting.statewise”,
args: {‘state’:cur_frm.doc.state,
‘lead’:cur_frm.doc.lead},
async: false,
callback: function {
if(r.message) {
alert(r.message.state),
alert(r.message.lead)
},
});

let chart = new frappe.Chart( “#chart”, {
data: {
labels:[state]

  datasets: [
    {
      name: "Total Lead", chartType: 'bar',
      values:[lead]
      
    },
    
  ],


},

title: "State-Wise Lead Count",
type: 'bar',
height: 350,
colors: ['blue'],

tooltipOptions: {
  formatTooltipX: d => (d + '').toUpperCase(),
 formatTooltipY: d => d + ' pts',

}

});

});

}

});

.py file

from future import unicode_literals
import frappe
from frappe.model.document import Document

@frappe.whitelist()

def statewise():
state=frappe.db.sql(“select state from tabLead where state is not null group by state”)
lead=frappe.db.sql(“select count(*) from tabLead where state is not null group by state”)
return state,lead

6

@nikzz as I said you have to store return values in variables first
define new variables before call function like : var state,lead=0;
then inside

if(r.message) {
state = r.message.state;
lead = r.message.lead;
}

then call these variables in frappe chart

7

Here’s the .py file

from future import unicode_literals
import frappe
from frappe.model.document import Document

@frappe.whitelist()

def statewise():
query=frappe.db.sql(“select state, count(*) as lead from tabLead where state is not null group by state”)

for row in query():
	statelist=list(row)
	state=statelist[0]
	lead=statelist[1]
	#print "state = {} lead = {}".format(state,lead)
	return state,lead

Here’s .js file

frappe.ui.form.on(‘graphtesting’, {
refresh: function(frm) {

$.getScript(“https://cdn.jsdelivr.net/npm/frappe-charts@1.1.0/dist/frappe-charts.min.iife.js”,function(){

var state=[];
var lead=[];

frappe.call({
method: “erpnext.selling.doctype.graphtesting.graphtesting.statewise”,
args: {“state”:cur_frm.doc.state,
“lead”:cur_frm.doc.lead},
async: false,
callback: function {
if(r.message) {

	state = r.message.state;
	lead = r.message.lead;
        console.log("state = "+state + " lead = "+ lead);

},
},
});

let chart = new frappe.Chart( “#chart”, {
data: {
labels:state

datasets: [
{
name: “Total Lead”, chartType: ‘bar’,
values:lead

},

],

},

title: “State-Wise Lead Count”,
type: ‘bar’,
height: 350,
colors: [‘blue’],

tooltipOptions: {
formatTooltipX: d => (d + ‘’).toUpperCase(),
formatTooltipY: d => d + ’ pts’,
}

});

});

}
});

No values getting printed on console, it’s blank. Guess data is not being fetched but same works when fetching values from db via python ide

Any suggestions what I am doing wrong

9

What i came to know is python is not fetching values from database. Any help regarding this ??

My .py code is above

10

@nikzz Make sure you SQL statement is correct and return some values

try to print with this code:

print "query = {}".format(frappe.as_json(query))

11

In your Js code you called python function and pass parameter “state” and “lead” but python function have no any argument so check it first

12

tried what you were saying but still no progress

image
image1366×768 151 KB

Also I checked the sql query is fetching data from sql db as well as when i use any python idle to getdata from that query. But nothing gets returned from custom script

Here’s .py file

from future import unicode_literals
import frappe
from frappe.model.document import Document
import json

@frappe.whitelist()

def state_wise(state):
query= frappe.db.sql(“”“select state, count(*) as lead from tabLead where state is not null group by state”“”)
for row in query():
statelist=list(row)
state=statelist[0]
lead=statelist[1]
print “query = {}”.format(frappe.as_json(query))
return state,lead

13

Can you please elaborate @Maheshwari_Bhavesh. I am putting that value in state and lead variable in .py file and trying to get those values in .js

Please elaborate so I can be able to do so

Thanks

14

Actually I also thinks that might be possible values are not getting fetched from database, may be syntax issue or something I am missing. Coz I was trying to dump data from a simple query to .json file . Doing so by python IDE does that but not when doing so in backend.

15 
from future import unicode_literals
import frappe
from frappe.model.document import Document
import json

@frappe.whitelist()
def state_wise(state):
query= frappe.db.sql("""select state, count(*) as lead from tabLead where state is not null group by state""")
for row in query:
      state=row[0]
      lead=row[1]
return (state,lead)
16

from future import unicode_literals
import frappe
from frappe.model.document import Document
import json

@frappe.whitelist()
def state_wise(state):
query= frappe.db.sql(“”“select state, count(lead_name) from tabLead where state is not null group by state”“”)
for row in query:
state=row[0]
lead=row[1]
return (state,lead)

didn’t worked :frowning:

.js here

frappe.ui.form.on(‘graphtesting’, {
onload: function(frm) {

$.getScript(“https://cdn.jsdelivr.net/npm/frappe-charts@1.1.0/dist/frappe-charts.min.iife.js”,function(){

var state = [];
var lead = [];

frappe.call({
query: “erpnext.selling.doctype.graphtesting.graphtesting.state_wise”,
args: {‘state’:frm.doc.state,
‘lead’:frm.doc.lead},
async: false,
callback: function(r) {
if(r.message) {

state = r.message.state;
lead = r.message.lead;

},
},
});

let chart = new frappe.Chart( “#chart”, {
data: {
labels:[state],

  datasets: [
    {
      name: "Total Lead", chartType: 'bar',
      values:[lead],
      
    },
    
  ],
  


},

title: "State-Wise Lead Count",
type: 'bar',
height: 300,
colors: ['blue'],

tooltipOptions: {
  formatTooltipX: d => (d + '').toUpperCase(),
 formatTooltipY: d => d + ' pts',

}

});

});

}

});

17

Please check api using portman first if you get any issue then post full trace here

18

yeah checked…that wan’t giving any response so tried this https://unpkg.com/frappe-charts@1.1.0/dist/frappe-charts.min.iife.js

but still same issue

Got It. Thanks for the help