コード例 #1
0
        public static double GetELBalance(string EmployeeID, string BPSScale, string JoiningDate, string MofAppointment)
        {
            string DutyPeriod = string.Empty, Year = String.Empty, Month = String.Empty, Days = String.Empty;
            double LeavesEarned = 0.0; double balance = 0.0;

            // for Consultants
            if ((MofAppointment == "T" || MofAppointment == "U"))
            {
                balance = 0;
            }
            else
            {
                strQuery = @"SELECT                                        
                    HR_LEAVEREQ.LEAVEID,                                                                   
                    convert(varchar,HR_LEAVEREQ.FROMDATE,103) AS FROMDATE,                        
                    convert(varchar,HR_LEAVEREQ.TODATE,103) AS TODATE,                                                     
                    dbo.INITCAP(HR_LEAVEREQ.LEAVETYPE) AS LEAVETYPE,                    
                    HR_LEAVEREQ.DUELEAVE,                    
                    HR_LEAVETYPE.ISEARNED,                    
                    HR_LEAVETYPE.ISDUTY                                                                          
                FROM                    
                    HR_LEAVETYPE  with (nolock),HR_LEAVEREQ  with (nolock)                    
                WHERE                          
                    HR_LEAVETYPE.DESCR = HR_LEAVEREQ.LEAVETYPE AND                         
                    HR_LEAVEREQ.LEAVETYPE <> 'CASUAL' AND                         
                    UPPER(HR_LEAVEREQ.STATUS) = 'APPROVED' AND                       
                    HR_LEAVEREQ.EMPLOYEEID  = '" + EmployeeID + @"'                     
                    ORDER BY Convert(date,TODATE,103) ASC";

                dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");

                DateTime DutyFrom = Convert.ToDateTime(JoiningDate); // DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                DateTime DutyTo   = DateTime.Now;                    // current date
                //DateDifference Age = null;
                DateTime RunningDutyTo     = System.DateTime.Now;    // current date
                DateTime NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", null);


                foreach (DataRow row in dsGrid.Tables["EMPLEAVEREQ"].Rows)
                {
                    string LeaveType = row["LEAVETYPE"].ToString().ToUpper();
                    DutyTo = DateTime.ParseExact(row["TODATE"].ToString(), "dd/MM/yyyy", null);

                    DateTime RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", null).AddDays(-1);
                    // RunningDutyTo = (RunningDutyTo > System.DateTime.Now ? System.DateTime.Now : RunningDutyTo);
                    RunningDutyTo = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? (DutyTo > System.DateTime.Now ? System.DateTime.Now : DutyTo) : RunningDutyFrom));

                    // update duty period for only duty leaves
                    // if (row["ISDUTY"].ToString().Equals("1"))
                    // {
                    // Calculating Duty Period
                    LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
                    balance     += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? (LeaveType.Equals("HALF AVERAGE PAY") ? Convert.ToDouble(row["DUELEAVE"].ToString()) / 2 : (LeaveType.Equals("ABSENT") ? Convert.ToDouble(row["DUELEAVE"].ToString()) * 2 : Convert.ToDouble(row["DUELEAVE"].ToString()))) : 0); // calculating new balance
                                                                                                                                                                                                                                                                                                                             // balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? Convert.ToDouble(row["DUELEAVE"].ToString()) : 0);  // calculating new balance
                                                                                                                                                                                                                                                                                                                             // }
                    DutyFrom = DutyTo.AddDays(1);
                }

                /************************ Creating Last Row and Calulating balance **********************/
                if (DutyFrom < DateTime.Now)
                {
                    LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, System.DateTime.Now); //Getting last Earned Leaves
                    balance     += LeavesEarned;                                                                   // Getting new Balance
                }

                /**********************************/
            }
            return(balance); // binding grid
        }
