如何根据vocuher编号显示条目

km0tfn4u  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(317)

预期结果

purchaseitem表结构

purchasebill表结构

电流输出

我正在准备登记表,根据日期从用户输入起始日期和截止日期,它必须根据billno打印分录…我使用两个查询来显示凭证编号,帐单日期和参与方名称,以及另一个查询项目名称数量。query1工作正常,我有一个查询变量的问题,它从用户提供的“从日期到日期”打印数据库中的所有条目,但我必须根据凭证号打印“从日期到日期”。请帮助我解决此问题。。。控制器代码:

$this->db->where('billdate >=', $newDate);
    $this->db->where('billdate <=', $newDate2);
    $this->db->from('purchaseitem');
        $this->db->order_by("vno", "asc");
        $this->db->join('purchasebill', 'purchasebill.no = purchaseitem.billno','left outer');
        $this->db->join('itemmaster','itemmaster.itcode = purchaseitem.Product_Code','left outer');
        $query = $this->db->get('')->result_array();
        $data['query'] = $query;
        $this->db->where('date >=', $newDate);
        $this->db->where('date <=', $newDate2);
        $this->db->select();
        $this->db->from('purchasebill');
        $this->db->order_by('voucherno');
        $this->db->group_by('voucherno');
        $this->db->join('parmaster','parmaster.Pcode = purchasebill.partyname','left outer');
        $query = $this->db->get('')->result_array();
        $data['query1'] = $query;
        $this->load->view('Receipt_View1',$data);

视图代码:

<?php $rowcount = 1 ?>                          
        <?php foreach ($query1 as $row): ?>

<tr>
                                            <td><?=$rowcount;?></td>
                                            <td><?=$row['voucherno'];?></td>
                                            <td><?=$row['date'];?></td>
                                            <td><?=$row['PName'];?></td>
                                            <?php $rowcount +=1?>
                                            <?php foreach ($query as $row): ?>
                                                <tr>
                                            <td><?=$row['Prdtname'];?></td>
                                            <td><?=$row['Qty'];?></td>
                                            <td><?=$row['bundle'];?></td>

                                            <?php endforeach ?> 
                                            <?php endforeach ?> 
</tr></tr>
ni65a41a

ni65a41a1#

这可以通过使用阵列控制器代码上的凭证号来完成:

public function Purchase_Register1()
{
    $startdate = $this->input->post('SDate');
$enddate = $this->input->post('EDate');
$date = str_replace('/', '-', $startdate);
$newDate = date("Y-m-d", strtotime($date));
$date2 = str_replace('/', '-', $enddate);
$newDate2 = date("Y-m-d", strtotime($date2));
$data['startdate'] = $startdate;
$data['enddate'] = $enddate;
if( ($this->input->post('all')) && ($this->input->post('item')) ) {
    $this->db->select('parmaster.PName, purchasebill.voucherno, purchaseitem.billdate, purchasebill.partyname, purchaseitem.prdtname, purchaseitem.quantity,purchaseitem.bundle');
    $this->db->from('purchasebill');
    $this->db->join('purchaseitem', 'purchasebill.voucherno = purchaseitem.vno');
    $this->db->join('parmaster', 'parmaster.pcode = purchasebill.partyname');
    $this->db->where('purchaseitem.billdate >=', $newDate);
    $this->db->where('purchaseitem.billdate <=', $newDate2);
    $this->db->order_by('purchasebill.voucherno');

    $result = $this->db->get()->result_array();
    if($result){
        $finalArray = array();
        $checkArray = array();
        foreach ($result as $key => $value) {
            if(in_array($value['voucherno'], $checkArray)){
                $finalArray[$value['voucherno']][] = $value;
            } else {
                $checkArray[] = $value['voucherno'];
                $finalArray[$value['voucherno']][] = $value;
            }
        }
        $data['query'] = $finalArray;
        $this->load->view('Whole_Sales/Purchase_Register2', $data);
        //echo '<pre>';print_r($finalArray);echo '</pre>';exit();
    } 
}else if( ($this->input->post('selected')) && ($this->input->post('businessType')) && ($this->input->post('item')) ) {
    $name = $this->input->post('businessType');
    $this->db->select('parmaster.PName, purchasebill.voucherno, purchaseitem.billdate, purchasebill.partyname, purchaseitem.prdtname, purchaseitem.quantity,purchaseitem.bundle');
    $this->db->from('purchasebill');
    $this->db->join('purchaseitem', 'purchasebill.voucherno = purchaseitem.vno');
    $this->db->join('parmaster', 'parmaster.pcode = purchasebill.partyname');
    $this->db->where('purchaseitem.billdate >=', $newDate);
    $this->db->where('purchaseitem.billdate <=', $newDate2);
    $this->db->where('PName',$name);
    $this->db->order_by('purchasebill.voucherno');
    $result = $this->db->get()->result_array();
    if($result){
        $finalArray = array();
        $checkArray = array();
        foreach ($result as $key => $value) {
            if(in_array($value['voucherno'], $checkArray)){
                $finalArray[$value['voucherno']][] = $value;
            } else {
                $checkArray[] = $value['voucherno'];
                $finalArray[$value['voucherno']][] = $value;
            }
        }
        $data['query'] = $finalArray;
        $this->load->view('Whole_Sales/Purchase_Register2', $data);

查看页面:

<?php
    $totalQuantity = 0;
    $totalBundle = 0;
     $rowcount = 1;
    foreach ($query as $key => $value){
        $counter = 0;
?>
        <?php
        foreach ($value as $sub_key => $sub_value) {
            if($counter == 0) {
                $totalQuantity = $totalQuantity + $sub_value['quantity'];
                $totalBundle = $totalBundle + $sub_value['bundle'];
            ?>
                <tr>
                    <td><?php echo $rowcount?>
                    <td><?php echo $sub_value['voucherno'] ?></td>
                    <td><?php echo $sub_value['billdate'] ?></td>
                    <td><?php echo $sub_value['PName'] ?></td>
                    <td><?php echo $sub_value['prdtname'];?></td>
                    <td><?php echo $sub_value['quantity'];?></td>
                    <td><?php echo $sub_value['bundle'];?></td>
                    <?php $rowcount +=1?>
                </tr>
            <?php
            $counter++;
            } else {
                $totalQuantity = $totalQuantity + $sub_value['quantity'];
                $totalBundle = $totalBundle + $sub_value['bundle'];
        ?>
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td><?php echo $sub_value['prdtname'];?></td>
            <td><?php echo $sub_value['quantity'];?></td>
            <td><?php echo $sub_value['bundle'];?></td>
        </tr>
        <?php
            } // else
        } // foreach
    }
    ?>

相关问题