public DataTable GetAllBDEmployeewiseRecord(object param)
{
Malconn conn = new Malconn();
string sql = "SELECT Employee_Info.EmpID AS EMPID, Employee_Info.Ename AS EName from MBDPayroll.dbo.Employee_Info WHERE MBDPayroll.dbo.Employee_Info.EmpID NOT IN (SELECT Terminate_Resign.EmpID FROM MBDPayroll.dbo.Terminate_Resign)";
sql = sql + " ORDER BY MBDPayroll.dbo.Employee_Info.EmpID ASC";
DataSet ds = new DataSet();
ds = conn.ExecuteSelectSQL(sql, null, false);
return ds.Tables[0];
}
public DataTable GetBDDeptRecord(object param)
{
Malconn conn = new Malconn();
//Database db = DatabaseFactory.CreateDatabase();
BDEmployeeEntity objAtten = (BDEmployeeEntity)param;
//string sql = "select CONVERT(DATE, MBDPayroll.dbo.Attendance.Atten_Date,103) AS PDate";
string sql = "select ( CAST(DATEPART(YY, MBDPayroll.dbo.Attendance.Atten_Date) AS VARCHAR(10))+ LEFT('-' + CAST(DATEPART(mm, MBDPayroll.dbo.Attendance.Atten_Date) AS VARCHAR(10)), 4) + LEFT('-' + CAST(DATEPART(dd, MBDPayroll.dbo.Attendance.Atten_Date) AS VARCHAR(10)), 4)) AS PDate";
sql=sql+" , MBDPayroll.dbo.Attendance.Emp_Id as EMPID, MBDPayroll.dbo.Employee_Info.Ename AS EName, MBDPayroll.dbo.Employee_Info.Designation AS Designation, MBDPayroll.dbo.Employee_Info.SECTION AS DeptName, (substring(MBDPayroll.dbo.Attendance.IN_time,1,2)+'.'+substring(MBDPayroll.dbo.Attendance.IN_time,3,4)) AS Intime, (substring(MBDPayroll.dbo.Attendance.Out_Time,1,2)+'.'+substring(MBDPayroll.dbo.Attendance.Out_Time,3,4)) AS Outtime, MBDPayroll.dbo.Attendance.Present_Absent as Status from MBDPayroll.dbo.Attendance, MBDPayroll.dbo.Employee_Info";
sql = sql + " Where MBDPayroll.dbo.Attendance.Emp_Id=MBDPayroll.dbo.Employee_Info.EmpID";
sql = sql + " AND MBDPayroll.dbo.Employee_Info.EmpID NOT IN (SELECT MBDPayroll.dbo.Terminate_Resign.EmpID FROM MBDPayroll.dbo.Terminate_Resign)";
sql = sql + " AND MBDPayroll.dbo.Attendance.Atten_Date BETWEEN convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103) AND MBDPayroll.dbo.Employee_Info.SECTION= '" + objAtten.DeptName + "'";
sql = sql + " ORDER BY CONVERT(DATE,MBDPayroll.dbo.Attendance.Atten_Date,103) ASC";
DataSet ds = new DataSet();
ds = conn.ExecuteSelectSQL(sql, null, false);
//DbCommand dbCommand = db.GetSqlStringCommand(sql);
//DataSet ds = db.ExecuteDataSet(dbCommand);
//return conn.ExecuteQueryData(sql);
return ds.Tables[0];
}
public DataTable GetBDEmployeedaycalRecord(object param)
{
Malconn conn = new Malconn();
//Database db = DatabaseFactory.CreateDatabase();
BDEmployeedaycal objAtten = (BDEmployeedaycal)param;
string sql = "select EmpID as EMPID, Ename as ENAME, Section as SECTION, CONVERT(DATE,J_Date,101) as JDate, Status as Status ";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as TTDay";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='H' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as Holiday";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent in('P','LA') and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as Present";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='A' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as Absent";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='CL' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as CL";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='SL' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as SL";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='ML' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as ML";
sql = sql + " ,(Select count(Present_Absent) FROM [MBDPayroll].[dbo].[Attendance] where Emp_Id = [MBDPayroll].dbo.Employee_Info.EmpID and Present_Absent='EL' and Atten_Date between convert(date,'" + objAtten.StartDate + "',103) AND convert(date,'" + objAtten.EndDate + "',103)) as EL";
sql = sql + " FROM [MBDPayroll].[dbo].[Employee_Info] where EmpID not in";
sql = sql + " (select EmpID from [MBDPayroll].dbo.Terminate_Resign ) "; //Not like 'w%' Means North Tower, Like means Welform
sql = sql + " order by [MBDPayroll].[dbo].[Employee_Info].Section";
DataSet ds = new DataSet();
ds = conn.ExecuteSelectSQL(sql, null, false);
return ds.Tables[0];
}