jQWidgets Forums

jQuery UI Widgets › Forums › Grid › Master-Detail Grid: Resetting Datasource

Tagged: 

This topic contains 8 replies, has 4 voices, and was last updated by  dippy 10 years, 10 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Master-Detail Grid: Resetting Datasource Posts
  • June 9, 2012 at 6:19 pm Master-Detail Grid: Resetting Datasource #4733

    csoga
    Participant

    Hello…. I am testing JqWidgets for some future projects and have run into a problem with a concept I’m trying to implement. I want to implement a grid containing a list of customers with another grid (not nested) displaying the list of contacts associated with a selected customer in the Customer grid.

    The grids support paging and are populated from a JSON datasource in Virtualmode using PHP. I’ve implemented some code to handle the Customer grid RowSelect event… and attempts to reset the data source for the Contact grid using the customer id associated with the row selected in the Customer grid, then invokes the Contact grid’s updatebounddata method to repopulate the grid.

    The basic concept as implemented seems to work OK but the grids do not refresh properly… and paging seems to get all screwed up once I start clicking on various rows in the Customer grid.

    The following is the code for what I have implemented (with simplified PHP code to return pre-set data (instead of fetching it from a database – which is what I am actually doing). Note that I initially populate the Contact grid with a Customer Id of 1…. and selecting any other row in the Customer Grid should display an empty Contact Grid (which it’s not doing). Any help would be highly appreciated…

    Here is the code for testgrid.html:

    Hekimax MojaCast Messenger Admin

    $(document).ready(function () {

    var theme = ‘classic’;
    var buttontheme = ‘shinyblack’;

    function _addCustGridEventListener()
    {
    $(‘#custGrid’).bind(‘rowselect’, function (event)
    {
    var args = event.args;
    var row = args.rowindex;

    //get the Customer Id and Show the contacts
    var custId = $(‘#custGrid’).jqxGrid(‘getcellvalue’, row, “CustomerId”);

    // Reset the contact grid source
    var con_source =
    {
    datatype: “json”,
    datafields: [
    { name: ‘ContactId’ },
    { name: ‘Status’}
    ],
    async: false,
    url: ‘getconlist.php’,
    root: ‘Rows’,
    data: {customerid: custId},
    beforeprocessing: function(data)
    {
    con_source.totalrecords = data[0].TotalRows;
    }
    };
    var dataAdapter = new $.jqx.dataAdapter(con_source);
    $(“#contactGrid”).jqxGrid({source: dataAdapter});
    $(‘#contactGrid’).jqxGrid(‘updatebounddata’);

    });

    }; //end of _addCustGridEventListener()

    // Set up and show the Customer data grid
    var cust_source =
    {
    datatype: “json”,
    datafields: [
    { name: ‘CustomerId’ },
    { name: ‘Status’},
    { name: ‘DateCreated’}
    ],
    url: ‘getcustlist.php’,
    root: ‘Rows’,
    async: false,
    beforeprocessing: function(data)
    {
    cust_source.totalrecords = data[0].TotalRows;
    }
    };
    var dataAdapter = new $.jqx.dataAdapter(cust_source);
    $(“#custGrid”).bind(“bindingcomplete”, function (event) {
    _addCustGridEventListener();
    });
    $(“#custGrid”).jqxGrid(
    {
    width: 350,
    height: 185,
    source: dataAdapter,
    theme: theme,
    columnsresize: true,
    pageable: true,
    pagesize: 5,
    virtualmode: true,
    rendergridrows: function()
    {
    return dataAdapter.records;
    },
    columns: [
    { text: ‘Customer Id’, dataField: ‘CustomerId’, width: 100 },
    { text: ‘Status’, dataField: ‘Status’, width: 100},
    { text: ‘Date Created’, dataField: ‘DateCreated’, width: 145}
    ]
    });

    // Set up the Contact data grid
    var custId = 1;
    var con_source =
    {
    datatype: “json”,
    datafields: [
    { name: ‘ContactId’ },
    { name: ‘Status’}
    ],
    async: false,
    url: ‘getconlist.php’,
    root: ‘Rows’,
    data: {customerid: custId},
    beforeprocessing: function(data)
    {
    con_source.totalrecords = data[0].TotalRows;
    }
    };
    var dataAdapter = new $.jqx.dataAdapter(con_source);
    $(“#contactGrid”).jqxGrid(
    {
    width: 350,
    height: 185,
    source: dataAdapter,
    theme: theme,
    columnsresize: true,
    pageable: true,
    pagesize: 5,
    virtualmode: true,
    rendergridrows: function()
    {
    return dataAdapter.records;
    },
    columns: [
    { text: ‘Id’, dataField: ‘ContactId’, width: 100 },
    { text: ‘Status’, dataField: ‘Status’, width: 245}
    ]
    });

    }); // end of document.ready()

    Master-Detail Grid Test (Without Nested Grids)
    Customers:

    Contacts:

    Here is the code for getcustlist.php:

    ‘1’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’2’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’3’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’4’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’5’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    }
    else if ($pagenum==1)
    {
    // Return second page
    $custList[] = array (‘CustomerId’=>’6’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’7’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’8’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    $custList[] = array (‘CustomerId’=>’9’, ‘Status’=>’Active’, ‘DateCreated’=>’2012-05-28’);
    }

    $data[] = array(
    ‘TotalRows’ => 9,
    ‘Rows’ => $custList
    );

    header(“Content-type: application/json”);
    echo json_encode($data);

    ?>

    Here is the code for getconlist.php:

    ‘1’,’Status’=>’Active’);
    $conList[] = array (‘ContactId’=>’2′,’Status’=>’Active’);
    $conList[] = array (‘ContactId’=>’3′,’Status’=>’Active’);
    $conList[] = array (‘ContactId’=>’4′,’Status’=>’Active’);
    $conList[] = array (‘ContactId’=>’5′,’Status’=>’Active’);
    $TotRows = 7;
    }
    else if ($customerId==1 && $pagenum==1)
    {
    // Return first page
    $conList[] = array (‘ContactId’=>’6′,’Status’=>’Active’);
    $conList[] = array (‘ContactId’=>’7′,’Status’=>’Active’);
    $TotRows = 7;
    }
    else
    {
    $conList[] = array ();
    $TotRows = 0;
    }

    $data[] = array(
    ‘TotalRows’ => $TotRows,
    ‘Rows’ => $conList
    );

    header(“Content-type: application/json”);
    echo json_encode($data);

    ?>

    June 9, 2012 at 9:15 pm Master-Detail Grid: Resetting Datasource #4736

    Peter Stoev
    Keymaster

    Here’s a sample implementation of master-details.

    index.php

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <link rel="stylesheet" href="../jqwidgets/styles/jqx.base.css" type="text/css" />
        <link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css" />
        <script type="text/javascript" src="../scripts/jquery-1.7.2.min.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script>
        <script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script>
    	<script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script>
      	<script type="text/javascript" src="../jqwidgets/jqxgrid.pager.js"></script>
       <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var source =
                {
                    datatype: "json",
                    datafields: [
                        { name: 'CustomerID'},
                        { name: 'CompanyName'},
                        { name: 'ContactName'},
                        { name: 'ContactTitle'},
                        { name: 'Address'},
                        { name: 'City'},
                    ],
    				id: 'CustomerID',
                    url: 'data.php',
    				root: 'Rows',
                    beforeprocessing: function (data) {
                        source.totalrecords = data[0].TotalRows;
                    }     
                };
                var dataAdapter = new $.jqx.dataAdapter(source);
    			
    			$("#jqxgrid").jqxGrid(
                {
                    source: dataAdapter,
                    theme: 'classic',
    				pageable: true,
    				autoheight: true,
                    virtualmode: true,
                    rendergridrows: function () {
                        return dataAdapter.records;
                    },				
                    columns: [
                      { text: 'Company Name', datafield: 'CompanyName', width: 250},
                      { text: 'ContactName', datafield: 'ContactName', width: 150 },
                      { text: 'Contact Title', datafield: 'ContactTitle', width: 180 },
                      { text: 'Address', datafield: 'Address', width: 200 },
                      { text: 'City', datafield: 'City', width: 120 }
                  ]
                });        
    			
    			var detailsInitialized = false;
    			 $("#jqxgrid").bind('rowselect', function (event) {
    				var row = event.args.rowindex;
    				var id = $("#jqxgrid").jqxGrid('getrowdata', row)['CustomerID'];
    				var source =
    				{
    					url: 'data.php',
    					async: false,
    					dataType: 'json',
    					data: {customerid: id},
    					datatype: "json",
    					datafields: [
    						 { name: 'ShippedDate' },
    						 { name: 'ShipName' },
    						 { name: 'ShipAddress' },
    						 { name: 'ShipCity' },
    						 { name: 'ShipCountry' }
    					]    
     				};
    				var dataAdapter = new $.jqx.dataAdapter(source);
    				// initialize jqxGrid
    				$("#ordersGrid").jqxGrid(
    				{
    					source: dataAdapter,
    					pageable: true,
    					autoheight: true,
    					columns: [
    						  { text: 'Shipped Date', datafield: 'ShippedDate', cellsformat: 'd', width: 200 },
    						  { text: 'Ship Name', datafield: 'ShipName', width: 200 },
    						  { text: 'Address', datafield: 'ShipAddress', width: 180 },
    						  { text: 'City', datafield: 'ShipCity', width: 100 },
    						  { text: 'Country', datafield: 'ShipCountry', width: 140 }
    					  ]
    				});
    			  });
            });
        </script>
    </head>
    <body class='default'>
       <div id="jqxgrid"></div>
       <div style="margin-top: 50px;" id="ordersGrid"></div>
    </body>
    </html>

    data.php

    <?php
    #Include the connect.php file
    include('connect.php');
    #Connect to the database
    //connection String
    $connect = mysql_connect($hostname, $username, $password)
    or die('Could not connect: ' . mysql_error());
    //Select The database
    $bool = mysql_select_db($database, $connect);
    if ($bool === False){
    	print "can't find $database";
    }
    // get data and store in a json array
    $query = "SELECT * FROM customers";
    if (isset($_GET['customerid']))
    {
    	$query = "SELECT SQL_CALC_FOUND_ROWS * FROM orders WHERE CustomerID='" .$_GET['customerid'] . "'";
    	$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
    	// get data and store in a json array
    	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    		$orders[] = array(
    			'OrderDate' => $row['OrderDate'],
    			'ShippedDate' => $row['ShippedDate'],
    			'ShipName' => $row['ShipName'],
    			'ShipAddress' => $row['ShipAddress'],
    			'ShipCity' => $row['ShipCity'],
    			'ShipCountry' => $row['ShipCountry']
    		  );
    	}
    	echo json_encode($orders);    	
    }
    else
    {
    	$pagenum = $_GET['pagenum'];
    	$pagesize = $_GET['pagesize'];
    	$start = $pagenum * $pagesize;
    	$query = "SELECT SQL_CALC_FOUND_ROWS * FROM customers LIMIT $start, $pagesize";
    	$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
    	$sql = "SELECT FOUND_ROWS() AS `found_rows`;";
    	$rows = mysql_query($sql);
    	$rows = mysql_fetch_assoc($rows);
    	$total_rows = $rows['found_rows'];
    	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    		$customers[] = array(
    			'CustomerID' => $row['CustomerID'],
    			'CompanyName' => $row['CompanyName'],
    			'ContactName' => $row['ContactName'],
    			'ContactTitle' => $row['ContactTitle'],
    			'Address' => $row['Address'],
    			'City' => $row['City'],
    			'Country' => $row['Country']
    		  );
    	}
        $data[] = array(
           'TotalRows' => $total_rows,
    	   'Rows' => $customers
    	);
    	echo json_encode($data);
    }
    ?>

    and connect.php

    <?php
    # FileName="connect.php"
    $hostname = "localhost";
    $database = "northwind";
    $username = "root";
    $password = "";
    ?>

    There are 2 Grids created in the Index.php. When the user selects a row in the first Grid, the Details Grid is populated with specific data depending on the selected row. The sample uses the Northwind database.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com

    June 10, 2012 at 12:35 am Master-Detail Grid: Resetting Datasource #4749

    csoga
    Participant

    Thanks Peter, for the quick response.

    The only difference between the example you sent me and what I had was that I had virtual mode enabled on the detail grid. Once I turned virtual mode off, the quirky rendering issues went away.

    On the other hand, once I turned virtual mode on in the example you sent, it also started experiencing the same rendering issues I was previously experiencing.

    Unfortunately the type of application I’m looking to implement will require the detail grid to be implemented in virtual mode – since the volume of records will be quite large.

    Do you have any suggestions as to what I need to do to eliminate the virtual mode rendering issues?

    Thanks!

    June 10, 2012 at 2:09 am Master-Detail Grid: Resetting Datasource #4751

    Peter Stoev
    Keymaster

    Another solution is to destroy and then initialize the Details Grid when you rebind it, too.

    Example:

    var parent = $("#jqxgrid").parent();
    $("#jqxgrid").jqxGrid('destroy');
    $("<div id='jqxgrid'></div>").appendTo(parent);

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com

    June 10, 2012 at 12:51 pm Master-Detail Grid: Resetting Datasource #4770

    csoga
    Participant

    Excellent! Thanks Peter… this solved my problem.

    May 6, 2014 at 11:54 am Master-Detail Grid: Resetting Datasource #54040

    ksheer
    Participant

    Dear Peter,

    Can we have same example with oracle database. It will be of great help to my project.

    May 6, 2014 at 12:15 pm Master-Detail Grid: Resetting Datasource #54042

    Peter Stoev
    Keymaster

    Hi ksheer,

    I am sorry, but we are unable to provide an example with Oracle Database. I hope that other Forum user could help you.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com

    May 6, 2014 at 6:55 pm Master-Detail Grid: Resetting Datasource #54060

    dippy
    Participant

    Hi csoga,

    I am also trying for the same thing. No break thru yet. will share the same once done.

    Are you able to populate the 2nd grid.?

    I have done exactly the demo way (removed the paging) and changed the Oracle connection and functions.

    tc.

    May 24, 2014 at 4:07 pm Master-Detail Grid: Resetting Datasource #54856

    dippy
    Participant

    Hi Csoga,

    Please find the oracle data example php page. 

    <?php
