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