Hi, can anyone guide me on how to fetch the monthly actual sales of the previous fiscal year via Custom Script (js) for the table below?
Below is one of my many attempts:
frappe.ui.form.on('Year On Year Sales Target Table', 'validate', function(frm, cdt, cdn) {
set_pfy_monthly_actual_sales(frm);
});
var set_pfy_monthly_actual_sales = function(frm, cdt, cdn) {
var pfy_monthly_actual_sales = 0.0;
frappe.call({
method: 'erpnext.selling.report.sales_person_wise_transaction_summary.sales_person_wise_transaction_summary',
args: {
'filters': [
{'doc_type': 'Sales Order'},
{'sales_person': frm.doc.assigned_salesperson},
{'company': frm.doc.company},
{'from_date': frm.doc.previous_fy_start_date},
{'to_date': frm.doc.previous_fy_end_date}
],
contribution_amount: data.contribution_amount //or contribution_amount.total_row or total_row.contribution_amount or contribution_amount += contribution_amount
},
callback: function(frm, cdt, cdn) {
$.each(frm.doc.yoy_sales_target, function(i, row) {
row.previous_actual = data.contribution_amount;
});
frappe.model.set_value(cdt, cdn, 'row.previous_actual', row.previous_actual);
}
});
};
Namrata
December 10, 2019, 7:30am
#3
write query in custom script like this
select sum(grand_total) as total_sale ,date_format(posting_date,β%Mβ) from tabSales Invoice
where docstatus = 1 and date_format(posting_date ,β%Yβ) = β2019β group by date_format(posting_date,β%mβ);
Thank you and I am truly grateful for your help as I have struggled for many many days. To be honest, I am not a coder and I have just learnt basic javascript recently. As per your advice, I will try to write a query in the custom script. Should I stumble upon any difficulties, I shall post it here.
sanjay
December 10, 2019, 11:28am
#6
erpnext.selling.report.sales_person_wise_transaction_summary.sales_person_wise_transaction_summary is not function so you canβt call.
You can get report result as mentioned below:
var set_pfy_monthly_actual_sales = function(frm, cdt, cdn) {
var pfy_monthly_actual_sales = 0.0;
frappe.call({
method: 'frappe.desk.query_report.run',
args: {
'report_name': 'Sales Person-wise Transaction Summary',
'filters': {
'doc_type': 'Sales Order',
'sales_person': frm.doc.assigned_salesperson,
'company': frm.doc.company,
'from_date': frm.doc.previous_fy_start_date, // in YYYY-MM-DD format
'to_date': frm.doc.previous_fy_end_date // in YYYY-MM-DD format
},
},
callback: function(r) {
var data = r.message;
if (data && data.result && data.result.length) {
let keys = [];
let values = data.result;
$.each(data.columns, function(i, d) {
keys.push(d.fieldname);
});
let result = frappe.utils.dict(keys, values);
// get total row
let total_row = result.filter(function(obj) { return obj.sales_invoice == "Total"; });
// get result without total and empty row
result = result.filter(function(obj) { return obj.sales_invoice != "Total" && obj.sales_invoice != "";});
let total_contribution_amt = 0.0;
$.each(result, function(i, d) {
total_contribution_amt += flt(d.contribution_amt);
});
console.log(total_contribution_amt);
$.each(total_row, function(i, d) {
console.log(d.contribution_amt);
});
}
}
});
};
1 Like