PHP jqgrid::render方法代码示例

//.........这里部分代码省略.........
        $col["dbname"] = "addresses.Phone"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Fax"; // caption of column
        $col["name"] = "Fax";
        $col["dbname"] = "addresses.Fax"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $col["search"] = false;
        $cols[] = $col;

        $g = new jqgrid();

        $grid["caption"] = "Documents";
       // $grid["multiselect"] = true;
        $grid["autowidth"] = true;
        $grid["resizable"] = true;
        //$grid["toppager"] = true;
        $grid["sortname"] = 'documents.Document_ID';
        $grid["sortorder"] = "ASC";
        $grid["add_options"] = array(
            'width'=>'420',
            "closeAfterEdit"=>true, // close dialog after add/edit
            "top"=>"200", // absolute top position of dialog
            "left"=>"200" // absolute left position of dialog
        );

        $g->set_options($grid);

        $g->set_actions(array(
                "add"=>false, // allow/disallow add
                "edit"=>true, // allow/disallow edit
                "delete"=>true, // allow/disallow delete
                "rowactions"=>true, // show/hide row wise edit/del/save option
                "export"=>true, // show/hide export to excel option
                "autofilter" => true, // show/hide autofilter for search
                "search" => "advance" // show single/multi field search condition (e.g. simple or advance)
            )
        );

        $g->select_command = "SELECT   addresses.*, persons.*, users.Default_Project,
                                       users.User_Login, users.User_Type, users.Last_Login, users.Active,
                                       users.Last_IP, documents.*, images.File_Name, images.Mime_Type
                              FROM documents
                              LEFT JOIN images ON images.Document_ID = documents.Document_ID
                              LEFT JOIN users ON users.User_ID = documents.User_ID
                              LEFT JOIN persons ON users.Person_ID = persons.Person_ID
                              LEFT JOIN person_addresses ON person_addresses.Person_ID = persons.Person_ID
                              LEFT JOIN addresses ON addresses.Address_ID = person_addresses.Address_ID";

        // set database table for CRUD operations
        $g->table = "documents";

        $g->set_columns($cols);

        // group columns header
        $g->set_group_header( array(
                "useColSpanStyle"=>true,
                "groupHeaders"=>array(
                    array(
                        "startColumnName"=>'Document_ID', // group starts from this column
                        "numberOfColumns"=>3, // group span to next 2 columns
                        "titleText"=>'Document Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'File_Name', // group starts from this column
                        "numberOfColumns"=>2, // group span to next 2 columns
                        "titleText"=>'Image Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'User_ID', // group starts from this column
                        "numberOfColumns"=>7, // group span to next 2 columns
                        "titleText"=>'User Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'First_Name', // group starts from this column
                        "numberOfColumns"=>6, // group span to next 2 columns
                        "titleText"=>'Person Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'Address1', // group starts from this column
                        "numberOfColumns"=>8, // group span to next 2 columns
                        "titleText"=>"User's Address" // caption of group header
                    )
                )
            )
        );

        // render grid and get html/js output
        $out = $g->render("documents");

        $this->render('index',array(
            'out'=>$out,
        ));
	}

$col["editable"] = true;
// this column is editable
$col["editoptions"] = array("size" => 20);
// with default display of textbox with size 20
$col["editrules"] = array("required" => true);
// and is required
$cols[] = $col;
$grid->set_columns($cols);
$e["on_insert"] = array("add_client", null, true);
$grid->set_events($e);
function add_client(&$data)
{
    $id = intval($_GET["rowid"]);
    $data["params"]["crewId"] = $id;
}
$out_detail = $grid->render("listP");
// generate grid output, with unique grid name as 'list1'
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/themes/start/jquery-ui.custom.css"></link>	
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/jqgrid/css/ui.jqgrid.css"></link>	
	
	<script src="lib/js/jquery.min.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>	
	<script src="lib/js/themes/jquery-ui.custom.min.js" type="text/javascript"></script>
	
	<!-- Add fancyBox main JS and CSS files -->

$col["editable"] = true;
$cols[] = $col;
$col = array();
$col["name"] = "litres";
$col["title"] = "Litres/piece";
$col["editable"] = true;
$cols[] = $col;
$col = array();
$col["name"] = "salesRate";
$col["title"] = "Sales Rate";
$col["editable"] = true;
$cols[] = $col;
$g->set_columns($cols);
$g->set_options($grid);
$g->set_actions(array("add" => true, "edit" => true, "delete" => true, "rowactions" => true, "search" => "advance", "showhidecolumns" => false));
$out = $g->render("1");
echo "<div style='width:90%;'>";
echo $out;
echo "</div>";
?>
<script>
$(function(){
	$(document).on('click' ,'#litres', function()
				{
			          var netWeight = $('#netWeight').val();
			          var litres = netWeight * 1.0917;
			          litres =   litres.toFixed(2);
			          $('#litres').val(litres);
			          //alert("hi");
				});
	});

