513 lines
30 KiB
C#
513 lines
30 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 EmployeeBankAccountDA
|
|
|
|
internal class EmployeeBankAccountDA
|
|
{
|
|
#region Constructor
|
|
|
|
private EmployeeBankAccountDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, EmployeeBankAccount item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("INSERT INTO BANKACCOUNTHISTORY(EmpBankAccountID, employeeID, changeDate, accountNo,AccountType, branchID, CreatedBy, CreationDate)" +
|
|
" VALUES(%n, %n, %D, %s,%n, %n, %n, %D)", item.ID.Integer, item.EmployeeID.Integer, item.EffectDate, item.AccountNo, (int)item.AccountType, DataReader.GetNullValue(item.BranchID,IDType.Integer), DataReader.GetNullValue(item.CreatedBy.Integer), DataReader.GetNullValue(item.CreatedDate));
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, EmployeeBankAccount item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"UPDATE BANKACCOUNTHISTORY SET employeeID=%n, changeDate=%D, accountNo=%s,AccountType=%n, branchID=%n, ModifiedBy=%n, ModifiedDate=%D
|
|
WHERE EmpBankAccountID=%n", item.EmployeeID.Integer, item.EffectDate, item.AccountNo, (int)item.AccountType, item.BranchID.Integer, DataReader.GetNullValue(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 * FROM BANKACCOUNTHISTORY ORDER BY CreationDAte");
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BANKACCOUNTHISTORY WHERE EmpBankAccountId=%n", nID.Integer);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID nEmpID, DateTime dEffectDate, int payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader(@"Select * from BANKACCOUNTHISTORY where
|
|
CHANGEDATE=(Select MAX(CHANGEDATE) from BANKACCOUNTHISTORY where EMPLOYEEID=%n and CHANGEDATE<=%d)
|
|
AND EMPLOYEEID=(Select EMPLOYEEID from Employee where EMPLOYEEID=%n and PAYROLLTYPEID=%n) ", nEmpID.Integer, dEffectDate, nEmpID.Integer, payrollTypeID);
|
|
}
|
|
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader(@"Select * from BANKACCOUNTHISTORY Where EmployeeId = %n", nID.Integer);
|
|
}
|
|
|
|
internal static DataSet GetEmpBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
bool bfixedAmount = ConfigurationManager.GetBoolValue("costcenter", "manadatoryinsalary", EnumConfigurationType.Logic);
|
|
if (!bfixedAmount)
|
|
{
|
|
#region DeadQuery
|
|
// string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
|
|
// SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,CC.DESCRIPTION AS CostCenter
|
|
// FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,
|
|
// BRANCHES as BR,CRG CC,SALARYEMPCOSTCENTER sempcc
|
|
// WHERE E.EMPLOYEEID=SM.EMPLOYEEID
|
|
// AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
|
|
// AND BR.BranchID=SM.BranchID
|
|
// AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
|
|
// AND SMD.ITEMGROUP=%n
|
|
// AND SM.EMPLOYEEID = sempcc.EMPLOYEEID
|
|
// AND SMD.SALARYMONTHLYID = sempcc.SALARYMONTHLYID
|
|
// AND sempcc.COSTCENTERID = CC.CRGID
|
|
// AND SM.EmployeeID IN(%q)
|
|
// order by E.EmployeeNo",
|
|
// dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);
|
|
#endregion
|
|
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
|
|
SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,CC.DESCRIPTION AS CostCenter
|
|
FROM SalaryMonthly SM
|
|
LEFT JOIN SALARYMONTHLYDETAIL SMD ON SMD.SALARYMONTHLYID = SM.SALARYMONTHLYID
|
|
LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = SM.EMPLOYEEID
|
|
LEFT JOIN BRANCHES BR ON BR.BRANCHID = SM.BRANCHID
|
|
LEFT JOIN SALARYEMPCOSTCENTER sempcc ON sempcc.SALARYMONTHLYID = SM.SALARYMONTHLYID
|
|
LEFT JOIN CRG CC ON CC.CRGID = sempcc.COSTCENTERID
|
|
WHERE SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
|
|
AND SMD.ITEMGROUP=%n AND SM.EmployeeID IN(%q)
|
|
order by E.EmployeeNo", dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
else
|
|
{
|
|
#region DeadQuery
|
|
// string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
|
|
// SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
|
|
// FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,
|
|
// BRANCHES as BR
|
|
// WHERE E.EMPLOYEEID=SM.EMPLOYEEID
|
|
// AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
|
|
// AND BR.BranchID=SM.BranchID
|
|
// AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
|
|
// AND SMD.ITEMGROUP=%n
|
|
// AND SM.EmployeeID IN(%q)
|
|
// order by E.EmployeeNo",
|
|
// dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);
|
|
#endregion
|
|
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.ITEMCODE,
|
|
SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
|
|
FROM SalaryMonthly SM
|
|
LEFT JOIN SALARYMONTHLYDETAIL SMD ON SMD.SALARYMONTHLYID = SM.SALARYMONTHLYID
|
|
LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = SM.EMPLOYEEID
|
|
LEFT JOIN BRANCHES BR ON BR.BRANCHID = SM.BRANCHID
|
|
WHERE SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
|
|
AND SMD.ITEMGROUP=%n AND SM.EmployeeID IN(%q)
|
|
order by E.EmployeeNo", dateTime, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, sEmpID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
internal static IDataReader GetNotYetProcessUptoToday(TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where IsProcessed =%b and CHANGEDATE <=%d", false, DateTime.Today);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataSet GetEmpEuroBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|
{
|
|
DataSet oEuroBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT tab1.EMPLOYEENO, tab1.[NAME], tab1.OUTPAYACCOUNTNO EuroAccountNo, SUM(tab1.CHANGEDAMOUNT) Amount
|
|
FROM (SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.OUTPAYACCOUNTNO,SMD.ITEMCODE,
|
|
SMD.ITEMGROUP,SMD.CHANGEDAMOUNT,'' AS CostCenter
|
|
FROM SalaryMonthly SM
|
|
INNER JOIN SalaryMonthlyDetail SMD ON SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
|
|
INNER JOIN Employee e ON e.EMPLOYEEID=SM.EMPLOYEEID
|
|
LEFT JOIN BRANCHES BR ON BR.BranchID=SM.BranchID
|
|
WHERE
|
|
SM.SalaryMonth=%d
|
|
AND SMD.ITEMGROUP=%n AND smd.ITEMCODE IN (-101,-113)
|
|
AND SM.EmployeeID IN(%q)) tab1
|
|
GROUP BY tab1.EMPLOYEENO, tab1.[NAME], tab1.OUTPAYACCOUNTNO",
|
|
dateTime, EnumSalaryGroup.Miscellaneous, sEmpID);
|
|
|
|
oEuroBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oEuroBankAdvices;
|
|
}
|
|
|
|
|
|
internal static DataSet GetEmpOPIBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID, int bankID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount
|
|
FROM Employee e
|
|
Left Join OPIProcessDetail od on e.EmployeeID = od.EmployeeID
|
|
LEFT JOIN OPIProcess o ON od.OPIProcessID = o.OPIProcessID
|
|
LEFT JOIN OPIProcessDetailItem odi ON od.OPIProcessDetailID = odi.OPIProcessDetailID
|
|
WHERE o.OPIMonth = %d
|
|
AND odi.OPIType = %n
|
|
|
|
AND od.EmployeeID IN(%q)
|
|
GROUP BY e.EmployeeNo,e.Name,od.AccountNo", dateTime, (int)EnumOpiType.Provision, sEmpID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
// Will Be Appended to Above Query
|
|
// AND od.BankID = %n And bankID,
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
internal static DataSet GetEmpCarFuelBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID, int bankID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount
|
|
FROM Employee e
|
|
Left Join CarFuelProcessDetail od on e.EmployeeID = od.EmployeeID
|
|
LEFT JOIN CarFuelProcess o ON od.CarFuelProcessID = o.CarFuelProcessID
|
|
LEFT JOIN CarFuelProcessDetailItem odi ON od.CarFuelProcessDetailID = odi.CarFuelProcessDetailID
|
|
WHERE o.CarFuelMonth = %d
|
|
AND odi.CarFuelType = %n
|
|
AND od.BankID = %n
|
|
AND od.EmployeeID IN(%q)
|
|
GROUP BY e.EmployeeNo,e.Name,od.AccountNo", dateTime, (int)EnumCarFuelType.Payment, bankID, sEmpID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
internal static DataSet GetHNMEmpOPIBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
// string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,Sum(odi.ChangeNetAmount) AS Amount
|
|
// FROM Employee e,OPIProcess as o,
|
|
// OPIProcessDetail AS od,OPIProcessDetailItem odi
|
|
// WHERE o.OPIProcessID = od.OPIProcessID
|
|
// AND od.OPIProcessDetailID = odi.OPIProcessDetailID
|
|
// AND od.EmployeeID = e.EmployeeID
|
|
// AND od.AccountNo IS NOT NULL
|
|
// AND odi.OPIType =%n
|
|
// AND o.OPIMonth = %d
|
|
// AND od.EmployeeID IN(%q)
|
|
// GROUP BY e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo",
|
|
// (int)EnumOpiType.Payment, dateTime, sEmpID);
|
|
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT e.EmployeeNo,e.Name,od.AccountNo,cc.Code AS CostCenter,Sum(odi.ChangeNetAmount) AS Amount
|
|
FROM Employee e
|
|
INNER Join
|
|
OPIProcessDetail AS od
|
|
ON od.EmployeeID = e.EmployeeID
|
|
AND od.AccountNo IS NOT NULL
|
|
AND od.EmployeeID IN(%q)
|
|
INNER Join
|
|
OPIProcessDetailItem odi
|
|
ON od.OPIProcessDetailID = odi.OPIProcessDetailID
|
|
AND odi.OPIType =%n
|
|
INNER Join
|
|
OPIProcess as o
|
|
ON o.OPIProcessID = od.OPIProcessID
|
|
AND o.OPIMonth = %d
|
|
LEFT JOIN EMPCOSTCENTER ecs
|
|
ON e.EMPLOYEEID = ecs.EMPLOYEEID
|
|
AND ecs.CurrentCC = 1
|
|
LEFT JOIN CRG AS cc
|
|
ON cc.CRGID = ecs.COSTCENTERID
|
|
GROUP BY e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo,cc.Code",
|
|
sEmpID, (int)EnumOpiType.Payment, dateTime);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
//internal static DataSet GetEmpExpenseAmount(TransactionContext tc, DateTime dateTime, string sEmpID)
|
|
//{
|
|
// DataSet oEmpExpenseAmount = new DataSet();
|
|
// try
|
|
// {
|
|
// SELECT e.EmployeeNo,e.Name,e.EMAILADDRESS,od.AccountNo FROM Employee e,OPIProcess as o,
|
|
//OPIProcessDetail AS od,OPIProcessDetailItem odi
|
|
//WHERE o.OPIProcessID = od.OPIProcessID
|
|
//AND od.OPIProcessDetailID = odi.OPIProcessDetailID
|
|
//AND od.EmployeeID = e.EmployeeID
|
|
//AND odi.OPIItemID=1
|
|
//AND o.OPIMonth = '30 Apr 2014'
|
|
|
|
// oEmpExpenseAmount = tc.ExecuteDataSet("Select EMEx.EmployeeNo,SUM(Amount) as Amount from"
|
|
// + " EmployeeMonthlyExpense as EMEx where"
|
|
// + " EMEx.EmployeeNo in(%q) "
|
|
// + " and EMEx.ExpenseMonth=%d"
|
|
// + " group by EMEx.EmployeeNo",
|
|
// sEmpID,dateTime);
|
|
|
|
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// throw new Exception(ex.Message);
|
|
// }
|
|
// return oEmpExpenseAmount;
|
|
//}
|
|
|
|
internal static DataSet GetEmpBankHistory(TransactionContext tc, DateTime dateTime, int payrollTypeID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select BA.EmpBankAccountID as AccountID,Emp.EMPLOYEEID as ID, Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,
|
|
US.LoginID as CommitedBy,BA.CreationDate from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,
|
|
BRANCHES as BR,USERS as US
|
|
where BA.EMPLOYEEID=Emp.EMPLOYEEID AND B.BANKID=BR.BANKID AND BA.BRANCHID=BR.BRANCHID AND US.UserID=BA.CreatedBy
|
|
AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId = %n order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dateTime),
|
|
GlobalFunctions.LastDateOfMonth(dateTime), payrollTypeID);
|
|
|
|
//oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,US.LoginID as CommitedBy,BA.CreationDate"
|
|
// + " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
|
|
// + " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
|
|
// + " AND B.BANKID=BR.BANKID"
|
|
// + " AND BA.BRANCHID=BR.BRANCHID"
|
|
// + " AND US.UserID=BA.CreatedBy"
|
|
// + " AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId=%n"
|
|
// + " order by Emp.EMPLOYEENO",GlobalFunctions.FirstDateOfMonth(dateTime),
|
|
// GlobalFunctions.LastDateOfMonth(dateTime), SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
oBankAdvices = tc.ExecuteDataSet(sql);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
internal static DataSet GetEmpBankHistory(TransactionContext tc, DateTime dSFromDate, DateTime dSToDate, int payrollTypeID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Select BA.EmpBankAccountID as AccountID,Emp.EMPLOYEEID as ID, Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,
|
|
US.LoginID as CommitedBy,BA.CreationDate from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,
|
|
BRANCHES as BR,USERS as US
|
|
where BA.EMPLOYEEID=Emp.EMPLOYEEID AND B.BANKID=BR.BANKID AND BA.BRANCHID=BR.BRANCHID AND US.UserID=BA.CreatedBy
|
|
AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId = %n order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dSFromDate),
|
|
GlobalFunctions.LastDateOfMonth(dSToDate), payrollTypeID);
|
|
|
|
//oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as CBank,BA.ACCOUNTNO as CACCOUNTNO,BR.NAME as CBranch,BA.CHANGEDATE,US.LoginID as CommitedBy,BA.CreationDate"
|
|
// + " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
|
|
// + " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
|
|
// + " AND B.BANKID=BR.BANKID"
|
|
// + " AND BA.BRANCHID=BR.BRANCHID"
|
|
// + " AND US.UserID=BA.CreatedBy"
|
|
// + " AND BA.CHANGEDATE between %d and %d ANd Emp.PayrollTypeId=%n"
|
|
// + " order by Emp.EMPLOYEENO",GlobalFunctions.FirstDateOfMonth(dateTime),
|
|
// GlobalFunctions.LastDateOfMonth(dateTime), SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
oBankAdvices = tc.ExecuteDataSet(sql);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
|
|
internal static DataSet GetEmpPrvBankHistory(TransactionContext tc, DateTime dateTime, int payrollTypeID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
oBankAdvices = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,B.NAME as PBank,BA.ACCOUNTNO as PACCOUNTNO,BR.NAME as PBranch,BA.CHANGEDATE,US.UserName as CommitedBy,BA.CreationDate"
|
|
+ " from BANKACCOUNTHISTORY as BA,EMPLOYEE as Emp,BANKS as B,BRANCHES as BR,USERS as US "
|
|
+ " where BA.EMPLOYEEID=Emp.EMPLOYEEID"
|
|
+ " AND B.BANKID=BR.BANKID"
|
|
+ " AND BA.BRANCHID=BR.BRANCHID"
|
|
+ " AND US.UserID=BA.CreatedBy"
|
|
+ " AND BA.CHANGEDATE <%d ANd Emp.PayrollTypeiD=%n"
|
|
+ " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dateTime)
|
|
, payrollTypeID);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
internal static IDataReader GetByDate(TransactionContext tc, DateTime startDate, DateTime endDate, int payrollTypeID, string sEmpIDs)
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
IDataReader dr;
|
|
if (sEmpIDs == "")
|
|
{
|
|
sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where CHANGEDATE between %d and %d AND EmployeeID in(select EmployeeID from EMPLOYEE where PAYROLLTYPEID=%n) order by CreationDate ", startDate, endDate, payrollTypeID);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("Select * from BANKACCOUNTHISTORY where CHANGEDATE between %d and %d AND EmployeeID in(%q) order by CreationDate ", startDate, endDate, sEmpIDs);
|
|
|
|
}
|
|
dr = tc.ExecuteReader(sql);
|
|
|
|
|
|
return dr;
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [BANKACCOUNTHISTORY] WHERE EmpBankAccountID=%n", nID.Integer);
|
|
}
|
|
|
|
internal static void DeleteByBankEffectDate(TransactionContext tc, DateTime effectDate, ID empID)
|
|
{
|
|
//string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d And CHANGEDATE <= (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,%d)+1,0))) and EMPLOYEEID=%n", effectDate, effectDate, empID.Integer);
|
|
string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d and EMPLOYEEID=%n", effectDate, empID.Integer);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
internal static void DeleteByBankEffectDate(TransactionContext tc, DateTime effectDate, ID empID, EnumBankAccountType type)
|
|
{
|
|
//string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d And CHANGEDATE <= (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,%d)+1,0))) and EMPLOYEEID=%n", effectDate, effectDate, empID.Integer);
|
|
string sSQL = SQLParser.MakeSQL("Delete from BANKACCOUNTHISTORY Where CHANGEDATE >= %d and EMPLOYEEID=%n and AccountType = %n", effectDate, empID.Integer, (int)type);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
internal static void DeleteAll(TransactionContext tc)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [BANKACCOUNTHISTORY]");
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
internal static DataSet GetEmpBankAdvice(TransactionContext tc, DateTime salaryMonthDate, int bankID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,SM.AccountNo,SMD.CHANGEDAMOUNT,BR.Name as Branch,BR.Code
|
|
FROM EMPLOYEE E,SalaryMonthly SM,SalaryMonthlyDetail SMD,BRANCHES as BR,Banks bn
|
|
WHERE E.EMPLOYEEID=SM.EMPLOYEEID
|
|
AND SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
|
|
AND BR.BranchID=SM.BranchID
|
|
And bn.BankID = br.BankID
|
|
AND SM.SalaryMonth=%d AND SMD.ITEMCODE=%n
|
|
AND SMD.ITEMGROUP=%n AND bn.BankId=%n
|
|
order by E.EmployeeNo",
|
|
salaryMonthDate, EnumSalaryItemCode.Net_Payable, EnumSalaryGroup.OtherItem, bankID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
internal static DataSet GetEmpOPIBankAdvice(TransactionContext tc, DateTime salaryMonthDate, int bankID)
|
|
{
|
|
DataSet oBankAdvices = new DataSet();
|
|
try
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,opd.AccountNo,Sum(opdi.ChangeNetAmount) AS CHANGEDAMOUNT,
|
|
BR.Name as Branch,BR.Code
|
|
FROM EMPLOYEE E, OPIProcess op,OPIProcessDetail opd,OPIProcessDetailItem opdi,
|
|
BRANCHES as BR,Banks bn
|
|
WHERE E.EMPLOYEEID = opd.EMPLOYEEID
|
|
AND opd.OPIProcessID = op.OPIProcessID
|
|
AND opd.OPIProcessDetailID = opdi.OPIProcessDetailID
|
|
AND BR.BranchID = opd.BranchID
|
|
And bn.BankID = br.BankID
|
|
AND op.OPIMonth = %d
|
|
AND bn.BankId = %n
|
|
GROUP BY E.EMPLOYEENO,E.NAME,opd.AccountNo,BR.Name,BR.Code
|
|
order by E.EmployeeNo",
|
|
salaryMonthDate, bankID);
|
|
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return oBankAdvices;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
#endregion
|
|
}
|