121 lines
9.1 KiB
C#
121 lines
9.1 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Ease.CoreV35.DataAccess;
|
|
using Ease.CoreV35.Model;
|
|
using Payroll.BO;
|
|
|
|
namespace Payroll.Service
|
|
{
|
|
[Serializable]
|
|
internal static class BudgetProcessMonthlyDA
|
|
{
|
|
#region Parent Class Data Access
|
|
public static IDataReader Get(TransactionContext tc, ID nID, ID PayrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select * from BudgetProcessMonthly where BudgetProcessMonthlyID=%n AND PayrollTypeID=%n", nID.Integer, PayrollTypeID.Integer);
|
|
}
|
|
|
|
public static IDataReader Get(TransactionContext tc, ID PayrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select * from BudgetProcessMonthly WHERE PAyrollTypeID=%n", PayrollTypeID.Integer);
|
|
}
|
|
public static IDataReader Get(TransactionContext tc, int nID, ID PayrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("Select * from BudgetProcessMonthly where BudgetID=%n AND PayrollTypeID=%n", nID, PayrollTypeID.Integer);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, BudgetProcessMonthly item, ID PayrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("Insert into BudgetProcessMonthly(BudgetProcessMonthlyID,BudgetID,DepartmentID,EmployeeID,GradeID,IsConfirm,LocationID,Month,NewJoinerID,NewJoinerName,PayrollTypeID) Values" +
|
|
"(%n,%n,%n,%n,%n,%n,%n,%d,%n,%s,%n)", item.ID.Integer, item.BudgetID.Integer, DataReader.GetNullValue(item.DepartmentID, IDType.Integer), DataReader.GetNullValue(item.EmployeeID, IDType.Integer), DataReader.GetNullValue(item.GradeID, IDType.Integer), item.IsConfirm, DataReader.GetNullValue(item.LocationID, IDType.Integer), item.Month, DataReader.GetNullValue(item.NewJoinerID,IDType.Integer ), item.NewJoinerName, PayrollTypeID.Integer);
|
|
}
|
|
|
|
public static void Update(TransactionContext tc, BudgetProcessMonthly item, ID PayrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("Update BudgetProcessMonthly SET BudgetID=%n,DepartmentID=%n,EmployeeID=%n,GradeID=%n,IsConfirm=%n,LocationID=%n,Month=%d,NewJoinerID=%n,NewJoinerName=%s,PayrollTypeID=%n where BudgetProcessMonthlyID=%n"
|
|
, item.BudgetID.Integer, item.DepartmentID.Integer, item.EmployeeID.Integer, item.GradeID.Integer, item.IsConfirm, item.LocationID.Integer, item.Month, DataReader.GetNullValue(item.NewJoinerID, IDType.Integer), item.NewJoinerName, PayrollTypeID.Integer, item.ID.Integer);
|
|
}
|
|
|
|
public static void Delete(TransactionContext tc, ID nID, ID PayrollTypeID)
|
|
{
|
|
tc.ExecuteNonQuery("Delete from BudgetProcessMonthly where BudgetProcessMonthlyID=%n AND payrollTypeID=%n", nID.Integer, PayrollTypeID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region Child Class Data Access
|
|
public static IDataReader GetBudgetDetails(TransactionContext tc, ID id)
|
|
{
|
|
return tc.ExecuteReader("Select * from BudgetProcessMonthlyDetail where BudgetProcessMonthlyID=%n",id.Integer);
|
|
}
|
|
|
|
public static IDataReader GetBudgetMonthlyCCs(TransactionContext tc, ID id)
|
|
{
|
|
return tc.ExecuteReader("Select * from BudgetMonthlyCC where BudgetProcessMonthlyID=%n ", id.Integer);
|
|
}
|
|
public static IDataReader GetBudgetMonthlyCCs(TransactionContext tc, int nBudID, DateTime fromdate, DateTime todate)
|
|
{
|
|
//return tc.ExecuteReader("select * from BudgetMonthlyCC where (Month>=%d AND Month<=%d) AND BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where BudgetID=%n)", Ease.CoreV35.Utility.Global.DateFunctions.FirstDateOfMonth(fromdate), Ease.CoreV35.Utility.Global.DateFunctions.LastDateOfMonth(todate), nBudID);
|
|
return tc.ExecuteReader("select * from BudgetMonthlyCC where (Month>=%d AND Month<=%d) AND BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where BudgetID=%n)", Ease.CoreV35.Utility.Global.DateFunctions.FirstDateOfMonth(fromdate), Ease.CoreV35.Utility.Global.DateFunctions.LastDateOfMonth(todate), nBudID);
|
|
}
|
|
|
|
public static void Delete(TransactionContext tc, string tableName, string columnName, ID id)
|
|
{
|
|
tc.ExecuteNonQuery("Delete from %s where %s=%n",tableName,columnName,id.Integer);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, BudgetProcessMonthlyDetail item)
|
|
{
|
|
tc.ExecuteNonQuery("Insert into BudgetProcessMonthlyDetail(BudgetProcessMonthlyDetailID,BudgetProcessMonthlyID,BudgetComponentID,Amount,EmployeeID,Month,Name,BudgetGroup,BudgetCode,Changedamount,OriginID,Position) Values" +
|
|
"(%n,%n,%n,%n,%n,%d,%s,%n,%n,%n,%n,%n)", item.ID.Integer, item.BudgetProcessMonthlyID.Integer, DataReader.GetNullValue(item.BudgetComponentID, IDType.Integer), item.Calculatedamount, DataReader.GetNullValue(item.EmployeeID, IDType.Integer), item.Month, item.Name, (int)item.BudgetGroup, (int)item.BudgetCode, item.Changedamount, DataReader.GetNullValue(item.OriginID, IDType.Integer), item.Position);
|
|
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, BudgetMonthlyCC item)
|
|
{
|
|
tc.ExecuteNonQuery("Insert into BudgetMonthlyCC(BudgetMonthlyCCID,BudgetProcessMonthlyID,CCID,EmployeeID,Month,Percentage) Values" +
|
|
"(%n,%n,%n,%n,%d,%n)", item.ID.Integer,item.BudgetProcessMonthlyID.Integer,item.CCID.Integer,DataReader.GetNullValue(item.EmployeeID,IDType.Integer ) ,item.Month,item.Percentage );
|
|
|
|
}
|
|
public static IDataReader GetBudgetDetail(TransactionContext tc, DateTime stDate, DateTime endDate, int nBudgetID)
|
|
{
|
|
string sSql = "";
|
|
sSql = SQLParser.MakeSQL("select EmployeeID as EmpId,Name as ItemName,sum(Amount) as Amount from BudgetProcessMonthlyDetail " +
|
|
" where BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n) " +
|
|
" group by EmployeeID,BudgetComponentID,Name "+
|
|
" order By EmployeeID,BudgetComponentID", stDate, endDate, nBudgetID,SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
return tc.ExecuteReader(sSql);
|
|
}
|
|
public static IDataReader Get(TransactionContext tc, DateTime dtFrom, DateTime dtTo, int id, ID PayrollTypeID)
|
|
{
|
|
return tc.ExecuteReader("(select 1 SERIALNO,EmployeeID as EmpId,originID,Budgetcode,Name as ItemName,sum(Amount) as Amount,Position from BudgetProcessMonthlyDetail" +
|
|
" where BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n)"+
|
|
" and name<>'CTC'"+
|
|
" group by EmployeeID,BudgetComponentID,originID,Budgetcode,Name,Position )UNION " +
|
|
" (select 2 SERIALNO,EmployeeID as EmpId,originID,Budgetcode,Name as ItemName,sum(Amount) as Amount,Position from BudgetProcessMonthlyDetail " +
|
|
" where BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n) "+
|
|
" and name='CTC'"+
|
|
" group by EmployeeID,BudgetComponentID,originID,Budgetcode,Name,Position)", dtFrom, dtTo, id, PayrollTypeID.Integer, dtFrom, dtTo, id, PayrollTypeID.Integer);
|
|
}
|
|
public static IDataReader Get2(TransactionContext tc, DateTime dtFrom, DateTime dtTo, int id, ID PayrollTypeID)
|
|
{
|
|
return tc.ExecuteReader(@"(select BudgetProcessMonthlyID, EmployeeID as EmpId,originID,Budgetcode,Name as ItemName, Amount,Position from BudgetProcessMonthlyDetail where BudgetProcessMonthlyID
|
|
in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n) and name<>'CTC' )
|
|
UNION
|
|
(select BudgetProcessMonthlyID,EmployeeID as EmpId,originID,Budgetcode,Name as ItemName, Amount,Position from BudgetProcessMonthlyDetail where BudgetProcessMonthlyID
|
|
in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n) and name='CTC' )
|
|
", dtFrom, dtTo, id, PayrollTypeID.Integer, dtFrom, dtTo, id, PayrollTypeID.Integer);
|
|
}
|
|
public static IDataReader GetCTC(TransactionContext tc, DateTime dtFrom, DateTime dtTo, int id)
|
|
{
|
|
return tc.ExecuteReader("select EmployeeID as EmpId,Name as ItemName,sum(Amount) as Amount from BudgetProcessMonthlyDetail " +
|
|
" where Name='CTC' AND BudgetProcessMonthlyID in(select BudgetProcessMonthlyID from BudgetProcessMonthly where (Month>=%d AND Month<=%d) AND BudgetID=%n AND PayrollTypeID=%n) " +
|
|
" group by EmployeeID,BudgetComponentID,Name " +
|
|
" order By EmployeeID,BudgetComponentID", dtFrom, dtTo, id, SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
}
|
|
#endregion
|
|
}
|
|
}
|