public function edit($id)
{
$this->data['styles']['href'] = array('components/jquery-ui/jquery-ui.css');
$this->data['scripts']['src'] = array('components/jquery-ui/jquery-ui.js');
$this->load->helper('input');
if (!empty($id)) {
$this->record = $id;
$this->data['title'] = 'Edit Payment';
} else {
$this->data['title'] = 'Create Payment';
}
if ($this->uri->segment(2) == 'edit') {
$depot_payment_info = $this->query_model->get('depot_payment', $id);
foreach ($depot_payment_info as $key => $value) {
if ($key == 'date' || $key == 'confirm_date') {
$this->data[$key] = convertYMDtoDMY('-', $value);
} else {
$this->data[$key] = $value;
}
}
}
if ($this->uri->segment('2') == 'create') {
$this->data['depot_id'] = $id;
}
if ($this->uri->segment('2') == 'edit') {
$this->data['record'] = $id;
}
$this->load->render('payment/edit', $this->data, FALSE);
}
public function afterRetrieve($data)
{
$field_vardefs = $this->vardefs['fields'];
$db_data = $data;
if ($db_data) {
foreach ($db_data as $db_key => $db_val) {
if (array_key_exists($db_key, $field_vardefs)) {
if (array_key_exists('type', $field_vardefs[$db_key])) {
if ($field_vardefs[$db_key]['type'] == 'date' || $field_vardefs[$db_key]['type'] == 'datetime') {
$data->{$db_key} = convertYMDtoDMY('-', $db_val);
}
}
}
}
}
return $data;
}
public function getCustomerToDepotPaymentReport($user_id, $filter_user_id, $start_date, $end_date, $limit = '', $start = '', $search_text = '', $count = '', $filter_data = array())
{
$payment_data = array();
$this->db->select('IFNULL(SUM(IFNULL((srip.order_quantity * srip.price),0)) - IFNULL(previousPayment.previousAmount, 0), 0) As due_balance', FALSE);
$this->db->from($this->retailer_invoice_product_table . ' As srip');
$this->db->join("( SELECT srp.depot_id, srp.retailer_id, SUM(srp.amount) AS previousAmount FROM {PRE}retailer_payment AS srp \n\t\t\t\tWHERE\n\t\t\t\tsrp.depot_id = " . $filter_user_id . " AND\n\t\t\t\tsrp.retailer_id = " . $user_id . " AND \n\t\t\t\tsrp.confirm_date < '" . $start_date . "' AND\n\t\t\t\tsrp.status = 'Done' AND\n\t\t\t\tsrp.deleted='0' \n\t\t\t\tGROUP BY srp.depot_id\n\t\t\t) AS previousPayment", "srip.created_by = previousPayment.depot_id AND srip.retailer_id = previousPayment.retailer_id", 'left', FALSE);
$this->db->where('srip.created_by', $filter_user_id);
$this->db->where('srip.retailer_id', $user_id);
$this->db->where('srip.date_confirm <', $start_date);
$this->db->where('srip.deleted', '0');
$this->db->group_by('srip.retailer_id');
$result = $this->db->get();
//echo $this->db->last_query();
//die();
if ($result->num_rows() == 1) {
$due_balance = $result->row()->due_balance;
$payment_data[0]['details'] = 'Op. Due Balance';
$payment_data[0]['due_balance'] = $due_balance;
} else {
$due_balance = 0;
$payment_data[0]['details'] = 'Op. Due Balance';
$payment_data[0]['due_balance'] = $due_balance;
}
$query = "\n\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\tsrip.retailer_invoice_id, \n\t\t\t\tDATE_FORMAT(srip.date_confirm, '%Y-%m-%d') as date,\n\t\t\t\tSUM(IFNULL((srip.order_quantity * srip.price),0)) As amount,\n \t\t\t'' As method,\n \t\t\t'' As receipt_no,\n \t\t\t'' As cheque_no,\n \t\t\t'' As transfer_id\n \t\t\tFROM {PRE}retailer_invoice_product AS srip \n\t\t\t\tWHERE\n\t\t\t\tsrip.created_by = " . $filter_user_id . " AND \n\t\t\t\tsrip.retailer_id = " . $user_id . " AND \n\t\t\t\tDATE(srip.date_confirm) BETWEEN '" . $start_date . "' AND '" . $end_date . "'\n\t\t\t\tAND srip.deleted='0'\n\t\t\t\tGROUP BY retailer_invoice_id\n\t\t\t)\n\t\t\tUNION ALL\n\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t'' As retailer_invoice_id,\n\t\t\t\tDATE_FORMAT(srp.confirm_date, '%Y-%m-%d') as date,\n\t\t\t\tsrp.amount As amount,\n\t\t\t\tsrp.method,\n\t\t\t\tsrp.receipt_no,\n\t\t\t\tsrp.cheque_no,\n\t\t\t\tsrp.transfer_id\n\t\t\t\tFROM {PRE}retailer_payment AS srp \n\t\t\t\tWHERE\n\t\t\t\tsrp.depot_id = " . $filter_user_id . " AND\n\t\t\t\tsrp.retailer_id = " . $user_id . " AND \n\t\t\t\tDATE(srp.confirm_date) BETWEEN '" . $start_date . "' AND '" . $end_date . "' AND\n\t\t\t\tsrp.status = 'Done' AND\n\t\t\t\tsrp.deleted='0'\n\t\t\t)\n\t\t\tORDER BY date ASC\n\t\t\t";
$result = $this->db->query($query);
//echo $this->db->last_query();
//die();
if ($result->num_rows() > 0) {
$res = $result->result();
if ($res) {
$i = 1;
foreach ($res as $row) {
$retailer_invoice_id = $row->retailer_invoice_id;
$date = $row->date;
$amount = $row->amount;
$method = $row->method;
if (!empty($retailer_invoice_id)) {
$details = anchor('invoice/view/' . $retailer_invoice_id . "?view=customer", "Invoice No." . $retailer_invoice_id, array('target' => '_blank'));
$payment_data[$i]['date'] = convertYMDtoDMY('-', $date);
$payment_data[$i]['details'] = $details;
$payment_data[$i]['debit'] = $amount;
$due_balance = $due_balance + $amount;
$payment_data[$i]['due_balance'] = $due_balance;
} else {
if (!empty($method)) {
$details = "Payment By " . $method;
if ($method == 'Cash') {
$details .= " <br/> Receipt No. " . $row->receipt_no;
} else {
if ($method == 'Cheque') {
$details .= " <br/> Cheque No. " . $row->cheque_no;
} else {
if ($method == 'NEFT') {
$details .= " <br/> Transfer ID. " . $row->transfer_id;
}
}
}
$payment_data[$i]['date'] = convertYMDtoDMY('-', $date);
$payment_data[$i]['details'] = $details;
$payment_data[$i]['credit'] = $amount;
$due_balance = $due_balance - $amount;
$payment_data[$i]['due_balance'] = $due_balance;
}
}
$i++;
}
}
}
return $payment_data;
}
$total_vatrs = 0;
$total_amount = 0;
$sr_no = 1;
foreach($invoice_product_info as $invoice_product)
{
if($invoice_product->id)
{
$total_vatrs += $invoice_product->vatrs;
$total_amount += $invoice_product->sub_total;
?>
<tr>
<td align="center"><?= $sr_no; ?></td>
<td align="center"><?= $invoice_product->name . ' ' . $invoice_product->packing_size . ' ' . $invoice_product->unit; ?></td>
<td align="center"><?= $invoice_product->batch_no; ?></td>
<td align="center"><?= convertYMDtoDMY("-", $invoice_product->expiry_date); ?></td>
<td align="center"><?= $invoice_product->order_quantity; ?></td>
<td align="center"><?= $invoice_product->price; ?></td>
<td align="center"><?= $invoice_product->mrp; ?></td>
<td align="center"><?= $invoice_product->vat == '' ? '0%' : $invoice_product->vat .' %'; ?></td>
<td align="right"><?= $invoice_product->vatrs; ?></td>
<td align="right"><?= $invoice_product->sub_total; ?></td>
</tr>
<?php
$sr_no++;
}
}
}
?>
<tr style="font-weight: bold;">
