/// <summary>
/// Obtém a consulta da tela de listagem (isso também pode ser colocado em outra classe ou em um model, ao depender do tamanho do projeto).
/// </summary>
/// <param name="oConn"></param>
/// <returns></returns>
private static DataTable getContatoDataList(DataAccess.IDbPersist oConn)
{
string filtro = " where 1 = 1 ";
string coluna = "";
string coluna_filtro = "";
if (Helper.RequestString("nome") != String.Empty)
{
string strfilt = Helper.RequestString("nome").Replace("'", "''");
filtro += " and ( match (p.nome) against ('" + strfilt + "' IN BOOLEAN MODE) or upper(p.nome) like upper('%" + strfilt + "%') ) ";
coluna += ", match (p.nome) against ('" + strfilt + "' ) as reelevance ";
coluna_filtro = " reelevance desc, ";
}
if (Helper.RequestString("empresa") != String.Empty)
{
string strfilt = Helper.RequestString("empresa").Replace("'", "''");
filtro += " and upper(p.empresa) like upper('%" + strfilt + "%') ";
}
if (Helper.RequestString("telefone") != String.Empty)
{
string strfilt = Helper.RequestString("telefone").Replace("'", "''");
filtro += " and ( upper(p.telefone_comercial) like upper('%" + strfilt + "%') or upper(p.telefone_pessoal) like upper('%" + strfilt + "%') ) ";
}
if (Helper.RequestString("email") != String.Empty)
{
string strfilt = Helper.RequestString("email").Replace("'", "''");
filtro += " and exists( select ce.id from contato_email ce where ce.id_contato = p.id and ce.email like upper('%" + strfilt + "%') ) ";
}
string sql = " select p.* " + coluna + " from contato p " + filtro + " order by " + coluna_filtro + " p.nome ";
DataTable dtLista = ConnAccess.fetchData(oConn, sql);
return(dtLista);
}
/// <summary>
/// Exportando dados para o excel..
/// </summary>
/// <returns></returns>
public ActionResult Excel()
{
DataAccess.IDbPersist oConn = base.getMainConn();
DataTable dt = getContatoDataList(oConn);
dt.Columns["id"].ColumnName = "ID";
dt.Columns["nome"].ColumnName = "Nome";
dt.Columns["empresa"].ColumnName = "Empresa";
dt.Columns["telefone_comercial"].ColumnName = "Telefone Comercial";
dt.Columns["telefone_pessoal"].ColumnName = "Telefone Pessoal";
dt.Columns["emails"].ColumnName = "Emails";
dt.Columns["data_cadastro"].ColumnName = "Data Cadastro";
dt.TableName = "Contato";
string path = Server.MapPath("~/tmp");
Helper.limpaExcessoarquivosTmp(path);
string nm_arquivo = Session.SessionID.Substring(0, 5) + "contato.xls";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ExcelLibrary.DataSetHelper.CreateWorkbook(path + "\\" + nm_arquivo, ds);
oConn.disconnect();
Response.Redirect("~/tmp/" + nm_arquivo);
return(View());
}
/// <summary>
/// Obtém a conexão principal com o banco de dados.
/// </summary>
/// <returns></returns>
public IDbPersist getMainConn()
{
if (ViewData["oConn"] != null)
{
return((DataAccess.IDbPersist)ViewData["oConn"]);
}
DataAccess.IDbPersist oConn = DataAccess.FactoryConn.getConn(System.Configuration.ConfigurationManager.AppSettings["tipo_sgbd"],
System.Configuration.ConfigurationManager.ConnectionStrings["bdConnectionString"].ConnectionString,
"connmysql");
ViewData["oConn"] = oConn;
return(oConn);
}
//
// GET: /Contato/
#region Actions
public ActionResult Lista()
{
DataAccess.IDbPersist oConn = base.getMainConn();
RendLibrary.Paginator paginator = new RendLibrary.Paginator();
//Setando a paginação.. quantidade mínima de registros por página.
if (System.Configuration.ConfigurationManager.AppSettings["minimo_pagina"].ToString() != "")
{
paginator.selQtdeRegistro = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["minimo_pagina"].ToString());
}
else
{
paginator.selQtdeRegistro = 10;
}
if (Helper.RequestString("selPagina") != String.Empty && Helper.isInteger(Helper.RequestString("selPagina")))
{
paginator.selPagina = Helper.RequestValue <int>("selPagina").Value;
}
if (Helper.RequestString("selQtdeRegistro") != String.Empty && Helper.isInteger(Helper.RequestString("selQtdeRegistro")))
{
paginator.selQtdeRegistro = Helper.RequestValue <int>("selQtdeRegistro").Value;
}
//paginator.selQtdeRegistro = 2;
DataTable dtLista = getContatoDataList(oConn);
paginator.totalRegistro = dtLista.Rows.Count;
//Fazendo o cálculo da paginação...
Paginator.calculoPaginacao(ref paginator);
ViewData["Paginator"] = paginator;
ViewData["dtLista"] = dtLista;
string controller = Request.RequestContext.RouteData.Values["controller"].ToString();
base.guardaSessao(controller);
return(View());
}
public ActionResult Cadastro()
{
DataAccess.IDbPersist oConn = base.getMainConn();
DataRow registro = ConnAccess.getNewRow(oConn, "contato");
DataRow dr_email = ConnAccess.getNewRow(oConn, "contato_email");
DataTable dtEmails = dr_email.Table;
string id = RendLibrary.Helper.RequestString("id");
string acao = RendLibrary.Helper.RequestString("acao");
string ispostback = RendLibrary.Helper.RequestString("ispostback");
if (ispostback == "1")
{
if (id.Trim() != String.Empty && RendLibrary.Helper.isInteger(id))
{
registro = ConnAccess.getRow(oConn, "contato", "id", id);
}
RendLibrary.Helper.loadDataRowFromForm(registro);
if (registro["data_cadastro"] == DBNull.Value)
{
registro["data_cadastro"] = DateTime.Now;
}
}
if (acao == "SAVE")
{
if (registro["id"] == DBNull.Value)
{
registro["data_cadastro"] = DateTime.Now;
ConnAccess.Insert(oConn, registro, "id", true);
registro["id"] = ConnAccess.getMax(oConn, "id", "contato", "");
}
else
{
ConnAccess.Update(oConn, registro, "id");
}
if (registro["id"] != DBNull.Value)
{
this.salvaEmails(Convert.ToInt32(registro["id"]), oConn);
}
TempData["st_Mensagem"] = "Contato salvo com sucesso!";
//TempData["st_Mensagem"] = "Contato salvo com sucesso!";
Response.Redirect("Cadastro?id=" + registro["id"].ToString() + "&acao=LOAD", true);
Response.End();
}
if (acao == "DEL" && id.Trim() != String.Empty && RendLibrary.Helper.isInteger(id))
{
ConnAccess.executeCommand(oConn, " delete from contato where id = " + id.ToString());
TempData["st_Mensagem"] = "Contato removido com sucesso!";
Response.Redirect("Cadastro", true);
Response.End();
}
if (id.Trim() != String.Empty && RendLibrary.Helper.isInteger(id))
{
registro = ConnAccess.getRow(oConn, "contato", "id", id);
dtEmails = ConnAccess.fetchData(oConn, " select * from contato_email where id_contato = " +
id + " order by ordem_cadastro ");
}
if (dtEmails.Rows.Count <= 0)
{
dtEmails.Rows.Add(dr_email.ItemArray);
}
ViewData["registro"] = registro;
ViewData["DtEmails"] = dtEmails;
return(View());
}