GroupBy/04-程式設計/Trunk/GroupBuy/CounsellorBL.ORD/OrderService.cs

using CounsellorBL.BLStructure;
using CounsellorBL.Common;
using CounsellorBL.GROUP;
using CounsellorBL.Helper;
using CounsellorBL.ORD.ConstDefinition;
using MonumentDefine;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using OT.COM.ArsenalDB;
using OT.COM.ArsenalDB.SQL;
using OT.COM.SignalerMessage;
using SoldierData.EnterprizeV4;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using WebMvcEF.Extensions;
using static MonumentDefine.Enums;
using Util = OT.COM.LogisticsUtil.Util;

namespace CounsellorBL.ORD
{
    public class OrderService : SingleDataTableTemplate<tb_ord_order_detail>
    {
        public OrderService()
        {
            dgReadCommandPostDataHandler = readCommandPostDataHandler;
            dgUpdateCommandGenerator = updateCommandGenerator;
            dgDeleteCommandGenerator = deleteCommandGenerator;
        }

        public new CResponseMessage Read(CRequestMessage i_crmInput) => base.Read(i_crmInput);

        public new CResponseMessage Update(CRequestMessage i_crmInput) => base.Update(i_crmInput);

        private class OrderDetail
        {
            public string name { get; set; }
            public string article_name { get; set; }
            public string uid { get; set; }
            public string order_uid { get; set; }
            public string article2product_uid { get; set; }
            public string member_uid { get; set; }
            public string member_name { get; set; }
            public int order_qty { get; set; }
            public int take_qty { get; set; }
            public int price { get; set; }
            public int wholesale_price { get; set; }
            public int status { get; set; }
            public string branch_name { get; set; }
            public int qty { get; set; }
            public string branch_uid { get; set; }
            public int seq { get; set; }
            public string memo { get; set; }
            public DateTime comment_time { get; set; }
            public int wprice_payment { get; set; }
            public string order_name { get; set; }
            public int purchase_amount { get; set; }
            public string purchase_order_uid { get; set; }
            public DateTime purchase_create_date { get; set; }
            public string specification { get; set; }
            public DateTime create_date { get; set; }
            public string dutyfree{ get; set; }
            public int branch_seq { get; set; }
        }

        private class GroupBranch
        {
            /// <summary>
            /// 社團名稱
            /// </summary>
            public string name { get; set; }
            public string branch_name { get; set; }
            /// <summary>
            /// 分店uid
            /// </summary>
            public string uid { get; set; }
            public string address { get; set;}
            public string phone_number { get; set; }
            public string contact_person { get; set; }
        }

        private class OrderResultData
        {
            public List<OrderMaster> records { get; set; }
            public int totalCount { get; set; }
        };
  

        [Auth(false)]
        public CResponseMessage ReadDetail(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            Command cRes = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    var command = Command.SetupSelectCmd(new tb_meb_member(), new tb_meb_member());

                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);   // 取得condition
                    string[] aMemberUID = new string[0];
                    List<OrderDetail> allData = new List<OrderDetail>();
                    if (dicCondition.ContainsKey(tb_meb_member.CN_UID))
                    {
                        if (dicCondition[tb_meb_member.CN_UID] != null)
                        {
                            var jaMemberUID = dicCondition[tb_meb_member.CN_UID] as JArray;
                            aMemberUID = jaMemberUID.Select(x => x.ToString()).ToArray();
                        }
                    }

                        string[] slicedArray = aMemberUID.ToArray();
                        QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                        QueryJsonElement qjeA = lBlocks.GetInst();
                        qjeA.table = tb_ord_order_detail.TABLENAME;
                        qjeA.displaycols = new List<string>()
                    {
                        tb_ord_order_detail.CN_ORDER_QTY,
                        tb_ord_order_detail.CN_TAKE_QTY,
                        tb_ord_order_detail.CN_STATUS,
                        tb_ord_order_detail.CN_MEMBER_UID,
                        tb_ord_order_detail.CN_SPECIFICATION,
                        tb_ord_order_detail.CN_COMMENT_TIME,
                        tb_ord_order_detail.CN_MEMO,
                        tb_ord_order_detail.CN_ORDER_UID
                    };
                        List<WhereNode> lswnMain = new List<WhereNode>();
                        if (dicCondition.ContainsKey(tb_ord_order_detail.CN_ORDER_UID))
                        {
                            var jaOrderUID = dicCondition[tb_ord_order_detail.CN_ORDER_UID] as JArray;
                            var aOrderUID = jaOrderUID.Select(x => x.ToString()).ToArray();
                            lswnMain.Add(new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), aOrderUID));
                        }
                        if (dicCondition.ContainsKey(tb_ord_order_detail.CN_STATUS))
                        {
                            lswnMain.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake));
                        }
                        if (lswnMain.Any())
                        {
                            qjeA.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lswnMain.ToArray());
                        }
                        QueryJsonElement qjeMain = lBlocks.GetInst();
                        qjeMain.table = tb_ord_order_master.TABLENAME;
                        qjeMain.jointype = QueryJsonElement.JOIN;
                        qjeMain.jointable = qjeA;
                        qjeMain.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_master.CN_UID,tb_ord_order_detail.CN_ORDER_UID }};
                        qjeMain.displaycols = new List<string>()
                        {
                            tb_ord_order_master.CN_BRANCH_UID,
                            tb_ord_order_master.CN_ORDER_CODE
                        };
                        qjeMain.aliascols = new Dictionary<string, List<string>>
                        {
                             { tb_ord_order_master.CN_NAME, new List<string>() { "order_name" } }
                        };
                        if (!dicCondition.ContainsKey(tb_ord_order_detail.CN_ORDER_UID))
                        {
                            qjeMain.wherecols = new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive);
                        }


                        QueryJsonElement qjeB = lBlocks.GetInst();
                        qjeB.table = tb_meb_member.TABLENAME;
                        qjeB.jointype = QueryJsonElement.LEFT_JOIN;
                        qjeB.jointable = qjeA;
                        qjeB.joincols = new Dictionary<string, string>() {
                             {  tb_meb_member.CN_UID,tb_ord_order_detail.CN_MEMBER_UID }};
                        qjeB.displaycols = new List<string>()
                    {
                        tb_meb_member.CN_WPRICE_PAYMENT,
                    };
                        qjeB.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_meb_member.CN_NAME, new List<string>() { "member_name" } }
                    };
                        List<WhereNode> lswnMainMeb = new List<WhereNode>();

                        if (dicCondition.ContainsKey(tb_meb_member.CN_GROUP_USER_ID) && dicCondition.ContainsKey(tb_meb_member.CN_GROUP_ID))
                        {
                            lswnMainMeb.Add(new WhereNode(tb_meb_member.CN_GROUP_USER_ID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_meb_member), dicCondition[tb_meb_member.CN_GROUP_USER_ID]));
                            lswnMainMeb.Add(new WhereNode(tb_meb_member.CN_GROUP_ID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_meb_member), dicCondition[tb_meb_member.CN_GROUP_ID]));
                            qjeB.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lswnMainMeb.ToArray());
                        }
                        if (dicCondition.ContainsKey(tb_meb_member.CN_UID) && dicCondition[tb_meb_member.CN_UID] != null)
                        {
                            var dbTYpe = GetMasterDBTableInfo(typeof(tb_meb_member));
                            dbTYpe.ConnectString = command.ConnectString;
                            var existSql = Command.SetupDirectSelect(dbTYpe, $"select value from STRING_SPLIT('{string.Join(",", slicedArray)}', ',') where value = c.uid");
                            qjeB.wherecols = new WhereNode(null, WhereNode.EColumnOperation.EOT_EXISTS, typeof(tb_meb_member), existSql);
                        }

                        QueryJsonElement qjeC = lBlocks.GetInst();
                        qjeC.table = tb_grp_branch.TABLENAME;
                        qjeC.jointype = QueryJsonElement.LEFT_JOIN;
                        qjeC.jointable = qjeMain;
                        qjeC.joincols = new Dictionary<string, string>() {
                             {  tb_grp_branch.CN_UID,tb_ord_order_master.CN_BRANCH_UID }};
                        qjeC.displaycols = new List<string>() { tb_grp_branch.CN_BRANCH_NAME };

                        QueryJsonElement qjeD = lBlocks.GetInst();
                        qjeD.table = tb_prd_article2product.TABLENAME;
                        qjeD.jointype = QueryJsonElement.LEFT_JOIN;
                        qjeD.jointable = qjeA;
                        qjeD.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                        qjeD.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_WHOLESALE_PRICE,
                        tb_prd_article2product.CN_UID,
                        tb_prd_article2product.CN_SEQ
                    };

                        QueryJsonElement qjeE = lBlocks.GetInst();
                        qjeE.table = tb_grp_article.TABLENAME;
                        qjeE.jointype = QueryJsonElement.LEFT_JOIN;
                        qjeE.jointable = qjeD;
                        qjeE.joincols = new Dictionary<string, string>() {
                             {  tb_grp_article.CN_UID,tb_prd_article2product.CN_ARTICLE_UID }};
                        qjeE.displaycols = new List<string>() { tb_grp_article.CN_DUTYFREE };
                        qjeE.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_article.CN_NAME, new List<string>() { "article_name" } }
                    };                        

                        QueryJsonElement qjeF = lBlocks.GetInst();
                        qjeF.table = tb_ord_purchase_detail.TABLENAME;
                        qjeF.jointype = QueryJsonElement.LEFT_JOIN;
                        qjeF.jointable = qjeA;
                        qjeF.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase_detail.CN_ORDER_DETAIL_UID,tb_ord_order_detail.CN_UID }};
                        qjeF.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_purchase_detail.CN_AMOUNT, new List<string>() { "purchase_amount" } },
                         { tb_ord_purchase_detail.CN_ORDER_DETAIL_UID, new List<string>() { "purchase_order_uid" } }
                    };

                        lBlocks.Add(qjeA);
                        lBlocks.Add(qjeMain);
                        lBlocks.Add(qjeB);
                        lBlocks.Add(qjeC);
                        lBlocks.Add(qjeD);
                        lBlocks.Add(qjeE);
                        lBlocks.Add(qjeF);

                        sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                        if (sMsg != null)
                        {
                            break;
                        }

                        Stopwatch stopWatch = new Stopwatch();
                        stopWatch.Start();
                        ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                        cRes.ReadLevel = Command.EReadLevel.ERL_DIRTY;
                        var qds = ai.RunQueryList<OrderDetail>(cRes);
                        stopWatch.Stop();
                        TimeSpan ts = stopWatch.Elapsed;

                        // Format and display the TimeSpan value.
                        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);
                        Logger.Info("單次查詢耗時:" + elapsedTime);
                        if (cRes.IsSuccess == false)
                        {
                            sMsg = cRes.LastErrorCode;
                            break;
                        }

                        allData.AddRange(qds);
                    

                    var groupData = allData.GroupBy(x => new { x.member_uid, x.member_name });
                    var oResultData = new List<List<OrderDetail>>();
                    foreach (var data in groupData)
                    {
                        var takedDataList = new List<OrderDetail>();
                        var arrivedGroup = data.GroupBy(x => x.purchase_order_uid);
                        foreach (var takedData in arrivedGroup)
                        {
                            if (takedData.Key == null)
                            {
                                takedDataList.AddRange(takedData);
                            }
                            else
                            {
                                takedDataList.Add(takedData.FirstOrDefault());
                            }
                        }

                        var pro = takedDataList.GroupBy(x => new { x.uid, x.order_uid, x.branch_uid, x.branch_name, x.status, x.specification})
                        .Select(x => new OrderDetail
                        {
                            order_qty = x.Sum(c => c.order_qty),
                            take_qty = x.Sum(c => c.take_qty),
                            price = x.Key.status == (int)Enums.OrderStatus.Taked ? 
                                (
                                    (x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().purchase_amount) /
                                    (x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().take_qty)
                                ) : 
                                x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().price,
                            wholesale_price = x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().wholesale_price,
                            specification = x.Key.specification,
                            uid = x.Key.uid,
                            order_uid = x.Key.order_uid,
                            seq = x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().seq,
                            name = x.FirstOrDefault()?.name,
                            member_uid = x.FirstOrDefault()?.member_uid,
                            member_name = x.FirstOrDefault()?.member_name,
                            status = x.Key.status,
                            article_name = x.FirstOrDefault()?.article_name,
                            order_name = x.FirstOrDefault()?.order_name,
                            memo = x.FirstOrDefault()?.memo,
                            branch_uid = x.Key.branch_uid,
                            branch_name = x.Key.branch_name,
                            comment_time = x.FirstOrDefault() == null ? DateTime.MinValue : x.FirstOrDefault().comment_time,
                            wprice_payment = x.FirstOrDefault() == null ? 0 : x.FirstOrDefault().wprice_payment,
                            dutyfree = x.FirstOrDefault()?.dutyfree
                        })
                        .OrderBy(x => x.status).ToList();

                        oResultData.Add(pro);
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, oResultData);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(ReadDetail)} 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 GetOrderRecord(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);   // 取得condition

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeOrder = lBlocks.GetInst();
                    qjeOrder.table = tb_ord_order_detail.TABLENAME;
                    qjeOrder.displaycols = new List<string>()
                    {
                        tb_ord_order_detail.CN_ORDER_QTY,
                        tb_ord_order_detail.CN_TAKE_QTY,
                        tb_ord_order_detail.CN_STATUS,
                        tb_ord_order_detail.CN_CREATE_DATE,
                        tb_ord_order_detail.CN_OPERATE_DATE,
                        tb_ord_order_detail.CN_SPECIFICATION
                    };
                    qjeOrder.wherecols = new WhereNode(tb_ord_order_detail.CN_MEMBER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), dicCondition[tb_ord_order_detail.CN_MEMBER_UID]);

                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.jointype = QueryJsonElement.JOIN;
                    qjeMain.jointable = qjeOrder;
                    qjeMain.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_master.CN_UID,tb_ord_order_detail.CN_ORDER_UID }};
                    qjeMain.displaycols = new List<string>() { tb_ord_order_master.CN_BRANCH_UID };
                    qjeMain.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "order_name" } }
                    };


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

                    QueryJsonElement qjeArticle2Product = lBlocks.GetInst();
                    qjeArticle2Product.table = tb_prd_article2product.TABLENAME;
                    qjeArticle2Product.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeArticle2Product.jointable = qjeOrder;
                    qjeArticle2Product.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeArticle2Product.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_UID,
                        tb_prd_article2product.CN_SEQ,
                    };

                    QueryJsonElement qjeArticle = lBlocks.GetInst();
                    qjeArticle.table = tb_grp_article.TABLENAME;
                    qjeArticle.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeArticle.jointable = qjeArticle2Product;
                    qjeArticle.joincols = new Dictionary<string, string>() {
                             {  tb_grp_article.CN_UID,tb_prd_article2product.CN_ARTICLE_UID }};
                    qjeArticle.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_article.CN_NAME, new List<string>() { "article_name" } }
                    };

                    lBlocks.Add(qjeOrder);
                    lBlocks.Add(qjeMain);
                    lBlocks.Add(qjeBranch);
                    lBlocks.Add(qjeArticle2Product);
                    lBlocks.Add(qjeArticle);

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

                    var groupData = qds.GroupBy(x => new { x.branch_uid, x.uid, x.specification });
                    var res = new List<OrderDetailEx>();
                    var processStatus = new List<int>() { (int)Enums.OrderStatus.Taked, (int)Enums.OrderStatus.Return, (int)Enums.OrderStatus.Abandon };
                    foreach (var row in groupData)
                    {
                        var dic = row.FirstOrDefault();
                        dic.order_qty = row.Sum(x => Convert.ToInt32(x.order_qty));
                        dic.take_qty = row.Sum(x => Convert.ToInt32(x.take_qty));
                        res.Add(dic);
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, res);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetOrderRecord)} 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 class OrderMasterPage
        {
            public string uid { get; set; }
            public int count { get; set; }
        }
        private class OrderMaster
        {
            public string uid { get; set; }
            public string article_uid { get; set; }
            public int sn { get; set; }
            public string order_code { get; set; }
            public string group_uid { get; set; }
            public string branch_uid { get; set; }
            public string branch_name { get; set; }
            public string name { get; set; }
            public int received { get; set; }            
            public int receivable { get; set; }
            public int receivedOld { get; set; }
            public int receivableOld { get; set; }
            public string progress { get; set; }
            public DateTime? create_date { get; set; }
            public DateTime? arrived_date { get; set; }
            public int status { get; set; }
            public int type { get; set; }
            public string remark { get; set; }
            public string isSame { get; set; }
        }
        private class OrderDetailEx : OrderDetail
        {
            public string group_uid { get; set; }
            public string article_uid { get; set; }
            public string order_code { get; set; }
            public int type { get; set; }
            public int detail_status { get; set; }
            public DateTime? arrived_date { get; set; }
            public DateTime? operate_date { get; set; }

            public int received { get; set; }
            public int receivable { get; set; }
            public string remark { get; set; }
        }

        protected string readCommandPostDataHandler(CRequestMessage i_crmInput, ArsenalInterface i_aiArsenal, Command i_cCmd, JArray i_jaData, tb_sys_session i_sSessionUser, out object o_oReault,
             [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
             [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
             [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            int nPageIdx = -1;
            int nPageNum = -1;
            string sMsg = "";
            object oResultData = null;
         
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.QRY_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession, false);

                    if (sMsg != null)
                    {
                        break;
                    }

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

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

                    var result = readCommandPostDataHandlerRefactor(i_crmInput, dicCondition, lsBranch, userBranch, nPageIdx, nPageNum);

                    //// 新舊資料比對
                    //readCommandPostDataHandlerOld(i_crmInput, i_aiArsenal, i_cCmd, i_jaData, i_sSessionUser, out object oldOrderResult);
                    //string json = JsonConvert.SerializeObject(oldOrderResult);
                    //OrderResultData re = JsonConvert.DeserializeObject<OrderResultData>(json);
                    ////var OrderResultDataRecords = (oldOrderResult as OrderResultData).records;
                    //var OrderResultDataRecords = re.records;
                    //foreach (var entity in result.records)
                    //{
                    //    if (OrderResultDataRecords.Select(x => x.uid).Contains(entity.uid))
                    //    {
                    //        entity.receivedOld = OrderResultDataRecords.Find(x => x.uid == entity.uid).received;
                    //        entity.receivableOld = OrderResultDataRecords.Find(x => x.uid == entity.uid).receivable;
                    //        entity.isSame = entity.receivedOld == entity.received && entity.receivableOld == entity.receivableOld ? "" : "failed";
                    //    }
                    //}

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

            o_oReault = oResultData;
            return sMsg;
        }

        /// <summary>
        /// 保留舊版, 測試用
        /// </summary>
        /// <returns></returns>
        protected string readCommandPostDataHandlerOld(CRequestMessage i_crmInput, ArsenalInterface i_aiArsenal, Command i_cCmd, JArray i_jaData, tb_sys_session i_sSessionUser, out object o_oReault,
             [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
             [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
             [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg;
            Command cRes = null;
            Command cResOrderMst = null;
            object oResultData = null;
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.QRY_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession, false);

                    if (sMsg != null)
                    {
                        break;
                    }


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

                    tb_ord_order_master om = new tb_ord_order_master();
                    om.SetFullDirty();

                    JObject jtDataItem = (JObject)jaDataArray[0];

                    List<Tuple<string, string>> ordercols = null;
                    if (jtDataItem.ContainsKey(BLWording.ORDERDATA))
                    {
                        List<Dictionary<string, string>> lDic = jtDataItem[BLWording.ORDERDATA].ToObject<List<Dictionary<string, string>>>();

                        List<Tuple<string, string>> ltss = new List<Tuple<string, string>>();

                        lDic.ForEach(f =>
                        {
                            string sKey = f.Keys.First();
                            ltss.Add(Tuple.Create(sKey, f[sKey]));
                        });

                        ordercols = ltss;
                    }

                    Command cOrder = Command.SetupSelectCmdByParam(om, null, i_ordercols: ordercols);

                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);   // 取得condition
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);
                    var userBranch = ProjectHelper.GetUserBranch(i_crmInput);
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    bool isGroup = false;
                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.displaycols = new List<string>() {
                        tb_ord_order_master.CN_UID
                    };
                    List<WhereNode> lswnMain = new List<WhereNode>();
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Purchase));
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), BLWording.STATUS_FLAG_ON));
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_start"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_start"]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_end"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_order_master), Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_end"]).AddDays(1)));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_STATUS))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_STATUS]));
                    }
                    else
                    {
                        // 扣除封存訂單
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ORDER_CODE))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ORDER_CODE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_ORDER_CODE]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_start"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ARRIVED_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_start"]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_end"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ARRIVED_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_order_master), Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_end"]).AddDays(1)));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_UID]));
                    }
                    List<WhereNode> lswnGroup = new List<WhereNode>();
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_GROUP_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_GROUP_UID]));
                    }
                    else if (dicCondition.ContainsKey("GroupOrBranch_uid")) // 處理shopping list 搜尋選項 uid 有可能是branch or group
                    {
                        tb_grp_group cGroup = new tb_grp_group();
                        cGroup.SetFullDirty();
                        WhereNode wnGroup = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), dicCondition["GroupOrBranch_uid"].ToString());
                        Command gSelect = Command.SetupSelectCmd(cGroup, wnGroup);
                        ArsenalInterface aiGroup = ArsenalDBMgr.GetInst(gSelect);
                        var qdsGroup = aiGroup.RunQueryList<tb_grp_group>(gSelect);
                        if (qdsGroup.Any())
                        {
                            lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition["GroupOrBranch_uid"].ToString()));
                            isGroup = true;
                        }
                    }
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), lsBranch.ToArray()));

                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_BRANCH_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString()));
                    }
                    else if (dicCondition.ContainsKey("GroupOrBranch_uid") && !isGroup)
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition["GroupOrBranch_uid"].ToString()));
                    }
                    else
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), userBranch.ToArray()));
                    }


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

                    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();
                            lswnMain.Add(new WhereNode(WhereNode.ENodeOperation.ENO_OR, new WhereNode[] { new WhereNode(tb_ord_order_master.CN_ORDER_CODE, WhereNode.EColumnOperation.EOT_LIKE, typeof(tb_ord_order_master), mainFullTextPattern),
                                new WhereNode(tb_ord_order_master.CN_NAME, WhereNode.EColumnOperation.EOT_LIKE, typeof(tb_ord_order_master), mainFullTextPattern) }));
                        }
                    }
                    if (lswnMain.Any())
                    {
                        qjeMain.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lswnMain.ToArray());
                    }
                    List<Tuple<QueryJsonElement, string, string>> mainOrder = new List<Tuple<QueryJsonElement, string, string>>();
                    if (mainOrderData != null &&
                            (
                                mainOrderData.First().Key != "received" ||
                                mainOrderData.First().Key != "receivable"
                            )
                        )
                    {
                        if (mainOrderData.First().Key == tb_grp_branch.CN_BRANCH_NAME)
                        {
                            mainOrder.Add(Tuple.Create(qjeBranch, mainOrderData.First().Key, mainOrderData.First().Value));
                        }
                        else
                        {
                            mainOrder.Add(Tuple.Create(qjeMain, mainOrderData.First().Key, mainOrderData.First().Value));
                        }
                    }
                    else
                    {
                        mainOrder.Add(Tuple.Create(qjeMain, tb_ord_order_master.CN_CREATE_DATE, BLWording.ORDER_DESC));
                    }
                    qjeMain.ordercols = mainOrder;


                    lBlocks.Add(qjeMain);
                    sMsg = MakeSelectJoinByBlocks(lBlocks, out 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_ord_order_master.CN_UID].ToString());
                    }

                    lBlocks = new QueryJsonElementCollection();
                    qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.displaycols = new List<string>() {
                        tb_ord_order_master.CN_UID,
                        tb_ord_order_master.CN_TYPE,
                        tb_ord_order_master.CN_ORDER_CODE,
                        tb_ord_order_master.CN_STATUS,
                        tb_ord_order_master.CN_CREATE_DATE,
                        tb_ord_order_master.CN_GROUP_UID,
                        tb_ord_order_master.CN_BRANCH_UID,
                        tb_ord_order_master.CN_ARRIVED_DATE,
                        tb_ord_order_master.CN_ARTICLE_UID,
                        tb_ord_order_master.CN_NAME
                    };
                    qjeMain.wherecols = new WhereNode(tb_ord_order_master.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), orderMstArray.ToArray());

                    QueryJsonElement qjeOrder = lBlocks.GetInst();
                    qjeOrder.table = tb_ord_order_detail.TABLENAME;
                    qjeOrder.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeOrder.jointable = qjeMain;
                    qjeOrder.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                    qjeOrder.displaycols = new List<string>() {
                        tb_ord_order_detail.CN_ORDER_QTY,
                        tb_ord_order_detail.CN_TAKE_QTY,
                    };
                    qjeOrder.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_detail.CN_STATUS, new List<string>() { "detail_status" } },
                         //{ QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                         //{ QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } },
                    };

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

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

                    QueryJsonElement qjeArticleProduct = lBlocks.GetInst();
                    qjeArticleProduct.table = tb_prd_article2product.TABLENAME;
                    qjeArticleProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeArticleProduct.jointable = qjeOrder;
                    qjeArticleProduct.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeArticleProduct.displaycols = new List<string>() {
                        tb_prd_article2product.CN_PRICE
                    };
                    if (dicCondition.ContainsKey(tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID))
                    {
                        qjeArticleProduct.wherecols = new WhereNode(tb_prd_article2product.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_prd_article2product), dicCondition[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString());
                    }
                    QueryJsonElement qjePurchase = lBlocks.GetInst();
                    qjePurchase.table = tb_ord_purchase_detail.TABLENAME;
                    qjePurchase.jointype = QueryJsonElement.LEFT_JOIN;
                    qjePurchase.jointable = qjeOrder;
                    qjePurchase.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase_detail.CN_ORDER_DETAIL_UID,tb_ord_order_detail.CN_UID }};
                    qjePurchase.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_purchase_detail.CN_AMOUNT, new List<string>() { "purchase_amount" } },
                         { tb_ord_purchase_detail.CN_ORDER_DETAIL_UID, new List<string>() { "purchase_order_uid" } },
                         { tb_ord_purchase_detail.CN_CREATE_DATE, new List<string>() { "purchase_create_date" } }
                    };



                    //qjeArticleProduct.groupcols = new List<Tuple<QueryJsonElement, string>>
                    //{
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ARTICLE_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_TYPE),
                    //   Tuple.Create(qjeMain,tb_ord_order_master.CN_STATUS),
                    //   Tuple.Create(qjeMain,  tb_ord_order_master.CN_CREATE_DATE),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ORDER_CODE),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_BRANCH_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ARRIVED_DATE),
                    //   Tuple.Create(qjeMain, tb_grp_branch.CN_GROUP_UID),
                    //   Tuple.Create(qjeMain, tb_grp_article.CN_NAME),
                    //   Tuple.Create(qjeBranch, tb_grp_branch.CN_BRANCH_NAME),
                    //   Tuple.Create(qjeArticleProduct, tb_prd_article2product.CN_PRICE)
                    //};

                    lBlocks.Add(qjeMain);
                    lBlocks.Add(qjeOrder);
                    lBlocks.Add(qjeGroup);
                    lBlocks.Add(qjeBranch);
                    lBlocks.Add(qjeArticleProduct);
                    lBlocks.Add(qjePurchase);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);

                    if (sMsg != null)
                    {
                        break;
                    }
                    ai = ArsenalDBMgr.GetInst(cRes);
                    var qds = ai.RunQueryList<OrderDetailEx>(cRes);
                    var takedDataList = new List<OrderDetailEx>();
                    var arrivedGroup = qds.GroupBy(x => x.purchase_order_uid).ToList();
                    foreach (var takedData in arrivedGroup)
                    {
                        if (takedData.Key == null)
                        {
                            takedDataList.AddRange(takedData);
                        }
                        else
                        {
                            takedDataList.Add(takedData.OrderByDescending(x => x.purchase_create_date).FirstOrDefault());
                        }
                    }
                    var groupData = takedDataList.GroupBy(x => x.uid).ToList();
                    var res = new List<OrderMaster>();
                    foreach (var data in groupData)
                    {
                        //var received = string.IsNullOrEmpty(data["soldCount"]?.ToString()) || string.IsNullOrEmpty(data[tb_prd_article2product.CN_PRICE]?.ToString()) ? 0 : (int)data["soldCount"] * (int)data[tb_prd_article2product.CN_PRICE];
                        //var receivable = string.IsNullOrEmpty(?.ToString()) || string.IsNullOrEmpty(data[tb_prd_article2product.CN_PRICE]?.ToString()) ? 0 : (int)data["count"] * (int)data[tb_prd_article2product.CN_PRICE];
                        var received = 0;
                        var receivable = 0;
                        foreach (var detail in data)
                        {
                            if (detail.detail_status == (int)Enums.OrderStatus.Taked)
                            {
                                if (detail.take_qty != 0)
                                {
                                    received += detail.purchase_amount;
                                    receivable += detail.order_qty * detail.purchase_amount / detail.take_qty;
                                }
                            }
                            else
                            {
                                received += detail.take_qty * detail.price;
                                receivable += detail.order_qty * detail.price;
                            }
                        }

                        var orderDetail = data.FirstOrDefault();
                        var model = new OrderMaster()
                        {
                            uid = data.Key,
                            received = received,
                            receivable = receivable,
                            progress = receivable == 0 || received == 0 ? "0%" : Math.Round((decimal)received / receivable * 100, 0, MidpointRounding.AwayFromZero).ToString() + "%",

                            article_uid = orderDetail?.article_uid,
                            order_code = orderDetail?.order_code,
                            group_uid = orderDetail?.group_uid,
                            branch_uid = orderDetail?.branch_uid,
                            branch_name = orderDetail?.branch_name,
                            create_date = orderDetail?.create_date,
                            arrived_date = orderDetail?.arrived_date,
                            status = orderDetail == null ? 0 : orderDetail.status,
                            type = orderDetail == null ? 0 : orderDetail.type,
                            name = orderDetail.type == (int)Enums.ArticleType.Stock ? string.Format("【現貨】{0}", orderDetail?.name) : orderDetail?.name,
                        };
                        res.Add(model);
                    }
                    var orderData = res.OrderByDescending(x => x.create_date).ToList();
                    //var orderData = res.GroupBy(x => new
                    //{
                    //    x.uid,
                    //    x.article_uid,
                    //    x.order_code,
                    //    x.group_uid,
                    //    x.branch_uid,
                    //    x.branch_name,
                    //    x.name,
                    //    x.create_date,
                    //    x.arrived_date,
                    //    x.status,
                    //    x.type
                    //}).Select(x => new OrderMaster
                    //{
                    //    uid = x.Key.uid,
                    //    article_uid = x.Key.article_uid,
                    //    order_code = x.Key.order_code,
                    //    group_uid = x.Key.group_uid,
                    //    branch_uid = x.Key.branch_uid,
                    //    branch_name = x.Key.branch_name,
                    //    name = x.Key.name,
                    //    received = x.Sum(c => c.received),
                    //    receivable = x.Sum(c => c.receivable),
                    //    progress = x.Sum(c => c.received) == 0 || x.Sum(c => c.receivable) == 0 ? "0%" : Math.Round((decimal)x.Sum(c => c.received) / x.Sum(c => c.receivable), 0, MidpointRounding.AwayFromZero).ToString() + "%",
                    //    create_date = x.Key.create_date,
                    //    arrived_date = x.Key.arrived_date,
                    //    status = x.Key.status,
                    //    type = x.Key.type
                    //}).OrderByDescending(x => x.create_date).ToList();
                    int sn = nPageIdx * nPageNum;

                    foreach (var d in orderData)
                    {
                        d.sn = ++sn;
                    }
                    oResultData = new { records = orderData, totalCount = qdsOrderMst.Total };
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                sMsg = $"{nameof(readCommandPostDataHandlerOld)} unknwon exception. i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
#if DEBUG
                System.Diagnostics.Debug.WriteLine(sMsg);
