public RequestHTTP getComprobantesFacturados(string query, int offset, int codven, bool veTodos)
{
var res = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
string queryVende = " and codven = " + codven;
if (veTodos)
{
queryVende = "";
}
res.objeto = bd.Database.SqlQuery <ivavenComprobante>(
"select fecha, tipodoc, letra, punto, numero,hasta ,nrocli, razsoc, total, cae, vencecae, numerofe,idpetic,ctacon,ctaconex,empresaid, id, provin, " +
"remito " +
"from ivaven " +
"where 1=1 " + query + " " + queryVende + " order by id desc offset " + offset + " rows fetch next 20 row only"
).ToList();
}
}catch (Exception ex)
{
res = res.falla(ex);
}
return(res);
}
public RequestHTTP getElement(string query)
{
var diccionario = new Dictionary <string, object>();
string sql = query;
connection = new SqlConnection(connetionString);
var req = new RequestHTTP();
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
diccionario.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
}
dataReader.Close();
command.Dispose();
connection.Close();
req.objeto = diccionario;
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------CLIENTES
public RequestHTTP getClientes(int codven, bool veTodos, int offset)
{
var req = new RequestHTTP();
try
{
List <ClienteBuscador> clientes;
using (GestionEntities bd = new GestionEntities())
{
if (veTodos)
{
req.objeto = bd.Database.SqlQuery <ClienteBuscador>(
"select nrocli, razsoc, cuit, fantasia, direcc, telef1, telef2, codven " +
"from cliente " +
"order by nrocli " +
"offset " + offset + " " +
"rows fetch next 20 row only").ToList();
}
else
{
req.objeto = bd.Database.SqlQuery <ClienteBuscador>(
"select nrocli, razsoc, cuit, fantasia, direcc, telef1, telef2, codven " +
"from cliente " +
"where codven = " + codven + " " +
"order by nrocli " +
"offset " + offset + " rows fetch next 20 row only").ToList();
}
}
return(req);
}catch (Exception e)
{
return(req.falla(e));
}
}
public RequestHTTP getCliente(int paran)
{
cliente cli;
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
if (bd.cliente.Where(a => a.nrocli == paran).Count() == 1)
{
cli = bd.cliente.Single(a => a.nrocli == paran);
}
else
{
cli = new cliente();
cli.nrocli = 0;
}
req.objeto = cli;
return(req);
}
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getVendedoresFiltro(string param)
{
var req = new RequestHTTP();
try
{
int nrocli;
using (GestionEntities bd = new GestionEntities())
{
if (int.TryParse(param, out nrocli))
{
nrocli = Convert.ToInt32(param);
req.objeto = bd.Database.SqlQuery <vendedor>("select codven, nombre from vende where codven = " + param + " and nombre like '%" + param + "%'").ToList();
}
else
{
req.objeto = bd.Database.SqlQuery <vendedor>("select codven, nombre from vende where nombre like '%" + param + "%'").ToList();
}
return(req);
}
}
catch (Exception e)
{
return(req.falla(e));
}
}
public RequestHTTP finalizarPedido(JObject jCabYDet)
{
var req = new RequestHTTP();
try
{
var jCabecera = (JObject)jCabYDet["cabecera"];
int punto = (int)jCabecera["punto"],
empresa = (int)jCabecera["empresa"];
decimal bonifcli = (decimal)jCabYDet["bonifcli"];
PedidoCab cabecera = armaCabecera(jCabecera, bonifcli);
List <PedidoDet> detalles = armaDetalle(cabecera, jCabYDet["lista"], bonifcli);
using (GestionEntities bd = new GestionEntities())
{
bd.PedidoCab.Add(cabecera);
foreach (var p in detalles)
{
bd.PedidoDet.Add(p);
}
factura fac = bd.factura.Single(a => a.PUNTO == punto && a.EmpresaId == empresa);
bd.factura.Attach(fac);
fac.PEDIDO++;
bd.SaveChanges();
req.objeto = new compi(cabecera.nroped, cabecera.punto, cabecera.tipodoc, cabecera.letra, cabecera.id);
return(req);
}
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getComprasProvee(int empresa, string desde, string hasta, int concepto, bool cotizacion)
{
var req = new RequestHTTP();
try
{
string ccCotiza = "and tipodoc <> 'CT'";
using (GestionEntities bd = new GestionEntities())
{
if (!cotizacion)
{
ccCotiza = "";
}
req.objeto = bd.Database.SqlQuery <comprasProveedores>(
"select max(nropro) nropro, max(razsoc) razsoc, sum(neto + neto1 + exento) total " +
"from ivacom " +
"where fecha >= '" + desde + "' and fecha <= '" + hasta + "' and concepto = " + concepto + " " + ccCotiza + " " +
"group by nropro " +
"order by total desc").ToList();
}
return(req);
}catch (Exception e)
{
return(req.falla(e));
}
}
//usado mostrar en la cabecera
public RequestHTTP getClientePresupuesto(int nrocli)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
req.objeto = bd.Database.SqlQuery <ClientePesupuesto>("SELECT dbo.cliente.nrocli, " +
" dbo.cliente.razsoc, dbo.cliente.direcc, dbo.cliente.locali AS localicod, " +
" dbo.localidades.nombre AS nombreLocali, depto.nombre AS deptoNombre, depto.id," +
" depto.provincia_id, dbo.provincias.nombre AS nombreProv, dbo.pais.pais, " +
" dbo.cliente.cuit, dbo.cliente.respon AS Iva, dbo.iva.descripcion AS descriIva, " +
" dbo.cliente.tipodoc, dbo.cliente.condicion, dbo.cliente.lista, dbo.vende.codven," +
" dbo.vende.nombre AS nombreVende, dbo.cliente.respon, dbo.cliente.telef1, " +
" dbo.cliente.bonif, dbo.cliente.bonif1, dbo.cliente.bonif2, dbo.cliente.bonif3," +
" dbo.cliente.bonif4, dbo.cliente.convenio, dbo.cliente.nopercib, dbo.cliente.expreso," +
" dbo.cliente.cond_vta" +
" FROM dbo.cliente" +
" LEFT OUTER JOIN dbo.localidades ON dbo.localidades.id = dbo.cliente.locali " +
" LEFT OUTER JOIN dbo.departamentos AS depto ON depto.id = dbo.localidades.departamento_id" +
" LEFT OUTER JOIN dbo.provincias ON dbo.provincias.id = depto.provincia_id " +
" LEFT OUTER JOIN dbo.pais ON dbo.provincias.pais_id = dbo.pais.id " +
" LEFT OUTER JOIN dbo.iva ON dbo.iva.id = dbo.cliente.respon " +
" LEFT OUTER JOIN dbo.vende ON dbo.vende.codven = dbo.cliente.codven where nrocli = " + nrocli).Single();
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getCobranzasPorVendedor(int empresa, int codven, bool cotizaciones, string desde, string hasta, int group)
{
var req = new RequestHTTP();
string qCotizaciones = "";
string labelNombre = "";
string groupb = "";
string order = "";
try
{
if (cotizaciones)
{
qCotizaciones = " and clicta.tipdoco <> 'CT'";
}
string consulta = "select sum(netocob) total, " + labelNombre + ", max(vende.codven) codven " +
"from clicta " +
"left join cliente on cliente.nrocli = clicta.nrocli " +
"left join vende on vende.codven = cliente.codven " +
"where empresa = " + empresa + " and fecha >= '" + desde + "' and fecha <= '" + hasta + "' and clicta.tipodoc = 'RC' and anulado = 0 " +
"and vende.codven = " + codven + qCotizaciones +
"group by " + groupb +
"having sum(netocob) <> 0 " +
"order by " + order;
switch (group)
{
case 1: //agrupado por dia
labelNombre = "convert(char(10),fecha,103) label, max(vende.nombre) nombre";
groupb = " fecha ";
order = " fecha ";
break;
case 2: //agrupado por semana
labelNombre = "convert(varchar,DATEPART(wk, fecha))+'/'+convert(varchar,year(fecha)) label, max(vende.nombre) nombre";
groupb = " DATEPART(wk, fecha), year(fecha) ";
order = " year(fecha), DATEPART(wk, fecha) ";
break;
case 3: //agrupado x mes
labelNombre = " convert(varchar,month(fecha))+'/'+ convert(varchar,year(fecha)) label, max(vende.nombre) nombre ";
groupb = " month(fecha), year(fecha) ";
order = " year(fecha), month(fecha) ";
break;
}
using (GestionEntities bd = new GestionEntities())
req.objeto = bd.Database.SqlQuery <ventasVendedor>(consulta).ToList();
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
public RequestHTTP getProductos(string value, int empid, int offset)
{
var req = new RequestHTTP();
try
{
List <Producto> lista;
using (GestionEntities bd = new GestionEntities())
{
bd.Database.CommandTimeout = 30;
if (value == "0")
{
lista = bd.Database.SqlQuery <Producto>(
"select pathfoto, stock.codpro, stock.descri, oferta, boniprod, incluido, ivaart.porcen1, envases.descri AS descriEnvase," +
" envases.codigo AS codEnvase, envases.simbolo AS simboloEnvase, isnull(stk.saldo,0) saldo " +
"FROM stock " +
"LEFT OUTER JOIN ivaart ON ivaart.codigo = stock.ivagrupo " +
"LEFT OUTER JOIN envases ON envases.id = stock.envase " +
"LEFT OUTER JOIN SaldoSTKALL(" + empid + ") AS stk ON stk.codpro = stock.codpro"
).ToList();
}
else
{
lista = bd.Database.SqlQuery <Producto>(
"select pathfoto, stock.id, stock.codpro, stock.descri, oferta, boniprod, incluido, ivaart.porcen1, envases.descri AS descriEnvase," +
" envases.codigo AS codEnvase, envases.simbolo AS simboloEnvase, isnull(stk.saldo,0) saldo " +
"FROM stock " +
"LEFT OUTER JOIN ivaart ON ivaart.codigo = stock.ivagrupo " +
"LEFT OUTER JOIN envases ON envases.id = stock.envase " +
"LEFT OUTER JOIN SaldoSTKALL(" + empid + ") AS stk ON stk.codpro = stock.codpro " +
"where stock.codpro like '%" + value + "%' or stock.descri like '%" + value + "%' " +
"order by descri " +
"offset " + offset + " rows fetch next 20 row only"
).ToList();
}
}
foreach (var prod in lista)
{
if (prod.pathfoto != "")
{
prod.pathfoto = acortarPath(prod.pathfoto);
}
if (prod.pathfoto == "")
{
prod.pathfoto = "empty";
}
}
req.objeto = lista;
return(req);
} catch (Exception e)
{
return(req.falla(e));
}
}
public RequestHTTP finalizarPresupuesto(JObject cabydet)
{
var req = new RequestHTTP();
try
{
var jCabecera = (JObject)cabydet["cabecera"];
int punto = (int)jCabecera["punto"], empresaid = (int)jCabecera["empresa"];
var jDetalle = cabydet["lista"];
decimal bonifcli = (decimal)cabydet["bonifcli"];
presupc cabecera = armarCabecera(jCabecera, bonifcli);
List <presupd> detalle = armarDetalle(jDetalle, cabecera, bonifcli);
using (GestionEntities bd = new GestionEntities())
{
bd.presupc.Add(cabecera);
foreach (presupd p in detalle)
{
bd.presupd.Add(p);
}
var factu = bd.factura.Single(a => a.PUNTO == punto && a.EmpresaId == empresaid);
bd.factura.Attach(factu);
factu.PRESUP++;
if (!Convert.ToBoolean(bd.SaveChanges()))
{
throw new Exception();
}
req.objeto = new compi(cabecera.numero, cabecera.punto, cabecera.tipodoc, cabecera.letra, cabecera.id);
return(req);
}
}
catch (DbEntityValidationException dbEx)
{
StreamWriter sw = new StreamWriter("C:/Users/usuario/Desktop/error.txt");
//Close the file
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
sw.WriteLine("Property:" + validationError.PropertyName + " Error:" + validationError.ErrorMessage);
}
}
sw.Close();
return(req.falla(new Exception()));
}
}
internal RequestHTTP getProveedoresFiltro(string param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <concepto>("select nropro, razsoc from provee where nropro like '" + param + "' or razsoc like '%" + param + "%'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getActividad(int param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <activida>("select * from activida where codigo =" + param).ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getTransportes()
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiTranspo>("select codigo, razsoc from transpo").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getmarcasFiltro(string param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiMarca>("select codigo, descripcion from marcas where codigo like '" + param + "' or descripcion like '%" + param + "%'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------SUBRUBROS
internal RequestHTTP getSubRubros()
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiSubrub>("select codigo, descri from subrub").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------ZONAS
public RequestHTTP getZonas()
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiZona>("select codigo, descri from zonas").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
public RequestHTTP GetVende(int param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <vendedor>("select codven, nombre from vende where codven = " + param).ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------MARCAS
internal RequestHTTP getmarcas()
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiMarca>("select codigo, descripcion from marcas").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------PROVINCIAS
public RequestHTTP getPrvincias()
{
var req = new RequestHTTP();
try
{
List <provincias> provincias;
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiProvincia>("select nombre, id from provincias").ToList();
}
return(req);
}catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getTransportesFiltro(string param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiTranspo>("select codigo, razsoc from transpo where razsoc like '%" + param + "%' or codigo like '" + param + "'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getConcepto(int param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiConcepto>("select codigo, descri from concepto where codigo = " + param).ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getActividadesFiltro(string param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <activida>("select * from activida where descri like '%" + param + "%' or codigo like '" + param + "'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getPuntos(int empresa)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.factura.Where(a => a.EmpresaId == empresa).ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getProveedor(int param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiProvee>("select nropro, razsoc from provee where nropro = " + param).ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
public JsonResult<RequestHTTP> GetTranspo()
{
var req = new RequestHTTP();
try
{
List<MiTranspo> transportes;
using (GestionEntities bd = new GestionEntities())
req.objeto = bd.Database.SqlQuery<MiTranspo>("select razsoc, codigo, direcc,telefo from transpo order by razsoc").ToList();
return Json(req);
}
catch (Exception e)
{
return Json(req.falla(e));
}
}
public RequestHTTP getPresup(string filtro, int offset)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <presupc>("select * from presupc where " + filtro + "order by fecha desc offset " + offset + " rows fetch next 20 row only").ToList();
return(req);
}
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getArticulosFiltro(string param)
{
var req = new RequestHTTP();
try
{
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <StockView>("select codpro, descri from stock where descri like '%" + param + "%' or codpro like '%" + param + "%'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
//----------------------------------------------------------VENDEDORES
public RequestHTTP getVendedores()
{
var req = new RequestHTTP();
try
{
List <vende> vendedores;
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.vende.ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}
internal RequestHTTP getProvinciasFiltro(string param)
{
var req = new RequestHTTP();
try
{
List <provincias> provincias;
using (GestionEntities bd = new GestionEntities())
{
req.objeto = bd.Database.SqlQuery <MiProvincia>("select nombre, id from provincias where nombre like '%" + param + "%' or id like '" + param + "'").ToList();
}
return(req);
}
catch (Exception e)
{
return(req.falla(e));
}
}