jQWidgets Forums

jQuery UI Widgets › Forums › Grid › Editable Grid with auto fill options (apportion,percentage fills)

Tagged: , ,

This topic contains 1 reply, has 2 voices, and was last updated by  Peter Stoev 11 years ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Editable Grid with auto fill options (apportion,percentage fills) Posts
  • June 4, 2014 at 7:40 am Editable Grid with auto fill options (apportion,percentage fills) #55298

    Rufus Jackson
    Participant

    Can you please tell is this Possible by using JQ Widgets Grid..?

    1.column wise total will be displayed in the last row
    2.row wise total will be displayed in the last column
    3.when we enter a value the cell the totals need to display on both last row and last column
    4.when we enter a value in the last row or last column it will divide the values to all the cells

    I have did something like that but i have many issues over that, can you please guide me in a right way that i can achieve the above things

    in the code I have constructed loops for validating user input and running appropriate function,

    var columns = [
{
    "text": "MRL Description",
    "datafield": "mrl_desc",
    "editable": false,
    "pinned": true,
    "width": "230"
	
},
{
    "text": "Product Description",
    "datafield": "product_desc",
    "editable": false,
    "pinned": true,
    "width": "230"
},
{
    "text": "Jan-2013",
    "datafield": "exp2013_2",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Feb-2013",
    "datafield": "exp2013_3",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "March-2013",
    "datafield": "exp2013_4",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "April-2013",
    "datafield": "exp2013_5",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "May-2013",
    "datafield": "exp2013_6",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "June-2013",
    "datafield": "exp2013_7",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "July-2013",
    "datafield": "exp2013_8",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Aug-2013",
    "datafield": "exp2013_9",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Sep-2013",
    "datafield": "exp2013_10",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Oct-2013",
    "datafield": "exp2013_11",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Nov-2013",
    "datafield": "exp2013_12",
    "editable": true,
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "Dec-2013",
    "datafield": "exp2013_13",
    "editable": true,
     "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
        return celledit(row, datafield, columntype, oldvalue, newvalue);

    }
},
{
    "text": "YTD",
	"editable": true,
    "datafield": "ytd",
    "width": "100",
    cellvaluechanging: function (row, datafield, columntype, oldvalue, newvalue)
    {
		
        return celledit(row, datafield, columntype, oldvalue, newvalue);
	}
}];

var rows = [
{
    "mrl_desc": "M111010- Cntrl Cash Vault LCY",
    "product_desc": "P9999999-Other Product",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},
{
"id":"mrl_2",
    "mrl_desc": "",
    "product_desc": "P979950-Recurring Deposit Ge",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},
{	
	"id":"mrl_1",
    "mrl_desc": "",
    "product_desc": "P9999185-Tier li Capital",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},
{	
	"id":"tot_cash1",
    "mrl_desc": "M111010- Total cash",
    "product_desc": "P9999999-Other Product",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},
{
"id":"tot_cash2",
    "mrl_desc": "",
    "product_desc": "P979950-Recurring Deposit Ge",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},
{	
	"id":"tot_cash3",
    "mrl_desc": "",
    "product_desc": "P9999185-Tier li Capital",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
},

{
	"id":"mrl_tot",
    "mrl_desc": "MRL TOTAL",
    "product_desc": "",
    "exp2013_2": "",
    "exp2013_3": "",
    "exp2013_4": "",
    "exp2013_5": "",
    "exp2013_6": "",
    "exp2013_7": "",
    "exp2013_8": "",
    "exp2013_9": "",
    "exp2013_10": "",
    "exp2013_11": "",
    "exp2013_12": "",
    "exp2013_13": "",
    "ytd": ""
}
];

//converting json data to a object though we can have columns and rows seperately
/*var json =JSON.stringify(json)
 var obj = $.parseJSON(json);
            var columns = obj[0].columns;
            var rows = obj[1].rows;*/

//mapping the datafields
var source = {
    datatype: "json",
    datafields: [
    {
        name: 'mrl_desc'
    },
    {
        name: 'product_desc',
    },
    {
        name: 'exp2013_2',
    },
    {
        name: 'exp2013_3',
    },
    {
        name: 'exp2013_4',
    },
    {
        name: 'exp2013_5',
    },
    {
        name: 'exp2013_6',
    },
    {
        name: 'exp2013_7',
    },
    {
        name: 'exp2013_8',
    },
    {
        name: 'exp2013_9',
    },
    {
        name: 'exp2013_10',
    },
    {
        name: 'exp2013_11',
    },
    {
        name: 'exp2013_12',
    },
    {
        name: 'exp2013_13',
    },
    {
        name: 'ytd',
    }],
	id:'id',
    localdata: rows
};
//console.log(columns);

