DS7/DSWeb/Areas/OA/Controllers/Wms_StockTakeController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using DSWeb.Areas.OA.Models.Wms_StockTake;
using DSWeb.Areas.CommMng.Models;
using DSWeb.TruckMng.Helper;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.TruckMng.Comm.Cookie;

namespace DSWeb.Areas.OA.Controllers
{
    /// <summary>
    /// 库存综合查询
    /// </summary>
    public class Wms_StockTakeController : Controller
    {
        //
        // GET: /Import/RptImportFeedetail/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Edit()
        {
            return View();
        }
        
        #region 按入库单

        public ContentResult GetDataList(int start, int limit, string sort, string condition)
        {
            var dataList = GetDataList(condition,sort, CookieConfig.GetCookie_UserId(Request), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]));

            var list = dataList.Skip(start).Take(limit);
            
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
            return new ContentResult() { Content = json };
        }

        private static List<Wms_StockTakemb> GetDataList(string strCondition, string sort, string userid, string username, string companyid)
        {
            /**
            var rangstr = GetRangDAStr("index", userid, username, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
           */
            var strSql = new StringBuilder();
            strSql.Append(" select vws.*,wms.AREANAME");
            strSql.Append(",(select name from company where gid=vws.corpid) as COMPANYNAME");
            strSql.Append(",vs.ETD,vs.mblno,vs.custno,ISBONDEDNAME=(case when vws.ISBONDED=1 then '是' else '否' end) ");//是否保税：SR2017062600003
            strSql.Append(",dbo.F_WMSINCOUNTRY_CN(NID) COUNTRYOFORIGIN ");
            //解决综合查询结果为重复的两条的问题
            //strSql.Append(" from vw_wms_stocktake as vws left join v_op_bs as vs on vs.BSNO=vws.ASSOCIATEDNO ");// 
            strSql.Append(" from vw_wms_stocktake as vws left join v_op_bs as vs on vs.BSNO=vws.ASSOCIATEDNO left join wms on vws.NID=WMS.GID ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            return SetData(strSql);
        }

        private static List<Wms_StockTakemb> SetData(StringBuilder strSql)
        {
            var headList = new List<Wms_StockTakemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new Wms_StockTakemb();
                    #region Set DB data to Object

                    data.WMSDATE = Convert.ToString(reader["WMSDATE"]);
                    data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
                    data.MBLNO = Convert.ToString(reader["MBLNO"]);
                    data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
                    data.WMSNO = Convert.ToString(reader["WMSNO"]);
                    data.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
                    data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
                    data.GOODSMODEL = Convert.ToString(reader["GOODSMODEL"]);
                    data.hj_GOODSRKSL = Convert.ToString(reader["hj_GOODSRKSL"]);
                    data.hj_GOODSPFSL_OUT = Convert.ToString(reader["hj_GOODSPFSL_OUT"]);
                    data.hj_GOODSSTOCK = Convert.ToString(reader["hj_GOODSSTOCK"]);
                    data.hj_GOODSPACK = Convert.ToString(reader["hj_GOODSPACK"]);
                    data.hj_GOODSPACKPFSL = Convert.ToString(reader["hj_GOODSPACKPFSL"]);
                    data.hj_GOODSPACKSTOCK = Convert.ToString(reader["hj_GOODSPACKSTOCK"]);
                    data.GID = Convert.ToString(reader["GID"]);
                    data.ASSOCIATEDNO = Convert.ToString(reader["ASSOCIATEDNO"]);
                    data.CONTRACTNO = Convert.ToString(reader["CONTRACTNO"]);
                    data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
                    data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]);
                    data.GOODSGRADE = Convert.ToString(reader["GOODSGRADE"]);
                    data.GOODSSLICE = Convert.ToString(reader["GOODSSLICE"]);
                    data.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
                    data.REMARK = Convert.ToString(reader["REMARK"]);
                    data.hj_GOODSPACKACTUAL = Convert.ToString(reader["hj_GOODSPACKACTUAL"]);
                    data.hj_GOODSRKSLACTUAL = Convert.ToString(reader["hj_GOODSRKSLACTUAL"]);
                    data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]);
                    data.GOODSNUM = Convert.ToString(reader["GOODSNUM"]);
                    data.WeiWanShui = Convert.ToString(reader["WeiWanShui"]); //需求编号：SR2017062200002
                    data.ISBONDEDNAME = Convert.ToString(reader["ISBONDEDNAME"]); //是否保税：SR2017062600003
                    if (Convert.ToString(reader["ISBONDED"]) == "0")
                    {
                        data.GOODSNUM = "0";//是否保税：SR2017062600003
                        data.WeiWanShui = "0";//是否保税：SR2017062600003
                    }
                    data.GOODSSLICE_OUT = Convert.ToString(reader["GOODSSLICE_OUT"]);//需求编号：SR2017071100001
                    data.CLEARSTOCKGAP = Convert.ToString(reader["CLEARSTOCKGAP"]);
                    data.AREANAME = Convert.ToString(reader["AREANAME"]);//需求编号：SR2020051900002
                    data.COUNTRYOFORIGIN= Convert.ToString(reader["COUNTRYOFORIGIN"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 按客户、分公司分组  按品名分组

        public ContentResult GetGroupList(int start, int limit, string sort, string condition, string GROUPBY)
        {
            var dataList = GetGroupList(condition, sort,  GROUPBY);
            var list = dataList.Skip(start).Take(limit);
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
            return new ContentResult() { Content = json };
        }

        private static List<Wms_StockTakemb> GetGroupList(string strCondition, string sort,string GROUPBY)//CUSTOMERNAME,goodsname,chargeunit
        {
            var strSql = new StringBuilder();
            var searchField = "";
            var GROUPField = "";
            if (GROUPBY == "Customer") {
                searchField = "CUSTOMERNAME,goodsname,chargeunit,ISBONDED,ISBONDEDNAME=(case when ISBONDED=1 then '是' else '否' end)";//是否保税：SR2017062600003
                GROUPField = "CUSTOMERNAME,goodsname,chargeunit,ISBONDED,MBLNO";//是否保税：SR2017062600003
            }
            if (GROUPBY == "Company")
            {
                searchField = "(select name from company where gid=corpid) companyname,goodsname,chargeunit,ISBONDED,ISBONDEDNAME=(case when ISBONDED=1 then '是' else '否' end)";//是否保税：SR2017062600003
                GROUPField = "corpid,goodsname,chargeunit,ISBONDED,MBLNO";//是否保税：SR2017062600003
            }
            if (GROUPBY == "GOODSNAME")
            {
                searchField = "'' companyname,goodsname,chargeunit,ISBONDED,ISBONDEDNAME=(case when ISBONDED=1 then '是' else '否' end)";//是否保税：SR2017062600003
                GROUPField = "corpid,goodsname,chargeunit,ISBONDED,MBLNO";//是否保税：SR2017062600003
            }
            if (GROUPBY == "CNTRNO")
            {
                searchField = "CNTRNO,goodsname,chargeunit,ISBONDED,ISBONDEDNAME=(case when ISBONDED=1 then '是' else '否' end)";//是否保税：SR2017062600003
                GROUPField = "CNTRNO,goodsname,chargeunit,ISBONDED,MBLNO";//是否保税：SR2017062600003
            }
            strSql.Append(" select " + searchField);
            strSql.Append(" ,sum(hj_GOODSRKSL) hj_GOODSRKSL,sum(hj_GOODSPFSL_OUT) hj_GOODSPFSL_OUT ");
            strSql.Append(" ,sum(hj_GOODSstock) hj_GOODSstock,sum(hj_GOODSPACK) hj_GOODSPACK ");
            strSql.Append(" ,sum(hj_GOODSPACKPFSL) hj_GOODSPACKPFSL,sum(hj_GOODSpackstock) hj_GOODSpackstock,sum(GOODSNUM) GOODSNUM,sum(WeiWanShui) WeiWanShui,MBLNO  "); //需求编号：SR2017062200002
            strSql.Append(" from");
            //strSql.Append(" (select vws.*, vs.ETD from vw_wms_stocktake vws left join vw_settlement vs on vs.mblno=vws.blno ");
            strSql.Append(" (select vws.*,vs.mblno,vs.ETD from vw_wms_stocktake vws left join v_op_bs vs on vs.BSNO=vws.ASSOCIATEDNO");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            strSql.Append(" )t1 ");
            strSql.Append(" group by " + GROUPField);
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }

            if (GROUPBY == "Customer")//按客户
            {
                return SetCustomerData(strSql);
            }
            else if (GROUPBY == "CNTRNO")//按客户
            {
                return SetCNTRNOData(strSql);
            }
            else //if (GROUPBY == "Company")//按分公司 按品名
            {
                return SetCompanyData(strSql);
            }            
        }


        #region 按CNTRNO分组实体类
        private static List<Wms_StockTakemb> SetCNTRNOData(StringBuilder strSql)
        {
            var headList = new List<Wms_StockTakemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new Wms_StockTakemb();
                    #region Set DB data to Object
                    data.CNTRNO = Convert.ToString(reader["CNTRNO"]);
                    data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
                    data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
                    data.hj_GOODSRKSL = Convert.ToString(reader["hj_GOODSRKSL"]);
                    data.hj_GOODSPFSL_OUT = Convert.ToString(reader["hj_GOODSPFSL_OUT"]);
                    data.hj_GOODSSTOCK = Convert.ToString(reader["hj_GOODSSTOCK"]);
                    data.hj_GOODSPACK = Convert.ToString(reader["hj_GOODSPACK"]);
                    data.hj_GOODSPACKPFSL = Convert.ToString(reader["hj_GOODSPACKPFSL"]);
                    data.hj_GOODSPACKSTOCK = Convert.ToString(reader["hj_GOODSPACKSTOCK"]);
                    data.GOODSNUM = Convert.ToString(reader["GOODSNUM"]);
                    data.WeiWanShui = Convert.ToString(reader["WeiWanShui"]); //需求编号：SR2017062200002
                    data.ISBONDEDNAME = Convert.ToString(reader["ISBONDEDNAME"]); //是否保税：SR2017062600003
                    data.MBLNO = Convert.ToString(reader["MBLNO"]);//泰泽需求，20190321
                    if (Convert.ToString(reader["ISBONDED"]) == "0")
                    {
                        data.GOODSNUM = "0";//是否保税：SR2017062600003
                        data.WeiWanShui = "0";//是否保税：SR2017062600003
                    }
                    //data.WHS_CODE = Convert.ToString(reader["WHS_CODE"]);//SR2020060800003
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 按客户分组实体类
        private static List<Wms_StockTakemb> SetCustomerData(StringBuilder strSql)
        {
            var headList = new List<Wms_StockTakemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new Wms_StockTakemb();
                    #region Set DB data to Object
                    data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
                    data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
                    data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
                    data.hj_GOODSRKSL = Convert.ToString(reader["hj_GOODSRKSL"]);
                    data.hj_GOODSPFSL_OUT = Convert.ToString(reader["hj_GOODSPFSL_OUT"]);
                    data.hj_GOODSSTOCK = Convert.ToString(reader["hj_GOODSSTOCK"]);
                    data.hj_GOODSPACK = Convert.ToString(reader["hj_GOODSPACK"]);
                    data.hj_GOODSPACKPFSL = Convert.ToString(reader["hj_GOODSPACKPFSL"]);
                    data.hj_GOODSPACKSTOCK = Convert.ToString(reader["hj_GOODSPACKSTOCK"]);
                    data.GOODSNUM = Convert.ToString(reader["GOODSNUM"]);
                    data.WeiWanShui = Convert.ToString(reader["WeiWanShui"]); //需求编号：SR2017062200002
                    data.ISBONDEDNAME = Convert.ToString(reader["ISBONDEDNAME"]); //是否保税：SR2017062600003
                    data.MBLNO = Convert.ToString(reader["MBLNO"]);//泰泽需求，20190321
                    if (Convert.ToString(reader["ISBONDED"]) == "0")
                    {
                        data.GOODSNUM = "0";//是否保税：SR2017062600003
                        data.WeiWanShui = "0";//是否保税：SR2017062600003
                    }
                    //data.WHS_CODE = Convert.ToString(reader["WHS_CODE"]);//SR2020060800003
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 按分公司分组实体类
        private static List<Wms_StockTakemb> SetCompanyData(StringBuilder strSql)
        {
            var headList = new List<Wms_StockTakemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new Wms_StockTakemb();
                    #region Set DB data to Object
                    data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]);
                    data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
                    data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
                    data.hj_GOODSRKSL = Convert.ToString(reader["hj_GOODSRKSL"]);
                    data.hj_GOODSPFSL_OUT = Convert.ToString(reader["hj_GOODSPFSL_OUT"]);
                    data.hj_GOODSSTOCK = Convert.ToString(reader["hj_GOODSSTOCK"]);
                    data.hj_GOODSPACK = Convert.ToString(reader["hj_GOODSPACK"]);
                    data.hj_GOODSPACKPFSL = Convert.ToString(reader["hj_GOODSPACKPFSL"]);
                    data.hj_GOODSPACKSTOCK = Convert.ToString(reader["hj_GOODSPACKSTOCK"]);
                    data.GOODSNUM = Convert.ToString(reader["GOODSNUM"]);
                    data.WeiWanShui = Convert.ToString(reader["WeiWanShui"]); //需求编号：SR2017062200002
                    data.ISBONDEDNAME = Convert.ToString(reader["ISBONDEDNAME"]); //是否保税：SR2017062600003
                    if (Convert.ToString(reader["ISBONDED"]) == "0")
                    {
                        data.GOODSNUM = "0";//是否保税：SR2017062600003
                        data.WeiWanShui = "0";//是否保税：SR2017062600003
                    }
                    data.MBLNO = Convert.ToString(reader["MBLNO"]);//泰泽需求，20190321
                    //data.WHS_CODE = Convert.ToString(reader["WHS_CODE"]);//SR2020060800003
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion
        
        #endregion
        
        #region 合计列表
        public ContentResult GetDataSumList(int start, int limit, string sort, string condition)
        {
            var dataList = GetDataSumList(condition, sort);
            var list = dataList.Skip(start).Take(limit);            
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
            return new ContentResult() { Content = json };
        }

        private static List<Wms_StockTakemb> GetDataSumList(string strCondition, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select "   
            + " sum(hj_GOODSPACK) as hj_GOODSPACK"//入库件数
            + ",sum(WeiWanShui) as WeiWanShui"//未完税件数
            + ",sum(GOODSNUM) as GOODSNUM"//完税件数
            + ",sum(hj_GOODSPACKSTOCK) as hj_GOODSPACKSTOCK"//库存件数
            + ",sum(hj_GOODSRKSL) as hj_GOODSRKSL"//入库数量
            + ",sum(hj_GOODSSTOCK) as hj_GOODSSTOCK"//库存数量
            + ",sum(hj_GOODSPACKPFSL) as hj_GOODSPACKPFSL"//出库件数
            + ",sum(GOODSSLICE_OUT) as GOODSSLICE_OUT"//出库包装件数
            + ",sum(hj_GOODSPFSL_OUT) as hj_GOODSPFSL_OUT"//出库数量
            + " from ("
            + " (select "                
            + " sum(hj_GOODSPACK) as hj_GOODSPACK"//入库件数
            + ",sum(WeiWanShui) as WeiWanShui"//未完税件数
            + ",sum(GOODSNUM) as GOODSNUM"//完税件数
            + ",sum(hj_GOODSPACKSTOCK) as hj_GOODSPACKSTOCK"//库存件数
            + ",sum(hj_GOODSRKSL) as hj_GOODSRKSL"//入库数量
            + ",sum(hj_GOODSSTOCK) as hj_GOODSSTOCK"//库存数量
            + ",0 as hj_GOODSPACKPFSL"//出库件数
            + ",0 as GOODSSLICE_OUT"//出库包装件数
            + ",0 as hj_GOODSPFSL_OUT"//出库数量
            + " from (select gid,wmsno,hj_GOODSPACK, WeiWanShui, GOODSNUM, hj_GOODSPACKSTOCK, hj_GOODSRKSL, hj_GOODSSTOCK,0 as hj_GOODSPACKPFSL,0 as GOODSSLICE_OUT,0 as hj_GOODSPFSL_OUT"
            + " from vw_wms_stocktake as vws left join v_op_bs as vs on vs.BSNO=vws.ASSOCIATEDNO");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            strSql.Append(" group by gid,wmsno,hj_GOODSPACK, WeiWanShui, GOODSNUM, hj_GOODSPACKSTOCK, hj_GOODSRKSL, hj_GOODSSTOCK) as a");
            strSql.Append(" group by wmsno)"
            + " union all "
            + " (select "                
            + " 0 as hj_GOODSPACK"//入库件数
            + ",0 as WeiWanShui"//未完税件数
            + ",0 as GOODSNUM"//完税件数
            + ",0 as hj_GOODSPACKSTOCK"//库存件数
            + ",0 as hj_GOODSRKSL"//入库数量
            + ",0 as hj_GOODSSTOCK"//库存数量
            + ",sum(hj_GOODSPACKPFSL) as hj_GOODSPACKPFSL"//出库件数
            + ",sum(GOODSSLICE_OUT) as GOODSSLICE_OUT"//出库包装件数
            + ",sum(hj_GOODSPFSL_OUT) as hj_GOODSPFSL_OUT"//出库数量
            + " from vw_wms_stocktake as vws left join v_op_bs as vs on vs.BSNO=vws.ASSOCIATEDNO");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            strSql.Append(")) as a ");
            return SetSumData(strSql);
        }

        private static List<Wms_StockTakemb> SetSumData(StringBuilder strSql)
        {
            var headList = new List<Wms_StockTakemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new Wms_StockTakemb();
                    #region Set DB data to Object                    
                    data.hj_GOODSPACK = Convert.ToString(reader["hj_GOODSPACK"]);//入库件数
                    data.WeiWanShui = Convert.ToString(reader["WeiWanShui"]);//未完税件数
                    data.GOODSNUM = Convert.ToString(reader["GOODSNUM"]);//完税件数
                    data.hj_GOODSPACKPFSL = Convert.ToString(reader["hj_GOODSPACKPFSL"]);//出库件数
                    data.GOODSSLICE_OUT = Convert.ToString(reader["GOODSSLICE_OUT"]);//出库包装件数
                    data.hj_GOODSPACKSTOCK = Convert.ToString(reader["hj_GOODSPACKSTOCK"]);//库存件数
                    data.hj_GOODSRKSL = Convert.ToString(reader["hj_GOODSRKSL"]);//入库数量
                    data.hj_GOODSPFSL_OUT = Convert.ToString(reader["hj_GOODSPFSL_OUT"]);//出库数量
                    data.hj_GOODSSTOCK = Convert.ToString(reader["hj_GOODSSTOCK"]);//库存数量
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

    }
}