/// <summary>
/// 实体修改记录
/// </summary>
/// <param name="newEntity">修改前的实体对象</param>
/// <param name="oldEntity">修改后的实体对象</param>
public void UpdateEntityLog(BaseOrganizeExpressEntity newEntity, BaseOrganizeExpressEntity oldEntity, string tableName = null)
{
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName + "_LOG";
}
BaseModifyRecordManager manager = new BaseModifyRecordManager(this.UserInfo, tableName);
foreach (var property in typeof(BaseOrganizeExpressEntity).GetProperties())
{
var oldValue = Convert.ToString(property.GetValue(oldEntity, null));
var newValue = Convert.ToString(property.GetValue(newEntity, null));
//不记录创建人、修改人、没有修改的记录
var fieldDescription = property.GetCustomAttributes(typeof(FieldDescription), false).FirstOrDefault() as FieldDescription;
if (!fieldDescription.NeedLog || oldValue == newValue)
{
continue;
}
var record = new BaseModifyRecordEntity();
record.ColumnCode = property.Name.ToUpper();
record.ColumnDescription = fieldDescription.Text;
record.NewValue = newValue;
record.OldValue = oldValue;
record.TableCode = this.CurrentTableName.ToUpper();
record.TableDescription = FieldExtensions.ToDescription(typeof(BaseOrganizeExpressEntity), "TableName");
record.RecordKey = oldEntity.Id.ToString();
record.IPAddress = Utilities.GetIPAddress(true);
manager.Add(record, true, false);
}
}
/// <summary>
/// 设置实体
/// </summary>
/// <param name="entity">实体</param>
private void SetObject(SQLBuilder sqlBuilder, BaseOrganizeExpressEntity entity)
{
SetObjectExpand(sqlBuilder, entity);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldNotDispatchRange, entity.NotDispatchRange);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldScanSelect, entity.ScanSelect);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldSitePrior, entity.SitePrior);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldPrivateRemark, entity.PrivateRemark);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldPublicRemark, entity.PublicRemark);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDispatchTimeLimit, entity.DispatchTimeLimit);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldAgentMoneyLimited, entity.AgentMoneyLimited);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDefaultSendPlace, entity.DefaultSendPlace);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDispatchRange, entity.DispatchRange);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldAllowToPayment, entity.AllowToPayment);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldAllowAgentMoney, entity.AllowAgentMoney);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDispatchOutRangeFee, entity.DispatchOutRangeFee);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldCurrency, entity.Currency);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDispatchRangeFee, entity.DispatchRangeFee);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldDispatchMoneyDesc, entity.DispatchMoneyDesc);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldWebSiteName, entity.WebSiteName);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldWebEnabled, entity.WebEnabled);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldInternalDispatch, entity.InternalDispatch);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldIsTransferCenter, entity.IsTransferCenter);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldIsErpOpen, entity.IsErpOpen);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldIsReceiveOrder, entity.IsReceiveOrder);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldIsReceiveComplain, entity.IsReceiveComplain);
}
/// <summary>
/// 更新实体
/// </summary>
/// <param name="entity">实体</param>
public int UpdateObject(BaseOrganizeExpressEntity entity)
{
SQLBuilder sqlBuilder = new SQLBuilder(DbHelper);
sqlBuilder.BeginUpdate(this.CurrentTableName);
this.SetObject(sqlBuilder, entity);
if (UserInfo != null)
{
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldModifiedUserId, UserInfo.Id);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldModifiedBy, UserInfo.RealName);
}
sqlBuilder.SetDBNow(BaseOrganizeExpressEntity.FieldModifiedOn);
sqlBuilder.SetWhere(this.PrimaryKey, entity.Id);
return(sqlBuilder.EndUpdate());
}
/// <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);
}
// 这个是声明扩展方法 partial void SetObjectExpand(SQLBuilder sqlBuilder, BaseOrganizeExpressEntity entity);
/// <summary>
/// 添加实体
/// </summary>
/// <param name="entity">实体</param>
public string AddObject(BaseOrganizeExpressEntity entity)
{
string key = string.Empty;
if (entity != null)
{
key = entity.Id.ToString();
}
SQLBuilder sqlBuilder = new SQLBuilder(DbHelper, this.Identity, this.ReturnId);
sqlBuilder.BeginInsert(this.CurrentTableName, this.PrimaryKey);
if (!this.Identity)
{
// 这里已经是指定了主键了，所以不需要返回主键了
sqlBuilder.ReturnId = false;
sqlBuilder.SetValue(this.PrimaryKey, entity.Id);
}
else
{
if (!this.ReturnId && (DbHelper.CurrentDbType == CurrentDbType.Oracle || DbHelper.CurrentDbType == CurrentDbType.DB2))
{
if (DbHelper.CurrentDbType == CurrentDbType.Oracle)
{
sqlBuilder.SetFormula(this.PrimaryKey, "SEQ_" + this.CurrentTableName.ToUpper() + ".NEXTVAL ");
}
if (DbHelper.CurrentDbType == CurrentDbType.DB2)
{
sqlBuilder.SetFormula(this.PrimaryKey, "NEXT VALUE FOR SEQ_" + this.CurrentTableName.ToUpper());
}
}
else
{
if (this.Identity && (DbHelper.CurrentDbType == CurrentDbType.Oracle || DbHelper.CurrentDbType == CurrentDbType.DB2))
{
BaseSequenceManager sequenceManager = new BaseSequenceManager(DbHelper);
entity.Id = int.Parse(sequenceManager.Increment(this.CurrentTableName));
sqlBuilder.SetValue(this.PrimaryKey, entity.Id);
}
}
}
this.SetObject(sqlBuilder, entity);
if (UserInfo != null)
{
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldCreateUserId, UserInfo.Id);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldCreateBy, UserInfo.RealName);
}
sqlBuilder.SetDBNow(BaseOrganizeExpressEntity.FieldCreateOn);
if (UserInfo != null)
{
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldModifiedUserId, UserInfo.Id);
sqlBuilder.SetValue(BaseOrganizeExpressEntity.FieldModifiedBy, UserInfo.RealName);
}
sqlBuilder.SetDBNow(BaseOrganizeExpressEntity.FieldModifiedOn);
if (this.Identity && (DbHelper.CurrentDbType == CurrentDbType.SqlServer || DbHelper.CurrentDbType == CurrentDbType.Access))
{
key = sqlBuilder.EndInsert().ToString();
}
else
{
sqlBuilder.EndInsert();
}
if (this.Identity && (DbHelper.CurrentDbType == CurrentDbType.Oracle || DbHelper.CurrentDbType == CurrentDbType.DB2))
{
return(entity.Id.ToString());
}
return(key);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体</param>
public int Update(BaseOrganizeExpressEntity entity)
{
return(this.UpdateObject(entity));
}
/// <summary>
/// 添加, 这里可以人工干预，提高程序的性能
/// </summary>
/// <param name="entity">实体</param>
/// <param name="identity">自增量方式，表主键是否采用自增的策略</param>
/// <param name="returnId">返回主键，不返回程序允许速度会快，主要是为了主细表批量插入数据优化用的</param>
/// <returns>主键</returns>
public string Add(BaseOrganizeExpressEntity entity, bool identity = false, bool returnId = false)
{
this.Identity = identity;
this.ReturnId = returnId;
return(this.AddObject(entity));
}
