ZiYangSftpDataUpload/TimedUpload/QuartzJobs/SFTPUploadJob.cs

using log4net;
using Quartz;
using SFTPUtils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using TimedUpload.Util;

namespace TimedUpload.QuartzJobs
{
    public class SFTPUploadJob : IJob
    {
        private readonly ILog log = LogManager.GetLogger(typeof(SFTPUploadJob));
        private static DateTime timeStampStartTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);


        SqlHelper _sqlHelper = new SqlHelper("NBConn");
        int port = 22;
        string outputDirectory = Config.GetValue("outputDirectory");
        string serverPath = Config.GetValue("serverPath");
        string host = Config.GetValue("host");
        string username = Config.GetValue("username");
        string privateKeyPath = Config.GetValue("privateKeyPath");

        public void Execute(IJobExecutionContext context)
        {
            // 生成今天的json文件
            string filename = GenerateTodayFile();
            // 生成的数据上传给sftp
            UploadDataInfo(filename);
        }




        /// <summary>
        /// 先生成json文件
        /// </summary>
        public string GenerateTodayFile()
        {
            // 获取当前日期时间
            DateTime today = DateTime.Now;

            // 分别获取今天年、月、日
            int year = today.Year;
            int month = today.Month;
            int day = today.Day;
            string resultMonth;
            if (month < 10)
            {
                resultMonth = "0" + month;
            }
            else
            {
                resultMonth = month.ToString();
            }
            // 获取明天天的日期
            DateTime tomorrow = DateTime.Today.AddDays(+1);

            // 分别获取明天年、月、日
            int mingYear = tomorrow.Year;
            int mingMonth = tomorrow.Month;
            int mingDay = tomorrow.Day;
            GenerateHisFile(year, month, day, mingYear, mingMonth, mingDay);
            string filename = year + resultMonth + day + ".json";
            return filename;
        }


        /// <summary>
        /// 上传数据
        /// </summary>
        public void UploadDataInfo(String fileName)
        {
            var sftpClient = new SftpClientWithKey(host, port, username, privateKeyPath);

            try
            {
                // 获取当前日期时间
                DateTime today = DateTime.Now;

                // 分别获取今天年、月、日
                int year = today.Year;
                int month = today.Month;
                string resultMonth;
                if (month < 10)
                {
                    resultMonth = "0" + month;
                }
                else
                {
                    resultMonth = month.ToString();
                }
                // 上传文件
                bool success = sftpClient.UploadFile(
                    outputDirectory + fileName,
                    serverPath+year+"/"+resultMonth,
                    null);

                Console.WriteLine($"上传结果: {success}");

                // 列出文件
                var files = sftpClient.ListFiles(serverPath+year+" / "+resultMonth);
                foreach (var file in files)
                {
                    Console.WriteLine(file.Name);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"错误: {ex.Message}");
            }
            finally
            {
                sftpClient.Dispose();
            }
        }


        /// <summary>
        /// 生成历史json文件
        /// </summary>
        public void GenerateHisFile(int startYear,int startMonth,int startDay,int endYear,int endMonth,int endDay)
        {
            try
            {

                try
                {

                    // 方法1：使用DataExporter
                    var jsonExporter = new JsonExporter();

                    DateTime startDate = new DateTime(startYear, startMonth, startDay);
                    DateTime endDate = new DateTime(endYear, endMonth, endDay);

                    Console.WriteLine("开始获取数据...");
                    var dailyData = GetDailyData(startDate, endDate);

                    Console.WriteLine("开始导出JSON文件...");
                    jsonExporter.ExportDailyDataToJson(dailyData, outputDirectory);

                    Console.WriteLine($"成功导出 {dailyData.Count} 天的数据");

                    // 方法2：使用批量处理（推荐大数据量）
                    // var bulkExporter = new BulkDataExporter();
                    // bulkExporter.ExportDirectToJsonFiles(startDate, endDate, outputDirectory);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"导出失败: {ex.Message}");
                    Console.WriteLine($"堆栈跟踪: {ex.StackTrace}");
                }


                // 先生成json文件
                //GenerateFile();
                //UploadDataInfo();


            }
            catch (Exception ex)
            {
                log.Error("上传数据错误:" + ex.Message);
            }

        }

        public List<DailyData> GetDailyData(DateTime startDate, DateTime endDate)
        {
            var dailyDataList = new List<DailyData>();

            // 获取所有日期
            var dates = GetDatesInRange(startDate, endDate);

            foreach (var date in dates)
            {
                var dailyData = new DailyData
                {
                    Date = date,
                    Records = GetRecordsForDay(date)
                };
                dailyData.RecordCount = dailyData.Records.Count;
                dailyDataList.Add(dailyData);
            }

            return dailyDataList;
        }

        private List<DateTime> GetDatesInRange(DateTime startDate, DateTime endDate)
        {
            var dates = new List<DateTime>();

            string sql =$@"
            SELECT DISTINCT CONVERT(date, r.ReadingDT) as Day
            FROM RMRS_MeterInfo m left join [dbo].[RMRS_HistoryRecord] r on m.MeterID = r.MeterID
            WHERE m.CompanyID = 'b9df6406-428e-4fb6-8bc7-ecc86a563bef' and m.DeleteMark = 0 and r.ReadingDT BETWEEN '{startDate}' AND '{endDate}'
            ORDER BY Day";


            DataTable dt = _sqlHelper.ExecuteDataTable(sql);

            foreach (DataRow row in dt.Rows)
            {
                dates.Add(Convert.ToDateTime(row["Day"]));
            }

            return dates;
        }

        private List<HistoryRecord> GetRecordsForDay(DateTime date)
        {
            var records = new List<HistoryRecord>();

            // 计算昨天的日期和昨天11点的时间
            DateTime yesterday = date.AddDays(-1);
            DateTime yesterday12AM = new DateTime(yesterday.Year, yesterday.Month, yesterday.Day, 12, 0, 0);

            string sql = $@"
            SELECT r.ReadingDT, r.Reading, m.ElecAddress
            FROM RMRS_MeterInfo m left join [dbo].[RMRS_HistoryRecord] r on m.MeterID = r.MeterID
            WHERE m.CompanyID = 'b9df6406-428e-4fb6-8bc7-ecc86a563bef' and m.DeleteMark = 0 AND ( CONVERT(date, r.ReadingDT) = '{date}' OR (
            r.ReadingDT >= '{yesterday12AM:yyyy-MM-dd HH:mm:ss}'
            AND r.ReadingDT < '{date:yyyy-MM-dd}') )
            ORDER BY r.ReadingDT";


            DataTable dt = _sqlHelper.ExecuteDataTable(sql);

            foreach (DataRow row in dt.Rows)
            {
                var record = new HistoryRecord
                {
                    ReadingDT = Convert.ToDateTime(row["ReadingDT"]),
                    Reading = Convert.ToDecimal(row["Reading"]),
                    ElecAddress = row["ElecAddress"].ToString()
                };
                records.Add(record);
            }

            return records;
        }

        public class HistoryRecord
        {
            [ScriptIgnore]
            public DateTime ReadingDT { get; set; }

            public string ReadingDateTime
            {
                get { return ReadingDT.ToString("yyyy-MM-dd HH:mm:ss"); }
            }

            public decimal Reading { get; set; }
            public string ElecAddress { get; set; }
        }

        public class DailyData
        {
            [ScriptIgnore]
            public DateTime Date { get; set; }

            public string ExportDate
            {
                get { return Date.ToString("yyyy-MM-dd"); }
            }

            public int RecordCount { get; set; }
            public List<HistoryRecord> Records { get; set; }
        }


    }


}