Geek asks and answers

How to create a general ledger / account using PHP Mysql

I am trying to create a double entry accounting system. I have completed the development of a database for this, please view this link to see a diagram of my database. http://i39.tinypic.com/juhbv6.png

I tried a lot to develop a mysql query to create a general ledger, but it didn't work out for me. All I managed to do was create a register for only one account, where I need to create an unlimited account.

I tried to execute the following query:

 $this->db->select('*');
    $this->db->from('credit_side');
    $this->db->join('debit_side', ' debit_side.transaction_id_dr = credit_side.transaction_id_cr ');
    $this->db->join('transaction_info', 'transaction_info.transaction_id = credit_side.transaction_id_cr ');
    $this->db->join('accounts', 'accounts.code = credit_side.account_code ');
    $this->db->where('debit_side.account_code', '1001'); 
    $this->db->order_by('voucher_date','ASC');

After you were unable to write a mysql query that can create a register for all accounts, I wrote down the logic for creating a General Ledger / T account.

Now please help me with mysql query?

, , . Advance:)

  • accounts.name AS AccountHead, accounts.code .

  • debit_side get debit_side.account_code, if debit_side.account_code= accounts.code, credit_side.account_code AS AccountName1 ( php, ) credit_side.amount AS Amount1, SUM(credit_side.amount) AS TotalAmount1 from credit_side debit_side.transaction_id_dr= credit_side.transaction_id_cr transaction_info.voucher_date 1 2 WHERE transaction_info.transaction_id= debit_side.transaction_id_dr

  • credit_side get credit_side.account_code,
    if credit_side.account_code= accounts.code, debit_side.account_code AS AccountName2 ( php, ) debit_side.amount AS Amount2, SUM(debit_side.amount) AS TotalAmount2 from debit_side credit_side.transaction_id_cr= debit_side.transaction_id_dr transaction_info.voucher_date 1 2 WHERE transaction_info.transaction_id= credit_side.transaction_id_cr

:

   <table width="200" border="0">
     <tr><td colspan="5">Account Head <?echo $AccountHead ; ?> </td> </tr>


    <tr>
           <td>Dr.</td>
           <td>amount.</td>
            <td>&nbsp;</td>
           <td>Cr</td>
          <td>Amount</td>

    </tr>
     <tr>
       <td><?echo $AccountName1 ; ?></td>
       <td><?echo $Ammount1 ; ?></td>
      <td></td>
 <td><?echo $AccountName2 ; ?></td>
     <td><?echo $Ammount2 ; ?></td>

     </tr>

     <tr>
     <td>Total</td>
     <td><?echo $TotalAmount1 ; ?></td>
     <td>&nbsp;</td>
 <td>Total  </td>
 <td><?echo $TotalAmount2 ; ?></td>

     </tr>
     </table>

enter image description here
+2
1
public function getDebits(){    
    $data   =   array(
                'debit_side.account_code DebitCode',
                'group_concat(distinct accounts.name) as DebitAccount',
                'group_concat(debit_side.amount) as DebitAmount',
                'group_concat(transaction_info.voucher_date) as DebitVoucherDate'
                );
    $this->db->select($data);
    $this->db->from('accounts');
    $this->db->join('credit_side','accounts.code = credit_side.account_code','left');
    $this->db->join('debit_side','debit_side.transaction_id_dr = credit_side.transaction_id_cr','left');
    $this->db->join('transaction_info','transaction_info.transaction_id = credit_side.transaction_id_cr','left');
    $this->db->group_by('debit_side.account_code');
    $this->db->order_by('debit_side.account_code','ASC');
    $query  =   $this->db->get();
    return  $query->result_array();
}


public  function getCredits()
{
    $data   =   array(
                'credit_side.account_code CreditCode',
                'group_concat(distinct accounts.name) as CreditAccount',
                'group_concat(credit_side.amount) as CreditAmount',
                'group_concat(transaction_info.voucher_date) as CreditVoucherDate'
                );
    $this->db->select($data);
    $this->db->from('accounts');
    $this->db->join('debit_side','accounts.code = debit_side.account_code','left');
    $this->db->join('credit_side','debit_side.transaction_id_dr = credit_side.transaction_id_cr','left');
    $this->db->join('transaction_info','transaction_info.transaction_id = credit_side.transaction_id_cr','left');
    $this->db->group_by('credit_side.account_code');
    $this->db->order_by('credit_side.account_code','ASC');
    $query  =   $this->db->get();
    return  $query->result_array();
}

, , , - . , , , ,

, . - . , .

$data   =   array(
                  'debit_side.account_code    Code',
                  'group_concat(distinct accounts.name) as DebitAccount',
                  'group_concat(debit_side.amount) as DebitAmount',
                  'group_concat(transaction_info.voucher_date) as DebitVoucherDate',
                  '(SELECT group_concat(distinct accounts.name) as CreditAccount FROM (accounts)
                    left JOIN debit_side ON accounts.code = debit_side.account_code
                    left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
                    left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
                    group by credit_side.account_code
                    having credit_side.account_code = `Code`) as CreditAccount',
                  '(SELECT  group_concat(credit_side.amount) as CreditAmount FROM (accounts)
                     left JOIN debit_side ON accounts.code = debit_side.account_code
                     left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
                     left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
                   group by credit_side.account_code
                   having credit_side.account_code = `Code`) as CreditAmount',  
                  '(SELECT  group_concat(transaction_info.voucher_date) as CreditVoucherDate FROM (accounts)
                    left JOIN debit_side ON accounts.code = debit_side.account_code
                    left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
                    left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
                    group by credit_side.account_code
                    having credit_side.account_code = `Code`) as CreditVoucherDate'

                );
    $this->db->select($data);
    $this->db->from('accounts');
    $this->db->join('credit_side','accounts.code = credit_side.account_code','left');
    $this->db->join('debit_side','debit_side.transaction_id_dr = credit_side.transaction_id_cr','left');
    $this->db->join('transaction_info','transaction_info.transaction_id = credit_side.transaction_id_cr','left');
    $this->db->group_by('debit_side.account_code');
    $this->db->order_by('debit_side.account_code','ASC');
    $query  =   $this->db->get();
    return  $query->result_array();

, , , .

,

+5

More articles:


All Articles