Title: Treegrid server side pagination
Post by: MFS on February 22, 2018, 03:35:44 AM
Hello. We try to use treegrid with server side pagination. In next lines you can see part of my code, treegrid code, datasource and sql. We are using Microsoft SQL Server for database server. SQL CODE IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Sif')) BEGIN EXEC ('CREATE SCHEMA [Sif] AUTHORIZATION [dbo]') END GO
CREATE TABLE Sif.SProfitniCentar ( [RedniBroj] bigint, [SProfitniCentar] smallint, [SProfitniCentarSef] smallint, [Oznaka] nvarchar(15), [Naziv] nvarchar(50) ) INSERT INTO Sif.SProfitniCentar VALUES ( 1, 1, NULL, NULL, N'Repro-materijal' ), ( 2, 2, NULL, NULL, N'Proizvodnja' ), ( 3, 3, NULL, NULL, N'Veleprodaja' ), ( 4, 4, NULL, NULL, N'Maloprodaja' ), ( 5, 6, NULL, NULL, N'Gotovi proizvodi' ), ( 6, 10, NULL, N'SOUR', N'SOUR' ), ( 7, 11, 10, N'OOUR1', N'OOUR1' ), ( 8, 12, 10, N'OOUR2', N'OOUR2' ), ( 9, 13, 10, N'SOUR-', N'drugi nivo' ), ( 10, 14, 13, N'SOUR--', N'treci nivo' ), ( 11, 15, 10, N'SOUR-3', N'SOUR-3' ), ( 12, 16, NULL, N'16', N'CENTRALNI MAGACIN' ), ( 13, 20, 1018, NULL, N'Centar 1' ), ( 14, 21, 1004, NULL, N'Centar 2' ), ( 15, 22, 1018, NULL, N'VK' ), ( 16, 23, 1018, NULL, N'BP' ), ( 17, 24, 1015, NULL, N'Grad' ), ( 18, 25, 1015, NULL, N'Tesla' ), ( 19, 26, 1015, NULL, N'Magla' ), ( 20, 27, 1003, NULL, N'Grad 2' ), ( 21, 28, 1002, NULL, N'SM' ), ( 22, 29, 1018, NULL, N'BK' ), ( 23, 30, 1005, NULL, N'UG' ), ( 24, 32, NULL, N'test', N'test' ), ( 25, 500, NULL, NULL, N'MI' ), ( 26, 502, 500, NULL, N'Zapad' ), ( 27, 1002, 502, NULL, N'1 - regija' ), ( 28, 1003, 502, NULL, N'2- regija' ), ( 29, 1004, 502, NULL, N'3- regija' ), ( 30, 1005, 502, NULL, N'4- reg' ), ( 31, 1015, 502, NULL, N'5- regija' ), ( 32, 1018, 502, NULL, N'6- regija' ) GO SELECT ROW_NUMBER() OVER (ORDER BY SPC.SProfitniCentar) AS RedniBroj, SPC.SProfitniCentar, SPC.SProfitniCentarSef, SPC.Oznaka, SPC.Naziv FROM Sif.SProfitniCentar AS SPC GO
CREATE PROCEDURE Sif.spSProfitniCentarList @Page INT, @PageSize INT, @Ukupno INT OUT, @SProfitniCentar INT, @Vrsta INT AS IF @Vrsta = 1 BEGIN -- LIST ALL FIELDS DECLARE @StartRow INT = (@Page - 1) * @PageSize + 1, @EndRow INT = @Page * @PageSize -- 1st try to count records but PHP PARAM OUT DOSNT WORK WELL /*SELECT @Ukupno = COUNT(TBL.SProfitniCentar) FROM Sif.SProfitniCentar AS TBL WHERE TBL.SProfitniCentar = @SProfitniCentar OR @SProfitniCentar IS NULL;*/ SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY TBL.SProfitniCentar) AS RedniBroj, TBL.SProfitniCentar, TBL.SProfitniCentarSef, TBL.SProfitniCentarSef AS _parentId, TBL.Naziv FROM Sif.SProfitniCentar AS TBL WHERE TBL.SProfitniCentar = @SProfitniCentar OR @SProfitniCentar IS NULL ) AS P WHERE P.RedniBroj BETWEEN @StartRow AND @EndRow ORDER BY P.SProfitniCentar; END ELSE IF @Vrsta = 2 BEGIN -- COUNT RECORDS IN TABLE SELECT COUNT(SPC.SProfitniCentar) AS Ukupno FROM Sif.SProfitniCentar AS SPC END GO
PHP DATA SOURCE CODE (You need to put your connection data for Microsoft sql server) <?php session_start(); //var_dump($_SESSION); $NazivServera = ''; $NazivBaze = ''; $KorisnickoImeZaBazu = ''; $LozinkaZaBazu = '';
$server = $NazivServera; $konekcionistring = array( "Database"=>$NazivBaze, "UID"=>$KorisnickoImeZaBazu, "PWD"=>$LozinkaZaBazu,"CharacterSet" => "UTF-8"); $konekcija = sqlsrv_connect( $server, $konekcionistring);
$CRUD = intval($_REQUEST['CRUD']);
if ($CRUD == 0) { $SProfitniCentar = null; $page = isset($_POST['page']) ? intval($_POST['page']) : 1; $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10; $result = array();
$parametri = array( array($page, SQLSRV_PARAM_IN), array($rows, SQLSRV_PARAM_IN), array(&$RecNo, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT), array($SProfitniCentar, SQLSRV_PARAM_IN) ); $procedura = "{call Sif.spSProfitniCentarList(?,?,?,?,1)}"; $polja = sqlsrv_query($konekcija,$procedura,$parametri); $procedura2 = "{call Sif.spSProfitniCentarList(?,?,?,?,2)}"; $polja2 = sqlsrv_query($konekcija,$procedura2,$parametri); while($row1 = sqlsrv_fetch_array($polja2)) { $BrojRedova = $row1['Ukupno']; } $result["total"] = $BrojRedova; /*-----------------------------------------*/ // $RecNo is 2 in $parametri but dosnt return right number of rows so I use simple count $BrojRedova // $result["total"] = $parametri[2]; /*-----------------------------------------*/ $items = array(); while($row = sqlsrv_fetch_object($polja)){ array_push($items, $row); } $result["rows"] = $items; echo json_encode($result); } ?>
TREEGRID CODE <div class="grid_10"> <div class="box round first"> <h2>Profitni centar</h2> <div class="block"> <div id="gridpozicija"> <!-- Grid forma --> <table id="grdSProfitniCentar" title="" class="easyui-treegrid" style="auto;height:510px" toolbar="#toolbarSProfitniCentar" pagination="true" rownumbers="true" fitColumns="true" singleSelect="true" data-options=" url:'dataSProfitniCentar.php', idField:'SProfitniCentar', treeField:'_parentId'"> <thead> <tr> <th field="SProfitniCentar" width="10%">Profitni Centar</th> <th field="_parentId" width="10%">Profitni Centar ŠEF</th> <th field="Naziv" width="30%">Naziv</th> </tr> </thead> </table> </div> </div> </div> </div>
On pictures in attachment you can see, that when I change number of page or rows it dosnt return correct number of rows in grid. When I test storage procedure in SQL with params that I send on treegrid (etc 2 page, 10 rows) I recive correct number of rows. First TRY with Page 1, Rows 10 DECLARE @Ukupno INT; EXEC Sif.spSProfitniCentarList @Page = 1, -- int @PageSize = 10, -- int @Ukupno = @Ukupno OUTPUT, -- int @SProfitniCentar = null, -- int @Vrsta = 1 -- int
RESULT ARE: RedniBroj SProfitniCentar SProfitniCentarSef _parentId 1 1 NULL NULL 2 2 NULL NULL 3 3 NULL NULL 4 4 NULL NULL 5 6 NULL NULL 6 10 NULL NULL 7 11 10 10 8 12 10 10 9 13 10 10 10 14 13 13
NEXT TRY with Page 2, Rows 10 RedniBroj SProfitniCentar SProfitniCentarSef _parentId 11 15 10 10 12 16 NULL NULL 13 20 1018 1018 14 21 1004 1004 15 22 1018 1018 16 23 1018 1018 17 24 1015 1015 18 25 1015 1015 19 26 1015 1015 20 27 1003 1003
You can see that sql storage procedure returs all tada that I need but in treegrid dosnt show all data... Why? Any suggestion?
Title: Re: Treegrid server side pagination
Post by: stworthy on February 22, 2018, 07:31:13 AM
Here is the second page records:
11 15 10 10 12 16 NULL NULL 13 20 1018 1018 14 21 1004 1004 15 22 1018 1018 16 23 1018 1018 17 24 1015 1015 18 25 1015 1015 19 26 1015 1015 20 27 1003 1003
This data lose some parent nodes. Only the row(12) is the root node, so it is displayed as the root node. The row(11) has the parent row(10) but this row(10) loses its parent information. So it can’t be display properly. If you add this row to the recordset:
6 10 NULL NULL
The row(6) will be displayed as the root node, so the rows(6,11,12) will be displayed correctly.
Here is the alternative solution for you:
1. If you have many root nodes, display the root nodes page by page.
2. Load each node’s children dynamically when expand a node.
Please look at this tutorial https://www.jeasyui.com/tutorial/tree/treegrid4.php
|