public DataTable getDataTable()
{
string sql = " select * from " + G_Table + " where id_contato = " + base.PkId.ToString() + " order by id ";
DataTable ds = ConnAccess.fetchData(ConnAccess.getConn(), sql);
return(ds);
}
/// <summary>
/// Método principal, executa consulta no sistema e popula GridView
/// Main method, get querie result and bind GridView
/// </summary>
/// <returns></returns>
public override void carregaGrid()
{
string filtro = " where 1 = 1 ";
//string nomecoluna = "nome";
//if (getRequest(txtDescricao.ID) != String.Empty)
//{
// filtro += " and " + nomecoluna + " like '%" + getRequest(txtDescricao.ID) + "%'";
// }
IFiltro opFiltro = getFiltroPrincipal();
filtro += opFiltro.getSqlFiltro();
string sql = " select * from " + this.G_table + " " + filtro;
DataTable ds = ConnAccess.fetchData(ConnAccess.getConn(), sql);
btExportar.Visible = (ds.Rows.Count > 0);
gvwDados.DataSource = ds;
gvwDados.DataBind();
//Processaremos alguma coisa, de acordo ao tipo de botão usado.
base.processaConsulta(this.obj_botao, ds, gvwDados);
dv_qtde_registros.InnerHtml = " <b> Qtde de Registros: </b> " + ds.Rows.Count.ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
string tabela = request("tabela");
string campoid = request("campoid");
string campodesc = request("campodesc");
string campocod = request("campocod");
string term = request("term").Replace("'", "");
string comp = request("comp");
if (tabela != String.Empty && term != String.Empty)
{
string sql = " select concat(" + campodesc + " , ' -COD:' , cast(" + campocod + " as char) , ' -ID:' , cast(" + campocod + " as char)) as descricao ";
if (campoid == String.Empty)
{
sql = " select concat(" + campodesc + " , ' -COD:' , cast(" + campocod + " as char)) as descricao ";
}
sql += " from " + tabela + " where ( " + campodesc + " like '%" + term + "%' or cast(" + campocod + " as char) like '%" + term + "%' ) " + comp + " order by " + campodesc;
//Response.Write(sql); Response.End();
DataTable dt = ConnAccess.fetchData(ConnAccess.getConn(), sql);
Response.Write(converteDataTableToJson(dt)); Response.End();
}
}
/// <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>
/// Set SQL to change results from database. I use sql directly to evict keep results on viewstate or session
/// But, if you prefer, change to use dataView Directly.
/// </summary>
/// <param name="sqlSelect"> Sql Select command</param>
/// <param name="valuefield">value field</param>
/// <param name="textfield">text field</param>
/// <param name="selectedValue">selected(s) value(s) separated(s) by comma (ex: 1,2,3) , if Empty type String.Empty</param>
public void setSQLSource(string sqlSelect, string valuefield, string textfield, string selectedValue)
{
DataView dw = new DataView();
//Implement bellow, using your database connection, to retrieve a DataView from your select command.
DataTable dt = ConnAccess.fetchData(ConnAccess.getConn(), sqlSelect);
dw = new DataView(dt);
//Change two lines above to use your own method connection
//This method assume that value field is number.. edit to your own preference.
if (selectedValue != String.Empty)
{
dw.RowFilter = valuefield + " not in (" + selectedValue + ") ";
}
listOptions1.DataSource = dw;
listOptions1.DataValueField = valuefield;
listOptions1.DataTextField = textfield;
listOptions1.DataBind();
dw.RowFilter = valuefield + " in (" + nvl(selectedValue, "0") + ") ";
listOptions2.DataSource = dw;
listOptions2.DataValueField = valuefield;
listOptions2.DataTextField = textfield;
listOptions2.DataBind();
this.setMyViewState("sqlSelect", sqlSelect);
this.setMyViewState("valuefield", valuefield);
this.setMyViewState("textfield", textfield);
if (!Page.IsPostBack)
{ //Keep texts on text hidden field
hd_values.Value = selectedValue;
string text_values = string.Empty;
for (int i = 0; i < dw.Count; i++)
{
if (i.Equals(0))
{
text_values += dw[i][textfield].ToString();
}
else
{
text_values += "," + dw[i][textfield].ToString();
}
}
hd_text.Value = text_values;
}
}
private void carregaNome(string opcao)
{
if (getValor(txtCodigo) == String.Empty && opcao != "1")
{
setValor(txtDescricao, String.Empty);
setValor(hdIDRegistro, String.Empty);
return;
}
IDbPersist oConn = ConnAccess.getConn();
string filtro = " and " + this.ColunaCodigoConsulta + " = " + valorFormatado();
if (opcao == "1")
{
filtro = " and " + this.ColunaIDConsulta + " = " + getValor(hdIDRegistro);
}
Control txtIdColigada = Utilities.Format.localizaControl("txtIdColigada", this.Page.Form);
if (txtIdColigada != null && getValor(txtIdColigada) != String.Empty)
{
filtro += " and CODCOLIGADA = " + getValor(txtIdColigada);
}
if (TabelaConsulta.Substring(0, 1) == ".")
{
// TabelaConsulta = SessionFacade.getApp("bancoRM") + TabelaConsulta;
}
DataTable dt = ConnAccess.fetchData(oConn, "select " + this.ColunaIDConsulta + " as id, " +
this.ColunaCodigoConsulta + " as cod, " +
this.ColunaDescricao + " as descr from " + this.TabelaConsulta + " where 1 = 1 " + filtro);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
setValor(txtCodigo, dr["cod"]);
setValor(hdIDRegistro, dr["id"]);
setValor(txtDescricao, dr["descr"]);
if (DispararAposLocalizar != null)
{
DispararAposLocalizar(dr, EventArgs.Empty);
}
}
else
{
Alert("Registro não localizado!");
setValor(txtDescricao, String.Empty);
setValor(hdIDRegistro, String.Empty);
return;
}
}
/// <summary>
/// Obtém dados que estão nos campos da tela.
/// Get field values, inside a datarow structure, ready to save
/// </summary>
/// <returns>DataRow contendo estrutura da tabela + dados da tela.</returns>
public override DataRow obtemForm()
{
DataTable dtModelo = ConnAccess.fetchData(ConnAccess.getConn(), " select * from " + G_Table + " where 1= 0 ");
DataRow dr = null;
base.AcaoID = 1;
if (base.PkId > 0)
{
dr = ConnAccess.getRow(ConnAccess.getConn(), G_Table, "id", base.PkId.ToString());
base.AcaoID = 2;
}
if (dr == null)
{
dr = dtModelo.NewRow();
}
dr["nome"] = ConnAccess.NullToDBNull(getValor(txtNome));
dr["empresa"] = ConnAccess.NullToDBNull(getValor(txtEmpresa));
dr["telefone_pessoal"] = ConnAccess.NullToDBNull(getValor(txtTelefonePessoal));
dr["telefone_comercial"] = ConnAccess.NullToDBNull(getValor(txtTelefoneComercial));
dr["emails"] = ConnAccess.NullToDBNull(getValor(txtEmails));
dr["data_cadastro"] = ConnAccess.NullToDBNull(getDate(txtDataCadastro));
dr.Table.TableName = G_Table;
try
{
dr.Table.PrimaryKey = new DataColumn[] {
dr.Table.Columns["id"]
};
}
catch { }
//Garantindo ultima formatação para o nosso data row que será salvo..
base.formataRowAntesSalvar(ref dr);
return(dr);
}
private void insereCampoFiltro(HtmlTableCell td,
string nome, string tipo, string regra, string[] parametros, DataRowView drv)
{
if (parametros.Length > 0 && parametros[0] != String.Empty)
{
DropDownList cmb = new DropDownList();
cmb.ID = "g_txt_filtro_" + nome;
string lista = parametros[0];
if (lista.IndexOf("lista:") > -1)
{
string[] qu0 = lista.Split(':');
string[] qu = qu0[1].Split('|');
System.Collections.Generic.IList <Entities.SimplesCodigoNome> ls_combo = new
System.Collections.Generic.List <Entities.SimplesCodigoNome>();
for (int i = 0; i < qu.Length; i++)
{
string item = qu[i];
string[] it = item.Split(',');
ls_combo.Add(new Entities.SimplesCodigoNome(it[0], it[1]));
}
carregaCombo(cmb, ls_combo, "Codigo", "Nome", String.Empty, true);
}
if (lista.IndexOf("querie:") > -1)
{
string[] qu = lista.Split(':');
//----------- Filtro por dependência ------------------
if (drv.DataView.Table.Columns.Contains("dependencia"))
{
if (drv["dependencia"].ToString() != String.Empty)
{
string[] ar_ttmp = drv["dependencia"].ToString().Split('$');
for (int sy = 0; sy < ar_ttmp.Length; sy++)
{
if (ar_ttmp[sy].Trim() == String.Empty)
{
continue;
}
string[] lipmid = ar_ttmp[sy].Split(new string[] { "->" }, System.StringSplitOptions.None);
if (getRequest("g_txt_filtro_" + lipmid[0]) != "")
{
qu[1] = qu[1].Replace("($)", lipmid[1].Replace("{0}", getRequest("g_txt_filtro_" + lipmid[0])));
}
}
}
//Control cr_btPesq = Utilities.Format.localizaControl("btPesquisar", this.Page.Form);
//&& cr_btPesq != null && cr_btPesq is Button
for (int sy = 0; sy < drv.DataView.Count; sy++)
{
if (drv.DataView[sy]["dependencia"] != DBNull.Value &&
drv.DataView[sy]["dependencia"].ToString().IndexOf(nome + "->") > -1)
{
cmb.Attributes.Remove("onchange");
cmb.Attributes.Add("onchange", this.Page.GetPostBackClientEvent(Button1, String.Empty));
}
}
}
qu[1] = qu[1].Replace("($)", "");
// -------------------------------------------
try
{
IDbPersist oConn = ConnAccess.getConn();
DataTable dtaB = ConnAccess.fetchData(oConn, qu[1]);
carregaCombo(cmb, dtaB, dtaB.Columns[0].ColumnName, dtaB.Columns[1].ColumnName,
String.Empty, true);
dtaB.Dispose();
}
catch (Exception exp)
{
//problema ao montar o componente!..
}
}
td.Controls.Add(cmb);
return;
}
if (tipo == "System.Double" || tipo == "System.Decimal")
{
HtmlGenericControl span = new HtmlGenericControl("span");
span.InnerHtml = " de ";
td.Controls.Add(span);
TextBox txt = new TextBox();
txt.ID = "g_txt_filtro_" + nome;
txt.Width = Unit.Parse("70px");
txt.CssClass = "c_txt_filtro";
txt.MaxLength = 18;
setSoDecimal(txt);
td.Controls.Add(txt);
HtmlGenericControl span2 = new HtmlGenericControl("span");
span2.InnerHtml = " até ";
td.Controls.Add(span2);
TextBox txt2 = new TextBox();
txt2.ID = "g_txt_filtro_" + nome + "2";
txt2.Width = Unit.Parse("70px");
txt2.CssClass = "c_txt_filtro";
txt2.MaxLength = 18;
setSoDecimal(txt2);
td.Controls.Add(txt2);
return;
}
else if (tipo == "System.DateTime")
{
System.Web.UI.UserControl uc =
(System.Web.UI.UserControl) this.Page.LoadControl("~/controles/UcFaseDatas.ascx");
td.Controls.Add(uc);
((IField)uc).Auxiliar = "N";
uc.ID = "g_txt_filtro_" + nome;
if (uc is IFieldDataHora)
{
((IFieldDataHora)uc).mostraHora = true;
}
return;
}
else
{
TextBox txt = new TextBox();
txt.ID = "g_txt_filtro_" + nome;
txt.Width = Unit.Parse("150px");
txt.CssClass = "c_txt_filtro";
txt.MaxLength = 250;
if (tipo == "System.Int32" || tipo == "System.Int16" || tipo == "System.Int64" || tipo == "System.Byte")
{
txt.MaxLength = 20;
setSoNumero(txt);
}
else
{
txt.Width = Unit.Parse("95%");
}
td.Controls.Add(txt);
}
}
public DataView getCampos()
{
string str_driver = PageCadastroBase.getExcelDriver();
if (str_driver == "")
{
str_driver = "Microsoft Excel Driver (*.xls)";
}
string conexao = "Driver={" + str_driver + "};DriverId=790;DBQ=" +
this.CaminhoExcel + ";ReadOnly= true ; ";
//conn.ConnectionString = conexao;
ConnODBC OdbcConn = null;
try
{
IDbPersist oTmp = FactoryConn.getConn("odbc", conexao, "odbc");
OdbcConn = (ConnODBC)oTmp;
//conn.Open();
}
catch (Exception exp)
{
Trace.Write("Erro ao tentar conectar o banco de dados de filtro via ODBC: " +
exp.Message + " - Arquivo em formato inválido! - String de Conexão:" + conexao);
//Alert("Erro ao tentar conectar o banco de dados de filtro via ODBC: " +
// exp.Message + " - Arquivo em formato inválido! ");
return(null);
}
OdbcConnection conn = (OdbcConnection)OdbcConn.getConn();
string[] restrictions = new string[4];
restrictions[3] = "Table";
DataTable userTables = conn.GetSchema("Tables");
string my_tabela = this.Tabela;
for (int i = 0; i < userTables.Rows.Count; i++)
{
string tab_col = userTables.Rows[i][2].ToString();
if (userTables.Rows[i][3].ToString().IndexOf("TABLE") > -1 && tab_col == this.Tabela + "$")
{
my_tabela = userTables.Rows[i][2].ToString();
break;
}
}
DataTable dt_campos =
ConnAccess.fetchData(OdbcConn, "select * from [" +
my_tabela + "] where visivel = 'True' order by tipo desc, ordem asc, label asc ");
//primaryKey desc, label,
conn.Close();
conn.Dispose();
string add_filtro = string.Empty;
if (dt_campos.Columns.Contains("filtro"))
{
add_filtro += " and filtro = '1' ";
}
DataView dw = dt_campos.DefaultView;
return(dw);
}
public void carregaMenu()
{
string filtro = string.Empty;
//if (!SessionFacade.Admin)
//{
// filtro += " and pagina in ('"+
// SessionFacade.getPropriedade("_paginas").Replace(",","','")+"') ";
//}
if (!SessionFacade.Admin)
{
filtro += " and id in (select id_processo from perfil_processos where id_perfil in ( " +
SessionFacade.listaProcessos + ") and acao is not null ) ";
}
////if (!SessionFacade.Admin)
////{
//// filtro += " and id in ( " +
//// SessionFacade.listaProcessos + ") ";
////}
IDbPersist oConn = ConnAccess.getConn();
DataTable dt = ConnAccess.fetchData(oConn, " select * from menu where nivel not like 'MO%' " +
" and id_item_pai is null " +
filtro + " order by nivel asc ");
string menu = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataTable dt_itens = ConnAccess.fetchData(oConn, " select * from menu where id_item_pai = " +
dr["id"].ToString() + filtro + " order by funcionalidade asc ");
if (base.Nvl(dr["pagina"], "#").ToString() == "#" &&
dt_itens.Rows.Count <= 0)
{
//É um menu, sem filhos, então não precisa aparecer...
}
else
{
menu += System.Environment.NewLine + "<li class=\"pureCssMenui0\"><a class=\"pureCssMenui0\" " +
" href=\"" + base.Nvl(dr["pagina"], "#").ToString() + "\"><span>" +
dr["funcionalidade"].ToString() +
"</span><![if gt IE 6]></a><![endif]><!--[if lte IE 6]><table><tr><td><![endif]-->";
}
if (dt_itens.Rows.Count > 0)
{
menu += System.Environment.NewLine +
"<ul class=\"pureCssMenum\">";
for (int z = 0; z < dt_itens.Rows.Count; z++)
{
menu += System.Environment.NewLine + "<li class=\"pureCssMenui\"><a class=\"pureCssMenui\" href=\"" +
dt_itens.Rows[z]["pagina"].ToString() + "\">" +
dt_itens.Rows[z]["funcionalidade"].ToString() + "</a></li>";
}
menu += "</ul>";
}
menu += "<!--[if lte IE 6]></td></tr></table></a><![endif]--></li>";
}
ul_menu.InnerHtml = menu;
}
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());
}