/// <summary>
/// 是否为管理员
/// </summary>
/// <param name="userId">用户编号</param>
/// <returns></returns>
public static bool IsAdministrator(string userId)
{
var result = false;
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection))
{
var commandText = @"SELECT COUNT(*) FROM " + BaseUserEntity.CurrentTableName
+ " WHERE Id = " + dbHelper.GetParameter(BaseUserEntity.FieldId)
+ " AND " + BaseUserEntity.FieldEnabled + " = " + dbHelper.GetParameter(BaseUserEntity.FieldEnabled)
+ " AND " + BaseUserEntity.FieldDeleted + " = " + dbHelper.GetParameter(BaseUserEntity.FieldDeleted)
+ " AND IsAdministrator = 1";
var dbParameters = new List <IDbDataParameter>
{
dbHelper.MakeParameter(BaseUserEntity.FieldId, userId),
dbHelper.MakeParameter(BaseUserEntity.FieldEnabled, 1),
dbHelper.MakeParameter(BaseUserEntity.FieldDeleted, 0)
};
var obj = dbHelper.ExecuteScalar(commandText, dbParameters.ToArray());
if (obj != null && Convert.ToInt32(obj) > 0)
{
result = true;
}
}
return(result);
}
/// <summary>
/// 服务器端检查在线状态
/// </summary>
/// <param name="taskId">任务标识</param>
/// <returns>离线人数</returns>
public int ServerCheckOnline(string taskId)
{
var result = 0;
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType))
{
try
{
dbHelper.Open(BaseSystemInfo.UserCenterWriteDbConnection);
var userLogonManager = new BaseUserLogonManager(dbHelper);
result = userLogonManager.CheckOnline();
}
catch (Exception ex)
{
LogUtil.WriteException(ex);
throw;
}
finally
{
dbHelper.Close();
}
}
return(result);
}
/// <summary>
/// ProcessUserCenterWriteDbWithLock
/// </summary>
/// <param name="userInfo"></param>
/// <param name="parameter"></param>
/// <param name="locker"></param>
/// <param name="fun"></param>
public static void ProcessUserCenterWriteDbWithLock(BaseUserInfo userInfo, ServiceInfo parameter, object locker, ProcessFunWithLock fun)
{
if (BaseSystemInfo.IsAuthorized(userInfo))
{
var milliStart = Begin(parameter.UserInfo, parameter.CurrentMethod);
var getOnline = false;
lock (locker)
{
using (var dbHelper = DbHelperFactory.Create(GetDbType(DbType.UserCenterWrite)))
{
try
{
dbHelper.Open(GetDbConnection(DbType.UserCenterWrite));
getOnline = fun(dbHelper, getOnline);
AddLog(parameter);
}
catch (Exception ex)
{
BaseExceptionManager.LogException(dbHelper, parameter.UserInfo, ex);
throw;
}
finally
{
dbHelper.Close();
}
}
}
End(parameter.UserInfo, milliStart, parameter.CurrentMethod, getOnline);
}
}
/// <summary>
/// 构造函数
/// </summary>
public BaseUserScopeManager()
{
if (dbHelper == null)
{
dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection);
}
CurrentTableName = BasePermissionScopeEntity.CurrentTableName;
}
static void Main(string[] args)
{
// Oracle
var connectionString = "";
var dbHelper = DbHelperFactory.Create(CurrentDbType.Oracle, connectionString);
var commandText = string.Empty;
dbHelper.ExecuteNonQuery(commandText);
}
/// <summary>
/// 获取数据库服务器时间
/// <param name="taskId">任务标识</param>
/// </summary>
/// <returns>数据库时间</returns>
public DateTime GetDbDateTime(string taskId)
{
var result = DateTime.Now;
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection))
{
result = DateTime.Parse(dbHelper.GetDbDateTime());
}
return(result);
}
private static void AddLogTask(object param)
{
var tuple = param as Tuple <string, string, string, string, string, string, string, Tuple <string> >;
if (tuple != null)
{
var systemCode = tuple.Item1;
var userId = tuple.Item2;
var userName = tuple.Item3;
var nickName = tuple.Item4;
var ipAddress = tuple.Item5;
var ipAddressName = tuple.Item6;
var macAddress = tuple.Item7;
var loginStatus = string.Empty;
if (tuple.Rest != null)
{
loginStatus = tuple.Rest.Item1;
}
var entity = new BaseLogonLogEntity
{
SystemCode = systemCode,
UserId = userId.ToInt(),
UserName = userName,
//Troy.Cui 20160927
NickName = nickName,
IpAddress = ipAddress,
IpAddressName = ipAddressName,
MacAddress = macAddress,
LogonStatus = loginStatus,
LogLevel = LogonStatusToLogLevel(loginStatus),
CreateTime = DateTime.Now
};
var tableName = GetSplitTableName();
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection))
{
var loginLogManager = new BaseLogonLogManager(tableName);
try
{
// 2015-07-13 把登录日志无法正常写入的，进行日志记录
loginLogManager.Add(entity);
}
catch (Exception ex)
{
LogUtil.WriteLog("AddLogTask: 异常信息:" + ex.Message
+ Environment.NewLine + "错误源:" + ex.Source
+ Environment.NewLine + "堆栈信息:" + ex.StackTrace, "Log");
}
}
}
}
/// <summary>
/// 构造函数
/// </summary>
public BaseDictionaryManager()
{
if (dbHelper == null)
{
dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection);
}
if (string.IsNullOrEmpty(CurrentTableName))
{
CurrentTableName = BaseDictionaryEntity.CurrentTableName;
//按用户公司分表
//CurrentTableName = BaseDictionaryEntity.CurrentTableName + GetTableSuffix();
}
CurrentTableDescription = FieldExtensions.ToDescription(typeof(BaseDictionaryEntity), "CurrentTableName");
PrimaryKey = "Id";
}
/// <summary>
/// 获取用户OpenId
/// </summary>
/// <param name="userInfo">用户信息</param>
/// <param name="cachingSystemCode">缓存SystemCode</param>
/// <returns></returns>
public string GetUserOpenId(BaseUserInfo userInfo, string cachingSystemCode = null)
{
var result = string.Empty;
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterReadDbConnection))
{
// 需要能支持多个业务子系统的登录方法、多密码、多终端登录
var userLogonEntityTableName = "BaseUserLogon";
if (!string.IsNullOrEmpty(cachingSystemCode))
{
userLogonEntityTableName = cachingSystemCode + "UserLogon";
}
var parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(BaseUserLogonEntity.FieldUserId, userInfo.Id)
};
result = DbUtil.GetProperty(dbHelper, userLogonEntityTableName, parameters, BaseUserLogonEntity.FieldOpenId);
dbHelper.Close();
}
return(result);
}
/// <summary>
/// ProcessDb
/// </summary>
/// <param name="serviceInfo"></param>
/// <param name="processFun"></param>
/// <param name="dbType"></param>
/// <param name="inTransaction"></param>
private static void ProcessDbHelp(ServiceInfo serviceInfo, ProcessFun processFun, DbType dbType, bool inTransaction)
{
// 2016-02-14 吉日嘎拉 增加耗时记录功能
var stopwatch = new Stopwatch();
stopwatch.Start();
using (var dbHelper = DbHelperFactory.Create(GetDbType(dbType), GetDbConnection(dbType)))
{
try
{
// dbHelper.Open(GetDbConnection(dbType));
if (inTransaction)
{
// dbHelper.BeginTransaction();
}
processFun(dbHelper);
stopwatch.Stop();
serviceInfo.ElapsedTicks = stopwatch.ElapsedTicks;
AddLog(serviceInfo);
if (inTransaction)
{
// dbHelper.CommitTransaction();
}
}
catch (Exception ex)
{
if (inTransaction)
{
// dbHelper.RollbackTransaction();
}
BaseExceptionManager.LogException(dbHelper, serviceInfo.UserInfo, ex);
throw;
}
}
}
// public async Task
/// <summary>
/// 更新访问数据
/// </summary>
/// <param name="userLogonEntity"></param>
/// <param name="openId"></param>
/// <param name="createOpenId"></param>
public void UpdateVisitTimeTask(BaseUserLogonEntity userLogonEntity, string openId, bool createOpenId = true)
{
var errorMark = 0;
var sql = string.Empty;
//默认给OpenId 8个小时有效期，每次更新在线状态的时候，再刷新一下OpenId的有效期，Troy.Cui 2020-02-29
DateTime?openIdTimeout = DateTime.Now.AddHours(8);
try
{
using (var dbHelper = DbHelperFactory.Create(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection))
{
// 是否更新访问日期信息
List <IDbDataParameter> dbParameters = null;
// 若有一周没登录了，需要重新进行手机验证
var mobileNeedValiated = false;
if (userLogonEntity.PreviousVisitTime.HasValue || userLogonEntity.FirstVisitTime.HasValue)
{
var ts = new TimeSpan();
if (userLogonEntity.LastVisitTime.HasValue)
{
ts = DateTime.Now.Subtract((DateTime)userLogonEntity.LastVisitTime);
mobileNeedValiated = (ts.TotalDays > 7);
}
else if (userLogonEntity.FirstVisitTime.HasValue)
{
ts = DateTime.Now.Subtract((DateTime)userLogonEntity.FirstVisitTime);
mobileNeedValiated = (ts.TotalDays > 7);
}
if (mobileNeedValiated)
{
sql = "UPDATE " + BaseUserContactEntity.CurrentTableName
+ " SET " + BaseUserContactEntity.FieldMobileValidated + " = 0 "
+ " WHERE " + BaseUserContactEntity.FieldUserId + " = " + DbHelper.GetParameter(BaseUserContactEntity.FieldUserId)
+ " AND " + BaseUserContactEntity.FieldMobileValidated + " = " + DbHelper.GetParameter(BaseUserContactEntity.FieldMobileValidated);
dbParameters = new List <IDbDataParameter>
{
DbHelper.MakeParameter(BaseUserContactEntity.FieldUserId, userLogonEntity.UserId),
DbHelper.MakeParameter(BaseUserContactEntity.FieldMobileValidated, 1)
};
errorMark = 10;
dbHelper.ExecuteNonQuery(sql, dbParameters.ToArray());
}
}
if (BaseSystemInfo.UpdateVisit)
{
// 第一次登录时间
if (userLogonEntity.FirstVisitTime == null)
{
sql = "UPDATE " + CurrentTableName
+ " SET " + BaseUserLogonEntity.FieldPasswordErrorCount + " = 0 "
+ ", " + BaseUserLogonEntity.FieldUserOnline + " = 1 "
+ ", " + BaseUserLogonEntity.FieldFirstVisitTime + " = " + dbHelper.GetDbNow()
+ ", " + BaseUserLogonEntity.FieldLogonCount + " = 1 "
+ ", " + BaseUserLogonEntity.FieldSystemCode + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldSystemCode)
+ ", " + BaseUserLogonEntity.FieldIpAddress + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldIpAddress)
+ ", " + BaseUserLogonEntity.FieldIpAddressName + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldIpAddressName)
+ ", " + BaseUserLogonEntity.FieldMacAddress + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldMacAddress)
+ ", " + BaseUserLogonEntity.FieldComputerName + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldComputerName);
dbParameters = new List <IDbDataParameter>
{
dbHelper.MakeParameter(BaseUserLogonEntity.FieldSystemCode, userLogonEntity.SystemCode),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldIpAddress, userLogonEntity.IpAddress),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldIpAddressName, userLogonEntity.IpAddressName),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldMacAddress, userLogonEntity.MacAddress),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldComputerName, userLogonEntity.ComputerName)
};
if (createOpenId)
{
sql += ", " + BaseUserLogonEntity.FieldOpenId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenId);
sql += ", " + BaseUserLogonEntity.FieldOpenIdTimeoutTime + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime);
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenId, openId));
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime, openIdTimeout));
}
sql += " WHERE " + BaseUserLogonEntity.FieldUserId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldUserId)
+ " AND " + BaseUserLogonEntity.FieldFirstVisitTime + " IS NULL";
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldUserId, userLogonEntity.UserId));
errorMark = 20;
dbHelper.ExecuteNonQuery(sql, dbParameters.ToArray());
}
else
{
// 最后一次登录时间
sql = "UPDATE " + CurrentTableName
+ " SET " + BaseUserLogonEntity.FieldPasswordErrorCount + " = 0 "
+ ", " + BaseUserLogonEntity.FieldPreviousVisitTime + " = " + BaseUserLogonEntity.FieldLastVisitTime
+ ", " + BaseUserLogonEntity.FieldUserOnline + " = 1 "
+ ", " + BaseUserLogonEntity.FieldLastVisitTime + " = " + dbHelper.GetDbNow()
+ ", " + BaseUserLogonEntity.FieldLogonCount + " = " + BaseUserLogonEntity.FieldLogonCount + " + 1 "
+ ", " + BaseUserLogonEntity.FieldSystemCode + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldSystemCode)
+ ", " + BaseUserLogonEntity.FieldIpAddress + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldIpAddress)
+ ", " + BaseUserLogonEntity.FieldIpAddressName + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldIpAddressName)
+ ", " + BaseUserLogonEntity.FieldMacAddress + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldMacAddress)
+ ", " + BaseUserLogonEntity.FieldComputerName + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldComputerName);
dbParameters = new List <IDbDataParameter>
{
dbHelper.MakeParameter(BaseUserLogonEntity.FieldSystemCode, userLogonEntity.SystemCode),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldIpAddress, userLogonEntity.IpAddress),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldIpAddressName, userLogonEntity.IpAddressName),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldMacAddress, userLogonEntity.MacAddress),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldComputerName, userLogonEntity.ComputerName)
};
if (createOpenId)
{
sql += ", " + BaseUserLogonEntity.FieldOpenId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenId);
sql += ", " + BaseUserLogonEntity.FieldOpenIdTimeoutTime + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime);
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenId, openId));
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime, openIdTimeout));
}
sql += " WHERE " + BaseUserLogonEntity.FieldUserId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldUserId);
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldUserId, userLogonEntity.UserId));
errorMark = 30;
dbHelper.ExecuteNonQuery(sql, dbParameters.ToArray());
}
}
else
{
sql = "UPDATE " + CurrentTableName
+ " SET " + BaseUserLogonEntity.FieldPasswordErrorCount + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldPasswordErrorCount)
+ ", " + BaseUserLogonEntity.FieldSystemCode + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldSystemCode);
dbParameters = new List <IDbDataParameter>
{
dbHelper.MakeParameter(BaseUserLogonEntity.FieldPasswordErrorCount, 0),
dbHelper.MakeParameter(BaseUserLogonEntity.FieldSystemCode, userLogonEntity.SystemCode)
};
if (createOpenId)
{
sql += ", " + BaseUserLogonEntity.FieldOpenId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenId);
sql += ", " + BaseUserLogonEntity.FieldOpenIdTimeoutTime + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime);
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenId, openId));
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldOpenIdTimeoutTime, openIdTimeout));
}
sql += " WHERE " + BaseUserLogonEntity.FieldUserId + " = " + dbHelper.GetParameter(BaseUserLogonEntity.FieldUserId);
// sql += " AND " + BaseUserEntity.FieldOpenId + " IS NULL ";
dbParameters.Add(dbHelper.MakeParameter(BaseUserLogonEntity.FieldUserId, userLogonEntity.UserId));
errorMark = 40;
dbHelper.ExecuteNonQuery(sql, dbParameters.ToArray());
}
}
}
catch (Exception ex)
{
var writeMessage = "BaseUserLogonManager.UpdateVisitTimeTask:发生时间:" + DateTime.Now
+ Environment.NewLine + "errorMark = " + errorMark
+ Environment.NewLine + "UserInfo:" + UserInfo.Serialize()
+ Environment.NewLine + "Message:" + ex.Message
+ Environment.NewLine + "Source:" + ex.Source
+ Environment.NewLine + "StackTrace:" + ex.StackTrace
+ Environment.NewLine + "TargetSite:" + ex.TargetSite
+ Environment.NewLine;
LogUtil.WriteLog(writeMessage, "Exception");
}
}
/// <summary>
/// 导入K8系统用户账户
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="condition">条件，不需要同步所有的数据</param>
/// <returns></returns>
public int Synchronous(string connectionString = null, string condition = null)
{
var result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
var dbHelper = DbHelperFactory.Create(CurrentDbType.Oracle, connectionString);
var staffManager = new BaseStaffManager(UserInfo);
if (string.IsNullOrEmpty(condition))
{
// 不不存在的用户删除掉tab_user是远程试图
/*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 10000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 10000 )";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 20000 AND id >= 10000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id <20000 AND id >= 10000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 30000 AND id >= 20000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 30000 AND id >= 20000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 40000 AND id >= 30000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 40000 AND id >= 30000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 50000 AND id >= 40000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 50000 AND id >= 40000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 60000 AND id >= 50000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 60000 AND id >= 50000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 70000 AND id >= 60000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 70000 AND id >= 60000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 80000 AND id >= 70000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 80000 AND id >= 70000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 90000 AND id >= 80000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 90000 AND id >= 80000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 100000 AND id >= 90000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 100000 AND id >= 90000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 110000 AND id >= 100000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 110000 AND id >= 100000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 120000 AND id >= 110000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 120000 AND id >= 110000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 130000 AND id >= 120000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 130000 AND id >= 120000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 200000 AND id >= 130000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 200000 AND id >= 130000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 300000 AND id >= 200000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 300000 AND id >= 200000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 400000 AND id >= 300000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 400000 AND id >= 300000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 600000 AND id >= 400000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 600000 AND id >= 400000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 800000 AND id >= 600000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 800000 AND id >= 600000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 1000000 AND id >= 800000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 1000000 AND id >= 800000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*/
}
// 01：可以从k8里读取公司、用户、密码的。
var commandText = "SELECT * FROM TAB_EMPLOYEE WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(condition))
{
commandText += condition + " ORDER BY UPDATETIME DESC";
}
else
{
// 只更新今天有变化的数据就可以了
// commandText += " AND TO_CHAR(SYSDATE, 'yy-mm-dd') = TO_CHAR(UPDATETIME, 'yy-mm-dd') ";
}
Console.WriteLine(commandText);
//var sTaffExpressManager = new BaseUserExpressManager(UserInfo);
//var userManager = new BaseUserManager(UserInfo);
//var userLogonManager = new BaseUserLogonManager(UserInfo);
//var dataReader = DbHelper.ExecuteReader(commandText);
//if (dataReader != null && !dataReader.IsClosed)
//{
// while (dataReader.Read())
// {
// result += ImportStaff(dataReader, staffManager, sTaffExpressManager, userManager,
// userLogonManager);
// }
// dataReader.Close();
//}
// 设置用户的公司主键，有时候不需要同步所有的账户，只同步增量账户
// 设置用户的公司主键
// commandText = @"UPDATE basestaff SET companyid = (SELECT MAX(Id) FROM baseorganization WHERE baseorganization.name = basestaff.companyname AND baseorganization.Id < 1000000) WHERE companyId IS NULL OR companyId = ''";
// 公司名称重复的数据需要找出来
ExecuteNonQuery(commandText);
Console.WriteLine(commandText);
}
return(result);
}
/// <summary>
/// 导入K8系统网点信息
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="conditional">条件，不需要同步所有的数据</param>
/// <returns>影响行数</returns>
public int ImportK8Organization(string connectionString = null, string conditional = null)
{
// delete from baseorganization where id < 1000000
var result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
var dbHelper = DbHelperFactory.Create(CurrentDbType.Oracle, connectionString);
var organizationManager = new Business.BaseOrganizationManager(this.DbHelper, this.UserInfo);
// 不不存在的组织机构删除掉TAB_SITE是远程试图
var commandText = "DELETE FROM BASEORGANIZE WHERE id < 1000000 AND id NOT IN (SELECT id FROM TAB_SITE)";
organizationManager.ExecuteNonQuery(commandText);
// 同步数据
commandText = "SELECT * FROM TAB_SITE WHERE BL_NOT_INPUT IS NULL OR BL_NOT_INPUT = 0 ";
if (!string.IsNullOrEmpty(conditional))
{
commandText += conditional;
}
var dataReader = dbHelper.ExecuteReader(commandText);
if (dataReader != null && !dataReader.IsClosed)
{
while (dataReader.Read())
{
// 这里需要从数据库读取、否则容易造成丢失数据
var entity = organizationManager.GetEntity(dataReader["ID"].ToString());
if (entity == null)
{
entity = new BaseOrganizationEntity();
//entity.Id = dr["ID"].ToString();
}
entity.Code = dataReader["SITE_CODE"].ToString();
if (string.IsNullOrEmpty(entity.ParentName) ||
!entity.ParentName.Equals(dataReader["SUPERIOR_SITE"].ToString()))
{
entity.ParentName = dataReader["SUPERIOR_SITE"].ToString();
entity.ParentId = 0;
}
entity.Name = dataReader["SITE_NAME"].ToString();
entity.ShortName = dataReader["SITE_NAME"].ToString();
entity.CategoryCode = dataReader["TYPE"].ToString();
entity.OuterPhone = dataReader["PHONE"].ToString();
entity.Fax = dataReader["FAX"].ToString();
entity.Province = dataReader["PROVINCE"].ToString();
entity.City = dataReader["CITY"].ToString();
entity.District = dataReader["RANGE_NAME"].ToString();
entity.CostCenter = dataReader["SUPERIOR_FINANCE_CENTER"].ToString();
entity.Area = dataReader["BIG_AREA_NAME"].ToString();
entity.CompanyName = dataReader["SITE1_NAME"].ToString();
if (!string.IsNullOrEmpty(dataReader["ORDER_BY"].ToString()))
{
entity.SortCode = int.Parse(dataReader["ORDER_BY"].ToString());
}
// 02：可以把读取到的数据能写入到用户中心的。
result = organizationManager.UpdateEntity(entity);
if (result == 0)
{
organizationManager.AddEntity(entity);
}
}
dataReader.Close();
}
// 填充 parentname
// select * from baseorganization where parentname is null
commandText = @"update baseorganization set parentname = (select fullname from baseorganization t where t.id = baseorganization.parentId) where parentname is null";
ExecuteNonQuery(commandText);
// 填充 parentId
// select * from baseorganization where parentId is null
commandText = @"UPDATE baseorganization SET parentId = (SELECT Id FROM baseorganization t WHERE t.fullname = baseorganization.parentname) WHERE parentId IS NULL";
// 100000 以下是基础数据的，100000 以上是通用权限管理系统的
// UPDATE baseorganization SET parentId = (SELECT Id FROM baseorganization t WHERE t.fullname = baseorganization.parentname) WHERE parentId < 100000
ExecuteNonQuery(commandText);
// 更新错误数据
commandText = @"UPDATE baseorganization SET parentId = null WHERE id = parentId";
ExecuteNonQuery(commandText);
// 设置员工的公司主键
commandText = @"UPDATE baseuser SET companyid = (SELECT MAX(Id) FROM baseorganization WHERE baseorganization.fullname = baseuser.companyname AND baseorganization.Id < 1000000) WHERE companyId IS NULL OR companyId = ''";
ExecuteNonQuery(commandText);
}
return(result);
}