<?php
/**
 * 
 * @copyright (c) 2005 - 2011 eifr.com
 * @license http://www.gnu.org/copyleft/gpl.html gnu/gpl
 * eifr is free software
 */

/**
 * ---evalSql---
 * 
 * @param int $type =0
 * @param string $sql_select ='',$var_words=''
 * @return string 
 */
function evalSql($sql_select = '', $var_words = '') {
    if (strpos($sql_select, '$') > 0 || strpos($sql_select, '".') > 0 || strpos($sql_select, "'.") > 0) {
        if (strpos($sql_select, "'.") > 0) {
            $sql = "\$sql='$sql_select';";
        } else {
            $sql = '$sql="' . $sql_select . '";';
        }
        
        global $evalSqlVars;  //$evalSqlVars = array('var_name'=>'var_value');
        if(is_array($evalSqlVars)) {
            foreach($evalSqlVars as $k=>$v) {
                $var_words .= '$'.$k.'="'.$v.'";';
            }
        }

        if ($var_words != '') eval($var_words);
        eval("$sql");
    } else {
        $sql = $sql_select;
    } 

    return $sql;
} 

/**
 * ---dbCheckDataIsExist---
 * check edit date value to exist.
 * edit data when $newvalue!= $originalvalue.
 * 
 * @param string $tablename 
 * @param string $wherefield 
 * @param string $newvalue 
 * @param string $originalvalue =''
 */
function dbCheckDataIsExist($tablename, $wherefield, $newvalue, $originalvalue = '') {
    global $db, $lang;

    $rtn = false;

    if ($newvalue != $originalvalue) {
        $sql = "SELECT count(*) as num FROM " . $tablename . " where " . $wherefield . "='" . $newvalue . "'";
        $rs = $db -> Execute($sql);
        if ($rs === false) {
            Log_e(__FILE__, __LINE__, $sql);
        }
        if ($rs -> fields['num'] != 0) {
            $rtn = true;
        } 

        $rs -> Close();
    } 

    if ($rtn) {
        showMsg('"' . $newvalue . '"' . $lang['exist'], '-1');
        exit();
    } 
} 

/**
 * ---dbGetRecordCount---
 * check Record count.
 * 
 * @param string $tablename ='',$wherevalue=''
 * @return bool 
 */
function dbGetRecordCount($tablename = '', $wherevalue = '') {
    global $db;

    $rtn = 0;
    $sqlstr = '';
    if ($wherevalue) {
        $sqlstr = " where " . $wherevalue . "";
    } 

    $sql = "SELECT count(*) as num FROM " . $tablename . $sqlstr . "";
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    $rtn = $rs -> fields['num'];
    $rs -> Close();
    return $rtn;
} 

/**
 * ---dbGetMaxValue---
 * get table maximum value
 * 
 * @param string $tablename ='',$fieldname='',$wherevalue=''
 * @return String 
 */
function dbGetMaxValue($tablename = '', $fieldname = '', $wherevalue = '') {
    global $db;
    $sqlstr = '';
    if ($wherevalue) {
        $sqlstr = " where " . $wherevalue . "";
    } 
    $sql = "select max(" . $fieldname . ") as rtn_value from " . $tablename . $sqlstr . "";
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    $rtn_value = $rs -> fields['rtn_value'];
    $rs -> Close();
    return $rtn_value;
} 

/**
 * ---dbGetMaxValue1---
 * get table maximum value+1
 * 
 * @param string $tablename ='',$fieldname='',$wherevalue=''
 * @return String 
 */
function dbGetMaxValue1($tablename = '', $fieldname = '', $wherevalue = '') {
    return dbGetMaxValue($tablename, $fieldname, $wherevalue) + 1;
} 

/**
 * ---dbGetMinValue---
 * get table minimum value
 * 
 * @param string $tablename ='',$fieldname='',$wherevalue=''
 * @return String 
 */
function dbGetMinValue($tablename = '', $fieldname = '', $wherevalue = '') {
    global $db;
    $sqlstr = '';
    if ($wherevalue) {
        $sqlstr = " where " . $wherevalue . "";
    } 
    $sql = "select min(" . $fieldname . ") as rtn_value from " . $tablename . $sqlstr . ""; 
    // echo $sql;
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    $rtn_value = $rs -> fields['rtn_value'];
    $rs -> Close();
    return $rtn_value;
} 

