using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OT.COM.ArsenalDB;
using OT.COM.LogisticsUtil;
using OT.COM.SignalerMessage;
using SoldierData;
using SoldierDataEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using SoldierData.syserp;

namespace CounsellorBL
{
    public class DBService : ServiceBase
    {

        protected CustomizeDBMgr cdbm = new CustomizeDBMgr();
        protected ArsenalDBMgr adbm = new ArsenalDBMgr();

        public ArsenalDBMgr ArsenalDBMgrInst
        {
            get { return adbm; }
        }

        public int NumPerPage { get; set; }

        public DBService()
            : base()
        {
            NumPerPage = 1000;

            // 
            if(Command.SingleConnection == null)
            {
                Command.SetupSingleConnection(cdbm.SingleConnection);
            }
        }

        public TableInfo GetMasterDBTableInfo(Type i_t)
        {
            TableInfo tiRes = cdbm.GetTableInfo(i_t);
            return tiRes;
        }

        protected string getViewModelByEntityName(string i_sTypeName, out object o_ebEntity)
        {
            string sMsg = null;
            object oWhere = null;
            try
            {
                ClassHelper ch = new ClassHelper();

                string sEntityName = null;
                int nIdx = i_sTypeName.IndexOf(".");

                if (nIdx == -1)
                {
                    sEntityName = string.Format("CounsellorBL.ViewModels.master.ViewModel_{0},CounsellorBL", i_sTypeName);
                }
                else
                {
                    string sCatelog = i_sTypeName.Substring(0, nIdx);
                    string sSubEntityName = i_sTypeName.Substring(nIdx + 1);

                    sEntityName = string.Format("CounsellorBL.ViewModels.{0}.ViewModel_{1},CounsellorBL", sCatelog, sSubEntityName);
                }


                sMsg = ch.GetInstByFullName(sEntityName, out oWhere);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            o_ebEntity = oWhere;

            return sMsg;
        }

        protected string getEntity(string i_sEntityName, out object o_ebEntity)
        {
            string sMsg = null;
            object oWhere = null;
            try
            {
                ClassHelper ch = new ClassHelper();

                string sEntityName = cdbm.GetFullEntityName(i_sEntityName);



                sMsg = ch.GetInstByFullName(sEntityName + ",SoldierData", out oWhere);  //錯誤時回傳No Type  或Create Type Fail錯誤
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            o_ebEntity = oWhere;

            return sMsg;
        }

        /// <summary>
        /// Session Validation
        /// </summary>
        /// <param name="i_sToken"></param>
        /// <param name="i_bRenewExpireTime"></param>
        /// <returns></returns>
        private CResponseMessage _checkTokenValid(string i_sToken, bool i_bRenewExpireTime = false)
        {
            CResponseMessage crm = null;
            string sMsg = null;

            do
            {
                WhereNode wn = new WhereNode(otb_session.CN_GUID, WhereNode.EColumnOperation.EOT_EQ, typeof(otb_session), i_sToken);
                Command cSelectSeesion = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(otb_session)), null, wn);

                otb_session sCur = adbm.RunQuerySingleORM<otb_session>(cSelectSeesion);

                if (null == sCur)
                {
                    sMsg = BaseExceptionWord.ex000023; //SESSION_NO_SESSION
                    break;
                }

                if (new Util().GetSettingInt("ExpireTimeMinute") < DateTime.Now.Subtract(sCur.modify_date).TotalMinutes)
                {
                    sMsg = BaseExceptionWord.ex000024; //SESSION_EXPIRED SESSION過期
                    break;
                }

                crm = new CResponseMessage() { RESULT = EResponseResult.RR_TRUE };

                crm.DATA.Add(BLWording.SESSION_USER_ID, sCur.create_user_guid);

                if (true == i_bRenewExpireTime)
                {
                    otb_session sModifyData = new otb_session() { modify_date = DateTime.Now };

                    WhereNode wnUpdate = new WhereNode(otb_session.CN_GUID, WhereNode.EColumnOperation.EOT_EQ, typeof(otb_session), i_sToken);
                    Command cUpdate = Command.SetupUpdateCmd(GetMasterDBTableInfo(typeof(otb_session)), sModifyData, wnUpdate);
                    adbm.RunEditSingleCmd(cUpdate);
                }
            }
            while (false);

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg);
            }

