404 lines
24 KiB
C#
404 lines
24 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using Ease.CoreV35.DataAccess;
|
|||
|
|
using Ease.CoreV35.Model;
|
|||
|
|
using System.Data;
|
|||
|
|
using Payroll.BO;
|
|||
|
|
|
|||
|
|
namespace Payroll.Service
|
|||
|
|
{
|
|||
|
|
internal class HRRequestDA
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, BO.HRRequest item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Insert into HRRequest
|
|||
|
|
(HRRequestID,ClaimNumber,EmployeeID,Description,
|
|||
|
|
Amount,RequestType,Remarks,RequestDate,
|
|||
|
|
SubmittedOriginalPaper,WfStatus,LetterTypeID,IssuerAddress,
|
|||
|
|
FromDate,ToDate,IsOfficial,IsWithFamily,
|
|||
|
|
RequestComplted,LoanID,Justification,
|
|||
|
|
Taxable,NonTaxable,Purpose,TypeOfProperty,Land,ValueofProperty,ExoectedDateofCompletion,NameofVendor,AddressofVendor,Teleohone,TaxAmount,Dependency,DependencyAges,ExceptionReason,InsuranceVendor,CostEstimate,InsuranceDate,ExpectedDateofRegistration,LocationofProperty,MailingAddress, CountryID)
|
|||
|
|
Values(%n,%s,%n,%s,%n,%n,%s,%d,%b,%n,%n,%s,%d,%d,%b,%b,%b,%n,%s,%n,%n,%s,%s,%s,%n,%d,%s,%s,%s,%n,%s,%s,%s,%s,%n,%d,%d,%s,%s, %n)",
|
|||
|
|
item.ID.Integer, item.ClaimNumber, item.EmployeeID.Integer,
|
|||
|
|
DataReader.GetNullValue(item.Description),
|
|||
|
|
DataReader.GetNullValue(item.Amount),
|
|||
|
|
(int)item.RequestType, DataReader.GetNullValue(item.Remarks),
|
|||
|
|
DataReader.GetNullValue(item.RequestDate),
|
|||
|
|
item.SubmittedOriginalPaper, (int)item.WfStatus,
|
|||
|
|
DataReader.GetNullValue(item.LetterTypeID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.IssuerAddress),
|
|||
|
|
DataReader.GetNullValue(item.FromDate),
|
|||
|
|
DataReader.GetNullValue(item.ToDate),
|
|||
|
|
item.IsOfficial, item.IsWithFamily, item.RequestComplted,
|
|||
|
|
DataReader.GetNullValue(item.LoanID.Integer),item.Justification,
|
|||
|
|
DataReader.GetNullValue(item.Taxable),DataReader.GetNullValue(item.NonTaxable),
|
|||
|
|
item.Purpose, item.TypeOfProperty, item.Land, item.ValueofProperty,item.ExoectedDateofCompletion, item.NameofVendor, item.AddressofVendor, item.Teleohone, item.TaxAmount, item.Dependency, item.DependencyAges, item.ExceptionReason, item.InsuranceVendor, item.CostEstimate, item.InsuranceDate,item.ExpectedDateofRegistration,item.LocationofProperty,item.MailingAddress, item.CountryID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, BO.HRRequest item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequest
|
|||
|
|
Set ClaimNumber=%s,EmployeeID=%n,Description=%s,
|
|||
|
|
Amount=%n,RequestType=%n,Remarks=%s,RequestDate=%d,
|
|||
|
|
SubmittedOriginalPaper=%b,WfStatus=%n,LetterTypeID=%n,IssuerAddress=%s,
|
|||
|
|
FromDate=%d,ToDate=%d,IsOfficial=%b,IsWithFamily=%b ,RequestComplted=%b,
|
|||
|
|
LoanID=%n,Justification=%s,Taxable=%n,NonTaxable=%n,
|
|||
|
|
Purpose=%s,TypeOfProperty=%s,Land=%s,ValueofProperty=%n,NameofVendor=%s,AddressofVendor=%s,Teleohone=%s,TaxAmount=%n,Dependency=%s,DependencyAges=%s,ExceptionReason=%s,InsuranceVendor=%s,CostEstimate=%n,InsuranceDate=%d,ExpectedDateofRegistration=%d,LocationofProperty=%s,MailingAddress=%s
|
|||
|
|
Where HRRequestID = %n",
|
|||
|
|
item.ClaimNumber,
|
|||
|
|
item.EmployeeID.Integer,
|
|||
|
|
DataReader.GetNullValue(item.Description),
|
|||
|
|
DataReader.GetNullValue(item.Amount),
|
|||
|
|
(int)item.RequestType, DataReader.GetNullValue(item.Remarks),
|
|||
|
|
DataReader.GetNullValue(item.RequestDate),
|
|||
|
|
item.SubmittedOriginalPaper,
|
|||
|
|
(int)item.WfStatus, DataReader.GetNullValue(item.LetterTypeID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.IssuerAddress),
|
|||
|
|
DataReader.GetNullValue(item.FromDate),
|
|||
|
|
DataReader.GetNullValue(item.ToDate),
|
|||
|
|
item.IsOfficial, item.IsWithFamily, item.RequestComplted,
|
|||
|
|
DataReader.GetNullValue(item.LoanID, IDType.Integer),item.Justification,
|
|||
|
|
DataReader.GetNullValue(item.Taxable), DataReader.GetNullValue(item.NonTaxable),
|
|||
|
|
item.Purpose, item.TypeOfProperty, item.Land, item.ValueofProperty, item.NameofVendor, item.AddressofVendor, item.Teleohone, item.TaxAmount,
|
|||
|
|
item.Dependency, item.DependencyAges, item.ExceptionReason, item.InsuranceVendor, item.CostEstimate, item.InsuranceDate, item.ExpectedDateofRegistration,item.LocationofProperty,item.MailingAddress,
|
|||
|
|
item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void InsertLetterRequest(TransactionContext tc, HRRequest item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Insert into HRRequest
|
|||
|
|
(HRRequestID,ClaimNumber,EmployeeID,RequestType,RequestDate,
|
|||
|
|
WfStatus,LetterTypeID,IssuerAddress,
|
|||
|
|
FromDate,ToDate,IsOfficial,IsWithFamily,EmbassyName,EmbassyAddress, Description,CountryID, RequestComplted)
|
|||
|
|
Values(%n,%s,%n,%n,%d,%n,%n,%s,%d,%d,%b,%b,%s,%s, %s, %n, %n)",
|
|||
|
|
item.ID.Integer,item.ClaimNumber,item.EmployeeID.Integer,
|
|||
|
|
(int)item.RequestType,
|
|||
|
|
DataReader.GetNullValue(item.RequestDate),
|
|||
|
|
(int)item.WfStatus,
|
|||
|
|
DataReader.GetNullValue(item.LetterTypeID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.IssuerAddress),
|
|||
|
|
DataReader.GetNullValue(item.FromDate),
|
|||
|
|
DataReader.GetNullValue(item.ToDate),
|
|||
|
|
item.IsOfficial, item.IsWithFamily, item.EmbassyName, item.EmbassyAddress, item.Description,
|
|||
|
|
item.CountryID.Integer, Convert.ToInt16(item.RequestComplted));
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateLetterRequest(TransactionContext tc, HRRequest item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequest
|
|||
|
|
Set ClaimNumber=%s,EmployeeID=%n,RequestType=%n,RequestDate=%d,
|
|||
|
|
WfStatus=%n,LetterTypeID=%n,IssuerAddress=%s,
|
|||
|
|
FromDate=%d,ToDate=%d,IsOfficial=%b,IsWithFamily=%b, EmbassyName=%s,EmbassyAddress=%s, Description=%s
|
|||
|
|
Where HRRequestID=%n",
|
|||
|
|
item.ClaimNumber, item.EmployeeID.Integer,
|
|||
|
|
(int)item.RequestType,
|
|||
|
|
DataReader.GetNullValue(item.RequestDate),
|
|||
|
|
(int)item.WfStatus,
|
|||
|
|
DataReader.GetNullValue(item.LetterTypeID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.IssuerAddress),
|
|||
|
|
DataReader.GetNullValue(item.FromDate),
|
|||
|
|
DataReader.GetNullValue(item.ToDate),
|
|||
|
|
item.IsOfficial, item.IsWithFamily, item.EmbassyName, item.EmbassyAddress, item.Description, item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where HRRequestID=%n", nID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLFAMedicalClaimByEmpID(TransactionContext tc, ID employeeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType IN(%n,%n) ORDER BY RequestDate DESC", employeeID.Integer,EnumRequestType.LTA,EnumRequestType.Medical);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLFAMedicalClaim(TransactionContext tc, ID id, DateTime dateTime1, DateTime dateTime2)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType IN(%n,%n) and RequestDate BETWEEN %d and %d ORDER BY RequestDate DESC", id.Integer, EnumRequestType.LTA, EnumRequestType.Medical, dateTime1, dateTime2);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLoanCalimbyScheduleDate(TransactionContext tc, DateTime fromdate, DateTime todate)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT * FROM HRRequest Where RequestComplted=%b AND HRRequestID IN( select HRRequestID
|
|||
|
|
from HRRequestPaymentSchedule where PaymentDate BETWEEN %d and %d) ", false, fromdate, todate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static IDataReader GetInsuranceClaimByEmpID(TransactionContext tc, ID id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n ORDER BY RequestDate DESC", id.Integer, EnumRequestType.Insurance);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetInsuranceClaim(TransactionContext tc, ID id, DateTime dateTime1, DateTime dateTime2)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n and RequestDate BETWEEN %d and %d ORDER BY RequestDate DESC", id.Integer, EnumRequestType.Insurance,dateTime1,dateTime2);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLetterClaimByEmpID(TransactionContext tc, ID id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n ORDER BY RequestDate DESC", id.Integer, EnumRequestType.Letter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLetterClaim(TransactionContext tc, ID id, DateTime dateTime1, DateTime dateTime2)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n and RequestDate BETWEEN %d and %d ORDER BY RequestDate DESC", id.Integer, EnumRequestType.Letter,dateTime1,dateTime2);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetLoanApplicationByEmpID(TransactionContext tc, ID empID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT hrr.*,l.[DESCRIPTION] as LoanName
|
|||
|
|
FROM (SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n) hrr
|
|||
|
|
LEFT JOIN LOANBASIC l
|
|||
|
|
ON l.LOANID = hrr.LoanID
|
|||
|
|
ORDER BY hrr.RequestDate DESC", empID.Integer, (int)EnumRequestType.Loan);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLoanApplicationByDateRange(TransactionContext tc, ID empID, DateTime startDate, DateTime endDate)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT hrr.*,l.[DESCRIPTION] as LoanName
|
|||
|
|
FROM (SELECT * FROM HRRequest Where EmployeeID=%n and RequestType=%n
|
|||
|
|
and RequestDate BETWEEN %d and %d) hrr
|
|||
|
|
LEFT JOIN LOANBASIC l
|
|||
|
|
ON l.LOANID = hrr.LoanID
|
|||
|
|
ORDER BY hrr.RequestDate DESC",
|
|||
|
|
empID.Integer, EnumRequestType.Loan, startDate, endDate);
|
|||
|
|
}
|
|||
|
|
public static DataSet GetInshuranceDetailByReqID(TransactionContext tc, int hRrequstId)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteDataSet(@"SELECT hd.HRRequestDetailID,hd.HRRequestID,hd.EmployeeID
|
|||
|
|
,hd.Remarks,hd.RemarksDate,hd.IsRead,hd.IsHR,
|
|||
|
|
CASE
|
|||
|
|
WHEN hd.EmployeeID = (SELECT h.EmployeeID FROM HRRequest h WHERE hd.HRRequestID = h.HRRequestID) THEN 'Me'
|
|||
|
|
ELSE (SELECT e.[NAME] FROM EMPLOYEE e WHERE e.EMPLOYEEID = hd.EmployeeID)
|
|||
|
|
END AS Sender,
|
|||
|
|
CASE hd.IsRead
|
|||
|
|
WHEN 1 THEN 'Read'
|
|||
|
|
ELSE 'Not Read'
|
|||
|
|
END AS ReadStstus
|
|||
|
|
FROM HRRequestDetail hd WHERE hd.HRRequestID = %n", hRrequstId);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader GetHRequestDetails(TransactionContext tc, ID iD)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(@"SELECT hrd.*,v.[Name] CourierName FROM HRRequestDetail hrd
|
|||
|
|
LEFT JOIN
|
|||
|
|
Vendor v
|
|||
|
|
ON v.VendorID = hrd.CourierID
|
|||
|
|
WHERE hrd.HRRequestID = %n", iD.Integer);
|
|||
|
|
// return tc.ExecuteReader(@"SELECT hd.HRRequestDetailID,hd.HRRequestID,hd.EmployeeID
|
|||
|
|
// ,hd.Remarks,hd.RemarksDate,hd.IsRead,hd.IsHR,hd.CourierID,
|
|||
|
|
// CASE
|
|||
|
|
// WHEN hd.EmployeeID = (SELECT h.EmployeeID FROM HRRequest h WHERE hd.HRRequestID = h.HRRequestID) THEN 'Me'
|
|||
|
|
// ELSE (SELECT e.[NAME] FROM EMPLOYEE e WHERE e.EMPLOYEEID = hd.EmployeeID)
|
|||
|
|
// END AS Sender,
|
|||
|
|
// CASE hd.IsRead
|
|||
|
|
// WHEN 1 THEN 'Read'
|
|||
|
|
// ELSE 'Not Read'
|
|||
|
|
// END AS detailReadStatus
|
|||
|
|
// FROM HRRequestDetail hd WHERE hd.HRRequestID = %n", iD.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public static IDataReader GetHRRequestPassports(TransactionContext tc, ID iD)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * from HRRequestPassport where HRRequestID=%n", iD.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public static IDataReader GetHRRequestLoans(TransactionContext tc, ID iD)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * from HRRequestLoan where HRRequestID=%n", iD.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public static IDataReader GetHRRequestLoanDetails(TransactionContext tc, ID iD)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * from HRRequestLoanDetail where HRRequestID=%n", iD.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public static IDataReader GetHRRequestPaymentSchedules(TransactionContext tc, ID iD)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * from HRRequestPaymentSchedule where HRRequestID=%n order By PaymentDate", iD.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static void InsertDetails(TransactionContext tc, HRRequestDetail item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Insert into HRRequestDetail (HRRequestDetailID,HRRequestID,EmployeeID,CourierID,
|
|||
|
|
CourierDate,TokenNumber,Remarks,RemarksDate,IsRead,IsHR)
|
|||
|
|
Values(%n,%n,%n,%n,%d,%s,%s,%d,%b,%b)",
|
|||
|
|
item.ID.Integer,item.HRRequestID.Integer,item.EmployeeID.Integer,
|
|||
|
|
DataReader.GetNullValue(item.CourierID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.CourierDate),
|
|||
|
|
DataReader.GetNullValue(item.TokenNumber),item.Remarks,
|
|||
|
|
item.RemarksDate,item.IsRead,item.IsHR);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateDetails(TransactionContext tc, HRRequestDetail item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequestDetail SET
|
|||
|
|
HRRequestID=%n,EmployeeID=%n,CourierID=%n,
|
|||
|
|
CourierDate=%d,TokenNumber=%s,Remarks=%s,RemarksDate=%d,
|
|||
|
|
IsRead=%b,IsHR=%b Where HRRequestDetailID=%n",
|
|||
|
|
item.HRRequestID.Integer, item.EmployeeID.Integer,
|
|||
|
|
DataReader.GetNullValue(item.CourierID.Integer),
|
|||
|
|
DataReader.GetNullValue(item.CourierDate),
|
|||
|
|
DataReader.GetNullValue(item.TokenNumber), item.Remarks,
|
|||
|
|
item.RemarksDate, item.IsRead, item.IsHR, item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void InsertLoanDetail (TransactionContext tc, HRRequestLoanDetail item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Insert into HRRequestLoanDetail (HRRequestLoanDetailID,HRRequestID,[Description],DescriptionValue,SerialNo)
|
|||
|
|
Values(%n,%n,%s,%s,%n)",
|
|||
|
|
item.ID.Integer, item.HRRequestID.Integer, item.Description,item.DescriptionValue,item.SerialNo);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static void InsertLoan(TransactionContext tc, HRRequestLoan item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Insert into HRRequestLoan (HRRequestLoanID,HRRequestID,Name,Code)
|
|||
|
|
Values(%n,%n,%s,%s)",
|
|||
|
|
item.ID.Integer, item.HRRequestID.Integer, item.Name, item.Code);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static void InsertPaymentSchedule(TransactionContext tc, HRRequestPaymentSchedule item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Insert into HRRequestPaymentSchedule (HRRequestPaymentScheduleID,HRRequestID,PaymentDate,PaymentDescription,Amount)
|
|||
|
|
Values(%n,%n,%d,%s,%n)",
|
|||
|
|
item.ID.Integer, item.HRRequestID.Integer, item.PaymentDate, item.PaymentDescription, item.Amount);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateLoan(TransactionContext tc, HRRequestLoan item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequestLoan SET
|
|||
|
|
HRRequestID=%n,Name=%s,Code=%s Where HRRequestDetailID=%n",
|
|||
|
|
item.HRRequestID.Integer, item.Name, item.Code, item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteHRRequestLoan(TransactionContext tc, ID HRRequestID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Delete From HRRequestLoan Where HRRequestID=%n", HRRequestID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteHRRequestLoanDetail(TransactionContext tc, ID HRRequestID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Delete From HRRequestLoanDetail Where HRRequestID=%n", HRRequestID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteHRRequestPaymentSchedule(TransactionContext tc, ID HRRequestID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Delete From HRRequestPaymentSchedule Where HRRequestID=%n", HRRequestID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void ReadHRRequestDetail(TransactionContext tc, ID HRRequestDetailID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Update HRRequestDetail SET IsRead = %b where HRRequestDetailID=%n",true,HRRequestDetailID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetHRRequetByWFStatus(TransactionContext tc, enumwfStatus status)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM HRRequest Where WfStatus = %n and RequestComplted=%b ORDER BY RequestDate DESC", (int)status,false);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static int GetMaxClaimNumber(TransactionContext tc, string year)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT SUBSTRING( MAX(ClaimNumber) ,5 , 4 ) As ReqNo FROM
|
|||
|
|
(SELECT * FROM HRRequest hr WHERE hr.ClaimNumber Like %s)AS tble", year);
|
|||
|
|
Object o = tc.ExecuteScalar(sql);
|
|||
|
|
return o.ToString() == string.Empty ? 0 : Convert.ToInt32(o.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void InsertHRPassport(TransactionContext tc, HRRequestPassport item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Insert into HRRequestPassport
|
|||
|
|
(HRRequestPassportID,HRRequestID,Name,Relation,PassportNo, ObjectID)
|
|||
|
|
Values(%n,%n,%s,%s,%s, %n)",
|
|||
|
|
item.ID.Integer, item.HRRequestID.Integer, item.Name,
|
|||
|
|
item.Relation, item.PassportNo,item.ObjectID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdatePaymentRequest(TransactionContext tc, HRRequest item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequest
|
|||
|
|
Set PaymentRequestCompleted=%b, PaymentMode=%n, BankID=%n, AccountNo=%s, PayRequestDate=%d, PayRemarks=%s
|
|||
|
|
Where HRRequestID = %n",
|
|||
|
|
item.IsPaymentRequested , item.PaymentMode, DataReader.GetNullValue( item.BankID, IDType.Integer),
|
|||
|
|
item.AccountNo, DataReader.GetNullValue(item.PaidDate), item.PaidRemarks ,
|
|||
|
|
item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateLoanShedulePaymentRequest(TransactionContext tc, HRRequestPaymentSchedule item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequestPaymentSchedule
|
|||
|
|
Set IsPaymentRequested=%b, PaymentMode=%n, BankID=%n, AccountNo=%s, PayRequestDate=%d, PayRemarks=%s
|
|||
|
|
Where HRRequestPaymentScheduleID = %n",
|
|||
|
|
item.IsPaymentRequested, item.PaymentMode, DataReader.GetNullValue(item.BankID, IDType.Integer),
|
|||
|
|
item.AccountNo, DataReader.GetNullValue(item.PaidDate), item.Remarks,
|
|||
|
|
item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void UpdateLoanSheduleLoanIssueID(TransactionContext tc, HRRequestPaymentSchedule item)
|
|||
|
|
{
|
|||
|
|
string sql
|
|||
|
|
= SQLParser.MakeSQL(@"Update HRRequestPaymentSchedule
|
|||
|
|
Set LoanIssueID=%n
|
|||
|
|
Where HRRequestPaymentScheduleID = %n",
|
|||
|
|
item.LoanIssueID.Integer, item.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataSet GetPaymentRequestedItems(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteDataSet(@"SELECT pays.*,e.EmployeeNo,e.Name AS EmpName, e.DesigDescription AS [Designation],
|
|||
|
|
CASE pays.RequestType
|
|||
|
|
WHEN 1 THEN 'LTA'
|
|||
|
|
WHEN 2 THEN 'Medical'
|
|||
|
|
WHEN 3 THEN 'Insurance'
|
|||
|
|
WHEN 4 THEN 'Letter'
|
|||
|
|
ELSE 'Loan'
|
|||
|
|
END AS RequestName
|
|||
|
|
FROM
|
|||
|
|
(SELECT hd.HRRequestID, Hd.RequestType, 0 as HRLoanID, hd.EmployeeID, hd.Amount from HRRequest hd
|
|||
|
|
Where PaymentRequestCompleted=1 AND HD.RequestType<>5 and hd.PaymentMode =0
|
|||
|
|
Union
|
|||
|
|
select HL.HRRequestID, HR.RequestType, HL.HRRequestPaymentScheduleID as HRLoanID, HR.EmployeeID,
|
|||
|
|
HL.Amount from HRRequestPaymentSchedule HL, HRRequest HR
|
|||
|
|
where HR.HRRequestID=HL.HRRequestID and HL.IsPaymentRequested=1 AND HR.RequestType=5 and HL.PaymentMode =0) AS pays,Employee e
|
|||
|
|
WHERE e.EmployeeID = pays.EmployeeID ");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|