コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }