using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OT.COM.ArsenalDB;
using OT.COM.LogisticsUtil;
using OT.COM.SignalerMessage;
using SoldierData.syserp;
using SoldierDataEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CounsellorBL
{
    /// <summary>
    /// 類別名稱: DBhelper
    /// 類別說明  新增，修改，刪除方法
    /// 起始作者: John
    ///	起始日期: 2016/06/04
    ///	最新修改人: John
    ///	最新修日期: 2016/06/14
    /// </summary>
    public class DBhelper : DBService
    {
        #region getQryListCommand
        /// <summary>
        /// 函式名稱:getQryListCommand
        /// 函式說明:獲取批次查詢Command
        /// 起始作者:Justin
        /// 起始日期:2016/10/05
        /// 最新修改人:Justin
        /// 最新日期:2016/10/05
        /// </summary>
        /// <param name="i_crmInput">
        /// 參數說明i_crm(Object):所需查詢資料...
        /// 參數說明i_sTbName:Tabel名稱
        /// 參數說明o_sMsg:錯誤訊息
        /// </param>
        /// <returns>
        /// 回傳 lComd(Object)：新增狀態相關訊息
        /// </returns>
        protected List<Command> getQryListCommand(object i_dicData, string i_sTbName, out string o_sMsg, List<Dictionary<string, string>> i_ordercols = null, Dictionary<string, string> i_aliascols = null, bool i_distinct = false)
        {
            string sMsg = null;

            OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();
            Dictionary<string, string> dicData = null;
            List<Command> lComd = new List<Command>();
            if (i_dicData is JArray)
            {

                JArray jaData = i_dicData as JArray;
                foreach (JObject jo in jaData)
                {

                    object tbData = null;
                    sMsg = getEntity(i_sTbName, out tbData); //傳出來一個物件
                    Type Tabletype = tbData.GetType();
                    EntityBase ebSelect = tbData as EntityBase;

                    if (null != sMsg)
                    {
                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;
                    }

                    dicData = jo["wheredata"].ToObject<Dictionary<string, string>>();
                    // WhereNode wnWhere = Command.whereDictionary2WhereNode(GetMasterDBTableInfo(Tabletype), dicData);
                    Command c = Command.SetupSelectCmdByParam(GetMasterDBTableInfo(tbData.GetType()), ebSelect, dicData, i_ordercols, i_aliascols, i_distinct);
                    lComd.Add(c);

                }


            }

            o_sMsg = sMsg;
            return lComd;
        }
        #endregion
        #region getAddListCommand
        /// <summary>
        /// 函式名稱:getAddListCommand
        /// 函式說明:獲取批次新增Command
        /// 起始作者:John
        /// 起始日期:2016/06/04
        /// 最新修改人:John
        /// 最新日期:2016/06/14
        /// </summary>
        /// <param name="i_crmInput">
        /// 參數說明i_crm(Object):所需新增資料...
        /// 參數說明i_sTbName:Tabel名稱
        /// 參數說明o_sMsg:錯誤訊息
        /// </param>
        /// <returns>
        /// 回傳 lComd(Object)：新增狀態相關訊息
        /// </returns>
        protected List<Command> getAddListCommand(object i_dicData, string i_sTbName, out string o_sMsg)
        {
            string sMsg = null;
            OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();

            List<Command> lComd = new List<Command>();
            if (i_dicData is JArray)
            {

                JArray jaData = i_dicData as JArray;
                foreach (JObject jo in jaData)
                {

                    Dictionary<string, object> dicData = jo.ToObject<Dictionary<string, object>>();
                    object oInsert = null;
                    sMsg = getEntity(i_sTbName, out oInsert);

                    if (null != sMsg)
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    EntityBase ebInsert = oInsert as EntityBase;
                    Dictionary<string, object> dicInput = new Dictionary<string, object>();
                    JObject jdata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.DATA].ToString());

                    foreach (JProperty property in jdata.Properties())
                    {

                        JToken jv = property.Value;
                        dicInput.Add(property.Name, jv.Value<string>());

                    }

                    Dictionary<string, string> dicFillRes = ebInsert.FillInsertData(dicInput);

                    if (0 != dicFillRes.Count)
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }
                    // Add User
                    Command c = Command.SetupInsertCmd(GetMasterDBTableInfo(oInsert.GetType()), ebInsert);
                    lComd.Add(c);

                }

            }

            o_sMsg = sMsg;
            return lComd;
        }
        #endregion

        #region getUpdListCommand
        /// <summary>
        /// 函式名稱:getUpdListCommand
        /// 函式說明:獲取批次修改Command
        /// 起始作者:John
        /// 起始日期:2016/06/04
        /// 最新修改人:John
        /// 最新日期:2016/06/14
        /// </summary>
        /// <param name="i_crmInput">
        /// 參數說明i_crm(Object):所需修改資料...
        /// 參數說明i_sTbName:Tabel名稱
        /// 參數說明o_sMsg:錯誤訊息
        /// </param>
        /// <returns>
        /// 回傳 lComd(Object)：修改狀態相關訊息
        /// </returns>
        protected List<Command> getUpdListCommand(object i_dicData, string i_sTbName, List<string> i_saUpdKeys, out string o_sMsg)
        {
            string sMsg = null;
            bool bIsHasPK = true;
            OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();


            List<Command> lComd = new List<Command>();
            if (i_dicData is JArray)
            {
                JArray jaData = i_dicData as JArray;
                foreach (JObject joData in jaData)
                {
                    Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                    object oData = null;
                    sMsg = getEntity(i_sTbName, out oData);

                    if (null != sMsg)
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    Type tRun = oData.GetType();
                    EntityBase ebData = oData as EntityBase;
                    EntityBase ebWhere = chTool.GetInstByType(tRun) as EntityBase;
                    Dictionary<string, object> dicInput = new Dictionary<string, object>();

                    if (!dicData.ContainsKey(BLWording.DATA))
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }
                    JObject jdata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.DATA].ToString());
                    foreach (JProperty property in jdata.Properties())
                    {

                        JToken jv = property.Value;
                        dicInput.Add(property.Name, jv.Value<string>());

                    }

                    Dictionary<string, string> dicFillRes = ebData.FillUpadateData(dicInput);

                    if (0 != dicFillRes.Count)
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    if (!dicData.ContainsKey(BLWording.WHEREDATA))
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    Dictionary<string, object> dicWhere = new Dictionary<string, object>();
                    JObject jwheredata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.WHEREDATA].ToString());
                    if (null != JProperty2Dic(jwheredata, ref dicWhere))
                    {
                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;
                    }

                    WhereNode wnWhere = null;
                    List<WhereNode> lwnWhere = new List<WhereNode>();
                    foreach (string sKey in i_saUpdKeys)
                    {
                        if (dicWhere.ContainsKey(sKey) == false)
                        {
                            bIsHasPK = false;
                            break;
                        }
                        if (dicWhere[sKey].ToString() == "" || dicWhere[sKey] == null)
                        {
                            bIsHasPK = false;
                            break;
                        }
                        WhereNode wn = new WhereNode(sKey, WhereNode.EColumnOperation.EOT_EQ, tRun, dicWhere[sKey]);
                        lwnWhere.Add(wn);
                    }
                    if (!bIsHasPK)
                    {
                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;
                    }

                    wnWhere = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
                    Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);
                    lComd.Add(c);

                }

            }

            o_sMsg = sMsg;
            return lComd;
        }
        #endregion

        #region getDelListCommand
        /// <summary>
        /// 函式名稱:getDelListCommand
        /// 函式說明:獲取批次刪除Command
        /// 起始作者:John
        /// 起始日期:2016/06/04
        /// 最新修改人:John
        /// 最新日期:2016/06/14
        /// </summary>
        /// <param name="i_crmInput">
        /// 參數說明i_crm(Object):所需刪除資料...
        /// 參數說明i_sTbName:Tabel名稱
        /// 參數說明o_sMsg:錯誤訊息
        /// </param>
        /// <returns>
        /// 回傳 lComd(Object)：刪除狀態相關訊息
        /// </returns>
        protected List<Command> getDelListCommand(CRequestMessage i_crm, object i_dicData, string i_sTbName, List<string> i_saDelKeys, out string o_sMsg)
        {
            string sMsg = null;
            bool bIsHasPK = true;
            OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();

            List<Command> lComd = new List<Command>();
            if (i_dicData is JArray)
            {

                JArray jaWhereData = i_dicData as JArray;
                foreach (JObject joData in jaWhereData)
                {

                    Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                    object oData = null;
                    sMsg = getEntity(i_sTbName, out oData);
                    Type tRun = oData.GetType();

                    if (null != sMsg)
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    if (!dicData.ContainsKey(BLWording.WHEREDATA))
                    {

                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;

                    }

                    Dictionary<string, object> dicWhere = new Dictionary<string, object>();
                    JObject jwheredata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.WHEREDATA].ToString());

                    foreach (JProperty property in jwheredata.Properties())
                    {

                        JToken jv = property.Value;
                        dicWhere.Add(property.Name, jv.Value<string>());

                    }

                    WhereNode wnWhere = null;
                    List<WhereNode> lwnWhere = new List<WhereNode>();
                    foreach (string sKey in i_saDelKeys)
                    {
                        if (dicWhere.ContainsKey(sKey) == false)
                        {
                            bIsHasPK = false;
                            break;
                        }
                        if (dicWhere[sKey].ToString() == "" || dicWhere[sKey] == null)
                        {
                            bIsHasPK = false;
                            break;
                        }
                        WhereNode wn = new WhereNode(sKey, WhereNode.EColumnOperation.EOT_EQ, tRun, dicWhere[sKey]);
                        lwnWhere.Add(wn);
                    }
                    if (!bIsHasPK)
                    {
                        sMsg = BaseExceptionWord.ex000022; //參數錯誤
                        break;
                    }

                    wnWhere = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());

                    TableInfo ti = GetMasterDBTableInfo(oData.GetType());
                    // Backup
                    string sBackupTable = _fetchString(i_crm, BLWording.CMDBACKUPENTITYTYPE);

                    if (string.IsNullOrEmpty(sBackupTable) == false)
                    {

                        ti.BackTable = new CustomizeDBMgr().GetEntityType(sBackupTable);

                    }
                    // Add User                   
                    Command c = Command.SetupDeleteCmd(ti, wnWhere);
                    lComd.Add(c);

                }

            }
            o_sMsg = sMsg;
            return lComd;
        }
        #endregion

    }

    public class DBhelper<T> : DBService where T : EntityBase, new()
    {
        #region 根據ID，獲取
        /// <summary>
        /// 函式名稱:QueryByPk
        /// 函式說明:根據ID，獲取 
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_sPk">Pk</param>
        /// <returns>單筆資料對象</returns>
        public T QueryByPk(string i_sPk)
        {
            T oEntity = new T();
            oEntity.SetValue(oEntity.PKNames[0],
                Convert.ChangeType(i_sPk, oEntity.GetPropertyType(oEntity.PKNames[0])));
            WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(oEntity);
            Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), new T(), wndPk);
            T oResult = this.adbm.RunQuerySingleORM<T>(cSelect);
            return oResult;
        }

        public T QueryEntity(T i_oEntity)
        {
            WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(i_oEntity);
            Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), new T(), wndPk);
            T oResult = this.adbm.RunQuerySingleORM<T>(cSelect);
            return oResult;
        }

        public IList<T> QueryByWhere(WhereNode i_oWhere)
        {
            T oEntity = new T();
            Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), oEntity, i_oWhere);
            IList<T> oResult = this.adbm.RunQueryList<T>(cSelect);
            return oResult;
        }
        #endregion

        #region 根據ID，刪除
        /// <summary>
        /// 函式名稱:DeleteByPk
        /// 函式說明:根據ID，刪除
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_sPk">Pk</param>
        /// <returns>變動的行數</returns>
        public int DeleteByPk(string i_sPk)
        {
            T oEntity = new T();
            oEntity.SetValue(oEntity.PKNames[0],
                Convert.ChangeType(i_sPk, oEntity.GetPropertyType(oEntity.PKNames[0])));
            return DeleteEntity(oEntity);
        }

        public int DeleteEntity(T i_oEntity)
        {
            WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(i_oEntity);
            Command cDelete = Command.SetupDeleteCmd(GetMasterDBTableInfo(typeof(T)), wndPk);
            int iUpdateRowCount = this.adbm.RunEditSingleCmd(cDelete);
            return iUpdateRowCount;
        }
        #endregion

        #region 插入
        /// <summary>
        /// 函式名稱:InsertEntity
        /// 函式說明:插入多筆，用於不同實體類
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_lsCmd">多筆新增資料集合</param>
        /// <returns>失敗返回0，成功返回大於0</returns>
        public int InsertEntity(List<Command> i_lsCmd)
        {
            int iUpdateRowCount = this.adbm.RunEditCmds(i_lsCmd);
            return iUpdateRowCount;
        }

        /// <summary>
        /// 函式名稱:GetinsertCmds
        /// 函式說明:產生批次新增Command
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_lsEntity"></param>
        /// <returns></returns>
        public List<Command> GetinsertCmds(IList<T> i_lsEntity)
        {
            List<Command> lsCmd = new List<Command>();
            foreach (T oEntity in i_lsEntity)
            {
                Command cInsert = Command.SetupInsertCmd(GetMasterDBTableInfo(typeof(T)), oEntity);
                lsCmd.Add(cInsert);
            }
            return lsCmd;
        }
        #endregion

        #region 更新
        /// <summary>
        /// 函式名稱:UpdateEntity
        /// 函式說明:批次更新
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_lsEntity"></param>
        /// <returns>變動的行數</returns>
        public int UpdateEntity(List<T> i_lsEntity)
        {
            List<Command> lsCommands = GetUpdateCmds(i_lsEntity);
            int iUpdateRowCount = this.adbm.RunEditCmds(lsCommands);
            return iUpdateRowCount;
        }

        /// <summary>
        /// 函式名稱:GetUpdateCmds
        /// 函式說明:產生批次新增Command
        /// 起始作者:John
        /// 起始日期:2016/06/27
        /// 最新修改人:John
        /// 最新日期:2016/06/27
        /// </summary>
        /// <param name="i_lsEntity"></param>
        /// <returns></returns>
        public List<Command> GetUpdateCmds(IList<T> i_lsEntity)
        {
            List<Command> lsCmd = new List<Command>();
            foreach (T oEntity in i_lsEntity)
            {
                WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(oEntity);
                Command cUpdate = Command.SetupUpdateCmd(GetMasterDBTableInfo(typeof(T)), oEntity, wndPk);
                lsCmd.Add(cUpdate);
            }
            return lsCmd;
        }
        #endregion
    }
}