SET sql_mode = '';
  select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl where  gl.date between '2020-01-01 00:00:00' and '2020-01-31 00:00:00'
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit



  select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl where  gl.date between '2020-02-01 00:00:00' and '2020-02-31 00:00:00'
    group by gl.transaction_no, gl.parent_object,gl.parent_pk
    HAVING debit != credit



  select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl where  gl.date between '2020-02-01 00:00:00' and '2020-02-31 00:00:00'
    group by gl.transaction_no, gl.parent_object,gl.parent_pk
    HAVING debit != credit

select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit



  select group_concat(pk) from (select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
  where  gl.transaction_no is not null and gl.parent_object = 'app\\models\\AccountReceivable'
    group by gl.transaction_no, gl.parent_object,gl.parent_pk
    HAVING debit != credit) x

    select gl.transaction_no,gl.date,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
    where  gl.transaction_no is not null and gl.parent_object = 'app\\models\\Costing'
      group by gl.transaction_no, gl.parent_object,gl.parent_pk
      HAVING debit != credit


      select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
      where  gl.transaction_no is not null and gl.parent_object = 'app\\modules\\sale\\models\\Costing'
        group by gl.transaction_no, gl.parent_object,gl.parent_pk
        HAVING debit != credit

select group_concat(pk) from (select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null and gl.parent_object = 'app\\modules\\sale\\models\\Invoice'
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit) x


  select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
  where  gl.transaction_no is not null and gl.parent_object = 'app\\modules\\pos\\models\\PosReceipt'
    group by gl.transaction_no, gl.parent_object,gl.parent_pk
    HAVING debit != credit

    select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
    where  gl.transaction_no is not null and gl.parent_object = 'app\\modules\\sale\\models\\Invoice'
      group by gl.transaction_no, gl.parent_object,gl.parent_pk
      HAVING debit != credit

select * from 0p4_gl_transaction gl
where  gl.transaction_no is not null and gl.parent_object = 'app\\modules\\sale\\models\\Invoice' and gl.gl_transaction_no like "CIN%"


# CIN id or INV ID to check
8088,8089,8093,8094,8114,8095,8096,8097,8098,8099,8100,8051,8053,8055,8056,8069,8070,8071,8082,8090,8101,8105,8106,8107,8108,8109,8110,8111,8112,8010,8013,8034,8102,7706

AR need to regenerate
606,607,914,813,336,1595,2312,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1870,1871,1872,1873,1874,1875,1876,2252,2283


select group_concat(pk) from (select gl.transaction_no,gl.parent_object,gl.parent_pk as pk, sum(gl.debit) as debit, sum(gl.credit) as credit from 0p4_gl_transaction gl
    where  gl.transaction_no is not null
      group by gl.transaction_no, gl.parent_object,gl.parent_pk
      HAVING debit != credit) x


46795,596.49

377,171.63


334141.67


select group_concat(account_payable_id) from (SELECT * FROM `0p4_account_payable` ap where ap.bank_charges >0) x


select gl.customer_name ,sum(gl.debit-gl.credit) as amount from  0p4_gl_transaction gl where gl.date <= '2020-01-31' and gl.gl_code='63007' where gl.debit!=gl.local_debit
group by gl.customer_id
having amount > 0


select /*gl.gl_code,*/ sum(gl.debit),sum(gl.credit),sum(gl.local_debit)-sum(gl.local_credit) as 'balance' from 0p4_gl_transaction gl where gl.date='2020-03-31' #group by gl.gl_code



