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