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