test/Member/SupplierWeb/Controllers/SupplyOrderShipController.cs

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

namespace SupplierWeb.Controllers
{
    [Route("web/supplyordership")]
    public class SupplyOrderShipController : BaseController
    {
        public SupplyOrderShipController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }

        ////查询采购订单（生成的，提交的）未发货
        [AuthPermission]
        [HttpPost, Route("purchaseorder/{staffId}")]
        public JsonResult purchaseorder(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            int PushAccountId = Convert.ToInt32(userids);


            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderMTEx(),"A.");
            filterstring += " and not exists (select PurOrderBillNo from " + Config.TablePrefix + "PurOrderShipment where PurOrderBillNo = A.BillNo) " +
                " and A.K_ContactId in (select ContactId from  ContactDoc where UserId = '" + PushAccountId + "')";
            filterstring = filterstring.Replace("and A.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and A.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and A.BusinessName", "and E.BusinessName");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select A.*," +
               //"B.AccountRealName," +
               "C.StaffName,E.BusinessName,F.Contact,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderMT A " +
                //" left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId and C.EntId = A.EntId " +
                " left join BusinessDoc E on E.BusinessId=A.SuppliersId and E.Is_Supp = 'Y' and E.EntId = A.EntId  " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId  and F.EntId = A.EntId " +
                //" left join ContactDoc F1 on F.ContactId=A.OppContId " +
                " where 1=1 " +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderMTEx> users = new List<PurOrderMTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderMTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        ////查看采购单的发货状态，查看物流进度  已发货
        [AuthPermission]
        [HttpPost, Route("ordershipment/{staffId}")]
        public JsonResult ordershipment(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            int PushAccountId = Convert.ToInt32(userids);

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderMTEx(),"A.");
            filterstring += " and exists (select PurOrderBillNo from " + Config.TablePrefix + "PurOrderShipment where PurOrderBillNo = A.BillNo) " +
                " and A.K_ContactId in (select ContactId from  ContactDoc where UserId = '" + PushAccountId + "')";
            filterstring = filterstring.Replace("and A.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and A.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and A.BusinessName", "and E.BusinessName");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
                "select A.*,B.AccountRealName,C.StaffName,E.BusinessName,F.Contact,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderMT A " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId  and C.EntId = A.EntId " +
                " left join supplyDoc D on A.SuppliersId=D.SuppliersId  and D.EntId = A.EntId " +
                " left join BusinessDoc E on E.BusinessId=D.SuppliersId and E.Is_Supp = 'Y'  and E.EntId = D.EntId " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId  and F.EntId = A.EntId " +
                //" left join ContactDoc F1 on F.ContactId=A.OppContId " +
                " where 1=1 " +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderMTEx> users = new List<PurOrderMTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderMTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        ////订单发货后在订单发货管理中 录入物流单号
        [AuthPermission]
        [HttpPost, Route("addshipment")]
        public JsonResult AddShipment(char k_isbh, string k_kdcode, string k_qrdates, char k_xspay,
            [FromBody] dynamic data)
        {
            //string BillSn=string.Empty;
            var temp = (data.BillNo as JArray)?.ToArray();
            if (data != null)
            {
                k_isbh = data.k_isbh == null ? 'N' : data.k_isbh;
                k_kdcode = data.k_kdcode;
                k_qrdates = data.k_qrdates == null ? DateTime.Now.ToString("yyyy-MM-dd") : DateTime.ParseExact(data.k_qrdates.ToString(), "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture).ToString("yyyy-MM-dd");
                k_xspay = data.k_xspay == null ? 'N' : data.k_xspay;
                //BillNo = (data.BillNo as JArray).ToArray();
                //BillSn = data.BillSn;
            }

            //#region 添加是否备货 物流单号 发货时间
            //string commandTextRelate = "UPDATE  PurOrderMT " +
            //     " SET " +
            //    " k_isbh = @k_isbh," +
            //    " k_kdcode = @k_kdcode" +
            //    " k_qrdates = @k_qrdates" +
            //    " k_xspay = @k_xspay" +
            //     " WHERE " +
            //     "BillNo = @BillNo";

            //string result;
            ////准备参数
            //List<List<Object>> parametersRelate = new List<List<Object>>();
            //parametersRelate.Add(new List<Object>() { "BillNo", BillNo });
            //parametersRelate.Add(new List<Object>() { "k_isbh", k_isbh });
            //parametersRelate.Add(new List<Object>() { "k_kdcode", k_kdcode });
            //parametersRelate.Add(new List<Object>() { "k_qrdates", k_qrdates });
            //parametersRelate.Add(new List<Object>() { "k_xspay", k_xspay });

            //List<SqlParameter> parametersRelate1 = DataAccess.ToParameters(parametersRelate);
            //int success = DataAccess.ExecuteCommand(commandTextRelate, parametersRelate1, out result);
            //#endregion
            if (temp.Length > 0)
            {
                foreach (var item in temp)
                {
                    string BillNo = item["BillNo"]?.ToString();
                    string BillSn = item["BillSn"]?.ToString();
                    #region 添加发货记录
                    string commandText = "INSERT INTO " + Config.TablePrefix + "PurOrderShipment " +
                    "(PurOrderBillNo,k_isbh,k_kdcode,k_qrdates,k_xspay)"
                    + " VALUES (@PurOrderBillNo,@k_isbh,@k_kdcode,@k_qrdates,@k_xspay)";
                    string resultInsert;
                    //准备参数
                    List<List<Object>> parameters = new List<List<Object>>();
                    parameters.Add(new List<Object>() { "PurOrderBillNo", BillNo });
                    parameters.Add(new List<Object>() { "k_isbh", k_isbh });
                    parameters.Add(new List<Object>() { "k_kdcode", k_kdcode });
                    parameters.Add(new List<Object>() { "k_qrdates", k_qrdates });
                    parameters.Add(new List<Object>() { "k_xspay", k_xspay });

                    List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
                    int successInsert = DataAccess.ExecuteCommand(commandText, parameters1, out resultInsert);
                    #endregion
                    int ShipmentId = BaseDAL.GetId(Config.TablePrefix + "PurOrderShipment");


                    #region 添加发货明细
                    string commandTextOrderDT = "INSERT INTO " + Config.TablePrefix + "PurShipmentOrderDT " +
                        "(BillNo,ShipmentId,BillSn)"
                        + " VALUES (@BillNo,@ShipmentId,@BillSn)";
                    string resultInsertOrderDT;
                    //准备参数
                    List<List<Object>> parametersOrderDT = new List<List<Object>>();
                    parametersOrderDT.Add(new List<Object>() { "BillNo", BillNo });
                    parametersOrderDT.Add(new List<Object>() { "ShipmentId", ShipmentId });
                    parametersOrderDT.Add(new List<Object>() { "BillSn", BillSn });
                    List<SqlParameter> parametersOrderDT1 = DataAccess.ToParameters(parametersOrderDT);
                    int successInsertOrderDT = DataAccess.ExecuteCommand(commandTextOrderDT, parametersOrderDT1, out resultInsertOrderDT);
                    #endregion
                }

                return Json(new
                {
                    success = true,
                    //success1 = success,
                    //result = result,
                    //successInsert = successInsert,
                    //resultInsert = resultInsert,
                    //successInsertOrderDT = successInsertOrderDT,
                    //resultInsertOrderDT = resultInsertOrderDT,
                });
            }
            return Json(new
            {
                success = false
            });
        }

        ////查询采购订单明细 
        [AuthPermission]
        [HttpPost, Route("purchaseorderdt/{staffId}")]
        public JsonResult purchaseorderdt(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] {  },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            int PushAccountId = Convert.ToInt32(userids);
            if (filters.Length == 0)
            {
                return Json(new
                {
                    items = new string[] {  },
                    sum = new { },
                    totalCount = 0
                });
            }

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderDTEx(),"G.");
            filterstring += " " +
                " and A.K_ContactId in (select ContactId from  ContactDoc where UserId = '" + PushAccountId + "')";
            filterstring = filterstring.Replace("and G.GoodsName", "and H.GoodsName");
            filterstring = filterstring.Replace("and G.GoodsCode", "and H.GoodsCode");
            filterstring = filterstring.Replace("and G.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and G.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and G.BusinessName", "and E.BusinessName");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select G.*,A.OppContId,A.SuppliersId,B.AccountRealName,C.StaffName," +
               "E.BusinessName,F.Contact," +
               "H.GoodsName,H.GoodsCode,H.GoodsSpec,H.Manufacturer," +
               "row_number() over" +
                "(  order by G." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderDT G left join PurOrderMT A on G.BillNo = A.BillNo  and A.EntId = G.EntId " +
                " left join GoodsDoc H on H.GoodsId=G.GoodsId  and H.EntId = G.EntId " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId and C.EntId = A.EntId " +
                " left join supplydoc D on A.SuppliersId=D.SuppliersId  and D.EntId = A.EntId " +
                " left join BusinessDoc E on E.BusinessId=D.SuppliersId and E.Is_Supp = 'Y'  and E.EntId = D.EntId " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId  and F.EntId = A.EntId " +
                //" left join ContactDoc F1 on F.ContactId=A.OppContId " +
                " where 1=1 " +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderDTEx> users = new List<PurOrderDTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderDTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }

        //订单明细 
        [AuthPermission]
        [HttpPost, Route("orderdtshipment/{staffId}")]
        public JsonResult orderdtshipment(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] {  },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            int PushAccountId = Convert.ToInt32(userids);
            //if (filters.Length == 0)
            //{
            //    return Json(new
            //    {
            //        items = new string[] {  },
            //        sum = new { },
            //        totalCount = 0
            //    });
            //}

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderDTEx(),"G.");
            filterstring += " " +
                " and A.K_ContactId in (select ContactId from  ContactDoc where UserId = '" + PushAccountId + "')";
            filterstring = filterstring.Replace("and G.GoodsName", "and H.GoodsName");
            filterstring = filterstring.Replace("and G.GoodsCode", "and H.GoodsCode");
            filterstring = filterstring.Replace("and G.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and G.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and G.BusinessName", "and E.BusinessName");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select G.*,A.OppContId,A.SuppliersId,B.AccountRealName,C.StaffName," +
               "E.BusinessName,F.Contact," +
               "H.GoodsName,H.GoodsCode,H.GoodsSpec,H.Manufacturer,X.k_kdcode," +
               "row_number() over" +
                "(  order by G." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderDT G left join PurOrderMT A on G.BillNo = A.BillNo  and A.EntId = G.EntId " +
                " left join GoodsDoc H on H.GoodsId=G.GoodsId  and H.EntId = G.EntId " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId " +
                " left join StaffDoc C on A.SaleManId=C.StaffId  and C.EntId = A.EntId " +
                " left join supplydoc D on A.SuppliersId=D.SuppliersId and D.EntId = A.EntId " +
                " left join BusinessDoc E on E.BusinessId=D.SuppliersId and E.Is_Supp = 'Y' and E.EntId = D.EntId " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId and F.EntId = A.EntId " +
                //" left join ContactDoc F1 on F.ContactId=A.OppContId " +
                "left join sup_PurShipmentOrderDT Z on G.BillNo = Z.BillNo and g.BillSn=z.BillSn " +
                "left join sup_PurOrderShipment X on Z.ShipmentId=X.Id " +
                " where 1=1 " +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderDTEx> users = new List<PurOrderDTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderDTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = totalcount
            });
        }




    }

}