What is the right approach?

[cpuinParticipant]

Hello,

I’m wandering what is the right approach to achieve the following :

I have sales displayed in dataTable.By default the PHP (using JSON) file return all sales for the current day.
I want to filter some periods using start and end date.I’m trying to do this modifying the url inserting some data (start and end day) and than rerender the table.It doesn’t work unfortunately.I’m also not sure wether this is the right way.
I don’t want the PHP to return as JSON all existing sales, because it will be very slow.I want to make this on the server side, but w/out reload the page.

$("#startDate").jqxDateTimeInput({width: '200px', height: '25px'});
$("#endDate").jqxDateTimeInput({width: '200px', height: '25px'});
$("#submitButton").jqxButton({width: '100px', height: '25px'});
	    $("#submitButton").mousedown(function () {
	    	                        var start = $("#startDate").val();
	    	                        var end = $("#endDate").val();
	    	                        url = 'json_sales.php?' + "startDate=" + start + "&endDate=" + end;
	    	                        
	    	                        $("#dataTable").jqxDataTable('updateBoundData');
									window.alert(url);
	    	                    });

[DimitarParticipant]

Hello cpuin,

You need some modifications to your code. Assuming the data table is bound to a data adapter called dataAdapter, here is what you need to do:

$("#submitButton").mousedown(function() {
    var start = $("#startDate").val();
    var end = $("#endDate").val();
    source.url = 'json_sales.php?' + "startDate=" + start + "&endDate=" + end;
    dataAdapter.dataBind();

    $("#dataTable").jqxDataTable('updateBoundData');
    window.alert(source.url);
});

Best Regards,
Dimitar

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

[cpuinParticipant]

Thank you for your answer.
I changed it with your code.Unfortunately nothing happens.No data loads in the table.The url is correct, because i tested it and it returns correct JSON objects.

[DimitarParticipant]

Hi cpuin,

Please provide us with a larger code sample including all relevant parts of your page so that we may determine what causes this issue.

Best Regards,
Dimitar

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

[cpuinParticipant]

