/// <summary> 对引用的前置单据的状态进行更新的方法 /// </summary> /// <param name="list_order"></param> void ImportPurchasePlanStatus(List<OrderImportStatus> list_order, List<OrderFinishInfo> list_orderinfo) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; List<SysSQLString> listSql = new List<SysSQLString>(); Dictionary<string, string> dic = new Dictionary<string, string>(); List<string> list_plan = new List<string>(); string plan_ids = string.Empty; #region 更新前置单据的导入状态字段 foreach (OrderImportStatus item in list_order) { if (item.importtype == "采购计划单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan set import_status=@import_status where order_num=@order_num;"; dic.Add("import_status", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order set is_occupy=@is_occupy where order_num=@order_num;"; dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } #endregion #region 更新前置单据中的各个配件的已完成数量 foreach (OrderFinishInfo item in list_orderinfo) { if (!list_plan.Contains(item.plan_id)) { list_plan.Add(item.plan_id); plan_ids = plan_ids + "'" + item.plan_id + "',"; } if (item.importtype == "采购计划单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan_p set finish_counts=@finish_counts where plan_id=@plan_id and parts_code=@parts_code;"; dic.Add("finish_counts", item.finish_num); dic.Add("plan_id", item.plan_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order_p set finish_count=@finish_count where sale_order_id=@sale_order_id and parts_code=@parts_code;"; dic.Add("finish_count", item.finish_num); dic.Add("sale_order_id", item.plan_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } #endregion bool ret = DBHelper.BatchExeSQLStringMultiByTrans("提交宇通采购订单,更新引用的采购计划单或销售订单的导入状态", listSql); if (ret) { if (list_orderinfo.Count > 0) { listSql.Clear(); plan_ids = plan_ids.Trim(','); string TableName = string.Format(@" ( select plan_id,sum(finish_counts) finish_counts, sum(finish_counts*business_price) as finish_money from tb_parts_purchase_plan_p where plan_id in ({0}) group by plan_id ) tb_purchase_finish", plan_ids); DataTable dt = DBHelper.GetTable("查询采购计划单各配件完成数量和完成金额", TableName, "*", "", "", ""); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan set finish_counts=@finish_counts,plan_finish_money=@plan_finish_money where plan_id=@plan_id;"; dic.Add("finish_counts", dt.Rows[i]["finish_counts"].ToString()); dic.Add("plan_finish_money", dt.Rows[i]["finish_money"].ToString()); dic.Add("plan_id", dt.Rows[i]["plan_id"].ToString()); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } DBHelper.BatchExeSQLStringMultiByTrans("完成宇通采购订单后,更新采购计划单的完成数量和完成金额", listSql); } } } }
private void adjustTable(DataTable dt, ref string exception) { //给table添加prbComment栏 dt.Columns.Add("PrbComment"); dt.Columns.Add("PrbPassed"); List<PostResponseBook> posts = new List<PostResponseBook>(); foreach (DataRow dr in dt.Rows) { posts.Clear(); //0:已提交 1:已审核 2:已保存 if (PostResponseBookBLL.Select(dr["UiID"].ToString(), ref posts, ref exception)) { dr["PrbComment"] = posts[0].PrbComment; if (posts[0].PrbPassed.ToString() == "0") { dr["PrbPassed"] = "已提交"; } else if (posts[0].PrbPassed.ToString() == "1") { dr["PrbPassed"] = "已审核"; } else { dr["PrbPassed"] = "已保存"; } } else { dr["PrbComment"] = ""; dr["PrbPassed"] = "未制作"; } } }
private void BuildRealPath() { int horLength = map.GetLength(0); int vertLength = map.GetLength(1); Point currentPosition = Point.Empty; Point destination = Point.Empty; for (int i = 0; i < horLength; i++) { for (int j = 0; j < vertLength; j++) { if (this.mapWithIdealPath[i, j] == MyRoadType.CurrentPosition) { currentPosition = new Point(i, j); } if (this.mapWithIdealPath[i, j] == MyRoadType.Destination) { destination = new Point(i, j); } } } HashSet<Point> usedPoints = new HashSet<Point>(); List<Point> currentLevel = new List<Point> { destination }; List<Point> nextLevel = new List<Point>(); Action<Point> addIfIdeal = p => { if (p.X >= 0 && p.X < horLength && p.Y >= 0 && p.Y < vertLength && this.mapWithIdealPath[p.X, p.Y] == MyRoadType.IdealPath) { nextLevel.Add(p); } }; while (true) { foreach (var point in currentLevel) { if (!usedPoints.Contains(point)) { if (CanDriveFromTo(currentPosition, point)) { this.nextPoint = new PointF((point.X + 0.5f) * MyTileSize, (point.Y + 0.5f) * MyTileSize); return; } usedPoints.Add(point); addIfIdeal(new Point(point.X - 1, point.Y - 1)); addIfIdeal(new Point(point.X, point.Y - 1)); addIfIdeal(new Point(point.X + 1, point.Y - 1)); addIfIdeal(new Point(point.X - 1, point.Y)); addIfIdeal(new Point(point.X + 1, point.Y)); addIfIdeal(new Point(point.X - 1, point.Y + 1)); addIfIdeal(new Point(point.X, point.Y + 1)); addIfIdeal(new Point(point.X + 1, point.Y + 1)); } } if (nextLevel.Count == 0) { break; } currentLevel.Clear(); currentLevel.AddRange(nextLevel); nextLevel.Clear(); } // TODO: We should not be here but we are. Fix this!!! this.nextPoint = new PointF((destination.X + 0.5f) * MyTileSize, (destination.Y + 0.5f) * MyTileSize); }
/// <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="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> /// <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="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; }
private static void ParseEmailData(long emailId, long ruleId) { // AddLog("ParseEmailData"); IRepository<EmailModels> emailRepository = new Repository<EmailModels>(); var email = emailRepository.Find(w => w.Id == emailId); if (email != null) { int row = -1; List<int> rows = new List<int>(); IRepository<EmailParsingRulesModels> emailParsingRulesRepository = new Repository<EmailParsingRulesModels>(); var emailParsingRule = emailParsingRulesRepository.Find(w => w.Id == ruleId); if (emailParsingRule != null) { // AddLog("emailParsingRule != null"); List<Models.TablesList> tablesList = new List<Models.TablesList>(); IRepository<Model.SaveEmailProperty> saveEmailPropertiesRepository = new Repository<Model.SaveEmailProperty>(); var allDefaultValues = saveEmailPropertiesRepository.FindAll(i => i.EmailParsingRulesModelsId == emailParsingRule.Id).ToList(); IRepository<Model.TableModels> tablesRepository = new Repository<TableModels>(); IRepository<EmailDataParsingRulesModels> emailDataParsingRulesRepository = new Repository<EmailDataParsingRulesModels>(); var emailDataParsingRules = emailDataParsingRulesRepository.FindAll( w => w.EmailParsingRulesModelsId_Id == emailParsingRule.Id && w.Status.Equals(Model.Enums.RuleStatusEnums.Active.ToString())).OrderBy(r => r.Name).ToList(); if (emailDataParsingRules != null && emailDataParsingRules.Count != 0) { // AddLog("emailDataParsingRules.Count != 0"); foreach (var dataParsingRule in emailDataParsingRules) { if (tablesList.Find(w => w.TableId == dataParsingRule.TableId_Id && w.IsChecked) == null && dataParsingRule.Status != Model.Enums.RuleStatusEnums.Paused.ToString()) { var name = dataParsingRule.TableId_Id.ToString(); var path = SpreadsheetHelper.GetSpreadsheetPathById((long)dataParsingRule.TableId_Id); try { SpreadsheetExtension.LoadDocument(name, path); IWorkbook workbook = SpreadsheetExtension.GetCurrentDocument(name); Worksheet worksheet = workbook.Worksheets[ emailParsingRule.EmailDataParsingRulesModels.FirstOrDefault().Worksheet]; if (emailParsingRule.IsUpdatingRule) { var changing = emailParsingRule.EmailDataParsingRulesModels.FirstOrDefault( w => w.IsUpdatindIndex && w.EmailParsingRulesModelsId_Id == emailParsingRule.Id); var changingIndex = BeforeParseValueFromEmail(email, changing); if (path != null) { foreach (var c in worksheet.Columns[dataParsingRule.Column].ExistingCells) { var value = c.Value.ToString(); if (value.Equals(changingIndex)) { row = c.RowIndex; rows.Add(row); } } byte[] size = workbook.SaveDocument(DocumentFormat.Xlsx); tablesRepository.Find(t => t.Id == dataParsingRule.TableId_Id).Size = size.Length; tablesRepository.Commit(); workbook.SaveDocument(path); SpreadsheetExtension.CloseDocument(name); } } else { worksheet.Rows.Insert(1); row = 1; } if (row != -1) { var author_Id = emailParsingRule.AuthorId_Id; foreach (var defaultValue in allDefaultValues) { if (emailParsingRule.Status != Model.Enums.RuleStatusEnums.Paused.ToString()) { InsertEmailLink(emailId, defaultValue, row, author_Id, emailParsingRule.CopyRuleFromPreviousOrder); } } foreach ( var dataParsing in emailParsingRule.EmailDataParsingRulesModels.Where( w => w.TableId_Id == dataParsingRule.TableId_Id)) { if (dataParsing.Status != Model.Enums.RuleStatusEnums.Paused.ToString()) { if (emailParsingRule.IsUpdatingRule) { foreach (var rowForUpdate in rows) { ParseCellValue(email, dataParsing, rowForUpdate, emailParsingRule.CopyRuleFromPreviousOrder); } rows.Clear(); } else { ParseCellValue(email, dataParsing, row, emailParsingRule.CopyRuleFromPreviousOrder); } } } tablesList.Add(new TablesList { IsChecked = true, TableId = (long)dataParsingRule.TableId_Id }); } } catch { AddLog("Table not found"); } } } } else if (allDefaultValues != null) { var tablesInallDefaultValues = allDefaultValues.GroupBy(a => a.TableId).Select(g => g.First()); foreach (var defaultValue in tablesInallDefaultValues) { try { var name = tablesRepository.Find(w => w.Name == defaultValue.TableId && w.AuthorId_Id == emailParsingRule.AuthorId_Id).Name; var path = SpreadsheetHelper.GetSpreadsheetPathById(tablesRepository.Find(w => w.Name == defaultValue.TableId && w.AuthorId_Id == emailParsingRule.AuthorId_Id).Id); SpreadsheetExtension.LoadDocument(name, path); IWorkbook workbook = SpreadsheetExtension.GetCurrentDocument(name); Worksheet worksheet = workbook.Worksheets[ Convert.ToInt32(defaultValue.Worksheet)]; worksheet.Rows.Insert(1); row = 1; byte[] size = workbook.SaveDocument(DocumentFormat.Xlsx); tablesRepository.Find(w => w.Name == defaultValue.TableId && w.AuthorId_Id == emailParsingRule.AuthorId_Id).Size = size.Length; tablesRepository.Commit(); workbook.SaveDocument(path); SpreadsheetExtension.CloseDocument(name); } catch (Exception ex) { AddLog(ex.Message); } } foreach (var defaultValue in allDefaultValues) { InsertEmailLink(emailId, defaultValue, row, emailParsingRule.AuthorId_Id, emailParsingRule.CopyRuleFromPreviousOrder); } } } } }
/// <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; }
//向table中加入被考评人信息 private void adjustTable(DataTable dt, ref int num,ref string exception) { dt.Columns.Add("EvaluatedName"); dt.Columns.Add("EvaluatedDep"); dt.Columns.Add("EvaluatedUnit"); dt.Columns.Add("EvaluatedStartTime"); dt.Columns.Add("EvaluatedEndTime"); dt.Columns.Add("EvaluatedJob"); dt.Columns.Add("EvaluationStatus"); List<UserInfo> ui = new List<UserInfo>(); num = 0; foreach (DataRow dr in dt.Rows) { ui.Clear(); UserInfoBLL.Select(ref ui, dr["EvaluatedID"].ToString(), ref exception); dr["EvaluatedName"] = ui[0].UiName; dr["EvaluatedDep"] = ui[0].UiDepartment; dr["EvaluatedUnit"] = ui[0].UiCompany; dr["EvaluatedStartTime"] = ui[0].UiStartTime; dr["EvaluatedEndTime"] = ui[0].UiStopTime; dr["EvaluatedJob"] = ui[0].UiJob; // if (dr["Status"].ToString().Equals("1")) { dr["EvaluationStatus"] = "已考评"; num++; } else { dr["EvaluationStatus"] = "未考评"; } } }
private void adjustTable1(DataTable dt, ref string exception) { dt.Columns.Add("Comment"); dt.Columns.Add("Passed"); List<Evaluator> evaluators = new List<Evaluator>(); string comment = ""; foreach (DataRow dr in dt.Rows) { if (EvaluatorCommentBLL.SelectComment((string)dr["UiID"], ref comment, ref exception)) { dr["Comment"] = comment; } else { dr["Comment"] = ""; } evaluators.Clear(); if (EvaluatorBLL.SelectByID(evaluators, (string)dr["UiID"], ref exception)) { //0:已提交 1:已审核 dr["Passed"] = "未审核"; foreach (Evaluator e in evaluators) { if (e.Pass.ToString() == "1") { dr["Passed"] = "已审核"; break; } } //if (evaluators[0].Pass.ToString() == "1") //{ // dr["Passed"] = "已审核"; //} //else if (evaluators[0].Pass.ToString() == "0") //{ // dr["Passed"] = "未审核"; //} } else { dr["Passed"] = "未制作"; } } }
private void adjustTable(DataTable dt, ref string exception) { dt.Columns.Add("EvaluatedName"); dt.Columns.Add("EvaluatorName"); dt.Columns.Add("EvaluatorUnit"); List<UserInfo> ui = new List<UserInfo>(); List<EvaluatorInfo> evi = new List<EvaluatorInfo>(); foreach (DataRow dr in dt.Rows) { evi.Clear(); EvaluatorInfoBLL.Select(evi, dr["UiID"].ToString(), ref exception); dr["EvaluatorName"] = evi[0].EvName; dr["EvaluatorUnit"] = evi[0].EvUnit; ui.Clear(); UserInfoBLL.Select(ref ui, dr["EvaluatedID"].ToString(), ref exception); dr["EvaluatedName"] = ui[0].UiName; } }
public static void DrawOperation(OpenGL gl, Project testProject, Vector rotation, List<List<double[]>> detailCache) { var bill = testProject.Settings; gl.Translate(bill.Length / 2, bill.Height / 2, bill.Width / 2);//высчитывается из размера заготовки gl.Rotate((float)rotation.Y, (float)rotation.X, 0); // вращение с зажатой средней кнопкой мыши gl.Translate(-bill.Length / 2, -bill.Height / 2, -bill.Width / 2); //var Bill = this.testProject.Settings; Billet.Draw(gl, bill.Height, bill.Length, bill.Width); // заготовка var operations = testProject.Operations;//колличество операций var boltReg = new Regex("BoltHole"); var pocketReg = new Regex("Pocket"); if (detailCache.Count != operations.Count) detailCache.Clear(); for (var i = 0; i < operations.Count; i++)//главный цикл отрисовки { var shapeName = operations[i].Shape.Name; if (boltReg.IsMatch(shapeName)) { var bolt = (Model.Primitives.BoltHole)operations[i].Shape; var boltlocation = operations[i].Location.LocationsList.GetEnumerator(); while (boltlocation.MoveNext()) { if (bolt.Modified || boltlocation.Current.Modified || detailCache.Count<=i) { try { detailCache.RemoveAt(i); } catch{} var location = new Point(boltlocation.Current.X, boltlocation.Current.Y); detailCache.Insert(i, BoltHole.ReCalc(bolt, 0.5, location)); //здесь уже всё ок, кроме величины шага boltlocation.Current.IsDrawn(); bolt.IsDrawn();//значит в кэше лежит актуальная информация } else { var location = new Point(boltlocation.Current.X, boltlocation.Current.Y); BoltHole.Draw(gl, detailCache[i]); //здесь уже всё ок, кроме величины шага } } } if (!pocketReg.IsMatch(shapeName)) continue; var poc = (Model.Primitives.Pocket)operations[i].Shape; var poclocation = operations[i].Location.LocationsList.GetEnumerator(); while (poclocation.MoveNext()) { if (poc.Modified || poclocation.Current.Modified || detailCache.Count <= i) { try { detailCache.RemoveAt(i); } catch { } var location = new Point(poclocation.Current.X, poclocation.Current.Y); var p = Pocket.ReCalc(poc, 0.5, location); detailCache.Insert(i, p); //здесь уже всё ок, кроме величины шага poclocation.Current.IsDrawn(); poc.IsDrawn();//значит в кэше лежит актуальная информация } else { var location = new Point(poclocation.Current.X, poclocation.Current.Y); Pocket.Draw(gl, detailCache[i]); //здесь уже всё ок, кроме величины шага } } } //отрисовщик траекторий gl.Begin(OpenGL.GL_LINE_STRIP); gl.Color(1f, 0, 0); var trajectorys = GCodeGenerator.TrajectoryStor.GetTrajectorys(); foreach (var point in trajectorys.SelectMany(operation => operation)) { gl.Vertex(point.GetCoordinates()); } }
//generate data for iteration 1 public void createIteration1PatientsAndVisits(int numberOfPatients) { Random random = new Random(); List<Patient> patients = new List<Patient>(); List<Patient> globallySimilarPatients = new List<Patient>(); List<Patient> globallyNonSimilarPatients = new List<Patient>(); List<PatientMatchNode> patientMatchNodes = new List<PatientMatchNode>(); float globallySimilarPatientsThreshold = 0.1f; float globallyNonSimilarPatientsThreshold = 0.1f; int requiredNumberOfGloballySimilarPatients = (int)((globallySimilarPatientsThreshold * numberOfPatients) + 0.5f); int requiredNumberOfGloballyNonSimilarPatients = (int)((globallyNonSimilarPatientsThreshold * numberOfPatients) + 0.5f); int currentNumberOfGloballySimilarPatients = 0; int currentNumberOfGloballyNonSimilarPatients = 0; DateTime maxRangeStart = new DateTime(2011, 01, 01); DateTime maxRangeEnd = new DateTime(2011, 12, 31); DateTime rangeStart = Util.getRandomDate(maxRangeStart, maxRangeEnd.AddMonths(-1)); DateTime rangeEnd = rangeStart.AddDays(15); int randRationalMin = 1; int randRationalMax = 5; DbMethods.getInstance().clearTables(); for (int i = 0; i < numberOfPatients; i++) { int patientID = i; DbMethods.getInstance().InsertPatient(); Patient patient = new Patient(patientID); PatientMatchNode patientNode = new PatientMatchNode(patient); int numberOfVisits = random.Next(10, 20); bool restart = true; do { patient.ClearVisits(); //generate directed visits for (int j = 0; j < numberOfVisits; j++) { Visit v = new Visit(-1, random.Next(1, 100), random.Next(randRationalMin, randRationalMax), Util.getRandomDate(rangeStart, rangeEnd)); if (!patient.HasVisit(v)) { patient.AddVisit(v); } else { j--; } } if (requiredNumberOfGloballySimilarPatients > 0) { //SIMILAR PATIENTS foreach (PatientMatchNode n in patientMatchNodes) { if (n.Patient.HasSimilarVisits(patient, 0.5f)) { n.addMatch(patient); restart = false; } } List<PatientMatchNode> nodesToRemove = new List<PatientMatchNode>(); foreach (PatientMatchNode n in patientMatchNodes) { if (n.getMatchesNumber() >= 4) { requiredNumberOfGloballySimilarPatients--; globallySimilarPatients.Add(n.Patient); nodesToRemove.Add(n); } } foreach (PatientMatchNode n in nodesToRemove) { patientMatchNodes.Remove(n); } nodesToRemove.Clear(); if(patientMatchNodes.Count < requiredNumberOfGloballySimilarPatients) { patientMatchNodes.Add(patientNode); restart = false; } } else if (requiredNumberOfGloballyNonSimilarPatients > 0) { //NON-SIMILAR PATIENTS rangeStart = maxRangeStart; rangeEnd = maxRangeEnd; bool hasSimilar = false; foreach (Patient p in patients) { if (!patient.HasNoSimilarVisits(p)) { hasSimilar = true; } } if (!hasSimilar) { restart = false; requiredNumberOfGloballyNonSimilarPatients--; globallyNonSimilarPatients.Add(patient); } } else { bool hasSimilar = false; foreach (Patient p in globallyNonSimilarPatients) { if (!patient.HasNoSimilarVisits(p)) { hasSimilar = true; } } if (!hasSimilar) { restart = false; } } } while(restart); //adding the visits foreach (Visit v in patient.Visits) { DbMethods.getInstance().InsertVisit(patientID, v.ProfessionalID, v.RationalID, v.Date); } patients.Add(patient); } }