#endif
            }

            o_oReault = oResultData;
            return sMsg;
        }

        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>();
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    var now = DateTime.Now;
                    foreach (JToken joData in i_jaData)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();

                        if (!dicData.ContainsKey(tb_ord_order_master.CN_STATUS))
                        {
                            sMsg = "狀態錯誤";
                            break;
                        }
                        var status = Convert.ToInt32(dicData[tb_ord_order_master.CN_STATUS]);
                        JArray jwheredata = dicData[BLWording.WHEREDATA] as JArray;
                        foreach (JObject data in jwheredata)
                        {
                            var oData = data.ToObject<Dictionary<string, string>>();
                            string orderUID = oData[tb_ord_order_master.CN_UID];
                            string articleUID = oData.ContainsKey(tb_ord_order_master.CN_ARTICLE_UID) ? oData[tb_ord_order_master.CN_ARTICLE_UID] : null;
                            string branchUID = oData[tb_ord_incoming_return_record.CN_BRANCH_UID];

                            // 判斷現貨訂單到貨(現貨訂單沒有article_uid)
                            if (string.IsNullOrEmpty(articleUID))
                            {
                                if (status == (int)Enums.OrderMainStatus.Arrived)
                                {
                                    // 查詢現貨數
                                    tb_prd_article2product cPro = new tb_prd_article2product();
                                    cPro.SetDirty(tb_prd_article2product.CN_UID, tb_prd_article2product.CN_QTY);
                                    tb_prd_article2product cCon = new tb_prd_article2product() { order_uid = orderUID };
                                    Command cSelect = Command.SetupSelectCmd(cPro, cCon);
                                    ai = ArsenalDBMgr.GetInst(cSelect);
                                    List<tb_prd_article2product> qdsPro = ai.RunQueryList<tb_prd_article2product>(cSelect);

                                    foreach (var product in qdsPro)
                                    {
                                        int productQty = GetOrderProductStock(orderUID, product.uid, product.specification);
                                        if (product.qty > productQty)
                                        {
                                            // 新增進貨紀錄(訂單數量-已進貨量)
                                            tb_ord_incoming_return_record cNew = new tb_ord_incoming_return_record()
                                            {
                                                article2product_uid = product.uid,
                                                operating = (int)Enums.IncomingReturnOperating.Incoming,
                                                qty = product.qty - productQty,
                                                order_uid = orderUID,
                                            };
                                            lCmds.Add(Command.SetupInsertCmd(cNew));

                                        }
                                        // 更新到貨日期及狀態
                                        tb_ord_order_master cArrived = new tb_ord_order_master() { arrived_date = DateTime.Now, status = (int)Enums.OrderMainStatus.Arrived };
                                        tb_ord_order_master cArrivedCon = new tb_ord_order_master() { uid = orderUID };
                                        lCmds.Add(Command.SetupUpdateCmd(cArrived, cArrivedCon));
                                    }
                                }
                                else
                                {
                                    tb_ord_order_master upData = new tb_ord_order_master() { status = status };
                                    tb_ord_order_master upCon = new tb_ord_order_master() { uid = orderUID };
                                    lCmds.Add(Command.SetupUpdateCmd(upData, upCon));
                                }
                            }
                            else
                            {
                                // 查詢訂單品項及數量
                                tb_ord_order_detail cOrder = new tb_ord_order_detail();
                                cOrder.SetDirty(tb_ord_order_detail.CN_UID, tb_ord_order_detail.CN_ORDER_QTY, tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID,
                                    tb_ord_order_detail.CN_TAKE_QTY, tb_ord_order_detail.CN_ORDER_QTY, tb_ord_order_detail.CN_STATUS,
                                    tb_ord_order_detail.CN_PRICE, tb_ord_order_detail.CN_MEMBER_UID, tb_ord_order_detail.CN_COMMENT_UID, tb_ord_order_detail.CN_COMMENT_TIME, tb_ord_order_detail.CN_SPECIFICATION);
                                tb_ord_order_detail cCon = new tb_ord_order_detail() { order_uid = orderUID };
                                Command cSelect = Command.SetupSelectCmd(cOrder, cCon);
                                ai = ArsenalDBMgr.GetInst(cSelect);
                                List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                                if (status == (int)Enums.OrderMainStatus.Arrived)
                                {
                                    foreach (var product in qdsOrder.GroupBy(x => new { x.article2product_uid, x.specification }))
                                    {
                                        var orderQty = product.Sum(x => x.order_qty);
                                        // 查詢商品已進貨數量
                                        int productQty = GetOrderProductStock(orderUID, product.Key.article2product_uid, product.Key.specification);
                                        if (orderQty > productQty)
                                        {
                                            // 新增進貨紀錄(訂單數量-已進貨量)
                                            tb_ord_incoming_return_record cNew = new tb_ord_incoming_return_record()
                                            {
                                                article2product_uid = product.Key.article2product_uid,
                                                operating = (int)Enums.IncomingReturnOperating.Incoming,
                                                qty = orderQty - productQty,
                                                order_uid = orderUID,
                                                specification = product.Key.specification
                                            };
                                            lCmds.Add(Command.SetupInsertCmd(cNew));
                                        }
                                        // 更新order_detail.status
                                        lCmds.AddRange(UpdateOrderIncomeReturnCmds(orderUID, product.Key.article2product_uid, product.Key.specification, (int)Enums.IncomingReturnOperating.Incoming, orderQty));
                                    }

                                    // 更新到貨日期及狀態
                                    tb_ord_order_master cArrived = new tb_ord_order_master() { arrived_date = DateTime.Now, status = (int)Enums.OrderMainStatus.Arrived };
                                    tb_ord_order_master cArrivedCon = new tb_ord_order_master() { uid = orderUID };
                                    lCmds.Add(Command.SetupUpdateCmd(cArrived, cArrivedCon));
                                }
                                else   // 封存
                                {
                                    tb_ord_order_master upData = new tb_ord_order_master() { status = status };
                                    tb_ord_order_master upCon = new tb_ord_order_master() { uid = orderUID };
                                    lCmds.Add(Command.SetupUpdateCmd(upData, upCon));

                                    foreach (var order in qdsOrder)
                                    {
                                        if (order.status == 1)
                                        {
                                            var indPrice = order.price / order.order_qty;
                                            // 將原本訂單轉已收款 且數量為已收的數量
                                            if (order.take_qty == 0)
                                            {
                                                tb_ord_order_detail upOrderDetail = new tb_ord_order_detail()
                                                {
                                                    status = (int)Enums.OrderStatus.Return,
                                                    operate_date = now
                                                };
                                                tb_ord_order_detail upOrderDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                                lCmds.Add(Command.SetupUpdateCmd(upOrderDetail, upOrderDetailCon));
                                            }
                                            else if (order.take_qty > 0)
                                            {
                                                tb_ord_order_detail upOrderDetail = new tb_ord_order_detail()
                                                {
                                                    status = (int)Enums.OrderStatus.Taked,
                                                    order_qty = order.take_qty,
                                                    take_qty = order.take_qty,
                                                    price = order.take_qty * indPrice,
                                                    operate_date = now
                                                };
                                                tb_ord_order_detail upOrderDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                                lCmds.Add(Command.SetupUpdateCmd(upOrderDetail, upOrderDetailCon));
                                                string sNewArticleUid = Guid.NewGuid().ToString();
                                                // 新增封存的Oder_detail 數量為剩下未收的數量
                                                tb_ord_order_detail cNew = new tb_ord_order_detail()
                                                {
                                                    uid = sNewArticleUid,
                                                    order_uid = orderUID,
                                                    article2product_uid = order.article2product_uid,
                                                    status = (int)Enums.OrderStatus.Return,
                                                    member_uid = order.member_uid,
                                                    order_qty = order.order_qty - order.take_qty,
                                                    take_qty = 0,
                                                    price = (order.order_qty - order.take_qty) * indPrice,
                                                    comment_uid = order.comment_uid,
                                                    comment_time = order.comment_time,
                                                    specification = order.specification,
                                                    operate_date = now
                                                };
                                                lCmds.Add(Command.SetupInsertCmd(cNew));
                                            }
                                            sMsg = getUpdateReturnCountCmd(order.member_uid, 1);
                                            if (sMsg != null)
                                            {
                                                break;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    if (sMsg != null)
                    {
                        break;
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
                Logger.Error(ex);
                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 
            }

            if (!string.IsNullOrWhiteSpace(sMsg))
            {
                Logger.Error(sMsg);
            }

            o_lcCmds = lCmds;
            return sMsg;
        }

        /// <summary>
        /// 訂單彈窗內取得訂單數量及庫存
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage GetProduct(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);   // 取得condition

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeA = lBlocks.GetInst();
                    qjeA.table = tb_ord_order_detail.TABLENAME;
                    qjeA.displaycols = new List<string>() {
                        tb_ord_order_detail.CN_SPECIFICATION,
                    };
                    qjeA.aliascols = new Dictionary<string, List<string>>
                    {
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } },
                         { "' '", new List<string>() { "qty" } },
                    };
                    var orderUID = dicCondition[tb_ord_order_master.CN_UID].ToString();
                    qjeA.wherecols = new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), orderUID);

                    QueryJsonElement qjeD = lBlocks.GetInst();
                    qjeD.table = tb_prd_article2product.TABLENAME;
                    qjeD.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeD.jointable = qjeA;
                    qjeD.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeD.displaycols = new List<string>() {
                        tb_prd_article2product.CN_UID,
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                    tb_prd_article2product.CN_SEQ
                    };

                    qjeD.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeA, tb_ord_order_detail.CN_SPECIFICATION),
                        Tuple.Create(qjeD, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeD, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeD, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeD, tb_prd_article2product.CN_SEQ)

                    };
                    qjeD.ordercols = new List<Tuple<QueryJsonElement, string, string>>()
                    {
                         Tuple.Create(qjeD, tb_prd_article2product.CN_SEQ,BLWording.ORDER_ASC)
                    };

                    lBlocks.Add(qjeA);
                    lBlocks.Add(qjeD);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    QueryDataSet qds = ai.RunQueryDataSet(cRes);

                    // 查詢進退貨紀錄
                    tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
                    cRecord.SetFullDirty();
                    tb_ord_incoming_return_record cCon = new tb_ord_incoming_return_record()
                    {
                        order_uid = orderUID
                    };
                    Command cSelect = Command.SetupSelectCmd(cRecord, cCon);
                    ai = ArsenalDBMgr.GetInst(cSelect);
                    List<tb_ord_incoming_return_record> qdsRecord = ai.RunQueryList<tb_ord_incoming_return_record>(cSelect);
                    if (qds.DATA.Tables.Count > 0)
                    {
                        foreach (DataRow data in qds.DATA.Tables[0].Rows)
                        {
                            var incomeQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Incoming && x.article2product_uid == data[tb_prd_article2product.CN_UID].ToString() && x.specification == data[tb_ord_order_detail.CN_SPECIFICATION].ToString()).Sum(x => x.qty);
                            var returnQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Return && x.article2product_uid == data[tb_prd_article2product.CN_UID].ToString() && x.specification == data[tb_ord_order_detail.CN_SPECIFICATION].ToString()).Sum(x => x.qty);
                            data["qty"] = incomeQty - returnQty;
                        }
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, qds);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetProduct)} 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>
        /// 現貨訂單已結團編輯頁顯示品項訂單數量及庫存量
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage GetStockOrderProduct(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);   // 取得condition

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeA = lBlocks.GetInst();
                    qjeA.table = tb_ord_order_master.TABLENAME;
                    var orderUID = dicCondition[tb_ord_order_master.CN_UID].ToString();
                    qjeA.wherecols = new WhereNode(tb_ord_order_master.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), orderUID);

                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                    qjeProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeProduct.jointable = qjeA;
                    qjeProduct.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                    qjeProduct.displaycols = new List<string>() {
                        tb_prd_article2product.CN_UID,
                        tb_prd_article2product.CN_SEQ,
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE
                    };

                    QueryJsonElement qjeDetail = lBlocks.GetInst();
                    qjeDetail.table = tb_ord_purchase_detail.TABLENAME;
                    qjeDetail.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeDetail.jointable = qjeProduct;
                    qjeDetail.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase_detail.CN_ARTICLE2PRODUCT_UID,tb_prd_article2product.CN_UID }};
                    qjeDetail.aliascols = new Dictionary<string, List<string>>
                    {
                         { QueryJsonElement.SUM(tb_ord_purchase_detail.CN_QTY), new List<string>() { "count"} },
                         { "' '", new List<string>() { "qty"} }
                    };

                    qjeProduct.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeProduct, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeProduct, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeProduct, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeProduct, tb_prd_article2product.CN_SEQ)
                    };

                    qjeProduct.ordercols = new List<Tuple<QueryJsonElement, string, string>>()
                    {
                         Tuple.Create(qjeProduct, tb_prd_article2product.CN_SEQ,BLWording.ORDER_ASC)
                    };

                    lBlocks.Add(qjeA);
                    lBlocks.Add(qjeProduct);
                    lBlocks.Add(qjeDetail);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    QueryDataSet qds = ai.RunQueryDataSet(cRes);

                    tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
                    cRecord.SetFullDirty();
                    tb_ord_incoming_return_record cCon = new tb_ord_incoming_return_record()
                    {
                        order_uid = orderUID
                    };
                    Command cSelect = Command.SetupSelectCmd(cRecord, cCon);
                    ai = ArsenalDBMgr.GetInst(cSelect);
                    List<tb_ord_incoming_return_record> qdsRecord = ai.RunQueryList<tb_ord_incoming_return_record>(cSelect);

                    if (qds.DATA.Tables.Count > 0)
                    {
                        foreach (DataRow data in qds.DATA.Tables[0].Rows)
                        {
                            var incomeQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Incoming && x.article2product_uid == data[tb_prd_article2product.CN_UID].ToString()).Sum(x => x.qty);
                            var returnQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Return && x.article2product_uid == data[tb_prd_article2product.CN_UID].ToString()).Sum(x => x.qty);
                            data["count"] = string.IsNullOrEmpty(data["count"]?.ToString()) ? 0 : data["count"];
                            data["qty"] = incomeQty - returnQty;
                        }
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, qds);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetProduct)} 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 class ProductModel
        {
            public string article2product_uid { get; set; }
            public int price { get; set; }
            public bool isArticleName { get; set; }
            public string specification { get; set; }
        }

        #region 列印功能
        /// <summary>
        /// 列印報表
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage Print(CRequestMessage i_crmInput)
        {
            string sMsg;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    // 填入報表
                    var printType = Convert.ToInt32(i_crmInput.param["printType"]);
                    var sPath = string.Empty;
                    switch (printType)
                    {
                        // 訂單統計報表
                        case 1:
                            sPath = GetStatisticalReportByPersonPath(i_crmInput);
                            break;
                        // 分店數量統計表
                        case 2:
                            sPath = GetStatisticalReportByBranchPath(i_crmInput);
                            break;
                        // 送單報表
                        case 3:
                            sPath = GetStatisticalReportBySendPath(i_crmInput);
                            break;
                        default:
                            break;
                    }

                    // 列印
                    sMsg = ReportExtension.ConvertToPdf(30, Path.Combine(AppDomain.CurrentDomain.BaseDirectory, sPath), out string sPdfPath);
                    if (sMsg != null)
                    {
                        break;
                    }

                    // 上傳檔案
                    FileUploadHelper fuh = new FileUploadHelper(GetFileUploadInfo(), i_crmInput);
                    sMsg = fuh.UploadLocalFile(sPdfPath, out tb_sys_uploadlog ulRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, new List<tb_sys_uploadlog>() { ulRes });
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(Print)} 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 class ReportDataModel
        {
            public string article2product_uid { get; set; }
            public string specification { get; set; }
            public int seq { get; set; }
        }

        /// <summary>
        /// 取得統計報表資料
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <param name="lsOrderDetail"></param>
        /// <param name="dicQueryArrivedQty"></param>
        /// <returns></returns>
        private string GetStatisticalReportData(CRequestMessage i_crmInput, out List<OrderDetail> lsOrderDetail, out Dictionary<ReportDataModel, int> dicQueryArrivedQty)
        {
            string sMsg = null;
            lsOrderDetail = new List<OrderDetail>();
            dicQueryArrivedQty = new Dictionary<ReportDataModel, int>();

            // 查詢資料
            JArray jaData = i_crmInput.param[BLWording.QRY_MASTER] as JArray;
            JArray jaWhereData = jaData[0][BLWording.WHEREDATA] as JArray;
            string[] orderUIDs = jaWhereData.OfType<JObject>().Select(x => (x.TryGetValue(tb_ord_order_master.CN_UID, out JToken value) ? value : null)?.ToString()).Where(x => !string.IsNullOrEmpty(x)).ToArray();

            // 查詢全部的訂單資訊
            sMsg = GetOrderDetails(orderUIDs, false, out List<OrderDetail> allOrderDetail);
            if (sMsg != null)
            {
                return sMsg;
            }

            // 查詢無訂單子表的訂單
            var noDetailUIDs = allOrderDetail.Where(d => d.article2product_uid == null).Select(d => d.uid).ToArray();
            List<OrderDetail> noDetailOrderDetail = new List<OrderDetail>();
            if (noDetailUIDs.Count() > 0) {//添加此處防呆
                sMsg = GetOrderDetails(noDetailUIDs, true, out noDetailOrderDetail);
                if (sMsg != null)
                {
                    return sMsg;
                }
            }
            lsOrderDetail.AddRange(allOrderDetail.Where(d => d.article2product_uid != null));
            lsOrderDetail.AddRange(noDetailOrderDetail.Where(d => d.article2product_uid != null));

            // 紀錄分店商品的到貨數量
            dicQueryArrivedQty = lsOrderDetail.GroupBy(x => new { x.uid, x.article2product_uid, x.specification, x.seq }).Select(g => new
            {
                g.Key.article2product_uid,
                g.Key.specification,
                g.Key.seq,
                qty = GetOrderProductStock(g.Key.uid, g.Key.article2product_uid, g.Key.specification)
            }).GroupBy(g => new { g.article2product_uid, g.specification, g.seq }).ToDictionary(g => new ReportDataModel() { article2product_uid = g.Key.article2product_uid, specification = g.Key.specification, seq = g.Key.seq }, g => g.Sum(x => x.qty));
            return sMsg;
        }

        /// <summary>
        /// 查詢訂單資訊，根據有沒有Detail查詢不同東西
        /// </summary>
        /// <param name="orderUIDs"></param>
        /// <param name="isNoDetail"></param>
        /// <param name="orderDetails"></param>
        /// <returns></returns>
        private string GetOrderDetails(string[] orderUIDs, bool isNoDetail, out List<OrderDetail> orderDetails)
        {
            orderDetails = new List<OrderDetail>();

            // 撰寫QJE
            QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

            // 訂單主表資訊
            QueryJsonElement qjeOrderMaster = lBlocks.GetInst();
            qjeOrderMaster.table = tb_ord_order_master.TABLENAME;
            qjeOrderMaster.displaycols = new List<string>()
            {
                tb_ord_order_master.CN_BRANCH_UID,
                tb_ord_order_master.CN_UID,
                tb_ord_order_master.CN_CREATE_DATE
            };
            qjeOrderMaster.wherecols = new WhereNode(tb_ord_order_master.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), orderUIDs);
            qjeOrderMaster.aliascols = new Dictionary<string, List<string>>
            {
                { tb_ord_order_master.CN_NAME, new List<string>() { "article_name" } }
            };
            lBlocks.Add(qjeOrderMaster);

            // 訂單社團
            QueryJsonElement qjeBranch = lBlocks.GetInst();
            qjeBranch.table = tb_grp_branch.TABLENAME;
            qjeBranch.jointype = QueryJsonElement.LEFT_JOIN;
            qjeBranch.jointable = qjeOrderMaster;
            qjeBranch.joincols = new Dictionary<string, string>()
            {
                { tb_grp_branch.CN_UID,tb_ord_order_master.CN_BRANCH_UID }
            };
            qjeBranch.displaycols = new List<string>()
            {
                tb_grp_branch.CN_BRANCH_NAME
            };
            qjeBranch.aliascols = new Dictionary<string, List<string>>
            {
                { tb_grp_branch.CN_SEQ, new List<string>() { "branch_seq" } }
            };
            lBlocks.Add(qjeBranch);

            // 若是沒有OrderDetail則直接查Article2Product
            QueryJsonElement qjeOrderDetail = lBlocks.GetInst();
            if (!isNoDetail)
            {
                // 訂單子表
                qjeOrderDetail.table = tb_ord_order_detail.TABLENAME;
                qjeOrderDetail.jointype = QueryJsonElement.LEFT_JOIN;
                qjeOrderDetail.jointable = qjeOrderMaster;
                qjeOrderDetail.joincols = new Dictionary<string, string>()
                {
                    { tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }
                };
                qjeOrderDetail.displaycols = new List<string>()
                {
                    tb_ord_order_detail.CN_ORDER_QTY,
                    tb_ord_order_detail.CN_TAKE_QTY,
                    tb_ord_order_detail.CN_STATUS,
                    tb_ord_order_detail.CN_MEMBER_UID,
                    tb_ord_order_detail.CN_SPECIFICATION,
                    tb_ord_order_detail.CN_CREATE_DATE
                };
                lBlocks.Add(qjeOrderDetail);

                // 會員帳號
                QueryJsonElement qjeMember = lBlocks.GetInst();
                qjeMember.table = tb_meb_member.TABLENAME;
                qjeMember.jointype = QueryJsonElement.LEFT_JOIN;
                qjeMember.jointable = qjeOrderDetail;
                qjeMember.joincols = new Dictionary<string, string>()
                {
                    { tb_meb_member.CN_UID,tb_ord_order_detail.CN_MEMBER_UID }
                };
                qjeMember.aliascols = new Dictionary<string, List<string>>
                {
                    { tb_meb_member.CN_NAME, new List<string>() { "member_name" } }
                };
                lBlocks.Add(qjeMember);
            }

            // 留言商品
            QueryJsonElement qjeArticle2Product = lBlocks.GetInst();
            var article2ProductJoincols = new Dictionary<string, string>();
            if (isNoDetail)
            {
                article2ProductJoincols.Add(tb_prd_article2product.CN_ORDER_UID, tb_ord_order_master.CN_UID);
            }
            else
            {
                article2ProductJoincols.Add(tb_prd_article2product.CN_UID, tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID);
            }
            qjeArticle2Product.table = tb_prd_article2product.TABLENAME;
            qjeArticle2Product.jointype = QueryJsonElement.LEFT_JOIN;
            qjeArticle2Product.jointable = isNoDetail ? qjeOrderMaster : qjeOrderDetail;
            qjeArticle2Product.joincols = article2ProductJoincols;
            qjeArticle2Product.displaycols = new List<string>()
            {
                tb_prd_article2product.CN_NAME,
                tb_prd_article2product.CN_PRICE,
                tb_prd_article2product.CN_QTY,
                tb_prd_article2product.CN_SEQ
            };
            qjeArticle2Product.aliascols = new Dictionary<string, List<string>>
            {
                { tb_prd_article2product.CN_UID, new List<string>() { "article2product_uid" } }
            };
            lBlocks.Add(qjeArticle2Product);

            string sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);
            if (sMsg != null)
            {
                return sMsg;
            }
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
            orderDetails = ai.RunQueryList<OrderDetail>(cRes).OrderBy(x => x.create_date).ThenBy(x => x.seq).ToList();
            return sMsg;
        }

        /// <summary>
        /// 訂單統計報表
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        private string GetStatisticalReportByPersonPath(CRequestMessage i_crmInput)
        {
            // 取回資料並整理資料
            _ = GetStatisticalReportData(i_crmInput, out List<OrderDetail> lsOrderDetail, out Dictionary<ReportDataModel, int> dicQueryArrivedQty);
            var groupData = lsOrderDetail.GroupBy(x => new { x.member_uid, x.member_name, x.branch_uid }).ToList();

            // 套表
            var sb = new StringBuilder();
            string sPath = NpoiExtension.Report("訂單統計報表", (sheet) =>
            {
                var check = "R";
                var uncheck = "£";

                // 特殊符號字型
                var Wingdings2Font = (XSSFFont)sheet.Workbook.CreateFont();
                Wingdings2Font.FontName = "Wingdings 2";
                Wingdings2Font.FontHeightInPoints = 12;

                // 開始撰寫Excel
                var row = 1;
                var titleRow = row;
                sheet.Cell(row++, 0).SetCellValue("");   // 商品名稱
                row++;   // 標題列

                // 複製Row，因為現貨訂單若未有人訂貨則不會有order_detail且不會有member_uid，故在畫面上濾掉
                var addRowCount = groupData.Count(g => !string.IsNullOrEmpty(g.Key.member_uid)) - 1;
                sheet.InsertRow(row, 1, addRowCount);

                // 撰寫商品列表，用foreach的原因是因為在這個情形計算index會比用for迴圈正確
                var dicProductItem = new Dictionary<string, ProductModel>();   // 商品列表(名稱、{商品uid,單價})
                var productIndex = 0;
                foreach (var groupMember in groupData)
                {
                    sb.Clear();

                    // 登記品項
                    var total = 0;
                    var itemNo = 1;
                    var liPosition = new List<int>();
                    var groupProduct = groupMember.GroupBy(x => new { x.article2product_uid, x.article_name, x.name, x.price, x.specification });
                    var itemCount = groupProduct.Count();   // 會員有幾項商品
                    foreach (var p in groupProduct)
                    {
                        bool isArticleName = string.IsNullOrEmpty(p.Key.name);
                        var specification = p.Key.specification == null ? "" : p.Key.specification;
                        var itemName = isArticleName ? p.Key.article_name : p.Key.article_name + "(" + p.Key.name + " " + specification + ")";
                        if (!dicProductItem.ContainsKey(itemName))
                        {
                            dicProductItem.Add(itemName, new ProductModel
                            {
                                article2product_uid = p.Key.article2product_uid,
                                price = p.Key.price,
                                isArticleName = isArticleName,
                                specification = p.Key.specification
                            });
                        }

                        liPosition.Add(sb.Length);
                        var orderQty = p.Sum(x => x.order_qty);
                        var takeQty = p.Sum(x => x.take_qty);
                        sb.AppendFormat("{0}{1} x {2}{3}", orderQty == takeQty ? check : uncheck, itemName, orderQty, itemNo < itemCount ? "\r\n" : "");
                        total += p.Key.price * orderQty;
                        itemNo++;
                    }
                    var richText = new XSSFRichTextString(sb.ToString());
                    foreach (var index in liPosition)
                    {
                        richText.ApplyFont(index, index + 1, Wingdings2Font);
                    }

                    // 填入EXCEL
                    var cell = 0;
                    if (!string.IsNullOrEmpty(groupMember.Key.member_uid))
                    {
                        sheet.Cell(row, cell++).SetCellValue(productIndex += 1);   // NO
                        sheet.Cell(row, cell++).SetCellValue(groupMember.Select(x => x.branch_name).FirstOrDefault());   // 分區
                        sheet.Cell(row, cell++).SetCellValue(groupMember.Key.member_name);   // 臉書帳號名
                        sheet.Cell(row, cell++).SetCellValue(richText);
                        sheet.Cell(row, cell++).SetCellValue(string.Format("{0}元", total));   // 金額
                        sheet.Cell(row, cell).SetCellValue(groupMember.Any(x => x.status == (int)Enums.OrderStatus.NotArrived) ? "N" : "Y");   // 到貨
                        sheet.AutoHeight(row++);
                    }
                }

                // 計算訂單數量、剩餘庫存數量、實際到貨數量總計
                var lsOrder = new List<string>();
                var orderTotal = 0;
                var lsStock = new List<string>();
                var stockTotal = 0;
                var lsArrivedCount = new List<string>();
                var arriverTotal = 0;
                foreach (var item in dicProductItem)
                {
                    string specification = item.Value.specification;
                    var orderCount = lsOrderDetail.Where(x => x.article2product_uid == item.Value.article2product_uid && x.specification == item.Value.specification).Sum(x => x.order_qty);
                    var arriverCount = dicQueryArrivedQty.Where(x => x.Key.article2product_uid == dicProductItem[item.Key].article2product_uid && x.Key.specification == dicProductItem[item.Key].specification).Sum(x => x.Value);
                    var stockCount = arriverCount - lsOrderDetail.Where(x => x.article2product_uid == item.Value.article2product_uid).Sum(x => x.take_qty);
                    lsOrder.Add(string.Format("{0} x {1}", item.Key, orderCount));
                    orderTotal += orderCount * dicProductItem[item.Key].price;
                    lsStock.Add(string.Format("{0} x {1}", item.Key, stockCount)); //item.Key? itemName?
                    stockTotal += stockCount * dicProductItem[item.Key].price;
                    lsArrivedCount.Add(string.Format("{0} x {1}", item.Key, arriverCount));
                    arriverTotal += arriverCount * dicProductItem[item.Key].price;
                }

                // 訂單數量、金額
                string order = string.Join("\r\n", lsOrder);
                ICell orderCell = sheet.Cell(row, 3);
                orderCell.SetCellValue(order);
                orderCell.CellStyle.WrapText = true;
                sheet.Cell(row, 4).SetCellValue(string.Format("{0}元", orderTotal));
                sheet.AutoHeight(row++);
                // 剩餘庫存量、金額
                string stock = string.Join("\r\n", lsStock);
                ICell stockCell = sheet.Cell(row, 3);
                stockCell.SetCellValue(stock);
                stockCell.CellStyle.WrapText = true;
                sheet.Cell(row, 4).SetCellValue(string.Format("{0}元", stockTotal));
                sheet.AutoHeight(row++);
                // 實際到貨數量、金額
                string arrivedCount = string.Join("\r\n", lsArrivedCount);
                ICell arrivedCountCell = sheet.Cell(row, 3);
                arrivedCountCell.SetCellValue(arrivedCount);
                arrivedCountCell.CellStyle.WrapText = true;
                sheet.Cell(row, 4).SetCellValue(string.Format("{0}元", arriverTotal));
                sheet.AutoHeight(row);

                // 如果為同商品則顯示商品名稱，否則刪除名稱列
                if (lsOrderDetail.GroupBy(x => x.article_name).Count() == 1)
                {
                    sheet.Cell(titleRow, 0).SetCellValue(lsOrderDetail.GroupBy(x => x.article_name).Select(x => x.Key).FirstOrDefault());
                }
                else
                {
                    sheet.ClearRow(titleRow, 1, true);
                }
            });
            return sPath;
        }

        /// <summary>
        /// 分店數量統計表
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        private string GetStatisticalReportByBranchPath(CRequestMessage i_crmInput)
        {
            // 取回資料並整理資料
            _ = GetStatisticalReportData(i_crmInput, out List<OrderDetail> lsOrderDetail, out Dictionary<ReportDataModel, int> dicQueryArrivedQty);
            var branchProducts = lsOrderDetail.GroupBy(x => new { x.branch_uid, x.branch_name, x.article2product_uid, x.article_name, x.name, x.specification, x.seq }).Select(g =>
             {
                 var qty = g.Sum(x => x.order_qty);
                 var specification = g.Key.specification == null ? "" : g.Key.specification;
                 return new
                 {
                     g.Key.branch_uid,
                     g.Key.branch_name,
                     g.Key.article2product_uid,
                     g.Key.seq,
                     article_name = string.IsNullOrEmpty(g.Key.name) ? g.Key.article_name : string.Format("{0}({1})", g.Key.article_name, g.Key.name + " " + specification),
                     qty,
                 };
             });
            var allBranchs = branchProducts.GroupBy(x => new { x.branch_uid, x.branch_name }).ToArray();
            var totalProducts = branchProducts.GroupBy(x => new { x.article2product_uid, x.article_name, x.seq }).Select(g => new
            {
                g.Key.article_name,
                g.Key.seq,
                qty = g.Sum(x => (int?)x.qty) ?? 0,
            }).ToArray();

            // 套表
            string sPath = NpoiExtension.Report("分店數量統計表", (sheet) =>
            {
                var rowNumber = 1;
                int firstRowNumber, lastRowNumber;
                IRow row;

                // 分店處理
                sheet.InsertRow(rowNumber, count: allBranchs.Count() - 1);
                foreach (var branch in allBranchs)
                {
                    firstRowNumber = rowNumber;

                    // 寫入分店商品資料
                    sheet.InsertRow(rowNumber, count: branch.Count() - 1);
                    foreach (var product in branch)
                    {
                        row = sheet.GetRow(rowNumber);
                        row.GetCell(1).SetCellValue(product.article_name);
                        row.GetCell(2).SetCellValue(product.qty);
                        sheet.AutoHeight(rowNumber++);
                    }

                    // 寫入分店資訊
                    row = sheet.GetRow(firstRowNumber);
                    row.GetCell(0).SetCellValue(string.Format("【{0}】", branch.Key.branch_name));

                    // 合併儲存格
                    lastRowNumber = rowNumber - 1;
                    sheet.AddMergedRegion(new CellRangeAddress(firstRowNumber, lastRowNumber, 0, 0));
                }

                // 總計資料
                firstRowNumber = rowNumber;

                // 寫入總計商品資料
                sheet.InsertRow(rowNumber, count: totalProducts.Count() - 1);
                foreach (var product in totalProducts.OrderBy(x => x.seq))
                {
                    row = sheet.GetRow(rowNumber);
                    row.GetCell(1).SetCellValue(product.article_name);
                    row.GetCell(2).SetCellValue(product.qty);
                    sheet.AutoHeight(rowNumber++);
                }

                // 合併儲存格
                lastRowNumber = rowNumber - 1;
                sheet.AddMergedRegion(new CellRangeAddress(firstRowNumber, lastRowNumber, 0, 0));
            });
            return sPath;
        }

        /// <summary>
        /// 送單報表
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        private string GetStatisticalReportBySendPath(CRequestMessage i_crmInput)
        {
            // 取回資料並整理資料
            _ = GetStatisticalReportData(i_crmInput, out List<OrderDetail> lsOrderDetail, out Dictionary<ReportDataModel, int> dicQueryArrivedQty);
            var BranchGroupList = GetBranchGroupList();// 取回分店與社團的對應            
            var branchProducts = lsOrderDetail.GroupBy(x => new { x.branch_uid, x.branch_name, x.article2product_uid, x.article_name, x.name, x.specification, x.seq, x.branch_seq }).Select(g =>
            {
                var qty = g.Sum(x => x.order_qty);
                var specification = g.Key.specification == null ? "" : g.Key.specification;
                return new
                {
                    g.Key.branch_uid,
                    g.Key.branch_name,
                    g.Key.article2product_uid,
                    g.Key.seq,
                    article_name = string.IsNullOrEmpty(g.Key.name) ? g.Key.article_name : string.Format("{0}({1})", g.Key.article_name, g.Key.name + " " + specification),
                    qty,
                    group_name = BranchGroupList.Find(item => item.uid == g.Key.branch_uid)?.name,
                    branch_seq = g.Key.branch_seq
                };
            }).OrderBy(g => g.group_name).ThenBy(g => g.branch_seq).ThenBy(item => item.branch_uid);
            var totalProducts = branchProducts.GroupBy(x => new { x.article_name, x.seq }).Select(g => new
            {
                g.Key.article_name,
                g.Key.seq,
                qty = g.Sum(x => (int?)x.qty) ?? 0,
            }).OrderBy(x => x.article_name).ToArray();
            // 所有商品的社團總數
            var GroupCount = branchProducts.GroupBy(x => new { x.group_name }).Count();
            // 所有商品的分店總數
            var BranchCount = branchProducts.GroupBy(x => new { x.branch_uid }).Count();
            // 套表
            string sPath = NpoiExtension.Report("送單報表", (sheet) =>
            {
                var rowNumber = 0;
                IRow row;

                // 分店商品資料                
                sheet.InsertRow(rowNumber, count: branchProducts.Count() + GroupCount + (BranchCount * 3));// 總行數
                var previousGroupName = "";// 上一個社團名稱
                var previousBranchGuid = "";// 上一個分店uid
                var firstData = true;// 是第一筆資料
                foreach (var product in branchProducts)
                {
                    if (!firstData && !product.branch_uid.Equals(previousBranchGuid))// 分店分隔符號
                    {
                        row = sheet.GetRow(rowNumber++);
                        row.GetCell(0).SetCellValue(new string('-', 30 * 2));
                        row = sheet.GetRow(rowNumber++);
                    }
                    //insert group title name                        
                    if (!product.group_name.Equals(previousGroupName))
                    {                        
                        row = sheet.GetRow(rowNumber++);                        
                        row.GetCell(0).SetCellValue(string.Format("【{0}】", product.group_name));
                    }
                    //insert branch divider and address
                    if (!product.branch_uid.Equals(previousBranchGuid))
                    {
                        row = sheet.GetRow(rowNumber++);
                        var currentBranch = BranchGroupList.Find(x => x.uid == product.branch_uid);
                        row.GetCell(0).SetCellValue(string.Format("【聯絡方式】{0}   {1}   {2}", currentBranch?.address, $"{currentBranch?.phone_number}", $"{currentBranch?.contact_person}"));
                    }
                    previousGroupName = product.group_name;
                    previousBranchGuid = product.branch_uid;
                    firstData = false;

                    row = sheet.GetRow(rowNumber++);
                    row.GetCell(0).SetCellValue(string.Format("【{0}】{1} x {2}", product.branch_name, product.article_name, product.qty));
                }
                // 分店分隔符號
                row = sheet.GetRow(rowNumber++);
                row.GetCell(0).SetCellValue(new string('-', 30 * 2));
                row = sheet.GetRow(rowNumber++);

                // 寫入總計商品資料
                sheet.InsertRow(rowNumber, count: totalProducts.Count());
                foreach (var product in totalProducts)
                {
                    row = sheet.GetRow(rowNumber++);
                    row.GetCell(0).SetCellValue(string.Format("【總計】{0} x {1}", product.article_name, product.qty));
                }
            });
            return sPath;
        }
        #endregion 列印功能

        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;
            List<Command> lcCmds = new List<Command>();
            Command cRes = null;

            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>>();
                            if (wheredataDic.ContainsKey(tb_ord_order_master.CN_UID))
                            {
                                sMstUID = wheredataDic[tb_ord_order_master.CN_UID].ToString();

                                // 查詢orderdeatil
                                tb_ord_order_detail cOrder = new tb_ord_order_detail();
                                cOrder.SetDirty(tb_ord_order_detail.CN_COMMENT_UID, tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID);
                                tb_ord_order_detail cOrderCon = new tb_ord_order_detail() { order_uid = sMstUID };
                                Command cSelect = Command.SetupSelectCmd(cOrder, cOrderCon);
                                ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
                                List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                                tb_ord_order_master cOrderMasterMain = new tb_ord_order_master();
                                cOrderMasterMain.SetDirty(tb_ord_order_master.CN_TYPE);
                                tb_ord_order_master cConMaster = new tb_ord_order_master()
                                {
                                    uid = sMstUID,
                                };
                                Command cSelectMaster = Command.SetupSelectCmd(cOrderMasterMain, cConMaster);
                                ArsenalInterface aiMaster = ArsenalDBMgr.GetInst(cSelectMaster);
                                List<tb_ord_order_master> qdsOrderMasterMain = aiMaster.RunQueryList<tb_ord_order_master>(cSelectMaster);

                                // 判斷是否為現貨訂單
                                if (qdsOrderMasterMain[0].type == (int)Enums.ArticleType.Stock)
                                {
                                    tb_prd_article2product cOrderProduct = new tb_prd_article2product();
                                    cOrderProduct.SetDirty(tb_prd_article2product.CN_UID);
                                    tb_prd_article2product cConProduct = new tb_prd_article2product()
                                    {
                                        order_uid = sMstUID,
                                    };
                                    Command cSelectProduct = Command.SetupSelectCmd(cOrderProduct, cConProduct);
                                    ArsenalInterface aiProduct = ArsenalDBMgr.GetInst(cSelectProduct);
                                    List<tb_prd_article2product> qdsProduct = aiProduct.RunQueryList<tb_prd_article2product>(cSelectProduct);
                                    List<string> productUid = new List<string>();
                                    foreach (var product in qdsProduct)
                                    {
                                        productUid.Add(product.uid);
                                    }
                                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                                    QueryJsonElement qjeA = lBlocks.GetInst();
                                    qjeA.table = tb_ord_order_master.TABLENAME;
                                    qjeA.displaycols = new List<string>() {
                                        tb_ord_order_master.CN_UID
                                    };

                                    QueryJsonElement qjeDetail = lBlocks.GetInst();
                                    qjeDetail.table = tb_ord_order_detail.TABLENAME;
                                    qjeDetail.jointype = QueryJsonElement.LEFT_JOIN;
                                    qjeDetail.jointable = qjeA;
                                    qjeDetail.joincols = new Dictionary<string, string>() {
                                    {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};

                                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                                    qjeProduct.jointype = QueryJsonElement.LEFT_JOIN;
                                    qjeProduct.jointable = qjeDetail;
                                    qjeProduct.joincols = new Dictionary<string, string>() {
                                    {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};

                                    qjeProduct.wherecols = new WhereNode(tb_prd_article2product.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_prd_article2product), productUid.ToArray());

                                    lBlocks.Add(qjeA);
                                    lBlocks.Add(qjeDetail);
                                    lBlocks.Add(qjeProduct);

                                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                                    if (sMsg != null)
                                    {
                                        break;
                                    }
                                    ArsenalInterface ai2 = ArsenalDBMgr.GetInst(cRes);
                                    List<tb_ord_order_master> qdsMaster = aiProduct.RunQueryList<tb_ord_order_master>(cRes);

                                    foreach (var master in qdsMaster)
                                    {
                                        tb_ord_order_master updateMaster1 = new tb_ord_order_master { status_flag = BLWording.STATUS_FLAG_OFF };
                                        tb_ord_order_master updateMasterCond1 = new tb_ord_order_master { uid = master.uid };
                                        lcCmds.Add(Command.SetupUpdateCmd(updateMaster1, updateMasterCond1));
                                    }
                                    tb_ord_order_master updateMaster = new tb_ord_order_master { status_flag = BLWording.STATUS_FLAG_OFF };
                                    tb_ord_order_master updateMasterCond = new tb_ord_order_master { uid = sMstUID };
                                    lcCmds.Add(Command.SetupUpdateCmd(updateMaster, updateMasterCond));
                                    continue;
                                }

                                // 刪除order
                                lcCmds.Add(Command.SetupDeleteCmd(new tb_ord_order_master() { uid = sMstUID }));

                                // 刪除comment
                                foreach (var c in qdsOrder)
                                {
                                    lcCmds.Add(Command.SetupDeleteCmd(new tb_grp_comment() { uid = c.comment_uid }));
                                }

                                // 得到該article_uid
                                tb_prd_article2product aOrder = new tb_prd_article2product();
                                aOrder.SetDirty(tb_prd_article2product.CN_ARTICLE_UID);
                                tb_prd_article2product aCon = new tb_prd_article2product()
                                {
                                    uid = qdsOrder[0].article2product_uid
                                };
                                Command aSelect = Command.SetupSelectCmd(aOrder, aCon);
                                ArsenalInterface aiArticle = ArsenalDBMgr.GetInst(aSelect);
                                List<tb_prd_article2product> qdsArticle = aiArticle.RunQueryList<tb_prd_article2product>(aSelect);

                                // 查詢order master 該article有幾筆
                                tb_ord_order_master cOrderMaster = new tb_ord_order_master();
                                cOrderMaster.SetDirty(tb_ord_order_master.CN_UID, tb_ord_order_master.CN_STATUS);
                                tb_ord_order_master cOrderMasterCon = new tb_ord_order_master() { article_uid = qdsArticle[0].article_uid };
                                Command oSelect = Command.SetupSelectCmd(cOrderMaster, cOrderMasterCon);
                                ArsenalInterface aiO = ArsenalDBMgr.GetInst(oSelect);
                                List<tb_ord_order_master> qdsOrderMaster = aiO.RunQueryList<tb_ord_order_master>(oSelect);
                                if (qdsOrderMaster.Count == 1)
                                {
                                    // 將 post_status 更改回 貼文完成
                                    tb_grp_article updateArticle = new tb_grp_article { post_status = (int)EPostStatus.EPS_ORDER };
                                    tb_grp_article updateArticleCond = new tb_grp_article { uid = qdsArticle[0].article_uid };
                                    lcCmds.Add(Command.SetupUpdateCmd(updateArticle, updateArticleCond));
                                }
                                else
                                {
                                    // 判斷除此訂單外是否有已到貨訂單
                                    var arrivedCount = qdsOrderMaster.Where(x => x.status == 2 && x.uid != sMstUID).ToList().Count;

                                    // 將post_status 改回已結單
                                    if (arrivedCount < 1)
                                    {
                                        tb_grp_article updateArticle = new tb_grp_article { post_status = (int)EPostStatus.EPS_ORDER };
                                        tb_grp_article updateArticleCond = new tb_grp_article { uid = qdsArticle[0].article_uid };
                                        lcCmds.Add(Command.SetupUpdateCmd(updateArticle, updateArticleCond));
                                    }
                                }
                            }
                        }
                    }
                }
                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;
        }

        /// <summary>
        /// 新增現貨訂單
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage CreateStockOrder(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.ADD_MASTER] as JArray;
                    JObject joData = jaData[0][BLWording.DATA] as JObject;
                    Dictionary<string, object> dicInput = joData.ToObject<Dictionary<string, object>>();
                    // 判斷為社團ID還是分店ID
                    tb_grp_branch cBranch = new tb_grp_branch();
                    cBranch.SetDirty(tb_grp_branch.CN_GROUP_UID);
                    tb_grp_branch cCon = new tb_grp_branch() { uid = dicInput[tb_ord_order_master.CN_BRANCH_UID].ToString() };
                    Command cSelect = Command.SetupSelectCmd(cBranch, cCon);
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
                    tb_grp_branch qds = ai.RunQuerySingleORM<tb_grp_branch>(cSelect);

                    string sNewArticleUid = Guid.NewGuid().ToString();
                    // 新增現貨訂單
                    tb_ord_order_master cNew = new tb_ord_order_master()
                    {
                        uid = sNewArticleUid,
                        name = dicInput[tb_ord_order_master.CN_NAME].ToString(),
                        type = (int)Enums.ArticleType.Stock,
                        status = (int)Enums.OrderMainStatus.NotArrived,
                        order_code = new GROUP.Helper.FbHelper().GetOrderCode()
                    };
                    var uid = dicInput[tb_ord_order_master.CN_BRANCH_UID].ToString();
                    var groupUID = qds == null ? uid : qds.group_uid;
                    if (qds == null)
                    {
                        cNew.group_uid = uid;
                    }
                    else
                    {
                        cNew.branch_uid = uid;
                        cNew.group_uid = groupUID;
                    }
                    lCmds.Add(Command.SetupInsertCmd(cNew));

                    string sProductKey = nameof(tb_prd_article2product);
                    if (!joData.ContainsKey(sProductKey))
                    {
                        sMsg = "grp.no_product";
                        break;
                    }
                    List<tb_prd_article2product> la2pProducts = joData[sProductKey].ToObject<List<tb_prd_article2product>>();
                    List<tb_prd_article2product> products = new List<tb_prd_article2product>();
                    foreach (var f in la2pProducts)
                    {
                        string sProductUid = f.prd_uid;
                        if (sProductUid == null)
                        {
                            sProductUid = Guid.NewGuid().ToString();
                            // Create Product first                                                                
                            lCmds.Add(Command.SetupInsertCmd(new tb_prd_product()
                            {
                                uid = sProductUid,
                                name = f.name,
                                group_id = groupUID,
                                price = f.price,
                                wholesale_price = f.wholesale_price,
                                cost_price = f.cost_price
                                // todo:預設商品碼
                            }));
                        }
                        tb_prd_article2product cNewProduct = new tb_prd_article2product()
                        {
                            uid = Guid.NewGuid().ToString(),
                            price = f.price,
                            wholesale_price = f.wholesale_price,
                            name = f.name,
                            prd_uid = sProductUid,
                            seq = f.seq,
                            qty = f.qty,
                            specification = f.specification?.ToUpper(),
                            cost_price = f.cost_price
                        };
                        cNewProduct.order_uid = sNewArticleUid;
                        products.Add(cNewProduct);
                        lCmds.Add(Command.SetupInsertCmd(cNewProduct));
                    }

                    foreach (var item in products)
                    {
                        tb_ord_incoming_return_record cNewRecord = new tb_ord_incoming_return_record();
                        cNewRecord.uid = Guid.NewGuid().ToString();
                        cNewRecord.order_uid = sNewArticleUid;
                        cNewRecord.article2product_uid = item.uid;
                        cNewRecord.operating = 1;
                        cNewRecord.qty = item.qty;
                        lCmds.Add(Command.SetupInsertCmd(cNewRecord));
                    }

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

                    if (sErrorCode != null)
                    {
                        sMsg = sErrorCode;
                        break;
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(CreateStockOrder)} 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 int GetOrderProductStock(string i_sOrderUID, string i_sarticle2productUID, string specification)
        {
            tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
            cRecord.SetFullDirty();
            tb_ord_incoming_return_record cCon = new tb_ord_incoming_return_record() { order_uid = i_sOrderUID, article2product_uid = i_sarticle2productUID };
            if (specification != null)
            {
                cCon.specification = specification;
            }
            Command cSelect = Command.SetupSelectCmd(cRecord, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
            List<tb_ord_incoming_return_record> qdsRecord = ai.RunQueryList<tb_ord_incoming_return_record>(cSelect);

            var incomeQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Incoming).Sum(x => x.qty);
            var returnQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Return).Sum(x => x.qty);
            return incomeQty - returnQty;
        }

        private int GetProductStock(string i_sArticle2ProductUID, string i_sbranchUID = null)
        {
            tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
            cRecord.SetFullDirty();
            tb_ord_incoming_return_record cCon = new tb_ord_incoming_return_record() { article2product_uid = i_sArticle2ProductUID };
            if (!string.IsNullOrEmpty(i_sbranchUID))
            {
                cCon.branch_uid = i_sbranchUID;
            }
            Command cSelect = Command.SetupSelectCmd(cRecord, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
            List<tb_ord_incoming_return_record> qdsRecord = ai.RunQueryList<tb_ord_incoming_return_record>(cSelect);

            var incomeQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Incoming).Sum(x => x.qty);
            var returnQty = qdsRecord.Where(x => x.operating == (int)Enums.IncomingReturnOperating.Return).Sum(x => x.qty);
            return incomeQty - returnQty;
        }

        /// <summary>
        /// 當進退貨所造成存貨改變時，更新訂單到貨狀態
        /// </summary>
        /// <param name="i_sArticle2ProductUID"></param>
        /// <param name="i_sBranchUID"></param>
        /// <param name="i_iType"></param>
        /// <param name="i_iQty"></param>
        /// <returns></returns>
        private List<Command> UpdateOrderIncomeReturnCmds(string i_sOrderUID, string i_sarticle2productUID, string specification, int i_iType, int i_iQty)
        {
            List<Command> lCmds = new List<Command>();

            tb_ord_order_master cOrderMasterMain = new tb_ord_order_master();
            cOrderMasterMain.SetDirty(tb_ord_order_master.CN_TYPE);
            tb_ord_order_master cConMaster = new tb_ord_order_master()
            {
                uid = i_sOrderUID,
            };
            Command cSelectMaster = Command.SetupSelectCmd(cOrderMasterMain, cConMaster);
            ArsenalInterface aiMaster = ArsenalDBMgr.GetInst(cSelectMaster);
            List<tb_ord_order_master> qdsOrderMasterMain = aiMaster.RunQueryList<tb_ord_order_master>(cSelectMaster);


            // 查詢訂單
            tb_ord_order_detail cOrder = new tb_ord_order_detail();
            cOrder.SetDirty(tb_ord_order_detail.CN_UID, tb_ord_order_detail.CN_ORDER_QTY, tb_ord_order_detail.CN_COMMENT_TIME, tb_ord_order_detail.CN_STATUS, tb_ord_order_detail.CN_TAKE_QTY);
            tb_ord_order_detail cCon = new tb_ord_order_detail()
            {
                order_uid = i_sOrderUID,
                article2product_uid = i_sarticle2productUID,
            };
            if (specification != null)
            {
                cCon.specification = specification;
            }
            Command cSelect = Command.SetupSelectCmd(cOrder, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
            List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

            // 查詢目前存貨
            var stockQty = GetOrderProductStock(i_sOrderUID, i_sarticle2productUID, specification);
            // 更新訂單到貨狀態
            // 進貨

            // 得到該article_uid
            tb_prd_article2product aOrder = new tb_prd_article2product();
            aOrder.SetDirty(tb_prd_article2product.CN_ARTICLE_UID, tb_prd_article2product.CN_QTY);
            tb_prd_article2product aCon = new tb_prd_article2product()
            {
                uid = i_sarticle2productUID
            };
            Command aSelect = Command.SetupSelectCmd(aOrder, aCon);
            ArsenalInterface aiArticle = ArsenalDBMgr.GetInst(aSelect);
            List<tb_prd_article2product> qdsArticle = aiArticle.RunQueryList<tb_prd_article2product>(aSelect);
            var articleUid = qdsArticle[0].article_uid;
            var qty = qdsArticle[0].qty;

            if(qdsOrderMasterMain == null || qdsOrderMasterMain.Count < 1)
            {
                Logger.Error($@"{nameof(UpdateOrderIncomeReturnCmds)} Exception, {nameof(qdsOrderMasterMain)} is null,  
                    {nameof(i_sOrderUID)}= {i_sOrderUID}, 
                    {nameof(i_sarticle2productUID)}= {i_sarticle2productUID}, 
                    {nameof(specification)}= {specification}, 
                    {nameof(i_iType)}= {i_iType}, 
                    {nameof(i_iQty)}= {i_iQty} ");

                throw new Exception("Order Msater not found, 更新進退貨資料失敗");
            }

            // 判斷是否為現貨訂單
            if (qdsOrderMasterMain[0].type == (int)Enums.ArticleType.Stock)
            {
                // 將 post_status 更改成 已到貨
                tb_prd_article2product updateProduct = new tb_prd_article2product { qty = i_iType == (int)Enums.IncomingReturnOperating.Incoming ? qty + i_iQty : qty - i_iQty };
                tb_prd_article2product updateProductCon = new tb_prd_article2product { uid = i_sarticle2productUID };
                lCmds.Add(Command.SetupUpdateCmd(updateProduct, updateProductCon));
            }

            if (i_iType == (int)Enums.IncomingReturnOperating.Incoming)
            {
                stockQty = stockQty + i_iQty;
                var takedQty = qdsOrder.Where(x => x.status == (int)Enums.OrderStatus.Taked).Sum(x => x.take_qty);

                if (stockQty > 0)
                {
                    tb_ord_order_master cUpCon = new tb_ord_order_master() { uid = i_sOrderUID };
                    tb_ord_order_master cUpData = new tb_ord_order_master() { status = (int)Enums.OrderMainStatus.Arrived };
                    lCmds.Add(Command.SetupUpdateCmd(cUpData, cUpCon));

                    // 將 post_status 更改成 已到貨
                    tb_grp_article updateArticle = new tb_grp_article { post_status = (int)EPostStatus.EPS_ARRIVED };
                    tb_grp_article updateArticleCond = new tb_grp_article { uid = articleUid };
                    lCmds.Add(Command.SetupUpdateCmd(updateArticle, updateArticleCond));
                }
                // 處理detail
                stockQty = i_iQty;
                foreach (var order in qdsOrder.Where(x => x.status == (int)Enums.OrderStatus.NotArrived).OrderBy(x => x.comment_time))
                {
                    if (stockQty >= order.order_qty)
                    {
                        tb_ord_order_detail cUpCon = new tb_ord_order_detail() { uid = order.uid };
                        tb_ord_order_detail cUpData = new tb_ord_order_detail() { status = (int)Enums.OrderStatus.NotTake };
                        lCmds.Add(Command.SetupUpdateCmd(cUpData, cUpCon));
                        stockQty -= order.order_qty;
                    }
                }
            }
            // 退貨
            else
            {
                stockQty = stockQty - i_iQty;
                var takedQty = qdsOrder.Where(x => x.status == (int)Enums.OrderStatus.Taked).Sum(x => x.take_qty);
                if (stockQty < takedQty)
                {
                    return null;
                }
                var notTakedCount = qdsOrder.Where(x => x.status == (int)Enums.OrderStatus.NotTake).ToList().Count;

                if (stockQty > 0)
                {
                    tb_ord_order_master cUpCon = new tb_ord_order_master() { uid = i_sOrderUID };
                    tb_ord_order_master cUpData = new tb_ord_order_master() { status = (int)Enums.OrderMainStatus.Arrived };
                    lCmds.Add(Command.SetupUpdateCmd(cUpData, cUpCon));
                }
                else
                {
                    tb_ord_order_master cUpCon = new tb_ord_order_master() { uid = i_sOrderUID };
                    tb_ord_order_master cUpData = new tb_ord_order_master() { status = (int)Enums.OrderMainStatus.NotArrived };
                    lCmds.Add(Command.SetupUpdateCmd(cUpData, cUpCon));
                    if (notTakedCount == 0)
                    {
                        // 查詢order master 該article有幾筆
                        tb_ord_order_master cOrderMaster = new tb_ord_order_master();
                        cOrder.SetDirty(tb_ord_order_master.CN_UID);
                        tb_ord_order_master cOrderMasterCon = new tb_ord_order_master() { article_uid = articleUid };
                        Command oSelect = Command.SetupSelectCmd(cOrderMaster, cOrderMasterCon);
                        ArsenalInterface aiO = ArsenalDBMgr.GetInst(oSelect);
                        List<tb_ord_order_master> qdsOrderMaster = aiO.RunQueryList<tb_ord_order_master>(oSelect);

                        // 判斷除此訂單外是否有已到貨訂單
                        var arrivedCount = qdsOrderMaster.Where(x => x.status == 2 && x.uid != i_sOrderUID).ToList().Count;

                        // 將post_status 改回已結單
                        if (arrivedCount < 1)
                        {
                            tb_grp_article updateArticle = new tb_grp_article { post_status = (int)EPostStatus.EPS_ORDER };
                            tb_grp_article updateArticleCond = new tb_grp_article { uid = qdsArticle[0].article_uid };
                            lCmds.Add(Command.SetupUpdateCmd(updateArticle, updateArticleCond));
                        }
                    }
                }
                // 處理 detail
                stockQty = stockQty - takedQty;
                foreach (var order in qdsOrder.Where(x => x.status == (int)Enums.OrderStatus.NotTake).OrderBy(x => x.comment_time))
                {
                    if (stockQty < order.order_qty)
                    {
                        tb_ord_order_detail cUpCon = new tb_ord_order_detail() { uid = order.uid };
                        tb_ord_order_detail cUpData = new tb_ord_order_detail() { status = (int)Enums.OrderStatus.NotArrived };
                        lCmds.Add(Command.SetupUpdateCmd(cUpData, cUpCon));
                    }
                    stockQty -= order.order_qty;
                }
            }
            return lCmds;
        }

        /// <summary>
        /// 新增進退貨資料
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage IncomeReturnRecord(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.ADD_MASTER] as JArray;
                    JObject joData = jaData[0] as JObject;
                    Dictionary<string, object> dicInput = joData.ToObject<Dictionary<string, object>>();

                    // 新增進退貨記錄
                    tb_ord_incoming_return_record cNew = new tb_ord_incoming_return_record();
                    cNew.FillData(dicInput);
                    lCmds.Add(Command.SetupInsertCmd(cNew));
                    var specification = dicInput[tb_ord_incoming_return_record.CN_SPECIFICATION] == null ? null : dicInput[tb_ord_incoming_return_record.CN_SPECIFICATION].ToString();
                    List<Command> lCmds2 = UpdateOrderIncomeReturnCmds(dicInput[tb_ord_incoming_return_record.CN_ORDER_UID].ToString(), dicInput[tb_ord_incoming_return_record.CN_ARTICLE2PRODUCT_UID].ToString(), specification, Convert.ToInt32(dicInput[tb_ord_incoming_return_record.CN_OPERATING]), Convert.ToInt32(dicInput[tb_ord_incoming_return_record.CN_QTY]));
                    if (lCmds2 == null)
                    {
                        sMsg = "退貨數量";
                        break;
                    }
                    // 更新訂單到貨狀態
                    lCmds.AddRange(lCmds2);

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

                    if (sErrorCode != null)
                    {
                        sMsg = sErrorCode;
                        break;
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(IncomeReturnRecord)} 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 GetIncomeReturnRecord(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);   // 取得condition

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeRecord = lBlocks.GetInst();
                    qjeRecord.table = tb_ord_incoming_return_record.TABLENAME;
                    qjeRecord.displaycols = new List<string>() {
                        tb_ord_incoming_return_record.CN_OPERATING,
                        tb_ord_incoming_return_record.CN_QTY,
                        tb_ord_incoming_return_record.CN_MEMO,
                        tb_ord_incoming_return_record.CN_CREATE_DATE
                    };
                    qjeRecord.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND,
                        new WhereNode(tb_ord_incoming_return_record.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_incoming_return_record), dicCondition[tb_ord_incoming_return_record.CN_BRANCH_UID]),
                        new WhereNode(tb_ord_incoming_return_record.CN_ARTICLE2PRODUCT_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_incoming_return_record), dicCondition[tb_ord_incoming_return_record.CN_UID]));

                    QueryJsonElement qjeEmp = lBlocks.GetInst();
                    qjeEmp.table = tb_hr_employee.TABLENAME;
                    qjeEmp.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeEmp.jointable = qjeRecord;
                    qjeEmp.joincols = new Dictionary<string, string>() {
                             {  tb_hr_employee.CN_UID,tb_ord_incoming_return_record.CN_CREATE_USER_UID }};
                    qjeEmp.displaycols = new List<string>() { tb_hr_employee.CN_NAME };

                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                    qjeProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeProduct.jointable = qjeRecord;
                    qjeProduct.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_incoming_return_record.CN_ARTICLE2PRODUCT_UID }};
                    qjeProduct.displaycols = new List<string>() { tb_prd_article2product.CN_SEQ };
                    qjeProduct.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_NAME, new List<string>() { "product_name" } }
                    };

                    lBlocks.Add(qjeRecord);
                    lBlocks.Add(qjeEmp);
                    lBlocks.Add(qjeProduct);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    QueryDataSet qds = ai.RunQueryDataSet(cRes);
                    if (cRes.IsSuccess == false)
                    {
                        sMsg = cRes.LastErrorCode;
                        break;
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, qds);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetIncomeReturnRecord)} 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 CheckOut(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            List<Command> lCmdsDetail = new List<Command>();
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    JObject oData = i_crmInput.param[BLWording.ADD_MASTER] as JObject;
                    Dictionary<string, object> joData = oData.ToObject<Dictionary<string, object>>();
                    JArray jaData = joData[BLWording.DATA] as JArray;
                    Dictionary<string, int> dicPoints = new Dictionary<string, int>();

                    if (joData.ContainsKey("shoppingPoints")) // 判斷是否使用購物金 並驗證金額大小
                    {
                        JArray jaPoints = joData["shoppingPoints"] as JArray;
                        foreach (var points in jaPoints)
                        {
                            var member_uid = points[tb_meb_member.CN_UID].ToString();
                            tb_meb_member cPoints = new tb_meb_member();
                            cPoints.SetDirty(tb_meb_member.CN_SHOPPING_ACCOUNT);
                            tb_meb_member cConPoints = new tb_meb_member()
                            {
                                uid = member_uid
                            };
                            Command pointsSelect = Command.SetupSelectCmd(cPoints, cConPoints);
                            ArsenalInterface aiPoints = ArsenalDBMgr.GetInst(pointsSelect);
                            var qdsPoints = aiPoints.RunQueryList<tb_meb_member>(pointsSelect);
                            if (Convert.ToDecimal(points[tb_meb_member.CN_SHOPPING_ACCOUNT]) > qdsPoints[0].shopping_account)
                            {
                                sMsg = "Shopping Points Wrong";
                                break;
                            }
                            else
                            {
                                dicPoints[member_uid] = Convert.ToInt32(points[tb_meb_member.CN_SHOPPING_ACCOUNT]);
                            }
                        }
                    }

                    if (sMsg != null)
                    {
                        break;
                    }

                    var addMainData = new List<tb_ord_purchase>();
                    var addPointsData = new List<tb_meb_shopping_points_record>();
                    var now = DateTime.Now;
                    // get create_uesr_id
                    var token = i_crmInput.token;
                    tb_sys_session cSession = new tb_sys_session();
                    cSession.SetDirty(tb_sys_session.CN_CREATE_USER_UID);
                    tb_sys_session cCon = new tb_sys_session()
                    {
                        uid = token
                    };
                    Command sessionSelect = Command.SetupSelectCmd(cSession, cCon);
                    ArsenalInterface aiSession = ArsenalDBMgr.GetInst(sessionSelect);
                    tb_sys_session qdsSession = aiSession.RunQuerySingleORM<tb_sys_session>(sessionSelect);
                    if (qdsSession != null && !string.IsNullOrEmpty(qdsSession.create_user_uid))
                    {
                        // get receive_branch_uid
                        tb_hr_employee cEmployee = new tb_hr_employee();
                        cEmployee.SetDirty(tb_hr_employee.CN_RECEIVE_BRANCH_UID);
                        tb_hr_employee eCon = new tb_hr_employee()
                        {
                            uid = qdsSession.create_user_uid
                        };
                        Command employeeSelect = Command.SetupSelectCmd(cEmployee, eCon);
                        ArsenalInterface aiEmployee = ArsenalDBMgr.GetInst(employeeSelect);
                        tb_hr_employee qdsEmployee = aiEmployee.RunQuerySingleORM<tb_hr_employee>(employeeSelect);

                        if (qdsEmployee != null) // 目前 receive_branch_uid 可以為null 後期須改為 != null
                        {
                            foreach (JObject row in jaData)
                            {
                                Dictionary<string, object> dicInput = row.ToObject<Dictionary<string, object>>();
                                string article2productUID = dicInput[tb_prd_article2product.CN_UID].ToString();
                                JArray orderUID = dicInput[tb_ord_order_detail.CN_ORDER_UID] as JArray;
                                var aOrderUid = orderUID.Select(x => x.ToString()).ToArray();
                                string specification = dicInput.ContainsKey(tb_ord_order_detail.CN_SPECIFICATION) ? dicInput[tb_ord_order_detail.CN_SPECIFICATION].ToString() : null;
                                dicInput.Remove(tb_prd_article2product.CN_UID);

                                // 用article2product_uid + member_uid + order_uid 查詢order_detail
                                tb_ord_order_detail cOrder = new tb_ord_order_detail();
                                cOrder.SetFullDirty();
                                List<WhereNode> lwWhereData = new List<WhereNode>();
                                lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), article2productUID));
                                lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aOrderUid));
                                if (specification != null)
                                {
                                    lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_SPECIFICATION, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), specification));
                                }
                                lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake));

                                var jaMember = dicInput["member"] as JArray;
                                var aMember = jaMember.Select(x => x.ToString()).ToArray();

                                if (aMember[0] != "")
                                {
                                    lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_MEMBER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aMember));
                                }
                                else
                                {
                                    lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_MEMBER_UID, WhereNode.EColumnOperation.EOT_ISNULL, typeof(tb_ord_order_detail)));
                                }
                                Command cSelect = Command.SetupSelectCmd(cOrder, new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwWhereData.ToArray()));
                                ai = ArsenalDBMgr.GetInst(cSelect);
                                List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                                var takeQty = Convert.ToInt32(dicInput[tb_ord_order_detail.CN_ORDER_QTY]);
                                int type = 0;
                                if (dicInput.ContainsKey("type"))
                                {
                                    type = Convert.ToInt32(dicInput["type"]);
                                }

                                foreach (var memberOrder in qdsOrder.GroupBy(x => x.member_uid))
                                {
                                    var memberUID = memberOrder.Key;
                                    var purchaseUID = addMainData.Any(x => x.member_uid == memberUID) ?
                                        addMainData.Where(x => x.member_uid == memberUID).Select(x => x.uid).SingleOrDefault() : Guid.NewGuid().ToString();
                                    var amount = 0;
                                    var returnCount = 0; // 計算退貨次數
                                    foreach (var order in memberOrder)
                                    {
                                        var detailNum = order.order_qty - order.take_qty;
                                        var updateDetailNum = takeQty >= detailNum ? detailNum : takeQty;
                                        if (takeQty != 0) // 數量 = 0 不新增purchase
                                        {
                                            tb_ord_purchase_detail cDeatil = new tb_ord_purchase_detail()
                                            {
                                                purchase_uid = purchaseUID,
                                                branch_uid = dicInput[tb_ord_purchase_detail.CN_BRANCH_UID].ToString(),
                                                order_detail_uid = order.uid,
                                                article2product_uid = article2productUID,
                                                specification = order.specification != "" ? order.specification : null,
                                                qty = updateDetailNum,
                                                amount = updateDetailNum * Convert.ToInt32(dicInput[tb_prd_article2product.CN_PRICE])
                                            };
                                            lCmdsDetail.Add(Command.SetupInsertCmd(cDeatil));
                                            amount += cDeatil.amount;
                                        }
                                        // 新增收款明細

                                        // 調整訂單狀態、take_qty       type = 1 為退貨 已取貨, = 2 為保留 未取貨
                                        if (takeQty != 0)
                                        {
                                            if (takeQty >= detailNum) // 該筆detail 全拿
                                            {
                                                lCmds.Add(Command.SetupUpdateCmd(new tb_ord_order_detail()
                                                {
                                                    status = (int)Enums.OrderStatus.Taked,
                                                    take_qty = updateDetailNum + order.take_qty,
                                                    operate_date = now
                                                }
                                                , new tb_ord_order_detail() { uid = order.uid }));
                                                takeQty -= updateDetailNum;
                                            }
                                            else
                                            {
                                                if (type == 1) // 退貨
                                                {
                                                    lCmds.Add(Command.SetupUpdateCmd(new tb_ord_order_detail()
                                                    {
                                                        status = (int)Enums.OrderStatus.Taked,
                                                        take_qty = updateDetailNum + order.take_qty,
                                                        order_qty = updateDetailNum + order.take_qty,
                                                        price = (updateDetailNum + order.take_qty) * order.price / order.order_qty,
                                                        operate_date = now
                                                    }
                                                    , new tb_ord_order_detail() { uid = order.uid }));
                                                    // 新增退貨訂單order
                                                    string sNewArticleUid = Guid.NewGuid().ToString();
                                                    tb_ord_order_detail cNew = new tb_ord_order_detail()
                                                    {
                                                        uid = sNewArticleUid,
                                                        order_uid = order.order_uid,
                                                        article2product_uid = order.article2product_uid,
                                                        member_uid = order.member_uid,
                                                        order_qty = order.order_qty - updateDetailNum - order.take_qty,
                                                        take_qty = 0,
                                                        price = (order.order_qty - updateDetailNum - order.take_qty) * order.price / order.order_qty,
                                                        status = (int)Enums.OrderStatus.Return,
                                                        comment_uid = order.comment_uid,
                                                        comment_time = order.comment_time,
                                                        operate_date = now,
                                                        specification = order.specification
                                                    };

                                                    lCmds.Add(Command.SetupInsertCmd(cNew));
                                                    returnCount += 1;
                                                }
                                                else // 保留
                                                {
                                                    lCmds.Add(Command.SetupUpdateCmd(new tb_ord_order_detail()
                                                    {
                                                        status = (int)Enums.OrderStatus.NotTake,
                                                        take_qty = updateDetailNum + order.take_qty
                                                    }
                                                    , new tb_ord_order_detail() { uid = order.uid }));
                                                }
                                                takeQty -= updateDetailNum;
                                            }
                                        }
                                        else
                                        {
                                            lCmds.Add(Command.SetupUpdateCmd(new tb_ord_order_detail()
                                            {
                                                status = type == 1 ? (int)Enums.OrderStatus.Return : (int)Enums.OrderStatus.NotTake,
                                                take_qty = updateDetailNum,
                                                operate_date = now
                                            }
                                            , new tb_ord_order_detail() { uid = order.uid }));

                                            returnCount += type == 1 ? 1 : 0;
                                            takeQty -= updateDetailNum;
                                        }
                                    }
                                    if (Convert.ToInt32(dicInput[tb_ord_order_detail.CN_ORDER_QTY]) != 0)  // 數量為零不新增
                                    {
                                        if (addMainData.Any(x => x.member_uid == memberUID))
                                        {
                                            var model = addMainData.Where(x => x.member_uid == memberUID).SingleOrDefault();
                                            model.amount += amount;
                                        }
                                        else
                                        {
                                            if (dicPoints.ContainsKey(memberUID)) // 新增purchase時 檢查是否需要扣購物金
                                            {
                                                amount -= dicPoints[memberUID];
                                                // 新增一筆扣款購物金
                                                tb_meb_shopping_points_record cPointsNew = new tb_meb_shopping_points_record()
                                                {
                                                    uid = Guid.NewGuid().ToString(),
                                                    member_uid = memberUID,
                                                    amount = -dicPoints[memberUID],
                                                    purchase_uid = purchaseUID
                                                };
                                                addPointsData.Add(cPointsNew);
                                            }
                                            tb_ord_purchase cNew = new tb_ord_purchase()
                                            {
                                                uid = purchaseUID,
                                                member_uid = memberUID,
                                                amount = amount,
                                                receive_branch_uid = qdsEmployee.receive_branch_uid
                                            };
                                            addMainData.Add(cNew);
                                        }
                                    }
                                    if (returnCount > 0)
                                    {
                                        sMsg = getUpdateReturnCountCmd(memberUID, returnCount);
                                        if (sMsg != null)
                                        {
                                            break;
                                        }
                                    }
                                }
                            }
                            // 新增收款記錄 與新增點數
                            foreach (var mainData in addMainData)
                            {
                                lCmds.Add(Command.SetupInsertCmd(mainData));
                                // 新增一筆點數
                                tb_meb_shopping_points_record cPointsNew = new tb_meb_shopping_points_record()
                                {
                                    uid = Guid.NewGuid().ToString(),
                                    member_uid = mainData.member_uid,
                                    amount = (int)(mainData.amount / 100),
                                    purchase_uid = mainData.uid
                                };
                                addPointsData.Add(cPointsNew);

                            }
                            // 新增購物金扣款
                            foreach (var pointsData in addPointsData)
                            {
                                lCmds.Add(Command.SetupInsertCmd(pointsData));
                            }
                            lCmds.AddRange(lCmdsDetail);
                            var groupPointsData = addPointsData.GroupBy(x => x.member_uid);
                            foreach (var memberPointsData in groupPointsData)
                            {
                                tb_meb_member cPoints = new tb_meb_member();
                                cPoints.SetDirty(tb_meb_member.CN_SHOPPING_ACCOUNT);
                                tb_meb_member cConPoints = new tb_meb_member()
                                {
                                    uid = memberPointsData.Key
                                };
                                Command pointsSelect = Command.SetupSelectCmd(cPoints, cConPoints);
                                ArsenalInterface aiPoints = ArsenalDBMgr.GetInst(pointsSelect);
                                var qdsPoints = aiPoints.RunQueryList<tb_meb_member>(pointsSelect);
                                lCmds.Add(Command.SetupUpdateCmd(new tb_meb_member()
                                {
                                    shopping_account = qdsPoints[0].shopping_account + Convert.ToInt32(memberPointsData.Sum(x => x.amount))
                                }
                                , new tb_meb_member() { uid = memberPointsData.Key }));
                            }

                            if (lCmds.Any())
                            {
                                ai = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());
                                ai.RunEditCmds(lCmds);
                                string sErrorCode = GetLastErrorCode(lCmds);

                                if (sErrorCode != null)
                                {
                                    sMsg = sErrorCode;
                                    break;
                                }
                            }
                            crmRes = new CSuccessResponseMessage(null, i_crmInput);
                        }
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(CheckOut)} 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 SetMemo(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmds = new List<Command>();
            Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);
            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    foreach (var joData in dicCondition)
                    {
                        string memberUID = (joData.Value as dynamic).member_uid.ToString();
                        string memo = (joData.Value as dynamic).memo.ToString();
                        tb_ord_order_detail upData = new tb_ord_order_detail() { member_uid = memberUID, status = (int)Enums.OrderStatus.NotTake };
                        tb_ord_order_detail upCon = new tb_ord_order_detail();
                        upCon.memo = memo;
                        lCmds.Add(Command.SetupUpdateCmd(upCon, upData));
                    }
                    if (lCmds.Any())
                    {
                        ArsenalInterface ai = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmds);
                        string sErrorCode = GetLastErrorCode(lCmds);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(SetMemo)} 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 class StockModel
        {
            public string uid { get; set; }
            public string article2product_uid { get; set; }
            public string article_name { get; set; }
            public string name { get; set; }
            public int price { get; set; }
            public int qty { get; set; }
            public int seq { get; set; }
            public string specification { get; set; }
        }

        /// <summary>
        /// 取得現貨區商品(現貨訂單、訂單已封存)
        /// </summary>
        /// <param name="i_crmInput"></param>
        /// <returns></returns>
        public CResponseMessage GetStockList(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;

            try
            {
                do
                {
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);
                    var session = ProjectHelper.GetLoginUser(i_crmInput);
                    if (session == null)
                    {
                        sMsg = "Session error!!";
                        Logger.Error($"Session error!! i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ");
                    }
                    sMsg = GetFinalData(dicCondition, session.receive_branch_uid, out List<StockModel> finalData, out List<tb_grp_group> qdsGroup);
                    if (null != sMsg)
                    {
                        break;
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, finalData);

                    if (qdsGroup.Any())
                    {
                        crmRes.param.Add("groupData", qdsGroup[0]);
                    }
                    else
                    {
                        tb_grp_branch branch = new tb_grp_branch();
                        branch.SetDirty(tb_grp_branch.CN_BRANCH_NAME, tb_grp_branch.CN_GROUP_UID, tb_grp_branch.CN_UID);
                        tb_grp_branch branchCon = new tb_grp_branch()
                        {
                            uid = dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString()
                        };
                        Command aSelect = Command.SetupSelectCmd(branch, branchCon);
                        ArsenalInterface aiBranch = ArsenalDBMgr.GetInst(aSelect);
                        List<tb_grp_branch> qdsBranch = aiBranch.RunQueryList<tb_grp_branch>(aSelect);
                        crmRes.param.Add("branchData", qdsBranch[0]);
                    }
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetStockList)} 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 GetFinalData(Dictionary<string, object> dicCondition, string branch_uid, out List<StockModel> finalData, out List<tb_grp_group> qdsGroup)
        {
            string sMsg = null;
            finalData = new List<StockModel>();
            do
            {
                var isGroup = false;
                string groupUid = "";
                qdsGroup = new List<tb_grp_group>();
                if (dicCondition.ContainsKey(tb_ord_order_master.CN_BRANCH_UID))
                {
                    tb_grp_group cGroup = new tb_grp_group();
                    cGroup.SetFullDirty();
                    WhereNode wnGroup = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString());
                    Command gSelect = Command.SetupSelectCmd(cGroup, wnGroup);
                    ArsenalInterface aiGroup = ArsenalDBMgr.GetInst(gSelect);
                    qdsGroup = aiGroup.RunQueryList<tb_grp_group>(gSelect);
                    if (qdsGroup.Any())
                    {
                        isGroup = true;
                    }
                    groupUid = dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString();
                }
                else
                {
                    dicCondition[tb_ord_order_master.CN_BRANCH_UID] = branch_uid;
                }


                // 查詢條件 : 社團uid、分店uid?
                QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                QueryJsonElement qjeMain = lBlocks.GetInst();
                qjeMain.table = tb_ord_order_master.TABLENAME;
                qjeMain.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "article_name"} },
                    };
                qjeMain.displaycols = new List<string>() { tb_ord_order_master.CN_UID, tb_ord_order_master.CN_BRANCH_UID };
                // 現貨訂單
                List<WhereNode> lwMain = new List<WhereNode>();
                lwMain.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Stock));
                lwMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), BLWording.STATUS_FLAG_ON));
                lwMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                if (isGroup)
                {
                    lwMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                }
                else
                {
                    lwMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]));
                }
                qjeMain.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain.ToArray());

                QueryJsonElement qjeProduct = lBlocks.GetInst();
                qjeProduct.table = tb_prd_article2product.TABLENAME;
                qjeProduct.jointype = QueryJsonElement.JOIN;
                qjeProduct.jointable = qjeMain;
                qjeProduct.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                qjeProduct.displaycols = new List<string>() {
                        tb_prd_article2product.CN_QTY,
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_SEQ
                    };
                qjeProduct.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                    };

                lBlocks.Add(qjeMain);
                lBlocks.Add(qjeProduct);

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

                foreach (var order in qds)
                {
                    order.specification = "";
                }

                // 封存訂單(訂單未取貨、退貨則視為現貨)
                //List<WhereNode> lwMain2 = new List<WhereNode>();
                //lwMain2.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                //if (isGroup)
                //{
                //    lwMain2.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                //}
                //else
                //{
                //    lwMain2.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]));
                //}
                //qjeMain2.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain2.ToArray());
                //qjeMain2.displaycols = new List<string>() { tb_ord_order_master.CN_UID };

                //QueryJsonElement qjeOrder = lBlocks.GetInst();
                //qjeOrder.table = tb_ord_order_detail.TABLENAME;
                //qjeOrder.jointype = QueryJsonElement.LEFT_JOIN;
                //qjeOrder.jointable = qjeMain2;
                //qjeOrder.joincols = new Dictionary<string, string>() {
                //             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                //qjeOrder.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_OR,
                //    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake),
                //    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Taked),
                //    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Return),
                //    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Abandon));
                //qjeOrder.displaycols = new List<string>() { tb_ord_order_detail.CN_SPECIFICATION };
                //qjeOrder.aliascols = new Dictionary<string, List<string>>
                //    {
                //         { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                //         { QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } }
                //    };

                //QueryJsonElement qjeProduct2 = lBlocks.GetInst();
                //qjeProduct2.table = tb_prd_article2product.TABLENAME;
                //qjeProduct2.jointype = QueryJsonElement.LEFT_JOIN;
                //qjeProduct2.jointable = qjeOrder;
                //qjeProduct2.joincols = new Dictionary<string, string>() {
                //             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                //qjeProduct2.displaycols = new List<string>() {
                //        tb_prd_article2product.CN_NAME,
                //        tb_prd_article2product.CN_PRICE,
                //        tb_prd_article2product.CN_SEQ
                //    };
                //qjeProduct2.aliascols = new Dictionary<string, List<string>>
                //    {
                //         { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                //    };


                //qjeMain2.groupcols = new List<Tuple<QueryJsonElement, string>>
                //    {
                //        Tuple.Create(qjeMain2, tb_ord_order_master.CN_NAME),
                //        Tuple.Create(qjeMain2, tb_ord_order_master.CN_UID),
                //        Tuple.Create(qjeOrder, tb_ord_order_detail.CN_SPECIFICATION),
                //        Tuple.Create(qjeProduct2, tb_prd_article2product.CN_NAME),
                //        Tuple.Create(qjeProduct2, tb_prd_article2product.CN_PRICE),
                //        Tuple.Create(qjeProduct2, tb_prd_article2product.CN_UID),
                //        Tuple.Create(qjeProduct2, tb_prd_article2product.CN_SEQ)
                //    };

                //lBlocks.Add(qjeMain2);
                //lBlocks.Add(qjeOrder);
                //lBlocks.Add(qjeProduct2);

                //sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                //if (sMsg != null)
                //{
                //    break;
                //}
                //ai = ArsenalDBMgr.GetInst(cRes);
                //QueryDataSet qds2 = ai.RunQueryDataSet(cRes);

                var qds2Model = new List<StockModel>() { };
                //foreach (DataRow row in qds2.DATA.Tables[0].Rows)
                //{
                //    var orderCount = Convert.ToInt32(row["count"]);
                //    var takeCount = Convert.ToInt32(row["soldCount"]);
                //    if (orderCount - takeCount > 0)
                //    {
                //        qds2Model.Add(new StockModel
                //        {
                //            uid = row[tb_ord_order_master.CN_UID].ToString(),
                //            article2product_uid = row[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString(),
                //            article_name = row["article_name"].ToString(),
                //            name = row[tb_prd_article2product.CN_NAME].ToString(),
                //            price = Convert.ToInt32(row[tb_prd_article2product.CN_PRICE]),
                //            qty = Convert.ToInt32(row["count"]) - Convert.ToInt32(row["soldCount"]),
                //            seq = Convert.ToInt32(row[tb_prd_article2product.CN_SEQ]),
                //            specification = row[tb_ord_order_detail.CN_SPECIFICATION].ToString()
                //        });
                //    }
                //}

                // 進貨量 > 訂購量
                lBlocks.Clear();
                cRes = null;
                QueryJsonElement qjeMain3 = lBlocks.GetInst();
                qjeMain3.table = tb_ord_order_master.TABLENAME;
                qjeMain3.displaycols = new List<string>() { tb_ord_order_master.CN_UID };
                qjeMain3.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "article_name"} },
                    };

                List<WhereNode> lwMain3 = new List<WhereNode>();
                lwMain3.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                lwMain3.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Article));
                if (isGroup)
                {
                    lwMain3.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                }
                else
                {
                    lwMain3.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]?.ToString()));
                }
                qjeMain3.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain3.ToArray());

                QueryJsonElement qjeDetail = lBlocks.GetInst();
                qjeDetail.table = tb_ord_order_detail.TABLENAME;
                qjeDetail.jointype = QueryJsonElement.LEFT_JOIN;
                qjeDetail.jointable = qjeMain3;
                qjeDetail.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                qjeDetail.displaycols = new List<string>() { tb_ord_order_detail.CN_SPECIFICATION };
                qjeDetail.aliascols = new Dictionary<string, List<string>>
                    {
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } }
                    };

                QueryJsonElement qjeProduct3 = lBlocks.GetInst();
                qjeProduct3.table = tb_prd_article2product.TABLENAME;
                qjeProduct3.jointype = QueryJsonElement.JOIN;
                qjeProduct3.jointable = qjeDetail;
                qjeProduct3.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                qjeProduct3.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_SEQ
                    };
                qjeProduct3.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                    };

                qjeMain3.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeMain3, tb_ord_order_master.CN_NAME),
                        Tuple.Create(qjeMain3, tb_ord_order_master.CN_UID),
                        Tuple.Create(qjeDetail, tb_ord_order_detail.CN_SPECIFICATION),
                        Tuple.Create(qjeProduct3, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeProduct3, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeProduct3, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeProduct3, tb_prd_article2product.CN_SEQ)
                    };

                lBlocks.Add(qjeMain3);
                lBlocks.Add(qjeDetail);
                lBlocks.Add(qjeProduct3);

                sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                if (sMsg != null)
                {
                    break;
                }
                ai = ArsenalDBMgr.GetInst(cRes);
                QueryDataSet qds3 = ai.RunQueryDataSet(cRes);

                //List<string> lsOrderUID = new List<string>();
                //foreach (DataRow row in qds3.DATA.Tables[0].Rows)
                //{
                //    lsOrderUID.Add(row[tb_ord_order_master.CN_UID].ToString());
                //}
                //List<tb_ord_incoming_return_record> qdsRecord = new List<tb_ord_incoming_return_record>();
                //var arrayCount = lsOrderUID.Count / 1500;
                //for (var i = 0; i <= arrayCount; i++)
                //{
                //    string[] slicedArray = lsOrderUID.Skip(i * 1500).Take(1500).ToArray();

                //    // 查詢進貨數量
                //    tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
                //    cRecord.SetFullDirty();
                //    WhereNode wn = new WhereNode(tb_ord_incoming_return_record.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_incoming_return_record), slicedArray);
                //    Command cSelect = Command.SetupSelectCmd(cRecord, wn);
                //    ai = ArsenalDBMgr.GetInst(cSelect);
                //    qdsRecord.AddRange(ai.RunQueryList<tb_ord_incoming_return_record>(cSelect));
                //}
                List<tb_ord_incoming_return_record> qdsRecord = new List<tb_ord_incoming_return_record>();
                {
                    lBlocks.Clear();
                    Command cInner = null;
                    QueryJsonElement qjeMain3_1 = lBlocks.GetInst();
                    qjeMain3_1.table = tb_ord_order_master.TABLENAME;
                    qjeMain3_1.displaycols = new List<string>() { tb_ord_order_master.CN_UID };
                    //qjeMain3_1.aliascols = new Dictionary<string, List<string>>
                    //{
                    //     { tb_ord_order_master.CN_NAME, new List<string>() { "article_name"} },
                    //};

                    List<WhereNode> lwMain3_1 = new List<WhereNode>();
                    lwMain3_1.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                    lwMain3_1.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Article));
                    if (isGroup)
                    {
                        lwMain3_1.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                    }
                    else
                    {
                        lwMain3_1.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]?.ToString()));
                    }
                    qjeMain3_1.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain3_1.ToArray());

                    QueryJsonElement qjeDetail3_1 = lBlocks.GetInst();
                    qjeDetail3_1.table = tb_ord_order_detail.TABLENAME;
                    qjeDetail3_1.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeDetail3_1.jointable = qjeMain3_1;
                    qjeDetail3_1.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                    //qjeDetail3_1.displaycols = new List<string>() { tb_ord_order_detail.CN_SPECIFICATION };
                    //qjeDetail3_1.aliascols = new Dictionary<string, List<string>>
                    //{
                    //     { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } }
                    //};

                    QueryJsonElement qjeProduct3_1 = lBlocks.GetInst();
                    qjeProduct3_1.table = tb_prd_article2product.TABLENAME;
                    qjeProduct3_1.jointype = QueryJsonElement.JOIN;
                    qjeProduct3_1.jointable = qjeDetail3_1;
                    qjeProduct3_1.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    //qjeProduct3_1.displaycols = new List<string>() {
                    //    tb_prd_article2product.CN_NAME,
                    //    tb_prd_article2product.CN_PRICE,
                    //    tb_prd_article2product.CN_SEQ
                    //};
                    //qjeProduct3_1.aliascols = new Dictionary<string, List<string>>
                    //{
                    //     { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                    //};

                    qjeMain3_1.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeMain3_1, tb_ord_order_master.CN_NAME),
                        Tuple.Create(qjeMain3_1, tb_ord_order_master.CN_UID),
                        Tuple.Create(qjeDetail3_1, tb_ord_order_detail.CN_SPECIFICATION),
                        Tuple.Create(qjeProduct3_1, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeProduct3_1, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeProduct3_1, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeProduct3_1, tb_prd_article2product.CN_SEQ)
                    };

                    lBlocks.Add(qjeMain3_1);
                    lBlocks.Add(qjeDetail3_1);
                    lBlocks.Add(qjeProduct3_1);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cInner);

                    if (sMsg != null)
                    {
                        break;
                    }

                    tb_ord_incoming_return_record cRecord = new tb_ord_incoming_return_record();
                    cRecord.SetFullDirty();
                    WhereNode wn = new WhereNode(tb_ord_incoming_return_record.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_INSQL, typeof(tb_ord_incoming_return_record), cInner);
                    Command cSelect = Command.SetupSelectCmd(cRecord, wn);
                    ai = ArsenalDBMgr.GetInst(cSelect);
                    qdsRecord.AddRange(ai.RunQueryList<tb_ord_incoming_return_record>(cSelect));
                }

                var groupItem = qdsRecord.GroupBy(x => new { x.article2product_uid, x.order_uid, x.operating, x.specification }).Select(x => new { order_uid = x.Key.order_uid, rticle2product_uid = x.Key.article2product_uid, specification = x.Key.specification, operating = x.Key.operating, qty = x.Sum(x => x.qty) }).ToDictionary(x=>Tuple.Create(x.operating,x.order_uid,x.rticle2product_uid,x.specification),x=>x);
                qdsRecord = null;
                foreach (DataRow row in qds3.DATA.Tables[0].Rows)
                {
                    var article2productUID = row[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString();
                    var specification = row[tb_ord_order_detail.CN_SPECIFICATION].ToString() == "" ? null : row[tb_ord_order_detail.CN_SPECIFICATION].ToString();
                  
                    int incomeQty = 0, returnQty = 0;
                    var keyIncome = Tuple.Create((int)Enums.IncomingReturnOperating.Incoming, row[tb_ord_order_master.CN_UID].ToString(), article2productUID, specification);
                    var keyReturn = Tuple.Create((int)Enums.IncomingReturnOperating.Return, row[tb_ord_order_master.CN_UID].ToString(), article2productUID, specification);
                    if (groupItem.ContainsKey(keyIncome))
                    {
                        incomeQty = groupItem[keyIncome].qty;
                    }
                    if (groupItem.ContainsKey(keyReturn))
                    {
                        returnQty = groupItem[keyReturn].qty;
                    }

                    var orderCount = row["count"] is DBNull ? 0 : Convert.ToInt32(row["count"]);
                    if (incomeQty - returnQty - orderCount > 0)
                    {
                        qds2Model.Add(new StockModel
                        {
                            uid = row[tb_ord_order_master.CN_UID].ToString(),
                            article2product_uid = article2productUID,
                            article_name = row["article_name"].ToString(),
                            name = row[tb_prd_article2product.CN_NAME].ToString(),
                            price = Convert.ToInt32(row[tb_prd_article2product.CN_PRICE]),
                            qty = incomeQty - returnQty - orderCount,
                            seq = Convert.ToInt32(row[tb_prd_article2product.CN_SEQ]),
                            specification = row[tb_ord_order_detail.CN_SPECIFICATION].ToString()
                        });
                    }
                }


                lBlocks.Clear();
                cRes = null;
                QueryJsonElement qjeMain4 = lBlocks.GetInst();
                qjeMain4.table = tb_ord_order_master.TABLENAME;
                qjeMain4.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "article_name"} },
                    };
                qjeMain4.displaycols = new List<string>() { tb_ord_order_master.CN_UID };

                // 已到貨訂單 已取貨與退貨 order_qty > take_qty 當現貨
                List<WhereNode> lwMain4 = new List<WhereNode>();
                lwMain4.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Arrived));
                lwMain4.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Purchase));
                if (isGroup)
                {
                    lwMain4.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                }
                else
                {
                    lwMain4.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]));
                }
                qjeMain4.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain4.ToArray());

                QueryJsonElement qjeOrder2 = lBlocks.GetInst();
                qjeOrder2.table = tb_ord_order_detail.TABLENAME;
                qjeOrder2.jointype = QueryJsonElement.LEFT_JOIN;
                qjeOrder2.jointable = qjeMain4;
                qjeOrder2.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                qjeOrder2.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_OR,
                    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Taked),
                    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Return),
                    new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Abandon));
                qjeOrder2.aliascols = new Dictionary<string, List<string>>
                    {
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } }
                    };
                qjeOrder2.displaycols = new List<string>() { tb_ord_order_detail.CN_SPECIFICATION };

                QueryJsonElement qjeProduct4 = lBlocks.GetInst();
                qjeProduct4.table = tb_prd_article2product.TABLENAME;
                qjeProduct4.jointype = QueryJsonElement.JOIN;
                qjeProduct4.jointable = qjeOrder2;
                qjeProduct4.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                qjeProduct4.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_SEQ
                    };
                qjeProduct4.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                    };


                qjeMain4.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeMain4, tb_ord_order_master.CN_NAME),
                        Tuple.Create(qjeMain4, tb_ord_order_master.CN_UID),
                        Tuple.Create(qjeOrder2, tb_ord_order_detail.CN_SPECIFICATION),
                        Tuple.Create(qjeProduct4, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeProduct4, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeProduct4, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeProduct4, tb_prd_article2product.CN_SEQ)
                    };

                lBlocks.Add(qjeMain4);
                lBlocks.Add(qjeOrder2);
                lBlocks.Add(qjeProduct4);

                sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                if (sMsg != null)
                {
                    break;
                }
                ai = ArsenalDBMgr.GetInst(cRes);
                QueryDataSet qds4 = ai.RunQueryDataSet(cRes);

                foreach (DataRow row in qds4.DATA.Tables[0].Rows)
                {
                    var orderCount = Convert.ToInt32(row["count"]);
                    var takeCount = Convert.ToInt32(row["soldCount"]);
                    if (orderCount - takeCount > 0)
                    {
                        qds2Model.Add(new StockModel
                        {
                            uid = row[tb_ord_order_master.CN_UID].ToString(),
                            article2product_uid = row[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString(),
                            article_name = row["article_name"].ToString(),
                            name = row[tb_prd_article2product.CN_NAME].ToString(),
                            price = Convert.ToInt32(row[tb_prd_article2product.CN_PRICE]),
                            qty = Convert.ToInt32(row["count"]) - Convert.ToInt32(row["soldCount"]),
                            seq = Convert.ToInt32(row[tb_prd_article2product.CN_SEQ]),
                            specification = row[tb_ord_order_detail.CN_SPECIFICATION].ToString()
                        });
                    }
                }

                // 扣掉現場銷貨的現貨order_detail qty
                lBlocks.Clear();
                cRes = null;
                QueryJsonElement qjeMain5 = lBlocks.GetInst();
                qjeMain5.table = tb_ord_order_master.TABLENAME;
                qjeMain5.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "article_name"} },
                    };
                qjeMain5.displaycols = new List<string>() { tb_ord_order_master.CN_UID };
                List<WhereNode> lwMain5 = new List<WhereNode>();
                lwMain5.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Arrived));
                lwMain5.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Purchase));

                if (isGroup)
                {
                    lwMain5.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), groupUid));
                }
                else
                {
                    lwMain5.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID]));
                }
                qjeMain5.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwMain5.ToArray());

                QueryJsonElement qjeOrder5 = lBlocks.GetInst();
                qjeOrder5.table = tb_ord_order_detail.TABLENAME;
                qjeOrder5.jointype = QueryJsonElement.LEFT_JOIN;
                qjeOrder5.jointable = qjeMain5;
                qjeOrder5.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                qjeOrder5.aliascols = new Dictionary<string, List<string>>
                    {
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                         { QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } }
                    };
                qjeOrder5.displaycols = new List<string>() { tb_ord_order_detail.CN_SPECIFICATION };

                List<WhereNode> lwDetail5 = new List<WhereNode>();
                lwDetail5.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake));
                lwDetail5.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.Taked));
                qjeOrder5.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_OR, lwDetail5.ToArray());

                QueryJsonElement qjeProduct5 = lBlocks.GetInst();
                qjeProduct5.table = tb_prd_article2product.TABLENAME;
                qjeProduct5.jointype = QueryJsonElement.JOIN;
                qjeProduct5.jointable = qjeOrder5;
                qjeProduct5.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                qjeProduct5.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_SEQ
                    };
                qjeProduct5.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_UID, new List<string>() { tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID } },
                    };

                qjeMain5.groupcols = new List<Tuple<QueryJsonElement, string>>
                    {
                        Tuple.Create(qjeMain5, tb_ord_order_master.CN_NAME),
                        Tuple.Create(qjeMain5, tb_ord_order_master.CN_UID),
                        Tuple.Create(qjeOrder5, tb_ord_order_detail.CN_SPECIFICATION),
                        Tuple.Create(qjeProduct5, tb_prd_article2product.CN_NAME),
                        Tuple.Create(qjeProduct5, tb_prd_article2product.CN_PRICE),
                        Tuple.Create(qjeProduct5, tb_prd_article2product.CN_UID),
                        Tuple.Create(qjeProduct5, tb_prd_article2product.CN_SEQ)
                    };

                lBlocks.Add(qjeMain5);
                lBlocks.Add(qjeOrder5);
                lBlocks.Add(qjeProduct5);

                sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                if (sMsg != null)
                {
                    break;
                }
                ai = ArsenalDBMgr.GetInst(cRes);
                QueryDataSet qds5 = ai.RunQueryDataSet(cRes);

                foreach (DataRow row in qds5.DATA.Tables[0].Rows)
                {
                    var orderCount = Convert.ToInt32(row["count"]);
                    if (orderCount > 0)
                    {
                        qds2Model.Add(new StockModel
                        {
                            uid = row[tb_ord_order_master.CN_UID].ToString(),
                            article2product_uid = row[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString(),
                            article_name = row["article_name"].ToString(),
                            name = row[tb_prd_article2product.CN_NAME].ToString(),
                            price = Convert.ToInt32(row[tb_prd_article2product.CN_PRICE]),
                            qty = -Convert.ToInt32(row["count"]),
                            seq = Convert.ToInt32(row[tb_prd_article2product.CN_SEQ]),
                            specification = row[tb_ord_order_detail.CN_SPECIFICATION].ToString(),
                        });
                    }
                }

                var Data = from x in qds.Concat(qds2Model).GroupBy(x => new { x.article2product_uid, x.specification })
                           select new StockModel
                           {
                               uid = x.FirstOrDefault().uid,
                               article2product_uid = x.Key.article2product_uid,
                               specification = x.Key.specification,
                               article_name = x.Select(x => x.article_name).FirstOrDefault(),
                               name = x.Select(x => x.name).FirstOrDefault(),
                               price = x.FirstOrDefault().price,
                               qty = x.Sum(c => c.qty),
                               seq = x.FirstOrDefault().seq
                           };
                finalData = Data.Where(x => x.qty > 0).ToList<StockModel>();
            }
            while (false);

            return sMsg;
        }

        public CResponseMessage UpdateDetail(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmdUpdate = new List<Command>();
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.UPD_MASTER] as JArray;
                    var now = DateTime.Now;
                    foreach (JToken joData in jaData)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();

                        var orderQty = Convert.ToInt32(dicData[tb_ord_order_detail.CN_ORDER_QTY]);   // 為 detail.order_qty - detail.take_qty
                        var returnQty = Convert.ToInt32(dicData[tb_ord_order_detail.CN_ORDER_QTY + "_copy"]);
                        var isEaqual = orderQty == returnQty;
                        var jaMember = dicData["member"] as JArray;
                        var aMember = jaMember.Select(x => x.ToString()).ToArray();
                        JArray orderUID = dicData[tb_ord_order_detail.CN_ORDER_UID] as JArray;
                        var aOrderUid = orderUID.Select(x => x.ToString()).ToArray();
                        // 用article2product_uid + member_uid + order_uid 查詢order_detail
                        tb_ord_order_detail cOrder = new tb_ord_order_detail();
                        cOrder.SetDirty(tb_ord_order_detail.CN_UID, tb_ord_order_detail.CN_ORDER_UID, tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID, tb_ord_order_detail.CN_ORDER_QTY, tb_ord_order_detail.CN_MEMBER_UID,
                            tb_ord_order_detail.CN_TAKE_QTY, tb_ord_order_detail.CN_PRICE, tb_ord_order_detail.CN_COMMENT_UID, tb_ord_order_detail.CN_COMMENT_TIME, tb_ord_order_detail.CN_STATUS, tb_ord_order_detail.CN_SPECIFICATION);
                        List<WhereNode> lwWhereData = new List<WhereNode>();
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), dicData[tb_ord_order_detail.CN_UID]));
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake));
                        if (dicData.ContainsKey(tb_ord_order_detail.CN_SPECIFICATION))
                        {
                            if (dicData[tb_ord_order_detail.CN_SPECIFICATION] != "" && dicData[tb_ord_order_detail.CN_UID] != null)
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_SPECIFICATION, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), dicData[tb_ord_order_detail.CN_SPECIFICATION]));
                            }
                        }
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aOrderUid));
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_MEMBER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aMember));
                        Command cSelect = Command.SetupSelectCmd(cOrder, new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwWhereData.ToArray()));
                        ai = ArsenalDBMgr.GetInst(cSelect);
                        List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                        foreach (var order in qdsOrder.OrderBy(x => x.comment_time))
                        {
                            // 判斷數量
                            if (returnQty > 0)
                            {
                                var orderDetailQty = order.order_qty - order.take_qty;
                                var updateNum = returnQty >= orderDetailQty ? orderDetailQty : returnQty;

                                tb_ord_order_detail cDetail = new tb_ord_order_detail();
                                if (orderDetailQty == updateNum)
                                {
                                    // 如果已經收過款退貨改新增一筆退貨 原訂單改已收款
                                    if (order.take_qty > 0 && order.status == (int)Enums.OrderStatus.NotTake)
                                    {
                                        // 新增退貨訂單order
                                        string sNewArticleUid = Guid.NewGuid().ToString();
                                        tb_ord_order_detail cNew = new tb_ord_order_detail()
                                        {
                                            uid = sNewArticleUid,
                                            order_uid = order.order_uid,
                                            article2product_uid = order.article2product_uid,
                                            member_uid = order.member_uid,
                                            order_qty = updateNum,
                                            take_qty = 0,
                                            price = updateNum * order.price / order.order_qty,
                                            status = (int)Enums.OrderStatus.Return,
                                            comment_uid = order.comment_uid,
                                            comment_time = order.comment_time,
                                            operate_date = now,
                                            specification = order.specification
                                        };
                                        cDetail.order_qty = order.order_qty - updateNum;
                                        cDetail.price = (order.order_qty - updateNum) * order.price / order.order_qty;
                                        cDetail.status = (int)Enums.OrderStatus.Taked;
                                        cDetail.operate_date = now;
                                        lCmdUpdate.Add(Command.SetupInsertCmd(cNew));
                                    }
                                    else
                                    {
                                        cDetail.status = (int)Enums.OrderStatus.Return;
                                        cDetail.operate_date = now;
                                    }
                                }
                                else
                                {
                                    // 部分退則改變訂單量
                                    cDetail.order_qty = order.order_qty - updateNum;
                                    cDetail.price = (order.order_qty - updateNum) * order.price / order.order_qty;
                                    // 新增退貨訂單order
                                    string sNewArticleUid = Guid.NewGuid().ToString();
                                    tb_ord_order_detail cNew = new tb_ord_order_detail()
                                    {
                                        uid = sNewArticleUid,
                                        order_uid = order.order_uid,
                                        article2product_uid = order.article2product_uid,
                                        member_uid = order.member_uid,
                                        order_qty = updateNum,
                                        take_qty = 0,
                                        price = updateNum * order.price / order.order_qty,
                                        status = (int)Enums.OrderStatus.Return,
                                        comment_uid = order.comment_uid,
                                        comment_time = order.comment_time,
                                        operate_date = now,
                                        specification = order.specification
                                    };
                                    lCmdUpdate.Add(Command.SetupInsertCmd(cNew));
                                }
                                tb_ord_order_detail cDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                lCmdUpdate.Add(Command.SetupUpdateCmd(cDetail, cDetailCon));
                                // 更新退貨次數
                                sMsg = getUpdateReturnCountCmd(order.member_uid, 1);
                                if (sMsg != null)
                                {
                                    break;
                                }
                                returnQty -= updateNum;
                            }
                        }
                    }
                    if (lCmdUpdate.Any())
                    {
                        ai = ArsenalDBMgr.GetInst(lCmdUpdate[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmdUpdate);
                        string sErrorCode = GetLastErrorCode(lCmdUpdate);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);

                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(UpdateDetail)} 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 GetStockPurchase(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

                    QueryJsonElement qjeProduct = lBlocks.GetInst();
                    qjeProduct.table = tb_prd_article2product.TABLENAME;
                    qjeProduct.displaycols = new List<string>() {
                        tb_prd_article2product.CN_UID,
                        tb_prd_article2product.CN_SEQ,
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE
                    };
                    qjeProduct.wherecols = new WhereNode(tb_prd_article2product.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_prd_article2product), dicCondition[tb_ord_order_master.CN_UID]);

                    QueryJsonElement qjeA = lBlocks.GetInst();
                    qjeA.table = tb_ord_order_master.TABLENAME;
                    qjeA.jointype = QueryJsonElement.JOIN;
                    qjeA.jointable = qjeProduct;
                    qjeA.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_master.CN_UID,tb_prd_article2product.CN_ORDER_UID }};
                    qjeA.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "order_name"} }
                    };

                    QueryJsonElement qjeDetail = lBlocks.GetInst();
                    qjeDetail.table = tb_ord_purchase_detail.TABLENAME;
                    qjeDetail.jointype = QueryJsonElement.JOIN;
                    qjeDetail.jointable = qjeProduct;
                    qjeDetail.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase_detail.CN_ARTICLE2PRODUCT_UID,tb_prd_article2product.CN_UID }};
                    qjeDetail.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_purchase_detail.CN_QTY, new List<string>() { tb_ord_order_detail.CN_ORDER_QTY} }
                    };
                    qjeDetail.displaycols = new List<string>() { tb_ord_purchase_detail.CN_QTY };

                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_purchase.TABLENAME;
                    qjeMain.jointype = QueryJsonElement.JOIN;
                    qjeMain.jointable = qjeDetail;
                    qjeMain.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase.CN_UID,tb_ord_purchase_detail.CN_PURCHASE_UID }};
                    qjeMain.displaycols = new List<string>() { tb_ord_purchase.CN_MEMBER_UID };

                    QueryJsonElement qjeMember = lBlocks.GetInst();
                    qjeMember.table = tb_meb_member.TABLENAME;
                    qjeMember.jointype = QueryJsonElement.JOIN;
                    qjeMember.jointable = qjeMain;
                    qjeMember.joincols = new Dictionary<string, string>() {
                             {  tb_meb_member.CN_UID,tb_ord_purchase.CN_MEMBER_UID }};
                    qjeMember.aliascols = new Dictionary<string, List<string>>
                    {
                         {tb_meb_member.CN_NAME , new List<string>() { "member_name"} }
                    };

                    lBlocks.Add(qjeProduct);
                    lBlocks.Add(qjeA);
                    lBlocks.Add(qjeDetail);
                    lBlocks.Add(qjeMain);
                    lBlocks.Add(qjeMember);
                    sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    ai = ArsenalDBMgr.GetInst(cRes);
                    List<OrderDetail> qds = ai.RunQueryList<OrderDetail>(cRes);
                    foreach (var data in qds)
                    {
                        data.status = (int)Enums.OrderStatus.Taked;
                    }
                    var groupData = qds.GroupBy(x => new { x.member_uid, x.member_name }).ToList();
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, groupData);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetStockPurchase)} 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 AbandonOrder(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmdUpdate = new List<Command>();
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.UPD_MASTER] as JArray;
                    var abandonReason = i_crmInput.param.ContainsKey(tb_ord_order_detail.CN_ABANDON_REASON) ? i_crmInput.param[tb_ord_order_detail.CN_ABANDON_REASON]?.ToString() : null;

                    foreach (JToken joData in jaData)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                        var orderQty = Convert.ToInt32(dicData[tb_ord_order_detail.CN_ORDER_QTY]);   // 為 detail.order_qty - detail.take_qty
                        var returnQty = Convert.ToInt32(dicData[tb_ord_order_detail.CN_ORDER_QTY + "_copy"]);
                        var isEaqual = orderQty == returnQty;
                        var jaMember = dicData["member"] as JArray;
                        var aMember = jaMember.Select(x => x.ToString()).ToArray();
                        JArray orderUID = dicData[tb_ord_order_detail.CN_ORDER_UID] as JArray;
                        var aOrderUid = orderUID.Select(x => x.ToString()).ToArray();
                        // 用article2product_uid + member_uid + order_uid 查詢order_detail
                        tb_ord_order_detail cOrder = new tb_ord_order_detail();
                        cOrder.SetDirty(tb_ord_order_detail.CN_UID, tb_ord_order_detail.CN_ORDER_UID, tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID, tb_ord_order_detail.CN_ORDER_QTY, tb_ord_order_detail.CN_MEMBER_UID,
                            tb_ord_order_detail.CN_TAKE_QTY, tb_ord_order_detail.CN_PRICE, tb_ord_order_detail.CN_COMMENT_UID, tb_ord_order_detail.CN_COMMENT_TIME, tb_ord_order_detail.CN_SPECIFICATION);
                        List<WhereNode> lwWhereData = new List<WhereNode>();
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), dicData[tb_ord_order_detail.CN_UID]));
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), (int)Enums.OrderStatus.NotTake));
                        if (dicData.ContainsKey(tb_ord_order_detail.CN_SPECIFICATION))
                        {
                            if (dicData[tb_ord_order_detail.CN_SPECIFICATION] != "" && dicData[tb_ord_order_detail.CN_UID] != null)
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_SPECIFICATION, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), dicData[tb_ord_order_detail.CN_SPECIFICATION]));
                            }
                        }
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aOrderUid));
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_MEMBER_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_detail), aMember));
                        Command cSelect = Command.SetupSelectCmd(cOrder, new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwWhereData.ToArray()));
                        ai = ArsenalDBMgr.GetInst(cSelect);
                        List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                        var now = DateTime.Now;
                        foreach (var order in qdsOrder.OrderBy(x => x.comment_time))
                        {
                            if (isEaqual)
                            {
                                // 全部棄單 => 將訂單狀態更改為棄單
                                tb_ord_order_detail orderDetail = new tb_ord_order_detail()
                                {
                                    status = (int)Enums.OrderStatus.Abandon,
                                    abandon_reason = abandonReason,
                                    operate_date = now
                                };
                                tb_ord_order_detail orderDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                lCmdUpdate.Add(Command.SetupUpdateCmd(orderDetail, orderDetailCon));
                            }
                            else
                            {
                                // 判斷數量
                                if (returnQty > 0)
                                {
                                    var orderDetailQty = order.order_qty - order.take_qty;
                                    var updateNum = returnQty >= orderDetailQty ? orderDetailQty : returnQty;

                                    tb_ord_order_detail cDetail = new tb_ord_order_detail();
                                    if (orderDetailQty == updateNum)
                                    {
                                        // 棄單量>=訂購量 => 全部棄
                                        cDetail.status = (int)Enums.OrderStatus.Abandon;
                                        cDetail.abandon_reason = abandonReason;
                                        cDetail.operate_date = now;
                                    }
                                    else
                                    {
                                        // 更新原本訂單
                                        cDetail.order_qty = order.order_qty - updateNum;
                                        cDetail.price = (order.order_qty - updateNum) * order.price / order.order_qty;
                                        // 新增棄單訂單order
                                        string sNewArticleUid = Guid.NewGuid().ToString();
                                        tb_ord_order_detail cNew = new tb_ord_order_detail()
                                        {
                                            uid = sNewArticleUid,
                                            order_uid = order.order_uid,
                                            article2product_uid = order.article2product_uid,
                                            member_uid = order.member_uid,
                                            order_qty = updateNum,
                                            take_qty = 0,
                                            price = updateNum * order.price / order.order_qty,
                                            status = (int)Enums.OrderStatus.Abandon,
                                            comment_uid = order.comment_uid,
                                            comment_time = order.comment_time,
                                            abandon_reason = abandonReason,
                                            operate_date = now,
                                            specification = order.specification
                                        };
                                        lCmdUpdate.Add(Command.SetupInsertCmd(cNew));
                                    }
                                    tb_ord_order_detail cDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                    lCmdUpdate.Add(Command.SetupUpdateCmd(cDetail, cDetailCon));
                                    returnQty -= updateNum;
                                }
                            }
                            // 更新退貨次數
                            sMsg = getUpdateReturnCountCmd(order.member_uid, 1);
                            if (sMsg != null)
                            {
                                break;
                            }
                        }
                    }
                    if (lCmdUpdate.Any())
                    {
                        ai = ArsenalDBMgr.GetInst(lCmdUpdate[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmdUpdate);
                        string sErrorCode = GetLastErrorCode(lCmdUpdate);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);

                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(AbandonOrder)} 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 CheckOutToOrder(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmdInsert = new List<Command>();
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.QRY_MASTER] as JArray;
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);

                    Dictionary<string, object> dicData = jaData[0][BLWording.DATA].ToObject<Dictionary<string, object>>();
                    JObject orderData = dicData["orderData"] as JObject;
                    JObject memberData = dicData["memberData"] as JObject;
                    var session = ProjectHelper.GetLoginUser(i_crmInput);
                    if (session == null)
                    {
                        sMsg = "Session error!!";
                        Logger.Error($"Session error!! i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ");
                    }
                    string receiveBranchUid = session.receive_branch_uid;
                    JArray detailData = dicData[BLWording.DATA] as JArray;
                    if (orderData.ContainsKey(tb_ord_order_master.CN_GROUP_UID))
                    {
                        if (orderData[tb_grp_branch.CN_UID].ToString() != receiveBranchUid)
                        {
                            sMsg = "請勿跨分店購買現貨";
                            break;
                        }
                    }

                    // 獲得該分店之現貨狀況
                    sMsg = GetFinalData(dicCondition, receiveBranchUid, out List<StockModel> finalData, out List<tb_grp_group> qdsGroup);
                    tb_grp_group cGroup = new tb_grp_group();
                    cGroup.SetDirty(tb_grp_group.CN_FB_GROUP_ID);
                    tb_grp_group cCon = new tb_grp_group()
                    {
                        uid = orderData[tb_ord_order_master.CN_GROUP_UID].ToString(),
                    };
                    Command cSelect = Command.SetupSelectCmd(cGroup, cCon);
                    ArsenalInterface ai2 = ArsenalDBMgr.GetInst(cSelect);
                    List<tb_grp_group> qdsGroupFBData = ai2.RunQueryList<tb_grp_group>(cSelect);
                    string memberUid = memberData != null ? memberData[tb_meb_member.CN_UID].ToString() : getAdditionalPurchaseMemberId(qdsGroupFBData[0].fb_group_id);
                    List<string> MasterUid = new List<string>();

                    foreach (JObject row in detailData)
                    {
                        Dictionary<string, object> dicInput = row.ToObject<Dictionary<string, object>>();

                        string article2productUid = dicInput[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString();
                        int takeQty = Convert.ToInt32(dicInput[tb_ord_order_detail.CN_TAKE_QTY]);
                        int price = Convert.ToInt32(dicInput[tb_ord_order_detail.CN_PRICE]);
                        string specification = dicInput.ContainsKey(tb_ord_order_detail.CN_SPECIFICATION) ? dicInput[tb_ord_order_detail.CN_SPECIFICATION].ToString() : null;

                        // 比較該分店之現貨與欲購買現貨的數量
                        var comparedData = finalData.Where(x => x.article2product_uid == article2productUid && x.specification == specification);

                        if (!comparedData.Any())
                        {
                            Logger.Debug("商品uid =" + article2productUid + "  規格:" + specification);
                            sMsg = "分店無此現貨商品";
                            break;
                        }

                        if (takeQty > comparedData.FirstOrDefault().qty)
                        {
                            sMsg = "分店現貨商品的數量數量不足";
                            break;
                        }

                        string sNewOrderMasterUid = Guid.NewGuid().ToString();
                        MasterUid.Add(sNewOrderMasterUid);

                        tb_ord_order_master cMasterNew = new tb_ord_order_master()
                        {
                            uid = sNewOrderMasterUid,
                            type = 3,
                            name = dicInput["article_name"].ToString(),
                            order_code = new GROUP.Helper.FbHelper().GetOrderCode(),
                            group_uid = orderData.ContainsKey(tb_ord_order_master.CN_GROUP_UID) ? orderData[tb_ord_order_master.CN_GROUP_UID].ToString() : orderData[tb_grp_group.CN_UID].ToString(),
                            branch_uid = session.receive_branch_uid,
                            status = (int)OrderMainStatus.Arrived,
                        };
                        lCmdInsert.Add(Command.SetupInsertCmd(cMasterNew));

                        string sNewOrderDetailUid = Guid.NewGuid().ToString();
                        tb_ord_order_detail cNew = new tb_ord_order_detail()
                        {
                            uid = sNewOrderDetailUid,
                            order_uid = sNewOrderMasterUid,
                            article2product_uid = article2productUid,
                            member_uid = memberUid,
                            order_qty = takeQty,
                            take_qty = 0,
                            price = takeQty * price,
                            specification = specification == "" ? null : specification,
                            status = (int)Enums.OrderStatus.NotTake,
                            comment_time = DateTime.Now,
                        };
                        lCmdInsert.Add(Command.SetupInsertCmd(cNew));
                    }

                    if (sMsg != null)
                    {
                        break;
                    }

                    if (lCmdInsert.Any())
                    {
                        ai = ArsenalDBMgr.GetInst(lCmdInsert[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmdInsert);
                        string sErrorCode = GetLastErrorCode(lCmdInsert);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param[BLWording.DATA] = MasterUid;
                    crmRes.param.Add("memberUid", memberUid);

                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(CheckOutToOrder)} 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 QrcodeToPurchase(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmdInsert = new List<Command>();
            List<Command> lCmdsDetail = new List<Command>();
            List<Command> lCmdsUpdate = new List<Command>();
            Command cRes = null;
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    JArray jaData = i_crmInput.param[BLWording.UPD_MASTER] as JArray;
                    Dictionary<string, object> dicData = jaData[0][BLWording.DATA].ToObject<Dictionary<string, object>>();

                    JObject memberData = dicData["memberData"] as JObject;
                    string memberUid = memberData["member_uid"].ToString();
                    var addMainData = new List<tb_ord_purchase>();
                    var session = ProjectHelper.GetLoginUser(i_crmInput);
                    if (session == null)
                    {
                        sMsg = "Session error!!";
                        Logger.Error($"Session error!! i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ");
                    }
                    string receive_branch_uid = session.receive_branch_uid;

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    QueryJsonElement qjeA = lBlocks.GetInst();
                    qjeA.table = tb_ord_order_detail.TABLENAME;
                    qjeA.displaycols = new List<string>()
                    {
                        tb_ord_order_detail.CN_UID,
                        tb_ord_order_detail.CN_ORDER_QTY,
                        tb_ord_order_detail.CN_TAKE_QTY,
                        tb_ord_order_detail.CN_STATUS,
                        tb_ord_order_detail.CN_MEMBER_UID,
                        tb_ord_order_detail.CN_SPECIFICATION,
                        tb_ord_order_detail.CN_COMMENT_TIME,
                        tb_ord_order_detail.CN_MEMO,
                        tb_ord_order_detail.CN_ORDER_UID
                    };
                    List<WhereNode> lswnMain = new List<WhereNode>();
                    lswnMain.Add(new WhereNode(tb_ord_order_detail.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), (int)Enums.OrderStatus.NotTake));
                    if (lswnMain.Any())
                    {
                        qjeA.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lswnMain.ToArray());
                    }
                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeMain.jointable = qjeA;
                    qjeMain.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_master.CN_UID,tb_ord_order_detail.CN_ORDER_UID }};
                    qjeMain.displaycols = new List<string>()
                    {
                        tb_ord_order_master.CN_BRANCH_UID,
                        tb_ord_order_master.CN_ORDER_CODE
                    };
                    qjeMain.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_master.CN_NAME, new List<string>() { "order_name" } }
                    };

                    QueryJsonElement qjeB = lBlocks.GetInst();
                    qjeB.table = tb_meb_member.TABLENAME;
                    qjeB.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeB.jointable = qjeA;
                    qjeB.joincols = new Dictionary<string, string>() {
                             {  tb_meb_member.CN_UID,tb_ord_order_detail.CN_MEMBER_UID }};
                    qjeB.displaycols = new List<string>()
                    {
                        tb_meb_member.CN_WPRICE_PAYMENT,
                    };
                    qjeB.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_meb_member.CN_NAME, new List<string>() { "member_name" } },
                    };
                    qjeB.wherecols = new WhereNode(tb_meb_member.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_meb_member), memberUid);


                    QueryJsonElement qjeC = lBlocks.GetInst();
                    qjeC.table = tb_grp_branch.TABLENAME;
                    qjeC.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeC.jointable = qjeMain;
                    qjeC.joincols = new Dictionary<string, string>() {
                             {  tb_grp_branch.CN_UID,tb_ord_order_master.CN_BRANCH_UID }};
                    qjeC.displaycols = new List<string>() { tb_grp_branch.CN_BRANCH_NAME };
                    qjeC.wherecols = new WhereNode(tb_grp_branch.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_branch), receive_branch_uid);

                    QueryJsonElement qjeD = lBlocks.GetInst();
                    qjeD.table = tb_prd_article2product.TABLENAME;
                    qjeD.jointype = QueryJsonElement.JOIN;
                    qjeD.jointable = qjeA;
                    qjeD.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeD.displaycols = new List<string>() {
                        tb_prd_article2product.CN_NAME,
                        tb_prd_article2product.CN_PRICE,
                        tb_prd_article2product.CN_WHOLESALE_PRICE,
                        tb_prd_article2product.CN_SEQ
                    };
                    qjeD.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_prd_article2product.CN_UID, new List<string>() { "article2product_uid" } },
                    };

                    QueryJsonElement qjeE = lBlocks.GetInst();
                    qjeE.table = tb_grp_article.TABLENAME;
                    qjeE.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeE.jointable = qjeD;
                    qjeE.joincols = new Dictionary<string, string>() {
                             {  tb_grp_article.CN_UID,tb_prd_article2product.CN_ARTICLE_UID }};
                    qjeE.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_article.CN_NAME, new List<string>() { "article_name" } },
                    };
                    qjeE.displaycols = new List<string>() { tb_grp_article.CN_DUTYFREE, };

                    lBlocks.Add(qjeA);
                    lBlocks.Add(qjeMain);
                    lBlocks.Add(qjeB);
                    lBlocks.Add(qjeC);
                    lBlocks.Add(qjeD);
                    lBlocks.Add(qjeE);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);
                    if (sMsg != null)
                    {
                        break;
                    }
                    ai = ArsenalDBMgr.GetInst(cRes);
                    var qds = ai.RunQueryList<OrderDetail>(cRes);
                    var pro = qds.GroupBy(x => new { x.uid, x.order_uid, x.branch_uid, x.branch_name, x.status, x.specification })
                            .Select(x => new OrderDetail
                            {
                                order_qty = x.Sum(c => c.order_qty),
                                take_qty = x.Sum(c => c.take_qty),
                                price = x.Select(x => x.price).FirstOrDefault(),
                                wholesale_price = x.Select(x => x.wholesale_price).FirstOrDefault(),
                                specification = x.Key.specification,
                                uid = x.Key.uid,
                                order_uid = x.Key.order_uid,
                                seq = x.Select(x => x.seq).FirstOrDefault(),
                                name = x.Select(x => x.name).FirstOrDefault(),
                                member_uid = x.Select(x => x.member_uid).FirstOrDefault(),
                                member_name = x.Select(x => x.member_name).FirstOrDefault(),
                                status = x.Key.status,
                                article_name = x.Select(x => x.article_name).FirstOrDefault(),
                                order_name = x.Select(x => x.order_name).FirstOrDefault(),
                                memo = x.Select(x => x.memo).FirstOrDefault(),
                                branch_uid = x.Key.branch_uid,
                                branch_name = x.Key.branch_name,
                                comment_time = x.Select(x => x.comment_time).FirstOrDefault(),
                                wprice_payment = x.Select(x => x.wprice_payment).FirstOrDefault(),
                                article2product_uid = x.FirstOrDefault().article2product_uid,
                                dutyfree = x.FirstOrDefault()?.dutyfree
                            });

                    var memberUID = memberUid;
                    var purchaseUID = addMainData.Any() ? addMainData.Select(x => x.uid).SingleOrDefault() : Guid.NewGuid().ToString();
                    var amount = 0;
                    foreach (var detail in pro)
                    {
                        // 新增收款明細
                        var price = detail.wprice_payment == 0 ? detail.price : detail.wholesale_price;
                        tb_ord_purchase_detail cDeatil = new tb_ord_purchase_detail()
                        {
                            purchase_uid = purchaseUID,
                            branch_uid = receive_branch_uid,
                            order_detail_uid = detail.uid,
                            article2product_uid = detail.article2product_uid,
                            qty = detail.order_qty - detail.take_qty,
                            amount = (detail.order_qty - detail.take_qty) * price,
                            specification = detail.specification
                        };
                        lCmdsDetail.Add(Command.SetupInsertCmd(cDeatil));
                        amount = cDeatil.amount;

                        lCmdsUpdate.Add(Command.SetupUpdateCmd(new tb_ord_order_detail()
                        {
                            status = (int)Enums.OrderStatus.Taked,
                            take_qty = cDeatil.qty + detail.take_qty,
                            operate_date = DateTime.Now
                        }
                        , new tb_ord_order_detail() { uid = detail.uid }));

                        if (addMainData.Any(x => x.member_uid == memberUID))
                        {
                            var model = addMainData.Where(x => x.member_uid == memberUID).SingleOrDefault();
                            model.amount += amount;
                        }
                        else
                        {
                            tb_ord_purchase cNew = new tb_ord_purchase()
                            {
                                uid = purchaseUID,
                                member_uid = memberUID,
                                amount = amount,
                                receive_branch_uid = receive_branch_uid
                            };
                            addMainData.Add(cNew);
                        }

                    }

                    foreach (var mainData in addMainData)
                    {
                        lCmdInsert.Add(Command.SetupInsertCmd(mainData));
                        tb_meb_shopping_points_record cPointsNew = new tb_meb_shopping_points_record()
                        {
                            uid = Guid.NewGuid().ToString(),
                            member_uid = mainData.member_uid,
                            amount = (int)(mainData.amount / 100),
                            purchase_uid = mainData.uid
                        };
                        lCmdInsert.Add(Command.SetupInsertCmd(cPointsNew));
                    }
                    lCmdInsert.AddRange(lCmdsDetail);
                    lCmdInsert.AddRange(lCmdsUpdate);

                    if (lCmdInsert.Any())
                    {
                        ArsenalInterface ai2 = ArsenalDBMgr.GetInst(lCmdInsert[0], GetDefaultSystemColumnInfo());
                        ai2.RunEditCmds(lCmdInsert);
                        string sErrorCode = GetLastErrorCode(lCmdInsert);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }
                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param.Add(BLWording.DATA, pro);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(CheckOutToOrder)} 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 ReturnArchiveOrder(CRequestMessage i_crmInput)
        {
            string sMsg = null;
            List<Command> lCmdUpdate = new List<Command>();
            CResponseMessage crmRes = null;
            ArsenalInterface ai = null;
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.UPD_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession);
                    JArray dataArray = jaDataArray[0]["data"] as JArray;
                    foreach (JToken joData in dataArray)
                    {
                        Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
                        string orderUID = dicData[tb_ord_order_master.CN_UID].ToString();
                        // 用article2product_uid + member_uid + order_uid 查詢order_detail
                        tb_ord_order_detail cOrder = new tb_ord_order_detail();
                        cOrder.SetFullDirty();
                        List<WhereNode> lwWhereData = new List<WhereNode>();
                        lwWhereData.Add(new WhereNode(tb_ord_order_detail.CN_ORDER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_detail), orderUID));
                        Command cSelect = Command.SetupSelectCmd(cOrder, new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwWhereData.ToArray()));
                        ai = ArsenalDBMgr.GetInst(cSelect);
                        List<tb_ord_order_detail> qdsOrder = ai.RunQueryList<tb_ord_order_detail>(cSelect);

                        bool isArrived = false;

                        foreach (var order in qdsOrder)
                        {
                            int stock = GetOrderProductStock(order.order_uid, order.article2product_uid, order.specification);
                            if (stock > 0)
                            {
                                isArrived = true;
                            }
                            // 將原本轉退貨的訂單轉回已到貨
                            if (order.status == (int)Enums.OrderStatus.Return && order.take_qty == 0)
                            {
                                tb_ord_order_detail upOrderDetail = new tb_ord_order_detail()
                                {
                                    status = (int)Enums.OrderStatus.NotTake,
                                    operate_date = null
                                };
                                tb_ord_order_detail upOrderDetailCon = new tb_ord_order_detail() { uid = order.uid };
                                lCmdUpdate.Add(Command.SetupUpdateCmd(upOrderDetail, upOrderDetailCon));
                                sMsg = getUpdateReturnCountCmd(order.member_uid, -1);
                                if (sMsg != null)
                                {
                                    break;
                                }
                            }
                        }

                        tb_ord_order_master upData = new tb_ord_order_master() { status = isArrived ? (int)Enums.OrderMainStatus.Arrived : (int)Enums.OrderMainStatus.NotArrived };
                        tb_ord_order_master upCon = new tb_ord_order_master() { uid = orderUID };
                        lCmdUpdate.Add(Command.SetupUpdateCmd(upData, upCon));
                    }

                    if (lCmdUpdate.Any())
                    {
                        ai = ArsenalDBMgr.GetInst(lCmdUpdate[0], GetDefaultSystemColumnInfo());
                        ai.RunEditCmds(lCmdUpdate);
                        string sErrorCode = GetLastErrorCode(lCmdUpdate);

                        if (sErrorCode != null)
                        {
                            sMsg = sErrorCode;
                            break;
                        }
                    }

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(ReturnArchiveOrder)} 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 static string getAdditionalPurchaseMemberId(string groupId)
        {
            tb_meb_member cMember = new tb_meb_member();
            cMember.SetDirty(tb_meb_member.CN_UID);
            tb_meb_member cCon = new tb_meb_member()
            {
                name = "現貨銷售",
                group_id = groupId
            };
            Command cSelect = Command.SetupSelectCmd(cMember, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
            List<tb_meb_member> qdsMember = ai.RunQueryList<tb_meb_member>(cSelect);
            return qdsMember[0].uid;
        }
        public string getUpdateReturnCountCmd(string memberUid, int count)
        {
            string sMsg = null;

            tb_meb_member cMember = new tb_meb_member();
            cMember.SetDirty(tb_meb_member.CN_RETURN_COUNT);
            tb_meb_member cCon = new tb_meb_member()
            {
                uid = memberUid
            };
            Command cSelect = Command.SetupSelectCmd(cMember, cCon);
            ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
            List<tb_meb_member> qdsMember = ai.RunQueryList<tb_meb_member>(cSelect);
            if (qdsMember.Count < 1)
            {
                return null;
            }

            Command cCmd = Command.SetupUpdateCmd(new tb_meb_member()
            {
                return_count = qdsMember[0].return_count + count
            }
            , new tb_meb_member() { uid = memberUid });

            ai = ArsenalDBMgr.GetInst(cCmd, GetDefaultSystemColumnInfo());
            ai.RunEditSingleCmd(cCmd);
            string sErrorCode = GetLastErrorCode(cCmd);

            if (sErrorCode != null)
            {
                sMsg = sErrorCode;
            }
            return sMsg;
        }
        public CResponseMessage GetOrders(CRequestMessage i_crmInput)
        {
            string sMsg;
            Command cRes = null;

            CResponseMessage crmRes = null;
            try
            {
                do
                {
                    sMsg = getCommonParameter(i_crmInput, BLWording.QRY_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession, false);

                    if (sMsg != null)
                    {
                        break;
                    }

                    Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);   // 取得condition
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);
                    var userBranch = ProjectHelper.GetUserBranch(i_crmInput);
                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
                    bool isGroup = false;
                    QueryJsonElement qjeMain = lBlocks.GetInst();
                    qjeMain.table = tb_ord_order_master.TABLENAME;
                    qjeMain.displaycols = new List<string>() {
                        tb_ord_order_master.CN_UID,
                        tb_ord_order_master.CN_TYPE,
                        tb_ord_order_master.CN_ORDER_CODE,
                        tb_ord_order_master.CN_STATUS,
                        tb_ord_order_master.CN_CREATE_DATE,
                        tb_ord_order_master.CN_GROUP_UID,
                        tb_ord_order_master.CN_BRANCH_UID,
                        tb_ord_order_master.CN_ARRIVED_DATE,
                        tb_ord_order_master.CN_ARTICLE_UID,
                        tb_ord_order_master.CN_NAME
                    };
                    List<WhereNode> lswnMain = new List<WhereNode>();
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_TYPE, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.ArticleType.Purchase));
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), BLWording.STATUS_FLAG_ON));
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_start"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_start"]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_end"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_order_master), Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_end"]).AddDays(1)));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_STATUS))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_STATUS]));
                    }
                    else
                    {
                        // 扣除封存訂單
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_STATUS, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_ord_order_master), (int)Enums.OrderMainStatus.Archive));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ORDER_CODE))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ORDER_CODE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_ORDER_CODE]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_start"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ARRIVED_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_start"]));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_end"))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_ARRIVED_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_order_master), Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_end"]).AddDays(1)));
                    }
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_UID]));
                    }
                    List<WhereNode> lswnGroup = new List<WhereNode>();
                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_GROUP_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_GROUP_UID]));
                    }
                    else if (dicCondition.ContainsKey("GroupOrBranch_uid")) // 處理shopping list 搜尋選項 uid 有可能是branch or group
                    {
                        tb_grp_group cGroup = new tb_grp_group();
                        cGroup.SetFullDirty();
                        WhereNode wnGroup = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), dicCondition["GroupOrBranch_uid"].ToString());
                        Command gSelect = Command.SetupSelectCmd(cGroup, wnGroup);
                        ArsenalInterface aiGroup = ArsenalDBMgr.GetInst(gSelect);
                        var qdsGroup = aiGroup.RunQueryList<tb_grp_group>(gSelect);
                        if (qdsGroup.Any())
                        {
                            lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition["GroupOrBranch_uid"].ToString()));
                            isGroup = true;
                        }
                    }
                    lswnMain.Add(new WhereNode(tb_ord_order_master.CN_GROUP_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), lsBranch.ToArray()));

                    if (dicCondition.ContainsKey(tb_ord_order_master.CN_BRANCH_UID))
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString()));
                    }
                    else if (dicCondition.ContainsKey("GroupOrBranch_uid") && !isGroup)
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_order_master), dicCondition["GroupOrBranch_uid"].ToString()));
                    }
                    else
                    {
                        lswnMain.Add(new WhereNode(tb_ord_order_master.CN_BRANCH_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_order_master), userBranch.ToArray()));
                    }

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

                    QueryJsonElement qjeOrder = lBlocks.GetInst();
                    qjeOrder.table = tb_ord_order_detail.TABLENAME;
                    qjeOrder.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeOrder.jointable = qjeMain;
                    qjeOrder.joincols = new Dictionary<string, string>() {
                             {  tb_ord_order_detail.CN_ORDER_UID,tb_ord_order_master.CN_UID }};
                    qjeOrder.displaycols = new List<string>() {
                        tb_ord_order_detail.CN_ORDER_QTY,
                        tb_ord_order_detail.CN_TAKE_QTY,
                    };
                    qjeOrder.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_order_detail.CN_STATUS, new List<string>() { "detail_status" } },
                         //{ QueryJsonElement.SUM(tb_ord_order_detail.CN_ORDER_QTY), new List<string>() { "count" } },
                         //{ QueryJsonElement.SUM(tb_ord_order_detail.CN_TAKE_QTY), new List<string>() { "soldCount" } },
                    };

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

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

                    QueryJsonElement qjeArticleProduct = lBlocks.GetInst();
                    qjeArticleProduct.table = tb_prd_article2product.TABLENAME;
                    qjeArticleProduct.jointype = QueryJsonElement.LEFT_JOIN;
                    qjeArticleProduct.jointable = qjeOrder;
                    qjeArticleProduct.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeArticleProduct.displaycols = new List<string>() {
                        tb_prd_article2product.CN_PRICE
                    };
                    if (dicCondition.ContainsKey(tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID))
                    {
                        qjeArticleProduct.wherecols = new WhereNode(tb_prd_article2product.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_prd_article2product), dicCondition[tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID].ToString());
                    }



                    //qjeArticleProduct.groupcols = new List<Tuple<QueryJsonElement, string>>
                    //{
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ARTICLE_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_TYPE),
                    //   Tuple.Create(qjeMain,tb_ord_order_master.CN_STATUS),
                    //   Tuple.Create(qjeMain,  tb_ord_order_master.CN_CREATE_DATE),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ORDER_CODE),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_BRANCH_UID),
                    //   Tuple.Create(qjeMain, tb_ord_order_master.CN_ARRIVED_DATE),
                    //   Tuple.Create(qjeMain, tb_grp_branch.CN_GROUP_UID),
                    //   Tuple.Create(qjeMain, tb_grp_article.CN_NAME),
                    //   Tuple.Create(qjeBranch, tb_grp_branch.CN_BRANCH_NAME),
                    //   Tuple.Create(qjeArticleProduct, tb_prd_article2product.CN_PRICE)
                    //};

                    lBlocks.Add(qjeMain);
                    lBlocks.Add(qjeOrder);
                    lBlocks.Add(qjeGroup);
                    lBlocks.Add(qjeBranch);
                    lBlocks.Add(qjeArticleProduct);

                    sMsg = MakeSelectJoinByBlocks(lBlocks, out cRes);

                    if (sMsg != null)
                    {
                        break;
                    }
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
                    var qds = ai.RunQueryList<OrderDetailEx>(cRes);
                    var groupData = qds.GroupBy(x => x.uid).ToList();
                    var res = new List<OrderMaster>();
                    foreach (var data in groupData)
                    {
                        var model = new OrderMaster()
                        {
                            uid = data.Key,
                            article_uid = data.Select(x => x.article_uid).FirstOrDefault(),
                            order_code = data.Select(x => x.order_code).FirstOrDefault(),
                            group_uid = data.Select(x => x.group_uid).FirstOrDefault(),
                            branch_uid = data.Select(x => x.branch_uid).FirstOrDefault(),
                            branch_name = data.Select(x => x.branch_name).FirstOrDefault(),
                            name = data.Select(x => x.type).FirstOrDefault() == (int)Enums.ArticleType.Stock ? string.Format("【現貨】{0}", data.Select(x => x.name).FirstOrDefault()) : data.Select(x => x.name).FirstOrDefault(),
                            create_date = data.Select(x => x.create_date).FirstOrDefault(),
                            arrived_date = data.Select(x => x.arrived_date).FirstOrDefault(),
                            status = data.Select(x => x.status).FirstOrDefault(),
                            type = data.Select(x => x.type).FirstOrDefault()
                        };
                        res.Add(model);
                    }
                    var orderData = res.OrderByDescending(x => x.create_date).ToList();

                    crmRes = new CSuccessResponseMessage(null, i_crmInput);
                    crmRes.param[BLWording.DATA] = orderData;
                }
                while (false);
            }
            catch (Exception ex)
            {
                LogHelper.DBLog(Util.GetLastExceptionMsg(ex));
                sMsg = $"{nameof(GetProduct)} 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>
        /// 取得所有分店跟分店所屬社團的資訊
        /// </summary>
        /// <returns></returns>
        private List<GroupBranch> GetBranchGroupList()
        {
            QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
            string sMsg = "";

            QueryJsonElement branch = lBlocks.GetInst();
            branch.table = tb_grp_branch.TABLENAME;
            branch.displaycols = new List<string>() { 
                tb_grp_branch.CN_BRANCH_NAME ,
                tb_grp_branch.CN_UID,
                tb_grp_branch.CN_ADDRESS,
                tb_grp_branch.CN_PHONE_NUMBER,
                tb_grp_branch.CN_CONTACT_PERSON,
                tb_grp_branch.CN_SEQ
            };
            
            QueryJsonElement group = lBlocks.GetInst();
            group.table = tb_grp_group.TABLENAME;
            group.displaycols = new List<string>() { tb_grp_group.CN_NAME};
            group.jointype = QueryJsonElement.JOIN;
            group.jointable = branch;
            group.joincols = new Dictionary<string, string>() {{ tb_grp_group.CN_UID,tb_grp_branch.CN_GROUP_UID }};

            lBlocks.Add(branch);
            lBlocks.Add(group);

            sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cRes);
            if (sMsg != null)
            {
                throw new Exception(sMsg);
            }

            ArsenalInterface ai = ArsenalDBMgr.GetInst(cRes);
            return ai.RunQueryList<GroupBranch>(cRes);
        }

        private (List<OrderMaster> records, int TotalCount) readCommandPostDataHandlerRefactor(CRequestMessage i_crmInput, Dictionary<string, string > dicCondition, List<string> lsBranch, List<string> userBranch, int nPageIdx, int nPageNum)
        {
            var records = new List<OrderMaster>();
            var branchName = ProjectHelper.GetUserBranchAndName(i_crmInput);

            // 查詢前10筆資料
            var error = new SQLLib(new MSSQLDirectSQLHelper()).readCommandPostDataHandlerQueryDetailSortGuid(i_crmInput, dicCondition, lsBranch, userBranch, false, nPageIdx, nPageNum, out Command command_);           
            ArsenalInterface ai = ArsenalDBMgr.GetInst(command_);
            var qdsOrderMst = ai.RunQueryList<OrderDetailEx>(command_);

            if (!string.IsNullOrWhiteSpace(command_.LastErrorCode))
            {
                throw new Exception(command_.LastErrorCode);
            }

            // 查詢total數量
            error = new SQLLib(new MSSQLDirectSQLHelper()).readCommandPostDataHandlerQueryDetailSortGuid(i_crmInput, dicCondition, lsBranch, userBranch, true, nPageIdx, nPageNum, out Command commandTotal_);
            ai = ArsenalDBMgr.GetInst(commandTotal_);
            var qdsOrderMstTotal = ai.RunQueryDataSet(commandTotal_, i_nPageIdx: nPageIdx, i_nNumOfPage: nPageNum);

            if (!string.IsNullOrWhiteSpace(command_.LastErrorCode))
            {
                throw new Exception(command_.LastErrorCode);
            }

            Int32.TryParse(qdsOrderMstTotal.DATA.Tables[0].Rows[0]?[0]?.ToString(), out int TotalCount);
            int sn = nPageIdx * nPageNum + 1;// 0 => 1         
            int received = 0;
            int receivable = 0;
            foreach (var detail in qdsOrderMst)
            {
                received =  detail.received;
                receivable = detail.receivable;
                records.Add(new OrderMaster()
                {
                    uid = detail.uid,
                    article_uid = detail.article_uid,
                    order_code = detail.order_code,
                    group_uid = detail.group_uid,
                    branch_uid = detail.branch_uid,
                    create_date = detail.create_date,
                    arrived_date = detail.arrived_date,
                    status = detail.status,
                    type = detail.type,
                    name = detail.name,
                    branch_name = branchName.Find(x => x.Uid == detail.branch_uid).BranchName,
                    received = received,
                    receivable = receivable,
                    progress = received == 0 || receivable == 0 ? "0%" : Math.Round((decimal)received / receivable * 100, 0, MidpointRounding.AwayFromZero).ToString() + "%",
                    remark = detail.remark,
                    sn = sn++,
                }); 
            }

            return (records, TotalCount);
        }
    }
}