public static Agente GetAgente(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODAGENT")))
return null;
Agente a = new Agente();
a.CodAgent = rdr.GetInt32("CODAGENT");
a.NomAgent = rdr.GetString("NOMAGENT");
return a;
}
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<Pedido> GetPedidos(Agente agente)
{
IList<Pedido> lp = new List<Pedido>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = "";
if (agente != null)
{
sql = @"
SELECT
c.nomclien AS CLIENTE,
null AS AGENTE,
sc.numpedcl AS NUMPEDCL,
sc.codclien AS CODCLIEN,
sc.fecpedcl AS FECPEDCL,
sc.fecentre AS FECENTRE,
sl2.total AS TOTALPED,
sl.numlinea AS NUMLINEA,
sl.codartic AS CODARTIC,
sl.nomartic AS NOMARTIC,
sl.precioar AS PRECIOAR,
sl.cantidad AS CANTIDAD,
sl.servidas AS SERVIDAS,
sl.dtoline1 AS DTOLINE1,
sl.dtoline2 AS DTOLINE2,
sl.importel AS IMPORTEL
FROM scaped AS sc
LEFT JOIN sclien AS c ON c.codclien = sc.codclien
LEFT JOIN sagent AS ag ON ag.codagent = c.codagent
LEFT JOIN sliped AS sl ON sl.numpedcl = sc.numpedcl
LEFT JOIN (SELECT numpedcl, SUM(importel) AS total
FROM sliped
GROUP BY numpedcl) AS sl2 ON sl2.numpedcl = sc.numpedcl
WHERE c.codagent = {0}
ORDER BY ag.nomagent, c.nomclien, sc.fecpedcl DESC,sc.numpedcl,sl.numlinea;
";
sql = String.Format(sql, agente.CodAgent);
}
else
{
sql = @"
SELECT
c.nomclien AS CLIENTE,
ag.nomagent AS AGENTE,
sc.numpedcl AS NUMPEDCL,
sc.codclien AS CODCLIEN,
sc.fecpedcl AS FECPEDCL,
sc.fecentre AS FECENTRE,
sl2.total AS TOTALPED,
sl.numlinea AS NUMLINEA,
sl.codartic AS CODARTIC,
sl.nomartic AS NOMARTIC,
sl.precioar AS PRECIOAR,
sl.cantidad AS CANTIDAD,
sl.servidas AS SERVIDAS,
sl.dtoline1 AS DTOLINE1,
sl.dtoline2 AS DTOLINE2,
sl.importel AS IMPORTEL
FROM scaped AS sc
LEFT JOIN sclien AS c ON c.codclien = sc.codclien
LEFT JOIN sagent AS ag ON ag.codagent = c.codagent
LEFT JOIN sliped AS sl ON sl.numpedcl = sc.numpedcl
LEFT JOIN (SELECT numpedcl, SUM(importel) AS total
FROM sliped
GROUP BY numpedcl) AS sl2 ON sl2.numpedcl = sc.numpedcl
ORDER BY ag.nomagent, c.nomclien, sc.fecpedcl DESC,sc.numpedcl,sl.numlinea;
";
}
cmd.CommandText = sql;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
int numPedCl = 0;
Pedido p = null;
while (rdr.Read())
{
// eliminamos pedidos sin líneas
if (rdr.IsDBNull(rdr.GetOrdinal("TOTALPED")))
continue;
if (rdr.GetInt32("NUMPEDCL") != numPedCl)
{
p = GetPedidoAgente(rdr);
numPedCl = p.NumPedcl;
p.LineasPedido.Add(GetLinPedido(rdr));
lp.Add(p);
}
else
{
p.LineasPedido.Add(GetLinPedido(rdr));
}
}
}
}
return lp;
}