Ejemplo n.º 1
0
 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];
 }
Ejemplo n.º 2
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];
        }