コード例 #2
0
        public static double GetELBalance(string EmployeeID, string BPSScale, string JoiningDate, string AppointmentMode, string TillDate)
        {
            strQuery = @"SELECT                                        
                    EMPLEAVEREQ.LEAVEID,                                                                   
                    Convert(varchar,EMPLEAVEREQ.FROMDATE,103) AS FROMDATE,                        
                    Convert(varchar,EMPLEAVEREQ.TODATE,103) AS TODATE,                                                     
                    dbo.INITCAP(EMPLEAVEREQ.LEAVETYPE) AS LEAVETYPE,                    
                    EMPLEAVEREQ.DUELEAVE,                    
                    EMSLEAVETYPE.ISEARNED,                    
                    EMSLEAVETYPE.ISDUTY                                                                          
                FROM                    
                    HR_LEAVETYPE as EMSLEAVETYPE with(nolock),HR_LEAVEREQ as EMPLEAVEREQ with(nolock)                   
                WHERE                          
                    EMSLEAVETYPE.DESCR = EMPLEAVEREQ.LEAVETYPE AND                         
                    EMPLEAVEREQ.LEAVETYPE <> 'CASUAL' AND                         
                    UPPER(EMPLEAVEREQ.STATUS) = 'APPROVED' AND                          
                    EMPLEAVEREQ.EMPLOYEEID = '" + EmployeeID + @"' and fromdate < Convert(date,'" + TillDate + "',103) " +
                       "ORDER BY TODATE ASC";


            dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");
            DateTime Till     = DateTime.ParseExact(TillDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
            DateTime DutyFrom = DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
            DateTime DutyTo   = Till; // Provided date

            //DateTime Age = System.DateTime.Now;
            DateDifference Age           = null;
            DateTime       RunningDutyTo = Till; // current date

            DateTime NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);

            string DutyPeriod = string.Empty, Year = String.Empty, Month = String.Empty, Days = String.Empty;
            double LeavesEarned = 0.0; double balance = 0.0;

            foreach (DataRow row in dsGrid.Tables["EMPLEAVEREQ"].Rows)
            {
                string LeaveType = row["LEAVETYPE"].ToString().ToUpper();
                DutyTo = DateTime.ParseExact(row["TODATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);

                DateTime RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddDays(-1);

                RunningDutyTo = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? DutyTo : RunningDutyFrom));

                // update duty period for only duty leaves
                // if (row["ISDUTY"].ToString().Equals("1"))
                // {
                // Calculating Duty Period
                RunningDutyTo = (RunningDutyTo > Till ? Till : RunningDutyTo);
                LeavesEarned  = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
                // balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? Convert.ToDouble(row["DUELEAVE"].ToString()) : 0);  // calculating new balance
                balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? (LeaveType.Equals("HALF AVERAGE PAY") ? Convert.ToDouble(row["DUELEAVE"].ToString()) / 2 : Convert.ToDouble(row["DUELEAVE"].ToString())) : 0);  // calculating new balance
                                                                                                                                                                                                                                    // }
                DutyFrom = DutyTo.AddDays(1);
            }

            /************************ Creating Last Row and Calulating balance **********************/
            if (DutyFrom < Till)
            {
                LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, Till); //Getting last Earned Leaves
                balance     += LeavesEarned;                                                    // Getting new Balance
            }
            /**********************************/
            return(balance); // binding grid
        }
