GroupBy/04-程式設計/Trunk/GroupBuy/CounsellorBL.SYS/MemberService.cs


using CounsellorBL.Common;
using CounsellorBL.Helper;
using MonumentDefine;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OT.COM.ArsenalDB;
using OT.COM.LogisticsUtil;
using OT.COM.SignalerMessage;
using SoldierData;
using SoldierData.EnterprizeV4;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace CounsellorBL.BLStructure.SYS
{
    class MemberService : SingleDataTableTemplate<tb_sys_user>
    {
        const string calcolor = "calcolor";
        const string role_uid = "role_uid";
        public MemberService()
        {
            dgReadCommandGenerator = readCommandGenerator;
            dgReadCommandPostDataHandler = readCommandPostDataHandler;

            dgCreateCommandGenerator = createCommandGenerator;

            dgDeleteCommandGenerator = deleteCommandGenerator;

            dgUpdateCommandGenerator = updateCommandGenerator;
        }

        [Auth(false)]
        public new CResponseMessage Read(CRequestMessage i_crmInput) => base.Read(i_crmInput);

        protected string readCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out Command o_c,
            [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
            [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
            [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg;
            Command cRes = null;

            try
            {
                do
                {
                    List<string> lsEmpColumns = EntityBase.GetAllColumnName(typeof(tb_hr_employee));
                    Dictionary<string, object> dicWhereData = i_jaData[0][BLWording.WHEREDATA].ToObject<Dictionary<string, object>>();

                    // 取得condition
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
                    var lsGroup = ProjectHelper.GetUserGroup(i_crmInput);
                    /**開始組指令**/
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

                    QueryJsonElement qjeUser = lBlocks.GetInst();
                    qjeUser.table = tb_sys_user.TABLENAME;
                    qjeUser.displaycols = new List<string>() { tb_sys_user.CN_ACCOUNT, tb_sys_user.CN_UID };

                    List<WhereNode> wnQuery = new List<WhereNode>();

                    if (dicCondition.ContainsKey(tb_sys_user.CN_STATUS_FLAG))
                    {
                        wnQuery.Add(new WhereNode(tb_sys_user.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user), dicCondition[tb_sys_user.CN_STATUS_FLAG]));
                    }

                    if (dicCondition.ContainsKey(tb_sys_user.CN_ACCOUNT))
                    {
                        wnQuery.Add(new WhereNode(tb_sys_user.CN_ACCOUNT, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user), dicCondition[tb_sys_user.CN_ACCOUNT]));
                    }


                    if (wnQuery.Any())
                    {
                        qjeUser.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, wnQuery.ToArray());
                    }


                    QueryJsonElement qjeEmp = lBlocks.GetInst();
                    qjeEmp.table = tb_hr_employee.TABLENAME;
                    qjeEmp.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeEmp.jointable = qjeUser;
                    qjeEmp.joincols = new Dictionary<string, string>() {
                             {  tb_hr_employee.CN_UID,tb_sys_user.CN_UID }};
                    qjeEmp.displaycols = lsEmpColumns;
                    qjeEmp.dicwherecols = dicCondition;

                    QueryJsonElement qjeEnterCode = lBlocks.GetInst();
                    qjeEnterCode.table = tb_sys_user2entercode.TABLENAME;
                    qjeEnterCode.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeEnterCode.jointable = qjeUser;
                    qjeEnterCode.joincols = new Dictionary<string, string>() {
                             { tb_sys_user2entercode.CN_USER_UID, tb_sys_user.CN_UID }};
                    qjeEnterCode.displaycols = new List<string>() { tb_sys_user2entercode.CN_USER_ENTERCODE };
                    qjeEnterCode.aliascols = new Dictionary<string, List<string>>() {
                        { tb_sys_user2entercode.CN_USER_ENTERCODE, new List<string>(){ "confirm_entercode" }
                        } };

                    lBlocks.Add(qjeUser);
                    lBlocks.Add(qjeEmp);
                    lBlocks.Add(qjeEnterCode);

                    QueryJsonElement qje2Role = lBlocks.GetInst();
                    qje2Role.table = tb_sys_user2role.TABLENAME;
                    qje2Role.jointable = qjeUser;
                    qje2Role.jointype = QueryJsonElement.LEFT_JOIN;
                    qje2Role.joincols = new Dictionary<string, string>() {
                             {  tb_sys_user2role.CN_USER_UID, tb_sys_user.CN_UID }};
                    qje2Role.displaycols = new List<string>() { tb_sys_user2role.CN_ROLE_UID };
                    if (dicWhereData != null && dicWhereData.ContainsKey(role_uid))
                    {
                        qje2Role.wherecols = new WhereNode(tb_sys_user2role.CN_ROLE_UID,
                            WhereNode.EColumnOperation.EOT_EQ,
                            typeof(tb_sys_user2role),
                            dicWhereData[role_uid].ToString()
                            );
                    }
                    lBlocks.Add(qje2Role);

                    QueryJsonElement qjeEmp2Branch = lBlocks.GetInst();
                    qjeEmp2Branch.table = tb_hr_employee2branch.TABLENAME;
                    qjeEmp2Branch.jointable = qjeEmp;
                    qjeEmp2Branch.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeEmp2Branch.joincols = new Dictionary<string, string>() {
                             {  tb_hr_employee2branch.CN_UID, tb_hr_employee.CN_UID }};
                    qjeEmp2Branch.displaycols = new List<string>() { tb_hr_employee2branch.CN_BRANCH_UID };
                    lBlocks.Add(qjeEmp2Branch);

                    QueryJsonElement qjeBranch = lBlocks.GetInst();
                    qjeBranch.table = tb_grp_branch.TABLENAME;
                    qjeBranch.jointable = qjeEmp2Branch;
                    qjeBranch.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeBranch.joincols = new Dictionary<string, string>() {
                             {  tb_grp_branch.CN_UID, tb_hr_employee2branch.CN_BRANCH_UID }};
                    qjeBranch.displaycols = new List<string>() { tb_grp_branch.CN_GROUP_UID };
                    lBlocks.Add(qjeBranch);

                    QueryJsonElement qjeGroup = lBlocks.GetInst();
                    qjeGroup.table = tb_grp_group.TABLENAME;
                    qjeGroup.jointable = qjeBranch;
                    qjeGroup.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeGroup.joincols = new Dictionary<string, string>() {
                             {  tb_grp_group.CN_UID, tb_grp_branch.CN_GROUP_UID }};
                    if (i_crmInput.token != "guest")
                    {
                        qjeGroup.wherecols = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_group), lsGroup.ToArray());
                    }
                    lBlocks.Add(qjeGroup);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRead);

                    if (sMsg != null)
                    {
                        break;
                    }
                    cRes = cRead;
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(readCommandGenerator)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

            o_c = cRes;
            return sMsg;
        }

        class MemberModel : tb_hr_employee
        {
            public string account { get; set; }
            public string user_entercode { get; set; }
            public string role_uid { get; set; }
            public string group_uid { get; set; }
            public string branch_uid { get; set; }
            public string confirm_entercode { get; set; }
        }
        protected string readCommandPostDataHandler(CRequestMessage i_crmInput, ArsenalInterface i_aiArsenal, Command i_cCmd, JArray i_jaData, tb_sys_session i_sSessionUser, out object o_oReault,
             [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
             [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
             [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg = null;
            object oResultData = null;

            try
            {
                do
                {
                    List<MemberModel> qds = i_aiArsenal.RunQueryList<MemberModel>(i_cCmd);
                    if (!i_cCmd.IsSuccess)
                    {
                        sMsg = i_cCmd.LastErrorCode;
                        break;
                    }
                    oResultData = qds
                        .GroupBy(x => new
                        {
                            x.account,
                            x.uid,
                            x.user_entercode,
                            x.email,
                            x.status_flag,
                            x.name,
                            x.confirm_entercode,
                            x.personal_setting,
                            x.receive_branch_uid
                        })
                        .Select(x => new
                        {
                            x.Key.account,
                            x.Key.name,
                            x.Key.uid,
                            x.Key.user_entercode,
                            x.Key.confirm_entercode,
                            x.Key.email,
                            x.Key.status_flag,
                            x.Key.personal_setting,
                            x.Key.receive_branch_uid,
                            role_uid = x.Where(x => !string.IsNullOrEmpty(x.role_uid)).Select(x => x.role_uid).Distinct().ToList(),
                            group_uid = x.Where(x => !string.IsNullOrEmpty(x.group_uid)).Select(x => x.group_uid).ToList(),
                            branch_uid = x.Where(x => !string.IsNullOrEmpty(x.branch_uid)).Select(x => x.branch_uid).ToList()
                        }).ToList();

                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(readCommandPostDataHandler)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

            o_oReault = oResultData;
            return sMsg;
        }
        protected string createCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out List<Command> o_lResult, List<string> i_saQryContainKeys,
            [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
            [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
            [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    Dictionary<string, object> dicData = i_jaData[0][BLWording.DATA].ToObject<Dictionary<string, object>>();

                    dicData.Remove(tb_hr_employee.CN_UID);
                    string sUserUID = Guid.NewGuid().ToString();
                    // 新增帳號
                    tb_sys_user cNewUser = new tb_sys_user
                    {
                        uid = sUserUID,
                        account = dicData[tb_sys_user.CN_ACCOUNT].ToString()
                    };
                    lCmds.Add(Command.SetupInsertCmd(cNewUser));

                    // 新增個人資訊
                    tb_hr_employee cNewEmp = new tb_hr_employee();
                    cNewEmp.FillData(dicData);
                    cNewEmp.uid = sUserUID;

                    PersonalSetting personalSetting = new PersonalSetting();

                    if (dicData.ContainsKey(calcolor))
                    {
                        personalSetting.CalColor = dicData[calcolor].ToString();
                    }
                    personalSetting.Favorite = new List<string>() { "0d5bcacf-0958-443d-aab4-0f207327cce7", "70c34b69-56fe-499c-ada6-967457dd4026", "2255ae6a-e1cb-4e01-a225-e97a7c73c1d5", "bbd2d114-556b-4663-99bb-c4f7a2698077", "6e03e650-8a35-47e7-8144-2bb4334ab7a9" };
                    cNewEmp.personal_setting = JsonConvert.SerializeObject(personalSetting);   // 將物件序列化

                    lCmds.Add(Command.SetupInsertCmd(cNewEmp));

                    sMsg = EncryptHelper.Encrypt(dicData[tb_sys_user2entercode.CN_USER_ENTERCODE].ToString(), out string sEnc);
                    if (sMsg != null)
                    {
                        break;
                    }

                    // 新增密碼
                    tb_sys_user2entercode cNewEnterCode = new tb_sys_user2entercode
                    {
                        user_uid = sUserUID,
                        user_entercode = sEnc
                    };
                    lCmds.Add(Command.SetupInsertCmd(cNewEnterCode));

                    // 新增角色資訊
                    if (dicData.ContainsKey(role_uid))
                    {
                        var jaRole = dicData[role_uid] as JArray;
                        if (jaRole != null)
                        {
                            foreach (var role in jaRole)
                            {
                                tb_sys_user2role cNew = new tb_sys_user2role()
                                {
                                    user_uid = sUserUID,
                                    role_uid = role.ToString()
                                };
                                lCmds.Add(Command.SetupInsertCmd(cNew));
                            }
                        }
                    }

                    // 新增所屬分店
                    if (dicData.ContainsKey(tb_hr_employee2branch.CN_BRANCH_UID))
                    {
                        var jaBranch = dicData[tb_hr_employee2branch.CN_BRANCH_UID] as JArray;
                        foreach (var branch in jaBranch)
                        {
                            tb_hr_employee2branch cNew = new tb_hr_employee2branch()
                            {
                                uid = sUserUID,
                                branch_uid = branch.ToString()
                            };
                            lCmds.Add(Command.SetupInsertCmd(cNew));
                        }
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(createCommandGenerator)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

            o_lResult = lCmds;
            return sMsg;
        }

        protected string updateCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out List<Command> o_lResult, List<string> i_saQryContainKeys,
            [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
            [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
            [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    Dictionary<string, object> dicData = i_jaData[0][BLWording.DATA].ToObject<Dictionary<string, object>>();
                    Dictionary<string, object> dicWhereData = i_jaData[0][BLWording.WHEREDATA].ToObject<Dictionary<string, object>>();

                    string sUserUID = dicWhereData[tb_sys_user.CN_UID].ToString();
                    var data = i_jaData[0][BLWording.DATA] as JObject;
                    data.Remove(role_uid);

                    // 更新個人資訊 tb_hr_employee
                    tb_hr_employee cEmpData = new tb_hr_employee();
                    sMsg = valueAssignment(i_crmInput, cEmpData, data, out List<Command> lcUpdEmp);
                    if (sMsg != null)
                    {
                        break;
                    }
                    Command cEmp = Command.SetupUpdateCmd(cEmpData, new WhereNode(tb_hr_employee.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_hr_employee), sUserUID));
                    lCmds.Add(cEmp);


                    // 更新帳號資訊 tb_sys_user
                    tb_sys_user cUserData = new tb_sys_user();
                    sMsg = valueAssignment(i_crmInput, cUserData, data, out List<Command> lcUpdUser);
                    if (sMsg != null)
                    {
                        break;
                    }
                    Command cUser = Command.SetupUpdateCmd(cUserData, new WhereNode(tb_sys_user.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user), sUserUID));
                    lCmds.Add(cUser);


                    // 更新密碼
                    if (dicData.ContainsKey(tb_sys_user2entercode.CN_USER_ENTERCODE) && dicData[tb_sys_user2entercode.CN_USER_ENTERCODE] != null)
                    {
                        sMsg = EncryptHelper.Encrypt(dicData[tb_sys_user2entercode.CN_USER_ENTERCODE].ToString(), out string sEnc);
                        if (sMsg != null)
                        {
                            break;
                        }
                        tb_sys_user2entercode cEnterCode = new tb_sys_user2entercode
                        {
                            user_entercode = sEnc
                        };
                        tb_sys_user2entercode cEnterCodeCond = new tb_sys_user2entercode() { user_uid = sUserUID };
                        lCmds.Add(Command.SetupUpdateCmd(cEnterCode, cEnterCodeCond));
                    }

                    // 更新角色
                    // 查詢原始角色資料
                    tb_sys_user2role cRole = new tb_sys_user2role();
                    cRole.SetDirty(tb_sys_user2role.CN_ROLE_UID);
                    tb_sys_user2role cRoleCon = new tb_sys_user2role() { user_uid = sUserUID };
                    Command cRoleSelect = Command.SetupSelectCmd(cRole, cRoleCon);
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRoleSelect);
                    List<tb_sys_user2role> qdsRole = ai.RunQueryList<tb_sys_user2role>(cRoleSelect);

                    var origRole = qdsRole.Select(x => x.role_uid).ToList();
                    var lsRole = new List<string>() { };
                    if (dicData.ContainsKey(role_uid))
                    {
                        var jaRole = dicData[role_uid] as JArray;
                        foreach (var role in jaRole)
                        {
                            lsRole.Add(role.ToString());
                        }
                    }
                    var addRole = lsRole.Except(origRole);
                    var delRole = origRole.Except(lsRole);

                    if (addRole.Any())
                    {
                        foreach (var id in addRole)
                        {
                            tb_sys_user2role cNew = new tb_sys_user2role()
                            {
                                user_uid = sUserUID,
                                role_uid = id
                            };
                            lCmds.Add(Command.SetupInsertCmd(cNew));
                        }
                    }

                    if (delRole.Any())
                    {
                        foreach (var id in delRole)
                        {
                            tb_sys_user2role cCond = new tb_sys_user2role() { user_uid = sUserUID, role_uid = id };
                            lCmds.Add(Command.SetupDeleteCmd(cCond));
                        }
                    }

                    // 更新所屬分店
                    // 查詢分店原始資料
                    tb_hr_employee2branch cEmp2Branch = new tb_hr_employee2branch();
                    cEmp2Branch.SetDirty(tb_hr_employee2branch.CN_BRANCH_UID);
                    tb_hr_employee2branch cCon = new tb_hr_employee2branch() { uid = sUserUID };
                    Command cSelect = Command.SetupSelectCmd(cEmp2Branch, cCon);
                    ai = ArsenalDBMgr.GetInst(cSelect);
                    List<tb_hr_employee2branch> qdsBranch = ai.RunQueryList<tb_hr_employee2branch>(cSelect);

                    var origin = qdsBranch.Select(x => x.branch_uid).ToList();
                    var lsBranch = new List<string>() { };
                    if (dicData.ContainsKey(tb_hr_employee2branch.CN_BRANCH_UID))
                    {
                        var jaBranch = dicData[tb_hr_employee2branch.CN_BRANCH_UID] as JArray;
                        foreach (var branch in jaBranch)
                        {
                            lsBranch.Add(branch.ToString());
                        }
                        var addBranch = lsBranch.Except(origin);
                        var delBranch = origin.Except(lsBranch);
                        if (addBranch.Any())
                        {
                            foreach (var id in addBranch)
                            {
                                tb_hr_employee2branch cNew = new tb_hr_employee2branch()
                                {
                                    uid = sUserUID,
                                    branch_uid = id
                                };
                                lCmds.Add(Command.SetupInsertCmd(cNew));
                            }
                        }

                        if (delBranch.Any())
                        {
                            foreach (var id in delBranch)
                            {
                                tb_hr_employee2branch cCond = new tb_hr_employee2branch() { uid = sUserUID, branch_uid = id };
                                lCmds.Add(Command.SetupDeleteCmd(cCond));
                            }
                        }
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(updateCommandGenerator)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

            o_lResult = lCmds;
            return sMsg;
        }

        protected string deleteCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out List<Command> o_lcResult, List<string> i_saQryContainKeys,
            [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
            [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
            [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    foreach (JToken jtkProg in i_jaData)
                    {
                        Dictionary<string, object> dicData = jtkProg.ToObject<Dictionary<string, object>>();

                        Dictionary<string, object> wheredataDic = null;
                        string user_uid = null;
                        if (dicData.ContainsKey(BLWording.WHEREDATA))
                        {
                            JObject wheredata = dicData[BLWording.WHEREDATA] as JObject;
                            wheredataDic = wheredata.ToObject<Dictionary<string, object>>();
                            if (wheredataDic.ContainsKey(BLWording.UID))
                            {
                                user_uid = wheredataDic[BLWording.UID].ToString();
                            }
                        }

                        // 刪除角色
                        tb_sys_user2role r = new tb_sys_user2role()
                        {
                            user_uid = user_uid
                        };
                        lCmds.Add(Command.SetupDeleteCmd(r));
                        // 刪除密碼
                        tb_sys_user2entercode p = new tb_sys_user2entercode()
                        {
                            user_uid = user_uid
                        };
                        lCmds.Add(Command.SetupDeleteCmd(p));
                        // 刪除個人資訊
                        tb_hr_employee u = new tb_hr_employee()
                        {
                            uid = user_uid
                        };
                        lCmds.Add(Command.SetupDeleteCmd(u));
                        // 刪除帳號
                        tb_sys_user cUser = new tb_sys_user()
                        {
                            uid = user_uid
                        };
                        lCmds.Add(Command.SetupDeleteCmd(cUser));

                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(deleteCommandGenerator)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

            o_lcResult = lCmds;
            return sMsg;
        }

        //儲存密碼資訊
        public CResponseMessage CreatePassword(CRequestMessage i_crmInput)
        {
            CResponseMessage crm = null;
            string sMsg = null;
            Command cRes = null;
            do
            {
                try
                {
                    List<Command> lCmdMaster = new List<Command>();
                    string sAccount = "";
                    string sEnterCode = "";

                    object i_dicData = i_crmInput.param[BLWording.ADD_MASTER];
                    if (i_dicData is JArray)
                    {
                        JArray jaData = i_dicData as JArray;
                        foreach (JToken joData in jaData)
                        {
                            Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                            JObject jdata = dicData[BLWording.DATA] as JObject;
                            Dictionary<string, object> dicInput = jdata.ToObject<Dictionary<string, object>>();

                            if (dicInput.ContainsKey(tb_sys_user.CN_ACCOUNT))
                            {
                                sAccount = dicInput[tb_sys_user.CN_ACCOUNT].ToString();
                            }
                            if (dicInput.ContainsKey(tb_sys_user2entercode.CN_USER_ENTERCODE))
                            {
                                sEnterCode = dicInput[tb_sys_user2entercode.CN_USER_ENTERCODE].ToString();
                            }

                        }
                    }

                    // 查詢原始資料
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeOrigin = lBlocks.GetInst();
                    qjeOrigin.table = tb_sys_user.TABLENAME;
                    qjeOrigin.displaycols = new List<string>() { tb_sys_user.CN_UID };
                    qjeOrigin.wherecols = new WhereNode(tb_sys_user.CN_ACCOUNT, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user), sAccount);
                    lBlocks.Add(qjeOrigin);
                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);

                    if (sMsg != null)
                    {
                        break;
                    }

                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);

                    List<tb_sys_user> qds = ai.RunQueryList<tb_sys_user>(cRes);

                    if (!cRes.IsSuccess)
                    {
                        sMsg = cRes.LastErrorCode;
                        break;
                    }

                    if (qds.Any())
                    {
                        var origin = qds.Select(x => x.uid).ToList();
                        if (origin.Any())
                        {
                            foreach (var id in origin)
                            {
                                tb_sys_user2entercode cNew = new tb_sys_user2entercode()
                                {
                                    user_uid = id,
                                    user_entercode = sEnterCode,
                                };

                                lCmdMaster.Add(Command.SetupInsertCmd(cNew));
                            }
                        }

                        ai = ArsenalDBMgr.GetInst(lCmdMaster[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmdMaster);
                        sMsg = GetLastErrorCode(lCmdMaster);

                        if (sMsg != null)
                        {
                            break;
                        }

                        crm = new CSuccessResponseMessage(null, i_crmInput);
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_crmInput);
                    sMsg = $"{nameof(CreatePassword)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}.";
#if DEBUG
                    System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
                }
            }
            while (false);


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

            return crm;
        }

        /// <summary>
        /// 取得帳號與角色的關係
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        [Auth(false)]
        public CResponseMessage GetUser2Role(CRequestMessage i_crmInput)
        {
            CResponseMessage crmRes = null;
            string sMsg;
            try
            {
                do
                {
                    string user_uid = null;
                    object i_dicData = i_crmInput.param[BLWording.QRY_MASTER];
                    if (i_dicData is JArray)
                    {
                        JArray jaData = i_dicData as JArray;
                        foreach (JToken joData in jaData)
                        {
                            Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                            user_uid = dicData[BLWording.USER_ID].ToString();
                        }
                    }
                    // 查詢原始資料
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeOrigin = lBlocks.GetInst();
                    qjeOrigin.table = tb_sys_user2role.TABLENAME;
                    qjeOrigin.displaycols = new List<string>() { tb_sys_user2role.CN_ROLE_UID };
                    qjeOrigin.aliascols = new Dictionary<string, List<string>>()
                        { {tb_sys_user2role.CN_ROLE_UID, new List<string>(){ tb_sys_role.CN_UID } } };
                    qjeOrigin.wherecols = new WhereNode(tb_sys_user2role.CN_USER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user2role), user_uid);
                    lBlocks.Add(qjeOrigin);

                    QueryJsonElement qjeRole = lBlocks.GetInst();
                    qjeRole.table = tb_sys_role.TABLENAME;
                    qjeRole.displaycols = new List<string>() { tb_sys_role.CN_NAME };
                    qjeRole.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeRole.jointable = qjeOrigin;
                    qjeRole.joincols = new Dictionary<string, string>() {
                        { tb_sys_role.CN_UID, tb_sys_user2role.CN_ROLE_UID}
                    };
                    lBlocks.Add(qjeRole);
                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);

                    if (sMsg != null)
                    {
                        break;
                    }

                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    List<tb_sys_role> qdRole = ai.RunQueryList<tb_sys_role>(cRes);

                    var resp = new Dictionary<object, string>();
                    if (qdRole.Count > 0)
                    {
                        foreach (var role in qdRole)
                        {
                            resp.Add(role.name, role.uid);
                        }
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    // 填寫回傳
                    crmRes.param.Add(BLWording.DATA, resp);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetUser2Role)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif 
            }

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


        /// <summary>
        /// 更新tb_sys_user personalsetting欄位
        /// </summary>
        /// <param name="i_sUserUID"></param>
        /// <param name="i_sKey"></param>
        /// <param name="i_sVal"></param>
        /// <returns></returns>
        public string UpdateUserProfile(string i_sUserUID, string i_sKey, string i_sVal, out Command o_cUpdateCommand)
        {
            string sMsg = null;
            Command cUpdateCommand = null;
            // 查詢personal_setting資料
            tb_hr_employee cSelect = new tb_hr_employee();
            tb_hr_employee cCon = new tb_hr_employee() { uid = i_sUserUID };
            cSelect.SetFullDirty();
            Command cSe = Command.SetupSelectCmd(cSelect, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSe);
            QueryDataSet qds = ai.RunQueryDataSet(cSe, null);
            var data = qds.DATA.Tables[0].AsEnumerable();

            if (data.Any())
            {
                var jsonSetting = "";
                var personalSetting = data.Select(x => x.Field<string>(tb_hr_employee.CN_PERSONAL_SETTING)).SingleOrDefault();
                if (personalSetting != null)
                {
                    var setting = JsonConvert.DeserializeObject<SoldierData.PersonalSetting>(personalSetting);   // 將json字串反序列化
                    switch (i_sKey)
                    {
                        case PersonalSetting.CN_FAVORITE:
                            List<string> lsFavorite = i_sVal.Split(new String[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            List<string> data_ls = new List<string>();
                            foreach (string s in lsFavorite)
                            {
                                if (!data_ls.Contains(s))
                                {
                                    data_ls.Add(s);
                                }
                            }
                            setting.Favorite = data_ls;
                            break;
                        case PersonalSetting.CN_CALCOLOR:
                            setting.CalColor = i_sVal;
                            break;
                    }
                    jsonSetting = JsonConvert.SerializeObject(setting);   // 將物件序列化
                }
                else
                {
                    SoldierData.PersonalSetting psModel = new SoldierData.PersonalSetting();
                    switch (i_sKey)
                    {
                        case PersonalSetting.CN_FAVORITE:
                            psModel.Favorite = i_sVal.Split(new String[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            break;
                        case PersonalSetting.CN_CALCOLOR:
                            psModel.CalColor = i_sVal;
                            break;
                    }
                    jsonSetting = JsonConvert.SerializeObject(psModel);   // 將物件序列化
                }

                // 更新帳號資料表的personal_setting欄位
                tb_hr_employee cUpdateData = new tb_hr_employee
                {
                    personal_setting = jsonSetting
                };
                tb_hr_employee cUpdateCond = new tb_hr_employee() { uid = i_sUserUID };
                cUpdateCommand = Command.SetupUpdateCmd(cUpdateData, cUpdateCond);
            }


            o_cUpdateCommand = cUpdateCommand;
            return sMsg;
        }

        /// <summary>
        /// 新增到我的最愛
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage Favorite(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;
            do
            {
                List<Dictionary<string, object>> lDic = getQryParameterList(i_crmInput);
                if (lDic != null && lDic.Any())
                {
                    tb_sys_session user = i_crmInput.customparam[BLWording.SESSION_USER] as tb_sys_session;
                    var userUID = user.update_user_uid;
                    sMsg = new MemberService().UpdateUserProfile(userUID, SoldierData.PersonalSetting.CN_FAVORITE, lDic[0]["content"].ToString(), out Command cUpdate);

                    if (sMsg != null)
                    {
                        break;
                    }

                    if (cUpdate != null)
                    {
                        ArsenalInterface ai = ArsenalDBMgr.GetInst(cUpdate, GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(new List<Command>() { cUpdate });
                        sMsg = GetLastErrorCode(cUpdate);
                        if (sMsg != null)
                        {
                            break;
                        }
                    }
                }
                crmRes = new CSuccessResponseMessage(null, i_crmInput);
            }
            while (false);

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