我有一个使用Codeigniter开发的简单存储程序。以下是主要表格。
商店_项目
+---------+----------------+---------------+--------+
| item_id | item_name | item_sub_type | status |
+---------+----------------+---------------+--------+
| 1 | A4 | 100 | 1 |
| 2 | A5 | 100 | 1 |
| 3 | CFL Bulb 15W | 102 | 1 |
| 4 | Toilet Cleaner | 102 | 1 |
+---------+----------------+---------------+--------+
item_sub_type
+-------------+---------------+-----+--------+
| sub_type_id | sub_type_name | cat | status |
+-------------+---------------+-----+--------+
| 100 | Stationary | S | 1 |
| 101 | Electric Item | S | 1 |
| 102 | Consumable | S | 1 |
| 103 | Vehicle Spare | V | 1 |
+-------------+---------------+-----+--------+
存储更新库存
+-----------------+----------------+--------------+-----------------+--------+
| update_stock_id | purchased_date | order_status | transfer_status | status |
+-----------------+----------------+--------------+-----------------+--------+
| 1 | 2019-10-01 | purchase | approved | 1 |
| 2 | 2019-10-02 | purchase | approved | 1 |
| 3 | 2019-10-03 | issue | approved | 1 |
+-----------------+----------------+--------------+-----------------+--------+
store_update_stock_details
+-------------------------+-----------------+------+-----+------------+--------+
| update_stock_details_id | update_stock_id | item | qty | unit_price | status |
+-------------------------+-----------------+------+-----+------------+--------+
| 1 | 1 | 1 | 10 | 450.00 | 1 |
| 2 | 1 | 2 | 5 | 375.00 | 1 |
| 3 | 2 | 1 | 20 | 450.00 | 1 |
| 4 | 2 | 3 | 100 | 850.00 | 1 |
| 5 | 2 | 4 | 15 | 125.00 | 1 |
| 6 | 3 | 1 | -15 | 450.00 | 1 |
| 7 | 3 | 3 | -10 | 850.00 | 1 |
| 8 | 3 | 4 | -2 | 125.00 | 1 |
+-------------------------+-----------------+------+-----+------------+--------+
store_update_stock_details表中的已发布数量用减号(-(表示。
我尝试按项目获取筛选的项目摘要&item_sub_type
报表控制器
public function stockReport()
{
$bc = array(array('link' => '#', 'page' => 'Stationary Stock Report'));
$meta = array('page_title' => 'Stationary Stock Report', 'bc' => $bc);
$where = NULL;
if ($this->input->post('item')) {
$item = $this->input->post('item');
$where .= " AND store_item.item_id = '$item' ";
} else {
$item = NULL;
}
$where = NULL;
if ($this->input->post('subType')) {
$subType = $this->input->post('subType');
$where .= " AND store_sub_type.sub_type_id = '$subType' ";
} else {
$subType = NULL;
}
$this->data['item'] = $this->Item_Model->getItem();
$this->data['subType'] = $this->Item_Model->getSubType();
$this->data['summary'] = $this->Report_model->stockSummary($where);
$this->render('reports/stockReport', $meta, $this->data);
}
项目模型
function getItem()
{
$q = $this->db->get_where('store_item', array('status' => '1'));
if ($q->num_rows() > 0) {
return $q->result();
}
return FALSE;
}
function getSubType()
{
$this->db->select("*");
$this->db->from('store_sub_type');
$this->db->where("status=1 and cat='S' ");
$this->db->order_by('sub_type_id');
$q1 = $this->db->get();
if ($q1->num_rows() > 0) {
return $q1->result();
}
return FALSE;
}
报表模型
function stockSummary($where){
$q = $this->db->query("
select
item_id,
item_name,
unit_price,
sub_type_id,
( SELECT @stock := sum(ifnull(qty,0)) AS qty FROM store_update_stock_details de2
JOIN store_update_stock st2 ON de2.update_stock_id=st2.update_stock_id
RIGHT JOIN store_item item2 ON de2.item=item2.item_id WHERE de2.status = 1 and
st2.transfer_status = 'Approved' AND tmp.item_id = item2.item_id GROUP BY item2.item_id) as cur_stock,
SUM(purchase) as purchase,
SUM(issues) as issues
FROM (
SELECT
item_name,
item_id,
unit_price,
store_sub_type.sub_type_id,
CASE WHEN order_status = 'purchase' THEN qty else 0 end as purchase ,
CASE WHEN order_status ='issue' and transfer_status='Issued' THEN qty else 0 end as issues ,
CASE WHEN store_update_stock.status != 0 and transfer_status !='Pending' and transfer_status !='Recomended' THEN qty else 0
end as balance
from store_update_stock
join store_update_stock_details on store_update_stock.update_stock_id=store_update_stock_details.update_stock_id
join store_item on store_update_stock_details.item=store_item.item_id
join store_sub_type on store_sub_type.sub_type_name=store_item.item_sub_type
where store_update_stock.status=1 $where
)tmp
group by item_id, unit_price
");
stockReport视图
<style>
.group {
background: #FFAAAA !important;
color: #009966;
}
.table > tbody > tr > th, .table > tbody > tr > td{
font-size: 13px;
}
</style>
<div class="box box-info">
<div class="box box-info collapsed-box">
<div class="box-header with-border">
<h3 class="box-title">List of Stationary / Consumables & Electical Items</h3>
<div class="box-tools pull-right">
<button type="button" class="btn btn-box-tool" style="font-size: 16px;" data-widget="collapse"><i
class="fa fa-plus"></i>
</button>
</div>
<!-- /.box-tools -->
</div>
<!-- /.box-header -->
<div class="box-body" style="display: block">
<form action="<?= site_url('reports/stockReport') ?>" method="post">
<div class="row">
<div class="col-md-3">
<div class="form-group"><label>Item</label>
<select name="item" id="item" class="form-control select2">
<option value="">Select Item</option>
<?php
foreach ($item as $row){
?>
<option value="<?=$row->item_id?>"><?=$row->item_name?></option>
<?php
}
?>
</select>
</div>
</div>
<div class="col-md-3">
<div class="form-group"><label>Item Sub Type</label>
<select name="subType" id="subType" class="form-control select2">
<option value="">Select Item Sub Type</option>
<?php
foreach ($subType as $row){
?>
<option value="<?=$row->sub_type_id?>"><?=$row->sub_type_name?></option>
<?php
}
?>
</select>
</div>
</div>
</div>
<div class="row">
<div class="col-md-3">
<div class="form-group">
<input type="submit" value="Search"
class="btn btn-primary btn-lg">
</div>
</div>
</div>
</form>
</div>
<!-- /.box-body -->
</div>
<div class="box-body">
<!--<div class="row">
<div class="col-md-4">
<a href="$"><button type="button" class="btn btn-default">Print</button></a>
</div>
</div>-->
<div class="row">
<div class="col-md-12 pull-right">
<button class="btn btn-default" onclick="printEl('datatable','Item List')">
<i class="fa fa-print "></i> Print
</button>
</div>
</div>
<div class="table-responsive" id="datatable">
<table id="ExData" cellpadding="0" cellspacing="0" border="0"
class="table table-bordered table-condensed table-hover table-striped reports-table">
<thead id="th">
<tr class="" style="background-color: #ff3399 !important;">
<th >In Num</th>
<th >Item ID</th>
<th >Item Name</th>
<th >Item Sub Type</th>
<th class="text-center">p_qty</th>
<th class="text-center">is_qty </th>
<th class="text-center">balance </th>
</tr>
</thead>
<tbody>
<?php
$c = 0;
$totalPurchase=0;
$totalIssues=0;
if(!empty($summary)) {
foreach ($summary as $row) {
$totalPurchase = $totalPurchase + $row->purchase;
$totalIssues = $totalIssues + $row->issues;
$totalBalance += $row->unit_price*($row->purchase - (-1) * $row->issues);
$c++;
?>
<tr>
<td><?= $c ?></td>
<td><?= $row->item_id ?></td>
<td><?= $row->item_name ?></td>
<td><?= $row->sub_type_id ?></td>
<td class="text-right"><?= $row->purchase+0 ?></td>
<td class="text-right"><?= ((-1) * $row->issues)+0 ?></td>
<td class="text-right"><?= $row->purchase - (-1) * $row->issues ?></td>
</tr>
<?php
}
?>
</tr>
<?php
}else{
echo "<tr><td colspan='6'>No Data Available</td></tr>";
}
?>
</tbody>
</table>
</div>
</div>
</div>
<!--end of add-->
<script>
function printEl($el, title, $json, $th, $pdf) {
var table = "";
if ($json) {
table += '<table id="SLData" class="table table-bordered table-hover table-striped">' +
'<thead><tr>';
$($th).each(function (i, el) {
table += $th[i];
});
table += "</tr></thead><tbody>";
$($json).each(function (i, el) {
table += '<tr>';
$(el).each(function (j, el2) {
table += '<td>' + el[j] + '</td>';
});
table += '</tr>';
});
table += "</tbody></table>";
}
var divToPrint = (table) ? table : document.getElementById($el).innerHTML;
var head = $('head').html(),
title = (title) ? '<h3 style="background-color: #e0ebff; font-size:20px;padding: 5px;color: #191919; font-family: 'Ubuntu', sans-serif !important;font-weight: bold;" align="center"><img src="<?php echo base_url(); ?>public/dist/img/logo-mini.png">' + 'Item list' + '</h3>' : '';
if (!$pdf) {
var printWin = window.open('', 'Print-Window');
printWin.document.open();
printWin.document.write('<html>' + head + '<body style="background: #ffffff;" onload="window.print();">' + title + divToPrint + '</body></html>');
printWin.document.close();
setTimeout(function () {
printWin.close();
// $("#INV_datatable th:last-child, #INV_datatable td:last-child").show();
}, 500);
}
}
</script>
以上表格&代码正确生成以下输出
+---------+----------------+---------------+-------+--------+---------+
| item_id | item_name | item_sub_type | p_qty | is_qty | balance |
+---------+----------------+---------------+-------+--------+---------+
| 1 | A4 | Stationary | 30 | 15 | 15 |
| 2 | A5 | Stationary | 5 | 0 | 5 |
| 3 | CFL Bulb 15W | Consumable | 100 | 10 | 90 |
| 4 | Toilet Cleaner | Consumable | 15 | 2 | 13 |
+---------+----------------+---------------+-------+--------+---------+
然后我需要根据item_name&item_sub_type如下
+---------+-----------+---------------+-------+--------+---------+
| item_id | item_name | item_sub_type | p_qty | is_qty | balance |
+---------+-----------+---------------+-------+--------+---------+
| 1 | A4 | Stationary | 30 | 15 | 15 |
| 2 | A5 | Stationary | 5 | 0 | 5 |
+---------+-----------+---------------+-------+--------+---------+
+---------+----------------+---------------+-------+--------+---------+
| item_id | item_name | item_sub_type | p_qty | is_qty | balance |
+---------+----------------+---------------+-------+--------+---------+
| 3 | CFL Bulb 15W | Consumable | 100 | 10 | 90 |
| 4 | Toilet Cleaner | Consumable | 15 | 2 | 13 |
+---------+----------------+---------------+-------+--------+---------+
但是筛选器(item_name&item_sub_type(没有正确筛选记录。我想我把过滤器的代码做得很好。可能出了什么问题？
有人能帮我吗？
您已经设置$where=NULL两次，因此它删除了where子句中的第一个条件，
只要删除第二个$where＝NULL，就可以了。
public function stockReport()
{
$bc = array(array('link' => '#', 'page' => 'Stationary Stock Report'));
$meta = array('page_title' => 'Stationary Stock Report', 'bc' => $bc);
$where = NULL; // 1st time
if ($this->input->post('item')) {
$item = $this->input->post('item');
$where .= " AND store_item.item_id = '$item' ";
} else {
$item = NULL;
}
// $where = NULL; // remove this, as you are reseting $where to NULL, so 1st condition is gone.
if ($this->input->post('subType')) {
$subType = $this->input->post('subType');
$where .= " AND store_sub_type.sub_type_id = '$subType' ";
} else {
$subType = NULL;
}
$this->data['item'] = $this->Item_Model->getItem();
$this->data['subType'] = $this->Item_Model->getSubType();
$this->data['summary'] = $this->Report_model->stockSummary($where);
$this->render('reports/stockReport', $meta, $this->data);
}