public function index() { //check login if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); $g = new jqgrid(); #ID $col = array(); $col['title'] = "ID"; $col['name'] = "customer_id"; $col['hidden'] = true; $col['isnull'] = true; $cols[] = $col; #Factory $col = array(); $col["title"] = $this->lang->line("factory_name"); $col["name"] = "factory_id"; $col["dbname"] = "cus.factory_id"; // this is required as we need to search in name field, not id $col["width"] = "100"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("select distinct factory_id as k, factory_name as v from transport_factory"); $col["editoptions"] = array("value" => ":;" . $str); $col["formatter"] = "select"; // display label, not value $col["editrules"] = array("required" => true); $cols[] = $col; #Customer_Type $col = array(); $col["title"] = $this->lang->line('customer_type'); $col["name"] = "customer_type_id"; $col["dbname"] = "cus_type.customer_type_title"; // this is required as we need to search in name field, not id $col["fixed"] = true; //$col["width"] = "65"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value //$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients"); $str = $g->get_dropdown_values("SELECT DISTINCT customer_type_id AS k, customer_type_title AS v FROM `transport_customer_type` WHERE customer_type_status ='2'"); #$str = $this->dropdrown->get_customer_type(); $col["editoptions"] = array("value" => $str); $col["formatter"] = "select"; // display label, not value $cols[] = $col; #Customer name $col = array(); $col['title'] = $this->lang->line('customer_agancy'); $col['name'] = "customers_name"; $col["editrules"] = array("required" => true); $col["search"] = true; $col['editable'] = true; $cols[] = $col; $col = array(); $col['title'] = $this->lang->line('car_number'); $col['name'] = "car_id"; $col['dbname'] = "cus.car_id"; $col["width"] = "100"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("SELECT DISTINCT car_id AS k,car_number AS v FROM transport_cars;"); $col["editoptions"] = array("value" => ":;" . $str); $col["formatter"] = "select"; // display label, not value $cols[] = $col; #remark $col = array(); $col['title'] = $this->lang->line('car_license'); $col['name'] = "car_license"; $col['editable'] = true; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => "2", "cols" => "20"); $cols[] = $col; #Address $col = array(); $col['title'] = $this->lang->line('Address1'); $col['name'] = "address1"; $col['editable'] = true; $col["search"] = false; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => "2", "cols" => "20"); $cols[] = $col; #Contact Person $col = array(); $col['title'] = $this->lang->line('contact_person'); $col['name'] = "contact_person"; $col['editable'] = true; $cols[] = $col; # Tel /* $col = array(); $col['title'] = $this->lang->line('tel'); $col['name'] = "mobile_number"; $col['editable'] = true; $col["editrules"] = array("number" => true); $cols[] = $col; */ #remark $col = array(); $col['title'] = $this->lang->line('note'); $col['name'] = "remark"; $col['editable'] = true; $col['edittype'] = "textarea"; $col["viewable"] = false; $col['editoptions'] = array("rows" => "2", "cols" => "20"); $cols[] = $col; $e["on_insert"] = array("add_client", null, true); #$e["on_delete"] = array("del_client", null, true); $g->set_events($e); $g->select_command = "SELECT\n\tcustomer_id,\n\tcus.customer_type_id,\n\tcus_type.customer_type_title,\n\tcus.factory_id,\n\tcustomers_name,\n\tcus.car_id,\n\tcus.car_license,\n\taddress1,\n\tcontact_person,\n\tmobile_number,\n\tremark\nFROM\n\ttransport_customers AS cus\nLEFT JOIN transport_factory AS fac ON (\n\tcus.factory_id = fac.factory_id\n)\nLEFT JOIN transport_customer_type AS cus_type ON (\n\tcus.customer_type_id = cus_type.customer_type_id\n)\nLEFT JOIN transport_cars AS car ON (cus.car_id=car.car_id)\nWHERE\n\tcus. STATUS = 1\nAND cus_type.customer_type_status != 1"; function add_client(&$data) { $check_sql = "SELECT count(*) as c from transport_customers where LOWER(`customers_name`) = '" . strtolower($data["params"]["customers_name"]) . "'"; $rs = mysql_fetch_assoc(mysql_query($check_sql)); if ($rs["c"] > 0) { phpgrid_error("ข้อมูลลูกค้าซ้ำ"); } } // end of sub function function del_client(&$data) { $check_sql = "UPDATE `transport_customers` SET `status`='0' WHERE (`customer_id`='{$data["id"]}')"; mysql_query($check_sql); } //Use Table $g->table = "transport_customers"; // pass the cooked columns to grid $g->set_columns($cols); $opt["sortname"] = 'customer_id'; $opt["sortorder"] = "desc"; $opt["rowList"] = array(10, 20, 30); $opt["caption"] = $this->lang->line('customer'); $opt["autowidth"] = true; $opt["multiselect"] = false; $opt["autowidth"] = true; $opt["view_options"]['width'] = '520'; $opt["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"]["beforeSubmit"] = "function(post,form){ return validate_form_once(post,form); }"; $g->set_options($opt); $g->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'add_customer'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'edit_customer'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'del_customer'), "export" => false, "autofilter" => true, "search" => "advance")); // set database table for CRUD operations // render grid and get html/js output $out_index = $g->render("list1"); $h2_title = $this->lang->line('customeroils'); //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'); } }
public function index() { if ($this->session->userdata('user_name')) { $g = new jqgrid(); $dispyPrice = $this->price->dispalyPrice(); $titke_cubic = $this->price->header_cubic(); $row_distance = $this->price->row_distance(); $i = 0; foreach ($row_distance as $key => $val) { // $i = $val['distance_id']; foreach ($titke_cubic as $row) { $data[$i]["id"] = $i; $data[$i]["distacne"] = $dispyPrice[$i]['distance']; $data[$i]['cubic'][$row->cubic_id] = 2 + $i; //$dispyPrice[$i][$row->cubic_id]; $data[$i]["pricelist_id"] = $dispyPrice[$row->id]; } $i = $val['distance_id']; } $g->table = $data; // ห$g->table = $pricelist; $col = array(); $col["title"] = "id"; // caption of column $col["name"] = "id"; $col["width"] = "250"; $col["sorttype"] = int; $col['editable'] = true; $col['hidden'] = true; $cols[] = $col; $col = array(); $col["title"] = "pricelist_id"; // caption of column $col["name"] = "pricelist_id"; $col["width"] = "250"; $col["sorttype"] = int; $col['editable'] = true; #$col['hidden'] = true; //$cols[] = $col; $col = array(); $col["title"] = "Cubic /<br/> Distance"; // caption of column $col["name"] = "distacne"; $col["width"] = "350"; $cols[] = $col; foreach ($titke_cubic as $row) { $col = array(); $col['title'] = "{$row->cubic_value}"; $col['name'] = "cubic[{$row->cubic_id}]"; $col['editable'] = true; $col['search'] = false; $cols[] = $col; } $g->set_columns($cols); $e["js_on_select_row"] = "do_onselect"; $e["on_update"] = array("do_update", null, true); $e["on_data_display"] = array("filter_display", null, true); $g->set_events($e); function filter_display($data) { /* These comments are just to show the input param format Array ( [params] => Array ( [0] => Array ( [client_id] => 1 [name] => Client 1 [gender] => My custom malea [company] => My custom Client 1 Company 1 ) [1] => Array ( [client_id] => 2 [name] => Client 2 [gender] => male [company] => Client 2 Com2pany 11 ) ....... */ foreach ($data["params"] as &$d) { foreach ($d as $k => $v) { $d[$k] = strtoupper($d[$k]); } } } function do_update(&$data) { // $obj = &get_instance(); print_r($data); $id = intval($_REQUEST["id"]); } $g->set_actions(array("add" => false, "edit" => true, "bulkedit" => true, "delete" => true, "rowactions" => true, "autofilter" => true, "search" => "simple")); $opt['caption'] = "d"; $opt["sortname"] = 'id'; // by default sort grid by this field $opt["sortorder"] = "asc"; // ASC or DESC $opt['rowNum'] = 30; $opt["autowidth"] = true; $opt["cellEdit"] = true; $g->set_options($opt); //Display $pricetable = $g->render("list1"); $this->_example_output((object) array('output' => '', 'out' => $out, 'dispyPrice' => $dispyPrice, 'list_row' => $num_row, 'pricelist' => $pricelist, 'pricetable' => $pricetable)); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
## ------------------ ## ## SERVER SIDE EVENTS ## ## ------------------ ## // params are array(<function-name>,<class-object> or <null-if-global-func>,<continue-default-operation>) // if you pass last argument as true, functions will act as a data filter, and insert/update will be performed by grid $e["on_insert"] = array("add_client", null, false); $e["on_update"] = array("update_client", null, false); $e["on_delete"] = array("delete_client", null, true); # $e["on_after_insert"] = array("after_insert", null, true); // return last inserted id for further working $e["on_data_display"] = array("filter_display", null, true); ## ------------------ ## ## CLIENT SIDE EVENTS ## ## ------------------ ## // just set the JS function name (should exist) $e["js_on_select_row"] = "do_onselect"; $grid->set_events($e); function update_client($data) { /* These comments are just to show the input param format $data => Array ( [client_id] => 2 [params] => Array ( [client_id] => 2 [name] => Client 2 [gender] => male [company] => Client 2 Company )
public function index() { //check login if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); $g = new jqgrid(); $col = array(); $col["title"] = "Id"; // caption of column $col["name"] = "tax_id"; $col["width"] = "10"; /*$col["fixed"] = true;*/ $col["search"] = false; $col["editable"] = false; $col["hidden"] = true; $cols[] = $col; #tax date $col = array(); $col['title'] = $this->lang->line('date'); $col['name'] = "tax_date"; $col["width"] = "170"; $col["editable"] = true; // this column is editable $col["editoptions"] = array("size" => 20, "defaultValue" => date("d-m-Y")); // with default display of textbox with size 20 $col["editrules"] = array("required" => true); // and is required $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'); } }
public function index() { //check login if ($this->session->userdata('user_name')) { $g = new jqgrid(); $opt["caption"] = "รายการรับ - จ่าย น้ำมัน"; // following params will enable subgrid -- by default first column (PK) of parent is passed as param 'id' $opt["detail_grid_id"] = "list2,list3"; $opt["subgridparams"] = "factory_id"; $opt["height"] = "90"; $opt['autowidth'] = true; $g->set_options($opt); $col = array(); $col['title'] = "id"; $col['name'] = "factory_id"; $col['hidden'] = true; // $col['dbname'] = "fac.factory_id"; $cols[] = $col; #factory Name $col = array(); $col['title'] = $this->lang->line('factory_name'); $col['name'] = "factory_name"; //$col['dbnaem'] = "fac.factory_name"; $col["search"] = false; $cols[] = $col; #factory Code $col = array(); $col['title'] = $this->lang->line('factory_code'); $col['name'] = "factory_code"; $col['dbnaem'] = "fac.factory_code"; $col["search"] = false; $cols[] = $col; #Recived Oil $col = array(); $col['title'] = $this->lang->line('sum_recived_oil'); $col['name'] = "receive_oil"; $col["search"] = false; $col["formatter"] = "number"; $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => 2); $cols[] = $col; #Sell Oil $col = array(); $col['title'] = $this->lang->line('sum_sell_oil'); $col['name'] = "sell_oil"; $col["search"] = false; $col["formatter"] = "number"; $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => 2); $cols[] = $col; #Total Amount $col = array(); $col['title'] = $this->lang->line("oil_total_amount"); $col['name'] = "total_amount"; $col["search"] = false; $col["formatter"] = "number"; $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => 2); $cols[] = $col; // Command 1 $g->select_command = "SELECT\n factory_id,\n factory_code,\n factory_name,\n SUM(receive_oil) AS receive_oil,\n SUM(sell_oil) AS sell_oil,\n oil_type,\n SUM(receive_oil - sell_oil) AS total_amount,\n\t\t factory_status\nFROM\n (\n SELECT\n t1.factory_id,\n t1.factory_code,\n t1.factory_name,\n\t\t\t\t\t\t\t t1.factory_status,\n t2.receive_oil,\n t2.sell_oil,\n t2.oil_type\n FROM\n transport_factory AS t1\n LEFT JOIN oilstock AS t2 ON t1.factory_id = t2.factory_id\n UNION\n SELECT\n t1.factory_id,\n t1.factory_code,\n t1.factory_name,\n\t\t\t\t\t\t\t\t\t\tt1.factory_status,\n t2.receive_oil,\n t2.sell_oil,\n t2.oil_type\n FROM\n transport_factory AS t1\n RIGHT JOIN oilstock AS t2 ON t1.factory_id = t2.factory_id\n ) oilstock\nGROUP BY\n factory_id\nHAVING factory_status =1"; $g->table = "transport_factory"; $g->set_columns($cols); $g->set_actions(array("add" => false, "edit" => false, "delete" => false, "rowactions" => false, "autofilter" => false)); // render grid and get html/js output $out_master = $g->render("list1"); /* if (!empty($_REQUEST["factory_id"])) { $_SESSION["factory_id"] = intval($_REQUEST["factory_id"]); } if (isset($_SESSION['factory_id'])) { $fac_id = $_SESSION["factory_id"]; } else { $fac_id = "0"; } */ $fac_id = intval($_GET["factory_id"]); #Detail Oil Recive **รับน้ำมัน $g2 = new jqgrid(); /*Option*/ $opt2["caption"] = $this->lang->line("recived_oil_list"); $opt2["sortname"] = 'stock_id'; $opt2["sortorder"] = "desc"; $opt2["height"] = "250"; $opt2['rowNum'] = 10; $opt2['rowList'] = array(10, 20, 30); $opt2["width"] = "979"; //$opt2["autowidth"] = true; //$opt3['hidegrid'] = true; $opt2["form"]["position"] = "center"; $opt2["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt2["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); /* $opt2["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('จำเป็นต้องเลือกโรงงาน'); return false; } }"; $opt2["add_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt2["edit_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt2["delete_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt2["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }'; */ // Check if master record is selected before detail addition $opt2["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('คุณยังไม่ได้เลือกโรงงาน'); return false; } }"; // reload master after detail update $opt2["onAfterSave"] = "function(){ jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); }"; $g2->set_options($opt2); #id $col2 = array(); $col2['title'] = "id"; $col2['name'] = 'stock_id'; $col2['hidden'] = true; $col2['editable'] = true; $cols2[] = $col2; #stick date $col2 = array(); $col2['title'] = $this->lang->line('date'); $col2['name'] = 'stock_date'; $col2['editable'] = true; $col2["editrules"] = array("required" => true); // and is required //$col2["editoptions"] = array("size" => 20, "defaultValue" => date("d-m-Y H:i")); // with default display of textbox with size 20 $col2["searchoptions"]["sopt"] = array("cn"); // contains search for easy searching # to make it date time $col2["formatter"] = "datetime"; # opts array can have these options: http://trentrichardson.com/examples/timepicker/#tp-options $col2["formatoptions"] = array("srcformat" => 'Y-m-d H:i:s', "newformat" => 'Y-m-d H:i', "opts" => array("timeFormat" => "HH:mm")); $col2["show"] = array("list" => true, "add" => true, "edit" => true, "view" => true); $cols2[] = $col2; #Ref No. $col2 = array(); $col2['title'] = $this->lang->line('reference_number'); $col2['name'] = 'ref_number'; $col2['editable'] = true; $cols2[] = $col2; $col2 = array(); $col2['title'] = $this->lang->line('customer_type'); $col2['name'] = "customer_type_id"; $col2['dbname'] = "oil.customer_type_id"; $col2["editable"] = true; $col2["edittype"] = "select"; $col2["editrules"] = array("required" => true); $str = $g2->get_dropdown_values("SELECT DISTINCT customer_type_id AS k,customer_type_title AS v FROM transport_customer_type WHERE customer_type_status = '2'"); $col2["editoptions"] = array("value" => ":;" . $str); $col2["editoptions"] = array("value" => $str, "onchange" => array("sql" => "SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers WHERE customer_type_id ='{customer_type_id}' ", "update_field" => "customer_id")); $col2["formatter"] = "select"; // display label, not value $col2["stype"] = "select"; // enable dropdown search $col2["searchoptions"] = array("value" => ":;" . $str); $cols2[] = $col2; # Customer Name $col2 = array(); $col2['title'] = $this->lang->line('customer_br'); $col2['name'] = "customer_id"; $col2['dbname'] = "oil.customer_id"; $col2["editable"] = true; $col2["edittype"] = "select"; $col2["editrules"] = array("required" => true); $str = $g2->get_dropdown_values("SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers "); $col2["editoptions"] = array("value" => ":;" . $str); $col2["editoptions"] = array("value" => $str, "onchange" => array("sql" => "SELECT DISTINCT car_id AS k,car_number AS v FROM `transport_oilcars` WHERE customer_id ='{customer_id}' AND `status`=1", "update_field" => "car_id")); $col2["editoptions"]["onload"]["sql"] = "SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers WHERE customer_type_id ='{customer_type_id}' AND `status` =1"; $col2["formatter"] = "select"; // display label, not value $col2["stype"] = "select"; // enable dropdown search $col2["searchoptions"] = array("value" => ":;" . $str); $cols2[] = $col2; #Car Number $col2 = array(); $col2['title'] = $this->lang->line('car_number'); $col2['name'] = "car_id"; $col2['dbname'] = "car.car_id"; $col2["editable"] = true; $col2["edittype"] = "select"; $col2["editrules"] = array("required" => true); $str = $g2->get_dropdown_values("SELECT DISTINCT car_id AS k, car_number AS v FROM transport_oilcars WHERE `status` =1"); $col2["editoptions"] = array("value" => ":;" . $str); // initially load 'note' of that $col2["editoptions"]["onload"]["sql"] = "SELECT DISTINCT car_id AS k, car_number AS v FROM transport_oilcars WHERE customer_id = '{customer_id}' AND `status` =1"; $col2["formatter"] = "select"; // display label, not value $col2["stype"] = "select"; // enable dropdown search $col2["searchoptions"] = array("value" => ":;" . $str); $cols2[] = $col2; #stock_details $col2 = array(); $col2['title'] = $this->lang->line('stock_details'); $col2['name'] = 'stock_details'; $col2['editable'] = true; $col2["edittype"] = "textarea"; $col2["editoptions"] = array("rows" => 2, "cols" => 20); $col2["formatter"] = "autocomplete"; // autocomplete $col2["formatoptions"] = array("sql" => "SELECT DISTINCT stock_details as k, stock_details as v FROM oilstock", "search_on" => "stock_details", "update_field" => "stock_details"); $cols2[] = $col2; #oil_value $col2 = array(); $col2['title'] = $this->lang->line('recived_oil'); $col2['name'] = 'receive_oil'; $col2['editable'] = true; $col2['search'] = false; $col2["editrules"] = array("required" => true, "number" => true); $col2["editoptions"] = array("onblur" => "update_reciveValue()", "defaultValue" => '0'); $col2['formatter'] = "number"; $col2["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $cols2[] = $col2; #oil_value $col2 = array(); $col2['title'] = $this->lang->line('list_per_price'); $col2['name'] = 'receive_price'; $col2['align'] = "right"; $col2['editable'] = true; #$col2['formatter'] = "number"; $col2['search'] = false; $col2["editrules"] = array("required" => true, "number" => true); $col2["editoptions"] = array("onblur" => "update_reciveValue()", "defaultValue" => '0'); $col2['formatter'] = "currency"; $col2["formatoptions"] = array("prefix" => "", "suffix" => '', "thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $cols2[] = $col2; #total_mount $col2 = array(); $col2['title'] = $this->lang->line('total_amount'); $col2['name'] = 'receive_amount'; $col2['align'] = "right"; $col2['editable'] = true; $col2["editrules"] = array("required" => true); # $col2['editoptions'] = array("readonly" => "readonly"); $col2['search'] = false; $col2['formatter'] = "currency"; $col2["formatoptions"] = array("prefix" => "", "suffix" => '', "thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => 2); $cols2[] = $col2; // $id = intval($_GET["rowid"]); /* $g2->select_command = "SELECT stock_id, stock_date, ref_number, oil.customer_type_id, cus_type.customer_type_title, oil.customer_id, cus.customers_name, oil.car_id, car.car_number, stock_details, receive_oil, receive_price, receive_amount FROM oilstock AS oil LEFT JOIN transport_cars AS car ON (oil.car_id = car.car_id) LEFT JOIN transport_customers AS cus ON ( oil.customer_id = cus.customer_id ) LEFT JOIN transport_customer_type AS cus_type ON ( cus.customer_type_id = cus_type.customer_type_id ) WHERE oil.factory_id = '$fac_id' AND oil_type = 1"; */ $g2->select_command = "SELECT\n\tstock_id,\n\tstock_date,\n\tref_number,\n\toil.customer_type_id,\n\tcus_type.customer_type_title,\n\toil.customer_id,\n\tcus.customer_name,\n\toil.car_id,\n\tcar.car_number,\t\n stock_details,\n\t\n\treceive_oil,\n\treceive_price,\n\treceive_amount\nFROM\n\toilstock AS oil\nLEFT JOIN transport_cars AS car ON (oil.car_id = car.car_id)\nLEFT JOIN transport_oilcustomers AS cus ON (\n\toil.customer_id = cus.customer_id\n)\nLEFT JOIN transport_customer_type AS cus_type ON (\n\tcus.customer_type_id = cus_type.customer_type_id\n)\nWHERE\n\toil.factory_id = '{$fac_id}'\nAND oil_type = 1"; #select table $g2->table = "oilstock"; #$g2->table = "oil_receive"; $e["on_insert"] = array("add_client", null, true); $g2->set_events($e); function add_client(&$data) { $id = intval($_GET["rowid"]); $data["params"]["factory_id"] = $id; $data["params"]["oil_type"] = 1; } $g2->set_actions(array("add" => true, "edit" => true, "delete" => true, "view" => false, "rowactions" => false, "autofilter" => true, "search" => "advance", "inlineadd" => false, "showhidecolumns" => false)); $g2->set_columns($cols2); #display Grid2 $out_list2 = $g2->render("list2"); /*Grid3 รายการจ่ายน้ำมัน*/ $g3 = new jqgrid(); /*Option*/ $opt3["caption"] = $this->lang->line('sell_oil_list'); $opt3["sortname"] = 'stock_id'; $opt3["sortorder"] = "desc"; $opt3["height"] = "250"; $opt3['rowNum'] = 10; $opt3['rowList'] = array(10, 20, 30); $opt3["width"] = "979"; //$opt3["autowidth"] = true; //$opt3['hidegrid'] = true; $opt3["form"]["position"] = "center"; $opt3["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt3["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); /* $opt3["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('จำเป็นต้องเลือกโรงงาน'); return false; } }"; $opt3["add_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt3["edit_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt3["delete_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); return true;}"; $opt3["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }'; */ // Check if master record is selected before detail addition $opt3["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('คุณยังไม่ได้เลือกโรงงาน'); return false; } }"; // reload master after detail update $opt3["onAfterSave"] = "function(){ jQuery('#list1').trigger('reloadGrid',[{jqgrid_page:1}]); }"; $g3->set_options($opt3); #id $col3 = array(); $col3['title'] = "id"; $col3['name'] = 'stock_id'; $col3['hidden'] = true; $col3['editable'] = true; $cols3[] = $col3; #stick date $col3 = array(); $col3['title'] = $this->lang->line('date'); $col3['name'] = 'stock_date'; $col3['editable'] = true; $col3["editrules"] = array("required" => true); // and is required //$col2["editoptions"] = array("size" => 20, "defaultValue" => date("d-m-Y H:i")); // with default display of textbox with size 20 $col3["searchoptions"]["sopt"] = array("cn"); // contains search for easy searching # to make it date time $col3["formatter"] = "datetime"; # opts array can have these options: http://trentrichardson.com/examples/timepicker/#tp-options $col3["formatoptions"] = array("srcformat" => 'Y-m-d H:i:s', "newformat" => 'Y-m-d H:i', "opts" => array("timeFormat" => "HH:mm")); $col3["show"] = array("list" => true, "add" => true, "edit" => true, "view" => true); $cols3[] = $col3; #Ref No. $col3 = array(); $col3['title'] = $this->lang->line('reference_number'); $col3['name'] = 'ref_number'; $col3['editable'] = true; $cols3[] = $col3; $col3 = array(); $col3['title'] = $this->lang->line('customer_type'); $col3['name'] = "customer_type_id"; $col3['dbname'] = "oil.customer_type_id"; $col3["editable"] = true; $col3["edittype"] = "select"; $col3["editrules"] = array("required" => true); $str = $g3->get_dropdown_values("SELECT DISTINCT customer_type_id AS k,customer_type_title AS v FROM transport_customer_type WHERE customer_type_status = '2'"); $col3["editoptions"] = array("value" => ":;" . $str); $col3["editoptions"] = array("value" => $str, "onchange" => array("sql" => "SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers WHERE customer_type_id ='{customer_type_id}' ", "update_field" => "customer_id")); $col3["formatter"] = "select"; // display label, not value $col3["stype"] = "select"; // enable dropdown search $col3["searchoptions"] = array("value" => ":;" . $str); $cols3[] = $col3; # Customer Name $col3 = array(); $col3['title'] = $this->lang->line('customer_br'); $col3['name'] = "customer_id"; $col3['dbname'] = "oil.customer_id"; $col3["editable"] = true; $col3["edittype"] = "select"; $col3["editrules"] = array("required" => true); $str = $g3->get_dropdown_values("SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers "); $col3["editoptions"] = array("value" => ":;" . $str); $col3["editoptions"] = array("value" => $str, "onchange" => array("sql" => "SELECT DISTINCT car_id AS k,car_number AS v FROM `transport_oilcars` WHERE customer_id ='{customer_id}' AND `status`=1", "update_field" => "car_id")); $col3["editoptions"]["onload"]["sql"] = "SELECT DISTINCT customer_id AS k , customer_name AS v FROM transport_oilcustomers WHERE customer_type_id ='{customer_type_id}' AND `status` =1"; $col3["formatter"] = "select"; // display label, not value $col3["stype"] = "select"; // enable dropdown search $col3["searchoptions"] = array("value" => ":;" . $str); $cols3[] = $col3; #Car Number $col3 = array(); $col3['title'] = $this->lang->line('car_number'); $col3['name'] = "car_id"; $col3['dbname'] = "car.car_id"; $col3["editable"] = true; $col3["edittype"] = "select"; $col3["editrules"] = array("required" => true); $str = $g3->get_dropdown_values("SELECT DISTINCT car_id AS k, car_number AS v FROM transport_oilcars WHERE `status` =1"); $col3["editoptions"] = array("value" => ":;" . $str); // initially load 'note' of that $col3["editoptions"]["onload"]["sql"] = "SELECT DISTINCT car_id AS k, car_number AS v FROM transport_oilcars WHERE customer_id = '{customer_id}' AND `status` =1"; $col3["formatter"] = "select"; // display label, not value $col3["stype"] = "select"; // enable dropdown search $col3["searchoptions"] = array("value" => ":;" . $str); $cols3[] = $col3; #stock_details $col3 = array(); $col3['title'] = $this->lang->line('stock_details'); $col3['name'] = 'stock_details'; $col3['editable'] = true; $col3["edittype"] = "textarea"; $col3["editoptions"] = array("rows" => 2, "cols" => 20); $col3["formatter"] = "autocomplete"; // autocomplete $col3["formatoptions"] = array("sql" => "SELECT DISTINCT stock_details as k, stock_details as v FROM oilstock", "search_on" => "stock_details", "update_field" => "stock_details"); $cols3[] = $col3; #oil_value $col3 = array(); $col3['title'] = $this->lang->line('sell_oil'); $col3['name'] = 'sell_oil'; $col3['search'] = false; $col3['editable'] = true; $col3["editrules"] = array("required" => true, "number" => true); $col3["editoptions"] = array("onblur" => "update_oilvalue()", "defaultValue" => '0'); $col3['formatter'] = "number"; $col3["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $cols3[] = $col3; #oil_price $col3 = array(); $col3['title'] = $this->lang->line('list_per_price'); $col3['name'] = 'sell_price'; $col3['align'] = "right"; $col3['editable'] = true; $col3['search'] = false; $col3["editrules"] = array("required" => true, "number" => true); $col3["editoptions"] = array("onblur" => "update_oilvalue()", "defaultValue" => '0'); $col3['formatter'] = "currency"; $col3["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $cols3[] = $col3; #total_mount $col3 = array(); $col3['title'] = $this->lang->line('total_amount'); $col3['name'] = 'sell_amount'; $col3['align'] = "right"; $col3['editable'] = true; $col3['search'] = false; $col3["editrules"] = array("required" => true); #$col3['editoptions'] = array("readonly" => "readonly"); $col3['formatter'] = "currency"; $col3["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $cols3[] = $col3; //$fac_id = intval($_REQUEST["rowid"]); /* $g3->select_command = "SELECT stock_id, stock_date, ref_number, oil.customer_type_id, cus_type.customer_type_title, oil.customer_id, cus.customers_name, oil.car_id, car.car_number, stock_details, sell_oil, sell_price, sell_amount FROM oilstock AS oil LEFT JOIN transport_cars AS car ON (oil.car_id = car.car_id) LEFT JOIN transport_customers AS cus ON ( oil.customer_id = cus.customer_id ) LEFT JOIN transport_customer_type AS cus_type ON ( cus.customer_type_id = cus_type.customer_type_id ) WHERE oil.factory_id = '$fac_id' AND oil_type = 2"; */ $g3->select_command = "SELECT\n\tstock_id,\n\tstock_date,\n\tref_number,\n\toil.customer_type_id,\n\tcus_type.customer_type_title,\n\toil.customer_id,\n\tcus.customer_name,\n\toil.car_id,\n\tcar.car_number,\n\tstock_details,\n\tsell_oil,\n\tsell_price,\n\tsell_amount\nFROM\n\toilstock AS oil\nLEFT JOIN transport_cars AS car ON (oil.car_id = car.car_id)\nLEFT JOIN transport_oilcustomers AS cus ON (\n\toil.customer_id = cus.customer_id\n)\nLEFT JOIN transport_customer_type AS cus_type ON (\n\tcus.customer_type_id = cus_type.customer_type_id\n)\nWHERE\n\toil.factory_id = '{$fac_id}'\nAND oil_type = 2"; $g3->table = "oilstock"; $e["on_insert"] = array("add_oiltype", null, true); $g3->set_events($e); function add_oiltype(&$data) { $id = intval($_GET["rowid"]); $data["params"]["factory_id"] = $id; $data["params"]["oil_type"] = 2; } $g3->set_columns($cols3); $g3->set_actions(array("add" => true, "edit" => true, "delete" => true, "view" => false, "rowactions" => true, "autofilter" => true, "search" => "advance", "inlineadd" => false, "showhidecolumns" => false)); #display Grid2 $out_list3 = $g3->render("list3"); //display $this->_example_output((object) array('output' => '', 'out_master' => $out_master, 'out_list2' => $out_list2, 'out_list3' => $out_list3)); //$this->load->view('oil/oil-view'); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
public function index() { if ($this->session->userdata('user_name')) { $g = new jqgrid(); $g->select_command = "SELECT id,fac.factory_code as factory_id ,dis.distance_code as distance_id,cubic.cubic_value as cubic_id,pr.price, start_date,end_date FROM pricelist as pr \nLEFT JOIN transport_factory as fac ON(pr.factory_id=fac.factory_id)\nLEFT JOIN transport_cubiccode as cubic ON(pr.cubic_id = cubic.cubic_id)\nLEFT JOIN distancecode as dis ON(pr.distance_id=dis.distance_id)"; $g->table = "pricelist"; $col = array(); $col["title"] = "id"; $col["name"] = "id"; //$col["dbname"] = "pr.pricelist_id"; $col["width"] = "10"; $col["hidden"] = true; $col["editable"] = false; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('factory_code'); // caption of column $col["name"] = "factory_id"; $col["dbname"] = "pr.factory_id"; $col["width"] = "10"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["editrules"] = array("required" => true); $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("SELECT DISTINCT factory_id AS k,factory_code AS v FROM transport_factory"); $col["editoptions"] = array("value" => ":;" . $str); // multi-select in search filter $col["stype"] = "select-multiple"; $col["searchoptions"]["value"] = $str; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('distance_code'); // caption of column $col["name"] = "distance_id"; $col["dbname"] = "pr.distance_id"; $col["width"] = "10"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["editrules"] = array("required" => true); $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("SELECT DISTINCT distance_id AS k,distance_code AS v FROM distancecode WHERE distance_status=1"); $col["editoptions"] = array("value" => ":;" . $str); // multi-select in search filter $col["stype"] = "select-multiple"; $col["searchoptions"]["value"] = $str; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('cubic_value'); // caption of column $col["name"] = "cubic_id"; $col["dbname"] = "pr.cubic_id"; $col["width"] = "10"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["editrules"] = array("required" => true); $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("SELECT DISTINCT cubic_id AS k,cubic_value AS v FROM transport_cubiccode WHERE cubic_status=1"); $col["editoptions"] = array("value" => ":;" . $str); // multi-select in search filter $col["stype"] = "select-multiple"; $col["searchoptions"]["value"] = $str; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('price'); // caption of column $col["name"] = "price"; $col["editable"] = true; $col["width"] = "10"; $col["editrules"] = array("required" => true); $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('start_date'); // caption of column $col["name"] = "start_date"; $col["formatter"] = "date"; // format as date $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd/m/Y'); // http://docs.jquery.com/UI/Datepicker/formatDate $col["width"] = "10"; $col["editrules"] = array("required" => true); $col["editable"] = true; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('end_date'); // caption of column $col["name"] = "end_date"; $col["formatter"] = "date"; // format as date $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd/m/Y'); // http://docs.jquery.com/UI/Datepicker/formatDate $col["width"] = "10"; $col["editrules"] = array("required" => true); $col["editable"] = true; $cols[] = $col; $g->set_columns($cols); $g->set_actions(array("add" => true, "edit" => true, "delete" => true, "clone" => true, "rowactions" => true, "search" => "advance", "showhidecolumns" => false)); // $grid["url"] = ""; // your paramterized URL -- defaults to REQUEST_URI $grid["rowNum"] = 50; // by default 20 $grid["sortname"] = 'id'; // by default sort grid by this field $grid["sortorder"] = "desc"; // ASC or DESC $grid["caption"] = $this->lang->line('pricelist'); // caption of grid $grid["autowidth"] = true; // expand grid to screen width $grid["multiselect"] = true; // allow you to multi-select through checkboxes $grid["form"]["position"] = "center"; $grid["altRows"] = true; $grid["altclass"] = "myAltRowClass"; $grid["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $grid["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $g->set_options($grid); $e["on_update"] = array("update_price", null, true); $e["on_insert"] = array("add_price", null, true); $g->set_events($e); function add_price(&$data) { $factory_id = $data["params"]["factory_id"]; $distance_id = $data["params"]["distance_id"]; $cubic_id = $data["params"]["cubic_id"]; $start_date = $data["params"]["start_date"]; $end_date = $data["params"]["end_date"]; $check_sql = "SELECT COUNT(*) AS c\nFROM\n\tpricelist\nWHERE\n\tfactory_id = '{$factory_id}'\nAND distance_id = '{$distance_id}'\nAND cubic_id = '{$cubic_id}'\nAND ( (start_date='0000-00-00' || start_date <= DATE_FORMAT('{$start_date}','%Y-%m-%d')) \nAND (end_date='0000-00-00' || end_date >= DATE_FORMAT('{$start_date}','%Y-%m-%d'))) \nAND ( (start_date='0000-00-00' || start_date <= DATE_FORMAT('{$end_date} ','%Y-%m-%d')) \nAND (end_date='0000-00-00' || end_date >= DATE_FORMAT('{$end_date}','%Y-%m-%d')))"; $rs = mysql_fetch_assoc(mysql_query($check_sql)); if ($rs["c"] > 0) { phpgrid_error("ข้อมูลราคาซ้ำ ไม่สามารถบันทึกได้"); } } // end of sub function function update_price(&$data) { //print_r($data); $obj =& get_instance(); $obj->load->model("price_model", "price"); $obj->load->library('conv_date'); $price_id = $data["params"]["id"]; //$startdate = date('Y-m-d',strtotime($data['params']['start_date'])); $startdate = $data['params']['start_date']; $enddate = $data['params']['start_date']; $c_stdate = $obj->price->check_before_update_price($price_id); //$c_endate = $obj->price->check_before_update_price($price_id,$en); $st_date = $c_stdate['start_date']; $en_date = $c_stdate['end_date']; //$ed_date = $c_endate; $m_date = $obj->conv_date->compareDate($startdate, $st_date); $e_date = $obj->conv_date->compareDate($enddate, $st_date); //$m_date ="E"; if ($m_date !== "E" && $e_date !== "E") { $check_sql = "SELECT COUNT(*) as c\n\nFROM\n\tpricelist\nWHERE\n\tfactory_id = '{$data["params"]["factory_id"]}'\nAND cubic_id = '{$data["params"]["cubic_id"]}'\nAND distance_id = '{$data["params"]["distance_id"]}'\nAND ( (start_date='0000-00-00' || start_date <= DATE_FORMAT('{$data["params"]["start_date"]}','%Y-%m-%d')) \nAND (end_date='0000-00-00' || end_date >= DATE_FORMAT('{$data["params"]["start_date"]}','%Y-%m-%d'))) \nOR\n( (start_date='0000-00-00' || start_date <= DATE_FORMAT('{$data["params"]["end_date"]}','%Y-%m-%d')) \nAND (end_date='0000-00-00' || end_date >= DATE_FORMAT('{$data["params"]["end_date"]}','%Y-%m-%d'))) LIMIT 1"; $rs = mysql_fetch_assoc(mysql_query($check_sql)); if ($rs["c"] > 0) { phpgrid_error("ข้อมูลราคาซ้ำ ไม่สามารถบันทึกได้"); } } } // End of function update_price //Display $pricetable = $g->render("list1"); $h2_title = "Price Setting"; $this->_example_output((object) array('output' => '', 'out' => $out, 'dispyPrice' => $dispyPrice, 'h2_title' => $h2_title, 'list_row' => $num_row, 'pricelist' => $pricelist, 'pricetable' => $pricetable)); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
public function index() { if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); $this->load->model('income_model', 'dropdrown'); $this->load->model('dropdown_model', 'cus_drop'); $g = new jqgrid(); $col = array(); $col["title"] = "Id"; // caption of column $col["name"] = "id"; $col["width"] = "10"; $col["fixed"] = true; $col["search"] = false; $col["editable"] = false; $col["export"] = false; $col["hidden"] = true; $cols[] = $col; $col = array(); $col['title'] = "cubic value"; $col['name'] = "cubic_id"; $col['dbname'] = "orders.cubic_id"; $col["editable"] = true; $col["formatter"] = "select"; $col["editable"] = true; $col["edittype"] = "select"; $str = $g->get_dropdown_values("SELECT DISTINCT cubic_id AS k, cubic_value AS v FROM transport_cubiccode WHERE cubic_status=1"); //$col["editoptions"] = array("value"=>":;".$str); $col["editoptions"] = array("value" => $str); $col["formatter"] = "select"; // display label, not value $cols[] = $col; /**Option Form */ $opt["sortname"] = 'id'; $opt["sortorder"] = "desc"; $opt["caption"] = $this->lang->line('Order_Transportation'); $opt['rowNum'] = 10; $opt['rowList'] = array(10, 20, 30); $opt['height'] = "300"; $opt["autowidth"] = true; $opt["multiselect"] = false; $opt["scroll"] = true; $opt["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#dp_number").focus(); }'; $opt["altRows"] = true; $opt["altclass"] = "myAltRowClass"; $g->set_options($opt); $g->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'add'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'edit'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'delete'), "rowactions" => true, "export" => true, "autofilter" => true, "search" => "advance")); $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 }
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(); $col["title"] = $this->lang->line('id'); // caption of column $col["name"] = "id"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col['hidden'] = true; $cols2[] = $col; #Expanse date $col = array(); $col["title"] = $this->lang->line('date'); $col["name"] = "expense_date"; $col["width"] = "20"; $col["editable"] = true; // this column is editable //$col["editoptions"] = array("size" => 20, "defaultValue" => date('d-m-Y')); // with default display of textbox with size 20 $col["editoptions"] = array("size" => 20); // with default display of textbox with size 20 $col["editrules"] = array("required" => true); // and is required # format as date $col["formatter"] = "date"; # opts array can have these options: http://api.jqueryui.com/datepicker/ $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd-m-Y', "opts" => array("changeYear" => false)); $cols2[] = $col; /* $col = array(); $col["title"] = $this->lang->line('reference_number'); // caption of column $col["name"] = "ref_number"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col["editable"] = true; $cols2[] = $col; */ # Descriptionns $col = array(); $col["title"] = $this->lang->line('desc'); // caption of column $col["name"] = "expense_details"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "100"; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '60'); $col["editrules"] = array("required" => true); #$col["formatter"] = "autocomplete"; // autocomplete /* $col["formatoptions"] = array( "sql" => "SELECT DISTINCT expense_details as k, expense_details as v FROM expense", "search_on" => "expense_details", "update_field" => "expense_details"); */ $col["editable"] = true; $cols2[] = $col; $col = array(); $col["title"] = $this->lang->line('amount'); $col["name"] = "total_amount"; $col["width"] = "30"; $col['align'] = "right"; $col['formatter'] = "currency"; $col["formatoptions"] = array("prefix" => "", "suffix" => '', "thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $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, "number" => true); // and is required $cols2[] = $col; #Not Remarl $col = array(); $col["title"] = $this->lang->line('note'); // caption of column $col["name"] = "note"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "40"; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '20'); $col["editable"] = true; /* $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array( "sql" => "SELECT DISTINCT note as k, note as v FROM expense", "search_on" => "note", "update_field" => "note"); */ $cols2[] = $col; $grid->set_columns($cols2); $e["js_on_load_complete"] = "grid2_onload"; $e["on_insert"] = array("add_client", null, true); $grid->set_events($e); function add_client(&$data) { $id = intval($_GET["rowid"]); $data["params"]["factory_id"] = $id; $data["params"]["car_id"] = 0; } // generate grid output, with unique grid name as 'list1' $out_detail = $grid->render("list2"); // Head Title $h2_title = $this->lang->line('expense'); $this->_example_output((object) array('output' => '', 'out_detail' => $out_detail, 'out_master' => $out_master, 'js_files' => array(), 'css_files' => array(), 'h2_title' => $h2_title)); } else { $this->_example_output(); } //end if } else { redirect('login', 'refresh'); } }
public function index() { if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); $this->load->model('income_model', 'dropdrown'); $this->load->model('dropdown_model', 'cus_drop'); date_default_timezone_set('Asia/Bangkok'); $g = new jqgrid(); $col = array(); $col["title"] = "Id"; // caption of column $col["name"] = "id"; $col["width"] = "10"; $col["fixed"] = true; $col["search"] = false; $col["editable"] = false; $col["export"] = false; $col["hidden"] = true; $cols[] = $col; #Date $col = array(); $col["title"] = $this->lang->line('date'); $col["name"] = "order_date"; $col["fixed"] = true; $col["width"] = "150"; $col["editable"] = true; // this column is editable $col["editoptions"] = array("size" => 20, "defaultValue" => date("d-m-Y H:i:s")); // with default display of textbox with size 20 $col["editrules"] = array("required" => true); // and is required $col["formatter"] = "datetime"; // format as date $col["formatoptions"] = array("srcformat" => 'Y-m-d H:i:s', "newformat" => 'd-m-Y H:i:s', "opts" => array("changeYear" => false, "timeFormat" => "hh:mm tt")); $col["search"] = true; $cols[] = $col; #DP Number $col = array(); $col["title"] = $this->lang->line('dp_number'); // caption of column $col["name"] = "dp_number"; $col["fixed"] = true; $col["width"] = "100"; $col["search"] = true; $col["editrules"] = array("required" => true); $col['editoptions'] = array("defaultValue" => "", "min" => "1", "max" => "10", "maxlength" => "10"); $col["editable"] = true; $cols[] = $col; #Customer $col = array(); $col["title"] = "customer_id"; $col["name"] = "customer_id"; $col["width"] = "10"; $col["editable"] = true; $col["export"] = false; $col["hidden"] = true; $cols[] = $col; #Customer name $col = array(); $col["title"] = $this->lang->line('customer'); $col["name"] = "cid"; $col["dbname"] = "cus.customers_name"; // this is required as we need to search in name field, not id $col["fixed"] = true; $col["width"] = "200"; $col["align"] = "left"; $col["editrules"] = array("required" => true); $col["search"] = true; $col["editable"] = true; $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array("sql" => "SELECT * FROM (SELECT customer_id as k, customers_name as v FROM transport_customers where customer_type_id=1 GROUP BY customers_name) o", "search_on" => "v", "update_field" => "customer_id"); $cols[] = $col; #factory $col = array(); $col["title"] = $this->lang->line('factory'); $col["name"] = "factory_id"; $col["dbname"] = "fac.factory_code"; // this is required as we need to search in name field, not id $col["fixed"] = true; $col["width"] = "100"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["editrules"] = array("required" => true); $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value //$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients"); $str = $this->dropdrown->get_factory_dropdown(); $col["editoptions"] = array("value" => $str); $col["editoptions"]["dataInit"] = "function(){ setTimeout(function(){ \$('select[name=factory_id]').select2({width:'80%', dropdownCssClass: 'ui-widget ui-jqdialog'}); },200); }"; $col["formatter"] = "select"; // display label, not value $cols[] = $col; #Real Distance $col = array(); $col['title'] = $this->lang->line('real_distance'); $col['name'] = "real_distance"; $col["fixed"] = true; $col['align'] = "center"; $col['width'] = "65"; $col["search"] = false; $col["editrules"] = array("required" => true, "number" => true); $col["editable"] = true; // this column is editable // $col["editoptions"] = array("defaultValue" => 1); $col["editoptions"] = array("defaultValue" => 1, "onchange" => array("sql" => "SELECT DISTINCT distance_id AS k,distance_code AS v FROM distancecode\nWHERE range_min <= '{real_distance}'\tAND range_max >='{real_distance}' ", "update_field" => "distance_id")); $cols[] = $col; #dis Distance_code $col = array(); $col['title'] = $this->lang->line('distance_code'); $col['name'] = "distance_id"; $col['dbname'] = "dis.distance_code"; $col["fixed"] = true; $col['width'] = "80"; $col["search"] = false; $col['align'] = "center"; $col["editable"] = true; $col["editrules"] = array("required" => true); $col["edittype"] = "select"; // render as select $str = $this->cus_drop->get_distancecode_dropdown(); $col["editoptions"] = array("value" => $str); #$col["formatter"] = "select"; // display label, not value // initially load 'note' of that client_id $col["editoptions"]["onload"]["sql"] = "SELECT DISTINCT distance_id AS k,distance_code AS v FROM distancecode\nWHERE range_min <= '{real_distance}'\tAND range_max >='{real_distance}' "; $col["formatter"] = "select"; // display label, not value $col["stype"] = "select"; // enable dropdown search $col["searchoptions"] = array("value" => ":;" . $str); $cols[] = $col; /*Cubic*/ $col = array(); $col['title'] = $this->lang->line('cubic_value'); $col["name"] = "cubic_id"; $col["dbname"] = "orders.cubic_id"; // this is required as we need to search in name field, not id $col['width'] = "80"; $col["fixed"] = true; $col["align"] = "center"; $col["search"] = false; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("select distinct cubic_id as k, cubic_value as v from transport_cubiccode where cubic_status=1"); //$str = $this->cus_drop->get_cubiccode_dropdown(); $col["editoptions"] = array("value" => ":;" . $str); $col["editoptions"]["dataInit"] = "function(){ setTimeout(function(){ \$('select[name=cubic_id]').select2({width:'80%', dropdownCssClass: 'ui-widget ui-jqdialog'}); },200); }"; $cols[] = $col; #price $col = array(); $col['title'] = $this->lang->line('price'); $col['name'] = "price"; $col['align'] = "right"; $col["fixed"] = true; $col['width'] = "80"; //$col["editrules"] = array("number" => true); $col['editoptions'] = array("defaultValue" => "0", "readonly" => true); #$col['hidden'] = true; $col["show"] = array("list" => true, "add" => false, "edit" => false, "view" => true); $col['editable'] = true; $col['search'] = false; $cols[] = $col; /*End Cubic*/ #car $col = array(); $col["title"] = $this->lang->line('car_number'); $col['name'] = "car_id"; $col['dbname'] = "car.car_number"; $col["fixed"] = true; $col['align'] = "center"; $col['width'] = "85"; $col['search'] = true; $col['editable'] = true; $col["editrules"] = array("required" => true); $col['edittype'] = "select"; $str = $this->cus_drop->get_car_dropdown(); $col["editoptions"] = array("value" => $str, "onchange" => array("sql" => "SELECT DISTINCT driver_id as k, driver_name as v FROM driver WHERE car_id = '{car_id}'", "update_field" => "driver_id")); $col['formatter'] = "select"; //$col["stype"] = "select"; // enable dropdown search //$col["searchoptions"] = array("car_id" => ":;".$str); $cols[] = $col; #Driver $col = array(); $col["title"] = $this->lang->line('driver'); $col['name'] = "driver_id"; $col['dbname'] = "dri.driver_name"; $col["fixed"] = true; $col['width'] = "150"; $col['search'] = true; $col['editable'] = true; $col['edittype'] = "select"; $col["editrules"] = array("required" => true); $str = $this->cus_drop->get_drivers_dropdown(); $col['editoptions'] = array("value" => $str); $col['formatter'] = "select"; $col["searchoptions"] = array("car_id" => ":;" . $str); $cols[] = $col; #OilUse $col = array(); $col['title'] = $this->lang->line('use_oil'); $col['name'] = "use_oil"; $col['dbname'] = "orders.use_oil"; $col["fixed"] = true; $col['align'] = "center"; $col['width'] = "70"; $col["editoptions"] = array("number" => true, "defaultValue" => '0'); $col['editrules'] = array("number" => true); $col['formater'] = "number"; $col['editable'] = true; $cols[] = $col; #Remark $col = array(); $col['title'] = $this->lang->line('remark'); $col['name'] = "remark"; $col['dbname'] = "orders.remark"; $col["fixed"] = true; $col['width'] = "120"; $col["edittype"] = "textarea"; $col["editoptions"] = array("rows" => 2, "cols" => 20); $col['editable'] = true; $cols[] = $col; /*Grid Option*/ $opt["sortname"] = 'id'; $opt["sortorder"] = "desc"; $opt["caption"] = $this->lang->line('Order_Transportation'); $opt['rowNum'] = 10; $opt['rowList'] = array(10, 20, 30); $opt['height'] = "360"; $opt["autowidth"] = true; $opt["multiselect"] = true; $opt["scroll"] = true; $opt["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#dp_number").focus();}'; $opt["edit_options"]["afterShowForm"] = 'function(formid) { jQuery("#dp_number").focus();}'; $opt["altRows"] = true; $opt["altclass"] = "myAltRowClass"; $opt["form"]["position"] = "center"; // or "all" $g->set_options($opt); $g->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'add'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'edit'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'truckorder', 'delete'), "rowactions" => false, "export" => true, "autofilter" => true, "search" => "advance")); $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,\t\n cubic.cubic_value as cubic_id,\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_insert"] = array("add_order", null, true); $e["on_after_insert"] = array("after_insert", null, true); $e["on_update"] = array("do_update", 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); $h2_title = $this->lang->line('order_car_truck'); //display $this->_example_output((object) array('output' => "", 'out' => $out, 'h2_title' => $h2_title, 'iprice' => $iprice, 'js_files' => array(), 'css_files' => array())); //$this->_example_output(); } else { redirect('login', 'refresh'); } //end if }
mysql_select_db(PHPGRID_DBNAME); // include and create object include PHPGRID_LIBPATH . "inc/jqgrid_dist.php"; $g = new jqgrid(); // set database table for CRUD operations $g->table = "t_clients"; // set few params $grid["caption"] = "Clients vente directe"; $grid["export"] = array("format" => "pdf", "filename" => "my-file", "heading" => "Clients vente directe ", "orientation" => "landscape", "paper" => "a4"); $grid["export"]["render_type"] = "html"; // export filtered data or all data $grid["export"]["range"] = "all"; // or "all" // params are array(<function-name>,<class-object> or <null-if-global-func>) $e["on_render_pdf"] = array("set_pdf_format", null); $g->set_events($e); function set_pdf_format($param) { $grid = $param["grid"]; $arr = $param["data"]; //$grid->SetFont('helvetica', '', 11); $html .= "<h1>" . $grid->options["export"]["heading"] . "</h1>"; $html .= '<table border="1" cellpadding="2" cellspacing="1">'; $i = 0; foreach ($arr as $v) { $shade = $i++ % 2 ? 'bgcolor="#efefef"' : ''; $html .= "<tr>"; foreach ($v as $d) { // bold header if ($i == 1) { $html .= "<td bgcolor=\"lightgrey\"><strong>{$d}</strong></td>";
public function index() { if ($this->session->userdata('user_name')) { $this->load->model('factory_model', 'factory'); $factory = $this->factory->getFactory(); if (isset($_REQUEST['submit'])) { //$startDate = '2014-10-01'; // $endDate = '2014-10-31'; $factory_id = $this->input->get_post('factory'); $startDate = $this->input->post('startDate'); $endDate = $this->input->post('endDate'); $factory_name = $this->factory->getNamefactory($factory_id); /*0000-00-00*/ $start_date = $this->conv_date->thai2engDate($startDate); $end_date = $this->conv_date->thai2engDate($endDate); $dispyPrice = $this->price->dispalyPrice($factory_id, $start_date, $end_date); $dispyPrice2 = $this->price->dispalyPrice($factory_id, $start_date, $end_date); $dataFilter = array('pricelist_factory' => $factory_id, 'select_startDate' => $startDate, 'select_endDate' => $endDate, 'selected_start_date' => $start_date, 'selected_end_date' => $end_date); $this->session->set_userdata($dataFilter); $_SESSION['factory'] = $factory_id; $_SESSION['startDate'] = $start_date; $_SESSION['endDate'] = $end_date; } // End if if ($this->session->userdata('pricelist_factory')) { $selected_factory_id = $this->session->userdata('pricelist_factory'); $selected_startDate = $this->session->userdata('select_startDate'); $selected_endDate = $this->session->userdata('select_endDate'); } $head_title = "ตารางราคาเดินรถ {$factory_name} ระหว่าง {$startDate} ถึง {$endDate}"; //Display Pricelist Detail $g = new jqgrid(); //$dispyPrice = $this->price->dispalyPrice($factory_id,$startDate,$endDate); $titke_cubic = $this->price->header_cubic(); $row_distance = $this->price->row_distance(); $title_distance = $this->price->row_distanceName(); //$i = 0; foreach ($row_distance as $key => $val) { $i = $val['distance_id']; foreach ($titke_cubic as $row) { $data[$i]["id"] = $i; $data[$i]["distacne"] = $title_distance[$i]; //$dispyPrice[$i]['distance']; $data[$i]["cubic[{$row->cubic_id}]"] = $dispyPrice[$i][$row->cubic_id]; $data[$i]["factory_id"] = $selected_factory_id; $data[$i]['start_date'] = $selected_startDate; $data[$i]['end_date'] = $selected_endDate; } // $i = $val['distance_id']; } $g->table = $data; //$g->table = "pricelist"; // ห$g->table = $pricelist; $col = array(); $col["title"] = "id"; // caption of column $col["name"] = "id"; $col["width"] = "60"; $col["sorttype"] = int; $col['editable'] = true; $col['hidden'] = true; $cols[] = $col; $col = array(); $col["title"] = $this->lang->line('cubic_and_distance'); // caption of column $col["name"] = "distacne"; $col["width"] = "350"; $col['editable'] = false; $cols[] = $col; $col = array(); $col["title"] = "factory_id"; // caption of column $col["name"] = "factory_id"; $col["width"] = "350"; $col['editable'] = true; $col["editrules"] = array("required" => true); $col['hidden'] = true; $cols[] = $col; $col = array(); $col["title"] = "start_date"; // caption of column $col["name"] = "start_date"; $col["width"] = "350"; $col['editable'] = true; $col["editrules"] = array("required" => true); $col['hidden'] = true; $cols[] = $col; $col = array(); $col["title"] = "end_date"; // caption of column $col["name"] = "end_date"; $col['editable'] = true; $col["editrules"] = array("required" => true); $col['hidden'] = true; $col["width"] = "350"; $cols[] = $col; foreach ($titke_cubic as $row) { $col = array(); $col['title'] = "{$row->cubic_value}"; $col['name'] = "cubic[{$row->cubic_id}]"; $col['editable'] = true; $col["editrules"] = array("number" => true); $col["formatter"] = "number"; $col["formatoptions"] = array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $col['search'] = false; $cols[] = $col; } $g->set_columns($cols); $g->set_actions(array("add" => false, "edit" => true, "bulkedit" => false, "delete" => true, "rowactions" => false, "autofilter" => true, "search" => "simple")); $opt['caption'] = "{$head_title}"; $opt["sortname"] = 'id'; // by default sort grid by this field $opt["sortorder"] = "asc"; // ASC or DESC $opt['rowNum'] = 30; $opt["autowidth"] = true; $opt["cellEdit"] = true; // excel visual params $opt["cellEdit"] = true; // inline cell editing, like spreadsheet $opt["rownumbers"] = true; $opt["rownumWidth"] = 30; $g->set_options($opt); //$e["on_insert"] = array("add_client", null, true); $e["on_update"] = array("update_prices", null, true); //$e["on_delete"] = array("delete_client", null, true); //$e["on_after_insert"] = array("after_insert", null, true); // return last inserted id for further working //$e["on_data_display"] = array("filter_display", null, true); $g->set_events($e); function update_prices(&$data) { global $_SESSION; /* These comments are just to show the input param format $data => Array ( [client_id] => 2 [params] => Array ( [client_id] => 2 [name] => Client 2 [gender] => male [company] => Client 2 Company ) ) */ /* Array ( [id] => 1 [params] => Array ( [cubic] => Array ( [3] => 50 ) [id] => 1 ) ) */ // ob_start(); $str = ob_get_clean(); $obj =& get_instance(); $obj->load->model("price_model", "price"); $data['params']['factory_id'] = $_SESSION['factory']; $data['params']['start_date'] = $_SESSION['startDate']; $data['params']['end_date'] = $_SESSION['endDate']; $price_id = $data['params']['id']; $price_data = $data['params']['cubic']; foreach ($price_data as $k => $v) { $data['params']['cubic_id'] = $k; $data['params']['price'] = $v; } $factory_id = $data['params']['factory_id']; $distance_id = $data['params']['id']; $cubic_id = $data['params']['cubic_id']; $price = $data['params']['price']; $start_date = $data['params']['start_date']; $end_date = $data['params']['end_date']; //$m = $obj->price->recursive($mm,$factory_id,$start_date,$end_date); $mm = $obj->price->check_price_id($factory_id, $distance_id, $cubic_id, $start_date, $end_date); if ($mm == 0) { $str = ob_get_clean(); $str = "INSERT INTO `pricelist` (\n\t`factory_id`,\n\t`cubic_id`,\n\t`distance_id`,\n\t`price`,\n\t`start_date`,\n\t`end_date`\n)\nVALUES\n\t(\n\t\t'{$factory_id}',\n\t\t'{$cubic_id}',\n\t\t'{$distance_id}',\n\t\t'{$price}',\n\t\t'{$start_date}',\n\t\t'{$end_date}'\n\t)"; } else { $str = ob_get_clean(); $str = "UPDATE `pricelist` SET `price`='{$price}' WHERE (`id`='{$mm}')"; } //$str = $obj->price->recursive_price($factory_id,$distance_id,$cubic_id,$price,$start_date,$end_date); mysql_query($str); print_r($data); } // End of function //Display $pricetable = $g->render("list1"); $pricelist2 = $this->price->displayPrice2(); $this->_example_output((object) array('output' => '', 'out' => $out, 'dispyPrice' => $dispyPrice, 'dispyPrice2' => $dispyPrice2, 'list_row' => $num_row, 'factory' => $factory, 'start_date' => $start_date, 'end_date' => $end_date, 'pricelist2' => $pricelist2, 'pricetable' => $pricetable)); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
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')) { $grid = new jqgrid(); $opt["caption"] = $this->lang->line('factory'); $opt["detail_grid_id"] = "list2"; $opt["autowidth"] = true; // extra params passed to detail grid, column name comma separated //$opt["subgridparams"] = "factory_id,factory_code,factory_name"; $opt["subgridparams"] = "factory_id,factory_code,factory_name"; $opt["export"] = array("filename" => "my-file", "sheetname" => "test", "format" => "pdf"); $opt["export"]["range"] = "filtered"; $grid->set_options($opt); $grid->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["width"] = "35"; $col["editable"] = false; $cols[] = $col; $col = array(); $col['title'] = $this->lang->line('factory_name'); $col['name'] = "factory_name"; $col["editable"] = false; $cols[] = $col; $grid->set_columns($cols); $grid->set_actions(array("add" => false, "edit" => false, "delete" => false, "rowactions" => false, "autofilter" => true, "search" => "advance")); $out_master = $grid->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"] = 400; // autofit height of subgrid $opt["rowNum"] = 10; // by default 20 $opt['rowList'] = array(10, 20, 30, 100); $opt["autowidth"] = true; $opt["caption"] = $this->lang->line('expense_list'); $opt["multiselect"] = true; $opt['height'] = '350'; //footer $opt["footerrow"] = true; $opt["reloadedit"] = true; $opt["export"] = array("filename" => "my-file", "sheetname" => "test", "format" => "pdf"); // export to excel parameters $opt["export"]["range"] = "filtered"; // 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"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '320'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '320'); $opt["add_options"]["topinfo"] = "บันทึกรายการซ่อม"; //$opt["add_options"]["bottominfo"] = "This text is dialog footer text"; $opt["form"]["position"] = "left"; $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }'; $grid->set_options($opt); $grid->set_actions(array("add" => true, "edit" => true, "delete" => true, "rowactions" => true, "export" => false, "autofilter" => true, "search" => "advance")); // $id = intval($_GET["rowid"]); //$factory_code = $_GET["factory_code"]; //$company = $_GET["factory_name"]; //$cid = intval($_GET["factory_id"]); 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"]); $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"; $col = array(); $col["title"] = $this->lang->line('id'); // caption of column $col["name"] = "id"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col['hidden'] = true; $cols2[] = $col; $col = array(); $col["title"] = $this->lang->line('date'); $col["name"] = "expense_date"; $col["width"] = "20"; $col["resizable"] = false; $col["editable"] = true; // this column is editable $col["editoptions"] = array("size" => 20, "defaultValue" => date('Y-m-d')); // with default display of textbox with size 20 $col["editrules"] = array("required" => true); // and is required # format as date $col["formatter"] = "date"; # opts array can have these options: http://api.jqueryui.com/datepicker/ $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd-m-Y', "opts" => array("changeYear" => false)); $cols2[] = $col; //car $col = array(); $col["title"] = $this->lang->line('car_number'); $col["name"] = "car_id"; $col["dbname"] = "transport_cars.car_number"; // this is required as we need to search in name field, not id $col["width"] = "20"; $col["align"] = "center"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value //$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients"); $str = $this->getdrop->grid_dropdown("SELECT distinct car_id AS k,car_number AS v FROM `transport_cars`"); $col["editoptions"] = array("value" => $str); $col["formatter"] = "select"; // display label, not value $cols2[] = $col; #ref_number /* $col = array(); $col["title"] = $this->lang->line('reference_number'); // caption of column $col["name"] = "ref_number"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col["editable"] = true; $cols2[] = $col; */ # Detail $col = array(); $col["title"] = $this->lang->line('desc'); // caption of column $col["name"] = "expense_details"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "70"; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '50'); $col["editrules"] = array("required" => true); $col["editable"] = true; $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array("sql" => "SELECT DISTINCT expense_details as k, expense_details as v FROM expense", "search_on" => "expense_details", "update_field" => "expense_details"); $cols2[] = $col; # Total Amount $col = array(); $col["title"] = $this->lang->line('amount'); $col["name"] = "total_amount"; $col["width"] = "20"; $col['align'] = "right"; $col['formatter'] = "currency"; $col["formatoptions"] = array("prefix" => "", "suffix" => '', "thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $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, "number" => true); // and is required $cols2[] = $col; #Note Remark $col = array(); $col["title"] = $this->lang->line('note'); // caption of column $col["name"] = "note"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '20'); $col["editable"] = true; $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array("sql" => "SELECT DISTINCT note as k, note as v FROM expense", "search_on" => "note", "update_field" => "note"); $cols2[] = $col; # Render set columns $grid->set_columns($cols2); $e["js_on_load_complete"] = "grid2_onload"; $e["on_insert"] = array("add_client", null, true); $grid->set_events($e); function add_client(&$data) { $id = intval($_GET["rowid"]); $data["params"]["factory_id"] = $id; } // generate grid output, with unique grid name as 'list1' $out_detail = $grid->render("list2"); // Head Title $h2_title = $this->lang->line('car_service'); $this->_example_output((object) array('output' => '', 'out_detail' => $out_detail, 'out_master' => $out_master, 'js_files' => array(), 'css_files' => array(), 'h2_title' => $h2_title)); } else { $this->_example_output(); } //end if } else { redirect('login', 'refresh'); } }
public function index() { //echo $this->session->userdata('user_id'); if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); if ($this->cizacl->check_isAllowed($i_rule, 'income', 'index')) { // master grid $grid = new jqgrid(); $opt["caption"] = $this->lang->line('factory'); // following params will enable subgrid -- by default first column (PK) of parent is passed as param 'id' $opt["detail_grid_id"] = "list2"; //$opt['width'] = 300; $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"; #set Grid Option $grid->set_options($opt); $grid->select_command = "SELECT * FROM transport_factory WHERE factory_status =1"; #Select table $grid->table = "transport_factory"; /*Define Column*/ #ID $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["editable"] = false; $col['hidden'] = true; $cols[] = $col; #Factory code $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; $grid->set_columns($cols); $grid->set_actions(array("add" => false, "edit" => false, "delete" => false, "rowactions" => false, "autofilter" => false)); //Display master $out_master = $grid->render("list1"); // detail grid $grid = new jqgrid(); $opt = array(); $opt["sortname"] = 'income_date'; // 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["caption"] = $this->lang->line('income_invoice'); // caption of grid //$opt["multiselect"] = true; // 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"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["add_options"]["topinfo"] = $this->lang->line('income_title_from_add'); $opt["add_options"]["bottominfo"] = $this->lang->line('remark_numberic'); $opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#ref_number").focus(); }'; $opt["form"]["position"] = "left"; $opt["form"]["nav"] = true; $opt["multiselect"] = false; $opt["rownumbers"] = true; // 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; // enable inline editing buttons $grid->set_actions(array("inline" => true, "rowactions" => true)); // Properties Grids $grid->set_options($opt); $grid->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'income', 'add'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'income', 'edit'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'income', 'delete'), "rowactions" => true, "export" => false, "autofilter" => true, "search" => "advance")); $id = intval($_GET["rowid"]); $factory_code = $_GET["factory_code"]; $company = $_GET["factory_name"]; $cid = intval($_GET["factory_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,income_date,factory_id,ref_number,income_details,total_amount,note,(SELECT SUM(total_amount)FROM\tincome WHERE factory_id = {$cid}\t) AS table_total FROM\t`income`WHERE\tfactory_id = {$cid}"; // this db table will be used for add,edit,delete //Select database $grid->table = "income"; /*Define column*/ #id $col = array(); $col["title"] = $this->lang->line('id'); // caption of column $col["name"] = "id"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col['hidden'] = true; $cols2[] = $col; #DATE $col = array(); $col["title"] = $this->lang->line('date'); $col["name"] = "income_date"; $col["width"] = "50"; $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 # format as date $col["formatter"] = "date"; # opts array can have these options: http://api.jqueryui.com/datepicker/ $col["formatoptions"] = array("srcformat" => 'Y-m-d', "newformat" => 'd-m-Y', "opts" => array("changeYear" => false)); $cols2[] = $col; # Reference Number $col = array(); $col["title"] = $this->lang->line('reference_number'); // caption of column $col["name"] = "ref_number"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "30"; $col["editable"] = true; $col['hidden'] = true; $cols2[] = $col; # Detail $col = array(); $col["title"] = $this->lang->line('desc'); // caption of column $col["name"] = "income_details"; // field name, must be exactly same as with SQL prefix or db field $col["editrules"] = array("required" => true); // and is required $col["width"] = "60"; $col["editable"] = true; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '50', "defaultValue" => ' '); $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array("sql" => "SELECT DISTINCT income_details as k, income_details as v FROM income", "search_on" => "income_details", "update_field" => "income_details"); $cols2[] = $col; #total Amount $col = array(); $col["title"] = $this->lang->line('amount'); $col["name"] = "total_amount"; $col["width"] = "40"; $col['align'] = "right"; $col['formatter'] = "currency"; $col["formatoptions"] = array("prefix" => "", "suffix" => '', "thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2'); $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, "number" => true); // and is required $cols2[] = $col; # Remark $col = array(); $col["title"] = $this->lang->line('note'); // caption of column $col["name"] = "note"; // field name, must be exactly same as with SQL prefix or db field $col["width"] = "40"; $col["editable"] = true; $col['edittype'] = "textarea"; $col['editoptions'] = array("rows" => '2', "cols" => '20', "defaultValue" => ' '); $col["formatter"] = "autocomplete"; // autocomplete $col["formatoptions"] = array("sql" => "SELECT DISTINCT note as k, note as v FROM income", "search_on" => "note", "update_field" => "note"); $cols2[] = $col; // virtual column for grand total $col = array(); $col["title"] = "table_total"; $col["name"] = "table_total"; $col["width"] = "100"; $col["hidden"] = true; $cols2[] = $col; // virtual column for running total $col = array(); $col["title"] = "running_total"; $col["name"] = "running_total"; $col["width"] = "100"; $col["hidden"] = true; $cols[] = $col; $grid->set_columns($cols2); $e["js_on_load_complete"] = "grid2_onload"; $e["on_insert"] = array("add_client", null, true); $grid->set_events($e); function add_client(&$data) { $id = intval($_GET["rowid"]); $data["params"]["factory_id"] = $id; } // generate grid output, with unique grid name as 'list1' $out_detail = $grid->render("list2"); // Head Title $h2_title = $this->lang->line('income'); $js_script = $this->income_model->income_fect_assoc($data); // Used JS Script //$id2 = intval($_GET["jqgrid_page"]); $id3 = intval($_GET['jqgrid_page']); //$data["params"]["factory_id"] = $id2; $js_script = $id3; //display $this->_example_output((object) array('output' => '', 'out_detail' => $out_detail, 'out_master' => $out_master, 'js_files' => array(), 'css_files' => array(), 'js_script' => $js_script, 'h2_title' => $h2_title)); } else { $this->_example_output(); } //end if } else { redirect('login', 'refresh'); } }
public function index() { //check login if ($this->session->userdata('user_name')) { $i_rule = $this->session->userdata('user_cizacl_role_id'); $g = new jqgrid(); #ID $col = array(); $col['title'] = "ID"; $col['name'] = "customer_id"; $col['hidden'] = true; $col['isnull'] = true; $cols[] = $col; #Factory $col = array(); $col["title"] = $this->lang->line("factory_name"); $col["name"] = "factory_id"; $col["dbname"] = "fac.factory_id"; // this is required as we need to search in name field, not id $col["width"] = "100"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value $str = $g->get_dropdown_values("select distinct factory_id as k, factory_name as v from transport_factory where factory_status =1"); $col["editoptions"] = array("value" => ":;" . $str); $col["formatter"] = "select"; // display label, not value $col["editrules"] = array("required" => true); $col["editoptions"] = array("value" => $str); $col["formatter"] = "select"; // display label, not value $col["stype"] = "select"; // enable dropdown search $col["searchoptions"] = array("value" => ":;" . $str); $cols[] = $col; #Customer_Type $col = array(); $col["title"] = $this->lang->line('customer_type'); $col["name"] = "customer_type_id"; $col["dbname"] = "cus_type.customer_type_title"; // this is required as we need to search in name field, not id $col["fixed"] = true; //$col["width"] = "65"; $col["align"] = "left"; $col["search"] = true; $col["editable"] = true; $col["edittype"] = "select"; // render as select # fetch data from database, with alias k for key, v for value //$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients"); $str = $g->get_dropdown_values("SELECT DISTINCT customer_type_id AS k, customer_type_title AS v FROM `transport_customer_type` WHERE customer_type_status ='2'"); #$str = $this->dropdrown->get_customer_type(); $col["editoptions"] = array("value" => $str); $col["formatter"] = "select"; // display label, not value $cols[] = $col; #Customer name $col = array(); $col['title'] = $this->lang->line('customer_agancy'); $col['name'] = "customer_name"; $col["editrules"] = array("required" => true); $col["search"] = true; $col['editable'] = true; $cols[] = $col; #remark $col = array(); $col['title'] = $this->lang->line('note'); $col['name'] = "remark"; $col['editable'] = true; $col['edittype'] = "textarea"; $col["viewable"] = false; $col['editoptions'] = array("rows" => "2", "cols" => "20"); $cols[] = $col; // $cols[] = $col; $e["on_insert"] = array("add_client", null, true); $e["on_delete"] = array("delete_client", null, true); #$e["on_delete"] = array("del_client", null, true); $g->set_events($e); $g->select_command = "SELECT\n\tcustomer_id,\n\tcustomer_name,\n\to_c.factory_id,\n\to_c.customer_type_id,\n\tcustomer_type_title,\n o_c.remark as remark\nFROM\n\ttransport_oilcustomers AS o_c\nLEFT JOIN transport_factory AS fac ON (\n\to_c.factory_id = fac.factory_id\n)\nLEFT JOIN transport_customer_type AS cus_type ON (\n\to_c.customer_type_id = cus_type.customer_type_id\n)\nWHERE\n\to_c.`status` = 1"; function add_client(&$data) { $check_sql = "SELECT count(*) as c from transport_oilcustomers where factory_id =" . $data["params"]["factory_id"] . " AND LOWER(`customer_name`) = '" . strtolower($data["params"]["customer_name"]) . "'"; $rs = mysql_fetch_assoc(mysql_query($check_sql)); if ($rs["c"] > 0) { phpgrid_error("ข้อมูลลูกค้าซ้ำ"); } } // end of sub function function delete_client(&$data) { ob_start(); print_r($data); $str = ob_get_clean(); $str = "UPDATE `transport_oilcustomers` SET `status`='0' WHERE (`customer_id`='{$data["id"]}')"; mysql_query($str); } //Use Table $g->table = "transport_oilcustomers"; // pass the cooked columns to grid $g->set_columns($cols); $opt["sortname"] = 'customer_name'; $opt["sortorder"] = "asc"; $opt["detail_grid_id"] = "list2"; $opt["multiselect"] = false; $opt["subgridparams"] = "customer_id"; $opt["rowList"] = array(10, 20, 30); $opt["caption"] = $this->lang->line('customer'); $opt["autowidth"] = true; $opt["multiselect"] = false; $opt["autowidth"] = true; $opt["view_options"]['width'] = '520'; $opt["add_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit" => true, 'width' => '420'); $opt["edit_options"]["beforeSubmit"] = "function(post,form){ return validate_form_once(post,form); }"; $g->set_options($opt); $g->set_actions(array("add" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'add_customer'), "edit" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'edit_customer'), "delete" => $this->cizacl->check_isAllowed($i_rule, 'customer', 'del_customer'), "export" => false, "autofilter" => true, "search" => "advance")); // set database table for CRUD operations // render grid and get html/js output $out_index = $g->render("list1"); // detail grid $grid = new jqgrid(); $id = intval($_GET["rowid"]); $opt2 = array(); $opt2["sortname"] = 'car_id'; // by default sort grid by this field $opt2["sortorder"] = "desc"; // ASC or DESC $opt2["height"] = ""; // autofit height of subgrid $opt2["width"] = "640"; $opt2["caption"] = $this->lang->line('car_details'); // caption of grid $opt2["multiselect"] = true; // allow you to multi-select through checkboxes $opt2["rowList"] = array(10, 20, 30); // Check if master record is selected before detail addition $opt2["add_options"]["beforeInitData"] = "function(formid){ var selr = jQuery('#list1').jqGrid('getGridParam','selrow'); if (!selr) { alert('กรุณาเลือกหน่วยงานของรถ'); return false; } }"; $grid->set_options($opt2); $grid->set_actions(array("add" => true, "edit" => true, "delete" => true, "rowactions" => true, "export" => false, "autofilter" => true, "search" => "advance")); #ID $col2 = array(); $col2['title'] = "ID"; $col2['name'] = "car_id"; $col2['hidden'] = true; $col2['isnull'] = true; $cols2[] = $col2; $col2 = array(); $col2['title'] = $this->lang->line('car_number'); $col2['name'] = "car_number"; $col2['editable'] = true; $col2["formatter"] = "autocomplete"; // autocomplete $col2["formatoptions"] = array("sql" => "SELECT car_number AS k,car_number AS v FROM `transport_cars` WHERE `status`=1", "search_on" => "car_number", "update_field" => "car_number"); $cols2[] = $col2; $col2 = array(); $col2['title'] = $this->lang->line('car_license'); $col2['name'] = "car_license"; $col2['editable'] = true; $cols2[] = $col2; $col2 = array(); $col2['name'] = "customer_id"; $col2['title'] = "customer_id"; $col2['editable'] = true; $col2['hidden'] = true; $cols2[] = $col2; $grid->select_command = "SELECT car_id,car_number,car_license,customer_id FROM transport_oilcars WHERE customer_id ={$id}"; $grid->table = "transport_oilcars"; $grid->set_columns($cols2); $e["on_insert"] = array("oilcars", null, true); $e["on_after_insert"] = array("after_insert", null, true); // return last inserted id for further working $grid->set_events($e); function oilcars(&$data) { $id = intval($_GET["rowid"]); $data["params"]["customer_id"] = $id; $check_sql = "SELECT count(*) as c from transport_oilcars WHERE customer_id = {$id} and LOWER(`car_number`) = '" . strtolower($data["params"]["car_number"]) . "'"; $rs = mysql_fetch_assoc(mysql_query($check_sql)); if ($rs["c"] > 0) { phpgrid_error("หมายเลขรถซ้ำ"); } } function after_insert($data) { /* These comments are just to show the input $data format Array ( [client_id] => 99 [params] => Array ( [client_id] => [name] => Test [gender] => male [company] => Comp Test ) ) */ /* ob_start(); print_r($data); $str = ob_get_clean(); phpgrid_error($str); */ $car_number = $data['params']['car_number']; } $out_detail = $grid->render("list2"); $h2_title = $this->lang->line('customeroils'); //display $this->_example_output((object) array('output' => '', 'out' => $out_index, 'out_detail' => $out_detail, 'h2_title' => $h2_title, 'js_files' => array(), 'css_files' => array())); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
public function index() { //check login if ($this->session->userdata('user_name')) { // Grid $this->load->library("jqgridnow_lib"); $g = new jqgrid(); $col = array(); $col["title"] = $this->lang->line('id'); // caption of column $col["name"] = "factory_id"; $col["width"] = "10"; $col["search"] = false; $col["editable"] = false; $col["hidden"] = true; $cols[] = $col; $col = array(); $col['title'] = $this->lang->line('factory_name'); $col['name'] = "factory_name"; $col['width'] = "40"; $col['search'] = true; $col['editable'] = true; $col["editrules"] = array("required" => true); $cols[] = $col; $col = array(); $col['title'] = $this->lang->line('factory_code'); $col['name'] = "factory_code"; $col['width'] = "40"; $col['search'] = true; $col['editable'] = true; $col["editrules"] = array("required" => true); $cols[] = $col; $col = array(); $col['title'] = $this->lang->line('factory_note'); $col['name'] = "factory_note"; $col['width'] = "40"; $col["sortable"] = false; // this column is not sortable $col["search"] = false; // this column is not searchable $col["editable"] = true; $col["edittype"] = "textarea"; // render as textarea on edit $col["editoptions"] = array("rows" => 2, "cols" => 30); // with these attributes $cols[] = $col; //properties grid $grid["sortname"] = 'factory_id'; $grid["sortorder"] = "desc"; $grid["caption"] = $this->lang->line('factory_setting'); $grid["autowidth"] = true; $grid["multiselect"] = false; $grid["rownumbers"] = true; $g->set_options($grid); $g->select_command = "SELECT\n\tfactory_id,\n\tfactory_name,\n\tfactory_code,\n\tfactory_note,\n\tfactory_status,\n\tstatus_name\nFROM\n\ttransport_factory AS fac\nLEFT JOIN allstatus AS st ON (fac.factory_status = st.id)\nWHERE\n\tfactory_status = 1"; $g->set_columns($cols); $e["on_delete"] = array("delete_factory", null, true); $g->set_events($e); function delete_factory($data) { $str_ch = "UPDATE transport_factory SET factory_status ='0' WHERE (factory_id ='{$data["factory_id"]}')"; phpgrid_error($str_ch); //mysql_query($str_ch); } $g->table = "transport_factory"; // render grid and get html/js output $out_index = $g->render("list1"); //display $this->_example_output((object) array('output' => '', 'out' => $out_index)); } else { //If no session, redirect to login page redirect('login', 'refresh'); } // end if }