/////////////////////////////////////////////////////////////////////////////
//	File Description    : 生成SQL语句
//	Copyright           : joyin
// -------------------------------------------------------------------------
//	Date Created		: Mar 26, 2010
//	Author		        : jiangxinjun
//
/////////////////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common; 
using HanHe.Common;
using HanHe.Entity;
using HanHe.Utility.Data;

namespace HanHe.DAL
{
    /// <summary>
    /// 生成SQL语句
    /// </summary>
    public static class SqlStringHelper
    {
        #region CreateInsertSql
        /// <summary>
        /// 生成Insert SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static string CreateInsertSql(string tableName, Database db)
        {
            string sql = CommonUseSqlFormat.INSERT_SQL;
            var schema = DAOManager.LoadTableSchema(tableName, db);

            List<string> columns = new List<string>();
            List<string> parameters = new List<string>();

            foreach (var item in schema)
            {
                if (!item.Value.IsIdentity) //Identity的不需要插入
                {
                    columns.Add(item.Key);
                    parameters.Add("@" + item.Key);
                }
            }

            return string.Format(sql, tableName
                , CollectionUti.Join(",", columns)
                , CollectionUti.Join(",", parameters));
        } 
        #endregion

        #region CreateInsertDbCommand
        /// <summary>
        /// 创建Insert的DbCommand
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="tableName">表名</param>
        /// <param name="dal">相关的DAL</param>
        /// <param name="cmd">返回Command</param>
        /// <returns>是否成功</returns>
        /// <remarks>会在赋值时，检测值是否大于数据库的字段长度。如果非法插入赋值，就会抛出CheckInvalidValueException异常</remarks>
        public static SqlExecuteResult CreateInsertDbCommand(BaseEntity entity, string tableName
            , BaseDAL dal, out DbCommand cmd)
        {
            SqlExecuteResult result = null;
            cmd = dal.DataAccess.GetSqlStringCommand(CreateInsertSql(tableName, dal.DataAccess));

            //设置参数
            var schema = DAOManager.LoadTableSchema(tableName, dal.DataAccess);

            List<BaseDAL.CommandMapper> mappers = new List<BaseDAL.CommandMapper>();
            foreach (var item in schema)
            {
                mappers.Add(new BaseDAL.CommandMapper(item.Key));
            }

            var checkInvalids = dal.SetCommandValueWithCheck(entity, mappers, cmd, tableName);

            if (CollectionUti.HasItem(checkInvalids))
            {
                result = SqlExecuteResult.Create(checkInvalids);
            }

            return result;
        } 
        #endregion

        #region CreateDynamicSearchDbCommand
        /// <summary>
        /// 动态查询字段信息
        /// </summary>
        public class DynamicSearchColumnInfo
        {
            /// <summary>
            /// 构造函数
            /// </summary>
            public DynamicSearchColumnInfo()
            {
            }

            private string oper;
            /// <summary>
            /// 操作符
            /// </summary>
            /// <remarks>默认如果字符串遇到以%开头或结尾的，就用操作符Like；其他的用=</remarks>
            public string Operator 
            {
                get
                {
                    if (!string.IsNullOrEmpty(oper))
                    {
                        return oper;
                    }
                    if(Value == null)
                    {
                        return "=";
                    }
                    string str = Value.ToString();
                    return (!string.IsNullOrEmpty(str) && (str.StartsWith("%") || str.EndsWith("%"))) ? "LIKE" : "=";
                }
                set
                {
                    oper = value;
                }
            }

            /// <summary>
            /// 字段名
            /// </summary>
            public string ColumnName { get; set; }

            /// <summary>
            /// 查询值
            /// </summary>
            public object Value { get; set; }

            /// <summary>
            /// 字段名前缀
            /// </summary>
            public string Prefix { get; set; }

            private string whereString;
            /// <summary>
            /// 直接Where字符串
            /// </summary>
            /// <remarks>如果设置了，则ColumnName设置无效，只做parameterName的作用</remarks>
            public string WhereString
            {
                get
                {
                    return this.whereString;
                }
                set
                {
                    whereString = value;
                }
            }
        }

