0

I'm trying to get an array of invoice objects for each client, but the error I'm getting is when a client has more than one invoice, a new object gets created for that client and it's new invoice. Instead, I would like for each invoice that belongs to the client to be within that client's invoice data. Also not sure how I would combine the total combined invoice to the outer object for "clientInvoiceTotal," combining all of it's invoices from the "invoiceTotal" property.

CREATE PROCEDURE get_report

@userId INT,
@year INT

AS

SELECT
JSON_QUERY((
    SELECT
        cl.client_name AS clientName,
        i.total AS clientInvoiceTotal,
        JSON_QUERY((
            SELECT
                i.invoice_id AS invoiceId,
                i.total AS invoiceTotal,
                JSON_QUERY((
                    SELECT
                        s.service_name AS serviceName,
                        cis.service_price AS invoiced
                    FROM
                        case_identifier_to_service cis
                        INNER JOIN services s ON s.service_id = cis.service_id
                    WHERE cis.invoice_id = i.invoice_id
                    FOR JSON PATH
                )) AS serviceData
                FROM invoices i WHERE i.invoice_id = c.invoice_id
            FOR JSON PATH
        )) AS clientData
    FROM
        cases c
        INNER JOIN clients cl ON cl.client_id = c.client_id
        INNER JOIN invoices i ON i.invoice_id = c.invoice_id
    WHERE c.user_id=@userId AND DATEPART(year, c.date_created) = @year
    FOR JSON PATH
)) AS report
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

The data that I SHOULD be getting back is:

{  
   "report":[  
      {  
         "clientName":"Client1",
         "clientInvoiceTotal":59500.00,
         "clientData":[  
            {  
               "invoiceId":100,
               "invoiceTotal":59500.00,
               "serviceData":[  
                  {  
                     "serviceName":"LDD",
                     "invoiced":59500.00
                  }
               ]
            }
         ]
      },
      {  
         "clientName":"Client2",
         "clientInvoiceTotal":53150.00,
         "clientData":[  
            {  
               "invoiceId":101,
               "invoiceTotal":8600.00,
               "serviceData":[  
                  {  
                     "serviceName":"BCP",
                     "invoiced":4900.00
                  },
                  {  
                     "serviceName":"BRS",
                     "invoiced":3700.00
                  }
               ]
            },
            {  
               "invoiceId":102,
               "invoiceTotal":44550.00,
               "serviceData":[  
                  {  
                     "serviceName":"DDBR",
                     "invoiced":44550.00
                  }
               ]
            }
         ]
      }
   ]
}

But instead, if a client has more than one invoice another object with duplicate client name is added, the data I'm getting back looks like this, which is INCORRECT:

{  
   "report":[  
      {  
         "clientName":"Client1",
         "clientInvoiceTotal":59500.00,
         "clientData":[  
            {  
               "invoiceId":100,
               "invoiceTotal":59500.00,
               "serviceData":[  
                  {  
                     "serviceName":"LDD",
                     "invoiced":59500.00
                  }
               ]
            }
         ]
      },
      {  
         "clientName":"Client2",
         "clientInvoiceTotal":8600.00,
         "clientData":[  
            {  
               "invoiceId":101,
               "invoiceTotal":8600.00,
               "serviceData":[  
                  {  
                     "serviceName":"BCP",
                     "invoiced":4900.00
                  },
                  {  
                     "serviceName":"BRS",
                     "invoiced":3700.00
                  }
               ]
            }
         ]
      },
      {  
         "clientName":"Client2",
         "clientInvoiceTotal":44550.00,
         "clientData":[  
            {  
               "invoiceId":102,
               "invoiceTotal":44550.00,
               "serviceData":[  
                  {  
                     "serviceName":"DDBR",
                     "invoiced":44550.00
                  }
               ]
            }
         ]
      }
   ]
}
hypnagogia
  • 173
  • 3
  • 16

0 Answers0