GroupBy/04-程式設計/Trunk/GroupBuy/CounsellorBL.Group/ArticleManage2Service.cs

namespace CounsellorBL.GROUP
{
    using CounsellorBL.BLStructure;
    using CounsellorBL.Common;
    using CounsellorBL.GROUP.ConstDefinition;
    using CounsellorBL.GROUP.Helper;
    using CounsellorBL.Helper;
    using ICSharpCode.SharpZipLib.Zip;
    using MonumentDefine;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Linq;
    using NPOI.SS.Formula.Functions;
    using OT.COM.ArsenalDB;
    using OT.COM.ArsenalDB.SQL;
    using OT.COM.LogisticsUtil;
    using OT.COM.SignalerMessage;
    using SoldierData.EnterprizeV4;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Globalization;
    using System.IO;
    using System.Linq;
    using System.Net;
    using static CounsellorBL.GROUP.ConstDefinition.GrpBLWording;
    using static CounsellorBL.GROUP.Helper.FbHelper;
    using static MonumentDefine.Enums;

    class ArticleManage2Service : SingleDataTableTemplate<tb_grp_article>
    {
        private class GroupViewModel
        {
            public string message { get; set; }
            public string fb_group_id { get; set; }
            public string user_token { get; set; }
            public string announcement { get; set; }
            public string group_user_uid { get; set; }
            public int status_flag { get; set; }
        }

        private class GroupArticleModel : GroupViewModel
        {
            public string uid { get; set; }
            public string group_uid { get; set; }
            public DateTime release_date { get; set; }
            public string name { get; set; }
            public string user_tokne { get; set; }            
        }

        class MediaInfo
        {
            public string uid { get; set; }
            public string display { get; set; }
            public string mediatype { get; set; }
        }
        class ArticleMediaInfo : tb_grp_article
        {
            public string media_type { get; set; }
            public string media_id { get; set; }
            public string media_uid { get; set; }
            public int seq { get; set; }
            public int cost_price { get; set; }
        }

        public ArticleManage2Service()
        {
            dgReadCommandGenerator = readCommandGenerator;
            dgCreateCommandGenerator = createCommandGenerator;

            dgUpdateCommandGenerator = updateCommandGenerator;

            dgDeleteCommandGenerator = deleteCommandGenerator;
        }

        [Auth(false)]

        public CResponseMessage UpdateStatus(CRequestMessage i_crmInput)
        {
            return commonEditor(i_crmInput, BLWording.UPD_MASTER, updateSingleCommandGeneratorDefault);
        }



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

                    // 取得condition
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

