2240037_WCSHSCP/WcfControlMonitorWebLib/Controllers/RouteStatusController.cs

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WcfControlMonitorLib;

namespace WcfControlMonitorWebLib.Controllers
{
    [ApiController, Route("api/routestatus")]
    public class RouteStatusController: ControllerBase
    {
        DataView dv = new DataView();

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="id">路径索引</param>
        /// <param name="startDevice">起点设备</param>
        /// <param name="endDevice">终点设备</param>
        /// <param name="status">路径状态</param>
        /// <param name="sort">排序字段</param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Get(
            [FromQuery] string id,
            [FromQuery] string startDevice,
            [FromQuery] string endDevice,
            [FromQuery] string status,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit)
        {
            //F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status
            if (offset == null)
            {
                offset = 0;
            }
            if (limit == null)
            {
                limit = 30;
            }
            #region  排序信息需要转一下
            if (sort?.Length > 1)
            {
                var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
                if ((bool)sort?.StartsWith("~"))
                {
                    switch (sort.Substring(2))
                    {
                        case "d":
                            sort = string.Format(format, "F_RouteID");
                            break;
                        case "tatus":
                            sort = string.Format(format, "f_status");
                            break;
                        default:
                            sort = string.Format(format, "F_RouteID");
                            break;
                    }
                }
                else
                {
                    switch (sort.Substring(1))
                    {
                        case "d":
                            sort = string.Format(format, "F_RouteID");
                            break;
                        case "tatus":
                            sort = string.Format(format, "f_status");
                            break;
                        default:
                            sort = string.Format(format, "F_RouteID");
                            break;
                    }
                }

            }
            else
            {
                sort = "F_RouteID";
            }
            #endregion
            #region  拼接 where 条件
            var where = "1=1";
            if (!string.IsNullOrEmpty(id))
            {
                where = $"{where} AND F_RouteID = {id}";
            }
            if (!string.IsNullOrEmpty(startDevice))
            {
                where = $"{where} AND F_StartDevice = '{startDevice}'";
            }
            if (!string.IsNullOrEmpty(endDevice))
            {
                where = $"{where} AND F_EndDevice = '{endDevice}'";

            }
            if (!string.IsNullOrEmpty(status))
            {
                where = $"{where} AND f_status = {status}";

            }

            #endregion
            var sql = $"SELECT top {limit} F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status FROM (SELECT F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  T_Base_Route WHERE   {where}) AS T WHERE ROW_NUM > {offset}";
            var sqlAll = $"SELECT F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status FROM (SELECT F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status,ROW_NUMBER() OVER (ORDER BY F_RouteID) AS ROW_NUM FROM  T_Base_Route WHERE   {where}) AS T WHERE ROW_NUM > 0";

            DataView dv1 = CStaticClass.dbo.ExceSQL(sqlAll.ToString()).Tables[0].DefaultView;//满足的条件的所有数据 返回总记录条数用

            dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;

            var items = new dynamic[dv.Count];
            for (int i = 0; i < dv.Count; i++)
            {
                items[i] = new
                {
                    //F_RouteID, F_RouteKind,F_RouteName,F_StartDevice,F_EndDevice,F_OutsideAltDevice,F_InsideAltDevice,f_status
                    id = Convert.ToInt32(dv[i]["F_RouteID"]),
                    routeKind = Convert.ToString(dv[i]["F_RouteKind"]),
                    routeName = Convert.ToString(dv[i]["F_RouteName"]),
                    startDevice = Convert.ToString(dv[i]["F_StartDevice"]),
                    endDevice = Convert.ToString(dv[i]["F_EndDevice"]),
                    outsideAltDevice = Convert.ToInt32(dv[i]["F_OutsideAltDevice"]),
                    insideAltDevice = Convert.ToInt32(dv[i]["F_InsideAltDevice"]),
                    status = Convert.ToInt32(dv[i]["f_status"])
                };
            }
            return Ok(new { total = dv1.Count, items });
        }
        
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="id">路径索引</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public IActionResult Modify(
            [FromRoute] string id, // routeid
            [FromMember] string status)
        {
            // 写入数据库

            int count = CStaticClass.dbo.ExecuteSql($"update T_Base_Route  set  f_status={status} where F_RouteID={id}");
            
            if (count > 0)
            {
                CStaticClass.dboM.ExecuteSql($"update IO_CONTROL_ROUTE set CONTROL_ROUTE_STATUS={status} where CONTROL_ROUTE_ID={id}");

                return Ok();
            }
            else
            {
                return StatusCode(422);
            }
        }
    }
}