php - 如何使用 SQL 将不同的数据库表组合成一个表?
问题描述
这是我的数据库
https://www.db-fiddle.com/f/ufQ7cpPPtcrQ17GAhLHP3W/12
基于起止日期的预期输出(起止日期 = 2007-01-01 todate=2009-10-01 of invoice,receipt,creditnote)
type date description invoiceno debit credit balance
INV 2007-01-24 LPO:RC/1086 1001 100.00 100.00
INV 2007-03-24 LPO:RC/1098 1002 200.00 300.00
Receipt 2007-04-04 2002 1001 50.00 250.00
Receipt 2007-04-04 2002 1002 100.00 150.00
Receipt 2007-11-28 2003 1002 100.00 50.00
INV 2008-02-14 LPO:RC/929 1003 500.00 550.00
Receipt 2008-07-07 2004 1003 500.00 50.00
INV 2008-08-16 LPO:RC/815 1004 20.00 70.00
Receipt 2008-12-23 2005 1004 20.00 50.00
INV 2009-01-02 LPO:RC/999 1005 1000.00 1050.00
CreditNote 2009-01-02 3005 1005 1000.00 50.00
Total debit: 820.00
Total credit: 770.00
Total bal: 50.00
控制器
public function list_soa(){
$val = $this->input->post('id');
$start_date =$this->input->post('from');
$end_date =$this->input->post('to');
$data['all_soa_details'] = $this->createcompany->getSalesWithPayments($val,$start_date,$end_date);
echo json_encode($data);
return;
}
模态:
public function getSales($id)
{
$q = $this->db->get_where('invoice_details', array('comp_id' => $id));
if( $q->num_rows() > 0 ) {
return $q->result();
}
return FALSE;
}
public function getPayments($id)
{
$this->db->select('*');
$this->db->from('receipt_details a');
$this->db->join('received_amount_details b', 'b.receipt_no =a.rece_No', 'left');
$this->db->where('a.comp_id', $id);
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
}
return FALSE;
}
public function getSalesWithPayments($companyId,$from,$to) {
$sales = [];
foreach ($this->getSales($companyId) as $sale) {
$sale->payment = null;
$sales[$sale->invoice_ID] = $sale;
}
foreach ($this->getPayments($companyId) as $payment) {
$sales[$payment->invoice_no]->payment = $payment;
}
return $sales;
}
阿贾克斯:
$('#fileterallsoa').click(function(event) {
var getID_comp = $('#getID_comp').val();
var from = $('#fromdate_soa_view').val();
var to = $('#todate_soa_view').val();
$.ajax({
url: base_url + "index.php/welcome/list_soa/",
type: "POST",
data: {
"id": getID_comp,
"from": from,
"to": to
},
success: function(data) {
var new_data = $.parseJSON(data);
console.log(new_data);
}
});
});
如上所示,如何在一张表中显示结果?我需要显示发票、收据和贷方通知单的日期,并为客户计算余额?
解决方案
您可以应用连接 3 个表的 select 语句。
$this->db->select('a.comp_id,a.date');
$this->db->from('invoice_details a');
$this->db->inner_join('receipt_details b','b.receipt_no=a.comp_id', 'left')
$this->db->inner_join('received_amount_details c', 'b.receipt_no =c.rece_No', 'left')
推荐阅读
- sql-server - 部署后如何保护 SSIS 包的敏感数据?
- ngrx - 从 NGRX 中的另一个 reducer 访问 reducer 状态
- twilio - 如何配置 Twilio 号码的 SMS URL?
- php - cURL response different from when opened in a browser
- php - How to validate request json in Lumen
- apollo - Apollo Server - 关于缓存/数据源选项的困惑
- php - Laravel Voyager 管理面板显示损坏的链接文本
- c# - Access a button from the form in other class in C#
- bash - 如何输入目录作为命令行参数
- internationalization - 如何使用 i18n 定义业务特定的口语?