public static string SELECT(ProductInfo item)
{
return(SELECT(new Library.Store.QueryConditions {
Producto = item
}));
}
public MovimientosStockListPorExpedienteRpt GetMovimientosStockListAgrupado(ExpedienteList items,
ProductInfo producto,
SerieInfo serie,
ReportFilter filter,
ReportFormat format)
{
MovimientosStockListPorExpedienteRpt doc = new MovimientosStockListPorExpedienteRpt();
List <ExpedientePrint> pList = new List <ExpedientePrint>();
List <StockPrint> movs = new List <StockPrint>();
StockList stocks = null;
int movsCount = 0;
foreach (ExpedientInfo item in items)
{
if (filter.SoloStock)
{
if ((item.StockKilos == 0) || (item.StockBultos == 0))
{
continue;
}
}
movsCount = movs.Count;
stocks = (item.Stocks == null) ? StockList.GetListByExpediente(item.Oid, false) : item.Stocks;
foreach (StockInfo stock in stocks)
{
if (filter.SoloMermas)
{
if ((stock.OidAlbaran != 0) || (stock.Kilos >= 0))
{
continue;
}
}
if ((filter.FechaIni <= stock.Fecha) && (stock.Fecha <= filter.FechaFin))
{
if ((producto == null) && (serie == null))
{
movs.Add(StockPrint.New(stock));
}
else if (producto != null)
{
if ((producto.Oid == stock.OidProducto) && (serie == null))
{
movs.Add(StockPrint.New(stock));
}
else if ((producto.Oid == stock.OidProducto) && (serie.Oid == stock.OidSerie))
{
movs.Add(StockPrint.New(stock));
}
}
else if (serie.Oid == stock.OidSerie)
{
movs.Add(StockPrint.New(stock));
}
}
}
if (movsCount < movs.Count)
{
pList.Add(item.GetPrintObject());
}
}
//Si no existen conceptos, no tiene sentido un informe detallado. Además, falla en Crystal Reports
if (movs.Count <= 0)
{
return(null);
}
doc.SetDataSource(pList);
doc.Subreports["StocksSubReport"].SetDataSource(movs);
FormatHeader(doc);
FormatReport(doc, filter, format);
return(doc);
}
internal static Decimal GetPrecioProveedor(ProductoProveedorInfo productoProveedor, BatchInfo partida, ProductInfo producto, ETipoFacturacion tipo)
{
Decimal precio = 0;
ETipoFacturacion tipoFacturacion = tipo;
if (productoProveedor != null)
{
precio = (productoProveedor.ETipoDescuento == ETipoDescuento.Precio) ? productoProveedor.Precio : producto.PrecioCompra;
tipoFacturacion = productoProveedor.ETipoFacturacion;
}
else
{
precio = producto.PrecioCompra;
tipoFacturacion = producto.ETipoFacturacion;
}
Decimal kilosBulto = (partida != null) ? partida.KilosPorBulto : producto.KilosBulto;
kilosBulto = (kilosBulto == 0) ? 1 : kilosBulto;
switch (tipo)
{
case ETipoFacturacion.Peso:
if (tipoFacturacion != ETipoFacturacion.Peso)
{
precio = precio * kilosBulto;
}
break;
default:
if (tipoFacturacion == ETipoFacturacion.Peso)
{
precio = precio / kilosBulto;
}
break;
}
return(Decimal.Round(precio, 2));
}
public ReportClass GetDetailReport(InputInvoiceInfo item, FormatConfFacturaAlbaranReport conf)
{
if (item == null)
{
return(null);
}
List <InputInvoiceLinePrint> conceptos = new List <InputInvoiceLinePrint>();
List <InputInvoicePrint> pList = new List <InputInvoicePrint>();
foreach (InputInvoiceLineInfo cfi in item.Conceptos)
{
conceptos.Add(cfi.GetPrintObject());
}
//Si no existen conceptos, no tiene sentido un informe detallado. Además, falla en Crystal Reports
if (conceptos.Count <= 0)
{
return(null);
}
pList.Add(item.GetPrintObject());
ProductList productos = ProductList.GetList(false);
foreach (InputInvoiceLinePrint cfp in conceptos)
{
if (cfp.OidProducto == 0)
{
continue;
}
ProductInfo prod = productos.GetItem(cfp.OidProducto);
if (prod != null)
{
if (prod.AyudaKilo > 0)
{
cfp.Concepto += " *";
}
}
}
List <ImpuestoResumen> irpf_list = new List <ImpuestoResumen>();
foreach (DictionaryEntry irpf in item.GetIRPF())
{
irpf_list.Add((ImpuestoResumen)irpf.Value);
}
ReportClass doc = null;
try
{
doc = GetReportFromName("Invoice", "InputInvoiceRpt");
}
catch
{
doc = new InputInvoiceRpt();
}
doc.Subreports["LinesSubRpt"].SetDataSource(conceptos);
if (doc.Subreports["IRPFSubListRpt"] != null)
{
doc.Subreports["IRPFSubListRpt"].SetDataSource(irpf_list);
}
doc.SetDataSource(pList);
CompanyInfo company = CompanyInfo.Get(Schema.Oid, false);
doc.SetParameterValue("nombreEmpresa", company.Name);
doc.SetParameterValue("dirEmpresa", company.Direccion);
doc.SetParameterValue("dir2Empresa", company.CodPostal + ". " + company.Municipio + ". " + company.Provincia);
doc.SetParameterValue("CIFEmpresa", company.VatNumber);
doc.SetParameterValue("nota", conf.nota);
doc.SetParameterValue("copia", (conf.copia != null) ? conf.copia : string.Empty);
doc.SetParameterValue("cuentaBancaria", (conf.cuenta_bancaria != string.Empty) ? conf.cuenta_bancaria : company.CuentaBancaria);
return(doc);
}
public static string SELECT(ProductInfo product,
ETipoExpediente expedientType,
ExpedientInfo expedient,
DateTime date)
{
string ba = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.BatchRecord));
string st = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.StockRecord));
string ex = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ExpedientRecord));
string pr = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ProductRecord));
string su = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.SupplierRecord));
string sr = nHManager.Instance.GetSQLTable(typeof(AlmacenRecord));
//LOS PRODUCTOS QUE HAN SIDO INCLUIDOS EN ALGUN EXPEDIENTE Y TIENEN SALIDAS DE STOCK
string query =
FIELDS() + @"
FROM " + ba + @" AS PA
INNER JOIN " + sr + @" AS AL ON AL.""OID"" = PA.""OID_ALMACEN""
INNER JOIN (SELECT ""OID_BATCH"", SUM(""KILOS"") AS ""ENTRADA""
FROM " + st + @"
WHERE ""KILOS"" > 0
AND ""FECHA"" <= '" + date.ToString("MM/dd/yyyy") + @"'
GROUP BY ""OID_BATCH"")
AS SA ON PA.""OID"" = SA.""OID_BATCH""
INNER JOIN (SELECT ""OID_BATCH"", SUM(""KILOS"") AS ""SALIDA""
FROM " + st + @"
WHERE ""KILOS"" < 0
AND ""FECHA"" <= '" + date.ToString("MM/dd/yyyy") + @"'
GROUP BY ""OID_BATCH"")
AS SB ON PA.""OID"" = SB.""OID_BATCH""
INNER JOIN " + ex + @" AS E ON E.""OID"" = PA.""OID_EXPEDIENTE""
INNER JOIN " + pr + @" AS P ON P.""OID"" = PA.""OID_PRODUCTO""
INNER JOIN " + su + @" AS PR ON PR.""OID"" = PA.""OID_PROVEEDOR""
WHERE TRUE";
if (product != null)
{
query += @"
AND P.""OID"" = " + product.Oid;
}
if (expedientType != ETipoExpediente.Todos)
{
query += @"
AND E.""TIPO_EXPEDIENTE"" = " + (long)expedientType;
if (expedient != null)
{
query += @"
AND E.""OID"" = " + expedient.Oid;
}
}
query += @"
UNION ";
//LOS PRODUCTOS QUE NO TIENEN SALIDAS DE STOCK
query +=
FIELDS() + @"
FROM " + ba + @" AS PA
INNER JOIN " + sr + @" AS AL ON AL.""OID"" = PA.""OID_ALMACEN""
INNER JOIN " + ex + @" AS E ON E.""OID"" = PA.""OID_EXPEDIENTE""
INNER JOIN " + pr + @" AS P ON P.""OID"" = PA.""OID_PRODUCTO""
INNER JOIN " + su + @" AS PR ON PR.""OID"" = PA.""OID_PROVEEDOR""
INNER JOIN (SELECT ""OID_BATCH"", SUM(""KILOS"") AS ""ENTRADA"", 0 AS ""SALIDA""
FROM " + st + @"
WHERE ""KILOS"" > 0
AND ""FECHA"" <= '" + date.ToString("MM/dd/yyyy") + @"'
GROUP BY ""OID_BATCH"")
AS SA ON PA.""OID"" = SA.""OID_BATCH""
WHERE PA.""OID"" NOT IN (SELECT DISTINCT ""OID_BATCH""
FROM " + st + @"
WHERE ""KILOS"" < 0
AND ""FECHA"" <= '" + date.ToString("MM/dd/yyyy") + "')";
if (product != null)
{
query += @"
AND P.""OID"" = " + product.Oid;
}
if (expedientType != ETipoExpediente.Todos)
{
query += @"
AND E.""TIPO_EXPEDIENTE"" = " + (long)expedientType;
if (expedient != null)
{
query += @"
AND E.""OID"" = " + expedient.Oid;
}
}
if (expedientType == ETipoExpediente.Todos)
{
//LOS PRODUCTOS QUE EXISTEN PERO NO HAN SIDO INCLUIDOS EN NINGUN EXPEDIENTE
query += @"
UNION ";
query += @"
SELECT P.""OID"" AS ""OID_PRODUCTO_PROVEEDOR""
,AL.""CODIGO"" AS ""ID_ALMACEN""
,AL.""NOMBRE"" AS ""ALMACEN""
,P.""OID"" AS ""OID_PRODUCTO""
,P.""CODIGO_ADUANERO"" AS ""CODIGO_PRODUCTO""
,P.""NOMBRE"" AS ""PRODUCTO""
,'' AS ""EXPEDIENTE""
,'' AS ""CONTENEDOR""
,0 AS ""TIPO_EXPEDIENTE""
,PR.""NOMBRE"" AS ""PROVEEDOR""
,P.""PRECIO_COMPRA"" AS ""PCD""
,P.""PRECIO_VENTA"" AS ""PVP""
,0 AS ""COSTE_KILO""
0 AS ""ENTRADA"", 0 AS ""SALIDA""
FROM " + pr + @"AS P
LEFT JOIN " + ba + @" AS PA ON P.""OID"" = PA.""OID_PRODUCTO""
INNER JOIN " + sr + @" AS AL ON AL.""OID"" = PA.""OID_ALMACEN""
LEFT JOIN " + su + @" AS PR ON PR.""OID"" = PA.""OID_PROVEEDOR""
WHERE P.""OID"" NOT IN (SELECT ""OID_PRODUCTO"" FROM " + ba + ")";
if (product != null)
{
query += @"
AND P.""OID"" = " + product.Oid;
}
}
query += @"
ORDER BY ""PRODUCTO""";
return(query);
}