                    QueryJsonElement qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.displaycols = lsEmpColumns;
                    qjeArticle.wherecols = new WhereNode(tb_grp_article.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), lsBranch.ToArray());
                    if (dicCondition.Any())
                    {
                        qjeArticle.dicwherecols = dicCondition;
                    }
                    qjeArticle.ordercols = new List<Tuple<QueryJsonElement, string, string>>()
                    {
                          new Tuple<QueryJsonElement, string, string>(qjeArticle, tb_grp_article.CN_CREATE_DATE, BLWording.ORDER_DESC)
                    };

                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMain.jointable = qjeArticle;
                    qjeMain.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_master.CN_ARTICLE_UID,tb_grp_article.CN_UID }};
                    qjeMain.displaycols = new List<string>()
                    {
                        tb_ord_order_master.CN_ORDER_CODE
                    };
                    lBlocks.Add(qjeArticle);
                    lBlocks.Add(qjeMain);
                    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;
        }
        [Auth(false)]
        public new CResponseMessage Update(CRequestMessage i_crmInput) => base.Update(i_crmInput);

        [Auth(false)]

        public CResponseMessage GetArticleInRange(CRequestMessage i_crmInput)
        {
            return simpleRead(i_crmInput, new SQLLib(new MSSQLDirectSQLHelper()).GetArticleInRange(out Command cRes), cRes);

        }


        [Auth(false)]

        public CResponseMessage GetScheduleWaitingArticleInRange(CRequestMessage i_crmInput)
        {
            return simpleRead(i_crmInput, new SQLLib(new MSSQLDirectSQLHelper()).GetScheduleWaitingArticleInRange(out Command cRes), cRes);

        }

        [Auth(false)]

        public CResponseMessage GetMediadWaitingArticleInRange(CRequestMessage i_crmInput)
        {
            return simpleRead(i_crmInput, new SQLLib(new MSSQLDirectSQLHelper()).GetMediadWaitingArticleInRange(out Command cRes), cRes);

        }

        [Auth(false)]

        public CResponseMessage GetFailWaitingArticleInRange(CRequestMessage i_crmInput)
        {
            return simpleRead(i_crmInput, new SQLLib(new MSSQLDirectSQLHelper()).GetFailWaitingArticleInRange(out Command cRes), cRes);

        }

        [Auth(false)]

        public CResponseMessage GetArticleInRangeTest(CRequestMessage i_crmInput)
        {
            return simpleRead(i_crmInput, new SQLLib(new MSSQLDirectSQLHelper()).GetArticleInRangeTest(out Command cRes), cRes);
        }

        protected string deleteCommandGenerator(CRequestMessage i_crmInput, JArray i_jaItems, 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;
            Command cRes = null;
            List<Command> lcCmds = new List<Command>();

            try
            {
                do
                {
                    foreach (JToken jtkItem in i_jaItems)
                    {
                        Dictionary<string, object> dicItem = jtkItem.ToObject<Dictionary<string, object>>();

                        sMsg = getManualLog(i_crmInput, dicItem, BLWording.LOG_ACTION_NAME_DELETESQL, out Command cLog);

                        if (sMsg != null)
                        {
                            break;
                        }

                        if (cLog != null)
                        {
                            lcCmds.Add(cLog);
                        }

                        string sMstUID = null;
                        if (dicItem.ContainsKey(BLWording.WHEREDATA) && dicItem[BLWording.WHEREDATA] is JObject wheredata)
                        {
                            Dictionary<string, object> wheredataDic = wheredata.ToObject<Dictionary<string, object>>();
                            // Get all MsterID
                            if (wheredataDic.ContainsKey("action") && wheredataDic.ContainsKey(BLWording.UID)) // For article-scheduler 因為此段刪除須要將文章轉草稿
                            {
                                if (wheredataDic["action"].ToString() == "update")
                                {
                                    sMstUID = wheredataDic[BLWording.UID].ToString();
                                    var oData = new tb_grp_article();
                                    oData.post_status = 0;
                                    oData.release_date = null;
                                    Command c = Command.SetupUpdateCmd(oData,
                                        new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), sMstUID));
                                    lcCmds.Add(c);
                                }
                            }
                            else if (wheredataDic.ContainsKey(BLWording.UID))
                            {
                                sMstUID = wheredataDic[BLWording.UID].ToString();

                                QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                                QueryJsonElement qjeA = lBlocks.GetInst();
                                qjeA.table = tb_grp_article.TABLENAME;
                                qjeA.displaycols = new List<string>()
                                {
                                    tb_grp_article.CN_DRAFT_UID
                                };
                                qjeA.wherecols = new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), sMstUID);
                                lBlocks.Add(qjeA);

                                sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                                if (sMsg != null)
                                {
                                    break;
                                }
                                ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                                var qds = ai.RunQueryList<tb_grp_article>(cRes);

                                var draft_uid = qds[0].draft_uid;


                                lBlocks = new QueryJsonElementCollection();
                                QueryJsonElement qjeAll = lBlocks.GetInst();
                                qjeAll.table = tb_grp_article.TABLENAME;
                                qjeAll.displaycols = new List<string>()
                                {
                                    tb_grp_article.CN_UID
                                };
                                qjeAll.wherecols = new WhereNode(tb_grp_article.CN_DRAFT_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), draft_uid);

                                lBlocks.Add(qjeAll);

                                sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                                if (sMsg != null)
                                {
                                    break;
                                }
                                ArsenalInterface aiAll = ArsenalDBMgr.GetInst(cRes);
                                var qdsAll = aiAll.RunQueryList<tb_grp_article>(cRes);
                                List<string> detailColumnUID = new List<string>();
                                foreach (var data in qdsAll)
                                {
                                    detailColumnUID.Add(data.uid.ToString());
                                }

                                tb_grp_article u = new tb_grp_article()
                                {
                                    status_flag = BLWording.STATUS_FLAG_OFF
                                };
                                tb_grp_article_media m = new tb_grp_article_media()
                                {
                                    status_flag = BLWording.STATUS_FLAG_OFF
                                };

                                tb_prd_article2product p = new tb_prd_article2product()
                                {
                                    status_flag = BLWording.STATUS_FLAG_OFF
                                };

                                Command c = Command.SetupUpdateCmd(u, new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), detailColumnUID.ToArray()));
                                Command c1 = Command.SetupUpdateCmd(m, new WhereNode(tb_grp_article_media.CN_ARTICLE_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article_media), detailColumnUID.ToArray()));
                                Command c2 = Command.SetupUpdateCmd(p, new WhereNode(tb_prd_article2product.CN_ARTICLE_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_prd_article2product), detailColumnUID.ToArray()));
                                lcCmds.Add(c);
                                lcCmds.Add(c1);
                                lcCmds.Add(c2);
                                // Get all action in this program
                                //lcCmds.Add(Command.SetupDeleteCmd(new tb_prd_article2product() { article_uid = sMstUID }));
                                //lcCmds.Add(Command.SetupDeleteCmd(new tb_grp_article_media() { article_uid = sMstUID }));
                                //lcCmds.Add(Command.SetupDeleteCmd(new tb_grp_article() { uid = sMstUID }));
                            }
                        }
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_crmInput);
                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 = lcCmds;
            return sMsg;
        }
        private string updateCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out List<Command> o_lcCmds, 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 joData in i_jaData)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();

                        JObject jwheredata = dicData[BLWording.WHEREDATA] as JObject;
                        JObject allData = dicData[BLWording.DATA] as JObject;
                        string uid = jwheredata[tb_grp_article.CN_UID].ToString();
                        tb_grp_article upData = new tb_grp_article()
                        {
                            message = allData[tb_grp_article.CN_MESSAGE].ToString(),
                            release_date = Convert.ToDateTime(allData[tb_grp_article.CN_POST_DATE].ToString()),
                            name = allData[tb_grp_article.CN_NAME].ToString(),
                            group_uid = allData[tb_grp_article.CN_GROUP_UID].ToString(),
                        };
                        tb_grp_article upCon = new tb_grp_article() { uid = uid };
                        lCmds.Add(Command.SetupUpdateCmd(upData, upCon));
                    }
                }
                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_lcCmds = lCmds;
            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 = null;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    foreach (JToken joData in i_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>>();
                        string sNewArticleUid = Guid.NewGuid().ToString();

                        if (dicInput.ContainsKey("isProductValid"))
                        {
                            if (Convert.ToBoolean(dicInput["isProductValid"]) == false)
                            {
                                sMsg = "商品價格錯誤";
                                break;
                            }
                        }

                        tb_grp_article cInsert = new tb_grp_article()
                        {
                            uid = sNewArticleUid,
                            name = (dicInput[tb_grp_article.CN_NAME] ?? "").ToString(),
                            group_uid = (dicInput[tb_grp_article.CN_GROUP_UID] ?? "").ToString(),
                            // fb_article_id = (dicInput[tb_grp_article.CN_FB_ARTICLE_ID] ?? "").ToString(),
                            message = dicInput[tb_grp_article.CN_MESSAGE].ToString(),
                            post_status = Convert.ToInt32(dicInput[tb_grp_article.CN_POST_STATUS]),
                            draft = 1,
                            draft_uid = sNewArticleUid,
                            dutyfree = (dicInput[tb_grp_article.CN_DUTYFREE] ?? "").ToString().ToLower() == "true" ? "1" : "0"
                        };
                        // 發布時間可能為空
                        if (dicInput.ContainsKey(tb_grp_article.CN_RELEASE_DATE) && dicInput[tb_grp_article.CN_RELEASE_DATE] != null)
                        {
                            cInsert.release_date = Convert.ToDateTime(dicInput[tb_grp_article.CN_RELEASE_DATE].ToString());
                        }
                        // 截團時間可能為空
                        if (dicInput.ContainsKey(tb_grp_article.CN_CLOSE_DATE) && dicInput[tb_grp_article.CN_CLOSE_DATE] != null)
                        {
                            cInsert.close_date = Convert.ToDateTime(dicInput[tb_grp_article.CN_CLOSE_DATE].ToString());
                        }
                        Command c = Command.SetupInsertCmd(cInsert);
                        lCmds.Add(c);

                        // Log
                        sMsg = getManualLog(i_crmInput, dicData, BLWording.LOG_ACTION_NAME_INSERTSQL, out Command cLog);

                        if (sMsg != null)
                        {
                            break;
                        }

                        if (cLog != null)
                        {
                            lCmds.Add(cLog);
                        }

                        string sMediaKey = nameof(tb_grp_article_media);
                        if (!jdata.ContainsKey(sMediaKey))
                        {
                            sMsg = "common.no_media_fle";
                            break;
                        }

                        List<MediaInfo> lmi = jdata[sMediaKey].ToObject<List<MediaInfo>>();
                        var seq = 1;
                        lmi.ForEach(f =>
                        {
                            lCmds.Add(Command.SetupInsertCmd(new tb_grp_article_media()
                            {
                                article_uid = sNewArticleUid,
                                media_id = f.uid,
                                type = f.mediatype,
                                seq = seq++
                            }));
                        });

                        string sProductKey = nameof(tb_prd_article2product);
                        if (!jdata.ContainsKey(sProductKey))
                        {
                            sMsg = "grp.no_product";
                            break;
                        }

                        List<tb_prd_article2product> la2pProducts = jdata[sProductKey].ToObject<List<tb_prd_article2product>>();
                        if (!la2pProducts.Any())
                        {
                            sMsg = "grp.no_product";
                            break;
                        }
                        new Article2ProductService().CreateProduct(sNewArticleUid, la2pProducts, cInsert.group_uid, ref lCmds);
                    }
                }
                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;
        }

        [Auth(false)]
        public CResponseMessage PushPost(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.QRY_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession);
                    if (sMsg != null)
                    {
                        break;
                    }
                    if (jaDataArray.Count != 1 || jaDataArray[0] == null)
                    {
                        sMsg = MessageWording.PARAM_NOT_EXPECTED;
                        break;
                    }
                    sMsg = _PushPostDetail(jaDataArray[0][BLWording.WHEREDATA][BLWording.UID].ToString(), jaDataArray[0][GrpBLWording.GRP_DOWNLOADURL].ToString(), true, out _);
                    if (sMsg != null)
                    {
                        break;
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    // 填寫回傳

                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = $"{nameof(PushPost)} exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ex={ex.Message}";
                Logger.Error(sMsg);
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif
            }

            if (string.IsNullOrEmpty(sMsg))
            {
                //Logger.Info(JsonConvert.SerializeObject(i_crmInput));
                //Logger.Info(JsonConvert.SerializeObject(crmRes));
            }
            else
            {
                crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                Logger.Error(JsonConvert.SerializeObject(i_crmInput));
                Logger.Error(JsonConvert.SerializeObject(crmRes));
            }
            return crmRes;
        }

        private string _PushPostDetail(string i_sUID, string i_sURL, bool i_bHandlePhoto, out string o_sResult)
        {
            string sMsg = null;
            string sResult = null;
            Logger.Info($"_PushPostDetail 處理 Uid:{i_sUID} i_sURL={i_sURL}");
            try
            {
                do
                {
                    /**開始組指令**/
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.displaycols = new List<string>()
                    {
                        tb_grp_article.CN_MESSAGE
                    };
                    qjeArticle.wherecols = new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), i_sUID);
                    lBlocks.Add(qjeArticle);

                    QueryJsonElement qjeGroup = lBlocks.GetInst();
                    qjeGroup.table = tb_grp_group.TABLENAME;
                    qjeGroup.jointable = qjeArticle;
                    qjeGroup.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeGroup.joincols = new Dictionary<string, string> { { tb_grp_group.CN_UID, tb_grp_article.CN_GROUP_UID } };
                    qjeGroup.displaycols = new List<string>()
                    {
                        tb_grp_group.CN_FB_GROUP_ID,tb_grp_group.CN_ANNOUNCEMENT
                    };
                    lBlocks.Add(qjeGroup);

                    QueryJsonElement qjeGroupUser = lBlocks.GetInst();
                    qjeGroupUser.table = tb_grp_group2user.TABLENAME;
                    qjeGroupUser.jointable = qjeGroup;
                    qjeGroupUser.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeGroupUser.joincols = new Dictionary<string, string> { { tb_grp_group2user.CN_UID, tb_grp_group.CN_POST_USER_UID } };
                    qjeGroupUser.displaycols = new List<string>()
                    {
                        tb_grp_group2user.CN_USER_TOKEN
                    };
                    qjeGroupUser.aliascols = new Dictionary<string, List<string>>() {
                      { tb_grp_group2user.CN_UID, new List<string>() { "group_user_uid" } },
                    };
                    lBlocks.Add(qjeGroupUser);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);

                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    List<GroupViewModel> qdArticle = ai.RunQueryList<GroupViewModel>(cRes);
                    GroupViewModel data = qdArticle.FirstOrDefault();

                    var mailHelper = new MailHelper();
                    // 取得當發文錯誤要發送的名單
                    string sMsgMailList = SystemSettingHelper.GetSetting(BLWording.ERROR_MAILLIST, out var mailList);
                    if (sMsg != null)
                    {
                        Logger.Error(sMsgMailList);
                    }
                    List<MailAccountInfo> lsMailList = new List<MailAccountInfo>();
                    if (mailList != null && !string.IsNullOrEmpty(mailList.key_value))
                    {
                        var lsMail = mailList.key_value.Split(new String[] { ",", ";" }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        foreach (var mail in lsMail)
                        {
                            lsMailList.Add(new MailAccountInfo() { EMail = mail });
                        }
                    }

                    lBlocks.Clear();
                    List<string> lsPhotoId = new List<string>();
                    bool bNeedRPA = false;
                    bool bHasPicError = false;
                    int nPhotoCountOrigin = 0;
                    if (i_bHandlePhoto)
                    {
                        List<string> lsFailURIs = new List<string>();
                        Logger.Info($"_PushPostDetail HandlePhoto Start Uid:{i_sUID}");
                        QueryJsonElement qjeMedia = lBlocks.GetInst();
                        qjeMedia.table = tb_grp_article_media.TABLENAME;
                        qjeMedia.displaycols = new List<string>()
                        {
                            tb_grp_article_media.CN_MEDIA_ID,
                            tb_grp_article_media.CN_URI,
                            tb_grp_article_media.CN_TYPE
                        };

                        qjeMedia.wherecols = WhereNode.GenNode<tb_grp_article_media>(f => f.article_uid == i_sUID);
                        lBlocks.Add(qjeMedia);
                        sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cResom);
                        if (sMsg != null)
                        {
                            break;
                        }

                        ai = ArsenalDBMgr.GetInst(cResom);
                        List<tb_grp_article_media> qdMedia = ai.RunQueryList<tb_grp_article_media>(cResom);
                        nPhotoCountOrigin = qdMedia.Count;
                        Logger.Info($"_PushPostDetail CallFbPostPushAPI nPhotoCountOrigin = {nPhotoCountOrigin}");
                        // 如果是上傳的圖片則組uri(圖片需要分次上傳)
                        foreach (tb_grp_article_media x in qdMedia.Where(x => x.type == BLWording.MediaType.Photo))
                        {
                            string uri = string.Format("{0}{1}", i_sURL, x.media_id);
                            Logger.Info($"_PushPostDetail CallFbPicturePushAPI uri = {uri}");
                            // TODO: 可以考慮Retry
                            string sMsgPic = new FbHelper().CallFbPicturePushAPI(data.user_token, data.fb_group_id, uri, out string sID);
                            if (sMsgPic != null)
                            {
                                bHasPicError = true;
                                lsFailURIs.Add(uri);
                                Logger.Error($"_PushPostDetail 發文錯誤:{sMsgPic}, GroupViewModel:{JsonConvert.SerializeObject(data)}");
                                var mailMsg = mailHelper.Send("發文錯誤", string.Format("圖片上傳錯誤:{0}", sMsg), lsMailList);
                                if (string.IsNullOrEmpty(mailMsg))
                                {
                                    Logger.Info(string.Format("信件發送成功:{0} => {1}", sMsg, lsMailList));
                                }
                                else
                                {
                                    Logger.Error(string.Format("信件發送失敗:{0} => {1}", mailMsg, lsMailList));
                                }
                                continue;
                            }
                            else
                            {
                                lsPhotoId.Add(sID);
                            }
                            Logger.Info($"_PushPostDetail CallFbPicturePushAPI end uri = {uri} ");
                        }

                        Logger.Info($"_PushPostDetail HandlePhoto:F1 bHasPicError={bHasPicError}  ({lsFailURIs.Count}/{nPhotoCountOrigin})");
                        // 判斷是否有影片決定更新RPA狀況
                        bNeedRPA = bHasPicError || (qdMedia != null && qdMedia.Any(x => x.type == BLWording.MediaType.Video));
                        Logger.Info($"_PushPostDetail HandlePhoto End Uid:{i_sUID} bHasPicError={bHasPicError} bNeedRPA={bNeedRPA}");
                    }

                    if (bHasPicError)
                    {
                        // 只要有圖錯就不要發文
                        sMsg = "有一張以上圖失敗就放棄上傳";
                        Logger.Error($"_PushPostDetail {sMsg}");
                        break;
                    }

                    Logger.Info($"_PushPostDetail CallFbPostPushAPI before lsPhotoId.Count= {nPhotoCountOrigin}");
                    sMsg = new FbHelper().CallFbPostPushAPI(data.user_token, data.message + "\n" + data.announcement, data.fb_group_id, lsPhotoId, out string sPostID);
                    Logger.Info($"_PushPostDetail CallFbPostPushAPI after sMsg={sMsg}");
                    if (sMsg != null)
                    {
                         Logger.Error($"F5 _PushPostDetail {sMsg}");
                        mailHelper.Send("發文錯誤", string.Format("貼文上傳錯誤:{0}", sMsg), lsMailList);
                        // break; <= 2021/06/17 CallFbPostPushAPI 理論上網路正常的狀況FB已經上了
                        break;
                    }

                    Logger.Info($"_PushPostDetail before Update");
                    tb_grp_article u = new tb_grp_article()
                    {
                        post_status = (int)EPostStatus.EPS_COMPLETE,
                        fb_article_id = sPostID,
                        group_user_uid = data.group_user_uid,
                        post_date = DateTime.Now
                    };

                    if (!bNeedRPA)
                    {
                        u.rpa_status = (int)Enums.Flag.Enable;
                    }

                    Command c = Command.SetupUpdateCmd(u, new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), i_sUID));
                    ai = ArsenalDBMgr.GetInst(c, GetDefaultSystemColumnInfo());
                    ai.RunEditSingleCmd(c);

                    Logger.Info($"_PushPostDetail after Update");
                    if (!c.IsSuccess)
                    {
                        sMsg = c.LastErrorCode;
                        Logger.Error($"_PushPostDetail c.LastErrorCode={c.LastErrorCode}");
                        break;
                    }

                    sResult = sPostID;
                }
                while (false);
            }
            catch (Exception ex)
            {
                sMsg = ex.Message;
                Logger.Error($"Exception:{sMsg}");
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif
            }

            o_sResult = sResult;

            Logger.Info($"_PushPostDetail 處理 Uid :{i_sUID} i_sURL={i_sURL} 結束 sMsg={sMsg} o_sResult = {o_sResult} ");
            return sMsg;
        }

        public CResponseMessage TransferOrder(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.UPD_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession);
                    if (sMsg != null)
                    {
                        break;
                    }
                    if (jaDataArray.Count != 1 || jaDataArray[0] == null)
                    {
                        sMsg = MessageWording.PARAM_NOT_EXPECTED;
                        break;
                    }

                    var article_uid = jaDataArray[0][BLWording.WHEREDATA][BLWording.UID].ToString();
                    var data = jaDataArray[0][BLWording.WHEREDATA][BLWording.DATA] as JArray;

                    sMsg = new FbHelper().CheckOrder(article_uid, data);
                    if (sMsg != null)
                    {
                        break;
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                Logger.Error(ex);
                sMsg = $"{nameof(TransferOrder)} unknwon exception.).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif

            }

            if (sMsg != null)
            {
                crmRes = new CErrorResponseMessage(sMsg);
                Logger.Error(JsonConvert.SerializeObject(i_crmInput));
                Logger.Error(JsonConvert.SerializeObject(crmRes));
            }
            else
            {
                crmRes = new CSuccessResponseMessage(null, i_crmInput);
                //Logger.Info(JsonConvert.SerializeObject(i_crmInput));
                //Logger.Info(JsonConvert.SerializeObject(crmRes));
            }

            return crmRes;
        }

        private string GetMemberPSID(string i_sArticleUID, out List<string> lsPSID)
        {
            string sMsg = null;
            lsPSID = null;
            try
            {
                do
                {
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeOrder = lBlocks.GetInst();
                    qjeOrder.table = tb_ord_order_detail.TABLENAME;
                    lBlocks.Add(qjeOrder);

                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.jointable = qjeOrder;
                    qjeMain.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMain.joincols = new Dictionary<string, string> { { tb_ord_order_master.CN_UID, tb_ord_order_detail.CN_ORDER_UID } };
                    qjeMain.wherecols = new WhereNode(tb_ord_order_master.CN_ARTICLE_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), i_sArticleUID);
                    lBlocks.Add(qjeMain);

                    QueryJsonElement qjeMember = lBlocks.GetInst();
                    qjeMember.table = tb_meb_member.TABLENAME;
                    qjeMember.jointable = qjeOrder;
                    qjeMember.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMember.joincols = new Dictionary<string, string> { { tb_meb_member.CN_UID, tb_ord_order_detail.CN_MEMBER_UID } };
                    qjeMember.displaycols = new List<string>()
                    {
                        tb_meb_member.CN_MESSAGE_USER_ID
                    };
                    lBlocks.Add(qjeMember);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);

                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    List<tb_meb_member> qds = ai.RunQueryList<tb_meb_member>(cRes);
                    lsPSID = qds.Select(x => x.message_user_id).ToList();
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(TransferOrder)} unknwon exception.).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif

            }
            return sMsg;
        }
        public CResponseMessage GetArticleMedia(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;

            try
            {
                do
                {
                    // 分頁
                    int nPageIdx = -1;
                    int nPageNum = -1;

                    if (i_crmInput != null)
                    {
                        if (i_crmInput.param.ContainsKey(BLWording.QRY_PAGE_IDX)
                            && Int32.TryParse(i_crmInput.param[BLWording.QRY_PAGE_IDX].ToString(), out int nPageIdxFromUI)
                            && nPageIdxFromUI >= 0)
                        {
                            nPageIdx = nPageIdxFromUI;
                        }

                        if (i_crmInput.param.ContainsKey(BLWording.QRY_PAGE_NUM)
                       && Int32.TryParse(i_crmInput.param[BLWording.QRY_PAGE_NUM].ToString(), out int nPageNumFromUI)
                       && nPageNumFromUI >= 0)
                        {
                            nPageNum = nPageNumFromUI;
                        }
                    }

                    List<string> lsEmpColumns = EntityBase.GetAllColumnName(typeof(tb_grp_article));

                    // 取得condition
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);

                    // Filter& Sort
                    Dictionary<string, string> mainOrderData = null;
                    string mainFullTextPattern = null;
                    if (i_crmInput != null && i_crmInput.param != null && i_crmInput.param.ContainsKey(BLWording.QRY_MASTER))
                    {
                        JArray joData = i_crmInput.param[BLWording.QRY_MASTER] as JArray;

                        if (joData.Any() && joData[0][BLWording.ORDERDATA] != null)
                        {
                            JArray jaData = joData[0][BLWording.ORDERDATA] as JArray;
                            mainOrderData = jaData[0].ToObject<Dictionary<string, string>>();
                        }
                        if (joData.Any() && joData[0][BLWording.FULLTEXT_PATTERN] != null)
                        {
                            mainFullTextPattern = joData[0][BLWording.FULLTEXT_PATTERN].ToString();
                        }
                    }

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    List<WhereNode> lswnArticle = new List<WhereNode>();
                    lswnArticle.Add(new WhereNode(tb_grp_article.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), lsBranch.ToArray()));
                    QueryJsonElement qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.displaycols = lsEmpColumns;
                    if (dicCondition.Any())
                    {
                        qjeArticle.dicwherecols = dicCondition;
                    }

                    if (!string.IsNullOrEmpty(mainFullTextPattern))
                    {
                        lswnArticle.Add(new WhereNode(tb_grp_article.CN_NAME, WhereNode.EColumnOperation.EOT_LIKE, typeof(tb_grp_article), mainFullTextPattern));
                    }
                    qjeArticle.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lswnArticle.ToArray());

                    QueryJsonElement qjeGroup = lBlocks.GetInst();
                    qjeGroup.table = tb_grp_group.TABLENAME;
                    qjeGroup.jointable = qjeArticle;
                    qjeGroup.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeGroup.joincols = new Dictionary<string, string> { { tb_grp_group.CN_UID, tb_grp_article.CN_GROUP_UID } };
                    if (!string.IsNullOrEmpty(mainFullTextPattern))
                    {
                        qjeGroup.wherecols = new WhereNode(tb_grp_group.CN_NAME, WhereNode.EColumnOperation.EOT_LIKE, typeof(tb_grp_group), mainFullTextPattern);
                    }

                    // Sort
                    List<Tuple<QueryJsonElement, string, string>> mainOrder = new List<Tuple<QueryJsonElement, string, string>>();
                    if (mainOrderData != null)
                    {
                        mainOrder.Add(Tuple.Create(qjeArticle, mainOrderData.First().Key, mainOrderData.First().Value));
                    }
                    else
                    {
                        mainOrder.Add(Tuple.Create(qjeArticle, tb_grp_article.CN_RELEASE_DATE, BLWording.ORDER_DESC));
                    }
                    qjeArticle.ordercols = mainOrder;

                    // 先依照分頁條件查出資料
                    lBlocks.Add(qjeArticle);
                    _ = MakeSelectJoinByBlocks(lBlocks, out Command cResOrderMst);
                    cResOrderMst.NeedCount = true;
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cResOrderMst);
                    var qdsOrderMst = ai.RunQueryDataSet(cResOrderMst, i_nPageIdx: nPageIdx, i_nNumOfPage: nPageNum);
                    List<string> orderMstArray = new List<string>();
                    foreach (DataRow data in qdsOrderMst.DATA.Tables[0].Rows)
                    {
                        orderMstArray.Add(data[tb_grp_article.CN_UID].ToString());
                    }

                    lBlocks = new QueryJsonElementCollection();
                    qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.displaycols = lsEmpColumns;
                    qjeArticle.wherecols = new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), orderMstArray.ToArray());

                    qjeGroup = lBlocks.GetInst();
                    qjeGroup.table = tb_grp_group.TABLENAME;
                    qjeGroup.jointable = qjeArticle;
                    qjeGroup.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeGroup.joincols = new Dictionary<string, string> { { tb_grp_group.CN_UID, tb_grp_article.CN_GROUP_UID } };

                    QueryJsonElement qjeMedia = lBlocks.GetInst();
                    qjeMedia.table = tb_grp_article_media.TABLENAME;
                    qjeMedia.jointable = qjeArticle;
                    qjeMedia.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMedia.joincols = new Dictionary<string, string> { { tb_grp_article_media.CN_ARTICLE_UID, tb_grp_article.CN_UID } };
                    qjeMedia.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_article_media.CN_TYPE, new List<string>() { "media_type" } },
                         { tb_grp_article_media.CN_UID, new List<string>() { "media_uid" } },
                         { tb_grp_article_media.CN_MEDIA_ID, new List<string>() { "media_id" } }
                    };
                    qjeMedia.wherecols = new WhereNode(tb_grp_article_media.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article_media), "photo");
                    qjeMedia.displaycols = new List<string>() { tb_grp_article_media.CN_SEQ };

                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                    qjeProduct.jointable = qjeArticle;
                    qjeProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeProduct.joincols = new Dictionary<string, string> { { tb_prd_article2product.CN_ARTICLE_UID, tb_grp_article.CN_UID } };
                    qjeProduct.displaycols = new List<string>() { tb_prd_article2product.CN_COST_PRICE };

                    lBlocks.Add(qjeArticle);
                    lBlocks.Add(qjeGroup);
                    lBlocks.Add(qjeMedia);
                    lBlocks.Add(qjeProduct);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRead);

                    ai = ArsenalDBMgr.GetInst(cRead);
                    var qds = ai.RunQueryList<ArticleMediaInfo>(cRead);
                    var groupData = qds.OrderByDescending(x => x.release_date).GroupBy(x => new { x.draft_uid }).ToList();

                    if (dicCondition.ContainsKey(tb_grp_article.CN_POST_STATUS))
                    {
                        if (dicCondition[tb_grp_article.CN_POST_STATUS] == "2")
                        {
                            groupData = qds.OrderBy(x => x.release_date).GroupBy(x => new { x.draft_uid }).ToList();
                        }
                    }

                    var lsFirstData = new List<ArticleMediaInfo>();
                    foreach (var data in groupData.OrderByDescending(x => x.Last().release_date))
                    {
                        var firstData = data.OrderBy(x => x.seq).First();
                        var haveNullCost = data.Where(x => x.uid == firstData.uid).Any(x => x.cost_price <= 0);
                        firstData.cost_price = haveNullCost ? (int)Enums.Flag.Disable : (int)Enums.Flag.Enable;
                        lsFirstData.Add(firstData);
                    }
                    var res = lsFirstData.Select(x => new
                    {
                        x.uid,
                        x.type,
                        x.group_uid,
                        x.name,
                        x.fb_article_id,
                        x.message,
                        close_date = x.close_date.HasValue ? x.close_date.Value.ToString("yyyy-MM-dd HH:mm:ss") : "",
                        release_date = x.release_date.HasValue ? x.release_date.Value.ToString("yyyy-MM-dd HH:mm:ss") : "",
                        x.post_status,
                        x.remark,
                        x.status_flag,
                        x.media_id,
                        x.media_type,
                        x.media_uid,
                        x.create_date,
                        x.draft_uid,
                        x.draft,
                        x.cost_price
                    }).ToList();
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, new { records = res, totalCount = qdsOrderMst.Total });
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetArticleMedia)} 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;
        }

        public CResponseMessage GetArticleMediaForScheduler(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;

            try
            {
                do
                {
                    List<string> lsEmpColumns = EntityBase.GetAllColumnName(typeof(tb_grp_article));

                    // 取得condition
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

                    QueryJsonElement qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.displaycols = lsEmpColumns;
                    qjeArticle.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND,
                                                            new WhereNode(tb_grp_article.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), lsBranch.ToArray()),
                                                            new WhereNode(tb_grp_article.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), 1),
                                                            new WhereNode(tb_grp_article.CN_RELEASE_DATE, WhereNode.EColumnOperation.EOT_ISNOTNULL, typeof(tb_grp_article))
                                                       );
                    if (dicCondition.Any())
                    {
                        qjeArticle.dicwherecols = dicCondition;
                    }
                    lBlocks.Add(qjeArticle);

                    QueryJsonElement qjeMedia = lBlocks.GetInst();
                    qjeMedia.table = tb_grp_article_media.TABLENAME;
                    qjeMedia.jointable = qjeArticle;
                    qjeMedia.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMedia.joincols = new Dictionary<string, string> { { tb_grp_article_media.CN_ARTICLE_UID, tb_grp_article.CN_UID } };
                    qjeMedia.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_article_media.CN_TYPE, new List<string>() { "media_type" } },
                         { tb_grp_article_media.CN_UID, new List<string>() { "media_uid" } },
                         { tb_grp_article_media.CN_MEDIA_ID, new List<string>() { "media_id" } }
                    };
                    qjeMedia.wherecols = new WhereNode(tb_grp_article_media.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article_media), "photo");
                    qjeMedia.displaycols = new List<string>() { tb_grp_article_media.CN_SEQ };
                    lBlocks.Add(qjeMedia);

                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                    qjeProduct.jointable = qjeArticle;
                    qjeProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeProduct.joincols = new Dictionary<string, string> { { tb_prd_article2product.CN_ARTICLE_UID, tb_grp_article.CN_UID } };
                    qjeProduct.displaycols = new List<string>() { tb_prd_article2product.CN_COST_PRICE };
                    lBlocks.Add(qjeProduct);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRead);

                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRead);
                    var qds = ai.RunQueryList<ArticleMediaInfo>(cRead);
                    var groupData = qds.OrderByDescending(x => x.create_date).GroupBy(x => new { x.draft_uid }).ToList();

                    if (dicCondition.ContainsKey(tb_grp_article.CN_POST_STATUS))
                    {
                        if (dicCondition[tb_grp_article.CN_POST_STATUS] == "2")
                        {
                            groupData = qds.OrderBy(x => x.release_date).GroupBy(x => new { x.draft_uid }).ToList();
                        }
                    }
                    var lsFirstData = new List<ArticleMediaInfo>();
                    foreach (var data in groupData.OrderByDescending(x => x.Last().create_date))
                    {
                        var firstData = data.OrderBy(x => x.seq).First();
                        var haveNullCost = data.Where(x => x.uid == firstData.uid).Any(x => x.cost_price <= 0);
                        firstData.cost_price = haveNullCost ? (int)Enums.Flag.Disable : (int)Enums.Flag.Enable;
                        lsFirstData.Add(firstData);
                    }
                    var res = lsFirstData.Select(x => new
                    {
                        x.uid,
                        x.type,
                        x.group_uid,
                        x.name,
                        x.fb_article_id,
                        x.message,
                        close_date = x.close_date.HasValue ? x.close_date.Value.ToString("yyyy-MM-dd HH:mm:ss") : "",
                        release_date = x.release_date.HasValue ? x.release_date.Value.ToString("yyyy-MM-dd HH:mm:ss") : "",
                        x.post_status,
                        x.status_flag,
                        x.media_id,
                        x.media_type,
                        x.media_uid,
                        x.create_date,
                        x.draft_uid,
                        x.draft,
                        x.cost_price
                    }).ToList();
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, res);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetArticleMedia)} 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;
        }
        private string createCopyArticle(CRequestMessage i_crmInput, JArray i_jaData, int draft, List<string> productUid)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            string sNewArticleUid = "";

            try
            {
                do
                {
                    foreach (JToken joData in i_jaData)
                    {
                        List<string> lsEmpColumns = EntityBase.GetAllColumnName(typeof(tb_grp_article));
                        var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);
                        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>>();
                        sNewArticleUid = Guid.NewGuid().ToString();

                        tb_grp_article cInsert = new tb_grp_article()
                        {
                            uid = sNewArticleUid,
                            name = (dicInput[tb_grp_article.CN_NAME] ?? "").ToString(),
                            group_uid = (dicInput[tb_grp_article.CN_GROUP_UID] ?? "").ToString(),
                            // fb_article_id = (dicInput[tb_grp_article.CN_FB_ARTICLE_ID] ?? "").ToString(),
                            message = dicInput[tb_grp_article.CN_MESSAGE].ToString(),
                            post_status = Convert.ToInt32(dicInput[tb_grp_article.CN_POST_STATUS]),
                            draft = draft,
                            draft_uid = draft == 1 ? sNewArticleUid : (dicInput[tb_grp_article.CN_DRAFT_UID] ?? "").ToString(),
                            remark = (dicInput[tb_grp_article.CN_REMARK])?.ToString(),
                            dutyfree = (dicInput[tb_grp_article.CN_DUTYFREE] ?? "").ToString().ToLower() == "true" ? "1" : "0"
                        };
                        // 發布時間可能為空
                        if (dicInput.ContainsKey(tb_grp_article.CN_RELEASE_DATE) && dicInput[tb_grp_article.CN_RELEASE_DATE] != null)
                        {
                            cInsert.release_date = Convert.ToDateTime(dicInput[tb_grp_article.CN_RELEASE_DATE].ToString());
                        }
                        // 截團時間可能為空
                        if (dicInput.ContainsKey(tb_grp_article.CN_CLOSE_DATE) && dicInput[tb_grp_article.CN_CLOSE_DATE] != null)
                        {
                            cInsert.close_date = Convert.ToDateTime(dicInput[tb_grp_article.CN_CLOSE_DATE].ToString());
                        }
                        Command c = Command.SetupInsertCmd(cInsert);
                        lCmds.Add(c);
                        //}



                        // Log
                        sMsg = getManualLog(i_crmInput, dicData, BLWording.LOG_ACTION_NAME_INSERTSQL, out Command cLog);

                        if (sMsg != null)
                        {
                            break;
                        }

                        if (cLog != null)
                        {
                            lCmds.Add(cLog);
                        }

                        string sMediaKey = nameof(tb_grp_article_media);
                        if (!jdata.ContainsKey(sMediaKey))
                        {
                            sMsg = "common.no_media_fle";
                            break;
                        }

                        List<MediaInfo> lmi = jdata[sMediaKey].ToObject<List<MediaInfo>>();
                        lmi.ForEach(f =>
                        {
                            lCmds.Add(Command.SetupInsertCmd(new tb_grp_article_media()
                            {
                                uid = Guid.NewGuid().ToString(),
                                article_uid = sNewArticleUid,
                                media_id = f.display.Substring(f.display.IndexOf('=') + 1),
                                type = f.mediatype,
                                status_flag = BLWording.STATUS_FLAG_ON
                            }));
                        });


                        string sProductKey = nameof(tb_prd_article2product);
                        if (!jdata.ContainsKey(sProductKey))
                        {
                            sMsg = "grp.no_product";
                            break;
                        }

                        //List<tb_prd_article2product> la2pProducts = jdata[sProductKey].ToObject<List<tb_prd_article2product>>();
                        //new Article2ProductService().CreateProduct(sNewArticleUid, la2pProducts, dicInput[tb_grp_article.CN_GROUP_UID].ToString(), ref lCmds);

                        if (dicInput.ContainsKey(nameof(tb_prd_article2product)))
                        {
                            List<tb_prd_article2product> la2pFrontEnd = (dicInput[nameof(tb_prd_article2product)] as JArray).ToObject<List<tb_prd_article2product>>();

                            var count = 0;
                            // Only in dicInput => Add
                            la2pFrontEnd.ForEach((f) =>
                            {
                                string sProductUid = "";
                                if (f.prd_uid == null && productUid.Count != 0)
                                {
                                    sProductUid = productUid[count];
                                    count++;
                                }
                                else
                                {
                                    sProductUid = f.prd_uid;
                                }
                                tb_prd_product pDisplay = new tb_prd_product();
                                pDisplay.SetFullDirty();
                                tb_prd_product p = new tb_prd_product()
                                {
                                    uid = sProductUid
                                };

                                Command cSelectP = Command.SetupSelectCmd(pDisplay, p);

                                ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelectP, GetDefaultSystemColumnInfo());

                                List<tb_prd_article2product> la2pOri = ai.RunQueryList<tb_prd_article2product>(cSelectP);

                                if (la2pOri.FirstOrDefault() == null)
                                {
                                    sProductUid = Guid.NewGuid().ToString();
                                    // Create Product first                                                                
                                    lCmds.Add(Command.SetupInsertCmd(new tb_prd_product()
                                    {
                                        uid = sProductUid,
                                        name = f.name,
                                        group_id = dicInput[tb_grp_article.CN_GROUP_UID].ToString(),
                                        price = f.price,
                                        wholesale_price = f.wholesale_price,
                                        cost_price = f.cost_price
                                    }));
                                }

                                lCmds.Add(Command.SetupInsertCmd(new tb_prd_article2product()
                                {
                                    uid = Guid.NewGuid().ToString(),
                                    price = f.price,
                                    wholesale_price = f.wholesale_price,
                                    name = f.name,
                                    article_uid = sNewArticleUid,
                                    prd_uid = sProductUid,
                                    seq = f.seq,
                                    specification = f.specification.ToUpper(),
                                    cost_price = f.cost_price
                                }));

                            });
                        }

                        ArsenalInterface ai = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmds);
                        sMsg = GetLastErrorCode(lCmds);
                        if (sMsg != null)
                        {
                            return sMsg;
                        }
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(createCopyArticle)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif
                throw new Exception(sMsg);
            }

            return sNewArticleUid;
        }
        public CResponseMessage UpdateArticle(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            CResponseMessage crmRes = null;
            JArray i_jaData = i_crmInput.param[BLWording.UPD_MASTER] as JArray;
            try
            {
                do
                {
                    string post_status = null;
                    string opost_status = "0";
                    bool isSchedule = false;
                    string action = null;
                    string oriArticleId = null;
                    string downloadurl = null;
                    List<string> productUid = new List<string>();
                    foreach (JToken joData in i_jaData)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                        if (dicData.ContainsKey("isProductValid"))
                        {
                            if (Convert.ToBoolean(dicData["isProductValid"]) == false)
                            {
                                sMsg = "商品價格錯誤";
                                break;
                            }
                        }
                        JObject jalldata = dicData[BLWording.DATA] as JObject;
                        downloadurl = dicData[GrpBLWording.GRP_DOWNLOADURL].ToString();
                        oriArticleId = jalldata[tb_grp_article.CN_UID].ToString();


                        sMsg = getManualLog(i_crmInput, dicData, BLWording.LOG_ACTION_NAME_INSERTSQL, out Command cLog);

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

                        if (cLog != null)
                        {
                            lCmds.Add(cLog);
                        }

                        if (dicData.ContainsKey("action"))
                        {
                            action = dicData["action"].ToString();

                        }
                        // 判斷有無release_date 來決定是否排程與是否發佈
                        if (jalldata.ContainsKey(tb_grp_article.CN_RELEASE_DATE))
                        {
                            bool isDateTime = DateTime.TryParse(jalldata[tb_grp_article.CN_RELEASE_DATE].ToString(), out DateTime release_date);
                            if (isDateTime && release_date < DateTime.Now.AddMinutes(-30) && action != "Save")
                            {
                                sMsg = "datetime wrong";
                                break;
                            }
                            if (isDateTime && action != "Save")
                            {
                                isSchedule = true;
                            }
                            else if (isDateTime && action == "Save")
                            {
                                isSchedule = true;
                            }
                        }
                        // 判斷是否為草稿
                        if (jalldata.ContainsKey(tb_grp_article.CN_POST_STATUS))
                        {
                            post_status = jalldata[tb_grp_article.CN_POST_STATUS].ToString();
                        }
                        if (jalldata.ContainsKey("opost_status"))
                        {
                            opost_status = jalldata["opost_status"].ToString();
                        }



                        //List<Command> lcMst = getUpdListCommand(i_crmInput, i_jaData, typeof(tb_grp_article).GetField("TABLENAME").GetValue(null).ToString(), i_saQryContainKeys, out sMsg);

                        //判斷是否為Establish 如果是就不Update
                        //if (action == "Establish")
                        //{
                        //    //lCmds.Add(lcMst[0]);
                        //    continue;
                        //}

                        var oData = new tb_grp_article();
                        if (jalldata[tb_grp_article.CN_CLOSE_DATE].ToString() == "")
                        {
                            oData.close_date = null;
                        }
                        else
                        {
                            oData.close_date = Convert.ToDateTime(jalldata[tb_grp_article.CN_CLOSE_DATE].ToString());
                        }
                        if (jalldata[tb_grp_article.CN_RELEASE_DATE].ToString() == "")
                        {
                            oData.release_date = null;
                        }
                        else
                        {
                            oData.release_date = Convert.ToDateTime(jalldata[tb_grp_article.CN_RELEASE_DATE].ToString());
                        }
                        oData.message = jalldata[tb_grp_article.CN_MESSAGE].ToString();
                        oData.name = jalldata[tb_grp_article.CN_NAME].ToString();
                        oData.group_uid = jalldata[tb_grp_article.CN_GROUP_UID].ToString();
                        oData.remark= jalldata[tb_grp_article.CN_REMARK].ToString();
                        oData.dutyfree = jalldata[tb_grp_article.CN_DUTYFREE].ToString().ToLower() == "true" ? "1" : "0";
                        if (int.Parse(opost_status) > 2)
                        {
                            if (action == "Post")
                            {
                                oData.post_status = int.Parse(opost_status);
                                post_status = opost_status;
                            }
                        }
                        else
                        {
                            if (action == "Post")
                            {
                                oData.post_status = int.Parse(post_status);
                            }
                        }
                        if (action != "Establish" && action != "Post")
                        {
                            Command c = Command.SetupUpdateCmd(oData,
    new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), oriArticleId));
                            lCmds.Add(c);

                        }

                        if (sMsg != null)
                        {
                            break;
                        }
                        //lCmds.AddRange(lcMst);

                        if (!dicData.ContainsKey(BLWording.DATA))
                        {
                            continue;
                        }
                        JObject jdata = dicData[BLWording.DATA] as JObject;
                        JObject jwheredata = dicData[BLWording.WHEREDATA] as JObject;
                        Dictionary<string, object> dicWhere = new Dictionary<string, object>();
                        if (null != JProperty2Dic(jwheredata, ref dicWhere))
                        {
                            sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
                            break;
                        }
                        Dictionary<string, object> dicInput = jdata.ToObject<Dictionary<string, object>>();

                        string sArticleUid = dicWhere[tb_grp_article.CN_UID].ToString();


                        if (dicInput.ContainsKey(nameof(tb_grp_article_media)))
                        {
                            tb_grp_article_media a2mDisplay = new tb_grp_article_media();
                            a2mDisplay.SetFullDirty();
                            tb_grp_article_media a2m = new tb_grp_article_media()
                            {
                                article_uid = sArticleUid
                            };

                            Command cSelecta2m = Command.SetupSelectCmd(a2mDisplay, a2m);

                            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelecta2m, GetDefaultSystemColumnInfo());

                            List<tb_grp_article_media> la2mOri = ai.RunQueryList<tb_grp_article_media>(cSelecta2m);

                            if (!cSelecta2m.IsSuccess)
                            {
                                sMsg = cSelecta2m.LastErrorCode;
                                break;
                            }
                            List<MediaInfo> la2mFrontEnd = (dicInput[nameof(tb_grp_article_media)] as JArray).ToObject<List<MediaInfo>>();

                            var seq = 1;
                            // Only in dicInput => Add
                            la2mFrontEnd.ForEach(f =>
                            {

                                tb_grp_article_media a2mMatch = la2mOri.FirstOrDefault(g => g.uid == f.uid);
                                if (f.uid == BLWording.CREATEUID || a2mMatch == null)
                                {
                                    lCmds.Add(Command.SetupInsertCmd(new tb_grp_article_media()
                                    {
                                        uid = Guid.NewGuid().ToString(),
                                        article_uid = sArticleUid,
                                        media_id = f.display.Substring(f.display.IndexOf('=') + 1),
                                        type = f.mediatype,
                                        status_flag = BLWording.STATUS_FLAG_ON
                                    }));
                                }
                                else
                                {
                                    // update seq
                                    lCmds.Add(Command.SetupUpdateCmd(new tb_grp_article_media()
                                    {
                                        seq = seq++
                                    },
                                    new tb_grp_article_media()
                                    {
                                        uid = f.uid
                                    }
                                    ));
                                }
                            });
                            // Only in la2mOri => Delete

                            la2mOri.ForEach(f =>
                            {
                                MediaInfo a2mMatch = la2mFrontEnd.FirstOrDefault(g => g.uid == f.uid);
                                if (a2mMatch == null)
                                {
                                    lCmds.Add(Command.SetupDeleteCmd(
                                           new tb_grp_article_media()
                                           {
                                               uid = f.uid
                                           }
                                        ));
                                }
                            });
                        }

                        if (dicInput.ContainsKey(nameof(tb_prd_article2product)))
                        {
                            tb_prd_article2product a2pDisplay = new tb_prd_article2product();
                            a2pDisplay.SetFullDirty();
                            tb_prd_article2product a2p = new tb_prd_article2product()
                            {
                                article_uid = sArticleUid
                            };

                            Command cSelectA2P = Command.SetupSelectCmd(a2pDisplay, a2p);

                            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelectA2P, GetDefaultSystemColumnInfo());

                            List<tb_prd_article2product> la2pOri = ai.RunQueryList<tb_prd_article2product>(cSelectA2P);

                            if (!cSelectA2P.IsSuccess)
                            {
                                sMsg = cSelectA2P.LastErrorCode;
                                break;
                            }
                            List<tb_prd_article2product> la2pFrontEnd = (dicInput[nameof(tb_prd_article2product)] as JArray).ToObject<List<tb_prd_article2product>>();


                            // Only in dicInput => Add
                            la2pFrontEnd.ForEach(f =>
                            {
                                string sProductUid = f.prd_uid;

                                if (sProductUid == null)
                                {
                                    sProductUid = Guid.NewGuid().ToString();
                                    productUid.Add(sProductUid);
                                    // Create Product first                                                                
                                    lCmds.Add(Command.SetupInsertCmd(new tb_prd_product()
                                    {
                                        uid = sProductUid,
                                        name = f.name,
                                        group_id = dicInput[tb_grp_article.CN_GROUP_UID].ToString(),
                                        price = f.price,
                                        wholesale_price = f.wholesale_price,
                                        cost_price = f.cost_price
                                    }));
                                }

                                tb_prd_article2product a2pMatch = la2pOri.FirstOrDefault(g => g.uid == f.uid);
                                if (f.uid == BLWording.CREATEUID || a2pMatch == null)
                                {
                                    lCmds.Add(Command.SetupInsertCmd(new tb_prd_article2product()
                                    {
                                        uid = Guid.NewGuid().ToString(),
                                        price = f.price,
                                        wholesale_price = f.wholesale_price,
                                        name = f.name,
                                        article_uid = sArticleUid,
                                        prd_uid = sProductUid,
                                        seq = f.seq,
                                        specification = f.specification.ToUpper(),
                                        cost_price = f.cost_price
                                    }));
                                }
                                else
                                {
                                    // Update
                                    lCmds.Add(Command.SetupUpdateCmd(
                                         new tb_prd_article2product()
                                         {
                                             price = f.price,
                                             wholesale_price = f.wholesale_price,
                                             name = f.name,
                                             article_uid = sArticleUid,
                                             prd_uid = sProductUid,
                                             seq = f.seq,
                                             specification = f.specification.ToUpper(),
                                             cost_price = f.cost_price
                                         },
                                         new tb_prd_article2product()
                                         {
                                             uid = a2pMatch.uid
                                         }
                                        ));

                                    lCmds.Add(Command.SetupUpdateCmd(
                                         new tb_prd_product()
                                         {
                                             name = f.name
                                         },
                                         new tb_prd_product()
                                         {
                                             uid = sProductUid
                                         }
                                        ));
                                }
                            });
                            // Only in la2pOri => Delete

                            la2pOri.ForEach(f =>
                            {
                                tb_prd_article2product a2pMatch = la2pFrontEnd.FirstOrDefault(g => g.uid == f.uid);
                                if (a2pMatch == null)
                                {
                                    if (int.Parse(opost_status) >= (int)Enums.EPostStatus.EPS_ORDER)
                                    {
                                        sMsg = "已結單文章不得刪除商品";
                                        return;
                                    }
                                    lCmds.Add(Command.SetupDeleteCmd(
                                           new tb_prd_article2product()
                                           {
                                               uid = f.uid
                                           }
                                        ));
                                }
                            });

                        }

                    }

                    if (sMsg != null)
                    {
                        break;
                    }

                    ArsenalInterface ai_upd = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());

                    int iRes = ai_upd.RunEditCmds(lCmds);

                    sMsg = GetLastErrorCode(lCmds);

                    if (sMsg != null)
                    {
                        sMsg = GetLastErrorMessage(lCmds);
                        break;
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.AFFECTCOUNT, iRes);

                    if (sMsg != null)
                    {
                        break;
                    }
                    // 開始判斷各種Action邏輯

                    string newArticleId = null;
                    if (int.Parse(opost_status) > 2) // status > 2 為已發文
                    {
                        if (isSchedule)
                        {
                            if (action == "Establish") // 是Establish就新增為底稿
                            {
                                createCopyArticle(i_crmInput, i_jaData, 1, productUid);
                            }
                            else if (action == "Post") // 是Post新增為非底稿
                            {
                                createCopyArticle(i_crmInput, i_jaData, 0, productUid);
                            }
                        }
                        else
                        {
                            if (action == "Establish") // 是Establish就新增為底稿
                            {
                                createCopyArticle(i_crmInput, i_jaData, 1, productUid);
                            }
                            else if (action == "Post") // 是Post新增為非底稿
                            {
                                newArticleId = createCopyArticle(i_crmInput, i_jaData, 0, productUid);
                            }
                        }
                    }
                    else
                    {
                        if (action == "Establish") // 非上傳文章之Establish 新增為底稿
                        {
                            newArticleId = createCopyArticle(i_crmInput, i_jaData, 1, productUid);
                        }
                    }

                    // 非排程且action為Post 就直接上傳
                    if (!isSchedule && action == "Post")
                    {
                        if (newArticleId != null)
                        {
                            sMsg = _PushPostDetail(newArticleId, downloadurl, true, out _);
                        }
                        else
                        {
                            sMsg = _PushPostDetail(oriArticleId, downloadurl, true, out _);
                        }
                    }

                    if (sMsg != null)
                    {
                        break;
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(UpdateArticle)} 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>
        /// 與原版的UpdateArticle差別在只更新社團ID進去
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage UpdateArticleManual(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            CResponseMessage crmRes = null;
            JArray i_jaData = i_crmInput.param[BLWording.UPD_MASTER] as JArray;
            try
            {
                do
                {
                    lCmds = getUpdListCommand(i_crmInput, i_jaData, typeof(tb_grp_article).GetField("TABLENAME").GetValue(null).ToString(), new List<string>() { "uid" }, out sMsg);

                    ArsenalInterface ai_upd = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());

                    int iRes = ai_upd.RunEditCmds(lCmds);
                    
                    sMsg = GetLastErrorCode(lCmds);

                    if (sMsg != null)
                    {
                        sMsg = GetLastErrorMessage(lCmds);
                        break;
                    }

                    crmRes = new CSuccessResponseMessage("", i_crmInput);

                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(UpdateArticle)} 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;
        }

        public CResponseMessage SearchArticle(CRequestMessage i_crmInput)
        {
            string sMsg;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    List<string> lsEmpColumns = EntityBase.GetAllColumnName(typeof(tb_grp_article));

                    // 取得condition
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);

                    // 取回文章ID跟UserToken
                    sMsg = GetUserToken(dicCondition[tb_grp_article.CN_GROUP_UID].ToString(), out List<GroupViewModel> qdGroup);

                    if (sMsg != null)
                    {
                        break;
                    }

                    var Articledata = new FbHelper().CallFbPostGetDataAPI(qdGroup.First().user_token, qdGroup.First().fb_group_id);


                    //QueryJsonElement qjeArticle = lBlocks.GetInst();
                    //qjeArticle.table = tb_grp_article.TABLENAME;
                    //qjeArticle.displaycols = lsEmpColumns;
                    //List<WhereNode> lwnMainInfo = new List<WhereNode>();
                    //lwnMainInfo.Add(new WhereNode(tb_grp_article.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_article), lsBranch.ToArray()));
                    //lwnMainInfo.Add(new WhereNode(tb_grp_article.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), BLWording.STATUS_FLAG_ON));
                    //if (dicCondition.ContainsKey(tb_grp_article.CN_NAME))
                    //{
                    //    lwnMainInfo.Add(new WhereNode(tb_grp_article.CN_NAME, WhereNode.EColumnOperation.EOT_LIKE, typeof(tb_grp_article), dicCondition[tb_grp_article.CN_NAME].ToString()));
                    //}
                    //qjeArticle.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnMainInfo.ToArray());
                    //lBlocks.Add(qjeArticle);

                    //sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRead);

                    //ArsenalInterface ai = ArsenalDBMgr.GetInst(cRead);
                    //var qds = ai.RunQueryList<ArticleMediaInfo>(cRead);
                    //var groupData = qds.OrderByDescending(x => x.create_date).GroupBy(x => new { x.draft_uid }).ToList();
                    string filter = dicCondition.ContainsKey(tb_grp_article.CN_NAME) ? dicCondition[tb_grp_article.CN_NAME].ToString() : "";
                    var res = Articledata.data.Where(x =>
                    {
                        if (x.message != null)
                        {
                            return x.message.Contains(filter);
                        }
                        return false;
                    }).Select(x => new
                    {
                        x.full_picture,
                        x.id,
                        x.message,
                        x.source,
                        x.created_time
                    }).ToList();
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, res);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(SearchArticle)} 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;
        }

        public CResponseMessage ImportArticle(CRequestMessage i_crmInput)
        {
            string sMsg;
            CResponseMessage crmRes = null;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    JArray i_jaData = i_crmInput.param[BLWording.ADD_MASTER] as JArray;

                    foreach (JToken joData in i_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("isProductValid"))
                        {
                            if (Convert.ToBoolean(dicInput["isProductValid"]) == false)
                            {
                                sMsg = "商品價格錯誤";
                                crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                                break;
                            }
                        }
                        if (!dicInput.ContainsKey("id"))
                        {
                            sMsg = "文章資料錯誤";
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }
                        // 先查詢資料是否存在
                        tb_grp_article cArticle = new tb_grp_article();
                        cArticle.SetFullDirty();
                        tb_grp_article cArticleCon = new tb_grp_article() { fb_article_id = dicInput["id"].ToString(), status_flag = BLWording.STATUS_FLAG_ON };
                        Command cArticleSelect = Command.SetupSelectCmd(cArticle, cArticleCon);
                        ArsenalInterface ai = ArsenalDBMgr.GetInst(cArticleSelect);
                        tb_grp_article qdsArticle = ai.RunQuerySingleORM<tb_grp_article>(cArticleSelect);

                        if (qdsArticle != null)
                        {
                            sMsg = "此文章已存在於系統";
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }

                        string sNewArticleUid = Guid.NewGuid().ToString();
                        string newArticleMediaUid = Guid.NewGuid().ToString();
                        string newArticleMediaId = Guid.NewGuid().ToString();
                        string newArticleMediaName = string.Format(CultureInfo.CurrentCulture, "{0}_{1}", (i_crmInput.customparam[BLWording.SESSION_USER] as tb_sys_session).update_user_uid, DateTime.Now.ToString("yyyyMMddHHMMssfff", CultureInfo.CurrentCulture));
                        WebClient webClient = new WebClient();
                        string filePath = Path.Combine(RootPath, SystemSettingHelper.FileFolder(SystemSettingHelper.EPath.EP_FILETMP)) + "\\" + newArticleMediaName + ".jpg";

                        // if FB Post has full_picture
                        if (!dicInput.ContainsKey("full_picture"))
                        {
                            sMsg = "欲匯入文章沒有圖片";
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }

                        webClient.DownloadFile(dicInput["full_picture"].ToString(), filePath);

                        FileUploadHelper fuh = new FileUploadHelper(GetFileUploadInfo(), i_crmInput);
                        sMsg = fuh.UploadLocalFile(filePath, out tb_sys_uploadlog ulRes);

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

                        lCmds.Add(Command.SetupInsertCmd(new tb_grp_article_media()
                        {
                            uid = newArticleMediaUid,
                            article_uid = sNewArticleUid,
                            media_id = ulRes.uid,
                            type = "photo",
                            seq = 1,
                            status_flag = BLWording.STATUS_FLAG_ON
                        }));

                        tb_grp_article cInsert = new tb_grp_article()
                        {
                            uid = sNewArticleUid,
                            name = (dicInput[tb_grp_article.CN_NAME] ?? "").ToString(),
                            group_uid = (dicInput[tb_grp_article.CN_GROUP_UID] ?? "").ToString(),
                            fb_article_id = (dicInput["id"] ?? "").ToString(),
                            post_date = Convert.ToDateTime(dicInput["created_time"]),
                            message = dicInput[tb_grp_article.CN_MESSAGE].ToString(),
                            post_status = (int)Enums.EPostStatus.EPS_COMPLETE,
                            draft = 1,
                            draft_uid = sNewArticleUid
                        };
                        // 發布時間可能為空
                        if (dicInput.ContainsKey(tb_grp_article.CN_RELEASE_DATE) && dicInput[tb_grp_article.CN_RELEASE_DATE] != null)
                        {
                            cInsert.release_date = Convert.ToDateTime(dicInput[tb_grp_article.CN_RELEASE_DATE].ToString());
                        }
                        Command c = Command.SetupInsertCmd(cInsert);
                        lCmds.Add(c);

                        // Log
                        sMsg = getManualLog(i_crmInput, dicData, BLWording.LOG_ACTION_NAME_INSERTSQL, out Command cLog);

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

                        if (cLog != null)
                        {
                            lCmds.Add(cLog);
                        }

                        string sProductKey = nameof(tb_prd_article2product);
                        if (!jdata.ContainsKey(sProductKey))
                        {
                            sMsg = "grp.no_product";
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }

                        List<tb_prd_article2product> la2pProducts = jdata[sProductKey].ToObject<List<tb_prd_article2product>>();
                        if (!la2pProducts.Any())
                        {
                            sMsg = "grp.no_product";
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }
                        new Article2ProductService().CreateProduct(sNewArticleUid, la2pProducts, cInsert.group_uid, ref lCmds);

                        ArsenalInterface ai_upd = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());

                        int iRes = ai_upd.RunEditCmds(lCmds);

                        sMsg = GetLastErrorCode(lCmds);

                        if (sMsg != null)
                        {
                            sMsg = GetLastErrorMessage(lCmds);
                            crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                            break;
                        }

                        crmRes = new CSuccessResponseMessage(null, i_crmInput);
                        crmRes.param.Add(BLWording.AFFECTCOUNT, iRes);
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(ImportArticle)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
                crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
