test/Member/SupplierWeb/Controllers/SupDemandController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Common.Model;
using CoreEntity.DAL;
using JCSoft.WX.Framework.Api;
using log4net;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using PublicLibrary.Model;
using SupplierWeb.Codes.mvc;
using ZcPeng.PublicLibrary;

namespace SupplierWeb.Controllers
{
    [Route("web/supdemand")]
    [ApiController]
    public class SupDemandController : BaseController
    {
        private readonly IHostingEnvironment _hostingEnvironment;
        public SupDemandController(IHostingEnvironment hostingEnvironment, IMemoryCache cache, IApiClient client) : base(cache, client)
        {
            _hostingEnvironment = hostingEnvironment;
        }
        [AuthPermission]
        [HttpPost]
        [Route("getdata")]
        public JsonResult GetData(string staffid, [FromBody] dynamic data)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆",
                    timeout = 1
                });
            }
            List<QueryFilter> filterList = new List<QueryFilter>();

            int pageIndex = 1;
            int pageSize = 15;
            string sortField;
            int sortDirection = 0;
            string[] sumFields;
            if (data != null)
            {
                if (data.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(data.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            var isAce = sortDirection == 0;
            var queryCondition = string.Empty;
            var param = new List<SqlParameter>();
            var start = (pageIndex - 1) * pageSize;
            var end = (start + 1 + pageSize);
            var dt = new DataTable();

            if (filterList != null)
            {
                foreach (var item in filterList)
                {
                    if (!string.IsNullOrEmpty(item.value))
                    {
                        switch (item.operate)
                        {
                            case "equal":
                                queryCondition += $" and {item.field}=@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "notequal":
                                queryCondition += $" and {item.field}<>@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "less":
                                queryCondition += $" and {item.field}<@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "lessorequal":
                                queryCondition += $" and {item.field}<=@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "greater":
                                queryCondition += $" and {item.field}>@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "greateroreeuqal":
                                queryCondition += $" and {item.field}>=@{item.field} ";
                                param.Add(new SqlParameter(item.field, item.value));
                                break;
                            case "contains":
                                queryCondition += $" and {item.field} like @{item.field} ";
                                param.Add(new SqlParameter("@" + item.field, "%" + item.value + "%"));
                                break;
                            case "startswith":
                                queryCondition += $" and {item.field} like @{item.field} ";
                                param.Add(new SqlParameter("@" + item.field, item.value + "%"));
                                break;
                            case "endswith":
                                queryCondition += $" and {item.field} like @{item.field} ";
                                param.Add(new SqlParameter("@" + item.field, "%" + item.value));
                                break;
                        }
                    }
                }
            }

            var sql =
                $"select * from (select *,row_number() over (order by ID desc) as rowNum,(stuff((select ',' + path from sup_UPLOADFILES where RelativeId = c.id and RelativeTable = 'sup_PurchaseDemand' and InUse = 1 FOR xml PATH('')), 1, 1, '')) as files from sup_PurchaseDemand C where 1=1  {queryCondition}) R where rowNum > {start} and rowNum < {end}";


            DataAccess.GetValues(sql, ref dt, param.ToArray(), out _);


            IList<Temp> result = new List<Temp>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<Temp>.ConvertToModel(dt);
            }

            var jsonData = JsonConvert.SerializeObject(result);

            var countSql =
                $"select count(1) from (select * from sup_PurchaseDemand C where 1=1  {queryCondition}) R";

            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out _);

            return Json(new
            {
                items = result,
                sum = new { },
                totalCount = count
            });
        }
        [AuthPermission]
        [HttpPost]
        [Route("add")]
        public JsonResult Add(string staffid, [FromBody] dynamic entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }

            string state = "0";
            if(entity != null)
            {
                if(entity.state != null )
                state = entity.state.ToString();
            }

            var sql0 = "select * from sup_Account where id = @userid";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var userName = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                userName = dt0.Rows[0]["AccountRealName"].ToString();
            }

            var sql =
                $"insert into sup_PurchaseDemand (Title, Content, CreatorID, CreatorName, CreateDate, State, LastModifier, LastModifierID, LastModifyDate) values (@Title, @Content, @CreatorID, @CreatorName, @CreateDate, @State, @LastModifier, @LastModifierID, @LastModifyDate);" +
                $"SELECT IDENT_CURRENT('sup_PurchaseDemand') as newid";
            var param = new List<SqlParameter>();

            param.Add(new SqlParameter("Title", entity.title.ToString()));
            param.Add(new SqlParameter("Content", entity.content.ToString()));
            param.Add(new SqlParameter("CreatorID", userid));
            param.Add(new SqlParameter("CreatorName", userName));
            param.Add(new SqlParameter("CreateDate", DateTime.Now));
            param.Add(new SqlParameter("State", state));
            param.Add(new SqlParameter("LastModifier", userName));
            param.Add(new SqlParameter("LastModifierID", userid));
            param.Add(new SqlParameter("LastModifyDate", DateTime.Now));
            //DataAccess.ExecuteCommand(sql, param, out var msg);
            DataAccess.GetOneValue(sql, param.ToArray(), out var result, out var msg);


            JObject obj = JsonConvert.DeserializeObject<JObject>(JsonConvert.SerializeObject(entity));
            //int id = BaseDAL.GetId("sup_PurchaseDemand");
            int id = Decimal.ToInt32((decimal)result);
            var flag = true;
            if (obj.TryGetValue("files", out var value))
            {
                var l = value.ToString().Split(',');

                var param1 = new List<SqlParameter>();
                var s = "update sup_UPLOADFILES set InUse =0 where RelativeId= @RelativeId and RelativeTable='sup_PurchaseDemand';";
                param1.Add(new SqlParameter("RelativeId", id));

                if (DataAccess.ExecuteCommand(s, param1.ToArray(), out _) < 1)
                {
                    flag = false;
                }

                foreach (var item in l)
                {
                    var param2 = new List<SqlParameter>();
                    var sql2 = $"update sup_UPLOADFILES set RelativeTable='sup_PurchaseDemand' ,RelativeId= @RelativeId,InUse=1 where path=@path";
                    param2.Add(new SqlParameter("RelativeId", id));
                    var path = item.Replace("\\", "/");
                    param2.Add(new SqlParameter("path", path));
                    if (DataAccess.ExecuteCommand(sql2, param2.ToArray(), out _) < 1)
                    {
                        flag = false;
                    }
                }
            }

            return Json(new
            {
                success = true,
                msg
            });
        }

        private ILog log = LogManager.GetLogger(Startup.Repository.Name, typeof(SupDemandController));
        [AuthPermission]
        [HttpPost]
        [Route("delete")]
        public JsonResult Delete([FromServices]IHostingEnvironment env, int id)
        {
            //var userid = getStaffUserid(staffid);
            ////判断参数是否合法
            //if (string.IsNullOrEmpty(userid))
            //{
            //    return Json(new
            //    {
            //        success = 0,
            //        msg = "没有登陆"
            //    });
            //}
            List<DataAccessCommand> list = new List<DataAccessCommand>(2);

            //删除文件
            var sql0 = "select Path from sup_UPLOADFILES where RelativeId = @RelativeId  and RelativeTable='sup_PurchaseDemand';";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("RelativeId", id));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var filePath = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                for(int i = 0; i< dt0.Rows.Count; i=i+1) { 
                    filePath = dt0.Rows[i]["Path"].ToString();
                    log.Info("env.WebRootPath:" + env.WebRootPath);
                    System.IO.File.Delete(Path.Combine(env.WebRootPath, filePath.Replace('/', Path.DirectorySeparatorChar)));
                }
            }

            
            //删除记录
            var sql = $"delete sup_PurchaseDemand where ID = @ID";
            var param = new List<SqlParameter>();

            param.Add(new SqlParameter("ID", id));
            //DataAccess.ExecuteCommand(sql, param, out var msg);
            list.Add(new DataAccessCommand(sql, param,CommandType.Text,true));

            var param1 = new List<SqlParameter>();
            var sql1 = "delete sup_UPLOADFILES where RelativeId= @RelativeId and RelativeTable='sup_PurchaseDemand';";
            param1.Add(new SqlParameter("RelativeId", id));
            //DataAccess.ExecuteCommand(sqlFil, paramFile, out var msgFile);
            list.Add(new DataAccessCommand(sql1, param1, CommandType.Text, false));

            bool success = DataAccess.ExecuteBatchCommands(list,out var result);
            return Json(new
            {
                success = success,
                msg = result
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("edit")]
        public JsonResult Edit(string staffid, [FromBody] dynamic entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }

            var sql0 = "select * from sup_Account where id = @userid";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var userName = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                userName = dt0.Rows[0]["AccountRealName"].ToString();
            }

            var sql =
                $"update sup_PurchaseDemand set Title=@Title, Content=@Content,Comment=@Comment, State=@State ,LastModifier=@LastModifier, LastModifierID=@LastModifierID, LastModifyDate=@LastModifyDate where ID = @ID";
            var param = new List<SqlParameter>();

            param.Add(new SqlParameter("Title", entity.title.ToString()));
            param.Add(new SqlParameter("Content", entity.content.ToString()));
            param.Add(new SqlParameter("Comment", entity.comment.ToString()));
            param.Add(new SqlParameter("State", entity.state.ToString()));
            param.Add(new SqlParameter("ID", entity.id.ToString()));
            param.Add(new SqlParameter("LastModifier", userName));
            param.Add(new SqlParameter("LastModifierID", userid));
            param.Add(new SqlParameter("LastModifyDate", DateTime.Now));
            DataAccess.ExecuteCommand(sql, param, out var msg);

            JObject obj = JsonConvert.DeserializeObject<JObject>(JsonConvert.SerializeObject(entity));
            var flag = true;
            if (obj.TryGetValue("files", out var value))
            {
                var l = value.ToString().Split(',');

                var param1 = new List<SqlParameter>();
                var s = "update sup_UPLOADFILES set InUse =0 where RelativeId= @RelativeId and RelativeTable='sup_PurchaseDemand';";
                param1.Add(new SqlParameter("RelativeId", (int)entity.id));

                if (DataAccess.ExecuteCommand(s, param1.ToArray(), out _) < 1)
                {
                    flag = false;
                }

                foreach (var item in l)
                {
                    var param2 = new List<SqlParameter>();
                    var sql2 = $"update sup_UPLOADFILES set RelativeTable='sup_PurchaseDemand' ,RelativeId= @RelativeId,InUse=1 where path=@path";
                    param2.Add(new SqlParameter("RelativeId", (int)entity.id));
                    var path = item.Replace("\\", "/");
                    param2.Add(new SqlParameter("path", path));
                    if (DataAccess.ExecuteCommand(sql2, param2.ToArray(), out _) < 1)
                    {
                        flag = false;
                    }
                }
            }

            return Json(new
            {
                success = true,
                msg
            });
        }
        [AuthPermission]
        [HttpGet]
        [Route("getlatestdata")]
        public JsonResult GetLatestData()
        {
            var sql = $"select top 5 * from sup_PurchaseDemand where State =1 order by LastModifyDate desc ";

            var param = new List<SqlParameter>();
            var dt = new DataTable();
            DataAccess.GetValues(sql, ref dt, param.ToArray(), out _);


            IList<Temp> result = new List<Temp>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<Temp>.ConvertToModel(dt);
            }

            var jsonData = JsonConvert.SerializeObject(result);

            return Json(new
            {
                items = result,
                success = true
            });
        }
        public class Temp
        {
            private string files;
            public int ID { get; set; }
            public string GoodsName { get; set; }
            public int Number { get; set; }
            public int CreatorID { get; set; }
            public string CreatorName { get; set; }
            public DateTime CreateDate { get; set; }
            public string Comment { get; set; }
            public bool State { get; set; }
            public DateTime LastModifyDate { get; set; }
            public int LastModifierID { get; set; }
            public string LastModifier { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
            public string Files
            {
                get
                {
                    if (string.IsNullOrEmpty(files))
                        return "";
                    return files;
                }
                set { files = value; }
            }

        }
    }
}