JLH/JLHHJSvr/Helper/ScheduleTaskHelper.cs

using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using LJLib.SQLEX;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;

namespace JLHHJSvr.Helper
{
    internal sealed class ScheduleTaskHelper
    {
        public const string TaskTypeMattressRecalc = "mattress_recalc";
        public const string TaskTypeBatchModifyMattressInterfaceConfig = "batch_modify_mattress_interface_config";

        private readonly object _runLock = new object();
        private bool _running;

        public static string GetTaskTypeName(string taskType)
        {
            switch (taskType)
            {
                case TaskTypeMattressRecalc:
                    return "重算报价";
                case TaskTypeBatchModifyMattressInterfaceConfig:
                    return "批量批改配置";
                default:
                    return taskType ?? string.Empty;
            }
        }

        public static int CreateTaskLog(SqlCommand cmd, u_task_log info)
        {
            var taskid = BllHelper.GetID(cmd, "u_task_log");
            cmd.CommandText = @"
INSERT INTO u_task_log
(taskid, task_type, run_mode, schedule_time, status, total_count, success_count, fail_count, skip_count, request_json, log_msg, create_emp, create_time, start_time)
VALUES
(@taskid, @task_type, @run_mode, @schedule_time, @status, @total_count, 0, 0, 0, @request_json, '', @create_emp, GETDATE(), " + (info.start_time.HasValue ? "GETDATE()" : "NULL") + ")";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@taskid", taskid);
            cmd.Parameters.AddWithValue("@task_type", info.task_type ?? string.Empty);
            cmd.Parameters.AddWithValue("@run_mode", info.run_mode);
            cmd.Parameters.AddWithValue("@schedule_time", (object)info.schedule_time ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@status", info.status);
            cmd.Parameters.AddWithValue("@total_count", info.total_count);
            cmd.Parameters.AddWithValue("@request_json", info.request_json ?? string.Empty);
            cmd.Parameters.AddWithValue("@create_emp", info.create_emp ?? string.Empty);
            cmd.ExecuteNonQuery();
            return taskid;
        }

        public static void InsertTaskLogItems(SqlCommand cmd, int taskid, List<u_task_log_mx> items)
        {
            foreach (var item in items)
            {
                cmd.CommandText = @"
INSERT INTO u_task_log_mx
(taskid, printid, biz_id, biz_code, biz_name, status, result_json, err_msg, start_time)
VALUES
(@taskid, @printid, @biz_id, @biz_code, @biz_name, @status, '', '', " + (item.start_time.HasValue ? "GETDATE()" : "NULL") + ")";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@taskid", taskid);
                cmd.Parameters.AddWithValue("@printid", item.printid);
                cmd.Parameters.AddWithValue("@biz_id", item.biz_id);
                cmd.Parameters.AddWithValue("@biz_code", item.biz_code ?? string.Empty);
                cmd.Parameters.AddWithValue("@biz_name", item.biz_name ?? string.Empty);
                cmd.Parameters.AddWithValue("@status", item.status);
                cmd.ExecuteNonQuery();
            }
        }

        public static void UpdateTaskLogItemResults(SqlCommand cmd, int taskid, List<u_task_log_mx> items)
        {
            foreach (var item in items)
            {
                cmd.CommandText = @"
UPDATE u_task_log_mx
SET status = @status, result_json = @result_json, err_msg = @err_msg, start_time = ISNULL(start_time, GETDATE()), end_time = GETDATE()
WHERE taskid = @taskid AND printid = @printid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@status", item.status);
                cmd.Parameters.AddWithValue("@result_json", item.result_json ?? string.Empty);
                cmd.Parameters.AddWithValue("@err_msg", LimitValue(item.err_msg, 4000));
                cmd.Parameters.AddWithValue("@taskid", taskid);
                cmd.Parameters.AddWithValue("@printid", item.printid);
                cmd.ExecuteNonQuery();
            }
        }

