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.
228 lines
12 KiB
228 lines
12 KiB
using MonumentDefine;
|
|
using Newtonsoft.Json.Linq;
|
|
using OT.COM.ArsenalDB;
|
|
using OT.COM.ArsenalDB.SQL;
|
|
using OT.COM.SignalerMessage;
|
|
using SoldierData.EnterprizeV4;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
|
|
namespace CounsellorBL.ORD.ConstDefinition
|
|
{
|
|
internal class SQLLib : DirectSQLLibBase
|
|
{
|
|
public SQLLib(ADirectSQLHelper i_sqlLibBase) : base(i_sqlLibBase) { }
|
|
|
|
public virtual string GetMessageLogRecordSuccessCount(string message_log_uid_, string record_status_flag_, out Command command_)
|
|
{
|
|
ADirectCmdParameter cp = GetParamter();
|
|
|
|
string sql = $@"SELECT COUNT({tb_ord_message_log_record.CN_STATUS_FLAG}) {tb_ord_message_log_record.CN_STATUS_FLAG} FROM {nameof(tb_ord_message_log_record)}
|
|
WHERE {tb_ord_message_log_record.CN_LOG_UID} = {cp.Add(message_log_uid_)}
|
|
AND {tb_ord_message_log_record.CN_STATUS_FLAG} = {cp.Add(record_status_flag_)}
|
|
AND {tb_ord_message_log_record.CN_ERROR_CODE} IS NULL
|
|
";
|
|
|
|
return GenerateCommand<tb_ord_message_log_record>(sql, cp, out command_);
|
|
}
|
|
|
|
/// <summary>
|
|
/// readCommandPostDataHandler, 替代原本查詢方法用來排序 receivable & received
|
|
/// </summary>
|
|
public string readCommandPostDataHandlerQueryDetailSortGuid(CRequestMessage i_crmInput, Dictionary<string, string> dicCondition, List<string> lsBranch, List<string> userBranch, bool IsTotalQuery, int nPageIdx, int nPageNum, out Command command_)
|
|
{
|
|
ADirectCmdParameter cp = GetParamter();
|
|
bool isGroup = false;
|
|
const string received = "received";
|
|
const string receivable = "receivable";
|
|
|
|
string selectColumn = IsTotalQuery ?
|
|
$"COUNT(a.{ tb_ord_order_master.CN_UID})" :
|
|
$@"a.{ tb_ord_order_master.CN_UID},
|
|
a.{received},
|
|
a.{receivable},
|
|
art.{tb_grp_article.CN_REMARK},
|
|
aa.{ tb_ord_order_master.CN_TYPE},
|
|
aa.{ tb_ord_order_master.CN_ORDER_CODE},
|
|
aa.{ tb_ord_order_master.CN_STATUS},
|
|
aa.{ tb_ord_order_master.CN_CREATE_DATE},
|
|
aa.{ tb_ord_order_master.CN_GROUP_UID},
|
|
aa.{ tb_ord_order_master.CN_BRANCH_UID},
|
|
aa.{ tb_ord_order_master.CN_ARRIVED_DATE},
|
|
aa.{ tb_ord_order_master.CN_ARTICLE_UID},
|
|
aa.{ tb_ord_order_master.CN_NAME}
|
|
";
|
|
|
|
string sql = $@"
|
|
SELECT {selectColumn} FROM
|
|
(
|
|
select
|
|
a.{tb_ord_order_master.CN_UID},
|
|
--sum (case when b.status = 2 then b.take_qty * c.price else 0 end) received,
|
|
--sum (case when b.status = 2 then b.take_qty * (d.amount / d.qty)else 0 end) received,-- take_qty為實際拿取數量, d.amount / d.qty計算實際購買金額
|
|
---- sum (case when b.status = 2 then d.amount else 0 end) received,-- d.amount 已取貨總 Total
|
|
--sum ((b.price /b.order_qty) * take_qty) received,
|
|
--sum(b.price) receivable
|
|
--sum (case when e.wprice_payment = 1 and c.wholesale_price != 0 then b.order_qty * c.wholesale_price else b.price end) receivable
|
|
---- sum (case when b.status = 2 then d.amount else b.price end) receivable
|
|
(case max(a.type) when '1' then sum (case when b.status = 2 then d.amount else 0 end)
|
|
when '2' then sum(ee.amount) else 0 end ) received,-- d.amount 已取貨總 Total
|
|
(case max(a.type) when '1' then sum (case when b.status = 2 then d.amount else b.price end)
|
|
when '2' then sum(ee.amount) else 0 end ) receivable
|
|
|
|
from {nameof(tb_ord_order_master)} a
|
|
LEFT JOIN {nameof(tb_ord_order_detail)} b on b.{tb_ord_order_detail.CN_ORDER_UID} = a.{tb_ord_order_master.CN_UID}
|
|
--LEFT JOIN {nameof(tb_prd_article2product)} c on c.{tb_prd_article2product.CN_UID} = b.{tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID}
|
|
--LEFT JOIN {nameof(tb_ord_purchase_detail)} d on d.{tb_ord_purchase_detail.CN_ORDER_DETAIL_UID} = b.{tb_ord_order_detail.CN_UID}
|
|
LEFT JOIN (SELECT {tb_ord_purchase_detail.CN_ORDER_DETAIL_UID}, SUM({tb_ord_purchase_detail.CN_AMOUNT}) amount FROM {nameof(tb_ord_purchase_detail)} GROUP BY {tb_ord_purchase_detail.CN_ORDER_DETAIL_UID}) d on d.{tb_ord_purchase_detail.CN_ORDER_DETAIL_UID} = b.uid -- Make sure is only one
|
|
--LEFT JOIN {nameof(tb_meb_member)} e on e.{tb_meb_member.CN_UID} = b.{tb_ord_order_detail.CN_MEMBER_UID}
|
|
|
|
LEFT JOIN (SELECT sum(x.{tb_prd_article2product.CN_PRICE}* z.{tb_ord_purchase_detail.CN_QTY}) amount ,x.{tb_prd_article2product.CN_ORDER_UID}
|
|
FROM {nameof(tb_prd_article2product)} x
|
|
LEFT JOIN {nameof(tb_ord_order_master)} y on y.{tb_ord_order_master.CN_UID}=x.{tb_prd_article2product.CN_ORDER_UID}
|
|
LEFT JOIN {nameof(tb_ord_purchase_detail)} z on z.{tb_ord_purchase_detail.CN_ARTICLE2PRODUCT_UID}=x.{tb_prd_article2product.CN_UID}
|
|
GROUP BY x.{tb_prd_article2product.CN_ORDER_UID}
|
|
) as ee on ee.{tb_prd_article2product.CN_ORDER_UID} = a.{tb_ord_order_master.CN_UID}
|
|
GROUP BY a.{tb_ord_order_master.CN_UID}
|
|
) a
|
|
LEFT JOIN {nameof(tb_ord_order_master)} aa on aa.{tb_ord_order_master.CN_UID} = a.{tb_ord_order_master.CN_UID} -- Get full column
|
|
LEFT JOIN {nameof(tb_grp_article)} art on art.uid = aa.article_uid -- Get remark
|
|
WHERE 1=1
|
|
";
|
|
|
|
sql += $"AND aa.{tb_ord_order_master.CN_TYPE} != {cp.Add((int)Enums.ArticleType.Purchase)} ";
|
|
sql += $"AND aa.{tb_ord_order_master.CN_STATUS_FLAG} = {cp.Add(BLWording.STATUS_FLAG_ON)} ";
|
|
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_start"))
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_CREATE_DATE} >= {cp.Add(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_start"])} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_end"))
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_CREATE_DATE} < { cp.Add(Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_end"]).AddDays(1))} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_STATUS))
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_STATUS} = {cp.Add(dicCondition[tb_ord_order_master.CN_STATUS])} ";
|
|
}
|
|
else
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_STATUS} != {cp.Add((int)Enums.OrderMainStatus.Archive)} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_ORDER_CODE))
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_ORDER_CODE} = {cp.Add(dicCondition[tb_ord_order_master.CN_ORDER_CODE])} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_start"))
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_ARRIVED_DATE} >= {cp.Add(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_start"])} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_end"))
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_ARRIVED_DATE} < {cp.Add(Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_end"]).AddDays(1))} ";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_UID))
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_UID} = {dicCondition[tb_ord_order_master.CN_UID]}";
|
|
}
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_GROUP_UID))
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} = {cp.Add(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())
|
|
{
|
|
sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} = {cp.Add(dicCondition["GroupOrBranch_uid"].ToString())} ";
|
|
isGroup = true;
|
|
}
|
|
}
|
|
sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} IN ({string.Join(',', lsBranch.Select(x => $"'{x}'").ToArray())}) ";
|
|
|
|
if (dicCondition.ContainsKey(tb_ord_order_master.CN_BRANCH_UID))
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} = {cp.Add(dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString())} ";
|
|
}
|
|
else if (dicCondition.ContainsKey("GroupOrBranch_uid") && !isGroup)
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} = {cp.Add(dicCondition["GroupOrBranch_uid"].ToString())} ";
|
|
}
|
|
else
|
|
{
|
|
sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} IN ({string.Join(',', userBranch.Select(x=> $"'{x}'").ToArray())}) "; ;
|
|
}
|
|
|
|
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();
|
|
|
|
sql +=
|
|
$@"
|
|
AND
|
|
(
|
|
aa.{tb_ord_order_master.CN_ORDER_CODE} LIKE {cp.Add($"%{mainFullTextPattern}%")}
|
|
OR aa.{tb_ord_order_master.CN_NAME} LIKE {cp.Add($"%{mainFullTextPattern}%")}
|
|
)
|
|
";
|
|
}
|
|
}
|
|
|
|
// 如果不是查詢總Total, Join Sort And Offset
|
|
if (!IsTotalQuery)
|
|
{
|
|
if (mainOrderData != null && mainOrderData.First().Key != tb_grp_branch.CN_BRANCH_NAME)
|
|
{
|
|
sql += $"ORDER BY {mainOrderData.First().Key} {mainOrderData.First().Value} ";
|
|
}
|
|
else
|
|
{
|
|
sql += $"ORDER BY {tb_grp_branch.CN_CREATE_DATE} DESC ";
|
|
}
|
|
|
|
sql += $"OFFSET {nPageIdx * nPageNum} ROW FETCH FIRST {nPageNum} ROW ONLY ";//配合上面語法
|
|
}
|
|
|
|
return GenerateCommand<tb_ord_message_log_record>(sql, cp, out command_);
|
|
}
|
|
|
|
public Command IsMemberOrderCheckedOut(string member_uid, string branch_uid)
|
|
{
|
|
Command command = null;
|
|
ADirectCmdParameter cp = GetParamter();
|
|
|
|
var sql = $@"
|
|
SELECT dtl.{tb_ord_order_detail.CN_UID} FROM {nameof(tb_ord_order_detail)} dtl
|
|
LEFT JOIN {nameof(tb_meb_member)} meb
|
|
ON meb.{tb_meb_member.CN_UID} = dtl.{tb_ord_order_detail.CN_MEMBER_UID}
|
|
LEFT JOIN {nameof(tb_grp_branch)} branch
|
|
ON branch.{tb_grp_branch.CN_UID} = meb.{tb_meb_member.CN_DEFAULT_BRANCH}
|
|
WHERE 1=1
|
|
AND dtl.{tb_ord_order_detail.CN_STATUS} = {cp.Add((int)Enums.OrderStatus.NotTake)}
|
|
AND meb.{tb_meb_member.CN_UID} = {cp.Add(member_uid)}
|
|
AND branch.{tb_grp_branch.CN_UID} = {cp.Add(branch_uid)}
|
|
";
|
|
|
|
GenerateCommand<tb_ord_message_log_record>(sql, cp, out command);
|
|
|
|
return command;
|
|
}
|
|
}
|
|
}
|