コード例 #3
0
        public static DataSet GetELDataSet(string EmployeeID, String BPSScale, string JoiningDate, string MofAppointment)
        {
            strQuery = @"SELECT                    
                    HR_LEAVEREQ.EMPLOYEEID,                        
                    HR_LEAVEREQ.LEAVEID,
                    'N/A' AS DUTYFROM,                         
                    'N/A' AS DUTYTO,                        
                    '0' AS YEAR,                        
                    '0' AS MONTH,                        
                    '0' AS DAYS,                        
                    '0' AS LEAVESEARNED,                    
                    convert(varchar,HR_LEAVEREQ.APPDATE,103) AS APPDATE,                        
                    convert(varchar,HR_LEAVEREQ.FROMDATE,103) AS FROMDATE,                        
                    convert(varchar,HR_LEAVEREQ.TODATE,103) AS TODATE,                                                     
                    dbo.INITCAP(HR_LEAVEREQ.LEAVETYPE) AS LEAVETYPE,                    
                    HR_LEAVEREQ.DUELEAVE,                    
                    HR_LEAVETYPE.ISEARNED,                    
                    HR_LEAVETYPE.ISDUTY,                    
                    HR_LEAVEREQ.STATUS,                        
                    '0' AS BALANACE                     
                FROM                    
                    HR_LEAVETYPE with (nolock),HR_LEAVEREQ  with (nolock)                 
                WHERE                          
                    HR_LEAVETYPE.DESCR = HR_LEAVEREQ.LEAVETYPE AND                         
                    HR_LEAVEREQ.LEAVETYPE <> 'CASUAL' AND                         
                    UPPER(HR_LEAVEREQ.STATUS) = 'APPROVED' AND                          
                    HR_LEAVEREQ.EMPLOYEEID = '" + EmployeeID + @"'                     
                    ORDER BY convert(date,TODATE,103) ASC";


            dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");


            DateTime DutyFrom          = DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture),
                     DutyTo            = System.DateTime.Now,
                     RunningDutyFrom   = System.DateTime.Now,
                     RunningDutyTo     = System.DateTime.Now,
                     NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);// current date

            DateDifference Age = null;


            string DutyPeriod = string.Empty, Year = String.Empty, Month = String.Empty, Days = String.Empty;
            double LeavesEarned = 0.0; double balance = 0.0;

            foreach (DataRow row in dsGrid.Tables["EMPLEAVEREQ"].Rows)
            {
                string LeaveType = row["LEAVETYPE"].ToString().ToUpper();
                DutyTo = DateTime.ParseExact(row["TODATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);

                RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddDays(-1);
                RunningDutyTo   = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? DutyTo : RunningDutyFrom));

                // update duty period for only duty leaves

                /************ in case of earned leaves or on duty leaves the duty period end at leave end date  *********/
                if (LeaveType.Equals("LEAVE ENCASHMENT") || LeaveType.Equals("DELETED"))
                {
                    row["FROMDATE"] = row["TODATE"] = String.Empty;
                }

                row["DUTYFROM"] = String.Format("{0:dd/MM/yyyy}", DutyFrom);
                RunningDutyTo   = (RunningDutyTo > System.DateTime.Now ? System.DateTime.Now : RunningDutyTo);
                row["DUTYTO"]   = String.Format("{0:dd/MM/yyyy}", (DutyFrom < RunningDutyTo ? RunningDutyTo : RunningDutyTo.AddDays(1)));
                // Calculating Duty Period

                LeavesEarned        = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
                row["LEAVESEARNED"] = LeavesEarned + (balance.Equals(0) ? "" : " + " + balance);  // displaying balance


                balance        += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? (LeaveType.Equals("HALF AVERAGE PAY") ? Convert.ToDouble(row["DUELEAVE"].ToString()) / 2 : (LeaveType.Equals("ABSENT") ? Convert.ToDouble(row["DUELEAVE"].ToString()) * 2 : Convert.ToDouble(row["DUELEAVE"].ToString()))) : 0); // calculating new balance
                row["BALANACE"] = balance;

                // displaying Duty Period
                if (DutyFrom < RunningDutyTo)
                {
                    if (RunningDutyTo > System.DateTime.Now)
                    {
                        RunningDutyTo = System.DateTime.Now;
                    }
                    Age   = new DateDifference(RunningDutyTo, DutyFrom); //new DateTime((RunningDutyTo - DutyFrom).Ticks).AddDays(1);
                    Year  = (Age.Years).ToString(); row["YEAR"] = Year;
                    Month = (Age.Months).ToString(); row["MONTH"] = Month;
                    Days  = (Age.Days).ToString(); row["DAYS"] = Days;
                }

                DutyFrom = DutyTo.AddDays(1);
            }

            /************************ Creating Last Row and Calulating balance **********************/
            if (DutyFrom < DateTime.Now)
            {
                LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, System.DateTime.Now); //Getting last Earned Leaves
                string LastBalance = LeavesEarned + (balance.Equals(0) ? "" : " + " + balance);                // Displaying Balance
                balance += LeavesEarned;                                                                       // Getting new Balance
                Age      = new DateDifference(System.DateTime.Now, DutyFrom);                                  //new DateTime((System.DateTime.Now - DutyFrom).Ticks);            // Calulating Duty Period
                string[] values = { EmployeeID, "0", String.Format("{0:dd/MM/yyyy}", DutyFrom.Date), String.Format("{0:dd/MM/yyyy}", System.DateTime.Now), (Age.Years).ToString(), (Age.Months).ToString(), (Age.Days).ToString(), LastBalance, "", "", "", "", "0", "0", "0", "0", balance.ToString() };
                dsGrid.Tables["EMPLEAVEREQ"].Rows.Add(values);                                                 // adding last created row
            }
            /**********************************/

            return(dsGrid); // binding grid
        }