function get_sql($db, $sql) { /*สร้างคำสั้ง SQL ของรายงาน Cross Tab */ $col_caption = array(); $row_total = array(); $col_field_name = $this->OP_[col_field_name]->get(); $data_field_name = $this->OP_[data_field_name]->get(); $tab_label = $this->OP_[tab_label]->get(); $data_function = $this->OP_[data_function]->get(); $limit = $this->OP_[tab_limit]->get(); //$my_sql = $sql; $tab_cmd = new OrSql(); $sql_cmd = new OrSql($sql); //ตรวจสอบคำสั่ง SQL แปลงเป็น Object $sql_tab_select = "DISTINCT `" . $col_field_name . "`"; //คำสั้งหาข้อมูลที่ต้องการแปลงเป็น Field $tab_cmd->OP_[cmd_select]->set($sql_tab_select); $tab_cmd->OP_[cmd_from]->set($sql_cmd->OP_[cmd_from]->get()); $tab_cmd->OP_[cmd_where]->set($sql_cmd->OP_[cmd_where]->get()); $tab_cmd->set_cmd_order("`" . $col_field_name . "`"); //echo "<b>debug</b> ".__FILE__." | ".__LINE__." | tab_cmd->get_cmd_sql() =".$tab_cmd->get_cmd_sql()."<br>"; $db->get_query($tab_cmd->get_cmd_sql()); if ($db->is_error()) { die($db->show_error()); } $i = 0; if ($data_function == 'sum') { $str_data = '`' . $data_field_name . '`'; } else { if ($data_function == 'count') { $str_data = 1; } } $str_function = 'sum'; while ($db->get_record()) { $i++; $id = $tab_label . $i; $col_caption[$id] = $db->record[$col_field_name]; $row_total[$id] = $str_function . "(IF(`" . $col_field_name . "` = '" . $db->record[$col_field_name] . "', " . $str_data . " , 0 )) AS `tt_" . $tab_label . $i . "` "; $col_function[$id] = $str_function . "(IF(`" . $col_field_name . "` = '" . $db->record[$col_field_name] . "', " . $str_data . " , 0 ))"; $my_value .= "," . $col_function[$id] . " AS `" . $id . "`"; //$my_value .= "," . $str_function. "(IF(`" . $col_field_name . "` = '" . $db->record[$col_field_name] . "', " . $str_data . " , 0 )) AS `" . $tab_label . $i . "` "; if ($i > $limit) { die('Data limit = ' . $limit . ' Cannot Process!'); break; } } $col_function[$tab_label . '0'] = $str_function . '(' . $str_data . ')'; $my_value .= ',' . $str_function . '(' . $str_data . ') AS `' . $tab_label . '0' . '` '; $row_total[$tab_label . '0'] = $str_function . '(' . $str_data . ') AS `' . 'tt_' . $tab_label . '0' . '` '; $col_caption[$tab_label . '0'] = 'รวม'; unset($db); $this->OP_[col_caption]->set($col_caption); $this->OP_[row_total]->set($row_total); $this->OP_[col_function]->set($col_function); //$sql_cmd = new OrSql($my_sql);//ตรวจสอบคำสั่ง SQL แปลงเป็น Object $sql_cmd->OP_[cmd_select]->set($sql_cmd->OP_[cmd_select]->get() . $my_value); $my_value = $sql_cmd->get_cmd_sql(); //echo "<b>debug</b> ".__FILE__." | ".__LINE__." | my_value =".$my_value."<br>"; $this->OP_[tab_sql]->set($my_value); return $my_value; }
function fetch_record($sql, $obj_filter = '', $cmd_msg = '', $reset = false) { if (get_class($obj_filter) == 'OrSql') { $cmd_filter = $obj_filter->OP_[cmd_filter]->get(); $cmd_order = $obj_filter->OP_[cmd_order]->get(); } else { die('fetch_record filter use OrSql Object!'); } $g_my_sec = new OrSec(); $sql_cmd = new OrSql(); $val_ = new OrSysvalue(); $db_event = $val_->db_event; /* รับค่าข้อมูลจาก form ได้แก่ 1. หมายเลข Record 2. จำนวน Record ทั้งหมด 3. คำสั่ง Filter 4. Messsage ที่แสดง 5. คำสั่งการเปลี่ยนเจ้าของ 6. ข้อมูล Event on_load */ if ($db_event[cmd_filter] != "") { $this->OP_[cmd_filter]->set(str_replace("#", "'", $db_event[cmd_filter])); } if ($db_event[cmd_msg] != "") { $this->OP_[cmd_msg]->set(str_replace("#", "'", $db_event[cmd_msg])); } if ($db_event[cmd_order] != "") { $this->OP_[cmd_order]->set(str_replace("#", "'", $db_event[cmd_order])); } if ($db_event[chg_owner] == '->') { $this->OP_[chg_owner]->set(true); $db_event[navigator] = 'Ref'; } $this->OP_[cur_rec]->set($db_event[cur_rec]); $this->OP_[total_rec]->set($db_event[total_rec]); if ($db_event[on_load] == '') { $EV_[EV_db_event] = $db_event; $this->OE_on_load($EV_); $this->OP_[on_load]->set(1); } else { $this->OP_[on_load]->set($db_event[on_load] + 1); } $evt_on_load = new OrFieldHidden("evt_on_load", "evt_form_db[on_load]"); $this->set_body($evt_on_load->get_tag($this->OP_[on_load]->get())); /* * ********************************************************************* */ $sql_cmd->set_cmd_sql($sql); //ตรวจสอบคำสั่ง SQL แปลงเป็น Object if ($this->OP_[chg_owner]->get()) { $this->set_controls(new OrSelectbox("sec_user")); } else { $this->set_controls(new OrFieldHidden("sec_user")); } //$this->set_controls(new OrFieldHidden("sec_ip")); //$this->set_controls(new OrFieldHidden("sec_script")); $txt_cmd_filter = new OrFieldHidden("evt_form_db[cmd_filter]"); $txt_cmd_order = new OrFieldHidden("evt_form_db[cmd_order]"); $txt_cmd_msg = new OrFieldHidden("evt_form_db[cmd_msg]"); //$this->db->get_query($sql); if ($reset or $db_event[navigator] == 'New') { $this->OP_[cmd_filter]->set(""); $this->OP_[cmd_order]->set(""); $this->OP_[cmd_msg]->set(""); $this->OP_[total_rec]->set(''); } else { if ($cmd_filter != "" or $cmd_order != '') { $this->OP_[cmd_filter]->set($cmd_filter); $this->set_body($txt_cmd_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_filter]->get()))); $this->OP_[cmd_order]->set($cmd_order); $this->set_body($txt_cmd_order->get_tag(str_replace("'", "#", $this->OP_[cmd_order]->get()))); $this->OP_[cmd_msg]->set($cmd_msg); $this->set_body($txt_cmd_msg->get_tag(str_replace("'", "#", $this->OP_[cmd_msg]->get()))); $this->OP_[total_rec]->set(''); } else { $this->set_body($txt_cmd_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_filter]->get()))); $this->set_body($txt_cmd_order->get_tag(str_replace("'", "#", $this->OP_[cmd_order]->get()))); $this->set_body($txt_cmd_msg->get_tag(str_replace("'", "#", $this->OP_[cmd_msg]->get()))); } } if ($this->OP_[cmd_filter]->get() != "") { $sql_cmd->set_cmd_where($this->OP_[cmd_filter]->get()); } $sql_count_row = $sql_cmd->get_cmd_sql(); if ($this->OP_[cmd_order]->get() != "") { $sql_cmd->set_cmd_order($this->OP_[cmd_order]->get(), ''); //กำหนดการเรียงเป็น '' เพื่อไม่ให้เกิดคำสั่งเรียงซัำ $sql = $sql_cmd->get_cmd_sql(); } $sql = $sql_cmd->get_cmd_sql(); /* ถ้าเป็นการเปิดครั้งแรก หรือ filter ตรวจสอบหน้าทั้งหมด */ if ($this->OP_[total_rec]->get() == '') { //echo "<b>debug</b> ".__FILE__." | ".__LINE__." | this->OP_[total_rec]->get() =".$this->OP_[total_rec]->get()."<br>"; /* $this->db->get_query($sql); $this->OP_[total_rec]->set($this->db->get_total_row()); */ $this->OP_[total_rec]->set($this->get_total_row($sql_count_row)); } //echo("<b>debug</b> ".__FILE__." | ".__LINE__." | db_event =".$db_event[navigator]."<br>"); switch ($db_event[navigator]) { case "New": $this->new_record(); break; case "Delete": $this->del_record(); $this->db->get_query($sql); $this->OP_[total_rec]->set($this->db->get_total_row($sql_count_row)); $this->first_rec($sql); break; case "Save": if ($this->OP_[cur_rec]->get() == '0') { if ($this->is_data_field()) { $this->add_record(); //$this->db->get_query($sql); //$this->OP_[total_rec]->set($this->db->get_total_row($sql_count_row)); //$this->last_rec($sql); $this->new_record(); } else { $this->new_record(true); //กำหนดให้เพื่อจำค่าที่บันทึกเวลาที่คีย์ข้อมูลแล้วกด Ref เรียกข้อมูลใหม่ } } else { /* บันทึกรายการ */ if ($this->is_data_field()) { $this->save_record(); } $this->reload($sql); } break; case "First": /* เลื่อนไปรายการแรกของข้อมูล */ $this->first_rec($sql); break; case "Next": /* เลื่อนไปรายการถัดไปของข้อมูล */ $this->next_rec($sql); break; case "Prev": /* เลื่อนไปรายการก่อนหน้า */ $this->prev_rec($sql); break; case "Last": /* เลื่อนไปรายการสุดท้ายของข้อมูล */ $this->db->get_query($sql); $this->OP_[total_rec]->set($this->db->get_total_row($sql_count_row)); $this->last_rec($sql); break; case "Ref": /* แสดงรายการปัจจุบัน */ if ($this->OP_[cur_rec]->get() > 0) { $this->reload($sql); } else { $this->new_record(true); //กำหนดให้เพื่อจำค่าที่บันทึกเวลาที่คีย์ข้อมูลแล้วกด Ref เรียกข้อมูลใหม่ //$this->OP_[nav]->set_event($this->OP_[nav]->OP_["btn_new"]->OP_[value]->get()); } break; default: $this->first_rec($sql); } return null; }
function fetch_record($sql, $obj_filter = '', $cmd_msg = '', $reset = false) { $my_sec = new OrSec(); if (get_class($obj_filter) == 'OrSql') { $cmd_filter = $obj_filter->OP_[cmd_filter]->get(); $cmd_group_filter = $obj_filter->OP_[cmd_group_filter]->get(); $cmd_order = $obj_filter->OP_[cmd_order]->get(); } else { die('fetch_record filter use OrSql Object!'); } $sql_cmd = new OrSql(); $val_ = new OrSysvalue(); $sql_cmd->set_cmd_sql($sql); $db_event = $val_->db_event; if ($db_event[cmd_filter] != "") { $this->OP_[cmd_filter]->set(str_replace("#", "'", $db_event[cmd_filter])); } if ($db_event[cmd_group_filter] != "") { $this->OP_[cmd_group_filter]->set(str_replace("#", "'", $db_event[cmd_group_filter])); } if ($db_event[cmd_msg] != "") { $this->OP_[cmd_msg]->set(str_replace("#", "'", $db_event[cmd_msg])); } if ($db_event[cmd_order] != "") { $this->OP_[cmd_order]->set(str_replace("#", "'", $db_event[cmd_order])); } if ($db_event[cur_page] > 0) { if ($db_event[go_page] > 0 and $db_event[go_page] != $db_event[cur_page]) { $this->OP_[cur_page]->set($db_event[go_page]); $db_event[navigator] = ''; } else { $this->OP_[cur_page]->set($db_event[cur_page]); } } /*ตรวจสอบเหตุการณ์ On Load*/ if ($db_event[on_load] == '') { $EV_[EV_db_event] = $db_event; $this->OE_on_load($EV_); $this->OP_[on_load]->set(1); } else { $this->OP_[on_load]->set($db_event[on_load] + 1); } $evt_on_load = new OrFieldHidden("evt_form_db[on_load]"); $this->set_body($evt_on_load->get_tag($this->OP_[on_load]->get())); /***************************/ if ($db_event[total_page] > 0) { $this->OP_[total_page]->set($db_event[total_page]); $this->OP_[total_row]->set($db_event[total_row]); } $txt_cmd_filter = new OrFieldHidden("evt_form_db[cmd_filter]"); $txt_cmd_group_filter = new OrFieldHidden("evt_form_db[cmd_group_filter]"); $txt_cmd_order = new OrFieldHidden("evt_form_db[cmd_order]"); $txt_cmd_msg = new OrFieldHidden("evt_form_db[cmd_msg]"); if ($reset) { $this->OP_[cmd_filter]->set(""); $this->OP_[cmd_group_filter]->set(""); $this->OP_[cmd_order]->set(""); $this->OP_[cmd_msg]->set(""); $this->OP_[total_page]->set(1); $this->OP_[total_row]->set(0); } else { if ($cmd_filter != '' or $cmd_order != '' or $cmd_group_filter != '') { $this->OP_[cmd_filter]->set($cmd_filter); $this->set_body($txt_cmd_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_filter]->get()))); $this->OP_[cmd_group_filter]->set($cmd_group_filter); $this->set_body($txt_cmd_group_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_group_filter]->get()))); $this->OP_[cmd_order]->set($cmd_order); $this->set_body($txt_cmd_order->get_tag(str_replace("'", "#", $this->OP_[cmd_order]->get()))); $this->OP_[cmd_msg]->set($cmd_msg); $this->set_body($txt_cmd_msg->get_tag(str_replace("'", "#", $this->OP_[cmd_msg]->get()))); $db_event[navigator] = 'First'; $this->OP_[total_page]->set(1); $this->OP_[total_row]->set(0); } else { $this->set_body($txt_cmd_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_filter]->get()))); $this->set_body($txt_cmd_group_filter->get_tag(str_replace("'", "#", $this->OP_[cmd_group_filter]->get()))); $this->set_body($txt_cmd_order->get_tag(str_replace("'", "#", $this->OP_[cmd_order]->get()))); $this->set_body($txt_cmd_msg->get_tag(str_replace("'", "#", $this->OP_[cmd_msg]->get()))); } } if ($db_event[row_page] != '') { $this->OP_[row_per_page]->set($db_event[row_page]); } if ($db_event[chg_row] != $db_event[row_page]) { if ($db_event[chg_row] != '') { $this->OP_[row_per_page]->set($db_event[chg_row]); } $db_event[navigator] = 'First'; $this->OP_[total_page]->set(1); $this->OP_[total_row]->set(0); } /*เหตุการณ์การกดปุ่มต่างๆ*/ $this->OP_[on_click]->set($db_event[navigator]); switch ($db_event[navigator]) { case "First": $this->OP_[go_page]->set(1); break; case "Next": if ($this->OP_[cur_page]->get() < $this->OP_[total_page]->get()) { $this->OP_[go_page]->set($this->OP_[cur_page]->get() + 1); } else { $this->OP_[go_page]->set($this->OP_[cur_page]->get()); } break; case "Prev": if ($this->OP_[cur_page]->get() > 1) { $this->OP_[go_page]->set($this->OP_[cur_page]->get() - 1); } break; case "Last": $this->OP_[go_page]->set($this->OP_[total_page]->get()); $this->OP_[total_page]->set(0); break; default: if ($this->OP_[cur_page]->get() != '') { $this->OP_[go_page]->set($this->OP_[cur_page]->get()); } else { $this->OP_[go_page]->set(1); } } $this->OP_[cmd_limit]->set(" LIMIT " . ($this->OP_[go_page]->get() - 1) * $this->OP_[row_per_page]->get() . "," . $this->OP_[row_per_page]->get()); /* * สร้างคำสั่ง Where */ if ($this->OP_[cmd_filter]->get() != "") { $sql_cmd->set_cmd_where($this->OP_[cmd_filter]->get()); } /* * สร้างคำสั่ง Having กรณีมี Group By */ if ($this->OP_[cmd_group_filter]->get() != "") { $sql_cmd->set_cmd_having($this->OP_[cmd_group_filter]->get()); } debug_mode(__FILE__, __LINE__, $sql_cmd->get_cmd_sql(), 'fetch_record : $sql_cmd'); $this->set_total_controls($sql_cmd->get_cmd_sql()); if ($this->OP_[total_page]->get() <= 1) { /*จะต้องไม่มีคำสั่ง ORDER ถึงจะมีค่า Total page ไม่ทราบสาเหตุ สุชาติ บุญหชัยรัตน์ 13/4/2547*/ $count_sql = ''; if ($sql_cmd->OP_[cmd_group]->get() == '') { /*ต้องไม่มีคำสั่ง Group By จึงสามารถใช้คำสีง Count ได้ สุชาติ บุญหชัยรัตน์ 19 /4/2547*/ $count_sql = 'SELECT count(*) AS `total_row` '; $count_sql .= ' FROM ' . $sql_cmd->OP_[cmd_from]->get(); if ($sql_cmd->OP_[cmd_where]->get() != '') { $count_sql .= ' WHERE ' . $sql_cmd->OP_[cmd_where]->get(); } } $this->set_total_page($count_sql, $sql_cmd->get_cmd_sql()); } if ($this->OP_[cmd_order]->get() != "") { $sql_cmd->set_cmd_order($this->OP_[cmd_order]->get(), ''); //กำหนดการเรียงเป็น '' เพื่อไม่ให้เกิดคำสั่งเรียงซัำ } debug_mode(__FILE__, __LINE__, $sql_cmd->get_cmd_sql(), 'function fetch_record'); $this->db->get_query($sql_cmd->get_cmd_sql() . $this->OP_[cmd_limit]->get()); if ($this->db->is_error()) { die("<b>ERROR</b> " . __FILE__ . " | " . __LINE__ . " | Description : " . $this->db->show_error()); } $this->OP_[sql_cmd]->set($sql_cmd); //Object SQL หลังการประมวล สุชาติ บุญหชัยรัตน์ 11/5/2547 //$this->form_fields = $this->get_form_fields(); return null; }
function fetch_record($sql, $filter = null, $filter_msg = '', $reset = false) { debug_mode(__FILE__, __LINE__, $sql, 'function fetch_record'); if (preg_match("/.group by/i", $sql) > 0) { $is_group_by = true; } else { $is_group_by = false; } debug_mode(__FILE__, __LINE__, $is_group_by, 'is_group_by'); $val_ = new OrSysvalue(); $val_msg = $val_->message; $db_event = $val_->db_event; $val_compare = $val_->compare; /* ตรวจสอบการกดปุ่มเพิ่มรายการใหม่ */ /* if($val_msg[btn_new] == 'New' ){ header("Location:".$this->OP_[edit_page_url]->get() . '?evt_form_db[navigator]=New'); } */ foreach ($this->controls as $control_id => $control) { if ($control->OP_[db_field]->get()) { $this->caption_fields[$control->OP_[caption]->get()] = $control_id; debug_mode(__FILE__, __LINE__, $control->OP_[caption]->get(), 'caption'); } } /** * ตรวจสอบ mode การทำงาน ถ้าเป็น Query ไม่ต้องดึงข้อมูล * */ if ($db_event[on_load] == '' and $this->OP_[default_mode]->get() == 'query' and $val_msg[btn_filter] != 'Filter' or $val_msg[btn_query] == 'Query') { $this->form_mode = 'query'; } else { /** * ตรวจสอบการคัดกรองข้อมูล ตามมาตรฐาน * */ if (is_null($filter)) { $filter = new OrSql(); } /** * ตรวจสอบคำสั่ง Group By */ if (preg_match("/.group by/i", $sql) > 0) { $is_group_by = true; } else { $is_group_by = false; } if ($val_msg[btn_filter] == 'Filter') { if ($this->filter_value[filter_by] != '') { $filter_msg .= 'ค้นหา ' . $this->filter_value[filter_by] . ' <i>ทุกข้อมูล</i> '; $new_filter = true; //foreach ($this->caption_fields AS $caption => $id) { foreach ($this->controls as $control_id => $control) { /** * ตรวจสอบค่าประเภทฟิลด์ข้อมูลต้องเป็นข้อความเท่านั้น จึงจะนำมาคัดข้อมูล */ if ($control->OP_[db_field]->get() and $control->OP_[db_type]->get() == 'text') { $id = $control_id; if ($is_group_by) { if ($this->is_group_filter($this->get_filter_name($id))) { debug_mode(__FILE__, __LINE__, $this->get_filter_name($id), 'filter_name'); $filter->set_cmd_group_filter($this->get_filter_name($id), 'LIKE', $this->filter_value[filter_by], 'OR'); //$filter_msg .= ' ' . $caption . ' '; } else { debug_mode(__FILE__, __LINE__, $this->get_filter_name($id), 'filter_name'); /** * ยกเว้นการค้นหาในกรณี Group By */ //$filter->set_cmd_filter($this->get_filter_name($id), 'LIKE', $this->filter_value[filter_by], 'OR'); } } else { $filter->set_cmd_filter($this->get_filter_name($id), 'LIKE', $this->filter_value[filter_by], 'OR', $new_filter); //$filter_msg .= ' ' . $caption . ' '; } $new_filter = false; } } } /* * ค้นจาก Query* */ if (is_array($this->filter_value)) { $filter_msg .= ' จาก '; $new_filter = true; foreach ($this->filter_value as $id => $value) { if ($id != 'filter_by') { $EV_[EV_field_name] = $id; $EV_[EV_field_value] = $value; $this->OE_set_filter($EV_); if ($value != '') { debug_mode(__FILE__, __LINE__, $this->filter_value[$id], 'Filter_value ' . $id); switch ($val_compare[$id]) { case 'BETWEEN': if ($val_msg[$id . '_II'] != '') { $value = $value . ' - ' . $val_msg[$id . '_II']; } break; case '': $val_compare[$id] = '='; default: } /* if ($val_compare[$id] == '') { $val_compare[$id] = 'LIKE'; } */ if ($is_group_by) { if ($this->is_group_filter($this->get_filter_name($id))) { $filter->set_cmd_group_filter($this->get_filter_name($id), $val_compare[$id], $value); $filter_msg .= ' ' . $this->controls[$id]->OP_[caption]->get() . ' ' . $val_compare[$id] . ' ' . $value; } else { $filter->set_cmd_filter($this->get_filter_name($id), $val_compare[$id], $value); $filter_msg .= ' ' . $this->controls[$id]->OP_[caption]->get() . ' ' . $val_compare[$id] . ' ' . $value; } } else { $filter->set_cmd_filter($this->get_filter_name($id), $val_compare[$id], $value, 'AND', $new_filter); $filter_msg .= ' ' . $this->controls[$id]->OP_[caption]->get() . ' ' . $val_compare[$id] . ' ' . $value; } $new_filter = false; } } } } if ($val_msg['order_by'] != '') { $sort_field = $val_msg['order_by']; $filter_msg .= ' เรียงตาม '; foreach ($this->caption_fields as $caption => $id) { if ($val_msg['order_by'] == $id) { $filter->set_cmd_order($id); $filter_msg .= ' ' . $caption . ' '; } } } } else { if ($val_msg[btn_filter] == 'No Filter') { $reset = true; } } parent::fetch_record($sql, $filter, $filter_msg, $reset); } return null; }