You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
5042 lines
273 KiB
5042 lines
273 KiB
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);
|
|
}
|
|
}
|
|
}
|