        /// <summary>
        /// 拼凑动态查询Sql
        /// </summary>
        /// <param name="sqlMainSql">Sql主句，用“{0}”标识组合条件放置的地方。如果没有，则直接拼接Where条件语句到语句最后</param>
        /// <param name="colInfos">搜索栏位信息</param>
        /// <param name="db">数据库访问工具类</param>
        /// <returns>Sql命令对象</returns>
        public static DbCommand CreateDynamicSearchDbCommand(string sqlMainSql
            , List<DynamicSearchColumnInfo> colInfos, Database db)
        {
            //先创建一个临时的DbCommand
            DbCommand cmd = db.GetSqlStringCommand(sqlMainSql);

            //如果所有值都为空，不需要添加条件，会默认添加一个“1=1”的条件进去。
            string whereStr = " 1=1 ";

            if (CollectionUti.HasItem(colInfos))
            {
                List<string> cacheParameters = new List<string>();

                List<string> whereStrs = new List<string>();
                foreach (var item in colInfos)
                {
                    DbType? dbType = GetDbType(item.Value);
                    if (dbType.HasValue)
                    {
                        //查找ParaName
                        string paraName = GetParameterName(item.ColumnName, cacheParameters);

                        if (string.IsNullOrEmpty(item.WhereString))
                        {
                            //自动创建Where条件语句

                            //添加rtrim，避免char类型形成的空格导致查询不匹配
                            string trimFunction = (dbType.Value == DbType.Int32
                                || dbType.Value == DbType.Decimal
                                || dbType.Value == DbType.Int64
                                || dbType.Value == DbType.Double) ? "" : "RTRIM";

                            if (string.IsNullOrEmpty(item.Prefix))
                            {
                                whereStrs.Add(string.Format(" {3}([{0}]) {1} @{2}"
                                    , item.ColumnName, item.Operator, paraName, trimFunction));
                            }
                            else
                            {
                                whereStrs.Add(string.Format(" {4}({2}.[{0}]) {1} @{3}"
                                    , item.ColumnName, item.Operator, item.Prefix, paraName, trimFunction));
                            }
                        }
                        else
                        {
                            //自定义的条件语句
                            whereStrs.Add(item.WhereString);
                        }

                        db.AddInParameter(cmd, paraName, dbType.Value, item.Value);
                    }
                }

                cacheParameters.Clear();

                if (whereStrs.Count > 0)
                {
                    whereStr = CollectionUti.Join(" AND ", whereStrs);
                    whereStrs.Clear();
                }

                //拼凑Sql
                if (sqlMainSql.IndexOf("{0}") < 0) 
                {
                    //如果没有{0}，自动加上Where {0}
                    sqlMainSql += " WHERE {0} ";
                }
            }

            //重新设置Command
            cmd.CommandText = string.Format(sqlMainSql, whereStr);

            return cmd;
        }

        /// <summary>
        /// 根据字段名获取对应的参数名称
        /// </summary>
        /// <param name="columnName">字段名称</param>
        /// <param name="cacheParameter">参数缓存列表，避免同样的字段返回同样的参数名称</param>
        /// <returns>参数名称</returns>
        private static string GetParameterName(string columnName, List<string> cacheParameter)
        {
            if (!cacheParameter.Contains(columnName))
            {
                cacheParameter.Add(columnName);
                return columnName;
            }
            else
            {
                //参数名後面+1
                string paraName = columnName + (cacheParameter.FindAll(_str => { return _str == columnName; }).Count + 1).ToString();
                cacheParameter.Add(columnName);
                return paraName;
            }
        }

        private static DbType? GetDbType(object val)
        {
            DbType? dbType = default(DbType?);
            if (val != null && !string.IsNullOrEmpty(val.ToString()))
            {
                if (val is int || val is int?)
                {
                    dbType = DbType.Int32;
                }
                else if (val is decimal || val is decimal?)
                {
                    dbType = DbType.Decimal;
                }
                else if (val is double || val is double?)
                {
                    dbType = DbType.Double;
                }
                else if (val is long || val is long?)
                {
                    dbType = DbType.Int64;
                }
                else if (val is DateTime || val is DateTime?)
                {
                    dbType = DbType.DateTime;
                }
                else
                {
                    dbType = DbType.String;
                }
            }
            return dbType;
        } 
        #endregion
    }
}