select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.local_debit) as debit, sum(gl.local_credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit


select sum(gl.local_debit) as debit, sum(gl.local_credit) as credit from  0p4_gl_transaction gl where  gl.transaction_no is not null


select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.local_debit) as debit, sum(gl.local_credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit or (debit=0  and credit =0)


select gl.transaction_no,gl.parent_object,gl.pk, sum(gl.local_debit) as debit, sum(gl.local_credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null
  group by gl.transaction_no, gl.parent_object,gl.parent_pk
  HAVING debit != credit or (debit=0  and credit =0)



select group_concat(parent_pk) from (select gl.transaction_no,gl.parent_object,gl.parent_pk, sum(gl.local_debit) as debit, sum(gl.local_credit) as credit from 0p4_gl_transaction gl
where  gl.transaction_no is not null and gl.parent_object='app\\models\\AccountReceivable'
group by gl.transaction_no, gl.parent_object,gl.parent_pk
HAVING debit != credit or (debit=0  and credit =0)) x


SELECT * FROM `0p4_invoice` WHERE round!=0 and round!='' and round is not null

select * from 1sv_chart_account c where c.yearly_reset=1

11,13,15,16,18,21,22,54,295,296,298,300,302,303,304,305,306,307,310,311,312,313,314,315,320,332,333,334,336,338,340,371,375,411,412,413,414,415,416,417,419,420,421,422,424,425,426,428,429,431,433,434,436,437,442,462,463,464,467,468,469,470,471,474,484,490,664,741,779,782,792,793,794,798,799,800,849,874,877,878,880,887,948,950,951,953,955,956,957,958,959,960,961,991,992,1016,1018,1020,1036,1136,1141,1155,1166,1168,1170,1171,1176,1177,1188,1189,1225,1231,1264,1280,1411,1413,1415,1438,1439,1487,1488,1507,1508,1509,1511,1523,1539,1586,1728,1733,1736,1737,1740,1743,1751,1753,1760,1764,1771,1772,1774,1784,1787,1819,1841,1850,1852,1853,1875,1879,1894,1896,2130,2131,2151,5829,5882,5884,6103,6104,6107,6110,6344,6361,6635,6728,6807,7702,7703,7847,7848,7849,7921,7923,7925,7966,7969,7993,7994,7995,8029,8030,8031,8046,8047,8048,8083,8084,8107,8108,8109





SELECT type FROM `0p4_invoice` WHERE `invoice_id` in (11,13,15,16,18,21,22,54,295,296,298,300,302,303,304,305,306,307,310,311,312,313,314,315,320,332,333,334,336,338,340,371,375,411,412,413,414,415,416,417,419,420,421,422,424,425,426,428,429,431,433,434,436,437,442,462,463,464,467,468,469,470,471,474,484,490,664,741,779,782,792,793,794,798,799,800,849,874,877,878,880,887,948,950,951,953,955,956,957,958,959,960,961,991,992,1016,1018,1020,1036,1136,1141,1155,1166,1168,1170,1171,1176,1177,1188,1189,1225,1231,1264,1280,1411,1413,1415,1438,1439,1487,1488,1507,1508,1509,1511,1523,1539,1586,1728,1733,1736,1737,1740,1743,1751,1753,1760,1764,1771,1772,1774,1784,1787,1819,1841,1850,1852,1853,1875,1879,1894,1896,2130,2131,2151,5829,5882,5884,6103,6104,6107,6110,6344,6361,6635,6728,6807,7702,7703,7847,7848,7849,7921,7923,7925,7966,7969,7993,7994,7995,8029,8030,8031,8046,8047,8048,8083,8084,8107,8108,8109)
where type='CONSIGNMENT'



select group_concat(invoice_id) from (SELECT * FROM `0p4_invoice` WHERE `invoice_id` in (11,13,15,16,18,21,22,54,295,296,298,300,302,303,304,305,306,307,310,311,312,313,314,315,320,332,333,334,336,338,340,371,375,411,412,413,414,415,416,417,419,420,421,422,424,425,426,428,429,431,433,434,436,437,442,462,463,464,467,468,469,470,471,474,484,490,664,741,779,782,792,793,794,798,799,800,849,874,877,878,880,887,948,950,951,953,955,956,957,958,959,960,961,991,992,1016,1018,1020,1036,1136,1141,1155,1166,1168,1170,1171,1176,1177,1188,1189,1225,1231,1264,1280,1411,1413,1415,1438,1439,1487,1488,1507,1508,1509,1511,1523,1539,1586,1728,1733,1736,1737,1740,1743,1751,1753,1760,1764,1771,1772,1774,1784,1787,1819,1841,1850,1852,1853,1875,1879,1894,1896,2130,2131,2151,5829,5882,5884,6103,6104,6107,6110,6344,6361,6635,6728,6807,7702,7703,7847,7848,7849,7921,7923,7925,7966,7969,7993,7994,7995,8029,8030,8031,8046,8047,8048,8083,8084,8107,8108,8109)
and type='INVOICE')


SELECT ar.* FROM `0p4_account_receivable` ar left join 0p4_account_receivable_oustanding aro on aro.`account_receivable_id`=ar.`account_receivable_id` where aro.transaction_date>ar.date group by aro.account_receivable_id


select * from 0p4_account_receivable_oustanding aro where aro.transaction_no='IN-2007/00101'


select i.invoice_id,i.invoice_no,i.date from 0p4_invoice i left join 0p4_gl_transaction gl on gl.parent_pk=i.invoice_id and gl.parent_object='app\\models\\Invoice'
where gl.gl_transaction_id is null and i.date >='2020-01-01' and i.sub_total >0
group by i.invoice_id limit 10


select * from  0p4_gl_transaction  where transaction_no='CIN-2005/00023' and document_type='INVOICE'


select j.date,j.journal_no,j.description,ca.gl_code,ca.gl_name,jd.description ,jd.debit,jd.credit from 0p4_journal j left join 0p4_journal_detail jd on jd.journal_id=j.journal_id
left join 0p4_chart_account ca on ca.chart_account_id=jd.chart_account_id