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.
544 lines
19 KiB
544 lines
19 KiB
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using OT.COM.ArsenalDB;
|
|
using OT.COM.LogisticsUtil;
|
|
using OT.COM.SignalerMessage;
|
|
using SoldierData.syserp;
|
|
using SoldierDataEntity;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
|
|
namespace CounsellorBL
|
|
{
|
|
/// <summary>
|
|
/// 類別名稱: DBhelper
|
|
/// 類別說明 新增，修改，刪除方法
|
|
/// 起始作者: John
|
|
/// 起始日期: 2016/06/04
|
|
/// 最新修改人: John
|
|
/// 最新修日期: 2016/06/14
|
|
/// </summary>
|
|
public class DBhelper : DBService
|
|
{
|
|
#region getQryListCommand
|
|
/// <summary>
|
|
/// 函式名稱:getQryListCommand
|
|
/// 函式說明:獲取批次查詢Command
|
|
/// 起始作者:Justin
|
|
/// 起始日期:2016/10/05
|
|
/// 最新修改人:Justin
|
|
/// 最新日期:2016/10/05
|
|
/// </summary>
|
|
/// <param name="i_crmInput">
|
|
/// 參數說明i_crm(Object):所需查詢資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lComd(Object)：新增狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getQryListCommand(object i_dicData, string i_sTbName, out string o_sMsg, List<Dictionary<string, string>> i_ordercols = null, Dictionary<string, string> i_aliascols = null, bool i_distinct = false)
|
|
{
|
|
string sMsg = null;
|
|
|
|
OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();
|
|
Dictionary<string, string> dicData = null;
|
|
List<Command> lComd = new List<Command>();
|
|
if (i_dicData is JArray)
|
|
{
|
|
|
|
JArray jaData = i_dicData as JArray;
|
|
foreach (JObject jo in jaData)
|
|
{
|
|
|
|
object tbData = null;
|
|
sMsg = getEntity(i_sTbName, out tbData); //傳出來一個物件
|
|
Type Tabletype = tbData.GetType();
|
|
EntityBase ebSelect = tbData as EntityBase;
|
|
|
|
if (null != sMsg)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
dicData = jo["wheredata"].ToObject<Dictionary<string, string>>();
|
|
// WhereNode wnWhere = Command.whereDictionary2WhereNode(GetMasterDBTableInfo(Tabletype), dicData);
|
|
Command c = Command.SetupSelectCmdByParam(GetMasterDBTableInfo(tbData.GetType()), ebSelect, dicData, i_ordercols, i_aliascols, i_distinct);
|
|
lComd.Add(c);
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lComd;
|
|
}
|
|
#endregion
|
|
#region getAddListCommand
|
|
/// <summary>
|
|
/// 函式名稱:getAddListCommand
|
|
/// 函式說明:獲取批次新增Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_crmInput">
|
|
/// 參數說明i_crm(Object):所需新增資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lComd(Object)：新增狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getAddListCommand(object i_dicData, string i_sTbName, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();
|
|
|
|
List<Command> lComd = new List<Command>();
|
|
if (i_dicData is JArray)
|
|
{
|
|
|
|
JArray jaData = i_dicData as JArray;
|
|
foreach (JObject jo in jaData)
|
|
{
|
|
|
|
Dictionary<string, object> dicData = jo.ToObject<Dictionary<string, object>>();
|
|
object oInsert = null;
|
|
sMsg = getEntity(i_sTbName, out oInsert);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
EntityBase ebInsert = oInsert as EntityBase;
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
JObject jdata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.DATA].ToString());
|
|
|
|
foreach (JProperty property in jdata.Properties())
|
|
{
|
|
|
|
JToken jv = property.Value;
|
|
dicInput.Add(property.Name, jv.Value<string>());
|
|
|
|
}
|
|
|
|
Dictionary<string, string> dicFillRes = ebInsert.FillInsertData(dicInput);
|
|
|
|
if (0 != dicFillRes.Count)
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
// Add User
|
|
Command c = Command.SetupInsertCmd(GetMasterDBTableInfo(oInsert.GetType()), ebInsert);
|
|
lComd.Add(c);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lComd;
|
|
}
|
|
#endregion
|
|
|
|
#region getUpdListCommand
|
|
/// <summary>
|
|
/// 函式名稱:getUpdListCommand
|
|
/// 函式說明:獲取批次修改Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_crmInput">
|
|
/// 參數說明i_crm(Object):所需修改資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lComd(Object)：修改狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getUpdListCommand(object i_dicData, string i_sTbName, List<string> i_saUpdKeys, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
bool bIsHasPK = true;
|
|
OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();
|
|
|
|
|
|
List<Command> lComd = new List<Command>();
|
|
if (i_dicData is JArray)
|
|
{
|
|
JArray jaData = i_dicData as JArray;
|
|
foreach (JObject joData in jaData)
|
|
{
|
|
Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
|
|
object oData = null;
|
|
sMsg = getEntity(i_sTbName, out oData);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
Type tRun = oData.GetType();
|
|
EntityBase ebData = oData as EntityBase;
|
|
EntityBase ebWhere = chTool.GetInstByType(tRun) as EntityBase;
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
|
|
if (!dicData.ContainsKey(BLWording.DATA))
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
JObject jdata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.DATA].ToString());
|
|
foreach (JProperty property in jdata.Properties())
|
|
{
|
|
|
|
JToken jv = property.Value;
|
|
dicInput.Add(property.Name, jv.Value<string>());
|
|
|
|
}
|
|
|
|
Dictionary<string, string> dicFillRes = ebData.FillUpadateData(dicInput);
|
|
|
|
if (0 != dicFillRes.Count)
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
if (!dicData.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
Dictionary<string, object> dicWhere = new Dictionary<string, object>();
|
|
JObject jwheredata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.WHEREDATA].ToString());
|
|
if (null != JProperty2Dic(jwheredata, ref dicWhere))
|
|
{
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
WhereNode wnWhere = null;
|
|
List<WhereNode> lwnWhere = new List<WhereNode>();
|
|
foreach (string sKey in i_saUpdKeys)
|
|
{
|
|
if (dicWhere.ContainsKey(sKey) == false)
|
|
{
|
|
bIsHasPK = false;
|
|
break;
|
|
}
|
|
if (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 (!bIsHasPK)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
wnWhere = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
|
|
Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);
|
|
lComd.Add(c);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
o_sMsg = sMsg;
|
|
return lComd;
|
|
}
|
|
#endregion
|
|
|
|
#region getDelListCommand
|
|
/// <summary>
|
|
/// 函式名稱:getDelListCommand
|
|
/// 函式說明:獲取批次刪除Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/04
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/14
|
|
/// </summary>
|
|
/// <param name="i_crmInput">
|
|
/// 參數說明i_crm(Object):所需刪除資料...
|
|
/// 參數說明i_sTbName:Tabel名稱
|
|
/// 參數說明o_sMsg:錯誤訊息
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳 lComd(Object)：刪除狀態相關訊息
|
|
/// </returns>
|
|
protected List<Command> getDelListCommand(CRequestMessage i_crm, object i_dicData, string i_sTbName, List<string> i_saDelKeys, out string o_sMsg)
|
|
{
|
|
string sMsg = null;
|
|
bool bIsHasPK = true;
|
|
OT.COM.LogisticsUtil.ClassHelper chTool = new OT.COM.LogisticsUtil.ClassHelper();
|
|
|
|
List<Command> lComd = new List<Command>();
|
|
if (i_dicData is JArray)
|
|
{
|
|
|
|
JArray jaWhereData = i_dicData as JArray;
|
|
foreach (JObject joData in jaWhereData)
|
|
{
|
|
|
|
Dictionary<string, object> dicData = joData.ToObject<Dictionary<string, object>>();
|
|
object oData = null;
|
|
sMsg = getEntity(i_sTbName, out oData);
|
|
Type tRun = oData.GetType();
|
|
|
|
if (null != sMsg)
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
if (!dicData.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
|
|
}
|
|
|
|
Dictionary<string, object> dicWhere = new Dictionary<string, object>();
|
|
JObject jwheredata = (JObject)JsonConvert.DeserializeObject(dicData[BLWording.WHEREDATA].ToString());
|
|
|
|
foreach (JProperty property in jwheredata.Properties())
|
|
{
|
|
|
|
JToken jv = property.Value;
|
|
dicWhere.Add(property.Name, jv.Value<string>());
|
|
|
|
}
|
|
|
|
WhereNode wnWhere = null;
|
|
List<WhereNode> lwnWhere = new List<WhereNode>();
|
|
foreach (string sKey in i_saDelKeys)
|
|
{
|
|
if (dicWhere.ContainsKey(sKey) == false)
|
|
{
|
|
bIsHasPK = false;
|
|
break;
|
|
}
|
|
if (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 (!bIsHasPK)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000022; //參數錯誤
|
|
break;
|
|
}
|
|
|
|
wnWhere = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
|
|
|
|
TableInfo ti = GetMasterDBTableInfo(oData.GetType());
|
|
// Backup
|
|
string sBackupTable = _fetchString(i_crm, BLWording.CMDBACKUPENTITYTYPE);
|
|
|
|
if (string.IsNullOrEmpty(sBackupTable) == false)
|
|
{
|
|
|
|
ti.BackTable = new CustomizeDBMgr().GetEntityType(sBackupTable);
|
|
|
|
}
|
|
// Add User
|
|
Command c = Command.SetupDeleteCmd(ti, wnWhere);
|
|
lComd.Add(c);
|
|
|
|
}
|
|
|
|
}
|
|
o_sMsg = sMsg;
|
|
return lComd;
|
|
}
|
|
#endregion
|
|
|
|
}
|
|
|
|
public class DBhelper<T> : DBService where T : EntityBase, new()
|
|
{
|
|
#region 根據ID，獲取
|
|
/// <summary>
|
|
/// 函式名稱:QueryByPk
|
|
/// 函式說明:根據ID，獲取
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_sPk">Pk</param>
|
|
/// <returns>單筆資料對象</returns>
|
|
public T QueryByPk(string i_sPk)
|
|
{
|
|
T oEntity = new T();
|
|
oEntity.SetValue(oEntity.PKNames[0],
|
|
Convert.ChangeType(i_sPk, oEntity.GetPropertyType(oEntity.PKNames[0])));
|
|
WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(oEntity);
|
|
Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), new T(), wndPk);
|
|
T oResult = this.adbm.RunQuerySingleORM<T>(cSelect);
|
|
return oResult;
|
|
}
|
|
|
|
public T QueryEntity(T i_oEntity)
|
|
{
|
|
WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(i_oEntity);
|
|
Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), new T(), wndPk);
|
|
T oResult = this.adbm.RunQuerySingleORM<T>(cSelect);
|
|
return oResult;
|
|
}
|
|
|
|
public IList<T> QueryByWhere(WhereNode i_oWhere)
|
|
{
|
|
T oEntity = new T();
|
|
Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(T)), oEntity, i_oWhere);
|
|
IList<T> oResult = this.adbm.RunQueryList<T>(cSelect);
|
|
return oResult;
|
|
}
|
|
#endregion
|
|
|
|
#region 根據ID，刪除
|
|
/// <summary>
|
|
/// 函式名稱:DeleteByPk
|
|
/// 函式說明:根據ID，刪除
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_sPk">Pk</param>
|
|
/// <returns>變動的行數</returns>
|
|
public int DeleteByPk(string i_sPk)
|
|
{
|
|
T oEntity = new T();
|
|
oEntity.SetValue(oEntity.PKNames[0],
|
|
Convert.ChangeType(i_sPk, oEntity.GetPropertyType(oEntity.PKNames[0])));
|
|
return DeleteEntity(oEntity);
|
|
}
|
|
|
|
public int DeleteEntity(T i_oEntity)
|
|
{
|
|
WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(i_oEntity);
|
|
Command cDelete = Command.SetupDeleteCmd(GetMasterDBTableInfo(typeof(T)), wndPk);
|
|
int iUpdateRowCount = this.adbm.RunEditSingleCmd(cDelete);
|
|
return iUpdateRowCount;
|
|
}
|
|
#endregion
|
|
|
|
#region 插入
|
|
/// <summary>
|
|
/// 函式名稱:InsertEntity
|
|
/// 函式說明:插入多筆，用於不同實體類
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_lsCmd">多筆新增資料集合</param>
|
|
/// <returns>失敗返回0，成功返回大於0</returns>
|
|
public int InsertEntity(List<Command> i_lsCmd)
|
|
{
|
|
int iUpdateRowCount = this.adbm.RunEditCmds(i_lsCmd);
|
|
return iUpdateRowCount;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetinsertCmds
|
|
/// 函式說明:產生批次新增Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_lsEntity"></param>
|
|
/// <returns></returns>
|
|
public List<Command> GetinsertCmds(IList<T> i_lsEntity)
|
|
{
|
|
List<Command> lsCmd = new List<Command>();
|
|
foreach (T oEntity in i_lsEntity)
|
|
{
|
|
Command cInsert = Command.SetupInsertCmd(GetMasterDBTableInfo(typeof(T)), oEntity);
|
|
lsCmd.Add(cInsert);
|
|
}
|
|
return lsCmd;
|
|
}
|
|
#endregion
|
|
|
|
#region 更新
|
|
/// <summary>
|
|
/// 函式名稱:UpdateEntity
|
|
/// 函式說明:批次更新
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_lsEntity"></param>
|
|
/// <returns>變動的行數</returns>
|
|
public int UpdateEntity(List<T> i_lsEntity)
|
|
{
|
|
List<Command> lsCommands = GetUpdateCmds(i_lsEntity);
|
|
int iUpdateRowCount = this.adbm.RunEditCmds(lsCommands);
|
|
return iUpdateRowCount;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetUpdateCmds
|
|
/// 函式說明:產生批次新增Command
|
|
/// 起始作者:John
|
|
/// 起始日期:2016/06/27
|
|
/// 最新修改人:John
|
|
/// 最新日期:2016/06/27
|
|
/// </summary>
|
|
/// <param name="i_lsEntity"></param>
|
|
/// <returns></returns>
|
|
public List<Command> GetUpdateCmds(IList<T> i_lsEntity)
|
|
{
|
|
List<Command> lsCmd = new List<Command>();
|
|
foreach (T oEntity in i_lsEntity)
|
|
{
|
|
WhereNode wndPk = Command.MakeWhereEntityBase2WhereNode(oEntity);
|
|
Command cUpdate = Command.SetupUpdateCmd(GetMasterDBTableInfo(typeof(T)), oEntity, wndPk);
|
|
lsCmd.Add(cUpdate);
|
|
}
|
|
return lsCmd;
|
|
}
|
|
#endregion
|
|
}
|
|
}
|