BaLinYouQiDataUpload/TimedUpload/QuartzJobs/DataUploadJob.cs

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

namespace TimedUpload.QuartzJobs
{
    [DisallowConcurrentExecution]
    public class DataUploadJob : IJob
    {
        private readonly ILog log = LogManager.GetLogger(typeof(IJob));
        //接口地址
        public static string str_ApiUrl = Constants.str_Url;
        //公司编号
        public static string str_Company = Constants.str_Company;

        public void Execute(IJobExecutionContext context)
        {
            //log.InfoFormat("数据上传服务");
            DateTime date = DateTime.Now;
            log.Info("数据上传任务开始执行.................\r\n");
            try
            {

                //#region 有现远传水表
                //UpdateMeterDread();
                //UpdateMeterDreadOfBigMeter();

                ////2021-01-23移动NB水表对接
                //UpdateNBMeter();
                UpDataValveControlState();
             //   #endregion
            }
            catch (Exception ex)
            {
                log.Error("任务上传异常:" + ex.Message);
            }

            log.InfoFormat("数据上传任务执行结束.................\r\n");
        }

        //上传数据
        public void UpdateMeterDread()
        {

            //2021-01-21 
            Int32 i_Count = 0;
            i_Count = Convert.ToInt32(ConfigurationManager.AppSettings["Count"]);
            //
            try
            {
                List<meterRead> meterList = new List<meterRead>();
                meterRead[] meters = new meterRead[] { };
                WebReference.MeterReadingImplService wm = new WebReference.MeterReadingImplService();
                // String sql = "select  表地址,表读数,ReviseDT from Bsc_MeterInfo ";select t.表地址, t.ReviseDT, t.表读数 from Bsc_MeterInfo t inner join (    select 表地址, max(ReviseDT) as MaxDate    from Bsc_MeterInfo    group by 表地址) tm on t.表地址 = tm.表地址 and t.ReviseDT = tm.MaxDate
                //String sql = "select t.表地址, t.ReviseDT, t.表读数 from Bsc_MeterInfo t inner join (    select 表地址, max(ReviseDT) as MaxDate    from Bsc_MeterInfo    group by 表地址) tm on t.表地址 = tm.表地址 and t.ReviseDT = tm.MaxDate ";
                String sql = "select t.表地址, t.ReviseDT, t.表读数 from Bsc_MeterInfo t inner join (    select 表地址, max(ReviseDT) as MaxDate    from Bsc_MeterInfo    group by 表地址) tm on t.表地址 = tm.表地址 and t.ReviseDT = tm.MaxDate ";
                DataTable dt = dbHelper.Fill(sql);
                StringBuilder sb_Info = new StringBuilder();
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //meterList = new List<meterRead>();
                        meterRead meter = new meterRead();
                        meter.meterAddress = dt.Rows[i]["表地址"].ToString();
                        meter.readDate = dt.Rows[i]["ReviseDT"].ToString();
                        meter.readNum = Convert.ToDouble(dt.Rows[i]["表读数"].ToString());
                        meter.metersort_id = "2";
                        meter.meterState = "3";
                        meterList.Add(meter);
                        sb_Info.Append(meter.meterAddress + "|" + meter.readDate + "|" + meter.readNum + "\r\n");

                        if (meterList.Count == i_Count || (meterList.Count != i_Count && i == dt.Rows.Count - 1))
                        //if (meterList.Count == 1 )
                        {

                            meters = meterList.ToArray();

                            String ss = wm.UPMeterRead(meters, "2");
                            if (ss == "true")
                            {
                                //log.Info(DateTime.Now.ToString() + "_[" + meter.meterAddress + "]上传成功");
                                log.Info(DateTime.Now.ToString() + sb_Info.ToString()+ "_上传成功");
                                //log2txt.WriteLog(DateTime.Now.ToString() + "_" + "上传成功");
                                sb_Info = new StringBuilder();
                            }
                            else if (ss == "")
                            {
                                log.Info("接口返回空" + sb_Info.ToString()  +"上传成功");
                                sb_Info = new StringBuilder();
                            }

                            else
                            {
                                //log.Info(DateTime.Now.ToString() + "_[" + meter.meterAddress + "]上传失败" + ss);

                                log.Info(DateTime.Now.ToString()  +sb_Info.ToString() + "_上传失败" + ss);
                                sb_Info = new StringBuilder();
                            }
                            meterList = new List<meterRead>();
                        }
                       
                    }

                   

                }

            }
            catch (Exception ex)
            {

                log.Info("接口返回：" + ex.ToString() + "_" + "上传失败");
            }
        }

        #region 2019-10-10 大表对接
        public void UpdateMeterDreadOfBigMeter()
        {
            try
            {
                List<meterRead> meterList = new List<meterRead>();
                meterRead[] meters = new meterRead[] { };
                WebReference.MeterReadingImplService wm = new WebReference.MeterReadingImplService();
                // String sql = "select  表地址,表读数,ReviseDT from Bsc_MeterInfo ";select t.表地址, t.ReviseDT, t.表读数 from Bsc_MeterInfo t inner join (    select 表地址, max(ReviseDT) as MaxDate    from Bsc_MeterInfo    group by 表地址) tm on t.表地址 = tm.表地址 and t.ReviseDT = tm.MaxDate
                // String sql = "select t.表地址, t.ReviseDT, t.表读数 from Bsc_MeterInfo t inner join (    select 表地址, max(ReviseDT) as MaxDate    from Bsc_MeterInfo    group by 表地址) tm on t.表地址 = tm.表地址 and t.ReviseDT = tm.MaxDate ";
                StringBuilder sb_Sql = new StringBuilder();

                sb_Sql.Append("  select C.值 as 表地址,A.正累计流量 as 表读数,A.采集时间 as ReviseDT,D.值 as SIM,A.电池电压, ");
                sb_Sql.Append("ISNULL(A.GPRS信号质量,0) as 信号,ISNULL(A.压力,0) as 供水压力,A.瞬时流量  from   最后状态 A   ");
                sb_Sql.Append("inner join  (");
                sb_Sql.Append("select MAX(A.id) as  max_Id ,A.设备ID  from   最后状态 A left join  ");
                sb_Sql.Append("设备信息 B on B.ID = A.设备ID where B.管理ID  = 125   Group by A.设备ID "); //设备管理ID 暂时写死
                sb_Sql.Append(")   b on   A.id = b.max_Id and A.设备ID = B.设备ID  ");
                sb_Sql.Append("left join   设备辅助信息 C on C.设备ID = B.设备ID and C.名称 = '表身号' ");
                sb_Sql.Append("left join   设备辅助信息 D on D.设备ID = B.设备ID and D.名称 = '手机卡号'  ");

                DataTable dt = dbHelperNB.Fill(sb_Sql.ToString());
                StringBuilder sb_Info = new StringBuilder();
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                      //  meterList = new List<meterRead>();
                        meterRead meter = new meterRead();
                        meter.meterAddress = dt.Rows[i]["表地址"].ToString();
                        meter.readDate = dt.Rows[i]["ReviseDT"].ToString();
                        meter.readNum = Convert.ToDouble(dt.Rows[i]["表读数"].ToString());
                        sb_Info.Append(meter.meterAddress + "|" + meter.readDate + "|" + meter.readNum + "\r\n");
                        meter.metersort_id = "8";
                        meter.meterState = "3";
                        meterList.Add(meter);
                     
                        if (meterList.Count == 100 || (meterList.Count != 100 && i == dt.Rows.Count - 1))
                        {
                            meters = meterList.ToArray();
                            String ss = wm.UPMeterRead(meters, "8");
                            if (ss == "true")
                            {
                                //log.Info(DateTime.Now.ToString() + "_" + "上传成功");
                                log.Info(DateTime.Now.ToString() + sb_Info + "_上传成功（大表）");
                                sb_Info = new StringBuilder();
                            }
                            else
                            {
                                log.Info(DateTime.Now.ToString() + sb_Info + "_上传失败（大表）" + ss);
                                sb_Info = new StringBuilder();
                            }
                            meterList = new List<meterRead>();
                        }
                    }



                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }




        #endregion

        #region 2021-01-23 移动NB水表对接
        public void UpdateNBMeter()
        {
            #region 变量
            String str_Sql;
            DataTable dt = new DataTable();
            String meterTypeNotValve = ConfigurationManager.AppSettings["meterTypeNotValve"];
            String meterTypeValve = ConfigurationManager.AppSettings["meterTypeValve"];

            #endregion

            #region 逻辑
            log.Debug("~~~~~~NB非阀控水表~~~~~");
            str_Sql = "select ElecAddress as 表地址,NowRadingDT as ReviseDT,NowReading as 表读数 from ONET_RMRS_MeterInfo where CompanyID ='6e50093a-2b74-4e3e-9993-c3bd1f23979a' and DeleteMark = 0 and MeterComTypeId<>6";
            dt = dbHelperNBYd.Fill(str_Sql);
            //非阀控
            insertData(dt, meterTypeNotValve);
            //阀控水表
            log.Debug("~~~~~~NB阀控水表~~~~~");
            str_Sql = "select ElecAddress as 表地址,NowRadingDT as ReviseDT,NowReading as 表读数 from ONET_RMRS_MeterInfo where CompanyID ='6e50093a-2b74-4e3e-9993-c3bd1f23979a' and DeleteMark = 0 and MeterComTypeId=6";
            dt = dbHelperNBYd.Fill(str_Sql);
            insertData(dt, meterTypeValve);

            #endregion

        }
        #endregion

        private void insertData(DataTable dt,String meterType) {
            List<meterRead> meterList = new List<meterRead>();
            //上传条数
            Int32 i_Count =  Convert.ToInt32(ConfigurationManager.AppSettings["Count"]); 
            StringBuilder sb_Info = new StringBuilder();
            meterRead[] meters = new meterRead[] { };
            //接口
            WebReference.MeterReadingImplService wm = new WebReference.MeterReadingImplService();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                  try
                   {
                        
                      meterRead meter = new meterRead();
                    meter.meterAddress = dt.Rows[i]["表地址"].ToString();
                    meter.readDate = dt.Rows[i]["ReviseDT"].ToString();
                    meter.readNum = Convert.ToDouble(dt.Rows[i]["表读数"].ToString());
                    meter.metersort_id = meterType;
                    meter.meterState = "3";
                    meterList.Add(meter);
                    sb_Info.Append(meter.meterAddress + "|" + meter.readDate + "|" + meter.readNum + "\r\n");

                    if (meterList.Count == i_Count || (meterList.Count != i_Count && i == dt.Rows.Count - 1))
                   
                    {
                        
                            meters = meterList.ToArray();

                            String ss = wm.UPMeterRead(meters, meterType);
                            if (ss == "true")
                            {
                               
                                log.Info(DateTime.Now.ToString() + sb_Info.ToString() + "_上传成功");
                               
                                sb_Info = new StringBuilder();
                            }
                            else if (ss == "")
                            {
                                log.Info("接口返回空" + sb_Info.ToString() + "上传成功");
                                sb_Info = new StringBuilder();
                            }

                            else
                            {
                               

                                log.Info(DateTime.Now.ToString() + sb_Info.ToString() + "_上传失败" + ss);
                                sb_Info = new StringBuilder();
                            }
                            meterList = new List<meterRead>();
                        }


                    }
                    catch (Exception ex)
                    {
                        log.Debug("~~~~~~~~" + dt.Rows[i]["表地址"].ToString() + ",报错：" + ex.ToString());
                    }

                }



            }

        }
        /// <summary>
        /// 阀控指令更新
        /// </summary>
        private void UpDataValveControlState()
        {
            //远传表厂家编号 
            String metersortId = "13";
            StringBuilder sql = new StringBuilder();
            List<valveControlState> vcStateList = new List<valveControlState>();
            valveControlState[] valveControlStates = new valveControlState[] { };

            valveControlState vcState = new valveControlState();

            WebReference.MeterReadingImplService wm = new WebReference.MeterReadingImplService();
            sql.Append("select distinct c.id,a.MeterID,c.meteraddress, a.ValveStatus,b.CmdResult,c.updateTime,ReturnCode  from  ONET_RMRS_MeterInfo a ");
            sql.Append("inner join ONET_RMRS_SendCmdRecord b on a.MeterID = b.MeterId ");
            sql.Append("inner join TempValveControlData c on a.ElecAddress = c.meteraddress ");
            sql.Append("where a.DeleteMark = 0 and CmdType = 1002 ");

            DataTable dt = dbHelperNBYd.Fill(sql.ToString());

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    vcState.id = dt.Rows[i]["id"].ToString();
                    
                    if (dt.Rows[i]["ValveStatus"].ToString() == "00")
                    {
                        vcState.meterState = "开阀";
                    }
                    else
                    {
                        vcState.meterState = "关阀";
                    }
                    // dt.Rows[i]["CmdResult"] = vcState.state;
                    //  if (dt.Rows[i]["CmdResult"].ToString() == "命令成功")
                    String returnCode = dt.Rows[i]["ReturnCode"] == null ? null : dt.Rows[i]["ReturnCode"].ToString();
                    if (returnCode == "0")      
                    {
                        vcState.state = "1";
                        log.Debug("########指令ID:"+ vcState.id+",水表地址:"+dt.Rows[i]["meteraddress"] +"执行成功");
                    }
                    else
                    {
                        vcState.state = "0";
                        vcState.result = "指令超时";
                        log.Debug("########指令ID:" + vcState.id + ",水表地址:" + dt.Rows[i]["meteraddress"] + "执行失败");
                    }
                    vcState.controlTime = dt.Rows[i]["updateTime"].ToString();
                    
                    vcStateList.Add(vcState);
                }
             //   valveControlStates = vcStateList.ToArray();
                Boolean result = wm.UPValveControlState(valveControlStates, metersortId);

                if (result)
                {
                    log.Info("上传成功");
                }
                else
                {
                    log.Info("上传失败");
                }

            }


            /*wm.UPValveControlState();*/



        }
        static IDbProvider dbHelper
        {
            get
            {
                var DbDefine = DbFactoryProvider.GetProvider(CurrentDbType.SqlServer, Constants.DBUrl);
                return DbDefine;
            }
        }
        static IDbProvider dbHelperNB
        {
            get
            {
                var DbDefine = DbFactoryProvider.GetProvider(CurrentDbType.SqlServer, Constants.DBUrlNb);
                return DbDefine;
            }
        }
        static IDbProvider dbHelperNBYd
        {
            get
            {
                var DbDefine = DbFactoryProvider.GetProvider(CurrentDbType.SqlServer, Constants.DBUrlNbYd);
                return DbDefine;
            }
        }
    }

    public class MeterInfo
    {
        //交易代码
        public String CBNY { get; set; }
        //抄表月份
        public String JYDM { get; set; }
        //表厂名称
        public String CJDM { get; set; }

        public List<DATA> DATA { get; set; }
    }

    public class DATA
    {
        //本月表数
        public String SYBS { get; set; }
        //抄表日期
        public String CBRQ { get; set; }
        //水表编号
        public String SBBH { get; set; }
        //用户编号
        public String CUSERID { get; set; }
        //抄表状态
        public String CBZT { get; set; }
        //本月表数
        public double BYBS { get; set; }
        //抄表标记
        public Int32 CCOPY { get; set; }
        //加水量
        public double JJSL { get; set; }
        //抄表备注
        public String REMARK { get; set; }
    }
    public class ValueControlInfo
    {
        public String ManufacturerCode { get; set; }

        public String MeterAddress { get; set; }

        public String CommandType { get; set; }

        public String ValueState { get; set; }
    }
}