public static bool registerUser(string login, string password, string cpf)
{
bool result = false;
string sql = "INSERT INTO `ROS_PERSON`(`PER_NAME`,`PER_CPF`, `PER_RG_CTPS`) " +
"VALUES(\"" + login + "\", \"" + cpf + "\", NULL);";
try
{
using (DatabaseROS db = new DatabaseROS())
{
long personId = db.executeScalarSQL(sql);
if (personId == -1)
{
throw new ArgumentException("Problema ao cadastrar usuário com este CPF");
}
else
{
string userSql = "INSERT INTO `ROS_USER`(`USR_LOGIN`,`USR_PASSWORD`, `USR_PER_ID`, `USR_PRO_ID`) " +
"VALUES(\"" + login + "\", \"" + password + "\", " + personId + ", \"1\");";
db.executeCommandSQL(userSql);
}
}
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
private void CarregaDados()
{
DatabaseROS db = new DatabaseROS();
DataTable dt = new DataTable();
SQLiteConnection conn = null;
string sql = "SELECT COM_NAME EMPRESA, PER_NAME NOME " +
"FROM ROS_SUPPLIER " +
"JOIN ROS_COMPANY ON ROS_SUPPLIER.SUP_COM_ID = ROS_COMPANY.COM_ID " +
"JOIN ROS_PERSON ON ROS_SUPPLIER.SUP_PER_ID = ROS_PERSON.PER_ID ";
try
{
conn = new SQLiteConnection(db.strConn);
SQLiteDataAdapter da = new SQLiteDataAdapter(sql, db.strConn);
da.Fill(dt);
gridPessoas.DataSource = dt.DefaultView;
}
catch (Exception ex)
{
MessageBox.Show("Erro :" + ex.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
public static long findCertificate(string name)
{
string sql = "SELECT CET_ID, CET_DESCRIPTION FROM ROS_CERTIFICATE_TYPE WHERE CET_DESCRIPTION = \"" + name + "\"";
long retorno = -1;
try
{
using (DatabaseROS db = new DatabaseROS())
{
retorno = db.executeSelectScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(retorno);
}
public static long findCompany(string name)
{
string sql = "SELECT COM_ID FROM `ROS_COMPANY` WHERE COM_NAME=\"" + name + "\"";
long retorno = -1;
try
{
using (DatabaseROS db = new DatabaseROS())
{
retorno = db.executeSelectScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(retorno);
}
public static long registerPerson(string name, string cpf, string rg_ctps)
{
string sql = "INSERT INTO `ROS_PERSON`(`PER_NAME`,`PER_CPF`, `PER_RG_CTPS`) " +
"VALUES(\"" + name + "\", \"" + cpf + "\", \"" + rg_ctps + "\");";
long id;
try
{
using (DatabaseROS db = new DatabaseROS())
{
id = db.executeScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(id);
}
public static long registerSecurityCertificate(long supplierId, long certId, string date)
{
string sql = "INSERT INTO ROS_SECURITY_CERTIFICATE (SEC_SUP_ID, SEC_CET_ID, SEC_DATE)" +
"VALUES(" + supplierId + "," + certId + ",\"" + date + "\");";
long id;
try
{
using (DatabaseROS db = new DatabaseROS())
{
id = db.executeScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(id);
}
public static long registerSupplier(long personId, long companyId)
{
string sql = "INSERT INTO `ROS_SUPPLIER`(`SUP_PER_ID`, `SUP_COM_ID`) " +
"VALUES(" + personId + "," + companyId + ");";
long id;
try
{
using (DatabaseROS db = new DatabaseROS())
{
id = db.executeScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(id);
}
public static long registerCompany(string name)
{
string sql = "INSERT INTO `ROS_COMPANY`(`COM_NAME`) " +
"VALUES(\"" + name + "\");";
long id;
try
{
using (DatabaseROS db = new DatabaseROS())
{
id = db.executeScalarSQL(sql);
}
}
catch (Exception ex)
{
throw ex;
}
return(id);
}
private void TelaCadastroPessoa_Load(object sender, EventArgs e)
{
string sql = "SELECT CET_DESCRIPTION FROM ROS_CERTIFICATE_TYPE";
try
{
using (DatabaseROS db = new DatabaseROS())
{
SQLiteDataReader reader = db.executeReader(sql);
while (reader.Read())
{
cbCertificates.Items.Add(reader.GetString(0));
}
}
}
catch (Exception ex)
{
MessageBox.Show("Erro :" + ex.Message);
}
}
public static bool loginUser(string login, string password, out string profile)
{
bool senhaOK = false;
profile = "";
string sql = "SELECT USR_PASSWORD, PRO_DESCRIPTION " +
"FROM ROS_USER US " +
"INNER JOIN ROS_PROFILE PRO ON USR_PRO_ID = PRO_ID " +
"WHERE USR_LOGIN = \"" + login + "\"";
using (DatabaseROS db = new DatabaseROS())
{
SQLiteDataReader reader = db.executeReader(sql);
while (reader.Read())
{
senhaOK = CustomMD5.CompareMD5(password, reader.GetString(0));
profile = reader.GetString(1);
}
}
return(senhaOK);
}
private void btnSalvar_Click(object sender, EventArgs e)
{
string sql = "INSERT INTO `CERTIFICACOES`(`CERT_NOME`) " +
"VALUES(\"" + tbNome.Text + "\");";
try
{
using (DatabaseROS db = new DatabaseROS())
{
db.executeCommandSQL(sql);
}
if (DialogResult.OK == MessageBox.Show("Grau de instrução cadastrado com sucesso!", "Sucesso!", MessageBoxButtons.OK, MessageBoxIcon.Information))
{
Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Erro :" + ex.Message);
}
}