var url = 'json_sales.php';
	    
	    
	    var source =
	    		{
	    			datatype: "json",
	    			datafields: [
	    				{ name: 'GoodID', type: 'string'},
	    				{ name: 'ObjectID', type: 'string'},
	    				{ name: 'Qtty' , type: 'double'},
	    				{ name: 'PriceOut' , type: 'double'},
	    				{ name: 'UserID' , type: 'string'},
	    				{ name: 'Total' , type: 'double'}
	    			],
	    			url: url,
	    			cache: false,
	    			addRow: function (rowID, rowData, position, commit) {
	    			                    // synchronize with the server - send insert command
	    			                    // call commit with parameter true if the synchronization with the server is successful 
	    			                    // and with parameter false if the synchronization failed.
	    			                    // you can pass additional argument to the commit callback which represents the new ID if it is generated from a DB.
	    			                    commit(true);
	    			                },
	    			updateRow: function (rowID, rowData, commit) {
	    			           var data = "update=true&" + $.param(rowData);
	    			           $.ajax({
	    			               dataType: 'json',
	    			               url: 'json_products.php',
	    			               cache: false,
	    			               data: data,
	    			               success: function (data, status, xhr) {
	    			                   // update command is executed.
	    			                   commit(true);
	    			               },
	    			               error: function (jqXHR, textStatus, errorThrown) {
	    			                   commit(false);
	    			                   alert('Does not work :(');
	    			                   }
	    			                  });
	    			                },
	    			deleteRow: function (rowID, commit) {
	    			                    // synchronize with the server - send delete command
	    			                    // call commit with parameter true if the synchronization with the server is successful 
	    			                    // and with parameter false if the synchronization failed.
	    			                    commit(true);
	    			                }
	    		};
	    		
	     var dataAdapter = new $.jqx.dataAdapter(source);
	     
	     $("#dataTable").jqxDataTable(
	     {
	         width: 850,
	         pageable: false,
	         pagerButtonsCount: 10,
	         source: dataAdapter,
	         filterable: true,
	         filtermode: 'simple',
	         sortable: true,
	         columnsResize: false,
	         altRows: true,
	         showAggregates: true,
	         aggregatesHeight: 60,
	         columns: [
	         	//{ text: 'Acct', dataField: 'Acct', width: 100 },
	             { text: 'Описание:', dataField: 'GoodID', width: 350 },
	             //{ text: 'PartnerID', dataField: 'PartnerID', width: 350 },
	             { text: 'Обект:', dataField: 'ObjectID', cellsFormat: 'f', width: 100 },
	             { text: 'Потребител:', dataField: 'UserID', width:100},
	             { text: 'К-во:', dataField: 'Qtty' , width: 100},
	             { text: 'Цена:', dataField: 'PriceOut', width:100},
	             { text: 'Общо:', dataField: 'Total', width:100, aggregates: [{
	                                         'Total':
	                                           function (aggregatedValue, currentValue, column, record) {
	                                               var total = currentValue * parseInt(record['Qtty']);
	                                               return aggregatedValue + total;
	                                           }
	                                     }],
	                                     aggregatesRenderer: function (aggregates, column, element) {    
	                                         var renderString = "<div style='margin: 4px; float: right;  height: 100%;'>";
	                                         renderString += "<strong>Всичко: </strong>" + aggregates.Total + "<br><strong>С ДДС:</strong>" + (aggregates.Total*1.2) + "</div>";
	                                         return renderString;
	                                     } }
	             ]
	             
	     });
	    
	    
	    
	    $("#startDate").jqxDateTimeInput({width: '200px', height: '25px', formatString: "yyyy-MM-dd"});
	    
	    
	    $("#endDate").jqxDateTimeInput({width: '200px', height: '25px', formatString: "yyyy-MM-dd"});
	    
	    $("#submitButton").jqxButton({width: '100px', height: '25px'});
	    $("#submitButton").mousedown(function() {
	        		var start = $('#startDate').val();
	        		var end = $('#endDate').val();
	        		source.url = 'json_sales.php?' + "startDate=" + start + "&endDate=" + end;
	        		dataAdapter.dataBind();
	        		$("#dataTable").jqxDataTable('updateBoundData');
	        		window.alert(source.url);
	    });

	    
	    
	    $("#excelExport").jqxButton();
	                $("#xmlExport").jqxButton();
	                $("#csvExport").jqxButton();
	                $("#tsvExport").jqxButton();
	                $("#htmlExport").jqxButton();
	                $("#jsonExport").jqxButton();
	                $("#excelExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'xls');
	                });
	                $("#xmlExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'xml');
	                });
	                $("#csvExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'csv');
	                });
	                $("#tsvExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'tsv');
	                });
	                $("#htmlExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'html');
	                });
	                $("#jsonExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'json');
	                });
	                
	     $("#printButton").jqxButton({ width: 80 });
	              
	                $("#printButton").click(function () {
	                     var gridContent = $("#dataTable").jqxDataTable('exportData', 'html');
	                     var newWindow = window.open('', '', 'width=800, height=500'),
	                     document = newWindow.document.open(),
	                     pageContent =
	                         '<!DOCTYPE html>' +
	                         '<html>' +
	                         '<head>' +
	                         '<meta charset="utf-8" />' +
	                         '<title>jQWidgets DataTable</title>' +
	                         '</head>' +
	                         '<body>' + gridContent + '</body></html>';
	                     document.write(pageContent);
	                     document.close();
	                     newWindow.print();
	                 });

[DimitarParticipant]

Hi cpuin,

Your code seems correct. Another thing you can try is to create a new data adapter with a new source on mousedown and bind the data table to it (reset its source property).

Best Regards,
Dimitar

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

[cpuinParticipant]

I have changed the code like this, unfortunately it doesn’t load nothing! In same time when manually trigger the url the PHP return right JSON:

