我正在尝试创建一个双重入门财务会计系统.我已经为它完成了数据库的设计,请查看此链接以查看我的数据库图表. http://i39.tinypic.com/juhbv6.png
我已经尝试了很多来开发mysql查询以生成总帐,但我没有得到任何关闭.我设法做的只是为一个帐户创建分类帐,我必须创建无限帐户.
我试过的查询如下：
$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');
在没有编写可以为所有帐户生成分类帐的mysql查询之后,我已经写下了创建总帐/ T帐户的逻辑.
现在,请你帮我解决mysql查询？
在进行以下操作之前,请检查数据库.
提前致谢 ：)
>从帐户获取accounts.name AS AccountHead,accounts.code.
>转到表debit_side并获取debit_side.account_code,
如果debit_side.account_code = accounts.code然后得到credit_side.account_code AS AccountName1(但是我将在php中回显,我想获取名称而不是代码本身)和credit_side.amount AS Amount1,SUM(credit_side.amount)AS来自credit_side的TotalAmount1,其中debit_side.transaction_id_dr = credit_side.transaction_id_cr
和transaction_info.voucher_date介于date1和date2之间
WHERE transaction_info.transaction_id = debit_side.transaction_id_dr
>完成第二步后,转到表credit_side并获取credit_side.account_code,
如果credit_side.account_code = accounts.code然后得到debit_side.account_code AS AccountName2(但是我将在php中回显,我想得到名称而不是代码本身)和debit_side.amount AS Amount2,SUM(debit_side.amount)AS来自debit_side的TotalAmount2,其中credit_side.transaction_id_cr = debit_side.transaction_id_dr
和transaction_info.voucher_date介于date1和date2之间
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> </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> </td>
<td>Total </td>
<td><?echo $TotalAmount2 ; ?></td>
</tr>
</table>
总帐样本
解决方法:
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();
}
很抱歉迟到的回复,但在你做任何事情之前,这是你想要测试的完美之物.我想为此创建一个视图文件,但它似乎很复杂,需要更多的时间,我现在在湖边
EDITED
这个解决方案可能会让你对视图产生影响,因为我已经测试过它我怎么试图将这两个查询结合起来并成功.查询可能看起来有些复杂,但它取得了完美的结果.
这里是
$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();
虽然这个查询工作正常,但这里是这个查询的修改和优化版本我真的从这个查询中学到了东西,你也应该看看这个
Strange Behaviour of Group by in Query which needs to be optimized