test/Member/SupplierWeb/Controllers/PurchasePlanController.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 CoreEntity.DAL;
using Common.Model;
using PublicLibrary.Model;
using System.Data;
using System.Data.SqlClient;
using CoreEntity.Entity;
using ZcPeng.PublicLibrary;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json;
using SupplierWeb.Codes.mvc;

namespace SupplierWeb.Controllers
{
    [Route("web/purchaseplan")]
    public class PurchasePlanController : Controller
    {
        public PurchasePlanController()
        {

        }

        ////查询采购计划（生成的）
        [AuthPermission]
        [HttpPost,Route("index")]
        public JsonResult index(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            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 PurPlanMTExt(), "A.");
            //filterstring += " and A.BillState !=1 ";
            filterstring = filterstring.Replace("A.AccountRealName", "B.AccountRealName");
            filterstring = filterstring.Replace("A.PurRealName", "C.AccountRealName");
            filterstring = filterstring.Replace("A.BusinessName", "E.BusinessName");
            filterstring = filterstring.Replace("A.IsPrepayI", " Case A.IsPrepay When 'Y' Then 1 Else 0 End ");

            #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.*,Case A.IsPrepay When 'Y' Then 1 Else 0 End as IsPrepayI,B.AccountRealName,C.AccountRealName as PurRealName,E.BusinessName,row_number() over" +
                "(  order by A."+ sortField + " " + direct + " )  as rownum" +
                "  from  PurPlanMT A " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join " + Config.TablePrefix + "Account C on A.PurObj=C.Id" +
                " left join  BusinessDoc E on E.BusinessId=A.SuppliersId and E.Is_Supp = 'Y' and E.EntId = 'E1WB67UEYPG'" +
                " 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<PurPlanMTExt> users = new List<PurPlanMTExt>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurPlanMTExt>.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("dt")]
        public JsonResult dt(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            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 PurPlanDtExt(),"A.");
            filterstring = filterstring.Replace("A.AccountRealName", "B.AccountRealName");
            filterstring = filterstring.Replace("A.PurRealName", "D.AccountRealName");
            filterstring = filterstring.Replace("A.GoodsName", "F.GoodsName");
            filterstring = filterstring.Replace("A.UniKey", "concat(A.BillNo, A.BillSn)");
            filterstring = filterstring.Replace("A.BillState", "C.BillState");

            sortField = "A."+ sortField;
            sortField = sortField.Replace("A.UniKey", "concat(A.BillNo, A.BillSn)");

            //string joinstring = " and C.BillState !=1 ";

            #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.*,concat(A.BillNo ,A.BillSn) as UniKey,B.AccountRealName,D.AccountRealName as PurRealName,F.GoodsName,row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum" +
                "  from  PurPlanDT A " +
                " left join PurPlanMT C on C.BillNo=A.BillNo and C.EntId = A.EntId "+
                //joinstring +
                " left join " + Config.TablePrefix + "Account B on C.SaleManId=B.PurStaffId" +
                " left join " + Config.TablePrefix + "Account D on C.PurObj=D.Id" +
                " left join  GoodsDOC F on F.GoodsId=A.GoodsId and F.EntId = 'E1WB67UEYPG'" +
                " 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<PurPlanDtExt> users = new List<PurPlanDtExt>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurPlanDtExt>.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]
        [HttpGet,Route("newcategorydoc")]
        public ActionResult NewCategoryDoc(string echostr, string signature, string timestamp, string nonce)
        {

            #region 获取
            //WechatHelper.ReGetOpenId(HttpContext);
            #endregion
            return View();
        }

        ////查看供应商，业务员提交的新品种
        [AuthPermission]
        [HttpGet,Route("newcategory")]
        public ActionResult NewCategory(string echostr, string signature, string timestamp, string nonce)
        {

            #region 获取Openid
            //WechatHelper.ReGetOpenId(HttpContext);
            #endregion
            return View();
        }
        /////////////

        ///收货模式列表
        [HttpGet, Route("receiptmds")]
        public JsonResult ReceiptMds()
        {
            List<Option> options = PushRecordDAL.getOptionReceipt();
           
            return Json(options
            );
        }

        ///支付模式列表
        [HttpGet, Route("centpaymds")]
        public JsonResult CentPayMds()
        {
            List<Option> options = PushRecordDAL.getOptionCentPay();

            return Json(options
            );
        }


        ///机构列表
        [HttpGet, Route("orgs")]
        public JsonResult Orgs()
        {
            List<OrgDoc> roles = OrgDAL.getOrg();

            IList<Option> options = new List<Option>();
            foreach (OrgDoc role in roles)
            {
                var option = new Option() { label = role.ORGNAME, value = role.ORGID, disabled = (role.BEACTIVE.Equals("N")) };
                options.Add(option);
            }
            return Json(options
            );
        }

        ///部门列表
        [HttpGet, Route("depts")]
        public JsonResult Depts()
        {
            List<DeptDoc> roles = DeptDAL.getDept();

            IList<Option> options = new List<Option>();
            foreach (DeptDoc role in roles)
            {
                var option = new Option() { label = role.DEPARTMENT, value = role.DEPTID, disabled = (role.BEACTIVE.Equals("N")) };
                options.Add(option);
            }
            return Json(options
            );
        }

    }

}