protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string valor = (Request.QueryString["d"]);
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RTPAC cr = new CrystalReport_RTPAC();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from trabajo in entities.empleadoxcaja
join emp in entities.empleado on trabajo.empleado equals emp.cedula
join caja in entities.caja on trabajo.caja equals caja.id
join suc in entities.sucursal on caja.sucursal equals suc.nombre
where trabajo.activo && suc.compania == valor && trabajo.fechainicio == System.DateTime.Today
group trabajo by new { emp = emp, trabajo.empleado } into g
select new
{
Cedula = g.Key.emp.cedula,
Nombre = g.Key.emp.nombre1,
Apellido = g.Key.emp.apellido1,
Sucursal = g.Key.emp.sucursal,
TiempoPromedio = g.Sum(i => (i.horafinal.Hours - i.horainicio.Hours))
}).ToList());
CrystalReportViewer1.ReportSource = cr;
}
}
protected void Button2_Click(object sender, EventArgs e)
{
string valor = (Request.QueryString["d"]);
int empleado = 0;
try{ empleado = Int32.Parse(TextBox2.Text); }catch (Exception) {}
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RPMV_S_C cr = new CrystalReport_RPMV_S_C();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from fact in entities.factura
join medFact in entities.medicamentoxfactura on fact.id equals medFact.factura
join medic in entities.medicamento on medFact.medicamento equals medic.nombre
join caja in entities.caja on fact.caja equals caja.id
join suc in entities.sucursal on caja.sucursal equals suc.nombre
where medFact.activo && suc.compania == valor && fact.empleado == empleado
group medFact by new { proj = medic, medFact.medicamento } into g
select new
{
Nombre = g.Key.proj.nombre,
Proveedor = g.Key.proj.proveedor,
Precio = g.Key.proj.precio,
Cantidad = g.Sum(i => i.cantidad)
}).Take(20));
CrystalReportViewer1.ReportSource = cr;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string valor = (Request.QueryString["d"]);
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RPBI cr = new CrystalReport_RPBI();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from medSuc in entities.medicamentoxsucursal
join med in entities.medicamento on medSuc.medicamento equals med.nombre
join suc in entities.sucursal on medSuc.sucursal equals suc.nombre
where medSuc.activo && suc.compania == valor && medSuc.cantidad < medSuc.stockminimo
select new
{
Nombre = medSuc.medicamento,
Proveedor = med.proveedor,
StockMinimo = medSuc.stockminimo,
Cantidad = medSuc.cantidad,
Sucursal = suc.nombre
}).ToList());
CrystalReportViewer1.ReportSource = cr;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string valor = (Request.QueryString["d"]);
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RPMV_S_C cr = new CrystalReport_RPMV_S_C();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from fact in entities.factura
join medFact in entities.medicamentoxfactura on fact.id equals medFact.factura
join medic in entities.medicamento on medFact.medicamento equals medic.nombre
join caja in entities.caja on fact.caja equals caja.id
join suc in entities.sucursal on caja.sucursal equals suc.nombre
where medFact.activo && suc.compania == valor
group medFact by new { proj = medic, medFact.medicamento } into g
select new
{
Nombre = g.Key.proj.nombre,
Proveedor = g.Key.proj.proveedor,
Precio = g.Key.proj.precio,
Cantidad = g.Sum(i => i.cantidad)
}).Take(20));
CrystalReportViewer1.ReportSource = cr;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string fecha1 = String.Format("{0}", Request.Form["fecha1"]);
DateTime fechaInicio = Convert.ToDateTime(fecha1);
DateTime fechaFinal = Convert.ToDateTime(String.Format("{0}", Request.Form["fecha2"]));
string valor = (Request.QueryString["d"]);
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RPMV_S_C cr = new CrystalReport_RPMV_S_C();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from fact in entities.factura
join medFact in entities.medicamentoxfactura on fact.id equals medFact.factura
join medic in entities.medicamento on medFact.medicamento equals medic.nombre
join caja in entities.caja on fact.caja equals caja.id
join suc in entities.sucursal on caja.sucursal equals suc.nombre
where medFact.activo && suc.compania == valor &&
fact.fecha >= fechaInicio && fact.fecha <= fechaFinal
group medFact by new { proj = medic, medFact.medicamento } into g
select new
{
Nombre = g.Key.proj.nombre,
Proveedor = g.Key.proj.proveedor,
Precio = g.Key.proj.precio,
Cantidad = g.Sum(i => i.cantidad)
}).Take(20));
CrystalReportViewer1.ReportSource = cr;
}
public HttpResponseMessage LogearEmpleado(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
string rol = "";
string contrasena = MD5Hash(obj.opcion);
var empleado = entities.empleado.Select(m => new { m.rol, m.activo, m.cedula, m.contrasena }).
Where((x) => x.activo && x.cedula == obj.opcion2 && x.contrasena == contrasena).First();
rol = empleado.rol;
if (rol != "Cajero" && rol != "Administrador")
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized));
}
var informacion = (from e in entities.empleado
join s in entities.sucursal on e.sucursal equals s.nombre
where e.activo && e.cedula == obj.opcion2
select new{ e.sucursal, s.compania, e.rol }).First();
return(Request.CreateResponse(HttpStatusCode.OK, informacion));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ActualizarSucursal(sucursal sucursal)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.ActualizarSucursal(sucursal.nombre, sucursal.provincia, sucursal.ciudad, sucursal.senas, sucursal.descripcion, sucursal.compania);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage AgregarMedicamento(medicamento medicamento)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.InsertarMedicamento(medicamento.nombre, medicamento.precio, medicamento.prescripcion, medicamento.proveedor);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ActualizarMedicamentoSucursal(medicamentoxsucursal medicamento)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.ActualizarMedicamentoxsucursal(medicamento.sucursal, medicamento.medicamento, medicamento.cantidad);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage BorrarMedicamento(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.BorrarMedicamento(obj.opcion);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ActualizarProveedor(proveedor proveedor)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.ActualizarProveedor(proveedor.nombre, proveedor.sede);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ActualizarEmpleado(empleado empleado)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.ActualizarEmpleado(empleado.cedula, empleado.nombre1, empleado.nombre2, empleado.apellido1, empleado.apellido2, empleado.provincia,
empleado.ciudad, empleado.senas, empleado.fechanacimiento, empleado.sucursal, empleado.rol);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ConsultarProveedores()
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var proveedores = entities.proveedor.Select(p => new { p.nombre, p.sede, p.activo }).
Where((x) => x.activo).OrderBy((x) => x.nombre).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, proveedores));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ConsultarClientes()
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var clientes = entities.cliente.Select(p => new { p.cedula, p.nombre1, p.nombre2, p.apellido1, p.apellido2, p.ciudad, p.provincia, p.senas, p.fechanacimiento, p.activo }).
Where((x) => x.activo).OrderBy((x) => x.cedula).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, clientes));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ConsultarSucursal(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var sucursales = entities.sucursal.Select(p => new { p.nombre, p.provincia, p.descripcion, p.ciudad, p.senas, p.compania, p.activo }).
Where((x) => x.activo && x.compania == obj.opcion).OrderBy((x) => x.nombre).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, sucursales));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage verificarCantidad(objGeneral2 obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var medicamentos = entities.medicamentoxsucursal.Select(m => new { m.cantidad, m.stockminimo, m.activo, m.medicamento, m.sucursal }).
Where((x) => x.activo && x.medicamento == obj.opcion && x.sucursal == obj.opcion2).First();
return(Request.CreateResponse(HttpStatusCode.OK, medicamentos));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ConsultarMedicamentos()
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var medicamentos = entities.medicamento.Select(m => new { m.nombre, m.precio, m.prescripcion, m.proveedor, m.activo }).
Where((x) => x.activo).OrderBy((x) => x.nombre).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, medicamentos));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ActualizarCliente(clienteCompleto cliente)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.ActualizarCliente(cliente.cedula, cliente.nombre1, cliente.nombre2, cliente.apellido1, cliente.apellido2,
cliente.provincia, cliente.ciudad, cliente.senas, cliente.fechanacimiento, 1);
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage cerrarCaja(empleadoxcaja caja)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.Cerrarcaja(caja.empleado, caja.caja, caja.fechainicio, caja.horainicio,
caja.fechafinal, caja.horafinal, caja.efectivoinicial, caja.efectivofinal);
var facturas = entities.factura.Select(p => new { p.id, p.cliente, p.fecha, p.hora, p.tipo, p.total, p.activo, p.empleado, p.caja }).
Where((x) => x.activo && x.empleado == caja.empleado && x.caja == caja.caja && x.fecha <= caja.fechafinal && x.fecha >= caja.fechainicio &&
x.hora <= caja.horafinal && x.hora >= caja.horainicio).OrderBy((x) => x.id).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, facturas));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage ConsultarEmpleados(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var empleados = (from e in entities.empleado
join s in entities.sucursal on e.sucursal equals s.nombre
where e.activo && s.compania == obj.opcion
select new {
e.nombre1, e.nombre2, e.cedula, e.apellido1, e.apellido2, e.provincia, e.ciudad, e.senas, e.rol, e.sucursal, e.fechanacimiento
}).OrderBy((x) => x.nombre1).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, empleados));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string valor = (Request.QueryString["d"]);
gasStationBDEntities entities = new gasStationBDEntities();
CrystalReport_RLPD cr = new CrystalReport_RLPD();
cr.SetDatabaseLogon("BD3rb", "proyecto2BD", "gspbd.cofvv40de4gk.us-west-1.rds.amazonaws.com", "gasStationBD");
cr.SetDataSource((from fac in entities.factura
join caja in entities.caja on fac.caja equals caja.id
join suc in entities.sucursal on caja.sucursal equals suc.nombre
where fac.activo && suc.compania == valor && fac.fecha == System.DateTime.Today && suc.nombre == TextBox1.Text
orderby fac.id
select new
{
IdFactura = fac.id,
MontoTotal = fac.total,
Cajero = fac.empleado,
Cliente = fac.cliente
}).ToList());
CrystalReportViewer1.ReportSource = cr;
}
public HttpResponseMessage ConsultarPedidos(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var pedidos = (from p in entities.pedido
where p.activo && p.sucursal == obj.opcion
select new
{
p.numero,
p.cliente
}).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, pedidos));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage VerificarCaja(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
int cedula = -1;
var cliente = entities.cliente.Select(p => new { p.cedula, p.activo }).
Where((x) => x.activo && x.cedula == obj.opcion2).First();
cedula = cliente.cedula;
if (cedula == -1)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized));
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage salvarFactura(facturaCompleta factura)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
entities.InsertarFactura(factura.fecha, factura.hora.TimeOfDay, factura.total, factura.tipo, factura.caja, factura.empleado, factura.cliente, factura.productos);
int id = entities.factura.Max(u => u.id);
if (factura.pedido != -1)
{
entities.BorrarPedido(factura.pedido);
}
return(Request.CreateResponse(HttpStatusCode.OK, id));
}
catch (DataException e) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage VerificarCaja(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
int id = -1;
var caja = entities.caja.Select(p => new { p.id, p.activo, p.sucursal }).
Where((x) => x.activo && x.id == obj.opcion2 && x.sucursal == obj.opcion).First();
id = caja.id;
if (id == -1)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized));
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage verificarSupervisor(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
string rol = "";
string contrasena = MD5Hash(obj.opcion);
var empleado = entities.empleado.Select(m => new { m.rol, m.activo, m.cedula, m.contrasena }).
Where((x) => x.activo && x.cedula == obj.opcion2 && x.contrasena == contrasena).First();
rol = empleado.rol;
if (rol != "Supervisor")
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized));
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage consultarMedicamentosSucursal(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var medicamentos = (from m in entities.medicamento
join ms in entities.medicamentoxsucursal on m.nombre equals ms.medicamento
where ms.activo && ms.sucursal == obj.opcion
select new
{
m.nombre,
m.precio,
m.prescripcion
}).OrderBy((x) => x.nombre).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, medicamentos));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public HttpResponseMessage consultarDetallePedido(objGeneral obj)
{
using (gasStationBDEntities entities = new gasStationBDEntities())
{
if (ModelState.IsValid)
{
try
{
var detallePedido = (from medPed in entities.medicamentoxpedido
join med in entities.medicamento on medPed.medicamento equals med.nombre
where medPed.pedido == obj.opcion2
select new
{
medPed.medicamento,
medPed.cantidad,
med.precio
}).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, detallePedido));
}
catch (DataException) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); }
}
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
