예제 #1
0
        public Task <IEnumerable <PositionDisplayItem> > GetPositionenOfSpezialkonto(int bgSpezkontoId, BgSpezkontoTyp typ)
        {
            var query = new PositionenQuery {
                BgSpezkontoId = bgSpezkontoId, Typ = typ
            };

            return(_mediator.Process(query));
        }
예제 #2
0
        public override async Task <IEnumerable <PositionDisplayItem> > Handle(PositionenQuery query)
        {
            return(await _dbConnection.QueryAsync <PositionDisplayItem>(
                       @"DECLARE @BgSpezkontoID INT,
                               @BgSpezkontoTypCode INT;

                        SELECT @BgSpezkontoID      = @BgSpezkontoId,
                               @BgSpezkontoTypCode = @Typ;

                        -- Position im Budget
                        SELECT 
                          SortKey      = CASE
                                           WHEN BPO.BgKategorieCode = 101  THEN 2
                                           WHEN @BgSpezkontoTypCode = 2    THEN 1
                                           ELSE 3
                                         END,
                          Datum        = dbo.fnDateSerial(BBG.Jahr, BBG.Monat, 1),
                          DatumSort    = dbo.fnDateSerial(BBG.Jahr, BBG.Monat, 1),
                          Gutschrift   = CASE WHEN BPO.BgKategorieCode = 101 THEN NULL ELSE BPO.BetragRechnung END,
                          Belastung    = CASE WHEN BPO.BgKategorieCode = 101 THEN BPO.BetragRechnung ELSE NULL END,
                          Freigegeben  = CONVERT(BIT, CASE WHEN BBG.BgBewilligungStatusCode = 5 THEN 1 
                          WHEN EXISTS (SELECT TOP 1 1
                                             FROM dbo.KbBuchung                   BUC WITH (READUNCOMMITTED) 
                                               INNER JOIN dbo.KbBuchungKostenart  BUK WITH (READUNCOMMITTED) ON BUK.KbBuchungID  = BUC.KbBuchungID
                                             WHERE BUC.BgBudgetID = BBG.BgBudgetID
                                               AND BUC.KbBuchungTypCode IN (1, 2, 3, 4) /* Budget, Manuell, Automatisch, Ausgleich */                     
                                               AND BUC.KbBuchungStatusCode NOT IN (1, 7, 8) -- vorbereitet, gesperrt, storniert
                                            ) THEN 1
                        ELSE 0 END),
                          Verbucht     = CONVERT(BIT, CASE
                                                        WHEN BBG.BgBewilligungStatusCode = 5 AND BPO.BgKategorieCode <> 101 THEN 1
                                                        WHEN EXISTS (SELECT TOP 1 1
                                                                     FROM dbo.KbBuchung BUC WITH (READUNCOMMITTED)
                                                                       INNER JOIN dbo.KbBuchungKostenart BUK WITH (READUNCOMMITTED) ON BUK.KbBuchungID = BUC.KbBuchungID
                                                                     WHERE BUC.BgBudgetID = BPO.BgBudgetID AND BUK.BgPositionID = BPO.BgPositionID
                                                                       AND BUC.KbBuchungTypCode IN (1, 2) /* Einnahmen, Ausgaben */
                                                                       AND BUC.KbBuchungStatusCode NOT IN (1, 7, 8)) THEN 1
                                                        ELSE 0
                                                      END),
                          Gesperrt     = CONVERT(BIT, CASE WHEN BBG.BgBewilligungStatusCode = 9 THEN 1 ELSE 0 END),
                          Buchungstext = BPO.Buchungstext,
                          Saldo        = CONVERT(MONEY, NULL)
                        FROM dbo.vwBgPosition      BPO
                          INNER JOIN dbo.BgBudget  BBG WITH (READUNCOMMITTED) ON BBG.BgBudgetID = BPO.BgBudgetID 
                                                                             AND BBG.MasterBudget = 0
                        WHERE BPO.BgSpezkontoID = @BgSpezkontoID

                        UNION ALL

                        -- Startsaldo des Spezialkonto
                        SELECT 
                          SortKey      = 0, 
                          Datum        = NULL,
                          DatumSort    = NULL,
                          Gutschrift   = CASE WHEN StartSaldo > $0.00 AND BgSpezkontoTypCode <> 3 THEN StartSaldo END,
                          Belastung    = CASE WHEN StartSaldo > $0.00 AND BgSpezkontoTypCode = 3 AND OhneEinzelzahlung = 1 THEN StartSaldo 
                                              WHEN StartSaldo < $0.00 AND BgSpezkontoTypCode = 2 THEN StartSaldo 
                                              END,
                          Freigegeben  = NULL, 
                          Verbucht     = NULL, 
                          Gesperrt     = NULL, 
                          Buchungstext = 'Startsaldo', 
                          Saldo        = NULL
                        FROM dbo.BgSpezkonto WITH (READUNCOMMITTED)
                        WHERE BgSpezkontoID = @BgSpezkontoID 
                          AND (StartSaldo > $0.00 OR BgSpezkontoTypCode = 2)

                        UNION ALL

                        -- Abschluss des Spezialkonto
                        SELECT 
                          SortKey      = 4, 
                          Datum        = NULL,
                          DatumSort    = Created,
                          Gutschrift   = Betrag,
                          Belastung    = NULL,
                          Freigegeben  = CONVERT(BIT, 0), 
                          Verbucht     = CONVERT(BIT, 0), 
                          Gesperrt     = CONVERT(BIT, 0), 
                          Buchungstext = [Text], 
                          Saldo        = NULL
                        FROM dbo.BgSpezkontoAbschluss WITH (READUNCOMMITTED)
                        WHERE BgSpezkontoID = @BgSpezkontoID

                        ORDER BY DatumSort DESC, SortKey DESC;", new { query.BgSpezkontoId, query.Typ }));
        }