예제 #1
0
        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));
            }
        }
예제 #2
0
        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));
            }
        }
예제 #3
0
        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));
            }
        }
예제 #4
0
        //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));
            }
        }
예제 #5
0
        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));
            }
        }
예제 #6
0
        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));
            }
        }
예제 #7
0
        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));
            }
        }
예제 #8
0
        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));
            }
        }
예제 #9
0
        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));
            }
        }
예제 #10
0
        //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));
            }
        }
예제 #11
0
        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));
            }
        }
예제 #12
0
        //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));
            }
        }
예제 #13
0
        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));
            }
        }
예제 #14
0
        //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));
            }
        }
예제 #15
0
        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));
            }
        }