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);
}
}
}
