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