using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Ease.CoreV35.DataAccess;
using Ease.CoreV35.Model;
using Payroll.BO;

namespace Payroll.Service
{
    internal class FndMngmentWFDA
    {
        
        #region Get
        
        internal static IDataReader Get(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM FndMngmentWF where FndMngmentWFID=%n", id.Integer);
        }
        
        public static IDataReader Get(TransactionContext tc, EnumStatus status)
        {
            string sSql = string.Empty;
            
            if (status == EnumStatus.Regardless)
            {
                sSql = SQLParser.MakeSQL("SELECT * FROM FndMngmentWF");
            }
            else
            {
                sSql = SQLParser.MakeSQL("SELECT * FROM FndMngmentWF WHERE Status=%n", (int)status);
            }

            return tc.ExecuteReader(sSql);
        }

        internal static IDataReader GetFndMngmntEmps(TransactionContext tc, ID FndManagementID)
        {
            string sSql = string.Empty;

            sSql = SQLParser.MakeSQL(@"SELECT fdemp.*, IsNULL(d.Name,'') DesigName from
                                      (SELECT fnE.*,e.EmployeeNo,e.[Name] as EmployeeName,e.DesignationID 
                                       FROM FndMngmentEmp fnE,Employee e
                                       WHERE fnE.EmployeeID = e.EmployeeID and FndMngmentWFID=%n) fdemp
                                       LEFT JOIN Designation d
                                       ON fdemp.DesignationID = d.DesignationID", FndManagementID.Integer);

            return tc.ExecuteReader(sSql);
        }


        public static IDataReader GetFndMngmntEmpRmrks(TransactionContext tc, ID FndMngmentEmpID)
        {
            string sSql = string.Empty;

            sSql = SQLParser.MakeSQL("SELECT * FROM FndMngmentEmpRemarks WHERE FndMngmentEmpID=%n", FndMngmentEmpID.Integer);
            
            return tc.ExecuteReader(sSql);
        }

        internal static IDataReader GetAllFndMngmentEmpRemarks(TransactionContext tc, ID FndMngmentWFID)
        {
            string sSql = string.Empty;

            sSql = SQLParser.MakeSQL("SELECT * FROM FndMngmentEmpRemarks WHERE FndMngmentWFID=%n", FndMngmentWFID.Integer);

            return tc.ExecuteReader(sSql);
        }

        
        #endregion

        #region Delete
        
        public static void Delete(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM FndMngmentWF WHERE FndMngmentWFID=%n", id.Integer);
        }

        public static void DeleteFndMngmentEmps(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM FndMngmentEmp WHERE FndMngmentWFID=%n", id.Integer);
        }

        public static void DeleteFndMngmentEmpRemarkses(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM FndMngmentEmpRemarks WHERE FndMngmentWFID=%n", id.Integer);
        }

        #endregion

        #region Insert

        public static void Insert(TransactionContext tc, FndMngmentWF item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO FndMngmentWF(FndMngmentWFID, PurchaseType,PurchaseAmount,Description, EmployeeID, PFAmount, CFAmount, PFInterest,CFInterest,Gratuity,Status,CreatedBy,CreationDate) 
                                                               VALUES(%n,%s,%n,%s,%n,%n,%n,%n,%n,%n,%n,%n,%d)",
                                                               item.ID.Integer, item.PurchaseType, DataReader.GetNullValue(item.PurchaseAmount), DataReader.GetNullValue(item.Description), DataReader.GetNullValue(item.EmployeeID, IDType.Integer), DataReader.GetNullValue(item.PFAmount), DataReader.GetNullValue(item.CFAmount), DataReader.GetNullValue(item.PFInterest), DataReader.GetNullValue(item.CFInterest), DataReader.GetNullValue(item.Gratuity),(int)item.Status,item.CreatedBy.Integer, item.CreatedDate);
            tc.ExecuteNonQuery(sql);

        }

        public static void InsertFndMngmentEmp(TransactionContext tc, FndMngmentEmp item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO FndMngmentEmp(FndMngmentEmpID, FndMngmentWFID,EmployeeID,EmailSent, WebSent, SendDate) 
                                                               VALUES(%n,%n,%n,%b,%b,%d)",
                                                               item.ID.Integer, item.FndMngmentWFID.Integer, item.EmployeeID.Integer, item.EmailSent, item.WebSent,item.SentDate);
            tc.ExecuteNonQuery(sql);

        }

        public static void InsertFndMngmentEmpRemarks(TransactionContext tc, FndMngmentEmpRemarks item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO FndMngmentEmpRemarks(FndMngmentEmpRemarksID,FndMngmentEmpID, FndMngmentWFID,Remarks,RemarksDateTime, IsHR) 
                                                               VALUES(%n,%n,%n,%s,%D,%b)",
                                                               item.ID.Integer,DataReader.GetNullValue(item.FndMngmentEmpID,IDType.Integer), item.FndMngmentWFID.Integer, item.Remarks, item.RemarksDateTime, item.IsHR);
            tc.ExecuteNonQuery(sql);

        }


        #endregion

        #region Update
        
        internal static void Update(TransactionContext tc, FndMngmentWF item)
        {
            string sql =
                SQLParser.MakeSQL(
                    @"UPDATE FndMngmentWF SET PurchaseType=%s,PurchaseAmount=%n,Description=%s, EmployeeID=%n, PFAmount=%n, CFAmount=%n, PFInterest=%n,CFInterest=%n,Gratuity=%n,Status=%n,ModifiedBy=%n,ModifiedDate=%d
                      WHERE FndMngmentWFID=%n", item.PurchaseType, DataReader.GetNullValue(item.PurchaseAmount), DataReader.GetNullValue(item.Description), DataReader.GetNullValue(item.EmployeeID, IDType.Integer), DataReader.GetNullValue(item.PFAmount), DataReader.GetNullValue(item.CFAmount), DataReader.GetNullValue(item.PFInterest), DataReader.GetNullValue(item.CFInterest), DataReader.GetNullValue(item.Gratuity),(int)item.Status,DataReader.GetNullValue(item.ModifiedBy,IDType.Integer), DataReader.GetNullValue(item.ModifiedDate), item.ID.Integer);
            tc.ExecuteNonQuery(sql);
        }

        #endregion  
      
    
        
    }
}