public static IDbPersist getConn(string tipo, string connectionString, string var_key = "connection_system")
{
IDbPersist saida = null;
/*
* if ( $tipo == self::DB_POSTGRESQL)
* $saida = new PostGreSQLConnection ($arr_conection, $var_name);
*
*
* if ( $tipo == self::DB_SQLSERVER)
* $saida = new MSSQLConnection($arr_conection, $var_name);
*
* */
if (tipo == DB_MYSQL)
{
saida = new ConnMySql(); // MYSQLConnection($arr_conection, $var_name);
saida.connect(connectionString, var_key);
}
if (tipo == DB_ODBC)
{
saida = new ConnODBC();
saida.connect(connectionString, var_key);
}
// $saida = new ODBCConnection($arr_conection, $var_name);
return(saida);
}
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>
/// Método para salvar os emails que vem da tela.
/// </summary>
/// <param name="id_contato"></param>
/// <param name="oConn"></param>
private void salvaEmails(int id_contato, IDbPersist oConn)
{
DataRow dr_email = ConnAccess.getNewRow(oConn, "contato_email");
int qtde_emails = RendLibrary.Helper.RequestValue <int>("qtde_emails").Value;
string str_ids = "0";
int ordem = 0;
string emails = "";
for (int i = 0; i <= qtde_emails; i++)
{
string email = RendLibrary.Helper.RequestString("email" + i.ToString());
if (email.Trim() != String.Empty)
{
ordem++;
if (emails != String.Empty)
{
emails += ", ";
}
emails += email.Trim();
dr_email["email"] = email;
dr_email["id_contato"] = id_contato;
dr_email["ordem_cadastro"] = ordem;
string sql_item = " select id from contato_email where email='" + email + "' and id_contato=" + id_contato.ToString();
object id_tmp = ConnAccess.executeScalar(oConn, sql_item);
if (id_tmp != null && id_tmp != DBNull.Value)
{
dr_email["id"] = id_tmp;
ConnAccess.Update(oConn, dr_email, "id");
}
else
{
ConnAccess.Insert(oConn, dr_email, "id", true);
dr_email["id"] = ConnAccess.getMax(oConn, "id", dr_email.Table.TableName, " where id_contato = " + id_contato.ToString());
}
str_ids += "," + dr_email["id"].ToString();
}
}
ConnAccess.executeCommand(oConn, " delete from contato_email where id_contato = " + id_contato.ToString() + " and id not in ( " +
str_ids + " ) ");
ConnAccess.executeCommand(oConn, " update contato set emails = '" + emails + "' where id = " + id_contato.ToString());
}
public static int getMax(IDbPersist oConn, string coluna, string tabela, string where)
{
string sql = " select max(" + coluna + ") from " + tabela + where;
object cod = oConn.executeScalar(sql);
if (cod != null && cod.ToString() != String.Empty)
{
return(Convert.ToInt32(cod.ToString()));
}
return(0);
}
/// <summary>
/// Get One Row from DataBase
/// </summary>
/// <param name="oConn"></param>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public static DataRow fastOne(IDbPersist oConn, string table, string where = "", string order = "")
{
DataTable ar = ConnAccess.fastQuerie(oConn, table, where, order);
// connAccess.fastQuerie( $oConn, $table, $where, $order );
if (ar.Rows.Count > 0)
{
DataRow ret = ar.Rows[0];
return(ret);
}
return(null);
}
/// <summary>
/// Fast querie. Get a sql querie informing table + where
/// </summary>
/// <param name="oConn"></param>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public static DataTable fastQuerie(IDbPersist oConn, string table, string where = "", string order = "")
{
string sql = " select * from " + table;
if (where != "")
{
sql += " where " + where;
}
if (order != "")
{
sql += " order by " + order;
}
return(oConn.fetchDados(sql));
}
public static IDbPersist getConn()
{
System.Web.HttpContext Gcontext = System.Web.HttpContext.Current;
IDbPersist oConn = null;
if (Gcontext.Items["_myconn"] != null)
{
oConn = (IDbPersist)Gcontext.Items["_myconn"];
}
else
{
oConn = FactoryConn.getConn(System.Configuration.ConfigurationManager.AppSettings["tipo_sgbd"],
System.Configuration.ConfigurationManager.ConnectionStrings["bdConnectionString"].ToString(), "connmysql");
}
return(oConn);
}
public static DataRow getRow(IDbPersist conn, string tabela, string pk, string id)
{
string sql = " select * from " + tabela + " where " + pk + " = " + id;
DataTable ds = new DataTable();
ds = ConnAccess.fetchData(conn, sql);
ds.TableName = tabela;
if (ds.Rows.Count > 0)
{
return(ds.Rows[0]);
}
return(null);
}
/// <summary>
/// Cria um DataRow de dados vazio
/// </summary>
/// <param name="conn"></param>
/// <param name="tabela"></param>
/// <returns></returns>
public static DataRow getNewRow(IDbPersist conn, string tabela)
{
string sql = " select * from " + tabela + " where 1 = 0 ";
DataTable ds = new DataTable();
try
{
ds = ConnAccess.fetchData(conn, sql);
DataRow drr = ds.NewRow();
drr.Table.TableName = tabela;
return(drr);
}
catch (Exception exp)
{
throw exp;
}
return(null);
}
/// <summary>
/// Obtém uma linha DataRow.
/// </summary>
/// <param name="conn">Conexão Usada</param>
/// <param name="tabela"></param>
/// <param name="comp"></param>
/// <returns></returns>
public static DataRow getRow(IDbPersist conn, string tabela, string comp)
{
string sql = " select * from " + tabela + " where 1 = 1 " + comp;
try
{
DataTable ds = new DataTable();
ds = ConnAccess.fetchData(conn, sql);
ds.TableName = tabela;
if (ds.Rows.Count > 0)
{
return(ds.Rows[0]);
}
}
catch (Exception exp)
{
throw new Exception(sql + " - " + exp.Message);
}
return(null);
}
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);
}
/// <summary>
/// Insert new record
/// </summary>
/// <param name="oConn"></param>
/// <param name="dr"></param>
/// <param name="pk"></param>
/// <param name="auto_increment"></param>
public static void Insert(IDbPersist oConn, DataRow dr, string pk, bool auto_increment = true)
{
oConn.Insert(dr, auto_increment, pk);
}
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;
}
/// <summary>
/// Cuida de salvar informações provenientes da página
/// </summary>
/// <param name="bas"></param>
/// <param name="entidade"></param>
/// <param name="carregaGrid"></param>
/// <param name="msg"></param>
/// <param name="msgSemSucesso"></param>
protected virtual void salvar(DataRow entidade, Boolean carregaGrid,
string msg, string msgSemSucesso)
{
Boolean validado = true;
if (validaSalvar)
{
if (this.Tipovalidacao.Equals(1))
{
validado = validar();
}
else
{
validado = validar(entidade);
}
}
try
{
if (!validado)
{
return;
}
string nomepk = ConnAccess.getNomePrimaryKey(entidade.Table);
IDbPersist oConn = ConnAccess.getConn();
if (entidade[nomepk] != DBNull.Value && Convert.ToInt32(entidade[nomepk]) > 0)
{
ConnAccess.Update(oConn, entidade, nomepk);
}
else
{
ConnAccess.Insert(oConn, entidade, nomepk, true);
}
if (carregaGrid)
{
this.carregaGrid();
}
if (msg != String.Empty)
{
Alert(msg);
}
if (this.limpaFormulario)
{
this.limpaForm();
}
}
catch (Exception e)
{
this.HouveErro = true;
if (msgSemSucesso != String.Empty || e is System.Data.OleDb.OleDbException)
{
string innerMsg = string.Empty;
if (e.InnerException != null)
{
innerMsg = " ( " + e.InnerException.Message + ") ";
}
if (msgSemSucesso != String.Empty)
{
Alert(msgSemSucesso + " :" + e.Message + innerMsg);
}
else
{
Alert(e.Message + innerMsg);
}
}
}
}
/// <summary>
/// Execute a command
/// </summary>
/// <param name="oConn"></param>
/// <param name="sql"></param>
public static void executeCommand(IDbPersist oConn, string sql)
{
oConn.executeCommand(sql);
}
/// <summary>
/// Executing a scalar function
/// </summary>
/// <param name="oConn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static object executeScalar(IDbPersist oConn, string sql)
{
return(oConn.executeScalar(sql));
}
/// <summary>
/// Perfoms a querie
/// </summary>
/// <param name="oConn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable fetchData(IDbPersist oConn, string sql)
{
return(oConn.fetchDados(sql));
}
public StoreScp(ILogger <StoreScp> logger, IDbPersist dbPersist)
{
_logger = logger;
db = dbPersist;
}
/// <summary>
/// Update a record
/// </summary>
/// <param name="oConn"></param>
/// <param name="dr"></param>
/// <param name="pk"></param>
public static void Update(IDbPersist oConn, DataRow dr, string pk)
{
oConn.Update(dr, pk);
}
