<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" -->
<html>
<head>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8" />
<title>Sigma Grid 2.0 JSON Array Paging Sample</title>
<meta name="keywords" content="dhtml grid, AJAX grid, relationship,grid customization, cell text editor, cell dropdown list, cell calendar " >
<link rel="stylesheet" type="text/css" href="../../grid/gt_grid.css" />
<link rel="stylesheet" type="text/css" href="../../grid/skin/vista/skinstyle.css" />
<script type="text/javascript" src="../../grid/gt_msg_en.js"></script>
<script type="text/javascript" src="../../grid/gt_grid_all.js"></script>
<script type="text/javascript" src="../../grid/flashchart/fusioncharts/FusionCharts.js"></script>
<link rel="stylesheet" type="text/css" media="all" href="../../grid/calendar/calendar-blue.css" />
<script type="text/javascript" src="../../grid/calendar/calendar.js"></script>
<script type="text/javascript" src="../../grid/calendar/calendar-en.js"></script>
<script type="text/javascript" src="../../grid/calendar/calendar-setup.js"></script>
<script type="text/javascript" >
var grid_demo_id = "myGrid1" ;
function my_renderer(value ,record,columnObj,grid,colNo,rowNo){
var no= record[2];
var url= record[9];
return '<a target=blank href="
http://www.' + value + '.com">' + value + '</a>';
};
function my_renderer1(value ,record,columnObj,grid,colNo,rowNo){
var no= record[columnObj.fieldIndex];
var color = no<50?"red":(no>70?"green":"blue");
return "<span style=\"color:" + color +";\"><strong>" + no + "</strong></span>";
}
var dsOption= {
fields :[
{name : 'order_no' },
{name : 'employee' },
{name : 'country' },
{name : 'customer' },
{name : 'order2005' ,type: 'float' },
{name : 'order2006' ,type: 'float' },
{name : 'order2007' ,type: 'float' },
{name : 'order2008' ,type: 'float' },
{name : 'delivery_date' ,type:'date' }
],
recordType : 'array'
}
var myDialogEditorCreater = new Sigma.DialogEditor({
id: "myDialogEditor1",
gridId : "myGrid1" ,
width: 250,
height:150 ,
title : 'Name Editor',
body : ['<textarea id="my_name_input" rows="5" cols="20" style="width:99%"></textarea><br/>',
'<input type="button" value="OK" onclick="Sigma.$grid(\'myGrid1\').activeDialog.confirm()"/>'].join(''),
getValue : function(){
return Sigma.$("my_name_input").value;
},
setValue : function(value){
Sigma.$("my_name_input").value=value;
},
active : function(){
Sigma.U.focus(Sigma.$("my_name_input"));
}
// Developer could do validation, formating work by overwritting event handler beforeShow, afterShow, beforeHide, afterHide of dialog
});
var colsOption = [
{id: 'order_no' , header: "Order No" , width :60 , renderer:my_renderer ,editor:{type:"text"}},
{id: 'employee' , header: "Employee" , width :80 , editor:{type:"text"} },
{id: 'country' , header: "Country" , width :70 ,editor: myDialogEditorCreater },
{id: 'customer' , header: "Customer" , width :80 ,editor:{type:"text"} },
{id: 'order2005' , header: "2005" , width :60 ,renderer:my_renderer1,editor:{type:"text"} },
{id: 'order2006' , header: "2006" , width :60,editor:{type:"text"} },
{id: 'order2007' , header: "2007" , width :60,editor:{type:"text"} },
{id: 'order2008' , header: "2008" , width :60,editor:{type:"text"} },
{id: 'delivery_date' , header: "Delivery Date" , width :100,editor: {type :"date"}}
];
var gridOption={
id : grid_demo_id,
loadURL : 'Controller.php',
saveURL : 'Controller.php',
exportURL : 'Controller.php?export=true',
exportFileName : 'export_xls',
width: "900", //"100%", // 900,
height: "400", //"100%", // 550,
container : 'gridbox',
replaceContainer : true,
encoding : 'UTF-8', // Sigma.$encoding(),
dataset : dsOption ,
columns : colsOption ,
clickStartEdit : true ,
pageSize:50,
pageSizeList : [25,50,100,200,400],
showGridMenu : true,
toolbarContent : ' nav goto | pagesize | reload | add del save | print xls filter | state'
};
var mygrid=new Sigma.Grid( gridOption );
Sigma.Util.onLoad(function(){mygrid.render()});
//////////////////////////////////////////////////////////
</script>
</head>
<body>
<div id="page-container">
<div id="header">
<h1>
Product - Sigma Grid</h1>
</div>
<div id="content">
<h2>Sigma Grid Sample - Loading From JSON Array - Paginal Output</h2>
<div id="bigbox" style="margin:15px;display:!none;">
<div id="gridbox" style="border:0px solid #cccccc;background-color:#f3f3f3;padding:5px;height:200px;width:700px;" ></div>
</div>
</div>
<h3>About Sigmasoft</h3>
<p>
<ul>
<li>Input first letters of employee name(case sensitive):<input type="text" id="f_value1" value="" onKeyUp="doFilter()"></li>
<li>Grid will present orders of those employees whose name starts with your input just
now.</li>
<li>Note that employee names are case sensitive.</li>
</ul>
Sigmasoft Technologies LLC is a software company providing cross-browser javascript GUI components and tools & services involved. Our aim is to make AJAX simple and easy.
<br>Sigmasoft also provides end-to-end solutions in web development (Web 2.0, PHP, ASP.NET, ASP, JSP, XML, Flash), application development and IT consulting services. Please send email to
sales@sigmawidgets.com for further infomation.
</p>
<script type="text/javascript" >
function doFilter() {
var filterInfo=[
{
fieldName : "employee",
logic : "startWith",
value : Sigma.Util.getValue("f_value1")
}
]
var grid=Sigma.$grid("myGrid1");
var rowNOs=grid.filterGrid(filterInfo);
}
</script>
<div id="footer">All contents are (c) Copyright 2005 - 2008, Sigma Software Inc. All rights Reserved</div>
</div>
<script type="text/javascript">
jssc.colorAll("code");
</script>
</body>
</html>
Controller.php
<?php
header('Content-type:text/javascript;charset=UTF-8');
//require_once 'ocisess.php';-------------------------------------如果用ORACLE 数据库放session的话去掉//
session_start();
$userid= htmlentities(trim($_SESSION['userid']));
$json=json_decode(stripslashes($_POST["_gt_json"]));
//$pageSize = 10;//10 rows per page
$c = oci_connect('scott', 'tiger', 'webdb');
if (!$c) {
$e = oci_error(); // No parameter passed
var_dump($e);
}
//load action-------------------------------------------------
if($json->{'action'} == 'load'){
$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize= $json->{'pageInfo'}->{'pageSize'};
$s = oci_parse($c, 'select count(*) from orders where customer = :userid');
oci_bind_by_name($s, ":userid", $userid);
oci_execute($s);
if (OCIFetch($s)) {
$totalRec = OCIResult($s,1);
} else {
$totalRec = 0; // If no record was found
}
OCIFreeStatement($s);
if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
$pageNo = 1;
};
$minrow = ($pageNo - 1)*$pageSize+1; // row number of first row to return
$maxrow = $pageNo*$pageSize; // row number of last row to return
$mystmt = "select * from orders where customer = :userid order by order_no ";
$pagesql = "select *
from ( select a.*, rownum as rnum
from ( $mystmt ) a
where rownum <= :maxrow)
where rnum >= :minrow";
$s = oci_parse($c, $pagesql);
oci_bind_by_name($s, ":userid", $userid);
oci_bind_by_name($s, ":maxrow", $maxrow);
oci_bind_by_name($s, ":minrow", $minrow);
oci_execute($s);
$retArray = array();
while (($row = oci_fetch_row($s)) != false){
$retArray[] = $row;
}
$data = json_encode($retArray);
$ret = "{data:" . $data .",\n";
$ret .= "pageInfo:{totalRowNum:" . $totalRec . "},\n";
$ret .= "recordType : 'array'}";
echo $ret;
}
//save action-----------------------------------
else if($json->{'action'} == 'save')
{
// delete from json-------------------------
$sql = "";
$deletedRecords = $json->{'deletedRecords'};
foreach ($deletedRecords as $value)
{
$sql = "delete from orders where order_no = :order_no";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":order_no", $value[0]);
$rc = oci_execute($s);
if (!$rc) {
$e = oci_error($s); // Statement resource passed
var_dump($e);
}
}
//update from json
$sql = "";
$updatedRecords = $json->{'updatedRecords'};
foreach ($updatedRecords as $value)
{
$sql = "update orders set employee= :p1 ,country= :p2,delivery_date= :p8 where order_no = :p0";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":p0", $value[0]);
oci_bind_by_name($s, ":p1", $value[1]);
oci_bind_by_name($s, ":p2", $value[2]);
oci_bind_by_name($s, ":p8", $value[8]);
$rc = oci_execute($s);
if (!$rc) {
$e = oci_error($s); // Statement resource passed
var_dump($e);
}
}
//insert from json
$sql = "";
$insertedRecords = $json->{'insertedRecords'};
foreach ($insertedRecords as $value)
{
//用到了STDCLASS 处理出来数据
$value=get_object_vars($value);
$sql = "insert into orders values(:p0 ,:p1 ,:p2 ,:p3 ,:p4 ,:p5 , :p6 , :p7,sysdate)";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":p0", $value[0]);
oci_bind_by_name($s, ":p1", $value[1]);
oci_bind_by_name($s, ":p2", $value[2]);
oci_bind_by_name($s, ":p3", $value[3]);
oci_bind_by_name($s, ":p4", $value[4]);
oci_bind_by_name($s, ":p5", $value[5]);
oci_bind_by_name($s, ":p6", $value[6]);
oci_bind_by_name($s, ":p7", $value[7]);
//oci_bind_by_name($s, ":p8", $value[8]);
$rc = oci_execute($s);
if (!$rc) {
$e = oci_error($s); // Statement resource passed
var_dump($e);
}
}
$ret = "{success : true,exception:''}";
echo $ret;
}
//export action--------------------------------------
else if ($json->{'action'} == 'export'){
//This is sigma grid exporting handler
require_once('GridServerHandler.php');
$gridHandler = new GridServerHandler();
//bag!!! json 不能直接引用exportType 和EXPORT NAME ,需要开发者改该gridserverhandler
//获取不到$type 不知道为何//同时也获取不到EXPORT NAME 需要在gridserverhandler.php 里直接给个文件名
//估计原因是因为json 变量引用冲突造成的,但直接给值不影响使用
//$type = getParameter('exportType');
$type = 'xls';
$s = oci_parse($c, 'select * from orders order by order_no');
oci_execute($s);
$retArray = array();
while (($row = oci_fetch_row($s)) != false){
$retArray[] = $row;
}
$data1 = $retArray;
//如何控制能够选择导出方法 :当前和所有
//----导出所有页 方法---------------------
//$gridHandler->exportXLS($data1);
//---导出当前页方法-------------
$pageSize= $json->{'pageInfo'}->{'pageSize'};
$start = $json->{'pageInfo'}->{'startRowNum'};
$data2 = array_slice($data1, $start, $pageSize);
$gridHandler->exportXLS($data2);
}
?>
