EchoTex_Payroll/HRM.DA/DA/Assets/AssetSerialTranDA.cs

using System;
using System.Data;
using Ease.Core.DataAccess;
using HRM.BO;


namespace HRM.DA
{
    public class AssetSerialTranDA
    {
        #region Constructor

        private AssetSerialTranDA()
        {
        }

        #endregion


        #region Get

        internal static IDataReader Get(TransactionContext tc, int id)
        {
            return tc.ExecuteReader("SELECT * FROM AssetSerialTran WHERE AssetSerialTranID=%n", id);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM AssetSerialTran");
        }

        #endregion

        #region Insert

        internal static void Insert(TransactionContext tc, AssetSerialTran oAssetSerialTran)
        {
            string sql = SQLParser.MakeSQL("INSERT INTO AssetSerialTran(AssetSerialTranID, AssetID, SerialId, TranDate," +
                                           " Trantype, BatchNo, VendorID,storeID,AssetReceiverType,AssetReceiverID,UserId,CreatedBy, CreatedDate,Amount,IsTemporary,TillDate," +
                                           "damageAmount,damageRemarks, isFinalClerance ,faultybyEmpID)" +
                                           " VALUES(%n, %n, %n, %d, %n, %s, %n,%n, %n, %n, %n,%n,%d,%n,%b,%d, %n, %s, %n, %n)",
                oAssetSerialTran.ID, oAssetSerialTran.AssetId, oAssetSerialTran.SerialId, oAssetSerialTran.TranDate, oAssetSerialTran.TranType, oAssetSerialTran.BatchNo,
                oAssetSerialTran.VendorId, oAssetSerialTran.StoreId, oAssetSerialTran.AssetReceiverType, oAssetSerialTran.AssetReceiverId,
                oAssetSerialTran.UserId,
                DataReader.GetNullValue(oAssetSerialTran.CreatedBy),
                DataReader.GetNullValue(oAssetSerialTran.CreatedDate),oAssetSerialTran.Amount, oAssetSerialTran.IsTemporaryItem,
                oAssetSerialTran.TillDate, oAssetSerialTran.damageAmount, oAssetSerialTran.damageRemarks, oAssetSerialTran.isFinalClerance, oAssetSerialTran.faultybyEmpID);
            tc.ExecuteNonQuery(sql);
        }

        #endregion


        #region Update

        internal static void Update(TransactionContext tc, AssetSerialTran oAssetSerialTran)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE AssetSerialTran SET AssetID=%s,SerialId=%n,TranDate=%d,Trantype=%n,BatchNo=%s" +
                                            " VendorID=%n,StoreID=%n,AssetReceiverType=%n,AssetReceiverID=%n,UserId=%n," +
                                            " ModifiedBy=%n,ModifiedDate=%d,IsTemporary=%b,TillDate=%d WHERE AssetSerialTranID=%n",
                  oAssetSerialTran.ID, oAssetSerialTran.AssetId, oAssetSerialTran.SerialId, oAssetSerialTran.TranDate, oAssetSerialTran.TranType,
                oAssetSerialTran.VendorId, oAssetSerialTran.StoreId, oAssetSerialTran.AssetReceiverType, oAssetSerialTran.AssetReceiverId,
                oAssetSerialTran.UserId,
                DataReader.GetNullValue(oAssetSerialTran.ModifiedBy),
                DataReader.GetNullValue(oAssetSerialTran.ModifiedDate), oAssetSerialTran.IsTemporaryItem, oAssetSerialTran.TillDate,
                oAssetSerialTran.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static IDataReader GetLastBatchNo(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM assetSerialTran WHERE AssetSerialTranID IN (SELECT MAX(AssetSerialTranID) FROM assetSerialTran)");
        }


        internal static double GetDamageAmount(TransactionContext tc, int empID)
        {
            string sql = SQLParser.MakeSQL(@"
            select  SUM( c.DamageAmount)
            from AssetSerialTran c where 
            c.FaultybyEmpID = %n ",
            empID);

            var damount = tc.ExecuteScalar(sql);
            if (damount != DBNull.Value)
            {
                return Convert.ToDouble(damount);
            }
            else return 0;
        }
        internal static void UpdateAssetSerialStatus(AssetSerialTran serialTran, TransactionContext tc)
        {
            if (serialTran.TranType == EnumAssetTranType.Assign)
            {
                string sql = SQLParser.MakeSQL(
                @"UPDATE AssetSerial
                SET 
                AssetStatus=%n,AssetReceiverType=%n,AssetReceiverID=%n,AssignDate=%d,ReceivedDate=%d,IsTemporary=%b,TillDate=%d
                WHERE AssetSerialID= %n",
                (int)EnumAssetStatus.Assigned, (int)serialTran.AssetReceiverType, serialTran.AssetReceiverId, serialTran.AssignDate == null ? DateTime.Now : serialTran.AssignDate, serialTran.UploadReceiveDate == null ? DateTime.Now : serialTran.UploadReceiveDate, serialTran.IsTemporaryItem, serialTran.TillDate,serialTran.SerialId);
                tc.ExecuteNonQuery(sql);
            }
            else if (serialTran.TranType == EnumAssetTranType.Repaired)
            {
                string sql = SQLParser.MakeSQL(
               @"UPDATE AssetSerial
                SET 
                AssetStatus=%n,Remarks=%s,IsTemporary=%b,TillDate=%d,TentativeDate=%d
                WHERE AssetSerialID= %n",
               (int)EnumAssetStatus.Repaired,serialTran.Remarks,null,null,serialTran.TentativeDate, serialTran.SerialId);
                tc.ExecuteNonQuery(sql);
            }
            if (serialTran.TranType == EnumAssetTranType.Receive)
            {
                string sql = SQLParser.MakeSQL(
                @"UPDATE AssetSerial
                SET 
                AssetStatus=%n
                WHERE AssetSerialID= %n", (int)EnumAssetStatus.Free,
                serialTran.SerialId);
                tc.ExecuteNonQuery(sql);
            }

        }

        internal static void UpdateMailStatus(AssetSerialTran serialTran, TransactionContext tc)
        {
            if (serialTran.TranType == EnumAssetTranType.Assign)
            {
                string sql = SQLParser.MakeSQL(
                @"UPDATE AssetSerial
                SET 
                IsMailSent=%b
                WHERE AssetSerialID= %n",
                true,serialTran.SerialId);
                tc.ExecuteNonQuery(sql);
            }

        }

        internal static void UpdateAssetSerialHandoverStatus(AssetSerialTran serialTran, TransactionContext tc)
        {
            string sql = string.Empty;
            if (serialTran.IsFaultyItem == true)
            {
                sql = SQLParser.MakeSQL(
                @"UPDATE AssetSerial
                SET 
                AssetStatus=%n
                WHERE AssetSerialID= %n",
                (int)EnumAssetStatus.Faulty, serialTran.SerialId);
            }
            else
            {
                sql = SQLParser.MakeSQL(
               @"UPDATE AssetSerial
                SET 
                AssetStatus = 1
                WHERE AssetSerialID= %n",
                serialTran.SerialId);
            }
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Delete

        internal static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("DELETE FROM AssetSerialTran WHERE AssetSerialTranID=%n", id);
        }

        #endregion
    }
}