protected void btnFinalAc_Click(object sender, EventArgs e)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblAcesso values (" + Session["max"].ToString() + " , '" + txtUsuar.Text + "','" + txtSenha.Text + "', 2)");
Response.Redirect("obrigado.aspx");
}
public int confirmarAlterandoDAL(string val, string data, string servicos)
{
con = new ClasseConexao();
string[] info = val.Split(';');
string[] lstServicos = servicos.Split(',');
if (data.Equals(DateTime.Now.ToString().Split(' ')[0]))
{
con.executarSQL_NoQuery("UPDATE tblReserva SET idFuncionario = " + info[1] + ", codQuarto = " + info[2] + ", qtdeHospede = " + info[3] + ", dataEntrada = '" + info[4] + "', dataSaida = '" + info[5] + "', statusReserva = 's' WHERE codReserva = " + info[0]);
}
else
{
con.executarSQL_NoQuery("UPDATE tblReserva SET idFuncionario = " + info[1] + ", codQuarto = " + info[2] + ", qtdeHospede = " + info[3] + ", dataEntrada = '" + info[4] + "', dataSaida = '" + info[5] + " WHERE codReserva = " + info[0]);
}
con = new ClasseConexao();
con.executarSQL("DELETE tblReserva_Servico WHERE codReserva = " + info[0]);
for (int i = 0; i < lstServicos.Length; i++)
{
///if (lstServicos[i].Length == 6)
//{
con = new ClasseConexao();
con.executarSQL_NoQuery("INSERT INTO tblReserva_Servico VALUES(" + info[0] + ", " + lstServicos[i] + ")");
// }
}
con = new ClasseConexao();
return(con.executarSQL_NoQuery("UPDATE tblCliente SET nomeCliente = '" + info[6] + "', cpfCliente = '" + info[7] + "', dataNascimentoCliente = '" + info[8] + "', cepCliente = '" + info[9] + "', emailCliente = '" + info[10] + "', telefoneCliente = '" + info[11] + "' WHERE codCliente = " + info[12]));
}
protected void btnFinalBen_Click(object sender, EventArgs e)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("select max(DATA) from tblRegGlicemia where IDBENEFICIA = " + Session["id_ben"].ToString());
int resultado = (DateTime.Compare(Convert.ToDateTime(dt.Rows[0][0].ToString()), DateTime.Now));
if (resultado < 0)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("select max(DATA) from tblRegGlicemia where IDBENEFICIA = " + Session["id_ben"].ToString());
while ((DateTime.Compare(Convert.ToDateTime(dt.Rows[0][0].ToString()), DateTime.Now) < 0))
{
string data = dt.Rows[0][0].ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblRegGlicemia (IDBENEFICIA, DATA) values (" + Session["id_ben"].ToString() + ", " + "dateadd(day, 1, '" + Convert.ToDateTime(data).ToShortDateString() + "'))");
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("select max(DATA) from tblRegGlicemia where IDBENEFICIA = " + Session["id_ben"].ToString());
}
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("update tblRegGlicemia set " + ddlOca.SelectedValue + " = " + txtValor.Text + " where data = '" + txtData.Text + "' and idBeneficia = " + Session["id_ben"].ToString());
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("update tblRegGlicemia set " + ddlOca.SelectedValue + "insul" + " = " + txtInsul.Text + " where data = '" + txtData.Text + "' and idBeneficia = " + Session["id_ben"].ToString());
}
}
public DataTable getCheckOut()
{
conexao = new ClasseConexao();
String SQL = "EXEC usp_atualizarGridCheckOut";
data = conexao.executarSQL(SQL);
return(data.Tables[0]);
}
public void codigoCliente()
{
conexao = new ClasseConexao();
data = new DataSet();
string SQL = "Select MAX(codCliente) as codCliente from tblCliente";
data = conexao.executarSQL(SQL);
idCliente = data.Tables[0].Rows[0]["codCliente"].ToString();
}
public DataTable gerarGrafico_DAL()
{
xx = new ClasseConexao();
ds = new DataSet();
string sql = "SELECT q.descricao, COUNT(r.codReserva) As 'Numero de Reservas' FROM tblReserva r RIGHT JOIN tblQuarto q ON r.codQuarto = q.codQuarto AND MONTH(r.dataSaida) LIKE MONTH(GETDATE()) GROUP BY q.descricao";
ds = xx.executarSQL(sql);
return(ds.Tables[0]);
}
public DataTable gerarRelatorioDal()
{
xx = new ClasseConexao();
ds = new DataSet();
string sql = "Select tblFuncionario.idFuncionario, tblFuncionario.nomeFuncionario, COUNT(tblReserva.codReserva)AS Total FROM tblFuncionario INNER JOIN tblReserva ON tblFuncionario.idFuncionario = tblReserva.idFuncionario AND tblReserva.statusReserva LIKE 'V' GROUP BY tblFuncionario.idFuncionario, tblFuncionario.nomeFuncionario";
ds = xx.executarSQL(sql);
return(ds.Tables[0]);
}
public int confirmarCheckIn(string codFunc, string codReser)
{
try
{
con = new ClasseConexao();
con.executarSQL("exec usp_confirmarCheckIn " + codFunc + ", " + codReser);
return(1);
}
catch (Exception)
{
return(0);
}
}
public DataTable pesquisarReserva(string codReserva)
{
try
{
con = new ClasseConexao();
ds = new DataSet();
ds = con.executarSQL("SELECT c.*, r.*, s.* FROM tblCliente c INNER JOIN tblReserva r ON c.codCliente = r.codCliente AND r.codReserva = " + codReserva + " LEFT JOIN tblReserva_Servico s ON r.codReserva = s.codReserva");
return(ds.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public bool atualizarQuarto(String novoPreco, String NovaDescricao, String codigoQuarto)
{
try
{
conexao = new ClasseConexao();
String sql = "EXEC usp_alterarPrecoQuarto " + codigoQuarto + ",'" + NovaDescricao + "'," + novoPreco;
conexao.executarSQL(sql);
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool atualizarServico(String novoPreco, String novaDescricao, String codigoServico)
{
try
{
conexao = new ClasseConexao();
string sql = "EXEX usp_alterarPrecoServico " + codigoServico + "," + novoPreco + ",'" + novaDescricao + "'";
conexao.executarSQL(sql);
return(true);
}
catch (Exception)
{
return(false);
}
}
public DataTable pesquisar_Servico(String codReserva)
{
con = new ClasseConexao();
ds = new DataSet();
try
{
String sql = "SELECT * FROM tblReserva_Servico WHERE = codReserva =" + codReserva;
ds = con.executarSQL(sql);
return(ds.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public DataTable pesquisaQuarto(String pesqui)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select descricao from tblQuarto where codQuarto = '" + pesqui + "'";
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
protected void btnFinal_Click(object sender, EventArgs e)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblGeral values('" + sh.getNome() + "', '" + sh.getRG() + "', '" + sh.getCPF() + "','" + sh.getEmail() + "','" + sh.getEndereco() + "','" + sh.getTel() + "','" + sh.getCel() + "','" + sh.getDataNasc() + "','med')");
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("Select max(idGeral) from tblGeral where tipo='med'");
string max = (dt.Rows[0][0]).ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblMedicos values('" + max + "', '" + txtEspec.Text + "', '" + txtCRM.Text + "','" + txtLinkCurr.Text + "')");
Response.Redirect("obrigado.aspx");
}
public DataTable login(string login, string senha)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String SQL = " EXEC usp_loginFuncionario '" + login + "','" + senha + "'";
data = conexao.executarSQL(SQL);
}
catch (Exception)
{
return(null);
}
return(data.Tables[0]);
}
public DataTable totalServicos(String codReserva)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "EXEC usp_somaEstadia " + codReserva;
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public DataTable pesquisarReserva(String codReserva)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select codQuarto, dataEntrada, dataSaida, qtdeHospede, statusReserva from tblReserva where codReserva = " + codReserva;
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public DataTable pesquisarServico(String codReserva)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select * from tblReserva_Servico where codReserva = " + codReserva;
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public DataTable pesquisarCliente(String codReserva)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select * from tblCliente where codCliente in (Select codCliente from tblReserva where codReserva =" + codReserva + ")";
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public DataTable pesquisarQuarto(String codReserva)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select preco,numeroQuarto from tblQuarto where codQuarto in (Select codQuarto from tblReserva where codReserva = " + codReserva + ")";
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
protected void btnFinalVol_Click(object sender, EventArgs e)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblGeral values('" + sh.getNome() + "', '" + sh.getRG() + "', '" + sh.getCPF() + "','" + sh.getEmail() + "','" + sh.getEndereco() + "','" + sh.getTel() + "','" + sh.getCel() + "','" + sh.getDataNasc() + "','vol')");
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("Select max(idGeral) from tblGeral where tipo='vol'");
string max = (dt.Rows[0][0]).ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblVoluntarios values('" + max + "', 'Não definido', '" + txtMotiv.Text + "', '" + txtHab.Text + "')");
Response.Redirect("obrigado.aspx");
}
public DataTable pesquisarQuarto(String idQuarto)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String sql = "Select * from tblQuarto where codQuarto = " + idQuarto;
data = conexao.executarSQL(sql);
return(data.Tables[0]);
}
catch (Exception)
{
return(null);
}
}
public int reservaServico(String servico)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
data = conexao.executarSQL("Select MAX (codReserva) as codReserva from tblReserva");
String codReserva = data.Tables[0].Rows[0]["codReserva"].ToString();
if (servico.Equals("cafe"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100001");
}
if (servico.Equals("almoco"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100002");
}
if (servico.Equals("jantar"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100003");
}
if (servico.Equals("sauna"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100004");
}
if (servico.Equals("piscina"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100005");
}
if (servico.Equals("jogos"))
{
conexao = new ClasseConexao();
conexao.executarSQL("EXEC usp_servicosDaReserva " + codReserva + ",100006");
}
return(1);
}
catch (Exception)
{
return(0);
}
}
public int cadastrarReserva(String dados, String idFuncionarioDll)
{
try
{
String[] dadosReserva = dados.Split(',');
String reserva = "EXEC usp_inserirReserva " + idFuncionarioDll + ", " + idCliente + "," + dadosReserva[0] + "," + dadosReserva[1] + ",'" + dadosReserva[2] + "','" + dadosReserva[3] + "'";
conexao = new ClasseConexao();
if (conexao.executarSQL(reserva) != null)
{
return(1);
}
return(0);
}
catch (Exception)
{
return(0);
}
}
public int cadatrarCliente(String valores)
{
try
{
conexao = new ClasseConexao();
data = new DataSet();
String[] dadosCliente = valores.Split(',');
String aux = "'" + dadosCliente[0] + "', '" + dadosCliente[1] + "', '" + dadosCliente[2] + "', '" + dadosCliente[3] + "', '" + dadosCliente[4] + "', '" + dadosCliente[5] + "', '" + dadosCliente[6] + "', '" + dadosCliente[7] + "', '" + dadosCliente[8] + "', '" + dadosCliente[9] + "'";
String cliente = "EXEC usp_cadastrarCliente " + aux;
if (conexao.executarSQL(cliente) != null)
{
return(1);
}
return(0);
}
catch (Exception)
{
return(0);
}
}
protected void btnFinalBen_Click(object sender, EventArgs e)
{
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblGeral values('" + sh.getNome() + "', '" + sh.getRG() + "', '" + sh.getCPF() + "','" + sh.getEmail() + "','" + sh.getEndereco() + "','" + sh.getTel() + "','" + sh.getCel() + "','" + sh.getDataNasc() + "','ben')");
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("Select max(idGeral) from tblGeral where tipo='ben'");
Session["max"] = (dt.Rows[0][0]).ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblBeneficiados values('" + Session["max"].ToString() + "', '" + rblTipoDiabetes.SelectedValue + "', '" + txtAnoDiag.Text + "','" + txtConve.Text + "','" + txtMed.Text + "','" + txtEndo.Text + "', 'Site')");
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("select max(idBeneficiado) from tblBeneficiados");
string id_ben = dt.Rows[0][0].ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("insert into tblRegGlicemia (IDBENEFICIA, DATA) values (" + id_ben + ", " + "dateadd(day, -1, '" + DateTime.Now.ToShortDateString() + "'))");
Response.Redirect("cadacesso.aspx");
}
protected void btnLogin_Click(object sender, EventArgs e)
{
con = new ClasseConexao();
dt = new DataTable();
dt = con.executarSQL("select * from tblAcesso where usuario='" + txtUser.Text + "' and senha='" + txtSenha.Text + "'");
if (dt.Rows.Count > 0)
{
string idgeral = dt.Rows[0][1].ToString();
dt = new DataTable();
con = new ClasseConexao();
dt = con.executarSQL("select * from tblBeneficiados where idGeral =" + idgeral);
Session["id_ben"] = (dt.Rows[0][0]).ToString();
Session["nome_logon"] = txtUser.Text;
Session["logon"] = "1";
Response.Redirect("Logado.aspx");
}
else
{
Response.Write("<script language='javascript'> alert('Usuário ou senha inválido(s)');</script>");
txtUser.Text = "";
txtSenha.Text = "";
}
}