public static IList<Cliente> GetClientes(string parNom, Agente agente)
{
IList<Cliente> lc = new List<Cliente>();
if (agente == null)
{
lc = GetClientes(parNom);
return lc;
}
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"SELECT
c.codclien AS CODCLIEN,
c.nomclien AS NOMCLIEN,
c.nomcomer AS NOMCOMER,
c.domclien AS DOMCLIEN,
c.codpobla AS CODPOBLA,
c.pobclien AS POBCLIEN,
c.proclien AS PROCLIEN,
c.nifclien AS NIFCLIEN,
c.perclie1 AS PERCLIE1,
c.telclie1 AS TELCLIE1,
c.faxclie1 AS FAXCLIE1,
c.perclie2 AS PERCLIE2,
c.telclie2 AS TELCLIE2,
c.faxclie2 AS FAXCLIE2,
c.maiclie1 AS MAICLIE1,
c.maiclie2 AS MAICLIE2,
c.codmacta AS CODMACTA,
c.codactiv AS CODACTIV,
c.codtarif AS CODTARIF,
c.promocio AS PROMOCIO,
s.nomsitua AS SITUACION,
c.limcredi AS LIMITE_CREDITO,
c.credipriv AS CREDITO_PRIVADO
FROM sclien AS c
LEFT JOIN ssitua AS s ON s.codsitua = c.codsitua
WHERE codagent = {0}
AND s.ocultarbus = 0
AND nomclien LIKE '%{1}%'";
sql = String.Format(sql, agente.CodAgent, parNom);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Cliente c = new Cliente();
c = GetCliente(rdr);
if (c != null)
lc.Add(c);
}
}
conn.Close();
}
return lc;
}
public static IList<Cobro> GetCobros(Cliente cliente)
{
IList<Cobro> lc = new List<Cobro>();
using (MySqlConnection conn = GetConnectionConta())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = @"
SELECT
fecvenci AS FECHAVENCI,
CONCAT(numserie,RIGHT(CONCAT('00000000',codfaccl),7)) AS NUMFACT,
fecfaccl AS FECHAFACT,
nomforpa AS NOMFORPA,
impvenci+IF(gastos IS NULL,0,gastos)-IF(impcobro IS NULL,0,impcobro) AS TOTAL
FROM scobro
INNER JOIN sforpa ON scobro.codforpa=sforpa.codforpa
WHERE scobro.codmacta = '{0}'
AND impvenci+IF(gastos IS NULL,0,gastos)-IF(impcobro IS NULL,0,impcobro) <> 0
ORDER BY fecvenci
";
sql = String.Format(sql, cliente.Codmacta);
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
lc.Add(GetCobro(rdr));
}
}
conn.Close();
}
return lc;
}
public static Cliente GetCliente(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODCLIEN")))
return null;
Cliente c = new Cliente();
c.CodClien = rdr.GetInt32("CODCLIEN");
c.NomClien = rdr.GetString("NOMCLIEN");
c.NomComer = rdr.GetString("NOMCOMER");
c.DomClien = rdr.GetString("DOMCLIEN");
c.CodPobla = rdr.GetString("CODPOBLA");
c.PobClien = rdr.GetString("POBCLIEN");
c.ProClien = rdr.GetString("PROCLIEN");
c.NifClien = rdr.GetString("NIFCLIEN");
if (!rdr.IsDBNull(rdr.GetOrdinal("PERCLIE1")))
c.PerClie1 = rdr.GetString("PERCLIE1");
if (!rdr.IsDBNull(rdr.GetOrdinal("TELCLIE1")))
c.TelClie1 = rdr.GetString("TELCLIE1");
if (!rdr.IsDBNull(rdr.GetOrdinal("FAXCLIE1")))
c.FaxClie1 = rdr.GetString("FAXCLIE1");
if (!rdr.IsDBNull(rdr.GetOrdinal("PERCLIE2")))
c.PerClie2 = rdr.GetString("PERCLIE2");
if (!rdr.IsDBNull(rdr.GetOrdinal("TELCLIE2")))
c.TelClie2 = rdr.GetString("TELCLIE2");
if (!rdr.IsDBNull(rdr.GetOrdinal("FAXCLIE2")))
c.FaxClie2 = rdr.GetString("FAXCLIE2");
if (!rdr.IsDBNull(rdr.GetOrdinal("MAICLIE1")))
c.Maiclie1 = rdr.GetString("MAICLIE1");
if (!rdr.IsDBNull(rdr.GetOrdinal("MAICLIE2")))
c.Maiclie2 = rdr.GetString("MAICLIE2");
if (!rdr.IsDBNull(rdr.GetOrdinal("CODMACTA")))
c.Codmacta = rdr.GetString("CODMACTA");
c.CodActiv = rdr.GetInt32("CODACTIV");
c.CodTarif = rdr.GetInt32("CODTARIF");
c.Promocio = rdr.GetInt32("PROMOCIO");
if (!rdr.IsDBNull(rdr.GetOrdinal("SITUACION")))
c.Situacion = rdr.GetString("SITUACION");
if (!rdr.IsDBNull(rdr.GetOrdinal("LIMITE_CREDITO")))
c.LimiteCredito = rdr.GetDecimal("LIMITE_CREDITO");
int tcred = rdr.GetInt32("CREDITO_PRIVADO");
if (tcred == 1)
c.TipoCredito = "PRIVADO";
return c;
}
public static string GetClienteHtml(Cliente c)
{
string html = "";
if (c == null)
return html;
string plantilla = @"
<div class='container'>
<div class='row'>
<div class='col-sm-12'>
<blockquote>
<strong>{2} {3}</strong><br />
{4}<br />
{5} {6} {7}
</blockquote>
</div>
</div>
<div class='row'>
<div class='col-sm-6'>
<blockquote>
<strong>Administración</strong><br/>
<em>{8}</em><br />
Teléfono:
<a href='tel:{9}'>{9}</a><br />
Email:
<a href='mailto:{10}'>{10}</a>
</blockquote>
</div>
<div class='col-sm-6'>
<blockquote>
<strong>Comercial</strong><br/>
<em>{11}</em><br />
Teléfono:
<a href='tel:{12}'>{12}</a><br />
EMail:
<a href='mailto:{13}'>{13}</a>
</blockquote>
</div>
</div>
</div>
";
html = String.Format(plantilla, c.CodClien, c.NomClien,
c.NifClien, c.NomComer,
c.DomClien, c.CodPobla, c.PobClien, c.ProClien,
c.PerClie1, c.TelClie1, c.Maiclie1,
c.PerClie2, c.TelClie2, c.Maiclie2);
return html;
}
public static string GetTabClientesHtml(Cliente c, int nivel)
{
string html = "";
string tabs = "";
if (nivel == 0)
{
tabs = @"
<ul class='nav nav-tabs'>
<li id='Datos'>
<a href='ClientesDetalle.aspx?CodClien={0}'><h4>Datos</h4></a>
</li>
<li id='Ofertas'>
<a href='ClientesOfertas.aspx?CodClien={0}'><h4>Ofertas</h4></a>
</li>
<li id='Pedidos'>
<a href='ClientesPedidos.aspx?CodClien={0}'><h4>Pedidos</h4></a>
</li>
<li id='Albaranes'>
<a href='ClientesAlbaranes.aspx?CodClien={0}'><h4>Albaranes</h4></a>
</li>
<li id='Facturas'>
<a href='ClientesFacturas.aspx?CodClien={0}'><h4>Facturas</h4></a>
</li>
<li id='Precios'>
<a href='ClientesPrecios.aspx?CodClien={0}'><h4>Precios</h4></a>
</li>
<li id='PreciosEspeciales'>
<a href='ClientesPreciosEspeciales.aspx?CodClien={0}'><h4>Precios especiales</h4></a>
</li>
<li id='DescuentosEspeciales'>
<a href='ClientesDescuentosEspeciales.aspx?CodClien={0}'><h4>Descuentos especiales</h4></a>
</li>
<li id='Contacto'>
<a href='ClientesMapas.aspx?CodClien={0}'><h4>Contacto</h4></a>
</li>
</ul>
";
}
else
{
tabs = @"
<ul class='nav nav-tabs'>
<li id='Datos'>
<a href='ClientesDetalle.aspx?CodClien={0}'><h4>Datos</h4></a>
</li>
<li id='Ofertas'>
<a href='ClientesOfertas.aspx?CodClien={0}'><h4>Ofertas</h4></a>
</li>
<li id='Pedidos'>
<a href='ClientesPedidos.aspx?CodClien={0}'><h4>Pedidos</h4></a>
</li>
<li id='Albaranes'>
<a href='ClientesAlbaranes.aspx?CodClien={0}'><h4>Albaranes</h4></a>
</li>
<li id='Facturas'>
<a href='ClientesFacturas.aspx?CodClien={0}'><h4>Facturas</h4></a>
</li>
<li id='Precios'>
<a href='ClientesPrecios.aspx?CodClien={0}'><h4>Precios</h4></a>
</li>
<li id='Contacto'>
<a href='ClientesMapas.aspx?CodClien={0}'><h4>Contacto</h4></a>
</li>
</ul>
";
}
html = String.Format(tabs, c.CodClien);
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 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 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 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 string GetIndicadoresHtml(Cliente cliente)
{
string html = "";
string plantilla = @"
<div class='panel panel-default'>
<div class='panel-heading'>INDICADORES</div>
<div class='panel-body'>
<table class='table table-bordered'>
<tr>
<th class='text-center info'>Ofertas</th>
<th class='text-center info'>Pedidos</th>
<th class='text-center info'>Albaranes</th>
<th class='text-right success'>Saldo pendiente</th>
<th class='text-right danger'>Saldo vencido</th>
</tr>
<tr>
<td class='text-center'>{0}</td>
<td class='text-center'>{1}</td>
<td class='text-center'>{2}</td>
<td class='text-right'>{3:###,###,##0.00 €}</td>
<td class='text-right'>{4:###,###,##0.00 €}</td>
</tr>
</table>
<table class='table table-bordered'>
<tr>
<th class='text-center info'>Situación</th>
<th class='text-center info'>Límite credito</th>
<th class='text-center info'>Tipo credito</th>
</tr>
<tr>
<td class='text-center'>{5}</td>
<td class='text-center'>{6:###,###,##0.00 €}</td>
<td class='text-center'>{7}</td>
</tr>
</table>
</div>
</div>
";
int numOfertas = 0;
IList<Oferta> ofertas = GetOfertas(cliente.CodClien);
numOfertas = ofertas.Count;
int numPedidos = 0;
IList<Pedido> pedidos = GetPedidos(cliente.CodClien);
numPedidos = pedidos.Count;
int numAlbaranes = 0;
IList<Albaran> albaranes = GetAlbarans(cliente.CodClien);
numAlbaranes = albaranes.Count;
decimal saldoPendiente = 0;
decimal saldoVencido = 0;
IList<Cobro> cobros = GetCobros(cliente);
foreach (Cobro c in cobros)
{
saldoPendiente += c.Total;
if (c.FechaVenci < DateTime.Now)
saldoVencido += c.Total;
}
html = String.Format(plantilla, numOfertas, numPedidos, numAlbaranes, saldoPendiente, saldoVencido, cliente.Situacion, cliente.LimiteCredito, cliente.TipoCredito);
return html;
}
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;
}
