public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request)
{
var response = new PromoteStudentResponse();
using var con = new SqlConnection(SetConnection.GetConnection());
using var com = new SqlCommand();
con.Open();
com.Connection = con;
var transaction = con.BeginTransaction();
com.Transaction = transaction;
com.CommandText = "SELECT enr.IdEnrollment from Enrollment enr INNER JOIN Studies st on enr.IdStudy = st.IdStudy WHERE st.Name = @Name and enr.Semester = @Semester";
com.Parameters.AddWithValue("Name", request.Name);
com.Parameters.AddWithValue("Semester", request.Semester);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
throw new Exception("Nie znalezono semestru i/lub studiów");
}
else
{
dr.Close();
using (SqlConnection conn = new SqlConnection(SetConnection.GetConnection()))
{
conn.Open();
SqlCommand cmd = new SqlCommand("PromoteStudents", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Name", request.Name));
cmd.Parameters.Add(new SqlParameter("@Semester", request.Semester));
cmd.ExecuteReader();
conn.Close();
}
response.Semester = request.Semester + 1;
response.StartDate = DateTime.Now.ToString("dd.MM.yyyy");
response.StudiesName = request.Name;
transaction.Commit();
}
return(response);
}
private static void LoadMainData()
{
string strServer, strDatabase, strUser, strPassword;
bool nReturn = false;
try
{
DBConnection dbCon = new DBConnection();
dbCon.getDBConnectionInfo(out strServer, out strDatabase, out strUser, out strPassword);
m_objManager.Parent = null;
m_objManager.DBEngine = new EzSql2(strServer, strDatabase, strUser, strPassword);
m_objManager.DBEngine.open();
nReturn = true;
UIMessage.ENTER_TO_TAB = HPA.Properties.Settings.Default.EnterToTab;
UIMessage.DBEngine = m_objManager.DBEngine;
UIMessage.StoreProcName = "MST_Message_Get";
UIMessage.StoreProcParamMessageID = "@MessageID";
UIMessage.StoreProcParamLanguage = "@Language";
UIMessage.StoreProcParamOutName = "@Content";
UIMessage.MessageBoxTitle = "Paradise-HPA";
//UIMessage.TA_LoadLogTimeWhenOpen = true;
UIMessage.LoginWallpaper = HPA.Properties.Settings.Default.LoginWallpaper;
m_objManager.UserID = 3;
return;
}
catch
{
if (nReturn == false)
{
object obj;
SetConnection frmCn = new SetConnection()
{
AssemblyName = "HPA.Component.SystemAdmin", ClassName = "SetConnection"
};
m_objManager.OpenObject(frmCn, true, null, out obj);
}
return;
}
}
public EnrollResponse EnrollStudent(EnrollStudentRequest request)
{
using var con = new SqlConnection(SetConnection.GetConnection());
using var com = new SqlCommand();
con.Open();
com.Connection = con;
var transaction = con.BeginTransaction();
com.Transaction = transaction;
com.CommandText = "SELECT s.IdStudy FROM Studies s WHERE s.Name = @Name";
com.Parameters.AddWithValue("Name", request.Name);
var dr = com.ExecuteReader();
if (!dr.Read())
{
throw new Exception("Podane studia nie istnieją ");;
}
var idStudy = int.Parse(dr["IdStudy"].ToString());
dr.Close();
com.Parameters.Clear();
com.CommandText =
"SELECT * FROM Enrollment enr WHERE enr.Semester = 1 AND enr.IdStudy = @IdStudy";
com.Parameters.AddWithValue("IdStudy", idStudy);
dr = com.ExecuteReader();
var enrollResponse = new EnrollResponse();
if (!dr.Read())
{
dr.Close();
com.Parameters.Clear();
com.CommandText =
@"INSERT INTO Enrollment(IdEnrollment, Semester, StartDate, IdStudy) OUTPUT INSERTED.IdEnrollment, INSERTED.Semester, INSERTED.StartDate, INSERTED.IdStudy VALUES((SELECT MAX(E.IdEnrollment) FROM Enrollment E) + 1, @Semester, @StartDate, @IdStudy);";
com.Parameters.AddWithValue("Semester", 1);
com.Parameters.AddWithValue("StartDate", DateTime.Now);
com.Parameters.AddWithValue("IdStudy", idStudy);
enrollResponse.IdEnrollment = int.Parse(com.ExecuteScalar().ToString());
enrollResponse.Semester = int.Parse(com.Parameters["Semester"].Value.ToString());
enrollResponse.IdStudy = int.Parse(com.Parameters["IdStudy"].Value.ToString());
enrollResponse.StartDate =
DateTime.Parse(com.Parameters["StartDate"].Value.ToString()).ToString("yyyy-MM-dd");
}
else
{
enrollResponse.IdEnrollment = int.Parse(dr["IdEnrollment"].ToString());
enrollResponse.Semester = int.Parse(dr["Semester"].ToString());
enrollResponse.IdStudy = int.Parse(dr["IdStudy"].ToString());
enrollResponse.StartDate =
DateTime.Parse(dr["StartDate"].ToString()).ToString("yyyy-MM-dd");
}
dr.Close();
com.Parameters.Clear();
com.CommandText = "SELECT S.IndexNumber FROM Student S WHERE IndexNumber = @indexNumber";
com.Parameters.AddWithValue("indexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (dr.Read())
{
throw new Exception("Numer indeksu nie jest unikalny");;
}
dr.Close();
com.Parameters.Clear();
com.CommandText =
@"INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
com.Parameters.AddWithValue("FirstName", request.FirstName);
com.Parameters.AddWithValue("LastName", request.LastName);
com.Parameters.AddWithValue("BirthDate", request.BirthDate);
com.Parameters.AddWithValue("IdEnrollment", enrollResponse.IdEnrollment);
com.ExecuteNonQuery();
transaction.Commit();
return(enrollResponse);
}
