daan/水厂同步/TimedUpload/QuartzJobs/DASCDataJob.cs

using log4net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Quartz;
using RDIFramework.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using TimedUpload.utils;

namespace TimedUpload.QuartzJobs
{
    /// <summary>
    /// 大安水厂出厂水定时上传任务
    /// </summary>
    [DisallowConcurrentExecution]
    public class DASCDataJob : IJob
    {
        private readonly ILog log = LogManager.GetLogger(typeof(DASCDataJob));

        static IDbProvider dbHelper
        {
            get
            {
                var DbDefine = DbFactoryProvider.GetProvider(CurrentDbType.SqlServer, SystemConfig.pipeLineDbConn);
                return DbDefine;
            }
        }

        private string manufacturerCode = SystemConfig.pipeLineManufacturerCode;
        private string uploadUrl = SystemConfig.pipeLineNewUploadUrl;

        public void Execute(IJobExecutionContext context)
        {
            //1.获取要上传的测定信息
            //2.清水池 237 获取出厂累计流量1、出厂瞬时流量1
            //3.流量压力 236 获取出厂累计流量2、出厂瞬时流量2、二号出水压力、出厂累计流量3、出厂瞬时流量3
            try
            {
                string sql = "",devId,devNo,devName,lastUpdateTime;
                sql = "Select ID,考核表编号,最后更新时间,名称 From 设备信息 Where 考核表编号<>''";

                DataTable dt = dbHelper.Fill(sql);
                for (int row = 0; row < dt.Rows.Count; row++)
                {
                    devId = dt.Rows[row]["ID"].ToString();//ID
                    devNo = dt.Rows[row]["考核表编号"].ToString();//考核表编号
                    lastUpdateTime= dt.Rows[row]["最后更新时间"].ToString();
                    devName = dt.Rows[row]["名称"].ToString();
                    switch (devId)
                    {
                        case "237":
                            DealQSC(devId, devNo, lastUpdateTime, devName);
                            break;
                        case "236":
                            DealQSC2(devId, devNo, lastUpdateTime, devName);
                            break;
                    }
                }

            }
            catch(Exception ex)
            {
                log.Error("异常信息:" + ex.Message);
            }
        }

        //处理清水池流量
        public void DealQSC(string devId,string devNo,string lastUpdateTime,string devName)
        {
            try
            {
                int year = Convert.ToDateTime(lastUpdateTime).Year;
                string sql = "";
                string strTableName = GetHisTable(devId, year.ToString());
                if (string.IsNullOrEmpty(strTableName))
                    return;
                //先取数据
                sql = "Select 采集时间,出厂累计流量1、出厂瞬时流量1 From " + strTableName + " Where 采集时间 > '" + lastUpdateTime + "'  Order By 采集时间 asc";
                DataTable dtDevHis = dbHelper.Fill(sql);
                //接口是一条一条接收数据
                StringBuilder dataBuilder = new StringBuilder();
                for (int hisRow = 0; hisRow < dtDevHis.Rows.Count; hisRow++)
                {
                    FlowEntity entity = new FlowEntity()
                    {
                        devId=devId,
                        devName=devName,
                        MeterAssessmentCode= devNo,
                        GetDateTime=dtDevHis.Rows[hisRow]["采集时间"].ToString(),
                        NetCumulativeFlow= dtDevHis.Rows[hisRow]["出厂累计流量1"].ToString(),
                        PositiveCumulativeFlow= dtDevHis.Rows[hisRow]["出厂累计流量1"].ToString(),
                        NegativeCumulativeFlow = "0",
                        InstantaneousFlow= dtDevHis.Rows[hisRow]["出厂瞬时流量1"].ToString(),
                        Pressure= "",
                        BatteryVoltageValue= ""
                    };
                    CompactAndSendData(entity);
                }
                //跨年判断
                CrossYear(year, devId);
            }
            catch(Exception ex)
            {
                log.Error("设备名称:" + devName + ",出现异常：" + ex.Message);
            }
            //end
        }

