source - Cómo crear Contabilidad General/Cuenta T usando PHP Mysql

sistema de ingresos y egresos en php

Question

Estoy tratando de crear un sistema de contabilidad financiera de doble entrada. He completado el diseño de la base de datos para él, por favor revise este enlace para ver el diagrama de mi base de datos. http://i39.tinypic.com/juhbv6.png

He intentado mucho desarrollar la consulta de mysql para producir el libro mayor, pero no llegué cerca. Todo lo que he logrado hacer es crear un libro de contabilidad para una sola cuenta en la que tengo que crear una cuenta ilimitada.

La consulta que he intentado es la siguiente:

 $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'');

Después de no poder escribir la consulta de mysql que puede generar el libro de contabilidad de todas las cuentas, anoté la lógica para crear la cuenta de Libro mayor / T.

Ahora, ¿podría ayudarme con la consulta de MySQL?

Por favor revise la base de datos antes de pasar por lo siguiente. Gracias por adelantado :)

1. Obtenga accounts.name AS AccountHead, accounts.code de las cuentas.

2. Vaya a la tabla debit_side y get debit_side.account_code , si debit_side.account_code = accounts.code luego obtenga credit_side.account_code AS credit_side.account_code (pero cuando haga eco en php, quiero obtener el nombre en lugar del código en sí) y credit_side.amount AS Amount1, SUM(credit_side.amount) AS TotalAmount1 from credit_side donde debit_side.transaction_id_dr = credit_side.transaction_id_cr y transaction_info.voucher_date está entre la fecha1 y la fecha2 WHERE transaction_info.transaction_id = debit_side.transaction_id_dr

3. Después de completar el segundo paso, vaya a la tabla credit_side y get credit_side.account_code ,
   si credit_side.account_code = accounts.code entonces obtenga debit_side.account_code AS debit_side.account_code (pero cuando haga eco en php, quiero obtener el nombre en lugar del código en sí) y debit_side.amount AS Amount2, SUM(debit_side.amount) AS TotalAmount2 desde debit_side donde credit_side.transaction_id_cr = debit_side.transaction_id_dr y transaction_info.voucher_date está entre date1 y date2 WHERE transaction_info.transaction_id = credit_side.transaction_id_cr

Ahora en el archivo de vista pretendo tener lo siguiente:

   <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>

Muestra del libro mayor

Answer 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();
}

Lo siento por la respuesta tardía, pero esta es la prueba perfecta que desea probar antes de hacer algo con. quería crear un archivo de vista para esto, pero parece complicado y requiere más tiempo que en el momento

EDITADO

Esta solución puede estar trastocando la vista, ya que la he probado. Siempre he intentado combinar estas dos consultas y he tenido éxito. La consulta puede parecer compleja, pero está obteniendo los resultados perfectos. Aquí está

$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();

Aunque esta consulta funciona bien, pero aquí hay una versión modificada y optimizada de esta consulta, realmente aprendí cosas de esta consulta, eche un vistazo a esto también.

Comportamiento extraño de Group by en Query que debe optimizarse