public static List <MES_StationDataRecordDTO> ConvertMesDateToPis(Recordset mesRecode, string mesProjectName) { MES_SNOriginalService SNOriginalService = new MES_SNOriginalService ( new UnitOfWork(_DatabaseFactory), new MES_SNOriginalRepository(_DatabaseFactory), new Mes_StationColorRepository(_DatabaseFactory), new ShipMentRepository(_DatabaseFactory) ); EnumerationRepository enumration = new EnumerationRepository(_DatabaseFactory); //获取MES已出货,需要删除的站点 var mesDeleteProjectList = enumration.GetMesNeedDeleteStation("MES_DeleteProject", mesProjectName); // var allColorStationList = SNOriginalService.GetAllStationColor(); var allColorStationList = SNOriginalService.GetStationColorListByName(mesProjectName); List <MESResultModel> MESResultList = new List <MESResultModel>(); DataTable dataTable = new DataTable(); dataTable.Columns.Add("SeriesNumber", typeof(string)); dataTable.Columns.Add("CustomerName", typeof(string)); dataTable.Columns.Add("MES_ProcessID", typeof(int)); dataTable.Columns.Add("StationName", typeof(string)); dataTable.Columns.Add("Starttime", typeof(DateTime)); dataTable.Columns.Add("Color", typeof(string)); //获取前面的的数据测试 var startTime = DateTime.Now; Dictionary <string, string> dic = new Dictionary <string, string>(); if (false) { //测试代码 #region //var topDateList = SNOriginalService.GetTopDate(100); //foreach (var item in topDateList) //{ // MESResultModel MESResult = new MESResultModel(); // DataRow dataRow = dataTable.NewRow(); // MESResult.SeriesNumber = item.SeriesNumber; // MESResult.CustomerName = item.CustomerName; // MESResult.StationName = item.StationName; // MESResult.StartTime = item.Starttime; // MESResult.MES_ProcessID = "0"; // MESResult.MES_ProcessName = item.StationName; // dataRow["SeriesNumber"] = item.SeriesNumber; // dataRow["CustomerName"] = item.CustomerName; // dataRow["Starttime"] = item.Starttime; // dataRow["MES_ProcessID"] = "0"; // dataRow["StationName"] = item.StationName; // dataRow["Color"] = "0"; // var key = MESResult.SeriesNumber + MESResult.CustomerName + MESResult.StationName; // if (!dic.Keys.Contains(key)) // { // dic.Add(key, MESResult.SeriesNumber); // dataTable.Rows.Add(dataRow); // MESResultList.Add(MESResult); // } //} #endregion } else { #region while (!mesRecode.EOF) { MESResultModel MESResult = new MESResultModel(); DataRow dataRow = dataTable.NewRow(); if (mesRecode.Fields[1].Value.ToString().Length != 19) { mesRecode.MoveNext(); continue; } MESResult.SeriesNumber = mesRecode.Fields[1].Value.ToString(); MESResult.CustomerName = mesRecode.Fields[3].Value.ToString(); MESResult.StationName = mesRecode.Fields[17].Value.ToString(); MESResult.StartTime = Convert.ToDateTime(mesRecode.Fields[11].Value.ToString()); MESResult.MES_ProcessID = mesRecode.Fields[17].Value.ToString(); MESResult.MES_ProcessName = mesRecode.Fields[17].Value.ToString(); dataRow["SeriesNumber"] = mesRecode.Fields[1].Value.ToString(); dataRow["CustomerName"] = mesRecode.Fields[3].Value.ToString(); dataRow["Starttime"] = Convert.ToDateTime(mesRecode.Fields[11].Value.ToString()); dataRow["MES_ProcessID"] = mesRecode.Fields[17].Value.ToString(); dataRow["StationName"] = mesRecode.Fields[17].Value.ToString(); dataRow["Color"] = "0"; //判断MES的该10分钟的数据是不是有重复的 var key = MESResult.SeriesNumber + MESResult.CustomerName + MESResult.MES_ProcessID; if (!dic.Keys.Contains(key)) { dic.Add(key, MESResult.SeriesNumber); dataTable.Rows.Add(dataRow); MESResultList.Add(MESResult); } mesRecode.MoveNext(); } #endregion } mesRecode.Close(); //添加到删除的表 DataTable dataTableShipment = new DataTable(); dataTableShipment.Columns.Add("SeriesNumber", typeof(string)); // 1 够造需要删除数据 foreach (var deEnumModel in mesDeleteProjectList) { var deleteModel = MESResultList.Where(p => p.MES_ProcessID == deEnumModel.Enum_Value); StringBuilder sb = new StringBuilder(); foreach (var item in deleteModel) { DataRow dataShipMent = dataTableShipment.NewRow(); dataShipMent["SeriesNumber"] = item.SeriesNumber; dataTableShipment.Rows.Add(dataShipMent); } } SNOriginalService.AddShipMent(dataTableShipment); //2 判断历史是否有重复的数据 var repeatSNList = SNOriginalService.GetMES_SNOriginal(dataTable); Dictionary <string, string> dicRepeat = new Dictionary <string, string>(); foreach (var item in repeatSNList) { var key = item.SeriesNumber + item.CustomerName + item.StationName; if (!dicRepeat.Keys.Contains(key)) { dicRepeat.Add(key, item.SeriesNumber); } } //测试代码 //var endTime = DateTime.Now; //Console.WriteLine("执行时间毫秒: " + (endTime - startTime).TotalMilliseconds); //Console.WriteLine("执行时间秒: " + (endTime - startTime).TotalSeconds); //Console.WriteLine("执行时间分: " + (endTime - startTime).TotalMinutes); // 3 过滤需要数据 List <MES_StationDataRecordDTO> resultList = new List <MES_StationDataRecordDTO>();//返回结果集合 dataTable.Clear(); //构造颜色 DataTable dataTableColor = new DataTable(); dataTableColor.Columns.Add("SeriesNumber", typeof(string)); dataTableColor.Columns.Add("CustomerName", typeof(string)); dataTableColor.Columns.Add("MES_ProcessID", typeof(int)); dataTableColor.Columns.Add("StationName", typeof(string)); dataTableColor.Columns.Add("Starttime", typeof(DateTime)); dataTableColor.Columns.Add("Color", typeof(string)); foreach (var item in MESResultList) { var key = item.SeriesNumber + item.CustomerName + item.MES_ProcessID; if (!dicRepeat.Keys.Contains(key)) { MES_StationDataRecordDTO model = new MES_StationDataRecordDTO(); model.MES_ProcessID = item.MES_ProcessID; model.MES_ProcessName = item.MES_ProcessName; DataRow dataRow = dataTableColor.NewRow(); dataRow["SeriesNumber"] = item.SeriesNumber; dataRow["CustomerName"] = item.CustomerName; dataRow["Starttime"] = item.StartTime; dataRow["MES_ProcessID"] = item.MES_ProcessID; dataRow["StationName"] = item.StationName; DataRow dataOriRow = dataTable.NewRow(); dataOriRow["SeriesNumber"] = item.SeriesNumber; dataOriRow["CustomerName"] = item.CustomerName; dataOriRow["Starttime"] = item.StartTime; dataOriRow["MES_ProcessID"] = item.MES_ProcessID; dataOriRow["StationName"] = item.StationName; var temoStationColor = allColorStationList.Where(p => p.CustomerName == item.CustomerName && p.StationName == item.MES_ProcessID); if (temoStationColor != null && temoStationColor.Count() > 0) { //第一次经过工站的料品 var tempColor = temoStationColor.FirstOrDefault(); dataOriRow["Color"] = tempColor.Color; dataTable.Rows.Add(dataOriRow); model.Color = tempColor.Color; resultList.Add(model); } else { dataRow["Color"] = "0"; dataTableColor.Rows.Add(dataRow); } } } // 4将过滤后的批量数据插入 SNOriginalService.AddMES_SNOriginal(dataTable); //批量获取该10分钟的SN经过第一个站时的颜色 var count = dataTableColor.Rows.Count; var firStation = new StringBuilder(); foreach (var item in allColorStationList) { firStation.AppendFormat("\'{0}\'", item.StationName); firStation.Append(","); } var StationColorList = SNOriginalService.GetStationColor(dataTableColor, firStation.ToString().TrimEnd(',')); foreach (var item in StationColorList) { MES_StationDataRecordDTO model = new MES_StationDataRecordDTO(); model.MES_ProcessName = item.StationName; model.MES_ProcessID = item.MES_ProcessID.ToString(); model.Color = item.Color; resultList.Add(model); } SNOriginalService.AddMES_SNOriginal(dataTableColor); return(resultList); }
static void Main(string[] args) { //NewmMes_Service.SynchronizeMesInfo(syncParam); //mes_Service.SynchronizeMesInfo(syncParam); ProcessIDTRSConfigService process_Service = new ProcessIDTRSConfigService ( new UnitOfWork(_DatabaseFactory), new EnumerationRepository(_DatabaseFactory), new ProcessIDTRSConfigRepository(_DatabaseFactory), new ProductInputRepository(_DatabaseFactory), new MES_PIS_SyncFailedRecordRepository(_DatabaseFactory), new FlowChartDetailRepository(_DatabaseFactory) ); MesDataSyncService mes_Service = new MesDataSyncService ( new UnitOfWork(_DatabaseFactory), new EnumerationRepository(_DatabaseFactory), new MesDataSyncRepository(_DatabaseFactory), new ProductInputRepository(_DatabaseFactory), new ProcessIDTRSConfigRepository(_DatabaseFactory) ); LogMessageRecordService Log_Service = new LogMessageRecordService ( new UnitOfWork(_DatabaseFactory), new LogMessageRecodeRepository(_DatabaseFactory) ); MES_PIS_SyncFailedRecordService SyncFailedLogServer = new MES_PIS_SyncFailedRecordService ( new MES_PIS_SyncFailedRecordRepository(_DatabaseFactory), new UnitOfWork(_DatabaseFactory) ); MES_SNOriginalService SNOriginalService = new MES_SNOriginalService ( new UnitOfWork(_DatabaseFactory), new MES_SNOriginalRepository(_DatabaseFactory), new Mes_StationColorRepository(_DatabaseFactory), new ShipMentRepository(_DatabaseFactory) ); _LogService = Log_Service; _SyncFailedLogServer = SyncFailedLogServer; EnumerationRepository enumration = new EnumerationRepository(_DatabaseFactory); //一 获取枚举表-构造参数 //二 获取临时表的数据 //三 获取配置表的数据判断是否需要同步 //四 判断数据是插入还是更新 //1 获取当前日期和时段 //测试代码 //var starttime = DateTime.Now; if (false) { var i = 0; while (true) { Thread t1 = new Thread(new ThreadStart(AddData)); t1.Start(); Thread.Sleep(60000); Console.Write(++i + ", "); } } else { var startTime = string.Empty; var endTime = string.Empty; var current_Date = string.Empty; var current_TimeInterval = string.Empty; var _currentDateTime = DateTime.Now.AddMinutes(-10); //var _currentDateTime = Convert.ToDateTime("2018/09/28 10:10"); //while (_currentDateTime.AddMinutes(10) < Convert.ToDateTime("2018/09/28 15:10")) //{ // _currentDateTime = _currentDateTime.AddMinutes(10); var currentDateInfoModel = GetCurrentTime(_currentDateTime); current_TimeInterval = currentDateInfoModel.CurrentInterval; current_Date = currentDateInfoModel.CurrentDay; startTime = currentDateInfoModel.CurrentStartMinute; endTime = currentDateInfoModel.CurrentEndMinute; //获取需要同步的专案 var projectList = enumration.GetMES_Project("MES_ProjectType"); //2 获取配置表需要同步的制程 var ProcessConfList = process_Service.GetSyscProcessConfig(); foreach (var project in projectList) { #region List <MES_StationDataRecordDTO> MesPeocessInfoList = new List <MES_StationDataRecordDTO>(); var requestParam = project.Enum_Value.Split(','); var SqlServer = requestParam[0].ToString(); var Database = requestParam[1].ToString(); var UserID_ID = int.Parse(requestParam[2]); var customerIDString = requestParam[5].ToString(); var assemblyIDString = requestParam[6].ToString(); var customerNameString = requestParam[7].ToString(); //获取专案下面最新的一条数据 var LastDatemodel = mes_Service.GetLastSyncDate(customerIDString); var model = new MES_StationDataRecordDTO(); Recordset resultMesData = new Recordset(); //第一条数据直接滴啊用接口 if (LastDatemodel == null) { MesRequestParamModel mesRequest = new MesRequestParamModel() { SqlServer = SqlServer, Database = Database, UserID_ID = UserID_ID, StartDate = startTime, EndDate = endTime, CustomerIDString = customerIDString, AssemblyIDString = assemblyIDString }; resultMesData = GetMesAPIData(mesRequest); if (resultMesData.RecordCount == 0) { AddLog("MES-无数据数据", "RequestMESAPI", mesRequest); continue; } MesPeocessInfoList = ConvertMesDateToPis(resultMesData, customerNameString); AddMES_PIS(mes_Service, MesPeocessInfoList, ProcessConfList, currentDateInfoModel, mesRequest); } else { //当前同步的开始时间=已经同步的结束的时间 if (LastDatemodel.EndTimeInterval == startTime) { MesRequestParamModel mesRequest = new MesRequestParamModel() { SqlServer = SqlServer, Database = Database, UserID_ID = UserID_ID, StartDate = startTime, EndDate = endTime, CustomerIDString = customerIDString, AssemblyIDString = assemblyIDString }; resultMesData = GetMesAPIData(mesRequest); if (resultMesData.RecordCount == 0) { AddLog("MES-无数据数据", "RequestMESAPI", mesRequest); continue; } MesPeocessInfoList = ConvertMesDateToPis(resultMesData, customerNameString); AddMES_PIS(mes_Service, MesPeocessInfoList, ProcessConfList, currentDateInfoModel, mesRequest); } else { for (int i = 12; i >= 0; i--) { Recordset resultMesDataForFail = new Recordset(); //往前面推10分钟-最多查询前面一个小时数据 var tempTineModel = GetCurrentTime(_currentDateTime.AddMinutes(-(10 * i))); if (Convert.ToDateTime(LastDatemodel.EndTimeInterval) > Convert.ToDateTime(tempTineModel.CurrentStartMinute)) { continue; } else { current_Date = tempTineModel.CurrentDay; current_TimeInterval = tempTineModel.CurrentInterval; startTime = tempTineModel.CurrentStartMinute; endTime = tempTineModel.CurrentEndMinute; MesRequestParamModel mesRequest = new MesRequestParamModel() { SqlServer = SqlServer, Database = Database, UserID_ID = UserID_ID, StartDate = startTime, EndDate = endTime, CustomerIDString = customerIDString, AssemblyIDString = assemblyIDString }; resultMesDataForFail = GetMesAPIData(mesRequest); if (resultMesDataForFail.RecordCount == 0) { AddLog("MES-无数据数据", "RequestMESAPI", mesRequest); //记日志或者发邮件 continue; } MesPeocessInfoList = ConvertMesDateToPis(resultMesDataForFail, customerNameString); //AddMES_PIS(mes_Service, MesPeocessInfoList, ProcessConfList, tempTineModel, mesRequest); //找到相等时的时段执行完直接跳出 } //原逻辑 #region //if (LastDatemodel.EndTimeInterval == tempTineModel.CurrentStartMinute) //{ // current_Date = tempTineModel.CurrentDay; // current_TimeInterval = tempTineModel.CurrentInterval; // startTime = tempTineModel.CurrentStartMinute; // endTime = tempTineModel.CurrentEndMinute; // MesRequestParamModel mesRequest = new MesRequestParamModel() // { // SqlServer = SqlServer, // Database = Database, // UserID_ID = UserID_ID, // StartDate = startTime, // EndDate = endTime, // CustomerIDString = customerIDString, // AssemblyIDString = assemblyIDString // }; // resultMesDataForFail = GetMesAPIData(mesRequest); // if (resultMesDataForFail.RecordCount == 0) // { // AddLog("MES-无数据数据", "RequestMESAPI", mesRequest); // //记日志或者发邮件 // continue; // } // //MesPeocessInfoList = ConvertMesDateToPis(resultMesDataForFail); // //AddMES_PIS(mes_Service, MesPeocessInfoList, ProcessConfList, tempTineModel, mesRequest); // //找到相等时的时段执行完直接跳出 // break; //} //else //{ // current_Date = tempTineModel.CurrentDay; // current_TimeInterval = tempTineModel.CurrentInterval; // startTime = tempTineModel.CurrentStartMinute; // endTime = tempTineModel.CurrentEndMinute; // MesRequestParamModel mesRequest = new MesRequestParamModel() // { // SqlServer = SqlServer, // Database = Database, // UserID_ID = UserID_ID, // StartDate = startTime, // EndDate = endTime, // CustomerIDString = customerIDString, // AssemblyIDString = assemblyIDString // }; // resultMesDataForFail = GetMesAPIData(mesRequest); // if (resultMesDataForFail.RecordCount == 0) // { // AddLog("MES-无数据数据", "RequestMESAPI", mesRequest); // //记日志或者发邮件 // continue; // } // //MesPeocessInfoList = ConvertMesDateToPis(resultMesDataForFail); // //AddMES_PIS(mes_Service, MesPeocessInfoList, ProcessConfList, tempTineModel, mesRequest); //} #endregion //} } #endregion } } //测试代码 //var endtime = DateTime.Now; //Console.WriteLine("总耗时毫秒:" + (endtime - starttime).TotalMilliseconds); //Console.WriteLine("总耗时秒:" + (endtime - starttime).Milliseconds); //Console.WriteLine("总耗时分:" + (endtime - starttime).TotalMinutes); //Console.ReadKey(); } } }