112 lines
4.1 KiB
C#
112 lines
4.1 KiB
C#
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
|
|
}
|