MFS
Newbie
Posts: 47
|
|
« 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?
|
|
|
Logged
|
|
|
|