/// <summary>
/// 导入K8系统网点信息
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="conditional">条件，不需要同步所有的数据</param>
/// <returns>影响行数</returns>
public int Synchronous(string connectionString = null, string conditional = null)
{
// delete from baseorganize where id < 1000000
int result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationHelper.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.Oracle, connectionString);
BaseOrganizeManager organizeManager = new Business.BaseOrganizeManager(this.DbHelper, this.UserInfo);
// 不不存在的组织机构删除掉TAB_SITE是远程试图
string commandText = string.Empty;
if (string.IsNullOrWhiteSpace(conditional))
{
commandText = "DELETE FROM BASEORGANIZE WHERE id <= 10000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id <= 10000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 10000 AND id <= 20000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 10000 AND id <= 20000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 20000 AND id <= 30000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 20000 AND id <= 30000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 30000 AND id <= 40000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 30000 AND id <= 40000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 40000 AND id <= 50000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 40000 AND id <= 50000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 50000 AND id <= 60000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 50000 AND id <= 60000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 60000 AND id <= 70000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 60000 AND id <= 70000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 70000 AND id <= 80000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 70000 AND id <= 80000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 80000 AND id <= 90000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 80000 AND id <= 90000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 90000 AND id <= 100000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 90000 AND id <= 100000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = "DELETE FROM BASEORGANIZE WHERE id > 100000 AND id <= 1000000 AND id NOT IN (SELECT id FROM TAB_SITE WHERE id > 100000 AND id <= 1000000)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
}
/*
* string commandText = "SELECT Id FROM BASEORGANIZE WHERE id < 1000000";
* using (IDataReader dataReader = organizeManager.DbHelper.ExecuteReader(commandText))
* {
* while (dataReader.Read())
* {
* string id = dataReader["id"].ToString();
* commandText = "SELECT COUNT(1) AS Rcount FROM TAB_SITE WHERE id='" + id + "'";
* object rcount = dbHelper.ExecuteScalar(commandText);
* if (rcount == null || rcount.ToString().Equals("0"))
* {
* commandText = "DELETE FROM BASEORGANIZE WHERE id ='" + id + "'";
* organizeManager.DbHelper.ExecuteNonQuery(commandText);
* }
* }
* }
*/
// 同步数据
// commandText = "SELECT * FROM TAB_SITE WHERE (BL_NOT_INPUT IS NULL OR BL_NOT_INPUT = 0) ";
commandText = "SELECT * FROM TAB_SITE WHERE 1=1 ";
if (!string.IsNullOrEmpty(conditional))
{
commandText += conditional;
}
commandText += " ORDER BY ID DESC";
using (IDataReader dr = DbHelper.ExecuteReader(commandText))
{
System.Console.WriteLine(commandText);
while (dr.Read())
{
// 这里需要从数据库读取、否则容易造成丢失数据
BaseOrganizeEntity entity = organizeManager.GetObject(dr["ID"].ToString());
if (entity == null)
{
entity = new BaseOrganizeEntity();
entity.Id = int.Parse(dr["ID"].ToString());
}
entity.Code = dr["SITE_CODE"].ToString();
if (string.IsNullOrEmpty(entity.ParentName) || !entity.ParentName.Equals(dr["SUPERIOR_SITE"].ToString()))
{
entity.ParentName = dr["SUPERIOR_SITE"].ToString();
entity.ParentId = null;
}
entity.FullName = dr["SITE_NAME"].ToString();
entity.ShortName = dr["SITE_NAME"].ToString();
entity.CategoryCode = dr["TYPE"].ToString();
entity.OuterPhone = dr["PHONE"].ToString();
entity.Fax = dr["FAX"].ToString();
entity.Province = dr["PROVINCE"].ToString();
entity.City = dr["CITY"].ToString();
entity.District = dr["RANGE_NAME"].ToString();
entity.FinancialCenter = dr["SUPERIOR_FINANCE_CENTER"].ToString();
entity.Area = dr["AREA_NAME"].ToString();
entity.CostCenter = dr["SUPERIOR_TWO_FINANCE_CENTER"].ToString();
entity.Manager = dr["PRINCIPAL"].ToString();
entity.Master = dr["MANAGER"].ToString();
entity.BusinessPhone = dr["SALE_PHONE"].ToString();
entity.Description = dr["SITE_DESC"].ToString();
entity.MasterMobile = dr["MANAGER_PHONE"].ToString();
entity.EmergencyCall = dr["EXIGENCE_PHONE"].ToString();
entity.BankAccount = dr["FINANCIAL_ACCOUNT"].ToString();
entity.Street = dr["COUNTY_NAME"].ToString();
int weightRatio = 0;
int.TryParse(dr["THROW_RATE"].ToString(), out weightRatio);
entity.WeightRatio = weightRatio;
entity.CompanyName = dr["SITE1_NAME"].ToString();
entity.JoiningMethods = dr["NC_TYPE"].ToString();
int sendAir = 0;
if (!string.IsNullOrEmpty(dr["BL_AIR"].ToString()) && ValidateUtil.IsInt(dr["BL_AIR"].ToString()))
{
sendAir = int.Parse(dr["BL_AIR"].ToString());
}
entity.SendAir = sendAir;
entity.LevelTwoTransferCenter = dr["TWO_BALANCE_CENTER"].ToString();
Decimal billSubsidy = 0;
if (!string.IsNullOrEmpty(dr["CENTER_DISPATCH_FEE"].ToString()) && ValidateUtil.IsDouble(dr["CENTER_DISPATCH_FEE"].ToString()))
{
billSubsidy = Decimal.Parse(dr["CENTER_DISPATCH_FEE"].ToString());
}
entity.BillSubsidy = billSubsidy;
Decimal sendFee = 0;
if (!string.IsNullOrEmpty(dr["DISP_FEE"].ToString()) && ValidateUtil.IsDouble(dr["DISP_FEE"].ToString()))
{
sendFee = Decimal.Parse(dr["DISP_FEE"].ToString());
}
entity.SendFee = sendFee;
Decimal levelTwoTransferFee = 0;
if (!string.IsNullOrEmpty(dr["TRANSFER_FEE"].ToString()) && ValidateUtil.IsDouble(dr["TRANSFER_FEE"].ToString()))
{
levelTwoTransferFee = Decimal.Parse(dr["TRANSFER_FEE"].ToString());
}
entity.LevelTwoTransferFee = levelTwoTransferFee;
entity.BigArea = dr["BIG_AREA_NAME"].ToString();
entity.StatisticalName = dr["REC_CENTER"].ToString();
entity.BillBalanceSite = dr["TRANSFER_CENTER"].ToString();
entity.ProvinceSite = dr["PROV_SITE"].ToString();
int calculateComeFee = 0;
if (!string.IsNullOrEmpty(dr["BL_WEB"].ToString()) && ValidateUtil.IsInt(dr["BL_WEB"].ToString()))
{
calculateComeFee = int.Parse(dr["BL_WEB"].ToString());
}
entity.CalculateComeFee = calculateComeFee;
int calculateReceiveFee = 0;
if (!string.IsNullOrEmpty(dr["TB_SER_WW"].ToString()) && ValidateUtil.IsInt(dr["TB_SER_WW"].ToString()))
{
calculateReceiveFee = int.Parse(dr["TB_SER_WW"].ToString());
}
entity.CalculateReceiveFee = calculateReceiveFee;;
if (!string.IsNullOrEmpty(dr["BL_NOT_INPUT"].ToString()) && dr["BL_NOT_INPUT"].ToString().Equals("1"))
{
entity.Enabled = 0;
}
else
{
entity.Enabled = 1;
}
if (!string.IsNullOrEmpty(dr["ORDER_BY"].ToString()))
{
entity.SortCode = int.Parse(dr["ORDER_BY"].ToString());
}
// 02：可以把读取到的数据能写入到用户中心的。
System.Console.WriteLine("同步：" + entity.FullName + " " + entity.Id.ToString() + "...");
result = organizeManager.UpdateObject(entity);
if (result == 0)
{
organizeManager.AddObject(entity);
}
//更新网点基础资料扩展表
BaseOrganizeExpressManager organizeExpressManager = new Business.BaseOrganizeExpressManager(this.DbHelper, this.UserInfo);
BaseOrganizeExpressEntity organizeExpressEntity = organizeExpressManager.GetObject(dr["ID"].ToString());
if (organizeExpressEntity == null)
{
organizeExpressEntity = new BaseOrganizeExpressEntity();
organizeExpressEntity.Id = int.Parse(dr["ID"].ToString());
}
organizeExpressEntity.Currency = dr["DEFAULT_CURRENCY"].ToString();
organizeExpressEntity.DefaultSendPlace = dr["DEFAULT_SEND_PLACE"].ToString();
int allow_ToPayment = 0;
if (!string.IsNullOrEmpty(dr["BL_ALLOW_TOPAYMENT"].ToString()) && ValidateUtil.IsInt(dr["BL_ALLOW_TOPAYMENT"].ToString()))
{
allow_ToPayment = int.Parse(dr["BL_ALLOW_TOPAYMENT"].ToString());
}
organizeExpressEntity.AllowToPayment = allow_ToPayment;
organizeExpressEntity.DispatchRange = dr["DISPATCH_RANGE"].ToString();
organizeExpressEntity.NotDispatchRange = dr["NOT_DISPATCH_RANGE"].ToString();
organizeExpressEntity.DispatchTimeLimit = dr["DISPATCH_TIME_LIMIT"].ToString();
int allowAgentMoney = 0;
if (!string.IsNullOrEmpty(dr["BL_ALLOW_AGENT_MONEY"].ToString()) && ValidateUtil.IsInt(dr["BL_ALLOW_AGENT_MONEY"].ToString()))
{
allowAgentMoney = int.Parse(dr["BL_ALLOW_AGENT_MONEY"].ToString());
}
organizeExpressEntity.AllowAgentMoney = allowAgentMoney;
organizeExpressEntity.PublicRemark = dr["PUBLIC_REMARK"].ToString();
organizeExpressEntity.PrivateRemark = dr["PRIVATE_REMARK"].ToString();
organizeExpressEntity.DispatchMoneyDesc = dr["DISPATCH_MONEY_DESC"].ToString();
int scanSelect = 0;
if (!string.IsNullOrEmpty(dr["BL_CALC_SITE"].ToString()) && ValidateUtil.IsInt(dr["BL_CALC_SITE"].ToString()))
{
scanSelect = int.Parse(dr["BL_CALC_SITE"].ToString());
}
organizeExpressEntity.ScanSelect = scanSelect;
Decimal dispatchRangeFee = 0;
if (!string.IsNullOrEmpty(dr["DISP_FEE1"].ToString()) && ValidateUtil.IsDouble(dr["DISP_FEE1"].ToString()))
{
dispatchRangeFee = Decimal.Parse(dr["DISP_FEE1"].ToString());
}
organizeExpressEntity.DispatchRangeFee = dispatchRangeFee;
Decimal dispatchOutRangeFee = 0;
if (!string.IsNullOrEmpty(dr["DISP_FEE2"].ToString()) && ValidateUtil.IsDouble(dr["DISP_FEE2"].ToString()))
{
dispatchOutRangeFee = Decimal.Parse(dr["DISP_FEE2"].ToString());
}
organizeExpressEntity.DispatchOutRangeFee = dispatchOutRangeFee;
Decimal agentMoneyLimited = 0;
if (!string.IsNullOrEmpty(dr["GOODS_PAYMENT_LIMITED"].ToString()) && ValidateUtil.IsDouble(dr["GOODS_PAYMENT_LIMITED"].ToString()))
{
agentMoneyLimited = Decimal.Parse(dr["GOODS_PAYMENT_LIMITED"].ToString());
}
organizeExpressEntity.AgentMoneyLimited = agentMoneyLimited;
Decimal sitePrior = 0;
if (!string.IsNullOrEmpty(dr["SITE_PRIOR"].ToString()) && ValidateUtil.IsDouble(dr["SITE_PRIOR"].ToString()))
{
sitePrior = Decimal.Parse(dr["SITE_PRIOR"].ToString());
}
organizeExpressEntity.SitePrior = sitePrior;
// 02001：可以把读取到的数据能写入到用户中心的网点基础资料扩展表。
System.Console.WriteLine("同步网点基础资料扩展表：" + entity.FullName + " " + entity.Id.ToString() + "...");
result = organizeExpressManager.Update(organizeExpressEntity);
if (result == 0)
{
organizeExpressManager.Add(organizeExpressEntity);
}
//更新省市县
//更新省份ID
commandText = @"UPDATE baseorganize SET provinceId = (SELECT Code FROM basearea t WHERE t.shortname = baseorganize.province) WHERE id = '" + entity.Id + "' ";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
//更新城市ID，由于城市可能出现重复的情况，所以需要选择当前省份的城市
commandText = @"UPDATE baseorganize SET cityId = (SELECT Code FROM basearea t WHERE t.fullname = baseorganize.city and t.parentid = baseorganize.provinceid) WHERE id = '" + entity.Id + "' ";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
//更新区县ID，由于区县可能出现重复的情况，所以需要选择当前城市的区县
commandText = @"UPDATE baseorganize SET districtId = (SELECT Code FROM basearea t WHERE t.fullname = baseorganize.district and t.parentid = baseorganize.cityid) WHERE id = '" + entity.Id + "' ";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
//更新乡镇ID，由于乡镇可能出现重复的情况，所以需要选择当前区县的乡镇
commandText = @"UPDATE baseorganize SET streetid = (SELECT Code FROM basearea t WHERE t.fullname = baseorganize.street and t.parentid = baseorganize.districtId) WHERE id = '" + entity.Id + "' ";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"UPDATE baseorganize SET parentId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.parentname AND t.deletionstatecode = 0) WHERE id = '" + entity.Id + "' "; // AND t.enabled = 1 WHERE parentId IS NULL
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"UPDATE baseorganize SET CompanyId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.CompanyName AND t.deletionstatecode = 0) WHERE CompanyId IS NULL "; // AND t.enabled = 1 id = '" + entity.Id + "' ";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"UPDATE baseorganize SET CompanyCode = (SELECT Code FROM baseorganize t WHERE t.fullname = baseorganize.CompanyName AND t.deletionstatecode = 0) WHERE CompanyCode IS NULL "; // AND t.enabled = 1WHERE id = '" + entity.Id + "'
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
}
dr.Close();
System.Console.WriteLine("完整同步网点表...");
}
if (string.IsNullOrWhiteSpace(conditional))
{
// 填充 parentname
// select * from baseorganize where parentname is null
commandText = @"UPDATE baseorganize SET parentname = (SELECT fullname FROM baseorganize t WHERE t.id = baseorganize.parentId) WHERE parentname IS NULL AND id < 1000000";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
// 填充 parentId
// select * from baseorganize where parentId is null
commandText = @"UPDATE baseorganize SET parentId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.parentname AND t.fullname = baseorganize.parentname AND t.deletionstatecode = 0 AND t.enabled = 1 AND t.id < 1000000)"; // WHERE parentId IS NULL
// 100000 以下是基础数据的，100000 以上是通用权限管理系统的
// UPDATE baseorganize SET parentId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.parentname) WHERE parentId < 100000
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"UPDATE baseorganize SET CompanyId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.CompanyName AND t.deletionstatecode = 0 AND t.enabled = 1 AND t.id < 1000000)"; // WHERE CompanyId IS NULL
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"UPDATE baseorganize SET CompanyCode = (SELECT Code FROM baseorganize t WHERE t.fullname = baseorganize.CompanyName AND t.deletionstatecode = 0 AND t.enabled = 1 AND t.id < 1000000) "; // WHERE CompanyCode IS NULL
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
// 更新错误数据
commandText = @"UPDATE baseorganize SET parentId = null WHERE id = parentId";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
// 设置员工的公司主键
commandText = @"UPDATE " + BaseUserEntity.TableName + " SET CompanyId = (SELECT MAX(Id) FROM baseorganize WHERE baseorganize.fullname = " + BaseUserEntity.TableName + ".companyname AND baseorganize.Id < 1000000) WHERE companyId IS NULL OR companyId = '' AND id < 1000000";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"update baseorganize set baseorganize.city = (select basearea.fullname from basearea where basearea.id = baseorganize.cityid) WHERE cityid IS NOT NULL AND id < 1000000";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"update baseorganize set baseorganize.district = (select basearea.fullname from basearea where basearea.id = baseorganize.districtid) WHERE districtid IS NOT NULL AND id < 1000000";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
commandText = @"update baseorganize set baseorganize.street = (select basearea.fullname from basearea where basearea.id = baseorganize.streetid) WHERE streetid IS NOT NULL AND id < 1000000";
this.DbHelper.ExecuteNonQuery(commandText);
System.Console.WriteLine(commandText);
}
}
return(result);
}
public static void CachePreheatingSpelling(bool flushDb = false)
{
// 组织机构数据缓存预热实现
BaseOrganizeManager organizeManager = new Business.BaseOrganizeManager();
// 减少数据库连接、减少内存站用、一边度取、一边设置缓存，只读取需要的数据
organizeManager.SelectFields = BaseOrganizeEntity.FieldId
+ ", " + BaseOrganizeEntity.FieldCode
+ ", " + BaseOrganizeEntity.FieldFullName
+ " , " + BaseOrganizeEntity.FieldSimpleSpelling
+ " , " + BaseOrganizeEntity.FieldCostCenterId
+ " , " + BaseOrganizeEntity.FieldProvinceId
+ " , " + BaseOrganizeEntity.FieldCompanyId
+ " , " + BaseOrganizeEntity.FieldCityId
+ " , " + BaseOrganizeEntity.FieldParentId
+ " , " + BaseOrganizeEntity.FieldSendAir
+ " , " + BaseOrganizeEntity.FieldEnabled
+ " , " + BaseOrganizeEntity.FieldDeletionStateCode
+ " , " + BaseOrganizeEntity.FieldSortCode;
// 读取有效的，没有被删除的网点数据
// List<KeyValuePair<string, object>> parameters = new List<KeyValuePair<string, object>>();
// parameters.Add(new KeyValuePair<string, object>(BaseOrganizeEntity.FieldEnabled, 1));
// parameters.Add(new KeyValuePair<string, object>(BaseOrganizeEntity.FieldDeletionStateCode, 0));
// 2016-02-19 宋彪 PooledRedisHelper.GetClient() 改为 PooledRedisHelper.GetSpellingClient()
using (var redisClient = PooledRedisHelper.GetSpellingClient())
{
if (flushDb)
{
redisClient.FlushDb();
}
double score = 0;
using (IDataReader dataReader = organizeManager.ExecuteReader(0, BaseOrganizeEntity.FieldFullName))
{
while (dataReader.Read())
{
score++;
// 读取到的数据直接强制设置到缓存里
BaseOrganizeEntity entity = new BaseOrganizeEntity();
entity.Id = dataReader[BaseOrganizeEntity.FieldId].ToString();
entity.Code = dataReader[BaseOrganizeEntity.FieldCode].ToString();
entity.FullName = dataReader[BaseOrganizeEntity.FieldFullName].ToString();
entity.SimpleSpelling = dataReader[BaseOrganizeEntity.FieldSimpleSpelling].ToString().ToLower();
entity.CostCenterId = dataReader[BaseOrganizeEntity.FieldCostCenterId].ToString();
entity.ProvinceId = dataReader[BaseOrganizeEntity.FieldProvinceId].ToString();
entity.CompanyId = dataReader[BaseOrganizeEntity.FieldCompanyId].ToString();
entity.CityId = dataReader[BaseOrganizeEntity.FieldCityId].ToString();
entity.ParentId = dataReader[BaseOrganizeEntity.FieldParentId].ToString();
entity.SendAir = BaseBusinessLogic.ConvertToInt(dataReader[BaseOrganizeEntity.FieldSendAir]);
entity.Enabled = BaseBusinessLogic.ConvertToInt(dataReader[BaseOrganizeEntity.FieldEnabled]);
entity.DeletionStateCode = BaseBusinessLogic.ConvertToInt(dataReader[BaseOrganizeEntity.FieldDeletionStateCode]);
entity.SortCode = BaseBusinessLogic.ConvertToInt(dataReader[BaseOrganizeEntity.FieldSortCode]);
if (!flushDb)
{
score = entity.SortCode.Value;
}
CachePreheatingSpelling(redisClient, entity, score);
if (flushDb)
{
// 2016-02-02 吉日嘎拉 设置一下排序属性
new Business.BaseManager(BaseOrganizeEntity.TableName).SetProperty(entity.Id, new KeyValuePair <string, object>(BaseOrganizeEntity.FieldSortCode, score));
}
}
}
}
}
/// <summary>
/// 导入K8系统网点信息
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="conditional">条件，不需要同步所有的数据</param>
/// <returns>影响行数</returns>
public int ImportK8Organize(string connectionString = null, string conditional = null)
{
// delete from baseorganize where id < 1000000
int result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationHelper.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.Oracle, connectionString);
BaseOrganizeManager organizeManager = new Business.BaseOrganizeManager(this.DbHelper, this.UserInfo);
// 不不存在的组织机构删除掉TAB_SITE是远程试图
string commandText = "DELETE FROM BASEORGANIZE WHERE id < 1000000 AND id NOT IN (SELECT id FROM TAB_SITE)";
organizeManager.DbHelper.ExecuteNonQuery(commandText);
// 同步数据
commandText = "SELECT * FROM TAB_SITE WHERE BL_NOT_INPUT IS NULL OR BL_NOT_INPUT = 0 ";
if (!string.IsNullOrEmpty(conditional))
{
commandText += conditional;
}
using (IDataReader dr = dbHelper.ExecuteReader(commandText))
{
while (dr.Read())
{
// 这里需要从数据库读取、否则容易造成丢失数据
BaseOrganizeEntity entity = organizeManager.GetObject(dr["ID"].ToString());
if (entity == null)
{
entity = new BaseOrganizeEntity();
entity.Id = int.Parse(dr["ID"].ToString());
}
entity.Code = dr["SITE_CODE"].ToString();
if (string.IsNullOrEmpty(entity.ParentName) || !entity.ParentName.Equals(dr["SUPERIOR_SITE"].ToString()))
{
entity.ParentName = dr["SUPERIOR_SITE"].ToString();
entity.ParentId = null;
}
entity.FullName = dr["SITE_NAME"].ToString();
entity.ShortName = dr["SITE_NAME"].ToString();
entity.CategoryCode = dr["TYPE"].ToString();
entity.OuterPhone = dr["PHONE"].ToString();
entity.Fax = dr["FAX"].ToString();
entity.Province = dr["PROVINCE"].ToString();
entity.City = dr["CITY"].ToString();
entity.District = dr["RANGE_NAME"].ToString();
entity.CostCenter = dr["SUPERIOR_FINANCE_CENTER"].ToString();
entity.Area = dr["BIG_AREA_NAME"].ToString();
entity.CompanyName = dr["SITE1_NAME"].ToString();
entity.JoiningMethods = dr["NC_TYPE"].ToString();
if (!string.IsNullOrEmpty(dr["ORDER_BY"].ToString()))
{
entity.SortCode = int.Parse(dr["ORDER_BY"].ToString());
}
// 02：可以把读取到的数据能写入到用户中心的。
result = organizeManager.UpdateObject(entity);
if (result == 0)
{
organizeManager.AddObject(entity);
}
}
dr.Close();
}
// 填充 parentname
// select * from baseorganize where parentname is null
commandText = @"update baseorganize set parentname = (select fullname from baseorganize t where t.id = baseorganize.parentId) where parentname is null";
this.DbHelper.ExecuteNonQuery(commandText);
// 填充 parentId
// select * from baseorganize where parentId is null
commandText = @"UPDATE baseorganize SET parentId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.parentname) WHERE parentId IS NULL";
// 100000 以下是基础数据的，100000 以上是通用权限管理系统的
// UPDATE baseorganize SET parentId = (SELECT Id FROM baseorganize t WHERE t.fullname = baseorganize.parentname) WHERE parentId < 100000
this.DbHelper.ExecuteNonQuery(commandText);
// 更新错误数据
commandText = @"UPDATE baseorganize SET parentId = null WHERE id = parentId";
this.DbHelper.ExecuteNonQuery(commandText);
// 设置员工的公司主键
commandText = @"UPDATE baseuser SET companyid = (SELECT MAX(Id) FROM baseorganize WHERE baseorganize.fullname = baseuser.companyname AND baseorganize.Id < 1000000) WHERE companyId IS NULL OR companyId = ''";
this.DbHelper.ExecuteNonQuery(commandText);
}
return(result);
}
