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;
}
