CEL_Payroll/Payroll.Service/SearchReport/DA/AuthorizedPersonDA.cs

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

namespace Payroll.Service
{
    #region  AuthorizedPersonDA
    internal class AuthorizedPersonDA
    {
        #region  Constructor

        private AuthorizedPersonDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, AuthorizedPerson item)
        {
            tc.ExecuteNonQuery("INSERT INTO AuthorisedPerson(PersonID, Name, Position, DESIGNATION, CreatedBy, CreatedDate)" +
            " VALUES(%n, %s, %n, %s, %n, %d)", 
            item.ID.Integer, item.Name, item.Sequence, item.Designation, 
            DataReader.GetNullValue(item.CreatedBy.Integer), DataReader.GetNullValue(item.CreatedDate));
        }
        #endregion

        #region  Update function
        internal static void Update(TransactionContext tc, AuthorizedPerson item)
        {
            tc.ExecuteNonQuery("UPDATE AuthorisedPerson SET Name=%s, Position=%n, DESIGNATION=%s, "+
            "ModifiedBy =%n, ModifiedDate = %d WHERE PersonID=%n",
            item.Name, item.Sequence, item.Designation, 
            DataReader.GetNullValue(item.ModifiedBy.Integer), DataReader.GetNullValue(item.ModifiedDate), item.ID.Integer);
        }
        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM AuthorisedPerson Order by Position");
        }

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM AuthorisedPerson WHERE PersonID=%n", nID.Integer);
        }
        public static object GetMaxPosition(TransactionContext tc)
        {
            return tc.ExecuteScalar("SELECT MAX(Position) FROM AuthorisedPerson");
        }

        internal static IDataReader GetByReportID(TransactionContext tc, ID reportID)
        {
            return tc.ExecuteReader(@"SELECT ap.* 
                                      FROM AuthorisedPerson ap ,REPORTAUTHORISATION ra
                                      WHERE ap.PERSONID = ra.PERSONID
                                      AND ra.REPORTID = %n", reportID.Integer);
        }
        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM [AuthorisedPerson] WHERE PersonID=%n", nID.Integer);
        }

        #endregion

        #region Other Functions
        public static bool IsExist(TransactionContext tc, string Name)
        {
            string str = Ease.CoreV35.DataAccess.SQLParser.MakeSQL("SELECT COUNT(*) FROM AuthorisedPerson Where Name = %s", Name);
            object obj = tc.ExecuteScalar(str);
            return Convert.ToInt32(obj) > 0;
        }

        //Get Image
        public static DataSet GetImage(SqlConnection connection, int nPersonID)
        {
            SqlDataAdapter sDataAdapter = new SqlDataAdapter();
            DataSet dSet = new DataSet();
            SqlCommand command = new SqlCommand("SELECT SignatureData FROM AuthorisedPerson WHERE PersonID=@PersonID", connection);
            command.Parameters.Add("@PersonID", SqlDbType.Int).Value = nPersonID;
            sDataAdapter.SelectCommand = command;            
            connection.Open();            
            sDataAdapter.Fill(dSet);
            return dSet;
        }

        //Save Image
        public static void UpdateData(SqlConnection connection, int nPersonID, byte[] SignatureData)
        {
            SqlCommand command = new SqlCommand("UPDATE AuthorisedPerson SET " + "SignatureData=@Signature WHERE PersonID=@PersonID ", connection);
            command.Parameters.Add("@PersonID", SqlDbType.Int).Value = nPersonID;
            command.Parameters.Add("@Signature", SqlDbType.Image, SignatureData.Length).Value = SignatureData;
            connection.Open();
            command.ExecuteNonQuery();
        }
        #endregion
    }

    #endregion
}