470 lines
29 KiB
C#
470 lines
29 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 CarFuelProcess
|
|||
|
|
internal class CarFuelProcessDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
public CarFuelProcessDA() { }
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
internal static void InsertProcess(TransactionContext tc, CarFuelProcess oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO CarFuelProcess( "
|
|||
|
|
+ " CarFuelProcessID, CarFuelMonth, ProcessDate, "
|
|||
|
|
+ " CreatedBy, CreationDate,PayrollTypeID,IsApproved)"
|
|||
|
|
|
|||
|
|
+ " VALUES(%n, %d, %d, "
|
|||
|
|
+ " %n, %d,%n,%b)",
|
|||
|
|
|
|||
|
|
oItem.ID.Integer, oItem.CarFuelMonth, oItem.ProcessDate,
|
|||
|
|
DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate),Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer,oItem.IsApproved);
|
|||
|
|
}
|
|||
|
|
internal static void InsertProcessDetail(TransactionContext tc, CarFuelProcessDetail oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO CarFuelProcessDetail( "
|
|||
|
|
+ " CarFuelProcessDetailID, AccountNo, BankID, BranchID, "
|
|||
|
|
+ " CategoryID, DepartmentID, DesignationID, EmployeeID, "
|
|||
|
|
+ " Gender, GradeID, IsConfirmed, IsFinalize, "
|
|||
|
|
+ " LocationID, CarFuelProcessID, 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,DataReader.GetNullValue( oItem.DesignationID, IDType.Integer), oItem.EmployeeID.Integer,
|
|||
|
|
oItem.Gender, oItem.GradeID.Integer, oItem.IsConfirmed, oItem.IsFinalize,
|
|||
|
|
oItem.LocationID.Integer, oItem.CarFuelProcessID.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, CarFuelProcessDetailItem oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO CarFuelProcessDetailItem( "
|
|||
|
|
+ " CarFuelProcessDetailItemID, ChangeNetAmount, Description, NetAmount, "
|
|||
|
|
+ " CarFuelItemID, CarFuelProcessDetailID, CarFuelType, 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.CarFuelItemID.Integer, oItem.CarFuelProcessDetailID.Integer, oItem.CarFuelType, oItem.TaxAmount,
|
|||
|
|
DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate),Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
internal static void UpdateSalaryDetail(TransactionContext tc, CarFuelProcessDetailItem item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Update CarFuelProcessDetailItem SET DESCRIPTION =%s, "
|
|||
|
|
+ " ChangeNetAmount =%n, NetAmount =%n, TaxAmount=%n WHERE CarFuelProcessDetailItemID=%n AND PayrollTypeID=%n" +
|
|||
|
|
" ",
|
|||
|
|
item.Description, item.ChangeNetAmount, item.NetAmount, item.TaxAmount, item.ID.Integer,Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
internal static void UpdateProcess(TransactionContext tc, CarFuelProcess oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE CarFuelProcess SET "
|
|||
|
|
+ " CarFuelMonth=%d, ProcessDate=%d, ModifiedBy=%n, ModifiedDate=%d,IsApproved=%b"
|
|||
|
|
+ " WHERE CarFuelProcessID=%n AND PayrollTypeID=%n",
|
|||
|
|
oItem.CarFuelMonth, oItem.ProcessDate, DataReader.GetNullValue(oItem.ModifiedBy,IDType.Integer), DataReader.GetNullValue(oItem.ModifiedDate),
|
|||
|
|
oItem.IsApproved,oItem.ID.Integer,Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
internal static void UpdateProcessDetail(TransactionContext tc, CarFuelProcessDetail oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE CarFuelProcessDetail( 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, "
|
|||
|
|
+ " CarFuelProcessID=%n, PayrollTypeID=%n, PFMemberType=%n, ReligionID=%n, "
|
|||
|
|
+ " Remarks=%s, ModifiedBy=%n, ModifiedDate=%d "
|
|||
|
|
+ " WHERE CarFuelProcessDetailID=%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.CarFuelProcessID.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,Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetail(TransactionContext tc, DateTime processMonth)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("select D.* from CarFuelProcessDetail D"+
|
|||
|
|
" inner join CarFuelProcess o on D.CarFuelProcessID= o.CarFuelProcessID"+
|
|||
|
|
" where o.CarFuelMonth =%d",processMonth);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM CarFuelProcess Where PayrollTypeID=%n Order By ProcessDate", payrollTypeID);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID nID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM CarFuelProcess WHERE CarFuelProcessID=%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, DateTime dCarFuelMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM CarFuelProcess WHERE CarFuelMonth>=%d AND CarFuelMonth<=%d AND PayrollTypeID=%n", dCarFuelMonth, dCarFuelMonth, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetProcessDetails(TransactionContext tc, DateTime processMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string sQL = SQLParser.MakeSQL("Select D.* from CarFuelProcess O, CarFuelProcessDetail D Where O.CarFuelMonth=%d AND O.CarFuelProcessID= D.CarFuelProcessID AND D.PayrollTypeID=%n ORDER BY D.EmployeeID ", processMonth, payrollTypeID);
|
|||
|
|
return tc.ExecuteReader(sQL);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetails(TransactionContext tc, ID employeeID, DateTime processMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("Select D.* from CarFuelProcess O, CarFuelProcessDetail D Where O.CarFuelMonth=%d AND O.CarFuelProcessID= D.CarFuelProcessID AND D.EmployeeID=%n AND D.PayrollTypeID=%n ", processMonth, employeeID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetLastMonthItems(TransactionContext tc, int nEmployeeID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("select * from CarFuelProcessDetailItem where CarFuelProcessDetailID in(" +
|
|||
|
|
"select CarFuelProcessDetailID from CarFuelProcessDetail where employeeid=%n AND CarFuelProcessID=(" +
|
|||
|
|
"select CarFuelProcessID from CarFuelProcess where CarFuelMonth=(select max(a.CarFuelMonth) from CarFuelProcess a,CarFuelProcessDetail b where a.CarFuelProcessID=b.CarFuelProcessID and EmployeeID=%n) and PayrollTypeID=%n))", nEmployeeID, nEmployeeID, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetProcessDetailsItems(TransactionContext tc, DateTime processMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string SSql = SQLParser.MakeSQL("Select I.* from CarFuelProcess O, CarFuelProcessDetail D, CarFuelProcessDetailItem I,CarFuelItem oi "+
|
|||
|
|
" Where O.CarFuelMonth=%d AND I.CarFuelProcessDetailID=D.CarFuelProcessDetailID"+
|
|||
|
|
" AND O.CarFuelProcessID= D.CarFuelProcessID AND I.PayrollTypeID=1 and I.CarFuelItemID=oi.CarFuelItemID"+
|
|||
|
|
" ORDER BY oi.SequenceNO", processMonth, payrollTypeID);
|
|||
|
|
return tc.ExecuteReader(SSql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetProcessDetails(TransactionContext tc, ID nCarFuelProcessID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("Select * from CarFuelProcessDetail Where CarFuelProcessID= %n AND PayrollTypeID=%n", nCarFuelProcessID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetProcessDetailItems(TransactionContext tc, ID CarFuelProcessDetailID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("Select * from CarFuelProcessDetailItem Where CarFuelProcessDetailID= %n AND PayrollTypeID=%n", CarFuelProcessDetailID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static double GetProvisionAmountByEmpID(TransactionContext tc, ID nEmpID)
|
|||
|
|
{
|
|||
|
|
object obj = tc.ExecuteScalar("select Sum(opdi.NetAmount) from CarFuelProcessDetailItem opdi, CarFuelProcessDetail opd " +
|
|||
|
|
"where opdi.CarFuelProcessDetailID = opd.CarFuelProcessDetailID " +
|
|||
|
|
"and opd.EmployeeID = %n", nEmpID.Integer);
|
|||
|
|
|
|||
|
|
return obj == DBNull.Value ? 0 : Convert.ToDouble(obj);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataSet GetDataSetOfCarFuelRegister(TransactionContext tc, string sSearch)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteDataSet("%q", sSearch);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetCarFuelRegister(TransactionContext tc, string sCarFuelItemID, DateTime dCarFuelMonth, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuelRegs = new DataSet();
|
|||
|
|
string sQuary = SQLParser.MakeSQL("SELECT op.CarFuelMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name as EmpName,e.BASICSALARY, oi.CarFuelItemId, "
|
|||
|
|
+ " oi.Name as CarFuelItem, Sum(opdi.NetAmount) as Amount "
|
|||
|
|
+ " FROM Employee e, CarFuelItem oi, CarFuelProcess op, CarFuelProcessDetail opd, CarFuelProcessDetailItem opdi "
|
|||
|
|
+ " WHERE op.CarFuelMonth >= %d AND op.CarFuelMonth <= %d "
|
|||
|
|
+ " AND e.EmployeeId IN(%q) "
|
|||
|
|
//+ " AND oi.CarFuelItemID IN(%q) "
|
|||
|
|
+ " AND op.CarFuelProcessID = opd.CarFuelProcessID "
|
|||
|
|
+ " AND e.EmployeeId = opd.EmployeeId "
|
|||
|
|
+ " AND opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId "
|
|||
|
|
+ " AND oi.CarFuelItemId = opdi.CarFuelItemId "
|
|||
|
|
+ " GROUP BY op.CarFuelMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name,e.BASICSALARY, oi.CarFuelItemId, oi.Name "
|
|||
|
|
+ " ORDER BY op.CarFuelMonth, e.EmployeeNo",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(dCarFuelMonth), GlobalFunctions.LastDateOfMonth(dCarFuelMonth), sEmpID, sCarFuelItemID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(sQuary);
|
|||
|
|
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetCarFuelRegister(TransactionContext tc, string sCarFuelItemID, DateTime dCarFuelMonth, DateTime dCarFuelMonth2, string sEmpID)
|
|||
|
|
{
|
|||
|
|
if (dCarFuelMonth > dCarFuelMonth2)
|
|||
|
|
{
|
|||
|
|
DateTime temp = dCarFuelMonth2;
|
|||
|
|
dCarFuelMonth2 = dCarFuelMonth;
|
|||
|
|
dCarFuelMonth = temp;
|
|||
|
|
}
|
|||
|
|
DataSet oCarFuelRegs = new DataSet();
|
|||
|
|
//+ " AND oi.CarFuelItemID IN(%q)
|
|||
|
|
string sQuary = SQLParser
|
|||
|
|
.MakeSQL(@"SELECT op.CarFuelMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo,
|
|||
|
|
e.Name as EmpName, oi.CarFuelItemId,
|
|||
|
|
oi.Name as CarFuelItem, Sum(opdi.NetAmount) as Amount
|
|||
|
|
FROM Employee e, CarFuelItem oi, CarFuelProcess op, CarFuelProcessDetail opd,
|
|||
|
|
CarFuelProcessDetailItem opdi
|
|||
|
|
WHERE
|
|||
|
|
op.CarFuelMonth >= %d AND op.CarFuelMonth <= %d
|
|||
|
|
AND e.EmployeeId IN(%q)
|
|||
|
|
AND op.CarFuelProcessID = opd.CarFuelProcessID
|
|||
|
|
AND e.EmployeeId = opd.EmployeeId
|
|||
|
|
AND opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId
|
|||
|
|
AND oi.CarFuelItemId = opdi.CarFuelItemId
|
|||
|
|
GROUP BY op.CarFuelMonth, e.EmployeeId,e.DEPARTMENTID, e.EmployeeNo, e.Name,
|
|||
|
|
oi.CarFuelItemId, oi.Name
|
|||
|
|
ORDER BY op.CarFuelMonth, e.EmployeeNo",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(dCarFuelMonth), GlobalFunctions.LastDateOfMonth(dCarFuelMonth2), sEmpID, sCarFuelItemID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(sQuary);
|
|||
|
|
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetCarFuelTotalValueRegister(TransactionContext tc, DateTime dFromCarFuelMonth, DateTime dToCarFuelMonth, int nCarFuelItemID, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuelRegs = new DataSet();
|
|||
|
|
string str = SQLParser.MakeSQL("SELECT op.CarFuelMonth, oi.CarFuelItemId, e.EmployeeId, e.EmployeeNo, e.Name as EmpName, "
|
|||
|
|
+ " Sum(opdi.NetAmount) as Amount "
|
|||
|
|
+ " FROM Employee e, CarFuelItem oi, CarFuelProcess op, CarFuelProcessDetail opd, CarFuelProcessDetailItem opdi "
|
|||
|
|
+ " WHERE op.CarFuelMonth >= %d AND op.CarFuelMonth <= %d "
|
|||
|
|
+ " AND e.EmployeeId IN(%q) "
|
|||
|
|
+ " AND op.CarFuelProcessID = opd.CarFuelProcessID "
|
|||
|
|
+ " AND e.EmployeeId = opd.EmployeeId "
|
|||
|
|
+ " AND opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId "
|
|||
|
|
+ " AND oi.CarFuelItemId = %n"
|
|||
|
|
+ " AND oi.CarFuelItemId = opdi.CarFuelItemId "
|
|||
|
|
+ " GROUP BY op.CarFuelMonth, e.EmployeeId, e.EmployeeNo, e.Name, oi.CarFuelItemId "
|
|||
|
|
+ " ORDER BY op.CarFuelMonth, e.EmployeeNo",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(dFromCarFuelMonth),
|
|||
|
|
GlobalFunctions.LastDateOfMonth(dToCarFuelMonth), sEmpID, nCarFuelItemID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(str);
|
|||
|
|
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static double GetPrevMonthAmount(TransactionContext tc, ID nEmpID, ID nCarFuelItemID, DateTime dFromCarFuelMonth, DateTime dToCarFuelMonth)
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL("SELECT sum(odi.NetAmount) "
|
|||
|
|
+ " FROM CarFuelProcess o, CarFuelProcessDetail od, CarFuelProcessDetailItem odi "
|
|||
|
|
+ " WHERE o.CarFuelMonth >= %d AND o.CarFuelMonth <= %d AND o.CarFuelProcessID = od.CarFuelProcessID "
|
|||
|
|
+ " AND od.EmployeeID = %n AND od.CarFuelProcessDetailID = odi.CarFuelProcessDetailID"
|
|||
|
|
+ " AND odi.CarFuelItemID = %n", dFromCarFuelMonth, dToCarFuelMonth, nEmpID.Integer, nCarFuelItemID.Integer);
|
|||
|
|
|
|||
|
|
object obj = tc.ExecuteScalar("%q", sSQL);
|
|||
|
|
return obj == DBNull.Value ? 0 : Convert.ToDouble(obj);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetDetailOtherPayrollItems(TransactionContext tc, string sCarFuelItemID, DateTime dCarFuelMonth, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuelRegs = new DataSet();
|
|||
|
|
string sQuary = SQLParser.MakeSQL("SELECT op.CarFuelMonth, e.EmployeeId, e.EmployeeNo,e.DEPARTMENTID, e.Name as EmpName,"
|
|||
|
|
+ " e.DESIGNATIONID,e.BRANCHID,oi.CarFuelItemId,oi.SequenceNo,oi.Name as CarFuelItem, Sum(opdi.NetAmount) as Amount,oi.SequenceNO"
|
|||
|
|
+ " FROM Employee e, "
|
|||
|
|
+ " CarFuelItem oi, CarFuelProcess op, CarFuelProcessDetail opd, CarFuelProcessDetailItem opdi "
|
|||
|
|
+ " WHERE op.CarFuelMonth >= %d AND op.CarFuelMonth <= %d "
|
|||
|
|
+ " AND e.EmployeeId IN(%q) AND op.CarFuelProcessID = opd.CarFuelProcessID"
|
|||
|
|
+ " AND e.EmployeeId = opd.EmployeeId AND opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId"
|
|||
|
|
+ " AND oi.CarFuelItemId = opdi.CarFuelItemId "
|
|||
|
|
+ " GROUP BY op.CarFuelMonth, e.EmployeeId, e.EmployeeNo, e.Name,e.DEPARTMENTID,e.DESIGNATIONID,"
|
|||
|
|
+ " e.BRANCHID,oi.CarFuelItemId, oi.Name,oi.SequenceNO "
|
|||
|
|
+ " ORDER BY oi.SequenceNO, e.EmployeeNo",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(dCarFuelMonth), GlobalFunctions.LastDateOfMonth(dCarFuelMonth), sEmpID, sCarFuelItemID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(sQuary);
|
|||
|
|
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmpCarFuelPaySlip(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuels = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT op.CarFuelMonth, E.EmployeeId, E.EmployeeNo, E.Name as Name, oi.CarFuelItemId,
|
|||
|
|
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 CarFuelItem, 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 CarFuelProcessDetail opd on E.EmployeeId = opd.EmployeeId
|
|||
|
|
inner join CarFuelProcess op on opd.CarFuelProcessID = op.CarFuelProcessID
|
|||
|
|
inner join CarFuelProcessDetailItem opdi on opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId
|
|||
|
|
inner join CarFuelItem oi on opdi.CarFuelItemId = oi.CarFuelItemId
|
|||
|
|
where E.EMPLOYEEID in(%q) AND op.CarFuelMonth >= %d AND op.CarFuelMonth <= %d
|
|||
|
|
ORDER BY oi.CarFuelItemID, e.EmployeeNo",
|
|||
|
|
sEmpID, GlobalFunctions.FirstDateOfMonth(dateTime), GlobalFunctions.LastDateOfMonth(dateTime));
|
|||
|
|
|
|||
|
|
oCarFuels = tc.ExecuteDataSet(sSQL);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oCarFuels;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetIDLCCarFuelRegister(TransactionContext tc, DateTime dCarFuelMonth, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuelRegs = new DataSet();
|
|||
|
|
string str = SQLParser.MakeSQL("select E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo,sum(OPDI.NetAmount) as Amount"
|
|||
|
|
+ " From Employee E,CarFuelProcessDetailItem OPDI,BRANCHES as BR,CarFuelProcessDetail OPD,"
|
|||
|
|
+ " CarFuelProcess op"
|
|||
|
|
+ " where E.EMPLOYEEID=OPD.EmployeeID AND OPD.CarFuelProcessDetailID=OPDI.CarFuelProcessDetailID"
|
|||
|
|
+ " And BR.BRANCHID = OPD.BranchID AND OPD.CarFuelProcessID=op.CarFuelProcessID"
|
|||
|
|
+ " AND op.CarFuelMonth >= %d AND op.CarFuelMonth <=%d"
|
|||
|
|
+ " AnD OPD.EmployeeID in(%q)"
|
|||
|
|
+ " group by E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo"
|
|||
|
|
+ " order by E.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dCarFuelMonth),
|
|||
|
|
GlobalFunctions.LastDateOfMonth(dCarFuelMonth), sEmpID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(str);
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetItemWiseCarFuel(TransactionContext tc, DateTime dFromCarFuelMonth, int CarFuelItemID, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuelRegs = 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, CarFuelItem oi, CarFuelProcess op, CarFuelProcessDetail opd,
|
|||
|
|
CarFuelProcessDetailItem opdi ,CRG CC,EMPCOSTCENTER empcc,Designation D
|
|||
|
|
WHERE op.CarFuelMonth =%d
|
|||
|
|
AND e.EmployeeId IN(%q)
|
|||
|
|
AND e.DESIGNATIONID = D.DESIGNATIONID
|
|||
|
|
AND op.CarFuelProcessID = opd.CarFuelProcessID
|
|||
|
|
AND e.EmployeeId = opd.EmployeeId
|
|||
|
|
AND opd.CarFuelProcessDetailId = opdi.CarFuelProcessDetailId
|
|||
|
|
AND e.EMPLOYEEID = empcc.EMPLOYEEID
|
|||
|
|
AND CC.CRGID = empcc.COSTCENTERID
|
|||
|
|
AND empcc.CurrentCC=1
|
|||
|
|
AND oi.CarFuelItemId = %n
|
|||
|
|
AND oi.CarFuelItemId = opdi.CarFuelItemId
|
|||
|
|
GROUP BY e.EmployeeNo, e.Name,e.ACCOUNTNO,
|
|||
|
|
CC.DESCRIPTION ,D.NAME
|
|||
|
|
ORDER BY e.EmployeeNo", GlobalFunctions.LastDateOfMonth(dFromCarFuelMonth), sEmpID, CarFuelItemID);
|
|||
|
|
|
|||
|
|
oCarFuelRegs = tc.ExecuteDataSet(str);
|
|||
|
|
return oCarFuelRegs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetManagersCarFuel(TransactionContext tc, DateTime dtFromCarFuel, DateTime dtToCarFuel, int CarFuelItemID, string sEmpID)
|
|||
|
|
{
|
|||
|
|
DataSet oCarFuel = 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.CarFuelMonth
|
|||
|
|
FROM Employee E
|
|||
|
|
LEFT JOIN Department D ON E.DEPARTMENTID = D.DEPARTMENTID
|
|||
|
|
LEFT JOIN Designation DES ON E.DESIGNATIONID = DES.DESIGNATIONID
|
|||
|
|
LEFT JOIN CarFuelProcessDetail opd ON E.EMPLOYEEID = opd.EmployeeID
|
|||
|
|
LEFT JOIN CarFuelProcessDetailItem opdi ON opd.CarFuelProcessDetailID = opdi.CarFuelProcessDetailID
|
|||
|
|
LEFT JOIN CarFuelItem op ON opdi.CarFuelItemID = op.CarFuelItemID
|
|||
|
|
LEFT JOIN CarFuelProcess opp ON opd.CarFuelProcessID = opp.CarFuelProcessID
|
|||
|
|
LEFT JOIN Location L ON E.LOCATIONID = L.LOCATIONID
|
|||
|
|
WHERE opp.CarFuelMonth BETWEEN %d AND %d
|
|||
|
|
AND opdi.CarFuelItemID = %n And E.EmployeeId IN(%q)
|
|||
|
|
ORDER BY E.EMPLOYEENO,opp.CarFuelMonth", dtFromCarFuel, dtToCarFuel, CarFuelItemID, sEmpID);
|
|||
|
|
|
|||
|
|
oCarFuel = tc.ExecuteDataSet(str);
|
|||
|
|
return oCarFuel;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DateTime? GetLastPaidCarFuelMonth(TransactionContext tc, int nEmpID)
|
|||
|
|
{
|
|||
|
|
DateTime? maxDate = DateTime.Today;
|
|||
|
|
object obj = tc.ExecuteScalar(@"SELECT MAX(CarFuelMonth)AS MONTH FROM CarFuelProcess
|
|||
|
|
WHERE CarFuelProcessID IN(SELECT CarFuelProcessID FROM
|
|||
|
|
CarFuelProcessDetail 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 CarFuelProcess o
|
|||
|
|
WHERE o.CarFuelProcessID IN(SELECT od.CarFuelProcessID FROM CarFuelProcessDetail od
|
|||
|
|
WHERE od.EmployeeID=%n)
|
|||
|
|
AND o.CarFuelMonth=%d", empiD.Integer, fromdate);
|
|||
|
|
return tc.ExecuteReader(sSQL);
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
internal static void DeleteProcess(TransactionContext tc, ID nProcessID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string sSQLDetailItem = SQLParser.MakeSQL(@"Delete FROM CarFuelProcessDetailItem WHERE
|
|||
|
|
CarFuelProcessDetailID IN (SELECT CarFuelProcessDetailID
|
|||
|
|
FROM CarFuelProcessDetail WHERE CarFuelProcessID =%n)",
|
|||
|
|
nProcessID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sSQLDetailItem);
|
|||
|
|
string sSQLDetail = SQLParser.MakeSQL(@"DELETE FROM CarFuelProcessDetail
|
|||
|
|
WHERE CarFuelProcessID = %n", nProcessID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sSQLDetail);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM CarFuelProcess WHERE CarFuelProcessID=%n AND PayrollTypeID=%n", nProcessID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteProcessDetail(TransactionContext tc, ID nProcessDetailID)
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL("DELETE FROM CarFuelProcessDetail WHERE CarFuelProcessDetailID=%n AND PayrollTypeID=%n", nProcessDetailID.Integer, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sSQL);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteItem(TransactionContext tc, ID nProcessDetailItemID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM CarFuelProcessDetailItem WHERE CarFuelProcessDetailItemID=%n AND PayrollTypeID=%n", nProcessDetailItemID.Integer, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
internal static void DeleteProcessDetailItem(TransactionContext tc, ID nProcessDetailID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM CarFuelProcessDetailItem WHERE CarFuelProcessDetailID=%n AND PayrollTypeID=%n", nProcessDetailID.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
internal static void Undo(TransactionContext tc, DateTime CarFuelMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string sSQLDetailItem = SQLParser.MakeSQL("DELETE FROM CarFuelProcessDetailItem "
|
|||
|
|
+ " WHERE CarFuelProcessDetailID IN (SELECT od.CarFuelProcessDetailID FROM CarFuelProcessDetail od "
|
|||
|
|
+ " WHERE od.CarFuelProcessID IN (SELECT o.CarFuelProcessID FROM CarFuelProcess o "
|
|||
|
|
+ " WHERE o.CarFuelMonth >= %d AND o.CarFuelMonth <= %d AND PayrollTypeID=%n))",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(CarFuelMonth), GlobalFunctions.LastDateOfMonth(CarFuelMonth), payrollTypeID);
|
|||
|
|
string sSQLDetail = SQLParser.MakeSQL("DELETE FROM CarFuelProcessDetail "
|
|||
|
|
+ " WHERE CarFuelProcessID IN (SELECT o.CarFuelProcessID FROM CarFuelProcess o "
|
|||
|
|
+ " WHERE o.CarFuelMonth >= %d AND o.CarFuelMonth <= %d AND PayrollTypeID=%n)",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(CarFuelMonth), GlobalFunctions.LastDateOfMonth(CarFuelMonth), payrollTypeID);
|
|||
|
|
string sSQLProcess = SQLParser.MakeSQL("DELETE FROM CarFuelProcess "
|
|||
|
|
+ " WHERE CarFuelMonth >= %d AND CarFuelMonth <= %d AND PayrollTypeID=%n",
|
|||
|
|
GlobalFunctions.FirstDateOfMonth(CarFuelMonth), GlobalFunctions.LastDateOfMonth(CarFuelMonth), payrollTypeID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sSQLDetailItem);
|
|||
|
|
tc.ExecuteNonQuery(sSQLDetail);
|
|||
|
|
tc.ExecuteNonQuery(sSQLProcess);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
}
|