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();
}
}
