EleKitService/RDH.Data/SPILItemTransactionBLL.cs

using RDH.Data.Models;
using System;
using System.Collections.Generic;
using System.DirectoryServices;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
namespace RDH.Data.BLL
{
    public partial class SPILItemTransactionBLL : BaseOrclBLL<SPILItemTransaction>
    {
        public SPILItemTransactionBLL() : base()
        {
            AliasTableName = "spilt";
        }

        internal override String TABLE_NAME { get { return "S_P_I_L_ITEM_TRANSACTION"; } }//TB_NAME
        internal override string ENTITY_NAME { get { return "SPILItemTransaction"; } }
        internal override String KEY_COLUMN { get { return "KEY"; } }//KEY_COLUMN

        internal override void InitMaps()
        {

            ColumnPropMaps.Add("KEY", "Key");
            ColumnPropMaps.Add("ITEM_TRANSACTION_KEY", "ItemTransactionKey");
            ColumnPropMaps.Add("STORAGE_SPACE_ITEM_LOT_KEY", "StorageSpaceItemLotKey");
            ColumnPropMaps.Add("S_S_I_LOT_SNAPSHOT_KEY", "SSILotSnapshotKey");
            ColumnPropMaps.Add("QUANTITY", "Quantity");
            ColumnPropMaps.Add("CREATE_BY", "CreateBy");
            ColumnPropMaps.Add("CREATE_TIME", "CreateTime");
            ColumnPropMaps.Add("FLAG", "Flag");
            ColumnPropMaps.Add("CLIENT_TIME", "ClientTime");
            ColumnPropMaps.Add("SERVER_TIME", "ServerTime");
            ColumnPropMaps.Add("EXT01", "Ext01");
            ColumnPropMaps.Add("EXT02", "Ext02");
            ColumnPropMaps.Add("EXT03", "Ext03");
            ColumnPropMaps.Add("EXT04", "Ext04");
            ColumnPropMaps.Add("EXT05", "Ext05");
            ColumnPropMaps.Add("EXT06", "Ext06");
            ColumnPropMaps.Add("EXT07", "Ext07");
            ColumnPropMaps.Add("EXT08", "Ext08");
            ColumnPropMaps.Add("EXT09", "Ext09");
            ColumnPropMaps.Add("EXT10", "Ext10");
            ColumnPropMaps.Add("DEVICE_KEY", "DeviceKey");
            ColumnPropMaps.Add("POCKET_HOLES", "PocketHoles");
            ColumnPropMaps.Add("ITEM_LOT_KEY", "ItemLotKey");//COLUMN_MAPS
        }

        internal override object GetParam(SPILItemTransaction spilitemtransaction)
        {
            return new
            {

                Key = spilitemtransaction.Key,
                ItemTransactionKey = spilitemtransaction.ItemTransactionKey,
                StorageSpaceItemLotKey = spilitemtransaction.StorageSpaceItemLotKey,
                SSILotSnapshotKey = spilitemtransaction.SSILotSnapshotKey,
                Quantity = spilitemtransaction.Quantity,
                CreateBy = spilitemtransaction.CreateBy,
                CreateTime = spilitemtransaction.CreateTime,
                Flag = spilitemtransaction.Flag,
                ClientTime = spilitemtransaction.ClientTime,
                ServerTime = spilitemtransaction.ServerTime,
                Ext01 = spilitemtransaction.Ext01,
                Ext02 = spilitemtransaction.Ext02,
                Ext03 = spilitemtransaction.Ext03,
                Ext04 = spilitemtransaction.Ext04,
                Ext05 = spilitemtransaction.Ext05,
                Ext06 = spilitemtransaction.Ext06,
                Ext07 = spilitemtransaction.Ext07,
                Ext08 = spilitemtransaction.Ext08,
                Ext09 = spilitemtransaction.Ext09,
                Ext10 = spilitemtransaction.Ext10,
                DeviceKey = spilitemtransaction.DeviceKey,
                PocketHoles = spilitemtransaction.PocketHoles,
                ItemLotKey = spilitemtransaction.ItemLotKey,   //Params
            };
        }
        public IEnumerable<SPILItemTransaction> ListMekKitDrugLendTransactionByRoom(String roomName) 
        {
            #region sql builder
            ItemTransactionBLL itemTransactionBLL = new ItemTransactionBLL();
            SqlBuilder sqlBuilder = new SqlBuilder(itemTransactionBLL);
            //item
            ItemBLL itemBLL = new ItemBLL();
            TableJoinInfo itemTable = new TableJoinInfo
            {
                LeftDal = itemTransactionBLL,
                LeftColumnName = "ITEM_KEY",
                RightDal = itemBLL,
                RightColumnName = "KEY",
                JoinType = TableJoinTypes.Inner,
            };
            sqlBuilder.AppendSelectionTable(itemTable);
            //SPILItemTransaction
            TableJoinInfo spiltTable = new TableJoinInfo
            {
                LeftDal = itemTransactionBLL,
                LeftColumnName = itemTransactionBLL.KEY_COLUMN,
                RightDal = this,
                RightColumnName = "ITEM_TRANSACTION_KEY",
                JoinType = TableJoinTypes.Inner,
            };
            sqlBuilder.AppendSelectionTable(spiltTable);
            //item lot
            ItemLotBLL itemLotBLL = new ItemLotBLL();
            TableJoinInfo itemlotTable = new TableJoinInfo
            {
                LeftDal = this,
                LeftColumnName = "ITEM_LOT_KEY",
                RightDal = itemLotBLL,
                RightColumnName = itemLotBLL.KEY_COLUMN,
                JoinType = TableJoinTypes.Inner,
            };
            sqlBuilder.AppendSelectionTable(itemlotTable);
            sqlBuilder.AppendWherePhrases($"{itemTransactionBLL.AliasTableName}.EXT02=:roomName AND NVL({itemTransactionBLL.AliasTableName}.ext10,'0')='0'");
            sqlBuilder.AppendWherePhrases($"{itemTransactionBLL.AliasTableName}.TRANSACTION_TYPE='LendByMedKit'");
            sqlBuilder.AppendWherePhrases($"(SYSDATE-{itemTransactionBLL.AliasTableName}.CREATE_TIME)<1");
            sqlBuilder.AppendOrderPhrases($"{itemTransactionBLL.AliasTableName}.CREATE_TIME DESC");
            #endregion
            List<SPILItemTransaction> rlt = new List<SPILItemTransaction>();
            //只读取一条主表的数据，防止其它操作记录的数据
            Guid? itemTransactionKey = null;
            SqlMapper.Query<ItemTransaction, Item, SPILItemTransaction, ItemLot, SPILItemTransaction>(ConnectionFactory.Current.GetSessionConnection(),
                sqlBuilder.ToString(),
                (it, i, spilt, il) =>
                {
                    if (itemTransactionKey == null) 
                    {
                        itemTransactionKey = it.Key;
                    }
                    if (it.Key == itemTransactionKey)
                    {
                        spilt.ItemTransaction = it;
                        it.Item = i;
                        spilt.ItemLotInfo = il;
                        rlt.Add(spilt);
                    }
                    return spilt;
                },
                new
                {
                    roomName = roomName
                },
                splitOn: sqlBuilder.GetSpiltors());
            return rlt;
        }
    }

}