private void mostrarGrid()
{
string tipoComprobante;
if (cbxTipoComprobante.Text == "BOLETA Y FACTURA")
{
tipoComprobante = " and tc.NombreComprobante = 'BOLETA' OR tc.NombreComprobante = 'FACTURA'";
}
else
{
tipoComprobante = " and tc.NombreComprobante like '%" + cbxTipoComprobante.Text + "%'";
}
fn.ActualizarGrid(dgvCompras, "select ROW_NUMBER() over(order by c.IDCompra) as Item,a.Almacen,c.FechaEmision,c.FechaVencimiento,tc.NombreComprobante as TipoComprobante,tc.Codigo,c.Serie,c.Numero as Correlativo,td.Tipo,p.Numero,p.RazonSocial as Denominacion,c.Moneda,c.TipoCambio,c.Impuesto,c.BaseGravada,c.Igv,c.BaseNoGravada,c.ISC,c.OtrosTributos,c.Descuento,c.Total,c.AumentaStock from Compra c left join TipoComprobante tc on c.IDTipoComprobante = tc.IDTipoComprobante left join Proveedor p on c.IDProveedor = p.IDProveedor left join TipoDocumento td on p.IDTipoDocumento = td.IDTipoDocumento left join Almacen a on c.IDAlmacen = c.IDAlmacen left join Usuario u on c.IDUsuario = u.IDUsuario where c.FechaEmision between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFecha2.Value.ToShortDateString() + "' and c.Serie like '%" + txtSerie.Text + "%' and c.Numero like '%" + txtNumero.Text + "%' and a.Almacen like '%" + cbxAlmacen.Text + "%' " + tipoComprobante + "");
SqlDataReader lectorFactura = fn.selectMultiValues("select sum(c.BaseGravada) as BaseGravada,sum(c.Igv) as IGV,sum(c.BaseNoGravada) as NoGravada,sum(c.ISC) as Isc,sum(c.OtrosTributos) as OtrosTributos,sum(c.Descuento) as Descuento,sum(c.Total) as Total from Compra c left join TipoComprobante tc on c.IDTipoComprobante = tc.IDTipoComprobante left join Proveedor p on c.IDProveedor = p.IDProveedor left join TipoDocumento td on p.IDTipoDocumento = td.IDTipoDocumento left join Almacen a on c.IDAlmacen = c.IDAlmacen left join Usuario u on c.IDUsuario = u.IDUsuario where c.FechaEmision between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFecha2.Value.ToShortDateString() + "' and c.Serie like '%" + txtSerie.Text + "%' and c.Numero like '%" + txtNumero.Text + "%' and a.Almacen like '%" + cbxAlmacen.Text + "%' " + tipoComprobante + "");
lectorFactura.Read();
lblDescuento.Text = lectorFactura["Descuento"].ToString();
lblGravada.Text = lectorFactura["BaseGravada"].ToString();
lblIgv.Text = lectorFactura["igv"].ToString();
lblIsc.Text = lectorFactura["isc"].ToString();
lblNoGravada.Text = lectorFactura["noGravada"].ToString();
lblOtrosTributos.Text = lectorFactura["OtrosTributos"].ToString();
lblTotal.Text = lectorFactura["total"].ToString();
lectorFactura.Close();
}
private void buscar()
{
//VARIABLES
string tipoVenta, tipoComprobante;
//TIPO DE VENTA
if (rbtnDelivery.Checked == true)
{
tipoVenta = " AND v.TipoVenta = 'VD'";
}
else if (rbtnSalon.Checked == true)
{
tipoVenta = " and v.TipoVenta = 'VS'";
}
else
{
tipoVenta = "";
}
string sucursal = "";
//SUCURSAL
if (string.IsNullOrWhiteSpace(cboSucursal.Text) == false)
{
sucursal = " and c.IDSucursal = '" + cboSucursal.SelectedValue + "'";
}
//TIPO DE COMPROBANTE
if (cbxTipoComp.Text == "FACTURA Y BOLETA")
{
tipoComprobante = "not like '%TICKET%'";
}
else if (cbxTipoComp.Text == "TODOS")
{
tipoComprobante = "LIKE '%%'";
}
else
{
tipoComprobante = "LIKE '%" + cbxTipoComp.Text + "%'";
}
if (chbxAnuladas.Checked == false)
{
//Mostramos Ventas Generales
fn.ActualizarGrid(dgVentas, "select iif(v.TipoVenta = 'VS','SALON','DELIVERY') AS TipoVenta,v.IDVenta,v.Fecha,v.Hora,tc.NombreComprobante as TipoComprobante,v.Serie,v.Numero,v.NombreCliente,tp.TipoPago,iif(v.Anulada = 1,'0',v.SubTotal) as SubTotal,iif(v.Anulada = 1,'0',v.Igv) as IGV,iif(v.Anulada = 1,'0',v.Total) as Total,v.CreditoConfirmado,r.Nombres as Repartidor,u.Usuario,mo.Nombres as Mozo,me.Mesa,v.Pagado AS PagadoDelivery,v.Estado as EstadoDelivery,iif(v.Anulada = 1,'ANULADA','OK') as EstadoVenta,v.MotivoNoPago from Venta v left join TipoPago tp on v.IDTipoPago = tp.IDTipoPago left join TipoComprobante tc on v.IDTipoCom = tc.IDTipoComprobante left join Repartidor r on v.IDRepartidor = r.IDRepartidor left join Usuario u on v.IDEmpleado = u.IDUsuario left join Mozo mo on v.IDMozo = mo.IDMozo left join Mesa me on v.IDMesa = me.IDMesa inner join CAJA c on v.IDCaja = c.ID where cast(c.FECHA_HORA as date) between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFin.Value.ToShortDateString() + "' and tp.TipoPago like '%" + cbxTipoPago.Text + "%' and tc.NombreComprobante " + tipoComprobante + " and v.Serie like '%" + txtSerie.Text + "%' " + sucursal + " and v.Numero like '%" + txtNumero.Text + "%' " + tipoVenta + "");
lblTotal.Text = fn.selectValue("select sum(v.Total) from Venta v left join TipoPago tp on v.IDTipoPago = tp.IDTipoPago left join TipoComprobante tc on v.IDTipoCom = tc.IDTipoComprobante inner join CAJA c on v.IDCaja = c.ID where cast(c.FECHA_HORA as date) between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFin.Value.ToShortDateString() + "' and tp.TipoPago like '%" + cbxTipoPago.Text + "%' and tc.NombreComprobante " + tipoComprobante + " and v.Serie like '%" + txtSerie.Text + "%' " + sucursal + " and v.Numero like '%" + txtNumero.Text + "%' " + tipoVenta + " and v.Anulada = 0");
//SI ESTA VACIO LE DEVOLVEROS 0
lblTotal.Text = fn.remplazarNulo(lblTotal.Text);
}
else
{
fn.ActualizarGrid(dgVentas, "select iif(v.TipoVenta = 'VS','SALON','DELIVERY') AS TipoVenta,v.IDVenta,v.Fecha,v.Hora,tc.NombreComprobante as TipoComprobante,v.Serie,v.Numero,v.NombreCliente,tp.TipoPago,v.SubTotal,v.Igv,v.Total,v.CreditoConfirmado,r.Nombres as Repartidor,u.Usuario,mo.Nombres as Mozo,me.Mesa,v.Pagado AS PagadoDelivery,v.Estado as EstadoDelivery,iif(v.Anulada = 1,'ANULADA','OK') as EstadoVenta,v.MotivoNoPago from Venta v left join TipoPago tp on v.IDTipoPago = tp.IDTipoPago left join TipoComprobante tc on v.IDTipoCom = tc.IDTipoComprobante left join Repartidor r on v.IDRepartidor = r.IDRepartidor left join Usuario u on v.IDEmpleado = u.IDUsuario left join Mozo mo on v.IDMozo = mo.IDMozo left join Mesa me on v.IDMesa = me.IDMesa inner join CAJA c on v.IDCaja = c.ID where cast(c.FECHA_HORA as date) between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFin.Value.ToShortDateString() + "' and tp.TipoPago like '%" + cbxTipoPago.Text + "%' and tc.NombreComprobante " + tipoComprobante + " and v.Serie like '%" + txtSerie.Text + "%' " + sucursal + " and v.Numero like '%" + txtNumero.Text + "%' " + tipoVenta + " and v.Anulada = 1");
lblTotal.Text = "00.00";
}
fn.ActualizarGrid(dgvDetalle, "select i.Insumo,CAST(sum(r.Cantidad * dv.Cantidad) AS DECIMAL(18,2)) as Cantidad from venta v inner join CAJA c on v.IDCaja = c.id inner join Sucursal s on c.IDSucursal = s.IDSucursal inner join VentaDetalle dv on v.IDVenta = dv.IDVenta inner join Presentacion p on dv.IDPresentacion = p.IDPresentacion inner join Receta r on p.IDPresentacion = r.IDPresentacion inner join StockAlmacen sa on r.IDStockAlmacen = sa.IDStockAlmacen inner join Insumo i on sa.IDInsumo = i.IDInsumo inner join Almacen a on sa.IDAlmacen = a.IDAlmacen inner join TipoComprobante tc on v.IDTipoCom = tc.IDTipoComprobante inner join TipoPago tp on v.IDTipoPago = tp.IDTipoPago where v.Anulada = 'False' and cast(c.fecha_hora as date) between '" + dtpFecha.Value.ToShortDateString() + "' and '" + dtpFin.Value.ToShortDateString() + "' and tp.TipoPago like '%" + cbxTipoPago.Text + "%' " + tipoVenta + " AND tc.NombreComprobante " + tipoComprobante + " " + sucursal + " group by i.Insumo");
}
private void MostrarDatosGrid()
{
fn.ActualizarGrid(dgvUsuario, "select IDUsuario as Codigo,s.Sucursal,u.Nombres as Nombre,u.Telefono,Tipo,Usuario,Contraseña,Habilitado,PermisosEspeciales as [P. Espce.],u.Tactil,Mozo from Usuario u left join sucursal s on u.IDSucursal = s.IDSucursal where Nombres like '%" + txtBuscar.Text + "%' order by IDUsuario desc");
dgvUsuario.Columns["Contraseña"].Visible = false;
dgvUsuario.Columns["Codigo"].Visible = false;
}
private void MostrarDatosGrid()
{
fn.ActualizarGrid(dgvProductos, "select sa.IDStockAlmacen,a.Almacen,i.Insumo,sa.Stock,um.UniMedida,sa.PermitirControl from stockalmacen sa,Almacen a,Insumo i,UnidadMedida um where sa.IDInsumo = i.IDInsumo and sa.IDAlmacen = a.IDAlmacen and i.IDUniMedida = um.IDUniMedida and a.Almacen = '" + cbxAlmacen.Text + "' and i.Insumo like '%" + txtBuscar.Text + "%'");
}
private void CargarGrid()
{
fn.ActualizarGrid(dgProveedor, "select top(20) IDProveedor as Codigo,td.Descripcion as TipoDocumento,p.Numero,p.RazonSocial,p.Telefono,p.Direccion,p.Contacto from proveedor p left join TipoDocumento td on p.IDTipoDocumento = td.IDTipoDocumento where p.Numero like '" + txtBuscarRUC.Text + "%' ORDER BY IDProveedor desc");
}
