public override void ExecuteProgramJob(SourceTaskItem sourceTask, LogTaskItem logTask) { string sqlStr = @" SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY deviceAddress ORDER BY dataTime DESC) AS RowNumber, *FROM ( SELECT * FROM baseData WHERE deviceAddress IN(SELECT deviceAddress FROM TransToType WHERE transTo = '12') ) AS A ) AS B WHERE RowNumber = 1 AND deviceAddress ='11056'"; //_log.InfoFormat(DateTime.Now + Environment.NewLine + "DustMonitoringTask sqlStr:{0}", sqlStr); DataTable dt = new SQLHelper(sourceTask.ConnectionString).ExecuteQuery(sqlStr, CommandType.Text); DateTime now = DateTime.Now; _log.InfoFormat(DateTime.Now + Environment.NewLine + "DustMonitoringTask sqlStr:{0}", dt.Rows.Count); List <BaseDataModel> dataList = new List <BaseDataModel>(); foreach (DataRow item in dt.Rows) { BaseDataModel baseDataModel = new BaseDataModel(); baseDataModel.ID = ConvertObject.ToInt32(item["ID"]); baseDataModel.deviceAddress = ConvertObject.ToString(item["deviceAddress"]);; baseDataModel.sensorCount = ConvertObject.ToString(item["sensorCount"], "0"); baseDataModel.PM25 = ConvertObject.ToString(item["PM25"], "0"); baseDataModel.PM10 = ConvertObject.ToString(item["PM10"], "0"); baseDataModel.noise = ConvertObject.ToString(item["noise"], "0"); baseDataModel.temperature = ConvertObject.ToString(item["temperature"], "0"); baseDataModel.humidity = ConvertObject.ToString(item["humidity"], "0"); baseDataModel.windSpeed = ConvertObject.ToString(item["windSpeed"], "0"); baseDataModel.windPower = ConvertObject.ToString(item["windPower"], "0"); baseDataModel.windDirection = ConvertObject.ToString(item["windDirection"], "0"); baseDataModel.dataTime = ConvertObject.ToDateTime(item["dataTime"]); baseDataModel.transTo = ConvertObject.ToString(item["transTo"], "0"); baseDataModel.transCode = ConvertObject.ToString(item["transCode"], "0"); //十分钟之内的数据才会统计 if (baseDataModel.dataTime >= now.AddMinutes(-10)) { dataList.Add(baseDataModel); } } foreach (BaseDataModel item in dataList) { DustMonitoringService dustMonitoringService = new DustMonitoringService(); dustMonitoringService.Regester(item); while (true) { if (dustMonitoringService._client.State == Cowboy.Sockets.TcpSocketConnectionState.Closed) { break; } Thread.Sleep(1000); } //sqlStr = string.Format("UPDATE baseData SET ISAsync='1' WHERE ID = '{0}'", baseDataModel.ID); //int result = new SQLHelper(sourceTask.ConnectionString).ExecuteNonQuery(sqlStr, CommandType.Text); //_log.InfoFormat("{0}{1}记录更新结果为:{2},{3}", DateTime.Now, Environment.NewLine, sqlStr, result); //Thread.Sleep(1000); } }
/// <summary> /// /// </summary> private void Excate(SourceTaskItem sourceTask) { string sqlStr = @" SELECT ISNULL(TransToType.longitude,'0') AS longitude, ISNULL(TransToType.latitude,'0') AS latitude, C.* FROM ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY deviceAddress ORDER BY dataTime DESC) AS RowNumber, *FROM ( SELECT * FROM baseData WHERE deviceAddress IN(SELECT deviceAddress FROM TransToType WHERE transTo1 = '11') ) AS A ) AS B WHERE RowNumber = 1 -- AND deviceAddress ='11004' ) AS C LEFT JOIN TransToType ON C.deviceAddress = TransToType.deviceAddress "; //_log.InfoFormat(DateTime.Now + Environment.NewLine + "DustMonitoringTask sqlStr:{0}", sqlStr); DataTable dt = new SQLHelper(sourceTask.ConnectionString).ExecuteQuery(sqlStr, CommandType.Text); DateTime now = DateTime.Now; _log.InfoFormat(DateTime.Now + Environment.NewLine + "DustMonitoringTask sqlStr:{0}", dt.Rows.Count); List <BaseDataModel> dataList = new List <BaseDataModel>(); foreach (DataRow item in dt.Rows) { BaseDataModel baseDataModel = new BaseDataModel(); baseDataModel.ID = ConvertObject.ToInt32(item["ID"]); baseDataModel.deviceAddress = ConvertObject.ToString(item["deviceAddress"]);; baseDataModel.sensorCount = ConvertObject.ToString(item["sensorCount"], "0"); baseDataModel.PM25 = ConvertObject.ToString(item["PM25"], "0"); baseDataModel.PM10 = ConvertObject.ToString(item["PM10"], "0"); baseDataModel.noise = ConvertObject.ToString(item["noise"], "0"); baseDataModel.temperature = ConvertObject.ToString(item["temperature"], "0"); baseDataModel.humidity = ConvertObject.ToString(item["humidity"], "0"); baseDataModel.windSpeed = ConvertObject.ToString(item["windSpeed"], "0"); baseDataModel.windPower = ConvertObject.ToString(item["windPower"], "0"); baseDataModel.windDirection = ConvertObject.ToString(item["windDirection"], "0"); baseDataModel.dataTime = ConvertObject.ToDateTime(item["dataTime"]); baseDataModel.transTo = ConvertObject.ToString(item["transTo"], "0"); baseDataModel.transCode = ConvertObject.ToString(item["transCode"], "0"); baseDataModel.longitude = ConvertObject.ToString(item["longitude"], "0"); baseDataModel.latitude = ConvertObject.ToString(item["latitude"], "0"); //十分钟之内的数据才会统计 if (baseDataModel.dataTime >= now.AddMinutes(-10)) { dataList.Add(baseDataModel); } } foreach (BaseDataModel item in dataList) { MonitoringPollutantsService monitoringPollutantsService = new MonitoringPollutantsService(); monitoringPollutantsService.UploadData(item); //sqlStr = string.Format("UPDATE baseData SET ISAsync='1' WHERE ID = '{0}'", baseDataModel.ID); //int result = new SQLHelper(sourceTask.ConnectionString).ExecuteNonQuery(sqlStr, CommandType.Text); //_log.InfoFormat("{0}{1}记录更新结果为:{2},{3}", DateTime.Now, Environment.NewLine, sqlStr, result); //Thread.Sleep(1000); } }
/// <summary> /// 执行 /// DevID:|:911000011#|#Time:|:2016-06-29 12:48:33#|#HUMI:|:27#|#TEMP:|:30#|#PRE:|:150#|#WINDD:|:200#|#WINDS:|:3#|#NOISE:|:120#|#PM25:|:45#|#PM10:|:100.00#|#TSP:|:300 /// </summary> private void Excate(SourceTaskItem sourceTask) { string sqlStr = "SELECT * FROM baseData WHERE ISAsync='0' OR ISAsync IS NULL"; _log.InfoFormat(DateTime.Now + Environment.NewLine + "UpLoadSaveYCJCTask sqlStr:{0}", sqlStr); List <BaseDataModel> baseDataModels = new List <Models.SaveYCJCService.BaseDataModel>(); DataTable dt = new SQLHelper(sourceTask.ConnectionString).ExecuteQuery(sqlStr, CommandType.Text); for (int i = 0; i < dt.Rows.Count; i++) { baseDataModels.Add(new BaseDataModel() { ID = ConvertObject.ToInt32(dt.Rows[i]["ID"]), deviceAddress = ConvertObject.ToString(dt.Rows[i]["deviceAddress"].ToString(), "-1"), sensorCount = ConvertObject.ToString(dt.Rows[i]["sensorCount"].ToString(), "-1"), PM25 = ConvertObject.ToString(dt.Rows[i]["PM25"].ToString(), "-1"), PM10 = ConvertObject.ToString(dt.Rows[i]["PM10"].ToString(), "-1"), noise = ConvertObject.ToString(dt.Rows[i]["noise"].ToString(), "-1"), temperature = ConvertObject.ToString(dt.Rows[i]["temperature"].ToString(), "-1"), humidity = ConvertObject.ToString(dt.Rows[i]["humidity"].ToString(), "-1"), windSpeed = ConvertObject.ToString(dt.Rows[i]["windSpeed"].ToString(), "-1"), windPower = ConvertObject.ToString(dt.Rows[i]["windPower"].ToString(), "-1"), windDirection = ConvertObject.ToString(dt.Rows[i]["windDirection"].ToString(), "-1"), dataTime = ConvertObject.ToDateTime(dt.Rows[i]["dataTime"].ToString()), transTo = ConvertObject.ToString(dt.Rows[i]["transTo"].ToString(), "-1"), transCode = ConvertObject.ToString(dt.Rows[i]["transCode"].ToString(), "-1") }); } foreach (BaseDataModel item in baseDataModels) { Dictionary <string, string> pairs = new Dictionary <string, string>(); //获取数据源 #region 参数 pairs.Add(YCJCService.DevID, item.deviceAddress); //deviceAddress pairs.Add(YCJCService.Time, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); pairs.Add(YCJCService.HUMI, item.humidity); //humidity pairs.Add(YCJCService.TEMP, item.temperature); //temperature pairs.Add(YCJCService.PRE, "-1"); pairs.Add(YCJCService.WINDD, item.windDirection); //windDirection pairs.Add(YCJCService.WINDS, item.windSpeed); //windSpeed pairs.Add(YCJCService.NOISE, item.noise); //noise pairs.Add(YCJCService.PM25, item.PM25); //PM25 pairs.Add(YCJCService.PM10, item.PM10); //PM10 pairs.Add(YCJCService.TSP, "-1"); #endregion string resultStr = service.SaveYCJC(pairs, "Service_JZ"); _log.InfoFormat("{0}{1}SaveYCJC 结果:{2}", DateTime.Now, Environment.NewLine, resultStr); ResultClass jsonObj = JsonConvert.DeserializeObject <ResultClass>(resultStr); if (string.IsNullOrEmpty(jsonObj.True)) { _log.InfoFormat("{0}{1}SaveYCJC 结果出现错误:{2}", DateTime.Now, Environment.NewLine, jsonObj.False); } else { sqlStr = string.Format("UPDATE baseData SET ISAsync='1' WHERE ID = '{0}'", item.ID); int result = new SQLHelper(sourceTask.ConnectionString).ExecuteNonQuery(sqlStr, CommandType.Text); _log.InfoFormat("{0}{1}记录更新结果为:{2},{3}", DateTime.Now, Environment.NewLine, sqlStr, result); } Thread.Sleep(30000); } }