        //处理流量压力
        public void DealQSC2(string devId,string devNo,string lastUpdateTime,string devName)
        {
            try
            {
                int year = Convert.ToDateTime(lastUpdateTime).Year;
                string sql = "";
                string strTableName = GetHisTable(devId, year.ToString());
                if (string.IsNullOrEmpty(strTableName))
                    return;
                //先取数据
                sql = "Select 采集时间,出厂累计流量2、出厂瞬时流量2,二号出水压力、出厂累计流量3、出厂瞬时流量3 From " + strTableName + " Where 采集时间 > '" + lastUpdateTime + "'  Order By 采集时间 asc";
                DataTable dtDevHis = dbHelper.Fill(sql);
                //接口是一条一条接收数据
                StringBuilder dataBuilder = new StringBuilder();
                for (int hisRow = 0; hisRow < dtDevHis.Rows.Count; hisRow++)
                {
                    //2号管
                    FlowEntity entity2 = new FlowEntity()
                    {
                        devId = devId,
                        devName = devName,
                        MeterAssessmentCode = devNo,
                        GetDateTime = dtDevHis.Rows[hisRow]["采集时间"].ToString(),
                        NetCumulativeFlow = dtDevHis.Rows[hisRow]["出厂累计流量2"].ToString(),
                        PositiveCumulativeFlow = dtDevHis.Rows[hisRow]["出厂累计流量2"].ToString(),
                        NegativeCumulativeFlow = "0",
                        InstantaneousFlow = dtDevHis.Rows[hisRow]["出厂瞬时流量2"].ToString(),
                        Pressure = dtDevHis.Rows[hisRow]["二号出水压力"].ToString(),
                        BatteryVoltageValue = ""
                    };
                    CompactAndSendData(entity2);
                    //3号管
                    FlowEntity entity3 = new FlowEntity()
                    {
                        devId = devId,
                        devName = devName,
                        MeterAssessmentCode = devNo,
                        GetDateTime = dtDevHis.Rows[hisRow]["采集时间"].ToString(),
                        NetCumulativeFlow = dtDevHis.Rows[hisRow]["出厂累计流量3"].ToString(),
                        PositiveCumulativeFlow = dtDevHis.Rows[hisRow]["出厂累计流量3"].ToString(),
                        NegativeCumulativeFlow = "0",
                        InstantaneousFlow = dtDevHis.Rows[hisRow]["出厂瞬时流量3"].ToString(),
                        Pressure = "",
                        BatteryVoltageValue = ""
                    };
                    CompactAndSendData(entity3);
                }
                //跨年判断
                CrossYear(year, devId);
            }
            catch (Exception ex)
            {
                log.Error("设备名称:" + devName + ",出现异常：" + ex.Message);
            }
            //end
        }    

        #region 请求接口
        public string postSend(string url, string param)
        {
            string strResult = "";
            Encoding myEncode = Encoding.GetEncoding("UTF-8");
            byte[] postBytes = Encoding.UTF8.GetBytes(param);

            HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url);
            req.Method = "POST";
            req.ContentType = "application/x-www-form-urlencoded;charset=UTF-8";
            req.ContentLength = postBytes.Length;

            try
            {
                using (Stream reqStream = req.GetRequestStream())
                {
                    reqStream.Write(postBytes, 0, postBytes.Length);
                }
                using (WebResponse res = req.GetResponse())
                {
                    using (StreamReader sr = new StreamReader(res.GetResponseStream(), myEncode))
                    {
                        strResult = sr.ReadToEnd();
                        return strResult;
                    }
                }
            }
            catch (WebException ex)
            {
                log.Error("Post数据出错:" + ex.Message);
                return "";
            }
        }
        #endregion

