You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
309 lines
15 KiB
309 lines
15 KiB
using CounsellorBL.BLStructure;
|
|
using CounsellorBL.Helper;
|
|
using MonumentDefine;
|
|
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
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 BranchBusinessReportService : SingleDataTableTemplate<tb_grp_branch>
|
|
{
|
|
|
|
public BranchBusinessReportService()
|
|
{
|
|
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);
|
|
var session = ProjectHelper.GetLoginUser(i_crmInput);
|
|
Logger.Info(session);
|
|
if (session == null)
|
|
{
|
|
sMsg = "Session error!!";
|
|
Logger.Error($"Session error!! i_crmInput={JsonConvert.SerializeObject(i_crmInput)}. ");
|
|
}
|
|
Dictionary<string, object> dicCondition = GetQueryMasterFirstWhereData(i_crmInput); // 取得condition
|
|
|
|
QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
|
|
|
|
QueryJsonElement qjeBranch = lBlocks.GetInst();
|
|
qjeBranch.table = tb_grp_branch.TABLENAME;
|
|
qjeBranch.aliascols = new Dictionary<string, List<string>>
|
|
{
|
|
{ tb_grp_branch.CN_UID, new List<string>() { tb_ord_purchase_detail.CN_BRANCH_UID } }
|
|
};
|
|
qjeBranch.displaycols = new List<string>() { tb_grp_branch.CN_UID };
|
|
|
|
QueryJsonElement qjePurchase = lBlocks.GetInst();
|
|
qjePurchase.table = tb_ord_purchase.TABLENAME;
|
|
qjePurchase.jointype = QueryJsonElement.JOIN;
|
|
qjePurchase.jointable = qjeBranch;
|
|
qjePurchase.joincols = new Dictionary<string, string>() {
|
|
{ tb_ord_purchase.CN_RECEIVE_BRANCH_UID,tb_grp_branch.CN_UID }};
|
|
qjePurchase.displaycols = new List<string>()
|
|
{
|
|
tb_ord_purchase.CN_AMOUNT
|
|
};
|
|
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 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 }};
|
|
|
|
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 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(qjeBranch);
|
|
lBlocks.Add(qjePurchase);
|
|
lBlocks.Add(qjeGroup);
|
|
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; }
|
|
}
|
|
}
|
|
}
|