/**
 * ---dbGetRecordPages---
 * 
 * @param string $sql = '', $page_where = '', $page_type = 'Num', $page_lim = 20, $page_adjacents = 6
 * @return array 
 */

function dbGetRecordPages($sql = '', $page_where = '', $page_type = 'Num', $page_lim = 20, $page_adjacents = 6) {
    global $db ;
    $rtn = array();
    $rtn['list'] = array();
    $rtn['page'] = ''; 
    // echo $sql;
    if ($sql == '') return $rtn;
    $page_list = array();

    $clPages = new Pages($page_type, $page_lim, $page_adjacents);
    $clPages -> query_string = $page_where;
    $clPages -> query_sql = $sql;
    $aryResult = $clPages -> Execute();
    $rs = $aryResult[0];
    $intList = $aryResult[2];

    if ($intList > 0) {
        $num = 0;
        while (!$rs -> EOF) {
            $num++;
            $rs -> fields['eifr_num'] = $num;
            $page_list[] = $rs -> fields;
            $rs -> MoveNext();
        } 
    } 
    $rs -> Close();

    $rtn['list'] = $page_list;
    $rtn['page'] = $aryResult[3];
    $rtn['totalpages'] = $aryResult[4];
    return $rtn;
} 

/**
 * ---dbGetRecordn---
 * 
 * @param string $sql =''
 * @return array 
 */
function dbGetRecordn($sql = '') {
    global $db ;
    $rtn = array();
    if ($sql == '') return $rtn;
    $rs = $db -> GetAll($sql);
    if($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
        return $rtn;
    }
    // echo $sql;
    for ($i = 0; $i < count($rs); $i++) {
        $rtn[] = $rs[$i];
    } 
    return $rtn;
} 

/**
 * ---dbGetRecord1---
 * 
 * @param string $sql =''
 * @return array 
 */
function dbGetRecord1($sql = '', $tbf = '') {
    global $db;
    $rtn = array();

    if ($sql == '') {
        Log_e(__FILE__, __LINE__, $sql);
    }

    if ($tbf != '') {
        $rtn = getTableFill($tbf);
    } 

    $rs = $db -> Execute($sql); 
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
        return $rtn;
    }

    if ($tbf != '') {
        $rtn = array_merge($rtn, $rs -> fields);
    } else {
        $rtn = $rs -> fields;
    } 

    // Log_v(__FILE__, __LINE__, serialize($rtn));
    return $rtn;
} 

/**
 * ---dbGetRecord---
 * 
 * @param int $type =0
 * @param string $sql ='',$page_where=''
 * @return array 
 */
function dbGetRecord($type = 0, $sql = '', $page_where = '', $page_lim = 20) { // 1:one record ; 2:multi-recodes 3: multi-recodes page break
    if ($type = 1) return dbGetRecord1($sql);
    if ($type = 2) return dbGetRecordn($sql);
    if ($type = 3) return dbGetRecordPages($sql, $page_where = '', $page_lim);
} 

/**
 * ---dbGetFieldValue---
 * show table field value
 * 
 * @param string $tb ,$fieldname,$wherevalue
 * @return string 
 */
function dbGetFieldValue($tablename, $fieldname, $wherevalue = '') {
    global $db;
    $rtn = '';
    $sqlstr = '';
    if ($wherevalue) {
        $sqlstr = " where " . $wherevalue . "";
    } 

    $sql = "select " . $fieldname . " from " . $tablename . $sqlstr . ""; 
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    if (!$rs -> EOF) {
        $rtn = $rs -> fields[$fieldname];
    } 
    $rs -> Close();

    return $rtn;
} 

/**
 * ---dbGetFieldSum---
 * 
 * @param string $tablename,$fieldname,$wherevalue=''
 * @return int 
 */
function dbGetFieldSum($tablename, $fieldname, $wherevalue = '') {
    global $db;

    $rtn = 0;
    $sqlstr = '';
    if ($wherevalue) {
        $sqlstr = " where " . $wherevalue . "";
    } 

    $sql = "SELECT sum($fieldname) as num FROM " . $tablename . $sqlstr . "";
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    $rtn = $rs -> fields['num'];
    $rs -> Close();
    return $rtn;
} 

