using HH.WMS.Common;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Basic;
using HH.WMS.Entitys.Common;
using HH.WMS.Entitys.Entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.WMS.DAL.InStock
{
    public class TN_WM_ARRIVALDAL : DapperBaseDAL
    {
        #region 更新到货单状态
        /// <summary>
        /// 提交到货单-主表
        /// </summary>
        /// <param name="entity">审批实体</param>
        /// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ExamineMst(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in entity)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" UPDATE TN_WM_ARRIVAL_MST SET CN_S_STATE=@CN_S_STATE,CN_S_AUDITOR=@CN_S_AUDITOR,CN_T_AUDIT_TIME=@CN_T_AUDIT_TIME,CN_S_AUDIT_REMARK=@CN_S_AUDIT_REMARK WHERE CN_S_ARRIVAL_NO=@CN_S_ARRIVAL_NO");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_STATE = examineType,
                    CN_S_ARRIVAL_NO = model.CN_S_FROM_NO,
                    CN_S_AUDITOR = model.CN_S_CREATOR,
                    CN_T_AUDIT_TIME = DateTime.Now,
                    CN_S_AUDIT_REMARK = model.CN_S_OPINION
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        /// <summary>
        /// 提交到货单-子表
        /// </summary>
        /// <param name="entity">审批实体</param>
        /// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult ExamineDtl(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (var model in entity)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" UPDATE TN_WM_ARRIVAL_DTL SET CN_S_STATE=@CN_S_STATE WHERE CN_S_ARRIVAL_NO=@CN_S_ARRIVAL_NO");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_STATE = examineType,
                    CN_S_ARRIVAL_NO = model.CN_S_FROM_NO
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        #endregion

        #region 更改到货单状态
        /// <summary>
        /// 更改到货单状态(已码盘)
        /// </summary>
        /// <param name="list">更新列表</param>
        /// <param name="state">状态</param>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-17</History>
        public OperateResult UpdateArrivalState(List<TrayItemRow> list, string state)
        {
            OperateResult result = new OperateResult();
            foreach (var item in list)
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append(" IF EXISTS( SELECT CN_S_ARRIVAL_NO FROM TN_WM_ARRIVAL_DTL P  LEFT JOIN ");
                sbStr.Append(" (SELECT SUM(CN_F_QUANTITY) CN_F_QUANTITY,CN_S_ITEM_CODE,CN_S_LOT_NO FROM TN_WM_B_TRAY_ITEM_REL  ");
                sbStr.Append(" WHERE  CN_S_LOT_NO='" + item.CN_S_LOT_NO + "'   GROUP BY CN_S_LOT_NO,CN_S_ITEM_CODE) DT   ON P.CN_S_ITEM_CODE=DT.CN_S_ITEM_CODE    ");
                sbStr.Append(" WHERE DT.CN_F_QUANTITY>=P.CN_F_QUANTITY AND P.CN_S_ARRIVAL_NO='" + item.CN_S_OP_NO + "') ");
                sbStr.Append(" UPDATE TN_WM_ARRIVAL_DTL SET CN_S_STATE='" + state + "' WHERE  CN_S_ARRIVAL_NO='" + item.CN_S_OP_NO + "' ");
                result = ExecuteTranSql(sbStr.ToString(), null, null);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }
        #endregion

        #region 入库单联机新增-选择来源
        /// <summary>
        /// 入库单页面，选择来源单据
        /// </summary>
        /// <param name="searchModel">实体</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-22</History>
        public OperateResult GetFromArrivalList(SearchModel searchModel)
        {
            var condition = searchModel.SearchCondition;
            string ConStr = "where 1=1 ";
            if (condition != null)
            {
                if (!string.IsNullOrEmpty(condition.CN_S_ARRIVAL_NO.ToString()))
                {
                    ConStr = ConStr + " and CN_S_ARRIVAL_NO like '%" + Util.ToStringInput(condition.CN_S_ARRIVAL_NO) + "%'";
                }
                if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
                {
                    ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
                }

            }
            string sql = @"(select CN_S_ARRIVAL_NO CN_S_OP_NO,*,CN_S_VENDOR_NO CN_S_VENDOR_CODE from TN_WM_ARRIVAL_MST WHERE CN_S_STATE in ('" + Constants.State_Audit + "','" + Constants.State_BeenCheck + "')) T " + ConStr;

            OperateResult result = new OperateResult();

            return ExecutePagingResult(sql, searchModel.PageIndex, searchModel.PageSize, "", "");
        }

        public List<TN_WM_ARRIVAL_DTLEntity> GetFromArrivalDtl(string arrivalNo)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT * FROM TN_WM_ARRIVAL_DTL WHERE CN_S_ARRIVAL_NO='" + arrivalNo + "'");

            return ExecuteQuery<TN_WM_ARRIVAL_DTLEntity>(sbStr.ToString());
        }
        #endregion

        #region 查询到货单列表
        /// <summary>
        /// 查询到货单列表
        /// </summary>
        /// <param name="searchModel">实体</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-24</History>
        public DataTable GetArrivalList(SearchModel searchModel, out long total, bool isExport)
        {
            var condition = searchModel.SearchCondition;

            string sql = " select * from TN_WM_ARRIVAL_MST where 1=1 ";
            string ConStr = "";
            if (condition != null)
            {
                if (!string.IsNullOrEmpty(condition.CN_S_ARRIVAL_NO.ToString()))
                {
                    ConStr = ConStr + " and CN_S_ARRIVAL_NO like '%" + Util.ToStringInput(condition.CN_S_ARRIVAL_NO) + "%'";
                }
                string state = condition.CN_S_STATE.ToString();
                if (!string.IsNullOrEmpty(state))
                {
                    if (state.IndexOf(",") > 0)
                    {
                        state = "'" + state.Replace(",", "','") + "'";
                        ConStr = ConStr + " and CN_S_STATE in (" + state + ")";
                    }
                    else
                    {
                        ConStr = ConStr + " and CN_S_STATE='" + state + "'";
                    }

                }
                if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
                {
                    ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
                }

                if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
                {
                    ConStr = ConStr + " and CN_T_CREATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
                }
                if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
                {
                    ConStr = ConStr + " and CN_T_CREATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
                }

                if (!string.IsNullOrEmpty(condition.CN_S_VENDOR_NAME.ToString()))
                {
                    ConStr = ConStr + " and CN_S_VENDOR_NAME like '%" + Util.ToStringInput(condition.CN_S_VENDOR_NAME) + "%'";
                }
                if (!string.IsNullOrEmpty(condition.IsHandWork.ToString()))
                {
                    if (condition.IsHandWork.ToString() == "true" || condition.IsHandWork.ToString() == "True")
                        ConStr = ConStr + " and (CN_S_FROM_NO='' or CN_S_FROM_NO IS NULL) ";
                    else
                        ConStr = ConStr + " and (CN_S_FROM_NO!='' and CN_S_FROM_NO IS not NULL) ";
                }
            }
            if (!string.IsNullOrEmpty(ConStr))
            {
                sql = sql + ConStr;
            }
            OperateResult result = new OperateResult();
            DataTable dt = new DataTable();
            if (!isExport)
            {
                return ExecutePagingData(" (" + sql + ") t ", searchModel.PageIndex, searchModel.PageSize, out total, "", "");
            }
            else
            {
                total = 0;//无效
                return ExecuteDataTable(sql, "");
            }
        }
        #endregion

        #region 查询到货单主表
        /// <summary>
        /// 查询到货单主表
        /// </summary>
        /// <returns></returns>
        /// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
        public DataTable GetMstList(string sqlwhere)
        {
            string sql = "select * from TN_WM_ARRIVAL_MST {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 查询到货单子表
        /// <summary>
        /// 查询到货单子表
        /// </summary>
        /// <returns></returns>
        /// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
        public DataTable GetDtlList(string sqlwhere)
        {
            string sql = "select * from TN_WM_ARRIVAL_DTL {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 查询到货单
        /// <summary>
        /// 查询到货单子表
        /// </summary>
        /// <returns></returns>
        /// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
        public DataTable GetMstJoinDtl(string sqlwhere)
        {
            string sql = "SELECT * FROM tn_wm_arrival_mst AS a INNER JOIN tn_wm_arrival_dtl AS b ON b.CN_S_ARRIVAL_NO = a.CN_S_ARRIVAL_NO {0}";
            sql = string.Format(sql, sqlwhere);
            return ExecuteDataTable(sql);
        }
        #endregion

        #region 获取OMS确认数据
        /// <summary>
        /// 获取OMS确认数据
        /// </summary>
        /// <param name="ArrivalNo"></param>
        /// <returns></returns>
        public DataTable GetArrivalOmsConfirmData(string ArrivalNo)
        {
            string sql = @"SELECT ARRM.CN_S_OP_TYPE,INM.CN_S_OP_NO,ARRD.CN_N_FROM_ROW_NO,INM.CN_S_FROM_NO,INM.CN_S_OWNER,INM.CN_S_STOCK_CODE,INM.CN_GUID,INM.CN_S_STATE,
                            ARRD.CN_S_ITEM_CODE,ARRD.CN_S_ITEM_NAME,ARRD.CN_F_QUANTITY PLANQTY,ARRD.CN_F_QUANTITY ACTUALQTY
                             FROM TN_WM_ARRIVAL_DTL ARRD 
                            LEFT JOIN TN_WM_ARRIVAL_MST ARRM ON ARRM.CN_GUID=ARRD.CN_S_MSTGUID
                            LEFT JOIN tn_wm_in_mst INM ON INM.CN_S_OP_NO=ARRM.CN_S_FROM_NO
                            WHERE ARRD.CN_S_ARRIVAL_NO='" + ArrivalNo + @"' AND INM.CN_S_OP_TYPE!='销售退库' AND INM.CN_S_FROM_NO IS NOT NULL ";
            return ExecuteDataTable(sql);
        }
        #endregion

        /// <summary>
        /// 升累积到货数的量
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAddupAllot(string opNoList, IDbTransaction trans)
        {
            string sql = "UPDATE TN_WM_ARRIVAL_DTL SET  CN_F_ADDUP_ALLOT_QTY=ISNULL(CN_F_ADDUP_ALLOT_QTY,0)+ISNULL(CN_F_QUANTITY,0) ";
            sql = sql + " WHERE CN_S_ARRIVAL_NO in ('" + opNoList + "')   ";
            return ExecuteTranSql(sql, null, trans);
        }

        public DataTable GetArrivalDtlData(string arrivalNo)
        {
            string sql = @"  SELECT d.*,INM.CN_S_OWNER,m.CN_S_DELIVERY_NOTE,m.CN_S_OP_TYPE,m.CN_S_STOCK_CODE
                              ,m.CN_T_DATE,m.CN_S_LOT_CODE,m.CN_S_DELIVERY_NOTE,m.CN_S_VENDOR_NO,m.CN_S_VENDOR_NAME 
                              ,IND.CN_F_ACTUAL_PRICE  ,IND.CN_F_ACTUAL_MONEY,INM.CN_S_FROM_NO as CN_S_CG_FROM_NO,IND.CN_N_ROW_NO
                              FROM TN_WM_ARRIVAL_DTL d left join tn_wm_arrival_mst m on d.CN_S_ARRIVAL_NO=m.CN_S_ARRIVAL_NO 
                              left join  TN_WM_IN_MST INM on inm.CN_S_OP_NO=m.CN_S_FROM_NO 
                              LEFT JOIN TN_WM_IN_DTL IND  ON  IND.CN_S_OP_NO=INM.CN_S_OP_NO  and d.CN_N_FROM_ROW_NO=ind.CN_N_ROW_NO
                              WHERE d.CN_S_ARRIVAL_NO='" + arrivalNo + @"'";
            return ExecuteDataTable(sql);

        }

        /// <summary>
        /// 降累积到货数的量
        /// </summary>
        /// <param name="opNoList"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult DeleteAddupAllot(string opNoList, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            string sql = @" UPDATE TN_WM_ARRIVAL_DTL SET  CN_F_ADDUP_ALLOT_QTY=ISNULL(CN_F_ADDUP_ALLOT_QTY,0)-ISNULL(CN_F_QUANTITY,0) WHERE CN_S_ARRIVAL_NO in (" + opNoList + ")";
            result = ExecuteTranSql(sql.ToString(), null, trans);
            return result;
        }

        public DataTable GetPutPosition(string arrivalCode, string itemCode)
        {
            string sql = @" SELECT DTL.CN_S_PUT_POSITION,DTL.CN_S_ITEM_CODE,DTL.CN_S_ITEM_NAME,DTL.CN_S_ITEM_STATE,MST.CN_S_OWNER,MST.CN_S_LOT_CODE,DTL.CN_S_PRODUCTION_BATCH
                            FROM TN_WM_ARRIVAL_DTL DTL left join TN_WM_ARRIVAL_MST MST ON DTL.CN_S_ARRIVAL_NO=MST.CN_S_ARRIVAL_NO
                            WHERE DTL.CN_S_ARRIVAL_NO='" + arrivalCode + "' AND DTL.CN_S_ITEM_CODE='" + itemCode + "'";
            return ExecuteDataTable(sql);
        }
    }
}