<?php
/**
 * 组装sql语句类 SqlCombinHelper
 *
 * @author sun
 */
class SqlCombinHelper {
    /**
     * 插入一条数据语句
     * @param string $tblname 表名
     * @param array $arrField 字段数组
     * @return string 
     */
    public static function insert_sql($tblname, $arrField) {
        $intFieldNum = count($arrField);
        $strFields = '';
        $strFieldValues = '';
        for($i=0; $i<$intFieldNum; $i++) {
            $strFields .= "," . key($arrField);
            $strFieldValues .= ",'" . $arrField[key($arrField)] . "'";
            next($arrField);
        }
        $sql  = "INSERT INTO " .$tblname . " ( ".ltrim($strFields, ',') 
              . " ) VALUES ( ". ltrim($strFieldValues, ',') . " ) ";
        return $sql;
    }
    
    /**
     * 插入多条数据语句
     * @param string $tblname 表名
     * @param array $arrField 字段数组
     * @return string 
     */
    public static function insertMutil_sql($tblname, $arrField) {
        if(is_array($arrField)&&!empty($arrField)) {
            //print_r($arrField[0]);
           $strFields = implode(",",array_keys($arrField[0]));
        }else {
            return false;
        }
        $temp = array();
        foreach ($arrField as $f) {
            $temp[] = "('".implode("','", $f)."')";
        }
        $values = implode(",", $temp);
        $sql  = "INSERT INTO " .$tblname . " ( ".ltrim($strFields, ',') 
              . " ) VALUES $values ";
        return $sql;
    }
    
    /**
     * 更新语句
     * @param type $tblname  表名
     * @param type $arrField 字段数组
     * @param type $strWhere 更新条件
     * @return string
     */
    public static function update_sql($tblname, $arrField, $strWhere = '') {
        $intFieldNum = count($arrField);
        $strFieldValues = '';
        for($i=0; $i<$intFieldNum; $i++) {
            $value = $arrField[key($arrField)];
            $strFieldValues .= "," . key($arrField) ."='" . $value."' ";
            next($arrField);
        }
        $strWhere = $strWhere != '' ? " WHERE $strWhere" : '';
        $sql  = "UPDATE " . $tblname ." SET " . ltrim($strFieldValues, ',')
              . $strWhere;
        return $sql;
    }
    
    /**
     * 在原有的字段基础上更新语句
     * @param type $tblname  表名
     * @param type $arrField 字段数组
     * @param type $strWhere 更新条件
     * @return string
     */
    public static function updateSelf_sql($tblname, $arrField, $strWhere) {
        $intFieldNum = count($arrField);
        $strFieldValues = '';
        for($i=0; $i<$intFieldNum; $i++) {
            $value = $arrField[key($arrField)];
            $ck = key($arrField);
            $strFieldValues .= "," . $ck ."=$ck+'" . $value."' ";
            next($arrField);
        }
        $strWhere = $strWhere != '' ? " WHERE $strWhere" : '';
        $sql  = "UPDATE " . $tblname ." SET " . ltrim($strFieldValues, ',')
              . $strWhere;
        return $sql;
    }
    
    /**
     * 拼接查询语句
     * @param type $tblname  表名
     * @param type $field    查询字段
     * @param type $strWhere 查询条件
     * @return string
     */
    public static function select_sql($tblname, $field, $strWhere = '') {
        if (is_array($field) && !empty($field)) {
            $field = implode(",", $field);
        }
        $strWhere = $strWhere != '' ? " WHERE $strWhere" : '';
        $sql = "SELECT  $field FROM $tblname ". $strWhere;
        return $sql;
    }
    
    /**
     * 拼接删除语句
     * @param type $tblname  表名
     * @param type $strWhere 删除条件
     * @return string
     */
    public static function delete_sql($tblname, $strWhere = '') {
        $strWhere = $strWhere != '' ? " WHERE $strWhere" : '';
        $sql = "DELETE FROM $tblname ". $strWhere;
        return $sql;
    }
    