            return crm;
        }

        protected CResponseMessage checkTokenWithCRequestMessage(CRequestMessage i_crm, out Dictionary<string, object> o_oDicData)
        {
            CResponseMessage crm = null;
            string sMsg = null;

            Dictionary<string, object> dicFormData = new Dictionary<string, object>();
            try
            {
                do
                {
                    foreach (string sKey in i_crm.DATA.Keys)
                    {
                        dicFormData.Add(sKey, i_crm.DATA[sKey]);
                    }



                    //if (false == dicFormData.Keys.Contains(BLWording.TOKEN))
                    if (i_crm.TOKEN == null)
                    {
                        sMsg = BaseExceptionWord.ex000025; //NO TOKEN
                        break;
                    }

                    crm = this._checkTokenValid(i_crm.TOKEN, true);

                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            o_oDicData = dicFormData;
            return crm;
        }

        /// <summary>
        /// For Query result
        /// </summary>
        protected class queryResponse
        {
            public long TotalCount { get; set; }
            public long CurrentCount { get; set; }

            public List<Dictionary<string, object>> Records { get; set; }

            public queryResponse(QueryDataSet i_qds)
            {
                List<Dictionary<string, object>> ldicData = new List<Dictionary<string, object>>();
                DataTable dt = i_qds.DATA.Tables[0];
                DataColumnCollection dcc = dt.Columns;
                int nSNCount = 1;

                this.TotalCount = i_qds.Total;

                this.CurrentCount = dt.Rows.Count;

                foreach (DataRow dr in dt.Rows)
                {
                    Dictionary<string, object> dicData = new Dictionary<string, object>();
                    dicData.Add("SN", nSNCount);
                    foreach (DataColumn dc in dcc)
                    {
                        object oData = dr[dc.ColumnName];
                        string sColumnName = dc.ColumnName.ToLower();

                        //dicData.Add(sColumnName, EntityUtil.Removepadding(sData, sColumnName, i_qds.DataType));

                        if (oData is string)
                        {
                            string sData = oData.ToString();
                            dicData.Add(sColumnName, sData.TrimEnd());
                        }
                        else if (oData is DateTime)
                        {
                            dicData.Add(sColumnName, ((DateTime)(oData)).ToString("yyyy/MM/dd HH:mm:ss"));
                        }
                        else
                        {
                            dicData.Add(sColumnName, oData.ToString());
                        }

                    }

                    ldicData.Add(dicData);
                    nSNCount++;
                }
                this.Records = ldicData;
            }
        }

        /// <summary>
        /// Get table row
        /// </summary>
        /// <param name="i_crm"></param>
        /// <returns></returns>
        public CResponseMessage GetInfoDataBase(string sEntityName, CRequestMessage i_crm, EntityBase ebDisplay)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;
            ClassHelper ch = new ClassHelper();
            int nNumOfPage = -1;
            int nPageIdx = -1;


            try
            {
                do
                {
                    string sSearchMode = _fetchString(i_crm, BLWording.SEARCHMODE);

                    if (null == sSearchMode)
                    {
                        sMsg = BaseExceptionWord.ex000026; //NO SEARCH Mode
                        break;
                    }

                    if (sSearchMode == BLWording.SEARCHMODE_RECORDIDX)
                    {
                        nNumOfPage = 1;
                        string sIDX = _fetchString(i_crm, BLWording.SEARCHMODE_RECORDIDX);
                        if (null == sIDX || Int32.TryParse(sIDX, out nPageIdx) == false)
                        {
                            sMsg = BaseExceptionWord.ex000027; //NO RECORDIDX
                            break;
                        }
                    }
                    /*else if (sSearchMode == BLWording.SEARCHMODE_FID)
                    {
                        int nID = -1;
                        string sFID = _fetchString(i_crm, BLWording.SEARCHMODE_FID);

                        if (null == sFID && Int32.TryParse(sFID, out nID))
                        {                            
                            sMsg = "NO COMMONPK";
                            break;
                        }
                        i_wnWhere.SetValue(BLWording.COMMONPK, Int32.Parse(sFID));
                    }*/
                    else if (sSearchMode == BLWording.SEARCHMODE_PAGEIDX)
                    {
                        string sPageIDX = _fetchString(i_crm, BLWording.SEARCHMODE_PAGEIDX);
                        if (null == sPageIDX || Int32.TryParse(sPageIDX, out nPageIdx) == false)
                        {
                            sMsg = BaseExceptionWord.ex000028; //NO PAGEIDX
                            break;
                        }

                        string sNumPerPage = _fetchString(i_crm, BLWording.SEARCHMODE_NUMPERGAGE);
                        if (null == sPageIDX || Int32.TryParse(sNumPerPage, out nNumOfPage) == false)
                        {
                            sMsg = BaseExceptionWord.ex000029; //NO NUMPERPAGE
                            break;
                        }
                    }

                    string sToken = _fetchString(i_crm, BLWording.TOKEN);

                    //#if !DEBUG
                    //                    Dictionary<string, object> dicFormData = null;

                    //                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);


                    //                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    //                    {
                    //                        sMsg = crmCheckToken.MSG;
                    //                        break;
                    //                    }
                    //#endif
                    Command cSelect = null;

                    if (i_crm.DATA.ContainsKey(BLWording.BLOCKS))
                    {
                        List<QueryJsonElement> lBlocks = null;

                        if (i_crm.DATA[BLWording.BLOCKS] is JArray)
                        {
                            JArray joBlocks = i_crm.DATA[BLWording.BLOCKS] as JArray;
                            lBlocks = joBlocks.ToObject<List<QueryJsonElement>>();
                        }
                        else if (i_crm.DATA[BLWording.BLOCKS] is List<QueryJsonElement>)
                        {
                            lBlocks = i_crm.DATA[BLWording.BLOCKS] as List<QueryJsonElement>;
                        }
                        else
                        {
                            sMsg = BaseExceptionWord.ex000030;//NO SUPPORT BLOCKS
                            break;
                        }


                        sMsg = MakeSelectJoinByBlocks(lBlocks, out cSelect);
                    }
                    else if (i_crm.DATA.ContainsKey(BLWording.DATA))
                    {
                        JObject joDic = i_crm.DATA[BLWording.DATA] as JObject;
                        Dictionary<string, object> dicSO = joDic.ToObject<Dictionary<string, object>>();

                        Dictionary<string, string> dicSelect = new Dictionary<string, string>();

                        foreach (string sKey in dicSO.Keys)
                        {
                            object o = dicSO[sKey];
                            if (o != null)
                            {
                                string s = dicSO[sKey].ToString();
                                if (s.Trim().Length != 0)
                                {
                                    if (ebDisplay.GetType().GetProperty(sKey) != null)
                                    {
                                        dicSelect.Add(sKey, s);
                                    }
                                }
                            }
                        }

                        cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(ebDisplay.GetType()), ebDisplay, dicSelect);
                    }
                    else
                    {
                        cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(ebDisplay.GetType()), ebDisplay);
                    }

                    if (sMsg != null)
                    {
                        break;
                    }

                    //nNumOfPage = reRangeNumOfPage(nNumOfPage);
                    QueryDataSet qds = adbm.RunQueryDataSet(cSelect, nPageIdx, nNumOfPage);

                    if (qds.IsSuccess == true)
                    {
                        crmRes = new CSuccessResponseMessage(null, i_crm);
                        queryResponse qr = new queryResponse(qds);
                        crmRes.DATA.Add(BLWording.ENTITYS, qr);
                    }
                    else
                    {
                        sMsg = qds.ErrorCode;
                    }

                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crmRes = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crmRes;
        }

     


      


        public string MakeSelectJoinByBlocks(List<QueryJsonElement> i_lBlocks, out Command o_cSelect)
        {
            string sMsg = null;
            Command cTemp = null;

            do
            {
                if (i_lBlocks == null || i_lBlocks.Count() == 0)
                {
                    sMsg = BaseExceptionWord.ex000032; //NO BLOCK
                    break;
                }

                convertWherenode(ref i_lBlocks);

                QueryJson qj = new QueryJson();

                qj.AddBlock(i_lBlocks.ToArray());

                sMsg = qj.MakeCommand(cdbm.GetTableInfo(new CustomizeDBMgr().GetEntityType(i_lBlocks[0].table)), out cTemp);
            }
            while (false);

            o_cSelect = cTemp;
            return sMsg;
        }

        protected void convertWherenode(ref List<QueryJsonElement> io_data)
        {
            ClassHelper ch = new ClassHelper();

            for (int i = 0; i < io_data.Count(); i++)
            {
                QueryJsonElement qjs = io_data[i];

                Type t = new CustomizeDBMgr().GetEntityType(qjs.table);

                qjs.databaseinfo = t.FullName.Split('.')[1];

                WhereNode wnDicwherecolsCompile = Command.whereDictionary2WhereNode(GetMasterDBTableInfo(t), qjs.dicwherecols);

                if (qjs.wherecols == null)
                {
                    qjs.wherecols = wnDicwherecolsCompile;
                }
                else
                {
                    if (wnDicwherecolsCompile != null)
                    {
                        qjs.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, qjs.wherecols, wnDicwherecolsCompile);
                    }
                }
            }
        }

        /// <summary>
        /// Get table row
        /// </summary>
        /// <param name="i_crm"></param>
        /// <returns></returns>
        public CResponseMessage GetInfoData(CRequestMessage i_crm)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;

            try
            {
                do
                {
                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000032;// NO MATCHED ENTITY
                        break;
                    }

                    object oWhere = null;

                    sMsg = getEntity(sEntityName, out oWhere);

                    if (sMsg != null)
                    {
                        break;
                    }

                    crmRes = GetInfoDataBase(sEntityName, i_crm, oWhere as EntityBase);

                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crmRes = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crmRes;
        }

        protected string makeWhereNode(CRequestMessage i_crm, Type tRun, Dictionary<string, object> dicFormData, ClassHelper ch, ref WhereNode io_wn)
        {
            string sMsg = null;

            try
            {
                do
                {
                    EntityBase ebWhere = ch.GetInstByType(tRun) as EntityBase;
                    Dictionary<string, object> dicInput = new Dictionary<string, object>();
                    List<WhereNode> lwnWhere = new List<WhereNode>();

                    io_wn = null;

                    JObject jo = null;
                    // Support Ethan request: Delete/Update detail but not PK
                    bool bUsePK = true;


                    if (dicFormData.ContainsKey(BLWording.WHEREDATA))
                    {
                        bUsePK = false;
                        jo = dicFormData[BLWording.WHEREDATA] as JObject;
                    }
                    else if (dicFormData.ContainsKey(BLWording.ORIGINDATA))
                    {
                        jo = dicFormData[BLWording.ORIGINDATA] as JObject;
                    }
                    else
                    {
                        jo = dicFormData[BLWording.DATA] as JObject;
                    }

                    foreach (JProperty property in jo.Properties())
                    {
                        JToken jv = property.Value;

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

                    }


                    PropertyInfo[] pis = tRun.GetProperties();

                    foreach (PropertyInfo pi in pis)
                    {
                        System.Attribute attr = System.Attribute.GetCustomAttribute(pi, typeof(ColumnMiscAttribute));
                        bool bSystemColumn = false; // Default: Not System Column
                        bool bPK = false;


                        if (null != attr)
                        {
                            ColumnMiscAttribute cma = attr as ColumnMiscAttribute;
                            DBColumnInfo dbi = cma.Data;
                            bSystemColumn = dbi.DescriptionExtension != null && dbi.DescriptionExtension.SysColumn;
                            bPK = dbi.IsPK;
                        }

                        if (dicInput.ContainsKey(pi.Name))
                        {
                            object oValue = dicInput[pi.Name];

                            if (bUsePK == true)
                            {
                                if (bPK == true)
                                {
                                    if (null != oValue)
                                    {
                                        //pi.SetValue(ebWhere, ch.ConvertValue(pi, oValue), null);
                                        WhereNode wnTemp = new WhereNode(pi.Name, WhereNode.EColumnOperation.EOT_EQ, tRun, ch.ConvertValue(pi, oValue));
                                        lwnWhere.Add(wnTemp);
                                    }
                                }
                            }
                            else
                            {
                                if (null != oValue)
                                {
                                    WhereNode wnTemp = new WhereNode(pi.Name, WhereNode.EColumnOperation.EOT_EQ, tRun, ch.ConvertValue(pi, oValue));
                                    lwnWhere.Add(wnTemp);
                                }
                            }
                        }
                    }

                    io_wn = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            return sMsg;
        }

        public CResponseMessage EraseEntity(CRequestMessage i_crm)
        {
            CResponseMessage crm = null;
            string sMsg = null;

            try
            {
                do
                {
                    ClassHelper ch = new ClassHelper();

                    Dictionary<string, object> dicFormData = null;

                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);

                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    {
                        sMsg = crmCheckToken.MSG;
                        break;
                    }

                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000033;//NO MATCHED7 ENTITY
                        break;
                    }

                    object oData = null;

                    sMsg = getEntity(sEntityName, out oData);

                    if (null != sMsg)
                    {
                        break;
                    }

                    EntityBase ebData = oData as EntityBase;



                    // Only return valid data
                    if (i_crm.DATA.ContainsKey(BLWording.ACTIONDATA))
                    {
                        JObject oActionData = i_crm.DATA[BLWording.ACTIONDATA] as JObject;
                        Dictionary<string, string> dicSS = oActionData.ToObject<Dictionary<string, string>>();
                        ebData.SetValue(otb_user.CN_ACTIVE_FLAG, dicSS[BLWording.MARKVALUE]);
                    }
                    else
                    {
                        ebData.SetValue(otb_user.CN_ACTIVE_FLAG, "Y");
                    }

                    WhereNode wnWhere = null;

                    sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);

                    if (null != sMsg)
                    {
                        break;
                    }


                    Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);
                    int nRes = adbm.RunEditSingleCmd(c);

                    if (0 == nRes)
                    {
                        sMsg = BaseExceptionWord.ex000034; //MARK FAIL
                        break;
                    }

                    crm = new CSuccessResponseMessage(null, i_crm);
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }

        public CResponseMessage DeleteEntity(CRequestMessage i_crm)
        {
            CResponseMessage crm = null;
            Util u = new Util();
            ClassHelper ch = new ClassHelper();
            string sMsg = null;

            try
            {
                do
                {
                    Dictionary<string, object> dicFormData = null;

                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);

                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    {
                        sMsg = crmCheckToken.MSG;
                        break;
                    }

                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
                        break;
                    }

                    object oData = null;

                    sMsg = getEntity(sEntityName, out oData);

                    if (null != sMsg)
                    {
                        break;
                    }

                    WhereNode wnWhere = null;

                    sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);

                    if (null != sMsg)
                    {
                        break;
                    }

                    if (wnWhere.IsLeaf == false && wnWhere.ChildrenNodes.Count == 0)
                    {
                        sMsg = BaseExceptionWord.ex000013; //NO CONDITION
                        break;
                    }

                    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);

                    int nRes = adbm.RunEditSingleCmd(c);

                    /*if (0 == nRes)
                    {
                        sMsg = "REMOVE FAIL";
                        break;
                    }*/

                    crm = new CSuccessResponseMessage(null, i_crm);
                    crm.DATA.Add(BLWording.REMOVECOUNT, nRes);

                    //crm.DATA.Add(BLWording.COMMONPK, c.LastInsertIdentity);
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = u.GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }

        public CResponseMessage UpdateEntity(CRequestMessage i_crm)
        {
            Util u = new Util();
            ClassHelper ch = new ClassHelper();
            CResponseMessage crm = null;
            string sMsg = null;

            try
            {
                do
                {
                    Dictionary<string, object> dicFormData = null;

                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);

                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    {
                        sMsg = crmCheckToken.MSG;
                        break;
                    }

                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
                        break;
                    }

                    object oData = null;

                    sMsg = getEntity(sEntityName, out oData);
                    if (null != sMsg)
                    {
                        break;
                    }

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

                    JObject jo = dicFormData[BLWording.DATA] as JObject;

                    foreach (JProperty property in jo.Properties())
                    {
                        JToken jv = property.Value;

                        if (property.Name == otb_user.CN_CREATE_DATE)
                        {
                            continue;
                        }

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

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

                    if (0 != dicFillRes.Count)
                    {
                        sMsg = BaseExceptionWord.ex000022; //PARAMETER ERROR 原+=
                        break;
                    }

                    WhereNode wnWhere = null;

                    sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);

                    if (null != sMsg)
                    {
                        break;
                    }

                    Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);

                    // Fix me
                    //int nID = (int)crmCheckToken.DATA[BLWording.SESSION_USER_ID];
                    //ch.SetValueByPropertyName(ebData, tb_user.CN_FK_N_MODIFIER_USER_ID, nID);

                    int nRes = adbm.RunEditSingleCmd(c);

                    if (0 == nRes)
                    {
                        sMsg = BaseExceptionWord.ex000011; //UPDATE FAIL
                        break;
                    }

                    crm = new CSuccessResponseMessage(null, i_crm);
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = u.GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }

        public CResponseMessage CreateEntity(CRequestMessage i_crm)
        {
            CResponseMessage crm = null;
            string sMsg = null;
            Util u = new Util();
            ClassHelper ch = new ClassHelper();

            try
            {
                do
                {
                    Dictionary<string, object> dicFormData = null;

                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);

                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    {
                        sMsg = crmCheckToken.MSG;
                        break;
                    }

                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
                        break;
                    }

                    object oInsert = null;

                    sMsg = getEntity(sEntityName, out oInsert);
                    if (null != sMsg)
                    {
                        break;
                    }

                    Type tRun = oInsert.GetType();
                    EntityBase ebInsert = oInsert as EntityBase;
                    Dictionary<string, object> dicInput = new Dictionary<string, object>();

                    JObject jo = dicFormData[BLWording.DATA] as JObject;

                    foreach (JProperty property in jo.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; //PARAMETER ERROR 原+=
                        break;
                    }




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

                    // Fix me
                    //int nID = (int)crmCheckToken.DATA[BLWording.SESSION_USER_ID];
                    //ch.SetValueByPropertyName(ebInsert, tb_user.CN_FK_N_CREATOR_USER_ID, nID);
                    //ch.SetValueByPropertyName(ebInsert, tb_user.CN_FK_N_MODIFIER_USER_ID, nID);

                    int nRes = adbm.RunEditSingleCmd(c);

                    if (0 >= nRes)
                    {
                        sMsg = c.LastErrorCode;
                        break;
                    }

                    crm = new CSuccessResponseMessage(null, i_crm);

                    Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(oInsert.GetType()), null, Command.MakeWhereEntityBase2WhereNode(ebInsert));
                    QueryDataSet qds = adbm.RunQueryDataSet(cSelect);
                    queryResponse qr = new queryResponse(qds);
                    crm.DATA.Add(BLWording.ENTITYS, qr);

                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = u.GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }

        public CResponseMessage ExcelEntity(CRequestMessage i_crm)
        {
            string sMsg = null;
            string sFilePath = "";
            bool bStatus = false;
            CResponseMessage crm = null;
            List<Dictionary<string, object>> list = null;

            try
            {
                do
                {
                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (null == sEntityName)
                    {
                        sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
                        break;
                    }

                    object oWhere = null;

                    sMsg = getEntity(sEntityName, out oWhere);

                    if (sMsg != null)
                    {
                        break;
                    }
                    string sdata = _fetchString(i_crm, "data") ?? "";
                    if (sdata != "")
                    {
                        if (i_crm.DATA["data"] is JArray)
                        {
                            JArray joData = i_crm.DATA["data"] as JArray;
                            list = joData.ToObject<List<Dictionary<string, object>>>();
                        }
                    }
                    else
                    {
                        crm = GetInfoDataBase(sEntityName, i_crm, oWhere as EntityBase);
                        queryResponse data = (queryResponse)crm.DATA[BLWording.ENTITYS];
                        list = data.Records;
                    }
                    bStatus = CreateExcelFiles(list, i_crm, out sFilePath);
                    crm = new CSuccessResponseMessage(null, i_crm);
                    crm.DATA.Add(BLWording.FILESTATUS, bStatus);
                    crm.DATA.Add(BLWording.FILEPATH, sFilePath);
                    //Directory.Delete(strFileName);
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }

        public bool CreateExcelFiles(List<Dictionary<string, object>> i_dicData, CRequestMessage i_crm, out string o_sFilePath)
        {
            string sFilePath = null;
            string sFileName = null;
            bool bStatus = false;
            int imaxcell = 0;

            do
            {
                Dictionary<string, object> items = null;
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("sheet1");
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                IRow dataRow = sheet.CreateRow(0);
                if (i_crm.DATA.ContainsKey(BLWording.ITEMS))
                {
                    JObject joItems = i_crm.DATA[BLWording.ITEMS] as JObject;
                    items = joItems.ToObject<Dictionary<string, object>>();
                }

                // Workarond: Forward capbility for DBService.ExcelEntity
                if (items == null && i_crm.DATA.ContainsKey(BLWording.DATA))
                {
                    JObject dic = i_crm.DATA[BLWording.DATA] as JObject;
                    JObject joItems = dic[BLWording.ITEMS] as JObject;
                    if (null == joItems)
                    {
                        break;
                    }
                    items = joItems.ToObject<Dictionary<string, object>>();
                }

                //填充表头
                if (items != null)
                {
                    int index = 0;
                    foreach (string sKey in items.Keys)
                    {
                        object sValue = items[sKey];
                        string s = sValue.ToString() ?? "";
                        if (s != "")
                        {
                            ICell cell = dataRow.CreateCell(index);
                            cell.CellStyle = style;
                            cell.SetCellValue(s);
                            index++;
                        }
                    }
                }
                //填充内容
                string sJsonKey = _fetchString(i_crm, "sjsonkey");
                string scartonkey = _fetchString(i_crm, "scartonkey");
                sJsonKey = sJsonKey ?? "";
                scartonkey = scartonkey ?? "";
                int cellIndex = 0;
                foreach (Dictionary<string, object> _list in i_dicData)
                {
                    if (_list != null)
                    {
                        dataRow = sheet.CreateRow(dataRow.RowNum + 1);
                        imaxcell = cellIndex > imaxcell ? cellIndex : imaxcell;
                        cellIndex = 0;
                        foreach (string sKey in items.Keys)
                        {
                            if (_list.Keys.Contains(sKey))
                            {
                                object sValue = _list[sKey];
                                string s = sValue.ToString() ?? "";
                                if (sKey != sJsonKey)
                                {
                                    ICell cell = dataRow.CreateCell(cellIndex);
                                    cell.CellStyle = style;
                                    if (sKey == scartonkey)
                                    { //箱號當為0時顯示為空
                                        cell.SetCellValue((s == "0" ? "" : s));
                                    }
                                    else
                                    {
                                        cell.SetCellValue(s);
                                    }
                                    cellIndex++;
                                }
                                else
                                {
                                    Dictionary<string, object> dicData = new Dictionary<string, object>();
                                    JObject jsonObj = (JObject)JsonConvert.DeserializeObject(s);
                                    Dictionary<string, object> jsonData = jsonObj.ToObject<Dictionary<string, object>>();
                                    dicData = jsonData;
                                    //if (jsonData["scan_type_flag"].ToString() == "1")
                                    //{
                                    //    JArray jDatas = jsonData["ScanDatas"] as JArray;
                                    //    jsonData = jDatas.Count > 0 ? getToExcelData(jDatas) : new Dictionary<string, object>();
                                    //}
                                    foreach (string _sKey in jsonData.Keys)
                                    {
                                        if (_sKey.ToLower().IndexOf("_scan_sval") > -1)
                                        {//整合性報表特殊處理（公用部分走上邊部分）
                                            object sJson = jsonData[_sKey];
                                            string sVal = sJson.ToString() ?? "";
                                            ICell cell = dataRow.CreateCell(cellIndex);
                                            cell.CellStyle = style;
                                            cell.SetCellValue(sVal);
                                            cellIndex++;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                //自適應
                for (int cellindex = 0; cellindex <= imaxcell; cellindex++)
                {
                    sheet.AutoSizeColumn(cellindex, true);
                }
                sFilePath = "Uploads/ExpExcel/";
                sFileName = _fetchString(i_crm, "filename");
                sFileName = (sFileName ?? "") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                string sSeverPath = AppDomain.CurrentDomain.BaseDirectory + "/" + sFilePath;
                string sFileFullName = sSeverPath + "/" + sFileName;
                if (!Directory.Exists(sSeverPath))
                {
                    Directory.CreateDirectory(sSeverPath);
                }
                //保存
                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(sFileFullName, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                        bStatus = true;
                    }
                }
            }
            while (false);

            o_sFilePath = sFilePath + sFileName;
            return bStatus;
        }
        /// <summary>
        /// 整合性報表案例1json字串處理
        /// </summary>
        /// <param name="i_crm">With given table name</param>
        /// <returns>list of property</returns>
        public Dictionary<string, object> getToExcelData(JArray i_jaData)
        {
            Dictionary<string, object> dicData = i_jaData.ToObject<Dictionary<string, object>>();
            if (i_jaData.Count > 0)
            {
                List<Dictionary<string, object>> lsDic = i_jaData.ToObject<List<Dictionary<string, object>>>();
                foreach (Dictionary<string, object> dic in lsDic)
                {
                    foreach (string sKey in dic.Keys)
                    {
                        if (sKey.ToLower().IndexOf("_scan_sval") > -1)
                        {
                            if (dicData[sKey] != null)
                            {
                                dicData[sKey] += ";" + dic[sKey];
                            }
                            else
                            {
                                dicData.Add(sKey, dic[sKey]);
                            }
                        }
                    }

                }
            }
            return dicData;
        }


        /// <summary>
        /// To fetch column info from a given table name
        /// </summary>
        /// <param name="i_crm">With given table name</param>
        /// <returns>list of property</returns>
        public CResponseMessage GetEntityProperties(CRequestMessage i_crm)
        {
            string sMsg = null;
            CResponseMessage crm = null;

            try
            {
                do
                {
                    Dictionary<string, object> dicFormData = null;

                    CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);

                    if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
                    {
                        sMsg = crmCheckToken.MSG;
                        break;
                    }

                    string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);

                    if (sEntityName == null)
                    {
                        sMsg = BaseExceptionWord.ex000035; //NO ENTITY
                        break;
                    }

                    Type tTest = new CustomizeDBMgr().GetEntityType(sEntityName);

                    if (tTest == null)
                    {
                        sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
                        break;
                    }


                    PropertyInfo[] pis = tTest.GetProperties();
                    List<DBColumnInfo> lColumns = new List<DBColumnInfo>();

                    foreach (PropertyInfo pi in pis)
                    {
                        System.Attribute attr = System.Attribute.GetCustomAttribute(pi, typeof(ColumnMiscAttribute));

                        if (null != attr)
                        {
                            ColumnMiscAttribute cma = attr as ColumnMiscAttribute;
                            DBColumnInfo dbi = cma.Data;

                            if (dbi != null)
                            {
                                lColumns.Add(dbi);
                            }
                        }
                    }
                    lColumns = lColumns.OrderBy(o => o.COLUMN_NAME).ToList();
                    crm = new CSuccessResponseMessage(null, i_crm);
                    crm.DATA.Add(BLWording.LISTCOLUMNS, lColumns);
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = new Util().GetLastExceptionMsg(ex);
            }

            if (null != sMsg)
            {
                crm = new CErrorResponseMessage(sMsg, i_crm);
            }

            return crm;
        }


        public static string GetLastErrorCode(Command i_cCmd)
        {
            return (i_cCmd.IsSuccess == false) ? i_cCmd.LastErrorCode : null;
        }

        public static string GetLastErrorCode(List<Command> i_lcCmds)
        {
            string sRes = null;

            if (i_lcCmds.Find(c => (c.IsSuccess == false)) != null)
            {
                sRes = i_lcCmds.Find(c => (c.IsSuccess == false)).LastErrorCode;
            }

            return sRes;
        }
    }
}