$("#startDate").jqxDateTimeInput({width: '200px', height: '25px', formatString: "yyyy-MM-dd"});
	    
	    
	    $("#endDate").jqxDateTimeInput({width: '200px', height: '25px', formatString: "yyyy-MM-dd"});
	    
	    $("#submitButton").jqxButton({width: '100px', height: '25px'});
	    $("#submitButton").mousedown(function() {
	        		var start = $('#startDate').val();
	        		var end = $('#endDate').val();
	        		var url = 'json_sales.php?' + "startDate=" + start + "&endDate=" + end;
	        		
	        		var source =
	        				{
	        					datatype: "json",
	        					datafields: [
	        						{ name: 'GoodID', type: 'string'},
	        						{ name: 'ObjectID', type: 'string'},
	        						{ name: 'Qtty' , type: 'double'},
	        						{ name: 'PriceOut' , type: 'double'},
	        						{ name: 'UserID' , type: 'string'},
	        						{ name: 'Total' , type: 'double'}
	        					],
	        					url: url,
	        					cache: false,
	        					addRow: function (rowID, rowData, position, commit) {
	        					                    // synchronize with the server - send insert command
	        					                    // call commit with parameter true if the synchronization with the server is successful 
	        					                    // and with parameter false if the synchronization failed.
	        					                    // you can pass additional argument to the commit callback which represents the new ID if it is generated from a DB.
	        					                    commit(true);
	        					                },
	        					updateRow: function (rowID, rowData, commit) {
	        					           var data = "update=true&" + $.param(rowData);
	        					           $.ajax({
	        					               dataType: 'json',
	        					               url: 'json_products.php',
	        					               cache: false,
	        					               data: data,
	        					               success: function (data, status, xhr) {
	        					                   // update command is executed.
	        					                   commit(true);
	        					               },
	        					               error: function (jqXHR, textStatus, errorThrown) {
	        					                   commit(false);
	        					                   alert('Does not work :(');
	        					                   }
	        					                  });
	        					                },
	        					deleteRow: function (rowID, commit) {
	        					                    // synchronize with the server - send delete command
	        					                    // call commit with parameter true if the synchronization with the server is successful 
	        					                    // and with parameter false if the synchronization failed.
	        					                    commit(true);
	        					                }
	        				};
	        				
	        		 var dataAdapter = new $.jqx.dataAdapter(source);
	        		 
	        		$("#dataTable").jqxDataTable(
	        		{
	        		    width: 850,
	        		    pageable: false,
	        		    pagerButtonsCount: 10,
	        		    source: dataAdapter,
	        		    filterable: true,
	        		    filtermode: 'simple',
	        		    sortable: true,
	        		    columnsResize: false,
	        		    altRows: true,
	        		    showAggregates: true,
	        		    aggregatesHeight: 60,
	        		    columns: [
	        		    	//{ text: 'Acct', dataField: 'Acct', width: 100 },
	        		        { text: 'Описание:', dataField: 'GoodID', width: 350 },
	        		        //{ text: 'PartnerID', dataField: 'PartnerID', width: 350 },
	        		        { text: 'Обект:', dataField: 'ObjectID', cellsFormat: 'f', width: 100 },
	        		        { text: 'Потребител:', dataField: 'UserID', width:100},
	        		        { text: 'К-во:', dataField: 'Qtty' , width: 100},
	        		        { text: 'Цена:', dataField: 'PriceOut', width:100},
	        		        { text: 'Общо:', dataField: 'Total', width:100, aggregates: [{
	        		                                    'Total':
	        		                                      function (aggregatedValue, currentValue, column, record) {
	        		                                          var total = currentValue * parseInt(record['Qtty']);
	        		                                          return aggregatedValue + total;
	        		                                      }
	        		                                }],
	        		                                aggregatesRenderer: function (aggregates, column, element) {    
	        		                                    var renderString = "<div style='margin: 4px; float: right;  height: 100%;'>";
	        		                                    renderString += "<strong>Всичко: </strong>" + aggregates.Total + "<br><strong>С ДДС:</strong>" + (aggregates.Total*1.2) + "</div>";
	        		                                    return renderString;
	        		                                } }
	        		        ]
	        		        
	        		});
	        		//dataAdapter.dataBind();
	        		//$("#dataTable").jqxDataTable('updateBoundData');
	        		window.alert(url);
	    });

	    
	    
	    $("#excelExport").jqxButton();
	                $("#xmlExport").jqxButton();
	                $("#csvExport").jqxButton();
	                $("#tsvExport").jqxButton();
	                $("#htmlExport").jqxButton();
	                $("#jsonExport").jqxButton();
	                $("#excelExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'xls');
	                });
	                $("#xmlExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'xml');
	                });
	                $("#csvExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'csv');
	                });
	                $("#tsvExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'tsv');
	                });
	                $("#htmlExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'html');
	                });
	                $("#jsonExport").click(function () {
	                    $("#dataTable").jqxDataTable('exportData', 'json');
	                });
	                
	     $("#printButton").jqxButton({ width: 80 });
	              
	                $("#printButton").click(function () {
	                     var gridContent = $("#dataTable").jqxDataTable('exportData', 'html');
	                     var newWindow = window.open('', '', 'width=800, height=500'),
	                     document = newWindow.document.open(),
	                     pageContent =
	                         '<!DOCTYPE html>' +
	                         '<html>' +
	                         '<head>' +
	                         '<meta charset="utf-8" />' +
	                         '<title>jQWidgets DataTable</title>' +
	                         '</head>' +
	                         '<body>' + gridContent + '</body></html>';
	                     document.write(pageContent);
	                     document.close();
	                     newWindow.print();
	                 });
	    
	    });

