82 lines
4.8 KiB
C#
82 lines
4.8 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
|
|||
|
|
{
|
|||
|
|
class PremiumProcessDA
|
|||
|
|
{
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM PremiumProcess WHERE PremiumProcessID=%n", id.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM PremiumProcess");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, PremiumProcess oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO PremiumProcess (PremiumProcessID, Month, Factor, PremierRate)" +
|
|||
|
|
" VALUES(%n, %d, %n, %n)", oItem.ID.Integer, oItem.Month, oItem.Factor, oItem.PremierRate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, PremiumProcess oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE PremiumProcess SET Month=%d, Factor=%n, PremierRate=%n" +
|
|||
|
|
" WHERE PremiumProcessID=%n", oItem.Month, oItem.Factor, oItem.PremierRate, oItem.ID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, ID id)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM PremiumProcessDetail WHERE PremiumProcessID=%n", id.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM PremiumProcess WHERE PremiumProcessID=%n", id.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetGroupInsurance(TransactionContext tc, DateTime dtCur)
|
|||
|
|
{
|
|||
|
|
DateTime dtCurFrom = GlobalFunctions.FirstDateOfMonth(dtCur);
|
|||
|
|
DateTime dtCurTo = GlobalFunctions.LastDateOfMonth(dtCur);
|
|||
|
|
DateTime dtPrevFrom = GlobalFunctions.FirstDateOfYear(dtCur);
|
|||
|
|
DateTime dtPrevTo = GlobalFunctions.LastDateOfMonth(dtCur.AddMonths(-1));
|
|||
|
|
string strSQLQuery = SQLParser.MakeSQL(@"SELECT emp.EMPLOYEENO EmployeeID,emp.NAME Name,grd.DESCRIPTION Grade,
|
|||
|
|
pos.Level1 Division,pos.Level2 Department,pos.RC,
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.Basic) as nvarchar) 'Actual Basic',
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.Gross) as nvarchar) Gross,
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.AssuredAmount) as nvarchar) 'Sum Assured Amount',
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.AssuredAmount/1000) as nvarchar) 'Per 1000',
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.PremiumAmount) as nvarchar) 'Premium Amount',
|
|||
|
|
Cast(CONVERT(DECIMAL(36,0),curtab.PremiumAmount-prevtab.PremiumAmount) as nvarchar) 'Adjusted Premium Amount',
|
|||
|
|
Cast(CONVERT(DECIMAL(36,2),curtab.PremierRate) as nvarchar) 'Rate BDT.' FROM
|
|||
|
|
(SELECT ppd.EmployeeID, ppd.Basic,ppd.Gross,ppd.AssuredAmount,ppd.PremiumAmount,pp.PremierRate
|
|||
|
|
FROM PremiumProcess pp,PremiumProcessDetail ppd
|
|||
|
|
WHERE pp.PremiumProcessID=ppd.PremiumProcessID
|
|||
|
|
AND pp.Month BETWEEN %d AND %d)curtab
|
|||
|
|
LEFT OUTER JOIN Employee emp ON emp.EMPLOYEEID=curtab.EmployeeID
|
|||
|
|
full OUTER join
|
|||
|
|
(SELECT ppd.EmployeeID, sum(ppd.PremiumAmount)PremiumAmount
|
|||
|
|
FROM PremiumProcess pp,PremiumProcessDetail ppd
|
|||
|
|
WHERE pp.PremiumProcessID=ppd.PremiumProcessID
|
|||
|
|
AND pp.Month BETWEEN %d AND %d
|
|||
|
|
GROUP BY ppd.EmployeeID)prevtab ON prevtab.employeeid=curtab.employeeid
|
|||
|
|
|
|||
|
|
LEFT OUTER join Grades grd ON grd.GRADEID=emp.GRADEID
|
|||
|
|
LEFT OUTER join EmpCurrentPosition pos ON pos.employeeid=emp.EMPLOYEEID
|
|||
|
|
|
|||
|
|
", dtCurFrom, dtCurTo, dtPrevFrom, dtPrevTo);
|
|||
|
|
return tc.ExecuteDataSet(strSQLQuery);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetByMonth(TransactionContext tc, DateTime month)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM PremiumProcess where Month=%d",month);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|