/// <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); } }