470 lines
28 KiB
C#
470 lines
28 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 OPIProcess
|
|
internal class OPIProcessDA
|
|
{
|
|
#region Constructor
|
|
|
|
public OPIProcessDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
internal static void InsertProcess(TransactionContext tc, OPIProcess oItem, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO OPIProcess( "
|
|
+ " OPIProcessID, OPIMonth, ProcessDate, "
|
|
+ " CreatedBy, CreationDate,PayrollTypeID,IsApproved)"
|
|
|
|
+ " VALUES(%n, %d, %d, "
|
|
+ " %n, %d,%n,%b)",
|
|
|
|
oItem.ID.Integer, oItem.OPIMonth, oItem.ProcessDate,
|
|
DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate), payrollTypeID.Integer,oItem.IsApproved);
|
|
}
|
|
internal static void InsertProcessDetail(TransactionContext tc, OPIProcessDetail oItem)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO OPIProcessDetail( "
|
|
+ " OPIProcessDetailID, AccountNo, BankID, BranchID, "
|
|
+ " CategoryID, DepartmentID, DesignationID, EmployeeID, "
|
|
+ " Gender, GradeID, IsConfirmed, IsFinalize, "
|
|
+ " LocationID, OPIProcessID, PayrollTypeID, PFMemberType, "
|
|
+ " ReligionID, Remarks, CreatedBy, CreationDate)"
|
|
|
|
+ " VALUES(%n, %s, %n, %n, "
|
|
+ " %n, %n, %n, %n, "
|
|
+ " %n, %n, %b, %b, "
|
|
+ " %n, %n, %n, %n, "
|
|
+ " %n, %s, %n, %d)",
|
|
|
|
oItem.ID.Integer, DataReader.GetNullValue(oItem.AccountNo), DataReader.GetNullValue(oItem.BankID, IDType.Integer), DataReader.GetNullValue(oItem.BranchID, IDType.Integer),
|
|
oItem.CategoryID.Integer, oItem.DepartmentID.Integer, oItem.DesignationID.Integer, oItem.EmployeeID.Integer,
|
|
oItem.Gender, oItem.GradeID.Integer, oItem.IsConfirmed, oItem.IsFinalize,
|
|
oItem.LocationID.Integer, oItem.OPIProcessID.Integer, oItem.PayrollTypeID.Integer, oItem.PFMemberType,
|
|
oItem.ReligionID.Integer, DataReader.GetNullValue(oItem.Remarks), DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate));
|
|
}
|
|
internal static void InsertProcessDetailItem(TransactionContext tc, OPIProcessDetailItem oItem, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO OPIProcessDetailItem( "
|
|
+ " OPIProcessDetailItemID, ChangeNetAmount, Description, NetAmount, "
|
|
+ " OPIItemID, OPIProcessDetailID, OPIType, TaxAmount, "
|
|
+ " CreatedBy, CreationDate,PayrollTypeID)"
|
|
|
|
+ " VALUES(%n, %n, %s, %n, "
|
|
+ " %n, %n, %n, %n, "
|
|
+ " %n, %d,%n)",
|
|
|
|
oItem.ID.Integer, oItem.ChangeNetAmount, oItem.Description, oItem.NetAmount,
|
|
oItem.OPIItemID.Integer, oItem.OPIProcessDetailID.Integer, oItem.OPIType, oItem.TaxAmount,
|
|
DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate), payrollTypeID.Integer);
|
|
}
|
|
internal static void UpdateSalaryDetail(TransactionContext tc, OPIProcessDetailItem item, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("Update OPIProcessDetailItem SET DESCRIPTION =%s, "
|
|
+ " ChangeNetAmount =%n, NetAmount =%n, TaxAmount=%n WHERE OPIProcessDetailItemID=%n AND PayrollTypeID=%n" +
|
|
" ",
|
|
item.Description, item.ChangeNetAmount, item.NetAmount, item.TaxAmount, item.ID.Integer, payrollTypeID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
internal static void UpdateProcess(TransactionContext tc, OPIProcess oItem, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE OPIProcess SET "
|
|
+ " OPIMonth=%d, ProcessDate=%d, ModifiedBy=%n, ModifiedDate=%d,IsApproved=%b"
|
|
+ " WHERE OPIProcessID=%n AND PayrollTypeID=%n",
|
|
oItem.OPIMonth, oItem.ProcessDate, DataReader.GetNullValue(oItem.ModifiedBy,IDType.Integer), DataReader.GetNullValue(oItem.ModifiedDate),
|
|
oItem.IsApproved,oItem.ID.Integer, payrollTypeID.Integer);
|
|
}
|
|
internal static void UpdateProcessDetail(TransactionContext tc, OPIProcessDetail oItem, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE OPIProcessDetail( SET "
|
|
+ " AccountNo=%s, BankID=%n, BranchID=%n, CategoryID=%n, "
|
|
+ " DepartmentID=%n, DesignationID=%n, EmployeeID=%n, Gender=%n, "
|
|
+ " GradeID=%n, IsConfirmed=%b, IsFinalize=%b, LocationID=%n, "
|
|
+ " OPIProcessID=%n, PayrollTypeID=%n, PFMemberType=%n, ReligionID=%n, "
|
|
+ " Remarks=%s, ModifiedBy=%n, ModifiedDate=%d "
|
|
+ " WHERE OPIProcessDetailID=%n AND PayrollTypeID=%n",
|
|
|
|
oItem.AccountNo, oItem.BankID.Integer, oItem.BranchID.Integer, oItem.CategoryID.Integer,
|
|
oItem.DepartmentID.Integer, oItem.DesignationID.Integer, oItem.EmployeeID.Integer, oItem.Gender,
|
|
oItem.GradeID.Integer, oItem.IsConfirmed, oItem.IsFinalize, oItem.LocationID.Integer,
|
|
oItem.OPIProcessID.Integer, oItem.PayrollTypeID.Integer, oItem.PFMemberType, oItem.ReligionID.Integer,
|
|
DataReader.GetNullValue(oItem.Remarks), DataReader.GetNullValue(oItem.ModifiedBy.Integer), DataReader.GetNullValue(oItem.ModifiedDate),
|
|
oItem.ID.Integer, payrollTypeID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader GetDetail(TransactionContext tc, DateTime processMonth)
|
|
{
|
|
return tc.ExecuteReader("select D.* from OPIProcessDetail D"+
|
|
" inner join OPIProcess o on D.OPIProcessID= o.OPIProcessID"+
|
|
" where o.OPIMonth =%d",processMonth);
|
|
}
|
|
|
|
internal static IDataReader Get3(TransactionContext tc, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM OPIProcess Where PayrollTypeID=%n Order By ProcessDate", payrollTypeID.Integer);
|
|
}
|
|
internal static IDataReader Get2(TransactionContext tc, ID nID, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM OPIProcess WHERE OPIProcessID=%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static IDataReader Get4(TransactionContext tc, DateTime dOPIMonth, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM OPIProcess WHERE OPIMonth>=%d AND OPIMonth<=%d AND PayrollTypeID=%n", dOPIMonth, dOPIMonth, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static IDataReader GetProcessDetails(TransactionContext tc, DateTime processMonth, ID payrollTypeID)
|
|
{
|
|
string sQL = SQLParser.MakeSQL("Select D.* from OPIProcess O, OPIProcessDetail D Where O.OPIMonth=%d AND O.OPIProcessID= D.OPIProcessID AND D.PayrollTypeID=%n ORDER BY D.EmployeeID ", processMonth, payrollTypeID.Integer);
|
|
return tc.ExecuteReader(sQL);
|
|
}
|
|
|
|
internal static IDataReader GetDetails(TransactionContext tc, ID employeeID, DateTime processMonth, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select D.* from OPIProcess O, OPIProcessDetail D Where O.OPIMonth=%d AND O.OPIProcessID= D.OPIProcessID AND D.EmployeeID=%n AND D.PayrollTypeID=%n ", processMonth, employeeID.Integer, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static IDataReader GetLastMonthItems(TransactionContext tc, int nEmployeeID, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("select * from OPIProcessDetailItem where OPIProcessDetailID in(" +
|
|
"select OPIProcessDetailID from OPIProcessDetail where employeeid=%n AND OPIProcessID=(" +
|
|
"select OPIProcessID from OPIProcess where OPIMonth=(select max(a.OPIMonth) from OPIProcess a,OPIProcessDetail b where a.OPIProcessID=b.OPIProcessID and EmployeeID=%n) and PayrollTypeID=%n))", nEmployeeID, nEmployeeID, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static IDataReader GetProcessDetailsItems(TransactionContext tc, DateTime processMonth, ID payrollTypeID)
|
|
{
|
|
string SSql = SQLParser.MakeSQL("Select I.* from OPIProcess O, OPIProcessDetail D, OPIProcessDetailItem I,OpiItem oi "+
|
|
" Where O.OPIMonth=%d AND I.OPIProcessDetailID=D.OPIProcessDetailID"+
|
|
" AND O.OPIProcessID= D.OPIProcessID AND I.PayrollTypeID=1 and I.OPIItemID=oi.OpiItemID"+
|
|
" ORDER BY oi.SequenceNO", processMonth, payrollTypeID.Integer);
|
|
return tc.ExecuteReader(SSql);
|
|
}
|
|
|
|
internal static IDataReader GetProcessDetails(TransactionContext tc, ID nOpiProcessID, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select * from OPIProcessDetail Where OPIProcessID= %n AND PayrollTypeID=%n", nOpiProcessID.Integer, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static IDataReader GetProcessDetailItems(TransactionContext tc, ID OPIProcessDetailID, ID payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select * from OPIProcessDetailItem Where OPIProcessDetailID= %n AND PayrollTypeID=%n", OPIProcessDetailID.Integer, payrollTypeID.Integer);
|
|
}
|
|
|
|
internal static double GetProvisionAmountByEmpID(TransactionContext tc, ID nEmpID)
|
|
{
|
|
object obj = tc.ExecuteScalar("select Sum(opdi.NetAmount) from OPIProcessDetailItem opdi, OPIProcessDetail opd " +
|
|
"where opdi.opiProcessDetailID = opd.opiProcessDetailID " +
|
|
"and opd.EmployeeID = %n", nEmpID.Integer);
|
|
|
|
return obj == DBNull.Value ? 0 : Convert.ToDouble(obj);
|
|
}
|
|
|
|
public static DataSet GetDataSetOfOPIRegister(TransactionContext tc, string sSearch)
|
|
{
|
|
return tc.ExecuteDataSet("%q", sSearch);
|
|
}
|
|
|
|
internal static DataSet GetOPIRegister(TransactionContext tc, string sOPIItemID, DateTime dOPIMonth, string sEmpID)
|
|
{
|
|
DataSet oOPIRegs = new DataSet();
|
|
string sQuary = SQLParser.MakeSQL("SELECT op.OPIMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name as EmpName,e.BASICSALARY, oi.OPIItemId, "
|
|
+ " oi.Name as OPIItem, Sum(opdi.NetAmount) as Amount "
|
|
+ " FROM Employee e, OPIItem oi, OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem opdi "
|
|
+ " WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d "
|
|
+ " AND e.EmployeeId IN(%q) "
|
|
//+ " AND oi.OpiItemID IN(%q) "
|
|
+ " AND op.OPIProcessID = opd.OPIProcessID "
|
|
+ " AND e.EmployeeId = opd.EmployeeId "
|
|
+ " AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId "
|
|
+ " AND oi.OPIItemId = opdi.OPIItemId "
|
|
+ " GROUP BY op.OPIMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name,e.BASICSALARY, oi.OPIItemId, oi.Name "
|
|
+ " ORDER BY op.OPIMonth, e.EmployeeNo",
|
|
GlobalFunctions.FirstDateOfMonth(dOPIMonth), GlobalFunctions.LastDateOfMonth(dOPIMonth), sEmpID, sOPIItemID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(sQuary);
|
|
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static DataSet GetOPIRegister(TransactionContext tc, string sOPIItemID, DateTime dOPIMonth, DateTime dOPIMonth2, string sEmpID)
|
|
{
|
|
if (dOPIMonth > dOPIMonth2)
|
|
{
|
|
DateTime temp = dOPIMonth2;
|
|
dOPIMonth2 = dOPIMonth;
|
|
dOPIMonth = temp;
|
|
}
|
|
DataSet oOPIRegs = new DataSet();
|
|
//+ " AND oi.OpiItemID IN(%q)
|
|
string sQuary = SQLParser
|
|
.MakeSQL(@"SELECT op.OPIMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo,
|
|
e.Name as EmpName, oi.OPIItemId,
|
|
oi.Name as OPIItem, Sum(opdi.NetAmount) as Amount
|
|
FROM Employee e, OPIItem oi, OPIProcess op, OPIProcessDetail opd,
|
|
OPIProcessDetailItem opdi
|
|
WHERE
|
|
op.OPIMonth >= %d AND op.OPIMonth <= %d
|
|
AND e.EmployeeId IN(%q)
|
|
AND op.OPIProcessID = opd.OPIProcessID
|
|
AND e.EmployeeId = opd.EmployeeId
|
|
AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId
|
|
AND oi.OPIItemId = opdi.OPIItemId
|
|
GROUP BY op.OPIMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name,
|
|
oi.OPIItemId, oi.Name
|
|
ORDER BY op.OPIMonth, e.EmployeeNo",
|
|
GlobalFunctions.FirstDateOfMonth(dOPIMonth), GlobalFunctions.LastDateOfMonth(dOPIMonth2), sEmpID, sOPIItemID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(sQuary);
|
|
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static DataSet GetOPITotalValueRegister(TransactionContext tc, DateTime dFromOpiMonth, DateTime dToOpiMonth, int nOPIItemID, string sEmpID)
|
|
{
|
|
DataSet oOPIRegs = new DataSet();
|
|
string str = SQLParser.MakeSQL("SELECT op.OPIMonth, oi.OPIItemId, e.EmployeeId, e.EmployeeNo, e.Name as EmpName, "
|
|
+ " Sum(opdi.NetAmount) as Amount "
|
|
+ " FROM Employee e, OPIItem oi, OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem opdi "
|
|
+ " WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d "
|
|
+ " AND e.EmployeeId IN(%q) "
|
|
+ " AND op.OPIProcessID = opd.OPIProcessID "
|
|
+ " AND e.EmployeeId = opd.EmployeeId "
|
|
+ " AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId "
|
|
+ " AND oi.OPIItemId = %n"
|
|
+ " AND oi.OPIItemId = opdi.OPIItemId "
|
|
+ " GROUP BY op.OPIMonth, e.EmployeeId, e.EmployeeNo, e.Name, oi.OPIItemId "
|
|
+ " ORDER BY op.OPIMonth, e.EmployeeNo",
|
|
GlobalFunctions.FirstDateOfMonth(dFromOpiMonth),
|
|
GlobalFunctions.LastDateOfMonth(dToOpiMonth), sEmpID, nOPIItemID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(str);
|
|
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static double GetPrevMonthAmount(TransactionContext tc, ID nEmpID, ID nOPIItemID, DateTime dFromOpiMonth, DateTime dToOpiMonth)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("SELECT sum(odi.NetAmount) "
|
|
+ " FROM OPIProcess o, OPIProcessDetail od, OPIProcessDetailItem odi "
|
|
+ " WHERE o.OPIMonth >= %d AND o.OPIMonth <= %d AND o.OPIProcessID = od.OPIProcessID "
|
|
+ " AND od.EmployeeID = %n AND od.OPIProcessDetailID = odi.OPIProcessDetailID"
|
|
+ " AND odi.OPIItemID = %n", dFromOpiMonth, dToOpiMonth, nEmpID.Integer, nOPIItemID.Integer);
|
|
|
|
object obj = tc.ExecuteScalar("%q", sSQL);
|
|
return obj == DBNull.Value ? 0 : Convert.ToDouble(obj);
|
|
}
|
|
|
|
internal static DataSet GetDetailOtherPayrollItems(TransactionContext tc, string sOPIItemID, DateTime dOPIMonth, string sEmpID)
|
|
{
|
|
DataSet oOPIRegs = new DataSet();
|
|
string sQuary = SQLParser.MakeSQL("SELECT op.OPIMonth, e.EmployeeId, e.EmployeeNo,e.DEPARTMENTID, e.Name as EmpName,"
|
|
+ " e.DESIGNATIONID,e.BRANCHID,oi.OPIItemId,oi.SequenceNo,oi.Name as OPIItem, Sum(opdi.NetAmount) as Amount,oi.SequenceNO"
|
|
+ " FROM Employee e, "
|
|
+ " OPIItem oi, OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem opdi "
|
|
+ " WHERE op.OPIMonth >= %d AND op.OPIMonth <= %d "
|
|
+ " AND e.EmployeeId IN(%q) AND op.OPIProcessID = opd.OPIProcessID"
|
|
+ " AND e.EmployeeId = opd.EmployeeId AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId"
|
|
+ " AND oi.OPIItemId = opdi.OPIItemId "
|
|
+ " GROUP BY op.OPIMonth, e.EmployeeId, e.EmployeeNo, e.Name,e.DEPARTMENTID,e.DESIGNATIONID,"
|
|
+ " e.BRANCHID,oi.OPIItemId, oi.Name,oi.SequenceNO "
|
|
+ " ORDER BY oi.SequenceNO, e.EmployeeNo",
|
|
GlobalFunctions.FirstDateOfMonth(dOPIMonth), GlobalFunctions.LastDateOfMonth(dOPIMonth), sEmpID, sOPIItemID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(sQuary);
|
|
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static DataSet GetEmpOPIPaySlip(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|
{
|
|
DataSet oOPIs = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT op.OPIMonth, E.EmployeeId, E.EmployeeNo, E.Name as Name, oi.OPIItemId,
|
|
DG.Name 'Designation', L.Description as LName,D.DESCRIPTION Department,E.PAYMENTMODE,
|
|
cat.DESCRIPTION as Category,E.JOININGDATE,E.DEPARTMENTID,B.NAME as BName,
|
|
oi.Name as OPIItem, opdi.NetAmount as CHANGEDAMOUNT,opd.AccountNo,opd.IsFinalize
|
|
FROM Employee E
|
|
inner join Location L on E.LOCATIONID = L.LOCATIONID
|
|
inner join CATEGORY cat on E.CATEGORYID= cat.CATEGORYID
|
|
inner join Department D on E.DEPARTMENTID = D.DEPARTMENTID
|
|
inner join Designation DG on E.DESIGNATIONID = DG.DESIGNATIONID
|
|
left outer join BRANCHES Br on E.BRANCHID = Br.BRANCHID
|
|
left outer join BANKS B on Br.BANKID = B.BANKID
|
|
inner join OPIProcessDetail opd on E.EmployeeId = opd.EmployeeId
|
|
inner join OPIProcess op on opd.OPIProcessID = op.OPIProcessID
|
|
inner join OPIProcessDetailItem opdi on opd.OPIProcessDetailId = opdi.OPIProcessDetailId
|
|
inner join OPIItem oi on opdi.OPIItemId = oi.OPIItemId
|
|
where E.EMPLOYEEID in(%q) AND op.OPIMonth >= %d AND op.OPIMonth <= %d
|
|
ORDER BY oi.OpiItemID, e.EmployeeNo",
|
|
sEmpID, GlobalFunctions.FirstDateOfMonth(dateTime), GlobalFunctions.LastDateOfMonth(dateTime));
|
|
|
|
oOPIs = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oOPIs;
|
|
}
|
|
|
|
internal static DataSet GetIDLCOPIRegister(TransactionContext tc, DateTime dOPIMonth, string sEmpID)
|
|
{
|
|
DataSet oOPIRegs = new DataSet();
|
|
string str = SQLParser.MakeSQL("select E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo,sum(OPDI.NetAmount) as Amount"
|
|
+ " From Employee E,OPIProcessDetailItem OPDI,BRANCHES as BR,OPIProcessDetail OPD,"
|
|
+ " OPIProcess op"
|
|
+ " where E.EMPLOYEEID=OPD.EmployeeID AND OPD.OPIProcessDetailID=OPDI.OPIProcessDetailID"
|
|
+ " And BR.BRANCHID = OPD.BranchID AND OPD.OPIProcessID=op.OPIProcessID"
|
|
+ " AND op.OPIMonth >= %d AND op.OPIMonth <=%d"
|
|
+ " AnD OPD.EmployeeID in(%q)"
|
|
+ " group by E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo"
|
|
+ " order by E.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dOPIMonth),
|
|
GlobalFunctions.LastDateOfMonth(dOPIMonth), sEmpID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(str);
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static DataSet GetItemWiseOPI(TransactionContext tc, DateTime dFromOPIMonth, int opiItemID, string sEmpID)
|
|
{
|
|
DataSet oOPIRegs = new DataSet();
|
|
string str = SQLParser.MakeSQL(@"SELECT e.EmployeeNo, e.Name as EmpName,e.ACCOUNTNO,D.NAME AS Designation,
|
|
Sum(opdi.NetAmount) as Amount ,CC.DESCRIPTION AS CostCenter
|
|
FROM Employee e, OPIItem oi, OPIProcess op, OPIProcessDetail opd,
|
|
OPIProcessDetailItem opdi ,CRG CC,EMPCOSTCENTER empcc,Designation D
|
|
WHERE op.OPIMonth =%d
|
|
AND e.EmployeeId IN(%q)
|
|
AND e.DESIGNATIONID = D.DESIGNATIONID
|
|
AND op.OPIProcessID = opd.OPIProcessID
|
|
AND e.EmployeeId = opd.EmployeeId
|
|
AND opd.OPIProcessDetailId = opdi.OPIProcessDetailId
|
|
AND e.EMPLOYEEID = empcc.EMPLOYEEID
|
|
AND CC.CRGID = empcc.COSTCENTERID
|
|
AND empcc.CurrentCC=1
|
|
AND oi.OPIItemId = %n
|
|
AND oi.OPIItemId = opdi.OPIItemId
|
|
GROUP BY e.EmployeeNo, e.Name,e.ACCOUNTNO,
|
|
CC.DESCRIPTION ,D.NAME
|
|
ORDER BY e.EmployeeNo", GlobalFunctions.LastDateOfMonth(dFromOPIMonth), sEmpID, opiItemID);
|
|
|
|
oOPIRegs = tc.ExecuteDataSet(str);
|
|
return oOPIRegs;
|
|
}
|
|
|
|
internal static DataSet GetManagersOPI(TransactionContext tc, DateTime dtFromOPI, DateTime dtToOPI, int opiItemID, string sEmpID)
|
|
{
|
|
DataSet oOPI = new DataSet();
|
|
string str = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.[NAME],D.[DESCRIPTION] AS Department,
|
|
DES.[NAME] AS Designation,opdi.ChangeNetAmount AS Amount,
|
|
L.[DESCRIPTION] AS Location,opp.OPIMonth
|
|
FROM Employee E
|
|
LEFT JOIN Department D ON E.DEPARTMENTID = D.DEPARTMENTID
|
|
LEFT JOIN Designation DES ON E.DESIGNATIONID = DES.DESIGNATIONID
|
|
LEFT JOIN OPIProcessDetail opd ON E.EMPLOYEEID = opd.EmployeeID
|
|
LEFT JOIN OPIProcessDetailItem opdi ON opd.OPIProcessDetailID = opdi.OPIProcessDetailID
|
|
LEFT JOIN OpiItem op ON opdi.OPIItemID = op.OpiItemID
|
|
LEFT JOIN OPIProcess opp ON opd.OPIProcessID = opp.OPIProcessID
|
|
LEFT JOIN Location L ON E.LOCATIONID = L.LOCATIONID
|
|
WHERE opp.OPIMonth BETWEEN %d AND %d
|
|
AND opdi.OpiItemID = %n And E.EmployeeId IN(%q)
|
|
ORDER BY E.EMPLOYEENO,opp.OPIMonth", dtFromOPI, dtToOPI, opiItemID, sEmpID);
|
|
|
|
oOPI = tc.ExecuteDataSet(str);
|
|
return oOPI;
|
|
}
|
|
|
|
internal static DateTime? GetLastPaidOPIMonth(TransactionContext tc, int nEmpID)
|
|
{
|
|
DateTime? maxDate = DateTime.Today;
|
|
object obj = tc.ExecuteScalar(@"SELECT MAX(OPIMonth)AS MONTH FROM OPIProcess
|
|
WHERE OPIProcessID IN(SELECT OPIProcessID FROM
|
|
OPIProcessDetail WHERE EmployeeID = %n)", nEmpID);
|
|
if (obj != DBNull.Value)
|
|
{
|
|
maxDate = Convert.ToDateTime(obj);
|
|
}
|
|
else
|
|
{
|
|
maxDate = null;
|
|
}
|
|
return maxDate;
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime fromdate, ID empiD)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT * FROM OPIProcess o
|
|
WHERE o.OPIProcessID IN(SELECT od.OPIProcessID FROM OPIProcessDetail od
|
|
WHERE od.EmployeeID=%n)
|
|
AND o.OPIMonth=%d", empiD.Integer, fromdate);
|
|
return tc.ExecuteReader(sSQL);
|
|
}
|
|
#endregion
|
|
|
|
#region Delete function
|
|
internal static void DeleteProcess(TransactionContext tc, ID nProcessID, ID payrollTypeID)
|
|
{
|
|
string sSQLDetailItem = SQLParser.MakeSQL(@"Delete FROM OPIProcessDetailItem WHERE
|
|
OPIProcessDetailID IN (SELECT OPIProcessDetailID
|
|
FROM OPIProcessDetail WHERE OPIProcessID =%n)",
|
|
nProcessID.Integer);
|
|
tc.ExecuteNonQuery(sSQLDetailItem);
|
|
string sSQLDetail = SQLParser.MakeSQL(@"DELETE FROM OPIProcessDetail
|
|
WHERE OPIProcessID = %n", nProcessID.Integer);
|
|
tc.ExecuteNonQuery(sSQLDetail);
|
|
tc.ExecuteNonQuery("DELETE FROM OPIProcess WHERE OPIProcessID=%n AND PayrollTypeID=%n", nProcessID.Integer, payrollTypeID.Integer);
|
|
}
|
|
internal static void DeleteProcessDetail(TransactionContext tc, ID nProcessDetailID, ID payrollTypeID)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("DELETE FROM OPIProcessDetail WHERE OPIProcessDetailID=%n AND PayrollTypeID=%n", nProcessDetailID.Integer, payrollTypeID.Integer);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
internal static void DeleteItem(TransactionContext tc, ID nProcessDetailItemID, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM OPIProcessDetailItem WHERE OPIProcessDetailItemID=%n AND PayrollTypeID=%n", nProcessDetailItemID.Integer, payrollTypeID.Integer);
|
|
}
|
|
internal static void DeleteProcessDetailItem(TransactionContext tc, ID nProcessDetailID, ID payrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM OPIProcessDetailItem WHERE OPIProcessDetailID=%n AND PayrollTypeID=%n", nProcessDetailID.Integer, payrollTypeID.Integer);
|
|
}
|
|
internal static void Undo(TransactionContext tc, DateTime OpiMonth, ID payrollTypeID)
|
|
{
|
|
string sSQLDetailItem = SQLParser.MakeSQL("DELETE FROM OPIProcessDetailItem "
|
|
+ " WHERE OPIProcessDetailID IN (SELECT od.OPIProcessDetailID FROM OPIProcessDetail od "
|
|
+ " WHERE od.OPIProcessID IN (SELECT o.OPIProcessID FROM opiProcess o "
|
|
+ " WHERE o.OPIMonth >= %d AND o.OPIMonth <= %d AND PayrollTypeID=%n))",
|
|
GlobalFunctions.FirstDateOfMonth(OpiMonth), GlobalFunctions.LastDateOfMonth(OpiMonth), payrollTypeID.Integer);
|
|
string sSQLDetail = SQLParser.MakeSQL("DELETE FROM OPIProcessDetail "
|
|
+ " WHERE OPIProcessID IN (SELECT o.OPIProcessID FROM opiProcess o "
|
|
+ " WHERE o.OPIMonth >= %d AND o.OPIMonth <= %d AND PayrollTypeID=%n)",
|
|
GlobalFunctions.FirstDateOfMonth(OpiMonth), GlobalFunctions.LastDateOfMonth(OpiMonth), payrollTypeID.Integer);
|
|
string sSQLProcess = SQLParser.MakeSQL("DELETE FROM opiProcess "
|
|
+ " WHERE OPIMonth >= %d AND OPIMonth <= %d AND PayrollTypeID=%n",
|
|
GlobalFunctions.FirstDateOfMonth(OpiMonth), GlobalFunctions.LastDateOfMonth(OpiMonth), payrollTypeID.Integer);
|
|
|
|
tc.ExecuteNonQuery(sSQLDetailItem);
|
|
tc.ExecuteNonQuery(sSQLDetail);
|
|
tc.ExecuteNonQuery(sSQLProcess);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
#endregion
|
|
}
|