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.
3441 lines
126 KiB
3441 lines
126 KiB
|
|
|
|
///-----------------------------------------------------------------------
|
|
/// <copyright file="DBService.cs" company="Origtek">
|
|
/// 程式代號: DBService
|
|
/// 程式名稱: DBService
|
|
/// 程式說明:
|
|
/// 起始作者: Nelson
|
|
/// 起始日期: 2016/09/03 17:03:37
|
|
/// 最新修改人: Hercules
|
|
/// 最新修日期: 2017/05/15 12:24:49
|
|
/// </copyright>
|
|
///-----------------------------------------------------------------------
|
|
namespace CounsellorBL
|
|
{
|
|
using CounsellorBL.BLStructure;
|
|
using CounsellorBL.Helper;
|
|
using CounsellorBL.Common;
|
|
using Microsoft.Extensions.Caching.Memory;
|
|
using MonumentDefine;
|
|
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using OT.COM.ArsenalDB;
|
|
using OT.COM.LogisticsUtil;
|
|
using OT.COM.SignalerMessage;
|
|
using SoldierData.EnterprizeV4;
|
|
using System;
|
|
using System.Collections.Concurrent;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using static CounsellorBL.Common.EntityBaseExtension;
|
|
using static OT.COM.ArsenalDB.EntityBase;
|
|
using Microsoft.Extensions.Caching.Distributed;
|
|
|
|
/// <summary>
|
|
/// 類別名稱:DBService
|
|
/// 類別說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
public abstract partial class DBService : ServiceBase
|
|
{
|
|
public delegate string DG_GenerateEditCommand(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 = "");
|
|
|
|
public delegate string DG_GenerateReadCommand(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out Command o_cCmd,
|
|
[System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
|
|
[System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
|
|
[System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "");
|
|
|
|
public delegate string DG_ExportFile(CRequestMessage i_crmInput, JArray i_jaData, tb_sys_session i_sSessionUser, out string o_sFilePath, 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 = "");
|
|
|
|
protected delegate string DG_EditPostCommand(ArsenalInterface ai, EntityBase i_oOldInst, EntityBase i_oNewInst, out List<Command> o_lcAddition,
|
|
[System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
|
|
[System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
|
|
[System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "");
|
|
|
|
protected delegate string DG_ModifyCreateInstance(CRequestMessage i_crm, ref object io_oInst);
|
|
|
|
protected List<DG_EditPostCommand> ldgUpdate_PostCommand { get; } = new List<DG_EditPostCommand>();
|
|
|
|
protected List<DG_ModifyCreateInstance> ldgModifyCreateInstance { get; } = new List<DG_ModifyCreateInstance>();
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="i_crmInput"></param>
|
|
/// <param name="ai"></param>
|
|
/// <param name="i_jaData"></param>
|
|
/// <param name="i_sSessionUser"></param>
|
|
/// <param name="o_oReault">In most case, return QueryResponse</param>
|
|
/// <returns></returns>
|
|
public delegate string DG_PostHandleReadData(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 = "");
|
|
|
|
public delegate string DG_GenCacheKey(CRequestMessage i_crmInput, out List<string> o_lsKeys,
|
|
[System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0,
|
|
[System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
|
|
[System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "");
|
|
|
|
public abstract string MainTable
|
|
{
|
|
get;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 定義連線檢查提醒信間隔時間
|
|
/// </summary>
|
|
public const int CONNECTIONBOUNDARY_NOTIFY_PERIOD = 10; // 10 Minutes
|
|
|
|
/// <summary>
|
|
/// 類別成員、類別屬性說明:cdbm
|
|
/// </summary>
|
|
|
|
protected Dictionary<string, object> dicDBMeta { get; private set; }
|
|
|
|
protected DG_ExportFile dgGetExportPath { get; set; }
|
|
|
|
protected DG_GenerateEditCommand dgImportCommandGenerator { get; set; }
|
|
|
|
protected DG_GenCacheKey dgGenCacheKey { get; set; }
|
|
public List<string> EditMustConditionKeys { get; protected set; } = new List<string>() { BLWording.UID };
|
|
|
|
/// <summary>
|
|
/// 類別名稱:checkDBLock
|
|
/// 類別說明:DB Lock檢核用資料結構
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
protected class checkDBLock
|
|
{
|
|
/// <summary>
|
|
/// 類別成員、類別屬性說明:預期檢核的ROWID
|
|
/// </summary>
|
|
public string RowID
|
|
{
|
|
get; set;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 類別成員、類別屬性說明:預期檢核的更新時間
|
|
/// </summary>
|
|
public DateTime? UpdateTime
|
|
{
|
|
get; set;
|
|
}
|
|
}
|
|
|
|
private string _MainTableName = null;
|
|
|
|
/// <summary>
|
|
/// 類別成員、類別屬性說明:DBService
|
|
/// </summary>
|
|
protected DBService()
|
|
: base()
|
|
{
|
|
//
|
|
if (Command.SingleConnection != null)
|
|
{
|
|
Command.SetupSingleConnection(CustomizeDBMgr.SingleConnection);
|
|
}
|
|
|
|
}
|
|
|
|
protected static Type _GetEntityType(string i_sEntityName)
|
|
{
|
|
return ClassHelper.GetTypeByFullNameEndWithTerm(i_sEntityName);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Gets the name of the main table for db lock
|
|
/// </summary>
|
|
/// <param name="i_tType">Type</param>
|
|
/// <returns>Formated name</returns>
|
|
public string GetMainTableName(Type i_tType)
|
|
{
|
|
if (_MainTableName == null && i_tType != null)
|
|
{
|
|
string sRes = $"{i_tType.FullName}";
|
|
_MainTableName = sRes.Substring(sRes.IndexOf('.', StringComparison.OrdinalIgnoreCase) + 1);
|
|
}
|
|
return _MainTableName;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetMasterDBTableInfo
|
|
/// 函式說明:產生資料表Meta Data含連線資訊
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_t">資料表類別</param>
|
|
/// <returns>資料表Meta Data</returns>
|
|
/// <summary>
|
|
public TableInfo GetMasterDBTableInfo(Type i_t)
|
|
{
|
|
return new TableInfo()
|
|
{
|
|
TargetTable = i_t,
|
|
DBMeta = dicDBMeta
|
|
};
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getEntity
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_sEntityName"></param>
|
|
/// <param name="o_ebEntity">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
protected static string getEntity(string i_sEntityName, out object o_ebEntity)
|
|
{
|
|
return ClassHelper.GetInstByFullNameEndWithTerm(i_sEntityName, out o_ebEntity); //錯誤時回傳No Type 或Create Type Fail錯誤
|
|
}
|
|
|
|
public tb_sys_session SetupGuestSession(CRequestMessage i_crmInput)
|
|
{
|
|
string GUESTTOKEN = "guest";
|
|
tb_sys_session sRes = null;
|
|
|
|
do
|
|
{
|
|
|
|
if (i_crmInput == null)
|
|
{
|
|
break;
|
|
}
|
|
if (this.OriRequest == null)
|
|
{
|
|
this.OriRequest = i_crmInput;
|
|
}
|
|
if (i_crmInput.customparam == null)
|
|
{
|
|
i_crmInput.customparam = new Dictionary<string, object>();
|
|
}
|
|
|
|
this.OriRequest.token = string.IsNullOrEmpty(i_crmInput.token) ? GUESTTOKEN : i_crmInput.token;
|
|
|
|
IMemoryCache imc = GetMemoryCache();
|
|
|
|
if (imc == null)
|
|
{
|
|
throw new NullReferenceException(MethodBase.GetCurrentMethod() + "." + nameof(imc) + "is NULL");
|
|
}
|
|
|
|
lock (imc)
|
|
{
|
|
if (!imc.TryGetValue(BLWording.TOKENMAP, out ConcurrentDictionary<string, tb_sys_session> dicMap))
|
|
{
|
|
dicMap = new ConcurrentDictionary<string, tb_sys_session>();
|
|
imc.Set(BLWording.TOKENMAP, dicMap);
|
|
}
|
|
|
|
string sToken = GUESTTOKEN;
|
|
|
|
if (!dicMap.ContainsKey(sToken))
|
|
{
|
|
tb_sys_session sFake = _getQuestSession("Admin");
|
|
sFake.uid = GUESTTOKEN;
|
|
dicMap.TryAdd(sToken, sFake);
|
|
}
|
|
else
|
|
{
|
|
dicMap[sToken].update_date = DateTime.Now;
|
|
}
|
|
|
|
i_crmInput.customparam[BLWording.SESSION_USER] = dicMap[sToken];
|
|
}
|
|
|
|
sRes = i_crmInput.customparam[BLWording.SESSION_USER] as tb_sys_session;
|
|
|
|
} while (false);
|
|
|
|
return sRes;
|
|
}
|
|
|
|
protected string getSessionByToken(string i_sToken, out tb_sys_session o_sSession)
|
|
{
|
|
string sMsg = null;
|
|
tb_sys_session sCur = null;
|
|
|
|
do
|
|
{
|
|
IMemoryCache imc = GetMemoryCache();
|
|
|
|
if (imc != null)
|
|
{
|
|
lock (imc)
|
|
{
|
|
string sTempMsg = null;
|
|
|
|
if (!imc.TryGetValue(BLWording.TOKENMAP, out ConcurrentDictionary<string, tb_sys_session> dicMap))
|
|
{
|
|
dicMap = new ConcurrentDictionary<string, tb_sys_session>();
|
|
imc.Set(BLWording.TOKENMAP, dicMap);
|
|
}
|
|
else
|
|
{
|
|
if (dicMap.ContainsKey(i_sToken))
|
|
{
|
|
tb_sys_session sessionTemp = dicMap[i_sToken];
|
|
|
|
if (sessionTemp.update_date.AddMinutes(1) > DateTime.Now)
|
|
{
|
|
sCur = sessionTemp.Clone() as tb_sys_session;
|
|
sTempMsg = "pass";
|
|
}
|
|
else
|
|
{
|
|
if (i_sToken == "guest")
|
|
{
|
|
Logger.Info("session timeout");
|
|
sCur = sessionTemp.Clone() as tb_sys_session;
|
|
sTempMsg = "pass";
|
|
}
|
|
dicMap.TryRemove(i_sToken, out _);
|
|
}
|
|
}
|
|
}
|
|
|
|
if (i_sToken == "guest")
|
|
{
|
|
Logger.Info("token guest sTempMsg = " + sTempMsg + " dicMap = " + dicMap);
|
|
}
|
|
|
|
if (sTempMsg != "pass")
|
|
{
|
|
int nExpMinute = Convert.ToInt32(CustomizeDBMgr.SettingData[BLWording.TOKEN_EXP_MINUTE], CultureInfo.CurrentCulture);
|
|
tb_sys_session sDisplay = new tb_sys_session();
|
|
sDisplay.SetFullDirty();
|
|
tb_sys_session sCond = new tb_sys_session() { uid = i_sToken };
|
|
|
|
Command cSelectSeesion = Command.SetupSelectCmd(sDisplay, sCond);
|
|
cSelectSeesion.ReadLevel = Command.EReadLevel.ERL_DIRTY;
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelectSeesion, null);
|
|
sCur = ai.RunQuerySingleORM<tb_sys_session>(cSelectSeesion);
|
|
|
|
if (null == sCur)
|
|
{
|
|
|
|
sMsg = MessageWording.SESSION_NOT_EXIST; //SESSION_NO_SESSION
|
|
if (i_sToken == "guest")
|
|
{
|
|
sMsg = "Messaging Error";
|
|
}
|
|
break;
|
|
}
|
|
|
|
if (nExpMinute < DateTime.Now.Subtract(sCur.update_date).TotalMinutes)
|
|
{
|
|
sMsg = MessageWording.SESSION_EXPIRED; //SESSION_EXPIRED SESSION過期
|
|
break;
|
|
}
|
|
dicMap.TryAdd(sCur.uid, sCur);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
o_sSession = sCur;
|
|
return sMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:_checkTokenValid
|
|
/// 函式說明:Session Validation
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_sToken"></param>
|
|
/// <param name="i_bRenewExpireTime"></param>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
protected CResponseMessage checkTokenValid(string i_sToken, bool i_bRenewExpireTime = false)
|
|
{
|
|
DateTime dtNow = DateTime.Now;
|
|
CResponseMessage crm = null;
|
|
string sMsg;
|
|
do
|
|
{
|
|
sMsg = getSessionByToken(i_sToken, out tb_sys_session sCur);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (i_bRenewExpireTime)
|
|
{
|
|
/*tb_sys_session sModifyData = new tb_sys_session() { update_date = dtNow };
|
|
sCur.update_date = dtNow;
|
|
WhereNode wnUpdate = new WhereNode(tb_sys_session.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_session), i_sToken);
|
|
Command cUpdate = Command.SetupUpdateCmd(sModifyData, wnUpdate);
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cUpdate, GetDefaultSystemColumnInfo());
|
|
ai.RunEditSingleCmd(cUpdate);*/
|
|
|
|
IMemoryCache imc = GetMemoryCache();
|
|
|
|
if (imc != null)
|
|
{
|
|
lock (imc)
|
|
{
|
|
if (!imc.TryGetValue(BLWording.TOKENMAP, out ConcurrentDictionary<string, tb_sys_session> dicMap))
|
|
{
|
|
dicMap = new ConcurrentDictionary<string, tb_sys_session>();
|
|
imc.Set(BLWording.TOKENMAP, dicMap);
|
|
}
|
|
|
|
if (dicMap.ContainsKey(sCur.uid))
|
|
{
|
|
dicMap[sCur.uid] = sCur;
|
|
}
|
|
else
|
|
{
|
|
dicMap.TryAdd(sCur.uid, sCur);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
crm = new CResponseMessage() { result = EResponseResult.RR_TRUE };
|
|
|
|
crm.param.Add(BLWording.SESSION_USER, sCur);
|
|
}
|
|
while (false);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:checkTokenWithCRequestMessage
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crm"></param>
|
|
/// <param name="o_oDicData"></param>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
public CResponseMessage CheckTokenWithCRequestMessage(CRequestMessage i_crm, out Dictionary<string, object> o_oDicData)
|
|
{
|
|
CResponseMessage crm = null;
|
|
string sMsg = null;
|
|
|
|
Dictionary<string, object> dicFormData = new Dictionary<string, object>();
|
|
|
|
do
|
|
{
|
|
if (i_crm == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
foreach (string sKey in i_crm.param.Keys)
|
|
{
|
|
dicFormData.Add(sKey, i_crm.param[sKey]);
|
|
}
|
|
|
|
if (i_crm.token == null)
|
|
{
|
|
sMsg = MessageWording.TOKEN_MISS; //NO TOKEN
|
|
break;
|
|
}
|
|
|
|
crm = this.checkTokenValid(i_crm.token, true);
|
|
}
|
|
while (false);
|
|
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
o_oDicData = dicFormData;
|
|
return crm;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:MakeSelectJoinByBlocks
|
|
/// 函式說明:產生
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_lBlocks"></param>
|
|
/// <param name="o_cSelect">
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static string MakeSelectJoinByBlocks(QueryJsonElementCollection i_qjelBlocks, out Command o_cSelect)
|
|
{
|
|
Command cTemp = null;
|
|
|
|
string sMsg;
|
|
do
|
|
{
|
|
List<QueryJsonElement> i_lBlocks = i_qjelBlocks;
|
|
|
|
if (i_lBlocks == null || i_lBlocks.Count == 0)
|
|
{
|
|
sMsg = MessageWording.BLOCK_MISS; //NO BLOCK
|
|
break;
|
|
}
|
|
|
|
convertWherenode(ref i_lBlocks);
|
|
|
|
QueryJson qj = new QueryJson();
|
|
|
|
sMsg = qj.AddBlock(i_lBlocks.ToArray());
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
sMsg = qj.MakeCommand(
|
|
new TableInfo()
|
|
{
|
|
TargetTable = _GetEntityType(i_lBlocks[0].table)
|
|
}, out cTemp);
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
// For Select Insert porting
|
|
if (i_lBlocks[0].SelectInsertTable != null)
|
|
{
|
|
cTemp.ChangeCmdType(Command.EC_Type.ECT_SELECTINSERT);
|
|
}
|
|
cTemp.ReadLevel = Command.EReadLevel.ERL_DIRTY;
|
|
}
|
|
while (false);
|
|
|
|
o_cSelect = cTemp;
|
|
return sMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:convertWherenode
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="io_data">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
protected static void convertWherenode(ref List<QueryJsonElement> io_data)
|
|
{
|
|
if (io_data != null)
|
|
{
|
|
for (int i = 0; i < io_data.Count; i++)
|
|
{
|
|
QueryJsonElement qjs = io_data[i];
|
|
Type t = _GetEntityType(qjs.table);
|
|
qjs.databaseinfo = t.FullName.Split('.')[1];
|
|
|
|
WhereNode wnDicwherecolsCompile = Command.whereDictionary2WhereNode(t, qjs.dicwherecols);
|
|
if (qjs.wherecols == null)
|
|
{
|
|
qjs.wherecols = wnDicwherecolsCompile;
|
|
}
|
|
else
|
|
{
|
|
if (wnDicwherecolsCompile != null)
|
|
{
|
|
qjs.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND, qjs.wherecols, wnDicwherecolsCompile);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:CreateExcel
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_dtData"></param>
|
|
/// <param name="i_crm"></param>
|
|
/// <param name="o_sFilePath"></param>
|
|
/// <param name="i_blAutoSizeColumn"></param>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
|
|
public static bool CreateExcel(DataTable i_dtData, CRequestMessage i_crm, out string o_sFilePath, bool i_blAutoSizeColumn = true)
|
|
{
|
|
bool bStatus = false;
|
|
int iCurentRow = 0;
|
|
string sFilePath = null;
|
|
string sFileName = null;
|
|
do
|
|
{
|
|
if (i_dtData == null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
int iMaxCol = i_dtData.Columns.Count;
|
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();//初始化匯出Excel物件
|
|
ISheet sheet = workbook.CreateSheet("sheet1");//設定sheet1的顯示名稱
|
|
|
|
ICellStyle style = workbook.CreateCellStyle();//創建單元格的樣式
|
|
style.BorderBottom = BorderStyle.Thin;
|
|
style.BorderLeft = BorderStyle.Thin;
|
|
style.BorderRight = BorderStyle.Thin;
|
|
style.BorderTop = BorderStyle.Thin;
|
|
|
|
IRow dataRow = sheet.CreateRow(0);
|
|
|
|
//獲取前段設定的表頭
|
|
Dictionary<string, string> items = null;
|
|
if (i_crm != null && i_crm.param.ContainsKey(BLWording.ITEMS))
|
|
{
|
|
JObject joItems = i_crm.param[BLWording.ITEMS] as JObject;
|
|
items = joItems.ToObject<Dictionary<string, string>>();
|
|
}
|
|
|
|
//填充表头
|
|
if (items != null)
|
|
{
|
|
List<DataColumn> lstRemoveCol = new List<DataColumn>();
|
|
foreach (DataColumn col in i_dtData.Columns)
|
|
{
|
|
if (items.Keys.Contains(col.ColumnName))
|
|
{
|
|
ICell cell = dataRow.CreateCell(i_dtData.Columns.IndexOf(col) - lstRemoveCol.Count);//減去移除的欄位個數
|
|
col.ColumnName = items[col.ColumnName];
|
|
cell.CellStyle = style;
|
|
cell.SetCellValue(col.ColumnName);
|
|
}
|
|
else
|
|
{
|
|
lstRemoveCol.Add(col);
|
|
iMaxCol--;
|
|
}
|
|
}
|
|
|
|
foreach (DataColumn col in lstRemoveCol)
|
|
{
|
|
i_dtData.Columns.Remove(col);
|
|
}
|
|
iCurentRow++;
|
|
}
|
|
else //填充欄位名稱
|
|
{
|
|
dataRow = sheet.CreateRow(iCurentRow);
|
|
iCurentRow++;
|
|
foreach (DataColumn dc in i_dtData.Columns)
|
|
{
|
|
ICell cell = dataRow.CreateCell(i_dtData.Columns.IndexOf(dc));
|
|
cell.CellStyle = style;
|
|
cell.SetCellValue(dc.ColumnName);
|
|
}
|
|
}
|
|
//填充內容
|
|
foreach (DataRow dr in i_dtData.Rows)
|
|
{
|
|
dataRow = sheet.CreateRow(iCurentRow);
|
|
iCurentRow++;
|
|
foreach (DataColumn dc in i_dtData.Columns)
|
|
{
|
|
ICell cell = dataRow.CreateCell(i_dtData.Columns.IndexOf(dc));
|
|
cell.CellStyle = style;
|
|
cell.SetCellValue(dr[dc].ToString() ?? "");
|
|
}
|
|
}
|
|
|
|
//自適應
|
|
if (i_blAutoSizeColumn)
|
|
{
|
|
for (int col = 0; col <= iMaxCol; col++)
|
|
{
|
|
sheet.AutoSizeColumn(col, true);
|
|
}
|
|
}
|
|
|
|
sFilePath = SystemSettingHelper.FileFolder(SystemSettingHelper.EPath.EP_FILETMP);
|
|
sFileName = _fetchString(i_crm, "filename");
|
|
sFileName = (sFileName ?? "") + DateTime.Now.ToString("yyyyMMddHHmmss", CultureInfo.CurrentCulture) + ".xls";
|
|
string sSeverPath = System.IO.Path.Combine(ServiceBase.RootPath, sFilePath);
|
|
string sFileFullName = System.IO.Path.Combine(sSeverPath, sFileName);
|
|
if (!Directory.Exists(sSeverPath))
|
|
{
|
|
Directory.CreateDirectory(sSeverPath);
|
|
}
|
|
//保存
|
|
using FileStream fs = new FileStream(sFileFullName, FileMode.Create, FileAccess.Write);
|
|
workbook.Write(fs);
|
|
bStatus = true;
|
|
}
|
|
while (false);
|
|
|
|
o_sFilePath = System.IO.Path.Combine(sFilePath, sFileName);
|
|
return bStatus;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:CreateExcelFiles
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_dicData"></param>
|
|
/// <param name="i_crm"></param>
|
|
/// <param name="o_sFilePath"></param>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
/// <param name="i_dicData"></param>
|
|
/// <param name="i_crm"></param>
|
|
/// <param name="o_sFilePath"></param>
|
|
/// <returns></returns>
|
|
public static bool CreateExcelFiles(List<Dictionary<string, object>> i_dicData, CRequestMessage i_crm, out string o_sFilePath)
|
|
{
|
|
string sFilePath = null;
|
|
string sFileName = null;
|
|
bool bStatus = false;
|
|
int imaxcell = 0;
|
|
|
|
do
|
|
{
|
|
if (i_dicData == null)
|
|
{
|
|
break;
|
|
}
|
|
Dictionary<string, object> items = null;
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
ISheet sheet = workbook.CreateSheet("sheet1");
|
|
ICellStyle style = workbook.CreateCellStyle();
|
|
style.BorderBottom = BorderStyle.Thin;
|
|
style.BorderLeft = BorderStyle.Thin;
|
|
style.BorderRight = BorderStyle.Thin;
|
|
style.BorderTop = BorderStyle.Thin;
|
|
IRow dataRow = sheet.CreateRow(0);
|
|
|
|
if (i_crm == null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (i_crm.param.ContainsKey(BLWording.ITEMS))
|
|
{
|
|
JObject joItems = i_crm.param[BLWording.ITEMS] as JObject;
|
|
items = joItems.ToObject<Dictionary<string, object>>();
|
|
}
|
|
|
|
// Workarond: Forward capbility for DBService.ExcelEntity
|
|
if (items == null && i_crm.param.ContainsKey(BLWording.DATA))
|
|
{
|
|
if (!(i_crm.param[BLWording.DATA] is JObject dic))
|
|
{
|
|
break;
|
|
}
|
|
if (!(dic[BLWording.ITEMS] is JObject joItems))
|
|
{
|
|
break;
|
|
}
|
|
items = joItems.ToObject<Dictionary<string, object>>();
|
|
}
|
|
|
|
//填充表头
|
|
if (items != null)
|
|
{
|
|
int index = 0;
|
|
foreach (string sKey in items.Keys)
|
|
{
|
|
object sValue = items[sKey];
|
|
string s = sValue.ToString() ?? "";
|
|
if (!string.IsNullOrEmpty(s))
|
|
{
|
|
ICell cell = dataRow.CreateCell(index);
|
|
cell.CellStyle = style;
|
|
cell.SetCellValue(s);
|
|
index++;
|
|
}
|
|
}
|
|
}
|
|
//填充内容
|
|
string sJsonKey = _fetchString(i_crm, "sjsonkey");
|
|
string scartonkey = _fetchString(i_crm, "scartonkey");
|
|
sJsonKey ??= "";
|
|
scartonkey ??= "";
|
|
int cellIndex = 0;
|
|
foreach (Dictionary<string, object> _list in i_dicData)
|
|
{
|
|
if (_list != null && items != null)
|
|
{
|
|
dataRow = sheet.CreateRow(dataRow.RowNum + 1);
|
|
imaxcell = cellIndex > imaxcell ? cellIndex : imaxcell;
|
|
cellIndex = 0;
|
|
foreach (string sKey in items.Keys)
|
|
{
|
|
if (_list.Keys.Contains(sKey))
|
|
{
|
|
object sValue = _list[sKey];
|
|
string s = sValue.ToString() ?? "";
|
|
if (sKey != sJsonKey)
|
|
{
|
|
ICell cell = dataRow.CreateCell(cellIndex);
|
|
cell.CellStyle = style;
|
|
if (sKey == scartonkey)
|
|
{
|
|
cell.SetCellValue((s == "0" ? "" : s));
|
|
}
|
|
else
|
|
{
|
|
cell.SetCellValue(s);
|
|
}
|
|
cellIndex++;
|
|
}
|
|
else
|
|
{
|
|
JObject jsonObj = (JObject)JsonConvert.DeserializeObject(s);
|
|
Dictionary<string, object> jsonData = jsonObj.ToObject<Dictionary<string, object>>();
|
|
|
|
foreach (string _sKey in jsonData.Keys)
|
|
{
|
|
if (_sKey.ToLower(CultureInfo.CurrentCulture).IndexOf("_scan_sval", StringComparison.OrdinalIgnoreCase) > -1)
|
|
{//整合性報表特殊處理（公用部分走上邊部分）
|
|
object sJson = jsonData[_sKey];
|
|
string sVal = sJson.ToString() ?? "";
|
|
ICell cell = dataRow.CreateCell(cellIndex);
|
|
cell.CellStyle = style;
|
|
cell.SetCellValue(sVal);
|
|
cellIndex++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
//自適應
|
|
for (int cellindex = 0; cellindex <= imaxcell; cellindex++)
|
|
{
|
|
sheet.AutoSizeColumn(cellindex, true);
|
|
}
|
|
sFilePath = Path.Combine("Uploads", "ExpExcel");
|
|
sFileName = _fetchString(i_crm, "filename");
|
|
sFileName = (sFileName ?? "") + DateTime.Now.ToString("yyyyMMddHHmmss", CultureInfo.CurrentCulture) + ".xls";
|
|
string sSeverPath = System.IO.Path.Combine(ServiceBase.RootPath, sFilePath);
|
|
string sFileFullName = Path.Combine(sSeverPath, sFileName);
|
|
if (!Directory.Exists(sSeverPath))
|
|
{
|
|
Directory.CreateDirectory(sSeverPath);
|
|
}
|
|
//保存
|
|
using FileStream fs = new FileStream(sFileFullName, FileMode.Create, FileAccess.Write);
|
|
workbook.Write(fs);
|
|
bStatus = true;
|
|
}
|
|
while (false);
|
|
|
|
o_sFilePath = sFilePath + sFileName;
|
|
return bStatus;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getToExcelData
|
|
/// 函式說明:整合性報表案例1json字串處理
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_jaData">
|
|
/// With given table name
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static Dictionary<string, object> getToExcelData(JArray i_jaData)
|
|
{
|
|
Dictionary<string, object> dicData = i_jaData?.ToObject<Dictionary<string, object>>();
|
|
if (dicData != null && dicData.Any())
|
|
{
|
|
List<Dictionary<string, object>> lsDic = i_jaData.ToObject<List<Dictionary<string, object>>>();
|
|
foreach (Dictionary<string, object> dic in lsDic)
|
|
{
|
|
foreach (string sKey in dic.Keys)
|
|
{
|
|
if (sKey.ToLower(CultureInfo.CurrentCulture).IndexOf("_scan_sval", StringComparison.OrdinalIgnoreCase) > -1)
|
|
{
|
|
if (dicData[sKey] != null)
|
|
{
|
|
dicData[sKey] += ";" + dic[sKey];
|
|
}
|
|
else
|
|
{
|
|
dicData.Add(sKey, dic[sKey]);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return dicData;
|
|
}
|
|
|
|
public static ErrorPack GetLastErrorPack(Command i_c)
|
|
{
|
|
return i_c == null || i_c.IsSuccess ? new ErrorPack() : new ErrorPack(i_c.LastErrorCode, i_c.LastErrorMsg);
|
|
}
|
|
|
|
public static ErrorPack GetLastErrorPack(List<Command> i_lcCmds)
|
|
{
|
|
Command cFind = i_lcCmds.FirstOrDefault(c => !c.IsSuccess);
|
|
return cFind == null ? new ErrorPack() : new ErrorPack(cFind.LastErrorCode, cFind.LastErrorMsg);
|
|
}
|
|
|
|
public static CErrorResponseMessage GetErrorResponseMessage(ErrorPack i_ep)
|
|
{
|
|
CErrorResponseMessage crm = null;
|
|
if (i_ep != null && !i_ep.IsSucces)
|
|
{
|
|
crm = new CErrorResponseMessage(i_ep.ErrorCode, null);
|
|
crm.param[BLWording.ERROR_DESCRIPTION] = i_ep.ErrorMsg;
|
|
}
|
|
return crm;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetLastErrorCode
|
|
/// 函式說明:取得DB錯誤碼
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_cCmd">SQL命令</param>
|
|
/// <returns>DB錯誤碼</returns>
|
|
/// <summary>
|
|
public static string GetLastErrorCode(Command i_cCmd)
|
|
{
|
|
return i_cCmd == null || i_cCmd.IsSuccess ? null : i_cCmd.LastErrorCode;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetLastErrorCode
|
|
/// 函式說明:取得第一組DB錯誤碼
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_lcCmds">SQL命令List</param>
|
|
/// <returns>第一組DB錯誤碼</returns>
|
|
/// <summary>
|
|
public static string GetLastErrorCode(List<Command> i_lcCmds)
|
|
{
|
|
return i_lcCmds?.Find(c => !c.IsSuccess)?.LastErrorCode;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetLastErrorCode
|
|
/// 函式說明:取得DB錯誤碼
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_cCmd">SQL命令</param>
|
|
/// <returns>DB錯誤碼</returns>
|
|
/// <summary>
|
|
public static string GetLastErrorMessage(Command i_cCmd)
|
|
{
|
|
return i_cCmd == null || i_cCmd.IsSuccess ? null : i_cCmd.LastErrorMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetLastErrorCode
|
|
/// 函式說明:取得第一組DB錯誤碼
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_lcCmds">SQL命令List</param>
|
|
/// <returns>第一組DB錯誤碼</returns>
|
|
/// <summary>
|
|
public static string GetLastErrorMessage(List<Command> i_lcCmds)
|
|
{
|
|
return i_lcCmds?.Find(c => !c.IsSuccess)?.LastErrorMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetRequestOrder
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static List<Dictionary<string, string>> GetRequestOrder(CRequestMessage i_crmInput)
|
|
{
|
|
List<Dictionary<string, string>> ordercol = null;
|
|
object oOrdercol = _fetchString(i_crmInput, BLWording.ORDERCOL);
|
|
if (oOrdercol != null)
|
|
{
|
|
JToken token = JArray.Parse(oOrdercol.ToString());
|
|
if (token != null)
|
|
{
|
|
ordercol = token.ToObject<List<Dictionary<string, string>>>();
|
|
}
|
|
}
|
|
return ordercol;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetQueryMasterFirstQJEDicwherecols
|
|
/// 函式說明: 一般用以提供QJE.dicwherecols
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>Master查詢條件Dictionary</returns>
|
|
/// <summary>
|
|
public static Dictionary<string, string> GetQueryMasterFirstQJEDicwherecols(CRequestMessage i_crmInput)
|
|
{
|
|
return GetQueryMasterFirstWhereData<Dictionary<string, string>>(i_crmInput);
|
|
}
|
|
|
|
public static WhereNode GetQueryMasterFirstWhereNode<T>(CRequestMessage i_crmInput)
|
|
{
|
|
Dictionary<string, string> dicCondition = GetQueryMasterFirstQJEDicwherecols(i_crmInput);
|
|
return Command.whereDictionary2WhereNode<T>(dicCondition);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetQueryMaster
|
|
/// 函式說明:Master查詢條件
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>Master查詢條件Dictionary</returns>
|
|
/// <summary>
|
|
public static T GetQueryMasterFirstWhereData<T>(CRequestMessage i_crmInput) where T : class
|
|
{
|
|
T dicData = 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())
|
|
{
|
|
dicData = joData[0][BLWording.WHEREDATA].ToObject<T>();
|
|
}
|
|
}
|
|
|
|
return dicData;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetQueryMasterFirstWhereData
|
|
/// 函式說明:Master查詢條件
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>Master查詢條件Dictionary</returns>
|
|
/// <summary>
|
|
public static Dictionary<string, object> GetQueryMasterFirstWhereData(CRequestMessage i_crmInput)
|
|
{
|
|
return GetQueryMasterFirstWhereData<Dictionary<string, object>>(i_crmInput);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetQueryDetail
|
|
/// 函式說明:Detail查詢條件
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>Detail查詢條件Dictionary</returns>
|
|
/// <summary>
|
|
public static Dictionary<string, string> GetQueryDetail(CRequestMessage i_crmInput)
|
|
{
|
|
Dictionary<string, string> dicData = null;
|
|
|
|
if (i_crmInput != null && i_crmInput.param != null && i_crmInput.param.ContainsKey(BLWording.QRY_DETAIL))
|
|
{
|
|
JArray joData = i_crmInput.param[BLWording.QRY_DETAIL] as JArray;
|
|
|
|
if (joData.Any())
|
|
{
|
|
dicData = joData[0][BLWording.WHEREDATA].ToObject<Dictionary<string, string>>();
|
|
}
|
|
}
|
|
|
|
return dicData;
|
|
}
|
|
|
|
#region DBLOCK
|
|
|
|
/// <summary>
|
|
/// Generate lock commands
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="o_lcCmds">Generated commands</param>
|
|
/// <returns></returns>
|
|
protected virtual string setupDBLockCommand(CRequestMessage i_crmInput, out List<Command> o_lcCmds)
|
|
{
|
|
List<Command> lCmds = new List<Command>();
|
|
string sMsg;
|
|
|
|
do
|
|
{
|
|
if (i_crmInput == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
sMsg = getSessionByToken(i_crmInput.token, out tb_sys_session sUser);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
foreach (JToken joItem in (i_crmInput.param[BLWording.ADD_MASTER] as JArray))
|
|
{
|
|
tb_sys_dblock o = new tb_sys_dblock()
|
|
{
|
|
program_uid = GetRequestProgramID(i_crmInput),
|
|
table_uid = MainTable,
|
|
lock_cust_desc = this.GetType().Name + " lock",
|
|
lock_ip = i_crmInput.clientip,
|
|
lock_hostname = i_crmInput.cleinthostsname,
|
|
lock_uid = joItem[BLWording.DATA][tb_sys_dblock.CN_LOCK_UID].ToString(),
|
|
lock_update_date = DateTime.Now,
|
|
lock_update_org_uid = sUser.update_org_uid,
|
|
lock_update_user_uid = sUser.update_user_uid
|
|
};
|
|
lCmds.Add(Command.SetupInsertCmd(o));
|
|
}
|
|
|
|
}
|
|
while (false);
|
|
|
|
o_lcCmds = lCmds;
|
|
return sMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// DBlock
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>執行結果</returns>
|
|
public CResponseMessage DBlock(CRequestMessage i_crmInput)
|
|
{
|
|
ArsenalInterface.ManualTrans mt = GetManualTrans<tb_sys_system_setting>();
|
|
ArsenalInterface ai = null;
|
|
string sMsg;
|
|
|
|
do
|
|
{
|
|
sMsg = setupDBLockCommand(i_crmInput, out List<Command> lAddLock);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
ai = ArsenalDBMgr.GetInst(lAddLock[0], GetDefaultSystemColumnInfo());
|
|
|
|
if (lAddLock.Count != ai.RunEditCmds(lAddLock, mt))
|
|
{
|
|
sMsg = GetLastErrorCode(lAddLock);
|
|
|
|
if (sMsg == null)
|
|
{
|
|
sMsg = MessageWording.CREATE_FAIL;
|
|
}
|
|
|
|
break;
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
CResponseMessage crmRes = null;
|
|
|
|
if (ai != null)
|
|
{
|
|
if (sMsg != null)
|
|
{
|
|
ai.Rollback(mt);
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
else
|
|
{
|
|
ai.Commit(mt);
|
|
ai.CloseManualTrans(mt);
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
}
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:DBLockRefresh
|
|
/// 函式說明:刷新DB Lock資料的更新時間(以Token)
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <summary>
|
|
public CResponseMessage DBLockRefresh(CRequestMessage i_crmInput)
|
|
{
|
|
ArsenalInterface.ManualTrans mt = GetManualTrans<tb_sys_dblock>();
|
|
ArsenalInterface ai = null;
|
|
string sMsg;
|
|
|
|
do
|
|
{
|
|
if (i_crmInput == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
List<string> sKeys = new List<string> { tb_sys_dblock.CN_LOCK_UID };
|
|
List<Command> lUpdLock = getUpdListCommand(i_crmInput, i_crmInput.param[BLWording.UPD_MASTER], tb_sys_dblock.TABLENAME, sKeys, out sMsg);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
ai = ArsenalDBMgr.GetInst(lUpdLock[0], GetDefaultSystemColumnInfo());
|
|
int nEffect = ai.RunEditCmds(lUpdLock, mt);
|
|
|
|
sMsg = GetLastErrorCode(lUpdLock);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (nEffect != lUpdLock.Count)
|
|
{
|
|
sMsg = MessageWording.DBLOCK_DATACHANGED;
|
|
break;
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
|
|
CResponseMessage crmRes;
|
|
if (sMsg != null)
|
|
{
|
|
ai?.Rollback(mt);
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
else
|
|
{
|
|
ai?.Commit(mt);
|
|
ai?.CloseManualTrans(mt);
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:DBLockUnlock
|
|
/// 函式說明:截除DB Lock資料(以Token)
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
public CResponseMessage DBLockUnlock(CRequestMessage i_crmInput)
|
|
{
|
|
ArsenalInterface.ManualTrans mt = GetManualTrans<tb_sys_dblock>();
|
|
ArsenalInterface ai = null;
|
|
string sMsg;
|
|
do
|
|
{
|
|
if (i_crmInput == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
List<string> sKeys = new List<string> { tb_sys_dblock.CN_LOCK_UID };
|
|
List<Command> lDelLock = getDelListCommand(i_crmInput, i_crmInput.param[BLWording.DEL_MASTER], tb_sys_dblock.TABLENAME, sKeys, out sMsg);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
ai = ArsenalDBMgr.GetInst(lDelLock[0], GetDefaultSystemColumnInfo());
|
|
int nEffect = ai.RunEditCmds(lDelLock);
|
|
|
|
sMsg = GetLastErrorCode(lDelLock);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (nEffect != lDelLock.Count)
|
|
{
|
|
sMsg = MessageWording.DBLOCK_DATACHANGED;
|
|
break;
|
|
}
|
|
|
|
}
|
|
while (false);
|
|
CResponseMessage crmRes;
|
|
if (sMsg != null)
|
|
{
|
|
ai?.Rollback(mt);
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
else
|
|
{
|
|
ai?.Commit(mt);
|
|
ai?.CloseManualTrans(mt);
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:DBLockCleanExpire
|
|
/// 函式說明:清除所有DB Lock資料(以Token)
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <summary>
|
|
protected CResponseMessage DBLockCleanExpire(CRequestMessage i_crmInput)
|
|
{
|
|
CResponseMessage crmRes = null;
|
|
|
|
string sMsg;
|
|
do
|
|
{
|
|
|
|
WhereNode wnCond1 = new WhereNode(tb_sys_dblock.CN_UPDATE_DATE, WhereNode.EColumnOperation.EOT_LT, typeof(tb_sys_dblock), DateTime.Now.AddMinutes(-5));
|
|
WhereNode wnCond2 = new WhereNode(tb_sys_dblock.CN_UPDATE_DATE, WhereNode.EColumnOperation.EOT_ISNULL, typeof(tb_sys_dblock));
|
|
WhereNode wnCond = new WhereNode(WhereNode.ENodeOperation.ENO_OR, wnCond1, wnCond2);
|
|
|
|
List<Command> lCmds = new List<Command>() {
|
|
Command.SetupDeleteCmd(wnCond)
|
|
};
|
|
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(lCmds[0], GetDefaultSystemColumnInfo());
|
|
ai.RunEditCmds(lCmds);
|
|
|
|
sMsg = GetLastErrorCode(lCmds);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
|
|
}
|
|
while (false);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:generateDBLockInst
|
|
/// 函式說明:產生Lock資料並填入資料
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="i_sTableName">欲鎖定資料表名稱</param>
|
|
/// <param name="i_sRowID">欲鎖定資料ROWID</param>
|
|
/// <returns>Lock資料</returns>
|
|
/// <summary>
|
|
protected tb_sys_dblock generateDBLockInst(CRequestMessage i_crmInput, string i_sTableName)
|
|
{
|
|
string sDept_id = OriRequest.customparam[BLWording.DEPT_ID].ToString();
|
|
string sUser_id = OriRequest.customparam[BLWording.USER_ID].ToString();
|
|
string sProgram_id = GetRequestProgramID(i_crmInput);
|
|
DateTime dtNow = DateTime.Now;
|
|
|
|
tb_sys_dblock dblRes = null;
|
|
|
|
if (i_crmInput != null)
|
|
{
|
|
dblRes = new tb_sys_dblock()
|
|
{
|
|
uid = i_crmInput.token,
|
|
program_uid = sProgram_id,
|
|
table_uid = i_sTableName,
|
|
update_org_uid = sDept_id,
|
|
create_org_uid = sDept_id,
|
|
create_user_uid = sUser_id,
|
|
update_user_uid = sUser_id,
|
|
update_date = dtNow,
|
|
create_date = dtNow
|
|
};
|
|
}
|
|
return dblRes;
|
|
}
|
|
|
|
protected static Dictionary<string, List<checkDBLock>> getDBLockCheck(object i_oData, string i_sLockTableName, Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null)
|
|
{
|
|
string sUpdateColumnName = tb_sys_dblock.CN_UPDATE_DATE;
|
|
Dictionary<string, List<checkDBLock>> dicCheck = new Dictionary<string, List<checkDBLock>>();
|
|
|
|
if (i_oData is JArray ja)
|
|
{
|
|
if (i_dicTableColumnMapping != null && i_dicTableColumnMapping.ContainsKey(i_sLockTableName))
|
|
{
|
|
Dictionary<string, string> dicColumnMapping = i_dicTableColumnMapping[i_sLockTableName];
|
|
if (dicColumnMapping != null && dicColumnMapping.ContainsKey(tb_sys_dblock.CN_UPDATE_DATE))
|
|
{
|
|
sUpdateColumnName = dicColumnMapping[tb_sys_dblock.CN_UPDATE_DATE];
|
|
}
|
|
}
|
|
|
|
List<checkDBLock> lch = new List<checkDBLock>();
|
|
foreach (JToken joData in ja)
|
|
{
|
|
string sRowID = joData[BLWording.WHEREDATA][tb_sys_dblock.CN_UID].ToString();
|
|
DateTime dtUpdateTime = DateTime.Parse(joData[BLWording.WHEREDATA][sUpdateColumnName].ToString(), CultureInfo.CurrentCulture);
|
|
lch.Add(new checkDBLock() { RowID = sRowID, UpdateTime = dtUpdateTime });
|
|
}
|
|
|
|
dicCheck.Add(i_sLockTableName, lch);
|
|
}
|
|
return dicCheck;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:deleteOneTableRows
|
|
/// 函式說明:單筆資料刪除
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <typeparam name="T">資料表型態</typeparam>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="i_sLockTableName">資料表名稱</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <summary>
|
|
protected CResponseMessage deleteOneTableRows<T>(CRequestMessage i_crmInput, string i_sLockTableName, Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null) where T : EntityBase, new()
|
|
{
|
|
return deleteOneTableRowsHandler<T>(i_crmInput, BLWording.DEL_MASTER, i_sLockTableName, i_dicTableColumnMapping);
|
|
}
|
|
|
|
protected CResponseMessage deleteOneTableRowsDetail<T>(CRequestMessage i_crmInput, string i_sLockTableName, Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null) where T : EntityBase, new()
|
|
{
|
|
return deleteOneTableRowsHandler<T>(i_crmInput, BLWording.DEL_DETAIL, i_sLockTableName, i_dicTableColumnMapping);
|
|
}
|
|
|
|
protected CResponseMessage deleteOneTableRowsHandler<T>(CRequestMessage i_crmInput, string i_Key, string i_sLockTableName, Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null) where T : EntityBase, new()
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crmRes = null;
|
|
|
|
do
|
|
{
|
|
if (i_crmInput == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
DBLockCleanExpire(i_crmInput);
|
|
|
|
Type tType = typeof(T);
|
|
Dictionary<string, List<checkDBLock>> dicCheck;
|
|
dicCheck = getDBLockCheck(i_crmInput.param[i_Key], i_sLockTableName, i_dicTableColumnMapping);
|
|
List<string> lsDeletRowIDs = new List<string>();
|
|
|
|
List<checkDBLock> lc = dicCheck[i_sLockTableName];
|
|
foreach (checkDBLock c in lc)
|
|
{
|
|
lsDeletRowIDs.Add(c.RowID);
|
|
}
|
|
|
|
List<Command> lCmds = new List<Command>
|
|
{
|
|
Command.SetupDeleteCmd(
|
|
new WhereNode(tb_sys_dblock.CN_UID, WhereNode.EColumnOperation.EOT_IN, tType, lsDeletRowIDs.ToArray())),
|
|
|
|
// Remove DBLock
|
|
generateDBUnlockCmdAll(i_crmInput)
|
|
};
|
|
|
|
crmRes = checkDBLockAndRun(i_crmInput, dicCheck, lCmds, i_dicTableColumnMapping);
|
|
|
|
}
|
|
while (false);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:lockOneTableOneRow
|
|
/// 函式說明:單筆資料鎖定
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="i_sLockTableName">鎖定資料表名稱</param>
|
|
/// <param name="i_lsExceptTables">若為非null，</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <summary>
|
|
protected CResponseMessage lockOneTableOneRow(CRequestMessage i_crmInput, string i_key, string i_sLockTableName, bool i_bUnlock = false, List<string> i_lsExceptTables = null,
|
|
Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null)
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crmRes = null;
|
|
QueryResponse qrRes = null;
|
|
|
|
do
|
|
{
|
|
if (i_crmInput == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
DBLockCleanExpire(i_crmInput);
|
|
|
|
if (i_bUnlock)
|
|
{
|
|
string sUpdateColumnName = (i_dicTableColumnMapping == null) ? tb_sys_dblock.CN_UPDATE_DATE : i_dicTableColumnMapping[i_sLockTableName][tb_sys_dblock.CN_UPDATE_DATE].ToString(CultureInfo.CurrentCulture);
|
|
|
|
Command cPrevDBLock_Data = Command.SetupSelectCmd(new tb_sys_dblock(), new tb_sys_dblock() { uid = i_crmInput.token, table_uid = i_sLockTableName });
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cPrevDBLock_Data, GetDefaultSystemColumnInfo());
|
|
tb_sys_dblock oDBLock = ai.RunQuerySingleORM<tb_sys_dblock>(cPrevDBLock_Data);
|
|
ai.RunEditSingleCmd(generateDBUnlockCmdExceptTable(i_crmInput, i_lsExceptTables));
|
|
if (oDBLock != null)
|
|
{
|
|
QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
|
|
Dictionary<string, string> dicCondition = new Dictionary<string, string>();
|
|
QueryJsonElement qjeRowIDs = lBlocks.GetInst();
|
|
qjeRowIDs.table = i_sLockTableName;
|
|
|
|
qjeRowIDs.displaycols = new List<string>() {
|
|
tb_sys_dblock.CN_UID,
|
|
sUpdateColumnName
|
|
};
|
|
dicCondition.Add(tb_sys_dblock.CN_UID, oDBLock.uid);
|
|
qjeRowIDs.dicwherecols = dicCondition;
|
|
lBlocks.Add(qjeRowIDs);
|
|
|
|
sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cSelect);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
cSelect.UseSQLTrim = true;
|
|
cSelect.NeedCount = false;
|
|
|
|
QueryDataSet qdsRes = ai.RunQueryDataSet(cSelect);
|
|
qrRes = new QueryResponse(qdsRes);
|
|
}
|
|
}
|
|
|
|
if (!i_crmInput.param.ContainsKey(i_key))
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //找無詞彙代碼
|
|
break;
|
|
}
|
|
|
|
List<checkDBLock> lcChecks = getCheckDBLock(i_crmInput, i_key,
|
|
(i_dicTableColumnMapping != null && i_dicTableColumnMapping.ContainsKey(i_sLockTableName)) ? i_dicTableColumnMapping[i_sLockTableName] : null);
|
|
|
|
List<Command> lRunCmds = new List<Command>() { };
|
|
|
|
Command cInsert = Command.SetupInsertCmd(generateDBLockInst(i_crmInput, i_sLockTableName));
|
|
|
|
lRunCmds.Add(cInsert);
|
|
Dictionary<string, List<checkDBLock>> dictionary = new Dictionary<string, List<checkDBLock>>
|
|
{
|
|
{ i_sLockTableName, lcChecks }
|
|
};
|
|
Dictionary<string, List<checkDBLock>> dicCheck = dictionary;
|
|
|
|
crmRes = checkDBLockAndRun(i_crmInput, dicCheck, lRunCmds, i_dicTableColumnMapping);
|
|
if (qrRes != null)
|
|
{
|
|
crmRes.param.Add(BLWording.RECORD, qrRes);
|
|
if (i_dicTableColumnMapping != null)
|
|
{
|
|
crmRes.param.Add(BLWording.UPDATETYPE, i_dicTableColumnMapping[i_sLockTableName]);
|
|
}
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:generateDBUnlockCmdExceptTable
|
|
/// 函式說明:產生排除部分資料表的解鎖命令(常用於DBLockDetail)
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="i_lsNotRemovedTables">特例不刪除的資料表</param>
|
|
/// <returns>命令</returns>
|
|
/// <summary>
|
|
protected static Command generateDBUnlockCmdExceptTable(CRequestMessage i_crmInput, List<string> i_lsNotRemovedTables = null)
|
|
{
|
|
Command cRes = null;
|
|
if (i_crmInput != null)
|
|
{
|
|
WhereNode wnToken = new WhereNode(tb_sys_dblock.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), i_crmInput.token);
|
|
|
|
WhereNode wnConidtion;
|
|
if (i_lsNotRemovedTables == null)
|
|
{
|
|
wnConidtion = wnToken;
|
|
}
|
|
else
|
|
{
|
|
List<WhereNode> lwnExcept = new List<WhereNode>();
|
|
|
|
foreach (string sTableName in i_lsNotRemovedTables)
|
|
{
|
|
WhereNode wnExceptOneTableTableName = new WhereNode(tb_sys_dblock.CN_TABLE_UID, WhereNode.EColumnOperation.EOT_NEQ, typeof(tb_sys_dblock), sTableName);
|
|
lwnExcept.Add(wnExceptOneTableTableName);
|
|
}
|
|
|
|
WhereNode wnExceptAll = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnExcept.ToArray());
|
|
|
|
wnConidtion = new WhereNode(WhereNode.ENodeOperation.ENO_AND, wnToken, wnExceptAll);
|
|
}
|
|
|
|
cRes = Command.SetupDeleteCmd(wnConidtion);
|
|
}
|
|
return cRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:generateDBUnlockCmdAll
|
|
/// 函式說明:產生資料表的解鎖命令
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <returns>命令</returns>
|
|
/// <summary>
|
|
/// <summary>
|
|
protected static Command generateDBUnlockCmdAll(CRequestMessage i_crmInput)
|
|
{
|
|
return i_crmInput != null ? Command.SetupDeleteCmd(new WhereNode(tb_sys_dblock.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), i_crmInput.token)) : null;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:checkDBLockAndRun
|
|
/// 函式說明:檢核，通過即執行命令。確保所有動作於同一Transaction
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput">前端Request</param>
|
|
/// <param name="i_dicChecks">所有檢核資料</param>
|
|
/// <param name="i_lcCmds">檢核通過要執行的命令</param>
|
|
/// <returns>執行結果</returns>
|
|
/// <summary>
|
|
protected CResponseMessage checkDBLockAndRun(CRequestMessage i_crmInput, Dictionary<string, List<checkDBLock>> i_dicChecks, List<Command> i_lcCmds,
|
|
Dictionary<string, Dictionary<string, string>> i_dicTableColumnMapping = null
|
|
)
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crmRes = null;
|
|
|
|
ArsenalInterface.ManualTrans mt = GetManualTrans<tb_sys_dblock>();
|
|
|
|
do
|
|
{
|
|
|
|
if (i_dicChecks == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
// Check UpdateTime避免資料已被修改過
|
|
foreach (string sLockTableName in i_dicChecks.Keys)
|
|
{
|
|
QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
|
|
|
|
QueryJsonElement qjeCheckUpdateTime = lBlocks.GetInst();
|
|
qjeCheckUpdateTime.table = sLockTableName;
|
|
|
|
qjeCheckUpdateTime.displaycols = new List<string>() {
|
|
QueryJsonElement.COUNT(tb_sys_dblock.CN_UID)
|
|
};
|
|
|
|
List<WhereNode> lwn = new List<WhereNode>();
|
|
|
|
List<checkDBLock> lch = i_dicChecks[sLockTableName];
|
|
|
|
foreach (checkDBLock ch in lch)
|
|
{
|
|
if (ch.UpdateTime != null)
|
|
{
|
|
WhereNode wnUpdateDate = null;
|
|
|
|
if (i_dicTableColumnMapping != null && i_dicTableColumnMapping.ContainsKey(sLockTableName) &&
|
|
i_dicTableColumnMapping[sLockTableName].ContainsKey(tb_sys_dblock.CN_UPDATE_DATE)
|
|
)
|
|
{
|
|
wnUpdateDate = new WhereNode(i_dicTableColumnMapping[sLockTableName][tb_sys_dblock.CN_UPDATE_DATE],
|
|
WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), ch.UpdateTime);
|
|
}
|
|
else
|
|
{
|
|
wnUpdateDate = new WhereNode(tb_sys_dblock.CN_UPDATE_DATE, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), ch.UpdateTime);
|
|
}
|
|
|
|
lwn.Add(new WhereNode(WhereNode.ENodeOperation.ENO_AND,
|
|
new WhereNode(tb_sys_dblock.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), ch.RowID),
|
|
wnUpdateDate)
|
|
);
|
|
}
|
|
}
|
|
|
|
if (lwn.Count == 0)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
qjeCheckUpdateTime.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_OR, lwn.ToArray());
|
|
lBlocks.Add(qjeCheckUpdateTime);
|
|
|
|
sMsg = MakeSelectJoinByBlocks(lBlocks, out Command cSelect);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect, GetDefaultSystemColumnInfo());
|
|
QueryDataSet qdsUpdateCount = ai.RunQueryDataSet(cSelect, mt);
|
|
|
|
sMsg = DBService.GetLastErrorCode(cSelect);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
object oCount = QueryJsonElement.GetFirstValue(qdsUpdateCount);
|
|
|
|
if (oCount == null || Convert.ToInt32(oCount, CultureInfo.CurrentCulture) != lch.Count)
|
|
{
|
|
sMsg = MessageWording.DBLOCK_DATACHANGED;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
// 檢查&上鎖
|
|
QueryJsonElementCollection lBlocksCheckAndLock = new QueryJsonElementCollection();
|
|
QueryJsonElement qjeRowIDs = lBlocksCheckAndLock.GetInst();
|
|
qjeRowIDs.table = tb_sys_dblock.TABLENAME;
|
|
|
|
qjeRowIDs.displaycols = new List<string>() {
|
|
tb_sys_dblock.CN_UID
|
|
};
|
|
|
|
List<WhereNode> lwnCheckAndLock = new List<WhereNode>();
|
|
foreach (string sLockTableName in i_dicChecks.Keys)
|
|
{
|
|
List<checkDBLock> lch = i_dicChecks[sLockTableName];
|
|
|
|
if (lch.Count == 0)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
foreach (checkDBLock ch in lch)
|
|
{
|
|
lwnCheckAndLock.Add(new WhereNode(WhereNode.ENodeOperation.ENO_AND,
|
|
new WhereNode(tb_sys_dblock.CN_TABLE_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), sLockTableName),
|
|
new WhereNode(tb_sys_dblock.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_dblock), ch.RowID)
|
|
));
|
|
}
|
|
}
|
|
|
|
qjeRowIDs.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_OR, lwnCheckAndLock.ToArray());
|
|
lBlocksCheckAndLock.Add(qjeRowIDs);
|
|
|
|
sMsg = MakeSelectJoinByBlocks(lBlocksCheckAndLock, out Command cSelectCheckAndLock);
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
cSelectCheckAndLock.UseUpdateFor = true;
|
|
|
|
QueryDataSet qdsUpdateCountCheckAndLock = mt.aiDB.RunQueryDataSet(cSelectCheckAndLock, mt);
|
|
|
|
sMsg = GetLastErrorCode(cSelectCheckAndLock);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (qdsUpdateCountCheckAndLock.Total != 0)
|
|
{
|
|
sMsg = MessageWording.DBLOCK_ALREADYLOCKED;
|
|
break;
|
|
}
|
|
|
|
mt.aiDB.RunEditCmds(i_lcCmds, mt);
|
|
|
|
sMsg = GetLastErrorCode(i_lcCmds);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
|
|
}
|
|
while (false);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
mt.aiDB.Rollback(mt);
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
else
|
|
{
|
|
mt.aiDB.Commit(mt);
|
|
}
|
|
|
|
mt.aiDB.CloseManualTrans(mt);
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getCheckDBLocks
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput"></param>
|
|
/// <param name="i_key"></param>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
|
|
protected static List<checkDBLock> getCheckDBLocks(CRequestMessage i_crmInput, string i_key)
|
|
{
|
|
List<checkDBLock> lchdbLock = new List<checkDBLock>();
|
|
|
|
if (i_crmInput != null && i_key != null)
|
|
{
|
|
JArray ja = i_crmInput.param[i_key] as JArray;
|
|
|
|
foreach (JToken joDataRaw in ja)
|
|
{
|
|
Dictionary<string, JToken> dictionary = joDataRaw.ToObject<Dictionary<string, JToken>>();
|
|
|
|
JToken joData;
|
|
if (dictionary.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
joData = joDataRaw[BLWording.WHEREDATA];
|
|
}
|
|
else
|
|
{
|
|
joData = joDataRaw;
|
|
}
|
|
|
|
string sRowID = joData[tb_sys_dblock.CN_UID].ToString();
|
|
DateTime dtUpdateTime = DateTime.Parse(joData[tb_sys_dblock.CN_UPDATE_DATE].ToString(), CultureInfo.CurrentCulture);
|
|
|
|
lchdbLock.Add(new checkDBLock() { RowID = sRowID, UpdateTime = dtUpdateTime });
|
|
}
|
|
}
|
|
return lchdbLock;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getOneCheckDBLock
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_crmInput"></param>
|
|
/// <param name="i_key"></param>
|
|
/// <param name="i_dicColumnMapping">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
protected static List<checkDBLock> getCheckDBLock(CRequestMessage i_crmInput, string i_key, Dictionary<string, string> i_dicColumnMapping = null)
|
|
{
|
|
List<checkDBLock> lcRes = new List<checkDBLock>();
|
|
|
|
if (i_crmInput != null && i_key != null && i_crmInput.param != null)
|
|
{
|
|
JArray ja = i_crmInput.param[i_key] as JArray;
|
|
|
|
foreach (JToken joDataRaw in ja)
|
|
{
|
|
Dictionary<string, JToken> dictionary = joDataRaw.ToObject<Dictionary<string, JToken>>();
|
|
|
|
JToken joData;
|
|
if (dictionary.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
joData = joDataRaw[BLWording.WHEREDATA];
|
|
}
|
|
else
|
|
{
|
|
joData = joDataRaw;
|
|
}
|
|
|
|
string sRowID = joData[tb_sys_dblock.CN_UID].ToString();
|
|
string sUpdateColumnName = tb_sys_dblock.CN_UPDATE_DATE;
|
|
|
|
if (i_dicColumnMapping != null && i_dicColumnMapping.ContainsKey(tb_sys_dblock.CN_UPDATE_DATE))
|
|
{
|
|
sUpdateColumnName = i_dicColumnMapping[tb_sys_dblock.CN_UPDATE_DATE];
|
|
}
|
|
|
|
DateTime dtUpdateTime = DateTime.Parse(joData[sUpdateColumnName].ToString(), CultureInfo.CurrentCulture);
|
|
lcRes.Add(new checkDBLock() { RowID = sRowID, UpdateTime = dtUpdateTime });
|
|
}
|
|
}
|
|
return lcRes;
|
|
}
|
|
|
|
#endregion DBLOCK
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetManualTrans
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <returns></returns>
|
|
/// <summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <returns></returns>
|
|
public ArsenalInterface.ManualTrans GetManualTrans<T>() where T : EntityBase, new()
|
|
{
|
|
ArsenalInterface.ManualTrans mtRes;
|
|
|
|
ArsenalInterface.ManualTrans mtTemp;
|
|
Command command = Command.SetupSelectCmd(new T());
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(command, GetDefaultSystemColumnInfo());
|
|
mtTemp = ai.GetManualTrans(command);
|
|
mtTemp.aiDB = ai;
|
|
mtTemp.Command = command;
|
|
mtRes = mtTemp;
|
|
|
|
return mtRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:EntityToDic
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_eb">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static Dictionary<string, object> EntityToDic(EntityBase i_eb)
|
|
{
|
|
Dictionary<string, object> dicRes = new Dictionary<string, object>();
|
|
|
|
if (i_eb != null)
|
|
{
|
|
Type t = i_eb.GetType();
|
|
|
|
foreach (PropertyInfo pi in t.GetProperties())
|
|
{
|
|
dicRes.Add(pi.Name, pi.GetValue(i_eb, null));
|
|
}
|
|
}
|
|
return dicRes;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetFileUploadInfo
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="參數名稱">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public override FileUploadInfo GetFileUploadInfo()
|
|
{
|
|
return new FileUploadInfo()
|
|
{
|
|
Logger = Logger,
|
|
RootPath = RootPath
|
|
};
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetRealPropertyName
|
|
/// 函式說明: 由ORM定義的CN_名稱 Ex. label_no.CN_LABEL_NO反譯 實際屬性名稱
|
|
/// 起始作者: Hercules
|
|
/// 起始日期: 2018/02/02
|
|
/// 最新修改人: Hercules
|
|
/// 最新修改日: 2018/02/02
|
|
/// </summary>
|
|
/// <param name="i_t">View Model Type</param>
|
|
/// <param name="i_sName">欄位名稱</param>
|
|
/// <returns></returns>
|
|
public static string GetRealPropertyName(Type i_t, string i_sName)
|
|
{
|
|
string sName = null;
|
|
if (i_t != null)
|
|
{
|
|
PropertyInfo[] pis = i_t.GetProperties();
|
|
|
|
foreach (PropertyInfo pi in pis)
|
|
{
|
|
ColumnMiscAttribute cma = EntityUtil.GetPropertyAttribute<ColumnMiscAttribute>(pi);
|
|
if (cma == null || cma.Data == null)
|
|
{
|
|
continue;
|
|
}
|
|
if (cma.Data.COLUMN_NAME == i_sName)
|
|
{
|
|
sName = pi.Name;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
return sName;
|
|
}
|
|
|
|
protected static List<Dictionary<string, object>> getQryParameterList(CRequestMessage i_crmInput)
|
|
{
|
|
return i_crmInput != null && i_crmInput.param != null && i_crmInput.param.ContainsKey(BLWording.QRY_MASTER) ? getParameterList(i_crmInput.param[BLWording.QRY_MASTER]) : null;
|
|
}
|
|
|
|
protected static List<Dictionary<string, object>> getParameterList(object i_dicData)
|
|
{
|
|
List<Dictionary<string, object>> lDicRes = new List<Dictionary<string, object>>();
|
|
|
|
if (i_dicData is JArray)
|
|
{
|
|
JArray jaData = i_dicData as JArray;
|
|
foreach (JToken jo in jaData)
|
|
{
|
|
lDicRes.Add(jo.ToObject<Dictionary<string, object>>());
|
|
}
|
|
}
|
|
return lDicRes;
|
|
}
|
|
|
|
#region getQryListCommand
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getQryListCommand
|
|
/// 函式說明:獲取批次查詢Command
|
|
/// 起始作者:Justin
|
|
/// 起始日期:2016/10/05
|
|
/// 最新修改人:Justin
|
|
/// 最新修改日:2016/10/05
|
|
/// </summary>
|
|
/// <param name="i_jaDataArray"></param>
|
|
/// <param name="i_sTbName"></param>
|
|
/// <param name="o_sMsg"></param>
|
|
/// <param name="i_ordercols"></param>
|
|
/// <param name="i_aliascols"></param>
|
|
/// <param name="i_distinct">
|
|
/// 參數說明i_crm(Object):所需查詢資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lcCmds(Object)：新增狀態相關訊息
|
|
/// </returns>
|
|
protected static List<Command> getQryListCommand(
|
|
JArray i_jaDataArray, string i_sTbName, out string o_sMsg, Dictionary<string, List<string>> i_aliascols = null, bool i_distinct = false,
|
|
bool i_bUseFullSelect = false, List<string> i_lColumnName = null, EColumnFilter i_ecfFilter = EColumnFilter.ES_NO_CREATE)
|
|
{
|
|
string sMsg = null;
|
|
|
|
List<Command> lcCmds = new List<Command>();
|
|
|
|
foreach (JObject jtDataItem in i_jaDataArray.OfType<JObject>())
|
|
{
|
|
sMsg = getEntity(i_sTbName, out object tbData); //傳出來一個物件
|
|
EntityBase ebSelect = tbData as EntityBase;
|
|
|
|
if (i_bUseFullSelect)
|
|
{
|
|
ebSelect.SetFullDirtyEx(i_ecfFilter);
|
|
}
|
|
else
|
|
{
|
|
if (i_lColumnName != null)
|
|
{
|
|
ebSelect.SetDirty(i_lColumnName.ToArray());
|
|
}
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
Dictionary<string, string> dicCond = null;
|
|
if (jtDataItem.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
dicCond = jtDataItem[BLWording.WHEREDATA].ToObject<Dictionary<string, string>>();
|
|
}
|
|
|
|
Dictionary<string, string> dicFullTextCond = null;
|
|
if (jtDataItem.ContainsKey(BLWording.FULLTEXT_PATTERN))
|
|
{
|
|
string sFullTextPattern = jtDataItem[BLWording.FULLTEXT_PATTERN].ToString().Trim();
|
|
|
|
if (sFullTextPattern.Length > 0)
|
|
{
|
|
dicFullTextCond = new Dictionary<string, string>();
|
|
foreach (string sKey in ebSelect.Dirty)
|
|
{
|
|
if (!dicFullTextCond.ContainsKey(sKey))
|
|
{
|
|
dicFullTextCond[sKey] = $"{sFullTextPattern}";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
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;
|
|
}
|
|
else
|
|
{
|
|
PropertyInfo pi = ebSelect.GetType().GetProperties().FirstOrDefault(f => f.Name == BLWording.CREATE_DATE);
|
|
|
|
if (pi != null)
|
|
{
|
|
ordercols = new List<Tuple<string, string>>() { new Tuple<string, string>(BLWording.CREATE_DATE, BLWording.ORDER_DESC) };
|
|
}
|
|
}
|
|
|
|
Command c = Command.SetupSelectCmdByParam(ebSelect, dicCond, ordercols, i_aliascols, i_distinct, i_dicFullTextSearch: dicFullTextCond);
|
|
c.NeedCount = true;
|
|
lcCmds.Add(c);
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lcCmds;
|
|
}
|
|
|
|
#endregion getQryListCommand
|
|
|
|
/// <summary>
|
|
/// For Create/Copy Assignment
|
|
/// </summary>
|
|
/// <param name="ebInsert"></param>
|
|
/// <param name="jdata"></param>
|
|
/// <param name="i_dicAddData"></param>
|
|
/// <param name="i_saNotNullChecks"></param>
|
|
/// <returns></returns>
|
|
protected static string valueAssignment(CRequestMessage i_crmInput, EntityBase ebInsert, JObject jdata, out List<Command> o_lcCmds, Dictionary<string, string> i_dicAddData = null, bool i_bIsCreate = false)
|
|
{
|
|
string sMsg = null;
|
|
List<Command> lcCmds = null;
|
|
|
|
|
|
do
|
|
{
|
|
if (i_crmInput == null || ebInsert == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
List<Command> lcCmdsTemp = new List<Command>();
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
|
|
IDictionary<string, JToken> dicDataProperty = jdata;
|
|
|
|
if (dicDataProperty == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
PropertyInfo[] pis = ebInsert.GetType().GetProperties();
|
|
|
|
foreach (JProperty property in jdata.Properties())
|
|
{
|
|
JToken jv = property.Value;
|
|
|
|
if (!pis.Any(f => f.Name == property.Name))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (jv is JArray jaAllUploads)
|
|
{
|
|
string sFolderPath = generateUploadFolderPath("files");
|
|
StringBuilder sbUIDs = new StringBuilder();
|
|
|
|
Dictionary<string, object> dicUpload = jaAllUploads[0].ToObject<Dictionary<string, object>>();
|
|
|
|
UploadFiles uf = new UploadFiles()
|
|
{
|
|
UploadName = dicUpload[BLWording.NAME].ToString(),
|
|
};
|
|
|
|
string sBase64 = (string)dicUpload[BLWording.BSTRING];
|
|
int nIdx = sBase64.IndexOf(',', StringComparison.OrdinalIgnoreCase);
|
|
uf.FileMeta = sBase64.Substring(0, nIdx);
|
|
|
|
byte[] bRawData = Convert.FromBase64String(sBase64.Substring(nIdx + 1));
|
|
|
|
string sSubFolderPath = Path.Combine(sFolderPath,
|
|
(i_crmInput.customparam[BLWording.SESSION_USER] as tb_sys_session).update_user_uid);
|
|
|
|
if (!Directory.Exists(sSubFolderPath))
|
|
{
|
|
Directory.CreateDirectory(sSubFolderPath);
|
|
}
|
|
|
|
string sFilePath = Path.Combine(sSubFolderPath,
|
|
string.Format(CultureInfo.CurrentCulture, "{0}{1}", DateTime.Now.ToString("yyyyMMddHHMMssfff", CultureInfo.CurrentCulture), Path.GetExtension(dicUpload[BLWording.NAME].ToString())));
|
|
|
|
System.IO.File.WriteAllBytes(sFilePath, bRawData);
|
|
|
|
uf.FilePath = sFilePath;
|
|
|
|
string sUid = Guid.NewGuid().ToString();
|
|
|
|
lcCmdsTemp.Add(Command.SetupInsertCmd(new tb_sys_uploadlog()
|
|
{
|
|
uid = sUid,
|
|
module = i_crmInput.module,
|
|
name = dicUpload[BLWording.NAME].ToString(),
|
|
path = sFilePath.Substring(RootPath.Length)
|
|
}));
|
|
|
|
if (sbUIDs.Length == 0)
|
|
{
|
|
sbUIDs.Append(sUid);
|
|
}
|
|
else
|
|
{
|
|
sbUIDs.Append($";{sUid}");
|
|
}
|
|
|
|
if (sbUIDs.Length != 0)
|
|
{
|
|
dicInput.Add(property.Name, sbUIDs.ToString()); // for exception
|
|
}
|
|
}
|
|
else
|
|
{
|
|
dicInput.Add(property.Name, jv.Value<string>());
|
|
}
|
|
}
|
|
|
|
if (i_dicAddData != null)
|
|
{
|
|
foreach (string sKey in i_dicAddData.Keys)
|
|
{
|
|
if (dicInput.ContainsKey(sKey))
|
|
{
|
|
dicInput[sKey] = i_dicAddData[sKey];
|
|
}
|
|
else
|
|
{
|
|
dicInput.Add(sKey, i_dicAddData[sKey]);
|
|
}
|
|
}
|
|
}
|
|
|
|
// Foolproof
|
|
if (i_bIsCreate)
|
|
{
|
|
if (dicInput.ContainsKey(BLWording.UID))
|
|
{
|
|
if (dicInput[BLWording.UID].ToString() == BLWording.CREATEUID)
|
|
{
|
|
dicInput[BLWording.UID] = Guid.NewGuid().ToString();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
dicInput.Add(BLWording.UID, Guid.NewGuid().ToString());
|
|
}
|
|
}
|
|
|
|
if (i_bIsCreate &&
|
|
dicInput.ContainsKey(BLWording.UID) &&
|
|
dicInput[BLWording.UID].ToString() == BLWording.CREATEUID)
|
|
{
|
|
dicInput.Remove(BLWording.UID);
|
|
}
|
|
|
|
Dictionary<string, string> dicFillRes = ebInsert.FillData(dicInput, EFillDataPolicy.EFDP_OVERRIDE);
|
|
|
|
if (0 != dicFillRes.Count)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
lcCmds = lcCmdsTemp;
|
|
}
|
|
while (false);
|
|
|
|
o_lcCmds = lcCmds;
|
|
return sMsg;
|
|
}
|
|
|
|
#region getAddListCommand
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getAddListCommand
|
|
/// 函式說明:獲取批次新增Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新修改日:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_oAllItems"></param>
|
|
/// <param name="i_sTbName"></param>
|
|
/// <param name="o_sMsg"></param>
|
|
/// <param name="i_saNotNullChecks">
|
|
/// 參數說明i_crm(Object):所需新增資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lcCmds(Object)：新增狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getAddListCommand(CRequestMessage i_crmInput, object i_oAllItems, string i_sTbName, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
List<Command> lcCmds = new List<Command>();
|
|
if (i_oAllItems is JArray jaItems)
|
|
{
|
|
foreach (JToken jtkItem in jaItems)
|
|
{
|
|
Dictionary<string, object> dicItem = jtkItem.ToObject<Dictionary<string, object>>();
|
|
|
|
if (dicItem == null)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
sMsg = getEntity(i_sTbName, out object oInsert);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
EntityBase ebInsert = oInsert as EntityBase;
|
|
sMsg = valueAssignment(i_crmInput, ebInsert, dicItem[BLWording.DATA] as JObject, out List<Command> o_lcAddInsert, i_bIsCreate: true);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (ldgModifyCreateInstance.Any())
|
|
{
|
|
foreach (DG_ModifyCreateInstance mci in ldgModifyCreateInstance)
|
|
{
|
|
sMsg = mci(i_crmInput, ref oInsert);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
}
|
|
|
|
// Log
|
|
sMsg = getManualLog(i_crmInput, dicItem, BLWording.LOG_ACTION_NAME_INSERTSQL, out Command cLog);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (cLog != null)
|
|
{
|
|
lcCmds.Add(cLog);
|
|
}
|
|
|
|
|
|
if (o_lcAddInsert != null && o_lcAddInsert.Count > 0)
|
|
{
|
|
lcCmds.AddRange(o_lcAddInsert);
|
|
}
|
|
|
|
Command c = Command.SetupInsertCmd(ebInsert);
|
|
lcCmds.Add(c);
|
|
}
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lcCmds;
|
|
}
|
|
|
|
#endregion getAddListCommand
|
|
|
|
#region getUpdListCommand
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getUpdListCommand
|
|
/// 函式說明:獲取批次修改Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新修改日:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_oItems"></param>
|
|
/// <param name="i_sTbName"></param>
|
|
/// <param name="i_saUpdKeys"></param>
|
|
/// <param name="o_sMsg"></param>
|
|
/// <param name="i_saIgronColumn"></param>
|
|
/// <param name="i_bRemoveUpdate">
|
|
/// 參數說明i_crm(Object):所需修改資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lcCmds(Object)：修改狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getUpdListCommand(CRequestMessage i_crmInput, object i_oItems, string i_sTbName, List<string> i_saUpdKeys, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
|
|
List<Command> lcCmds = new List<Command>();
|
|
if (i_oItems is JArray)
|
|
{
|
|
ArsenalInterface ai = null;
|
|
JArray jaItems = i_oItems as JArray;
|
|
foreach (JToken jtkItem in jaItems)
|
|
{
|
|
Dictionary<string, object> dicItem = jtkItem.ToObject<Dictionary<string, object>>();
|
|
|
|
if (!dicItem.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
sMsg = getEntity(i_sTbName, out object oData);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
Type tRun = oData.GetType();
|
|
EntityBase ebData = oData as EntityBase;
|
|
sMsg = valueAssignment(i_crmInput, ebData, dicItem[BLWording.DATA] as JObject, out List<Command> o_lcAddInsert);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (o_lcAddInsert != null && o_lcAddInsert.Count > 0)
|
|
{
|
|
lcCmds.AddRange(o_lcAddInsert);
|
|
}
|
|
|
|
sMsg = getManualLog(i_crmInput, dicItem, BLWording.LOG_ACTION_NAME_UPDATESQL, out Command cLog);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (cLog != null)
|
|
{
|
|
lcCmds.Add(cLog);
|
|
}
|
|
|
|
sMsg = generateWhereNode(dicItem, i_saUpdKeys, tRun, out WhereNode wnWhere);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
Command c = Command.SetupUpdateCmd(ebData, wnWhere);
|
|
lcCmds.Add(c);
|
|
|
|
if (ldgUpdate_PostCommand.Any())
|
|
{
|
|
generateWhereNode(dicItem, i_saUpdKeys, tRun, out WhereNode wnWherePost);
|
|
|
|
sMsg = getEntity(i_sTbName, out object oDataPost);
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
EntityBase ebDataPost = oDataPost as EntityBase;
|
|
ebDataPost.SetFullDirty();
|
|
|
|
Command cSelectPost = Command.SetupSelectCmd(ebDataPost, wnWherePost);
|
|
|
|
if (ai == null)
|
|
{
|
|
ai = ArsenalDBMgr.GetInst(cSelectPost);
|
|
}
|
|
|
|
List<object> loOldInsts = ai.RunQueryList(tRun, cSelectPost);
|
|
|
|
loOldInsts.ForEach(oInstOld =>
|
|
{
|
|
foreach (DG_EditPostCommand epc in ldgUpdate_PostCommand)
|
|
{
|
|
if (epc == null)
|
|
{
|
|
continue;
|
|
}
|
|
sMsg = epc(ai, oInstOld as EntityBase, ebData, out List<Command> o_lcAddition);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
lcCmds.AddRange(o_lcAddition);
|
|
}
|
|
});
|
|
}
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lcCmds;
|
|
}
|
|
|
|
protected string generateWhereNode(Dictionary<string, object> dicItem,
|
|
List<string> i_saUpdKeys, Type tRun, out WhereNode o_wnResult,
|
|
[System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
|
|
[System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "",
|
|
[System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0)
|
|
{
|
|
string sMsg = null;
|
|
//bool bIsHasPK = true;
|
|
WhereNode wnTemp = null;
|
|
try
|
|
{
|
|
do
|
|
{
|
|
if (dicItem == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
Dictionary<string, object> dicWhere = new Dictionary<string, object>();
|
|
JObject jwheredata = dicItem[BLWording.WHEREDATA] as JObject;
|
|
if (null != JProperty2Dic(jwheredata, ref dicWhere))
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
List<WhereNode> lwnWhere = new List<WhereNode>();
|
|
|
|
int nContainKeyCount = 0;
|
|
if (i_saUpdKeys != null)
|
|
{
|
|
foreach (string sKey in dicWhere.Keys)
|
|
{
|
|
if (i_saUpdKeys.Contains(sKey))
|
|
{
|
|
nContainKeyCount++;
|
|
}
|
|
WhereNode wn = new WhereNode(sKey, WhereNode.EColumnOperation.EOT_EQ, tRun, dicWhere[sKey]);
|
|
lwnWhere.Add(wn);
|
|
}
|
|
|
|
/*foreach (string sKey in i_saUpdKeys)
|
|
{
|
|
if (!dicWhere.ContainsKey(sKey))
|
|
{
|
|
bIsHasPK = false;
|
|
break;
|
|
}
|
|
if (string.IsNullOrWhiteSpace(dicWhere[sKey].ToString()) || dicWhere[sKey] == null)
|
|
{
|
|
bIsHasPK = false;
|
|
break;
|
|
}
|
|
|
|
WhereNode wn = new WhereNode(sKey, WhereNode.EColumnOperation.EOT_EQ, tRun, dicWhere[sKey]);
|
|
lwnWhere.Add(wn);
|
|
}*/
|
|
}
|
|
if (nContainKeyCount == 0)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
wnTemp = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
|
|
|
|
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.DBLog(Util.GetLastExceptionMsg(ex), i_nCodeLine, i_sMemberName, i_sSourcePath);
|
|
sMsg = $"{nameof(generateWhereNode)} unknwon exception. Call from {i_sMemberName} {i_sSourcePath}({i_nCodeLine}).";
|
|
#if DEBUG
|
|
System.Diagnostics.Debug.WriteLine(sMsg);
|
|
#endif
|
|
}
|
|
|
|
o_wnResult = wnTemp;
|
|
return sMsg;
|
|
}
|
|
|
|
|
|
#endregion getUpdListCommand
|
|
|
|
#region getDelListCommand
|
|
|
|
/// <summary>
|
|
/// 函式名稱:getDelListCommand
|
|
/// 函式說明:獲取批次刪除Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新修改日:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_crmInput"></param>
|
|
/// <param name="i_oAllItems"></param>
|
|
/// <param name="i_sTbName"></param>
|
|
/// <param name="i_saDelKeys"></param>
|
|
/// <param name="o_sMsg">
|
|
/// 參數說明i_crm(Object):所需刪除資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lcCmds(Object)：刪除狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getDelListCommand(CRequestMessage i_crmInput, object i_oAllItems, string i_sTbName, List<string> i_saDelKeys, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
|
|
List<Command> lcCmds = new List<Command>();
|
|
if (i_oAllItems is JArray)
|
|
{
|
|
JArray jaItems = i_oAllItems as JArray;
|
|
foreach (JToken jtItem in jaItems)
|
|
{
|
|
Dictionary<string, object> dicItem = jtItem.ToObject<Dictionary<string, object>>();
|
|
sMsg = getEntity(i_sTbName, out object oData);
|
|
Type tRun = oData.GetType();
|
|
|
|
if (null != sMsg)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
sMsg = getManualLog(i_crmInput, dicItem, BLWording.LOG_ACTION_NAME_DELETESQL, out Command cLog);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (cLog != null)
|
|
{
|
|
lcCmds.Add(cLog);
|
|
}
|
|
|
|
sMsg = generateWhereNode(dicItem, i_saDelKeys, tRun, out WhereNode wnWhere);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
TableInfo ti = GetMasterDBTableInfo(oData.GetType());
|
|
// Backup
|
|
string sBackupTable = _fetchString(i_crmInput, BLWording.CMDBACKUPENTITYTYPE);
|
|
|
|
if (!string.IsNullOrEmpty(sBackupTable))
|
|
{
|
|
ti.BackTable = _GetEntityType(sBackupTable);
|
|
}
|
|
// Add User
|
|
Command c = Command.SetupDeleteCmd(ti, wnWhere);
|
|
lcCmds.Add(c);
|
|
}
|
|
}
|
|
o_sMsg = sMsg;
|
|
return lcCmds;
|
|
}
|
|
|
|
|
|
|
|
#endregion getDelListCommand
|
|
|
|
public static string getManualLog(CRequestMessage i_crmInput,
|
|
Dictionary<string, object> i_dicItem,
|
|
string i_sAction_name,
|
|
out Command o_cLog,
|
|
[System.Runtime.CompilerServices.CallerMemberName] string i_sMemberName = "",
|
|
[System.Runtime.CompilerServices.CallerFilePath] string i_sSourcePath = "",
|
|
[System.Runtime.CompilerServices.CallerLineNumber] int i_nCodeLine = 0)
|
|
{
|
|
string sMsg = null;
|
|
Command cLog = null;
|
|
|
|
// 團購 For performance
|
|
/*
|
|
do
|
|
{
|
|
|
|
List<LogItem> lli = new List<LogItem>();
|
|
|
|
if (i_dicItem == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
|
|
switch (i_sAction_name)
|
|
{
|
|
case BLWording.LOG_ACTION_NAME_DELETESQL:
|
|
{
|
|
foreach (KeyValuePair<string, object> jp in (i_dicItem[BLWording.WHEREDATA] as JObject).ToObject<Dictionary<string, object>>())
|
|
{
|
|
if (jp.Value != null)
|
|
{
|
|
string sValue = jp.Value.ToString();
|
|
lli.Add(new LogItem()
|
|
{
|
|
olddata = sValue,
|
|
columnname = jp.Key
|
|
});
|
|
}
|
|
}
|
|
}
|
|
break;
|
|
case BLWording.LOG_ACTION_NAME_UPDATESQL:
|
|
{
|
|
if (i_dicItem.ContainsKey(BLWording.UPDATEDIF))
|
|
{
|
|
JArray jaLogData = i_dicItem[BLWording.UPDATEDIF] as JArray;
|
|
lli = jaLogData.ToObject<List<LogItem>>();
|
|
}
|
|
}
|
|
break;
|
|
case BLWording.LOG_ACTION_NAME_INSERTSQL:
|
|
{
|
|
foreach (KeyValuePair<string, object> jp in (i_dicItem[BLWording.DATA] as JObject).ToObject<Dictionary<string, object>>())
|
|
{
|
|
|
|
if (jp.Value != null && jp.Value.ToString().Length > 0)
|
|
{
|
|
lli.Add(new LogItem()
|
|
{
|
|
newdata = jp.Value,
|
|
columnname = jp.Key
|
|
});
|
|
}
|
|
}
|
|
}
|
|
break;
|
|
default:
|
|
throw new NotImplementedException($"{i_sSourcePath}(line:{i_nCodeLine}) Caller:{i_sMemberName}");
|
|
}
|
|
|
|
tb_sys_manuallog ml = new tb_sys_manuallog()
|
|
{
|
|
program_uid = GetRequestProgramID(i_crmInput),
|
|
message = GetRequestMessage(i_dicItem),
|
|
remark = JsonConvert.SerializeObject(lli, new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore }),
|
|
action_name = i_sAction_name
|
|
};
|
|
|
|
cLog = Command.SetupInsertCmd(ml);
|
|
}
|
|
while (false);
|
|
*/
|
|
o_cLog = cLog;
|
|
return sMsg;
|
|
}
|
|
|
|
|
|
protected static string getWhereData(Dictionary<string, object> i_dicItem, out Dictionary<string, object> o_dicWhereData)
|
|
{
|
|
string sMsg = null;
|
|
Dictionary<string, object> dicWhereData = null;
|
|
|
|
do
|
|
{
|
|
if (i_dicItem == null)
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED;
|
|
break;
|
|
}
|
|
if (!i_dicItem.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
sMsg = MessageWording.PARAM_NOT_EXPECTED; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
Dictionary<string, object> dicWhereDataTemp = new Dictionary<string, object>();
|
|
foreach (KeyValuePair<string, object> jp in (i_dicItem[BLWording.WHEREDATA] as JObject).ToObject<Dictionary<string, object>>())
|
|
{
|
|
if (jp.Value != null)
|
|
{
|
|
string sValue = jp.Value.ToString();
|
|
dicWhereDataTemp.Add(jp.Key, sValue);
|
|
}
|
|
}
|
|
dicWhereData = dicWhereDataTemp;
|
|
}
|
|
while (false);
|
|
|
|
|
|
o_dicWhereData = dicWhereData;
|
|
return sMsg;
|
|
}
|
|
|
|
public SystemColumnInfo GetDefaultSystemColumnInfo()
|
|
{
|
|
string sUID;
|
|
string sOrgID = null;
|
|
|
|
SystemColumnInfo scRes = new SystemColumnInfo(
|
|
(Dictionary<string, object> i_dicInsertData, Dictionary<string, object> i_dicUpdateData) =>
|
|
{
|
|
i_dicInsertData[tb_sys_dblock.CN_UID] = Guid.NewGuid().ToString().ToUpper(CultureInfo.CurrentCulture);
|
|
i_dicInsertData[tb_sys_dblock.CN_UPDATE_DATE] =
|
|
i_dicInsertData[tb_sys_dblock.CN_CREATE_DATE] =
|
|
i_dicUpdateData[tb_sys_dblock.CN_UPDATE_DATE] = DateTime.Now;
|
|
}
|
|
);
|
|
|
|
|
|
if (this.OriRequest != null)
|
|
{
|
|
string sMsg = getSessionByToken(this.OriRequest.token, out tb_sys_session sSession);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
throw new ArgumentException(sMsg);
|
|
}
|
|
sUID = sSession.update_user_uid;
|
|
sOrgID = sSession.update_org_uid;
|
|
}
|
|
else
|
|
{
|
|
sUID = "3CE873C2-33AB-41E6-B6FE-4C528DEF5AF8"; // Workaound To
|
|
sOrgID = "001";
|
|
}
|
|
|
|
DateTime dtNow = DateTime.Now;
|
|
scRes.InsertData = new Dictionary<string, object>()
|
|
{
|
|
{ BLWording.CREATE_ORG_UID, sOrgID},
|
|
{ BLWording.CREATE_USER_UID, sUID},
|
|
{ BLWording.CREATE_DATE, dtNow},
|
|
{ BLWording.UPDATE_ORG_UID, sOrgID},
|
|
{ BLWording.UPDATE_USER_UID, sUID},
|
|
{ BLWording.UPDATE_DATE, dtNow}
|
|
};
|
|
scRes.UpdateData = new Dictionary<string, object>()
|
|
{
|
|
{ BLWording.UPDATE_ORG_UID, sOrgID},
|
|
{ BLWording.UPDATE_USER_UID, sUID},
|
|
{ BLWording.UPDATE_DATE, dtNow}
|
|
};
|
|
|
|
return scRes;
|
|
}
|
|
|
|
private static tb_sys_session _getQuestSession(string i_sAccountName)
|
|
{
|
|
tb_sys_user uSelect = new tb_sys_user() { account = i_sAccountName };
|
|
uSelect.SetFullDirty();
|
|
Command cSelect = Command.SetupSelectCmd(uSelect);
|
|
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
|
|
|
|
tb_sys_user u = ai.RunQuerySingleORM<tb_sys_user>(cSelect);
|
|
DateTime dtNow = DateTime.Now;
|
|
tb_sys_session sRes = new tb_sys_session()
|
|
{
|
|
create_org_uid = u.create_org_uid,
|
|
create_user_uid = u.uid,
|
|
update_org_uid = u.update_org_uid,
|
|
update_user_uid = u.uid,
|
|
update_date = dtNow,
|
|
create_date = dtNow,
|
|
role_name = i_sAccountName
|
|
};
|
|
return sRes;
|
|
}
|
|
|
|
protected static string getSessionRoleName(tb_sys_session i_ssSession)
|
|
{
|
|
string sRes = null;
|
|
|
|
if (i_ssSession != null)
|
|
{
|
|
QueryJsonElementCollection lBlocks = new QueryJsonElementCollection();
|
|
|
|
QueryJsonElement qjeUser2role = lBlocks.GetInst();
|
|
qjeUser2role.table = tb_sys_user2role.TABLENAME;
|
|
qjeUser2role.wherecols = new WhereNode(WhereNode.ENodeOperation.ENO_AND,
|
|
new WhereNode(tb_sys_user2role.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user2role), 1),
|
|
new WhereNode(tb_sys_user2role.CN_USER_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_user2role), i_ssSession.update_user_uid));
|
|
lBlocks.Add(qjeUser2role);
|
|
|
|
QueryJsonElement qjeRole = lBlocks.GetInst();
|
|
qjeRole.table = tb_sys_role.TABLENAME;
|
|
qjeRole.jointype = QueryJsonElement.LEFT_JOIN;
|
|
qjeRole.joincols = new Dictionary<string, string>() { { tb_sys_role.CN_UID, tb_sys_user2role.CN_ROLE_UID } };
|
|
qjeRole.jointable = qjeUser2role;
|
|
qjeRole.displaycols = new List<string>() { tb_sys_role.CN_NAME };
|
|
qjeRole.wherecols = new WhereNode(tb_sys_role.CN_STATUS_FLAG, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_sys_role), 1);
|
|
|
|
lBlocks.Add(qjeRole);
|
|
|
|
MakeSelectJoinByBlocks(lBlocks, out Command cSelect);
|
|
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cSelect);
|
|
List<tb_sys_role> lRoles = ai.RunQueryList<tb_sys_role>(cSelect);
|
|
|
|
sRes = lRoles.Count == 1 ? lRoles[0].name : null;
|
|
}
|
|
return sRes;
|
|
}
|
|
|
|
[Auth(false)]
|
|
public CResponseMessage Upload(CRequestMessage i_crmInput)
|
|
{
|
|
FileUploadHelper fileUpload = new FileUploadHelper(this.GetFileUploadInfo(), i_crmInput);
|
|
string sMsg = fileUpload.UploadFile(i_crmInput, out List<tb_sys_uploadlog> lsUploadItem, null);
|
|
|
|
CResponseMessage crmRes;
|
|
if (sMsg != null)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
}
|
|
else
|
|
{
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
crmRes.param[BLWording.DATA] = lsUploadItem;
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
protected static string generateUploadFolderPath(string i_sfolderpath = null)
|
|
{
|
|
string sRes;
|
|
if (i_sfolderpath != null)
|
|
{
|
|
sRes = Path.Combine(RootPath, SystemSettingHelper.FileFolder(SystemSettingHelper.EPath.EP_FILE), i_sfolderpath);
|
|
}
|
|
else
|
|
{
|
|
sRes = Path.Combine(RootPath, SystemSettingHelper.FileFolder(SystemSettingHelper.EPath.EP_FILETMP));
|
|
}
|
|
|
|
if (!Directory.Exists(sRes))
|
|
{
|
|
Directory.CreateDirectory(sRes);
|
|
}
|
|
|
|
return sRes;
|
|
}
|
|
|
|
protected static string generateUploadFolderName(CRequestMessage i_crmInput)
|
|
{
|
|
return i_crmInput == null ? MessageWording.PARAM_NOT_EXPECTED : string.Format(CultureInfo.CurrentCulture, "{0}_{1}", (i_crmInput.customparam[BLWording.SESSION_USER] as tb_sys_session).update_user_uid, DateTime.Now.ToString("yyyyMMddHHMMssfff", CultureInfo.CurrentCulture));
|
|
}
|
|
|
|
public virtual CResponseMessage Import(CRequestMessage i_crmInput)
|
|
{
|
|
return commonEditor(i_crmInput, null, dgImportCommandGenerator);
|
|
}
|
|
|
|
public virtual CResponseMessage Export(CRequestMessage i_crmInput)
|
|
{
|
|
CResponseMessage crmRes = null;
|
|
|
|
string sMsg;
|
|
|
|
do
|
|
{
|
|
if (dgGetExportPath == null)
|
|
{
|
|
sMsg = MessageWording.NO_EXPORT_DELEGATE;
|
|
break;
|
|
}
|
|
|
|
sMsg = getCommonParameter(i_crmInput, BLWording.QRY_MASTER, out JArray jaDataArray, out tb_sys_session sUserSession, false);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
sMsg = dgGetExportPath(i_crmInput, jaDataArray, sUserSession, out string o_sFilePath, EditMustConditionKeys);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (o_sFilePath == null)
|
|
{
|
|
sMsg = MessageWording.NO_EXPORTFILE_CREATED;
|
|
break;
|
|
}
|
|
|
|
FileUploadHelper fuh = new FileUploadHelper(GetFileUploadInfo(), i_crmInput);
|
|
|
|
sMsg = fuh.UploadLocalFile(o_sFilePath, 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);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sMsg))
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
Logger.Error(JsonConvert.SerializeObject(i_crmInput));
|
|
Logger.Error(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
else
|
|
{
|
|
//Logger.Info(JsonConvert.SerializeObject(i_crmInput));
|
|
//Logger.Info(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
protected virtual CResponseMessage commonEditor(CRequestMessage i_crmInput, string i_sRequestType, DG_GenerateEditCommand i_dgEdit)
|
|
{
|
|
CResponseMessage crmRes = null;
|
|
string sErrorCode = null;
|
|
string sErrorMsg = null;
|
|
|
|
do
|
|
{
|
|
try
|
|
{
|
|
if (i_dgEdit == null)
|
|
{
|
|
sErrorCode = MessageWording.NOT_IMPLEMENT;
|
|
break;
|
|
}
|
|
|
|
sErrorCode = getCommonParameter(i_crmInput, i_sRequestType, out JArray jaDataArray, out tb_sys_session sUserSession);
|
|
|
|
if (sErrorCode != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (sUserSession == null)
|
|
{
|
|
Logger.Info("commonEditor session error");
|
|
sErrorCode = MessageWording.SESSION_NOT_EXIST;
|
|
break;
|
|
}
|
|
|
|
sErrorCode = i_dgEdit(i_crmInput, jaDataArray, sUserSession, out List<Command> lCmdMaster, EditMustConditionKeys);
|
|
|
|
if (sErrorCode != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (lCmdMaster.Count == 0)
|
|
{
|
|
sErrorCode = MessageWording.NO_PROCESS_COMMAND;
|
|
break;
|
|
}
|
|
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(lCmdMaster[0], GetDefaultSystemColumnInfo());
|
|
|
|
int iRes = ai.RunEditCmds(lCmdMaster);
|
|
|
|
sErrorCode = GetLastErrorCode(lCmdMaster);
|
|
|
|
if (sErrorCode != null)
|
|
{
|
|
sErrorMsg = GetLastErrorMessage(lCmdMaster);
|
|
break;
|
|
}
|
|
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
crmRes.param.Add(BLWording.AFFECTCOUNT, iRes);
|
|
|
|
if (i_sRequestType == BLWording.ADD_MASTER || i_sRequestType == BLWording.CPY_MASTER)
|
|
{
|
|
List<object> lsUid = new List<object>();
|
|
lCmdMaster.ForEach(f =>
|
|
{
|
|
if (f.EditData != null)
|
|
{
|
|
lsUid.Add(f.EditData.GetValue(BLWording.UID));
|
|
}
|
|
});
|
|
crmRes.param.Add(BLWording.UID, lsUid);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
crmRes = new CErrorResponseMessage(ex.ToString(), i_crmInput);
|
|
crmRes.param[BLWording.ERROR_DESCRIPTION] = ex.ToString();
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
Logger.Error(sb
|
|
.AppendLine(ex.ToString())
|
|
.AppendLine(ex.StackTrace)
|
|
.AppendLine(JsonConvert.SerializeObject(i_crmInput))
|
|
.AppendLine(JsonConvert.SerializeObject(crmRes))
|
|
.ToString());
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sErrorCode))
|
|
{
|
|
crmRes = new CErrorResponseMessage(sErrorCode, i_crmInput);
|
|
crmRes.param[BLWording.ERROR_DESCRIPTION] = sErrorMsg;
|
|
Logger.Error(sErrorMsg);
|
|
Logger.Error(JsonConvert.SerializeObject(i_crmInput));
|
|
Logger.Error(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
|
|
protected virtual CResponseMessage simpleRead(CRequestMessage i_crmInput, DG_GenerateReadCommand i_dgSimpleRead, DG_PostHandleReadData i_dgPostReadData = null)
|
|
{
|
|
return simpleQuery(i_crmInput, BLWording.QRY_MASTER, i_dgSimpleRead, i_dgPostReadData);
|
|
}
|
|
|
|
protected virtual CResponseMessage simpleRead(CRequestMessage i_crmInput, string i_sConvertMessage, Command i_cCur, DG_PostHandleReadData i_dgPostReadData = null)
|
|
{
|
|
return simpleQuery(i_crmInput, BLWording.QRY_MASTER, i_sConvertMessage, i_cCur, i_dgPostReadData);
|
|
}
|
|
|
|
protected virtual CResponseMessage simpleQuery(CRequestMessage i_crmInput, string i_sRequestType, string i_sConvertMessage, Command cRead, DG_PostHandleReadData i_dgPostReadData = null)
|
|
{
|
|
string sMsg = i_sConvertMessage;
|
|
CResponseMessage crmRes = null;
|
|
|
|
|
|
do
|
|
{
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (cRead == null)
|
|
{
|
|
sMsg = "NO COMMAND";
|
|
break;
|
|
}
|
|
|
|
|
|
object oResultData;
|
|
ArsenalInterface ai = ArsenalDBMgr.GetInst(cRead);
|
|
|
|
if (i_dgPostReadData != null)
|
|
{
|
|
sMsg = getCommonParameter(i_crmInput, i_sRequestType, out JArray jaDataArray, out tb_sys_session sUserSession);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
sMsg = i_dgPostReadData(i_crmInput, ai, cRead, jaDataArray, sUserSession, out oResultData);
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
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;
|
|
}
|
|
}
|
|
|
|
QueryDataSet qds = ai.RunQueryDataSet(cRead, i_nPageIdx: nPageIdx, i_nNumOfPage: nPageNum);
|
|
|
|
if (!cRead.IsSuccess)
|
|
{
|
|
sMsg = cRead.LastErrorCode;
|
|
break;
|
|
}
|
|
oResultData = new QueryResponse(qds);
|
|
}
|
|
crmRes = new CSuccessResponseMessage(null, i_crmInput);
|
|
// 填寫回傳
|
|
crmRes.param.Add(BLWording.DATA, oResultData);
|
|
}
|
|
while (false);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sMsg))
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
Logger.Error(JsonConvert.SerializeObject(i_crmInput));
|
|
Logger.Error(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
else
|
|
{
|
|
//Logger.Debug(JsonConvert.SerializeObject(i_crmInput));
|
|
//Logger.Debug(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
protected virtual CResponseMessage simpleQuery(CRequestMessage i_crmInput, string i_sRequestType, DG_GenerateReadCommand i_dgSimpleRead, DG_PostHandleReadData i_dgPostReadData = null)
|
|
{
|
|
string sMsg;
|
|
CResponseMessage crmRes = null;
|
|
|
|
|
|
do
|
|
{
|
|
if (i_dgSimpleRead == null)
|
|
{
|
|
sMsg = MessageWording.NOT_IMPLEMENT;
|
|
break;
|
|
}
|
|
|
|
sMsg = getCommonParameter(i_crmInput, i_sRequestType, out JArray jaDataArray, out tb_sys_session sUserSession);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
sMsg = i_dgSimpleRead(i_crmInput, jaDataArray, sUserSession, out Command cRead);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
cRead.ReadLevel = Command.EReadLevel.ERL_DIRTY;
|
|
crmRes = simpleQuery(i_crmInput, i_sRequestType, sMsg, cRead, i_dgPostReadData);
|
|
}
|
|
while (false);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(sMsg))
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crmInput);
|
|
Logger.Error(JsonConvert.SerializeObject(i_crmInput));
|
|
Logger.Error(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
else
|
|
{
|
|
//Logger.Debug(JsonConvert.SerializeObject(i_crmInput));
|
|
//Logger.Debug(JsonConvert.SerializeObject(crmRes));
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
public Dictionary<string, string> FilterQueryDictionary<TEntity>(Dictionary<string, string> i_dic) where TEntity : EntityBase, new()
|
|
{
|
|
var entityPropertitesName = new TEntity().GetType().GetProperties().Select(x=>x.Name);
|
|
|
|
return i_dic.Where(item =>
|
|
entityPropertitesName.Contains(item.Key) &&
|
|
!string.IsNullOrWhiteSpace(item.Value))
|
|
.ToDictionary(k => k.Key, v => v.Value);
|
|
}
|
|
}
|
|
}
|