public static string downloadOhListForHRMSUpload() { string ohList = ""; int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); try { var employeesOHQuery = "SELECT CONVERT(VARCHAR(10), cast(Split.a.value('.', 'NVARCHAR(MAX)')AS DATETIME), 121) [Date], 'Optional Holiday' Holiday" + " , MONTH(Split.a.value('.', 'NVARCHAR(MAX)')) Month, YEAR(Split.a.value('.', 'NVARCHAR(MAX)')) Year, RTRIM(LTRIM(A.emp_Code)) EmpCode, a.NAME AS EmployeeName" + " FROM(SELECT OL.emp_Code, CAST('<X>' + REPLACE([Holidays], ',', '</X><X>') + '</X>' AS XML) AS String, U.FirstName + ' ' + ISNULL(U.MiddleName, '') + ' ' + ISNULL(U.LastName, '') AS NAME" + " FROM[dbo].[Ohlog] OL" + " join [dbo].[User] U on U.EmployeeCode = OL.emp_Code where OL.Year = " + ohYear + " and U.IsActive = 1 " + " ) AS A" + " CROSS APPLY String.nodes('/X') AS Split(a)" + " order by A.NAME, Date"; DataTable dtEmployeesOH = BaseDAL.ExecuteDataTable(employeesOHQuery); ohList = DataTableToCSV(dtEmployeesOH, ','); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
public static string downloadEmployeeHolidays() { string ohList = ""; try { int noOfOptionalHolidays = Int32.Parse((ConfigurationManager.AppSettings["noOfOptionalHolidays"])); int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); var query = "select row_number() over (order by len(o.Holidays) - len(replace(o.Holidays, ',', '')) + 1 desc, u.UserName) as SerialNo,UPPER(u.FirstName) as FirstName,UPPER(u.LastName) as LastName, u.UserName as Email,u.EmployeeCode, len(o.Holidays) - len(replace(o.Holidays, ',', '')) + 1 as AppliedHolidayCount"; for (var i = 1; i <= noOfOptionalHolidays; i++) { query = query + "," + "dbo.CSVParser(o.Holidays," + i + ") as OH" + i; } query = query + " from[User] u left join OhLog o on u.EmployeeCode = o.emp_Code and o.Year = " + ohYear + " where u.IsActive = 1 order by AppliedHolidayCount desc, u.UserName"; DataTable dt1 = BaseDAL.ExecuteDataTable(query); ohList = DataTableToCSV(dt1, ','); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
public static string downloadEmployeeHolidaysDetail() { string ohList = ""; int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); try { var employeesQuery = "SELECT U.FirstName+' '+ISNULL(U.MiddleName,'')+' '+ISNULL(U.LastName,'') AS NAME, OH.Holidays, len(OH.Holidays) -len(replace(OH.Holidays,',',''))+ case when Holidays like '%NULL%' then 0 else 1 end as LeaveTotal" + " FROM [dbo].[User] U" + " LEFT JOIN [dbo].[OhLog] OH ON U.EmployeeCode = OH.emp_Code and OH.Year = " + ohYear + " where U.IsActive = 1 ORDER BY U.FirstName"; DataTable dtEmployees = BaseDAL.ExecuteDataTable(employeesQuery); var leavesPerDayQuery = "SELECT [Holiday], COUNT(*) AS [Count] FROM(SELECT Split.a.value('.', 'NVARCHAR(MAX)')[Holiday]" + " FROM(SELECT CAST('<X>' + REPLACE([Holidays], ',', '</X><X>') + '</X>' AS XML) AS String FROM Ohlog) AS A" + " CROSS APPLY String.nodes('/X') AS Split(a)) AS O" + " GROUP BY[Holiday]"; DataTable dtLeavesPerDay = BaseDAL.ExecuteDataTable(leavesPerDayQuery); ohList = DetailedDataTableToCSV(dtEmployees, dtLeavesPerDay); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
public static bool saveOptionalHolidays(string[] oh, string year) { var userId = HttpContext.Current.Session[Constants.SessionName.USERID]; string empQuery = string.Format("select EmployeeCode from [dbo].[User] where UserId='{0}'", userId); Object obj = BaseDAL.ExecuteScalar(empQuery); var empCode = obj.ToString(); var query = "Select * From dbo.OhLog Where emp_Code =" + empCode + " And Year =" + year; DataTable dt1 = BaseDAL.ExecuteDataTable(query); if (dt1.Rows.Count > 0) { //already entry for employee return(false); } else { try { var OhString = string.Join(",", oh); string query1 = string.Format("insert into [dbo].[OhLog]([Year],[emp_Code],[Holidays]) values('{0}','{1}','{2}')", year, empCode, OhString); int result = BaseDAL.ExecuteNonQuery(query1); if (result > 0) { return(true); } return(false); } catch (Exception ex) { return(false); } } }