function dbInsert($table, $record_arr) {
    global $db;
    $rc = false;
    $sql = "INSERT INTO " . $table . " (" . implode(', ', array_keys($record_arr)) . ") VALUES ('" . implode("','", array_values($record_arr)) . "')";
    Log_v(__FILE__, __LINE__, $sql);
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    if ($rs) {
        $rc = true;
    } 

    return $rc;
} 

function dbUpdate($table, $record_arr, $where) {
    global $db;
    $valstr = array ();
    $rc = false;
    foreach ($record_arr as $key => $val) {
        $valstr[] = $key . " = '" . $val . "'";
    } 
    $sql = "UPDATE " . $table . " SET " . implode(', ', $valstr);
    $sql .= ($where != '') ? " WHERE " . $where : '';
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    if ($rs) {
        $rc = true;
    } 

    return $rc;
} 

function dbDelete($table, $where, $allowDelAll = false) {
    global $db;
    $rc = false;
    $sqlstr = '';
    if ($where) {
        $sqlstr = " WHERE " . $where;
    } 
    if ($sqlstr == '' && $allowDelAll === false) {
        Log_e(__FILE__, __LINE__, 'DOT NOT allow to delete all records, unless set $allowDelAll=true');
        return false;
    } 
    $sql = "DELETE FROM " . $table . $sqlstr;
    $rs = $db -> Execute($sql);
    if ($rs === false) {
        Log_e(__FILE__, __LINE__, $sql);
    }
    if ($rs) {
        $rc = true;
    } 

    return $rc;
} 

/**
 * ---dbUpdateRelatedField---
 * update relation table
 * 
 * @param  $tablename ='',$rel_field='',$from_id='',$to_id='',$is_equal=true,$primary_field=''
 * @return bool 
 */
function dbUpdateRelatedField($tablename = '', $rel_field = '', $from_id = '', $to_id = '', $is_equal = true, $primary_field = '') {
    global $db, $url;
    $rtn = true;
    if ($from_id == '' || $tablename == '' || $rel_field == '') return $rtn ;
    if ($is_equal) {
        $sql_up = "update $tablename set $rel_field='$to_id' where $rel_field='$from_id'";
        $rs_up = $db -> Execute($sql_up);
        if ($rs_up === false) {
            Log_e(__FILE__, __LINE__, $sql);
        }
        if ($rs_up) {
            $rtn = true;
        } else {
            $rtn = false;
        } 
        $rs_up -> Close();
    } else {
        if ($primary_field == '') return $rtn ;
        $sql = "select $primary_field, $rel_field from $tablename where $rel_field<>'' and  $rel_field<>'-1' ";
        $rs = $db -> Execute($sql); 
        if ($rs === false) {
            Log_e(__FILE__, __LINE__, $sql);
        }
        // echo $sql.'<br/>';
        while (!$rs -> EOF) {
            $rel_value = ',' . $rs -> fields[$rel_field] . ',';
            $rel_value = str_replace(",$from_id,", ",$to_id,", $rel_value);
            $rel_value = substr($rel_value, 1, strlen($rel_value)-1);
            $rel_value_ary = explode(',', $rel_value);
            $rel_value_ary = array_unique($rel_value_ary);
            foreach($rel_value_ary as $key => $value) {
                if ($value == '') unset($rel_value_ary[$key]);
            } 
            reset($rel_value_ary);
            $rel_value = implode(',', $rel_value_ary);

            $sql_up = "update $tablename set $rel_field='$rel_value' where $primary_field='" . $rs -> fields[$primary_field] . "'";
            $rs_up = $db -> Execute($sql_up);
            if ($rs_up === false) {
                Log_e(__FILE__, __LINE__, $sql);
            }
            if ($rs_up) {
                $rtn = true;
            } else {
                $rtn = false;
            } 
            $rs_up -> Close();

            $rs -> MoveNext();
        } 
        $rs -> Close();
    } 

    return $rtn ;
} 

function getTableFill($tbf) {
    $rtn = array();
    if ($tbf != '') {
        include(EIFR_ROOT . 'database/' . $tbf . '.php');
        $tbfvar = 'tbf_' . $tbf;
        $rtn = ${$tbfvar};
    } 
    return $rtn;
} 

/**
 * End of file dbHelper.php
 */