/// <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);
}
}
public void Execute(IJobExecutionContext context)
{
dataMap = context.JobDetail.JobDataMap;
//database program
string jobtype = dataMap.Get("JOBTYPE").ToString();
SourceTaskItem sourceTask = (SourceTaskItem)dataMap.Get("SOURCETASK");
TargetTaskItem targetTask = (TargetTaskItem)dataMap.Get("TARGETTASK");
LogTaskItem logTask = (LogTaskItem)dataMap.Get("LOGTASK");
if (jobtype == "program")
{
ExecuteProgramJob(sourceTask, logTask);
}
else if (jobtype == "database")
{
ExecuteDataJob(sourceTask, targetTask, logTask);
}
}
/// <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)
{
//base.ExecuteProgramJob(sourceTask, logTask);
_log.InfoFormat(DateTime.Now + Environment.NewLine + "MonitoringPollutantsTask 开始执行");
Excate(sourceTask);
}
public virtual void ExecuteProgramJob(SourceTaskItem sourceTask, LogTaskItem logTask)
{
}
public virtual void ExecuteDataJob(SourceTaskItem sourceTask, TargetTaskItem targetTask, LogTaskItem logTask)
{
}
public override void ExecuteProgramJob(SourceTaskItem sourceTask, LogTaskItem logTask)
{
_log.InfoFormat(DateTime.Now + Environment.NewLine + "TBJDataUploadTask 开始执行");
Excate(sourceTask);
}
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);
}
}
/// <summary>
///
/// </summary>
private void Excate(SourceTaskItem sourceTask)
{
try
{
//string sqlStr = @"
// SELECT DISTINCT ISNULL(Department.ProjectName,'') AS ProjectName,
// ISNULL(Department.ConstructionPermitNum,'') AS ConstructionPermitNum,
// ISNULL(Users.Names,'') AS Names,
// ISNULL(Users.ICNum,'') AS ICNum,
// ISNULL(Record.Types,'') AS Types,
// ISNULL(CONVERT(varchar(100), Record.OpenTime, 120),'') AS attentime,
// ISNULL(Record.Pic,'') AS photo,
// ISNULL(Record.OpenID,'') AS attcode,
// ISNULL(Record.RecordID,'') AS RecordID
// FROM Record
// INNER JOIN Users ON Record.OpenID = Users.OpenID
// INNER JOIN Department ON Users.DepartmentID = Users.DepartmentID
// AND Department.DepartmentID > 0
// WHERE
// Record.ISAsync = '0' OR Record.ISAsync IS NULL
// ";
string sqlStr = @"
SELECT DISTINCT ISNULL('绿地熹和府项目','') AS ProjectName,
ISNULL('登建施字第410185201903070501号','') AS ConstructionPermitNum,
ISNULL(Users.Names,'') AS Names,
ISNULL(Users.ICNum,'') AS ICNum,
ISNULL(Record.Types,'') AS Types,
ISNULL(CONVERT(varchar(100), Record.OpenTime, 120),'') AS attentime,
ISNULL(Record.Pic,'') AS photo,
ISNULL(Record.OpenID,'') AS attcode,
ISNULL(Record.RecordID,'') AS RecordID
FROM Record
INNER JOIN Users ON Record.OpenID = Users.OpenID
INNER JOIN Department ON Users.DepartmentID = Users.DepartmentID
AND Department.DepartmentID > 0 and len(Users.ICNum) > 5
WHERE
Record.ISAsync = '0' OR Record.ISAsync IS NULL
order by RecordID
";
_log.InfoFormat(DateTime.Now + Environment.NewLine + "AttendanceInfoSyscTask sqlStr:{0}", sqlStr);
//List<AttendanceItem> attendanceList = Query.ExecuteSQLQuery(sqlStr, SystemEnvironment.Instance.DefaultDataSource).ToList<AttendanceItem>();
List <AttendanceItem> attendanceList = new SQLHelper(sourceTask.ConnectionString).ExecuteQuery(sqlStr, CommandType.Text).ToList <AttendanceItem>();
List <AttendanceInfo> workerInfos = attendanceList.GroupBy(x => new { ProjectName = x.ProjectName, ConstructionPermitNum = x.ConstructionPermitNum })
.Select(_item => new AttendanceInfo()
{
projectName = _item.Key.ProjectName,
//constructionPermitNum = _item.Key.ConstructionPermitNum,
constructionPermitNum = "SGXKZZZ",
WorkerAttenInfos = _item.Select(_x => new WorkerAttenInfo()
{
workerName = _x.Names, //必填
idCardNum = string.IsNullOrEmpty(_x.ICNum) ? "410222100909098998" : "", //必填
inoutType = (_x.Types == "2") ? 0 : 1, //必填
attenTime = _x.attentime, //必填
photoBase64 = "",
attenCode = _x.attcode, //必填
recordID = _x.RecordID //必填
}).ToList()
}).ToList();
//工人考勤数据上传
foreach (AttendanceInfo info in workerInfos)
{
string result = new HttpMethod().SendAttendanceInfo(info, _log, sourceTask.ConnectionString);
_log.InfoFormat("AttendanceInfoSyscTask result:{0}", result);
}
}
catch (Exception ex)
{
_log.InfoFormat(DateTime.Now + Environment.NewLine + "AttendanceInfoSyscTask 发生异常:{0}", ex.Message);
}
}