private void TipoExpediente_CB_SelectedIndexChanged(object sender, EventArgs e)
{
if (TipoExpediente_CB.SelectedItem == null)
{
return;
}
if (_facturas_todas == null)
{
return;
}
ETipoExpediente tipo = (ETipoExpediente)((long)TipoExpediente_CB.SelectedValue);
if (tipo != ETipoExpediente.Todos)
{
FCriteria criteria = new FCriteria <long>("TipoExpediente", (long)TipoExpediente_CB.SelectedValue);
_facturas = LineaFomentoList.GetSortedList(_facturas_todas.GetSubList(criteria), "NExpediente", ListSortDirection.Ascending);
}
else
{
_facturas = LineaFomentoList.GetSortedList(_facturas_todas, "NExpediente", ListSortDirection.Ascending);
}
Datos_Facturas.DataSource = _facturas;
SetUnlinkedGridValues(Facturas_DGW.Name);
SetGridColors(Facturas_DGW);
}
/// <summary>
/// Retrieve the complete list from db
/// </summary>
/// <param name="get_childs">retrieving the childs</param>
/// <returns></returns>
public static InventarioValoradoList GetListStock(ETipoExpediente tipo,
ExpedientInfo expediente,
DateTime fecha)
{
CriteriaEx criteria = Cliente.GetCriteria(Cliente.OpenSession());
criteria.Childs = false;
criteria.Query = InventarioValoradoList.SELECT_WITH_STOCK(tipo, expediente, fecha);
InventarioValoradoList list = DataPortal.Fetch <InventarioValoradoList>(criteria);
CloseSession(criteria.SessionCode);
return(list);
}
public static ExpedienteList GetList(ETipoExpediente t, bool childs, bool cache)
{
ExpedienteList list;
if (!Cache.Instance.Contains(typeof(ExpedienteList)))
{
list = ExpedienteList.GetList(t, childs);
Cache.Instance.Save(typeof(ExpedienteList), list);
}
else
{
list = Cache.Instance.Get(typeof(ExpedienteList)) as ExpedienteList;
}
return(list);
}
/// <summary>
/// Retrieve the complete list from db
/// </summary>
/// <param name="get_childs">retrieving the childs</param>
/// <returns></returns>
public static InventarioValoradoList GetList(ProductInfo producto,
ETipoExpediente tipo,
ExpedientInfo expediente,
DateTime fecha)
{
CriteriaEx criteria = Product.GetCriteria(Product.OpenSession());
criteria.Childs = false;
criteria.Query = InventarioValoradoList.SELECT(producto, tipo, expediente, fecha);
InventarioValoradoList list = DataPortal.Fetch <InventarioValoradoList>(criteria);
CloseSession(criteria.SessionCode);
return(list);
}
public static string SELECT_SIN_FOMENTO(ETipoExpediente expedientType)
{
string lf = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.LineaFomentoRecord));
QueryConditions conditions = new QueryConditions();
string query = Expedient.FIELDS(conditions) +
Expedient.JOIN(conditions) +
Expedient.WHERE(conditions) +
" AND E.\"OID\" NOT IN (SELECT LF.\"OID_EXPEDIENTE\"" +
" FROM "+ lf + " AS LF " +
" GROUP BY \"OID_EXPEDIENTE\")"+
" AND \"TIPO_EXPEDIENTE\" = " + ((long)expedientType).ToString();
query += " ORDER BY E.\"CODIGO\"";
return(query);
}
public static string SELECT_BY_TYPE(ETipoExpediente expedientType)
{
QueryConditions conditions = new QueryConditions();
string query =
Expedient.FIELDS(conditions) +
Expedient.JOIN(conditions) +
Expedient.WHERE(conditions);
if (expedientType != ETipoExpediente.Todos)
{
query += " AND E.\"TIPO_EXPEDIENTE\" = " + (long)expedientType;
}
query += " ORDER BY E.\"CODIGO\"";
return(query);
}
public static ExpedienteList GetList(ETipoExpediente t, bool childs)
{
return(GetList(ExpedienteList.SELECT_BY_TYPE(t), childs));
}
public static ExpedienteList GetList(ETipoExpediente t)
{
return(ExpedienteList.GetList(t, true));
}
public static SortedBindingList <ExpedientInfo> GetSortedListByYear(bool childs, ETipoExpediente t, int ano, string sortProperty, ListSortDirection sortDirection)
{
CriteriaEx criteria = Expedient.GetCriteria(Expedient.OpenSession());
criteria.Childs = childs;
criteria.Query = ExpedienteList.SELECT_BY_TYPE_AND_YEAR(t, ano);
ExpedienteList list = DataPortal.Fetch <ExpedienteList>(criteria);
CloseSession(criteria.SessionCode);
SortedBindingList <ExpedientInfo> sortedList = new SortedBindingList <ExpedientInfo>(list);
sortedList.ApplySort(sortProperty, sortDirection);
return(sortedList);
}
public static SortedBindingList <ExpedientInfo> GetSortedList(string sortProperty, ListSortDirection sortDirection, bool childs, ETipoExpediente t)
{
SortedBindingList <ExpedientInfo> sortedList = new SortedBindingList <ExpedientInfo>(GetList(t, childs));
sortedList.ApplySort(sortProperty, sortDirection);
return(sortedList);
}
public static ExpedienteList GetListSinFomento(ETipoExpediente t, bool childs)
{
return(GetList(ExpedienteList.SELECT_SIN_FOMENTO(t), childs));
}
public static ExpedienteList GetListByYear(ETipoExpediente t, int ano, bool childs)
{
return(GetList(ExpedienteList.SELECT_BY_TYPE_AND_YEAR(t, ano), childs));
}
public static string SELECT_WITH_STOCK(ETipoExpediente tipo,
ExpedientInfo expediente,
DateTime fecha)
{
string ba = nHManager.Instance.GetSQLTable(typeof(BatchRecord));
string st = nHManager.Instance.GetSQLTable(typeof(StockRecord));
string ex = nHManager.Instance.GetSQLTable(typeof(ExpedientRecord));
string pr = nHManager.Instance.GetSQLTable(typeof(ProductRecord));
string su = nHManager.Instance.GetSQLTable(typeof(SupplierRecord));
string sr = nHManager.Instance.GetSQLTable(typeof(AlmacenRecord));
//LOS PRODUCTOS QUE HAN SIDO INCLUIDOS EN ALGUN EXPEDIENTE Y TIENEN SALIDAS DE STOCK
string query =
SELECT_FIELDS() + @"
FROM " + ba + " AS PA " + @"
INNER JOIN " + sr + @" AS AL ON AL.""OID"" = PA.""OID_ALMACEN""
LEFT 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""
FROM " + st + @"
WHERE ""KILOS"" > 0 AND ""FECHA"" <= '" + fecha.ToString("MM/dd/yyyy") + @"'
GROUP BY ""OID_BATCH"")
AS SA ON PA.""OID"" = SA.""OID_BATCH""
LEFT JOIN (SELECT ""OID_BATCH""
,SUM(""KILOS"") AS ""SALIDA""
FROM " + st + @"
WHERE ""KILOS"" < 0 AND ""FECHA"" <= '" + fecha.ToString("MM/dd/yyyy") + @"'
GROUP BY ""OID_BATCH"")
AS SB ON SA.""OID_BATCH"" = SB.""OID_BATCH""
WHERE (COALESCE(""ENTRADA"", 0) + COALESCE(""SALIDA"", 0) > 0)";
if (tipo != ETipoExpediente.Todos)
{
query += @"
AND COALESCE(E.""TIPO_EXPEDIENTE"", " + (long)ETipoExpediente.Todos + @") = " + (long)tipo;
if (expediente != null)
{
query += @"
AND COALESCE(E.""OID"", 0) = " + expediente.Oid;
}
}
/*query += " UNION ";
*
* //LOS PRODUCTOS QUE NO TIENEN SALIDAS DE STOCK
* query += SELECT_FIELDS() +
* " FROM " + pa + " AS PA " +
* " INNER JOIN " + te + " AS E ON E.\"OID\" = PA.\"OID_EXPEDIENTE\"" +
* " INNER JOIN " + tp + " AS P ON P.\"OID\" = PA.\"OID_PRODUCTO\"" +
* " INNER JOIN " + tpr + " AS PR ON PR.\"OID\" = PA.\"OID_PROVEEDOR\"" +
* " INNER JOIN (SELECT \"OID_BATCH\", SUM(\"KILOS\") AS \"ENTRADA\", 0 AS \"SALIDA\"" +
* " FROM " + ts +
* " WHERE \"KILOS\" > 0 AND \"FECHA\" <= '" + fecha.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 " + ts +
* " WHERE \"KILOS\" < 0 AND \"FECHA\" <= '" + fecha.ToString("MM/dd/yyyy") + "')";
*
* if (tipo != ETipoExpediente.Todos)
* {
* query += " AND E.\"TIPO_EXPEDIENTE\" = " + ((long)tipo).ToString();
*
* if (expediente != null)
* query += " AND E.\"OID\" = " + expediente.Oid.ToString();
* }*/
query += @"
ORDER BY ""PRODUCTO""";
return(query);
}
public static string SELECT(ProductInfo producto,
ETipoExpediente tipo,
ExpedientInfo expediente,
DateTime fecha)
{
string ba = nHManager.Instance.GetSQLTable(typeof(BatchRecord));
string st = nHManager.Instance.GetSQLTable(typeof(StockRecord));
string ex = nHManager.Instance.GetSQLTable(typeof(ExpedientRecord));
string pr = nHManager.Instance.GetSQLTable(typeof(ProductRecord));
string su = nHManager.Instance.GetSQLTable(typeof(SupplierRecord));
string sr = nHManager.Instance.GetSQLTable(typeof(AlmacenRecord));
//LOS PRODUCTOS QUE HAN SIDO INCLUIDOS EN ALGUN EXPEDIENTE Y TIENEN SALIDAS DE STOCK
string query =
SELECT_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"" <= '" + fecha.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"" <= '" + fecha.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 (producto != null)
{
query += @"
AND P.""OID"" = " + producto.Oid;
}
if (tipo != ETipoExpediente.Todos)
{
query += @"
AND E.""TIPO_EXPEDIENTE"" = " + (long)tipo;
if (expediente != null)
{
query += @"
AND E.""OID"" = " + expediente.Oid;
}
}
query += @"
UNION ";
//LOS PRODUCTOS QUE NO TIENEN SALIDAS DE STOCK
query +=
SELECT_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"" <= '" + fecha.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"" <= '" + fecha.ToString("MM/dd/yyyy") + "')";
if (producto != null)
{
query += @"
AND P.""OID"" = " + producto.Oid;
}
if (tipo != ETipoExpediente.Todos)
{
query += @"
AND E.""TIPO_EXPEDIENTE"" = " + (long)tipo;
if (expediente != null)
{
query += @"
AND E.""OID"" = " + expediente.Oid;
}
}
if (tipo == 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 (producto != null)
{
query += @"
AND P.""OID"" = " + producto.Oid;
}
}
query += @"
ORDER BY ""PRODUCTO""";
return(query);
}
