using Google.Type; using Org.BouncyCastle.Crypto; using System; using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.YearlyCalendar { public static class YearlyCalendarQB { public const string GET_YEARLYCALENDAR = "EXEC SP_employeeCalendar @employeeid, @fromdate, @todate"; // public const string GET_YEARLYCALENDAR = @" //;WITH cte AS ( // SELECT // dt.d AS attendancedate, // CASE // WHEN ISNULL(a.dailyattendanceid, -1) = -1 OR a.attendancedate >= GETDATE() // THEN // CASE // WHEN m.detailtype = 0 THEN 9 -- HOLIDAY // WHEN e.weekoffday = DATEPART(weekday, dt.d) THEN 2 -- WEEKLYOFF // ELSE 3 -- OTHERS // END // ELSE // CASE // WHEN m.detailtype = 0 THEN 9 -- HOLIDAY // WHEN e.weekoffday = DATEPART(weekday, dt.d) THEN 2 -- WEEKLYOFF // ELSE a.daytype -- Actual stored value // END // END AS daytype, // ISNULL(a.LEAVEID, -1) AS leaveid // FROM DateTally dt // JOIN memployee e // ON e.employeeid = @employeeid // LEFT JOIN tdailyattendance a // ON a.Employeeid = e.EMPLOYEEID // AND dt.d = a.attendancedate // LEFT JOIN morganizationunit ou // ON e.workouid = ou.ouid // LEFT JOIN MCALENDARDETAIL m // ON ( // (e.CALENDARID = m.CALENDARID AND e.calendarid <> -1) // OR (ou.calendarid = m.calendarid AND e.calendarid = -1) // ) // AND dt.d = m.detaildate // WHERE dt.d >= @fromdate // AND dt.d <= @todate //) //SELECT // YEAR(a.attendancedate) AS Year, // MONTH(a.attendancedate) AS MonthNumber, // LEFT(DATENAME(month, a.attendancedate), 3) AS Month, // MAX(CASE WHEN DAY(a.attendancedate) = 1 THEN a.daytype ELSE NULL END) AS D1, // MAX(CASE WHEN DAY(a.attendancedate) = 2 THEN a.daytype ELSE NULL END) AS D2, // MAX(CASE WHEN DAY(a.attendancedate) = 3 THEN a.daytype ELSE NULL END) AS D3, // MAX(CASE WHEN DAY(a.attendancedate) = 4 THEN a.daytype ELSE NULL END) AS D4, // MAX(CASE WHEN DAY(a.attendancedate) = 5 THEN a.daytype ELSE NULL END) AS D5, // MAX(CASE WHEN DAY(a.attendancedate) = 6 THEN a.daytype ELSE NULL END) AS D6, // MAX(CASE WHEN DAY(a.attendancedate) = 7 THEN a.daytype ELSE NULL END) AS D7, // MAX(CASE WHEN DAY(a.attendancedate) = 8 THEN a.daytype ELSE NULL END) AS D8, // MAX(CASE WHEN DAY(a.attendancedate) = 9 THEN a.daytype ELSE NULL END) AS D9, // MAX(CASE WHEN DAY(a.attendancedate) = 10 THEN a.daytype ELSE NULL END) AS D10, // MAX(CASE WHEN DAY(a.attendancedate) = 11 THEN a.daytype ELSE NULL END) AS D11, // MAX(CASE WHEN DAY(a.attendancedate) = 12 THEN a.daytype ELSE NULL END) AS D12, // MAX(CASE WHEN DAY(a.attendancedate) = 13 THEN a.daytype ELSE NULL END) AS D13, // MAX(CASE WHEN DAY(a.attendancedate) = 14 THEN a.daytype ELSE NULL END) AS D14, // MAX(CASE WHEN DAY(a.attendancedate) = 15 THEN a.daytype ELSE NULL END) AS D15, // MAX(CASE WHEN DAY(a.attendancedate) = 16 THEN a.daytype ELSE NULL END) AS D16, // MAX(CASE WHEN DAY(a.attendancedate) = 17 THEN a.daytype ELSE NULL END) AS D17, // MAX(CASE WHEN DAY(a.attendancedate) = 18 THEN a.daytype ELSE NULL END) AS D18, // MAX(CASE WHEN DAY(a.attendancedate) = 19 THEN a.daytype ELSE NULL END) AS D19, // MAX(CASE WHEN DAY(a.attendancedate) = 20 THEN a.daytype ELSE NULL END) AS D20, // MAX(CASE WHEN DAY(a.attendancedate) = 21 THEN a.daytype ELSE NULL END) AS D21, // MAX(CASE WHEN DAY(a.attendancedate) = 22 THEN a.daytype ELSE NULL END) AS D22, // MAX(CASE WHEN DAY(a.attendancedate) = 23 THEN a.daytype ELSE NULL END) AS D23, // MAX(CASE WHEN DAY(a.attendancedate) = 24 THEN a.daytype ELSE NULL END) AS D24, // MAX(CASE WHEN DAY(a.attendancedate) = 25 THEN a.daytype ELSE NULL END) AS D25, // MAX(CASE WHEN DAY(a.attendancedate) = 26 THEN a.daytype ELSE NULL END) AS D26, // MAX(CASE WHEN DAY(a.attendancedate) = 27 THEN a.daytype ELSE NULL END) AS D27, // MAX(CASE WHEN DAY(a.attendancedate) = 28 THEN a.daytype ELSE NULL END) AS D28, // MAX(CASE WHEN DAY(a.attendancedate) = 29 THEN a.daytype ELSE NULL END) AS D29, // MAX(CASE WHEN DAY(a.attendancedate) = 30 THEN a.daytype ELSE NULL END) AS D30, // MAX(CASE WHEN DAY(a.attendancedate) = 31 THEN a.daytype ELSE NULL END) AS D31, // MAX(CASE WHEN DAY(a.attendancedate) = 1 THEN a.leaveid ELSE NULL END) AS LeaveId1, // MAX(CASE WHEN DAY(a.attendancedate) = 2 THEN a.leaveid ELSE NULL END) AS LeaveId2, // MAX(CASE WHEN DAY(a.attendancedate) = 3 THEN a.leaveid ELSE NULL END) AS LeaveId3, // MAX(CASE WHEN DAY(a.attendancedate) = 4 THEN a.leaveid ELSE NULL END) AS LeaveId4, // MAX(CASE WHEN DAY(a.attendancedate) = 5 THEN a.leaveid ELSE NULL END) AS LeaveId5, // MAX(CASE WHEN DAY(a.attendancedate) = 6 THEN a.leaveid ELSE NULL END) AS LeaveId6, // MAX(CASE WHEN DAY(a.attendancedate) = 7 THEN a.leaveid ELSE NULL END) AS LeaveId7, // MAX(CASE WHEN DAY(a.attendancedate) = 8 THEN a.leaveid ELSE NULL END) AS LeaveId8, // MAX(CASE WHEN DAY(a.attendancedate) = 9 THEN a.leaveid ELSE NULL END) AS LeaveId9, // MAX(CASE WHEN DAY(a.attendancedate) = 10 THEN a.leaveid ELSE NULL END) AS LeaveId10, // MAX(CASE WHEN DAY(a.attendancedate) = 11 THEN a.leaveid ELSE NULL END) AS LeaveId11, // MAX(CASE WHEN DAY(a.attendancedate) = 12 THEN a.leaveid ELSE NULL END) AS LeaveId12, // MAX(CASE WHEN DAY(a.attendancedate) = 13 THEN a.leaveid ELSE NULL END) AS LeaveId13, // MAX(CASE WHEN DAY(a.attendancedate) = 14 THEN a.leaveid ELSE NULL END) AS LeaveId14, // MAX(CASE WHEN DAY(a.attendancedate) = 15 THEN a.leaveid ELSE NULL END) AS LeaveId15, // MAX(CASE WHEN DAY(a.attendancedate) = 16 THEN a.leaveid ELSE NULL END) AS LeaveId16, // MAX(CASE WHEN DAY(a.attendancedate) = 17 THEN a.leaveid ELSE NULL END) AS LeaveId17, // MAX(CASE WHEN DAY(a.attendancedate) = 18 THEN a.leaveid ELSE NULL END) AS LeaveId18, // MAX(CASE WHEN DAY(a.attendancedate) = 19 THEN a.leaveid ELSE NULL END) AS LeaveId19, // MAX(CASE WHEN DAY(a.attendancedate) = 20 THEN a.leaveid ELSE NULL END) AS LeaveId20, // MAX(CASE WHEN DAY(a.attendancedate) = 21 THEN a.leaveid ELSE NULL END) AS LeaveId21, // MAX(CASE WHEN DAY(a.attendancedate) = 22 THEN a.leaveid ELSE NULL END) AS LeaveId22, // MAX(CASE WHEN DAY(a.attendancedate) = 23 THEN a.leaveid ELSE NULL END) AS LeaveId23, // MAX(CASE WHEN DAY(a.attendancedate) = 24 THEN a.leaveid ELSE NULL END) AS LeaveId24, // MAX(CASE WHEN DAY(a.attendancedate) = 25 THEN a.leaveid ELSE NULL END) AS LeaveId25, // MAX(CASE WHEN DAY(a.attendancedate) = 26 THEN a.leaveid ELSE NULL END) AS LeaveId26, // MAX(CASE WHEN DAY(a.attendancedate) = 27 THEN a.leaveid ELSE NULL END) AS LeaveId27, // MAX(CASE WHEN DAY(a.attendancedate) = 28 THEN a.leaveid ELSE NULL END) AS LeaveId28, // MAX(CASE WHEN DAY(a.attendancedate) = 29 THEN a.leaveid ELSE NULL END) AS LeaveId29, // MAX(CASE WHEN DAY(a.attendancedate) = 30 THEN a.leaveid ELSE NULL END) AS LeaveId30, // MAX(CASE WHEN DAY(a.attendancedate) = 31 THEN a.leaveid ELSE NULL END) AS LeaveId31, // MAX(CASE WHEN DAY(a.attendancedate) = 1 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode1, // MAX(CASE WHEN DAY(a.attendancedate) = 2 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode2, // MAX(CASE WHEN DAY(a.attendancedate) = 3 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode3, // MAX(CASE WHEN DAY(a.attendancedate) = 4 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode4, // MAX(CASE WHEN DAY(a.attendancedate) = 5 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode5, // MAX(CASE WHEN DAY(a.attendancedate) = 6 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode6, // MAX(CASE WHEN DAY(a.attendancedate) = 7 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode7, // MAX(CASE WHEN DAY(a.attendancedate) = 8 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode8, // MAX(CASE WHEN DAY(a.attendancedate) = 9 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode9, // MAX(CASE WHEN DAY(a.attendancedate) = 10 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode10, // MAX(CASE WHEN DAY(a.attendancedate) = 11 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode11, // MAX(CASE WHEN DAY(a.attendancedate) = 12 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode12, // MAX(CASE WHEN DAY(a.attendancedate) = 13 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode13, // MAX(CASE WHEN DAY(a.attendancedate) = 14 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode14, // MAX(CASE WHEN DAY(a.attendancedate) = 15 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode15, // MAX(CASE WHEN DAY(a.attendancedate) = 16 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode16, // MAX(CASE WHEN DAY(a.attendancedate) = 17 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode17, // MAX(CASE WHEN DAY(a.attendancedate) = 18 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode18, // MAX(CASE WHEN DAY(a.attendancedate) = 19 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode19, // MAX(CASE WHEN DAY(a.attendancedate) = 20 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode20, // MAX(CASE WHEN DAY(a.attendancedate) = 21 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode21, // MAX(CASE WHEN DAY(a.attendancedate) = 22 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode22, // MAX(CASE WHEN DAY(a.attendancedate) = 23 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode23, // MAX(CASE WHEN DAY(a.attendancedate) = 24 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode24, // MAX(CASE WHEN DAY(a.attendancedate) = 25 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode25, // MAX(CASE WHEN DAY(a.attendancedate) = 26 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode26, // MAX(CASE WHEN DAY(a.attendancedate) = 27 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode27, // MAX(CASE WHEN DAY(a.attendancedate) = 28 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode28, // MAX(CASE WHEN DAY(a.attendancedate) = 29 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode29, // MAX(CASE WHEN DAY(a.attendancedate) = 30 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode30, // MAX(CASE WHEN DAY(a.attendancedate) = 31 THEN l.LEAVECODE ELSE NULL END) AS LeaveCode31, // MAX(CASE WHEN DAY(a.attendancedate) = 1 THEN l.LEAVENAME ELSE NULL END) AS LeaveName1, // MAX(CASE WHEN DAY(a.attendancedate) = 2 THEN l.LEAVENAME ELSE NULL END) AS LeaveName2, // MAX(CASE WHEN DAY(a.attendancedate) = 3 THEN l.LEAVENAME ELSE NULL END) AS LeaveName3, // MAX(CASE WHEN DAY(a.attendancedate) = 4 THEN l.LEAVENAME ELSE NULL END) AS LeaveName4, // MAX(CASE WHEN DAY(a.attendancedate) = 5 THEN l.LEAVENAME ELSE NULL END) AS LeaveName5, // MAX(CASE WHEN DAY(a.attendancedate) = 6 THEN l.LEAVENAME ELSE NULL END) AS LeaveName6, // MAX(CASE WHEN DAY(a.attendancedate) = 7 THEN l.LEAVENAME ELSE NULL END) AS LeaveName7, // MAX(CASE WHEN DAY(a.attendancedate) = 8 THEN l.LEAVENAME ELSE NULL END) AS LeaveName8, // MAX(CASE WHEN DAY(a.attendancedate) = 9 THEN l.LEAVENAME ELSE NULL END) AS LeaveName9, // MAX(CASE WHEN DAY(a.attendancedate) = 10 THEN l.LEAVENAME ELSE NULL END) AS LeaveName10, // MAX(CASE WHEN DAY(a.attendancedate) = 11 THEN l.LEAVENAME ELSE NULL END) AS LeaveName11, // MAX(CASE WHEN DAY(a.attendancedate) = 12 THEN l.LEAVENAME ELSE NULL END) AS LeaveName12, // MAX(CASE WHEN DAY(a.attendancedate) = 13 THEN l.LEAVENAME ELSE NULL END) AS LeaveName13, // MAX(CASE WHEN DAY(a.attendancedate) = 14 THEN l.LEAVENAME ELSE NULL END) AS LeaveName14, // MAX(CASE WHEN DAY(a.attendancedate) = 15 THEN l.LEAVENAME ELSE NULL END) AS LeaveName15, // MAX(CASE WHEN DAY(a.attendancedate) = 16 THEN l.LEAVENAME ELSE NULL END) AS LeaveName16, // MAX(CASE WHEN DAY(a.attendancedate) = 17 THEN l.LEAVENAME ELSE NULL END) AS LeaveName17, // MAX(CASE WHEN DAY(a.attendancedate) = 18 THEN l.LEAVENAME ELSE NULL END) AS LeaveName18, // MAX(CASE WHEN DAY(a.attendancedate) = 19 THEN l.LEAVENAME ELSE NULL END) AS LeaveName19, // MAX(CASE WHEN DAY(a.attendancedate) = 20 THEN l.LEAVENAME ELSE NULL END) AS LeaveName20, // MAX(CASE WHEN DAY(a.attendancedate) = 21 THEN l.LEAVENAME ELSE NULL END) AS LeaveName21, // MAX(CASE WHEN DAY(a.attendancedate) = 22 THEN l.LEAVENAME ELSE NULL END) AS LeaveName22, // MAX(CASE WHEN DAY(a.attendancedate) = 23 THEN l.LEAVENAME ELSE NULL END) AS LeaveName23, // MAX(CASE WHEN DAY(a.attendancedate) = 24 THEN l.LEAVENAME ELSE NULL END) AS LeaveName24, // MAX(CASE WHEN DAY(a.attendancedate) = 25 THEN l.LEAVENAME ELSE NULL END) AS LeaveName25, // MAX(CASE WHEN DAY(a.attendancedate) = 26 THEN l.LEAVENAME ELSE NULL END) AS LeaveName26, // MAX(CASE WHEN DAY(a.attendancedate) = 27 THEN l.LEAVENAME ELSE NULL END) AS LeaveName27, // MAX(CASE WHEN DAY(a.attendancedate) = 28 THEN l.LEAVENAME ELSE NULL END) AS LeaveName28, // MAX(CASE WHEN DAY(a.attendancedate) = 29 THEN l.LEAVENAME ELSE NULL END) AS LeaveName29, // MAX(CASE WHEN DAY(a.attendancedate) = 30 THEN l.LEAVENAME ELSE NULL END) AS LeaveName30, // MAX(CASE WHEN DAY(a.attendancedate) = 31 THEN l.LEAVENAME ELSE NULL END) AS LeaveName31 //FROM cte a //LEFT JOIN MLEAVE l // ON a.leaveid = l.leaveid // AND a.leaveid <> -1 //GROUP BY // YEAR(a.attendancedate), // MONTH(a.attendancedate), // LEFT(DATENAME(month, a.attendancedate), 3) //ORDER BY // YEAR(a.attendancedate), // MONTH(a.attendancedate) //"; } } //public const string GET_YEARLYCALENDAR = @" //SELECT // YEAR(attendancedate) AS Year, // MONTH(attendancedate) AS MonthNumber, // LEFT(DATENAME(month, a.attendancedate), 3) AS Month, // MAX(CASE WHEN DAY(a.attendancedate) = 1 THEN daytype ELSE NULL END) AS d1, // MAX(CASE WHEN DAY(a.attendancedate) = 2 THEN daytype ELSE NULL END) AS d2, // MAX(CASE WHEN DAY(a.attendancedate) = 3 THEN daytype ELSE NULL END) AS d3, // MAX(CASE WHEN DAY(a.attendancedate) = 4 THEN daytype ELSE NULL END) AS d4, // MAX(CASE WHEN DAY(a.attendancedate) = 5 THEN daytype ELSE NULL END) AS d5, // MAX(CASE WHEN DAY(a.attendancedate) = 6 THEN daytype ELSE NULL END) AS d6, // MAX(CASE WHEN DAY(a.attendancedate) = 7 THEN daytype ELSE NULL END) AS d7, // MAX(CASE WHEN DAY(a.attendancedate) = 8 THEN daytype ELSE NULL END) AS d8, // MAX(CASE WHEN DAY(a.attendancedate) = 9 THEN daytype ELSE NULL END) AS d9, // MAX(CASE WHEN DAY(a.attendancedate) = 10 THEN daytype ELSE NULL END) AS d10, // MAX(CASE WHEN DAY(a.attendancedate) = 11 THEN daytype ELSE NULL END) AS d11, // MAX(CASE WHEN DAY(a.attendancedate) = 12 THEN daytype ELSE NULL END) AS d12, // MAX(CASE WHEN DAY(a.attendancedate) = 13 THEN daytype ELSE NULL END) AS d13, // MAX(CASE WHEN DAY(a.attendancedate) = 14 THEN daytype ELSE NULL END) AS d14, // MAX(CASE WHEN DAY(a.attendancedate) = 15 THEN daytype ELSE NULL END) AS d15, // MAX(CASE WHEN DAY(a.attendancedate) = 16 THEN daytype ELSE NULL END) AS d16, // MAX(CASE WHEN DAY(a.attendancedate) = 17 THEN daytype ELSE NULL END) AS d17, // MAX(CASE WHEN DAY(a.attendancedate) = 18 THEN daytype ELSE NULL END) AS d18, // MAX(CASE WHEN DAY(a.attendancedate) = 19 THEN daytype ELSE NULL END) AS d19, // MAX(CASE WHEN DAY(a.attendancedate) = 20 THEN daytype ELSE NULL END) AS d20, // MAX(CASE WHEN DAY(a.attendancedate) = 21 THEN daytype ELSE NULL END) AS d21, // MAX(CASE WHEN DAY(a.attendancedate) = 22 THEN daytype ELSE NULL END) AS d22, // MAX(CASE WHEN DAY(a.attendancedate) = 23 THEN daytype ELSE NULL END) AS d23, // MAX(CASE WHEN DAY(a.attendancedate) = 24 THEN daytype ELSE NULL END) AS d24, // MAX(CASE WHEN DAY(a.attendancedate) = 25 THEN daytype ELSE NULL END) AS d25, // MAX(CASE WHEN DAY(a.attendancedate) = 26 THEN daytype ELSE NULL END) AS d26, // MAX(CASE WHEN DAY(a.attendancedate) = 27 THEN daytype ELSE NULL END) AS d27, // MAX(CASE WHEN DAY(a.attendancedate) = 28 THEN daytype ELSE NULL END) AS d28, // MAX(CASE WHEN DAY(a.attendancedate) = 29 THEN daytype ELSE NULL END) AS d29, // MAX(CASE WHEN DAY(a.attendancedate) = 30 THEN daytype ELSE NULL END) AS d30, // MAX(CASE WHEN DAY(a.attendancedate) = 31 THEN daytype ELSE NULL END) AS d31 //FROM // tdailyattendance a //WHERE // a.Employeeid = @employeeid // AND a.attendancedate >= @fromdate // AND a.attendancedate <= @todate //GROUP BY // YEAR(attendancedate), // MONTH(attendancedate), // LEFT(DATENAME(month, a.attendancedate), 3) //ORDER BY // YEAR(attendancedate), // MONTH(attendancedate) //";