2240037_WCSHSCP/WcfControlMonitorWebLib/Controllers/DeviceStatusController.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/devicestatus")]
    public class DeviceStatusController : ControllerBase
    {
        DataView dv = new DataView();
        SControlMonitor SControlMonitor = new SControlMonitor(false);//SControlMonitor构造函数改了 调用这里面的方法

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="id">设备编号</param>
        /// <param name="lockedState">执行状态</param>
        /// <param name="errorCode">设备状态</param>
        /// <param name="manTaskReserve">预约锁</param>
        /// <param name="sort"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Get(
            [FromQuery] string id,
            [FromQuery] string lockedState,
            [FromQuery] string errorCode,
            [FromQuery] string manTaskReserve,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit)
        {
            //F_DeviceIndex id,F_DeviceName,F_DeviceKindIndex,F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods
            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, "T_Base_Device.F_DeviceIndex");
                            break;
                        default:
                            sort = string.Format(format, "T_Base_Device.F_DeviceIndex");
                            break;
                    }
                }
                else
                {
                    switch (sort.Substring(1))
                    {
                        case "d":
                            sort = string.Format(format, "T_Base_Device.F_DeviceIndex");
                            break;
                        default:
                            sort = string.Format(format, "T_Base_Device.F_DeviceIndex");
                            break;
                    }
                }

            }
            else
            {
                sort = "T_Base_Device.F_DeviceKindIndex";
            }
            #endregion
            #region  拼接 where 条件
            var where = "1=1";
            if (!string.IsNullOrEmpty(id))
            {
                where = $"{where} AND T_Base_Device.F_DeviceIndex = {id}";
            }
            if (!string.IsNullOrEmpty(lockedState))
            {
                if (lockedState == "1")
                {
                    where = $"{where} AND F_LockedState > 0";
                }
                else
                {
                    where = $"{where} AND F_LockedState = {lockedState}";
                }
            }
            if (!string.IsNullOrEmpty(errorCode))
            {
                if (errorCode == "1")
                {
                    where = $"{where} AND F_errorcode >= 30";
                }
                else
                {
                    where = $"{where} AND F_errorcode = {errorCode}";
                }
                
            }
            if (!string.IsNullOrEmpty(manTaskReserve))
            {
                if (manTaskReserve == "0")
                {
                    where = $"{where} AND F_ManTaskReserve = {manTaskReserve}";
                }
                else
                {
                    where = $"{where} AND F_ManTaskReserve > 0 ";
                }
                
            }

            #endregion
            var sql = $"SELECT top {limit} F_DeviceIndex,F_DeviceName,F_DeviceKindIndex,F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods FROM (SELECT T_Base_Device.F_DeviceIndex,T_Base_Device.F_DeviceName,T_Base_Device.F_DeviceKindIndex,T_Base_Device_Kind.F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  T_Base_Device,T_Base_Device_Kind WHERE T_Base_Device.F_DeviceKindIndex =  T_Base_Device_Kind.F_DeviceKindIndex  and  {where}) AS T WHERE ROW_NUM > {offset}";
            var sqlAll = $"SELECT F_DeviceIndex,F_DeviceName,F_DeviceKindIndex,F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods FROM (SELECT T_Base_Device.F_DeviceIndex,T_Base_Device.F_DeviceName,T_Base_Device.F_DeviceKindIndex,T_Base_Device_Kind.F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods,ROW_NUMBER() OVER (ORDER BY T_Base_Device.F_DeviceKindIndex) AS ROW_NUM FROM  T_Base_Device,T_Base_Device_Kind WHERE T_Base_Device.F_DeviceKindIndex =  T_Base_Device_Kind.F_DeviceKindIndex  and  {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_DeviceIndex,F_DeviceName,F_DeviceKindIndex,F_DeviceKindName,F_LockedState,F_errorcode,F_ManTaskReserve,F_HaveGoods
                    id = Convert.ToInt32(dv[i]["F_DeviceIndex"]),
                    deviceName = Convert.ToString(dv[i]["F_DeviceName"]),
                    deviceKindIndex = Convert.ToInt32(dv[i]["F_DeviceKindIndex"]),
                    deviceKindName = Convert.ToString(dv[i]["F_DeviceKindName"]),
                    lockedState = Convert.ToInt32(dv[i]["F_LockedState"]),
                    manTaskReserve = Convert.ToInt32(dv[i]["F_ManTaskReserve"]),
                    errorCode = Convert.ToInt32(dv[i]["F_errorcode"]),
                    haveGoods = Convert.ToInt32(dv[i]["F_HaveGoods"])
                };
            }
            return Ok(new { total = dv1.Count, items });
        }

        
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="id">设备编号</param>
        /// <param name="lockedState">执行状态</param>
        /// <param name="manTaskReserve">预约锁</param>
        /// <param name="errorCode">设备状态</param>
        /// <param name="haveGoods">逻辑有货</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public IActionResult Modify(
            [FromRoute] string id, // deviceIndex
            [FromMember] string lockedState, 
            [FromMember] string manTaskReserve, 
            [FromMember] string errorCode,
            [FromMember] string haveGoods)
        {
            // 写入数据库
            int a;
            string error;
            if (lockedState == "0")
            {
                lockedState = "0";
                errorCode = "0";
            }
            else if (lockedState == "-1")
            {
                lockedState = "-1";
                errorCode = "998";
            }
            else
            {
                if (int.TryParse(lockedState, out a) == false)
                {
                    return StatusCode(422);
                }
            }

            int count = CStaticClass.dbo.ExecuteSql($"UPDATE T_Base_Device SET F_LockedState ={lockedState},f_errorcode={errorCode},F_ManTaskReserve ={manTaskReserve},F_HaveGoods = {haveGoods}  where F_DeviceIndex= {id}");
            
            if (count > 0)
            {
                Model.MDevice dev = Model.CGetInfo.GetDeviceInfo(Convert.ToInt32(id));
                if (lockedState == "-1")
                {
                    dev.RunState = 4;
                }
                else
                {
                    dev.RunState = Convert.ToInt32(lockedState);//20181204
                }
                dev.ManTaskReserve = Convert.ToInt32(manTaskReserve);
                dev.ErrorCode = Convert.ToInt32(errorCode);
                if (haveGoods == "0")
                {
                    dev.LogicHaveGoods = false;
                }
                else
                {
                    dev.LogicHaveGoods = true;
                }
                SControlMonitor.SetDeviceState(dev,out error);

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