Customization: Fetching Monthly Sales of Previous Fiscal Year

Frappe Framework Custom Script
1

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?

image
image954×255 4.38 KB

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);
        }
    });
};
2

Anyone?

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’);

4

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.

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