#endif
            }
            return crmRes;
        }
        public CResponseMessage BindFbPostToArticle(CRequestMessage i_crmInput)
        {
            string sMsg;
            CResponseMessage crmRes = null;
            List<Command> lCmds = new List<Command>();

            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.UPD_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession);

                    if (sMsg != null)
                    {
                        break;
                    }
                    if (jaDataArray.Count != 1 || jaDataArray[0] == null)
                    {
                        sMsg = MessageWording.PARAM_NOT_EXPECTED;
                        break;
                    }

                    JObject joData = jaDataArray[0] as JObject;
                    Dictionary<string, object> dicCondition = new Dictionary<string, object>();

                    if (joData != null)
                    {
                        dicCondition = joData[BLWording.DATA].ToObject<Dictionary<string, object>>();
                    }

                    // 判斷為社團ID還是分店ID
                    sMsg = GetUserToken(dicCondition[tb_grp_article.CN_GROUP_UID].ToString(), out List<GroupViewModel> qdGroup);

                    if (sMsg != null || qdGroup == null)
                    {
                        sMsg = "尋找社團資訊時發生了問題";
                        break;
                    }

                    qdGroup = qdGroup.Where(x => x.status_flag == BLWording.STATUS_FLAG_ON).ToList();

                    if(qdGroup.Count == 0)
                    {
                        sMsg = "尋找社團資訊時發生了問題";
                        break;
                    }

                    if (qdGroup.First().fb_group_id != dicCondition[tb_grp_group.CN_FB_GROUP_ID].ToString())
                    {
                        sMsg = "社團網址錯誤";
                        break;
                    }

                    var Articledata = new FbHelper().CallSingleFbPostGetDataAPI(qdGroup.First().user_token, dicCondition[tb_grp_article.CN_FB_ARTICLE_ID].ToString().Split("_")[1]);
                    if (Articledata == null)
                    {
                        sMsg = "與FB貼文連結失敗";
                        break;
                    }

                    tb_grp_article upData = new tb_grp_article()
                    {
                        fb_article_id = dicCondition[tb_grp_article.CN_FB_ARTICLE_ID].ToString(),
                        post_date = Articledata.created_time,
                        post_status = (int)Enums.EPostStatus.EPS_COMPLETE
                    };
                    tb_grp_article upCon = new tb_grp_article() { uid = dicCondition[tb_grp_article.CN_UID].ToString() };
                    lCmds.Add(Command.SetupUpdateCmd(upData, upCon));

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

                    if (sMsg != null)
                    {
                        sMsg = GetLastErrorMessage(lCmds);
                        crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
                        break;
                    }

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

        }
        private string GetUserToken(string groupUid, out List<GroupViewModel> qdGroup)
        {
            string sMsg = null;
            // 取回文章ID跟UserToken
            QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

            QueryJsonElement qjeGroup = lBlocks.GetInst();
            qjeGroup.table = tb_grp_group.TABLENAME;
            qjeGroup.displaycols = new List<string>()
                    {
                        tb_grp_group.CN_FB_GROUP_ID
                    };
            qjeGroup.wherecols = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), groupUid);
            lBlocks.Add(qjeGroup);

            QueryJsonElement qjeGroupUser = lBlocks.GetInst();
            qjeGroupUser.table = tb_grp_group2user.TABLENAME;
            qjeGroupUser.jointable = qjeGroup;
            qjeGroupUser.jointype = QueryJsonElement.LEFT_JOIN;
            qjeGroupUser.joincols = new Dictionary<string, string> { { tb_grp_group2user.CN_GROUP_UID, tb_grp_group.CN_UID } };
            qjeGroupUser.displaycols = new List<string>()
                    {
                        tb_grp_group2user.CN_USER_TOKEN,
                        tb_grp_group2user.CN_STATUS_FLAG
                    };
            lBlocks.Add(qjeGroupUser);

            sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);

            if (sMsg != null)
            {
                qdGroup = null;
                return "error";
            }
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
            qdGroup = ai.RunQueryList<GroupViewModel>(cRes);
            sMsg = GetLastErrorCode(cRes);
            if (sMsg != null)
            {
                return sMsg;
            }
            return null;
        }

        /// <summary>
        /// 自動匯入FB社團文章ID, 處理使用發文API後FB回傳發文失敗錯誤訊息(Internal Server Error), 但文章還是會發送去社團的狀況
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        //public CResponseMessage AutoImportArticle(CRequestMessage i_crmInput)
        //{
        //    var information = "";
        //    var grabDate = 10;
        //    int groupArticleTotalCount = 0, matchFbPosts = 0, updateSuccessCount = 0;
        //    var articleGroup = GetIntradayNoFbPostIdArticle(grabDate).GroupBy(x => x.fb_group_id);

        //    foreach (var articles in articleGroup)
        //    {
        //        FbGetPost getPost = new FbHelper().CallFbPostGetDataAPI(articles.FirstOrDefault().user_token, articles.Key);
        //        //FbGetPost getPost = new FbHelper().CallFbPostGetDataAPI(articles.FirstOrDefault().user_token, articles.Key, grabDate);// 找出指定群組內的貼文
        //        matchFbPosts = 0; 
        //        updateSuccessCount = 0;
        //        groupArticleTotalCount = articles.Count();

        //        foreach (var article in articles)
        //        {
        //            // 取發布貼文日期正負10分鐘的同名貼文
        //            var fbPost = getPost.data.Where(
        //                x => x.message != null &&
        //                x.message.Contains(article.name) &&
        //                x.created_time > article.release_date.AddMinutes(-10) && 
        //                x.created_time < article.release_date.AddMinutes(10)
        //            ).FirstOrDefault();

        //            if (fbPost != null)
        //            {
        //                matchFbPosts++;

        //                if (UpdateArticleFbPostId(article.uid, fbPost.id))
        //                {
        //                    updateSuccessCount++;
        //                }
        //            }
        //        }

        //        information += $@"{nameof(AutoImportArticle)} porcess FB group id: {articles.FirstOrDefault()?.fb_group_id} Complete, {nameof(groupArticleTotalCount)} = {groupArticleTotalCount}, {nameof(matchFbPosts)} = {matchFbPosts}, {nameof(updateSuccessCount)} = {updateSuccessCount} .";
        //    }

        //    Logger.Debug($@"{information}");// 紀錄匹配文章數量與db更新成功數量
        //    CResponseMessage crmRes = new CSuccessResponseMessage(
        //        $"{information}",
        //        i_crmInput
        //    );

        //    return crmRes;
        //}

        /// <summary>
        /// 取得當天所有已發布貼文卻沒有FB貼文ID的文章
        /// </summary>
        /// <returns></returns>
        private List<GroupArticleModel> GetIntradayNoFbPostIdArticle(int grabDate_)
        {
            string sMsg = null;
            QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

            QueryJsonElement article = lBlocks.GetInst();
            article.table = tb_grp_article.TABLENAME;
            article.displaycols = new List<string>()
            {
                tb_grp_article.CN_UID,
                tb_grp_article.CN_NAME,
                tb_grp_article.CN_GROUP_UID,
                tb_grp_article.CN_RELEASE_DATE,
            };
            article.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND,
                new WhereNode(tb_grp_article.CN_FB_ARTICLE_ID, WhereNode.EColumnOperation.EOT_ISNULL, typeof(tb_grp_article)),
                //new WhereNode(tb_grp_article.CN_POST_DATE, WhereNode.EColumnOperation.EOT_GT, typeof(tb_grp_article), DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd HH:mm:ss"))
                new WhereNode(tb_grp_article.CN_RELEASE_DATE, WhereNode.EColumnOperation.EOT_GT, typeof(tb_grp_article), DateTime.Now.AddDays(-grabDate_).ToString("yyyy/MM/dd HH:mm:ss"))
            );

            QueryJsonElement group = lBlocks.GetInst();
            group.table = tb_grp_group.TABLENAME;
            group.displaycols = new List<string>()
            {
                tb_grp_group.CN_FB_GROUP_ID
            };
            group.jointable = article;
            group.joincols = new Dictionary<string, string>() {
                            {  tb_grp_group.CN_UID,tb_grp_article.CN_GROUP_UID }};

            QueryJsonElement group2user = lBlocks.GetInst();
            group2user.table = tb_grp_group2user.TABLENAME;
            group2user.displaycols = new List<string>()
            {
                tb_grp_group2user.CN_USER_TOKEN
            };
            group2user.jointable = article;
            group2user.joincols = new Dictionary<string, string>() {
                            {  tb_grp_group2user.CN_GROUP_UID,tb_grp_article.CN_GROUP_UID }};

            lBlocks.Add(article);
            lBlocks.Add(group);
            lBlocks.Add(group2user);

            sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);

            if (sMsg != null)
            {
                throw new Exception(sMsg);
            }
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
            var result = ai.RunQueryList<GroupArticleModel>(cRes);
            sMsg = GetLastErrorCode(cRes);
            if (sMsg != null)
            {
                throw new Exception(sMsg);
            }

            return result;
        }

        /// <summary>
        /// 使用UID更新fb_article_id
        /// </summary>
        /// <param name="article_uid_"></param>
        /// <param name="fb_article_id_"></param>
        /// <returns></returns>
        private bool UpdateArticleFbPostId(string article_uid_, string fb_article_id_)
        {
            var sMsg = "";
            var Commands = new List<Command>();
            
            var oData = new tb_grp_article();
            oData.fb_article_id = fb_article_id_;// Just update Data

            Command c = Command.SetupUpdateCmd(oData,
                new WhereNode(tb_grp_article.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_article), article_uid_));

            Commands.Add(c);

            ArsenalInterface ai = ArsenalDBMgr.GetInst(Commands[0], GetDefaultSystemColumnInfo());
            ai.RunEditCmds(Commands);
            sMsg = GetLastErrorCode(Commands);
            if (sMsg != null)
            {
                Logger.Error($"{nameof(UpdateArticleFbPostId)} => {sMsg}");
                return false;
            }

            return true;
        }
    }
}