/// <summary>
///
/// </summary>
private void Excate(SourceTaskItem sourceTask)
{
try
{
string sqlStr = "SELECT * FROM Users WHERE ISAsync='0'";
_log.InfoFormat(DateTime.Now + Environment.NewLine + "WorkerInfoSyscTask sqlStr:{0}", sqlStr);
DataTable dt = new SQLHelper(sourceTask.ConnectionString).ExecuteQuery(sqlStr, CommandType.Text);
//DataTable dt = Query.ExecuteSQLQuery(sqlStr, SystemEnvironment.Instance.DefaultDataSource);
List <WorkerInfo> WorkerInfos = dt.AsEnumerable().Select(x => new WorkerInfo()
{
name = ConvertObject.ToString(x["Names"]),
idCardNum = ConvertObject.ToString(x["ICNum"]),
gender = ConvertObject.ToInt32(x["Sex"]),
//constructionPermitNum = "410185201903070501",
constructionPermitNum = "SGXKZZZ",
bankCardNum = ConvertObject.ToString(x["CardNo"]),
attenNum = ConvertObject.ToString(x["No"]),
nation = ConvertObject.ToString(x["Nation"]),
headPicBase64 = PictureTools.GetBase64String(ConvertObject.ToString(x["Pic"])),//Pic
nativePlace = "",
phone = ConvertObject.ToString(x["Phone"]),
workState = 1,
entranceTime = ConvertObject.ToString(x["StartDate"]),
}).ToList();
//工人信息上传
if (WorkerInfos.Count > 0)
{
string result = new HttpMethod().SendWorkerInfo(WorkerInfos);
_log.InfoFormat("WorkerInfoSyscTask result:{0}", result);
sqlStr = string.Format("UPDATE Users SET ISAsync = '1' WHERE ISAsync = '0'");
int resultint = new SQLHelper(sourceTask.ConnectionString).ExecuteNonQuery(sqlStr, CommandType.Text);
_log.InfoFormat("WorkerInfoSyscTask resultint:{0}", resultint);
}
}
catch (Exception ex)
{
_log.InfoFormat(DateTime.Now + Environment.NewLine + "WorkerInfoSyscTask 发生异常:{0}", ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public SqlResult ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlResult result = null;
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打开数据库连接
result.status = true;
result.context = ConvertObject.ToString(command.ExecuteScalar());
}
}
return(result);//返回查询结果
}
/// <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);
}
}
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>
/// 执行
/// 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);
}
}
static RequestHelper()
{
HostUrl = ConvertObject.ToString(ConfigurationManager.AppSettings["HostUrl"]);
UserName = ConvertObject.ToString(ConfigurationManager.AppSettings["UserName"]);
Password = ConvertObject.ToString(ConfigurationManager.AppSettings["Password"]);
}