212 lines
11 KiB
C#
212 lines
11 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 EmployeeCostCenterDA
|
|
|
|
internal class EmployeeCostCenterDA
|
|
{
|
|
#region Constructor
|
|
|
|
private EmployeeCostCenterDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, EmployeeCostCenter item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("INSERT INTO EMPCOSTCENTER(ID, employeeID, monthDate, costCenterID, percentage, CurrentCC, CreatedBy, CreationDate)" +
|
|
" VALUES(%n, %n, %d, %n, %n, %b, %n, %d)", item.ID.Integer, item.EmployeeID.Integer, item.MonthDate, item.CostCenterID.Integer, item.Percentage, item.IsCurrentCC, item.CreatedBy.Integer, item.CreatedDate);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, EmployeeCostCenter item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("UPDATE EMPCOSTCENTER SET employeeID=%n, monthDate=%d, costCenterID=%n, percentage=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE ID=%n", item.EmployeeID.Integer, item.MonthDate, item.CostCenterID.Integer, item.Percentage, item.ModifiedBy.Integer, DataReader.GetNullValue(item.ModifiedDate), item.ID.Integer);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT EMPCOSTCENTER.* FROM EMPCOSTCENTER, Employee WHERE EMPCOSTCENTER.EmployeeID = Employee.EmployeeID AND Employee.Status=%n AND"
|
|
+ " CurrentCC=%b ORDER BY EmployeeNo, COSTCENTERID", EnumEmployeeStatus.Live, true);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID employeeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EMPCOSTCENTER WHERE "
|
|
+ " EMPLOYEEID=%n AND CurrentCC=%b ", employeeID.Integer, true);
|
|
}
|
|
|
|
internal static IDataReader GetByCCID(TransactionContext tc, ID nCCID)
|
|
{
|
|
return tc.ExecuteReader("SELECT EMPCOSTCENTER.* FROM EMPCOSTCENTER, Employee WHERE EMPCOSTCENTER.EmployeeID = Employee.EmployeeID AND Employee.Status=%n AND"
|
|
+ " COSTCENTERID=%n AND CurrentCC=%b ORDER BY EmployeeNo, COSTCENTERID", EnumEmployeeStatus.Live, nCCID.Integer, true);
|
|
}
|
|
internal static IDataReader GetByEmpIDCCID(TransactionContext tc, ID nCCID, ID nEmpID)
|
|
{
|
|
return tc.ExecuteReader("SELECT EMPCOSTCENTER.* FROM EMPCOSTCENTER, Employee WHERE EMPCOSTCENTER.EmployeeID = Employee.EmployeeID AND Employee.Status=%n AND"
|
|
+ " COSTCENTERID=%n AND CurrentCC=%b AND EMPCOSTCENTER.EMPLOYEEID=%n ORDER BY EmployeeNo, COSTCENTERID", EnumEmployeeStatus.Live, nCCID.Integer, true, nEmpID.Integer);
|
|
}
|
|
internal static DataSet GetEmpCC(TransactionContext tc,DateTime dEffectDate,string SEmpID)
|
|
{
|
|
DataSet Emp = new DataSet();
|
|
tc.FillDataset(Emp, new string [] { "Employee"},"Select EmployeeID,EmployeeNo,Name from Employee","");
|
|
tc.FillDataset(Emp, new string[] { "EmpCC" }, "Select EmployeeID,CostCenterID,CurrentCC,Percentage from EmpCostCenter", "");
|
|
tc.FillDataset(Emp, new string[] { "CRG" }, "Select CRGID,Description from CRG", "");
|
|
Emp.Tables["Employee"].Columns.Add("CostCenterID",typeof(int));
|
|
Emp.Tables["Employee"].Columns.Add("Description",typeof(string));
|
|
Emp.Tables["Employee"].Columns.Add("Percentage", typeof(double));
|
|
|
|
#region Old Code
|
|
|
|
//return tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,CC.DESCRIPTION,EMC.PERCENTAGE from EMPCOSTCENTER as EMC,EMPLOYEE as Emp,CRG as CC where"
|
|
// + " EMC.COSTCENTERID=CC.CRGID"
|
|
// + " and Emp.EMPLOYEEID=EMC.EMPLOYEEID"
|
|
// + " and EMP.EMPLOYEEID in(%q)"
|
|
// + " and EMC.MONTHDATE=%d and EMC.CURRENTCC=1 order by EMp.EMPLOYEENO ", SEmpID, dEffectDate);
|
|
|
|
|
|
//return tc.ExecuteDataSet("Select EmpPer.* ,"
|
|
// + " (Select CRG.DESCRIPTION from CRG where CRG.CRGID=EmpPer.CC) as CCDes"
|
|
// + " from"
|
|
// + " (Select EMPLOYEE.EMPLOYEENO,EMPLOYEE.NAME,EMPLOYEE.EMPLOYEEID,"
|
|
// + " (select EMPCOSTCENTER.COSTCENTERID from EMPCOSTCENTER where EMPCOSTCENTER.EMPLOYEEID= EMPLOYEE.EMPLOYEEID ) as CC ,"
|
|
// + " (select EMPCOSTCENTER.CURRENTCC from EMPCOSTCENTER where EMPCOSTCENTER.EMPLOYEEID= EMPLOYEE.EMPLOYEEID ) as CurrentCC ,"
|
|
// + " (select EMPCOSTCENTER.PERCENTAGE from EMPCOSTCENTER where EMPCOSTCENTER.EMPLOYEEID= EMPLOYEE.EMPLOYEEID ) as Per "
|
|
// + " from EMPLOYEE"
|
|
// + " )as EmpPer"
|
|
// + " where EmpPer.EMPLOYEEID in(%q)"
|
|
// + " order by EmpPer.EMPLOYEENO ",SEmpID);
|
|
|
|
#endregion
|
|
|
|
return Emp;
|
|
}
|
|
|
|
internal static DateTime GetMaxDate(TransactionContext tc, string EmpID, DateTime dMonthDate)
|
|
{
|
|
DateTime Result;
|
|
object ob = tc.ExecuteScalar("Select Max(Monthdate) as MonthDate from EmpCostCenter where MonthDate < %d and EmployeeID=%s", dMonthDate, EmpID);
|
|
|
|
if (ob!=null && ob.ToString().Trim().Length>0)
|
|
{
|
|
Result = Convert.ToDateTime(ob);
|
|
}
|
|
else
|
|
{
|
|
Result = DateTime.MinValue;
|
|
}
|
|
|
|
return Result;
|
|
|
|
}
|
|
|
|
internal static DataSet GetEmpCCDetails(TransactionContext tc, DateTime dMonthDate, string nEmpID)
|
|
{
|
|
|
|
if (dMonthDate == DateTime.MinValue)
|
|
{
|
|
return tc.ExecuteDataSet("Select Emp.EmployeeNo,Emp.Name,C.Description,EmpCC.Percentage "
|
|
+ " from Employee as Emp,EmpCostCenter as EmpCC,CRG as C"
|
|
+ " where Emp.EmployeeID=EmpCC.EmployeeID"
|
|
+ " AND EmpCC.CostcenterID=C.CRGID"
|
|
+ " AND EmpCC.CurrentCC=1 "
|
|
+ " AND Emp.EmployeeID=%s", nEmpID);
|
|
|
|
}
|
|
else
|
|
{
|
|
return tc.ExecuteDataSet("Select Emp.EmployeeNo,Emp.Name,C.Description,EmpCC.Percentage "
|
|
+ " from Employee as Emp,EmpCostCenter as EmpCC,CRG as C"
|
|
+ " where Emp.EmployeeID=EmpCC.EmployeeID"
|
|
+ " AND EmpCC.CostcenterID=C.CRGID"
|
|
+ " AND EmpCC.Monthdate=%d"
|
|
+ " AND EmpCC.CurrentCC=0 "
|
|
+ " AND Emp.EmployeeID=%s", dMonthDate,nEmpID);
|
|
}
|
|
|
|
|
|
//return tc.ExecuteDataSet("SELECT Emp.EMPLOYEENO,Emp.NAME,"
|
|
// + " CurrCCName =CASE WHEN EMC.MONTHDATE=%d AND EMC.CURRENTCC=1 THEN CC.DESCRIPTION ELSE '' END,"
|
|
// + " CurrCCPer = CASE WHEN EMC.MONTHDATE=%d AND EMC.CURRENTCC=1 THEN EMC.PERCENTAGE ELSE '' END,"
|
|
// + " PrvCCName = CASE WHEN EMC.MONTHDATE=%d THEN CC.DESCRIPTION ELSE '' END,"
|
|
// + " PrvCCPer = CASE WHEN EMC.MONTHDATE=%d THEN EMC.PERCENTAGE ELSE '' END"
|
|
// + " FROM EMPCOSTCENTER as EMC,EMPLOYEE as Emp,CRG as CC "
|
|
// + " where"
|
|
// + " EMC.COSTCENTERID=CC.CRGID"
|
|
// + " and Emp.EMPLOYEEID=EMC.EMPLOYEEID"
|
|
// + " and EMP.EMPLOYEEID in(%q)"
|
|
// + " ORDER BY EMp.EMPLOYEENO ",dFromDate,dFromDate,dToDate,dToDate,SEmpID);
|
|
}
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM EMPCOSTCENTER WHERE ID=%n", nID.Integer);
|
|
}
|
|
internal static void UpdateCurrentCCbeforeMonth(TransactionContext tc, ID employeeid, bool Iscurrent, DateTime month)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("Update EMPCOSTCENTER set CurrentCC=%b WHERE EmployeeID=%n AND MonthDate <=%d", Iscurrent, employeeid.Integer, month);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
internal static void UpdatePreviousCC(TransactionContext tc, ID employeeid, bool Iscurrent)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("Update EMPCOSTCENTER set CurrentCC=%b WHERE EmployeeID=%n ", Iscurrent, employeeid.Integer);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
internal static void UpdateLastCC(TransactionContext tc, int nID, bool Iscurrent,int ccID)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("Update EMPCOSTCENTER set CurrentCC=%b,CostCenterID=%n WHERE ID=%n ", Iscurrent, ccID, nID);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
internal static Int16 GetlastCC(TransactionContext tc, ID employeeid)
|
|
{
|
|
Int16 nID = 0;
|
|
string sSQL = SQLParser.MakeSQL("SELECT MAX(ID) from EMPCOSTCENTER WHERE EmployeeID=%n", employeeid.Integer);
|
|
object ob= tc.ExecuteScalar(sSQL);
|
|
if (ob != null && ob.ToString().Trim().Length > 0)
|
|
{
|
|
nID= Convert.ToInt16(ob);
|
|
}
|
|
return nID;
|
|
}
|
|
internal static void DeleteByMonth(TransactionContext tc, DateTime month, ID employeeID)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("Delete from EMPCOSTCENTER WHERE EmployeeID=%n And MonthDate =%d",employeeID.Integer, month);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
internal static IDataReader GetByMonthStartEnd(TransactionContext tc, DateTime strt, DateTime end, int payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT EMPCOSTCENTER.* FROM EMPCOSTCENTER, Employee WHERE EMPCOSTCENTER.EmployeeID = Employee.EmployeeID AND Employee.Status=%n AND"
|
|
+ " MonthDate >= %d And MonthDate <= %d and Employee.PayrollTypeId=%n ORDER BY EmployeeNo, COSTCENTERID", EnumEmployeeStatus.Live, strt, end, payrollTypeID);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
}
|