    /**
     * 插入语句
     * @param string $tblname 表名
     * @param array $arrField 字段数组
     * @return string 
     */
    public static function replace_sql($tblname, $arrField) {
        $intFieldNum = count($arrField);
        $strFields = '';
        $strFieldValues = '';
        for($i=0; $i<$intFieldNum; $i++) {
            $strFields .= "," . key($arrField);
            $strFieldValues .= ",'" . $arrField[key($arrField)] . "'";
            next($arrField);
        }
        $sql  = "REPLACE INTO " .$tblname . " ( ".ltrim($strFields, ',') 
              . " ) VALUES ( ". ltrim($strFieldValues, ',') . " ) ";
        return $sql;
    }
    
    /**
     * 统计查询语句
     * @param type $tblname 表名
     * @param type $strWhere 查询条件
     * @return string
     */
    public static function count_sql($tblname, $strWhere = '') {
        $strWhere = $strWhere == '' ? '' : " WHERE $strWhere";
        $sql = "SELECT COUNT(*) AS num FROM $tblname ". $strWhere;
        return $sql;
    }
    
    /**
     * 统计查询语句
     * @param type $tblname 表名
     * @param type $strWhere 查询条件
     * @return string
     */
    public static function sum_sql($tblname, $field, $strWhere = '') {
        $strWhere = $strWhere == '' ? '' : " WHERE $strWhere";
        $sql = "SELECT sum($field) AS num FROM $tblname ". $strWhere;
        return $sql;
    }
    /**
     * 拼接两张表统计内连接
     * @param type $tblname1 表名一
     * @param type $tblname2 表名二
     * @param type $onWhere   on条件 
     * @param type $strWhere  查询条件
     * @return string
     */
    public static function innerCount_sql($tblname1, $tblname2, $onWhere, 
            $strWhere = '') {
        $strWhere = $strWhere == '' ? '' : " WHERE $strWhere";
        $sql = "SELECT COUNT(*) AS num FROM $tblname1 INNER JOIN $tblname2 ON "
                . "$onWhere $strWhere";
        return $sql;
    }
    
    /**
     * 拼接两张表内连接
     * @param type $tblname1 表名一
     * @param type $tblname2 表名二
     * @param type $searchField  搜索的字段
     * @param type $onWhere   on条件 
     * @param type $strWhere  查询条件
     * @return string
     */
    public static function innerSelect_sql($tblname1, $tblname2, $searchField, 
            $onWhere, $strWhere = '') {
        $strWhere = $strWhere == '' ? '' : " WHERE $strWhere";
        $sql = "SELECT $searchField FROM $tblname1 INNER JOIN $tblname2 ON "
                . "$onWhere $strWhere";
        return $sql;
    }
    
    public static function orderby() {
        $args = func_get_args();
        if($args) {
            $orderby = array();
            foreach ($args as $value) {
                foreach ($value as $key => $val) {
                   $orderby[] = $key." ".$val; 
                }
            }
            return "ORDER BY ".implode(',', $orderby);
        }
    }
    
    public static function changeToKeyArray($array, $key) {
        $newArray = array();
        if($array) {
            foreach ($array as $value) {
                if(key_exists($key, $value)) {
                    $newArray[$value[$key]] = $value;
                }
            }
        }
        return $newArray;
    }

    public static function changeToDoubleKeyArray($array,$key,$key1=null) {
        $newArray = array();
        if($array) {
			$i = 0;
            foreach ($array as $value) {
                if(key_exists($key, $value)) {
					if ($key1 === null) {
						$newArray[$value[$key]][] = $value;
					} else {
						$newArray[$value[$key]][$value[$key1]] = $value;
					}
                    
                }
            }
        }
        return $newArray;
    }
    
    /**
     * 获取数组中某列的值组成的数组，去重
     * @param array $array
     * @param string $key
     * @return array
     */
    public static function getColumnDataByKey($array,$key) {
        if(is_array($array)) {
            $res = array();
            foreach ($array as $v) {
                $res[] = $v[$key];
            }
            if(!empty($res)) {
                array_unique($res);
            }
            return $res;
        } else {
            return array();
        }
    }
}
