public void FillingTotalHours()//fill total hours per month in PPMtotalHoursFilled 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 <TotalHoursFilledPerPerson> list1 = new List <TotalHoursFilledPerPerson>(); foreach (var item in list) { Connection.Open(); SqlCommand Command1 = new SqlCommand("PPMtotalHoursFilledPerPerson @EmployeeName", Connection); Command1.Parameters.AddWithValue("@EmployeeName", item.ResourceName); SqlDataReader DataReader1 = Command1.ExecuteReader(); while (DataReader1.Read()) { TotalHoursFilledPerPerson obj1 = new TotalHoursFilledPerPerson() { ResourceNumber = Convert.ToString(DataReader1[0]), ResourceName = Convert.ToString(DataReader1[1]), Hours = Convert.ToDouble(DataReader1[2]), }; list1.Add(obj1); } Connection.Close(); } foreach (var item in list1) { Connection.Open(); SqlCommand Command2 = new SqlCommand("insertintoPPMtotalHoursFilled @EmployeeNumber,@EmployeeName,@TotalHoursForPPM", Connection); Command2.Parameters.AddWithValue("@EmployeeNumber", item.ResourceNumber); Command2.Parameters.AddWithValue("@EmployeeName", item.ResourceName); Command2.Parameters.AddWithValue("@TotalHoursForPPM", item.Hours); Command2.ExecuteNonQuery(); Connection.Close(); } }
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(); } }