/// <summary>
/// 當資料庫建值前置名稱有異動時，可呼叫此程序變更之。
/// 將以Link關係自動找出所有應改異動的件值
/// <param node_4="primayr_key_fieldName">ex.EODM_DeptMemberId</param>
/// <param node_4="oldKeyPrefix">ex.EODE_</param>
/// </summary>
public static void alterKeyPrefixName(string primary_key_fieldName, string oldKeyPrefix)
{
var fields = FdmService.Instance.AllColumns
.Select(x => x.Value)
.Where(x => !x.Table.IsSessionTable && x.Table.TableKind == FdmTableKindEnum.Table)
.Where(x => x.ColumnKind == FdmColumnKind.Link && x.Links.exists(x2 => x2.LinkKey.ColumnName == primary_key_fieldName))
.ToList();
fields.Add(FdmService.Instance.AllColumns[primary_key_fieldName]);
//逐一修正每一個鍵值
var o_table = FdmService.Instance.AllColumns[primary_key_fieldName].Table;
foreach (var field in fields)
{
var qry = new NsDmQuery();
var t1 = qry.from(field.Table.TableName);
qry.Where = t1[field.ColumnName].left(oldKeyPrefix.Length) == oldKeyPrefix;
var dt = qry.queryData();
foreach (DataRow row in dt.Rows)
{
var keynew = o_table.KeyPrefix + row.getString(field.ColumnName).Substring(oldKeyPrefix.Length);
row[field.ColumnName] = keynew;
}
dt.ns_update();
}
}
/// <summary>
/// 將所有的空字串改成NULL
/// </summary>
public static void step1()
{
foreach (var table in FdmService.Instance.AllTables)
{
var t1 = table.Value;
if (t1.IsSessionTable)
{
continue;
}
if (t1.TableKind != FdmTableKindEnum.Table)
{
continue;
}
var cols = t1.Columns.Where(x => x.DataType.DotNetType == typeof(string)).Select(x => x).ToArray();
var dmqry = new NsDmQuery();
var t2 = dmqry.from(t1.TableName);
var dt = dmqry.queryData();
foreach (DataRow row in dt.Rows)
{
row.SetModified();
}
dt.ns_update();
Console.WriteLine(t1.TableName);
}
}
private static void do_db_clone()
{
var db_ctx = new FdbConfigContext();
var dbii_ctx = new FdbConfigContext();
foreach (var sys in FdmService.Instance.AllSystems.Values)
{
var cn = FtdConfigService.Instance.ConfigData.DbConns.findKey(sys.SystemName + "_II");
dbii_ctx.setSystemConn(sys.SystemName, cn);
}
//Copy所有SERVERII表格
var qry = new NsDmQuery();
//var is_cont = false;
foreach (var table in FdmService.Instance.AllTables.Values.OrderBy(x => x.TableName))
{
//if (table.System.SystemName == "EO")
// continue;
if (table.IsSessionTable)
{
continue;
}
if (table.TableKind != FdmTableKindEnum.Table)
{
continue;
}
if (table.TableProvider.DbReadOnly)
{
continue;
}
//if (table.TableName == AppDataName.HC_VitalSignLimit)
//{
// is_cont = true;
//}
//if (!is_cont)
// continue;
qry.from(table.TableName);
qry.selectAll();
var dt = qry.queryData();
using (dbii_ctx.Use)
{
var provider = table.TableProvider;
Console.WriteLine("Truncate Table : " + table.TableName);
provider.DbAccess.truncateTable(provider.DbTableName);
Console.WriteLine("Import Table : " + table.TableName);
foreach (DataRow row in dt.Rows)
{
row.SetAdded();
}
dt.ns_update_import_mode();
}
}
Console.WriteLine("Import OK");
}
protected override DataTable getMailQueues()
{
var qry = new NsDmQuery();
qry.from <WT_MailQueue>();
var dt = qry.queryData();
return(dt);
}
protected override DataTable queryTasks()
{
var qry = new NsDmQuery();
qry.from <WT_ScheduleTask>();
var dt = qry.queryData();
return(dt);
}
protected override DataTable queryDates(DataRow rowTask)
{
var rowTask2 = rowTask as WT_ScheduleTaskRow;
var qry = new NsDmQuery();
var t1 = qry.from <WT_ScheduleDate>();
qry.Where = t1.WTSD_ScheduleTaskId == rowTask2.WTST_ScheduleTaskId.toConstReq1();
var dt = qry.queryData();
return(dt);
}
public EO_PermissionSettingDataTable queryPermissionSettingsBy(string[] objectIds, string[] permCodes, string[] extendFieldNames)
{
var qry = new NsDmQuery();
var t1 = qry.from <EO_PermissionSetting>();
qry.selectAll(extendFieldNames);
qry.Where = t1.EOPS_ObjectId.batchin(objectIds.toConstReq1())
& t1.EOPS_PermissionCode_XX.@in(permCodes.toConstReq1());
var dt = (EO_PermissionSettingDataTable)qry.queryData();
return(dt);
}
public EO_PermissionSettingRow queryPermissionSettingByPermIdUserId(string permId, string userId, string[] extendFieldNames)
{
var qry = new NsDmQuery();
var t1 = qry.from <EO_PermissionSetting>();
qry.selectAll(extendFieldNames);
qry.Where = t1.EOPS_PermissionId == permId.toConstReq1()
& t1.EOPS_PermissionUserId == userId.toConstReq1();
var dt = ((EO_PermissionSettingDataTable)qry.queryData());
return(dt.FirstRow);
}
/// <summary>
/// 紀錄事件
/// </summary>
public static void logEvent(string eventCode, string userId, string objectId)
{
var qry = new NsDmQuery();
{
var t1 = qry.from <EO_UserEvent>();
qry.Where = t1.EOUE_EventCode == eventCode.toConstReq1();
}
var row = qry.queryData <EO_UserEventDataTable>().FirstRow;
//不存在自動新增
{
if (row == null)
{
var dt = new EO_UserEventDataTable();
row = dt.newTypedRow();
row.ns_AssignNewId();
row.EOUE_Description = string.Empty;
row.EOUE_EventCode = eventCode;
row.EOUE_KindName = eventCode;
row.EOUE_ListOrder = 1;
dt.addTypedRow(row);
dt.ns_update();
dt.AcceptChanges();
}
}
////不重複才新增
//if (LastEventCode == eventCode + "_" + objectId)
// return;
//新增此事件
{
var sourceIP = getSourceIP();
var dt2 = new EO_UserEventLogDataTable();
var row2 = dt2.newTypedRow();
row2.ns_AssignNewId();
row2.EOUEL_EventDate = DateTime.Now;
row2.EOUEL_UserEventId = row.EOUE_UserEventId;
row2.EOUEL_UserId = userId;
row2.EOUEL_ObjectId = objectId;
row2.EOUEL_SourceIP = sourceIP;
dt2.addTypedRow(row2);
dt2.ns_update();
}
//LastEventCode = eventCode + "_" + objectId;
}
protected override FtdDbFile getDatabaseFile(string fileId)
{
var qry = new NsDmQuery();
if (fileId.StartsWith("WTWF_"))
{
var t1 = qry.from <WT_WebFile>();
qry.selectAll(t1.WTWF_StorageFullName_XX);
qry.Where = t1.WTWF_WebFileId == fileId.toConstReq1();
var row = qry.queryData <WT_WebFileDataTable>().FirstRow;
FtdDbFile dfile = new FtdDbFile();
dfile.FileId = fileId;
dfile.FileFullName = row.WTWF_StorageFullName_XX;
return(dfile);
}
return(base.getDatabaseFile(fileId));
}
/// <summary>
/// 取得指定物件新權限設定所必須的改變
/// </summary>
public EO_PermissionSettingDataTable infoObjectNewPermissionSettings(string objectId, string permissionCode, List <string> newUserIds)
{
var qry = new NsDmQuery();
var dt1 = queryPermissionSettingsByObjectIdsPermId(new string[] { objectId }, permissionCode, new string[] { });
var oldUserIds = new List <string>();
{
foreach (var row1 in dt1.TypeRows)
{
oldUserIds.Add(row1.EOPS_PermissionUserId);
}
}
var t1 = qry.from <EO_Permission>();
qry.Where = t1.EOP_PermissionCode == permissionCode.toConstReq1();
var rowp = ((EO_PermissionDataTable)qry.queryData()).FirstRow;
//新人員不存在者新增
foreach (var userid in newUserIds)
{
if (!oldUserIds.Contains(userid))
{
var row1 = dt1.newTypedRow();
row1.ns_AssignNewId();
row1.EOPS_PermissionUserId = userid;
row1.EOPS_PermissionId = rowp.EOP_PermissionId;
row1.EOPS_ObjectId = objectId;
dt1.addTypedRow(row1);
}
}
//舊人員不存在者刪除
foreach (var userid in oldUserIds)
{
if (!newUserIds.Contains(userid))
{
dt1.Rows[oldUserIds.IndexOf(userid)].Delete();
}
}
return(dt1);
}
/// <summary>
/// 取得給使用者的權限
/// </summary>
public EO_PermissionDataTable queryPermissionForUsers(string[] userIds, string[] extendFieldNames)
{
var qrydb = new NsDbQuery();
DataTable dt1 = null;
{
//人員所擁有的權限
qrydb.setSelect(s =>
{
var t1 = s.from <EO_PermissionSetting>();
var t2 = s.join <EO_Permission>()
.on(t => t.EOP_PermissionId == t1.EOPS_PermissionId);
s.select(t2.EOP_PermissionId);
s.Where = t1.EOPS_PermissionUserId.batchin(userIds.toConstReq1())
& t2.EOP_IsObjectNeed == "N"
& t2.EOP_IsEveryOneAllow == "N";
});
dt1 = qrydb.queryData();
}
{
//EveryOne都可以有的權限
qrydb.setSelect(s =>
{
var t1 = s.from <EO_Permission>();
s.select(t1.EOP_PermissionId);
s.Where = t1.EOP_IsObjectNeed == "N"
& t1.EOP_IsEveryOneAllow == "Y";
});
qrydb.fillData(dt1);
}
{
var pks = FtdDataHelper.getDistinctArray <string>(dt1, dt1.Columns[0]);
var qry = new NsDmQuery();
var t1 = qry.from <EO_Permission>();
qry.selectAll(extendFieldNames);
qry.Where = t1.EOP_PermissionId.batchin(pks.toConstReq1());
var dt = (EO_PermissionDataTable)qry.queryData();
return(dt);
}
}
public static void run()
{
var tables = new List <DataTable>();
foreach (var sys in FdmService.Instance.AllSystems)
{
foreach (var table in sys.Value.Tables)
{
if (table.IsSessionTable)
{
continue;
}
Console.WriteLine(DateTime.Now.ToString("HH:mm:ss") + " " + table.TableName);
var qry = new NsDmQuery();
var t1 = qry.from(table.TableName);
qry.selectAll(t1.AllExt);
qry.queryData();
}
}
}
/// <summary>
/// 登入資料驗證
/// </summary>
public virtual bool accountValidate(string account, string password, ref string empId, out string errorMsg)
{
errorMsg = "";
var qry = new NsDmQuery();
var t1 = qry.from <EO_LoginAccount>();
qry.Where = t1.EOLA_LoginAccount == account.toConstReq1();
var rowa = ((EO_LoginAccountDataTable)qry.queryData()).FirstRow;
if (rowa == null)
{
//資安考量：勿明確說明哪一種錯誤
errorMsg = "帳號或密碼錯誤";
return(false);
}
//停用
if (rowa.EOLA_IsEnable == "F")
{
errorMsg = "帳號已停用";
return(false);
}
//登入次數太多招致停權五分鐘
if (rowa.EOLA_FailureDate.HasValue)
{
if (rowa.EOLA_FailureDate.Value > DateTime.Now)
{
errorMsg = string.Format("登入失敗超過三次停權五分鐘，請於「{0}」後再試。", rowa.EOLA_FailureDate.Value.ToString("HH:mm", CultureInfo.InvariantCulture));
return(false);
}
else
{
//停權已超過5分鐘的話，解除之
rowa.EOLA_FailureDate = null;
rowa.EOLA_FailureCount = 0;
rowa.ns_update();
}
}
//密碼驗證
if (!rowa.EOLA_LoginPassword.equalIgnoreCase(password.Trim()))
{
rowa.EOLA_FailureCount = rowa.EOLA_FailureCount + 1;
if (rowa.EOLA_FailureCount >= 3)
{
rowa.EOLA_FailureDate = DateTime.Now.AddMinutes(5);
}
rowa.ns_update();
//資安考量：勿明確說明哪一種錯誤
errorMsg = "帳號或密碼錯誤";
return(false);
}
{
rowa.EOLA_LastLoginDate = DateTime.Now;
rowa["EOLA_FailureDate"] = DBNull.Value;
rowa.EOLA_FailureCount = 0;
rowa.ns_update();
}
//登入者
empId = rowa.EOLA_LoginAccountId;
return(true);
}
/// <summary>
/// 會員登入
/// </summary>
public void loginUser(string userId)
{
var qry = new NsDmQuery();
//Guest登入
if (userId == AppLoginUser.GuestUserId)
{
var usr = new AppLoginUser();
usr.UserId = userId;
usr.UserName = "******";
usr.UserPermission = FtdPermissionService.Instance.getActorPermission(usr.UserId);
usr.TitleName = "訪客";
usr.TitleCode = "";
usr.GridAllowPaging = bool.Parse(FtdConfigService.Instance.getAppSettingValue("FwbUiService.GridAllowPaging", "true"));
usr.MachineCode = FtdConfigService.Instance.getAppSettingValue("MachineCode", "true");
//usr.UrlGIS = FtdConfigService.Instance.getAppSettingValue("URL_GIS", "");
//usr.UrlKmWs = FtdConfigService.Instance.getAppSettingValue("URL_KMWS", "");
//usr.UrlKmEntry = FtdConfigService.Instance.getAppSettingValue("URL_KMEntry", "");
//usr.IsControlled = false;
LoginUser = usr;
return;
}
//有帳號的會員登入
else
{
var t1 = qry.from <EO_Employee>();
qry.selectAll(
t1.AllDirect
);
qry.Where = t1.EOE_EmployeeId == userId.toConstReq1();
var dt = (EO_EmployeeDataTable)qry.queryData();
var rowusr = dt.FirstRow;
if (rowusr == null)
{
return;
}
var usr = new AppLoginUser();
usr.LoginAccount = rowusr.EOE_LoginAccount_XX;
usr.UserId = rowusr.EOE_EmployeeId;
usr.UserName = rowusr.EOE_EmployeeName;
usr.TitleCode = rowusr.EOE_EmployeeTitleCode_XX;
usr.TitleName = rowusr.EOE_EmployeeTitleName_XX;
usr.DepartmentId = rowusr.EOE_DepartmentId;
usr.DepartmentName = rowusr.EOE_DepartmentName_XX;
//usr.IsOrganManager = rowusr.EOE_IsOrganManager;
//usr.OrganId = rowusr.EOE_OrganId;
//usr.OrganSName = rowusr.EOE_OrganSName_XX;
//usr.OrganAName = rowusr.EOE_OrganAName_XX;
//usr.OrganShortId = rowusr.EOE_OrganShortId_XX;
//usr.StoreLoc = rowusr.EOE_OrganStoreLoc_XX;
//usr.IsCity = rowusr.EOE_OrganTitle_XX;
//usr.IsEnterpriseLimit = rowusr.EOE_EnterpriseLimit_XX;
//usr.IsOfficialLimit = rowusr.EOE_OfficialLimit_XX;
//usr.UnitName = rowusr.EOE_UnitName;
usr.Phone1 = rowusr.EOE_Phone1;
usr.Phone2 = rowusr.EOE_Phone2;
usr.Email = rowusr.EOE_EmployeeEmail;
usr.GridAllowPaging = bool.Parse(FtdConfigService.Instance.getAppSettingValue("FwbUiService.GridAllowPaging", "true"));
usr.UserPermission = FtdPermissionService.Instance.getActorPermission(usr.UserId);
#region //找出父、子階部門id
//var dtDept = NsDmHelper.EO_Department
// .selectAll()
// .query();
//#region //找父階部門id
//List<string> parentIds = new List<string>();
//Action<string, List<string>> gerParentIdFunc = null;
//gerParentIdFunc = (deptId, ids) =>
//{
// var dept = dtDept.Where(x => x.EOD_DepartmentId == deptId).FirstOrDefault();
// if (dept == null || dept.EOD_ParentId.isNullOrEmpty())
// return;
// ids.Add(dept.EOD_ParentId);
// gerParentIdFunc(dept.EOD_ParentId, ids);
//};
//gerParentIdFunc(usr.DepartmentId, parentIds);
//#endregion
//#region //找子階部門id
//List<string> childIds = new List<string>();
//Action<string, List<string>> gerChildIdFunc = null;
//gerChildIdFunc = (deptId, ids) =>
//{
// var depts = dtDept.Where(x => x.EOD_ParentId == deptId);
// if (depts == null || depts.Count() == 0)
// return;
// foreach (var d in depts)
// {
// ids.Add(d.EOD_DepartmentId);
// gerChildIdFunc(d.EOD_DepartmentId, ids);
// }
//};
//gerChildIdFunc(usr.DepartmentId, childIds);
//#endregion
//if (parentIds.Count > 0)
// usr.ParentDeptId = parentIds[0];
//else
// usr.ParentDeptId = "";
//usr.ParentDeptIds = parentIds.ToArray();
//usr.ChildDeptIds = childIds.ToArray();
#endregion
usr.MachineCode = FtdConfigService.Instance.getAppSettingValue("MachineCode", "true");
//usr.UrlGIS = FtdConfigService.Instance.getAppSettingValue("URL_GIS", "");
//usr.UrlGISLink = FtdConfigService.Instance.getAppSettingValue("URL_GISLink", "");
//usr.UrlKmWs = FtdConfigService.Instance.getAppSettingValue("URL_KMWS", "");
//usr.UrlKmEntry = FtdConfigService.Instance.getAppSettingValue("URL_KMEntry", "");
LoginUser = usr;
usr.UserMenu = EoDataService.Instance.getUserMenus(rowusr.EOE_EmployeeId);
var userMenu = usr.UserMenu;
var xmlMenuNodes = userMenu.selectNodes(node => true
//, new[] { "itemNo", "class", "title" }
);
usr.UserFunctions = xmlMenuNodes;
var dicUsrFunPermSet = EoDataService.Instance.queryUserFunPermSet(usr.UserId, "EOM_MAINMENU");
usr.UserFunPermSet = dicUsrFunPermSet;
//是否管控中
//int cntPM = PmDataService.Instance.PmControlCase_getExpiredCount(usr.OrganId);
//usr.IsControlled = (cntPM > 0);
}
}