public static IList<Articulo> GetArticulosExt(string parNom, string parProve, string parFam, string codigo, bool obsoletos)
{
IList<Articulo> la = new List<Articulo>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
a.codartic AS CODARTIC,
a.nomartic AS NOMARTIC,
p.codprove AS CODPROVE,
p.nomprove AS NOMPROVE,
COALESCE(p.nomcomer,'') AS NOMCOMER,
COALESCE(p.domprove,'') AS DOMPROVE,
COALESCE(p.codpobla,'')AS CODPOBLA,
COALESCE(p.pobprove,'') AS POBPROVE,
COALESCE(p.proprove,'') AS PROPROVE,
COALESCE(p.nifPROVE,'') AS NIFPROVE,
COALESCE(p.perprov1,'') AS PERPROV1,
COALESCE(p.telprov1,'') AS TELPROV1,
COALESCE(p.faxprov1,'') AS FAXPROV1,
COALESCE(p.perprov2,'') AS PERPROV2,
COALESCE(p.telprov2,'') AS TELPROV2,
COALESCE(p.faxprov2,'') AS FAXPROV2,
COALESCE(p.maiprov1,'') AS MAIPROV1,
COALESCE(p.maiprov2,'') AS MAIPROV2,
f.codfamia AS CODFAMIA,
f.nomfamia AS NOMFAMIA,
a.preciove AS PRECIOVE,
a.rotacion AS ROTACION,
stk.stock AS STOCK,
COALESCE(slpr.pedido,0) AS PEDIDO,
COALESCE(a.referprov,'') AS REFERPROV,
COALESCE(slp.reservas,0) AS RESERVAS
FROM sartic AS a
LEFT JOIN sprove AS p ON p.codprove = a.codprove
LEFT JOIN sfamia AS f ON f.codfamia = a.codfamia
LEFT JOIN (SELECT SUM(canstock) AS stock, codartic FROM salmac GROUP BY codartic) AS stk ON stk.codartic = a.codartic
LEFT JOIN (SELECT SUM(cantidad) AS reservas, codartic FROM sliped GROUP BY codartic) AS slp ON slp.codartic = a.codartic
LEFT JOIN (SELECT SUM(cantidad) AS pedido, codartic FROM slippr GROUP BY codartic) AS slpr ON slpr.codartic = a.codartic
WHERE TRUE";
if (parNom != "")
sql += String.Format(" AND a.nomartic LIKE '%{0}%'", parNom);
if (parProve != "")
sql += String.Format(" AND p.nomprove LIKE '%{0}%'", parProve);
if (parFam != "")
sql += String.Format(" AND f.nomfamia LIKE '%{0}%'", parFam);
if (codigo != "")
sql += String.Format(" AND a.codartic='{0}'", codigo);
if (!obsoletos)
sql += " AND a.codstatu <> 1";
sql += " ORDER BY a.nomartic";
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Articulo a = new Articulo();
a = GetArticuloExt(rdr);
if (a != null)
{
la.Add(a);
}
}
}
conn.Close();
}
return la;
}
public static string GetArticuloHtmlExt(Articulo a)
{
string html = "";
string plantilla = @"
<div class='panel panel-default'>
<div class='panel-heading'>
<a data-toggle='collapse' data-parent='#accordion' href='#collapse{8}'>
<h4>{0} C:{1}</h4>
</a>
</div>
<div id='collapse{8}' class='panel-collapse collapse'>
<div class='panel-body'>
<div class='container'>
<div class='row'>
<div class='col-sm-12'>
<table class='table table-bordered'>
<tr class='info'>
<th>Familia</th>
<th>Proveedor</th>
<th class='text-center'>Referencia.</th>
</tr>
<tr>
<td>{5}</td>
<td>{6}</td>
<th class='text-center'>{4}</th>
</tr>
</table>
</div>
</div>
<div class='row'>
<div class='col-sm-12'>
<table class='table table-bordered'>
<tr class='info'>
<th class='text-right'>PVP</th>
<th class='text-right'>Stock</th>
<th class='text-right'>Reservas</th>
<th class='text-right'>Pedidos</th>
<th class='text-center'>Rotacion</th>
</tr>
<tr>
<td class='text-right'>{7:#,###,##0.00}</td>
<td class='text-right'>{2:0.00}</td>
<td class='text-right'>{3:0.00}</td>
<td class='text-right'>{10:0.00}</td>
<td class='text-center'>{9}</td>
</tr>
</table>
</div>
</div>
<div class='row'>
<div class='col-sm-10'></div>
<div class='col-sm-2'>
<a class='btn btn-primary btn-lg text-center' href='ArticulosDetalle.aspx?CodArtic={1}'>Ver detalles</a>
</div>
</div>
</div>
</div>
</div>
</div>
";
string pattern = "[^0-9a-zA-z_\\-:]";
Regex rgx = new Regex(pattern);
string cod = rgx.Replace(a.CodArtic, "");
string rotacion = "NO";
if (a.Rotacion)
rotacion = "SI";
html = String.Format(plantilla, a.NomArtic, a.CodArtic, a.Stock, a.Reservas, a.Referprov, a.Familia.NomFamia, a.Proveedor.NomProve, a.Preciove, cod, rotacion, a.Pedido);
return html;
}
public static IList<Articulo> GetArticulos(string parNom, Cliente cliente)
{
IList<Articulo> la = new List<Articulo>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
codartic AS CODARTIC,
nomartic AS NOMARTIC,
preciove AS PRECIOVE,
codfamia AS CODFAMIA,
codmarca AS CODMARCA
FROM sartic
WHERE nomartic LIKE '%{0}%'
AND codstatu <> 1
ORDER BY nomartic";
sql = String.Format(sql, parNom);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Articulo a = new Articulo();
a = GetArticulo(rdr);
if (a != null)
{
a.Precio = GetPrecio(a, cliente);
a.Stock = GetStock(a.CodArtic);
la.Add(a);
}
}
}
conn.Close();
}
return la;
}
public static Articulo GetArticuloExt(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODARTIC")))
return null;
Articulo a = new Articulo();
a.CodArtic = rdr.GetString("CODARTIC");
a.NomArtic = rdr.GetString("NOMARTIC");
a.Preciove = rdr.GetDecimal("PRECIOVE");
a.Familia = GetFamilia(rdr);
a.Proveedor = GetProveedor(rdr);
a.Stock = rdr.GetDecimal("STOCK");
if (rdr.GetInt32("ROTACION") == 1)
a.Rotacion = true;
a.Reservas = rdr.GetDecimal("RESERVAS");
a.Referprov = rdr.GetString("REFERPROV");
a.Pedido = rdr.GetDecimal("PEDIDO");
return a;
}
public static string GetArticuloHtml(Articulo a)
{
string html = "";
string plantilla = @"
<div class='panel panel-default'>
<div class='panel-heading'>
<a data-toggle='collapse' data-parent='#accordion' href='#collapse{8}'>
<h4>{1} C:{0} # {5:#,###,##0.00 €} # Stock: {7:###,##0.00}</h4>
</a>
</div>
<div id='collapse{8}' class='panel-collapse collapse'>
<div class='panel-body'>
<table class='table table-bordered'>
<tr class='info'>
<th>Origen</th>
<th class='text-right'>Precio</th>
<th class='text-right'>Dto1 (%)</th>
<th class='text-right'>Dto2 (%)</th>
<th class='text-right'>Importe</th>
</tr>
<tr>
<td>{6}</td>
<td class='text-right'>{2:#,###,##0.00}</td>
<td class='text-right'>{3:0.00}</td>
<td class='text-right'>{4:0.00}</td>
<td class='text-right'>{5:#,###,##0.00}</td>
</tr>
</table>
</div>
</div>
</div>
";
string pattern = "[^0-9a-zA-z_\\-:]";
Regex rgx = new Regex(pattern);
string cod = rgx.Replace(a.CodArtic, "");
html = String.Format(plantilla, a.CodArtic, a.NomArtic, a.Precio.Pvp, a.Precio.Dto1, a.Precio.Dto2, a.Precio.Importe, a.Precio.Origen, a.Stock, cod);
return html;
}
public static Precio GetPrecioEspeciales(Articulo a, Cliente c)
{
Precio precio = new Precio();
bool dtoPermi = false;
precio.Origen = "ESPECIAL";
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
dtopermi AS DTOPERMI,
precioac AS PRECIOAC,
dtoespec AS DTOESPEC
FROM sprees
WHERE codclien = {0}
AND codartic = '{1}';
";
sql = String.Format(sql, c.CodClien, a.CodArtic);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
precio.Pvp = rdr.GetDecimal("PRECIOAC");
dtoPermi = rdr.GetBoolean("DTOPERMI");
if (!rdr.IsDBNull(rdr.GetOrdinal("DTOESPEC")))
precio.Dto1 = rdr.GetDecimal("DTOESPEC");
}
conn.Close();
}
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
dtopermi AS DTOPERMI,
precionu AS PRECIONU,
dtoespe1 AS DTOESPE1
FROM sprees
WHERE codclien = {0}
AND codartic = '{1}'
AND (fechanue <= '{2:yyyy-MM-dd}');
";
sql = String.Format(sql, c.CodClien, a.CodArtic, DateTime.Now);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
precio.Pvp = rdr.GetDecimal("PRECIONU");
dtoPermi = rdr.GetBoolean("DTOPERMI");
if (!rdr.IsDBNull(rdr.GetOrdinal("DTOESPE1")))
precio.Dto1 = rdr.GetDecimal("DTOESPE1");
}
conn.Close();
}
if (precio.Pvp != 0 && dtoPermi && precio.Dto1 == 0)
{
// calcular los descuentos
precio = GetDescuento(a, c, precio);
}
precio = CalcularDescuento(precio);
return precio;
}
public static Precio GetPrecioTarifas(Articulo a, Cliente c)
{
Precio precio = new Precio();
bool dtoPermi = false;
precio.Origen = "TARIFAS";
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
dtopermi AS DTOPERMI,
precioac AS PRECIOAC
FROM slista
WHERE codlista = {0}
AND codartic = '{1}';
";
sql = String.Format(sql, c.CodTarif, a.CodArtic);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
precio.Pvp = rdr.GetDecimal("PRECIOAC");
dtoPermi = rdr.GetBoolean("DTOPERMI");
}
conn.Close();
}
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
dtopermi AS DTOPERMI,
precionu AS PRECIONU
FROM slista
WHERE codlista = {0}
AND codartic = '{1}'
AND (fechanue <= '{2:yyyy-MM-dd}');
";
sql = String.Format(sql, c.CodTarif, a.CodArtic, DateTime.Now);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
precio.Pvp = rdr.GetDecimal("PRECIONU");
dtoPermi = rdr.GetBoolean("DTOPERMI");
}
conn.Close();
}
if (precio.Pvp != 0 && dtoPermi)
{
// calcular los descuentos
precio = GetDescuento(a, c, precio);
}
precio = CalcularDescuento(precio);
return precio;
}
public static Precio GetPrecio(Articulo a, Cliente c)
{
Precio precio = new Precio();
bool precioMinimo = GetPrecioMinimo();
if (!precioMinimo)
{
precio = GetPrecioPromocion(a, c);
if (precio.Pvp != 0)
return precio;
precio = GetPrecioEspeciales(a, c);
if (precio.Pvp != 0)
return precio;
precio = GetPrecioTarifas(a, c);
if (precio.Pvp != 0)
return precio;
precio = GetPrecioArticulo(a, c);
}
else
{
Precio pAux = new Precio();
pAux.Importe = 9999999;
pAux.Origen = "ERROR";
precio = GetPrecioPromocion(a, c);
if (precio.Importe != 0 && precio.Importe < pAux.Importe)
pAux = precio;
precio = GetPrecioEspeciales(a, c);
if (precio.Importe != 0 && precio.Importe < pAux.Importe)
pAux = precio;
precio = GetPrecioTarifas(a, c);
if (precio.Importe != 0 && precio.Importe < pAux.Importe)
pAux = precio;
precio = GetPrecioArticulo(a, c);
if (precio.Importe != 0 && precio.Importe < pAux.Importe)
pAux = precio;
precio = pAux;
}
return precio;
}
public static Precio GetPrecioArticulo(Articulo a, Cliente c)
{
Precio precio = new Precio();
precio.Origen = "ARTICULO";
precio.Pvp = a.Preciove;
//precio = GetDescuento(a, c, precio);
precio = CalcularDescuento(precio);
return precio;
}
public static Articulo GetArticulo(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODARTIC")))
return null;
Articulo a = new Articulo();
a.CodArtic = rdr.GetString("CODARTIC");
a.NomArtic = rdr.GetString("NOMARTIC");
a.Preciove = rdr.GetDecimal("PRECIOVE");
a.CodFamia = rdr.GetInt32("CODFAMIA");
a.CodMarca = rdr.GetInt32("CODMARCA");
return a;
}
public static IList<LineaStock> GetLineasStock(Articulo articulo)
{
IList<LineaStock> lls = new List<LineaStock>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"
SELECT
s1.codartic AS CODARTIC,
s1.codalmac AS CODALMAC,
s2.nomalmac AS NOMALMAC,
s1.canstock AS CANSTOCK
FROM salmac AS s1
LEFT JOIN salmpr AS s2 ON s2.codalmac = s1.codalmac
WHERE s1.codartic = '{0}'
";
sql = String.Format(sql, articulo.CodArtic);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
lls.Add(GetLineaStock(rdr));
}
}
conn.Close();
}
return lls;
}
public static IList<LineaComponente> GetLineasComponente(Articulo articulo)
{
IList<LineaComponente> llc = new List<LineaComponente>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"
SELECT
s1.numlinea AS NUMLINEA,
s2.nomartic AS NOMARTIC,
s1.cantidad AS CANTIDAD
FROM sarti1 AS s1
LEFT JOIN sartic AS s2 ON s2.codartic = s1.codarti1
WHERE s1.codartic = '{0}';
";
sql = String.Format(sql, articulo.CodArtic);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
llc.Add(GetLineaComponente(rdr));
}
}
conn.Close();
}
return llc;
}
public static Precio GetDescuentoCM(Articulo a, Cliente c, Precio p)
{
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"
SELECT
dtoline1 AS DTOLINE1,
dtoline2 AS DTOLINE2
FROM sdtofm
WHERE codclien = {0}
AND codmarca = {1}
AND fechadto <= '{2:yyyy-MM-dd}';
";
sql = String.Format(sql, c.CodClien, a.CodMarca, DateTime.Now);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
p.Dto1 = rdr.GetDecimal("DTOLINE1");
p.Dto2 = rdr.GetDecimal("DTOLINE2");
}
conn.Close();
}
return p;
}
public static Precio GetDescuento(Articulo a, Cliente c, Precio p)
{
// segun cliente
p = GetDescuentoCFM(a, c, p);
if (p.Dto1 > 0)
return p;
p = GetDescuentoCF(a, c, p);
if (p.Dto1 > 0)
return p;
p = GetDescuentoCM(a, c, p);
if (p.Dto1 > 0)
return p;
// segun actividad
p = GetDescuentoAFM(a, c, p);
if (p.Dto1 > 0)
return p;
p = GetDescuentoAF(a, c, p);
if (p.Dto1 > 0)
return p;
p = GetDescuentoAM(a, c, p);
if (p.Dto1 > 0)
return p;
return p;
}