internal static string SELECT_BASE(ETipoQuery tipo, Library.Store.QueryConditions conditions)
{
string cf = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.InputInvoiceLineRecord));
string pr = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ProductRecord));
string ca = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.InputDeliveryLineRecord));
string al = nHManager.Instance.GetSQLTable(typeof(AlmacenRecord));
string ex = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ExpedientRecord));
string gt = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ExpenseRecord));
string ba = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.BatchRecord));
string query;
query =
SELECT_FIELDS(tipo, conditions) + @"
FROM " + cf + @" AS CF
LEFT JOIN " + pr + @" AS PR ON PR.""OID"" = CF.""OID_PRODUCTO""
LEFT JOIN " + ca + @" AS CA ON CA.""OID"" = CF.""OID_CONCEPTO_ALBARAN""
LEFT JOIN " + al + @" AS AL ON AL.""OID"" = CA.""OID_ALMACEN""
LEFT JOIN " + ba + @" AS BA ON BA.""OID"" = CA.""OID_BATCH""
LEFT JOIN " + ex + @" AS EX ON EX.""OID"" = CA.""OID_EXPEDIENTE""
LEFT JOIN (SELECT GT.""OID_CONCEPTO_FACTURA"", GT.""OID_EXPEDIENTE"", EX.""CODIGO""
FROM " + gt + @" AS GT
INNER JOIN " + ex + @" AS EX ON EX.""OID"" = GT.""OID_EXPEDIENTE""
GROUP BY ""OID_CONCEPTO_FACTURA"", ""OID_EXPEDIENTE"", EX.""CODIGO"")
AS GT ON GT.""OID_CONCEPTO_FACTURA"" = CF.""OID""";// AND EX.""OID"" = GT.""OID_EXPEDIENTE""";
return(query);
}
internal static string SELECT_BASE(QueryConditions conditions, ETipoQuery tipoQuery)
{
string lp = nHManager.Instance.GetSQLTable(typeof(InputOrderLineRecord));
string cap = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.InputDeliveryLineRecord));
string al = nHManager.Instance.GetSQLTable(typeof(AlmacenRecord));
string ex = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ExpedientRecord));
string query;
query = SELECT_FIELDS(tipoQuery) +
" FROM " + lp + " AS LP" +
" LEFT JOIN " + al + " AS AL ON AL.\"OID\" = LP.\"OID_ALMACEN\"" +
" LEFT JOIN " + ex + " AS EX ON EX.\"OID\" = LP.\"OID_EXPEDIENTE\"";
//Pendiente de Albarán
query += " LEFT JOIN (SELECT \"OID_LINEA_PEDIDO\"" +
" ,SUM(\"CANTIDAD\") AS \"CANTIDAD\""+
" ,SUM(\"CANTIDAD_BULTOS\") AS \"CANTIDAD_BULTOS\""+
" FROM "+ cap + " AS CAP" +
" WHERE CAP.\"OID_LINEA_PEDIDO\" != 0"+
" GROUP BY CAP.\"OID_LINEA_PEDIDO\")"+
" AS CAP ON CAP.\"OID_LINEA_PEDIDO\" = LP.\"OID\"";
return(query);
}
internal static string SELECT_FIELDS(ETipoQuery tipo, QueryConditions conditions)
{
string query;
query = @"
SELECT CF.*
,COALESCE(AL.""OID"", 0) AS ""OID_ALMACEN""
,COALESCE(AL.""CODIGO"", '') AS ""STORE_ID""
,COALESCE(AL.""NOMBRE"", '') AS ""STORE""
,COALESCE(BA.""CODIGO"", '') AS ""ID_BATCH""
,COALESCE(EX.""CODIGO"", '') AS ""EXPEDIENTE""
,COALESCE(PR.""CUENTA_CONTABLE_COMPRA"", '') AS ""CUENTA_CONTABLE""
,COALESCE(GT.""OID_EXPEDIENTE"", 0) AS ""OID_EXPEDIENTE_GASTO""
,COALESCE(GT.""CODIGO"", '') AS ""CODIGO_EXPEDIENTE_GASTO""";
switch (tipo)
{
case ETipoQuery.GENERAL:
query += @"
,'' AS ""N_FACTURA""
,NULL AS ""FECHA_FACTURA""
,'' AS ""ACREEDOR""";
break;
case ETipoQuery.COSTES:
query += @"
,FC.""N_FACTURA"" AS ""N_FACTURA""
,FC.""FECHA"" AS ""FECHA_FACTURA""
,A.""NOMBRE"" AS ""ACREEDOR""";
break;
}
return(query);
}
internal static string SELECT_FIELDS(ETipoQuery tipoQuery)
{
string query;
query = "SELECT " + (long)tipoQuery + " AS \"QUERY\"" +
" ,LP.*"+
" ,(LP.\"CANTIDAD\" - COALESCE(CAP.\"CANTIDAD\", 0)) AS \"CANTIDAD_PENDIENTE\""+
" ,(LP.\"CANTIDAD_BULTOS\" - COALESCE(CAP.\"CANTIDAD_BULTOS\",0)) AS \"CANTIDAD_BULTOS_PENDIENTE\""+
" ,COALESCE(AL.\"CODIGO\", '') AS \"ALMACEN\"" +
" ,COALESCE(EX.\"CODIGO\", '') AS \"EXPEDIENTE\"";
return(query);
}