Example #1
0
    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);
    }
Example #2
0
    /// <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> &nbsp;" + 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);
        }
Example #5
0
    /// <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;
        }
    }
Example #6
0
    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);
    }
Example #8
0
    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);
        }
    }
Example #9
0
    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());
        }