using System; using System.Collections.Generic; using System.Linq; using System.Text; using Ease.CoreV35.DataAccess; using Payroll.BO; using System.Data; using Ease.CoreV35.Model; using Payroll.BO; namespace Payroll.Service { #region DailyAttnProcessDA internal class DailyAttnProcessDA { #region Constructor private DailyAttnProcessDA() { } #endregion #region Insert function internal static void Insert(TransactionContext tc, DailyAttnProcess item) { tc.ExecuteNonQuery("INSERT INTO DailyAttnProcess(DailyAttnProcessID, EmployeeID, AttnDate, ShiftID, InTime, OutTime,WorkDayType, AttnType, Comments, IsManualEntry, LateHour, EarlyHour, OTHour, ReferenceID, CreatedBy, CreatedDate)" + " VALUES(%n, %n, %d, %n, %D, %D, %n, %n, %s,%b, %n, %n, %n, %n, %n, %d)", item.ID.Integer, item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), DataReader.GetNullValue(item.InTime), DataReader.GetNullValue(item.OutTime), item.WorkDayType, item.AttenType, item.Comments, item.IsManualEntry, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID.Integer), item.CreatedBy.Integer, item.CreatedDate); } #endregion #region Update function internal static void Update(TransactionContext tc, DailyAttnProcess item) { tc.ExecuteNonQuery("UPDATE DailyAttnProcess SET EmployeeID=%n, AttnDate=%d, ShiftID=%n, InTime=%D, OutTime=%D,WorkDayType=%n, AttnType=%n, Comments=%s,Reason=%s, IsManualEntry=%b,IsLate=%b, LateHour=%n, EarlyHour=%n, OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" + " WHERE DailyAttnProcessID=%n", item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), item.InTime, item.OutTime,item.WorkDayType,item.AttenType, item.Comments,item.Reason, item.IsManualEntry,item.IsLate, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID.Integer), item.ModifiedBy.Integer, item.ModifiedDate, item.ID.Integer); } internal static void UpdateLeave(TransactionContext tc, ID employeeID, ID leaveID, DateTime leavaDate, int n) { //if (n == 1) //{ tc.ExecuteNonQuery("UPDATE DailyAttnProcess SET InTime=null, OutTime=null, OTHour = 0.0,WorkDayType=%n, AttnType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" + " WHERE EmployeeID=%n AND AttnDate=%d ",EnumWorkPlanDayType.WorkingDay, EnumAttendanceType.Leave, leaveID.Integer, User.CurrentUser.ID.Integer, DateTime.Now, employeeID.Integer, leavaDate); //} //else //{ // tc.ExecuteNonQuery("UPDATE BuyerDailyAttProcess SET InTime=null, OutTime=null, OTHour = 0.0,WorkDayType=%n, AttnType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" + // " WHERE EmployeeID=%n AND AttnDate=%d ", EnumAttendanceType.Leave, leaveID.Integer, User.CurrentUser.ID.Integer, DateTime.Now, employeeID.Integer, leavaDate); //} } #endregion #region Get Function internal static IDataReader Get(TransactionContext tc) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess"); } internal static IDataReader GetProcessByWPG(TransactionContext tc, EnumWorkPlanGroup wpg, DateTime attnDate) { //string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess where DailyAttnProcessID=3021145"); return tc.ExecuteReader("SELECT * FROM DailyAttnProcess where AttnDate=%d AND EmployeeID IN " + "(SELECT EmployeeID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID=%n)", attnDate, wpg); // return tc.ExecuteReader(sql); } internal static IDataReader Get(TransactionContext tc, DateTime attnDate) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d", attnDate); } internal static IDataReader GetManualProcess(TransactionContext tc, DateTime attnDate) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d And IsManualEntry=1", attnDate); } //new internal static IDataReader GetWhrOutTimeIsNull(TransactionContext tc, DateTime attnDate, ID shiftID) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d AND ShiftID=%n AND Intime IS NOT NULL AND OutTime IS NULL", attnDate, shiftID.Integer); } // internal static IDataReader Get(TransactionContext tc, ID empID, DateTime fromDate, DateTime toDate) { string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate BETWEEN %d AND %d order by AttnDate ASC", empID.Integer, fromDate, toDate); return tc.ExecuteReader(sql); } internal static IDataReader Get(TransactionContext tc, string empID, DateTime fromDate, DateTime toDate) { string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE EmployeeID IN(%q) AND AttnDate BETWEEN %d AND %d order by AttnDate ASC", empID, fromDate, toDate); return tc.ExecuteReader(sql); } internal static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate) { string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE AttnDate BETWEEN %d AND %d order by AttnDate ASC", fromDate, toDate); return tc.ExecuteReader(sql); } internal static IDataReader Get(TransactionContext tc, DateTime attnDate, ID shiftID, EnumAttendanceType attnType) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d AND ShiftID=%n AND AttnType=%n", attnDate, shiftID.Integer, attnType); } internal static IDataReader Get(TransactionContext tc, ID nID) { return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE DailyAttnProcessID=%n", nID); } internal static DataSet GetMonthlyAttn(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID) { DataSet monthlyAttn = new DataSet(); try { string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.NAME, des.NAME As Designation, dept.[DESCRIPTION] Department, g.DESCRIPTION As Grade, e.JOININGDATE, e.GROSSSALARY, IsNull(Pr.Present,0) Present, IsNull(Lt.Late, 0) Late, IsNull(Ab.Absent, 0) Absent, IsNull(HD.Holiday, 0) Holiday, IsNull(Lv.Leave,0) Leave, " + "Isnull(OD.OutsideDuty, 0) OutsideDuty, Isnull(DL.Delays, 0) Delays, Isnull(EL.Early, 0) Early, " + "((sum (floor(d.OTHour))+ floor(((sum (d.OTHour-floor(d.OTHour)))*100)/60)) + cast(((cast(((sum(d.OTHour-floor(d.OTHour)))*100)as int))%q60) as float)/100) As TotalOT, " + "((Case when Pr.Present is null then 0 else Pr.Present end) + (Case when HD.Holiday is Null Then 0 Else HD.Holiday end)+(Case when Lv.Leave is Null Then 0 Else Lv.Leave end)) As TotalAttn " + "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 " + "INNER JOIN Department dept ON dept.DEPARTMENTID=e.DepartmentID " + "inner join GRADES g on g.GRADEID = e.GRADEID " + "LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Present " + "from DailyAttnProcess " + "where AttnType IN(1,11) and AttnDate between %d and %d " + "group by EmployeeID " + ") Pr on d.EmployeeID=Pr.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Late " + "from DailyAttnProcess " + "where AttnType=11 and AttnDate between %d and %d " + "group by EmployeeID " + ") Lt on d.EmployeeID=Lt.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Absent " + "from DailyAttnProcess " + "where AttnType=2 and AttnDate between %d and %d " + "group by EmployeeID " + ") Ab on d.EmployeeID=Ab.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Holiday " + "from DailyAttnProcess " + "where AttnType IN(5,8) and AttnDate between %d and %d " + "group by EmployeeID " + ") HD on d.EmployeeID=HD.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Leave " + "from DailyAttnProcess " + "where AttnType=4 and AttnDate between %d and %d " + "group by EmployeeID " + ") Lv on d.EmployeeID=Lv.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as OutsideDuty " + "from DailyAttnProcess " + "where AttnType=7 and AttnDate between %d and %d " + "group by EmployeeID " + ") OD on d.EmployeeID=OD.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Delays " + "from DailyAttnProcess " + "where AttnType=3 and AttnDate between %d and %d " + "group by EmployeeID " + ") DL on d.EmployeeID=DL.EmployeeID " + "left outer join " + "(" + "select EmployeeID, count(DailyAttnProcessID) as Early " + "from DailyAttnProcess " + "where AttnType=12 and AttnDate between %d and %d " + "group by EmployeeID " + ") EL on d.EmployeeID=EL.EmployeeID " + "group by e.EMPLOYEENO, e.NAME,ac.CardNumber, des.NAME, g.DESCRIPTION, e.JOININGDATE, e.GROSSSALARY, " + "Pr.Present,Lt.Late,Ab.Absent,HD.Holiday,Lv.Leave, OD.OutsideDuty, dl.Delays, EL.Early, dept.[DESCRIPTION] ", "%", dFromDate, dToDate, sEmpID, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate); monthlyAttn = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return monthlyAttn; } internal static DataSet GetDailyInOut(TransactionContext tc, DateTime attnDate, string sEmpID) { DataSet dailyInOut = new DataSet(); try { string sql = 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.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift, IsNull(dept.DEPARTMENTID,0) DeptID,IsNull(dept.DESCRIPTION,'') as DeptName,d.ReferenceID 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 dept ON dept.DEPARTMENTID = e.DEPARTMENTID 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(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return dailyInOut; } internal static DataSet GetDailyDataByStatus(TransactionContext tc, DateTime attnDate,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.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift, dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID from (SELECT * FROM DailyAttnProcess WHERE AttnDate=%d And AttnType 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 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", attnDate, 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.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift, dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID from (SELECT * FROM DailyAttnProcess WHERE AttnDate=%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 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", attnDate, sEmpID); } dailyInOut = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return dailyInOut; } 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.AttnType 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 from (SELECT * FROM DailyAttnProcess WHERE AttnDate between %d and %d And AttnType 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 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.AttnType 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 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 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 GetDailyInOutAndAbsent(TransactionContext tc, DateTime attnDate, string sEmpID) { DataSet dailyInOutAndAbsent = new DataSet(); try { string sql = SQLParser.MakeSQL("select A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME as Designation, d.AttnType, d.Comments,d.InTime,d.OutTime,d.LateHour,d.OTHour,s.ShortName,dept.DEPARTMENTID,dept.DESCRIPTION as DepartmentName " + "from DailyAttnProcess D, AccessCard A, Employee e, DESIGNATION deg,Shift s, DEPARTMENT dept where E.EMPLOYEEID = d.EmployeeID " + "AND E.CardID = A.AccessCardID " + " AND deg.DESIGNATIONID = e.DESIGNATIONID " + " AND s.ShiftID = d.ShiftID " + " AND dept.DEPARTMENTID = e.DEPARTMENTID " + "AND AttnDate=%d AND E.EmployeeID IN(%q)", attnDate, sEmpID); dailyInOutAndAbsent = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return dailyInOutAndAbsent; } internal static DataSet GetDailyAbsent(TransactionContext tc, DateTime attnDate, string sEmpID) { DataSet dailyAbsent = new DataSet(); try { //string sql = SQLParser.MakeSQL("select c.CardNumber, e.EMPLOYEENO, e.Name, des.NAME as Designation, d.AttnType, d.Comments " // + "from DailyAttnProcess d inner join EMPLOYEE e on e.EMPLOYEEID = d.EmployeeID " // + "inner join CardOperation c on c.CardID = e.CardID " // + "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID " // + "where AttnDate=%d AND d.EmployeeID IN(%q) AND AttnType=%n", attnDate, sEmpID, EnumAttendanceType.Absent); string sql = SQLParser.MakeSQL("select A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME as Designation, d.AttnType, d.Comments " + "from DailyAttnProcess D, AccessCard A, Employee e, DESIGNATION deg where E.EMPLOYEEID = d.EmployeeID " + "AND E.CardID = A.AccessCardID " + " AND deg.DESIGNATIONID = e.DESIGNATIONID " + "AND AttnDate=%d AND E.EmployeeID IN(%q) AND AttnType=%n", attnDate, sEmpID, EnumAttendanceType.Absent); dailyAbsent = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return dailyAbsent; } internal static DataSet GetMonthlyDetail(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.AttnType 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 group by e.EMPLOYEENO,e.Name,des.NAME, e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType,ac.CardNumber order by d.AttnDate", dFromDate, dToDate, sEmpID); monthlyDetail = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return monthlyDetail; } internal static DataSet GetMonthlyLongAbsent(TransactionContext tc, DateTime dFromDate, DateTime dToDate, int totalAbsentDays) { DataSet monthlyDetail = new DataSet(); try { string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,e.Name,des.NAME as Designation, " + "e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType " + "from DailyAttnProcess d INNER JOIN " + "(SELECT EmployeeID,absCount=SUM(CASE WHEN AttnType=%n THEN 1 ELSE 0 END) from DailyAttnProcess " + "WHERE AttnDate between %d and %d GROUP BY EmployeeID) abs ON D.EmployeeID=abs.EmployeeID inner join EMPLOYEE e on e.EMPLOYEEID = d.EmployeeID " + "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID " + "where AttnDate between %d and %d AND abs.absCount>=%n group by e.EMPLOYEENO,e.Name,des.NAME, e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType " + "order by d.AttnDate", EnumAttendanceType.Absent, dFromDate, dToDate, dFromDate, dToDate, totalAbsentDays); monthlyDetail = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return monthlyDetail; } internal static DataSet GetEmpAttenInfo(TransactionContext tc, string sEmpID) { DataSet empAttenInfo = new DataSet(); try { string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,e.Name,ac.CardNumber,s.ShortName,w.Name " + "from employee e inner join AccessCard ac on ac.AccessCardID = e.CardID " + "inner join EmployeeWorkPlanSetup es on es.EmployeeID = e.EMPLOYEEID " + "inner join shift s on s.ShiftID = es.ShiftID " + "inner join WorkPlanGroup w on w.WorkPlanGroupID = es.WorkPlanGroupID WHERE e.EMPLOYEEID IN(%q) order by e.EMPLOYEENO ", sEmpID); empAttenInfo = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return empAttenInfo; } internal static DataSet GetEmpCardInfo(TransactionContext tc, string sEmpID) { DataSet empAttenInfo = new DataSet(); try { string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS IDNo, e.NAME,e.JOININGDATE AS DOJ, IsNULL(e.[PhotoPath],'') AS EmpPhoto, IsNULL(des.NAME,'') AS Desig, IsNULL(gd.DESCRIPTION,'') AS Grade, IsNULL(gd.COLORCODE,'White') AS GradeColor, IsNULL(dept.DESCRIPTION,'') AS Dept, IsNULL(acrd.CardNumber,'') AS CardNo, CASE WHEN dept.TIRE = 4 THEN dept.DESCRIPTION WHEN dept.TIRE = 5 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID) ELSE '' END Section, CASE WHEN e.BLOODGROUP =1 THEN 'NONE' WHEN e.BLOODGROUP =2 THEN 'A+' WHEN e.BLOODGROUP =3 THEN 'A-' WHEN e.BLOODGROUP =4 THEN 'B+' WHEN e.BLOODGROUP =5 THEN 'B-' WHEN e.BLOODGROUP =6 THEN 'O+' WHEN e.BLOODGROUP =7 THEN 'O-' WHEN e.BLOODGROUP =8 THEN 'AB+' WHEN e.BLOODGROUP =9 THEN 'AB-' WHEN e.BLOODGROUP =10 THEN 'NA' ELSE '' END BG FROM (SELECT * FROM EMPLOYEE WHERE EMPLOYEEID IN (%q)) e LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID LEFT JOIN GRADES gd ON e.GRADEID = gd.GRADEID LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN AccessCard acrd ON e.CardID = acrd.AccessCardID ", sEmpID); empAttenInfo = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return empAttenInfo; } internal static DataSet GetEmpCardInfoPerPage(TransactionContext tc, string sEmpID) { DataSet empAttenInfo = new DataSet(); try { string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS IDNo, e.NAME,e.JOININGDATE AS DOJ, IsNULL(e.[PhotoPath],'') AS EmpPhoto, IsNULL(des.NAME,'') AS Desig, IsNULL(gd.DESCRIPTION,'') AS Grade, IsNULL(gd.COLORCODE,'White') AS GradeColor, IsNULL(dept.DESCRIPTION,'') AS Dept, IsNULL(acrd.CardNumber,'') AS CardNo, CASE WHEN dept.TIRE = 4 THEN dept.DESCRIPTION WHEN dept.TIRE = 5 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID) ELSE '' END Section, CASE WHEN e.BLOODGROUP =1 THEN 'NONE' WHEN e.BLOODGROUP =2 THEN 'A+' WHEN e.BLOODGROUP =3 THEN 'A-' WHEN e.BLOODGROUP =4 THEN 'B+' WHEN e.BLOODGROUP =5 THEN 'B-' WHEN e.BLOODGROUP =6 THEN 'O+' WHEN e.BLOODGROUP =7 THEN 'O-' WHEN e.BLOODGROUP =8 THEN 'AB+' WHEN e.BLOODGROUP =9 THEN 'AB-' WHEN e.BLOODGROUP =10 THEN 'NA' ELSE '' END BG, IsNULL(CardOpp.AssignDate,'') AS IssueDate, CASE WHEN dept.TIRE = 3 THEN dept.DESCRIPTION WHEN dept.TIRE = 4 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID) ELSE '' END AS EmpType, ISNULL(e.NationalID,'') AS NationalID, IsNULL(cts.ADDVill,'') ADDVill, IsNULL(cts.ADDDist,'') ADDDist, IsNULL(cts.ADDPO,'') ADDPO, IsNULL(cts.ADDPS,'') ADDPS, IsNULL(cts.ADDPhone,'') ADDPhone FROM (SELECT * FROM EMPLOYEE WHERE EMPLOYEEID IN (%q)) e LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID LEFT JOIN GRADES gd ON e.GRADEID = gd.GRADEID LEFT JOIN GRADESEGMENT gds ON gd.GRADESEGMENTID = gds.GRADESEGMENTID LEFT JOIN DEPARTMENT dept ON e.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN AccessCard acrd ON e.CardID = acrd.AccessCardID LEFT JOIN (SELECT EmployeeID,CardID,MAX(AssignDate) AS AssignDate FROM CardOperation GROUP BY EmployeeID,CARDID) CardOpp ON CardOpp.EmployeeID = e.EmployeeID AND CardOpp.CardID = e.CardID LEFT JOIN (SELECT EmployeeID,ParmanentAddress AS ADDVill, ISNULL(dis.NAME,'') ADDDist, '' AS ADDPO, ISNULL(thn.NAME,'') ADDPS, ParmanentMobile AS ADDPhone FROM EMPCONTACT ec LEFT JOIN District dis ON ec.PARMANENTDISTRICTID = dis.DISTRICTID LEFT JOIN THANA thn ON ec.PARMANENTTHANAID = thn.THANAID) cts ON e.EmployeeID = cts.EmployeeID", sEmpID); empAttenInfo = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return empAttenInfo; } internal static DataSet GetHolidays(TransactionContext tc, DateTime attnDate, ID empID) { DataSet holidays = new DataSet(); try { string sql = SQLParser.MakeSQL("select count(EmployeeID) as Holidays from DailyAttnProcess " + " where EmployeeID=%n and AttnDate between %d And %d and AttnType IN(%n,%n) ", empID.Integer, GlobalFunctions.FirstDateOfMonth(attnDate), GlobalFunctions.LastDateOfMonth(attnDate), EnumAttendanceType.Holiday, EnumAttendanceType.WeeklyHoliday); holidays = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return holidays; } internal static DataSet GetPresentDays(TransactionContext tc, DateTime attnDate, ID empID) { DataSet presentDays = new DataSet(); try { string sql = SQLParser.MakeSQL("select count(EmployeeID) as PresentDays from DailyAttnProcess " + " where EmployeeID=%n and AttnDate between %d And %d and AttnType In(1,11) ", empID.Integer, GlobalFunctions.FirstDateOfMonth(attnDate), GlobalFunctions.LastDateOfMonth(attnDate)); presentDays = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return presentDays; } internal static IDataReader GetLastProcessDate(TransactionContext tc) { try { string sql = SQLParser.MakeSQL("SELECT MAX(AttnDate) as LastProcessDate FROM DailyAttnProcess WHERE IsManualEntry=0"); return tc.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } } internal static IDataReader GetLastProcessDateByPayrollType(TransactionContext tc, ID PayrolltypeID) { try { string sql = SQLParser.MakeSQL(@"SELECT MAX(AttnDate) as LastProcessDate FROM DailyAttnProcess dap, Employee e WHERE dap.IsManualEntry = 0 AND dap.EmployeeID = e.EmployeeID AND e.PayrollTypeID = %n", PayrolltypeID.Integer); return tc.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } } public static IDataReader GetDailyEmployeeAbsent(TransactionContext tc, DateTime dateTime, ID nID) { try { string sql = SQLParser.MakeSQL("Select * from DailyAttnProcess where EmployeeID=%n and AttnDate=%d and AttnType=%n", nID.Integer, dateTime, EnumAttendanceType.Absent); return tc.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } } internal static IDataReader GetEmployeesFirstAttendances(TransactionContext tc, string employeeIDs) { try { string sql = SQLParser.MakeSQL(@"select d.* from DailyAttnProcess d, (SELECT MIN(AttnDate) as AttnDate ,[EmployeeID] FROM DailyAttnProcess WHERE EmployeeID IN(%q) and attnType=%n group by EmployeeID )temp WHERE temp.EmployeeID = d.EmployeeID AND temp.AttnDate = d.AttnDate order by d.EmployeeID", employeeIDs, EnumAttendanceType.Present); return tc.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } } internal static IDataReader Get(TransactionContext tc, ID empID, ID shiftID, DateTime fromdate, DateTime todate, EnumAttendanceType enumAttendanceType) { try { string sql = SQLParser.MakeSQL("Select * from DailyAttnProcess where EmployeeID=%n AND ShiftID=%n AND AttnDate BETWEEN %d AND %d AND AttnType=%n", empID.Integer, shiftID.Integer, fromdate, todate, enumAttendanceType); return tc.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } } #endregion #region Delete function internal static void Delete(TransactionContext tc, ID nID) { tc.ExecuteNonQuery("DELETE FROM [DailyAttnProcess] WHERE DailyAttnProcessID=%n", nID.Integer); } internal static void Delete(TransactionContext tc, ID empID, DateTime attnDate) { tc.ExecuteNonQuery("DELETE FROM [DailyAttnProcess] WHERE EmployeeID=%n AND AttnDate=%d", empID.Integer, attnDate); } internal static void Deletewithoutmanulentry(TransactionContext tc, DateTime attnDate) { tc.ExecuteNonQuery("DELETE FROM DailyAttnProcess WHERE AttnDate=%d AND IsManualEntry=%b", attnDate, false); } #endregion internal static bool IsExist(TransactionContext tc, ID empID, DateTime attnDate) { bool Exist = false; Object obj = tc.ExecuteScalar("Select * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID.Integer, attnDate); Exist = Convert.ToInt32(obj) > 0 ? true : false; return Exist; } } #endregion }