public IEnumerable <ScanLog> GetAllScansLog()
{
Scan scan = null;
string selectCommand = @" SELECT * FROM ScanLog ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
yield return(new ScanLog((int)reader.GetInt32(reader.GetOrdinal("artcode")),
(int)reader.GetInt32(reader.GetOrdinal("id_gamma")),
(int)reader.GetInt32(reader.GetOrdinal("Qty")),
(string)reader.GetString(reader.GetOrdinal("Dt")),
(string)reader.GetString(reader.GetOrdinal("Barcode")),
(AType)(int)reader.GetSqlInt32(reader.GetOrdinal("ActionType"))));
}
}
}
}
}
}
public Audiencia BuscarPorID(Int64 _id)
{
Audiencia a = null;
try
{
String SQL = String.Format("SELECT * FROM audiencia WHERE id_audiencia = {0} ", _id);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
if (data.Read())
{
a = new Audiencia();
a.Id_audiencia = data.GetInt32(0);
a.Advogado.Nome_adv = data.GetString(1);
a.Cliente.Nome_cli = data.GetString(2);
a.Processo.Id_processo = data.GetInt32(3);
a.Data_audiencia = data.GetDate(4);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(a);
}
//Buscar todos os laudos no banco de dados
public List <Prontuario> BuscarTodosProntuarios()
{
List <Prontuario> Listaprontuarios = new List <Prontuario>();
try
{
string sql = string.Format("SELECT Id, IdReceituario, IdLaudo, IdConsulta, DataHistorico, Inf FROM Prontuario");
SqlCeDataReader data = BancodeDados.ExecutarSelect(sql);
while (data.Read())
{
Prontuario item = new Prontuario();
item.Id = data.GetInt32(0);
item.Idreceituario = data.GetInt32(1);
item.Idlaudo = data.GetInt32(2);
item.Idconsulta = data.GetInt32(3);
item.Datahistorico = data.GetString(4);
item.Informacao = data.GetString(5);
Listaprontuarios.Add(item);
}
data.Close();
BancodeDados.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(Listaprontuarios);
}
public IEnumerable <Scan> GetAllScans()
{
Scan scan = null;
string selectCommand = @" SELECT * FROM scan ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
yield return(new Scan((int)reader.GetInt32(reader.GetOrdinal("artcode")),
(int)reader.GetInt32(reader.GetOrdinal("id_gamma")),
(int)reader.GetInt32(reader.GetOrdinal("qty")))
{
Dt = reader.GetDateTime(reader.GetOrdinal("dt")),
StartDt = reader.GetString(reader.GetOrdinal("StartDt")),
EndDt = reader.GetString(reader.GetOrdinal("EndDt")),
});
}
}
}
}
}
}
public static void SetTDispositivo(TDispositivo dispositivo, SqlCeDataReader dr, SqlCeConnection conn)
{
dispositivo.DispositivoId = dr.GetInt32(0);
dispositivo.Nombre = dr.GetString(1);
dispositivo.Empresa = dr.GetString(2);
dispositivo.Instalacion = GetTInstalacion(dr.GetInt32(3), conn);
dispositivo.Tipo = GetTTipoDispositivo(dr.GetInt32(4), conn);
dispositivo.Funcion = dr.GetString(5);
dispositivo.Estado = dr.GetString(6);
dispositivo.CodBarras = dr.GetString(7);
if (dr[14] != DBNull.Value)
{
dispositivo.Posicion = dr.GetString(14);
}
if (dr[8] != DBNull.Value)
{
dispositivo.FechaCaducidad = dr.GetDateTime(8);
}
dispositivo.Caducado = dr.GetBoolean(9);
if (dr[10] != DBNull.Value)
{
dispositivo.FechaBaja = dr.GetDateTime(10);
}
if (dr[11] != DBNull.Value)
{
dispositivo.Modelo = GetTModeloDispositivo(dr.GetInt32(11), conn);
}
}
//Buscar um unico laudo pela sua ID
public Prontuario BuscarProntuarioID(int _IdProntuario)
{
Prontuario prontuario = null;
try
{
string sql = string.Format("SELECT IdReceituario, IdLaudo, IdConsulta, DataHistorico, Inf FROM Prontuario WHERE Id={0}", _IdProntuario);
SqlCeDataReader data = BancodeDados.ExecutarSelect(sql);
while (data.Read())
{
prontuario = new Prontuario();
prontuario.Id = data.GetInt32(0);
prontuario.Idreceituario = data.GetInt32(1);
prontuario.Idlaudo = data.GetInt32(2);
prontuario.Idconsulta = data.GetInt32(3);
prontuario.Datahistorico = data.GetString(4);
prontuario.Informacao = data.GetString(5);
}
data.Close();
BancodeDados.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(prontuario);
}
// fill in comboboxes with options
private void prefillComboBox()
{
List <User> users = new List <User>();
using (SqlCeConnection con = new SqlCeConnection(conString))
{
con.Open();
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand("SELECT id, name, gender, age, height, weight FROM Users", con))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string gender = reader.GetString(2);
int age = reader.GetInt32(3);
int height = reader.GetInt32(4);
int weight = reader.GetInt32(5);
User user = new User(id, name, gender, age, height, weight);
users.Add(user);
}
}
}
this.cmBoxUsers.ItemsSource = users;
if (users.Count > 0)
{
this.cmBoxUsers.SelectedItem = users.Last();
}
}
//Buscar um unico laudo pela sua ID
public Consulta BuscarConsultaID(int _IdConsulta)
{
Consulta consulta = null;
try
{
string sql = string.Format("SELECT Id, IdPaciente, IdMedico, Motivo, HorarioAtendimento, Data, Status FROM Consulta WHERE Id={0}", _IdConsulta);
SqlCeDataReader data = BancodeDados.ExecutarSelect(sql);
while (data.Read())
{
consulta = new Consulta();
consulta.Id = data.GetInt32(0);
consulta.Idpaciente = data.GetInt32(1);
consulta.Idmedico = data.GetInt32(2);
consulta.Motivo = data.GetString(3);
consulta.Horario = data.GetString(4);
consulta.Data = data.GetString(5);
consulta.Status = data.GetString(6);
}
data.Close();
BancodeDados.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(consulta);
}
public static TUsuario GetTUsuario(int id, SqlCeConnection conn)
{
TUsuario usuario = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Usuario WHERE usuario_id = {0}", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
usuario = new TUsuario()
{
UsuarioId = dr.GetInt32(0),
Nombre = dr.GetString(1),
Login = dr.GetString(2),
Password = dr.GetString(3),
Grupo_Trabajo = dr.GetInt32(4),
Abm = dr.GetByte(5)
};
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(usuario);
}
private User getUserByID(int userId)
{
User user = null;
using (SqlCeConnection con = new SqlCeConnection(conString))
{
con.Open();
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand("SELECT id,name,gender,age,height,weight FROM Users WHERE id = @id", con))
{
com.Parameters.AddWithValue("@id", userId);
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string gender = reader.GetString(2);
int age = reader.GetInt32(3);
int height = reader.GetInt32(4);
int weight = reader.GetInt32(5);
user = new User(id, name, gender, age, height, weight);
}
}
}
return(user);
}
public Dictionary <Int32, Categoria> ListarTodos()
{
Dictionary <Int32, Categoria> mapaCategorias = new Dictionary <Int32, Categoria>();
try
{
String SQL = "SELECT * FROM Categoria;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Categoria o = new Categoria();
o.id = data.GetInt32(0);
o.codigo = data.GetInt32(1);
o.nome = data.GetString(2);
o.valor = data.GetInt32(3);
mapaCategorias.Add(o.id, o);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(mapaCategorias);
}
public Categoria BuscarPorid(Int32 _codigo)
{
Categoria o = null;
try
{
String SQL = "SELECT * FROM Categoria;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
o = new Categoria();
o.id = data.GetInt32(0);
o.codigo = data.GetInt32(1);
o.nome = data.GetString(2);
o.valor = data.GetInt32(3);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(o);
}
public List <Categoria> ListarCategoriaPorProduto(Int32 id)
{
List <Categoria> listaCategorias = new List <Categoria>();
try
{
String SQL = String.Format("SELECT * FROM Categoria WHERE id = {1}", id);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Categoria o = new Categoria();
o.id = data.GetInt32(0);
o.codigo = data.GetInt32(1);
o.nome = data.GetString(2);
o.valor = data.GetInt32(3);
listaCategorias.Add(o);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(listaCategorias);
}
public List <Audiencia> ListarTodos()
{
List <Audiencia> listaAudiencia = new List <Audiencia>();
try
{
String SQL = "SELECT * FROM audiencia;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Audiencia a = new Audiencia();
a.Id_audiencia = data.GetInt32(0);
a.Advogado.Nome_adv = data.GetString(1);
a.Cliente.Nome_cli = data.GetString(2);
a.Processo.Id_processo = data.GetInt32(3);
a.Data_audiencia = data.GetDate(4);
listaAudiencia.Add(a);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(listaAudiencia);
}
public static IList <TDatoRevision> GetTDatosRevision(TRevision revision, SqlCeConnection conn)
{
IList <TDatoRevision> datosRevision = new List <TDatoRevision>();
if (revision == null)
{
return(datosRevision);
}
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM DatosRevision WHERE revision_id = {0} ORDER BY posicion",
revision.RevisionId);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TDatoRevision datoRevision = new TDatoRevision()
{
DatosId = dr.GetInt32(0),
TRevision = GetTRevisionC(dr.GetInt32(1), conn),
Valor = dr.GetString(2),
Tipo = dr.GetString(3),
Posicion = dr.GetInt32(4),
Nombre = dr.GetString(5)
};
datosRevision.Add(datoRevision);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(datosRevision);
}
public List <Processo> ListarTodos()
{
List <Processo> listaProcesso = new List <Processo>();
try
{
String SQL = "SELECT * FROM processo;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Processo p = new Processo();
p.Id_processo = data.GetInt32(0);
p.Cliente.Nome_cli = data.GetString(1);
p.Advogado.Nome_adv = data.GetString(2);
p.Tipo_processo = data.GetInt32(3);
p.Situacao_processo = data.GetDate(4);
listaProcesso.Add(p);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(listaProcesso);
}
public static TDatoRevision GetTDatoRevision(int id, SqlCeConnection conn)
{
TDatoRevision datoRevision = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM DatosRevision WHERE datos_id = {0}", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
datoRevision = new TDatoRevision()
{
DatosId = dr.GetInt32(0),
TRevision = GetTRevision(dr.GetInt32(1), conn),
Valor = dr.GetString(2),
Tipo = dr.GetString(3),
Posicion = dr.GetInt32(4),
Nombre = dr.GetString(5)
};
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(datoRevision);
}
public void fillComboBoxLiaison()
{
this.comboBoxLiaison.Items.Clear();
beginConnection();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM liaison";
SqlCeDataReader rdr = cmd.ExecuteReader();
ComboboxLiasonItem item;
while (rdr.Read())
{
item = new ComboboxLiasonItem();
item.nom = rdr.GetString(1);
item.siteA = rdr.GetInt32(2);
item.siteB = rdr.GetInt32(3);
if (rdr.GetString(4) != null)
{
item.configRadio = rdr.GetString(4);
}
this.comboBoxLiaison.Items.Add(item);
}
rdr.Close();
endConnection();
}
/// <summary>
/// Returns editionID of give EditionGUID; will create a new entry into the editions table if unknown
/// </summary>
/// <param name="editionGuid">GUID of edition</param>
/// <returns>Returns editionID of given EditionGUID</returns>
private int GetEditionID(string editionGuid)
{
int editionID = -1;
lock (this.databaseSyncRoot)
{
this.selectEditionCommand.Parameters["@editionGuid"].Value = editionGuid;
SqlCeDataReader reader = this.selectEditionCommand.ExecuteReader();
if (!reader.Read())
{
// edition unknown, create new entry:
this.insertEditionCommand.Parameters["@editionGuid"].Value = editionGuid;
this.insertEditionCommand.ExecuteNonQuery();
// re-read to get newly created editionID
reader = this.selectEditionCommand.ExecuteReader();
if (reader.Read())
{
editionID = reader.GetInt32(reader.GetOrdinal("EditionID"));
}
}
else
{
editionID = reader.GetInt32(reader.GetOrdinal("EditionID"));
}
}
return(editionID);
}
public static TIncidenciaEvolucion GetTIncidenciaEvolucion(int id, SqlCeConnection conn)
{
TIncidenciaEvolucion tIncidenciaEvolucion = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM IncidenciaEvolucion WHERE incidencia_evolucion_id = {0} AND abm <> 2", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tIncidenciaEvolucion = new TIncidenciaEvolucion()
{
IncidenciaEvolucionId = dr.GetInt32(0),
Comentarios = dr.GetString(3),
Operativo = dr.GetBoolean(5),
Abm = dr.GetByte(6)
};
if (dr[2] != DBNull.Value)
{
tIncidenciaEvolucion.FechaEvolucion = dr.GetDateTime(2);
}
if (dr[4] != DBNull.Value)
{
tIncidenciaEvolucion.TUsuario = GetTUsuario(dr.GetInt32(4), conn);
}
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(tIncidenciaEvolucion);
}
//scans
public Tuple <LinkedList <int>, LinkedList <int>, LinkedList <String>, LinkedList <String>, LinkedList <DateTime> > Scans(String colName, String criterion)
{
LinkedList <int> scanID = new LinkedList <int>();
LinkedList <int> scanTypeID = new LinkedList <int>();
LinkedList <String> pointCloudFileReference = new LinkedList <String>();
LinkedList <String> description = new LinkedList <String>();
LinkedList <DateTime> timestamp = new LinkedList <DateTime>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT * FROM Scans WHERE @ColName LIKE @Criterion";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ColName", colName);
selectQuery.Parameters.Add("@Criterion", criterion);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
scanID.AddLast(reader.GetInt32(0));
scanTypeID.AddLast(reader.GetInt32(1));
pointCloudFileReference.AddLast(reader.GetString(2));
description.AddLast(reader.GetString(3));
timestamp.AddLast(Convert.ToDateTime(reader.GetDateTime(4).ToString()));
}
reader.Close();
return(Tuple.Create(scanID, scanTypeID, pointCloudFileReference, description, timestamp));
}
public static List <PTProcessInfo> ProcessInfoForDate(PTDate date)
{
List <PTProcessInfo> processInfoList = new List <PTProcessInfo>();
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM ProcessInfos WHERE DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PTProcessInfo processInfo;
processInfo.index = reader.GetInt32(0);
processInfo.name = reader.GetString(1);
processInfo.activeTime = new TimeSpan(0, reader.GetInt32(2), 0);
processInfoList.Add(processInfo);
}
}
finally
{
connection.Close();
}
return(processInfoList);
}
public Processo BuscarPorID(Int64 _id)
{
Processo p = null;
try
{
String SQL = String.Format("SELECT * FROM processo WHERE id_processo = {0} ", _id);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
if (data.Read())
{
Processo p = new Processo();
p.Id_processo = data.GetInt32(0);
p.Cliente.Nome_cli = data.GetString(1);
p.Advogado.Nome_adv = data.GetString(2);
p.Tipo_processo = data.GetInt32(3);
p.Situacao_processo = data.GetDate(4);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(p);
}
public List <Jogo> ListarJogosPorDesenvolvedora(Int64 _desenvolvedora_cnpj)
{
List <Jogo> listaJogos = new List <Jogo>();
try
{
String SQL = String.Format("SELECT * FROM jogo WHERE desenvolvedora_cnpj = {0};", _desenvolvedora_cnpj);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Jogo j = new Jogo();
j.Codigo = data.GetInt64(0);
j.Nome = data.GetString(1);
j.Franquia = data.GetString(2);
j.Lancamento = data.GetString(3);
j.Genero = data.GetInt32(4);
j.Midia = data.GetInt32(5);
j.Cnpj = data.GetInt64(6);
j.Numero = data.GetInt64(7);
listaJogos.Add(j);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(listaJogos);
}
public Jogo BuscarPorID(Int64 _codigo)
{
Jogo j = null;
try
{
String SQL = String.Format("SELECT * FROM jogo WHERE codigo = {0} ", _codigo);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
if (data.Read())
{
j.Codigo = data.GetInt64(0);
j.Nome = data.GetString(1);
j.Franquia = data.GetString(2);
j.Lancamento = data.GetString(3);
j.Genero = data.GetInt32(4);
j.Midia = data.GetInt32(5);
j.Cnpj = data.GetInt64(6);
j.Numero = data.GetInt64(7);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(j);
}
private static void AddToListColumns(ref List <Column> list, SqlCeDataReader dr)
{
if (!dr.GetString(0).StartsWith("__sys"))
{
list.Add(new Column
{
ColumnName = dr.GetString(0)
, IsNullable = (YesNoOption)Enum.Parse(typeof(YesNoOption), dr.GetString(1))
, DataType = dr.GetString(2)
, CharacterMaxLength = (dr.IsDBNull(3) ? 0 : dr.GetInt32(3))
, NumericPrecision = (dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr[4], CultureInfo.InvariantCulture))
#if V31
#else
, AutoIncrementBy = (dr.IsDBNull(5) ? 0 : Convert.ToInt64(dr[5], CultureInfo.InvariantCulture))
, AutoIncrementSeed = (dr.IsDBNull(6) ? 0 : Convert.ToInt64(dr[6], CultureInfo.InvariantCulture))
, AutoIncrementNext = (dr.IsDBNull(12) ? 0 : Convert.ToInt64(dr[12], CultureInfo.InvariantCulture))
#endif
, ColumnHasDefault = (dr.IsDBNull(7) ? false : dr.GetBoolean(7))
, ColumnDefault = (dr.IsDBNull(8) ? string.Empty : dr.GetString(8).Trim())
, RowGuidCol = (dr.IsDBNull(9) ? false : dr.GetInt32(9) == 378 || dr.GetInt32(9) == 282)
, NumericScale = (dr.IsDBNull(10) ? 0 : Convert.ToInt32(dr[10], CultureInfo.InvariantCulture))
, TableName = dr.GetString(11)
, Ordinal = dr.GetInt32(13)
});
}
}
public static PTProcessInfo GetInfoForProcessOnDate(string processName, PTDate date)
{
PTProcessInfo info = new PTProcessInfo(0, string.Empty, default(TimeSpan));
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM ProcessInfos WHERE Name=@name AND DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = processName;
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read())
{
info.index = reader.GetInt32(0);
info.name = reader.GetString(1);
info.activeTime = new TimeSpan(0, reader.GetInt32(2), 0);
}
}
finally
{
connection.Close();
}
return(info);
}
public static TPrograma GetTPrograma(int id, SqlCeConnection conn)
{
TPrograma programa = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Programa WHERE programa_id = {0}", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
programa = new TPrograma()
{
ProgramaId = dr.GetInt32(0),
FechaProgramada = dr.GetDateTime(1),
Usuario = GetTUsuario(dr.GetInt32(2), conn),
Estado = dr.GetString(3),
Comentarios = dr.GetString(4),
Abm = dr.GetByte(5)
};
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(programa);
}
public Armazem BuscarPorNome(Int32 _id)
{
Armazem o = null;
try
{
String SQL = "SELECT * FROM Armazem;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
o = new Armazem();
o.id = data.GetInt32(0);
o.codigo = data.GetInt32(1);
o.nome = data.GetString(2);
o.valor = data.GetInt32(3);
o.categoria = data.GetString(4);
o.deposito = data.GetInt32(5);
o.quantidade = data.GetInt32(7);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(o);
}
public static List <Movie> fetchAllMovies()
{
List <Movie> movies = new List <Movie>();
string sql =
@"SELECT m.id, m.title, m.year, m.genre_id, g.name
FROM movies AS m JOIN genres AS g ON m.genre_id = g.id";
SqlCeCommand command = new SqlCeCommand(sql, connection.Connection);
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string title = reader.GetString(1);
int year = reader.GetInt32(2);
int genreId = reader.GetInt32(3);
string genreName = reader.GetString(4);
Movie newMovie = new Movie(id, title, year, new Genre(genreId, genreName));
movies.Add(newMovie);
}
return(movies);
}
public static TEdificio GetEdificioFromDr(SqlCeDataReader dr)
{
TEdificio edif = new TEdificio();
edif.edificioId = dr.GetInt32(0);
edif.nombre = dr.GetString(1);
edif.grupoId = dr.GetInt32(2);
return edif;
}
public static TRondaPunto GetRondaPuntoFromDr(SqlCeDataReader dr)
{
TRondaPunto rp = new TRondaPunto();
rp.rondaPuntoId = dr.GetInt32(0);
rp.rondaId = dr.GetInt32(1);
rp.orden = dr.GetInt32(2);
rp.puntoId = dr.GetInt32(3);
return rp;
}
public static TAdministrador GetAdministradorFromDr(SqlCeDataReader dr)
{
TAdministrador adm = new TAdministrador();
adm.administradorId = dr.GetInt32(0);
adm.nombre = dr.GetString(1);
adm.login = dr.GetString(2);
adm.password = dr.GetString(3);
adm.email = dr.GetString(4);
adm.nivel = dr.GetInt32(5);
return adm;
}
public static TPunto GetPuntoFromDr(SqlCeDataReader dr)
{
TPunto p = new TPunto();
p.puntoId = dr.GetInt32(0);
p.nombre = dr.GetString(1);
p.edificioId = dr.GetInt32(2);
p.tag = dr.GetString(3);
p.cota = dr.GetString(4);
p.cubiculo = dr.GetString(5);
p.observaciones = dr.GetString(6);
return p;
}
public static TDescargaLinea GetDescargaLineaFromDr(SqlCeDataReader dr)
{
TDescargaLinea dl = new TDescargaLinea();
dl.descargaLineaId = dr.GetInt32(0);
dl.descargaId = dr.GetInt32(1);
dl.tag = dr.GetString(2);
dl.fechaHora = dr.GetDateTime(3);
dl.tipo = dr.GetString(4);
dl.tipoId = dr.GetInt32(5);
dl.nombre = dr.GetString(6);
dl.incidenciaId = dr.GetInt32(7);
dl.observaciones = dr.GetString(8);
return dl;
}
public static TVigilante GetVigilanteFromDr(SqlCeDataReader dr)
{
TVigilante vig = new TVigilante();
vig.vigilanteId = dr.GetInt32(0);
vig.nombre = dr.GetString(1);
return vig;
}
public static TGrupo GetGrupoFromDr(SqlCeDataReader dr)
{
TGrupo g = new TGrupo();
g.grupoId = dr.GetInt32(0);
g.nombre = dr.GetString(1);
return g;
}
public static TIncidencia GetIncidenciaFromDr(SqlCeDataReader dr)
{
TIncidencia inci = new TIncidencia();
inci.incidenciaId = dr.GetInt32(0);
inci.nombre = dr.GetString(1);
return inci;
}
public static TVigilante GetVigilanteFromDr(SqlCeDataReader dr)
{
TVigilante v = new TVigilante();
v.vigilanteId = dr.GetInt32(0);
v.nombre = dr.GetString(1);
v.tag = dr.GetString(2);
v.tagf = dr.GetString(3);
return v;
}
public static TRonda GetRondaFromDr(SqlCeDataReader dr)
{
TRonda r = new TRonda();
r.rondaId = dr.GetInt32(0);
r.nombre = dr.GetString(1);
r.tag = dr.GetString(2);
r.tagf = dr.GetString(3);
return r;
}
public static TRonda GetRondaFromDr(SqlCeDataReader dr)
{
TRonda r = new TRonda();
bool primero = true;
while (dr.Read())
{
if (primero)
{
r.rondaId = dr.GetInt32(0);
r.nombre = dr.GetString(1);
r.tag = dr.GetString(2);
r.tagf = dr.GetString(3);
r.RondasPuntos = new List<TRondaPunto>();
primero = false;
}
TRondaPunto rp = new TRondaPunto();
TPunto p = new TPunto();
TEdificio e = new TEdificio();
TGrupo g = new TGrupo();
rp.rondaPuntoId = dr.GetInt32(4);
rp.orden = dr.GetInt32(5);
p.puntoId = dr.GetInt32(6);
p.nombre = dr.GetString(7);
e.edificioId = dr.GetInt32(8);
p.tag = dr.GetString(9);
e.nombre = dr.GetString(10);
g.grupoId = dr.GetInt32(11);
g.nombre = dr.GetString(12);
p.cota = dr.GetString(13);
p.cubiculo = dr.GetString(14);
r.mintime = dr.GetInt32(15);
r.maxtime = dr.GetInt32(16);
p.csnmax = dr.GetInt32(17);
p.csnmargen = dr.GetInt32(18);
p.lastcontrol = dr.GetDateTime(19);
e.Grupo = g;
p.Edificio = e;
rp.Punto = p;
rp.Ronda = r;
r.RondasPuntos.Add(rp);
}
return r;
}
public static TPunto GetPuntoFromDr(SqlCeDataReader dr)
{
TGrupo g = new TGrupo();
TEdificio e = new TEdificio();
TPunto p = new TPunto();
p.puntoId = dr.GetInt32(0);
p.nombre = dr.GetString(1);
e.edificioId = dr.GetInt32(2);
p.tag = dr.GetString(3);
p.cota = dr.GetString(4);
p.cubiculo = dr.GetString(5);
p.observaciones = dr.GetString(6);
e.nombre = dr.GetString(7);
g.grupoId = dr.GetInt32(8);
g.nombre = dr.GetString(9);
p.csnmax = dr.GetInt32(10);
p.csnmargen = dr.GetInt32(11);
p.lastcontrol = dr.GetDateTime(12);
return p;
}
public static TTerminal GetTerminalFromDr(SqlCeDataReader dr)
{
TTerminal t = new TTerminal();
t.terminalId = dr.GetInt32(0);
t.numero = dr.GetString(1);
t.nombre = dr.GetString(2);
if (dr[3] != DBNull.Value)
t.fechaAlta = dr.GetDateTime(3);
if (dr[4] != DBNull.Value)
t.fechaBaja = dr.GetDateTime(4);
return t;
}
static JournalItem ReadOneJournalItem(SqlCeDataReader reader)
{
var journalItem = new JournalItem();
if (!reader.IsDBNull(reader.GetOrdinal("JournalItemType")))
journalItem.JournalItemType = (JournalItemType)reader.GetByte(reader.GetOrdinal("JournalItemType"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemDateTime")))
journalItem.SystemDateTime = reader.GetDateTime(reader.GetOrdinal("SystemDateTime"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceDateTime")))
journalItem.DeviceDateTime = reader.GetDateTime(reader.GetOrdinal("DeviceDateTime"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectUID")))
journalItem.ObjectUID = reader.GetGuid(reader.GetOrdinal("ObjectUID"));
if (!reader.IsDBNull(reader.GetOrdinal("Name")))
journalItem.Name = reader.GetString(reader.GetOrdinal("Name"));
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalItem.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectState")))
journalItem.ObjectState = reader.GetInt32(reader.GetOrdinal("ObjectState"));
if (!reader.IsDBNull(reader.GetOrdinal("GKObjectNo")))
journalItem.GKObjectNo = (ushort)reader.GetInt16(reader.GetOrdinal("GKObjectNo"));
if (!reader.IsDBNull(reader.GetOrdinal("GKIpAddress")))
journalItem.GKIpAddress = reader.GetString(reader.GetOrdinal("GKIpAddress"));
if (!reader.IsDBNull(reader.GetOrdinal("GKJournalRecordNo")))
journalItem.GKJournalRecordNo = reader.GetInt32(reader.GetOrdinal("GKJournalRecordNo"));
if (!reader.IsDBNull(reader.GetOrdinal("KAUNo")))
journalItem.ControllerAddress = (ushort)reader.GetInt32(reader.GetOrdinal("KAUNo"));
if (!reader.IsDBNull(reader.GetOrdinal("StateClass")))
journalItem.StateClass = (XStateClass)reader.GetByte(reader.GetOrdinal("StateClass"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalItem.UserName = reader.GetString(reader.GetOrdinal("UserName"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectStateClass")))
journalItem.ObjectStateClass = (XStateClass)reader.GetByte(reader.GetOrdinal("ObjectStateClass"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectName")))
journalItem.ObjectName = reader.GetString(reader.GetOrdinal("ObjectName"));
if (!reader.IsDBNull(reader.GetOrdinal("AdditionalDescription")))
journalItem.AdditionalDescription = reader.GetString(reader.GetOrdinal("AdditionalDescription"));
if (!reader.IsDBNull(reader.GetOrdinal("Subsystem")))
journalItem.SubsystemType = (XSubsystemType)reader.GetByte(reader.GetOrdinal("Subsystem"));
return journalItem;
}
private EntCredential DataReaderToObject(SqlCeDataReader reader)
{
EntCredential credential = new EntCredential();
credential.CredentialID = reader.GetInt32(reader.GetOrdinal("CredentialID"));
credential.Username = reader.GetString(reader.GetOrdinal("Username"));
credential.Password = reader.GetString(reader.GetOrdinal("Password"));
credential.CredentialName = reader.GetString(reader.GetOrdinal("CredentialName"));
return credential;
}
static JournalRecord ReadOneJournalRecord(SqlCeDataReader reader)
{
var journalRecord = new JournalRecord();
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalRecord.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("Detalization")))
journalRecord.Detalization = reader.GetString(reader.GetOrdinal("Detalization"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceCategory")))
journalRecord.DeviceCategory = reader.GetInt32(reader.GetOrdinal("DeviceCategory"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceDatabaseId")))
journalRecord.DeviceDatabaseId = reader.GetString(reader.GetOrdinal("DeviceDatabaseId"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceName")))
journalRecord.DeviceName = reader.GetString(reader.GetOrdinal("DeviceName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceTime")))
journalRecord.DeviceTime = reader.GetDateTime(reader.GetOrdinal("DeviceTime"));
if (!reader.IsDBNull(reader.GetOrdinal("OldId")))
journalRecord.OldId = reader.GetInt32(reader.GetOrdinal("OldId"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelDatabaseId")))
journalRecord.PanelDatabaseId = reader.GetString(reader.GetOrdinal("PanelDatabaseId"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelName")))
journalRecord.PanelName = reader.GetString(reader.GetOrdinal("PanelName"));
if (!reader.IsDBNull(reader.GetOrdinal("StateType")))
journalRecord.StateType = (StateType)reader.GetInt32(reader.GetOrdinal("StateType"));
if (!reader.IsDBNull(reader.GetOrdinal("SubsystemType")))
journalRecord.SubsystemType = (SubsystemType)reader.GetInt32(reader.GetOrdinal("SubsystemType"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemTime")))
journalRecord.SystemTime = reader.GetDateTime(reader.GetOrdinal("SystemTime"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalRecord.User = reader.GetString(reader.GetOrdinal("UserName"));
if (!reader.IsDBNull(reader.GetOrdinal("ZoneName")))
journalRecord.ZoneName = reader.GetString(reader.GetOrdinal("ZoneName"));
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
journalRecord.No = reader.GetInt32(reader.GetOrdinal("Id"));
return journalRecord;
}
private object DataReaderToObject(SqlCeDataReader reader, EntityType entity)
{
object entityObject = null;
switch (entity)
{
case EntityType.Processor:
entityObject = new EntProcessor();
break;
case EntityType.OS:
entityObject = new EntOS();
break;
case EntityType.Bios:
entityObject = new EntBios();
break;
case EntityType.MotherBoard:
entityObject = new EntMotherBoard();
break;
case EntityType.Disk:
entityObject = new EntDisk();
break;
case EntityType.Memory:
entityObject = new EntMemory();
break;
case EntityType.LogicalDrive:
entityObject = new EntLogicalDrive();
break;
case EntityType.CDRom:
entityObject = new EntCDRom();
break;
case EntityType.Video:
entityObject = new EntVideo();
break;
case EntityType.Multimedia:
entityObject = new EntMultimedia();
break;
case EntityType.Monitor:
entityObject = new EntMonitor();
break;
case EntityType.Share:
entityObject = new EntShare();
break;
case EntityType.StartUp:
entityObject = new EntStartUp();
break;
case EntityType.Hotfix:
entityObject = new EntHotfixes();
break;
case EntityType.Processes:
entityObject = new EntProcesses();
break;
case EntityType.Softwares:
entityObject = new EntSoftwares();
break;
case EntityType.Services:
entityObject = new EntServices();
break;
case EntityType.IPRoutes:
entityObject = new EntIPRoutes();
break;
case EntityType.EnvironmentVar:
entityObject = new EntEnvironmentVars();
break;
case EntityType.Computer:
entityObject = new EntComputer();
break;
case EntityType.Printer:
entityObject = new EntPrinter();
break;
case EntityType.UserGroup:
entityObject = new EntUserGroups();
break;
case EntityType.NetworkAdapter:
entityObject = new EntNetworkAdapter();
break;
}
Type t = entityObject.GetType();
PropertyInfo[] pi = t.GetProperties();
foreach (PropertyInfo prop in pi)
{
if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
continue;
switch (prop.PropertyType.Name)
{
case "String":
string strValue = reader.GetString(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, strValue, null);
break;
case "Int32":
int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, intValue, null);
break;
case "Double":
double dValue = reader.GetDouble(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, dValue, null);
break;
case "DateTime":
DateTime dtValue = DateTime.MinValue;
if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value)
{
dtValue = DateTime.MinValue;
}
else
{
dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name));
}
prop.SetValue(entityObject, dtValue, null);
break;
case "Boolean":
bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, bValue, null);
break;
}
}
return entityObject;
}
private EntScan DataReaderToObject(SqlCeDataReader reader)
{
EntScan scan = new EntScan();
scan.MachineID = reader.GetInt32(reader.GetOrdinal("MachineID"));
scan.ScanID = reader.GetInt32(reader.GetOrdinal("ScanID"));
scan.Date = reader.GetDateTime(reader.GetOrdinal("ScanDate"));
return scan;
}
// Loads member data
public void LoadMember(SqlCeDataReader reader)
{
//MemberID = Int32.Parse(reader["Id"].ToString());
MemberID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
CardNumber = reader.GetInt32(1);
}
LName = reader["LastName"].ToString();
FName = reader["FirstName"].ToString();
Sex = reader["Sex"].ToString();
if (!reader.IsDBNull(4))
{
DateOfBirth = reader.GetDateTime(4);
}
Street = reader["Street"].ToString();
Suburb = reader["Suburb"].ToString();
City = reader["City"].ToString();
if (!reader.IsDBNull(9))
{
PostalCode = reader.GetInt32(9);
}
HomePhone = reader["HomePhone"].ToString();
CellPhone = reader["CellPhone"].ToString();
Email = reader["email"].ToString();
Occupation = reader["Occupation"].ToString();
if (!reader.IsDBNull(16))
{
PersonalTrainer = reader.GetInt32(16);
}
//PersonalTrainer = reader["PersonalTrainer"].ToString();
Notes = reader["Notes"].ToString();
if (!reader.IsDBNull(15))
{
Image = (byte[])reader["Image"];
}
}
private EntDiscover DataReaderToObject(SqlCeDataReader reader)
{
EntDiscover machine = new EntDiscover();
machine.MachineID = reader.GetInt32(reader.GetOrdinal("MachineID"));
machine.IPAddr = reader.GetString(reader.GetOrdinal("IPAddress"));
machine.LastChecked = reader.GetDateTime(reader.GetOrdinal("LastChecked"));
machine.Discovered = reader.GetBoolean(reader.GetOrdinal("Discovered"));
machine.StatusMessage = reader.GetString(reader.GetOrdinal("StatusMessage"));
machine.DomainName = reader.GetString(reader.GetOrdinal("DomainName"));
machine.MachineName = reader.GetString(reader.GetOrdinal("MachineName"));
machine.CredentialID = reader.GetInt32(reader.GetOrdinal("CredentialID"));
return machine;
}
static FS2JournalItem ReadOneJournalItem(SqlCeDataReader reader)
{
var journalItem = new FS2JournalItem();
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
journalItem.No = reader.GetInt32(reader.GetOrdinal("Id"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemTime")))
journalItem.SystemTime = reader.GetDateTime(reader.GetOrdinal("SystemTime"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceTime")))
journalItem.DeviceTime = reader.GetDateTime(reader.GetOrdinal("DeviceTime"));
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalItem.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("Detalization")))
journalItem.Detalization = reader.GetString(reader.GetOrdinal("Detalization"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceName")))
journalItem.DeviceName = reader.GetString(reader.GetOrdinal("DeviceName"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelName")))
journalItem.PanelName = reader.GetString(reader.GetOrdinal("PanelName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceUID")))
journalItem.DeviceUID = reader.GetGuid(reader.GetOrdinal("DeviceUID"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelUID")))
journalItem.PanelUID = reader.GetGuid(reader.GetOrdinal("PanelUID"));
if (!reader.IsDBNull(reader.GetOrdinal("ZoneName")))
journalItem.ZoneName = reader.GetString(reader.GetOrdinal("ZoneName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceCategory")))
journalItem.DeviceCategory = reader.GetInt32(reader.GetOrdinal("DeviceCategory"));
if (!reader.IsDBNull(reader.GetOrdinal("StateType")))
journalItem.StateType = (StateType)reader.GetInt32(reader.GetOrdinal("StateType"));
if (!reader.IsDBNull(reader.GetOrdinal("SubsystemType")))
journalItem.SubsystemType = (SubsystemType)reader.GetInt32(reader.GetOrdinal("SubsystemType"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalItem.UserName = reader.GetString(reader.GetOrdinal("UserName"));
return journalItem;
}
