WEBUI/WMS.BZCore/ServiceCore/QueryCell.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using WMS.Info;
using WMS.Util;

namespace WMS.Core
{
    public class QueryCell
    {
        /// <summary>
        /// 打印条码
        /// </summary>
        /// <param name="locCode"></param>
        /// <returns></returns>
        public string PrintBarcode(string locCode)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                var strSql = new StringBuilder();
                strSql.Append("SELECT ");
                strSql.Append(@"
                t.F_NO
                ");
                strSql.Append("  FROM BASE_WARECELL t ");
                strSql.Append("  WHERE 1=1 AND F_NO='" + locCode + "'");
                // 虚拟参数
                DataTable dt = suger.Ado.GetDataTable(strSql.ToString());
                return "";//Print.PrintBarCode(dt, BarCodeType.货位, "广东***公司", false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            { suger.Dispose(); }
        }

        #region 获取数据

        /// <summary>
        /// 获取列表数据
        /// <summary>
        /// <returns></returns>
        public IEnumerable<BASE_WARECELL> GetList(string keyword)
        {
            try
            {
                return SysDbCore.GetDbCtx().Queryable<BASE_WARECELL>().Where(it => it.F_ISDELETE == 0).WhereIF(!keyword.IsEmpty(), it => it.F_NO.Contains(keyword) || it.F_NAME.Contains(keyword)).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取储位信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<BASE_WARECELL> GetLocList(string houserNo, int line)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                string sql = "SELECT F_WAREHOUSENO,F_NO,F_ISSTOP,F_STATENUM,F_LINE,F_COL,F_LAYER,F_DEPTH,isnull(F_CNTRGRPNO,' ') F_CNTRGRPNO FROM V_WTV_LOCSHOW WHERE F_WAREHOUSENO=@F_WAREHOUSENO AND F_LINE=@F_LINE  GROUP BY F_WAREHOUSENO,F_NO,F_ISSTOP,F_STATENUM,F_LINE,F_COL,F_LAYER,F_DEPTH,F_CNTRGRPNO";
                return suger.Ado.SqlQuery<BASE_WARECELL>(sql, new { F_WAREHOUSENO = houserNo, F_LINE = line });
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }
        }


        

        /// <summary>
        /// 获取储位明细信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<BASE_WARECELL> GetLocDetailList(string conCode, string barCode)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                string sql = string.Empty;
                sql = "SELECT NVL(F_CONTRGRPBARCODE,' ')F_CONTRGRPBARCODE,NVL(F_CONTRBARCODE,' ') F_CONTRBARCODE,NVL(F_MATNO,' ') F_MATNO, NVL(F_MATNAME,' ')F_MATNAME,F_QTY,NVL(F_BATCHNO,' ')F_BATCHNO, NVL(F_MATVERSION,' ')F_MATVERSION,NVL(F_MATGEAR,' ')F_MATGEAR,NVL(F_OUTLOCKONO,' ') F_OUTLOCKONO FROM V_WTV_LOCSHOWDETAIL WHERE F_CONTRGRPBARCODE=@F_CONTRGRPBARCODE OR F_CONTRBARCODE=@F_CONTRBARCODE";

