public DataTable PesquisarMDL(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\EMPRESARIO3\Dados\EP3.mdb";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "select chvbfj, cnpjcpf, rzs from bfj where ";
if (_mdlEmpresa.FiltroAbrirChamado == "CNPJ")
{
//Query += "cnpjcpf LIKE \"%\" + @Filtro + \"%\""; procura qualquer parte
Query += "cnpjcpf LIKE + @Filtro + \"%\"";
}
else if (_mdlEmpresa.FiltroAbrirChamado == "Nome")
{
Query += "rzs LIKE \"%\" + @Filtro + \"%\"";
}
Query += " order by rzs";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtFiltro = cmd.CreateParameter();
pmtFiltro.ParameterName = "@Filtro";
pmtFiltro.DbType = DbType.String;
pmtFiltro.Value = _mdlEmpresa.FiltrotxtProcurar;
cmd.Parameters.Add(pmtFiltro);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable empresas = new DataTable();
da.Fill(empresas);
return(empresas);
}
public bool MudarStatusSendoAtendido(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "update tb_chamados " +
"set " +
"SendoAtendido='1' " +
"where id = @id";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
var pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtID);
cmd.ExecuteNonQuery();
int resultado = cmd.ExecuteNonQuery();
ConexaoDB.Close();
return(resultado > 0);
}
public DataTable PesquisarTelefones(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "SELECT tel1, tel2, tel3, tel4, tel5 FROM ende WHERE chvbfj=@id";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtID);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable telefones = new DataTable();
da.Fill(telefones);
ConexaoDB.Close();
return(telefones);
}
public DataTable PesquisaFollowUPIndividual(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "Select chvent, M_desc, Dt_fwu " +
"From fwu " +
"where chvent=@id order by Dt_fwu desc";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.chvvnda;
cmd.Parameters.Add(pmtID);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable empresas = new DataTable();
da.Fill(empresas);
ConexaoDB.Close();
return(empresas);
}
public DataTable EmpresaPesquisarMDL(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosmdb.mdb";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "select * from tb_empresas where ";
if (_mdlEmpresa.FiltroAbrirChamado == "CNPJ")
{
Query += "cnpj LIKE \"%\" + @Filtro + \"%\"";
}
else if (_mdlEmpresa.FiltroAbrirChamado == "Nome")
{
Query += "nome LIKE \"%\" + @Filtro + \"%\"";
}
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtFiltro = cmd.CreateParameter();
pmtFiltro.ParameterName = "@Filtro";
pmtFiltro.DbType = DbType.String;
pmtFiltro.Value = _mdlEmpresa.FiltrotxtProcurar;
cmd.Parameters.Add(pmtFiltro);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable empresas = new DataTable();
da.Fill(empresas);
return(empresas);
}
public DataTable PesquisaResumo(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "Select rec.chvbfj, rec.chvori, rec.dt_dem, vndB.chvvnda, vndB.c_vlr, vndB.chvps, ps.chvps, ps.Dsc " +
"From (( " +
"rec " +
"inner join vndB " +
"on vndB.chvvnda = rec.chvori) " +
"inner join ps " +
"on ps.chvps = vndB.chvps) " +
"where rec.chvbfj=@id order by rec.dt_dem desc"; // ps.Dsc";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtID);
/* DataSet ds = new DataSet();
* DataTable dtClientes = new DataTable();
*
* OleDbDataReader dr = cmd.ExecuteReader();
* int nColunas = dr.FieldCount;
* int count = Convert.ToInt32(cmd.ExecuteScalar());
* int dr1 = int.Parse(cmd.ExecuteScalar().ToString());
* string[] linhaDados = new string[nColunas];
* while (dr.Read())
* {
* percorre cada uma das colunas
* for (int a = 0; a < nColunas; a++)
* {
* linhaDados[a] = dr.GetInt32(a).ToString();
* //da erro nessa parte.
* }
* atribui a linha ao datagridview
* dataGridView1.Rows.Add(linhaDados);
*
* }*/
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable empresas = new DataTable();
da.Fill(empresas);
ConexaoDB.Close();
return(empresas);
}
public bool AbrirSoChamado(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "insert into tb_chamados(aberto, SendoAtendido, fk_idempresa, data) values(@aberto, @SendoAtendido, @fk_idempresa, @data);";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
var pmtAbrirChamado = cmd.CreateParameter();
pmtAbrirChamado.ParameterName = "@aberto";
pmtAbrirChamado.DbType = DbType.String;
pmtAbrirChamado.Value = "1";
cmd.Parameters.Add(pmtAbrirChamado);
var pmtSendoAtendido = cmd.CreateParameter();
pmtSendoAtendido.ParameterName = "@SendoAtendido";
pmtSendoAtendido.DbType = DbType.String;
pmtSendoAtendido.Value = "0";
cmd.Parameters.Add(pmtSendoAtendido);
var pmtfkidempresa = cmd.CreateParameter();
pmtfkidempresa.ParameterName = "@fk_idempresa";
pmtfkidempresa.DbType = DbType.String;
pmtfkidempresa.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtfkidempresa);
DateTime dataAtu = DateTime.Now;
var pmtData = cmd.CreateParameter();
pmtData.ParameterName = "@data";
pmtData.DbType = DbType.String;
pmtData.Value = dataAtu;
cmd.Parameters.Add(pmtData);
if (cmd.ExecuteNonQuery() > 0)
{
ConexaoDB.Close();
return(true);
}
else
{
ConexaoDB.Close();
return(false);
}
}
public bool CadastrarEmpresaMDL(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosmdb.mdb";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "insert into tb_empresas(CNPJ, Nome, Telefone) values(@CNPJ, @Nome, @Telefone)";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
var pmtCNPJ = cmd.CreateParameter();
pmtCNPJ.ParameterName = "@CNPJ";
pmtCNPJ.DbType = DbType.String;
pmtCNPJ.Value = _mdlEmpresa.CNPJ;
cmd.Parameters.Add(pmtCNPJ);
var pmtNome = cmd.CreateParameter();
pmtNome.ParameterName = "@Nome";
pmtNome.DbType = DbType.String;
pmtNome.Value = _mdlEmpresa.Nome;
cmd.Parameters.Add(pmtNome);
var pmtTelefone = cmd.CreateParameter();
pmtTelefone.ParameterName = "@Telefone";
pmtTelefone.DbType = DbType.String;
pmtTelefone.Value = _mdlEmpresa.Telefone;
cmd.Parameters.Add(pmtTelefone);
if (cmd.ExecuteNonQuery() > 0)
{
ConexaoDB.Close();
return(true);
}
else
{
ConexaoDB.Close();
return(false);
}
}
public string DataInativo(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "Select Dt_Inat from cli where chvbfj=@id";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.txtAbrirChamadoID;
cmd.Parameters.Add(pmtID);
OleDbDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
return(myReader["Dt_Inat"].ToString());
}
ConexaoDB.Close();
return(string.Empty);
/* 1 DataSet ds = new DataSet();
* if (da.Fill(ds) != 0)
* {
* for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
* {
* _mdlEmpresa.txtDataBloqueio = ds.Tables[0].Rows[i]["Dt_Bloq"].ToString();
* }
* }
*
* return _mdlEmpresa.txtDataBloqueio; 1 */
}
public string PesquisarVendedor(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
//string Query = "Select vnda.chvvnd, vnda.chvvnda, vndb.chvnda, vend.chvvend, vend.chvbfj, bfj.chvbfj, bfj.rzs " +
string Query = "Select vndb.chvvnda, vnda.chvvnda, vnda.chvvnd, vend.chvvend, bfj.chvbfj, vend.chvbfj, bfj.rzs " +
"From ((( " +
"vndb " +
"inner join vnda " +
"on vndb.chvvnda = vnda.chvvnda) " +
"inner join vend " +
"on vnda.chvvnd = vend.chvvend) " +
"inner join bfj " +
"on bfj.chvbfj = vend.chvbfj) " +
"where vndb.chvvnda=@NPed";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
OleDbParameter pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@NPed";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.VendedorvndBchvvnda;
cmd.Parameters.Add(pmtID);
OleDbDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
return(myReader["rzs"].ToString());
}
ConexaoDB.Close();
return(string.Empty);
}
public bool FinalizarChamado(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.mdb";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "update tb_chamados " +
"set " +
"aberto=@aberto, " +
"fk_idtecnico=@tec, " +
"atendimento=@atendimento, " +
"resumo=@resumo, " +
"dataFinal=@dataFinal, " +
"telefone=@telefone, " +
"contato=@contato " +
"where id = @id";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
cmd.CommandType = CommandType.Text;
var pmtFecharChamado = cmd.CreateParameter();
pmtFecharChamado.ParameterName = "@aberto";
pmtFecharChamado.DbType = DbType.String;
pmtFecharChamado.Value = "0";
cmd.Parameters.Add(pmtFecharChamado);
var pmttec = cmd.CreateParameter();
pmttec.ParameterName = "@tec";
pmttec.DbType = DbType.String;
pmttec.Value = _mdlEmpresa.cbbTecnico;
cmd.Parameters.Add(pmttec);
var pmtAtendimento = cmd.CreateParameter();
pmtAtendimento.ParameterName = "@atendimento";
pmtAtendimento.DbType = DbType.String;
pmtAtendimento.Value = _mdlEmpresa.Atendimento;
cmd.Parameters.Add(pmtAtendimento);
var pmtResumo = cmd.CreateParameter();
pmtResumo.ParameterName = "@resumo";
pmtResumo.DbType = DbType.String;
pmtResumo.Value = _mdlEmpresa.Resumo;
cmd.Parameters.Add(pmtResumo);
DateTime dataFim = DateTime.Now;
var pmtDataF = cmd.CreateParameter();
pmtDataF.ParameterName = "@dataFinal";
pmtDataF.DbType = DbType.String;
pmtDataF.Value = dataFim;
cmd.Parameters.Add(pmtDataF);
var pmtTelefone = cmd.CreateParameter();
pmtTelefone.ParameterName = "@telefone";
pmtTelefone.DbType = DbType.String;
pmtTelefone.Value = _mdlEmpresa.Telefone;
cmd.Parameters.Add(pmtTelefone);
var pmtContato = cmd.CreateParameter();
pmtContato.ParameterName = "@contato";
pmtContato.DbType = DbType.String;
pmtContato.Value = _mdlEmpresa.Contato;
cmd.Parameters.Add(pmtContato);
var pmtID = cmd.CreateParameter();
pmtID.ParameterName = "@id";
pmtID.DbType = DbType.String;
pmtID.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtID);
cmd.ExecuteNonQuery();
int resultado = cmd.ExecuteNonQuery();
ConexaoDB.Close();
return(resultado > 0);
}
public bool AbrirChamadoCompleto(global::CamadaModelos.mdlEmpresa _mdlEmpresa)
{
string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.mdb";
OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess);
ConexaoDB.Open();
string Query = "insert into tb_chamados(aberto, fk_idempresa, data) values(@aberto, @fk_idempresa, @data);";
OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB);
var pmtAbrirChamado = cmd.CreateParameter();
pmtAbrirChamado.ParameterName = "@aberto";
pmtAbrirChamado.DbType = DbType.String;
pmtAbrirChamado.Value = "1";
cmd.Parameters.Add(pmtAbrirChamado);
var pmtfkidempresa = cmd.CreateParameter();
pmtfkidempresa.ParameterName = "@fk_idempresa";
pmtfkidempresa.DbType = DbType.String;
pmtfkidempresa.Value = _mdlEmpresa.ID;
cmd.Parameters.Add(pmtfkidempresa);
DateTime dataAtu = DateTime.Now;
var pmtData = cmd.CreateParameter();
pmtData.ParameterName = "@data";
pmtData.DbType = DbType.String;
pmtData.Value = dataAtu;
cmd.Parameters.Add(pmtData);
if (cmd.ExecuteNonQuery() > 0)
{
ConexaoDB.Close();
}
else
{
ConexaoDB.Close();
}
string ConexaoAccess1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.mdb";
OleDbConnection ConexaoDB1 = new OleDbConnection(ConexaoAccess1);
ConexaoDB1.Open();
string Query1 = "insert into tb_empresas(id, cnpj, nome) values(@id, @cnpj, @nome);";
OleDbCommand cmd1 = new OleDbCommand(Query1, ConexaoDB1);
var pmtId = cmd1.CreateParameter();
pmtId.ParameterName = "@id";
pmtId.DbType = DbType.String;
pmtId.Value = _mdlEmpresa.ID;
cmd1.Parameters.Add(pmtId);
var pmtCNPJ = cmd1.CreateParameter();
pmtCNPJ.ParameterName = "@cnpj";
pmtCNPJ.DbType = DbType.String;
pmtCNPJ.Value = _mdlEmpresa.CNPJ;
cmd1.Parameters.Add(pmtCNPJ);
var pmtnome = cmd1.CreateParameter();
pmtnome.ParameterName = "@nome";
pmtnome.DbType = DbType.String;
pmtnome.Value = _mdlEmpresa.Nome;
cmd1.Parameters.Add(pmtnome);
if (cmd1.ExecuteNonQuery() > 0)
{
ConexaoDB1.Close();
return(true);
}
else
{
ConexaoDB1.Close();
return(false);
}
}
