785 lines
46 KiB
C#
785 lines
46 KiB
C#
|
using Ease.Core.DataAccess;
|
||
|
|
using System;
|
||
|
|
using HRM.BO;
|
||
|
|
using System.Collections.Generic;
|
||
|
|
using System.Data;
|
||
|
|
using System.Linq;
|
||
|
|
using System.Text;
|
||
|
|
using System.Threading.Tasks;
|
||
|
|
|
||
|
|
namespace HRM.DA
|
||
|
|
{
|
||
|
|
internal class EchoTexExceptionReportDA
|
||
|
|
{
|
||
|
|
internal static DataSet GetEmpPaySlipGrossForOthers(TransactionContext tc, DateTime dateTime, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet oSMonthlys = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.NAME Name,E.EMPLOYEENO,G.CODE Grade,DG.Name 'Designation',L.Description as LName,
|
||
|
|
D.DESCRIPTION Department,B.NAME as BName,SM.THISMONTHBASIC ActualBasic,E.PAYMENTMODE,
|
||
|
|
SM.ACCOUNTNO,SM.SalaryMonth,SMD.DESCRIPTION Description,SMD.ITEMGROUP,SM.ISFINALIZED,
|
||
|
|
SMD.CHANGEDAMOUNT,SMD.ItemCode,SMD.ItemID,cat.DESCRIPTION as Category,E.JOININGDATE,E.TINNo,
|
||
|
|
E.DEPARTMENTID
|
||
|
|
FROM SALARYMONTHLY SM
|
||
|
|
inner join EMPLOYEE E on SM.EMPLOYEEID=E.EMPLOYEEID
|
||
|
|
inner join Grades G on Sm.GRADEID=G.GRADEID
|
||
|
|
inner join DEPARTMENT as D on SM.DEPARTMENTID=D.DEPARTMENTID
|
||
|
|
inner join DESIGNATION as DG on E.DESIGNATIONID =DG.DESIGNATIONID
|
||
|
|
inner join LOCATION as L on SM.LOCATIONID=L.LOCATIONID
|
||
|
|
left outer join BRANCHES as Br on SM.BRANCHID=Br.BRANCHID
|
||
|
|
left outer join BANKS as B on Br.BANKID=B.BANKID
|
||
|
|
inner join CATEGORY cat on E.CATEGORYID=cat.CATEGORYID
|
||
|
|
inner join SALARYMONTHLYDETAIL as SMD on SM.SALARYMONTHLYID=SMD.SALARYMONTHLYID
|
||
|
|
AND SM.SalaryMonth=%d AND SMD.ITEMGROUP IN(1,2,8)
|
||
|
|
AND SM.EmployeeID IN(%q) ORDER BY E.EmployeeNo, Position", dateTime, sEmpID);
|
||
|
|
oSMonthlys = tc.ExecuteDataSet(sSQL);
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return oSMonthlys;
|
||
|
|
}
|
||
|
|
internal static DataSet GetEmpSalarySheetEchoTex(TransactionContext tc, DateTime dateTime, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet rootDataset = new DataSet();
|
||
|
|
DataSet tempdataset = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string query1 = SQLParser.MakeSQL(@"select A.EmployeeNo, A.Name,A.GName,A.DName,A.GCode,A.JoiningDate,A.SequenceNo,
|
||
|
|
A.Description, SUM(A.changedamount) Amount
|
||
|
|
from
|
||
|
|
(select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo
|
||
|
|
from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup in(8) AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)
|
||
|
|
UNION
|
||
|
|
select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate, sd.ItemGroup,sd.ItemCode,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,D.Name as DName,G.Code as GCode,G.SequenceNo from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup in(1) AND sd.ItemCode not in(-133) AND S.EmployeeID IN(%q)
|
||
|
|
) A group by
|
||
|
|
A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.DName,A.SequenceNo,A.GCode,
|
||
|
|
A.ItemCode, A.ItemID, A.Description,A.Position,A.JoiningDate order by A.Position,A.EmployeeNo,A.SequenceNo,A.GCode",
|
||
|
|
dateTime, sEmpID, dateTime, sEmpID, dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query1);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "GrossItem";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query2 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName,'Total Gross' as Description, Sum(A.amount) as amount
|
||
|
|
from
|
||
|
|
( select S.EmployeeID, Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName
|
||
|
|
from SALARYMONTHLY S,Grades G,DESIGNATION D, SalaryMonthlyDetail Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID AND S.SalaryMonth=%d and Sd.ItemGroup IN (1, 8) AND S.EmployeeID IN(%q)
|
||
|
|
group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name
|
||
|
|
UNION
|
||
|
|
select S.EmployeeID, -Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,G.Code as GCode,G.sequenceNo,D.Name as DName
|
||
|
|
from GRADES G,DESIGNATION D,SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND S.SalaryMonth=%d AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q)
|
||
|
|
group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name
|
||
|
|
) A, Employee E where A.EmployeeID = E.EmployeeID
|
||
|
|
group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,A.DName,E.JoiningDate ", dateTime, sEmpID, dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query2);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "TotalGross";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query3 = SQLParser.MakeSQL(@"select A.EmployeeNo, A.Name,A.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName,
|
||
|
|
A.Description, SUM(A.changedamount) Amount,A.ItemCode,A.ItemID
|
||
|
|
from
|
||
|
|
(select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate,sd.ItemGroup,sd.ItemCode,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName
|
||
|
|
from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup=3 AND S.EmployeeID IN(%q)
|
||
|
|
union
|
||
|
|
select S.EmployeeID, E.EmployeeNo, E.Name,E.JoiningDate,sd.ItemGroup,sd.ItemCode,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, -sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.sequenceNo,D.Name as DName
|
||
|
|
from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup=2 AND S.EmployeeID IN(%q)
|
||
|
|
) A group by
|
||
|
|
A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.SequenceNo,A.GCode,A.DName,
|
||
|
|
A.ItemCode, A.ItemID, A.Description,A.Position,A.JoiningDate order by A.EmployeeNo,A.Position,A.SequenceNo,A.GCode",
|
||
|
|
dateTime, sEmpID, dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query3);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "DeductItem";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query4 = SQLParser.MakeSQL(@"select A.EmployeeNo, A.Name,A.GName,A.GCode,A.SequenceNo,A.DName,A.JoiningDate,
|
||
|
|
A.Description, SUM(A.changedamount) Amount
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode,E.JoiningDate,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName
|
||
|
|
from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ITEMCODE =-139 AND Sd.ItemGroup IN (4) AND S.EmployeeID IN(%q)
|
||
|
|
UNION
|
||
|
|
select S.EmployeeID, E.EmployeeNo, E.Name, sd.ItemGroup,sd.ItemCode,E.JoiningDate,
|
||
|
|
sd.ItemID, sd.Description,sd.Position, sd.changedamount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName from Grades G,DESIGNATION D,Employee E, SALARYMONTHLY S, SalaryMonthlyDetail Sd
|
||
|
|
where G.GradeID= S.GradeID AND D.DESIGNATIONID=E.DESIGNATIONID AND E.employeeID=S.EmployeeID AND S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ITEMCODE =-139 AND Sd.ItemGroup IN (4) AND S.EmployeeID IN(%q)
|
||
|
|
) A group by
|
||
|
|
A.EmployeeID, A.EmployeeNo, A.Name,A.GName,A.SequenceNo,A.GCode,A.DName,A.JoiningDate,
|
||
|
|
A.ItemCode, A.ItemID, A.Description,A.Position order by A.Position,A.SequenceNo,A.GCode,A.EmployeeNo", dateTime, sEmpID, dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query4);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "LeaveDays";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query5 = SQLParser.MakeSQL(@"
|
||
|
|
select E.EmployeeNo, E.Name,A.GName,A.GCode,A.sequenceNo,A.DName, A.Description, Sum(A.amount) as amount
|
||
|
|
,E.JoiningDate
|
||
|
|
from
|
||
|
|
(select S.EmployeeID,Sd.Description, Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName
|
||
|
|
from GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND S.SalaryMonth=%d AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID AND Sd.ITEMCODE in(-135,-133,-140,-141,-142) AND Sd.ItemGroup IN (4) AND S.EmployeeID IN(%q)
|
||
|
|
group by S.EmployeeID,Sd.Description,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name
|
||
|
|
) A, Employee E where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.Description,A.GCode,A.DName,E.JoiningDate
|
||
|
|
|
||
|
|
", dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query5);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "AttendenceDays";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query6 = SQLParser.MakeSQL(@"select E.EmployeeNo, E.Name,E.JoiningDate,A.GName,A.GCode,A.SequenceNo,A.DName, 'Total Deduction' as Description, Sum(A.amount) as amount
|
||
|
|
from
|
||
|
|
( select S.EmployeeID, Sum(sd.changedamount) as Amount,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName
|
||
|
|
from GRADES G,DESIGNATION D,SALARYMONTHLY S,SalaryMonthlyDetail Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND S.SalaryMonth=%d AND G.GRADEID=S.GRADEID AND D.DESIGNATIONID=S.DESIGNATIONID and Sd.ItemGroup IN (2,3) AND S.EmployeeID IN(%q)
|
||
|
|
group by S.EmployeeID,G.DESCRIPTION,G.SequenceNo,G.Code,D.Name
|
||
|
|
) A, Employee E where A.EmployeeID = E.EmployeeID group by E.EmployeeNo, E.Name,A.GName,A.SequenceNo,A.GCode,A.DName,E.JoiningDate order by E.EmployeeNo,A.sequenceno,A.GCode", dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query6);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "TotalDeduction";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query7 = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName ,'Net Pay' as Description,SD.CHANGEDAMOUNT as Amount
|
||
|
|
from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND S.SalaryMonth=%d
|
||
|
|
AND G.GRADEID=S.GRADEID
|
||
|
|
AND D.DESIGNATIONID=S.DESIGNATIONID
|
||
|
|
AND E.EMPLOYEEID =S.EMPLOYEEID
|
||
|
|
and Sd.ItemGroup IN (5) AND Sd.ItemCode IN(-132) AND S.EmployeeID IN(%q)
|
||
|
|
order by E.EMPLOYEENO,G.SequenceNo,G.Code ", dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query7);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "NetPay";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query8 = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName ,'CPF' as Description,SD.CHANGEDAMOUNT as Amount
|
||
|
|
from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd
|
||
|
|
where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
AND S.SalaryMonth=%d
|
||
|
|
AND G.GRADEID=S.GRADEID
|
||
|
|
AND D.DESIGNATIONID=S.DESIGNATIONID
|
||
|
|
AND E.EMPLOYEEID =S.EMPLOYEEID
|
||
|
|
AND Sd.ItemGroup IN (3) AND Sd.ItemCode IN(-128) AND S.EmployeeID IN(%q) order by E.EMPLOYEENO,G.SequenceNo,G.Code ", dateTime, sEmpID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query8);
|
||
|
|
|
||
|
|
tempdataset.Tables[0].TableName = "CPF";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
// string query9 = SQLParser.MakeSQL(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName ,'CPF' as Description,SD.CHANGEDAMOUNT as Amount
|
||
|
|
// from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd
|
||
|
|
// where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
// AND S.SalaryMonth=%d
|
||
|
|
// AND G.GRADEID=S.GRADEID
|
||
|
|
// AND D.DESIGNATIONID=S.DESIGNATIONID
|
||
|
|
// AND E.EMPLOYEEID =S.EMPLOYEEID
|
||
|
|
// AND Sd.ItemGroup IN (3) AND Sd.ItemCode IN(-128)
|
||
|
|
// AND S.EmployeeID IN(%q) order by E.EMPLOYEENO,G.SequenceNo,G.Code ", dateTime, sEmpID);
|
||
|
|
|
||
|
|
// tempdataset = tc.ExecuteDataSet(@"Select E.EMPLOYEENO,E.NAME,E.JoiningDate,G.DESCRIPTION as GName,G.Code as GCode,G.SequenceNo,D.Name as DName ,'CPF' as Description,SD.CHANGEDAMOUNT as Amount
|
||
|
|
// from EMPLOYEE as E,GRADES as G,DESIGNATION D,SALARYMONTHLY as S,SALARYMONTHLYDETAIL as Sd
|
||
|
|
// where S.SalaryMonthlyID=Sd.SalaryMonthlyID
|
||
|
|
// AND S.SalaryMonth=%d
|
||
|
|
// AND G.GRADEID=S.GRADEID
|
||
|
|
// AND D.DESIGNATIONID=S.DESIGNATIONID
|
||
|
|
// AND E.EMPLOYEEID =S.EMPLOYEEID
|
||
|
|
// AND Sd.ItemGroup IN (3) AND Sd.ItemCode IN(-128) AND S.EmployeeID IN(%q) order by E.EMPLOYEENO,G.SequenceNo,G.Code ", dateTime, sEmpID);
|
||
|
|
|
||
|
|
// tempdataset.Tables[0].TableName = "CPF";
|
||
|
|
// rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return rootDataset;
|
||
|
|
}
|
||
|
|
internal static DataSet GetEmpBankAdvice(TransactionContext tc, DateTime dateTime, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet oBankAdvices = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string sSQL = string.Empty;
|
||
|
|
sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,SM.AccountNo,SMD.Amount as CHANGEDAMOUNT,'' AS CostCenter FROM
|
||
|
|
(SELECT SalaryMonthlyID,EmployeeID,AccountNo,BranchID FROM SALARYMONTHLY
|
||
|
|
WHERE SalaryMonth = %d
|
||
|
|
AND EMPLOYEEID IN (%q)
|
||
|
|
AND BRANCHID IS NOT Null) SM
|
||
|
|
INNER JOIN
|
||
|
|
EMPLOYEE E
|
||
|
|
ON SM.EmployeeID = E.EMPLOYEEID
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT SALARYMONTHLYID,
|
||
|
|
Sum(
|
||
|
|
CASE
|
||
|
|
WHEN ItemGroup = 1 THEN CalculatedAmount
|
||
|
|
WHEN ItemGroup = 2 THEN -CalculatedAmount
|
||
|
|
WHEN ItemGroup = 3 THEN -CalculatedAmount
|
||
|
|
END) Amount
|
||
|
|
FROM SALARYMONTHLYDETAIL
|
||
|
|
Where ITEMGROUP IN(1,2,3)
|
||
|
|
GROUP BY SALARYMONTHLYID) SMD
|
||
|
|
ON SM.SalaryMonthlyID = SMD.SalaryMonthlyID", dateTime, sEmpID);
|
||
|
|
|
||
|
|
// bool bfixedAmount = ConfigurationManager.GetBoolValue("costcenter", "manadatoryinsalary", EnumConfigurationType.Logic);
|
||
|
|
// if (!bfixedAmount)
|
||
|
|
// {
|
||
|
|
// 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);
|
||
|
|
|
||
|
|
|
||
|
|
// }
|
||
|
|
// else
|
||
|
|
// {
|
||
|
|
// 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);
|
||
|
|
|
||
|
|
// }
|
||
|
|
oBankAdvices = tc.ExecuteDataSet(sSQL);
|
||
|
|
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return oBankAdvices;
|
||
|
|
}
|
||
|
|
internal static DataSet GetMonthlyAttendanceDetail(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet monthlyDetail = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string sql = string.Empty;
|
||
|
|
|
||
|
|
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,des.NAME as Designation,
|
||
|
|
e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttenType,
|
||
|
|
'Department'= case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
||
|
|
END ,
|
||
|
|
'Floor'=case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END ,
|
||
|
|
'Section'= case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Line'=case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then ''
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END
|
||
|
|
from (SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate between %d and %d And EmployeeID IN(%q))d
|
||
|
|
inner join EMPLOYEE e
|
||
|
|
on e.EMPLOYEEID = d.EmployeeID
|
||
|
|
inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID
|
||
|
|
LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID
|
||
|
|
Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
||
|
|
group by e.EMPLOYEENO,e.Name,des.NAME, e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttenType,ac.CardNumber,
|
||
|
|
case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
||
|
|
END ,
|
||
|
|
case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END ,
|
||
|
|
case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END ,
|
||
|
|
case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then ''
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END
|
||
|
|
order by d.AttnDate", dFromDate, dToDate, sEmpID);
|
||
|
|
|
||
|
|
monthlyDetail = tc.ExecuteDataSet(sql);
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return monthlyDetail;
|
||
|
|
}
|
||
|
|
internal static DataSet GetDailyInOut(TransactionContext tc, DateTime attnDate, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet dailyInOut = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
|
||
|
|
string sqlQuery = SQLParser.MakeSQL(@"SELECT tab1.* FROM Employee e,
|
||
|
|
(select e.employeeid,e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.Name, deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
|
||
|
|
d.AttenType as Status, d.Comments,d.InTime,s.InTime ShiftInTime, d.OutTime, s.OutTime ShiftOutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
||
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
||
|
|
END,
|
||
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then ''
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
d.ReferenceID, e.DEPARTMENTID DeptID, dept5.DESCRIPTION as DeptName
|
||
|
|
from Employee e
|
||
|
|
INNER join(SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate=%d And EmployeeID IN(%q)) D
|
||
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
||
|
|
INNER Join DESIGNATION deg
|
||
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
||
|
|
Left JOIN DEPARTMENT dept5
|
||
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept4
|
||
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept3
|
||
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept2
|
||
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
||
|
|
Left JOIN Category cg
|
||
|
|
ON E.CATEGORYID = cg.CATEGORYID
|
||
|
|
LEFT JOIN Shift s
|
||
|
|
ON s.ShiftID = D.ShiftID
|
||
|
|
LEFT JOIN AccessCard ac
|
||
|
|
ON e.CardID = ac.AccessCardID)tab1
|
||
|
|
where e.employeeid=tab1.employeeID and e.status=1
|
||
|
|
", attnDate, sEmpID);
|
||
|
|
dailyInOut = tc.ExecuteDataSet(sqlQuery);
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return dailyInOut;
|
||
|
|
}
|
||
|
|
internal static DataSet GetDailyAttnSummary(TransactionContext tc, DateTime attnDate, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet dailyAbsent = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string strGenderCase = string.Empty, strGenderCaseNumber = string.Empty, strStatusCase = string.Empty, strStatusCaseNumber = string.Empty;
|
||
|
|
|
||
|
|
#region Creating Case From Enum
|
||
|
|
StringBuilder sb = new StringBuilder();
|
||
|
|
StringBuilder sbNumber = new StringBuilder();
|
||
|
|
string wThenString = "When {0} then '{1}' ", wThenNumber = "When {0} then {1} ";
|
||
|
|
sb.Append("Case GENDER ");
|
||
|
|
sbNumber.Append("Case GENDER ");
|
||
|
|
List<EnumGender> enumGenders = Enum.GetValues(typeof(EnumGender)).Cast<EnumGender>()
|
||
|
|
.ToList(); ;
|
||
|
|
foreach (EnumGender item in enumGenders)
|
||
|
|
{
|
||
|
|
switch (item)
|
||
|
|
{
|
||
|
|
case EnumGender.Male:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, "M");
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, (int)item);
|
||
|
|
break;
|
||
|
|
case EnumGender.Female:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, "F");
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, (int)item);
|
||
|
|
break;
|
||
|
|
default:
|
||
|
|
break;
|
||
|
|
|
||
|
|
}
|
||
|
|
}
|
||
|
|
sb.Append("Else 'Other' End ");
|
||
|
|
sbNumber.Append("Else 3 End ");
|
||
|
|
strGenderCase = sb.ToString();
|
||
|
|
strGenderCaseNumber = sbNumber.ToString();
|
||
|
|
|
||
|
|
sb = new StringBuilder();
|
||
|
|
sbNumber = new StringBuilder();
|
||
|
|
|
||
|
|
sb.Append("Case AttenType ");
|
||
|
|
sbNumber.Append("Case AttenType ");
|
||
|
|
List<EnumAttendanceType> enumAttnTypes = Enum.GetValues(typeof(EnumAttendanceType)).Cast<EnumAttendanceType>()
|
||
|
|
.ToList(); ;
|
||
|
|
foreach (EnumAttendanceType item in enumAttnTypes)
|
||
|
|
{
|
||
|
|
switch (item)
|
||
|
|
{
|
||
|
|
case EnumAttendanceType.Present:
|
||
|
|
case EnumAttendanceType.Delay:
|
||
|
|
case EnumAttendanceType.Late:
|
||
|
|
case EnumAttendanceType.Early:
|
||
|
|
case EnumAttendanceType.HalfDay:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, "Present");
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 1);
|
||
|
|
break;
|
||
|
|
|
||
|
|
|
||
|
|
case EnumAttendanceType.Leave:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, item.ToString());
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 2);
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.WeeklyHoliday:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, "Weekly Holiday");
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 3);
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.Holiday:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, item.ToString());
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 4);
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.OutSideDuty:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, "Outside Duty");
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 5);
|
||
|
|
break;
|
||
|
|
|
||
|
|
case EnumAttendanceType.Absent:
|
||
|
|
sb.AppendFormat(wThenString, (int)item, item.ToString());
|
||
|
|
sbNumber.AppendFormat(wThenNumber, (int)item, 6);
|
||
|
|
break;
|
||
|
|
default:
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
sb.Append("End ");
|
||
|
|
sbNumber.Append("End ");
|
||
|
|
strStatusCase = sb.ToString();
|
||
|
|
strStatusCaseNumber = sbNumber.ToString();
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
string sql = SQLParser.MakeSQL(@"SELECT IsNULL(d.NAME,'NA') AS DESIGNATION,'Strenth' AS Status,0 AS StatusSerial,
|
||
|
|
%q Gender,
|
||
|
|
%q GenderSerial,
|
||
|
|
Count(*) AS HeadCount
|
||
|
|
FROM
|
||
|
|
(SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate = %d
|
||
|
|
And EmployeeID in(%q)) dap
|
||
|
|
INNER JOIN
|
||
|
|
EMPLOYEE e
|
||
|
|
ON e.EmployeeID = dap.EmployeeID
|
||
|
|
LEFT JOIN
|
||
|
|
Designation d
|
||
|
|
ON e.DESIGNATIONID = d.DESIGNATIONID
|
||
|
|
GROUP BY d.Name,Gender
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT IsNULL(d.NAME,'NA') AS DESIGNATION,
|
||
|
|
%q Status,
|
||
|
|
%q StatusSerial,
|
||
|
|
%q Gender,
|
||
|
|
%q GenderSerial,
|
||
|
|
Count(*) AS HeadCount
|
||
|
|
FROM
|
||
|
|
(SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate = %d
|
||
|
|
And EmployeeID in(%q)) dap
|
||
|
|
INNER JOIN
|
||
|
|
EMPLOYEE e
|
||
|
|
ON e.EmployeeID = dap.EmployeeID
|
||
|
|
LEFT JOIN
|
||
|
|
Designation d
|
||
|
|
ON e.DESIGNATIONID = d.DESIGNATIONID
|
||
|
|
GROUP BY d.Name,Gender,AttenType", strGenderCase, strGenderCaseNumber,
|
||
|
|
attnDate, sEmpID,
|
||
|
|
strStatusCase, strStatusCaseNumber,
|
||
|
|
strGenderCase, strGenderCaseNumber,
|
||
|
|
attnDate, sEmpID);
|
||
|
|
|
||
|
|
dailyAbsent = tc.ExecuteDataSet(sql);
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return dailyAbsent;
|
||
|
|
}
|
||
|
|
internal static DataSet GetDateRangeDataByStatus(TransactionContext tc, DateTime attnFromDate, DateTime attnToDate, string sStatus, string sEmpID)
|
||
|
|
{
|
||
|
|
DataSet dailyInOut = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string sql;
|
||
|
|
if (sStatus != string.Empty)
|
||
|
|
{
|
||
|
|
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,
|
||
|
|
e.Name,deg.NAME as Designation,
|
||
|
|
cg.DESCRIPTION AS EmpType,
|
||
|
|
|
||
|
|
d.AttenType as Status,
|
||
|
|
d.Comments,
|
||
|
|
d.AttnDate,
|
||
|
|
d.InTime,
|
||
|
|
d.OutTime,
|
||
|
|
ISNULL(d.OTHour,0) OTHour,
|
||
|
|
D.ShiftID,
|
||
|
|
IsNULL(s.ShortName,'') as Shift,
|
||
|
|
dept.DEPARTMENTID DeptID,
|
||
|
|
|
||
|
|
d.ReferenceID,
|
||
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
||
|
|
END,
|
||
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then ''
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END
|
||
|
|
from (SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate between %d and %d And AttenType IN(%q) And EmployeeID IN(%q)) D
|
||
|
|
INNER Join Employee e
|
||
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
||
|
|
INNER Join DESIGNATION deg
|
||
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
||
|
|
INNER JOIN DEPARTMENT dept
|
||
|
|
ON dept.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
||
|
|
INNER JOIN Category cg
|
||
|
|
ON E.CATEGORYID = cg.CATEGORYID
|
||
|
|
LEFT JOIN Shift s
|
||
|
|
ON s.ShiftID = D.ShiftID
|
||
|
|
LEFT JOIN AccessCard ac
|
||
|
|
ON e.CardID = ac.AccessCardID", attnFromDate, attnToDate, sStatus, sEmpID);
|
||
|
|
}
|
||
|
|
else
|
||
|
|
{
|
||
|
|
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,
|
||
|
|
e.Name,deg.NAME as Designation,
|
||
|
|
cg.DESCRIPTION AS EmpType,
|
||
|
|
|
||
|
|
d.AttenType as Status,
|
||
|
|
d.Comments,
|
||
|
|
d.AttnDate,
|
||
|
|
d.InTime,
|
||
|
|
d.OutTime,
|
||
|
|
d.OTHour,
|
||
|
|
D.ShiftID,
|
||
|
|
IsNULL(s.ShortName,'') as Shift,
|
||
|
|
dept.DEPARTMENTID DeptID,
|
||
|
|
dept.DESCRIPTION as DeptName,
|
||
|
|
d.ReferenceID,'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
||
|
|
END,
|
||
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END,
|
||
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
||
|
|
when dept5.tire = 4 then ''
|
||
|
|
when dept5.tire = 3 then ''
|
||
|
|
when dept5.tire = 2 then ''
|
||
|
|
END
|
||
|
|
from (SELECT * FROM DailyAttnProcess
|
||
|
|
WHERE AttnDate between %d and %d And EmployeeID IN(%q)) D
|
||
|
|
INNER Join Employee e
|
||
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
||
|
|
INNER Join DESIGNATION deg
|
||
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
||
|
|
INNER JOIN DEPARTMENT dept
|
||
|
|
ON dept.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
||
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
||
|
|
INNER JOIN Category cg
|
||
|
|
ON E.CATEGORYID = cg.CATEGORYID
|
||
|
|
LEFT JOIN Shift s
|
||
|
|
ON s.ShiftID = D.ShiftID
|
||
|
|
LEFT JOIN AccessCard ac
|
||
|
|
ON e.CardID = ac.AccessCardID", attnFromDate, attnToDate, sEmpID);
|
||
|
|
}
|
||
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return dailyInOut;
|
||
|
|
}
|
||
|
|
internal static DataSet GetMonthlyKPIDetail(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID, List<Leave> oLeaves)
|
||
|
|
{
|
||
|
|
DataSet monthlyDetail = new DataSet();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
|
||
|
|
string sql = string.Empty;
|
||
|
|
|
||
|
|
StringBuilder statusCase = new StringBuilder();
|
||
|
|
|
||
|
|
StringBuilder foreColor = new StringBuilder();
|
||
|
|
|
||
|
|
#region Generating Cases
|
||
|
|
statusCase.Append("'KPIStatus' = case");
|
||
|
|
foreColor.Append("'ForeColor' = case");
|
||
|
|
List<EnumAttendanceType> attnTypes = Enum.GetValues(typeof(EnumAttendanceType)).Cast<EnumAttendanceType>().ToList();
|
||
|
|
|
||
|
|
foreach (EnumAttendanceType enItem in attnTypes)
|
||
|
|
{
|
||
|
|
|
||
|
|
switch (enItem)
|
||
|
|
{
|
||
|
|
case EnumAttendanceType.Absent:
|
||
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "A");
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.Holiday:
|
||
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "F");
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.WeeklyHoliday:
|
||
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "H");
|
||
|
|
foreColor.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "Red");
|
||
|
|
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.Leave:
|
||
|
|
|
||
|
|
foreach (Leave lvItem in oLeaves)
|
||
|
|
{
|
||
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} and isNULL(dap.ReferenceID,0) ={1} then '{2}'", (int)enItem, lvItem.ID, lvItem.Code);
|
||
|
|
}
|
||
|
|
|
||
|
|
break;
|
||
|
|
case EnumAttendanceType.Present:
|
||
|
|
case EnumAttendanceType.HalfDay:
|
||
|
|
case EnumAttendanceType.Delay:
|
||
|
|
case EnumAttendanceType.Late:
|
||
|
|
case EnumAttendanceType.Early:
|
||
|
|
case EnumAttendanceType.OutSideDuty:
|
||
|
|
default:
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
statusCase.Append(" else ISNULL(RIGHT('00'+Convert(varchar(50),Convert(INT,FlooR(dap.OTHour))),2)+':'+ RIGHT('00'+Convert(varchar(50),Convert(INT,(dap.OTHour%1)*60)),2),'00:00') END ");
|
||
|
|
foreColor.Append(" else 'Black' END ");
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
sql = SQLParser.MakeSQL(@"SELECT eInfo.Name EmployeeName,eInfo.EmployeeNo IDNo, eInfo.Designation, eInfo.Department,
|
||
|
|
eInfo.GradeName GRADE,eInfo.PaymentModeName AS BC,dap.AttnDate,dap.AttenType,IsNULL(dap.OTHour,0) OTHour,
|
||
|
|
CASE WHEN dap.OutTime>dap.InTime THEN
|
||
|
|
isnull(datediff(minute,dap.InTime ,dap.OutTime),0)
|
||
|
|
ELSE 0
|
||
|
|
END AS Minutes ,
|
||
|
|
isNULL(dap.ReferenceID,0) ReferenceID,%q,ISNULL(Line,'') Line,ISNULL(eInfo.Section,'') Section,ISNULL(eInfo.Floor,'') Floor,
|
||
|
|
'BackColor' = CASE
|
||
|
|
WHEN dap.OTHour >=6
|
||
|
|
THEN 'Red'
|
||
|
|
WHEN dap.OTHour >=5 AND dap.OTHour <6
|
||
|
|
Then '#C85F08'
|
||
|
|
WHEN dap.OTHour >=4 AND dap.OTHour <5
|
||
|
|
THEN 'Orange'
|
||
|
|
WHEN dap.OTHour >=3 AND dap.OTHour <4
|
||
|
|
THEN 'Yellow'
|
||
|
|
ELSE 'White'
|
||
|
|
END,
|
||
|
|
%q
|
||
|
|
FROM
|
||
|
|
vw_EmployeeImportantInfo eInfo
|
||
|
|
INNER JOIN DailyAttnProcess dap
|
||
|
|
ON eInfo.EmployeeID = dap.EmployeeId
|
||
|
|
And eInfo.EmployeeID IN(%q)
|
||
|
|
and dap.AttnDate between %d and %d", statusCase, foreColor, sEmpID, dFromDate, dToDate);
|
||
|
|
|
||
|
|
monthlyDetail = tc.ExecuteDataSet(sql);
|
||
|
|
monthlyDetail.Tables[0].TableName = "MonthlyKPIDetail";
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
return monthlyDetail;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|