var dataAdapter = new $.jqx.dataAdapter(source);
$("#exp_table").jqxGrid(
{
    theme: 'energyblue',
    altrows: true,
    editable: true,
    width: "99.8%",
    height: "100%",
    source: dataAdapter,
    columns: columns,
    selectionmode: 'singlecell',
});

function celledit(row, column, datafield, oldvalue, value)
{

    var rows = $('#exp_table').jqxGrid('getrows'); //getting row details
    var row_count = rows.length; //row count
    var column_count = $("#exp_table").jqxGrid('columns').records.length;
    var rowIDs = new Array();
    var total = 0;
	
	
	if (column == "ytd" && row == row_count-1)
    {
	
		if(value != "")
		{
	//check total
	for (k = 0; k < row_count - 1; k++)
        {
            for (i = 2; i < column_count - 1; i++)
            {
                var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, 'exp2013_' + i);
                if (tot_val != "")
                {
                    total += parseFloat(tot_val);
                }

            }
         }	
		
		if (total == "")
        {
			//for mrl
            var for_empty_mrl = parseFloat(value) / (column_count - 3);
            for (i = 2; i < column_count - 1; i++)
            {
                $("#exp_table").jqxGrid('setcellvalue', row, "exp2013_" + i, parseFloat(for_empty_mrl).toFixed(2));
            }
			//for ytd
			var for_empty_ytd = parseFloat(value) / (row_count - 1);
            for (k = 0; k < row_count - 1; k++)
            {
                $("#exp_table").jqxGrid('setcellvalue', k, column, parseFloat(for_empty_ytd).toFixed(2));
            }
			//for inner cell values
			for_empty_cell = for_empty_ytd/(column_count-3);
			for (i = 2; i < column_count - 1; i++)
			{
            
				for (k = 0; k < row_count - 1; k++)
				{ 
                 $("#exp_table").jqxGrid('setcellvalue', k, 'exp2013_' + i,for_empty_cell.toFixed(2));
				}
			}
        }
		else
		{
		alert(total);
		}
		
		}
	
	}
	
	else if (column == 'ytd' && row != row_count-1)
    {
		if(value != "")
		{

        for (i = 2; i < column_count - 1; i++)
        {
            var tot_val = $("#exp_table").jqxGrid('getcellvalue', row, 'exp2013_' + i);
            if (tot_val != "")
            {
                total += parseFloat(tot_val);
            }
        }
        if (total == "")
        {
            var for_empty = parseFloat(value) / (column_count - 3);
            for (i = 2; i < column_count - 1; i++)
            {
                $("#exp_table").jqxGrid('setcellvalue', row, "exp2013_" + i, parseFloat(for_empty).toFixed(2));
            }

        }
        else
        {
            var multiplier = parseFloat(value) / parseFloat(total);
            for (i = 2; i < column_count - 1; i++)
            {
                var tot_val = $("#exp_table").jqxGrid('getcellvalue', row, 'exp2013_' + i);
                if (tot_val != "")
                {
                    var new_val = parseFloat(multiplier) * parseFloat(tot_val);
                    $("#exp_table").jqxGrid('setcellvalue', row, "exp2013_" + i, parseFloat(new_val).toFixed(2));
                }
            }
        }

        //MRL TOTAL 
        var tot_mrl_val = 0;
        for (i = 2; i < column_count - 1; i++)
        {
            tot_mrl_val = 0;
            for (k = 0; k < row_count - 1; k++)
            { //
                var mrl_cell_val = $("#exp_table").jqxGrid('getcellvalue', k, 'exp2013_' + i);
                if (mrl_cell_val != "")
                {
                    tot_mrl_val += parseFloat(mrl_cell_val);
                }
                else
                {
                    tot_mrl_val = tot_mrl_val;
                }

            }

            if (tot_mrl_val != "")
            {
                $("#exp_table").jqxGrid('setcellvalue', row_count - 1, 'exp2013_' + i, parseFloat(tot_mrl_val).toFixed(2));
            }
        }
		
		//YTD and MRL Total
		total=0;
		for (k = 0; k < row_count - 1; k++)
        {
            var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, "ytd");
            if (tot_val != "")
            {
                total += parseFloat(tot_val);
            }

        }
        if (total != "")
        {
            $("#exp_table").jqxGrid('setcellvalue', row_count-1, "ytd", parseFloat(total).toFixed(2));
        }

		}
	}

    else if (row == row_count - 1 && column != "ytd")

    {
        if(value != "")
		{
        for (k = 0; k < row_count - 1; k++)
        {
            var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, column);
            if (tot_val != "")
            {
                total += parseFloat(tot_val);
            }
        }
        if (total == "")
        {
            var for_empty = parseFloat(value) / (row_count - 1);
            for (k = 0; k < row_count - 1; k++)
            {
                $("#exp_table").jqxGrid('setcellvalue', k, column, parseFloat(for_empty).toFixed(2));
            }

        }
        else
        {
            var multiplier = parseFloat(value) / parseFloat(total);
            for (k = 0; k < row_count - 1; k++)
            {
                var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, column);
                if (tot_val != "")
                {
                    var new_val = parseFloat(multiplier) * parseFloat(tot_val);
                    $("#exp_table").jqxGrid('setcellvalue', k, column, parseFloat(new_val).toFixed(2));
                }
            }
        }

        //YTD TOTAL
        for (k = 0; k < row_count - 1; k++)
        {
            total = 0;
            for (i = 2; i < column_count - 1; i++)
            {
                var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, 'exp2013_' + i);
                if (tot_val != "")
                {
                    total += parseFloat(tot_val);
                }

            }
            if (total != "")
            {
                $("#exp_table").jqxGrid('setcellvalue', k, "ytd", parseFloat(total).toFixed(2));
            }
        }
		
		}
    
	}

    else
    {

        //for selecting other columns
        //for searching the same product on the grid

        var current_product_desc = $('#exp_table').jqxGrid('getcell', row, "product_desc"); //get the current rows product desc
        var current_product_desc = current_product_desc.value; //product desc of current row
        var current_mrl_desc = $('#exp_table').jqxGrid('getcell', row, "mrl_desc"); //get the current rows product desc
        var current_mrl_desc = current_mrl_desc.value; //mrl desc of current row
        for (j = 0; j < row_count; j++)
        { //looping all other rows 

            var cell = $('#exp_table').jqxGrid('getcell', j, "product_desc"); //geting cell iformation
            var product_desc = cell.value;
            var cell = $('#exp_table').jqxGrid('getcell', j, "mrl_desc"); //geting cell iformation
            if (cell.value == '')
            {

            }
            else
            {

                var mrl_desc = cell.value;
            }
            if (product_desc == current_product_desc)
            {
                rowIDs.push(cell.row);

            }
        }

        var column_index = $('#exp_table').jqxGrid('getcolumnindex', column); //gets the column index value

        if (/^\-?(\d+(\.\d*)?)\%?(\<|\>)?$/.test(value) == true || /^(\<|\>)?\-?(\d+(\.\d*)?)\%?$/.test(value) == true)
        {

            if (/^\-?(\d+(\.\d*)?)(\<)$/.test(value) == true | /^(\<)\-?(\d+(\.\d*)?)$/.test(value) == true) //loop for less than symbol
            {
                var val = value.replace("<", '');
                for (i = column_index; i > 1; i--)
                {
                    //
                    $("#exp_table").jqxGrid('setcellvalue', row, 'exp2013_' + i, parseFloat(val).toFixed(2));

                }
            }

            else if (/^\-?(\d+(\.\d*)?)\%(\<)$/.test(value) == true || /^(\<)\-?(\d+(\.\d*)?)\%$/.test(value) == true) //loop for % less than symbol
            {
                var val_1 = value.replace("%<", '');

                if (oldvalue != "")
                {
                    var val = (parseFloat(oldvalue) + (parseFloat(oldvalue) * parseFloat(val_1)) / 100);
                }
                else
                {
                    var val = "";
                }
                for (i = column_index; i > 1; i--)
                {

                    var oldval = $("#exp_table").jqxGrid('getcellvalue', row, 'exp2013_' + i);
                    if (oldval != "")
                    {
                        var newval = (parseFloat(oldval) + (parseFloat(oldval) * parseFloat(val_1)) / 100);

                        $("#exp_table").jqxGrid('setcellvalue', row, 'exp2013_' + i, parseFloat(newval).toFixed(2));
                    }

                }

            }

            else if (/^\-?(\d+(\.\d*)?)(\>)$/.test(value) == true || /^(\>)\-?(\d+(\.\d*)?)$/.test(value) == true) //loop for greater than symbol
            {
                var val = value.replace(">", '');
                for (i = column_index; i < column_count - 1; i++)
                {

                    $("#exp_table").jqxGrid('setcellvalue', row, 'exp2013_' + i, parseFloat(val).toFixed(2));

                }

            }
            else if (/^\-?(\d+(\.\d*)?)\%(\>)$/.test(value) == true || /^(\>)\-?(\d+(\.\d*)?)\%$/.test(value) == true) //loop for % greater than
            {

                var val_1 = value.replace("%>", '');
                if (oldvalue != "")
                {
                    var val = (parseFloat(oldvalue) + (parseFloat(oldvalue) * parseFloat(val_1)) / 100);
                }
                else
                {
                    var val = "";
                }
                for (i = column_index; i < column_count - 1; i++)
                {

                    var oldval = $("#exp_table").jqxGrid('getcellvalue', row, 'exp2013_' + i);

                    if (oldval != "")
                    {
                        var newval = (parseFloat(oldval) + (parseFloat(oldval) * parseFloat(val_1)) / 100);
                        $("#exp_table").jqxGrid('setcellvalue', row, 'exp2013_' + i, parseFloat(newval).toFixed(2));
                    }

                }
            }
            else
            {
                val = value;

                $("#exp_table").jqxGrid('setcellvalue', row, column, parseFloat(value).toFixed(2));

            }

        }
        else
        {
            val = "";
        }
		
		
		//$("#exp_table").jqxGrid('setcellvalue', row, column,val);
        //YTD TOTAL
		var ytd_total =0;
        for (i = 2; i < column_count - 1; i++)
        {
            var tot_val = $("#exp_table").jqxGrid('getcellvalue', row, 'exp2013_' + i);
            if (tot_val != "")
            {
                ytd_total += parseFloat(tot_val);
            }

        }
        if (ytd_total != "")
        {
            $("#exp_table").jqxGrid('setcellvalue', row, "ytd", parseFloat(ytd_total).toFixed(2));
        }

        //MRL TOTAL 
        
        for (i = 2; i < column_count - 1; i++)
        {
            var mrl_total = 0;
            for (k = 0; k < row_count - 1; k++)
            { //
                var mrl_cell_val = $("#exp_table").jqxGrid('getcellvalue', k, 'exp2013_' + i);
                if (mrl_cell_val != "")
                {
                    mrl_total += parseFloat(mrl_cell_val);
                }
                else
                {
                    mrl_total = mrl_total;
                }

            }

            if (mrl_total != "")
            {
                $("#exp_table").jqxGrid('setcellvalue', row_count - 1, 'exp2013_' + i, parseFloat(mrl_total).toFixed(2));
            }
        }
		
		//YTD and MRL Total
		var ytd_mrl_total =0;
		for (k = 0; k < row_count - 1; k++)
        {
            var tot_val = $("#exp_table").jqxGrid('getcellvalue', k, "ytd");
            if (tot_val != "")
            {
                ytd_mrl_total += parseFloat(tot_val);
            }

        }
        if (ytd_mrl_total != "")
        {
            $("#exp_table").jqxGrid('setcellvalue', row_count-1, "ytd", parseFloat(ytd_mrl_total).toFixed(2));
        }
      //console.log(row_data);
        rowIDs = [];
alert($("#exp_table").jqxGrid('getrowid', row));
        if (val != "")
        {
            return parseFloat(val).toFixed(2);
        }
        return val;
	

    }

}

    Sorry for the code length …if there is a way please let me know..

    June 4, 2014 at 7:59 am Editable Grid with auto fill options (apportion,percentage fills) #55305

    Peter Stoev
    Keymaster

    Hi Rufus,

    Computed Columns and classic Aggregates are supported – http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm#demos/jqxgrid/aggregates.htm and http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/computedcolumn.htm?arctic.

    For validating user’s input, you’d better use the column’s validation callback function which ensures that the user’s input is valid.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/

  • Author
    Posts
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.