//.........这里部分代码省略.........
        $col = array();
        $col["title"] = "Phone"; // caption of column
        $col["name"] = "Phone";
        $col["dbname"] = "addresses.Phone"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Fax"; // caption of column
        $col["name"] = "Fax";
        $col["dbname"] = "addresses.Fax"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $col["search"] = false;
        $cols[] = $col;


        $g = new jqgrid();

        $grid["caption"] = "PO";
       // $grid["multiselect"] = true;
        $grid["autowidth"] = true;
        $grid["resizable"] = false;
        //$grid["toppager"] = true;
        $grid["sortname"] = 'companies.Company_Name';
        $grid["sortorder"] = "ASC";
        $grid["add_options"] = array(
            'width'=>'420',
            "closeAfterEdit"=>true, // close dialog after add/edit
            "top"=>"200", // absolute top position of dialog
            "left"=>"200" // absolute left position of dialog
        );

        $g->set_options($grid);

        $g->set_actions(array(
                "add"=>false, // allow/disallow add
                "edit"=>true, // allow/disallow edit
                "delete"=>true, // allow/disallow delete
                "rowactions"=>true, // show/hide row wise edit/del/save option
                "export"=>true, // show/hide export to excel option
                "autofilter" => true, // show/hide autofilter for search
                "search" => "advance" // show single/multi field search condition (e.g. simple or advance)
            )
        );

        $g->select_command = "SELECT   companies.*, addresses.*,
                                       pos.*, images.File_Name, images.Mime_Type
                              FROM pos
                              LEFT JOIN documents ON documents.Document_ID = pos.Document_ID
                              LEFT JOIN images ON images.Document_ID = documents.Document_ID
                              LEFT JOIN vendors ON pos.Vendor_ID = vendors.Vendor_ID
                              LEFT JOIN clients ON clients.Client_ID = vendors.Vendor_Client_ID
                              LEFT JOIN companies ON clients.Company_ID = companies.Company_ID
                              LEFT JOIN company_addresses ON company_addresses.Company_ID = companies.Company_ID
                              LEFT JOIN addresses ON addresses.Address_ID = company_addresses.Address_ID";

        // set database table for CRUD operations
        $g->table = "pos";

        $g->set_columns($cols);

        // group columns header
        $g->set_group_header( array(
                "useColSpanStyle"=>true,
                "groupHeaders"=>array(
                    array(
                        "startColumnName"=>'PO_ID', // group starts from this column
                        "numberOfColumns"=>13, // group span to next 13 columns
                        "titleText"=>'PO Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'File_Name', // group starts from this column
                        "numberOfColumns"=>2, // group span to next 2 columns
                        "titleText"=>'Image Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'Company_ID', // group starts from this column
                        "numberOfColumns"=>6, // group span to next 2 columns
                        "titleText"=>'Company Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'Address1', // group starts from this column
                        "numberOfColumns"=>8, // group span to next 2 columns
                        "titleText"=>"Company's Address" // caption of group header
                    )
                )
            )
        );

        // render grid and get html/js output
        $out = $g->render("po");

        $this->render('index',array(
            'out'=>$out,
        ));
	}

// include and create object
include PHPGRID_LIBPATH . "inc/jqgrid_dist.php";
$g = new jqgrid();
// custom data (if passed) need to be filled in URL as query string ($_REQUEST);
//$grid["url"] = "subgrid_detail.php?rowid=".$_REQUEST["rowid"]."&subgrid=".$_REQUEST["subgrid"]."&closed=".$_REQUEST["closed"];
// if no custom param, it is auto set inside lib -- dont need to set
//$grid["url"] = "subgrid_detail.php?rowid=".$_REQUEST["rowid"]."&subgrid=".$_REQUEST["subgrid"];
$grid["sortname"] = 'id';
// by default sort grid by this field
$grid["sortorder"] = "desc";
// ASC or DESC
$grid["height"] = "";
// autofit height of subgrid
$grid["caption"] = "Item Data";
// caption of grid
$grid["autowidth"] = true;
// expand grid to screen width
$grid["multiselect"] = true;
// allow you to multi-select through checkboxes
$grid["export"] = array("filename" => "my-file", "sheetname" => "test");
// export to excel parameters
$g->set_options($grid);
$g->set_actions(array("add" => false, "edit" => false, "delete" => false, "rowactions" => true, "export" => true, "autofilter" => true, "search" => "advance"));
$c_id = $_REQUEST["rowid"];
# composite key implementation
$g->select_command = "select concat(id,'-',num) as `key`, i.* FROM invlines i\n\t\t\t\t\t\tWHERE id = {$c_id}";
// this db table will be used for add,edit,delete
$g->table = "invlines";
// generate grid output, with unique grid name as 'list1'
$out = $g->render("sub2");
echo $out;

