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

using CounsellorBL.BLStructure;
using CounsellorBL.Helper;
using MonumentDefine;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HPSF;
using OT.COM.ArsenalDB;
using OT.COM.SignalerMessage;
using SoldierData.EnterprizeV4;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Util = OT.COM.LogisticsUtil.Util;

namespace CounsellorBL.ORD
{
    public class GroupReceiveReportService : SingleDataTableTemplate<tb_grp_group>
    {

        public GroupReceiveReportService()
        {
            dgReadCommandGenerator = readCommandGenerator;
            dgReadCommandPostDataHandler = readCommandPostDataHandler;
        }

        public new CResponseMessage Read(CRequestMessage i_crmInput) => base.Read(i_crmInput);
        protected string readCommandGenerator(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out Command o_c,
            [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
            [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
            [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg;
            Command cRes = null;
            try
            {
                do
                {
                    var lsBranch = ProjectHelper.GetUserGroup(i_crmInput);
                    var authBranch = ProjectHelper.GetUserBranch(i_crmInput);
                    Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput);   // 取得condition

                    QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();

                    QueryJsonElement qjePurchase = lBlocks.GetInst();
                    qjePurchase.table = tb_ord_purchase.TABLENAME;
                    qjePurchase.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_purchase.CN_AMOUNT, new List<string>() { tb_ord_purchase.CN_AMOUNT } },
                         {"' '", new List<string>() { tb_ord_purchase.CN_UID } },
                    };

                    List<WhereNode> lwWhereData = new List<WhereNode>();
                    if (dicCondition.ContainsKey("create_date_start"))
                    {
                        if (DateTime.Today.Day > 5)
                        {
                            var thisMonth = DateTime.Today.AddDays(-(DateTime.Today.Day - 1));
                            if (Convert.ToDateTime(dicCondition["create_date_start"]) >= thisMonth)
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), dicCondition["create_date_start"]));
                            }
                            else
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), thisMonth.ToString("yyyy-MM-dd")));
                            }
                        }
                        else
                        {
                            var lastMonth = DateTime.Today.AddMonths(-1).AddDays(-(DateTime.Today.Day - 1));
                            if (Convert.ToDateTime(dicCondition["create_date_start"]) >= lastMonth)
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), dicCondition["create_date_start"]));
                            }
                            else
                            {
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), lastMonth.ToString("yyyy-MM-dd")));
                            }
                        }
                    }
                    if (dicCondition.ContainsKey("create_date_end"))
                    {
                        if (!dicCondition.ContainsKey("create_date_start"))
                        {
                            if (DateTime.Today.Day > 5)
                            {
                                var thisMonth = DateTime.Today.AddDays(-(DateTime.Today.Day - 1));
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), thisMonth.ToString("yyyy-MM-dd")));
                            }
                            else
                            {
                                var lastMonth = DateTime.Today.AddMonths(-1).AddDays(-(DateTime.Today.Day - 1));
                                lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), lastMonth.ToString("yyyy-MM-dd")));
                            }                            
                        }

                        lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_purchase), Convert.ToDateTime(dicCondition["create_date_end"]).AddDays(1).ToString("yyyy-MM-dd")));
                    }
                    if (!dicCondition.Any())
                    {
                        var today = DateTime.Today;
                        lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_GTEQ, typeof(tb_ord_purchase), today));
                        lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_CREATE_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_ord_purchase), today.AddDays(1).ToString("yyyy-MM-dd")));
                    }
                    lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_ord_purchase), (int)Enums.Flag.Enable));
                    lwWhereData.Add(new WhereNode(tb_ord_purchase.CN_RECEIVE_BRANCH_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_ord_purchase), authBranch.ToArray()));
                    if (lwWhereData.Any())
                    {
                        qjePurchase.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwWhereData.ToArray());
                    }

                    QueryJsonElement qjeBranch = lBlocks.GetInst();
                    qjeBranch.table = tb_grp_branch.TABLENAME;
                    qjeBranch.jointype = QueryJsonElement.JOIN;
                    qjeBranch.jointable = qjePurchase;
                    qjeBranch.joincols = new Dictionary<string, string>() {
                             {  tb_grp_branch.CN_UID,tb_ord_purchase.CN_RECEIVE_BRANCH_UID }};
                    qjeBranch.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_branch.CN_UID, new List<string>() { tb_ord_purchase_detail.CN_BRANCH_UID } }
                    };

                    QueryJsonElement qjeGroup = lBlocks.GetInst();
                    qjeGroup.table = tb_grp_group.TABLENAME;
                    qjeGroup.jointype = QueryJsonElement.JOIN;
                    qjeGroup.jointable = qjeBranch;
                    qjeGroup.joincols = new Dictionary<string, string>() {
                             {  tb_grp_group.CN_UID,tb_grp_branch.CN_GROUP_UID }};
                    if (dicCondition.ContainsKey(tb_grp_article.CN_GROUP_UID))
                    {
                        qjeGroup.wherecols = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), dicCondition[tb_grp_article.CN_GROUP_UID]);
                    }
                    else
                    {
                        qjeGroup.wherecols = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_IN, typeof(tb_grp_group), lsBranch.ToArray());
                    }
                    qjeGroup.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_grp_group.CN_UID, new List<string>() { tb_grp_branch.CN_GROUP_UID } }
                    };

                    QueryJsonElement qjeEmp = lBlocks.GetInst();
                    qjeEmp.table = tb_hr_employee.TABLENAME;
                    qjeEmp.jointype = QueryJsonElement.JOIN;
                    qjeEmp.jointable = qjePurchase;
                    qjeEmp.joincols = new Dictionary<string, string>() {
                             {  tb_hr_employee.CN_UID,tb_ord_purchase.CN_CREATE_USER_UID }};
                    qjeEmp.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_hr_employee.CN_NAME, new List<string>() { "payee" } }
                    };

                    QueryJsonElement qjePurchaseDetail = lBlocks.GetInst();
                    qjePurchaseDetail.table = tb_ord_purchase_detail.TABLENAME;
                    qjePurchaseDetail.jointype = QueryJsonElement.JOIN;
                    qjePurchaseDetail.jointable = qjePurchase;
                    qjePurchaseDetail.joincols = new Dictionary<string, string>() {
                             {  tb_ord_purchase_detail.CN_PURCHASE_UID,tb_ord_purchase.CN_UID }};
                    qjePurchaseDetail.aliascols = new Dictionary<string, List<string>>
                    {
                         { tb_ord_purchase_detail.CN_PURCHASE_UID, new List<string>() { "purchase_uid" } },
                         { tb_ord_purchase_detail.CN_QTY, new List<string>() { tb_ord_purchase_detail.CN_QTY } },
                    };

                    QueryJsonElement qjeArticle2Product = lBlocks.GetInst();
                    qjeArticle2Product.table = tb_prd_article2product.TABLENAME;
                    qjeArticle2Product.jointype = QueryJsonElement.JOIN;
                    qjeArticle2Product.jointable = qjePurchaseDetail;
                    qjeArticle2Product.joincols = new Dictionary<string, string>() {
                             {  tb_prd_article2product.CN_UID,tb_ord_purchase_detail.CN_ARTICLE2PRODUCT_UID }};
                    qjeArticle2Product.displaycols = new List<string>()
                    {
                        tb_prd_article2product.CN_COST_PRICE,
                        tb_prd_article2product.CN_PRD_UID,
                    };



                    lBlocks.Add(qjePurchase);
                    lBlocks.Add(qjeBranch);
                    lBlocks.Add(qjeGroup);
                    lBlocks.Add(qjeEmp);
                    lBlocks.Add(qjePurchaseDetail);
                    lBlocks.Add(qjeArticle2Product);

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

            o_c = cRes;
            return sMsg;
        }
        protected string readCommandPostDataHandler(CRequestMessage i_crmInput, ArsenalInterface i_aiArsenal, Command i_cCmd, JArray i_jaData, tb_sys_session i_sSessionUser, out object o_oReault,
           [System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
           [System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
           [System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "")
        {
            string sMsg = null;
            object oResultData = null;
            try
            {
                do
                {
                    //QueryDataSet qds = i_aiArsenal.RunQueryDataSet(i_cCmd);
                    ArsenalInterface ai = ArsenalDBMgr.GetInst(i_cCmd);
                    List<ReportData> lRes = ai.RunQueryList<ReportData>(i_cCmd);

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

                    //找出detail中的成本
                    var cost_result = lRes.GroupBy(x => new
                    {
                        x.prd_uid,
                        x.payee,
                        x.group_uid,
                        x.branch_uid,
                        x.purchase_uid,
                        x.amount,
                        x.cost_price,
                        x.qty,
                    })
                    .Select(x => new ReportData
                    {
                        payee = x.Key.payee,
                        group_uid = x.Key.group_uid,
                        branch_uid = x.Key.branch_uid,
                        prd_uid = x.Key.prd_uid,
                        amount = x.Key.amount,
                        purchase_uid =x.Key.purchase_uid,
                        cost_price = x.Key.cost_price,
                        qty = x.Key.qty,
                        calCost = x.Sum(x => x.cost_price * x.qty),
                    });

                    // 透過group by 加總成本
                    var report = cost_result.GroupBy(x => new
                    {
                        x.payee,
                        x.group_uid,
                        x.branch_uid,
                        x.amount,
                        x.purchase_uid
                    }).Select(x => new ReportData
                    {
                        payee = x.Key.payee,
                        group_uid = x.Key.group_uid,
                        branch_uid = x.Key.branch_uid,
                        amount = x.Key.amount,
                        calCost = x.Sum(x => x.calCost),
                    }).GroupBy(x => new //再group by一次依照收款人分出資料
                    {
                        x.payee,
                        x.group_uid,
                        x.branch_uid,
                    }).Select(x => new ReportData
                    {
                        payee = x.Key.payee,
                        group_uid = x.Key.group_uid,
                        branch_uid = x.Key.branch_uid,
                        amount = x.Sum(c=>c.amount),
                        calCost = x.Sum(x => x.calCost),
                    });
                    // 最後用SELECT 算出獲利傳回前端
                    var data = report.Select(x => new ReportData
                    {
                        payee = x.payee,
                        group_uid = x.group_uid,
                        branch_uid = x.branch_uid,
                        amount = x.amount,
                        profit = x.amount - x.calCost,
                        uid = Guid.NewGuid().ToString()
                    }).OrderBy(x => x.group_uid).ThenBy(x => x.branch_uid) ;

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

            o_oReault = oResultData;
            return sMsg;
        }

        private class ReportData
        {
            public int amount { get; set; }
            public string uid { get; set; }
            public string branch_uid { get; set; }
            public string group_uid { get; set; }
            public string prd_uid { get; set; }
            public string payee { get; set; }
            public string purchase_uid { get; set; }
            public int qty { get; set; }
            public int calCost { get; set; }
            public int cost_price { get; set; }
            public int profit { get; set; }
        }
    }
}