[DimitarParticipant]

Hi cpuin,

Everything seems in order. What comes to mind is that the data you receive from the PHP file needs to be mapped to be loaded correctly by the data table. Would you be able to provide us with a sample of your JSON data so that we may determine if this is what causes the issue?

Best Regards,
Dimitar

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

[cpuinParticipant]

Now everything is OK.But i’m still looking for an answer of the question is this the right way to achieve this.I mean moderating the url and send it with GET?Because i also want the user to select whether some of the columns to be included or not, display only some users sales etc.

[DimitarParticipant]

Hi cpuin,

I am not sure which of the two suggestions worked for you – but if you not only update the data but also change the displayed (and loaded with data) columns, maybe the second one is more suitable, but you would also have to set the columns property accordingly.

Best Regards,
Dimitar

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

[cpuinParticipant]

Now it works!

[robfParticipant]

Hi – am I correct to understand that there is not a simple way to generate a tree view using arbitrary, valid JSON. I see other online tools that will generate trees with no upfront mappings. they just parse and visually represent the json as tree – ==

Maybe I am missing something?

[Peter StoevKeymaster]

Hi robf,

No, you are not correct. I’d suggest you to take a look at the TreeGrid’s demos. Ex: http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxtreegrid/javascript-tree-grid-binding-to-nested-json.htm?arctic – Tree loaded from arbitrary JSON.

Best Regards,
Peter Stoev

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

[robfParticipant]

Hi Peter,
Thank you for your response. In the example you posted I still see “structure” to the JSON – i.e. there is still a dataFields mapping

dataFields: [
                    { name: 'EmployeeID', type: 'number' },
                    { name: 'FirstName', type: 'string' },
                    { name: 'LastName', type: 'string' },
                    {...  { name: 'BirthDate', type: 'date' }
                ],

I am pulling arbitrary JSON – e.g. CONFIG files that hold various bits of meta data for an app. Keys may be “preferences”, “clients”, “options”, etc. The JSON is valid but again, arbitrary. I want to insert this into a simple tree view instead of what I use now which is a pretty-print json representation. == has good example of pasting any valid json and creating tree.

Thank you again for your time.
Robert

[Peter StoevKeymaster]

Hi robf,

It is pretty normal requirement to have some data description for loading correctly data from external data source. Our widgets can load any kind of data CSV, TSV, JSON, XML, Array and setting dataFields which define which parts of the data will be loaded and the data type is requirement. This is requirement for operations like sorting, filtering, etc and this is requirement for the columns definition. If some widget somewhere does it in a different way, Ok, but we do it in that way.

Best Regards,
Peter Stoev

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

[Author]

Posts