                return suger.Ado.SqlQuery<BASE_WARECELL>(sql, new { F_CONTRGRPBARCODE = conCode, F_CONTRBARCODE = barCode });
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }

        }

        /// <summary>
        /// 获取托盘，周转箱号
        /// </summary>
        /// <param name="contrgrpno"></param>
        /// <returns></returns>
        public IEnumerable<LocShowDetail> GetLocCodeList(string contrgrpno)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx(); // SELECT * FROM V_WTV_LOCSHOWDETAIL WHERE F_CONTRGRPNO='{0}' ORDER BY F_CONTRSORT,contrgrpno
                string sql = string.Format("SELECT * FROM V_WTV_LOCSHOWDETAIL WHERE F_CONTRGRPNO='{0}'", contrgrpno);
                return suger.Ado.SqlQuery<LocShowDetail>(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }

        }

        /// <summary>
        /// 根据仓库编码获取不同的出库口
        /// </summary>
        /// <param name="WarehouseCode"></param>
        /// <returns></returns>
        public IEnumerable<string> GetAddrtoList(string WarehouseCode)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                string sql = string.Format("SELECT F_NUM FROM SYS_DATA WHERE F_ISSTOP=0 AND F_ISDELETE=0 AND F_CODE LIKE 'EWarehouse{0}%'", WarehouseCode);
                DataTable dt = suger.Ado.GetDataTable(sql);
                List<string> lstStr = new List<string>();
                foreach (DataRow row in dt.Rows)
                    lstStr.Add(row[0].ToString());
                return lstStr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }


        }

        /// <summary>
        /// 根据仓库编码获取不同的出库口
        /// </summary>
        /// <param name="WarehouseCode"></param>
        /// <returns></returns>
        public IEnumerable<TreeModel> GetLearunAddrtoList(string WarehouseCode)
        {
            
            List<TreeModel> treeList = new List<TreeModel>();
            try
            {

                string str = "EWarehouse" + WarehouseCode;
                List<SYS_DATA> list = SysDbCore.GetDbCtx().Queryable<SYS_DATA>().Where(it => it.F_ISDELETE == 0 && it.F_ISSTOP == 0 && it.F_CODE.Contains(str)).ToList();
                foreach (var item in list)
                {
                    TreeModel node = new TreeModel();
                    node.id = item.F_NO;
                    node.text = item.F_NAME.ToString();
                    node.value = item.F_NUM.ToString();
                    node.showcheck = true;
                    node.checkstate = 0;
                    node.isexpand = true;
                    node.icon = ACLAuthorize.IcoAuthorize;
                    node.parentId = "";
                    treeList.Add(node);
                }
                return treeList.ToTree();
            }
            catch (Exception ex)
            {

                throw;
            }


        }

        /// <summary>
        /// 获取仓库编号
        /// </summary>
        /// <returns></returns>
        public IEnumerable<string> GetHouseNo()
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                string sql = "SELECT F_NO||'-'||F_NAME from BASE_WAREHOUSE WHERE F_TYPENUM = 1 GROUP BY F_NO,F_NAME ";
                DataTable dt = suger.Ado.GetDataTable(sql);
                List<string> lstStr = new List<string>();
                foreach (DataRow row in dt.Rows)
                    lstStr.Add(row[0].ToString());
                return lstStr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }
        }

        /// <summary>
        /// 获取仓库有多少排
        /// </summary>
        /// <param name="houseNo">仓库编号</param>
        /// <returns></returns>
        public int GetMaxLine(string houseNo)
        {
            try
            {
                return SysDbCore.GetDbCtx().Queryable<BASE_WARECELL>().Where(it => it.F_WAREHOUSENO == houseNo).Max(it => it.F_LINE);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 提交数据

        /// <summary>
        /// 下架，生成出库任务
        /// </summary>
        /// <param name="houserNo">仓库号</param>
        /// <param name="F_CONTRGRPNO">托盘号</param>
        /// <param name="Addrto">目标地址</param>
        /// <returns></returns>
        public string AddOutTask(string houserNo, string F_CONTRGRPNO, string Addrto)
        {
            string res = "下架成功";
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                if (string.IsNullOrEmpty(houserNo))
                    throw new Exception("仓库不能为空");
                if (string.IsNullOrEmpty(F_CONTRGRPNO))
                    throw new Exception("托盘不能为空");
                if (string.IsNullOrEmpty(Addrto))
                    throw new Exception("目标地址为空");

                var warehouse = suger.Queryable<BASE_WAREHOUSE>().Where(v => v.F_NO == houserNo).First();
                // this.BaseRepository().FindEntity<BASE_WARECELL>(v => v.F_NO == houserNo);
                if (warehouse == null)
                    throw new Exception("未找到仓库信息");
                if (warehouse.F_ISSTOP != 0 || warehouse.F_ISDELETE != 0)
                    throw new Exception("仓库被停用或者被删除");
                if (warehouse.F_TYPENUM != 1)
                    throw new Exception("仓库不是立库，无法下架");

                StringBuilder sql = new StringBuilder();
                sql.AppendFormat(@"SELECT C.* FROM BASE_WARECELL C LEFT JOIN BILL_INVCONTR I ON I.F_CONTRGRPNO = C.F_CNTRGRPNO WHERE I.F_CONTRGRPBARCODE = '{0}'", F_CONTRGRPNO);

                var cell = suger.Queryable<BASE_WARECELL>(sql.ToString());
                if (cell == null)
                    throw new Exception("未找到库存，下架失败");

                //string EOutPort = "EWarehouse" + houserNo + "_" + Addrto;
                //int AddVal = (int)Enum.Parse(typeof(EOutPort), EOutPort);

                return "";

            }
            catch (Exception ex)
            {
                res = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 获取BASE_WARECELL表实体数据
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public BASE_WARECELL GetBASE_WARECELLEntity(string keyValue)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                return suger.Queryable<BASE_WARECELL>(keyValue).First();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }
        }

        /// <summary>
        /// 获取主表实体数据
        /// <param name="processId">流程实例ID</param>
        /// <summary>
        /// <returns></returns>
        public BASE_WARECELL GetEntityByProcessId(string processId)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                return suger.Queryable<BASE_WARECELL>().Where(t => t.F_NO == processId).First();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }
        }

        /// <summary>
        /// 删除实体数据
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public void DeleteEntity(string keyValue)
        {
            SqlSugarClient suger = null;
            try
            {
                suger = SysDbCore.GetDbCtx();
                suger.Deleteable<BASE_WARECELL>().Where(t => t.F_NO == keyValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                suger.Dispose();
            }
        }

        /// <summary>
        /// 保存实体数据（新增、修改）
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public void SaveEntity(LoginUserInfo loginUserInfo, string keyValue, BASE_WARECELL entity)
        {
            try
            {
                if (entity == null)
                    throw SysExCore.ThrowFailException("输入数据为空。");
                if (string.IsNullOrWhiteSpace(entity.F_NO))
                    throw SysExCore.ThrowFailException("编码为空。");
                if (string.IsNullOrWhiteSpace(entity.F_NAME))
                    throw SysExCore.ThrowFailException("名称为空。");
                entity.F_EDITTIME = DateTime.Now;
                entity.F_EDITUSERNO = loginUserInfo.UserNo;
                if (string.IsNullOrEmpty(keyValue))
                {
                    entity.F_ADDTIME = DateTime.Now;
                    entity.F_ADDUSERNO = loginUserInfo.UserNo;
                    entity.F_ISDELETE = 0;
                    entity.F_ISSTOP = 0;
                    SysDbCore.GetDbCtx().Insertable<BASE_WARECELL>(entity).ExecuteCommand();
                }
                else
                {
                    SysDbCore.GetDbCtx().Updateable<BASE_WARECELL>(entity).IgnoreColumns(it => new { it.F_ADDTIME, it.F_ADDUSERNO }).Where(it => it.F_NO == keyValue).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

    }
}