#Include the connect.php file
include('xe.php');
#Connect to the database
//connection String

if ($xeconn == False){
	print "can't find $database";
}
// get data and store in a json array
$query = "select * from cps_ainvc_head_test";
if (isset($_GET['accinvoiceno']))
{
	$query = "SELECT Nvl(B.LINE_NO,0)LINE_NO,
		   A.CINVC_NO,
           A.AINVC_NO,
           B.ITEM,
           Initcap(B.ITEM_DESC)ITEM_DESC,
           Round(Nvl(B.EST_UNIT_TERR_LND_CST,0),3)EST_UNIT_TERR_LND_CST,
           Nvl(B.SUG_INIT_PRICE,0)SUG_INIT_PRICE,
           Nvl(B.BUY_FINAL_RSP,0)BUY_FINAL_RSP,
           B.AVG_VAT_RATE           
        FROM CPS_AINVC_HEAD_TEST A,
             CPS_AINVC_DETL_TEST B, 
             DOC_APPROVER_MERCH_HIERARCHY C,
             DOC_APPROVERS D,
             DOC_APPROVER_LVL E,
             DOC_APPROVAL_AUTHORITY F 
        WHERE A.CINVC_NO=B.CINVC_NO
        AND A.AINVC_NO=B.AINVC_NO
		AND A.AINVC_NO='".$_GET['accinvoiceno']."'         	
        AND A.FINAL_TO_WH=B.FINAL_TO_WH
        AND B.DEPT=C.APP_DEPT    
        AND B.DOC_CURR_APP_SEQ_NO=E.APP_FLOW_SEQ_NO
        AND B.RSP_CHANGE_IND IS NULL 
        AND C.APP_CODE=D.APP_CODE
        AND C.APP_CODE=1
        AND E.DOC_TYPE='CPS'
        AND E.DOC_TYPE=F.DOC_TYPE
        AND E.APP_TYPE=F.APP_TYPE
		ORDER BY B.LINE_NO";
		
	$result = oci_parse($xeconn,$query);
	oci_execute($result);
	
	// get data and store in a json array
	while ($row =oci_fetch_array($result, OCI_ASSOC))
	{
		$accInvDetails[] = array(
		'LineNo' => $row['LINE_NO'],		
        'CustomInvoiceNo' => $row['CINVC_NO'],		
        'AccInvoiceNo' => $row['AINVC_NO'],		
		'ItemCode' => $row['ITEM'],		
		'ItemDesc' => $row['ITEM_DESC'],		
		'EstTerrLndCst' => $row['EST_UNIT_TERR_LND_CST'],
		'SuggRSP' => $row['SUG_INIT_PRICE'],
		'FinalRSP' => $row['BUY_FINAL_RSP'],
		'VATPer' => $row['AVG_VAT_RATE'],
      );
	}
    $data[] = array(    
	   'Rows' => $accInvDetails
	);
	echo json_encode($data);    	
}
else
{	
	$query = "select 
				 AINVC_NO,
                 CINVC_NO,           
                 AINVC_DT,
                 FINAL_TO_WH,
                 VOLUME,
                 SUM(AI_VALUE)AI_VALUE,
                 AGEING                 
			  from                 
					(SELECT  A.AINVC_NO,
					A.CINVC_NO,           
					A.AINVC_DT,
					A.FINAL_TO_WH,
					ROUND(A.VOLUME,2)VOLUME,
					ROUND(SUM(B.AI_ITEM_RATE*B.AI_QTY),0)AI_VALUE,
					ROUND((SYSDATE-A.AINVC_DT),0)AGEING					                           
					FROM CPS_AINVC_HEAD_TEST A,
						CPS_AINVC_DETL_TEST B, 
						DOC_APPROVER_MERCH_HIERARCHY C,
						DOC_APPROVERS D,
						DOC_APPROVER_LVL E,
						DOC_APPROVAL_AUTHORITY F 
					WHERE A.CINVC_NO=B.CINVC_NO
					AND A.AINVC_NO=B.AINVC_NO					
					AND A.FINAL_TO_WH=B.FINAL_TO_WH
					AND B.DEPT=C.APP_DEPT    
					AND B.DOC_CURR_APP_SEQ_NO=E.APP_FLOW_SEQ_NO
					AND B.RSP_CHANGE_IND IS NULL 
					AND C.APP_CODE=D.APP_CODE
					AND C.APP_CODE=1
					AND A.APPROVED_STATUS='N'
					AND E.DOC_TYPE='CPS'
					AND E.DOC_TYPE=F.DOC_TYPE
					AND E.APP_TYPE=F.APP_TYPE
				GROUP BY
					A.AINVC_NO,
					A.CINVC_NO,           
					A.AINVC_DT,
					A.FINAL_TO_WH,
					A.VOLUME 
				ORDER BY
					ROUND((SYSDATE-A.AINVC_DT),0) DESC)                                       
				GROUP BY  
				AINVC_NO,
				CINVC_NO,           
				AINVC_DT,
				FINAL_TO_WH,
				VOLUME,
				AGEING";			
				
	$result = oci_parse($xeconn,$query);
	oci_execute($result);
	
	while ($row =oci_fetch_array($result, OCI_ASSOC)) 
	{
		$accInvHead[] = array(
	    'AccInvoiceNo' => $row['AINVC_NO'],	 	
	    'CustomInvoiceNo' => $row['CINVC_NO'],		      	
		'AccInvoiceDate' => $row['AINVC_DT'],		
		'TerrCode' => $row['FINAL_TO_WH'],		
		'Volume' => $row['VOLUME'],
		'AiValue' => $row['AI_VALUE'],
		'Ageing' => $row['AGEING'],		
        );
	}
    $data[] = array(    
	   'Rows' => $accInvHead
	);
	echo json_encode($data);
}
?>
  • Author
    Posts
Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.