Пример #1
0
 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;
 }
Пример #2
0
 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;
 }
Пример #3
0
 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;
 }