public string deleteUser(getUid userId) { try { string q = "delete from nworksuser where uid='" + userId.uid + "';"; MySqlCommand cmd = new MySqlCommand(q, conn); MySqlDataReader rdr; conn.Open(); rdr = cmd.ExecuteReader(); conn.Close(); return(string.Format("User Deleted Successfully!")); }catch (Exception ex) { return(string.Format(ex.ToString())); } }
public string deleteUser(getUid userId) { try { string q1 = "select _type from nworksuser where uid='" + userId.uid + "';"; MySqlCommand command = new MySqlCommand(q1, conn); conn.Open(); MySqlDataReader rdr1 = command.ExecuteReader(); string _type = ""; while (rdr1.Read()) { _type = rdr1.GetString("_type"); } conn.Close(); if (_type == "Admin") { string q = "delete from nworksuser where uid='" + userId.uid + "';"; MySqlCommand cmd = new MySqlCommand(q, conn); MySqlDataReader rdr; conn.Open(); rdr = cmd.ExecuteReader(); conn.Close(); } else if (_type == "Admin_Employee") { string q = "update nworksuser set _type='Employee' where uid='" + userId.uid + "';"; MySqlCommand cmd = new MySqlCommand(q, conn); MySqlDataReader rdr; conn.Open(); rdr = cmd.ExecuteReader(); conn.Close(); } return(string.Format("User Deleted Successfully!")); } catch (Exception ex) { return(string.Format(ex.ToString())); } }
public string getEmailAddress(getUid user) { try { string EmailAddress = ""; string q = "select email from nworksuser where uid='" + user.uid + "';"; MySqlCommand cmd = new MySqlCommand(q, conn); conn.Open(); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { EmailAddress = rdr.GetString("email"); } conn.Close(); return(EmailAddress); } catch (Exception ex) { return(string.Format(ex.ToString())); } }
public List <ModelEventCalendarForUser> GetEventCalendar(getUid user) { string q1 = "select srno,occasion,_date,week_day from holiday_calendar order by _date;"; string q2 = "select count(*) from leaves where empno='" + user.uid + "';"; string q3 = "select leavedate from leaves where empno='" + user.uid + "';"; List <ModelEventCalendarForUser> Calendar = new List <ModelEventCalendarForUser>(); conn.Open(); MySqlCommand cmd2 = new MySqlCommand(q2, conn); Int32 empLeaveCount = Convert.ToInt32(cmd2.ExecuteScalar()); conn.Close(); conn.Open(); DataTable table = new DataTable(); MySqlCommand cmd1 = new MySqlCommand(q1, conn); MySqlDataAdapter adapter = new MySqlDataAdapter(cmd1); adapter.Fill(table); conn.Close(); foreach (DataRow row in table.Rows) { ModelEventCalendarForUser obj = new ModelEventCalendarForUser(); obj.Occasion = row["occasion"].ToString(); obj.OccasionDate = Convert.ToDateTime(row["_date"].ToString()).ToString("dd-MMM-yyyy"); obj.OccasionWeekDay = row["week_day"].ToString(); //check whether user already taken current ocassion if (empLeaveCount == 0) { Debug.WriteLine("Marking all as true."); obj.isAllow = true; Calendar.Add(obj); continue; } conn.Open(); MySqlCommand cmd3 = new MySqlCommand(q3, conn); MySqlDataReader rdr1 = cmd3.ExecuteReader(); while (rdr1.Read()) { string leaveTaken = rdr1.GetString("leavedate"); Debug.WriteLine("Leave Taken: " + leaveTaken); string eventDate = row["_date"].ToString(); Debug.WriteLine("Event Date: " + eventDate); if (leaveTaken.Equals(eventDate)) { Debug.WriteLine("Inside If"); obj.isAllow = false; break; } Debug.WriteLine("Marking " + leaveTaken + " as true."); obj.isAllow = true; } Debug.WriteLine(""); Calendar.Add(obj); rdr1.Close(); conn.Close(); } return(Calendar); }
public userLeavesStatus userLeaveStatus(getUid user) { int Current_Year = DateTime.Now.Year; int Current_Month = DateTime.Now.Month; int Current_Day = DateTime.Now.DayOfYear; DateTime firstDay = new DateTime(Current_Year, 1, 1); DateTime lastDay = new DateTime(Current_Year, 12, 31); int y2ydiff = 0; int y2mdiff = 0; float stdYearlyLeaves = 22; userLeavesStatus MyData = new userLeavesStatus(); string q2 = "select * from nworksuser where uid='" + user.uid + "';"; conn.Open(); MySqlCommand cmd2 = new MySqlCommand(q2, conn); MySqlDataReader rdr2 = cmd2.ExecuteReader(); DateTime Date_of_joining = new DateTime(); string IsUserActive = ""; DateTime leavedate = DateTime.Today; while (rdr2.Read()) { Date_of_joining = rdr2.GetDateTime("hire_date"); IsUserActive = rdr2.GetString("UserActive"); leavedate = rdr2.GetDateTime("leavedate"); } conn.Close(); Debug.WriteLine("Total days : " + (lastDay - firstDay).TotalDays.ToString()); if (Date_of_joining.Year.ToString() == Current_Year.ToString()) { Current_Day = Convert.ToInt32((DateTime.Today.Date - Date_of_joining).TotalDays); } if (Date_of_joining.Year.ToString() == Current_Year.ToString()) { DateTime endOfMonth = new DateTime(Current_Year, Current_Month, DateTime.DaysInMonth(Current_Year, Current_Month)); y2mdiff = Convert.ToInt32((endOfMonth - Date_of_joining).TotalDays); y2ydiff = Convert.ToInt32((lastDay - Date_of_joining).TotalDays); } else { DateTime endOfMonth = new DateTime(Current_Year, Current_Month, DateTime.DaysInMonth(Current_Year, Current_Month)); y2mdiff = Convert.ToInt32((endOfMonth - firstDay).TotalDays); y2ydiff = Convert.ToInt32((lastDay - firstDay).TotalDays); } //total used in this year string q3 = "select count(*) from Leaves where empno='" + user.uid + "' and holiday='Leave' and leavedate between '" + firstDay.ToString("yyyy-MM-dd") + "' and '" + DateTime.Today.ToString("yyyy-MM-dd") + "';"; conn.Open(); MySqlCommand cmd3 = new MySqlCommand(q3, conn); float usedLeavesCount = float.Parse(Convert.ToInt32(cmd3.ExecuteScalar()).ToString()); Debug.WriteLine("Today :" + DateTime.Today.ToShortDateString()); Debug.WriteLine("first day :" + firstDay.ToShortDateString()); conn.Close(); string q4 = "select count(*) from Leaves where empno='" + user.uid + "' and holiday='Half Day' and leavedate between '" + firstDay.ToString("yyyy-MM-dd") + "' and '" + DateTime.Today.ToString("yyyy-MM-dd") + "';"; conn.Open(); MySqlCommand cmd4 = new MySqlCommand(q4, conn); float used_HalfDay_Count = float.Parse((Convert.ToDouble(cmd4.ExecuteScalar()) / 2).ToString()); Debug.WriteLine("Half day count :" + used_HalfDay_Count.ToString()); conn.Close(); //applied but not used string q5 = "select count(*) from Leaves where empno='" + user.uid + "' and holiday='Leave' and leavedate between '" + DateTime.Today.ToString("yyyy-MM-dd") + "' and '" + lastDay.ToString("yyyy-MM-dd") + "';"; conn.Open(); MySqlCommand cmd5 = new MySqlCommand(q5, conn); float not_usedLeavesCount = float.Parse(Convert.ToInt32(cmd5.ExecuteScalar()).ToString()); conn.Close(); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //CALCULATE a AND c string q1 = "select * from additional_earned_leaves where Current_Year='" + Current_Year + "' and uid='" + user.uid + "';"; conn.Open(); MySqlCommand cmd1 = new MySqlCommand(q1, conn); MySqlDataReader rdr1 = cmd1.ExecuteReader(); while (rdr1.Read()) { MyData.a_rolled_over_from_previous_year = rdr1.GetFloat("Rolled_Over_from_Previous_Year"); MyData.c_additional_earned_in_current_year = rdr1.GetFloat("Earned_in_current_year"); } conn.Close(); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculation of B double LeavesEarnedUptoYearEnd = 0; if (IsUserActive == "false") { double EarnedLeaves; if (Date_of_joining.Year == Current_Year) { EarnedLeaves = (((leavedate - Date_of_joining).TotalDays + 1) / 365) * 22; LeavesEarnedUptoYearEnd = EarnedLeaves; } else { EarnedLeaves = (((leavedate - firstDay).TotalDays + 1) / 365) * 22; LeavesEarnedUptoYearEnd = EarnedLeaves; } MyData.b_earned_in_current_year = float.Parse(Math.Round(EarnedLeaves, 1, MidpointRounding.AwayFromZero).ToString()); //B) } else { double EarnedLeaves; if (Date_of_joining.Year == Current_Year) { EarnedLeaves = (((DateTime.Today - Date_of_joining).TotalDays + 1) / 365) * 22; LeavesEarnedUptoYearEnd = (((lastDay - Date_of_joining).TotalDays + 1) / 365) * 22; } else { EarnedLeaves = (((DateTime.Today - firstDay).TotalDays + 1) / 365) * 22; LeavesEarnedUptoYearEnd = (((lastDay - firstDay).TotalDays + 1) / 365) * 22; } MyData.b_earned_in_current_year = float.Parse(Math.Round(EarnedLeaves, 1, MidpointRounding.AwayFromZero).ToString()); //B) } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculations of D MyData.d_total_in_current_year = float.Parse(Math.Round((MyData.a_rolled_over_from_previous_year + MyData.b_earned_in_current_year + MyData.c_additional_earned_in_current_year), 1, MidpointRounding.AwayFromZero).ToString()); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculations of E MyData.e_total_used_in_current_year = usedLeavesCount + used_HalfDay_Count; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculation of F MyData.f_total_applied_but_notused_in_current_year = not_usedLeavesCount; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculation of G string q11 = "select count(*) from leaves where iswithpay='false' and empno='" + user.uid + "';"; conn.Open(); MySqlCommand cmd11 = new MySqlCommand(q11, conn); MyData.g_total_without_pay_in_current_year = float.Parse(Convert.ToInt32(cmd11.ExecuteScalar()).ToString()); conn.Close(); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculation of I float i_total_left_in_current_year2date = (MyData.a_rolled_over_from_previous_year + MyData.c_additional_earned_in_current_year + (stdYearlyLeaves / 365) * Current_Day) - MyData.e_total_used_in_current_year - MyData.f_total_applied_but_notused_in_current_year + MyData.g_total_without_pay_in_current_year; string[] MyString0 = i_total_left_in_current_year2date.ToString().Split('.'); if (MyString0.Length == 2) { if (Convert.ToDouble("0." + MyString0[1]) < 0.3) { MyString0[1] = "0.0"; i_total_left_in_current_year2date = float.Parse(MyString0[0]) + float.Parse(MyString0[1]); } else if (Convert.ToDouble("0." + MyString0[1]) > 0.8) { MyString0[1] = "1.0"; i_total_left_in_current_year2date = float.Parse(MyString0[0]) + float.Parse(MyString0[1]); } else { MyString0[1] = "0.5"; i_total_left_in_current_year2date = float.Parse(MyString0[0]) + float.Parse(MyString0[1]); } } MyData.i_total_left_in_current_year2date = i_total_left_in_current_year2date; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculations of H float h_total_left_in_current_year = MyData.a_rolled_over_from_previous_year + MyData.c_additional_earned_in_current_year + (stdYearlyLeaves / 365 * y2ydiff) - MyData.e_total_used_in_current_year - MyData.f_total_applied_but_notused_in_current_year + MyData.g_total_without_pay_in_current_year; //float h_total_left_in_current_year = MyData.a_rolled_over_from_previous_year + MyData.c_additional_earned_in_current_year + float.Parse(LeavesEarnedUptoYearEnd.ToString()) - MyData.e_total_used_in_current_year - MyData.f_total_applied_but_notused_in_current_year + MyData.g_total_without_pay_in_current_year; string[] MyString = h_total_left_in_current_year.ToString().Split('.'); if (MyString.Length == 2) { if (Convert.ToDouble("0." + MyString[1]) < 0.3) { MyString[1] = "0.0"; h_total_left_in_current_year = float.Parse(MyString[0]) + float.Parse(MyString[1]); Debug.WriteLine(h_total_left_in_current_year.ToString()); } else if (Convert.ToDouble("0." + MyString[1]) > 0.8) { MyString[1] = "1.0"; h_total_left_in_current_year = float.Parse(MyString[0]) + float.Parse(MyString[1]); Debug.WriteLine(h_total_left_in_current_year.ToString()); } else { MyString[1] = "0.5"; h_total_left_in_current_year = float.Parse(MyString[0]) + float.Parse(MyString[1]); Debug.WriteLine(h_total_left_in_current_year.ToString()); } } MyData.h_total_left_in_current_year = h_total_left_in_current_year; //(H) /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculations of J float j_total_left_in_current_year2month = (MyData.a_rolled_over_from_previous_year + MyData.c_additional_earned_in_current_year + (stdYearlyLeaves / 365) * y2mdiff) - MyData.e_total_used_in_current_year - MyData.f_total_applied_but_notused_in_current_year + MyData.g_total_without_pay_in_current_year; string[] MyString2 = j_total_left_in_current_year2month.ToString().Split('.'); if (MyString2.Length == 2) { if (Convert.ToDouble("0." + MyString2[1]) < 0.3) { MyString2[1] = "0.0"; j_total_left_in_current_year2month = float.Parse(MyString2[0]) + float.Parse(MyString2[1]); } else if (Convert.ToDouble("0." + MyString2[1]) > 0.8) { MyString2[1] = "1.0"; j_total_left_in_current_year2month = float.Parse(MyString2[0]) + float.Parse(MyString2[1]); Debug.WriteLine(i_total_left_in_current_year2date.ToString()); } else { MyString2[1] = "0.5"; j_total_left_in_current_year2month = float.Parse(MyString2[0]) + float.Parse(MyString2[1]); } } MyData.j_total_left_in_current_year2month = j_total_left_in_current_year2month; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// return(MyData); }
public userHolidaysStatus userLeaveStatus(getUid user) { float a_earned_in_current_year = 0; int Current_Year = DateTime.Now.Year; DateTime firstDay = new DateTime(Current_Year, 1, 1); DateTime lastDay = new DateTime(Current_Year, 12, 31); int Current_Month = DateTime.Now.Month; int y2ydiff = 0; int y2mdiff = 0; float stdYearlyHolidays = 8; int Current_Day = DateTime.Now.DayOfYear; userHolidaysStatus MyData = new userHolidaysStatus(); string q2 = "select * from nworksuser where uid='" + user.uid + "';"; conn.Open(); MySqlCommand cmd2 = new MySqlCommand(q2, conn); MySqlDataReader rdr2 = cmd2.ExecuteReader(); DateTime Date_of_joining = new DateTime(); string IsUserActive = ""; DateTime leavedate = DateTime.Today; while (rdr2.Read()) { Date_of_joining = rdr2.GetDateTime("hire_date"); IsUserActive = rdr2.GetString("UserActive"); leavedate = rdr2.GetDateTime("leavedate"); } conn.Close(); if (Date_of_joining.Year.ToString() == Current_Year.ToString()) { Current_Day = Convert.ToInt32((DateTime.Today.Date - Date_of_joining).TotalDays); } if (Date_of_joining.Year.ToString() == Current_Year.ToString()) { DateTime endOfMonth = new DateTime(Current_Year, Current_Month, DateTime.DaysInMonth(Current_Year, Current_Month)); y2mdiff = Convert.ToInt32((endOfMonth - Date_of_joining).TotalDays); y2ydiff = Convert.ToInt32((lastDay - Date_of_joining).TotalDays); } else { DateTime endOfMonth = new DateTime(Current_Year, Current_Month, DateTime.DaysInMonth(Current_Year, Current_Month)); y2mdiff = Convert.ToInt32((endOfMonth - firstDay).TotalDays); y2ydiff = Convert.ToInt32((lastDay - firstDay).TotalDays); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculate A if (IsUserActive == "false") { double EarnedHolidays; if (Date_of_joining.Year == DateTime.Today.Year) { EarnedHolidays = (((leavedate - Date_of_joining).TotalDays + 1) / 365) * 8; } else { EarnedHolidays = (((leavedate - firstDay).TotalDays + 1) / 365) * 8; } MyData.a_earned_in_current_year = float.Parse(Math.Round(EarnedHolidays, 1, MidpointRounding.AwayFromZero).ToString()); a_earned_in_current_year = float.Parse(EarnedHolidays.ToString()); //A) Debug.WriteLine(EarnedHolidays); } else { double EarnedHolidays; if (Date_of_joining.Year == DateTime.Today.Year) { EarnedHolidays = (((DateTime.Today - Date_of_joining).TotalDays + 1) / 365) * 8; } else { EarnedHolidays = (((DateTime.Today - firstDay).TotalDays + 1) / 365) * 8; } MyData.a_earned_in_current_year = float.Parse(Math.Round(EarnedHolidays, 1, MidpointRounding.AwayFromZero).ToString()); a_earned_in_current_year = float.Parse(EarnedHolidays.ToString()); //A) Debug.WriteLine(EarnedHolidays); } /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculate B string q3 = "select count(*) from Leaves where empno='" + user.uid + "' and holiday='Holiday' and leavedate between '" + firstDay.ToString("yyyy-MM-dd") + "' and '" + DateTime.Today.ToString("yyyy-MM-dd") + "';"; conn.Open(); MySqlCommand cmd3 = new MySqlCommand(q3, conn); float usedHolidaysCount = float.Parse(Convert.ToInt32(cmd3.ExecuteScalar()).ToString()); conn.Close(); MyData.b_total_used_in_current_year = usedHolidaysCount; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculate C string q5 = "select count(*) from Leaves where empno='" + user.uid + "' and holiday='Holiday' and leavedate between '" + DateTime.Today.ToString("yyyy-MM-dd") + "' and '" + lastDay.ToString("yyyy-MM-dd") + "';"; conn.Open(); MySqlCommand cmd5 = new MySqlCommand(q5, conn); float not_usedHolidaysCount = float.Parse(Convert.ToInt32(cmd5.ExecuteScalar()).ToString()); conn.Close(); MyData.c_total_applied_but_notused_in_current_year = not_usedHolidaysCount; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //calculate E float All_Taken_Holidays = MyData.b_total_used_in_current_year + MyData.c_total_applied_but_notused_in_current_year; float e_total_left_in_current_year2date = stdYearlyHolidays / 365 * Current_Day - All_Taken_Holidays; string[] MyString0 = e_total_left_in_current_year2date.ToString().Split('.'); if (MyString0.Length == 2) { e_total_left_in_current_year2date = float.Parse(MyString0[0]); } MyData.e_total_left_in_current_year2date = e_total_left_in_current_year2date; /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculate D float d_total_left_in_current_year = (stdYearlyHolidays / 365 * y2ydiff) - (MyData.b_total_used_in_current_year + MyData.c_total_applied_but_notused_in_current_year); string[] MyString = d_total_left_in_current_year.ToString().Split('.'); if (MyString.Length == 2) { d_total_left_in_current_year = float.Parse(MyString[0]); } MyData.d_total_left_in_current_year = d_total_left_in_current_year; //D) /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //Calculate F float f_total_left_in_current_year2Month = (stdYearlyHolidays / 365 * y2mdiff) - All_Taken_Holidays; string[] MyString1 = f_total_left_in_current_year2Month.ToString().Split('.'); if (MyString1.Length == 2) { f_total_left_in_current_year2Month = float.Parse(MyString1[0]); } MyData.f_total_left_in_current_year2Month = f_total_left_in_current_year2Month; return(MyData); }