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