//.........这里部分代码省略.........
         $col["formatter"] = "date";
         // format as date
         $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd-m-Y', "opts" => array("changeYear" => false));
         $cols[] = $col;
         #ref_number
         $col = array();
         $col['title'] = $this->lang->line('ref_no');
         $col['name'] = "ref_number";
         $col["editable"] = true;
         // this column is editable
         $cols[] = $col;
         #tax Datail
         $col = array();
         $col['title'] = $this->lang->line('tax_detail');
         $col['name'] = "tax_details";
         $col["width"] = "170";
         $col["editable"] = true;
         // this column is editable
         $col['edittype'] = "textarea";
         $col["editoptions"] = array("rows" => "2", "cols" => "20");
         $col["editrules"] = array("required" => true);
         $col["formatter"] = "autocomplete";
         // autocomplete
         $col["formatoptions"] = array("sql" => "SELECT DISTINCT tax_details as k, tax_details as v FROM taxbuy", "search_on" => "tax_details", "update_field" => "tax_details");
         $cols[] = $col;
         #Price
         $col = array();
         $col['title'] = $this->lang->line('total_price');
         $col['name'] = "total_price";
         $col["editable"] = true;
         $col["editrules"] = array("number" => true, "required" => true);
         $col["editoptions"] = array("onblur" => "update_vat()");
         $col["align"] = "right";
         $col["formatter"] = "number";
         $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2');
         $col["search"] = false;
         $cols[] = $col;
         #vat
         $col = array();
         $col['title'] = $this->lang->line('vat');
         $col['name'] = "total_vat";
         $col["editable"] = true;
         $col["editrules"] = array("number" => true, "required" => true);
         #$col["editoptions"] = array("onblur" => "update_vat()");
         $col["formatter"] = "number";
         $col["align"] = "right";
         $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2');
         $col["search"] = false;
         $cols[] = $col;
         #Total
         $col = array();
         $col['title'] = $this->lang->line('total_amount');
         $col["align"] = "right";
         $col['name'] = "total_amount";
         $col["editrules"] = array("number" => true, "required" => true);
         $col["editable"] = true;
         $col["formatter"] = "number";
         $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2');
         $col["search"] = false;
         $cols[] = $col;
         #Note
         $col = array();
         $col["title"] = $this->lang->line('remark');
         $col["name"] = "note";
         $col["width"] = "150";
         $col['editable'] = true;
         $col["edittype"] = "textarea";
         $col["editoptions"] = array("rows" => "2", "cols" => "20");
         $cols[] = $col;
         //$g->set_options($opt);
         $e["js_on_load_complete"] = "grid1_onload";
         $g->set_events($e);
         //Use Table
         $g->table = "taxbuy";
         // pass the cooked columns to grid
         $g->set_columns($cols);
         $opt["caption"] = $this->lang->line('buytax_list');
         $opt["sortname"] = 'tax_id';
         $opt["sortorder"] = "desc";
         $opt['rowNum'] = 10;
         $opt['rowList'] = array(10, 20, 30, 100);
         $opt["autowidth"] = true;
         $opt["footerrow"] = true;
         $opt["reloadedit"] = true;
         $opt["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '400');
         $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '400');
         $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }';
         $opt["edit_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }';
         $g->set_options($opt);
         $g->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'buytax', 'add_buytax'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'buytax', 'edit_buytax'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'buytax', 'del_buytax'), "view" => false, "rowactions" => false, "autofilter" => true, "search" => "advance", "inlineadd" => false, "showhidecolumns" => false));
         // render grid and get html/js output
         $out_index = $g->render("list1");
         $h2_title = $this->lang->line('buytax');
         //display
         $this->_example_output((object) array('output' => '', 'out' => $out_index, 'h2_title' => $h2_title, 'js_files' => array(), 'css_files' => array()));
     } else {
         //If no session, redirect to login page
         redirect('login', 'refresh');
     }
 }

// Custom UPDATE
function update_changes($data)
{
    // mysql_query("START TRANSACTION");
    // $query1 = mysql_query("UPDATE Components_SKU SET
    // 	amount=amount-'{$data["params"]["amount"]}' WHERE component_id = {$data["component_id"]}");
    // $query2 = mysql_query("UPDATE invlines SET
    // 	invdate='{$data["params"]["invdate"]}' WHERE id = {$data["id"]}");
    // if ($query1 and $query2) {
    //     mysql_query("COMMIT");
    // } else {
    //     mysql_query("ROLLBACK");
    // }
}
// generate grid output, with unique grid name as 'list1'
$out_detail = $g->render("list2");
?>

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>RadioSklad</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="">
    <meta name="author" content="">

    <!-- HTML5 shim, for IE6-8 support of HTML5 elements -->
    <!--[if lt IE 9]>
      <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
	<![endif]-->

