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.
1370 lines
47 KiB
1370 lines
47 KiB
|
|
using Newtonsoft.Json.Linq;
|
|
using Newtonsoft.Json;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using OT.COM.ArsenalDB;
|
|
using OT.COM.LogisticsUtil;
|
|
using OT.COM.SignalerMessage;
|
|
using SoldierData;
|
|
using SoldierDataEntity;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using SoldierData.syserp;
|
|
|
|
namespace CounsellorBL
|
|
{
|
|
public class DBService : ServiceBase
|
|
{
|
|
|
|
protected CustomizeDBMgr cdbm = new CustomizeDBMgr();
|
|
protected ArsenalDBMgr adbm = new ArsenalDBMgr();
|
|
|
|
public ArsenalDBMgr ArsenalDBMgrInst
|
|
{
|
|
get { return adbm; }
|
|
}
|
|
|
|
public int NumPerPage { get; set; }
|
|
|
|
public DBService()
|
|
: base()
|
|
{
|
|
NumPerPage = 1000;
|
|
|
|
//
|
|
if(Command.SingleConnection == null)
|
|
{
|
|
Command.SetupSingleConnection(cdbm.SingleConnection);
|
|
}
|
|
}
|
|
|
|
public TableInfo GetMasterDBTableInfo(Type i_t)
|
|
{
|
|
TableInfo tiRes = cdbm.GetTableInfo(i_t);
|
|
return tiRes;
|
|
}
|
|
|
|
protected string getViewModelByEntityName(string i_sTypeName, out object o_ebEntity)
|
|
{
|
|
string sMsg = null;
|
|
object oWhere = null;
|
|
try
|
|
{
|
|
ClassHelper ch = new ClassHelper();
|
|
|
|
string sEntityName = null;
|
|
int nIdx = i_sTypeName.IndexOf(".");
|
|
|
|
if (nIdx == -1)
|
|
{
|
|
sEntityName = string.Format("CounsellorBL.ViewModels.master.ViewModel_{0},CounsellorBL", i_sTypeName);
|
|
}
|
|
else
|
|
{
|
|
string sCatelog = i_sTypeName.Substring(0, nIdx);
|
|
string sSubEntityName = i_sTypeName.Substring(nIdx + 1);
|
|
|
|
sEntityName = string.Format("CounsellorBL.ViewModels.{0}.ViewModel_{1},CounsellorBL", sCatelog, sSubEntityName);
|
|
}
|
|
|
|
|
|
sMsg = ch.GetInstByFullName(sEntityName, out oWhere);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
o_ebEntity = oWhere;
|
|
|
|
return sMsg;
|
|
}
|
|
|
|
protected string getEntity(string i_sEntityName, out object o_ebEntity)
|
|
{
|
|
string sMsg = null;
|
|
object oWhere = null;
|
|
try
|
|
{
|
|
ClassHelper ch = new ClassHelper();
|
|
|
|
string sEntityName = cdbm.GetFullEntityName(i_sEntityName);
|
|
|
|
|
|
|
|
sMsg = ch.GetInstByFullName(sEntityName + ",SoldierData", out oWhere); //錯誤時回傳No Type 或Create Type Fail錯誤
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
o_ebEntity = oWhere;
|
|
|
|
return sMsg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Session Validation
|
|
/// </summary>
|
|
/// <param name="i_sToken"></param>
|
|
/// <param name="i_bRenewExpireTime"></param>
|
|
/// <returns></returns>
|
|
private CResponseMessage _checkTokenValid(string i_sToken, bool i_bRenewExpireTime = false)
|
|
{
|
|
CResponseMessage crm = null;
|
|
string sMsg = null;
|
|
|
|
do
|
|
{
|
|
WhereNode wn = new WhereNode(otb_session.CN_GUID, WhereNode.EColumnOperation.EOT_EQ, typeof(otb_session), i_sToken);
|
|
Command cSelectSeesion = Command.SetupSelectCmd(GetMasterDBTableInfo(typeof(otb_session)), null, wn);
|
|
|
|
otb_session sCur = adbm.RunQuerySingleORM<otb_session>(cSelectSeesion);
|
|
|
|
if (null == sCur)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000023; //SESSION_NO_SESSION
|
|
break;
|
|
}
|
|
|
|
if (new Util().GetSettingInt("ExpireTimeMinute") < DateTime.Now.Subtract(sCur.modify_date).TotalMinutes)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000024; //SESSION_EXPIRED SESSION過期
|
|
break;
|
|
}
|
|
|
|
crm = new CResponseMessage() { RESULT = EResponseResult.RR_TRUE };
|
|
|
|
crm.DATA.Add(BLWording.SESSION_USER_ID, sCur.create_user_guid);
|
|
|
|
if (true == i_bRenewExpireTime)
|
|
{
|
|
otb_session sModifyData = new otb_session() { modify_date = DateTime.Now };
|
|
|
|
WhereNode wnUpdate = new WhereNode(otb_session.CN_GUID, WhereNode.EColumnOperation.EOT_EQ, typeof(otb_session), i_sToken);
|
|
Command cUpdate = Command.SetupUpdateCmd(GetMasterDBTableInfo(typeof(otb_session)), sModifyData, wnUpdate);
|
|
adbm.RunEditSingleCmd(cUpdate);
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
protected 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>();
|
|
try
|
|
{
|
|
do
|
|
{
|
|
foreach (string sKey in i_crm.DATA.Keys)
|
|
{
|
|
dicFormData.Add(sKey, i_crm.DATA[sKey]);
|
|
}
|
|
|
|
|
|
|
|
//if (false == dicFormData.Keys.Contains(BLWording.TOKEN))
|
|
if (i_crm.TOKEN == null)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000025; //NO TOKEN
|
|
break;
|
|
}
|
|
|
|
crm = this._checkTokenValid(i_crm.TOKEN, true);
|
|
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
o_oDicData = dicFormData;
|
|
return crm;
|
|
}
|
|
|
|
/// <summary>
|
|
/// For Query result
|
|
/// </summary>
|
|
protected class queryResponse
|
|
{
|
|
public long TotalCount { get; set; }
|
|
public long CurrentCount { get; set; }
|
|
|
|
public List<Dictionary<string, object>> Records { get; set; }
|
|
|
|
public queryResponse(QueryDataSet i_qds)
|
|
{
|
|
List<Dictionary<string, object>> ldicData = new List<Dictionary<string, object>>();
|
|
DataTable dt = i_qds.DATA.Tables[0];
|
|
DataColumnCollection dcc = dt.Columns;
|
|
int nSNCount = 1;
|
|
|
|
this.TotalCount = i_qds.Total;
|
|
|
|
this.CurrentCount = dt.Rows.Count;
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
Dictionary<string, object> dicData = new Dictionary<string, object>();
|
|
dicData.Add("SN", nSNCount);
|
|
foreach (DataColumn dc in dcc)
|
|
{
|
|
object oData = dr[dc.ColumnName];
|
|
string sColumnName = dc.ColumnName.ToLower();
|
|
|
|
//dicData.Add(sColumnName, EntityUtil.Removepadding(sData, sColumnName, i_qds.DataType));
|
|
|
|
if (oData is string)
|
|
{
|
|
string sData = oData.ToString();
|
|
dicData.Add(sColumnName, sData.TrimEnd());
|
|
}
|
|
else if (oData is DateTime)
|
|
{
|
|
dicData.Add(sColumnName, ((DateTime)(oData)).ToString("yyyy/MM/dd HH:mm:ss"));
|
|
}
|
|
else
|
|
{
|
|
dicData.Add(sColumnName, oData.ToString());
|
|
}
|
|
|
|
}
|
|
|
|
ldicData.Add(dicData);
|
|
nSNCount++;
|
|
}
|
|
this.Records = ldicData;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Get table row
|
|
/// </summary>
|
|
/// <param name="i_crm"></param>
|
|
/// <returns></returns>
|
|
public CResponseMessage GetInfoDataBase(string sEntityName, CRequestMessage i_crm, EntityBase ebDisplay)
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crmRes = null;
|
|
ClassHelper ch = new ClassHelper();
|
|
int nNumOfPage = -1;
|
|
int nPageIdx = -1;
|
|
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
string sSearchMode = _fetchString(i_crm, BLWording.SEARCHMODE);
|
|
|
|
if (null == sSearchMode)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000026; //NO SEARCH Mode
|
|
break;
|
|
}
|
|
|
|
if (sSearchMode == BLWording.SEARCHMODE_RECORDIDX)
|
|
{
|
|
nNumOfPage = 1;
|
|
string sIDX = _fetchString(i_crm, BLWording.SEARCHMODE_RECORDIDX);
|
|
if (null == sIDX || Int32.TryParse(sIDX, out nPageIdx) == false)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000027; //NO RECORDIDX
|
|
break;
|
|
}
|
|
}
|
|
/*else if (sSearchMode == BLWording.SEARCHMODE_FID)
|
|
{
|
|
int nID = -1;
|
|
string sFID = _fetchString(i_crm, BLWording.SEARCHMODE_FID);
|
|
|
|
if (null == sFID && Int32.TryParse(sFID, out nID))
|
|
{
|
|
sMsg = "NO COMMONPK";
|
|
break;
|
|
}
|
|
i_wnWhere.SetValue(BLWording.COMMONPK, Int32.Parse(sFID));
|
|
}*/
|
|
else if (sSearchMode == BLWording.SEARCHMODE_PAGEIDX)
|
|
{
|
|
string sPageIDX = _fetchString(i_crm, BLWording.SEARCHMODE_PAGEIDX);
|
|
if (null == sPageIDX || Int32.TryParse(sPageIDX, out nPageIdx) == false)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000028; //NO PAGEIDX
|
|
break;
|
|
}
|
|
|
|
string sNumPerPage = _fetchString(i_crm, BLWording.SEARCHMODE_NUMPERGAGE);
|
|
if (null == sPageIDX || Int32.TryParse(sNumPerPage, out nNumOfPage) == false)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000029; //NO NUMPERPAGE
|
|
break;
|
|
}
|
|
}
|
|
|
|
string sToken = _fetchString(i_crm, BLWording.TOKEN);
|
|
|
|
//#if !DEBUG
|
|
// Dictionary<string, object> dicFormData = null;
|
|
|
|
// CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
|
|
// if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
// {
|
|
// sMsg = crmCheckToken.MSG;
|
|
// break;
|
|
// }
|
|
//#endif
|
|
Command cSelect = null;
|
|
|
|
if (i_crm.DATA.ContainsKey(BLWording.BLOCKS))
|
|
{
|
|
List<QueryJsonElement> lBlocks = null;
|
|
|
|
if (i_crm.DATA[BLWording.BLOCKS] is JArray)
|
|
{
|
|
JArray joBlocks = i_crm.DATA[BLWording.BLOCKS] as JArray;
|
|
lBlocks = joBlocks.ToObject<List<QueryJsonElement>>();
|
|
}
|
|
else if (i_crm.DATA[BLWording.BLOCKS] is List<QueryJsonElement>)
|
|
{
|
|
lBlocks = i_crm.DATA[BLWording.BLOCKS] as List<QueryJsonElement>;
|
|
}
|
|
else
|
|
{
|
|
sMsg = BaseExceptionWord.ex000030;//NO SUPPORT BLOCKS
|
|
break;
|
|
}
|
|
|
|
|
|
sMsg = MakeSelectJoinByBlocks(lBlocks, out cSelect);
|
|
}
|
|
else if (i_crm.DATA.ContainsKey(BLWording.DATA))
|
|
{
|
|
JObject joDic = i_crm.DATA[BLWording.DATA] as JObject;
|
|
Dictionary<string, object> dicSO = joDic.ToObject<Dictionary<string, object>>();
|
|
|
|
Dictionary<string, string> dicSelect = new Dictionary<string, string>();
|
|
|
|
foreach (string sKey in dicSO.Keys)
|
|
{
|
|
object o = dicSO[sKey];
|
|
if (o != null)
|
|
{
|
|
string s = dicSO[sKey].ToString();
|
|
if (s.Trim().Length != 0)
|
|
{
|
|
if (ebDisplay.GetType().GetProperty(sKey) != null)
|
|
{
|
|
dicSelect.Add(sKey, s);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(ebDisplay.GetType()), ebDisplay, dicSelect);
|
|
}
|
|
else
|
|
{
|
|
cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(ebDisplay.GetType()), ebDisplay);
|
|
}
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
//nNumOfPage = reRangeNumOfPage(nNumOfPage);
|
|
QueryDataSet qds = adbm.RunQueryDataSet(cSelect, nPageIdx, nNumOfPage);
|
|
|
|
if (qds.IsSuccess == true)
|
|
{
|
|
crmRes = new CSuccessResponseMessage(null, i_crm);
|
|
queryResponse qr = new queryResponse(qds);
|
|
crmRes.DATA.Add(BLWording.ENTITYS, qr);
|
|
}
|
|
else
|
|
{
|
|
sMsg = qds.ErrorCode;
|
|
}
|
|
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public string MakeSelectJoinByBlocks(List<QueryJsonElement> i_lBlocks, out Command o_cSelect)
|
|
{
|
|
string sMsg = null;
|
|
Command cTemp = null;
|
|
|
|
do
|
|
{
|
|
if (i_lBlocks == null || i_lBlocks.Count() == 0)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000032; //NO BLOCK
|
|
break;
|
|
}
|
|
|
|
convertWherenode(ref i_lBlocks);
|
|
|
|
QueryJson qj = new QueryJson();
|
|
|
|
qj.AddBlock(i_lBlocks.ToArray());
|
|
|
|
sMsg = qj.MakeCommand(cdbm.GetTableInfo(new CustomizeDBMgr().GetEntityType(i_lBlocks[0].table)), out cTemp);
|
|
}
|
|
while (false);
|
|
|
|
o_cSelect = cTemp;
|
|
return sMsg;
|
|
}
|
|
|
|
protected void convertWherenode(ref List<QueryJsonElement> io_data)
|
|
{
|
|
ClassHelper ch = new ClassHelper();
|
|
|
|
for (int i = 0; i < io_data.Count(); i++)
|
|
{
|
|
QueryJsonElement qjs = io_data[i];
|
|
|
|
Type t = new CustomizeDBMgr().GetEntityType(qjs.table);
|
|
|
|
qjs.databaseinfo = t.FullName.Split('.')[1];
|
|
|
|
WhereNode wnDicwherecolsCompile = Command.whereDictionary2WhereNode(GetMasterDBTableInfo(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>
|
|
/// Get table row
|
|
/// </summary>
|
|
/// <param name="i_crm"></param>
|
|
/// <returns></returns>
|
|
public CResponseMessage GetInfoData(CRequestMessage i_crm)
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crmRes = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000032;// NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
object oWhere = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oWhere);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
crmRes = GetInfoDataBase(sEntityName, i_crm, oWhere as EntityBase);
|
|
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crmRes = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crmRes;
|
|
}
|
|
|
|
protected string makeWhereNode(CRequestMessage i_crm, Type tRun, Dictionary<string, object> dicFormData, ClassHelper ch, ref WhereNode io_wn)
|
|
{
|
|
string sMsg = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
EntityBase ebWhere = ch.GetInstByType(tRun) as EntityBase;
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
List<WhereNode> lwnWhere = new List<WhereNode>();
|
|
|
|
io_wn = null;
|
|
|
|
JObject jo = null;
|
|
// Support Ethan request: Delete/Update detail but not PK
|
|
bool bUsePK = true;
|
|
|
|
|
|
if (dicFormData.ContainsKey(BLWording.WHEREDATA))
|
|
{
|
|
bUsePK = false;
|
|
jo = dicFormData[BLWording.WHEREDATA] as JObject;
|
|
}
|
|
else if (dicFormData.ContainsKey(BLWording.ORIGINDATA))
|
|
{
|
|
jo = dicFormData[BLWording.ORIGINDATA] as JObject;
|
|
}
|
|
else
|
|
{
|
|
jo = dicFormData[BLWording.DATA] as JObject;
|
|
}
|
|
|
|
foreach (JProperty property in jo.Properties())
|
|
{
|
|
JToken jv = property.Value;
|
|
|
|
dicInput.Add(property.Name, jv.Value<string>());
|
|
|
|
}
|
|
|
|
|
|
PropertyInfo[] pis = tRun.GetProperties();
|
|
|
|
foreach (PropertyInfo pi in pis)
|
|
{
|
|
System.Attribute attr = System.Attribute.GetCustomAttribute(pi, typeof(ColumnMiscAttribute));
|
|
bool bSystemColumn = false; // Default: Not System Column
|
|
bool bPK = false;
|
|
|
|
|
|
if (null != attr)
|
|
{
|
|
ColumnMiscAttribute cma = attr as ColumnMiscAttribute;
|
|
DBColumnInfo dbi = cma.Data;
|
|
bSystemColumn = dbi.DescriptionExtension != null && dbi.DescriptionExtension.SysColumn;
|
|
bPK = dbi.IsPK;
|
|
}
|
|
|
|
if (dicInput.ContainsKey(pi.Name))
|
|
{
|
|
object oValue = dicInput[pi.Name];
|
|
|
|
if (bUsePK == true)
|
|
{
|
|
if (bPK == true)
|
|
{
|
|
if (null != oValue)
|
|
{
|
|
//pi.SetValue(ebWhere, ch.ConvertValue(pi, oValue), null);
|
|
WhereNode wnTemp = new WhereNode(pi.Name, WhereNode.EColumnOperation.EOT_EQ, tRun, ch.ConvertValue(pi, oValue));
|
|
lwnWhere.Add(wnTemp);
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (null != oValue)
|
|
{
|
|
WhereNode wnTemp = new WhereNode(pi.Name, WhereNode.EColumnOperation.EOT_EQ, tRun, ch.ConvertValue(pi, oValue));
|
|
lwnWhere.Add(wnTemp);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
io_wn = new WhereNode(WhereNode.ENodeOperation.ENO_AND, lwnWhere.ToArray());
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
return sMsg;
|
|
}
|
|
|
|
public CResponseMessage EraseEntity(CRequestMessage i_crm)
|
|
{
|
|
CResponseMessage crm = null;
|
|
string sMsg = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
ClassHelper ch = new ClassHelper();
|
|
|
|
Dictionary<string, object> dicFormData = null;
|
|
|
|
CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
{
|
|
sMsg = crmCheckToken.MSG;
|
|
break;
|
|
}
|
|
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033;//NO MATCHED7 ENTITY
|
|
break;
|
|
}
|
|
|
|
object oData = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oData);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
EntityBase ebData = oData as EntityBase;
|
|
|
|
|
|
|
|
// Only return valid data
|
|
if (i_crm.DATA.ContainsKey(BLWording.ACTIONDATA))
|
|
{
|
|
JObject oActionData = i_crm.DATA[BLWording.ACTIONDATA] as JObject;
|
|
Dictionary<string, string> dicSS = oActionData.ToObject<Dictionary<string, string>>();
|
|
ebData.SetValue(otb_user.CN_ACTIVE_FLAG, dicSS[BLWording.MARKVALUE]);
|
|
}
|
|
else
|
|
{
|
|
ebData.SetValue(otb_user.CN_ACTIVE_FLAG, "Y");
|
|
}
|
|
|
|
WhereNode wnWhere = null;
|
|
|
|
sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
|
|
Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);
|
|
int nRes = adbm.RunEditSingleCmd(c);
|
|
|
|
if (0 == nRes)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000034; //MARK FAIL
|
|
break;
|
|
}
|
|
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
public CResponseMessage DeleteEntity(CRequestMessage i_crm)
|
|
{
|
|
CResponseMessage crm = null;
|
|
Util u = new Util();
|
|
ClassHelper ch = new ClassHelper();
|
|
string sMsg = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
Dictionary<string, object> dicFormData = null;
|
|
|
|
CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
{
|
|
sMsg = crmCheckToken.MSG;
|
|
break;
|
|
}
|
|
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
object oData = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oData);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
WhereNode wnWhere = null;
|
|
|
|
sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (wnWhere.IsLeaf == false && wnWhere.ChildrenNodes.Count == 0)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000013; //NO CONDITION
|
|
break;
|
|
}
|
|
|
|
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);
|
|
|
|
int nRes = adbm.RunEditSingleCmd(c);
|
|
|
|
/*if (0 == nRes)
|
|
{
|
|
sMsg = "REMOVE FAIL";
|
|
break;
|
|
}*/
|
|
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
crm.DATA.Add(BLWording.REMOVECOUNT, nRes);
|
|
|
|
//crm.DATA.Add(BLWording.COMMONPK, c.LastInsertIdentity);
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = u.GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
public CResponseMessage UpdateEntity(CRequestMessage i_crm)
|
|
{
|
|
Util u = new Util();
|
|
ClassHelper ch = new ClassHelper();
|
|
CResponseMessage crm = null;
|
|
string sMsg = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
Dictionary<string, object> dicFormData = null;
|
|
|
|
CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
{
|
|
sMsg = crmCheckToken.MSG;
|
|
break;
|
|
}
|
|
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
object oData = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oData);
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
Type tRun = oData.GetType();
|
|
EntityBase ebData = oData as EntityBase;
|
|
EntityBase ebWhere = ch.GetInstByType(tRun) as EntityBase;
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
|
|
JObject jo = dicFormData[BLWording.DATA] as JObject;
|
|
|
|
foreach (JProperty property in jo.Properties())
|
|
{
|
|
JToken jv = property.Value;
|
|
|
|
if (property.Name == otb_user.CN_CREATE_DATE)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
dicInput.Add(property.Name, jv.Value<string>());
|
|
}
|
|
|
|
Dictionary<string, string> dicFillRes = ebData.FillUpadateData(dicInput);
|
|
|
|
if (0 != dicFillRes.Count)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000022; //PARAMETER ERROR 原+=
|
|
break;
|
|
}
|
|
|
|
WhereNode wnWhere = null;
|
|
|
|
sMsg = makeWhereNode(i_crm, oData.GetType(), dicFormData, ch, ref wnWhere);
|
|
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
Command c = Command.SetupUpdateCmd(GetMasterDBTableInfo(oData.GetType()), ebData, wnWhere);
|
|
|
|
// Fix me
|
|
//int nID = (int)crmCheckToken.DATA[BLWording.SESSION_USER_ID];
|
|
//ch.SetValueByPropertyName(ebData, tb_user.CN_FK_N_MODIFIER_USER_ID, nID);
|
|
|
|
int nRes = adbm.RunEditSingleCmd(c);
|
|
|
|
if (0 == nRes)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000011; //UPDATE FAIL
|
|
break;
|
|
}
|
|
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = u.GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
public CResponseMessage CreateEntity(CRequestMessage i_crm)
|
|
{
|
|
CResponseMessage crm = null;
|
|
string sMsg = null;
|
|
Util u = new Util();
|
|
ClassHelper ch = new ClassHelper();
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
Dictionary<string, object> dicFormData = null;
|
|
|
|
CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
{
|
|
sMsg = crmCheckToken.MSG;
|
|
break;
|
|
}
|
|
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
object oInsert = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oInsert);
|
|
if (null != sMsg)
|
|
{
|
|
break;
|
|
}
|
|
|
|
Type tRun = oInsert.GetType();
|
|
EntityBase ebInsert = oInsert as EntityBase;
|
|
Dictionary<string, object> dicInput = new Dictionary<string, object>();
|
|
|
|
JObject jo = dicFormData[BLWording.DATA] as JObject;
|
|
|
|
foreach (JProperty property in jo.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; //PARAMETER ERROR 原+=
|
|
break;
|
|
}
|
|
|
|
|
|
|
|
|
|
// Add User
|
|
Command c = Command.SetupInsertCmd(GetMasterDBTableInfo(oInsert.GetType()), ebInsert);
|
|
|
|
// Fix me
|
|
//int nID = (int)crmCheckToken.DATA[BLWording.SESSION_USER_ID];
|
|
//ch.SetValueByPropertyName(ebInsert, tb_user.CN_FK_N_CREATOR_USER_ID, nID);
|
|
//ch.SetValueByPropertyName(ebInsert, tb_user.CN_FK_N_MODIFIER_USER_ID, nID);
|
|
|
|
int nRes = adbm.RunEditSingleCmd(c);
|
|
|
|
if (0 >= nRes)
|
|
{
|
|
sMsg = c.LastErrorCode;
|
|
break;
|
|
}
|
|
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
|
|
Command cSelect = Command.SetupSelectCmd(GetMasterDBTableInfo(oInsert.GetType()), null, Command.MakeWhereEntityBase2WhereNode(ebInsert));
|
|
QueryDataSet qds = adbm.RunQueryDataSet(cSelect);
|
|
queryResponse qr = new queryResponse(qds);
|
|
crm.DATA.Add(BLWording.ENTITYS, qr);
|
|
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = u.GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
public CResponseMessage ExcelEntity(CRequestMessage i_crm)
|
|
{
|
|
string sMsg = null;
|
|
string sFilePath = "";
|
|
bool bStatus = false;
|
|
CResponseMessage crm = null;
|
|
List<Dictionary<string, object>> list = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (null == sEntityName)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
object oWhere = null;
|
|
|
|
sMsg = getEntity(sEntityName, out oWhere);
|
|
|
|
if (sMsg != null)
|
|
{
|
|
break;
|
|
}
|
|
string sdata = _fetchString(i_crm, "data") ?? "";
|
|
if (sdata != "")
|
|
{
|
|
if (i_crm.DATA["data"] is JArray)
|
|
{
|
|
JArray joData = i_crm.DATA["data"] as JArray;
|
|
list = joData.ToObject<List<Dictionary<string, object>>>();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
crm = GetInfoDataBase(sEntityName, i_crm, oWhere as EntityBase);
|
|
queryResponse data = (queryResponse)crm.DATA[BLWording.ENTITYS];
|
|
list = data.Records;
|
|
}
|
|
bStatus = CreateExcelFiles(list, i_crm, out sFilePath);
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
crm.DATA.Add(BLWording.FILESTATUS, bStatus);
|
|
crm.DATA.Add(BLWording.FILEPATH, sFilePath);
|
|
//Directory.Delete(strFileName);
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
public 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
|
|
{
|
|
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.DATA.ContainsKey(BLWording.ITEMS))
|
|
{
|
|
JObject joItems = i_crm.DATA[BLWording.ITEMS] as JObject;
|
|
items = joItems.ToObject<Dictionary<string, object>>();
|
|
}
|
|
|
|
// Workarond: Forward capbility for DBService.ExcelEntity
|
|
if (items == null && i_crm.DATA.ContainsKey(BLWording.DATA))
|
|
{
|
|
JObject dic = i_crm.DATA[BLWording.DATA] as JObject;
|
|
JObject joItems = dic[BLWording.ITEMS] as JObject;
|
|
if (null == 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 (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 = sJsonKey ?? "";
|
|
scartonkey = scartonkey ?? "";
|
|
int cellIndex = 0;
|
|
foreach (Dictionary<string, object> _list in i_dicData)
|
|
{
|
|
if (_list != 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)
|
|
{ //箱號當為0時顯示為空
|
|
cell.SetCellValue((s == "0" ? "" : s));
|
|
}
|
|
else
|
|
{
|
|
cell.SetCellValue(s);
|
|
}
|
|
cellIndex++;
|
|
}
|
|
else
|
|
{
|
|
Dictionary<string, object> dicData = new Dictionary<string, object>();
|
|
JObject jsonObj = (JObject)JsonConvert.DeserializeObject(s);
|
|
Dictionary<string, object> jsonData = jsonObj.ToObject<Dictionary<string, object>>();
|
|
dicData = jsonData;
|
|
//if (jsonData["scan_type_flag"].ToString() == "1")
|
|
//{
|
|
// JArray jDatas = jsonData["ScanDatas"] as JArray;
|
|
// jsonData = jDatas.Count > 0 ? getToExcelData(jDatas) : new Dictionary<string, object>();
|
|
//}
|
|
foreach (string _sKey in jsonData.Keys)
|
|
{
|
|
if (_sKey.ToLower().IndexOf("_scan_sval") > -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 = "Uploads/ExpExcel/";
|
|
sFileName = _fetchString(i_crm, "filename");
|
|
sFileName = (sFileName ?? "") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
|
|
string sSeverPath = AppDomain.CurrentDomain.BaseDirectory + "/" + sFilePath;
|
|
string sFileFullName = sSeverPath + "/" + sFileName;
|
|
if (!Directory.Exists(sSeverPath))
|
|
{
|
|
Directory.CreateDirectory(sSeverPath);
|
|
}
|
|
//保存
|
|
using (MemoryStream ms = new MemoryStream())
|
|
{
|
|
using (FileStream fs = new FileStream(sFileFullName, FileMode.Create, FileAccess.Write))
|
|
{
|
|
workbook.Write(fs);
|
|
bStatus = true;
|
|
}
|
|
}
|
|
}
|
|
while (false);
|
|
|
|
o_sFilePath = sFilePath + sFileName;
|
|
return bStatus;
|
|
}
|
|
/// <summary>
|
|
/// 整合性報表案例1json字串處理
|
|
/// </summary>
|
|
/// <param name="i_crm">With given table name</param>
|
|
/// <returns>list of property</returns>
|
|
public Dictionary<string, object> getToExcelData(JArray i_jaData)
|
|
{
|
|
Dictionary<string, object> dicData = i_jaData.ToObject<Dictionary<string, object>>();
|
|
if (i_jaData.Count > 0)
|
|
{
|
|
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().IndexOf("_scan_sval") > -1)
|
|
{
|
|
if (dicData[sKey] != null)
|
|
{
|
|
dicData[sKey] += ";" + dic[sKey];
|
|
}
|
|
else
|
|
{
|
|
dicData.Add(sKey, dic[sKey]);
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
return dicData;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// To fetch column info from a given table name
|
|
/// </summary>
|
|
/// <param name="i_crm">With given table name</param>
|
|
/// <returns>list of property</returns>
|
|
public CResponseMessage GetEntityProperties(CRequestMessage i_crm)
|
|
{
|
|
string sMsg = null;
|
|
CResponseMessage crm = null;
|
|
|
|
try
|
|
{
|
|
do
|
|
{
|
|
Dictionary<string, object> dicFormData = null;
|
|
|
|
CResponseMessage crmCheckToken = checkTokenWithCRequestMessage(i_crm, out dicFormData);
|
|
|
|
if (EResponseResult.RR_FALSE == crmCheckToken.RESULT)
|
|
{
|
|
sMsg = crmCheckToken.MSG;
|
|
break;
|
|
}
|
|
|
|
string sEntityName = _fetchString(i_crm, BLWording.CMDENTITYTYPE);
|
|
|
|
if (sEntityName == null)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000035; //NO ENTITY
|
|
break;
|
|
}
|
|
|
|
Type tTest = new CustomizeDBMgr().GetEntityType(sEntityName);
|
|
|
|
if (tTest == null)
|
|
{
|
|
sMsg = BaseExceptionWord.ex000033; //NO MATCHED ENTITY
|
|
break;
|
|
}
|
|
|
|
|
|
PropertyInfo[] pis = tTest.GetProperties();
|
|
List<DBColumnInfo> lColumns = new List<DBColumnInfo>();
|
|
|
|
foreach (PropertyInfo pi in pis)
|
|
{
|
|
System.Attribute attr = System.Attribute.GetCustomAttribute(pi, typeof(ColumnMiscAttribute));
|
|
|
|
if (null != attr)
|
|
{
|
|
ColumnMiscAttribute cma = attr as ColumnMiscAttribute;
|
|
DBColumnInfo dbi = cma.Data;
|
|
|
|
if (dbi != null)
|
|
{
|
|
lColumns.Add(dbi);
|
|
}
|
|
}
|
|
}
|
|
lColumns = lColumns.OrderBy(o => o.COLUMN_NAME).ToList();
|
|
crm = new CSuccessResponseMessage(null, i_crm);
|
|
crm.DATA.Add(BLWording.LISTCOLUMNS, lColumns);
|
|
}
|
|
while (false);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sMsg = new Util().GetLastExceptionMsg(ex);
|
|
}
|
|
|
|
if (null != sMsg)
|
|
{
|
|
crm = new CErrorResponseMessage(sMsg, i_crm);
|
|
}
|
|
|
|
return crm;
|
|
}
|
|
|
|
|
|
public static string GetLastErrorCode(Command i_cCmd)
|
|
{
|
|
return (i_cCmd.IsSuccess == false) ? i_cCmd.LastErrorCode : null;
|
|
}
|
|
|
|
public static string GetLastErrorCode(List<Command> i_lcCmds)
|
|
{
|
|
string sRes = null;
|
|
|
|
if (i_lcCmds.Find(c => (c.IsSuccess == false)) != null)
|
|
{
|
|
sRes = i_lcCmds.Find(c => (c.IsSuccess == false)).LastErrorCode;
|
|
}
|
|
|
|
return sRes;
|
|
}
|
|
}
|
|
}
|