MemberCenter/Member/MemberWeb/Controllers/UploadController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using Common.Model;
using CoreEntity.DAL;
using JCSoft.WX.Framework.Api;
using LigerRM.Common;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Hosting.Server;
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/upload")]
    [ApiController]
    public class UploadController : BaseController
    {
        private readonly IHostingEnvironment _hostingEnvironment;
        public UploadController(IHostingEnvironment hostingEnvironment, IMemoryCache cache, IApiClient client) : base(cache, client)
        {
            _hostingEnvironment = hostingEnvironment;
        }


        [HttpPost]
        [Route("uploadprofile")]
        public JsonResult UploadPrfile()
        {
            //var contentType = Request.ContentType;
            //var s = Request.HttpContext.ToString();
            var files = Request.Form.Files;

            foreach (var file in files)
            {
                var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
                

                string filePath = _hostingEnvironment.WebRootPath ;
                if (!Directory.Exists(filePath+ Path.DirectorySeparatorChar + "UploadFiles"+ Path.DirectorySeparatorChar))
                {
                    Directory.CreateDirectory(filePath + Path.DirectorySeparatorChar+"UploadFiles"+ Path.DirectorySeparatorChar);
                }
                var fileType = Path.GetExtension(fileName).Substring(1);               
                fileName = Guid.NewGuid() + Path.GetExtension(fileName);
                var vpath = "/UploadFiles/" + fileName;

                string fileFullName = filePath + vpath;

                using (FileStream fs = System.IO.File.Create(fileFullName))
                {
                    file.CopyTo(fs);
                    fs.Flush();
                }

                var sql = $"insert into sup_uploadfiles (FileName, FileType, Path, UploadDate, Comment,InUse) values (@FileName, @FileType, @Path, @UploadDate, @Comment,1);select SCOPE_IDENTITY()";
                var param = new List<SqlParameter>();

                param.Add(new SqlParameter("FileName", fileName));
                param.Add(new SqlParameter("FileType", fileType));
                param.Add(new SqlParameter("Path", vpath));
                param.Add(new SqlParameter("UploadDate", DateTime.Now));
                param.Add(new SqlParameter("Comment", string.Empty));

                DataAccess.GetOneValue(sql, param.ToArray(), out var result, out _);
                return new JsonResult(new
                {
                    success = true,
                    //path = result
                    path = vpath
                });
            }
            return new JsonResult(new
            {
                success = false,
                path = string.Empty
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("getdata")]
        public JsonResult getdata(string staffid, [FromBody] dynamic entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

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

            int pageIndex = 1;
            int pageSize = 15;
            string sortField;
            int sortDirection = 0;
            //string[] sumFields;
            if (entity != null)
            {
                if (entity.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(entity.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = entity.pageIndex;
                pageSize = entity.pageSize;
                sortField = entity.sortField;
                sortDirection = entity.sortDirection;
            }
            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)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(), out param,new Temp());
            }

            //var sql = $"select * from (select row_number() over (order by id) as rowNum,* from (select (select stuff((select ','+path from sup_UPLOADFILES  where RelativeId=a.ID and RelativeTable='k_contactsp' and InUse = 1  FOR xml PATH('')), 1, 1, '')) as field_file,a.ID, a.GoodsId, b.Contact, b.UserId, (select GoodsName from GOODSDOC where A.GoodsId = GOODSDOC.GoodsId) as GoodsName, (select ApprovalNo from GOODSATTR where A.GoodsId = GOODSATTR.GoodsId) as ApprovalNo from k_contactsp A left join CONTACTDOC B on a.ContactId = b.ContactId where UserId =  {userid}) R where 1=1 {queryCondition} ) RR where rowNum > {start} and rowNum < {end} ";

            var sql = $"select * from" +
                $"(select row_number() over(order by id) as rowNum," +
                $" * from(select(select stuff((select ',' + path from sup_UPLOADFILES where RelativeId = a.ID and RelativeTable = 'newgood' and InUse = 1 FOR xml PATH('')), 1, 1, '')) as field_file," +
                $" * from NEWGOOD A where UserId = {userid}) R where 1 = 1 {queryCondition} " +
                $") RR" +
                $" 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 countSql = $"select count(1) from(select a.ID from k_contactsp A left join CONTACTDOC B on a.ContactId = b.ContactId where UserId = {userid} {queryCondition} ) R";

            var countSql = $"select count(1) from(select ID from newgood where UserId = {userid} {queryCondition} ) R";
            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out _);

            return Json(new
            {
                items = result,
                sum = new { },
                totalCount = count
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("getalldata")]
        public JsonResult getalldata(string staffid, [FromBody] dynamic entity)
        {
            var userid = getStaffUserid(staffid);
            var RoleId = getStaff("roleid");
            //判断参数是否合法
            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 (entity != null)
            {
                if (entity.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(entity.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = entity.pageIndex;
                pageSize = entity.pageSize;
                sortField = entity.sortField;
                sortDirection = entity.sortDirection;
            }
            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)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(),out param, new Temp(), "R.");
            }

            //string StaffDocId = StaffDocDAL.GetStaffId(userid);
            //string filterPurRole = ((RoleId == "1" || RoleId == "6" || RoleId == "7" || RoleId == "8") ? "" :
            //    (" and A0.userid in" +
            //    " (select A1.UserId from K_SaleContact A left join ContactDoc A1 on A.K_ContactId = A1.ContactId " +
            //    "where A.SaleManId = '" + StaffDocId + "' and A1.Beactive = 'Y')  "));

            //var sql = $"select * from (select row_number() over (order by id) as rowNum,* from (select (select stuff((select ','+path from sup_UPLOADFILES  where RelativeId=a.ID and RelativeTable='k_contactsp' and InUse = 1 FOR xml PATH('')), 1, 1, '')) as field_file,a.ID, a.GoodsId, b.Contact, b.UserId, (select GoodsName from GOODSDOC where A.GoodsId = GOODSDOC.GoodsId) as GoodsName, (select ApprovalNo from GOODSATTR where A.GoodsId = GOODSATTR.GoodsId) as ApprovalNo, case (select top 1 1 from sup_UPLOADFILES  where RelativeId=a.ID and RelativeTable='k_contactsp' and InUse =1 ) when 1 then 0 else 1 end  as state from k_contactsp A left join CONTACTDOC B on a.ContactId = b.ContactId) R where 1=1 {queryCondition} )RR where rowNum > {start} and rowNum < {end} ";

            var filter = FilterTranslator.ruleSql(ref param);

            var sql = $"select * from" +
                $"(select row_number() over(order by id) as rowNum," +
                $" * from" +
                $"(select(select stuff((select ',' + path from sup_UPLOADFILES where RelativeId = A0.ID and RelativeTable = 'newgood' and InUse = 1 FOR xml PATH('')), 1, 1, '')) as field_file," +
                $" * " +
                $"from NEWGOOD A0 where 1=1 " +
             filter +
                $") R where 1 = 1 {queryCondition} " +
                $") RR 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 countSql = $"select count(1) from(select a.ID from k_contactsp A left join CONTACTDOC B on a.ContactId = b.ContactId where 1=1 {queryCondition}  ) R";
            var countSql = $"select count(1) from newgood A0 " +
                $"where 1=1  {queryCondition} " +
            filter +
                $" ";
            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out var resultstr);

            return Json(new
            {
                items = result,
                sum = new { },
                totalCount = count,
                msg = resultstr
            });
        }

        [HttpPost]
        [Route("update")]
        public JsonResult update([FromServices]IHostingEnvironment env, string staffid, [FromBody] dynamic entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }


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

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

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

                if(value.ToString() == "")
                {

                }else
                foreach (var item in l)
                {
                    var param = new List<SqlParameter>();
                    var sql = $"update sup_UPLOADFILES set RelativeTable='newgood' ,RelativeId= @RelativeId,InUse=1 where path=@path";
                    param.Add(new SqlParameter("RelativeId", (int)entity.id));
                    var path = item.Replace("\\", "/");
                    param.Add(new SqlParameter("path", path));
                    if (DataAccess.ExecuteCommand(sql, param.ToArray(), out msg) < 1)
                    {
                        flag = false;
                    }
                }
                //删除无用的文件
                var sqlDel = $"select path from sup_UPLOADFILES where RelativeTable='newgood' and RelativeId= @RelativeId and InUse=0;";
                var paramDel = new List<SqlParameter>();
                paramDel.Add(new SqlParameter("RelativeId", (int)entity.id));
                var dt = new DataTable();
                DataAccess.GetValues(sqlDel, ref dt, paramDel.ToArray(), out msg);
                if(dt != null && dt.Rows.Count > 0)
                {
                    for(var k = 0; k < dt.Rows.Count; k++)
                    {
                        string path = (string)dt.Rows[k]["path"];
                        path = path.ToString().Replace('/',Path.DirectorySeparatorChar);
                        System.IO.File.Delete(env.WebRootPath+ path);
                    }
                }
            }

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


        [HttpPost]
        [Route("download")]
        public FileResult download(string filePath)
        {
            if (string.IsNullOrEmpty(filePath))
            {
                return null;
            }
            string path = _hostingEnvironment.WebRootPath + "/" + filePath;
            string fileName = " ";
            byte[] fileBytes = System.IO.File.ReadAllBytes(path);
            return File(fileBytes, "application/octet-stream", fileName);
        }


        public class Temp
        {
            private string _field_file;
            public int ID { get; set; }
            public string FileId { get; set; }
            public string GoodsId { get; set; }
            public string Contact { get; set; }
            public int UserId { get; set; }
            public string GoodsName { get; set; }
            public string ApprovalNo { get; set; }
            public int pageIndex { get; set; }
            public string sortField { get; set; }
            public int pageSize { get; set; }
            public int sortDirection { get; set; }
            //public dynamic filters { get; set; }
            public string field_file { get { return _field_file == null ? "" : _field_file; } set => _field_file = value; }
            public string name { get; set; }
            public string path { get; set; }
            public bool state { get; set; }

            public string UserName { get; set; }
        }
    }


}