//.........这里部分代码省略.........
        mysql_query("SET NAMES 'utf8'");

        Yii::import('ext.phpgrid.inc.jqgrid');

        // set columns
        $col = array();
        $col["title"] = "Message_ID"; // caption of column
        $col["name"] = "Message_ID";
        $col["dbname"] = "Message_ID"; // grid column name, same as db field or alias from sql
        $col["resizable"] = false;
        $col["editable"] = false; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = false;
        $col["sortable"] = true;
        $cols[] = $col;

        // set columns
        $col = array();
        $col["title"] = "Message_Text"; // caption of column
        $col["name"] = "Message_Text";
        $col["dbname"] = "Message_Text"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = true;
        $col["sortable"] = true;
        $cols[] = $col;

        // set columns
        $col = array();
        $col["title"] = "Message_Type"; // caption of column
        $col["name"] = "Message_Type";
        $col["dbname"] = "Message_Type"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = true;
        $col["sortable"] = true;
        $cols[] = $col;

        $g = new jqgrid();

        $grid["caption"] = "Custom messages";
        // $grid["multiselect"] = true;
        $grid["autowidth"] = true;
        $grid["resizable"] = true;
        //$grid["toppager"] = true;
        $grid["sortname"] = 'Message_ID';
        $grid["sortorder"] = "ASC";
        $grid["add_options"] = array(
            'width'=>'420',
            "closeAfterEdit"=>true, // close dialog after add/edit
            "top"=>"200", // absolute top position of dialog
            "left"=>"200" // absolute left position of dialog
        );

        $g->set_options($grid);

        $g->set_actions(array(
                "add"=>true, // allow/disallow add
                "edit"=>true, // allow/disallow edit
                "delete"=>true, // allow/disallow delete
                "rowactions"=>true, // show/hide row wise edit/del/save option
                "export"=>true, // show/hide export to excel option
                "autofilter" => true, // show/hide autofilter for search
                "search" => "advance" // show single/multi field search condition (e.g. simple or advance)
            )
        );

        $g->select_command = "SELECT custom_messages.*
                              FROM custom_messages";

        // set database table for CRUD operations
        $g->table = "custom_messages";

        $g->set_columns($cols);

        // group columns header
        $g->set_group_header( array(
                "useColSpanStyle"=>true,
                "groupHeaders"=>array(
                    array(
                        "startColumnName"=>'Message_ID', // group starts from this column
                        "numberOfColumns"=>2, // group span to next 2 columns
                        "titleText"=>'ID' // caption of group header
                    ),
                )
            )
        );

        // render grid and get html/js output
        $out = $g->render("Messages");

        $this->render('index',array(
            'out'=>$out,
        ));
    }

 public function index()
 {
     //check login
     if ($this->session->userdata('user_name')) {
         $i_rule = $this->session->userdata('user_cizacl_role_id');
         if ($this->cizacl->check_isAllowed($i_rule, 'income')) {
             $g = new jqgrid();
             $opt["caption"] = $this->lang->line('factory');
             $opt["detail_grid_id"] = "list2";
             $opt['autowidth'] = true;
             $opt['height'] = "60";
             // extra params passed to detail grid, column name comma separated
             //$opt["subgridparams"] = "client_id,gender,company";
             $opt["subgridparams"] = "factory_id,factory_code,factory_name";
             $opt["export"]["range"] = "filtered";
             $g->set_options($opt);
             $g->table = "transport_factory";
             $col = array();
             $col["title"] = $this->lang->line('id');
             // caption of column
             $col["name"] = "factory_id";
             // field name, must be exactly same as with SQL prefix or db field
             $col["width"] = "10";
             $col['hidden'] = true;
             $col["editable"] = false;
             $cols[] = $col;
             $col = array();
             $col['title'] = $this->lang->line('factory_code');
             $col['name'] = "factory_code";
             $col["editable"] = false;
             $cols[] = $col;
             $col = array();
             $col['title'] = $this->lang->line('factory_name');
             $col['name'] = "factory_name";
             //$col["width"] = "10";
             $col["editable"] = false;
             $cols[] = $col;
             $g->set_columns($cols);
             $g->set_actions(array("add" => false, "edit" => false, "delete" => false, "rowactions" => false, "autofilter" => false, "search" => "advance"));
             $out_master = $g->render("list1");
             // detail grid
             $grid = new jqgrid();
             $opt = array();
             $opt["sortname"] = 'id';
             // by default sort grid by this field
             $opt["sortorder"] = "desc";
             // ASC or DESC
             $opt["height"] = 300;
             // autofit height of subgrid
             $opt["rowNum"] = 10;
             // by default 20
             $opt['rowList'] = array(10, 20, 30, 100, 1000);
             $opt["autowidth"] = true;
             $opt['form']['position'] = "left";
             $opt['form']['nav'] = true;
             $opt["caption"] = $this->lang->line('expense_list');
             // caption of grid
             $opt["multiselect"] = false;
             // allow you to multi-select through checkboxes
             //footer
             $opt["footerrow"] = true;
             $opt["reloadedit"] = true;
             // Check if master record is selected before detail addition
             $opt["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('Please select master record first'); return false; } }";
             $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }';
             // excel visual params
             // $opt["cellEdit"] = true; // inline cell editing, like spreadsheet
             $opt["rownumbers"] = true;
             $opt["rownumWidth"] = 30;
             $grid->set_options($opt);
             // disable all dialogs except edit
             $grid->navgrid["param"]["edit"] = false;
             $grid->navgrid["param"]["add"] = false;
             $grid->navgrid["param"]["del"] = false;
             $grid->navgrid["param"]["search"] = false;
             $grid->navgrid["param"]["refresh"] = true;
             $grid->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'expense', 'add_expense'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'expense', 'edit_expense'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'expense', 'del_expense'), "rowactions" => true, "export" => false, "autofilter" => true, "search" => "advance"));
             // enable inline editing buttons
             $grid->set_actions(array("inline" => true, "rowactions" => true));
             // set database table for CRUD operations
             if (!empty($_REQUEST)) {
                 $id = intval($_GET["rowid"]);
                 $factory_code = $_GET["factory_code"];
                 $company = $_GET["factory_name"];
                 $cid = intval($_GET["factory_id"]);
                 $this->session->set_userdata('facs_id', $cid);
             }
             $fac_id = $this->session->userdata('facs_id');
             // for non-int fields as PK
             // $id = (empty($_GET["rowid"])?0:$_GET["rowid"]);
             // and use in sql for filteration
             //$grid->select_command = "SELECT id,client_id,invdate,amount,tax,total,'$company' as 'company' FROM invheader WHERE client_id = $cid";
             //$grid->select_command = "SELECT id,income_date,factory_id,ref_number,total_amount,note FROM `income` WHERE factory_id=$cid";
             //$grid->select_command = "SELECT id,expense_date,factory_id,car_id,ref_number,expense_details,total_amount,	note,(SELECT SUM(total_amount)FROM	expense WHERE factory_id = $cid	) AS sumtotals FROM	`expense`WHERE	factory_id = $cid";
             // this db table will be used for add,edit,delete
             $grid->select_command = "SELECT\n\tid,\n\texpense_date,\n\tfactory_id,\n\texpense.car_id,\n\tcar_number,\n\tref_number,\n\texpense_details,\n\ttotal_amount,\n\texpense.note,\n\t(\n\t\tSELECT\n\t\t\tSUM(total_amount)\n\t\tFROM\n\t\t\texpense\n\t\tWHERE\n\t\t\tfactory_id = {$fac_id}\n\t) AS sumtotals\nFROM\n\t`expense`\nLEFT JOIN transport_cars ON(expense.car_id=transport_cars.car_id)\nWHERE\n\tfactory_id = {$fac_id} AND expense.car_id=0";
             //$grid->table = "invheader";
             $grid->table = "expense";
             #ID
             $col = array();
