public static TEstado GetTEstadoCierre(SqlCeConnection conn)
{
TEstado te = null;
string sql = "SELECT * FROM Estado WHERE en_cierre = 1";
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
te = new TEstado();
te.EstadoId = dr.GetInt32(0);
te.Nombre = dr.GetString(1);
te.EnCierre = dr.GetBoolean(2);
te.EnApertura = dr.GetBoolean(3);
te.Abm = dr.GetByte(4);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(te);
}
private static BSP_Ticket DataReaderAObjeto(SqlCeDataReader rdrLector)
{
BSP_Ticket oBSP_Ticket = new BSP_Ticket();
oBSP_Ticket.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID"));
oBSP_Ticket.Billete = rdrLector.GetInt64(rdrLector.GetOrdinal("Billete"));
oBSP_Ticket.Tipo = rdrLector.GetString(rdrLector.GetOrdinal("Tipo"));
if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("FechaVenta")))
{
oBSP_Ticket.FechaVenta = rdrLector.GetDateTime(rdrLector.GetOrdinal("FechaVenta"));
}
oBSP_Ticket.TarContado = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarContado"));
oBSP_Ticket.TarCredito = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarCredito"));
oBSP_Ticket.IVA105 = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVA105"));
oBSP_Ticket.ComPorcentaje = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComPorcentaje"));
oBSP_Ticket.ComValor = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComValor"));
oBSP_Ticket.ComOver = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComOver"));
oBSP_Ticket.ComIVA = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComIVA"));
oBSP_Ticket.Total = rdrLector.GetDecimal(rdrLector.GetOrdinal("Total"));
oBSP_Ticket.Rg = rdrLector.GetBoolean(rdrLector.GetOrdinal("Rg")) ? BSP_Rg.Internacional : BSP_Rg.Doméstico;
oBSP_Ticket.Moneda = rdrLector.GetBoolean(rdrLector.GetOrdinal("Moneda")) ? Moneda.Dolar : Moneda.Peso;
oBSP_Ticket.Concepto = new Concepto {
ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdConcepto"))
};
oBSP_Ticket.Compania = new Compania {
ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdCompania"))
};
oBSP_Ticket.SemanaID = rdrLector.GetInt64(rdrLector.GetOrdinal("SemanaID"));
return(oBSP_Ticket);
}
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[12] != DBNull.Value)
{
dispositivo.Operativo = dr.GetBoolean(12);
}
//if (!CntSciTerminal.FechaNula(dispositivo.FechaBaja))
// dispositivo.FechaBaja = dr.GetDateTime(10);
if (dr[10] != DBNull.Value)
{
dispositivo.FechaBaja = dr.GetDateTime(10);
}
if (dr[11] != DBNull.Value)
{
dispositivo.Modelo = GetTModeloDispositivo(dr.GetInt32(11), conn);
}
dispositivo.Abm = dr.GetByte(15);
}
public void Openbaza()
{
string connectionString;
string fileName = "Baza.sdf";
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
if (File.Exists(fileName) == false)
{
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
cn.Open();
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE dane (typ nvarchar (7), kod nvarchar (15), nazwa nvarchar(100), stan nvarchar(10), cenazk nvarchar(20), cenasp nvarchar(20), vat nvarchar(5), devstat nvarchar(20), bad_cena bit, bad_stan bit, cenapolka numeric(10,3), zliczono numeric(10,3))", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE bufor (id int identity not null primary key, dokid int, kod nvarchar (15), nazwa nvarchar (100), cenazk nvarchar(10), ilosc numeric(10,3), stan nvarchar(10), cenasp nvarchar(10), vat nvarchar(10))", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE dok (id int identity not null primary key, nazwadok nvarchar (120), typ nvarchar(10), data datetime, send bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE opcje (id int identity not null primary key, transfer nvarchar (20), com nvarchar(20), ip nvarchar(20), ufile nvarchar (120), dfile nvarchar(120), bdll nvarchar(50), bflag bit, ipflag bit, port int, skaner nvarchar(120), licence nvarchar(40), devstat nvarchar(20))", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("INSERT INTO opcje (transfer, com, ip, ufile, dfile, bdll, bflag, ipflag, port, skaner, licence) VALUES ('bluetooth', 'COM5', '0.0.0.0', '\\Inwent.imp', '\\Inwent.exp', 'MSStack', 0, 0, 8790, '\\folder\\program_czytnika.exe', '0000000000')", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE edihead (id int identity not null primary key, FileName nvarchar(40), TypPolskichLiter nvarchar(20), TypDok nvarchar (20), NrDok nvarchar(30), Data nvarchar(30), DataRealizacji nvarchar (30), Magazyn nvarchar(30), SposobPlatn nvarchar(10), TerminPlatn nvarchar(10), IndeksCentralny nvarchar(10), NazwaWystawcy nvarchar(120), AdresWystawcy nvarchar(120), KodWystawcy nvarchar(120), MiastoWystawcy nvarchar(120), UlicaWystawcy nvarchar(120), NIPWystawcy nvarchar(120), BankWystawcy nvarchar(120), KontoWystawcy nvarchar(120), TelefonWystawcy nvarchar(30), NrWystawcyWSieciSklepow nvarchar(20), NazwaOdbiorcy nvarchar(120), AdresOdbiorcy nvarchar(120), KodOdbiorcy nvarchar(20), MiastoOdbiorcy nvarchar(120), UlicaOdbiorcy nvarchar(120), NIPOdbiorcy nvarchar(120), BankOdbiorcy nvarchar(120), KontoOdbiorcy nvarchar(120), TelefonOdbiorcy nvarchar(120), NrOdbiorcyWSieciSklepow nvarchar(20), DoZaplaty nvarchar(20), status nvarchar(20), complete bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE edibody (id int identity not null primary key, NrDok nvarchar(30), Nazwa nvarchar (120), kod nvarchar(20), Vat nvarchar(7), Jm nvarchar (7), Asortyment nvarchar(120), Sww nvarchar(30), PKWiU nvarchar(30), Ilosc nvarchar(10), Cena nvarchar(10), Wartosc nvarchar(10), IleWOpak nvarchar(10), CenaSp nvarchar(10), status nvarchar(20), complete bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE ediend (id int identity not null primary key, NrDok nvarchar(30), Vat nvarchar (20), SumaNet nvarchar(20), SumaVat nvarchar(20), status nvarchar(20), complete bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE fedihead (id int identity not null primary key, ehid int, FileName nvarchar(40), TypPolskichLiter nvarchar(20), TypDok nvarchar (20), NrDok nvarchar(30), Data nvarchar(30), DataRealizacji nvarchar (30), Magazyn nvarchar(30), SposobPlatn nvarchar(10), TerminPlatn nvarchar(10), IndeksCentralny nvarchar(10), NazwaWystawcy nvarchar(120), AdresWystawcy nvarchar(120), KodWystawcy nvarchar(120), MiastoWystawcy nvarchar(120), UlicaWystawcy nvarchar(120), NIPWystawcy nvarchar(120), BankWystawcy nvarchar(120), KontoWystawcy nvarchar(120), TelefonWystawcy nvarchar(30), NrWystawcyWSieciSklepow nvarchar(20), NazwaOdbiorcy nvarchar(120), AdresOdbiorcy nvarchar(120), KodOdbiorcy nvarchar(20), MiastoOdbiorcy nvarchar(120), UlicaOdbiorcy nvarchar(120), NIPOdbiorcy nvarchar(120), BankOdbiorcy nvarchar(120), KontoOdbiorcy nvarchar(120), TelefonOdbiorcy nvarchar(120), NrOdbiorcyWSieciSklepow nvarchar(20), DoZaplaty nvarchar(20), status nvarchar (20), complete bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE fedibody (id int identity not null primary key, ebid int, NrDok nvarchar(30), Nazwa nvarchar (120), kod nvarchar(20), Vat nvarchar(7), Jm nvarchar (7), Asortyment nvarchar(120), Sww nvarchar(30), PKWiU nvarchar(30), Wymagane nvarchar(10), Ilosc nvarchar(10), Cena nvarchar(10), Wartosc nvarchar(10), IleWOpak nvarchar(10), CenaSp nvarchar(10), status nvarchar, complete bit)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("CREATE TABLE fediend (id int identity not null primary key, eeid int, NrDok nvarchar(30), Vat nvarchar (20), SumaNet nvarchar(20), SumaVat nvarchar(20), status nvarchar(20), complete bit)", cn);
cmd.ExecuteNonQuery();
cn.Close();
}
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
transfer = dr.GetString(1);
com = dr.GetString(2);
ip = dr.GetString(3);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bdll = dr.GetString(6);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
port = dr.GetInt32(9);
skaner = dr.GetString(10);
}
cn.Close();
}
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[12] != DBNull.Value)
{
dispositivo.Operativo = dr.GetBoolean(12);
}
//if (!CntSciTerminal.FechaNula(dispositivo.FechaBaja))
// dispositivo.FechaBaja = dr.GetDateTime(10);
if (dr[10] != DBNull.Value)
{
dispositivo.FechaBaja = dr.GetDateTime(10);
}
if (dr[11] != DBNull.Value)
{
dispositivo.Modelo = GetTModeloDispositivo(dr.GetInt32(11), conn);
}
dispositivo.Abm = dr.GetByte(15);
// nuevos campos vrs 2018.0.1.0
if (dr[16] != DBNull.Value)
{
dispositivo.NIndustria = dr.GetString(16);
}
if (dr[17] != DBNull.Value)
{
dispositivo.CargaKg = dr.GetDouble(17);
}
if (dr[18] != DBNull.Value)
{
dispositivo.Fabricante = GetTFabricante(dr.GetInt32(18), conn);
}
if (dr[19] != DBNull.Value)
{
dispositivo.FechaFabricacion = dr.GetDateTime(19);
}
if (dr[20] != DBNull.Value)
{
dispositivo.AgenteExtintor = GetTAgenteExtintor(dr.GetInt32(20), conn);
}
}
public Options()
{
InitializeComponent();
btassigned();
IPHostEntry host;
string localIP = "";
host = Dns.GetHostEntry(Dns.GetHostName());
foreach (IPAddress ip in host.AddressList)
{
if (ip.AddressFamily == AddressFamily.InterNetwork)
{
localIP = ip.ToString();
break;
}
}
ip_t.Text = localIP;
string connectionString;
// string fileName = "Baza.sdf";
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
typ_t.Text = dr.GetString(1);
ufile_t.Text = dr.GetString(4);
dfile_t.Text = dr.GetString(5);
runpicatchbt_cb.Checked = dr.GetBoolean(7);
runpicatchtcp_cb.Checked = dr.GetBoolean(8);
port_t.Text = Convert.ToString(dr.GetInt32(9));
skaner_t.Text = dr.GetString(10);
server_t.Text = dr.GetString(11);
login_t.Text = dr.GetString(12);
haslo_t.Text = dr.GetString(13);
baza_t.Text = dr.GetString(14);
checkBox1.Checked = dr.GetBoolean(15);
serverpcm = dr.GetString(11);
loginpcm = dr.GetString(12);
passwdpcm = dr.GetString(13);
bazapcm = dr.GetString(14);
dbflag = dr.GetBoolean(15);
}
cn.Close();
}
public override UserVO ReadRecord(SqlCeDataReader reader)
{
UserVO result = new UserVO();
result.Id = reader.GetInt32(reader.GetOrdinal(idColumnName));
result.Username = reader[userName].ToString();
result.Password = reader[userPassword].ToString();
result.IsActive = reader.GetBoolean(reader.GetOrdinal(userIsActive));
result.IsLoggedIn = reader.GetBoolean(reader.GetOrdinal(userIsLoggedIn));
return(result);
}
private void AddToListIndexes(ref List <Index> list, SqlCeDataReader dr)
{
list.Add(new Index
{
TableName = dr.GetString(0)
, IndexName = dr.GetString(1)
, Unique = dr.GetBoolean(3)
, Clustered = dr.GetBoolean(4)
, OrdinalPosition = dr.GetInt32(5)
, ColumnName = dr.GetString(6)
, SortOrder = (dr.GetInt16(7) == 1 ? SortOrderEnum.ASC : SortOrderEnum.DESC)
});
}
//Adicionado para implementar as opções de filtro
public Dictionary <Int64, Pessoa> BuscarListaFiltrada(String _filtros)
{
Dictionary <Int64, Pessoa> mapaPessoas = new Dictionary <Int64, Pessoa>();
try
{
String SQL = "SELECT * FROM pessoa WHERE ";
Int64 saida;
if (Int64.TryParse(_filtros, out saida))
{
SQL += String.Format("cpf = {0}", _filtros);
}
else
{
SQL += String.Format("nome LIKE '%{0}%'", _filtros);
}
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Pessoa p = new Pessoa();
p.Cpf = data.GetInt64(0);
p.Nome = data.GetString(1);
p.Tel = data.GetString(2);
p.Email = data.GetString(3);
p.TipoEndereco = data.GetInt32(4);
p.Logradouro = data.GetString(5);
p.Cidade = data.GetInt32(6);
p.Estado = data.GetInt32(7);
p.Genero = data.GetString(8);
p.EstadoCivil = data.GetString(9);
p.Filhos = data.GetBoolean(10);
p.Animais = data.GetBoolean(11);
p.Fumante = data.GetBoolean(12);
mapaPessoas.Add(p.Cpf, p);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(mapaPessoas);
}
public Dictionary <Int64, Pessoa> ListarTodos()
{
Dictionary <Int64, Pessoa> mapaPessoas = new Dictionary <Int64, Pessoa>();
try
{
String SQL = "SELECT * FROM pessoa;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Pessoa p = new Pessoa();
p.Cpf = data.GetInt64(0);
p.Nome = data.GetString(1);
p.Nascimento = data.GetString(2);
p.Celular = data.GetString(3);
p.TipoEnd = data.GetInt32(4);
p.Lograd = data.GetString(5);
p.Estado = data.GetInt32(6);
p.Cidade = data.GetInt32(7);
p.Sexo = data.GetString(8);
p.EstadoCivil = data.GetString(9);
p.Filhos = data.GetBoolean(10);
p.Animais = data.GetBoolean(11);
mapaPessoas.Add(p.Cpf, p);
}
data.Close();
BD.FecharConexao();
/*
* foreach (Pessoa p in listaPessoas)
* {
* EnderecoDAO dao = new EnderecoDAO();
*
* p.Enderecos = dao.BuscarEnderecosPorPessoa(p.Id);
* }
* */
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(mapaPessoas);
}
public PICATCHEDI(int ediflag, DataGridViewRow selected)
{
selectedRow = selected;
InitializeComponent();
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
transfer = dr.GetString(1);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
//port_t.Text = Convert.ToString(dr.GetInt32(9));
skaner = dr.GetString(10);
}
cn.Close();
if (skaner == "PC-MARKET")
{
if (ediflag == 0)
{
pcmarket_schema_import();
}
if (ediflag == 1)
{
pcmarket_schema_export();
}
if (ediflag == 2)
{
pcmarket_schema_export_rem();
}
if (ediflag == 3)
{
pcmarket_schema_export_ety();
}
}
}
public static TDispositivo GetTDispositivo(int id, SqlCeConnection conn)
{
TDispositivo dispositivo = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT * FROM Dispositivo WHERE dispositivo_id = {0}", id);
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dispositivo = new TDispositivo();
dispositivo.DispositivoId = dr.GetInt32(0);
dispositivo.Nombre = dr.GetString(1);
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);
}
dispositivo.Operativo = dr.GetBoolean(12);
dispositivo.Abm = dr.GetByte(15);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(dispositivo);
}
public static User CreateUserFromId(int id)
{
User user = null;
string command = "select id, login, firstname, lastname, isadmin from users where id=@id";
using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString))
{
SqlCeCommand com = new SqlCeCommand(command, con);
com.Parameters.Add("id", id);
try
{
con.Open();
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
user = new User(
(int)reader.GetDecimal(0), reader.GetString(1)
, reader.IsDBNull(2) ? "" : reader.GetString(2)
, reader.IsDBNull(3) ? "" : reader.GetString(3)
, reader.IsDBNull(4) ? false : reader.GetBoolean(4));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(user);
}
public Vaga BuscarPorID(Int64 _id)
{
Vaga e = null;
try
{
String SQL = String.Format("SELECT * FROM vaga WHERE id = {0} ", _id);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
if (data.Read())
{
e = new Vaga();
e.Id = data.GetInt64(0);
e.Status = data.GetBoolean(1);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(e);
}
public static IList <TIncidenciaEvolucion> GetTIncidenciaEvolucions(SqlCeConnection conn)
{
List <TIncidenciaEvolucion> incidenciaEvolucions = new List <TIncidenciaEvolucion>();
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM IncidenciaEvolucion";
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TIncidenciaEvolucion tincidenciaEvolucion = new TIncidenciaEvolucion()
{
IncidenciaEvolucionId = dr.GetInt32(0),
Incidencia = GetTIncidencia(dr.GetInt32(1), conn),
FechaEvolucion = dr.GetDateTime(2),
Comentarios = dr.GetString(3),
Usuario = GetTUsuario(dr.GetInt32(4), conn),
Operativo = dr.GetBoolean(5),
Abm = dr.GetByte(6)
};
if (tincidenciaEvolucion != null)
{
incidenciaEvolucions.Add(tincidenciaEvolucion);
}
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(incidenciaEvolucions);
}
Transaction ReaderToTransaction(SqlCeDataReader reader, Resource par)
{
return(new Transaction(reader.GetInt32(reader.GetOrdinal(COLUMN_ID)), reader.GetInt32(reader.GetOrdinal(COLUMN_PAR_ID)),
reader.GetBoolean(reader.GetOrdinal(COLUMN_IS_REMOVAL)), reader.GetInt32(reader.GetOrdinal(COLUMN_TRANS_NUM)), par,
reader.GetString(reader.GetOrdinal(COLUMN_COMMENT)), reader.GetString(reader.GetOrdinal(COLUMN_DATE_CREATED)),
reader.GetString(reader.GetOrdinal(COLUMN_SHORT_DATE))));
}
public List <Vaga> ListarTodos()
{
List <Vaga> listaVagas = new List <Vaga>();
try
{
String SQL = "SELECT * FROM vaga;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Vaga e = new Vaga();
e.Id = data.GetInt64(0);
e.Status = data.GetBoolean(1);
listaVagas.Add(e);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(listaVagas);
}
public static TIncidenciaEvolucion GetTIncidenciaEvolucion(int id, SqlCeConnection conn)
{
TIncidenciaEvolucion incidenciaEvolucion = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM IncidenciaEvolucion WHERE incidencia_evolucion_id = {0}",
id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
incidenciaEvolucion = new TIncidenciaEvolucion()
{
IncidenciaEvolucionId = dr.GetInt32(0),
Operativo = dr.GetBoolean(5),
Usuario = GetTUsuario(dr.GetInt32(4), conn),
Incidencia = GetTIncidencia(dr.GetInt32(1), conn),
Comentarios = dr.GetString(3)
};
// control de nulos en fechas.
if (dr[2] != DBNull.Value)
{
incidenciaEvolucion.FechaEvolucion = dr.GetDateTime(2);
}
}
if (!dr.IsClosed)
{
dr.Close();
}
}
// No profundizamos para evitar bucles.
return(incidenciaEvolucion);
}
//Get All Systems from the local database table
public List <RemoteSystem> getAllSystems()
{
this.connect();
List <RemoteSystem> systemsList = new List <RemoteSystem>();
String query = "Select * from " + TABLE_NAME;
SqlCeCommand cmd = new SqlCeCommand(query, this.conn);
SqlCeDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
RemoteSystem rSystem = new RemoteSystem();
if (!DBNull.Value.Equals(sqlReader[0]))
{
rSystem.setID(sqlReader.GetInt32(0));
}
if (!DBNull.Value.Equals(sqlReader[1]))
{
rSystem.setSystemName(sqlReader.GetString(1));
}
if (!DBNull.Value.Equals(sqlReader[2]))
{
rSystem.setDomain(sqlReader.GetString(2));
}
if (!DBNull.Value.Equals(sqlReader[3]))
{
rSystem.setIgnoreFlag(sqlReader.GetBoolean(3));
}
systemsList.Add(rSystem);
}
this.disconnect();
return(systemsList);
}
public UserEntity Validate(string userNm, string pwd)
{
UserEntity model = new UserEntity();
string sql = string.Format("SELECT * FROM [Users] where usernm='{0}' and pwd='{1}';", userNm, pwd);
SqlCeConnection _Conn = new SqlCeConnection(conn);
_Conn.Open();
SqlCeCommand cmd = new SqlCeCommand(sql, _Conn);
SqlCeDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
model.Id = rdr.GetInt32(0);
model.UserNm = rdr.GetString(1);
model.Pwd = rdr.GetString(2);
model.IsConfigured = rdr.GetBoolean(3);
if (!rdr.IsDBNull(4))
{
model.InstallID = rdr.GetGuid(4);
}
model.Location = rdr.IsDBNull(5) == true ? "" : rdr.GetString(5);
model.PinCode = rdr.IsDBNull(6) == true ? "" : rdr.GetString(6);
}
_Conn.Close();
return(model);
}
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 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);
}
public Dictionary <Int64, Model.Console> BuscarListaFiltrada(String _filtros)
{
try
{
Dictionary <Int64, Model.Console> listPessoas = new Dictionary <Int64, Model.Console>();
string SQL = "SELECT * FROM console WHERE ";
Int64 saida;
if (Int64.TryParse(_filtros, out saida))
{
SQL += String.Format("numero = {0}", _filtros);
}
else
{
SQL += String.Format("nome LIKE '%{0}%'", _filtros);
}
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Model.Console c = new Model.Console();
c.Numero = data.GetInt64(0);
c.Nome = data.GetString(1);
c.Fabricante = data.GetString(2);
c.Tipo = data.GetInt32(3);
c.Lancamento = data.GetString(4);
c.Geracao = data.GetInt32(5);
c.TravaFisica = data.GetBoolean(6);
c.TravaLogica = data.GetBoolean(7);
listPessoas.Add(c.Numero, c);
}
data.Close();
BD.FecharConexao();
return(listPessoas);
}
catch (Exception ex)
{
BD.FecharConexao();
throw new Exception(ex.Message);
}
}
public Dictionary <Int64, Model.Console> ListarTodos()
{
Dictionary <Int64, Model.Console> mapaConsoles = new Dictionary <Int64, Model.Console>();
try
{
String SQL = "SELECT * FROM console;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Model.Console c = new Model.Console();
c.Numero = data.GetInt64(0);
c.Nome = data.GetString(1);
c.Fabricante = data.GetString(2);
c.Tipo = data.GetInt32(3);
c.Lancamento = data.GetString(4);
c.Geracao = data.GetInt32(5);
c.TravaFisica = data.GetBoolean(6);
c.TravaLogica = data.GetBoolean(7);
AcessorioDAO daoAcessorio = new AcessorioDAO();
c.acessorios = daoAcessorio.ListarAcessoriosPorConsole(c.Numero);
ControleDAO daoControle = new ControleDAO();
c.controles = daoControle.ListarControlesPorConsole(c.Numero);
JogoDAO daoJogo = new JogoDAO();
c.jogos = daoJogo.ListarJogosPorConsole(c.Numero);
mapaConsoles.Add(c.Numero, c);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(mapaConsoles);
}
public Form10(int rownumber, int licence)
{
//
// Required for Windows Form Designer support
lic = licence;
InitializeComponent();
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM dok", cn);
DataTable table = new DataTable();
da.Fill(table);
index = table.Rows[rownumber][0].ToString();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT nazwadok, typ, data, send FROM dok WHERE id = ?";
cmd.Parameters.Add("@k", SqlDbType.Int);
cmd.Parameters["@k"].Value = int.Parse(index);
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(0);
comboBox1.Text = dr.GetString(1);
data_t.Text = dr.GetSqlDateTime(2).ToString();
if (dr.GetBoolean(3) == true)
{
send_c.Checked = dr.GetBoolean(3);
}
}
nazwa_t.Focus();
data_t.Text = System.DateTime.Now.ToString();
//
// TODO: Add any constructor code after InitializeComponent call
cn.Close();
//
}
public Form19(int rownumber, int editflag)
{
//
// Required for Windows Form Designer support
//
editflag2 = editflag;
InitializeComponent();
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
DataTable table = new DataTable();
if (editflag == 0)
{
da = new SqlCeDataAdapter("SELECT * FROM edihead WHERE complete = 0", cn);
da.Fill(table);
}
else if (editflag == 1)
{
da = new SqlCeDataAdapter("SELECT * FROM edihead WHERE complete = 1", cn);
da.Fill(table);
}
index = table.Rows[rownumber][0].ToString();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT NrDok, TypDok, Data, NazwaOdbiorcy, UlicaOdbiorcy, MiastoOdbiorcy, DataRealizacji, complete FROM edihead WHERE id = ?";
cmd.Parameters.Add("@k", SqlDbType.Int);
cmd.Parameters["@k"].Value = int.Parse(index);
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(3) + " " + dr.GetString(4) + " " + dr.GetString(5);
comboBox1.Text = dr.GetString(1);
data_t.Text = dr.GetString(2);
textBox2.Text = dr.GetString(6);
checkBox1.Checked = dr.GetBoolean(7);
textBox1.Text = dr.GetString(0);
}
nazwa_t.Focus();
//
// TODO: Add any constructor code after InitializeComponent call
cn.Close();
//
}
/// <summary>
/// Naplna objekt Udalost datami z DB
/// </summary>
/// <param name="row">data z DB</param>
public override void PopulateDto(SqlCeDataReader row)
{
this.Id = row.GetInt64(0);
this.User = row.GetString(1);
this.Nazov = row.GetString(2);
this.PopisUdalosti = row.GetString(3);
this.Miesto = row.GetString(4);
this.Cas = row.GetDateTime(5);
this.Priorita = row.GetBoolean(6);
}
protected bool?getBoolean(SqlCeDataReader reader, string columnName)
{
int index = reader.GetOrdinal(columnName);
if (!reader.IsDBNull(index))
{
return(reader.GetBoolean(index));
}
return(null);
}
public Form11(int rownumber, int licence)
{
lic = licence;
//
// Required for Windows Form Designer support
//
rownum = rownumber;
string connectionString;
string fileName = "Baza.sdf";
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
transfer = dr.GetString(1);
com = dr.GetString(2);
ip = dr.GetString(3);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bdll = dr.GetString(6);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
port = dr.GetInt32(9);
skaner = dr.GetString(10);
}
cn.Close();
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
//Buscar todos os especialistas no banco de dados
public List <Especialista> BuscarTodosEspecialistas()
{
List <Especialista> ListaEspecialista = new List <Especialista>();
try
{
string sql = string.Format("SELECT Id, Usuario, Senha, Cpf, Rg, Telefone, Celular, Email, Sexo, Status, Obs, Idade, AreaAtuacao, Especialidade1, Especialidade2, HoraAtendimentoIni, HoraAtendimentoFim, TipoDocumentoMedico, NumeroDocumento, Uf, Situacao, TipoInscricao, AtendimentoSegunda, AtendimentoTerca, AtendimentoQuarta, AtendimentoQuinta, AtendimentoSexta, AtendimentoSabado, AtendimentoDomingo, TipoPermissao, Nome, Profissao FROM Especialista");
SqlCeDataReader data = BancodeDados.ExecutarSelect(sql);
while (data.Read())
{
Especialista item = new Especialista();
item.Id = data.GetInt32(0);
item.Usuario = data.GetString(1);
item.Senha = data.GetString(2);
item.Cpf = data.GetString(3);
item.Rg = data.GetString(4);
item.Telefone = data.GetString(5);
item.Celular = data.GetString(6);
item.Email = data.GetString(7);
item.Sexo = data.GetString(8);
item.Status = data.GetString(9);
item.Obs = data.GetString(10);
item.Idade = data.GetString(11);
item.Areaatuacao = data.GetString(12);
item.Especialidade1 = data.GetString(13);
item.Especialidade2 = data.GetString(14);
item.Horaatendimentoini = data.GetString(15);
item.Horaatendimentofim = data.GetString(16);
item.Tipodocumentomedico = data.GetString(17);
item.Numerodocumento = data.GetString(18);
item.Uf = data.GetString(19);
item.Situacao = data.GetString(20);
item.Tipoinscricao = data.GetString(21);
item.Atendimentosegunda = data.GetBoolean(22);
item.Atendimentoterca = data.GetBoolean(23);
item.Atendimentoquarta = data.GetBoolean(24);
item.Atendimentoquinta = data.GetBoolean(25);
item.Atendimentosexta = data.GetBoolean(26);
item.Atendimentosabado = data.GetBoolean(27);
item.Atendimentodomingo = data.GetBoolean(28);
item.Tipopermissao = data.GetString(29);
item.Nomecompleto = data.GetString(30);
item.Profissao = data.GetString(31);
ListaEspecialista.Add(item);
}
data.Close();
BancodeDados.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(ListaEspecialista);
}
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;
}
//
// GetUserFromReader
// A helper function that takes the current row from the SqlCeDataReader
// and hydrates a MembershipUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(SqlCeDataReader reader)
{
if (string.IsNullOrWhiteSpace(reader.GetString(1))) return null;
object providerUserKey = null;
string strGooid = Guid.NewGuid().ToString();
if (reader.GetValue(0).ToString().Length > 0)
providerUserKey = new Guid(reader.GetValue(0).ToString());
else
providerUserKey = new Guid(strGooid);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.UtcNow : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.UtcNow : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.UtcNow : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.UtcNow : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.UtcNow : reader.GetDateTime(11);
MembershipUser u = new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
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;
}
