public void FillFourni()
{
OleDbCommand Com;
OleDbDataReader Dr;
try
{
string DBcom = "SELECT ID_FOURNISSEUR,NOM_FOURNISSEUR FROM FOURNISSEUR";
Com = new OleDbCommand(DBcom, DB);
Dr = Com.ExecuteReader();
if (Dr.HasRows == false)
{
return;
}
while (Dr.Read())
{
Fourni_Code.Add(Dr.GetString(0));
Fourni_Name.Add(Dr.GetString(1));
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
public Produtoo FindById(int IdProduto)
{
try
{
OpenConnection();
Cmd = new SqlCommand("select * from Produto where IdProduto = @v1", Con);
Cmd.Parameters.AddWithValue("@v1", IdProduto);
Dr = Cmd.ExecuteReader();
if (Dr.Read())
{
Produtoo p = new Produtoo();
p.IdProduto = Dr.GetInt32(0);
p.Nome = Dr.GetString(1);
p.Preco = Dr.GetDouble(2);
p.Quantidade = Dr.GetInt32(3);
p.DataCompra = Dr.GetDateTime(4);
return(p);
}
return(null);
}
catch
{
throw new Exception("Erro ao Consultar Produto");
}
finally
{
CloseConnection();
}
}
public void setFornecedores(string cnpj)
{
try
{
Conectar();
Cmd = new SqlCommand("ListarFornecedor", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@CNPJ", cnpj);
Dr = Cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
fornecedor.cnpj = Dr.GetString(0);
fornecedor.descFor = Dr.GetString(1);
fornecedor.contatoFor = Dr.GetString(2);
fornecedor.enderecoFor = Dr.GetString(3);
fornecedor.cidade = Dr.GetString(4);
}
}
}
finally
{
Desconectar();
}
}
public List <Produtoo> FindAll()
{
try
{
OpenConnection();
Cmd = new SqlCommand("Select * From Produto", Con);
Dr = Cmd.ExecuteReader();
List <Produtoo> lista = new List <Produtoo>();
while (Dr.Read())
{
Produtoo p = new Produtoo();
p.IdProduto = Dr.GetInt32(0);
p.Nome = Dr.GetString(1);
p.Preco = Dr.GetDouble(2);
p.Quantidade = Dr.GetInt32(3);
p.DataCompra = Dr.GetDateTime(4);
lista.Add(p);
}
return(lista);
}
catch
{
throw new Exception("Erro ao consultar");
}
finally
{
CloseConnection();
}
}
public void setFuncionario(int idFunc)
{
try{
Conectar();
Cmd = new SqlCommand("ListarFuncionario", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@idFuncionario", idFunc);
Dr = Cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
Funcionario.idFuncionario = Dr.GetInt32(0);
Funcionario.nomeFunc = Dr.GetString(1);
Funcionario.senhaFunc = Dr.GetString(2);
Funcionario.RG = Dr.GetString(3);
Funcionario.salario = Dr.GetDouble(4);
Funcionario.email = Dr.GetString(5);
Funcionario.telefone = Dr.GetString(6);
Funcionario.cep = Dr.GetString(7);
}
}
}
finally
{
Desconectar();
}
}
public double getProduto(int codBarras)
{
double precoUnit = 0;
try
{
Conectar();
Cmd = new SqlCommand("valorUnit", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@codBarras", codBarras);
Dr = Cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
Produto.idProduto = Dr.GetInt32(0);
Produto.descProd = Dr.GetString(1);
Produto.preco = Dr.GetDouble(2);
Produto.maisValia = Dr.GetDouble(3);
}
}
}
finally{
Desconectar();
}
return(precoUnit);
}
public bool ReabreChamadoDAO()
{
try
{
SqlCommand Cmd;
SqlDataReader Dr;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
SELECT *
from Chamado where idChamado = @Chamado", Con);
Cmd.Parameters.AddWithValue("@Chamado", ObjChamado.Id);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
ObjChamado.Id = Dr.GetInt32(0);
ObjChamado.IdCliente = Dr.GetInt32(1);
ObjChamado.Assunto = Dr.GetString(2);
ObjChamado.Descricao = Dr.GetString(3);
ObjChamado.IdStatus = Dr.GetInt32(4);
ObjChamado.IdServico = Dr.GetInt32(7);
ObjChamado.IdPrioridade = Dr.GetInt32(8);
}
Dr.Close();
Cmd = new SqlCommand(@"
UPDATE
[dbo].[Chamado] SET
dataFechamento = '2000-01-01 00:00:00.000'
,idStatusChamado = 4
,ContReabertura = @ContReabertura
WHERE idChamado = @Chamado and idEmpresa = @Empresa;", Con);
Cmd.Parameters.AddWithValue("@Chamado", ObjChamado.Id);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Cmd.Parameters.AddWithValue("@ContReabertura", new ManterChamadoDAO(ObjChamado).GetContReaberturaDAO() + 1);
Cmd.ExecuteNonQuery();
return(true);
}
}
catch (SqlException)
{
throw;
}
}
public bool Acesso = false; //Informa se o login é valido
public bool Acessar(int userType, string login, String senha)
{
try
{
Conectar();
Cmd = new SqlCommand("AcessoSistema", Con); //Procedimento que retorna o login
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@userType", userType);
Cmd.Parameters.AddWithValue("@login", login);
Cmd.Parameters.AddWithValue("@senha", senha);
Dr = Cmd.ExecuteReader();
if (Dr.HasRows) //Caso seja o sql tenha retorno, o valor é tido como valido
{
if (userType == 1)
{
while (Dr.Read())
{
Funcionario.idFuncionario = Dr.GetInt32(0);
Funcionario.nomeFunc = Dr.GetString(1);
}
}
else
{
while (Dr.Read())
{
Administrador.idAdmin = Dr.GetInt32(0);
Administrador.nomeAdmin = Dr.GetString(1);
}
}
Acesso = true;
}
}
catch (Exception e)
{
throw new Exception("Erro ao cadastrar: " + e.Message);
}
finally
{
Desconectar();
}
return(Acesso);
}
public List <Servico> ConsultaServicosByTipoDAO()
{
try
{
SqlDataReader Dr;
SqlCommand Cmd;
List <Servico> ServicoList = new List <Servico>();
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
SELECT *
FROM [dbo].[Servico]
WHERE ativo = 1 and idTipo = @IdTipo", Con);
Cmd.Parameters.AddWithValue("@IdTipo", ObjServico.IdTipo);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Servico Servicos = FactoryServico.GetNewServico();
Servicos.Id = Dr.GetInt32(0);
Servicos.IdTipo = Dr.GetInt32(1);
Servicos.NomeServ = Dr.GetString(2);
Servicos.DescServ = Dr.GetString(3);
Servicos.Sla = Dr.GetInt32(4);
Servicos.IdStatus = Dr.GetInt32(5);
ServicoList.Add(Servicos);
}
return(ServicoList);
}
}
catch (SqlException)
{
throw;
}
}
public List <courseClass> fetchCourses(int fetch, int jump)
{
fetch = fetch <= 0 ? 20 : fetch;
jump = jump < 0 ? 0 : jump;
List <courseClass> response = new List <courseClass>();
try
{
SqlConnection con = new SqlConnection(data);
SqlCommand cmd = new SqlCommand("SELECT * FROM courseData ORDER BY created DESC", con);
SqlDataReader Dr;
con.Open();
Dr = cmd.ExecuteReader();
while (Dr.Read())
{
try
{
courseClass reader = new courseClass();
//reader.courseId = Dr.GetGuid(0);
//reader.name = Dr.GetString(1) == null ? "GeneralName" : Dr.GetString(1);
//reader.creator = Dr.GetGuid(2);
//reader.company = Dr.GetGuid(3);
//reader.status = Dr.GetString(4) == null ? "GeneralName" : Dr.GetString(4);
//reader.information = Dr.GetString(5) == null ? "GeneralName" : Dr.GetString(5);
//reader.price = Dr.GetInt32(7).ToString() == null ? 0 : Dr.GetInt32(7);
//reader.currency = Dr.GetString(8) == null ? "GeneralName" : Dr.GetString(8);
//reader.duration = Dr.GetInt32(9).ToString() == null ? 0 : Dr.GetInt32(9);
//reader.durationUnit = Dr.GetString(10) == null ? "GeneralName" : Dr.GetString(10);
// Experimental
reader.courseId = Dr.GetGuid(0);
if (Dr.GetString(1) != null)
{
reader.name = Dr.GetString(1);
}
if (Dr.GetGuid(2) != null)
{
reader.creator = Dr.GetGuid(2);
}
//if (Dr.GetGuid(3) != null) { reader.company = Dr.GetGuid(3); }
reader.created = Dr.GetDateTime(4);
if (Dr.GetString(5) != null)
{
reader.status = Dr.GetString(5);
}
if (Dr.GetString(6) != null)
{
reader.information = Dr.GetString(6);
}
reader.price = 49;
reader.currency = "EUR";
reader.duration = 6;
reader.durationUnit = "DAY";
response.Add(reader);
}
catch (Exception ex)
{
courseClass reader = new courseClass();
reader.status = ex.ToString();
response.Add(reader);
continue;
}
}
Dr.Close();
con.Close();
if (response.Count <= 0)
{
for (int i = 0; i < 5; i++)
{
courseClass reader = new courseClass();
reader.name = "MyCourse" + i;
reader.information = "My new Course is about something cool";
response.Add(reader);
}
}
return(response);
}
catch (SqlException ex)
{
courseClass reader = new courseClass();
reader.courseId = new Guid();
reader.name = "";
reader.creator = new Guid();
reader.company = new Guid();
reader.created = DateTime.Now;
reader.status = "";
reader.information = ex.ToString();
reader.img = "";
reader.price = 0;
reader.currency = "";
reader.duration = 0;
reader.durationUnit = "";
response.Add(reader);
}
catch (Exception e)
{
// Add error reporting
courseClass reader = new courseClass();
reader.courseId = new Guid();
reader.name = "";
reader.creator = new Guid();
reader.company = new Guid();
reader.created = DateTime.Now;
reader.status = "";
reader.information = e.ToString();
reader.img = "";
reader.price = 1;
reader.currency = "";
reader.duration = 1;
reader.durationUnit = "";
response.Add(reader);
}
return(response);
}
/// <summary>
/// Read in data, pivot & buffer for supplied set of rows.
/// This is called for retrieval only, not for search
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
DbCommandMx drd;
int rowsFetched = 0, vosCreated = 0;
MetaTable mt;
MetaColumn mc = null;
DateTime dt;
PivotMetaBroker mb;
List <GenericMetaBroker> mbList;
string cid, pivotKey, tableFilter, s, txt, tok;
int fci, mci, pvi, pci, si, i1;
object[] vo = null;
object o;
if (!PivotInCode) // let Oracle do the pivoting?
{
base.ExecuteQuery(eqp);
return;
}
// Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source
int t0 = TimeOfDay.Milliseconds();
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
mt = eqp.QueryTable.MetaTable;
string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql
MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey];
if (mpd.FirstTableName != mt.Name)
{
return; // retrieve data for all tables when we see first table
}
mpd.ClearBuffers();
// Build sql
StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable
tableFilter = "";
if (mt.TableFilterColumns.Count == 1)
{ // build single in list if single filter column
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(mt.TableFilterValues[0]);
tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")";
}
}
else // multiple table filter columns, build and/or expressions
{
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(" or ");
}
tableFilter = "(" + GetTableFilterCriteria(mt) + ")";
sb.Append(tableFilter);
}
tableFilter = "(" + sb.ToString() + ")";
}
string sql = "select * from " + mt.TableMap + " where ";
if (tableFilter != "")
{
sql += tableFilter + " and ";
}
sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) ";
// Read unpivoted data, merge/pivot & buffer pivoted rows
List <string> keySubset = eqp.SearchKeySubset;
if (keySubset == null)
{
keySubset = GetPreviewSubset(); // assume previewing of single table if no subset
}
List <string> parmList = new List <string>();
for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable);
if (key != null)
{
parmList.Add(key);
}
}
drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.String);
drd.ExecuteListReader(parmList);
while (drd.Read())
{
rowsFetched++;
string tableFilterKey = ""; // get column values to identify table
for (fci = 0; fci < mt.TableFilterColumns.Count; fci++)
{
o = drd.GetObjectByName(mt.TableFilterColumns[fci]);
if (o == null)
{
s = "";
}
else
{
s = o.ToString();
}
if (tableFilterKey != "")
{
tableFilterKey += ", ";
}
tableFilterKey += s;
}
mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey];
if (mt == null)
{
continue; // continue if don't know about this table
}
if (!mpd.MbInstances.ContainsKey(mt.Name))
{
continue; // have row hash for broker?
}
int mbIdx = 0;
mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList);
while (true) // copy out for each metabroker
{
mt = mb.Qt.MetaTable;
if (mt == null)
{
continue;
}
if (mb.MultipivotRowDict == null)
{
mb.MultipivotRowDict = new Dictionary <string, object[]>();
}
string rowKey = "";
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString();
}
rowKey += "<" + s + ">";
}
if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row?
{
vo = (object[])mb.MultipivotRowDict[rowKey];
}
else // new row, create vo for it & fill in merged column values
{
vo = new Object[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values
{
mc = mb.SelectList[si];
if (mc.PivotValues != null)
{
continue; // skip pivoted cols for now
}
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci]))
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (mc.IsKey) // normalize cid adding prefix as needed
{
o = CompoundId.Normalize(o.ToString(), mt);
}
vo[si] = o;
break;
}
}
}
mb.MultipivotRowDict[rowKey] = vo;
vosCreated++;
}
// Pivot out data based on pivot column values
if (mb.PivotKeys == null)
{ // build set of pivot keys for the pivoted columns in the table if not done yet
mb.PivotKeys = new string[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++)
{
mc = mb.SelectList[si];
if (mc.PivotValues == null)
{
continue; // skip non-pivoted cols
}
pivotKey = "";
for (pvi = 0; pvi < mc.PivotValues.Count; pvi++)
{
pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">";
}
mb.PivotKeys[si] = pivotKey;
}
}
pivotKey = "";
for (pci = 0; pci < mt.PivotColumns.Count; pci++)
{ // build pivot key for this unpivoted row
o = drd.GetObjectByName(mt.PivotColumns[pci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString().ToLower();
}
pivotKey += "<" + s + ">";
}
for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values
{
if (mb.PivotKeys[si] == null || // skip non-pivoted cols
pivotKey != mb.PivotKeys[si])
{
continue; // and non-matches
}
mc = mb.SelectList[si];
int ci = drd.Rdr.GetOrdinal(mc.ColumnMap);
if (mc.DataType == MetaColumnType.Integer)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetInt(ci);
}
else // value with possible resultId, linking information
{
txt = drd.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Number)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDouble(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.QualifiedNo)
{
// todo
}
else if (mc.DataType == MetaColumnType.String)
{
if (!mc.DetailsAvailable)
{
vo[si] = drd.GetString(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Date)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDateTime(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Structure)
{ // structures come in as compound identifiers (todo: fix for annotation)
tok = Dr.GetValue(si).ToString();
cid = CompoundId.Normalize(tok, Qt.MetaTable);
vo[si] = cid;
}
else if (mc.DataType == MetaColumnType.MolFormula)
{
vo[si] = drd.GetString(ci);
}
else if (mc.DataType == MetaColumnType.DictionaryId)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Image)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Hyperlink)
{
txt = drd.GetString(ci);
Hyperlink hlink = new Hyperlink();
vo[si] = hlink;
hlink.Text = txt;
}
}
if (mbList == null)
{
break; // single broker
}
mbIdx++; // go to next broker
if (mbIdx >= mbList.Count)
{
break; // at end of brokers?
}
mb = (PivotMetaBroker)mbList[mbIdx];
} // end of broker loop
} // end of read loop
drd.Dispose();
return;
}