//.........这里部分代码省略.........

//.........这里部分代码省略.........
         $g->select_command = "SELECT\n\tid,\n\tdp_number,\n\torders.customer_id,\n    cus.customers_name AS cid,\n    orders.factory_id,\n\tfac.factory_code,\n\treal_distance,\n\torders.distance_id,\n\tdis.distance_code,\n\torders.cubic_id,\n    cubic.cubic_value,\n    orders.price AS price,\n\torders.use_oil,\n    orders.car_id,\n\tcar.car_number,\n\torders.driver_id,\n\tdri.driver_name,\n    order_date,\n    orders.delivery_datetime,\n    orders.remark\n\nFROM\n\t`orders` AS orders\nLEFT JOIN transport_factory AS fac ON (\n\torders.factory_id = fac.factory_id\n)\nLEFT JOIN transport_customers AS cus ON (\n\torders.customer_id = cus.customer_id\n)\nLEFT JOIN transport_cubiccode AS cubic ON (\n\torders.cubic_id = cubic.cubic_id\n)\nLEFT JOIN distancecode AS dis ON (\n\torders.distance_id = dis.distance_id\n)\nLEFT JOIN transport_cars AS car ON (\norders.car_id = car.car_id\n)\nLEFT JOIN driver AS dri ON (\n\torders.driver_id = dri.driver_id\n)";
         // this db table will be used for add,edit,delete
         $g->table = "orders";
         $e["on_after_insert"] = array("after_insert", null, true);
         $e["on_update"] = array("do_update", null, true);
         /*      
         $e["on_insert"] = array(
             "add_order",
             null,
             true);
         */
         $e["on_data_display"] = array("filter_display", null, true);
         $g->set_events($e);
         function add_order(&$data)
         {
             //print_r($data);
             $obj =& get_instance();
             $obj->load->model("price_model", "pricelist");
             //$id = intval($_REQUEST["id"]);
             $factory_id = $data["params"]["factory_id"];
             $distance_id = $data["params"]["distance_id"];
             $cubid_id = $data["params"]["cubic_id"];
             $order_date = $data["params"]["order_date"];
             #get price
             $order_price = $obj->pricelist->get_order_Price($factory_id, $cubid_id, $distance_id, $order_date);
             if ($order_price == null) {
                 $str = ob_get_clean();
                 $str = "ไม่มีการกำหนดราคาค่าขนส่ง";
                 phpgrid_error($str);
             }
             /*check DP NUmber*/
             $check_sql = "SELECT count(*) as c from orders where LOWER(`dp_number`) = '" . strtolower($data["params"]["dp_number"]) . "'";
             $rs = mysql_fetch_assoc(mysql_query($check_sql));
             if ($rs["c"] > 0) {
                 phpgrid_error("หมายเลข DP Number ซ้ำ");
             }
             /*End Check DP Number*/
             //$str = "UPDATE orders SET price ='{$data["parmas"]["price"]}' WHERE id = '{$data["id"]}'";
             //mysql_query($str);
         }
         function do_update(&$data)
         {
             //print_r($data);
             $obj =& get_instance();
             $obj->load->model("price_model", "pricelist");
             $id = intval($_REQUEST["id"]);
             $factory_id = $data["params"]["factory_id"];
             $distance_id = $data["params"]["distance_id"];
             $cubid_id = $data["params"]["cubic_id"];
             $order_date = $data["params"]["order_date"];
             #get price
             $order_price = $obj->pricelist->get_order_Price($factory_id, $cubid_id, $distance_id, $order_date);
             if ($order_price == null) {
                 $str = ob_get_clean();
                 $str = "ไม่มีการกำหนดราคาค่าขนส่ง";
                 phpgrid_error($str);
             } else {
                 $data["params"]["price"] = $order_price;
                 $str = "UPDATE orders SET price ='{$data["parmas"]["price"]}' WHERE id = '{$data["id"]}'";
                 mysql_query($str);
             }
             //$str = "UPDATE orders SET price ='{$data["parmas"]["price"]}' WHERE id = '{$data["id"]}'";
             //mysql_query($str);
         }
         function after_insert(&$data)
         {
             //print_r($data);
             $obj =& get_instance();
             $obj->load->model("price_model", "pricelist");
             $id = $data["id"];
             $factory_id = $data["params"]["factory_id"];
             $distance_id = $data["params"]["distance_id"];
             $cubid_id = $data["params"]["cubic_id"];
             $order_date = $data["params"]["order_date"];
             #get price
             $order_price = $obj->pricelist->get_order_Price($factory_id, $cubid_id, $distance_id, $order_date);
             $str = "UPDATE orders SET price ='{$order_price}' \n                    WHERE id = {$data["id"]}";
             mysql_query($str);
         }
         function filter_display($data)
         {
             foreach ($data["params"] as &$d) {
                 foreach ($d as $k => $v) {
                     $d[$k] = strtoupper($d[$k]);
                 }
             }
         }
         // pass the cooked columns to grid
         $g->set_columns($cols);
         // generate grid output, with unique grid name as 'list1'
         $out = $g->render("list1");
         $iprice = $this->price->get_order_Price(2, 2, 2);
         //display
         $this->_example_output((object) array('output' => "", 'out' => $out, 'iprice' => $iprice, 'js_files' => array(), 'css_files' => array()));
         //$this->_example_output();
     } else {
         redirect('login', 'refresh');
     }
     //end if
 }

