public bool Adicionar(Produto produto)//Metódo que retorna um,ou seja,verdadeiro se o produto pôde ser cadastrado sem erro e se não ele retorna 0,ou seja,falso
{
int verifica;
try {
string inserir = "INSERT INTO produto(nome,descricao,marca,tipo,sub_tipo,genero,faixa_etaria,preco_venda,preco_custo,qtd_min,qtd_max,data_criacao,obs,id_fornecedor) VALUES(@nome,@descricao,@marca,@tipo,@sub_tipo,@genero,@faixa_etaria,@preco_venda,@preco_custo,@qtd_min,@qtd_max,@data_criacao,@obs,@id_fornecedor);";
AdProduto = new MySqlCommand(inserir, Con);
Con.Open();
AdProduto.Parameters.Add(new MySqlParameter("nome", produto.Nome));
AdProduto.Parameters.Add(new MySqlParameter("descricao", produto.Descricao));
AdProduto.Parameters.Add(new MySqlParameter("marca", produto.Marca));
AdProduto.Parameters.Add(new MySqlParameter("tipo", produto.Tipo));
AdProduto.Parameters.Add(new MySqlParameter("sub_tipo", produto.SubTipo));
AdProduto.Parameters.Add(new MySqlParameter("genero", produto.Genero));
AdProduto.Parameters.Add(new MySqlParameter("faixa_etaria", produto.FaixaEtaria));
AdProduto.Parameters.Add(new MySqlParameter("preco_venda", produto.PrecoVenda));
AdProduto.Parameters.Add(new MySqlParameter("preco_custo", produto.PrecoCusto));
AdProduto.Parameters.Add(new MySqlParameter("qtd_min", produto.QtdMin));
AdProduto.Parameters.Add(new MySqlParameter("qtd_max", produto.QtdMax));
AdProduto.Parameters.Add(new MySqlParameter("data_criacao", produto.DataCriacao.ToString("yyyy-MM-dd")));
AdProduto.Parameters.Add(new MySqlParameter("obs", produto.Obs));
AdProduto.Parameters.Add(new MySqlParameter("id_fornecedor", produto.Fornecedor.IdFornecedor));
AdProduto.Prepare();
AdProduto.ExecuteNonQuery();
Con.Close();
verifica = 1;
}
catch (Exception erro)
{
verifica = 0;
throw erro;
}
return(verifica > 0);
}
public void SerializeFile(TextBox textbox)
{
CreateFolder();
Con.Open();
Reader = Command.ExecuteReader();
Serializer = new MyDictionary {
MyDict = new Dictionary <string, string>(), FilePath = $@"{textbox.Text}"
};
while (Reader.Read())
{
Serializer.MyDict.Add(Reader.GetString(0), Reader.GetString(1));
}
if (textbox.Text.Contains(".json"))
{
DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(Serializer.GetType());
FileStream stream = File.Create(textbox.Text);
jsonSerializer.WriteObject(stream, Serializer);
stream.Close();
}
if (textbox.Text.Contains(".xml"))
{
DataContractSerializer xmlSerializer = new DataContractSerializer(Serializer.GetType());
FileStream stream = File.Create($@"{textbox.Text}");
xmlSerializer.WriteObject(stream, Serializer);
stream.Close();
}
Con.Close();
}
//Busca as tarefas por datas
public List <Tarefa> ListarPorDataInicialDataFinal(string dtInicial, string dtFinal)
{
try
{
List <Tarefa> lista = new List <Tarefa>();
DataSet ds = new DataSet();
AbrirConexao();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Tarefa WHERE DataEntrega BETWEEN #" + dtInicial + "# AND #" + dtFinal + "#", Con);
da.Fill(ds);
Con.Close();
DataTable dt = ds.Tables[0];
foreach (DataRow rows in dt.Rows)
{
Tarefa t = new Tarefa();
t.Codigo = int.Parse(rows["Codigo"].ToString());
t.Nome = rows["Nome"].ToString();
t.Tipo = rows["Tipo"].ToString();
t.Responsavel = rows["Responsavel"].ToString();
t.DataEntrega = rows["DataEntrega"].ToString().Substring(0, 10);
lista.Add(t);
}
return(lista);
}
catch (Exception er)
{
throw new Exception("Erro ao listar os clientes :" + er.Message);
}
finally
{
FecharConexao();
}
}
public List <string[]> ejcPsdConsultaEndpointAddress(string lifnr, string idproveedor)
{
this.abrirConexion();
string proc = "consultarEndpoints";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@lifnr", lifnr);
SqlCommand.Parameters.AddWithValue("@idProveedor", idproveedor);
SqlDataReader datareader = SqlCommand.ExecuteReader();
//datareader.Read();
List <string[]> listaendpoints = new List <string[]>();
int i = 0;
while (datareader.Read())
{
string[] res = new string[4];
res[0] = datareader[0].ToString().Trim(); //endpoint
res[1] = datareader[1].ToString().Trim(); //descripcion
res[2] = datareader[2].ToString().Trim();
res[3] = datareader[3].ToString().Trim();
listaendpoints.Add(res);
i++;
}
datareader.Dispose();
Con.Close();
return(listaendpoints);
}
/// <summary>
/// Closes all connections and releases all resources
/// </summary>
public void Close()
{
if (_transaction != null)
{
_transaction.Dispose();
}
if (readers.Count > 0)
{
for (int i = 0; i < readers.Count; i++)
{
SQLiteDataReader reader = readers[i];
try {
if (!reader.IsClosed) // don't close if the user of this command has already closed it
{
reader.Close();
reader.Dispose();
}
}catch {}
}
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (Con != null)
{
try {
Con.Close();
Con.Dispose();
}catch {}
_con = null;
}
}
public string ejcPsdUpdateUsuario(string usuario, string nombre, string apellidos, string pass1, string inicioVigencia, string FinVigencia, string esCambiarPassNext, string creadoPor, string email, string usuarioACambiar, string rol, string sqlSociedades)
{
abrirConexion();
string proc = "psdPActualizaUsuario";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@usuario", usuario);
SqlCommand.Parameters.AddWithValue("@nombre", nombre);
SqlCommand.Parameters.AddWithValue("@apellidos", apellidos);
SqlCommand.Parameters.AddWithValue("@password", pass1);
SqlCommand.Parameters.AddWithValue("@inicioVigencia", inicioVigencia).DbType = DbType.DateTime;
SqlCommand.Parameters.AddWithValue("@finVigencia", FinVigencia).DbType = DbType.DateTime;
SqlCommand.Parameters.AddWithValue("@esCambiarNext", esCambiarPassNext);
SqlCommand.Parameters.AddWithValue("@creadoPor", creadoPor);
SqlCommand.Parameters.AddWithValue("@email", email);
SqlCommand.Parameters.AddWithValue("@usuarioACambiar", usuarioACambiar);
SqlCommand.Parameters.AddWithValue("@rol", rol);
SqlCommand.Parameters.AddWithValue("@sqlUpdateSoc", sqlSociedades);
SqlDataReader datareader = SqlCommand.ExecuteReader();
datareader.Read();
string res;
res = datareader[0].ToString().Trim();
datareader.Dispose();
Con.Close();
return(res);
}
public string[] ejcPsdProveedorDetProveedor(
string identificador,
string nombre,
string proveedor,
string idInstancia
)
{
abrirConexion();
string[] res = new string[1];
string proc = "insertProveedorDetProveedor";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@RFC", identificador);
SqlCommand.Parameters.AddWithValue("@nombre", nombre);
SqlCommand.Parameters.AddWithValue("@lifnr", proveedor);
SqlCommand.Parameters.AddWithValue("@idInstancia", int.Parse(idInstancia));
SqlDataReader datareader = SqlCommand.ExecuteReader();
datareader.Read();
res[0] = datareader[0].ToString().Trim();
datareader.Dispose();
Con.Close();
return(res);
}
private int getNextPrintID()
{
SQL = "select max(print_id) from check_queue where elt_account_number = " + elt_account_number;
int id = 0;
Cmd = new SqlCommand(SQL, Con);
try
{
Con.Open();
string id_str = Cmd.ExecuteScalar().ToString();
if (id_str != "")
{
id = Int32.Parse(id_str);
id = id + 1;
}
else
{
id = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
return(id);
}
public bool checkIfExistCheck(CheckQueueRecord cRec)
{
SQL = "select count(print_id) from check_queue where elt_account_number = " + elt_account_number + " and print_id=" + cRec.print_id;
Cmd = new SqlCommand(SQL, Con);
int rowCount = 0;
try
{
Con.Open();
rowCount = Int32.Parse(Cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
if (rowCount > 0)
{
return(true);
}
else
{
return(false);
}
}
public Task <string> Save(SalaryCreateModel aModel)
{
try
{
// const string query = @"UPDATE tbl_EMPLOYEE_HR SET Code=@Code, Name=@Name, PresentAddress=@PresentAddress, PermanentAddress=@PermanentAddress, MobileNo=@MobileNo, DateOfBirth=@DateOfBirth, Gender=@Gender, Nationality=@Nationality, Religion=@Religion, DateOfJoining=@DateOfJoining,
// DateOfConfirmation=@DateOfConfirmation, WorkingStatus=@WorkingStatus, DeparmentId=@DeparmentId, DesignationId=@DesignationId, EmployeeBankAccountNo=@EmployeeBankAccountNo, CompanyBankAccountNo=@CompanyBankAccountNo, CompanyBankId=@CompanyBankId, CompanyBankBranchId=@CompanyBankBranchId,
// BranchId=@BranchId, Basic_Al=@Basic_Al, HouseRent_Al=@HouseRent_Al, Conveyance_Al=@Conveyance_Al, Project_Al=@Project_Al, Mobile_Al=@Mobile_Al, Night_Al=@Night_Al, Medical_Al=@Medical_Al, Technical_Al=@Technical_Al,
// Meal_Al=@Meal_Al, Transport_Al=@Transport_Al, Other_Al=@Other_Al, BasicEarning=@BasicEarning, ProvidentFund_Deduct=@ProvidentFund_Deduct, TDS_Deduct=@TDS_Deduct, Security_Deduct=@Security_Deduct, Others_Deduct=@Others_Deduct, BasicDeduction=@BasicDeduction,
// GrossSalary=@GrossSalary,PaymentType=@PaymentType, PaymentAmountCashPc=@PaymentAmountCashPc, PaymentAmountBankPc=@PaymentAmountBankPc,ShiftStatus=@ShiftStatus, EmpCardNo=@EmpCardNo, IsGetHoliday=@IsGetHoliday, EmpImage=@EmpImage, UserName=@UserName,ProjectId=@ProjectId, Valid=@Valid, EntryDate=@EntryDate WHERE Id=@Id ";
const string query = @" UPDATE tbl_HR_BONUS_REGISTER SET
CashBank=@CashBank,
BonusPc=@BonusPc,
BonusAmount=@BonusAmount
WHERE Id=@EmpId ";
Con.Open();
var cmd = new SqlCommand(query, Con);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@EmpId", aModel.EmpId);
cmd.Parameters.AddWithValue("@BonusAmount", aModel.BonusAmount);
cmd.Parameters.AddWithValue("@BonusPc", aModel.PrepareBonus);
cmd.Parameters.AddWithValue("@CashBank", aModel.CashBank);
cmd.ExecuteNonQuery();
Con.Close();
return(Task.FromResult <string>("Update success"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public string getCheckNo(int print_id)
{
string check_no = "";
SQL = "select check_no from check_queue where elt_account_number = " + elt_account_number + " and print_id = " + print_id;
Cmd = new SqlCommand(SQL, Con);
try
{
Con.Open();
check_no = Cmd.ExecuteScalar().ToString();
if (check_no == "")
{
check_no = "0";
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
return(check_no);
}
public List <IdNameForDropdownModel> GetIdCasCadeDropDown(string query)
{
var lists = new List <IdNameForDropdownModel>();
try
{
Con.Open();
var cmd = new SqlCommand(query, Con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
lists.Add(new IdNameForDropdownModel()
{
Id = Convert.ToInt32(rdr["Id"]),
Name = rdr["Name"].ToString(),
// CatId = Convert.ToInt32(rdr["CatId"])
});
}
rdr.Close();
Con.Close();
return(lists);
}
catch (Exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
throw;
}
}
public Order Update(Order order)
{
//do some checks on items before updating
Con = CreateConnection();
Query = "update Orders " +
"set CustomerID=@CustomerID," +
"DateCreated=@DateCreated," +
"Status=@Status";
Com.CommandText = Query;
Com.Connection = Con;
Com.Parameters.Clear();
Com.Parameters.Add(new SqlParameter("@CustomerID", order.CustomerNumber));
Com.Parameters.Add(new SqlParameter("@DateCreated", order.DateCreated));
Com.Parameters.Add(new SqlParameter("@Status", order.OrderStatus));
Com.ExecuteNonQuery();
Com.Dispose();
Con.Close();
order.Products.ToList().ForEach(p =>
{
UpdateOrderItem(p.SKU, p.Quantity, order.OrderNumber);
});
return(order);
}
//Save Order
public void Save(Order order)
{
if (order.Products == null || order.Products.Count == 0)
{
throw new EmptyOrderException();
}
Con = CreateConnection();
Query = "SaveOrder";
Com.CommandText = Query;
Com.Connection = Con;
Com.CommandType = CommandType.StoredProcedure;
Com.Parameters.Clear();
Com.Parameters.Add(new SqlParameter("@CustomerID", order.CustomerNumber));
string OrderNumber = Convert.ToString(Com.ExecuteScalar());
order.OrderNumber = OrderNumber;
Com.Dispose();
Con.Close();
order.Products.ToList().ForEach(p =>
{
InsertOrderItem(p.SKU, order.OrderNumber, p.Quantity);
});
}
public void ejcPsdUpdateConfig(string esActivPortal, string correoAsunto, string correoCuerpo, string email,
string emailPass, string esCreacionUsuarios, string maxUsuarios, string esBloqSociedad,
string numPassRecordar, string intervalTiempo, string maxIntentosFail, string configPor, string caducidadPass,
string identificador, string strignSQLComplemento, string tiempoBloqAdmin, string maxXML)
{
abrirConexion();
string proc = "configuracionGeneral";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@esActivPortal", esActivPortal);
SqlCommand.Parameters.AddWithValue("@correoAsunto", correoAsunto);
SqlCommand.Parameters.AddWithValue("@correoCuerpo", correoCuerpo);
SqlCommand.Parameters.AddWithValue("@email", email);
SqlCommand.Parameters.AddWithValue("@emailPass", emailPass);
SqlCommand.Parameters.AddWithValue("@esCreacionUsuarios", esCreacionUsuarios);
SqlCommand.Parameters.AddWithValue("@maxUsuarios", maxUsuarios);
SqlCommand.Parameters.AddWithValue("@esBloqSociedad", esBloqSociedad);
SqlCommand.Parameters.AddWithValue("@numPassRecordar", numPassRecordar);
SqlCommand.Parameters.AddWithValue("@intervalTiempo", intervalTiempo);
SqlCommand.Parameters.AddWithValue("@maxIntentosFail", maxIntentosFail);
SqlCommand.Parameters.AddWithValue("@configPor", configPor);
SqlCommand.Parameters.AddWithValue("@caducidadPass", caducidadPass);
SqlCommand.Parameters.AddWithValue("@identificadorMail", identificador);
SqlCommand.Parameters.AddWithValue("@stringSQLComplement", strignSQLComplemento);
SqlCommand.Parameters.AddWithValue("@tiempoBloqAdmin", tiempoBloqAdmin);
SqlCommand.Parameters.AddWithValue("@maxXML", maxXML);
SqlCommand.ExecuteNonQuery();
Con.Close();
}
public bool voidCheck(int print_id)
{
SQL = "update check_queue set chk_void='Y' where elt_account_number = " + elt_account_number + " and print_id = " + print_id;
Cmd = new SqlCommand(SQL, Con);
int rowCount = 0;
try
{
Con.Open();
rowCount = Int32.Parse(Cmd.ExecuteNonQuery().ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
if (rowCount > 0)
{
return(true);
}
else
{
return(false);
}
}
public string ejcQuery(string sqlString)
{
abrirConexion();
string proc = "ejecQuery";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@sqlString", sqlString);
SqlDataReader datareader = SqlCommand.ExecuteReader();
datareader.Read();
string res = "";
try
{
res = datareader[0].ToString().Trim();
}
catch (Exception)
{
}
datareader.Dispose();
Con.Close();
return(res);
}
public Task <string> Save(PartyInfoModel aModel)
{
try
{
string partyId = GetPartyId();
const string query = @"INSERT INTO PartyInfo (PartyId,PartyName,Address,ContactNo,OpeningBal,TrDate,UserName,EntryTime,ShowPC,PStatus,VendorFor,Ratio)
VALUES (@PartyId,@PartyName,@Address,@ContactNo,@OpeningBal,@TrDate,@UserName,@EntryTime,@ShowPC,@PStatus,@VendorFor,@Ratio)" ;
Con.Open();
var cmd = new SqlCommand(query, Con);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PartyId", partyId);
cmd.Parameters.AddWithValue("@PartyName", aModel.PartyName);
cmd.Parameters.AddWithValue("@Address", aModel.Address);
cmd.Parameters.AddWithValue("@ContactNo", aModel.ContactNo);
cmd.Parameters.AddWithValue("@OpeningBal", aModel.OpeningBal);
cmd.Parameters.AddWithValue("@TrDate", aModel.TrDate);
cmd.Parameters.AddWithValue("@UserName", aModel.UserName);
cmd.Parameters.AddWithValue("@EntryTime", aModel.EntryTime);
cmd.Parameters.AddWithValue("@ShowPC", aModel.ShowPC);
cmd.Parameters.AddWithValue("@PStatus", aModel.PStatus);
cmd.Parameters.AddWithValue("@VendorFor", aModel.VendorFor);
cmd.Parameters.AddWithValue("@Ratio", aModel.Ratio);
cmd.ExecuteNonQuery();
Con.Close();
return(Task.FromResult("Save successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public string ejcPsdActualizaRol(string nombreRol, string esActive, string facturas, string partidas, string pagos, string datosMaestros, string usuarios, string idAnterior, string esCreacion)
{
abrirConexion();
string proc = "actualizaRol";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@idAnterior", idAnterior);
SqlCommand.Parameters.AddWithValue("@nombreRol", nombreRol);
SqlCommand.Parameters.AddWithValue("@esActive", esActive);
SqlCommand.Parameters.AddWithValue("@facturas", facturas);
SqlCommand.Parameters.AddWithValue("@partidas", partidas);
SqlCommand.Parameters.AddWithValue("@pagos", pagos);
SqlCommand.Parameters.AddWithValue("@datosMaestros", datosMaestros);
SqlCommand.Parameters.AddWithValue("@usuarios", usuarios);
SqlCommand.Parameters.AddWithValue("@esCreacion", esCreacion);
SqlDataReader datareader = SqlCommand.ExecuteReader();
datareader.Read();
string res;
res = datareader[0].ToString().Trim();
datareader.Dispose();
Con.Close();
return(res);
}
public void Update(Product product)
{
Con = this.CreateConnection();
Query = "UpdateProduct";
Com.Connection = Con;
Com.CommandText = Query;
Com.CommandType = CommandType.StoredProcedure;
Com.Parameters.Clear();
Com.Parameters.Add(new SqlParameter("@CategoryID", product.Category.ID));
Com.Parameters.Add(new SqlParameter("@ProductID", product.ID));
Com.Parameters.Add(new SqlParameter("@PromoDept", product.PromoDept));
Com.Parameters.Add(new SqlParameter("@PromoFront", product.PromoFront));
Com.Parameters.Add(new SqlParameter("@Name", product.Name));
Com.Parameters.Add(new SqlParameter("@Description", product.Description));
Com.ExecuteNonQuery();
Com.Dispose();
Con.Close();
}
public string[] ejcPsdVerifcarUsuario(string usuario, string password)
{
abrirConexion();
string proc = "verifUser";
//password = "";
//string ret = "";
SqlCommand = new SqlCommand(proc, Con);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@usuario", usuario);
SqlCommand.Parameters.AddWithValue("@pass", password);
SqlDataReader datareader = SqlCommand.ExecuteReader();
datareader.Read();
string[] res2 = new string[10];
res2[0] = datareader[0].ToString().Trim();
res2[1] = datareader[1].ToString().Trim();
res2[2] = datareader[2].ToString().Trim();
res2[3] = datareader[3].ToString().Trim();
res2[4] = datareader[4].ToString().Trim();
res2[5] = datareader[5].ToString().Trim();
res2[6] = datareader[6].ToString().Trim();
res2[7] = datareader[7].ToString().Trim();
res2[8] = datareader[8].ToString().Trim();
res2[9] = datareader[9].ToString().Trim();
datareader.Dispose();
Con.Close();
return(res2);
}
public static bool AddScore(DateTime data, int number_of_turns, string who_won)
{
bool wynik = false;
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
QuerySQL = string.Format("INSERT INTO scores(date, number_of_turns, who_won) VALUES('{0}', {1}, '{2}')", data.ToString("yyyy-MM-dd HH:MM:ss"), number_of_turns, who_won);
Command.CommandText = QuerySQL;
Command.ExecuteNonQuery();
}
catch (Exception ex)
{
string byk = string.Format("Błąd\n{0}", ex.Message);
MessageBox.Show(byk, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Con.Close();
}
return(wynik);
}
public IEnumerable <CartItems> GetCartItems(int userId)
{
IEnumerable <CartItems> list = new List <CartItems>();
try
{
var query = $@"select ci.Id [CartItemId],ci.ProductId,ci.Quantity,ci.[Json],ci.UnitPrice,ci.UserId,ci.DateModified,ci.DateAdded ,
p.Name as ProductName,p.UniqueId as ProductUniqueKey, Concat(ud.FirstName,' ',ud.lastname) as [CustomerName],(select top 1 icr.[image] from tbl_ICRWithProduct icr where
icr.ProductId=p.Id) as [CoverImage]
from cartitem ci
Left join Products p on p.id= ci.productId
left join [Users] u on u.Id=ci.userid
left join [UserDetail] ud on ud.UserId=u.Id
where ci.userid={userId} and ci.isactive=1 ";
Con.Open();
list = Con.Query <CartItems>(query, commandTimeout: 0);
}
catch (Exception e)
{
Log.Debug(e);
}
finally
{
Con.Close();
}
return(list);
}
public void Update(Manufacturer manufacturer)
{
Query = "UpdateManufacturer";
Con = CreateConnection();
Com.Connection = Con;
Com.CommandText = Query;
Com.CommandType = CommandType.StoredProcedure;
Com.Parameters.Clear();
Com.Parameters.Add(new SqlParameter("@ManufacturerID", manufacturer.ID));
Com.Parameters.Add(new SqlParameter("@Name", manufacturer.Name));
Com.Parameters.Add(new SqlParameter("@Logo", manufacturer.Logo));
Com.Parameters.Add(new SqlParameter("@Email", manufacturer.Address.Email));
Com.Parameters.Add(new SqlParameter("@PhoneNumber", manufacturer.Address.PhoneNumber));
Com.Parameters.Add(new SqlParameter("@WorkNumber", manufacturer.Address.WorkNumber));
Com.Parameters.Add(new SqlParameter("@AddressID", manufacturer.Address.ID));
Com.Parameters.Add(new SqlParameter("@Address1", manufacturer.Address.Address1));
Com.Parameters.Add(new SqlParameter("@Address2", manufacturer.Address.Address2));
Com.ExecuteNonQuery();
Con.Close();
}
//Busca todas as tarefas que estão expirando na data atual
public List <Tarefa> ListarPorData(string dataAtual)
{
try
{
List <Tarefa> lista = new List <Tarefa>();
DataSet ds = new DataSet();
AbrirConexao();
//ESSE SQL FAZ COM QUE AS DATAS SEJAM COMPARADA, ASSIM SABEMOS QUEM ESTÁ, QUAL TAREFA ESTÁ ATRASADA OU NO DIA FINAL DE ENTREGA.
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Tarefa WHERE DataEntrega <= #" + dataAtual + "# order by DataEntrega", Con);
da.Fill(ds);
Con.Close();
DataTable dt = ds.Tables[0];
foreach (DataRow rows in dt.Rows)
{
Tarefa t = new Tarefa();
t.Codigo = int.Parse(rows["Codigo"].ToString());
t.Nome = rows["Nome"].ToString();
t.Tipo = rows["Tipo"].ToString();
t.Responsavel = rows["Responsavel"].ToString();
t.DataEntrega = rows["DataEntrega"].ToString().Substring(0, 10);
lista.Add(t);
}
return(lista);
}
catch (Exception er)
{
throw new Exception("Erro ao listar os clientes :" + er.Message);
}
finally
{
FecharConexao();
}
}
/**
* Autor : Luis Jesús Castro Guerrero
* Fecha : Viernes 16 de mayo 2014, 04:19 PM.
* Descripción: Método que ejecuta el procedimiento psdConsultarPantallaPorIdRol, para obtener las pantallas que corresponden al rol que esta asignado al usuario.
* **/
public int[] ejcPsdConsultaPantallaByIdRol(int idRol)
{
this.abrirConexion();
this.SqlCommand = new SqlCommand("psdConsultarPantallaPorIdRol", this.Con);
this.SqlCommand.CommandType = CommandType.StoredProcedure;
this.SqlCommand.Parameters.AddWithValue("@idRol", idRol);
SqlDataReader datRdr = this.SqlCommand.ExecuteReader();
int[] idPantalla = null;
if (datRdr.HasRows)
{
List <int> idPantallas = new List <int>();
while (datRdr.Read())
{
idPantallas.Add(int.Parse(datRdr["idPantalla"].ToString()));
}
idPantalla = new int[idPantallas.Count];
int i = 0;
foreach (int item in idPantallas)
{
idPantalla[i] = item;
i++;
}
}
Con.Close();
return(idPantalla ?? new int[] { });
}
public void ExportAsTxt(TextBox textBoxPath)
{
CreateFolder();
using (var command = new SqlCommand(SqlQuery, Con))
{
Con.Open();
using (var reader = command.ExecuteReader())
{
string filePath = $@"{textBoxPath.Text}";
if (filePath == @"C:\MyDictionary\")
{
filePath = @"C:\MyDictionary\Dictionary.txt";
}
string wordpairs = "";
while (reader.Read())
{
if (reader.GetString(0) != "")
{
wordpairs += reader.GetString(0) + " - " + reader.GetString(1) + "\n";
}
}
File.WriteAllText(filePath, wordpairs);
}
Con.Close();
}
}
/**
* Autor : Luis Jesús Castro Guerrero
* Fecha : Lunes 02 de junio 2014, 4:45 PM
* Descripción: Método que ejecuta el procedimiento consultarValFactByIdGrpVal,para obtener los id de la validaciones que contiene el id del grupo de validaciones
* **/
public int[] ejcPsdConsultaValFactByIdGrpVal(int idGrupoValidacion)
{
this.abrirConexion();
this.SqlCommand = new SqlCommand("consultarValFactByIdGrpVal", this.Con);
this.SqlCommand.CommandType = CommandType.StoredProcedure;
this.SqlCommand.Parameters.AddWithValue("@idGrupoValidacion", idGrupoValidacion);
SqlDataReader datRdr = this.SqlCommand.ExecuteReader();
int[] idValidacion = null;
if (datRdr.HasRows)
{
List <int> idValidaciones = new List <int>();
while (datRdr.Read())
{
idValidaciones.Add(int.Parse(datRdr["idValidacionFactura"].ToString()));
}
idValidacion = new int[idValidaciones.Count];
int i = 0;
foreach (int item in idValidaciones)
{
idValidacion[i] = item;
i++;
}
}
Con.Close();
return(idValidacion ?? new int[] { });
}
//Busca todas as tarefas no banco de dados
public List <Tarefa> Listar()
{
try
{
List <Tarefa> lista = new List <Tarefa>();
DataSet ds = new DataSet();
AbrirConexao();
OleDbDataAdapter da = new OleDbDataAdapter("Select * from Tarefa order by Codigo", Con);
da.Fill(ds);
Con.Close();
DataTable dt = ds.Tables[0];
foreach (DataRow rows in dt.Rows)
{
Tarefa t = new Tarefa();
t.Codigo = int.Parse(rows["Codigo"].ToString());
t.Nome = rows["Nome"].ToString();
t.Tipo = rows["Tipo"].ToString();
t.Responsavel = rows["Responsavel"].ToString();
t.DataEntrega = rows["DataEntrega"].ToString().Substring(0, 10);
lista.Add(t);
}
return(lista);
}
catch (Exception er)
{
throw new Exception("Erro ao listar as tarefas :" + er.Message);
}
finally
{
FecharConexao();
}
}
public bool AtualizarProduto(Produto produto)//Metódo que retorna um,ou seja,verdadeiro se o produto pôde ser atualizado sem erro e se não ele retorna 0,ou seja,falso
{
int verifica;
try {
string editar_produto = "UPDATE produto SET nome=@nome,descricao=@descricao,marca=@marca,tipo=@tipo,sub_tipo=@sub_tipo,genero=@genero,faixa_etaria=@faixa_etaria,preco_venda=@preco_venda,preco_custo=@preco_custo,qtd_min=@qtd_min,qtd_max=@qtd_max,data_criacao=@data_criacao,obs=@obs,id_fornecedor=@id_fornecedor where id_produto=@id_produto";
AdProduto = new MySqlCommand(editar_produto, Con);
Con.Open();
AdProduto.Parameters.Add(new MySqlParameter("id_produto", produto.IdProduto));
AdProduto.Parameters.Add(new MySqlParameter("nome", produto.Nome));
AdProduto.Parameters.Add(new MySqlParameter("descricao", produto.Descricao));
AdProduto.Parameters.Add(new MySqlParameter("marca", produto.Marca));
AdProduto.Parameters.Add(new MySqlParameter("tipo", produto.Tipo));
AdProduto.Parameters.Add(new MySqlParameter("sub_tipo", produto.SubTipo));
AdProduto.Parameters.Add(new MySqlParameter("genero", produto.Genero));
AdProduto.Parameters.Add(new MySqlParameter("faixa_etaria", produto.FaixaEtaria));
AdProduto.Parameters.Add(new MySqlParameter("preco_venda", produto.PrecoVenda));
AdProduto.Parameters.Add(new MySqlParameter("preco_custo", produto.PrecoCusto));
AdProduto.Parameters.Add(new MySqlParameter("qtd_min", produto.QtdMin));
AdProduto.Parameters.Add(new MySqlParameter("qtd_max", produto.QtdMax));
AdProduto.Parameters.Add(new MySqlParameter("data_criacao", produto.DataCriacao.ToString("yyyy-MM-dd")));
AdProduto.Parameters.Add(new MySqlParameter("obs", produto.Obs));
AdProduto.Parameters.Add(new MySqlParameter("id_fornecedor", produto.Fornecedor.IdFornecedor));
AdProduto.Prepare();
AdProduto.ExecuteNonQuery();
Con.Close();
verifica = 1;
}
catch (Exception erro) {
verifica = 0;
throw erro;
}
return(verifica > 0);
}
