Example #1
0
        public List <leavereport> getLeaveRemain(string empid, string leaveid)
        {
            leavereport        lvapp;
            List <leavereport> LVlist = new List <leavereport>();

            try
            {
                SqlConnection conn  = new SqlConnection(Login.connString);
                string        query = "select  DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,d.OfficeID,d.OfficeName,a.LeaveID " +
                                      "from LeaveRequest a, (select b.UserID,b.Name  from ViewUserEmployeeList b where b.EmployeeID = '" + empid + "') c, ViewEmployeeDetails d, ERPUser e " +
                                      "where a.UserID = c.UserID and a.LeaveID in ('" + leaveid + "') and a.UserID=e.UserID and d.EmployeeID=e.EmployeeID and a.Status = 1 " +
                                      " and a.DocumentStatus = 99 and a.LeaveRequestStatus in (1,11,10,9,7) and year(a.SanctionedFromDate)=year(GetDate()) ";
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lvapp = new leavereport();
                    lvapp.leavepending = reader.IsDBNull(0)? '0' : reader.GetInt32(0);
                    lvapp.EmployeeID   = reader.GetString(1);
                    lvapp.EmployeeName = reader.GetString(2);
                    lvapp.officeid     = reader.GetString(3);
                    lvapp.officename   = reader.GetString(4);
                    lvapp.leaveid      = reader.GetString(5);
                    LVlist.Add(lvapp);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
            }
            return(LVlist);
        }
Example #2
0
        public List <leavereport> getleavetype()
        {
            leavereport        lvpr;
            List <leavereport> LeaveApr = new List <leavereport>();

            try
            {
                SqlConnection conn  = new SqlConnection(Login.connString);
                string        query = "select distinct LeaveID from LeaveRequest where datepart(YEAR,SanctionedFromDate)=YEAR(GETDATE())";
                SqlCommand    cmd   = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lvpr         = new leavereport();
                    lvpr.leaveid = reader.GetString(0);
                    LeaveApr.Add(lvpr);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
            }
            return(LeaveApr);
        }
Example #3
0
        public List <leavereport> getLeaveRemain2(int Opt, string Region, string Officeid)
        {
            leavereport        lvapp;
            List <leavereport> LVlist = new List <leavereport>();

            try
            {
                int           inttemp = 0;
                DateTime      dttemp  = DateTime.Now;
                SqlConnection conn    = new SqlConnection(Login.connString);
                string        query   = "";
                if (Opt == 2)
                {
                    query = "select  DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d  FULL OUTER JOIN ERPUser e ON  e.EmployeeID = d.EmployeeID  FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1 and   d.OfficeID = '" + Officeid + "'";
                }
                else if (Opt == 3)
                {
                    query = "select  DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d  FULL OUTER JOIN ERPUser e ON  e.EmployeeID = d.EmployeeID  FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1 and  d.RegionID = '" + Region + "'";
                }
                else
                {
                    query = "select  DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d  FULL OUTER JOIN ERPUser e ON  e.EmployeeID = d.EmployeeID  FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1";
                }
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lvapp = new leavereport();
                    lvapp.leavepending   = reader.IsDBNull(0) ? inttemp : reader.GetInt32(0);
                    lvapp.EmployeeID     = reader.GetString(1);
                    lvapp.EmployeeName   = reader.GetString(2);
                    lvapp.officeid       = reader.IsDBNull(5) ? " " : reader.GetString(5);
                    lvapp.officename     = reader.IsDBNull(4) ? "" : reader.GetString(4);
                    lvapp.leaveid        = reader.IsDBNull(3) ? " ": reader.GetString(3);
                    lvapp.sanctionedFrom = reader.IsDBNull(6) ? dttemp : reader.GetDateTime(6);
                    lvapp.sanctionedTo   = reader.IsDBNull(7) ? dttemp : reader.GetDateTime(7);
                    lvapp.status         = reader.IsDBNull(8) ? inttemp : reader.GetInt32(8);
                    lvapp.documentStatus = reader.IsDBNull(9) ? inttemp : reader.GetInt32(9);
                    LVlist.Add(lvapp);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
            }
            return(LVlist);
        }
Example #4
0
        public List <leavereport> getFilteredLeaveApproval(string officeid, int opt, string region)
        {
            leavereport        lvpr;
            List <leavereport> LeaveApr = new List <leavereport>();

            try
            {
                SqlConnection conn   = new SqlConnection(Login.connString);
                string        query1 = "select a.EmployeeID,a.Name,a.OfficeID,a.OfficeName,b.PostingDate " +
                                       "from ViewEmployeePosting a ,(select c.EmployeeID,MAX(c.PostingDate) as PostingDate from ViewEmployeePosting c group by c.EmployeeID) b " +
                                       "where a.EmployeeID = b.EmployeeID and b.PostingDate = a.PostingDate ";

                string query2 = "select s1.EmployeeID, s1.Name,s1.OfficeID,s1.OfficeName," +
                                " s1.PostingDate from ViewEmployeePosting s1 inner join" +
                                "(select max(PostingDate) PostingDate,EmployeeID from ViewEmployeePosting " +
                                "group by EmployeeID) s2 on s1.EmployeeID = s2.EmployeeID and " +
                                "s1.PostingDate = s2.PostingDate where s1.OfficeID = '" + officeid + "' order by s1.EmployeeID";//part

                string query3 = "select s1.EmployeeID, s1.Name,s1.OfficeID,s1.OfficeName," +
                                " s1.PostingDate from ViewEmployeePosting s1 inner join" +
                                "(select max(PostingDate) PostingDate,EmployeeID from ViewEmployeePosting " +
                                "group by EmployeeID) s2 on s1.EmployeeID = s2.EmployeeID and " +
                                "s1.PostingDate = s2.PostingDate,office b where b.RegionID = '" + region + "'and b.OfficeID=s1.OfficeID order by s1.EmployeeID";//region-part and office all

                string query = "";
                switch (opt)
                {
                case 1:
                    query = query1;
                    break;

                case 2:
                    query = query2;
                    break;

                case 3:
                    query = query3;
                    break;

                default:
                    query = "";
                    break;
                }
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lvpr              = new leavereport();
                    lvpr.EmployeeID   = reader.GetString(0);
                    lvpr.EmployeeName = reader.GetString(1);
                    lvpr.officeid     = reader.GetString(2);
                    lvpr.officename   = reader.GetString(3);
                    LeaveApr.Add(lvpr);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
            }
            return(LeaveApr);
        }