<?php
/**
 * SQL字符串操作
 *
 */
class Util_Utils_SqlString
{

    /**
     * 合并一个整数型的sql in 字符串
     *
     * @param $list array
     * @return string
     */
    public static function mergeSqlIntegerInString ($list)
    {
        $return = '';
        foreach ($list as $v)
        {
            $return .= (int) $v . ',';
        }
        return substr($return, 0, - 1);
    }

    /**
     * 合并一个字符串型的sql in 字符串
     *
     * @param $list array
     * @return string
     */
    public static function mergeSqlInString ($list)
    {
        $ret = '';
        foreach ($list as $v)
        {
            $v = addslashes($v);
            $ret .= "'$v',";
        }
        return substr($ret, 0, - 1);
    }

    /**
     * 在数组每个元素前头增加一个前缀
     *
     * @param $prefix string
     * @param $list array
     * @param $pairs &array 如果输入返回增加了前缀的数组和没有增加前缀数组的对应
     * @return array
     */
    public static function prefixValues ($prefix, $list, &$pairs = null)
    {
        $return = array();
        foreach ($list as $v)
        {
            $return[$v] = $prefix . $v;
            if (isset($pairs))
            {
                $pairs[$v] = $prefix . $v;
            }
        }
        return $return;
    }

    /**
     * 计算两个数组的差集
     *
     * @param $list1 array
     * @param $list2 array
     * @param $pairs array $list2 与 $list的对应 key(list1) value(list2)
     * @return array
     */
    public static function diffByPairs ($list1, $list2, $pairs)
    {
        $return = array();
        foreach ($list1 as $v)
        {
            if (! in_array($pairs[$v], $list2))
            {
                $return[] = $v;
            }
        }
        return $return;
    }

    /**
     * 从缓存中取DAO已存的数据，根据前缀，在getByList功能性函数中使用
     *
     * @param $idList array
     * @param $prefix string
     * @param $cacheObj Q_Cache_Adapter_Memcache
     * @param $listNotHit &array 如果存在则返回没有命中的List
     * @return array false
     */
    public static function getListFromCache ($idList, $prefix, $cacheObj, &$listNotHit = null)
    {
        $pairs = array();
        $keys = self::prefixValues($prefix, $idList, $pairs);
        $return = $cacheObj->get(array_values($keys));
        $return = is_array($return) ? array_filter($return) : $return;
        $realReturn = array();
        if ($return)
        {
            $pairs1 = array_flip($pairs);
            foreach ($return as $k => $v)
            {
                $realReturn[$pairs1[$k]] = $v;
            }
            if (isset($listNotHit))
            {
                $listNotHit = self::diffByPairs($idList, array_keys($return), $pairs);
            }
        }
        else
        {
            $realReturn = false;
            if (isset($listNotHit))
            {
                $listNotHit = $idList;
            }
        }
        return $realReturn;
    }

    /**
     * 将列表结果存入缓存
     *
     * @param $result array
     * @param $prefix string
     * @param $cacheObj Q_Cache_Adapter_Memcache
     * @param $expire integer
     * @return void
     */
    public static function setListToCache ($result, $prefix, $cacheObj, $expire = 60)
    {
        foreach ($result as $k => $v)
        {
            $cacheObj->set($prefix . $k, $v, $expire);
        }
    }

    /**
     * 组合SQL更新语句
     *
     * @param $params array
     * @return string
     */
    public static function mergeSqlUpstring ($params)
    {
        $return = '';
        foreach ($params as $k => $v)
        {
            $return .= $k . ' = :' . $k . ',';
        }
        return substr($return, 0, - 1);
    }

    /**
     * 合并整数字串
     *
     * @param $params array
     * @return string
     */
    public static function mergeIntegerString ($params)
    {
        return self::mergeSqlIntegerInString($params);
    }

    /**
     * 组合INSERT INTO table (InsertString) VALUES (InsertFields)的参数
     *
     * @param $params array
     * @return string
     */
    public static function mergeInsertString ($params)
    {
        $return = '';
        foreach ($params as $k => $v)
        {
            $return .= ':' . $k . ',';
        }
        return substr($return, 0, - 1);
    }

    /**
     * 组合INSERT INTO table (InsertString) VALUES (InsertFields)的参数
     *
     * @param $params array
     * @return string
     */
    public static function mergeInsertFields ($params)
    {
        $return = '';
        foreach ($params as $k => $v)
        {
            $return .= $k . ',';
        }
        return substr($return, 0, - 1);
    }

    /**
     * 组合SQL条件WHERE语句
     *
     * @example 'field1 =: field1 AND field2 = :field2 '
     *          Util_Utils_SqlString::mergeSqlWhereString ( array ( 'field1' => '***',
     *          ) );
     * @example 'field1 <= :field AND '
     *          Util_Utils_SqlString::mergeSqlWhereString ( array ( 'field1' => '***',
     *          ), ' <= :', ' AND ', false, null );
     * @example 'field2 LIKE ":field1%" OR field2 LIKE ":field2%" '
     *          Util_Utils_SqlString::mergeSqlWhereString ( array ( 'field1' => '***',
     *          ), ' LIKE ":', '%" OR ', true, -3 );
     *         
     * @param $params array
     * @param $sign string 连接符号 eg: '=: != >: <: <=: >=:'
     * @param $separator string 条件之间分隔符 eg: ' AND OR '
     * @param $section boolean 控制返回的结果是否需要截取掉分隔符 (true: 是, false: 否 )
     * @param $length int 截取的长度
     * @return string
     */
    public static function mergeSqlWhereString ($params, $sign = '=:', $separator = ' AND ', $section = true, $length = null)
    {
        $return = '';
        foreach ($params as $k => $v)
        {
            $return .= $k . $sign . $k . $separator;
        }
        if ($section)
        {
            if (! isset($length))
            {
                $length = 0 - strlen($separator);
            }
            $return = substr($return, 0, $length);
        }
        return $return;
    }
}