using GenerateCode_GEBrilliantFactory;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GenerateCode_WeiBen_WMS.DataBaseFactory
{
    public class SqlServerDataBaseServiceHandle : DataBaseServiceHandle
    {
        public override List<ColumnModel> GetColumnList(string tableName, string connStr)
        {
            string strSql = @" select
col.name as ColumnName,
t.name as DataType,
ep.value as Description,
col.max_length as DataLength,
col.is_nullable as IsNullable,

(
    select top 1 ind.is_primary_key from sys.index_columns ic
    left join sys.indexes ind
    on ic.object_id=ind.object_id
    and ic.index_id=ind.index_id
    and ind.name like 'PK_%'
    where ic.object_id=obj.object_id
    and ic.column_id=col.column_id
) as IsPrimaryKey,
col.Precision as MyPrecision,
col.Scale 
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
left join sys.extended_properties ep
on ep.major_id=obj.object_id
and ep.minor_id=col.column_id
and ep.name='MS_Description'
where obj.name='" + tableName + "'  ";


            List<ColumnModel> columnList = new List<ColumnModel>();
            try
            {
                DataSet ds = SqlHelper.Query(connStr, strSql);
                columnList = DataTableToList(ds.Tables[0]);
            }
            catch
            {
                throw;
            }
            finally
            {
            }
            return columnList;
        }

        public override List<ColumnModel> DataTableToList(DataTable dt)
        {
            try
            {
                List<ColumnModel> modelList = new List<ColumnModel>();
                int rowsCount = dt.Rows.Count;
                if (rowsCount > 0)
                {
                    ColumnModel model;
                    for (int n = 0; n < rowsCount; n++)
                    {
                        model = new ColumnModel();
                        if (dt.Rows[n]["Precision"].ToString() != "")
                        {
                            model.MyPrecision = int.Parse(dt.Rows[n]["Precision"].ToString());
                        }
                        if (dt.Rows[n]["Scale"].ToString() != "")
                        {
                            model.Scale = int.Parse(dt.Rows[n]["Scale"].ToString());
                        }
                        model.ColumnName = dt.Rows[n]["ColumnName"].ToString();
                        model.DataLength = dt.Rows[n]["DataLength"].ToString();
                        model.DataType = dt.Rows[n]["DataType"].ToString();

                        if (model.DataType.ToUpper() == "nvarchar".ToUpper())
                        {
                            if (model.DataLength != "-1")
                            {
                                model.DataLength = ((int)Convert.ToInt32(model.DataLength) / 2).ToString();
                            }
                        }

                        model.Description = dt.Rows[n]["Description"].ToString();
                        if (dt.Rows[n]["IsNullable"].ToString() != "")
                        {
                            if ((dt.Rows[n]["IsNullable"].ToString() == "1") || (dt.Rows[n]["IsNullable"].ToString().ToLower() == "true"))
                            {
                                model.IsNullable = true;
                            }
                            else
                            {
                                model.IsNullable = false;
                            }
                        }
                        if (dt.Rows[n]["IsPrimaryKey"].ToString() != "")
                        {
                            if ((dt.Rows[n]["IsPrimaryKey"].ToString() == "1") || (dt.Rows[n]["IsPrimaryKey"].ToString().ToLower() == "true"))
                            {
                                model.IsPrimaryKey = true;
                            }
                            else
                            {
                                model.IsPrimaryKey = false;
                            }
                        }

                        modelList.Add(model);
                    }
                }
                return modelList;
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}