public userLeavesStatus userLeaveStatus([FromBody] string uid) { userLeavesStatus MyData = new userLeavesStatus(); string q1 = "select * from additional_earned_leaves where uid='" + 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"); MyData.a_rolled_over_from_previous_year = 10; MyData.c_additional_earned_in_current_year = 20; } conn.Close(); MyData.a_rolled_over_from_previous_year = 0; //from admin MyData.b_earned_in_current_year = 0; //formula MyData.c_additional_earned_in_current_year = 0; //from admin MyData.d_total_in_current_year = (MyData.a_rolled_over_from_previous_year + MyData.b_earned_in_current_year + MyData.c_additional_earned_in_current_year); MyData.e_total_used_in_current_year = 0; MyData.f_total_applied_but_notused_in_current_year = 0; MyData.g_total_without_pay_in_current_year = 0; MyData.h_total_left_in_current_year = 0; return(MyData); }
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); }