public void DeleteTransaction(int t)
{
string queryPerson = @"UPDATE Person SET status=2 WHERE PersonId= :Person";
string queryUser = @"UPDATE Users SET status=2 WHERE PersonId= :Person";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Update.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[1];
OracleParameter[] parameters2 = new OracleParameter[1];
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":Person", t);
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryUser;
parameters2[0] = new OracleParameter(":Person", t);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Delete student by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not delete Student({1}).", DateTime.Now, ex.Message));
}
}
public void DeleteTransaction(int idsub)
{
string queryClass = @"UPDATE Class SET status=0 WHERE idClass= :class";
try
{
int i = 1;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Del Class.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
OracleParameter[] parameters1 = new OracleParameter[1];
cmds[0].CommandText = queryClass;
parameters1[0] = new OracleParameter(":class", idsub);
cmds[0].Parameters.AddRange(parameters1);
cmds[0].CommandText = queryClass;
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Dele Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Dele Class({1}).", DateTime.Now, ex.Message));
}
}
public void UpdateEnabled(int id, int stat)
{
string queryPerson = @"UPDATE Person SET status= :stat WHERE Personid= :Person";
string queryStudent = @"UPDATE Users SET status= :stat WHERE Personid= :Person";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Update.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[1];
OracleParameter[] parameters2 = new OracleParameter[1];
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":Person", id);
parameters1[1] = new OracleParameter(":stat", stat);
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryStudent;
parameters2[0] = new OracleParameter(":stat", stat);
parameters2[1] = new OracleParameter(":Person", id);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Modif student by" + Session.SessionCurrent.ToString() + "/" + DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not modif Student({1}).", DateTime.Now, ex.Message));
}
}
//INSERT TEACHER SUBJECTS
public void InsertTeacherSubject(Class c)
{
string queryClass = @" UPDATE CLASS SET TEACHERID= :teacher, STATUS=2 WHERE IDCLASS= :class";
try
{
int i = 1;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Add Class to Teacher.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
OracleParameter[] parameters1 = new OracleParameter[2];
cmds[0].CommandText = queryClass;
parameters1[0] = new OracleParameter(":teacher", c.TeacherId);
parameters1[1] = new OracleParameter(":class", c.IdClass);
cmds[0].Parameters.AddRange(parameters1);
cmds[0].CommandText = queryClass;
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Dele Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Dele Class({1}).", DateTime.Now, ex.Message));
}
}
public void InsertTransaction(Model.Dosage d)
{
string queryUpdate = @"UPDATE DOSAGE SET STATUS=0";
string queryInsert = @"INSERT INTO DOSAGE(NROAUTHORIZATION,DEADLINE,DOSAGEKEY)
VALUES(:NROAUTHORIZATION,ADD_MONTHS(CURRENT_TIMESTAMP,6),:DOSAGEKEY)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Insert New Dosage.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
cmds[0].CommandText = queryUpdate;
cmds[1].CommandText = queryInsert;
cmds[1].Parameters.Add(new OracleParameter(":NROAUTHORIZATION", d.NroAuthorization));
cmds[1].Parameters.Add(new OracleParameter(":DOSAGEKEY", d.DosageKey));
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert Dosage Succesfuly.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Dosage({1}).", DateTime.Now, ex.Message));
}
}
public void InsertTransaction(string Nit, string Buss)
{
string queryInsert = @"INSERT INTO PAYER(NIT,BUSINESSNAME)
VALUES(:NIT,:BUSINESSNAME)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Insert New Payer.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
cmds[0].CommandText = queryInsert;
cmds[0].Parameters.Add(new OracleParameter(":NIT", Nit));
cmds[0].Parameters.Add(new OracleParameter(":BUSINESSNAME", Buss));
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert Payer Succesfuly.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Payer({1}).", DateTime.Now, ex.Message));
}
}
public void InsertTransaction(List <Payment> u, double total, Payer p)
{
string queryInvoice = @"INSERT INTO INVOICE(INVOICEDATE,TOTAL,NROINVOICE,CONTROLCODE,IDPAYER)
VALUES(CURRENT_TIMESTAMP,:TOTAL,:NROINVOICE,:CONTROLCODE,:IDPAYER)";
string queryDetail = @"INSERT INTO DETAIL(DESCRIPTIONS,AMOUNT,IDPAYMENT,IDINVOICE)
VALUES(:DESCRIPTIONS,:AMOUNT,:IDPAYMENT,:IDINVOICE)";
try
{
int details = 0;
foreach (var d in u)
{
details++;
}
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Add Class.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
List <OracleParameter[]> parameters = new List <OracleParameter[]>();
DosageImpl dosageImpl = new DosageImpl();
Dosage dosage = dosageImpl.Get();
int numberinvoice = dosage.FinalNumber + 1;
string controlCode = ControlCode.generateControlCode(dosage.NroAuthorization.ToString(), numberinvoice.ToString(), p.Nit,
DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(),
total.ToString(), dosage.DosageKey);
int id = DBImplementation.GetIdentityFromTable("INVOICE");
DBImplementation.ResetIdentFromTable("INVOICE");
cmds[0].CommandText = queryInvoice;
cmds[0].Parameters.Add(new OracleParameter(":TOTAL", total));
cmds[0].Parameters.Add(new OracleParameter(":NROINVOICE", numberinvoice));
cmds[0].Parameters.Add(new OracleParameter(":CONTROLCODE", controlCode));
cmds[0].Parameters.Add(new OracleParameter(":IDPAYER", p.IdPayer));
cmds[0].Parameters.AddRange(parameters[0]);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Invoice Succesfully.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Class({1}).", DateTime.Now, ex.Message));
}
}
public void UpdateTransactionFirst(Grade g, double tot, double sum)
{
string queryGrade = @"UPDATE GRADE SET GRADE1 = :grade1,GRADE2= :grade2,GRADE3= :grade3,GRADE4= :grade4,
TESTGRADE= :testgrade,AVERAGE=:average,UPDATEDATE=CURRENT_TIMESTAMP WHERE GRADEID= :gradeid";
string queryAverage = @"UPDATE AVERAGEGRADETOTAL SET FIRSTTRIMESTER= :tot,TOTALAVERAGE = :suma WHERE IDAVERAGE= :idAverage";
try
{
int i = 1;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Update Grades.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[7];
cmds[0].CommandText = queryGrade;
parameters1[0] = new OracleParameter(":grade1", g.Grade1);
parameters1[1] = new OracleParameter(":grade2", g.Grade2);
parameters1[2] = new OracleParameter(":grade3", g.Grade3);
parameters1[3] = new OracleParameter(":grade4", g.Grade4);
parameters1[4] = new OracleParameter(":testgrade", g.Testgrade);
parameters1[5] = new OracleParameter(":average", g.Average);
parameters1[6] = new OracleParameter(":gradeid", g.IdGrado);
cmds[0].Parameters.AddRange(parameters1);
OracleParameter[] parameters2 = new OracleParameter[3];
cmds[1].CommandText = queryAverage;
parameters2[0] = new OracleParameter(":tot", tot);
parameters2[1] = new OracleParameter(":suma", sum);
parameters2[2] = new OracleParameter(":idAverage", g.IdAverage);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Dele Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Dele Class({1}).", DateTime.Now, ex.Message));
}
}
public void DelTeacherSubject(Class c)
{
string queryClass = @"UPDATE Class SET teacherid=21, status=1 WHERE idClass= :class";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Add Class to Teacher.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
OracleParameter[] parameters1 = new OracleParameter[1];
cmds[0].CommandText = queryClass;
parameters1[0] = new OracleParameter(":class", c.IdClass);
cmds[0].Parameters.AddRange(parameters1);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Add Class to Teacher" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Class to Teacher({1}).", DateTime.Now, ex.Message));
}
}
//DELETE
public void Delete(Teacher t)
{
string queryUser = @"DELETE Users WHERE PersonId= :PersonId";
string queryTeacher = @"DELETE Teacher WHERE PersonId= :PersonId";
string queryPerson = @"DELETE Person WHERE PersonId= :PersonId";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Delete teacher Insert.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(3);
OracleParameter[] parameters1 = new OracleParameter[1];
OracleParameter[] parameters2 = new OracleParameter[1];
OracleParameter[] parameters3 = new OracleParameter[1];
cmds[0].CommandText = queryTeacher;
parameters1[0] = new OracleParameter(":PersonId", t.PersonId);
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryUser;
parameters2[0] = new OracleParameter(":PersonId", t.PersonId);
cmds[1].Parameters.AddRange(parameters2);
cmds[2].CommandText = queryPerson;
parameters3[0] = new OracleParameter(":PersonId", t.PersonId);
cmds[2].Parameters.AddRange(parameters3);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Delete Teacher by" + Session.SessionCurrent.ToString() + ". Objeto enviado: {1} por el usuario #{2}.", DateTime.Now, t.Names + " " + t.LastName));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not delete Teacher({1}). Objeto: {2}.", DateTime.Now, ex.Message, t.Names + " " + t.LastName));
}
}
public void InsertTransaction(User u, Teacher t)
{
string queryPerson = @"INSERT INTO Person (names, lastName, secondLastName,
addres, phone, birthDate,gender,startDate,email,latitude,longitude,TownId,photo)
VALUES( :names, :lastName, :secondLastName, :address, :phone,
:birthDate, :gender, :startDate, :email , :latitude, :longitude, :TownId, :Photo)";
string queryUser = @"INSERT INTO USERACCOUNT (USERNAME, PASSWORD, ROLE,PERSONID)
VALUES( :userName, STANDARD_HASH(:password, 'MD5'), :role, :PersonId);";
string queryTeacher = @"INSERT INTO Teacher (Personid)
VALUES ( :Personid)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start teacher Insert.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(3);
OracleParameter[] parameters1 = new OracleParameter[13];
OracleParameter[] parameters2 = new OracleParameter[4];
OracleParameter[] parameters3 = new OracleParameter[1];
int id = DBImplementation.GetIdentityFromTable("Person");
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":names", t.Names);
parameters1[1] = new OracleParameter(":lastName", t.LastName);
if (t.SecondLastName != null)
{
parameters1[2] = new OracleParameter(":secondLastName", t.SecondLastName);
}
else
{
parameters1[2] = new OracleParameter(":secondLastName", DBNull.Value);
}
parameters1[3] = new OracleParameter(":address", t.Address);
parameters1[4] = new OracleParameter(":phone", t.Phone);
parameters1[5] = new OracleParameter(":birthDate", t.BirthDate);
parameters1[6] = new OracleParameter(":gender", t.Gender);
parameters1[7] = new OracleParameter(":startDate", t.StartDate);
parameters1[8] = new OracleParameter(":email", t.Email);
parameters1[9] = new OracleParameter(":latitude", t.Latitude);
parameters1[10] = new OracleParameter(":longitude", t.Longitude);
parameters1[11] = new OracleParameter(":TownId", t.TownId);
if (t.PathImage != null)
{
File.Copy(t.PathImage, DBImplementation.pathImages + id + ".png");
parameters1[12] = new OracleParameter(":Photo", id);
}
else
{
parameters1[12] = new OracleParameter(":Photo", "0");
}
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryUser;
parameters2[0] = new OracleParameter(":PersonId", id);
parameters2[1] = new OracleParameter(":userName", u.UserName);
parameters2[2] = new OracleParameter(":password", u.Password);
parameters2[3] = new OracleParameter(":role", t.Names);
cmds[1].Parameters.AddRange(parameters2);
cmds[2].CommandText = queryTeacher;
parameters3[0] = new OracleParameter(":Personid", id);
cmds[2].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert teacher by" + Session.SessionCurrent.ToString() + " Objeto enviado: {1} por el usuario #{2}.", DateTime.Now, u.UserName));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not insert Teacher({1}). Objeto: {2}.", DateTime.Now, ex.Message, u.UserName));
}
}
//UPDATE
public void UpdateTransaction(Student t)
{
string queryPerson = @"UPDATE Person SET names=
:names, lastName= :lastName, secondLastName= :secondLastName,
addres= :address, phone= :phone,email= :email,latitude= :latitude,
longitude= :longitude,TownId= :TownId,photo= :photo WHERE Personid= :Person
";
string queryStudent = @"UPDATE Student SET rudeNumber= :rude WHERE studentId
=(SELECT S.studentId FROM Student S INNER JOIN Person P ON P.Personid=S.PersonId WHERE P.Personid= :Person)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Update.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[11];
OracleParameter[] parameters2 = new OracleParameter[2];
int id = DBImplementation.GetIdentityFromTable("Person");
user = users(t.Names, t.LastName, id);
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":Person", t.PersonId);
parameters1[1] = new OracleParameter(":names", t.Names);
parameters1[2] = new OracleParameter(":lastName", t.LastName);
if (t.SecondLastName != null)
{
parameters1[3] = new OracleParameter(":secondLastName", t.SecondLastName);
}
else
{
parameters1[3] = new OracleParameter(":secondLastName", DBNull.Value);
}
parameters1[4] = new OracleParameter(":address", t.Address);
parameters1[5] = new OracleParameter(":phone", t.Phone);
parameters1[6] = new OracleParameter(":email", t.Email);
parameters1[7] = new OracleParameter(":latitude", t.Latitude);
parameters1[8] = new OracleParameter(":longitude", t.Longitude);
parameters1[9] = new OracleParameter(":TownId", t.TownId);
if (t.Photo == t.PathImage)
{
parameters1[10] = new OracleParameter(":photo", t.PersonId);
}
else
{
if (t.PathImage == DBImplementation.pathImages + "0.png")
{
parameters1[10] = new OracleParameter(":photo", 0);
}
else
{
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
File.Delete(DBImplementation.pathImages + t.PersonId + ".png");
File.Copy(t.PathImage, DBImplementation.pathImages + t.PersonId + ".png");
parameters1[0] = new OracleParameter(":photo", t.PersonId);
}
}
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryStudent;
parameters2[0] = new OracleParameter(":rude", t.RudeNumber);
parameters2[1] = new OracleParameter(":Person", t.PersonId);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Modif student by" + Session.SessionCurrent.ToString() + "/" + DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not modif Student({1}).", DateTime.Now, ex.Message));
}
}
public void InsertTransaction(Student t)
{
string queryPerson = @"INSERT INTO PERSON (NAMES,LASTNAME,SECONDLASTNAME,ADDRES, PHONE, BIRTHDATE,GENDER,EMAIL,LATITUDE,LONGITUDE,TOWNID,PHOTO)
VALUES( :names,:lastName,:secondLastName,:addres,:phone,:birthdate,:gender,:email,:latitude,:longitude,:townId,:photo)";
string queryUser = @"INSERT INTO USERACCOUNT (USERNAME, PASSWORD, ROLE,PERSONID)
VALUES( :userName, STANDARD_HASH(:password, 'MD5'), :role, :PersonId)";
string queryStudent = @"INSERT INTO Student(rudeNumber,PersonId,idCourse)
VALUES( :rude, :Personid, :Course)" ;
string queryAve = @"INSERT INTO AverageGradeTotal(firstTrimester)
VALUES(0) ";
string queryGrades = @"INSERT INTO Grade(studentid,idClass,idAverage)
VALUES( :student, :class, :average) ";
string queryFirst = @"INSERT INTO FirstTrimester(gradeId)
VALUES( :id)";
string querySecond = @"INSERT INTO SecondTrimester(gradeId)
VALUES( :id)";
string queryThird = @"INSERT INTO ThirdTrimester(gradeId)
VALUES( :id)";
try
{
int subjects = 0;
DataTable dt = new DataTable();
dt = SelectSubjects(t.IdCourse);
foreach (DataRow d in dt.Rows)
{
subjects++;
}
int i = 3;
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
List <OracleParameter[]> parameters = new List <OracleParameter[]>();
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Insert.", DateTime.Now));
int id = DBImplementation.GetIdentityFromTable("Person"); DBImplementation.ResetIdentFromTable("Person");
user = users(t.Names, t.LastName, id);
parameters.Add(new OracleParameter[12]);
cmds[0].CommandText = queryPerson;
parameters[0][0] = new OracleParameter(":names", t.Names);
parameters[0][1] = new OracleParameter(":lastName", t.LastName);
if (t.SecondLastName != null)
{
parameters[0][2] = new OracleParameter(":secondLastName", t.SecondLastName);
}
else
{
parameters[0][2] = new OracleParameter(":secondLastName", DBNull.Value);
}
parameters[0][3] = new OracleParameter(":addres", t.Address);
parameters[0][4] = new OracleParameter(":phone", t.Phone);
parameters[0][5] = new OracleParameter(":birthdate", t.BirthDate);
parameters[0][6] = new OracleParameter(":gender", t.Gender);
parameters[0][7] = new OracleParameter(":email", t.Email);
parameters[0][8] = new OracleParameter(":latitude", t.Latitude);
parameters[0][9] = new OracleParameter(":longitude", t.Longitude);
parameters[0][10] = new OracleParameter(":townId", t.TownId);
if (t.Photo != null)
{
File.Copy(t.Photo, DBImplementation.pathImages + id + ".png");
parameters[0][11] = new OracleParameter(":photo", id);
}
else
{
parameters[0][11] = new OracleParameter(":photo", "0");
}
cmds[0].Parameters.AddRange(parameters[0]);
cmds[1].CommandText = queryUser;
parameters.Add(new OracleParameter[4]);
parameters[1][0] = new OracleParameter(":userName", user.UserName);
parameters[1][1] = new OracleParameter(":password", user.Password);
parameters[1][2] = new OracleParameter(":role", "E");
parameters[1][3] = new OracleParameter(":PersonId", id);
cmds[1].Parameters.AddRange(parameters[1]);
/*
* cmds[2].CommandText = queryStudent;
* parameters.Add(new OracleParameter[3]);
*
* parameters[2][0] = new OracleParameter(":rude", t.RudeNumber);
* parameters[2][1] = new OracleParameter(":PersonId", id);
* parameters[2][2] = new OracleParameter(":Course", t.IdCourse);
* cmds[2].Parameters.AddRange(parameters[2]);
*
* int idstudent = DBImplementation.GetIdentityFromTable("STUDENT");
* DBImplementation.ResetIdentFromTable("STUDENT");
* int idgradetotal = DBImplementation.GetIdentityFromTable("GRADE");
* DBImplementation.ResetIdentFromTable("GRADE");
* int idgradeincrement = DBImplementation.GetIncementFromTable("GRADE");
* int idaveragetotal = DBImplementation.GetIdentityFromTable("AVERAGEGRADETOTAL");
* DBImplementation.ResetIdentFromTable("AVERAGEGRADETOTAL");
* int idaverageincrement = DBImplementation.GetIncementFromTable("AVERAGEGRADETOTAL");
* int idaverage = idaveragetotal - idaverageincrement;
* int idgrade = idgradetotal - idgradeincrement;
*
* foreach (DataRow d in dt.Rows)
* {
* idaverage = idaverage + idaverageincrement;
* cmds[i].CommandText = queryAve;
* parameters.Add(new OracleParameter[0]);
* i++;
* for (int k = 0; k < 3; k++)
* {
*
* switch (k)
* {
* case 0:
* idgrade = idgrade + idgradeincrement;
* cmds[i].CommandText = queryGrades;
* parameters.Add(new OracleParameter[3]);
* parameters[i][0] = new OracleParameter(":student", idstudent);
* parameters[i][1] = new OracleParameter(":class", d[0].ToString());
* parameters[i][2] = new OracleParameter(":average", idaverage);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
* cmds[i].CommandText = queryFirst;
* parameters.Add(new OracleParameter[1]);
* parameters[i][0] = new OracleParameter(":id", idgrade);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
* break;
* case 1:
* idgrade = idgrade + idgradeincrement;
* cmds[i].CommandText = queryGrades;
* parameters.Add(new OracleParameter[3]);
* parameters[i][0] = new OracleParameter(":student", idstudent);
* parameters[i][1] = new OracleParameter(":class", d[0].ToString());
* parameters[i][2] = new OracleParameter(":average", idaverage);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
* cmds[i].CommandText = querySecond;
* parameters.Add(new OracleParameter[1]);
* parameters[i][0] = new OracleParameter(":id", idgrade);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
* break;
* case 2:
* idgrade = idgrade + idgradeincrement;
* cmds[i].CommandText = queryGrades;
* parameters.Add(new OracleParameter[3]);
* parameters[i][0] = new OracleParameter(":student", idstudent);
* parameters[i][1] = new OracleParameter(":class", d[0].ToString());
* parameters[i][2] = new OracleParameter(":average", idaverage);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
* cmds[i].CommandText = queryThird;
* parameters.Add(new OracleParameter[1]);
* parameters[i][0] = new OracleParameter(":id", idgrade);
* cmds[i].Parameters.AddRange(parameters[i]);
* i++;
*
* break;
* }
* }
*
* }
*/
DBImplementation.ExecuteNBasicCommand(cmds);
SendEmail(t.Email, user.UserName, user.Password);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Insert Succesfuly.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not insert Student({1}).", DateTime.Now, ex.Message));
}
}
//INSERT
public void InsertTransaction(int idsub, List <int> u, int count, int course)
{
string queryClass = @"INSERT INTO Class(idMatter,idCourse)
VALUES( :subject , :course)";
string queryClassSche = @"INSERT INTO ClassSchedules(idClass,schedulesid)
VALUES( :idclass, :idsche)";
string queryAve = @"INSERT INTO AverageGradeTotal(firstTrimester)
VALUES(0) ";
string queryGrades = @"INSERT INTO Grade(studentid,idClass,idAverage)
VALUES( :student, :class, :average) ";
string queryFirst = @"INSERT INTO FirstTrimester(gradeId)
VALUES( :id)";
string querySecond = @"INSERT INTO SecondTrimester(gradeId)
VALUES( :id)";
string queryThird = @"INSERT INTO ThirdTrimester(gradeId)
VALUES( :id)";
try
{
int reset;
int i = 1;
int idgradetotal = DBImplementation.GetIdentityFromTable("Grade");
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: 1.", DateTime.Now));
DBImplementation.ResetIdentFromTable("Grade");
int idgradeincrement = DBImplementation.GetIncementFromTable("Grade");
int idgrade = idgradetotal - idgradeincrement;
int idaveragetotal = DBImplementation.GetIdentityFromTable("AverageGradeTotal");
int idaverageincrement = DBImplementation.GetIncementFromTable("AverageGradeTotal");
DBImplementation.ResetIdentFromTable("AverageGradeTotal");
int idaverage = idaveragetotal - idaverageincrement;
int students = 0;
DataTable dt = new DataTable();
dt = SelectStudents(course);
foreach (DataRow d in dt.Rows)
{
students++;
}
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Add Class.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(count + (students * 7));
List <OracleParameter[]> parameters = new List <OracleParameter[]>();
int id = DBImplementation.GetIdentityFromTable("Class");
DBImplementation.ResetIdentFromTable("Class");
cmds[0].CommandText = queryClass;
parameters.Add(new OracleParameter[2]);
parameters[0][0] = new OracleParameter(":subject", idsub);
parameters[0][1] = new OracleParameter(":course", course);
cmds[0].Parameters.AddRange(parameters[0]);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
foreach (var lis in u)
{
cmds[i].CommandText = queryClassSche;
parameters.Add(new OracleParameter[2]);
parameters[i][0] = new OracleParameter(":idclass", id);
parameters[i][1] = new OracleParameter(":idsche", lis);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
}
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
foreach (DataRow d in dt.Rows)
{
idaverage = idaverage + idaverageincrement;
cmds[i].CommandText = queryAve;
parameters.Add(new OracleParameter[0]);
i++;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
for (int k = 0; k < 3; k++)
{
switch (k)
{
case 0:
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
idgrade = idgrade + idgradeincrement;
cmds[i].CommandText = queryGrades;
parameters.Add(new OracleParameter[3]);
parameters[i][0] = new OracleParameter(":student", d[0].ToString());
parameters[i][1] = new OracleParameter(":class", id);
parameters[i][2] = new OracleParameter(":average", idaverage);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
cmds[i].CommandText = queryFirst;
parameters.Add(new OracleParameter[1]);
parameters[i][0] = new OracleParameter(":id", idgrade);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
break;
case 1:
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
idgrade = idgrade + idgradeincrement;
cmds[i].CommandText = queryGrades;
parameters.Add(new OracleParameter[3]);
parameters[i][0] = new OracleParameter(":student", d[0].ToString());
parameters[i][1] = new OracleParameter(":class", id);
parameters[i][2] = new OracleParameter(":average", idaverage);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
cmds[i].CommandText = querySecond;
parameters.Add(new OracleParameter[1]);
parameters[i][0] = new OracleParameter(":id", idgrade);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
break;
case 2:
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: addclassforeach", DateTime.Now));
idgrade = idgrade + idgradeincrement;
cmds[i].CommandText = queryGrades;
parameters.Add(new OracleParameter[3]);
parameters[i][0] = new OracleParameter(":student", d[0].ToString());
parameters[i][1] = new OracleParameter(":class", id);
parameters[i][2] = new OracleParameter(":average", idaverage);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
cmds[i].CommandText = queryThird;
parameters.Add(new OracleParameter[1]);
parameters[i][0] = new OracleParameter(":id", idgrade);
cmds[i].Parameters.AddRange(parameters[i]);
i++;
break;
}
}
}
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Add Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Class({1}).", DateTime.Now, ex.Message));
}
}
public void InsertTransaction(Administrator t)
{
string queryPerson = @"INSERT INTO Person (names, lastName, secondLastName, addres, phone, birthDate,gender,email,latitude,longitude,TownId,photo)
VALUES( :names, :lastName, :secondLastName, :address, :phone, :birthDate, :gender, :email, :latitude, :longitude, :TownId, :Photo)";
string queryUser = @"INSERT INTO USERACCOUNT (USERNAME, PASSWORD, ROLE,PERSONID)
VALUES( :userName, STANDARD_HASH(:password, 'MD5'), :role, :PersonId);";
string queryAdmin = @"INSERT INTO ADMINISTRATIVE(POSITION,PROFESSION,SPECIALITY,PERSONID)
VALUES( :position, :profesion, :speciality , :Personid)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Insert.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(3);
OracleParameter[] parameters1 = new OracleParameter[12];
OracleParameter[] parameters2 = new OracleParameter[4];
OracleParameter[] parameters3 = new OracleParameter[4];
int id = DBImplementation.GetIdentityFromTable("Person");
user = users(t.Names, t.LastName, id);
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":names", id);
parameters1[1] = new OracleParameter(":lastName", t.LastName);
if (t.SecondLastName != null)
{
parameters1[2] = new OracleParameter(":secondLastName", t.SecondLastName);
}
else
{
parameters1[2] = new OracleParameter(":secondLastName", DBNull.Value);
}
parameters1[3] = new OracleParameter(":address", t.Address);
parameters1[4] = new OracleParameter(":phone", t.Phone);
parameters1[5] = new OracleParameter(":birthDate", t.BirthDate);
parameters1[6] = new OracleParameter(":gender", t.Gender);
parameters1[7] = new OracleParameter(":email", t.Email);
parameters1[8] = new OracleParameter(":latitude", t.Latitude);
parameters1[9] = new OracleParameter(":longitude", t.Longitude);
parameters1[10] = new OracleParameter(":TownId", t.TownId);
if (t.PathImage != null)
{
File.Copy(t.PathImage, DBImplementation.pathImages + id + ".png");
parameters1[11] = new OracleParameter(":Photo", id);
}
else
{
parameters1[11] = new OracleParameter(":Photo", "0");
}
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryUser;
parameters2[0] = new OracleParameter(":PersonId", id);
parameters2[1] = new OracleParameter(":userName", user.UserName);
parameters2[2] = new OracleParameter(":password", user.Password);
parameters2[3] = new OracleParameter(":role", user.Role);
cmds[1].Parameters.AddRange(parameters2);
cmds[2].CommandText = queryAdmin;
parameters3[0] = new OracleParameter(":Personid", id);
parameters3[1] = new OracleParameter(":position", t.Position);
parameters3[2] = new OracleParameter(":profesion", t.Profesion);
parameters3[3] = new OracleParameter(":speciality", t.Speciality);
cmds[2].Parameters.AddRange(parameters3);
DBImplementation.ExecuteNBasicCommand(cmds);
SendEmail(t.Email, user.UserName, user.Password);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert Admin by" + Session.SessionCurrent.ToString() + " Object Send: ({1})", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not insert Admin({1}).", DateTime.Now, ex.Message));
}
}