/// <summary> 三包服务单创建/更新
/// </summary>
/// <param name="contactModel">三包服务单实体</param>
/// <returns>返回错误信息，如果不为空，则操作失败</returns>
public static bool UpLoadServiceOrder(string jsonStr)
{
//如果没有接入码或者sap代码,则不调用接口
if (string.IsNullOrEmpty(GlobalStaticObj_YT.ClientID) ||
string.IsNullOrEmpty(GlobalStaticObj_YT.SAPCode))
{
return true;
}
Model.serviceorder model = Newtonsoft.Json.JsonConvert.DeserializeObject<Model.serviceorder>(jsonStr);//取出tg_id
jsonStr = Newtonsoft.Json.JsonConvert.SerializeObject(model);
string tg_id = model.tg_id;
//jsonStr = System.IO.File.ReadAllText("tmp\\serviceorder.txt", Encoding.UTF8);
SUServiceOrder.serviceOrder orderModel = Newtonsoft.Json.JsonConvert.DeserializeObject<SUServiceOrder.serviceOrder>(jsonStr);
orderModel.sap_code = GlobalStaticObj_YT.SAPCode;
YuTongDic ytDic = new YuTongDic();
if (orderModel.FilesDetails == null)
{
orderModel.FilesDetails = new SUServiceOrder.Files[0];
model.FilesDetails = new Files[0];
}
if (orderModel.ChangePartsDetails == null)
{
orderModel.ChangePartsDetails = new SUServiceOrder.ChangePartsDetail[0];
}
else
{
foreach (SUServiceOrder.ChangePartsDetail detail in orderModel.ChangePartsDetails)
{
detail.parts_source = ytDic.GetYTDicCode("new_parts_source_yt", detail.parts_source);//新件来源
}
}
if (orderModel.RepairItemsDetails == null)
{
orderModel.RepairItemsDetails = new SUServiceOrder.RepairItems[0];
}
orderModel.bill_type_yt = ytDic.GetYTDicCode("bill_type_yt", orderModel.bill_type_yt);//单据类型
orderModel.vehicle_use = ytDic.GetYTDicCode("custom_property_yt", orderModel.vehicle_use);//用户性质（车辆用途）
orderModel.travel_lookup_code = ytDic.GetYTDicCode("traffic_mode_yt", orderModel.travel_lookup_code);//交通方式
orderModel.fault_cause = ytDic.GetYTDicCode("cause_fault_yt", orderModel.fault_cause);//故障原因
orderModel.policy_cost_type = ytDic.GetYTDicCode("cost_type_care_policy_yt", orderModel.policy_cost_type);//费用类型（政策照顾）
orderModel.fault_duty_corp = ytDic.GetYTDicCode("fault_company_yt", orderModel.fault_duty_corp);//故障责任单位
orderModel.part_guarantee_period = ytDic.GetYTDicCode("parts_warranty_agreement_yt", orderModel.part_guarantee_period);//配件协议包修期
orderModel.luxury_cost_type = ytDic.GetYTDicCode("luxury_cost_type", orderModel.luxury_cost_type);//费用类型（高档车）
orderModel.sap_code = GlobalStaticObj_YT.SAPCode;
string requestType = string.IsNullOrEmpty(orderModel.crm_service_bill_code) ? "CREATE" : "UPDATE";
ServicePointManager.ServerCertificateValidationCallback = WebServUtil.ValidateServerCertificate;
SUServiceOrder.clientInfo clientInfo = new SUServiceOrder.clientInfo();
clientInfo.clientID = GlobalStaticObj_YT.ClientID;
clientInfo.serviceID = "serviceOrderSU";
SUServiceOrder.serviceOrderSUService serv = new SUServiceOrder.serviceOrderSUService();
string stationCode = Secret.Encrypt3DES_UTF8(GlobalStaticObj_YT.SAPCode, GlobalStaticObj_YT.KeySecurity_YT);
//string name = Secret.Encrypt3DES_UTF8("张三", GlobalStaticObj_YT.KeySecurity_YT);
//string name1 = Secret.Decrypt3DES_UTF8(name, GlobalStaticObj_YT.KeySecurity_YT);
string dateStr = Secret.Encrypt3DES_UTF8(GlobalStaticObj_Server.Instance.CurrentDateTime.ToString("yyyy-MM-dd HH:mm:ss"), GlobalStaticObj_YT.KeySecurity_YT);
requestType = Secret.Encrypt3DES_UTF8(requestType, GlobalStaticObj_YT.KeySecurity_YT);
clientInfo = WebServUtil.EncModel<SUServiceOrder.clientInfo>(clientInfo);
orderModel = WebServUtil.EncModel<SUServiceOrder.serviceOrder>(orderModel);
orderModel.ChangePartsDetails = WebServUtil.EncList<SUServiceOrder.ChangePartsDetail>(orderModel.ChangePartsDetails);
orderModel.RepairItemsDetails = WebServUtil.EncList<SUServiceOrder.RepairItems>(orderModel.RepairItemsDetails);
orderModel.FilesDetails = WebServUtil.EncList<SUServiceOrder.Files>(orderModel.FilesDetails);
SUServiceOrder.Result result = serv.serviceOrderSU(stationCode, dateStr, requestType, orderModel, clientInfo);
#region 记录日志
orderModel = WebServUtil.DesModel(orderModel);
orderModel.ChangePartsDetails = WebServUtil.DesList(orderModel.ChangePartsDetails);
orderModel.RepairItemsDetails = WebServUtil.DesList(orderModel.RepairItemsDetails);
orderModel.FilesDetails = WebServUtil.DesList(orderModel.FilesDetails);
foreach (SUServiceOrder.Files file in orderModel.FilesDetails)
{
if (!string.IsNullOrEmpty(file.Doc))
{
file.Doc = ".....";
}
}
Utility.Log.Log.writeLineToLog("【三包服务单创建/更新】\r\n" + Newtonsoft.Json.JsonConvert.SerializeObject(orderModel), "接口");
#endregion
string state = Secret.Decrypt3DES_UTF8(result.state, GlobalStaticObj_YT.KeySecurity_YT);
if (state == "F")
{
string errMsg = Secret.Decrypt3DES_UTF8(result.errorMsg, GlobalStaticObj_YT.KeySecurity_YT);
Utility.Log.Log.writeLineToLog("【三包服务单-创建/更新】" + errMsg, "接口");
//return "宇通接口错误:" + errMsg;
return false;
}
else
{
result = WebServUtil.DesModel(result);
Utility.Log.Log.writeLineToLog("【三包服务单-创建/更新】返回数据\r\n" + Newtonsoft.Json.JsonConvert.SerializeObject(result), "接口");
Dictionary<string, string> dicFields = new Dictionary<string, string>();
dicFields.Add("service_no_yt", result.crm_service_bill_code);
dicFields.Add("series_num_yt", result.dsn_service_bill_code);
bool flag = DBHelper.Submit_AddOrEdit("更新三包服务单:宇通service_no_yt,series_num_yt", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_maintain_three_guaranty", "tg_id", tg_id, dicFields);
//return flag ? "" : "DB错误:更新三包服务单失败:宇通宇通crm_service_bill_code,dsn_service_bill_code";
return flag;
}
}
/// <summary>旧件回收-创建
/// </summary>
/// <param name="create_time_start">周期</param>
/// <returns></returns>
public static bool UpPartReturnCreate(string create_time)
{
//如果没有接入码或者sap代码,则不调用接口
if (string.IsNullOrEmpty(GlobalStaticObj_YT.ClientID) ||
string.IsNullOrEmpty(GlobalStaticObj_YT.SAPCode))
{
return true;
}
Utility.Log.Log.writeLineToLog("【旧件回收-新增】" + create_time, "接口");
//jsonStr = System.IO.File.ReadAllText("tmp\\repailbill.txt", Encoding.UTF8);
string create_time_start = string.Empty;
string create_time_end = string.Empty;
if (string.IsNullOrEmpty(create_time))
{
return false;
}
string[] times = create_time.Split(',');
if (times.Length != 2)
{
return false;
}
create_time_start = times[0];
create_time_end = times[1];
string requestType = "CREATE";
ServicePointManager.ServerCertificateValidationCallback = WebServUtil.ValidateServerCertificate;
CreatePartReturn.clientInfo clientInfo = new CreatePartReturn.clientInfo();
clientInfo.clientID = GlobalStaticObj_YT.ClientID;
clientInfo.serviceID = "partReturnCreate";
CreatePartReturn.partReturnCreateService serv = new CreatePartReturn.partReturnCreateService();
string stationCode = Secret.Encrypt3DES_UTF8(GlobalStaticObj_YT.SAPCode, GlobalStaticObj_YT.KeySecurity_YT);
//string stationCode = Secret.Encrypt3DES_UTF8("0000101882", GlobalStaticObj_YT.KeySecurity_YT);
string dateStr = Secret.Encrypt3DES_UTF8(GlobalStaticObj_Server.Instance.CurrentDateTime.ToString("yyyy-MM-dd HH:mm:ss"), GlobalStaticObj_YT.KeySecurity_YT);
requestType = Secret.Encrypt3DES_UTF8(requestType, GlobalStaticObj_YT.KeySecurity_YT);
clientInfo = WebServUtil.EncModel<CreatePartReturn.clientInfo>(clientInfo);
create_time_start = Secret.Encrypt3DES_UTF8(create_time_start, GlobalStaticObj_YT.KeySecurity_YT);
create_time_end = Secret.Encrypt3DES_UTF8(create_time_end, GlobalStaticObj_YT.KeySecurity_YT);
CreatePartReturn.Result result = serv.partReturnCreate(stationCode, dateStr, requestType, create_time_start, create_time_end, clientInfo);
string state = Secret.Decrypt3DES_UTF8(result.state, GlobalStaticObj_YT.KeySecurity_YT);
if (state == "F")
{
string errMsg = Secret.Decrypt3DES_UTF8(result.errorMsg, GlobalStaticObj_YT.KeySecurity_YT);
Utility.Log.Log.writeLineToLog("【旧件回收-新增】" + errMsg, "接口");
return false;
}
result = WebServUtil.DesModel(result);
CreatePartReturn.ResultPartDetail[] details = WebServUtil.DesList(result.PartDetails);
Utility.Log.Log.writeLineToLog("【旧件回收-新增】返回数据\r\n" + Newtonsoft.Json.JsonConvert.SerializeObject(result), "接口");
string returnID = Guid.NewGuid().ToString();
//result = Newtonsoft.Json.JsonConvert.DeserializeObject<CreatePartReturn.Result>("{'state':'','errorMsg':'','info_status_yt':'PCM_FIX_CALLBACK_SUBMIT','old_bill_num':'60024216','com_code':'0000152877','create_time_start':'2014-12-01','create_time_end':'2014-12-31','PartDetails':[{'service_no':'71027988','car_parts_code':'3101-00385','parts_remarks':'','change_num':'2.0','send_num':'','process_mode':'CALLBACK_YUTONG','remarks':''},{'service_no':'71027988','car_parts_code':'9301-02759','parts_remarks':'','change_num':'3.0','send_num':'','process_mode':'CALLBACK_YUTONG','remarks':''}]}");
//details = result.PartDetails;
List<SysSQLString> listSql = new List<SysSQLString>();
#region 旧件返厂单
YuTongDic ytDic = new YuTongDic();
SysSQLString sqlOld = new SysSQLString();
sqlOld.cmdType = CommandType.Text;
sqlOld.sqlString = @"insert into tb_maintain_oldpart_recycle(return_id,create_time_start,create_time_end,receipt_time,remarks,oldpart_receipts_no,service_station_code,info_status_yt)
values( @return_id,@create_time_start,@create_time_end,@receipt_time,@remarks,@oldpart_receipts_no,@service_station_code,@info_status_yt)";
sqlOld.Param = new Dictionary<string, string>();
sqlOld.Param.Add("return_id", returnID);//返厂单ID
//sqlOld.Param.Add("receipts_no", Secret.Decrypt3DES_UTF8(result.old_bill_num, GlobalStaticObj_YT.KeySecurity_YT));//返厂单号
string createStart = result.create_time_start;
if (!string.IsNullOrEmpty(createStart))
{
sqlOld.Param.Add("create_time_start", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(createStart)).ToString());//创建开始时间
}
else
{
sqlOld.Param.Add("create_time_start", null);//创建开始时间
}
string endStart = result.create_time_end;
if (!string.IsNullOrEmpty(endStart))
{
sqlOld.Param.Add("create_time_end", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(endStart)).ToString());//创建结束时间
}
else
{
sqlOld.Param.Add("create_time_end", null);//创建结束时间
}
sqlOld.Param.Add("receipt_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//单据日期
sqlOld.Param.Add("remarks", null);//备注
sqlOld.Param.Add("oldpart_receipts_no", result.old_bill_num);//旧件回收单号
sqlOld.Param.Add("service_station_code", result.com_code);//服务站编码
sqlOld.Param.Add("info_status_yt", ytDic.GetLocalDicID("oldpart_recycle_status", result.info_status_yt));//单据状态
listSql.Add(sqlOld);
#endregion
foreach (CreatePartReturn.ResultPartDetail detail in details)
{
SysSQLString sqlDetail = new SysSQLString();
sqlDetail.cmdType = CommandType.Text;
sqlDetail.sqlString = @"insert into tb_maintain_oldpart_recycle_material_detail (parts_id,service_no,parts_code,parts_name,change_num,send_num,process_mode,remarks,maintain_id)
values (@parts_id,@service_no,@parts_code,@parts_name,@change_num,@send_num,@process_mode,@remarks,@maintain_id)";
sqlDetail.Param = new Dictionary<string, string>();
sqlDetail.Param.Add("parts_id", Guid.NewGuid().ToString());//返厂旧件ID
sqlDetail.Param.Add("service_no", detail.service_no);//服务单号
string partsName = DBHelper.GetSingleValue("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, string.Format("select top 1 parts_name from tb_parts where car_parts_code='{0}'", detail.car_parts_code));
sqlDetail.Param.Add("parts_code", detail.car_parts_code);//配件编码
sqlDetail.Param.Add("parts_name", partsName);//配件名称
sqlDetail.Param.Add("change_num", detail.change_num);//更换数量
sqlDetail.Param.Add("send_num", detail.send_num);//发送数量
sqlDetail.Param.Add("process_mode", ytDic.GetLocalDicID("set_mode_yt", detail.process_mode));//处理方式
sqlDetail.Param.Add("remarks", detail.parts_remarks);//备注
sqlDetail.Param.Add("maintain_id", returnID);
listSql.Add(sqlDetail);
}
if (DBHelper.BatchExeSQLStringMultiByTrans("宇通：创建旧件返厂单", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, listSql))
{
return true;
}
else
{
return false;
}
}
/// <summary>旧件回收--更新
/// </summary>
/// <param name="jsonStr"></param>
/// <returns></returns>
public static bool UpPartRetureUpdate(string jsonStr)
{
//如果没有接入码或者sap代码,则不调用接口
if (string.IsNullOrEmpty(GlobalStaticObj_YT.ClientID) ||
string.IsNullOrEmpty(GlobalStaticObj_YT.SAPCode))
{
return true;
}
UpdatePartReture.partReturn model = Newtonsoft.Json.JsonConvert.DeserializeObject<UpdatePartReture.partReturn>(jsonStr);//取出tg_id
model.sap_code = GlobalStaticObj_YT.SAPCode;
if (model.PartDetails == null)
{
model.PartDetails = new UpdatePartReture.partReturnPartDetail[0];
}
YuTongDic ytDic = new YuTongDic();
model.info_status_yt = ytDic.GetYTDicCode("oldpart_recycle_status", model.info_status_yt);
//替换处理方式编码
foreach (UpdatePartReture.partReturnPartDetail detail in model.PartDetails)
{
detail.process_mode = ytDic.GetYTDicCode("set_mode_yt", detail.process_mode);
}
Utility.Log.Log.writeLineToLog("【旧件回收-更新】" + Newtonsoft.Json.JsonConvert.SerializeObject(model), "接口");
#region 测试数据
//UpdatePartReture.partReturn model = new UpdatePartReture.partReturn();
//model.crm_old_bill_num = "60024236";
//model.info_status_yt = "PCM_FIX_CALLBACK_ENTER";
//model.create_time_start = "2014-12-01";
//model.create_time_end = "2014-12-31";
//model.sap_code = GlobalStaticObj_YT.SAPCode;
//UpdatePartReture.partReturnPartDetail detail = new UpdatePartReture.partReturnPartDetail();
//detail.parts_id = "e89db41c-a3f3-4826-ab65-ff6ebf2bc0ff";
//detail.service_no = "71027151";
//detail.car_parts_code = "1703-01920";
//detail.parts_remark = "";
//detail.change_num = "0.10";
//detail.send_num = "0.10";
//detail.process_mode = "CALLBACK_YUTONG";
//detail.remark = "";
//model.PartDetails = new UpdatePartReture.partReturnPartDetail[1];
//model.PartDetails[0] = detail;
#endregion
string requestType = "UPDATE";
ServicePointManager.ServerCertificateValidationCallback = WebServUtil.ValidateServerCertificate;
UpdatePartReture.clientInfo clientInfo = new UpdatePartReture.clientInfo();
clientInfo.clientID = GlobalStaticObj_YT.ClientID;
clientInfo.serviceID = "partRetureUpdate";
UpdatePartReture.partRetureUpdateService serv = new UpdatePartReture.partRetureUpdateService();
string stationCode = Secret.Encrypt3DES_UTF8(GlobalStaticObj_YT.SAPCode, GlobalStaticObj_YT.KeySecurity_YT);
string dateStr = Secret.Encrypt3DES_UTF8(GlobalStaticObj_Server.Instance.CurrentDateTime.ToString("yyyy-MM-dd HH:mm:ss"), GlobalStaticObj_YT.KeySecurity_YT);
requestType = Secret.Encrypt3DES_UTF8(requestType, GlobalStaticObj_YT.KeySecurity_YT);
clientInfo = WebServUtil.EncModel<UpdatePartReture.clientInfo>(clientInfo);
model = WebServUtil.EncModel<UpdatePartReture.partReturn>(model);
model.PartDetails = WebServUtil.EncList<UpdatePartReture.partReturnPartDetail>(model.PartDetails);
UpdatePartReture.Result result = serv.partRetureUpdate(stationCode, dateStr, requestType, model, clientInfo);
string state = Secret.Decrypt3DES_UTF8(result.state, GlobalStaticObj_YT.KeySecurity_YT);
if (state == "F")
{
string errMsg = Secret.Decrypt3DES_UTF8(result.errorMsg, GlobalStaticObj_YT.KeySecurity_YT);
Utility.Log.Log.writeLineToLog("【旧件回收-更新】" + errMsg, "接口");
return false;
}
else
{
return true;
}
}
/// <summary>
/// 非批量保存配件信息
/// </summary>
/// <param name="partArr">配件信息</param>
/// <param name="updateCount">更新条数</param>
/// <returns></returns>
private static bool SavePartNotBatch(QueryPart.part[] partArr, ref int updateCount)
{
long nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime);
int partIndex = 0;//列表索引
int partCount = 10000;//每批执行条数
int partSum = partArr.Count() / partCount + 1;//执行批数
//partSum = 0;
bool flag = true;//执行结果
DateTime startDate = DateTime.Now;
YuTongDic dic = new YuTongDic();
//配件
DataTable dtParts = DBHelper.GetTable("配件信息", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts", "car_parts_code,parts_id",
string.Format("data_source='{0}'", (int)DataSources.EnumDataSources.YUTONG), null, null);
Dictionary<string, string> dicParts = new Dictionary<string, string>();
foreach (DataRow dr in dtParts.Rows)
{
dicParts.Add(dr["car_parts_code"].ToString(), dr["parts_id"].ToString());
}
//配件替代信息
DataTable dtReplace = DBHelper.GetTable("配件替代信息", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts_replace", "parts_id,repl_id", null, null, null);
//Dictionary<string, string> dicReplace = new Dictionary<string, string>();
List<string> listReplace = new List<string>();
foreach (DataRow dr in dtReplace.Rows)
{
//dicReplace.Add(dr["repl_id"].ToString(), dr["parts_id"].ToString());
listReplace.Add(string.Format("{0},{1}", dr["repl_id"], dr["parts_id"]));
}
CodingRule comm = new CodingRule(DataSources.EnumProjectType.Parts);
string partCode = string.Empty;//配件编码
#region 生成表列
//配件表
List<DataRow> listTbParts = new List<DataRow>();
DataTable dtTbParts = new DataTable();
dtTbParts.Columns.Add(new DataColumn("parts_id", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("ser_parts_code", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("car_parts_code", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("parts_name", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("sales_unit_code", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("data_source", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("default_unit", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("sales_unit_name", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("model", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("retail", typeof(decimal)));
dtTbParts.Columns.Add(new DataColumn("price3a_back", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("price2a_back", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("status", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dtTbParts.Columns.Add("parts_type", typeof(string));
dtTbParts.Columns.Add(new DataColumn("base_unit_code", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("base_unit_name", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("sales_unit_quantity", typeof(int)));
dtTbParts.Columns.Add(new DataColumn("base_unit_quantity", typeof(int)));
dtTbParts.Columns.Add(new DataColumn("create_by", typeof(string)));
dtTbParts.Columns.Add(new DataColumn("create_time", typeof(long)));
//配件价格信息
DataTable dtTbPartsPrice = new DataTable();
List<DataRow> listTbPrice = new List<DataRow>();
dtTbPartsPrice.Columns.Add(new DataColumn("pp_id", typeof(string)));
dtTbPartsPrice.Columns.Add(new DataColumn("parts_id", typeof(string)));
dtTbPartsPrice.Columns.Add("is_stock", typeof(string));
dtTbPartsPrice.Columns.Add("is_purchase", typeof(string));
dtTbPartsPrice.Columns.Add("is_sale", typeof(string));
dtTbPartsPrice.Columns.Add(new DataColumn("unit", typeof(string)));
dtTbPartsPrice.Columns.Add(new DataColumn("ref_out_price", typeof(decimal)));
dtTbPartsPrice.Columns.Add(new DataColumn("out_price_two", typeof(decimal)));
dtTbPartsPrice.Columns.Add(new DataColumn("out_price_three", typeof(decimal)));
dtTbPartsPrice.Columns.Add("sort_index", typeof(string));
dtTbPartsPrice.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dtTbPartsPrice.Columns.Add(new DataColumn("create_by", typeof(string)));
dtTbPartsPrice.Columns.Add(new DataColumn("create_time", typeof(long)));
//替换配件表
DataTable dtTbReplace = new DataTable();
List<DataRow> listTbReplace = new List<DataRow>();
dtTbReplace.Columns.Add(new DataColumn("replace_id", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("parts_id", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("repl_id", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("repl_parts_code", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("repl_parts_status", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("repl_remark", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("change", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("create_by", typeof(string)));
dtTbReplace.Columns.Add(new DataColumn("create_time", typeof(long)));
//单位设置
DataTable dtTbPartsSetup = new DataTable();
List<DataRow> listTbSetup = new List<DataRow>();
dtTbPartsSetup.Columns.Add(new DataColumn("set_id", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("parts_id", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("stock_unit", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("purchase_unit", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("sale_unit", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("stock_purchase", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("purchase_sale", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("create_by", typeof(string)));
dtTbPartsSetup.Columns.Add(new DataColumn("create_time", typeof(long)));
dtTbPartsSetup.Columns.Add(new DataColumn("enable_flag", typeof(string)));
#endregion
for (int i = 0; i < partSum; i++)
{
List<SysSQLString> list = new List<SysSQLString>();
dtTbParts.Rows.Clear();
dtTbReplace.Rows.Clear();
listTbParts.Clear();
listTbReplace.Clear();
listTbPrice.Clear();
listTbSetup.Clear();
for (int y = partIndex; y < partCount; y++)
{
int index = i * partCount + y;
if (index >= partArr.Count())
{
break;
}
#region 配件信息
QueryPart.part item = partArr[index];
string price3a = WebServUtil.GetEncField(item.price3a);//加密
string price2a = WebServUtil.GetEncField(item.price2a);//加密
string partType = dic.GetLocalDicID("part_type", item.part_type);//配件类型
string partsID = null;
bool isAdd = false;//是否新增
if (dicParts.ContainsKey(item.car_parts_code))
{
partsID = dicParts[item.car_parts_code];
}
if (!string.IsNullOrEmpty(partsID))
{
#region 更新语句
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" update tb_parts set ");
strSql.AppendFormat(" parts_name = '{0}' , ", item.parts_name);
strSql.AppendFormat(" sales_unit_code = '{0}' , ", item.unit_code);
strSql.AppendFormat(" sales_unit_name = '{0}' , ", item.unit_name);
strSql.AppendFormat(" model = '{0}' , ", item.model);
strSql.AppendFormat(" status = '{0}' , ", item.status == "0" ? "1" : "0");
strSql.AppendFormat(" retail = '{0}' , ", item.retail);
strSql.AppendFormat(" price3a = {0} , ", price3a);
strSql.AppendFormat(" price2a = {0} , ", price2a);
strSql.AppendFormat(" parts_type='{0}',", partType);
strSql.AppendFormat(" base_unit_code = '{0}' , ", item.basic_unit_code);
strSql.AppendFormat(" base_unit_name = '{0}' , ", item.basic_unit_name);
if (!string.IsNullOrEmpty(item.unit_name_quantity))
{
strSql.AppendFormat(" sales_unit_quantity = {0} , ", item.unit_name_quantity);
}
if (!string.IsNullOrEmpty(item.basic_unit_quantity))
{
strSql.AppendFormat(" base_unit_quantity = {0} , ", item.basic_unit_quantity);
}
strSql.AppendFormat(" enable_flag = '{0}' ,", (int)DataSources.EnumEnableFlag.USING);
strSql.AppendFormat(" data_source = '{0}' ,", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat(" update_time = {0} ,", nowTicks);
strSql.AppendFormat(" update_by = '{0}' ", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat(" where car_parts_code='{0}'; ", item.car_parts_code);
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
#endregion
}
else
{
isAdd = true;
#region 插入语句
partsID = Guid.NewGuid().ToString();
//strSql.Append(" insert into tb_parts(");
//strSql.Append("parts_id,car_parts_code,parts_name,sales_unit_code,data_source,sales_unit_name,model,retail,price3a,price2a,status,enable_flag,base_unit_code,base_unit_name,sales_unit_quantity,base_unit_quantity,create_by,create_time");
//strSql.Append(") values (");
//strSql.AppendFormat("'{0}',", partsID);
//strSql.AppendFormat("'{0}',", item.car_parts_code);
//strSql.AppendFormat("'{0}',", item.parts_name);
//strSql.AppendFormat("'{0}',", item.unit_code);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumDataSources.YUTONG);
//strSql.AppendFormat("'{0}',", item.unit_name);
//strSql.AppendFormat("'{0}',", item.model);
//strSql.AppendFormat("'{0}',", item.retail);
//strSql.AppendFormat("{0},", price3a);
//strSql.AppendFormat("{0},", price2a);
//strSql.AppendFormat("'{0}',", item.status);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumEnableFlag.USING);
//strSql.AppendFormat("'{0}',", item.basic_unit_code);
//strSql.AppendFormat("'{0}',", item.basic_unit_name);
//strSql.AppendFormat("'{0}',", item.unit_name_quantity);
//strSql.AppendFormat("'{0}',", item.basic_unit_quantity);
//strSql.AppendFormat("'{0}',", GlobalStaticObj_Server.Instance.ClientID);
//strSql.AppendFormat("{0})", nowTicks);
//DataRow dr = dtParts.NewRow();
//dr["parts_id"] = partsID;
//dr["car_parts_code"] = item.car_parts_code;
//dtParts.Rows.Add(dr);
dicParts.Add(item.car_parts_code, partsID);
partCode = comm.AddNewNo();
DataRow drParts = dtTbParts.NewRow();
drParts["parts_id"] = partsID;
drParts["ser_parts_code"] = partCode;
drParts["car_parts_code"] = item.car_parts_code;
drParts["parts_name"] = item.parts_name;
drParts["sales_unit_code"] = item.unit_code;
drParts["data_source"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
drParts["default_unit"] = item.unit_name;
drParts["sales_unit_name"] = item.unit_name;
drParts["model"] = item.model;
if (!string.IsNullOrEmpty(item.retail))
{
drParts["retail"] = Convert.ToDecimal(item.retail);
}
drParts["price3a_back"] = item.price3a;
drParts["price2a_back"] = item.price2a;
drParts["parts_type"] = partType;
drParts["status"] = item.status == "0" ? "1" : "0";
drParts["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
drParts["base_unit_code"] = item.basic_unit_code;
drParts["base_unit_name"] = item.basic_unit_name;
if (!string.IsNullOrEmpty(item.unit_name_quantity))
{
drParts["sales_unit_quantity"] = (int)Convert.ToDecimal(item.unit_name_quantity);
}
if (!string.IsNullOrEmpty(item.basic_unit_quantity))
{
drParts["base_unit_quantity"] = (int)Convert.ToDecimal(item.basic_unit_quantity);
}
drParts["create_by"] = GlobalStaticObj_Server.Instance.UserID;
drParts["create_time"] = nowTicks;
listTbParts.Add(drParts);
//dtTbParts.Rows.Add(drParts);
#endregion
}
#endregion
#region 替代配件
foreach (QueryPart.replaceDetail itemReplace in item.partReplace)
{
if (!dicParts.ContainsKey(item.car_parts_code) || dicParts.ContainsKey(itemReplace.repl_parts_code)
|| string.IsNullOrEmpty(itemReplace.repl_parts_code))
{
continue;
}
string partid = dicParts[item.car_parts_code];
string replacepartid = dicParts[itemReplace.repl_parts_code];
if (listReplace.Contains(string.Format("{0},{1}", replacepartid, partid)))
{
#region 更新语句
SysSQLString sysSQLString0 = new SysSQLString();
sysSQLString0.cmdType = CommandType.Text;
sysSQLString0.Param = new Dictionary<string, string>();
StringBuilder strSql0 = new StringBuilder();
strSql0.Append(" update tb_parts_replace set ");
strSql0.AppendFormat(" repl_parts_code = '{0}' , ", itemReplace.repl_parts_code);
strSql0.AppendFormat(" repl_parts_status = '{0}' , ", itemReplace.repl_parts_status);
strSql0.AppendFormat(" repl_remark = '{0}' , ", itemReplace.repl_remark);
strSql0.AppendFormat(" change = '{0}' ", itemReplace.change);
strSql0.AppendFormat(" update_time = {0} ", nowTicks);
strSql0.AppendFormat(" update_by = '{0}' ", GlobalStaticObj_Server.Instance.UserID);
strSql0.AppendFormat(" where parts_id='{0}' and repl_id='{1}'", partid, replacepartid);
list.Add(sysSQLString0);
#endregion
}
else
{
#region 插入语句
replacepartid = Guid.NewGuid().ToString();
//strSql0.Append(" insert into tb_parts_replace(");
//strSql0.Append("replace_id,parts_id,repl_id,repl_parts_code,repl_parts_status,repl_remark,change,create_by,create_time");
//strSql0.Append(") values (");
//strSql0.AppendFormat("'{0}',", replacepartid);
//strSql0.AppendFormat("'{0}',", partid);
//strSql0.AppendFormat("'{0}',", Guid.NewGuid());
//strSql0.AppendFormat("'{0}',", itemReplace.repl_parts_code);
//strSql0.AppendFormat("'{0}',", itemReplace.repl_parts_status);
//strSql0.AppendFormat("'{0}',", itemReplace.repl_remark);
//strSql0.AppendFormat("'{0}',", itemReplace.change);
//strSql0.AppendFormat("'{0}',", GlobalStaticObj_Server.Instance.ClientID);
//strSql0.AppendFormat("{0})", nowTicks);
//DataRow dr = dtReplace.NewRow();
//dr["parts_id"] = partid;
//dr["repl_id"] = replacepartid;
//dtReplace.Rows.Add(dr);
listReplace.Add(string.Format("{0},{1}", replacepartid, partid));
DataRow drReplace = dtTbReplace.NewRow();
drReplace["replace_id"] = replacepartid;
drReplace["parts_id"] = partid;
drReplace["repl_id"] = Guid.NewGuid().ToString();
drReplace["repl_parts_code"] = itemReplace.repl_parts_code;
drReplace["repl_parts_status"] = itemReplace.repl_parts_status;
drReplace["repl_remark"] = itemReplace.repl_remark;
drReplace["change"] = itemReplace.change;
drReplace["create_by"] = GlobalStaticObj_Server.Instance.UserID;
drReplace["create_time"] = nowTicks;
//dtTbReplace.Rows.Add(drReplace);
listTbReplace.Add(drReplace);
#endregion
}
}
#endregion
#region 配件价格信息
if (!isAdd)
{
DataRow drPrice = dtTbPartsPrice.NewRow();
drPrice["pp_id"] = Guid.NewGuid().ToString();
drPrice["parts_id"] = partsID;
drPrice["unit"] = item.unit_name;
drPrice["is_stock"] = "0";
drPrice["is_purchase"] = "0";
drPrice["is_sale"] = "1";
if (!string.IsNullOrEmpty(item.retail))
{
drPrice["ref_out_price"] = Convert.ToDecimal(item.retail);//参考售价
}
if (!string.IsNullOrEmpty(item.price2a))
{
drPrice["out_price_two"] = Convert.ToDecimal(item.price2a);
}
if (!string.IsNullOrEmpty(item.price3a))
{
drPrice["out_price_three"] = Convert.ToDecimal(item.price3a);
}
drPrice["sort_index"] = "0";
drPrice["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
drPrice["create_by"] = GlobalStaticObj_Server.Instance.UserID;
drPrice["create_time"] = nowTicks;
listTbPrice.Add(drPrice);
}
#endregion
partArr[index] = null;//释放项
}
//flag = DBHelper.BatchExeSQLStringMultiByTrans("宇通：同步配件", list);
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tb_parts", listTbParts);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步配件", GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts", listTbParts);
if (!flag)
{
break;
}
#region 更新配件编码
List<SysSQLString> listUpCode = new List<SysSQLString>();
SysSQLString sqlCode = new SysSQLString();
sqlCode.cmdType = CommandType.Text;
sqlCode.Param = new Dictionary<string, string>();
sqlCode.Param.Add("bill_code_rule_id", comm.ruleID);
sqlCode.Param.Add("last_bill_no", partCode);
sqlCode.sqlString = "update sys_bill_code_rule set last_bill_no=@last_bill_no where bill_code_rule_id=@bill_code_rule_id";
listUpCode.Add(sqlCode);
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, listUpCode);
if (!flag)
{
break;
}
#endregion
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tb_parts_replace", listTbReplace);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步替换配件", GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts_replace", listTbReplace);
if (!flag)
{
break;
}
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步价格信息", GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts_price", listTbPrice);
if (!flag)
{
break;
}
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步单位设置", GlobalStaticObj_Server.Instance.MainAccCode, "tb_parts_setup", listTbSetup);
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("更新配件信息", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
break;
}
}
}
#region 加密价格
List<SysSQLString> listUp = new List<SysSQLString>();
SysSQLString sqlPrice = new SysSQLString();
sqlPrice.cmdType = CommandType.Text;
string price3aEnc = WebServUtil.GetEncFieldByField("price3a_back");
string price2aEnc = WebServUtil.GetEncFieldByField("price2a_back");
sqlPrice.sqlString = string.Format("update tb_parts set price3a={0},price2a={1},price2a_back=null,price3a_back=null where price3a_back is not null or price2a_back is not null",
price2aEnc, price2aEnc);
sqlPrice.Param = new Dictionary<string, string>();
listUp.Add(sqlPrice);
DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, listUp);
#endregion
SysConfig sysConfig = new SysConfig();
sysConfig.UpdateLastTime("PartLastTime");
updateCount += partArr.Count();
dtParts = null;
dtReplace = null;
dtTbParts = null;
dtTbPartsPrice = null;
dtTbPartsSetup = null;
dtTbReplace = null;
dic = null;
DateTime endDate = DateTime.Now;
TimeSpan span = endDate - startDate;
return flag;
}
/// <summary> 车型信息同步
/// </summary>
/// <returns>返回同步车型条数，如为-1，同步失败</returns>
public static string LoadBusModel()
{
//如果没有接入码或者sap代码,则不调用接口
if (string.IsNullOrEmpty(GlobalStaticObj_YT.ClientID) ||
string.IsNullOrEmpty(GlobalStaticObj_YT.SAPCode))
{
return "";
}
ServicePointManager.ServerCertificateValidationCallback = WebServUtil.ValidateServerCertificate;
QueryBusModel.clientInfo clientInfo = new QueryBusModel.clientInfo();
clientInfo.clientID = GlobalStaticObj_YT.ClientID;
clientInfo.serviceID = "busModelQuery";
QueryBusModel.busModelQueryService serv = new QueryBusModel.busModelQueryService();
string stationCode = Secret.Encrypt3DES_UTF8(GlobalStaticObj_YT.SAPCode, GlobalStaticObj_YT.KeySecurity_YT);
string dateStr = Secret.Encrypt3DES_UTF8(GlobalStaticObj_Server.Instance.CurrentDateTime.ToString("yyyy-MM-dd HH:mm:ss"), GlobalStaticObj_YT.KeySecurity_YT);
string requestType = Secret.Encrypt3DES_UTF8("QUERY", GlobalStaticObj_YT.KeySecurity_YT);
clientInfo = WebServUtil.EncModel<QueryBusModel.clientInfo>(clientInfo);
DateTime dtStart = GlobalStaticObj_Server.Instance.CurrentDateTime;//开始时间
QueryBusModel.Result result = new QueryBusModel.Result();
string message = string.Empty;
try
{
result = serv.busModelQuery(stationCode, dateStr, requestType, clientInfo);
}
catch (InvalidOperationException ioe)
{
//Utility.Log.Log.writeLineToLog(ioe, "车型信息同步");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("车型信息同步", ioe);
message = ioe.Message;
return "车型信息同步超时！";
}
catch (Exception ex)
{
//Utility.Log.Log.writeLineToLog(ex, "车型信息同步");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("车型信息同步", ex);
message = ex.Message;
return "车型信息同步出错！";
}
string state = Secret.Decrypt3DES_UTF8(result.state, GlobalStaticObj_YT.KeySecurity_YT);
if (state == "F")
{
string errMsg = Secret.Decrypt3DES_UTF8(result.errorMsg, GlobalStaticObj_YT.KeySecurity_YT);
//Utility.Log.Log.writeLineToLog("【车型信息同步】" + errMsg, "接口");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("车型信息同步", errMsg);
return "车型信息接口调用失败";
}
QueryBusModel.busModel[] busModelArr = result.Details;
//if (busModelArr.Length == 0)
//{
// return "车型信息接口没有返回数据";
//}
int updateCount = 0;
busModelArr = WebServUtil.DesList<QueryBusModel.busModel>(busModelArr);
long nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime);
List<SysSQLString> list = new List<SysSQLString>();
DataTable dtModels = DBHelper.GetTable("判断车型信息是否存在", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle_models", "vm_id,models_crm_id ", "", "", "");
YuTongDic ytDic = new YuTongDic();
DataTable dtTbModels = new DataTable();
List<DataRow> listTbModels = new List<DataRow>();
dtTbModels.Columns.Add("vm_id", typeof(string));
dtTbModels.Columns.Add("models_crm_id", typeof(string));
dtTbModels.Columns.Add("vm_name", typeof(string));
dtTbModels.Columns.Add("out_price", typeof(decimal));
dtTbModels.Columns.Add("out_special_price", typeof(decimal));
dtTbModels.Columns.Add("data_source", typeof(string));
dtTbModels.Columns.Add("vm_class", typeof(string));
dtTbModels.Columns.Add("v_sale_type", typeof(string));
dtTbModels.Columns.Add("report_price", typeof(decimal));
dtTbModels.Columns.Add("repair_price", typeof(decimal));
dtTbModels.Columns.Add("begin_date", typeof(string));
dtTbModels.Columns.Add("end_date", typeof(string));
dtTbModels.Columns.Add("status", typeof(string));
dtTbModels.Columns.Add("enable_flag", typeof(string));
dtTbModels.Columns.Add("create_by", typeof(string));
dtTbModels.Columns.Add("create_time", typeof(long));
//StringBuilder sbDelete = new StringBuilder();//删除ID
foreach (QueryBusModel.busModel item in busModelArr)
{
if (string.IsNullOrEmpty(item.vm_code))
{
continue;
}
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
StringBuilder strSql = new StringBuilder();
//bool isContactExist = DBHelper.IsExist("判断车型信息是否存在", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle_models", "vm_code='" + item.vm_code + "'");
string vm_id = string.Empty;
DataRow[] drsModels = dtModels.Select("models_crm_id='" + item.vm_code + "'");
if (drsModels.Count() > 0)
{
vm_id = drsModels[0]["vm_id"].ToString();
//sbDelete.AppendFormat("'{0}',", vm_id);
}
string vm_type = ytDic.GetLocalDicID("vm_class", item.vm_type);
if (string.IsNullOrEmpty(vm_id))
{
vm_id = Guid.NewGuid().ToString();
DataRow drModel = dtTbModels.NewRow();
drModel["vm_id"] = vm_id;
drModel["models_crm_id"] = item.vm_code;
drModel["vm_name"] = item.remark;
if (!string.IsNullOrEmpty(item.out_price))
{
drModel["out_price"] = Convert.ToDecimal(item.out_price);
}
if (!string.IsNullOrEmpty(item.out_special_price))
{
drModel["out_special_price"] = Convert.ToDecimal(item.out_special_price);
}
drModel["data_source"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
drModel["vm_class"] = vm_type;
drModel["v_sale_type"] = item.v_sale_type;
if (!string.IsNullOrEmpty(item.report_price))
{
drModel["report_price"] = Convert.ToDecimal(item.report_price);
}
if (!string.IsNullOrEmpty(item.repair_price))
{
drModel["repair_price"] = Convert.ToDecimal(item.repair_price);
}
drModel["begin_date"] = item.begin_date;
drModel["end_date"] = item.end_date;
drModel["status"] = item.status == "0" ? "1" : "0";
drModel["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
drModel["create_by"] = GlobalStaticObj_Server.Instance.UserID;
drModel["create_time"] = nowTicks;
listTbModels.Add(drModel);
}
else
{
strSql.Append(" update tb_vehicle_models set ");
//strSql.Append("vm_name=@vm_name,");
strSql.AppendFormat("vm_name='{0}',", item.remark.Replace("'", "''"));
if (!string.IsNullOrEmpty(item.out_price))
{
strSql.AppendFormat("out_price={0},", item.out_price);
}
if (!string.IsNullOrEmpty(item.out_special_price))
{
strSql.AppendFormat("out_special_price={0},", item.out_special_price);
}
strSql.AppendFormat("data_source='{0}',", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat("vm_class='{0}',", vm_type);
strSql.AppendFormat("v_sale_type='{0}',", item.v_sale_type);
if (!string.IsNullOrEmpty(item.report_price))
{
strSql.AppendFormat("report_price={0},", item.report_price);
}
if (!string.IsNullOrEmpty(item.repair_price))
{
strSql.AppendFormat("repair_price={0},", item.repair_price);
}
strSql.AppendFormat("begin_date='{0}',", item.begin_date);
strSql.AppendFormat("end_date='{0}',", item.end_date);
strSql.AppendFormat("status='{0}',", item.status == "0" ? "1" : "0");
strSql.AppendFormat("update_by='{0}',", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat("update_time={0}", nowTicks);
strSql.AppendFormat(" where vm_id='{0}'", vm_id);
sysSQLString.sqlString = strSql.ToString();
//sysSQLString.Param.Add("vm_name", item.remark);
list.Add(sysSQLString);
}
}
bool flag = true;
//if (sbDelete.Length > 0)
//{
// flag = DBHelper.BatchDeleteDataByWhere("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle_models", " vm_id in (" + sbDelete.ToString().TrimEnd(',') + ")");
// if (!flag)
// {
// return "";
// }
//}
if (listTbModels.Count > 0)
{
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("", GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle_models", listTbModels);
if (!flag)
{
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("车型信息同步", "车型新增失败");
return "车型新增失败";
}
}
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("车型信息同步", "车型更新失败");
return "车型更新失败";
}
}
updateCount += busModelArr.Count();
DateTime dtEnd = GlobalStaticObj_Server.Instance.CurrentDateTime;//结束时间
int totalCount = int.Parse(DBHelper.GetSingleValue("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "select count(1) from tb_vehicle_models where data_source='2'"));
WebServUtil.WriteInterficeSync(DataSources.EnumInterfaceType.BusModel, DataSources.EnumExternalSys.YTCRM, totalCount, updateCount, GlobalStaticObj_Server.Instance.CurrentDateTime);
WebServUtil.WriteInterficeSyncLog(DataSources.EnumInterfaceType.BusModel, DataSources.EnumExternalSys.YTCRM, DataSources.EnumSyncDirection.DownLoad, "tb_vehicle_models", dtStart, dtEnd, updateCount, message);
return "";
}
/// <summary>
/// 非批量保存车辆客户信息
/// </summary>
/// <param name="customerArr">车辆客户信息</param>
/// <param name="updateCount">更新条数</param>
/// <returns></returns>
private static bool SaveCustomerNotBatch(QueryCustomer.customer[] customerArr, ref int updateCount)
{
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
int customerIndex = 0;//列表索引
int customerCount = 10000;//每批执行条数
//customerCount = customerArr.Count();
int customerSum = customerArr.Count() / customerCount + 1;//执行批数
//int contactSum = 3;
bool flag = true;//执行结果
DateTime startDate = DateTime.Now;
YuTongDic dic = new YuTongDic();
//客户
DataTable dtCustomer = DBHelper.GetTable("获取车辆客户ID", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_customer", "cust_id,cust_code", null, null, null);
#region 生成表列
DataTable dt = new DataTable();
List<DataRow> listTbCustomer = new List<DataRow>();
dt.Columns.Add(new DataColumn("cust_crm_guid", typeof(string)));
dt.Columns.Add(new DataColumn("cust_id", typeof(string)));
dt.Columns.Add(new DataColumn("cust_code", typeof(string)));
dt.Columns.Add(new DataColumn("cust_name", typeof(string)));
dt.Columns.Add(new DataColumn("province", typeof(string)));
dt.Columns.Add(new DataColumn("city", typeof(string)));
dt.Columns.Add(new DataColumn("county", typeof(string)));
dt.Columns.Add(new DataColumn("cust_address", typeof(string)));
dt.Columns.Add(new DataColumn("zip_code", typeof(string)));
dt.Columns.Add(new DataColumn("cust_phone", typeof(string)));
dt.Columns.Add(new DataColumn("cust_fax", typeof(string)));
dt.Columns.Add(new DataColumn("cust_email", typeof(string)));
dt.Columns.Add(new DataColumn("cust_website", typeof(string)));
dt.Columns.Add(new DataColumn("credit_rating", typeof(string)));
dt.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dt.Columns.Add(new DataColumn("data_source", typeof(string)));
dt.Columns.Add(new DataColumn("cust_relation", typeof(string)));
dt.Columns.Add(new DataColumn("indepen_legalperson", typeof(string)));
dt.Columns.Add(new DataColumn("market_segment", typeof(string)));
dt.Columns.Add(new DataColumn("institution_code", typeof(string)));
dt.Columns.Add(new DataColumn("com_constitution", typeof(string)));
dt.Columns.Add(new DataColumn("registered_capital", typeof(string)));
dt.Columns.Add(new DataColumn("agency", typeof(string)));
dt.Columns.Add(new DataColumn("status", typeof(string)));
dt.Columns.Add(new DataColumn("sap_code", typeof(string)));
dt.Columns.Add(new DataColumn("business_scope", typeof(string)));
dt.Columns.Add(new DataColumn("ent_qualification", typeof(string)));
dt.Columns.Add(new DataColumn("create_by", typeof(string)));
dt.Columns.Add(new DataColumn("create_time", typeof(long)));
#endregion
for (int i = 0; i < customerSum; i++)
{
List<SysSQLString> list = new List<SysSQLString>();
dt.Rows.Clear();
listTbCustomer.Clear();
for (int y = customerIndex; y < customerCount; y++)
{
int index = i * customerCount + y;
if (index >= customerArr.Count())
{
break;
}
QueryCustomer.customer item = customerArr[index];
//bool isContactExist = DBHelper.IsExist("判断车辆客户信息是否存在", "tb_customer", "cust_code='" + item.cust_code + "'");
string custID = null;
if (dtCustomer != null && dtCustomer.Rows.Count > 0)
{
DataRow[] drs = dtCustomer.Select("cust_code='" + item.cust_code + "'");
if (drs.Count() > 0)
{
custID = drs[0]["cust_id"].ToString();
}
}
string credit_rating = dic.GetLocalDicID("credit_rating", item.credit_rating);
string cust_relation = dic.GetLocalDicID("cust_relation", item.cust_relation);
string com_constitution = dic.GetLocalDicID("com_constitution", item.com_constitution);
string ent_qualification = dic.GetLocalDicID("ent_qualification", item.ent_qualification);
string market_segment = dic.GetLocalDicID("market_segment", item.market_segment);
if (!string.IsNullOrEmpty(custID))
{
#region 更新语句
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
sysSQLString.Param.Add("business_scope", item.business_scope);
StringBuilder strSql = new StringBuilder();
strSql.Append("update tb_customer set ");
strSql.AppendFormat(" cust_crm_guid= '{0}' , ", item.cust_crm_guid);
strSql.AppendFormat(" cust_code= '{0}' , ", item.cust_code);
strSql.AppendFormat(" cust_name= '{0}' , ", item.cust_name);
strSql.AppendFormat(" province= '{0}' , ", item.province);
strSql.AppendFormat(" city= '{0}' , ", item.city);
strSql.AppendFormat(" county= '{0}' , ", item.county);
strSql.AppendFormat(" cust_address= '{0}' , ", item.cust_address);
strSql.AppendFormat(" zip_code= '{0}' , ", item.zip_code);
strSql.AppendFormat(" cust_phone= '{0}' , ", item.cust_phone);
strSql.AppendFormat(" cust_fax= '{0}' , ", item.cust_fax);
strSql.AppendFormat(" cust_email= '{0}' , ", item.email);
strSql.AppendFormat(" cust_website= '{0}' , ", item.cust_website);
strSql.AppendFormat(" credit_rating= '{0}' , ", credit_rating);
strSql.AppendFormat(" enable_flag= '{0}' , ", (int)DataSources.EnumEnableFlag.USING);
strSql.AppendFormat(" data_source= '{0}' , ", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat(" cust_relation= '{0}' , ", cust_relation);
strSql.AppendFormat(" indepen_legalperson= '{0}' , ", item.indepen_legalperson);
strSql.AppendFormat(" market_segment= '{0}' , ", market_segment);
strSql.AppendFormat(" institution_code= '{0}' , ", item.institution_code);
strSql.AppendFormat(" com_constitution= '{0}' , ", com_constitution);
strSql.AppendFormat(" registered_capital= '{0}' , ", item.registered_capital);
strSql.AppendFormat(" agency= '{0}' , ", item.agency);
strSql.AppendFormat(" status= '{0}' , ", item.status == "0" ? "1" : "0");
strSql.AppendFormat(" sap_code= '{0}' , ", item.sap_code);
strSql.Append(" business_scope= @business_scope , ");
strSql.AppendFormat(" ent_qualification= '{0}' , ", ent_qualification);
strSql.AppendFormat(" update_by= '{0}' , ", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat(" update_time={0} ", nowTicks);
strSql.AppendFormat(" where cust_id='{0}' ", custID);
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
#endregion
}
else
{
#region 插入语句
#region 验证字段长度
//string file = string.Empty;
//if (item.zip_code.Length > 5)
//{
// file = "zip_code";
//}
//if (item.cust_crm_guid.Length > 40)
//{
// file = "cust_crm_guid";
//}
//if (item.cust_code.Length > 30)
//{
// file = "cust_code";
//}
//if (item.cust_name.Length > 100)
//{
// file = "cust_name";
//}
//if (item.province.Length > 40)
//{
// file = "province";
//}
//if (item.city.Length > 40)
//{
// file = "city";
//}
//if (item.county.Length > 40)
//{
// file = "county";
//}
//if (item.cust_address.Length > 100)
//{
// file = "cust_address";
//}
//if (item.cust_phone.Length > 15)
//{
// file = "cust_phone";
//}
//if (item.cust_fax.Length > 15)
//{
// file = "cust_fax";
//}
//if (item.email.Length > 30)
//{
// file = "email";
//}
//if (item.cust_website.Length > 100)
//{
// file = "cust_website";
//}
//if (credit_rating.Length > 40)
//{
// file = "credit_rating";
//}
//if (item.indepen_legalperson.Length > 5)
//{
// file = "indepen_legalperson";
//}
//if (cust_relation.Length > 40)
//{
// file = "cust_relation";
//}
//if (market_segment.Length > 40)
//{
// file = "market_segment";
//}
//if (item.institution_code.Length > 50)
//{
// file = "institution_code";
//}
//if (com_constitution.Length > 40)
//{
// file = "com_constitution";
//}
//if (item.registered_capital.Length > 20)
//{
// file = "registered_capital";
//}
//if (item.agency.Length > 30)
//{
// file = "agency";
//}
//if (item.sap_code.Length > 50)
//{
// file = "sap_code";
//}
//if (item.business_scope.Length > 500)
//{
// file = "business_scope";
//}
//if (ent_qualification.Length > 40)
//{
// file = "ent_qualification";
//}
//if (!string.IsNullOrEmpty(file))
//{
// file = "";
//}
#endregion
DataRow dr = dt.NewRow();
dr["cust_crm_guid"] = item.cust_crm_guid;
dr["cust_id"] = Guid.NewGuid().ToString();
dr["cust_code"] = item.cust_code;
dr["cust_name"] = item.cust_name;
dr["province"] = item.province;
dr["city"] = item.city;
dr["county"] = item.county;
dr["cust_address"] = item.cust_address;
dr["zip_code"] = item.zip_code;
dr["cust_phone"] = item.cust_phone;
dr["cust_fax"] = item.cust_fax;
dr["cust_email"] = item.email;
dr["cust_website"] = item.cust_website;
dr["credit_rating"] = credit_rating;
dr["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
dr["data_source"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
dr["cust_relation"] = cust_relation;
dr["indepen_legalperson"] = item.indepen_legalperson;
dr["market_segment"] = market_segment;
dr["institution_code"] = item.institution_code;
dr["com_constitution"] = com_constitution;
dr["registered_capital"] = item.registered_capital;
dr["agency"] = item.agency;
dr["status"] = item.status == "0" ? "1" : "0";
dr["sap_code"] = item.sap_code;
dr["business_scope"] = item.business_scope;
dr["ent_qualification"] = ent_qualification;
dr["create_by"] = GlobalStaticObj_Server.Instance.UserID;
dr["create_time"] = nowTicks;
//dt.Rows.Add(dr);
listTbCustomer.Add(dr);
#endregion
}
customerArr[index] = null;
}
//flag = DBHelper.BatchExeSQLMultiByTrans("宇通：同步车辆客户信息", list);
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tb_customer", listTbCustomer);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步车辆客户信息", GlobalStaticObj_Server.Instance.MainAccCode, "tb_customer", listTbCustomer);
if (!flag)
{
break;
}
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
break;
}
}
}
SysConfig sysConfig = new SysConfig();
sysConfig.UpdateLastTime("CustomerLastTime");
updateCount += customerArr.Count();
dtCustomer = null;//释放
dic = null;
DateTime endDate = DateTime.Now;
TimeSpan span = endDate - startDate;
return flag;
}
/// <summary>
/// 非批量保存车辆信息
/// </summary>
/// <param name="busArr">车辆信息</param>
/// <returns></returns>
private static bool SaveBusNotBatch(QueryBus.Detail[] busArr, ref int updateCount)
{
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
int busIndex = 0;//列表索引
int busCount = 10000;//每批执行条数
//busCount = busArr.Count();
int busSum = busArr.Count() / busCount + 1;//执行批数
int busSumCount = busArr.Count();
//int contactSum = 3;
bool flag = true;//执行结果
DateTime startDate = DateTime.Now;
YuTongDic dic = new YuTongDic();
//车辆
DataTable dtVehicle = DBHelper.GetTable("获取车辆信息车辆ID", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle", "v_id,turner", null, null, null);
//车辆司机
DataTable dtDriver = DBHelper.GetTable("获取车辆司机关系表中的司机ID", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tr_driver_vehicle", "driver_id,v_id", null, null, null);
DataTable dtContacts = DBHelper.GetTable("获取联系人", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_contacts", "cont_id,cont_crm_guid,cont_name", "cont_crm_guid is not null", null, null);
//品牌
DataTable dtBrand = DBHelper.GetTable("获取品牌", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "sys_dictionaries a inner join sys_dictionaries b on a.dic_id=b.parent_id", "b.dic_id,b.dic_name", "a.dic_code='sys_vehicle_brand'", "", "");
//车型
DataTable dtVehicleModels = DBHelper.GetTable("获取车型信息", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle_models", "models_crm_id,vm_id", "isnull(models_crm_id,'')<>''", "", "");
#region 初始化表列
///车辆表
DataTable dt = new DataTable();
List<DataRow> listTbVehicle = new List<DataRow>();
dt.Columns.Add(new DataColumn("v_id", typeof(string)));
dt.Columns.Add(new DataColumn("data_source", typeof(string)));
dt.Columns.Add(new DataColumn("license_plate", typeof(string)));
dt.Columns.Add(new DataColumn("v_brand", typeof(string)));
dt.Columns.Add(new DataColumn("vin", typeof(string)));
dt.Columns.Add(new DataColumn("v_model", typeof(string)));
dt.Columns.Add(new DataColumn("carbuy_date", typeof(long)));
dt.Columns.Add(new DataColumn("engine_num", typeof(string)));
dt.Columns.Add(new DataColumn("turner", typeof(string)));
dt.Columns.Add(new DataColumn("customer_unit", typeof(string)));
dt.Columns.Add(new DataColumn("use_unit", typeof(string)));
dt.Columns.Add(new DataColumn("vehicle_use", typeof(string)));
dt.Columns.Add(new DataColumn("operating_line", typeof(string)));
dt.Columns.Add(new DataColumn("point_departure", typeof(string)));
dt.Columns.Add(new DataColumn("cont_name", typeof(string)));
dt.Columns.Add(new DataColumn("cont_phone", typeof(string)));
dt.Columns.Add(new DataColumn("place_arrival", typeof(string)));
dt.Columns.Add(new DataColumn("warranty_period", typeof(int)));
dt.Columns.Add("status", typeof(string));
dt.Columns.Add(new DataColumn("warranty_mileage", typeof(decimal)));
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)));
///车辆司机表
DataTable dtTbDriver = new DataTable();
List<DataRow> listTbDriver = new List<DataRow>();
dtTbDriver.Columns.Add(new DataColumn("driver_id", typeof(string)));
dtTbDriver.Columns.Add(new DataColumn("driver_name", typeof(string)));
dtTbDriver.Columns.Add(new DataColumn("driver_phone", typeof(string)));
dtTbDriver.Columns.Add(new DataColumn("enable_flag", typeof(string)));
dtTbDriver.Columns.Add(new DataColumn("create_by", typeof(string)));
dtTbDriver.Columns.Add(new DataColumn("create_time", typeof(long)));
///车辆司机关联表
DataTable dtTrDriver = new DataTable();
List<DataRow> listTrDriver = new List<DataRow>();
dtTrDriver.Columns.Add(new DataColumn("dir_v_id", typeof(string)));
dtTrDriver.Columns.Add(new DataColumn("v_id", typeof(string)));
dtTrDriver.Columns.Add(new DataColumn("driver_id", typeof(string)));
#endregion
for (int i = 0; i < busSum; i++)
{
List<SysSQLString> list = new List<SysSQLString>();
dt.Rows.Clear();
dtTbDriver.Rows.Clear();
dtTrDriver.Rows.Clear();
listTbDriver.Clear();
listTbVehicle.Clear();
listTrDriver.Clear();
for (int y = busIndex; y < busCount; y++)
{
int index = i * busCount + y;
if (index >= busSumCount)
{
break;
}
QueryBus.Detail item = busArr[index];
string v_id = null;
string dtCarBuyDate = "";
//购买日期为空处理
if (!String.IsNullOrEmpty(item.carbuy_date))
{
dtCarBuyDate = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(item.carbuy_date)).ToString();
}
string vehicle_use = dic.GetLocalDicID("vehicle_use", item.vehicle_use);
string cont_id = string.Empty;//联系人ID转换成本地ID
if (!string.IsNullOrEmpty(item.cont_name))
{
DataRow[] drsContacts = dtContacts.Select(string.Format("cont_crm_guid='{0}'", item.cont_name));
if (drsContacts.Count() > 0)
{
cont_id = drsContacts[0]["cont_id"].ToString();
}
}
DataRow[] drs = dtVehicle.Select("turner='" + item.turner + "'");
if (drs.Count() > 0)
{
v_id = drs[0]["v_id"].ToString();
}
string brand_id = string.Empty;//品牌ID
DataRow[] drsBrand = dtBrand.Select(string.Format("dic_name='{0}'", item.v_brand));
if (drsBrand.Count() > 0)
{
brand_id = drsBrand[0]["dic_id"].ToString();
}
string models_id = string.Empty;//车型ID
DataRow[] drsModels = dtVehicleModels.Select(string.Format("models_crm_id='{0}'", item.v_model));
if (drsModels.Count() > 0)
{
models_id = drsModels[0]["vm_id"].ToString();
}
#region 验证字段长度
//string file = string.Empty;
//if (item.license_plate.Length > 30)
//{
// file = "license_plate";
//}
//if (item.v_brand.Length > 40)
//{
// file = "v_brand";
//}
//if (item.v_model.Length > 40)
//{
// file = "v_model";
//}
//if (item.vin.Length > 20)
//{
// file = "vin";
//}
//if (item.engine_num.Length > 30)
//{
// file = "engine_num";
//}
//if (item.turner.Length > 50)
//{
// file = "turner";
//}
//if (item.customer_unit.Length > 50)
//{
// file = "customer_unit";
//}
//if (item.use_unit.Length > 50)
//{
// file = "use_unit";
//}
//if (vehicle_use.Length > 50)
//{
// file = "vehicle_use";
//}
//if (item.operating_line.Length > 50)
//{
// file = "operating_line";
//}
//if (item.point_departure.Length > 100)
//{
// file = "point_departure";
//}
//if (item.cont_phone.Length > 50)
//{
// file = "cont_phone";
//}
//if (item.place_arrival.Length > 100)
//{
// file = "place_arrival";
//}
//if (!string.IsNullOrEmpty(file))
//{
// return false;
//}
#endregion
if (!string.IsNullOrEmpty(v_id))
{
#region 更新语句
StringBuilder strSql = new StringBuilder();
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
strSql.Append("update tb_vehicle set ");
strSql.AppendFormat(" data_source= '{0}' , ", (int)DataSources.EnumDataSources.YUTONG);
strSql.AppendFormat(" license_plate= '{0}' , ", item.license_plate);
strSql.AppendFormat(" v_brand= '{0}' , ", brand_id);
strSql.AppendFormat(" vin= '{0}' , ", item.vin);
strSql.AppendFormat(" v_model= '{0}' , ", models_id);
if (!string.IsNullOrEmpty(dtCarBuyDate))
{
strSql.AppendFormat(" carbuy_date= {0} , ", dtCarBuyDate);
}
strSql.AppendFormat(" engine_num= '{0}' , ", item.engine_num);
strSql.AppendFormat(" turner= '{0}' , ", item.turner);
strSql.AppendFormat(" customer_unit= '{0}' , ", item.customer_unit);
strSql.AppendFormat(" use_unit= '{0}' , ", item.use_unit);
strSql.AppendFormat(" vehicle_use= '{0}' , ", vehicle_use);
strSql.AppendFormat(" operating_line= '{0}' , ", item.operating_line);
strSql.AppendFormat(" point_departure= '{0}' , ", item.point_departure);
strSql.AppendFormat(" cont_name= '{0}' , ", cont_id);
strSql.AppendFormat(" cont_phone= '{0}' , ", item.cont_phone);
strSql.AppendFormat(" place_arrival= '{0}' , ", item.place_arrival);
if (!string.IsNullOrEmpty(item.warranty_period))
{
strSql.AppendFormat(" warranty_period= '{0}' , ", item.warranty_period);
}
if (!string.IsNullOrEmpty(item.warranty_mileage))
{
strSql.AppendFormat(" warranty_mileage= {0} , ", item.warranty_mileage);
}
strSql.AppendFormat(" status='{0}',", item.status == "0" ? "1" : "0");
strSql.AppendFormat(" enable_flag= '{0}' , ", (int)DataSources.EnumEnableFlag.USING);
strSql.AppendFormat(" update_time= {0} , ", nowTicks);
strSql.AppendFormat(" update_by= '{0}' ", GlobalStaticObj_Server.Instance.UserID);
strSql.AppendFormat(" where turner='{0}' ", item.turner);
sysSQLString.sqlString = strSql.ToString();
list.Add(sysSQLString);
#endregion
}
else
{
#region 插入语句
v_id = Guid.NewGuid().ToString();
//strSql.Append("insert into tb_vehicle(");
//strSql.Append("v_id,data_source,license_plate,v_brand,vin,v_model,carbuy_date,engine_num,turner,customer_unit,use_unit,vehicle_use,operating_line,point_departure,cont_name,cont_phone,place_arrival,warranty_period,warranty_mileage,enable_flag,create_by,create_time");
//strSql.Append(") values (");
//strSql.AppendFormat("'{0}',", v_id);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumDataSources.YUTONG);
//strSql.AppendFormat("'{0}',", item.license_plate);
//strSql.AppendFormat("'{0}',", item.v_brand);
//strSql.AppendFormat("'{0}',", item.vin);
//strSql.AppendFormat("'{0}',", item.v_model);
//strSql.AppendFormat("'{0}',", item.carbuy_date);
//strSql.AppendFormat("'{0}',", item.engine_num);
//strSql.AppendFormat("'{0}',", item.turner);
//strSql.AppendFormat("'{0}',", item.customer_unit);
//strSql.AppendFormat("'{0}',", item.use_unit);
//strSql.AppendFormat("'{0}',", vehicle_use);
//strSql.AppendFormat("'{0}',", item.operating_line);
//strSql.AppendFormat("'{0}',", item.point_departure);
//strSql.AppendFormat("'{0}',", item.cont_name);
//strSql.AppendFormat("'{0}',", item.cont_phone);
//strSql.AppendFormat("'{0}',", item.place_arrival);
//strSql.AppendFormat("'{0}',", item.warranty_period);
//strSql.AppendFormat("'{0}',", item.warranty_mileage);
//strSql.AppendFormat("'{0}',", (int)DataSources.EnumEnableFlag.USING);
//strSql.AppendFormat("'{0}',", GlobalStaticObj_Server.Instance.ClientID);
//strSql.AppendFormat("{0})", nowTicks);
DataRow dr = dt.NewRow();
dr["v_id"] = v_id;
dr["data_source"] = ((int)DataSources.EnumDataSources.YUTONG).ToString();
dr["license_plate"] = item.license_plate;
dr["v_brand"] = brand_id;
dr["vin"] = item.vin;
dr["v_model"] = models_id;
if (!string.IsNullOrEmpty(dtCarBuyDate))
{
dr["carbuy_date"] = dtCarBuyDate;
}
dr["engine_num"] = item.engine_num;
dr["turner"] = item.turner;
dr["customer_unit"] = item.customer_unit;
dr["use_unit"] = item.use_unit;
dr["vehicle_use"] = vehicle_use;
dr["operating_line"] = item.operating_line;
dr["point_departure"] = item.point_departure;
dr["cont_name"] = cont_id;
dr["cont_phone"] = item.cont_phone;
dr["place_arrival"] = item.place_arrival;
if (!string.IsNullOrEmpty(item.warranty_period))
{
dr["warranty_period"] = item.warranty_period;
}
if (!string.IsNullOrEmpty(item.warranty_mileage))
{
dr["warranty_mileage"] = item.warranty_mileage;
}
dr["status"] = item.status == "0" ? "1" : "0";
dr["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
dr["create_by"] = GlobalStaticObj_Server.Instance.UserID;
dr["create_time"] = nowTicks;
//dt.Rows.Add(dr);
listTbVehicle.Add(dr);
#endregion
}
#region 车辆司机信息处理 tr_driver_vehicle,tb_driver
string driver_id = null;
string driver_name = string.Empty;
DataRow[] drsDriver = dtDriver.Select("v_id='" + v_id + "'");
if (drsDriver.Count() > 0)
{
driver_id = drsDriver[0]["driver_id"].ToString();
}
DataRow[] drsDriverName = dtContacts.Select(string.Format("cont_crm_guid='{0}'", item.driver_name));
if (drsDriverName.Count() > 0)
{
driver_name = drsDriverName[0]["cont_name"].ToString();
}
if (!string.IsNullOrEmpty(driver_id))
{
#region 更新车辆司机信息
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
//if (item.driver_name.Length > 15)
//{
// file = "driver_name";
//}
//if (item.driver_phone.Length > 15)
//{
// file = "driver_phone";
//}
sysSQLString.sqlString = string.Format(" update tb_driver set driver_name='{0}',driver_phone='{1}',update_by='{2}',update_time={3} where driver_id='{4}'",
driver_name, item.driver_phone, GlobalStaticObj_Server.Instance.UserID, nowTicks, driver_id);
sysSQLString.Param = new Dictionary<string, string>();
list.Add(sysSQLString);
#endregion
}
else
{
#region 插入车辆司机信息
driver_id = Guid.NewGuid().ToString();
// sysSQLString = new SysSQLString();
// sysSQLString.cmdType = CommandType.Text;
// sysSQLString.sqlString = string.Format(@"insert into tb_driver(driver_id,driver_name,driver_phone,enable_flag,create_by,create_time)
// values('{0}','{1}','{2}','{3}','{4}',{5})",
// driver_id, item.driver_name, item.driver_phone, (int)DataSources.EnumEnableFlag.USING, GlobalStaticObj_Server.Instance.ClientID, nowTicks);
// sysSQLString.Param = new Dictionary<string, string>();
//list.Add(sysSQLString);
DataRow drDriver = dtTbDriver.NewRow();
drDriver["driver_id"] = driver_id;
drDriver["driver_name"] = driver_name;
drDriver["driver_phone"] = item.driver_phone;
drDriver["enable_flag"] = ((int)DataSources.EnumEnableFlag.USING).ToString();
drDriver["create_by"] = GlobalStaticObj_Server.Instance.UserID;
drDriver["create_time"] = nowTicks;
//dtTbDriver.Rows.Add(drDriver);
listTbDriver.Add(drDriver);
#endregion
#region 插入车辆司机关系表
string dir_v_id = Guid.NewGuid().ToString();
//sysSQLString = new SysSQLString();
//sysSQLString.cmdType = CommandType.Text;
//sysSQLString.sqlString = string.Format("insert into tr_driver_vehicle(dir_v_id,v_id,driver_id) values('{0}','{1}','{2}')",
// dir_v_id, v_id, driver_id);
//sysSQLString.Param = new Dictionary<string, string>();
//list.Add(sysSQLString);
DataRow drTr = dtTrDriver.NewRow();
drTr["dir_v_id"] = dir_v_id;
drTr["v_id"] = v_id;
drTr["driver_id"] = driver_id;
//dtTrDriver.Rows.Add(drTr);
listTrDriver.Add(drTr);
#endregion
}
#endregion
busArr[index] = null;//释放项
}
//flag = DBHelper.BatchExeSQLStringMultiByTrans("宇通：同步车辆信息", list);
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tb_vehicle", listTbVehicle);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步车辆信息", GlobalStaticObj_Server.Instance.MainAccCode, "tb_vehicle", listTbVehicle);
if (!flag)
{
break;
}
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tb_driver", listTbDriver);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("同步司机", GlobalStaticObj_Server.Instance.MainAccCode, "tb_driver", listTbDriver);
if (!flag)
{
break;
}
//flag = DBHelper.BatchExeSQLStringMultiByTrans("tr_driver_vehicle", listTrDriver);
flag = DBHelper.SqlBulkByTransNoLogNoBackUp("", GlobalStaticObj_Server.Instance.MainAccCode, "tr_driver_vehicle", listTrDriver);
if (!flag)
{
break;
}
if (list.Count > 0)
{
flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.Instance.MainAccCode, list);
if (!flag)
{
break;
}
}
}
SysConfig sysConfig = new SysConfig();
sysConfig.UpdateLastTime("BusLastTime");
updateCount += busArr.Count();
#region 释放资源
dic = null;
//车辆
dtVehicle = null;
//车辆司机
dtDriver = null;
dtContacts = null;
//品牌
dtBrand = null;
//车型
dtVehicleModels = null;
#endregion
DateTime endDate = DateTime.Now;
TimeSpan span = endDate - startDate;
return flag;
}
/// <summary> 服务站信息同步
/// </summary>
/// <param name="clientID">接入码</param>
/// <param name="sapCode">服务站代码</param>
/// <param name="isSave">是否存库</param>
/// <returns>返回同步服务站信息条数，如为-1，同步失败</returns>
public static string LoadServiceStation(string clientID, string sapCode, bool isSave)
{
ServicePointManager.ServerCertificateValidationCallback = WebServUtil.ValidateServerCertificate;
QueryServiceStation.clientInfo clientInfo = new QueryServiceStation.clientInfo();
//clientInfo.clientID = GlobalStaticObj_Server.Instance.ClientID;
clientInfo.clientID = clientID;
clientInfo.serviceID = "queryServiceStation";
QueryServiceStation.portTypeService serv = new QueryServiceStation.portTypeService();
//string stationCode = Secret.Encrypt3DES_UTF8(GlobalStaticObj_YT.SAPCode, GlobalStaticObj_YT.KeySecurity_YT);
string stationCode = Secret.Encrypt3DES_UTF8(sapCode, GlobalStaticObj_YT.KeySecurity_YT);
string dateStr = Secret.Encrypt3DES_UTF8(GlobalStaticObj_Server.Instance.CurrentDateTime.ToString("yyyy-MM-dd HH:mm:ss"), GlobalStaticObj_YT.KeySecurity_YT);
string requestType = Secret.Encrypt3DES_UTF8("QUERY", GlobalStaticObj_YT.KeySecurity_YT);
clientInfo = WebServUtil.EncModel<QueryServiceStation.clientInfo>(clientInfo);
DateTime dtStart = GlobalStaticObj_Server.Instance.CurrentDateTime;//开始时间
string message = string.Empty;//错误消息
QueryServiceStation.Result result = new QueryServiceStation.Result();
try
{
result = serv.queryServiceStation(stationCode, dateStr, requestType, clientInfo);
}
catch (InvalidOperationException ioe)
{
//Utility.Log.Log.writeLineToLog(ioe, "服务站信息同步");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("服务站信息同步", ioe);
return "服务站接口调用超时！";
}
catch (Exception ex)
{
//Utility.Log.Log.writeLineToLog(ex, "服务站信息同步");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("服务站信息同步", ex);
return "服务站接口调用出错！";
}
string state = Secret.Decrypt3DES_UTF8(result.state, GlobalStaticObj_YT.KeySecurity_YT);
if (state == "F")
{
string errMsg = Secret.Decrypt3DES_UTF8(result.errorMsg, GlobalStaticObj_YT.KeySecurity_YT);
//Utility.Log.Log.writeLineToLog("【服务站信息同步】" + errMsg, "接口");
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("服务站信息同步", errMsg);
return "服务站接口调用失败！";
}
QueryServiceStation.station stt = result.station;
if (string.IsNullOrEmpty(stt.com_code))
{
return "";
}
if (!isSave)
{
//return stt.com_name;
return "";
}
stt = WebServUtil.DesModel<QueryServiceStation.station>(stt);
string nowTicks = Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString();
List<SysSQLString> list = new List<SysSQLString>();
SysSQLString sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
sysSQLString.Param = new Dictionary<string, string>();
StringBuilder strSql = new StringBuilder();
bool isServiceStationExist = DBHelper.IsExist("判断服务站信息是否存在", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, "tb_company", "com_code='" + result.station.com_code + "'");
if (isServiceStationExist)
{
#region 更新语句
strSql.Append("update tb_company set ");
strSql.Append(" parent_id= @parent_id , ");
strSql.Append(" com_code= @com_code , ");
strSql.Append(" com_name= @com_name , ");
strSql.Append(" com_short_name= @com_short_name , ");
strSql.Append(" province= @province , ");
strSql.Append(" city= @city , ");
strSql.Append(" county= @county , ");
strSql.Append(" category= @category , ");
strSql.Append(" zip_code= @zip_code , ");
strSql.Append(" com_email= @com_email , ");
strSql.Append(" com_phone= @com_phone , ");
strSql.Append(" com_fax= @com_fax , ");
strSql.Append(" repair_qualification= @repair_qualification , ");
strSql.Append(" unit_properties= @unit_properties , ");
strSql.Append(" legal_person= @legal_person , ");
strSql.Append(" indepen_check= @indepen_check , ");
strSql.Append(" indepen_legalperson= @indepen_legalperson , ");
strSql.Append(" financial_indepen= @financial_indepen , ");
strSql.Append(" tax_qualification= @tax_qualification , ");
strSql.Append(" tax_num= @tax_num , ");
strSql.Append(" work_time= @work_time , ");
strSql.Append(" staff_counts= @staff_counts , ");
strSql.Append(" ser_staff_counts= @ser_staff_counts , ");
strSql.Append(" trench_counts= @trench_counts , ");
strSql.Append(" reception_area= @reception_area , ");
strSql.Append(" cust_lounge_area= @cust_lounge_area , ");
strSql.Append(" meeting_room_area= @meeting_room_area , ");
strSql.Append(" training_room_area= @training_room_area , ");
strSql.Append(" repair_workshop_area= @repair_workshop_area , ");
strSql.Append(" big_repaired_area= @big_repaired_area , ");
strSql.Append(" parts_sales_area= @parts_sales_area , ");
strSql.Append(" parts_warehouse_area= @parts_warehouse_area , ");
strSql.Append(" ytparts_warehouse_area= @ytparts_warehouse_area , ");
strSql.Append(" oldparts_warehouse_area= @oldparts_warehouse_area , ");
strSql.Append(" repair_instructions= @repair_instructions , ");
strSql.Append(" enable_flag= @enable_flag , ");
strSql.Append(" apply_time= @apply_time , ");
strSql.Append(" approved_time= @approved_time , ");
strSql.Append(" com_level= @com_level , ");
strSql.Append(" workhours_price= @workhours_price , ");
strSql.Append(" winter_subsidy= @winter_subsidy , ");
strSql.Append(" territory= @territory , ");
strSql.Append(" street= @street , ");
strSql.Append(" institution_code= @institution_code , ");
strSql.Append(" service_phone= @service_phone , ");
strSql.Append(" star_level= @star_level , ");
strSql.Append(" sap_code= @sap_code , ");
strSql.Append(" center_library= @center_library , ");
strSql.Append(" data_source= @data_source , ");
strSql.Append(" status=@status,");
strSql.Append(" update_by= @update_by , ");
strSql.Append(" update_time= @update_time ");
strSql.Append(" where com_code=@com_code ");
#endregion
}
else
{
#region 插入语句
strSql.Append("insert into tb_company(");
strSql.Append("com_id,parent_id,com_code,com_name,com_short_name,province,city,county,category,zip_code,com_email,com_phone,com_fax,repair_qualification,unit_properties,legal_person,indepen_check,indepen_legalperson,financial_indepen,tax_qualification,tax_num,work_time,staff_counts,ser_staff_counts,trench_counts,reception_area,cust_lounge_area,meeting_room_area,training_room_area,repair_workshop_area,big_repaired_area,parts_sales_area,parts_warehouse_area,ytparts_warehouse_area,oldparts_warehouse_area,repair_instructions,enable_flag,apply_time,approved_time,com_level,workhours_price,winter_subsidy,territory,street,institution_code,service_phone,star_level,sap_code,center_library,data_source,create_by,create_time,update_by,update_time,status");
strSql.Append(") values (");
strSql.Append("@com_id,@parent_id,@com_code,@com_name,@com_short_name,@province,@city,@county,@category,@zip_code,@com_email,@com_phone,@com_fax,@repair_qualification,@unit_properties,@legal_person,@indepen_check,@indepen_legalperson,@financial_indepen,@tax_qualification,@tax_num,@work_time,@staff_counts,@ser_staff_counts,@trench_counts,@reception_area,@cust_lounge_area,@meeting_room_area,@training_room_area,@repair_workshop_area,@big_repaired_area,@parts_sales_area,@parts_warehouse_area,@ytparts_warehouse_area,@oldparts_warehouse_area,@repair_instructions,@enable_flag,@apply_time,@approved_time,@com_level,@workhours_price,@winter_subsidy,@territory,@street,@institution_code,@service_phone,@star_level,@sap_code,@center_library,@data_source,@create_by,@create_time,@update_by,@update_time,@status) ");
#endregion
object objServStatId = DBHelper.GetSingleValue("获取服务站id", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, "tb_signing_info", "sign_id", "", "");
if (objServStatId == null)
{
return "获取服务站ID失败！";
}
sysSQLString.Param.Add("com_id", objServStatId.ToString());
sysSQLString.Param.Add("create_by", GlobalStaticObj_Server.Instance.UserID);
sysSQLString.Param.Add("create_time", nowTicks);
}
#region 参数项 47
YuTongDic ytDic = new YuTongDic();
//sysSQLString.Param.Add("parent_id", result.station.parent);
sysSQLString.Param.Add("parent_id", "-1");
sysSQLString.Param.Add("com_code", result.station.com_code);
sysSQLString.Param.Add("com_name", result.station.com_name);
sysSQLString.Param.Add("com_short_name", result.station.com_short_name);
sysSQLString.Param.Add("province", result.station.province);
sysSQLString.Param.Add("city", result.station.city);
sysSQLString.Param.Add("county", result.station.county);
sysSQLString.Param.Add("category", result.station.category);
sysSQLString.Param.Add("zip_code", result.station.zip_code);
sysSQLString.Param.Add("com_email", result.station.com_email);
sysSQLString.Param.Add("com_phone", result.station.com_phone);
sysSQLString.Param.Add("com_fax", result.station.com_fax);
sysSQLString.Param.Add("repair_qualification", result.station.repair_qualification);
sysSQLString.Param.Add("unit_properties", result.station.unit_properties);
sysSQLString.Param.Add("legal_person", result.station.legal_person);
sysSQLString.Param.Add("indepen_check", result.station.indepen_check);
sysSQLString.Param.Add("indepen_legalperson", result.station.indepen_legalperson);
sysSQLString.Param.Add("financial_indepen", result.station.financial_indepen);
sysSQLString.Param.Add("tax_qualification", result.station.tax_qualification);
sysSQLString.Param.Add("tax_num", result.station.tax_num);
sysSQLString.Param.Add("work_time", result.station.work_time);
sysSQLString.Param.Add("staff_counts", string.IsNullOrEmpty(result.station.staff_counts) ? null : result.station.staff_counts);
sysSQLString.Param.Add("ser_staff_counts", string.IsNullOrEmpty(result.station.ser_staff_counts) ? null : result.station.ser_staff_counts);
sysSQLString.Param.Add("trench_counts", string.IsNullOrEmpty(result.station.trench_counts) ? null : result.station.trench_counts);
sysSQLString.Param.Add("reception_area", string.IsNullOrEmpty(result.station.reception_area) ? null : result.station.reception_area);
sysSQLString.Param.Add("cust_lounge_area", string.IsNullOrEmpty(result.station.cust_lounge_area) ? null : result.station.cust_lounge_area);
sysSQLString.Param.Add("meeting_room_area", string.IsNullOrEmpty(result.station.meeting_room_area) ? null : result.station.meeting_room_area);
sysSQLString.Param.Add("training_room_area", string.IsNullOrEmpty(result.station.training_room_area) ? null : result.station.training_room_area);
sysSQLString.Param.Add("repair_workshop_area", string.IsNullOrEmpty(result.station.repair_workshop_area) ? null : result.station.repair_workshop_area);
sysSQLString.Param.Add("big_repaired_area", string.IsNullOrEmpty(result.station.big_repaired_area) ? null : result.station.big_repaired_area);
sysSQLString.Param.Add("parts_sales_area", string.IsNullOrEmpty(result.station.parts_sales_area) ? null : result.station.parts_sales_area);
sysSQLString.Param.Add("parts_warehouse_area", string.IsNullOrEmpty(result.station.parts_warehouse_area) ? null : result.station.parts_warehouse_area);
sysSQLString.Param.Add("ytparts_warehouse_area", string.IsNullOrEmpty(result.station.ytparts_warehouse_area) ? null : result.station.ytparts_warehouse_area);
sysSQLString.Param.Add("oldparts_warehouse_area", string.IsNullOrEmpty(result.station.oldparts_warehouse_area) ? null : result.station.oldparts_warehouse_area);
sysSQLString.Param.Add("repair_instructions", result.station.repair_instructions);
sysSQLString.Param.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString());
sysSQLString.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString());//状态
string dtApplyTime = "";
if (!String.IsNullOrEmpty(result.station.apply_time))
{
dtApplyTime = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(result.station.apply_time)).ToString();
}
sysSQLString.Param.Add("apply_time", dtApplyTime);
string dtApprovedTime = "";
if (!String.IsNullOrEmpty(result.station.apply_time))
{
dtApprovedTime = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(result.station.approved_time)).ToString();
}
sysSQLString.Param.Add("approved_time", dtApprovedTime);
sysSQLString.Param.Add("com_level", result.station.com_level);
sysSQLString.Param.Add("workhours_price", string.IsNullOrEmpty(result.station.workhours_price) ? null : result.station.workhours_price);
sysSQLString.Param.Add("winter_subsidy", result.station.winter_subsidy);
sysSQLString.Param.Add("territory", result.station.territory);
sysSQLString.Param.Add("street", result.station.street);
sysSQLString.Param.Add("institution_code", result.station.institution_code);
sysSQLString.Param.Add("service_phone", result.station.service_phone);
sysSQLString.Param.Add("star_level", result.station.star_level);
sysSQLString.Param.Add("sap_code", result.station.sap_code);
if (!string.IsNullOrEmpty(result.station.center_library))
{
sysSQLString.Param.Add("center_library", WebServUtil.GetLocalDicID("center_library", result.station.center_library));
}
else
{
sysSQLString.Param.Add("center_library", WebServUtil.GetLocalDicID("center_library", result.station.sap_code));
}
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);
int updateCount = 0;
bool flag = DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("宇通：同步服务站信息", GlobalStaticObj_Server.CommAccCode, list);
if (!flag)
{
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("服务站信息同步", "更新服务站信息失败");
return "更新服务站信息失败！";
}
updateCount = list.Count;
DateTime dtEnd = GlobalStaticObj_Server.Instance.CurrentDateTime;//结束时间
int totalCount = int.Parse(DBHelper.GetSingleValue("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, "select count(1) from tb_company where data_source='2'"));
WebServUtil.WriteInterficeSync(DataSources.EnumInterfaceType.ServiceStation, DataSources.EnumExternalSys.YTCRM, totalCount, updateCount, GlobalStaticObj_Server.Instance.CurrentDateTime);
WebServUtil.WriteInterficeSyncLog(DataSources.EnumInterfaceType.ServiceStation, DataSources.EnumExternalSys.YTCRM, DataSources.EnumSyncDirection.DownLoad, "tb_company", dtStart, dtEnd, updateCount, "");
return "";
}
/// <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;
}
