Try ERPNext Buy Support Partners Foundation

Customization: Fetching Monthly Sales of Previous Fiscal Year

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

Anyone?

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.

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