示例#1
0
 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;
 }
示例#2
0
 function get_total_row($sql_count_row)
 {
     $db_page = $this->db;
     $sql_cmd = new OrSql();
     $sql_cmd->set_cmd_sql($sql_count_row);
     $sql_cmd->OP_[cmd_select]->set(' count(*) AS `total_row` ');
     $count_sql = $sql_cmd->get_cmd_sql();
     $db_page->get_query($count_sql);
     if (!$db_page->is_error()) {
         $db_page->get_record();
         $this->OP_[total_rec]->set($db_page->record[total_row]);
     } else {
         $db_page->show_error();
     }
     return $this->OP_[total_rec]->get();
 }
示例#3
0
 function set_total_controls($sql)
 {
     /*ตรวจสอบสร้างคำสั่ง SQL เพื่อสร้างยอดรวม*/
     debug_mode(__FILE__, __LINE__, $sql, 'set_total_controls : $sql');
     $sql_cmd = new OrSql();
     // สร้างใหม่เพื่อไม่ให้กระทบค่าเดิม
     $sql_cmd->set_cmd_sql($sql);
     if (is_array($this->total_function)) {
         $db = $this->db;
         $i = 0;
         foreach ($this->total_function as $id => $obj) {
             $field_name = $this->get_filter_name($obj->field_name);
             if (strpos($field_name, "`") === false and strpos($field_name, "(") === false) {
                 $field_name = '`' . $field_name . '`';
             }
             if ($i == 0) {
                 $str_select = $obj->field_function . '(' . $field_name . ') AS `' . $id . '` ';
             } else {
                 $str_select .= ' , ' . $obj->field_function . '(' . $field_name . ')  AS `' . $id . '` ';
             }
             $i++;
         }
     }
     if ($str_select != '' and $this->OP_['tab_select_total']->get() != '') {
         $str_comma = ' , ';
     }
     if ($str_select != '' or $this->OP_['tab_select_total']->get() != '') {
         $sql_cmd->OP_[cmd_select]->set($str_select . $str_comma . $this->OP_['tab_select_total']->get());
         $sql_cmd->OP_[cmd_group]->set('');
         debug_mode(__FILE__, __LINE__, $sql_cmd->get_cmd_sql(), 'set_total_controls : $sql_cmd');
         $db->get_query($sql_cmd->get_cmd_sql());
         if ($this->db->is_error()) {
             die("gen_total_list_tag had error = " . $db->show_error());
         }
         $db->get_record();
     }
     /**
      * สร้าง Controls ค่าผลรวม
      **/
     foreach ($this->total_controls as $control_id => $control) {
         $control->OP_[value]->set($db->record[$control_id]);
         debug_mode(__FILE__, __LINE__, $control->OP_[value]->get(), 'ผลของ' . $control_id);
     }
     return null;
 }
示例#4
0
 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;
 }