/// <summary> 保存联系人
/// </summary>
/// <param name="contactArr">联系人</param>
/// <param name="updateCount">更新条数</param>
/// <returns>True OR False</returns>
private static bool SaveContactNotBatch(QueryContact.contact[] contactArr, ref int updateCount)
{
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
int contactIndex = 0;//联系人列表索引
int contactCount = 10000;//每批执行条数
//contactCount = contactArr.Count();
int contactSum = contactArr.Count() / contactCount + 1;//执行批数
//int contactSum = 3;
bool flag = true;//执行结果
DateTime startDate = DateTime.Now;
YuTongDic dic = new YuTongDic();
YTCustomer ytCustomer = new YTCustomer();
//联系人
DataTable dtContacts = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_contacts", "cont_id,cont_crm_guid", "", "", "");
DataTable dtBaseContacts = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tr_base_contacts", "cont_id,relation_object_id", "relation_object='tb_customer'", "", "");
#region 生成表列
DataTable dt = new DataTable();
List<DataRow> listTb = new List<DataRow>();
dt.Columns.Add(new DataColumn("cont_id", typeof(string)));
dt.Columns.Add(new DataColumn("cont_crm_guid", typeof(string)));
dt.Columns.Add(new DataColumn("cont_name", typeof(string)));
dt.Columns.Add(new DataColumn("sex", typeof(string)));
dt.Columns.Add(new DataColumn("nation", typeof(string)));
dt.Columns.Add(new DataColumn("cont_post", typeof(string)));
//dt.Columns.Add(new DataColumn("cont_phone", typeof(string)));
dt.Columns.Add(new DataColumn("cont_phone_back", typeof(string)));
dt.Columns.Add(new DataColumn("post_remark", typeof(string)));
dt.Columns.Add(new DataColumn("parent_customer", typeof(string)));
dt.Columns.Add(new DataColumn("status", typeof(string)));
dt.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dt.Columns.Add(new DataColumn("create_by", typeof(string)));
dt.Columns.Add(new DataColumn("create_time", typeof(long)));
dt.Columns.Add(new DataColumn("data_source", typeof(string)));
dt.Columns.Add("contacts_type", typeof(string));
DataTable dtTrContacts = new DataTable();
List<DataRow> listTrContacts = new List<DataRow>();
dtTrContacts.Columns.Add("id", typeof(string));
dtTrContacts.Columns.Add("cont_id", typeof(string));
dtTrContacts.Columns.Add("relation_object", typeof(string));
dtTrContacts.Columns.Add("relation_object_id", typeof(string));
#endregion
//StringBuilder sbMessage=new StringBuilder ();
for (int i = 0; i <= contactSum; i++)
{
dt.Rows.Clear();
listTb.Clear();
listTrContacts.Clear();
List<SysSQLString> list = new List<SysSQLString>();
for (int y = contactIndex; y < contactCount; y++)
{
int index = i * contactCount + y;
if (index >= contactArr.Count())
{
break;
}
QueryContact.contact item = contactArr[index];
StringBuilder strSql = new StringBuilder();
DataRow[] drsCont = dtContacts.Select("cont_crm_guid='" + item.cont_crm_guid + "'");
bool isContactExist = false;
//bool isContactExist = DBHelper.IsExist("判断联系人信息是否存在", "tb_contacts", "cont_crm_guid='" + item.cont_crm_guid + "'");
string nation = dic.GetLocalDicID("nation", item.nation);//民族
string cont_post = dic.GetLocalDicID("cont_post", item.cont_post);//职务
int cont_type = 0;
if (!string.IsNullOrEmpty(item.doc_type))
{
cont_type = Convert.ToInt32(item.doc_type);
DataSources.ContactType contType = (DataSources.ContactType)cont_type;
if (contType != DataSources.ContactType.Contact)
{
continue;
}
}
if (drsCont.Count() > 0)
{
isContactExist = true;
}
if (isContactExist)
{
#region 更新语句
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
strSql.Append("update tb_contacts set ");
strSql.AppendFormat(" cont_name = '{0}' , ", item.cont_name);
strSql.AppendFormat(" sex = '{0}' , ", item.sex);
strSql.AppendFormat(" nation = '{0}' , ", nation);
strSql.AppendFormat(" cont_post = '{0}' , ", item.cont_post);
//strSql.Append(" cont_phone = @cont_phone , ");
strSql.AppendFormat(" cont_phone = {0} , ", WebServUtil.GetEncField(item.cont_phone));
strSql.AppendFormat(" post_remark = '{0}' , ", item.cont_post_remark);
strSql.AppendFormat(" parent_customer = '{0}' , ", item.parent_customer);
strSql.AppendFormat(" contacts_type={0},", cont_type);
strSql.AppendFormat(" status = '{0}' , ", item.status == "0" ? "1" : "0");
strSql.AppendFormat(" enable_flag = '{0}' , ", (int)DataSources.EnumEnableFlag.USING);
strSql.AppendFormat(" data_source = '{0}' , ", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat(" update_by = '{0}' , ", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat(" update_time = {0} ", nowTicks);
strSql.AppendFormat(" where cont_crm_guid='{0}' ", item.cont_crm_guid);
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
#endregion
}
else
{
#region 插入语句
//strSql.Append("insert into tb_contacts(");
//strSql.Append("cont_id,cont_crm_guid,cont_name,sex,nation,cont_post,cont_phone,post_remark,parent_customer,status,enable_flag,data_source,create_by,create_time");
//strSql.Append(") values (");
//strSql.AppendFormat("'{0}',", Guid.NewGuid());
//strSql.AppendFormat("'{0}',", item.cont_crm_guid);
//strSql.AppendFormat("'{0}',", item.cont_name);
//strSql.AppendFormat("'{0}',", item.sex);
//strSql.AppendFormat("'{0}',", nation);
//strSql.AppendFormat("'{0}',", item.cont_post);
//strSql.Append(WebServUtil.GetEncField(item.cont_phone));
//strSql.AppendFormat(",'{0}',", item.cont_post_remark);
//strSql.AppendFormat("'{0}',", item.parent_customer);
//strSql.AppendFormat("'{0}',", item.status);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumEnableFlag.USING);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumDataSources.YUTONG);
//strSql.AppendFormat("'{0}',", GlobalStaticObj_Server.Instance.UserID);
//strSql.AppendFormat("{0})", nowTicks);
//if (item.cont_name.Length > 15)
//{
// sbMessage.AppendFormat("[cont_name:{0}]", item.cont_name);
//}
//if (cont_post!=null && cont_post.Length > 40)
//{
// sbMessage.AppendFormat("[cont_post:{0}]", cont_post);
//}
//if (item.parent_customer.Length > 50)
//{
// sbMessage.AppendFormat("[parent_customer:{0}]", item.parent_customer);
//}
//if (item.cont_post_remark.Length > 300)
//{
// sbMessage.AppendFormat("[cont_post_remark:{0}]", item.cont_post_remark);
//}
DataRow dr = dt.NewRow();
string contID = Guid.NewGuid().ToString();
dr["cont_id"] = contID;
dr["cont_crm_guid"] = item.cont_crm_guid;
dr["cont_name"] = item.cont_name;
dr["sex"] = item.sex;
dr["nation"] = nation;
dr["cont_post"] = cont_post;
dr["cont_phone_back"] = item.cont_phone;
dr["post_remark"] = item.cont_post_remark;
dr["parent_customer"] = item.parent_customer;
dr["status"] = item.status == "0" ? "1" : "0";
dr["contacts_type"] = cont_type;
dr["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
dr["data_source"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
dr["create_by"] = GlobalStaticObj_Server.Instance.UserID;
dr["create_time"] = nowTicks;
//dt.Rows.Add(dr);
listTb.Add(dr);
//关联客户档案
string custID = ytCustomer.GetLocalCustID(item.parent_customer);
if (!string.IsNullOrEmpty(custID))
{
DataRow[] drsBase = dtBaseContacts.Select(string.Format("cont_id='{0}' and relation_object_id='{1}'", contID, custID));
if (drsBase.Count() == 0)
{
DataRow drTrContacts = dtTrContacts.NewRow();
drTrContacts["id"] = Guid.NewGuid().ToString();
drTrContacts["cont_id"] = contID;
drTrContacts["relation_object"] = "tb_customer";
drTrContacts["relation_object_id"] = custID;
listTrContacts.Add(drTrContacts);
}
}
#endregion
}
contactArr[index] = null;
}
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步联系人", GlobalStaticObj_Server.Instance.MainAccCode, "tb_contacts", listTb);
if (!flag)
{
break;
}
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("", GlobalStaticObj_Server.Instance.MainAccCode, "tr_base_contacts", listTrContacts);
if (!flag)
{
break;
}
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
return false;
}
}
//flag = true;
if (!flag)
{
break;
}
}
#region 加密电话
List<SysSQLString> listUpdatePhone = new List<SysSQLString>();
SysSQLString sqlPhone = new SysSQLString();
sqlPhone.cmdType = CommandType.Text;
sqlPhone.Param = new Dictionary<string, string>();
string phoneEnc = WebServUtil.GetEncFieldByField("cont_phone_back");
sqlPhone.sqlString = string.Format("update tb_contacts set cont_phone={0},cont_phone_back=null where cont_phone_back is not null", phoneEnc);
listUpdatePhone.Add(sqlPhone);
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, listUpdatePhone);
#endregion
SysConfig sysConfig = new SysConfig();
sysConfig.UpdateLastTime("ContactLastTime");
dtContacts = null;//释放
dtBaseContacts = null;
ytCustomer = null;
updateCount += contactArr.Count();
dic = null;
DateTime endDate = DateTime.Now;
TimeSpan span = endDate - startDate;
return flag;
}
/// <summary>保存用户
/// </summary>
/// <param name="contactArr">用户</param>
/// <param name="updateCount">更新条数</param>
/// <returns>True OR False</returns>
private static bool SaveUserNotBatch(QueryContact.contact[] contactArr, ref int updateCount)
{
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
int contactIndex = 0;//联系人列表索引
int contactCount = 10000;//每批执行条数
//contactCount = contactArr.Count();
int contactSum = contactArr.Count() / contactCount + 1;//执行批数
//int contactSum = 3;
bool flag = true;//执行结果
DateTime startDate = DateTime.Now;
YuTongDic dic = new YuTongDic();
YTCustomer ytCustomer = new YTCustomer();
//用户
DataTable dtContacts = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "sys_user", "user_id,cont_crm_guid", "", "", "");
#region 生成表列
DataTable dt = new DataTable();
List<DataRow> listTb = new List<DataRow>();
dt.Columns.Add(new DataColumn("user_id", typeof(string)));
dt.Columns.Add(new DataColumn("cont_crm_guid", typeof(string)));
dt.Columns.Add(new DataColumn("user_name", typeof(string)));
dt.Columns.Add(new DataColumn("sex", typeof(string)));
dt.Columns.Add(new DataColumn("nation", typeof(string)));
dt.Columns.Add(new DataColumn("post", typeof(string)));
//dt.Columns.Add(new DataColumn("cont_phone", typeof(string)));
dt.Columns.Add(new DataColumn("user_phone", typeof(string)));
//dt.Columns.Add(new DataColumn("post_remark", typeof(string)));
//dt.Columns.Add(new DataColumn("parent_customer", typeof(string)));
dt.Columns.Add(new DataColumn("status", typeof(string)));
dt.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dt.Columns.Add(new DataColumn("create_by", typeof(string)));
dt.Columns.Add(new DataColumn("create_time", typeof(long)));
dt.Columns.Add(new DataColumn("data_sources", typeof(string)));
//dt.Columns.Add("contacts_type", typeof(string));
#endregion
for (int i = 0; i <= contactSum; i++)
{
dt.Rows.Clear();
listTb.Clear();
List<SysSQLString> list = new List<SysSQLString>();
for (int y = contactIndex; y < contactCount; y++)
{
int index = i * contactCount + y;
if (index >= contactArr.Count())
{
break;
}
QueryContact.contact item = contactArr[index];
StringBuilder strSql = new StringBuilder();
DataRow[] drsCont = dtContacts.Select("cont_crm_guid='" + item.cont_crm_guid + "'");
bool isContactExist = false;
//bool isContactExist = DBHelper.IsExist("判断联系人信息是否存在", "tb_contacts", "cont_crm_guid='" + item.cont_crm_guid + "'");
string nation = dic.GetLocalDicID("nation", item.nation);//民族
string cont_post = dic.GetLocalDicID("cont_post", item.cont_post);//职务
int cont_type = 0;
if (!string.IsNullOrEmpty(item.doc_type))
{
cont_type = Convert.ToInt32(item.doc_type);
DataSources.ContactType contType = (DataSources.ContactType)cont_type;
if (contType != DataSources.ContactType.Server)
{
continue;
}
}
if (drsCont.Count() > 0)
{
isContactExist = true;
}
if (isContactExist)
{
#region 更新语句
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
strSql.Append("update sys_user set ");
strSql.AppendFormat(" user_name = '{0}' , ", item.cont_name);
strSql.AppendFormat(" sex = '{0}' , ", item.sex);
strSql.AppendFormat(" nation = '{0}' , ", nation);
strSql.AppendFormat(" post = '{0}' , ", item.cont_post);
//strSql.Append(" cont_phone = @cont_phone , ");
strSql.AppendFormat(" user_phone = {0} , ", item.cont_phone);
//strSql.AppendFormat(" post_remark = '{0}' , ", item.cont_post_remark);
//strSql.AppendFormat(" parent_customer = '{0}' , ", item.parent_customer);
//strSql.AppendFormat(" contacts_type={0},", cont_type);
strSql.AppendFormat(" status = '{0}' , ", item.status == "0" ? "1" : "0");
strSql.AppendFormat(" enable_flag = '{0}' , ", (int)DataSources.EnumEnableFlag.USING);
strSql.AppendFormat(" data_sources = '{0}' , ", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat(" update_by = '{0}' , ", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat(" update_time = {0} ", nowTicks);
strSql.AppendFormat(" where cont_crm_guid='{0}' ", item.cont_crm_guid);
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
#endregion
}
else
{
#region 插入语句
DataRow dr = dt.NewRow();
string contID = Guid.NewGuid().ToString();
dr["user_id"] = contID;
dr["cont_crm_guid"] = item.cont_crm_guid;
dr["user_name"] = item.cont_name;
dr["sex"] = item.sex;
dr["nation"] = nation;
dr["post"] = cont_post;
dr["user_phone"] = item.cont_phone;
//dr["post_remark"] = item.cont_post_remark;
//dr["parent_customer"] = item.parent_customer;
dr["status"] = item.status == "0" ? "1" : "0";
//dr["contacts_type"] = cont_type;
dr["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
dr["data_sources"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
dr["create_by"] = GlobalStaticObj_Server.Instance.UserID;
dr["create_time"] = nowTicks;
listTb.Add(dr);
#endregion
}
contactArr[index] = null;//释放项
}
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步用户", GlobalStaticObj_Server.Instance.MainAccCode, "sys_user", listTb);
if (!flag)
{
break;
}
updateCount += listTb.Count;
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
return flag;
}
updateCount += list.Count;
}
//flag = true;
if (!flag)
{
break;
}
}
SysConfig sysConfig = new SysConfig();
sysConfig.UpdateLastTime("UserLastTime");
dtContacts = null;//释放
ytCustomer = null;//
dic = null;
DateTime endDate = DateTime.Now;
TimeSpan span = endDate - startDate;
return flag;
}
/// <summary> 保存联系人
/// </summary>
/// <param name="contactArr">联系人</param>
/// <returns>True OR False</returns>
private static bool SaveContact(QueryContact.contact[] contactArr)
{
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
List<SysSQLString> list = new List<SysSQLString>();
foreach (QueryContact.contact item in contactArr)
{
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
StringBuilder strSql = new StringBuilder();
//bool isContactExist = DBHelper.IsExist("判断联系人信息是否存在", "tb_contacts", "cont_crm_guid='" + item.cont_crm_guid + "'");
bool isContactExist = false;
if (isContactExist)
{
#region 更新语句
strSql.Append("update tb_contacts set ");
strSql.Append(" cont_name = @cont_name , ");
strSql.Append(" sex = @sex , ");
strSql.Append(" nation = @nation , ");
strSql.Append(" cont_post = @cont_post , ");
strSql.Append(" cont_phone = @cont_phone , ");
strSql.Append(" post_remark = @post_remark , ");
strSql.Append(" parent_customer = @parent_customer , ");
strSql.Append(" status = @status , ");
strSql.Append(" enable_flag = @enable_flag , ");
strSql.Append(" data_source = @data_source , ");
strSql.Append(" update_by = @update_by , ");
strSql.Append(" update_time = @update_time ");
strSql.Append(" where cont_crm_guid=@cont_crm_guid ");
#endregion
}
else
{
#region 插入语句
strSql.Append("insert into tb_contacts(");
strSql.Append("cont_id,cont_crm_guid,cont_name,sex,nation,cont_post,cont_phone,post_remark,parent_customer,status,enable_flag,data_source,create_by,create_time,update_by,update_time");
strSql.Append(") values (");
strSql.Append("@cont_id,@cont_crm_guid,@cont_name,@sex,@nation,@cont_post,@cont_phone,@post_remark,@parent_customer,@status,@enable_flag,@data_source,@create_by,@create_time,@update_by,@update_time");
strSql.Append(") ");
#endregion
sysSQLString.Param.Add("cont_id", Guid.NewGuid().ToString());
sysSQLString.Param.Add("create_by", GlobalStaticObj_Server.Instance.UserID);
sysSQLString.Param.Add("create_time", nowTicks);
}
#region 参数项 9
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param.Add("cont_crm_guid", item.cont_crm_guid);
sysSQLString.Param.Add("cont_name", item.cont_name);
sysSQLString.Param.Add("sex", item.sex);
sysSQLString.Param.Add("nation", WebServUtil.GetLocalDicID("nation", item.nation));
sysSQLString.Param.Add("cont_post", item.cont_post);
sysSQLString.Param.Add("cont_phone", WebServUtil.GetEncFieldValue(item.cont_phone));//加密
sysSQLString.Param.Add("post_remark", item.cont_post_remark);
sysSQLString.Param.Add("parent_customer", item.parent_customer);
sysSQLString.Param.Add("status", item.status);
sysSQLString.Param.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString());
sysSQLString.Param.Add("data_source", ((int)DataSources.EnumDataSources.YUTONG).ToString());
sysSQLString.Param.Add("update_by", GlobalStaticObj_Server.Instance.UserID);
sysSQLString.Param.Add("update_time", nowTicks);
#endregion
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
}
bool flag = DBHelper.BatchExeSQLStringMultiByTrans("宇通：同步联系人", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, list);
return flag;
}