        public static void FinishTaskLog(SqlCommand cmd, int taskid, int totalCount, int successCount, int failCount, int skipCount, string logMsg)
        {
            var status = failCount > 0 && (successCount > 0 || skipCount > 0) ? 4 : (failCount > 0 ? 3 : 2);
            cmd.CommandText = @"
UPDATE u_task_log
SET status = @status,
    total_count = @total_count,
    success_count = @success_count,
    fail_count = @fail_count,
    skip_count = @skip_count,
    log_msg = @log_msg,
    end_time = GETDATE()
WHERE taskid = @taskid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@status", status);
            cmd.Parameters.AddWithValue("@total_count", totalCount);
            cmd.Parameters.AddWithValue("@success_count", successCount);
            cmd.Parameters.AddWithValue("@fail_count", failCount);
            cmd.Parameters.AddWithValue("@skip_count", skipCount);
            cmd.Parameters.AddWithValue("@log_msg", LimitValue(logMsg, 8000));
            cmd.Parameters.AddWithValue("@taskid", taskid);
            cmd.ExecuteNonQuery();
        }

        public void ExecuteDueTasks()
        {
            lock (_runLock)
            {
                if (_running) return;
                _running = true;
            }

            try
            {
                var taskIds = GetDueTaskIds();
                foreach (var taskid in taskIds)
                {
                    ExecuteTask(taskid);
                }
            }
            catch (Exception ex)
            {
                Trace.Write(ex);
            }
            finally
            {
                lock (_runLock)
                {
                    _running = false;
                }
            }
        }

