private void ExportaXLS_Click(object sender, RoutedEventArgs e)
{
var options = new Syncfusion.UI.Xaml.Grid.Converter.ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
options.CellsExportingEventHandler = CellExportingHandler;
var excelEngine = GridKardex.ExportToExcel(GridKardex.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
if (sfd.ShowDialog() == true)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else if (sfd.FilterIndex == 2)
{
workBook.Version = ExcelVersion.Excel2010;
}
else
{
workBook.Version = ExcelVersion.Excel2013;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created workbook.
if (MessageBox.Show("Usted quiere abrir el archivo en excel?", "Ver archvo",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
private void BuscarProbucto()
{
Expression <Func <Vw_Movimiento, bool> > pred = x => true;
pred = checkDesde.Checked ? pred.And(x => x.Fecha > dpDesde.Value.Date) : pred;
pred = pred.And(x => x.Fecha <= dpHasta.Value.Date);
Expression <Func <Vw_Producto, bool> > pred2 = x => true;
pred2 = checkTabaco.Checked ? pred2.And(x => x.DESCRIPCION == cbProducto.Text) : pred2;
Expression <Func <Vw_Deposito, bool> > pred3 = x => true;
pred3 = checkDeposito.Checked ? pred3.And(x => x.nombre == cbDeposito.Text) : pred3;
List <GridKardex> lista1 = new List <GridKardex>();
var movimientos =
(from m in Context.Vw_Movimiento.Where(pred)
.OrderBy(x => x.NumeroCaja)
join p in Context.Vw_Producto.Where(pred2)
on m.ProductoId equals p.ID
join d in Context.Vw_Deposito.Where(pred3)
on m.DepositoId equals d.id
group new { m, p, d } by new
{
Fecha = m.FechaCaja,
Deposito = d.nombre,
TipoTabaco = p.DESCRIPCION,
TipoDocumento = m.Documento,
NumeroDocumento = m.NumeroDocumento,
m.Unidad
} into g
select new
{
g.Key.Fecha,
g.Key.Deposito,
g.Key.TipoDocumento,
g.Key.NumeroDocumento,
g.Key.TipoTabaco,
g.Key.Unidad,
Ingreso = g.Sum(c => c.m.Ingreso),
Egreso = g.Sum(c => c.m.Egreso),
Saldo = g.Sum(c => c.m.Ingreso) - g.Sum(c => c.m.Egreso)
})
.OrderByDescending(x => x.Fecha)
.ToList();
foreach (var item in movimientos)
{
var rowInventario = new GridKardex();
rowInventario.Fecha = item.Fecha.ToShortDateString();
rowInventario.Deposito = item.Deposito;
rowInventario.NumeroDocumento = string.Empty;
rowInventario.TipoDocumento = item.TipoDocumento;
rowInventario.TipoTabaco = item.TipoTabaco;
rowInventario.Unidad = item.Unidad;
rowInventario.Ingreso = string.Empty;
rowInventario.Egreso = string.Empty;
rowInventario.Saldo = item.Saldo.Value.ToString();
lista1.Add(rowInventario);
}
lista1.Reverse();
gridControlInventario.DataSource = new BindingList <GridKardex>(lista1);
gridViewInventario.Columns[0].Width = 200;
gridViewInventario.Columns[8].Visible = false;
gridViewInventario.Columns["NumeroDocumento"].SortOrder = DevExpress.Data.ColumnSortOrder.Ascending;
if (!checkDesde.Checked)
{
gridControlInventario.DataSource = movimientos;
gridViewInventario.Columns[0].Width = 120;
gridViewInventario.Columns[1].Width = 150;
gridViewInventario.Columns[2].Width = 300;
gridViewInventario.Columns[3].Width = 120;
gridViewInventario.Columns[4].Width = 120;
gridViewInventario.Columns[5].Width = 120;
gridViewInventario.Columns[8].Visible = false;
}
else if (checkDesde.Checked)
{
List <GridKardex> lista = new List <GridKardex>();
foreach (var movimiento in movimientos)
{
var rowInventario = new GridKardex();
rowInventario.Fecha = movimiento.Fecha.ToShortDateString();
rowInventario.Deposito = movimiento.Deposito;
rowInventario.NumeroDocumento = movimiento.NumeroDocumento;
rowInventario.TipoDocumento = movimiento.TipoDocumento;
rowInventario.TipoTabaco = movimiento.TipoTabaco;
rowInventario.Unidad = movimiento.Unidad;
rowInventario.Ingreso = movimiento.Ingreso.Value.ToString();
rowInventario.Egreso = movimiento.Egreso.Value.ToString();
rowInventario.Saldo = movimiento.Saldo.Value.ToString();
lista.Add(rowInventario);
}
Expression <Func <Movimiento, bool> > pred4 = x => true;
pred4 = pred4.And(x => x.Fecha <= dpDesde.Value.Date);
var desde = dpDesde.Value.ToShortDateString();
var saldos =
(from m in Context.Vw_Movimiento.Where(pred)
join p in Context.Vw_Producto.Where(pred2)
on m.ProductoId equals p.ID
join d in Context.Vw_Deposito.Where(pred3)
on m.DepositoId equals d.id
group new { m, p, d } by new
{
Fecha = "Saldo al día " + desde,
Deposito = d.nombre,
TipoTabaco = p.DESCRIPCION,
TipoDocumento = m.Documento,
m.Unidad
} into g
select new
{
g.Key.Fecha,
g.Key.Deposito,
g.Key.TipoDocumento,
g.Key.TipoTabaco,
g.Key.Unidad,
Ingreso = g.Sum(c => c.m.Ingreso),
Egreso = g.Sum(c => c.m.Egreso),
Saldo = g.Sum(c => c.m.Ingreso) - g.Sum(c => c.m.Egreso)
})
.ToList();
foreach (var saldo in saldos)
{
var rowInventario = new GridKardex();
rowInventario.Fecha = saldo.Fecha;
rowInventario.Deposito = saldo.Deposito;
rowInventario.NumeroDocumento = string.Empty;
rowInventario.TipoDocumento = saldo.TipoDocumento;
rowInventario.TipoTabaco = saldo.TipoTabaco;
rowInventario.Unidad = saldo.Unidad;
rowInventario.Ingreso = string.Empty;
rowInventario.Egreso = string.Empty;
rowInventario.Saldo = saldo.Saldo.Value.ToString();
lista.Add(rowInventario);
}
lista.Reverse();
gridControlInventario.DataSource = new BindingList <GridKardex>(lista);
gridViewInventario.Columns[0].Width = 200;
gridViewInventario.Columns[8].Visible = false;
gridViewInventario.Columns["NumeroDocumento"].SortOrder = DevExpress.Data.ColumnSortOrder.Descending;
}
}
private void ExportaXLS_Click(object sender, RoutedEventArgs e)
{
try
{
var options = new Syncfusion.UI.Xaml.Grid.Converter.ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
//options.ExcludeColumns.Add("idreg");
//options.ExcludeColumns.Add("cod_trn");
//options.ExcludeColumns.Add("nom_trn");
//options.ExcludeColumns.Add("num_trn");
//options.ExcludeColumns.Add("fec_trn");
//options.ExcludeColumns.Add("bod_tra");
//options.ExcludeColumns.Add("ent_uni");
//options.ExcludeColumns.Add("ent_cost");
//options.ExcludeColumns.Add("ent_ctotal");
//options.ExcludeColumns.Add("sal_uni");
//options.ExcludeColumns.Add("sal_cost");
//options.ExcludeColumns.Add("sal_ctotal");
//options.ExcludeColumns.Add("saldo_uni");
//options.ExcludeColumns.Add("saldo_cost");
//options.ExcludeColumns.Add("saldo_ctotal");
var excelEngine = GridKardex.ExportToExcel(GridKardex.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
if (sfd.ShowDialog() == true)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else if (sfd.FilterIndex == 2)
{
workBook.Version = ExcelVersion.Excel2010;
}
else
{
workBook.Version = ExcelVersion.Excel2013;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created workbook.
if (MessageBox.Show("Usted quiere abrir el archivo en excel?", "Ver archvo", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
catch (Exception w)
{
MessageBox.Show("error al exportar:" + w);
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
ResetValue();
if (FecIni.Text.Length <= 0)
{
MessageBox.Show("debe de ingresar la fecha de corte");
FecIni.Focus();
return;
}
if (string.IsNullOrEmpty(TextBoxRef.Text.Trim()))
{
MessageBox.Show("debe de ingresar una referencia");
TextBoxRef.Focus();
return;
}
if (string.IsNullOrEmpty(TextBoxbod.Text.Trim()))
{
MessageBox.Show("debe de ingresar una bodega");
TextBoxbod.Focus();
return;
}
SqlConnection con = new SqlConnection(SiaWin._cn);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("_EmpInventarioKardes", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Fecha", FecIni.Text);
cmd.Parameters.AddWithValue("@Ref", TextBoxRef.Text);
cmd.Parameters.AddWithValue("@Bods", TextBoxbod.Text);
cmd.Parameters.AddWithValue("@codemp", codemp);
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
GridKardex.ItemsSource = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
GridKardex.Focus();
GridKardex.SelectedIndex = 0;
decimal CantEnt = Convert.ToDecimal(ds.Tables[0].Compute("Sum(ent_uni)", "").ToString());
decimal TotEnt = Convert.ToDecimal(ds.Tables[0].Compute("Sum(ent_ctotal)", "").ToString());
TxtTotalUnEnt.Text = CantEnt.ToString("N2");
TxtTotalUncostEnt.Text = TotEnt.ToString("N2");
int promedioEntrada = 0;
if (TotEnt > 0 & CantEnt > 0)
{
TxtTotalUncosEnt.Text = (TotEnt / CantEnt).ToString("N2");
promedioEntrada = Convert.ToInt32(TotEnt / CantEnt);
}
else
{
TxtTotalUncosEnt.Text = "0";
}
ProEnt.Text = promedioEntrada.ToString();
decimal CantSal = Convert.ToDecimal(ds.Tables[0].Compute("Sum(sal_uni)", "").ToString());
decimal TotSal = Convert.ToDecimal(ds.Tables[0].Compute("Sum(sal_ctotal)", "").ToString());
TxtTotalUnSal.Text = CantSal.ToString("N2");
TxtTotalUncostSal.Text = TotSal.ToString("N2");
int promedioSalida = 0;
if (TotSal > 0 & CantSal > 0)
{
TxtTotalUncosSal.Text = (TotSal / CantSal).ToString("N2");
promedioSalida = Convert.ToInt32(TotSal / CantSal);
}
else
{
TxtTotalUncosSal.Text = "0";
}
ProSal.Text = promedioSalida.ToString();
decimal CantSaldo = Convert.ToDecimal(ds.Tables[0].Compute("Sum(saldo_uni)", "").ToString());
decimal TotSaldo = Convert.ToDecimal(ds.Tables[0].Compute("Sum(saldo_ctotal)", "").ToString());
TxtTotalUnSaldo.Text = CantSaldo.ToString("N2");
TxtTotalUncostSaldo.Text = TotSaldo.ToString("N2");
int promedioSaldo = 0;
if (TotSaldo > 0 & CantSaldo > 0)
{
promedioSaldo = Convert.ToInt32(TotSaldo / CantSaldo);
TxtTotalUncosSaldo.Text = (TotSaldo / CantSaldo).ToString("N2");
}
else
{
TxtTotalUncosSaldo.Text = "0";
}
ProSaldo.Text = promedioSaldo.ToString();
}
Total.Text = ds.Tables[0].Rows.Count.ToString();
}
catch (Exception w)
{
MessageBox.Show("error al cargar la consulta programada:" + w.ToString());
}
}
