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