public bool AlocarAluno(int aulaId, int alunoId)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query1 = "SELECT * FROM checkin WHERE FK_AULA_ID = " + aulaId + " AND FK_ALUNO_ID = " + alunoId;
DataTable dt = dao.result(query1);
if (dt.Rows.Count == 0)
{
string query2 = "INSERT INTO checkin (FK_AULA_ID, FK_ALUNO_ID) VALUES ('" + aulaId + "', '" + alunoId + "')";
dao.executeQuery(query2);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar alocar o aluno na aula:" + ex.Message);
}
finally
{
dao = null;
}
}
public bool dologin(string user, string password)
{
try
{
DataTable dt = new DataTable();
dao = new ConnectorDAO();
dao.connect();
string query = "SELECT NOME, PERMISSAO FROM usuarios WHERE EMAIL = '" + user + "' AND SENHA = '" + password + "'";
dt = dao.result(query);
Console.WriteLine("user = "******"user = " + password);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
}
public DataTable getAlunosMatriculados(int aulaId)
{
DataTable dtCheckin = this.getCheckins(aulaId);
string arr = "";
int i = 0;
while (i < dtCheckin.Rows.Count)
{
if (i == dtCheckin.Rows.Count - 1)
{
arr += dtCheckin.Rows[i]["ID"];
}
else
{
arr += dtCheckin.Rows[i]["ID"] + ",";
}
i++;
}
try
{
dao = new ConnectorDAO();
dao.connect();
string sql = "";
if (arr.Length > 0)
{
sql = "SELECT ID, NOME FROM alunos ";
sql += "WHERE ID NOT IN (" + arr + ")";
}
else
{
sql = "SELECT ID, NOME FROM alunos";
}
dtCheckin = dao.result(sql);
return(dtCheckin);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não foi possível listar os alunos: " + ex.Message);
}
}
public bool DesalocarAluno(int aulaId, int alunoId)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "DELETE FROM checkin WHERE FK_ALUNO_ID = " + alunoId + " AND FK_AULA_ID = " + aulaId;
dao.executeQuery(query);
return(true);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não foi possível desalocar o aluno:" + ex.Message);
}
}
public DataTable getUsuarios()
{
DataTable dt = new DataTable();
try
{
dao = new ConnectorDAO();
dao.connect();
dt = dao.result("SELECT ID, NOME, EMAIL, PERMISSAO FROM usuarios");
return(dt);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não foi possível listar os usuarios:" + ex.Message);
}
}
public void deleteUsuario(int usuarioId)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "DELETE FROM usuarios WHERE ID = " + usuarioId;
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar excluir o usuário:" + ex.Message);
}
finally
{
dao = null;
}
}
public void updateAula(AulasModel aula)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "UPDATE aulas SET TITULO = '" + aula.Titulo + "' WHERE ID = '" + aula.Id + "'";
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar atualizar a aula:" + ex.Message);
}
finally
{
dao = null;
}
}
public DataTable getAulas()
{
DataTable dt = new DataTable();
try
{
dao = new ConnectorDAO();
dao.connect();
dt = dao.result("SELECT ID, TITULO FROM aulas ORDER BY TITULO");
return(dt);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não foi possível listar as aulas:" + ex.Message);
}
}
public void deleteAula(int aulaId)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "DELETE FROM aulas WHERE ID = " + aulaId;
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar excluir a aula:" + ex.Message);
}
finally
{
dao = null;
}
}
public DataTable getAlunos()
{
DataTable dt = new DataTable();
try
{
dao = new ConnectorDAO();
dao.connect();
dt = dao.result("SELECT ID, NOME, EMAIL, CELULAR, DATE_FORMAT(DATA_NASCIMENTO, '%d/%m/%Y') AS NASCIMENTO, PLANO, STATUS FROM alunos");
return(dt);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não possível listar os alunos:" + ex.Message);
}
}
public void updateAluno(AlunosModel aluno)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "UPDATE alunos SET NOME = '" + aluno.Nome + "', EMAIL = '" + aluno.Email + "', CELULAR = '" + aluno.Celular + "', DATA_NASCIMENTO = '" + aluno.DataNascimento + "', PLANO = '" + aluno.Plano + "', STATUS = '" + aluno.Status + "' WHERE ID = '" + aluno.Id + "'";
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar atualizar o aluno:" + ex.Message);
}
finally
{
dao = null;
}
}
public void insertAluno(AlunosModel aluno)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "INSERT INTO alunos (NOME, EMAIL, CELULAR, DATA_NASCIMENTO, PLANO, STATUS) VALUES ('" + aluno.Nome + "', '" + aluno.Email + "', '" + aluno.Celular + "', '" + aluno.DataNascimento + "', '" + aluno.Plano + "', '" + aluno.Status + "')";
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar cadastrar o aluno:" + ex.Message);
}
finally
{
dao = null;
}
}
public void updateUsuario(UsuariosModel usuario)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "UPDATE usuarios SET NOME = '" + usuario.Nome + "', EMAIL = '" + usuario.Email + "', SENHA = '" + usuario.Senha + "', PERMISSAO = '" + usuario.Permissao + "' WHERE ID = '" + usuario.Id + "'";
dao.executeQuery(query);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar atualizar o usuário:" + ex.Message);
}
finally
{
dao = null;
}
}
public bool insertUsuario(UsuariosModel usuario)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "INSERT INTO usuarios (NOME, EMAIL, SENHA, PERMISSAO) VALUES ('" + usuario.Nome + "', '" + usuario.Email + "', '" + usuario.Senha + "', '" + usuario.Permissao + "')";
dao.executeQuery(query);
return(true);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar inserir o usuário:" + ex.Message);
}
finally
{
dao = null;
}
}
public bool insertAula(AulasModel aula)
{
try
{
dao = new ConnectorDAO();
dao.connect();
string query = "INSERT INTO aulas (TITULO) VALUES ('" + aula.Titulo + "')";
dao.executeQuery(query);
return(true);
}
catch (Exception ex)
{
throw new Exception("Erro ao tentar inserir a aula:" + ex.Message);
}
finally
{
dao = null;
}
}
public DataTable getCheckins(int aulaId)
{
DataTable dt = new DataTable();
try
{
dao = new ConnectorDAO();
dao.connect();
string sql = "SELECT c.FK_ALUNO_ID AS ID, al.NOME FROM checkin c ";
sql += "JOIN aulas a ON a.ID = c.FK_AULA_ID ";
sql += "JOIN alunos al ON al.ID = c.FK_ALUNO_ID ";
sql += "WHERE c.FK_AULA_ID = " + aulaId;
dt = dao.result(sql);
return(dt);
}
catch (Exception ex)
{
throw new Exception("Ocorreu um erro e não foi possível listar os check-ins:" + ex.Message);
}
}
public static WorkflowDocument LoadDatabase(Database db, string workflowID, string USER_ACCOUNT)
{
WorkflowButtonList buttonList = new WorkflowButtonList();
WorkflowLineHeaderList lineHeaderList = new WorkflowLineHeaderList();
ButtonDAO daoButton = new ButtonDAO();
LineHeaderDAO daoLineHeader = new LineHeaderDAO();
LineDetailDAO daoLineDetail = new LineDetailDAO();
ConnectorDAO daoConnector = new ConnectorDAO();
#region " Load Buttons "
//== Generate all Button (not binding event click)
// Event ButtonClick will bind when assign document to viewer.
List <ButtonDTO> listButtons = daoButton.FindButton(db, workflowID, USER_ACCOUNT, SystemMaintenance.DataDefine.AUTO_ARRANGE_ICON, SystemMaintenance.DataDefine.ICON_PER_ROW);
for (int i = 0; i < listButtons.Count; i++)
{
ButtonDTO dto = listButtons[i];
WorkflowButton button = new WorkflowButton(dto);
button.Dock = DockStyle.Fill;
button.Anchor = AnchorStyles.None;
button.Name = String.Format("{0}.{1}.{2}", dto.WF_ID, dto.ROW_INDEX, dto.COL_INDEX);
//button.Click += new EventHandler(buttonClick);
button.Location = new Point(0, 0);
if (dto.FLG_VIEW == 1)
{
button.Visible = true;
}
else
{
button.Visible = false;
}
// add to collection
buttonList.Add(button);
}
#endregion
//ถ้าเปิดให้ auto arrange icon จะไม่มี work flow line
if (SystemMaintenance.DataDefine.AUTO_ARRANGE_ICON != true)
{
#region " Load Lines "
List <LineHeaderDTO> listLineHeader = daoLineHeader.GetLineHeaders(db, workflowID);
for (int i = 0; i < listLineHeader.Count; i++)
{
LineHeaderDTO lineHeaderDTO = listLineHeader[i];
List <LineDetailDTO> listLineDetail = daoLineDetail.GetLineDetails(db, workflowID, lineHeaderDTO.ID);
List <ConnectorDTO> listConnector = daoConnector.GetConnectors(db, workflowID, lineHeaderDTO.ID);
// Create Model Header
WorkflowLineHeader wf_header = new WorkflowLineHeader(null, lineHeaderDTO.ID, lineHeaderDTO.ZINDEX);
// Loop for Create inner Model LineDetail.
for (int iLine = 0; iLine < listLineDetail.Count; iLine++)
{
LineDetailDTO lineDetailDTO = listLineDetail[iLine];
WorkflowLineDetail wf_detail = new WorkflowLineDetail(wf_header
,
new Cell(null, lineDetailDTO.FROM_ROW,
lineDetailDTO.FROM_COL)
,
new Cell(null, lineDetailDTO.TO_ROW,
lineDetailDTO.TO_COL)
, (WorkflowLineStatus)lineDetailDTO.STATUS);
wf_header.Lines.Add(wf_detail);
}
// Loop for Create inner Model LineConnector.
for (int iConnector = 0; iConnector < listConnector.Count; iConnector++)
{
ConnectorDTO connectorDTO = listConnector[iConnector];
WorkflowLineConnector wf_connector = new WorkflowLineConnector(wf_header
,
new Cell(null, connectorDTO.ROW_INDEX,
connectorDTO.COL_INDEX)
,
(WorkflowConnectorType)
connectorDTO.STATUS);
wf_header.Connectors.Add(wf_connector);
}
lineHeaderList.Add(wf_header);
}
#endregion
}
WorkflowDocument document = new WorkflowDocument(workflowID, buttonList, lineHeaderList);
return(document);
}