        #region 组装数据发送数据
        public void CompactAndSendData(FlowEntity entity)
        {
            StringBuilder dataBuilder = new StringBuilder();
            dataBuilder.Clear();
            dataBuilder.Append("{");
            dataBuilder.Append("\"ManufacturerCode\":\"").Append(manufacturerCode).Append("\",");
            dataBuilder.Append("\"MeterAssessmentCode\":\"").Append(entity.MeterAssessmentCode).Append("\",");
            dataBuilder.Append("\"GetDateTime\":\"").Append(Convert.ToDateTime(DataBaseHelper.DataFormat(entity.GetDateTime)).ToString("yyyy-MM-dd HH:mm:ss")).Append("\",");
            dataBuilder.Append("\"NetCumulativeFlow\":\"").Append(DataBaseHelper.DataFormat(entity.NetCumulativeFlow)).Append("\",");
            dataBuilder.Append("\"PositiveCumulativeFlow\":\"").Append(DataBaseHelper.DataFormat(entity.PositiveCumulativeFlow)).Append("\",");
            dataBuilder.Append("\"NegativeCumulativeFlow\":\"").Append(DataBaseHelper.DataFormat(entity.NegativeCumulativeFlow)).Append("\",");
            dataBuilder.Append("\"InstantaneousFlow\":\"").Append(DataBaseHelper.DataFormat(entity.InstantaneousFlow)).Append("\",");
            dataBuilder.Append("\"Pressure\":\"").Append(DataBaseHelper.DataFormat(entity.Pressure)).Append("\",");
            dataBuilder.Append("\"BatteryVoltageValue\":\"").Append(DataBaseHelper.DataFormat(entity.BatteryVoltageValue)).Append("\",");
            dataBuilder.Append("\"MeterFault\":\"").Append("0").Append("\",");
            dataBuilder.Append("\"BoxDoorAlarm\":\"").Append("NULL").Append("\",");
            dataBuilder.Append("\"remark1\":\"").Append("NULL").Append("\",");
            dataBuilder.Append("\"remark2\":\"").Append("NULL").Append("\",");
            dataBuilder.Append("\"remark3\":\"").Append("NULL").Append("\",");
            dataBuilder.Append("\"remark4\":\"").Append("NULL").Append("\",");
            dataBuilder.Append("\"remark5\":\"").Append("NULL").Append("\"");
            dataBuilder.Append("}");

            string updateReuslt = postSend(uploadUrl, dataBuilder.ToString());
            JObject jo = (JObject)JsonConvert.DeserializeObject(updateReuslt);
            string execCode = jo["execCode"].ToString();//执行返回代码
            string execMsg = jo["execMsg"].ToString();//返回返回描述
                                                      //执行成功
            if (execCode == "0")
            {
                //更新 记录表
                string UpdateHisSmartSql = "UPDATE 设备信息 SET 最后更新时间 = '" + entity.GetDateTime + "' WHERE ID = " + entity.devId;
                dbHelper.ExecuteNonQuery(UpdateHisSmartSql);
            }
            else
            {
                //插入异常表
                log.Debug("设备名称:" + entity.devName + ",插入异常:" + execMsg);
            }
        }
        #endregion

        #region 历史记录表判断
        public string GetHisTable(string devId,string year)
        {
            string tableName = "";
            tableName="历史记录_" + ("000000" + devId).Substring(devId.Length, 6) + "_" + year;
            if (DataBaseHelper.CheckTableExiste(tableName))
            {
                tableName = "";
            }
            return tableName;
        }
        #endregion

        #region 跨年判断
        public void CrossYear(int year,string devId)
        {
            int thisYear = DateTime.Now.Year;
            string sql = "";
            if (thisYear > year)
            {
                //跨年处理  记录表的更新时间   更新为新一年的 最初时间  例如"2019-01-01 00:00:00"
                sql = "UPDATE 设备信息 SET 最后更新时间 = '" + thisYear + "-01-01 00:00:00.000" + "' WHERE ID = " + devId;
                dbHelper.ExecuteNonQuery(sql);
            }
        }
        #endregion
    }
}