public Town GetID(string id, string id2, string id3)
{
Town a = null;
OracleCommand cmd = null;
string query = @"SELECT DISTINCT T.TownId FROM Town T INNER JOIN Province P ON T.ProvinceId=(SELECT ProvinceId FROM Province WHERE provinceName = :ProvinceName) INNER JOIN City C ON (SELECT CityId FROM Province WHERE provinceName= :ProvinceName)=(SELECT CityId FROM City WHERE CityName= :CityName) where t.townName= :townName";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[3];
parameters1[0] = new OracleParameter(":CityName", id);
parameters1[1] = new OracleParameter(":ProvinceName", id2);
parameters1[2] = new OracleParameter(":townName", id3);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Town(byte.Parse(dr[0].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Town GetPlace(byte id)
{
Town a = null;
OracleCommand cmd = null;
string query = @"SELECT TownId, townName,ProvinceId FROM Town WHERE TownId= :townName";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":townName", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Town(byte.Parse(dr[0].ToString()), dr[1].ToString(), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Person GetPass(string user)
{
Person a = null;
OracleCommand cmd = null;
string query = @"SELECT P.email,U.UserId FROM Users U INNER JOIN Person P ON P.Personid=U.Personid WHERE U.userName= :User";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":User", user);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Person(dr[0].ToString(),int.Parse(dr[1].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return a;
}
public City GetPlace(byte id)
{
City a = null;
OracleCommand cmd = null;
OracleParameter[] parameters1 = new OracleParameter[1];
string query = @"SELECT CityId, CityName,state FROM City WHERE CityId=:CityId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
parameters1[0] = new OracleParameter(":CityId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new City(byte.Parse(dr[0].ToString()), dr[1].ToString(), byte.Parse(dr[2].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Person Get(int id)
{
Person a = null;
OracleCommand cmd = null;
string query = @"SELECT P.Personid, P.names, P.lastName, COALESCE(P.secondLastName,''), P.addres, P.phone, P.birthDate,P.gender,P.status,P.registrationDate,NVL(P.updateDate,'01/01/1900'),P.startDate,NVL(P.finishDate,'01/01/1900'),P.email, P.latitude, P.longitude, P.TownId,P.photo FROM Person P INNER JOIN Users U ON U.PersonId=P.Personid WHERE U.UserId= :PersonId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":PersonId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Person(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), DateTime.Parse(dr[6].ToString()), dr[7].ToString(), byte.Parse(dr[8].ToString()), DateTime.Parse(dr[9].ToString()), DateTime.Parse(dr[10].ToString()), DateTime.Parse(dr[11].ToString()), DateTime.Parse(dr[12].ToString()), dr[13].ToString(), Convert.ToDouble(dr[14].ToString()), Convert.ToDouble(dr[15].ToString()), byte.Parse(dr[16].ToString()), dr[17].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return a;
}
//Get
public Horario Get(byte id)
{
Horario a = null;
OracleCommand cmd = null;
string query = @"SELECT timeTableId, hourStart, hourFinish, ISNULL(updateDate,0), status, registrationDate, day
FROM timetable
WHERE timeTableId = :timeTableId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":timeTableId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Horario(byte.Parse(dr[0].ToString()), DateTime.Parse(dr[1].ToString()), DateTime.Parse(dr[2].ToString()), DateTime.Parse(dr[3].ToString()), byte.Parse(dr[4].ToString()), DateTime.Parse(dr[5].ToString()), dr[6].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//GET
public Course Get(int id)
{
Course a = null;
OracleCommand cmd = null;
string query = @"SELECT idCourse,numberCourse,letterCourse,section FROM Course WHERE idCourse= :Course";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":Course", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Course(int.Parse(dr[0].ToString()), int.Parse(dr[1].ToString()), dr[2].ToString(), dr[3].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//GET
public Class Get(int id)
{
Class a = null;
OracleCommand cmd = null;
string query = @"SELECT C.idClass FROM Class C WHERE C.idClass = :classId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":classId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Class(int.Parse(dr[0].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Dosage Get()
{
Dosage a = new Dosage();
OracleCommand cmd = null;
string query = @"SELECT IDDOSAGE,NROAUTHORIZATION,DOSAGEKEY,COALESCE(FINALNUMBER,0) FROM DOSAGE WHERE STATUS>0";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a.IdDosage = int.Parse(dr[0].ToString());
a.NroAuthorization = long.Parse(dr[1].ToString());
a.DosageKey = dr[2].ToString();
a.FinalNumber = int.Parse(dr[2].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Payer Get(string id)
{
Payer a = new Payer();
OracleCommand cmd = null;
string query = @"SELECT IDPAYER ,NIT,BUSINESSNAME
FROM PAYER WHERE NIT=:NIT";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":NIT", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a.IdPayer = int.Parse(dr[0].ToString());
a.Nit = dr[1].ToString();
a.BusinessName = dr[2].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//GET
public Student Get(int id, int person)
{
Student a = null;
OracleCommand cmd = null;
string query = @"SELECT P.Personid
,P.names ,P.lastName
,P.secondLastName ,S.rudeNumber,P.email,P.gender,P.phone ,P.addres ,P.birthDate ,
P.status , P.registrationDate
,(NVL(TO_CHAR(P.updateDate),'1993-01-01')) ,(NVL(TO_CHAR(P.startDate),'1993-01-01')),(NVL(TO_CHAR(P.finishDate),'1993-01-01')) ,P.latitude,P.longitude,P.TownId,P.photo ,
S.studentId
FROM Person P left JOIN Student S ON S.PersonId=P.PersonId WHERE S.idCourse= :Course AND P.PersonId= :PersonId" ;
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[2];
parameters1[0] = new OracleParameter(":Course", id);
parameters1[1] = new OracleParameter(":PersonId", person);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Student(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[8].ToString(), dr[7].ToString(),
DateTime.Parse(dr[9].ToString()), dr[6].ToString(), byte.Parse(dr[10].ToString()), DateTime.Parse(dr[11].ToString()),
DateTime.Parse(dr[12].ToString()), DateTime.Parse(dr[13].ToString()), DateTime.Parse(dr[14].ToString()), dr[5].ToString(),
Convert.ToDouble(dr[15].ToString()), Convert.ToDouble(dr[16].ToString()), byte.Parse(dr[17].ToString()), dr[18].ToString(),
dr[4].ToString(), int.Parse(dr[19].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public Invoice Get(int id)
{
Invoice a = new Invoice();
OracleCommand cmd = null;
string query = @"SELECT S.STUDENTID, P.NAMES||' '||P.LASTNAME||' '||P.SECONDLASTNAME NAMES,
(SELECT COUNT(IDPAYMENT) FROM PAYMENT PA WHERE PA.STUDENTID=S.STUDENTID AND PA.STATUS>0) ""PENDING PAYMENTS""
FROM STUDENT S
INNER JOIN PERSON P ON P.PERSONID = S.PERSONID
INNER JOIN USERACCOUNT U ON U.PERSONID = P.PERSONID
WHERE U.STATUS > 0 AND S.STUDENTID =:STUDENTID";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":STUDENTID", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a.IdInvoide = int.Parse(dr[0].ToString());
a.BussinesName = dr[1].ToString();
a.IdPayer = int.Parse(dr[2].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//Get
public Teacher Get(int id)
{
Teacher a = null;
OracleCommand cmd = null;
string query = @"SELECT P.Personid, P.names, P.lastName,
COALESCE(P.secondLastName,''), P.addres, P.phone, P.birthDate,P.gender,
P.status,P.registrationDate,NVL(TO_CHAR(P.updateDate),'1900-01-01'),P.startDate,
NVL(TO_CHAR(P.finishDate),'1900-01-01'),P.email, P.latitude, P.longitude, P.TownId,
P.photo,T.teacherid FROM Person P
INNER JOIN Teacher T ON T.PersonId=P.Personid WHERE P.Personid = :PersonId";
OracleDataReader dr = null;
CultureInfo provider = CultureInfo.InvariantCulture;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":PersonId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Teacher(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString());
a.Id = int.Parse(dr[18].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}