        private List<int> GetDueTaskIds()
        {
            var ids = new List<int>();
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = @"
SELECT taskid
FROM u_task_log WITH (NOLOCK)
WHERE run_mode = 2 AND status = 0 AND schedule_time <= GETDATE()
ORDER BY schedule_time, taskid";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ids.Add(Convert.ToInt32(reader["taskid"]));
                    }
                }
            }
            return ids;
        }

        private void ExecuteTask(int taskid)
        {
            var taskType = string.Empty;
            var createEmp = string.Empty;
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = @"
UPDATE u_task_log
SET status = 1, start_time = GETDATE()
WHERE taskid = @taskid AND run_mode = 2 AND status = 0";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@taskid", taskid);
                if (cmd.ExecuteNonQuery() == 0) return;

                cmd.CommandText = "SELECT task_type, create_emp FROM u_task_log WHERE taskid = @taskid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@taskid", taskid);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        taskType = Convert.ToString(reader["task_type"]);
                        createEmp = Convert.ToString(reader["create_emp"]);
                    }
                }
            }

            var success = 0;
            var fail = 0;
            var taskTypeName = GetTaskTypeName(taskType);
            var log = new StringBuilder();

            foreach (var item in GetPendingItems(taskid))
            {
                var errMsg = string.Empty;
                var isSuccess = false;

                using (var con = GlobalVar.ConnectionString.NewSqlConnection())
                using (var cmd = con.CreateCommand())
                {
                    con.Open();
                    using (cmd.Transaction = con.BeginTransaction())
                    {
                        try
                        {
                            UpdateItemRunning(cmd, taskid, item.printid);
                            ExecuteOne(cmd, taskType, createEmp, item);
                            UpdateItemResult(cmd, taskid, item.printid, 2, string.Empty);
                            cmd.Transaction.Commit();
                            isSuccess = true;
                        }
                        catch (Exception ex)
                        {
                            cmd.Transaction.Rollback();
                            errMsg = ex.Message;
                            Trace.Write(ex);
                        }
                    }
                }

                if (isSuccess)
                {
                    success++;
                    log.AppendLine($"任务类型:{taskTypeName} 单据:{item.biz_code} 执行成功");
                }
                else
                {
                    fail++;
                    SaveItemFail(taskid, item.printid, errMsg);
                    log.AppendLine($"任务类型:{taskTypeName} 单据:{item.biz_code} 执行失败,错误:{errMsg}");
                }

                UpdateTaskProgress(taskid, success, fail);
            }

            FinishTask(taskid, success, fail, log.ToString());
        }

        private void ExecuteOne(SqlCommand cmd, string taskType, string createEmp, u_schedule_task_mx item)
        {
            if (taskType == TaskTypeMattressRecalc)
            {
                var helper = HelperBase.GetHelper<MattressHelper>(cmd, new HelperBase.Context
                {
                    tokendata = new TokenData { username = createEmp },
                    opdate = DateTime.Now
                });
                helper.RecalculateOne(new u_mattress { mattressid = item.biz_id });
                return;
            }

            throw new NotSupportedException("Unsupported task type:" + taskType);
        }

        private List<u_schedule_task_mx> GetPendingItems(int taskid)
        {
            var list = new List<u_schedule_task_mx>();
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = @"
SELECT taskid, printid, biz_id, biz_code, biz_name, request_json
FROM u_task_log_mx
WHERE taskid = @taskid AND status = 0
ORDER BY printid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@taskid", taskid);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new u_schedule_task_mx
                        {
                            taskid = Convert.ToInt32(reader["taskid"]),
                            printid = Convert.ToInt32(reader["printid"]),
                            biz_id = Convert.ToInt32(reader["biz_id"]),
                            biz_code = Convert.ToString(reader["biz_code"]),
                            biz_name = Convert.ToString(reader["biz_name"]),
                            request_json = Convert.ToString(reader["request_json"])
                        });
                    }
                }
            }
            return list;
        }

        private void UpdateItemRunning(SqlCommand cmd, int taskid, int printid)
        {
            cmd.CommandText = @"
UPDATE u_task_log_mx
SET status = 1, start_time = GETDATE(), err_msg = ''
WHERE taskid = @taskid AND printid = @printid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@taskid", taskid);
            cmd.Parameters.AddWithValue("@printid", printid);
            cmd.ExecuteNonQuery();
        }

        private void UpdateItemResult(SqlCommand cmd, int taskid, int printid, int status, string errMsg)
        {
            cmd.CommandText = @"
UPDATE u_task_log_mx
SET status = @status, err_msg = @err_msg, start_time = ISNULL(start_time, GETDATE()), end_time = GETDATE()
WHERE taskid = @taskid AND printid = @printid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@status", status);
            cmd.Parameters.AddWithValue("@err_msg", Limit(errMsg, 4000));
            cmd.Parameters.AddWithValue("@taskid", taskid);
            cmd.Parameters.AddWithValue("@printid", printid);
            cmd.ExecuteNonQuery();
        }

        private void SaveItemFail(int taskid, int printid, string errMsg)
        {
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                UpdateItemResult(cmd, taskid, printid, 3, errMsg);
            }
        }

        private void UpdateTaskProgress(int taskid, int success, int fail)
        {
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = @"
UPDATE u_task_log
SET success_count = @success_count, fail_count = @fail_count
WHERE taskid = @taskid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@success_count", success);
                cmd.Parameters.AddWithValue("@fail_count", fail);
                cmd.Parameters.AddWithValue("@taskid", taskid);
                cmd.ExecuteNonQuery();
            }
        }

        private void FinishTask(int taskid, int success, int fail, string logMsg)
        {
            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = @"
UPDATE u_task_log
SET status = @status, success_count = @success_count, fail_count = @fail_count,
    log_msg = @log_msg, end_time = GETDATE()
WHERE taskid = @taskid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@status", fail > 0 && success > 0 ? 4 : (fail > 0 ? 3 : 2));
                cmd.Parameters.AddWithValue("@success_count", success);
                cmd.Parameters.AddWithValue("@fail_count", fail);
                cmd.Parameters.AddWithValue("@log_msg", logMsg ?? string.Empty);
                cmd.Parameters.AddWithValue("@taskid", taskid);
                cmd.ExecuteNonQuery();
            }
        }

        private string Limit(string value, int maxLength)
        {
            return LimitValue(value, maxLength);
        }

        private static string LimitValue(string value, int maxLength)
        {
            if (string.IsNullOrEmpty(value)) return string.Empty;
            return value.Length <= maxLength ? value : value.Substring(0, maxLength);
        }
    }
}