public ActionResult MissingDatePersons() { List <MissingPersons> list = new List <MissingPersons>(); List <MissingPersons> list1 = new List <MissingPersons>(); // SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS;Integrated Security=sspi;database=FileAnalysis"); SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS; Initial Catalog = FileAnalysis; User ID = sa; Password = Passw0rd@12;"); Connection.Open(); SqlCommand Command = new SqlCommand("GettingPPMMissedDates", Connection); SqlDataReader DataReader = Command.ExecuteReader(); while (DataReader.Read()) { MissingPersons obj = new MissingPersons() { ResourceNumber = Convert.ToString(DataReader[0]), ResourceName = Convert.ToString(DataReader[1]), DatesMissed = Convert.ToString(DataReader[2]) }; list.Add(obj); } Connection.Close(); foreach (var item in list) { SqlConnection Connection1 = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS; Initial Catalog = FileAnalysis; User ID = sa; Password = Passw0rd@12;"); Connection1.Open(); SqlCommand Command1 = new SqlCommand("GettingPPMHoursFilled @EmployeeNumber", Connection1); Command1.Parameters.AddWithValue("@EmployeeNumber", item.ResourceNumber); SqlDataReader DataReader1 = Command1.ExecuteReader(); while (DataReader1.Read()) { MissingPersons obj1 = new MissingPersons() { ResourceNumber = item.ResourceNumber, ResourceName = item.ResourceName, DatesMissed = item.DatesMissed, HoursFilledInRemainingDays = Convert.ToDouble(DataReader1[0]) }; list1.Add(obj1); } Connection1.Close(); } return(View(list1)); }
public void MissingDates()//store missed ppm dates in a ppmmisseddates table { List <GettingAllEmployees> list = new List <GettingAllEmployees>(); // SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS;Integrated Security=sspi;database=FileAnalysis"); SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS; Initial Catalog = FileAnalysis; User ID = sa; Password = Passw0rd@12;"); Connection.Open(); SqlCommand Command = new SqlCommand("PPMAllEmployeesNames", Connection); SqlDataReader DataReader = Command.ExecuteReader(); while (DataReader.Read()) { GettingAllEmployees obj = new GettingAllEmployees() { ResourceNumber = Convert.ToString(DataReader[0]), ResourceName = Convert.ToString(DataReader[1]) }; list.Add(obj); } Connection.Close(); List <MissingPersons> list1 = new List <MissingPersons>(); foreach (var item in list) { string MissedDates = null; int loop = 0;//to remove last comma(,) Connection.Open(); SqlCommand Command1 = new SqlCommand("PPMGettingMissedDates @ResourceName", Connection); Command1.Parameters.AddWithValue("@ResourceName", item.ResourceName); SqlDataReader DataReader1 = Command1.ExecuteReader(); while (DataReader1.Read()) { if (loop == 0) { MissedDates += Convert.ToInt16(DataReader1[0]); loop++; } else { MissedDates = MissedDates + ","; MissedDates += Convert.ToInt16(DataReader1[0]); } } if (MissedDates != null) { MissingPersons MissedPersonsObj = new MissingPersons() { ResourceNumber = item.ResourceNumber, ResourceName = item.ResourceName, DatesMissed = MissedDates, }; list1.Add(MissedPersonsObj); MissedDates = null; } Connection.Close(); } foreach (var item in list1) { int i; Connection.Open(); SqlCommand Command2 = new SqlCommand("insertintoppmmisseddates @EmployeeNumber,@EmployeeName,@PPMMissedDates", Connection); Command2.Parameters.AddWithValue("@EmployeeNumber", item.ResourceNumber); Command2.Parameters.AddWithValue("@EmployeeName", item.ResourceName); Command2.Parameters.AddWithValue("@PPMMissedDates", item.DatesMissed); i = Command2.ExecuteNonQuery(); Connection.Close(); } }