2240037_WCSHSCP/WcfControlMonitorWebLib/Controllers/ApplyRecordController.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/applyrecord")]
    public class ApplyRecordController: ControllerBase
    {
        DataView dv = new DataView();

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="id">申请编号</param>
        /// <param name="wareHouse">仓库编号</param>
        /// <param name="applyDeviceCode">申请设备</param>
        /// <param name="applyTimeFrom">申请时间开始</param>
        /// <param name="applyTimeTo">申请时间To</param>
        /// <param name="sort"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Get(
            [FromQuery] string id,
            [FromQuery] string wareHouse,
            [FromQuery] string applyDeviceCode,
            [FromQuery] string applyTimeFrom,
            [FromQuery] string applyTimeTo,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit)
        {
            //CONTROL_APPLY_ID as id,CONTROL_APPLY_TYPE as aplyType,WAREHOUSE_CODE as wareHouse,DEVICE_CODE as applyDeviceCode,STOCK_BARCODE as barCode,
            //CONTROL_APPLY_PARAMETER as PARAMETER,CONTROL_APPLY_PARA01 as PARA01,CONTROL_APPLY_PARA02 as PARA02,APPLY_TASK_STATUS as status,
            //CREATE_TIME as applyTime,CONTROL_APPLY_REMARK as REMARK
            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, "CONTROL_APPLY_ID");
                            break;
                        case "pplyDeviceIndex":
                            sort = string.Format(format, "DEVICE_CODE");
                            break;
                        default:
                            sort = string.Format(format, "CREATE_TIME");
                            break;
                    }
                }
                else
                {
                    switch (sort.Substring(1))
                    {
                        case "d":
                            sort = string.Format(format, "CONTROL_APPLY_ID");
                            break;
                        case "pplyDeviceIndex":
                            sort = string.Format(format, "DEVICE_CODE");
                            break;
                        default:
                            sort = string.Format(format, "CREATE_TIME");
                            break;
                    }
                }
            }
            else
            {
                sort = "CREATE_TIME";
            }
            #endregion
            #region  拼接 where 条件
            var where = "1=1";
            if (!string.IsNullOrEmpty(id))
            {
                where = $"{where} AND CONTROL_APPLY_ID = {id}";
            }
            if (!string.IsNullOrEmpty(wareHouse))
            {
                where = $"{where} AND WAREHOUSE_CODE = {wareHouse}";
            }
            if (!string.IsNullOrEmpty(applyDeviceCode))
            {
                where = $"{where} AND DEVICE_CODE = '{applyDeviceCode}'";
            }
            if (!string.IsNullOrEmpty(applyTimeFrom))
            {
                where = $"{where} AND CREATE_TIME >= '{applyTimeFrom}'";
            }
            if (!string.IsNullOrEmpty(applyTimeTo))
            {
                where = $"{where} AND CREATE_TIME <= '{applyTimeTo}'";
            }

            #endregion
            var sql = $"SELECT TOP {limit} CONTROL_APPLY_ID as id,CONTROL_APPLY_TYPE as aplyType,WAREHOUSE_CODE as wareHouse,DEVICE_CODE as applyDeviceCode,STOCK_BARCODE as barCode,CONTROL_APPLY_PARAMETER as PARAMETER,CONTROL_APPLY_PARA01 as PARA01,CONTROL_APPLY_PARA02 as PARA02,APPLY_TASK_STATUS as status,CREATE_TIME as applyTime,CONTROL_APPLY_REMARK as REMARK FROM (SELECT CONTROL_APPLY_ID,CONTROL_APPLY_TYPE,WAREHOUSE_CODE,DEVICE_CODE,STOCK_BARCODE,CONTROL_APPLY_PARAMETER,CONTROL_APPLY_PARA01,CONTROL_APPLY_PARA02,APPLY_TASK_STATUS,CREATE_TIME,CONTROL_APPLY_REMARK,ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  IO_CONTROL_APPLY_WCS WHERE   {where}) AS T WHERE ROW_NUM > {offset}";
            var sqlAll = $"SELECT CONTROL_APPLY_ID as id,CONTROL_APPLY_TYPE as aplyType,WAREHOUSE_CODE as wareHouse,DEVICE_CODE as applyDeviceCode,STOCK_BARCODE as barCode,CONTROL_APPLY_PARAMETER as PARAMETER,CONTROL_APPLY_PARA01 as PARA01,CONTROL_APPLY_PARA02 as PARA02,APPLY_TASK_STATUS as status,CREATE_TIME as applyTime,CONTROL_APPLY_REMARK as REMARK FROM (SELECT CONTROL_APPLY_ID,CONTROL_APPLY_TYPE,WAREHOUSE_CODE,DEVICE_CODE,STOCK_BARCODE,CONTROL_APPLY_PARAMETER,CONTROL_APPLY_PARA01,CONTROL_APPLY_PARA02,APPLY_TASK_STATUS,CREATE_TIME,CONTROL_APPLY_REMARK,ROW_NUMBER() OVER (ORDER BY CREATE_TIME) AS ROW_NUM FROM  IO_CONTROL_APPLY_WCS 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
                {
                    //CONTROL_APPLY_ID as id,CONTROL_APPLY_TYPE as aplyType,WAREHOUSE_CODE as wareHouse,DEVICE_CODE as applyDeviceCode,STOCK_BARCODE as barCode,
                    //CONTROL_APPLY_PARAMETER as PARAMETER,CONTROL_APPLY_PARA01 as PARA01,CONTROL_APPLY_PARA02 as PARA02,APPLY_TASK_STATUS as status,
                    //CREATE_TIME as applyTime,CONTROL_APPLY_REMARK as REMARK
                    id = Convert.ToInt32(dv[i]["id"]),
                    aplyType = Convert.ToString(dv[i]["aplyType"]),
                    wareHouse = Convert.ToString(dv[i]["wareHouse"]),
                    applyDeviceCode = Convert.ToString(dv[i]["applyDeviceCode"]),
                    barCode = Convert.ToString(dv[i]["barCode"]),
                    remark = Convert.ToString(dv[i]["REMARK"]),
                    status = Convert.ToString(dv[i]["status"]),
                    applyTime = Convert.ToString(dv[i]["applyTime"])
                };
            }
            return Ok(new { total = dv1.Count, items });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [HttpGet("wareHouse")]
        public IActionResult GetControlTaskKind()
        {
            var sql = $"select F_WarehouseIndex,F_Description from T_Warehouse ";

            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
                {
                    id = Convert.ToInt32(dv[i]["F_WarehouseIndex"]),
                    name = Convert.ToString(dv[i]["F_Description"]),
                };
            }
            return Ok(new { total = dv.Count, items });
        }

        /// <summary>
        /// 批量处理 删除
        /// </summary>
        /// <param name="data">id CONTROL_APPLY_ID</param>
        /// <returns></returns>
        [HttpPost("batch")]
        public IActionResult Batch(
            [FromMember] string[] data)
        {
            int count = 0;
            // 写入数据库
            foreach (var item in data)
            {
                var sql = $"delete from IO_CONTROL_APPLY_WCS where CONTROL_APPLY_ID={item}";
                if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                {
                    count++;
                }
            }
            if (count > 0)
            {
                return StatusCode(200);
            }
            else
            {
                return StatusCode(422);
            }

        }

        /// <summary>
        /// king kun
        /// </summary>
        /// <returns></returns>
        [HttpGet("wmsApply")]
        public IActionResult GetErrorApply()
        {
            var sql1 = $"select F_DeviceIndex,F_ErrorCode,F_DeviceName from T_Base_Device where F_ErrorCode !=0 and F_DeviceKindIndex in (1,2)";

            dv = CStaticClass.dbo.ExceSQL(sql1.ToString()).Tables[0].DefaultView;
            var items = new dynamic[dv.Count];
            for (int i = 0; i < dv.Count; i++)
            {
                items[i] = new
                {
                    device_code = Convert.ToInt32(dv[i]["F_DeviceIndex"]),
                    stock_barcode = Convert.ToString(dv[i]["F_DeviceName"]),
                    control_apply_remark = Convert.ToString(dv[i]["F_ErrorCode"]),
                };
            }
            return Ok(new { total = dv.Count, items });
        }
    }
}