//.........这里部分代码省略.........
        $col["search"] = true;
        $col["sortable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Additional Storage Fee"; // caption of column
        $col["name"] = "Additional_Storage_Fee";
        $col["dbname"] = "Additional_Storage_Fee"; // grid column name, same as db field or alias from sql
        $col["resizable"] = false;
        $col["editable"] = true; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = true;
        $col["sortable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Trial_Period"; // caption of column
        $col["name"] = "Trial_Period";
        $col["dbname"] = "Trial_Period"; // grid column name, same as db field or alias from sql
        $col["resizable"] = false;
        $col["editable"] = true; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = true;
        $col["sortable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Description"; // caption of column
        $col["name"] = "Description";
        $col["dbname"] = "Description"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["hidden"] = false;
        $col["viewable"] = true;
        $col["search"] = true;
        $col["sortable"] = false;
        $col["edittype"] = "textarea";
        $col["editoptions"] = array("rows"=>15, "cols"=>80);
        $cols[] = $col;


        $g = new jqgrid();

        $grid["caption"] = "Service Level Settings";
        // $grid["multiselect"] = true;
        $grid["autowidth"] = true;
        $grid["resizable"] = true;
        //$grid["toppager"] = true;
        $grid["sortname"] = 'Service_Level_ID';
        $grid["sortorder"] = "ASC";
        $grid["add_options"] = array(
            'width'=>'600',
            "closeAfterEdit"=>true, // close dialog after add/edit
            "top"=>"200", // absolute top position of dialog
            "left"=>"200" // absolute left position of dialog
        );

        $g->set_options($grid);

        $g->set_actions(array(
                "add"=>true, // allow/disallow add
                "edit"=>true, // allow/disallow edit
                "delete"=>false, // allow/disallow delete
                "rowactions"=>true, // show/hide row wise edit/del/save option
                "export"=>true, // show/hide export to excel option
                "autofilter" => true, // show/hide autofilter for search
                "search" => "advance" // show single/multi field search condition (e.g. simple or advance)
            )
        );

        $g->select_command = "SELECT service_level_settings.*
                              FROM service_level_settings";

        // set database table for CRUD operations
        $g->table = "service_level_settings";

        $g->set_columns($cols);

        // group columns header
        $g->set_group_header( array(
                "useColSpanStyle"=>true,
                "groupHeaders"=>array(
                    array(
                        "startColumnName"=>'Service_Level_ID', // group starts from this column
                        "numberOfColumns"=>11, // group span to next 2 columns
                        "titleText"=>'Service Level Settings Information' // caption of group header
                    ),
                )
            )
        );

        // render grid and get html/js output
        $out = $g->render("service_level_settings");

        $this->render('index',array(
            'out'=>$out,
        ));
    }

//.........这里部分代码省略.........
        $col = array();
        $col["title"] = "Country"; // caption of column
        $col["name"] = "Country";
        $col["dbname"] = "addresses.Country"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Phone"; // caption of column
        $col["name"] = "Phone";
        $col["dbname"] = "addresses.Phone"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $cols[] = $col;

        $col = array();
        $col["title"] = "Fax"; // caption of column
        $col["name"] = "Fax";
        $col["dbname"] = "addresses.Fax"; // grid column name, same as db field or alias from sql
        $col["resizable"] = true;
        $col["editable"] = true; // this column is editable
        $col["viewable"] = true;
        $col["search"] = false;
        $cols[] = $col;

        $g = new jqgrid();

        $grid["caption"] = "Vendor List";
       // $grid["multiselect"] = true;
        $grid["autowidth"] = true;
        $grid["resizable"] = true;
        //$grid["toppager"] = true;
        $grid["sortname"] = 'companies.Company_Name';
        $grid["sortorder"] = "ASC";

        $grid["add_options"] = array(
            'width'=>'420',
            "closeAfterEdit"=>true, // close dialog after add/edit
            "top"=>"200", // absolute top position of dialog
            "left"=>"200" // absolute left position of dialog
        );

        $g->set_options($grid);

        $g->set_actions(array(
                "add"=>false, // allow/disallow add
                "edit"=>true, // allow/disallow edit
                "delete"=>true, // allow/disallow delete
                "rowactions"=>true, // show/hide row wise edit/del/save option
                "export"=>true, // show/hide export to excel option
                "autofilter" => true, // show/hide autofilter for search
                "search" => "advance" // show single/multi field search condition (e.g. simple or advance)
            )
        );

        $g->select_command = "SELECT vendors.*, companies.*, addresses.*
                              FROM vendors
                              LEFT JOIN clients ON clients.Client_ID = vendors.Vendor_Client_ID
                              LEFT JOIN companies ON clients.Company_ID = companies.Company_ID
                              LEFT JOIN company_addresses ON company_addresses.Company_ID = companies.Company_ID
                              LEFT JOIN addresses ON addresses.Address_ID = company_addresses.Address_ID";

        // set database table for CRUD operations
        $g->table = "vendors";

        $g->set_columns($cols);

        // group columns header
        $g->set_group_header( array(
                "useColSpanStyle"=>true,
                "groupHeaders"=>array(
                    array(
                        "startColumnName"=>'Vendor_ID', // group starts from this column
                        "numberOfColumns"=>9, // group span to next 2 columns
                        "titleText"=>'Vendor Information' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'Company_ID', // group starts from this column
                        "numberOfColumns"=>6, // group span to next 2 columns
                        "titleText"=>'Vendor Company' // caption of group header
                    ),
                    array(
                        "startColumnName"=>'Address1', // group starts from this column
                        "numberOfColumns"=>8, // group span to next 2 columns
                        "titleText"=>"Vendor's Address" // caption of group header
                    )
                )
            )
        );

        // render grid and get html/js output
        $out = $g->render("Vendors");

        $this->render('index',array(
            'out'=>$out,
        ));
	}

$col["name"] = "gender";
// nama colom gender diambil dari database mysql
$col["editable"] = true;
$cols[] = $col;
$col = array();
$col["title"] = "Perusahaan";
// nama kolom yang akan ditampilkan
$col["name"] = "company";
// nama colom company diambil dari database mysql
$col["editable"] = true;
$col["editoptions"] = array("defaultValue" => "Default Company");
$cols[] = $col;
$g->set_columns($cols);
$g->set_actions(array("add" => true, "edit" => true, "delete" => true, "clone" => true, "rowactions" => true, "search" => "advance", "showhidecolumns" => false));
// untuk menampilkan tabel
$tampil = $g->render("list1");
// untuk Tema
$themes = array("black-tie", "blitzer", "cupertino", "dark-hive", "dot-luv", "eggplant", "excite-bike", "flick", "hot-sneaks", "humanity", "le-frog", "mint-choc", "overcast", "pepper-grinder", "redmond", "smoothness", "south-street", "start", "sunny", "swanky-purse", "trontastic", "ui-darkness", "ui-lightness", "vader");
$i = rand(0, 8);
if (is_numeric($_GET["themeid"])) {
    $i = $_GET["themeid"];
} else {
    $i = 14;
}
?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Membuat CRUD dengan phpjqGrid,boostrap & mysql</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

 function getDataGrid($id)
 {
     $g = new jqgrid();
     $pageL = $this->database->getTableLink($id);
     $type = 'gridoptions';
     $result = $this->database->getGrid1($type, $id);
     while ($row = $result->FetchRow()) {
         $value = $row[2];
         if ($row[0] != 'table' and $row[0] != 'where') {
             if ($row[1] == 'number') {
                 $value = (int) $value;
             }
             if ($row[1] == 'boolean') {
                 if ($value == 'true') {
                     $value = true;
                 } else {
                     $value = false;
                 }
             }
             $grid[$row[0]] = $value;
         }
         if ($row[0] == 'sql') {
             $value = str_replace('operatorId', $_SESSION['opridLogin'], $value);
             $g->select_command = $value;
         }
         if ($row[0] == 'table') {
             $g->table = $value;
         }
         if ($id == 'nb_factura_de_pg') {
             if (isset($_GET["factura"])) {
                 $factura = $_GET["factura"];
                 if (!is_numeric($factura)) {
                     $factura = 0;
                 }
             } else {
                 $factura = 0;
             }
             $g->select_command = "Select * from nb_detallef_tbl where factura like '" . $factura . "-%'";
         }
     }
     $campos = $this->database->getGrid2($id);
     $type = 'gridcoloptions';
     $cols = array();
     while ($camposDescribe = $campos->FetchRow()) {
         $result = $this->database->getGrid3($type, $id, $camposDescribe[0]);
         $col = array();
         while ($row = $result->FetchRow()) {
             $value = $row[2];
             if ($row[0] == 'link') {
                 $value = "nabu.php?p=" . $pageL[0] . "&accion=b&" . $value;
             }
             if ($row[0] == 'linkE') {
                 $row[0] = 'link';
             }
             if ($row[1] == 'number') {
                 $value = (int) $value;
             }
             if ($row[1] == 'boolean') {
                 if ($value == 'true') {
                     $value = true;
                 } else {
                     $value = false;
                 }
             }
             $col[$row[0]] = $value;
         }
         $cols[] = $col;
     }
     $this->database->conectar();
     $g->set_columns($cols);
     $g->set_options($grid);
     if ($id == 'nb_factura_de_pg') {
         $configGrid = true;
     } else {
         $configGrid = false;
     }
     $g->set_actions(array("add" => $configGrid, "edit" => $configGrid, "delete" => $configGrid, "rowactions" => true, "search" => "advance"));
     return $g->render("list1");
 }

    function set_pdf_format($arr)
    {
        $pdf = $arr["pdf"];
        $data = $arr["data"];
        /*
            foreach ($data as $v1) {
            foreach ($v1 as $v2) {
                echo "$v2\n";
            }
        	}
        	echo count($data, COUNT_RECURSIVE);*/
        //$pdf->SetFont('dejavusans', '', 10);
        $pdf->SetLineWidth(0.1);
    }
    // generate grid output, with unique grid name as 'list1'
    $out_detail = $d_grid->render("detail");
}
//fin else du départ
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/themes/south-street/jquery-ui.custom.css"></link>
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/jqgrid/css/ui.jqgrid.css"></link>

	<script src="lib/js/jquery.min.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/i18n/grid.locale-fr.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
	<script src="lib/js/themes/jquery-ui.custom.min.js" type="text/javascript"></script>
	<script src="//cdn.jsdelivr.net/jquery.hotkeys/0.8b/jquery.hotkeys.js"></script>