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?
