{"action":"created","comment":{"url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/comments/221559318","pull_request_review_id":160234113,"id":221559318,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIyMTU1OTMxOA==","diff_hunk":"@@ -771,3 +771,79 @@ BEGIN\n       HAVING balance <> 0\n     ) AS zz ORDER BY zz.date;\n END $$\n+\n+\n+-- This Procedure retrieve the balace(debit, credit) of each invoices during a specified period\n+\n+DROP PROCEDURE IF EXISTS `UnbalancedInvoicePayments`$$\n+CREATE   PROCEDURE `UnbalancedInvoicePayments`(IN dateFrom DATE, IN dateTo DATE)\n+BEGIN\n+  DECLARE _uuid, _debtor_uuid, _tempRef BINARY(16);\n+  DECLARE _invoiceID VARCHAR(100);\n+  DECLARE _date DATE;\n+\n+  DECLARE done BOOLEAN;\n+  DECLARE curs1 CURSOR FOR \n+    SELECT  i.uuid, i.debtor_uuid, i.date \n+    FROM invoice i\n+    WHERE i.date BETWEEN dateFrom AND dateTo\n+    ORDER BY DATE;\n+   \n+    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\n+\n+  -- all invoice balances will be stored temporarely in stage_invoice_balance table\n+\n+  CREATE TEMPORARY TABLE IF NOT EXISTS stage_invoice_balance(\n+    tempRef BINARY(16),\n+    invoice_uuid BINARY(16),\n+    debit DECIMAL(19, 4),\n+    credit DECIMAL(19, 4),\n+    debtor_uuid BINARY(16),\n+    DATE DATE,\n+    reference VARCHAR(100)\n+  );\n+\n+  SET _tempRef = HUID(UUID());\n+\n+  OPEN curs1;\n+    read_loop: LOOP\n+    FETCH curs1 INTO _uuid, _debtor_uuid, _date;\n+      IF done THEN\n+        LEAVE read_loop;\n+      END IF;\n+      -- some payment may be posted in the general ledger so it union  with posting_journal is required\n+\n+      SELECT IFNULL(d.text, '') INTO _invoiceID \n+      FROM document_map d WHERE d.uuid = _uuid\n+      LIMIT 1;\n+      -- check if the invoice is debited in stage_invoice_balance, this operation should not be done twise\n+      -- the invoice must be debited once and after\n+\n+      INSERT INTO stage_invoice_balance\n+      SELECT _tempRef, _uuid, SUM(invoices_payments.debit_equiv) AS debit, SUM(invoices_payments.credit_equiv) AS credit, _debtor_uuid, _date, _invoiceID\n+      FROM\n+\t      ( SELECT  p.record_uuid AS UUID, p.debit_equiv, p.credit_equiv\n+          FROM posting_journal p\n+          WHERE p.record_uuid = _uuid AND p.entity_uuid = _debtor_uuid\n+          UNION ALL\n+          SELECT p.record_uuid AS UUID, p.debit_equiv, p.credit_equiv\n+          FROM general_ledger p\n+          WHERE  (p.record_uuid = _uuid AND p.entity_uuid = _debtor_uuid) OR (p.reference_uuid = _uuid AND p.entity_uuid = _debtor_uuid)\n+        ) AS invoices_payments\n+      GROUP BY invoices_payments.uuid;\n+\n+    END LOOP;\n+  CLOSE curs1;\n+\n+  SELECT x.invoice_uuid, x.debtor_uuid, x.debit, x.credit, (x.debit - x.credit) AS balance,\n+    (x.credit/x.debit) AS paymentPercentage , x.reference\n+  FROM (\n+    SELECT  BUID(invoice_uuid) AS invoice_uuid, BUID(debtor_uuid) AS debtor_uuid, \n+    SUM(debit) AS debit, SUM(credit) AS credit, DATE AS creation_date, reference\n+    FROM stage_invoice_balance\n+    WHERE tempRef = _tempRef\n+    GROUP BY reference","path":"server/models/procedures/invoicing.sql","position":75,"original_position":75,"commit_id":"a2a02674058659d0531fea75d776519d0c378972","original_commit_id":"a2a02674058659d0531fea75d776519d0c378972","user":{"login":"jniles","id":896472,"node_id":"MDQ6VXNlcjg5NjQ3Mg==","avatar_url":"https://avatars0.githubusercontent.com/u/896472?v=4","gravatar_id":"","url":"https://api.github.com/users/jniles","html_url":"https://github.com/jniles","followers_url":"https://api.github.com/users/jniles/followers","following_url":"https://api.github.com/users/jniles/following{/other_user}","gists_url":"https://api.github.com/users/jniles/gists{/gist_id}","starred_url":"https://api.github.com/users/jniles/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jniles/subscriptions","organizations_url":"https://api.github.com/users/jniles/orgs","repos_url":"https://api.github.com/users/jniles/repos","events_url":"https://api.github.com/users/jniles/events{/privacy}","received_events_url":"https://api.github.com/users/jniles/received_events","type":"User","site_admin":false},"body":"It will be faster to group by `invoice_uuid`.  It should do the same thing, I think, but `invoice_uuid` is binary.","created_at":"2018-10-01T10:22:24Z","updated_at":"2018-10-01T10:57:01Z","html_url":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191#discussion_r221559318","pull_request_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/comments/221559318"},"html":{"href":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191#discussion_r221559318"},"pull_request":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191"}}},"pull_request":{"url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191","id":217230876,"node_id":"MDExOlB1bGxSZXF1ZXN0MjE3MjMwODc2","html_url":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191","diff_url":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191.diff","patch_url":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191.patch","issue_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/3191","number":3191,"state":"open","locked":false,"title":"feat(reports) unbalanced invoice payments report","user":{"login":"jeremielodi","id":25838121,"node_id":"MDQ6VXNlcjI1ODM4MTIx","avatar_url":"https://avatars2.githubusercontent.com/u/25838121?v=4","gravatar_id":"","url":"https://api.github.com/users/jeremielodi","html_url":"https://github.com/jeremielodi","followers_url":"https://api.github.com/users/jeremielodi/followers","following_url":"https://api.github.com/users/jeremielodi/following{/other_user}","gists_url":"https://api.github.com/users/jeremielodi/gists{/gist_id}","starred_url":"https://api.github.com/users/jeremielodi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jeremielodi/subscriptions","organizations_url":"https://api.github.com/users/jeremielodi/orgs","repos_url":"https://api.github.com/users/jeremielodi/repos","events_url":"https://api.github.com/users/jeremielodi/events{/privacy}","received_events_url":"https://api.github.com/users/jeremielodi/received_events","type":"User","site_admin":false},"body":"This report shows the balance of invoices witch are not completely paid or overpaid during a period\"","created_at":"2018-09-21T09:53:18Z","updated_at":"2018-10-01T10:57:01Z","closed_at":null,"merged_at":null,"merge_commit_sha":"8d49b66074669835a02be1b433c55894891532ad","assignee":null,"assignees":[],"requested_reviewers":[],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191/commits","review_comments_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191/comments","review_comment_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/comments{/number}","comments_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/3191/comments","statuses_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/statuses/a2a02674058659d0531fea75d776519d0c378972","head":{"label":"jeremielodi:invoice_payment_balance","ref":"invoice_payment_balance","sha":"a2a02674058659d0531fea75d776519d0c378972","user":{"login":"jeremielodi","id":25838121,"node_id":"MDQ6VXNlcjI1ODM4MTIx","avatar_url":"https://avatars2.githubusercontent.com/u/25838121?v=4","gravatar_id":"","url":"https://api.github.com/users/jeremielodi","html_url":"https://github.com/jeremielodi","followers_url":"https://api.github.com/users/jeremielodi/followers","following_url":"https://api.github.com/users/jeremielodi/following{/other_user}","gists_url":"https://api.github.com/users/jeremielodi/gists{/gist_id}","starred_url":"https://api.github.com/users/jeremielodi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jeremielodi/subscriptions","organizations_url":"https://api.github.com/users/jeremielodi/orgs","repos_url":"https://api.github.com/users/jeremielodi/repos","events_url":"https://api.github.com/users/jeremielodi/events{/privacy}","received_events_url":"https://api.github.com/users/jeremielodi/received_events","type":"User","site_admin":false},"repo":{"id":92812291,"node_id":"MDEwOlJlcG9zaXRvcnk5MjgxMjI5MQ==","name":"bhima-2.X","full_name":"jeremielodi/bhima-2.X","private":false,"owner":{"login":"jeremielodi","id":25838121,"node_id":"MDQ6VXNlcjI1ODM4MTIx","avatar_url":"https://avatars2.githubusercontent.com/u/25838121?v=4","gravatar_id":"","url":"https://api.github.com/users/jeremielodi","html_url":"https://github.com/jeremielodi","followers_url":"https://api.github.com/users/jeremielodi/followers","following_url":"https://api.github.com/users/jeremielodi/following{/other_user}","gists_url":"https://api.github.com/users/jeremielodi/gists{/gist_id}","starred_url":"https://api.github.com/users/jeremielodi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jeremielodi/subscriptions","organizations_url":"https://api.github.com/users/jeremielodi/orgs","repos_url":"https://api.github.com/users/jeremielodi/repos","events_url":"https://api.github.com/users/jeremielodi/events{/privacy}","received_events_url":"https://api.github.com/users/jeremielodi/received_events","type":"User","site_admin":false},"html_url":"https://github.com/jeremielodi/bhima-2.X","description":"A hospital information management application for rural Congolese hospitals","fork":true,"url":"https://api.github.com/repos/jeremielodi/bhima-2.X","forks_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/forks","keys_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/keys{/key_id}","collaborators_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/teams","hooks_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/hooks","issue_events_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/issues/events{/number}","events_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/events","assignees_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/assignees{/user}","branches_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/branches{/branch}","tags_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/tags","blobs_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/git/refs{/sha}","trees_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/git/trees{/sha}","statuses_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/statuses/{sha}","languages_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/languages","stargazers_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/stargazers","contributors_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/contributors","subscribers_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/subscribers","subscription_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/subscription","commits_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/commits{/sha}","git_commits_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/git/commits{/sha}","comments_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/comments{/number}","issue_comment_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/issues/comments{/number}","contents_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/contents/{+path}","compare_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/compare/{base}...{head}","merges_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/merges","archive_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/downloads","issues_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/issues{/number}","pulls_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/pulls{/number}","milestones_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/milestones{/number}","notifications_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/labels{/name}","releases_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/releases{/id}","deployments_url":"https://api.github.com/repos/jeremielodi/bhima-2.X/deployments","created_at":"2017-05-30T08:23:54Z","updated_at":"2017-05-30T08:23:58Z","pushed_at":"2018-10-01T08:30:06Z","git_url":"git://github.com/jeremielodi/bhima-2.X.git","ssh_url":"git@github.com:jeremielodi/bhima-2.X.git","clone_url":"https://github.com/jeremielodi/bhima-2.X.git","svn_url":"https://github.com/jeremielodi/bhima-2.X","homepage":"","size":16072,"stargazers_count":0,"watchers_count":0,"language":"JavaScript","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"gpl-2.0","name":"GNU General Public License v2.0","spdx_id":"GPL-2.0","url":"https://api.github.com/licenses/gpl-2.0","node_id":"MDc6TGljZW5zZTg="},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"IMA-WorldHealth:master","ref":"master","sha":"55b66096a39007253e6323e1ee55585749072940","user":{"login":"IMA-WorldHealth","id":5330583,"node_id":"MDEyOk9yZ2FuaXphdGlvbjUzMzA1ODM=","avatar_url":"https://avatars1.githubusercontent.com/u/5330583?v=4","gravatar_id":"","url":"https://api.github.com/users/IMA-WorldHealth","html_url":"https://github.com/IMA-WorldHealth","followers_url":"https://api.github.com/users/IMA-WorldHealth/followers","following_url":"https://api.github.com/users/IMA-WorldHealth/following{/other_user}","gists_url":"https://api.github.com/users/IMA-WorldHealth/gists{/gist_id}","starred_url":"https://api.github.com/users/IMA-WorldHealth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/IMA-WorldHealth/subscriptions","organizations_url":"https://api.github.com/users/IMA-WorldHealth/orgs","repos_url":"https://api.github.com/users/IMA-WorldHealth/repos","events_url":"https://api.github.com/users/IMA-WorldHealth/events{/privacy}","received_events_url":"https://api.github.com/users/IMA-WorldHealth/received_events","type":"Organization","site_admin":false},"repo":{"id":49052669,"node_id":"MDEwOlJlcG9zaXRvcnk0OTA1MjY2OQ==","name":"bhima-2.X","full_name":"IMA-WorldHealth/bhima-2.X","private":false,"owner":{"login":"IMA-WorldHealth","id":5330583,"node_id":"MDEyOk9yZ2FuaXphdGlvbjUzMzA1ODM=","avatar_url":"https://avatars1.githubusercontent.com/u/5330583?v=4","gravatar_id":"","url":"https://api.github.com/users/IMA-WorldHealth","html_url":"https://github.com/IMA-WorldHealth","followers_url":"https://api.github.com/users/IMA-WorldHealth/followers","following_url":"https://api.github.com/users/IMA-WorldHealth/following{/other_user}","gists_url":"https://api.github.com/users/IMA-WorldHealth/gists{/gist_id}","starred_url":"https://api.github.com/users/IMA-WorldHealth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/IMA-WorldHealth/subscriptions","organizations_url":"https://api.github.com/users/IMA-WorldHealth/orgs","repos_url":"https://api.github.com/users/IMA-WorldHealth/repos","events_url":"https://api.github.com/users/IMA-WorldHealth/events{/privacy}","received_events_url":"https://api.github.com/users/IMA-WorldHealth/received_events","type":"Organization","site_admin":false},"html_url":"https://github.com/IMA-WorldHealth/bhima-2.X","description":"A hospital information management application for rural Congolese hospitals","fork":false,"url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X","forks_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/forks","keys_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/keys{/key_id}","collaborators_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/teams","hooks_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/hooks","issue_events_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/events{/number}","events_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/events","assignees_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/assignees{/user}","branches_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/branches{/branch}","tags_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/tags","blobs_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/git/refs{/sha}","trees_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/git/trees{/sha}","statuses_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/statuses/{sha}","languages_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/languages","stargazers_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/stargazers","contributors_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/contributors","subscribers_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/subscribers","subscription_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/subscription","commits_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/commits{/sha}","git_commits_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/git/commits{/sha}","comments_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/comments{/number}","issue_comment_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/comments{/number}","contents_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/contents/{+path}","compare_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/compare/{base}...{head}","merges_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/merges","archive_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/downloads","issues_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues{/number}","pulls_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls{/number}","milestones_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/milestones{/number}","notifications_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/labels{/name}","releases_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/releases{/id}","deployments_url":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/deployments","created_at":"2016-01-05T08:30:30Z","updated_at":"2018-09-28T05:12:31Z","pushed_at":"2018-10-01T10:12:15Z","git_url":"git://github.com/IMA-WorldHealth/bhima-2.X.git","ssh_url":"git@github.com:IMA-WorldHealth/bhima-2.X.git","clone_url":"https://github.com/IMA-WorldHealth/bhima-2.X.git","svn_url":"https://github.com/IMA-WorldHealth/bhima-2.X","homepage":"","size":16016,"stargazers_count":89,"watchers_count":89,"language":"JavaScript","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":53,"mirror_url":null,"archived":false,"open_issues_count":245,"license":{"key":"gpl-2.0","name":"GNU General Public License v2.0","spdx_id":"GPL-2.0","url":"https://api.github.com/licenses/gpl-2.0","node_id":"MDc6TGljZW5zZTg="},"forks":53,"open_issues":245,"watchers":89,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191"},"html":{"href":"https://github.com/IMA-WorldHealth/bhima-2.X/pull/3191"},"issue":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/3191"},"comments":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/issues/3191/comments"},"review_comments":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191/comments"},"review_comment":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/pulls/3191/commits"},"statuses":{"href":"https://api.github.com/repos/IMA-WorldHealth/bhima-2.X/statuses/a2a02674058659d0531fea75d776519d0c378972"}},"author_association":"COLLABORATOR"}}
{"actor":{"display_login":"jniles"}}