501 lines
18 KiB
PHP
501 lines
18 KiB
PHP
<?php
|
|
if (!defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
/*
|
|
* InvoicePlane
|
|
*
|
|
* @author InvoicePlane Developers & Contributors
|
|
* @copyright Copyright (c) 2012 - 2017 InvoicePlane.com
|
|
* @license https://invoiceplane.com/license.txt
|
|
* @link https://invoiceplane.com
|
|
*/
|
|
|
|
/**
|
|
* Class Mdl_Items
|
|
*/
|
|
class Mdl_Items extends Response_Model
|
|
{
|
|
public $table = 'ip_invoice_items';
|
|
public $primary_key = 'ip_invoice_items.item_id';
|
|
public $date_created_field = 'item_date_added';
|
|
|
|
public function today()
|
|
{
|
|
$sql="SELECT (SELECT count(distinct invoice_id) FROM ip_invoices where invoice_status_id != 1 and date(invoice_date_created) = CURRENT_DATE and creditinvoice_parent_id is null) as today,ifnull(sum(it.item_quantity),0) as sum_of_quan,ifnull(sum(if(it.item_quantity=0,1,it.item_quantity) * it.item_price),0) as sum_of_today FROM ip_invoice_items it
|
|
join ip_invoices inv on inv.invoice_id = it.invoice_id
|
|
where inv.invoice_status_id != 1 and date(invoice_date_created) = CURRENT_DATE;";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
public function month()
|
|
{
|
|
|
|
|
|
|
|
if (date('m') >= 4) {
|
|
$yearl = date('Y').'-'.(date('Y')+1);
|
|
} else {
|
|
$yearl = (date('Y')-1).'-'.date('Y');
|
|
}
|
|
$ab=$yearl;
|
|
$fa=substr($ab,0,-5);
|
|
$aa=substr($ab,5,5);
|
|
|
|
$sql="SELECT (SELECT count(distinct inv.invoice_id) FROM ip_invoices inv
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and MONTH(inv.invoice_date_created) = MONTH(CURRENT_DATE()) and
|
|
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31') and creditinvoice_parent_id is null) as months,ifnull(sum(it.item_quantity),0) as sum_of_quantity,ifnull(sum(if(it.item_quantity=0,1,it.item_quantity) * it.item_price),0) as sum_of_month FROM ip_invoice_items it
|
|
join ip_invoices inv on inv.invoice_id = it.invoice_id
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and MONTH(inv.invoice_date_created) = MONTH(CURRENT_DATE()) and
|
|
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31')";
|
|
|
|
|
|
|
|
|
|
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
public function year()
|
|
{
|
|
if (date('m') >= 4) {
|
|
$yearl = date('Y').'-'.(date('Y')+1);
|
|
} else {
|
|
$yearl = (date('Y')-1).'-'.date('Y');
|
|
}
|
|
$ab=$yearl;
|
|
$fa=substr($ab,0,-5);
|
|
$aa=substr($ab,5,5);
|
|
$sql="SELECT (SELECT count(distinct inv.invoice_id) FROM ip_invoices inv
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and
|
|
(inv.invoice_date_created >= '".$fa."-04-01' and inv.invoice_date_created <= '".$aa."-03-31') and creditinvoice_parent_id is null) as year,ifnull(sum(iit.item_quantity),0) as sum_of_quantity,ifnull(sum(if(iit.item_quantity=0,1,iit.item_quantity) * iit.item_price),0) as sum_of_year
|
|
FROM ip_invoice_items iit
|
|
join ip_invoices ii on ii.invoice_id = iit.invoice_id
|
|
join ip_invoice_custom icf on icf.invoice_id = ii.invoice_id
|
|
where ii.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and (ii.invoice_date_created >= '".$fa."-04-01' and ii.invoice_date_created <= '".$aa."-03-31');";
|
|
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
|
|
public function vehicle()
|
|
{
|
|
$sql="select iit.invoice_id as invoice_id,iic.invoice_custom_fieldvalue as invoice_custom_fieldvalue from ip_invoice_custom iic
|
|
join ip_invoice_items iit on iit.invoice_id = iic.invoice_id where invoice_custom_fieldid = '8'";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
public function item_name()
|
|
{
|
|
$sql="SELECT product_description as item_name FROM ip_products";
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
// public function year_wise($a,$b)
|
|
// {
|
|
// $sql="SELECT inv.invoice_date_created as months,sum(it.item_quantity) as quantity,sum(it.item_quantity * it.item_price) as value FROM ip_invoice_items it
|
|
// join ip_invoices inv on inv.invoice_id = it.invoice_id where inv.invoice_status_id != 1 and (YEAR(inv.invoice_date_created) = ? and MONTH(inv.invoice_date_created) >= 4 ) or ( YEAR(inv.invoice_date_created) = ? and MONTH(inv.invoice_date_created) <= 3 )
|
|
// group by month(months)
|
|
// ORDER BY month(months);";
|
|
// $query = $this->db->query($sql,array($a,$b));
|
|
// return $query->result();
|
|
|
|
// }
|
|
|
|
|
|
public function year_wise($a,$b)
|
|
{
|
|
$sql="SELECT inv.invoice_date_created as months,sum(it.item_quantity) as quantity,sum(it.item_quantity * it.item_price) as value FROM ip_invoice_items it
|
|
join ip_invoices inv on inv.invoice_id = it.invoice_id
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8'and
|
|
(inv.invoice_date_created) >= '$a-04-01' and date(inv.invoice_date_created) <= '$b-03-31'
|
|
group by month(months)
|
|
ORDER BY month(months);";
|
|
//echo $sql;
|
|
$query = $this->db->query($sql);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function month_wise($new)
|
|
{
|
|
//echo $new;
|
|
$month= date("m",strtotime($new));
|
|
$year = date("Y",strtotime($new));
|
|
//echo $month;//
|
|
//echo $year;die();
|
|
$sql="select clt.client_id as client_id,clt.client_name as client_name,ip.product_description as product_name,sum(item_quantity) as quantity,sum(item_quantity * item_price) as value from ip_clients clt
|
|
join ip_invoices inv on inv.client_id = clt.client_id
|
|
join ip_invoice_items items on items.invoice_id = inv.invoice_id
|
|
join ip_products ip on ip.product_id = items.item_product_id
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and month(inv.invoice_date_created) = ? and year(inv.invoice_date_created) = ?
|
|
group by client_name,product_name";
|
|
|
|
|
|
$query = $this->db->query($sql,array($month,$year));
|
|
//print_r($query->result());
|
|
// echo $sql;
|
|
return $query->result();
|
|
|
|
}
|
|
public function year_wise_total($a,$b)
|
|
{
|
|
|
|
$sql="select clt.client_id as client_id,clt.client_name as client_name,ip.product_description as product_name,sum(item_quantity) as quantity,sum(item_quantity * item_price) as value
|
|
from ip_clients clt
|
|
join ip_invoices inv on inv.client_id = clt.client_id
|
|
join ip_invoice_items items on items.invoice_id = inv.invoice_id
|
|
join ip_invoice_custom icf on icf.invoice_id = inv.invoice_id
|
|
join ip_products ip on ip.product_id = items.item_product_id
|
|
where inv.invoice_status_id != 1 and icf.invoice_custom_fieldid = '8' and (inv.invoice_date_created) >= '".$a."-04-01' and date(inv.invoice_date_created) <= '".$b."-03-31' group by client_name,product_name";
|
|
$query = $this->db->query($sql);
|
|
//echo $sql;
|
|
//print_r($a);
|
|
return $query->result();
|
|
|
|
}
|
|
public function fiscalYear()
|
|
{
|
|
$sql="SELECT
|
|
CASE WHEN MONTH(inv.invoice_date_created)>=4 THEN
|
|
concat(YEAR(inv.invoice_date_created), '-',YEAR(inv.invoice_date_created)+1)
|
|
ELSE concat(YEAR(inv.invoice_date_created)-1,'-', YEAR(inv.invoice_date_created)) END AS financial_year,
|
|
SUM(it.item_quantity)
|
|
FROM ip_invoice_items it
|
|
join ip_invoices inv on inv.invoice_id = it.invoice_id
|
|
where inv.invoice_status_id != 1
|
|
GROUP BY financial_year ";
|
|
$query = $this->db->query($sql);
|
|
//print_r($query->result());
|
|
return $query->result();
|
|
}
|
|
public function selectmonth($a,$b)
|
|
{
|
|
$sql="SELECT
|
|
monthname(invoice_date_created) as months
|
|
FROM ip_invoices
|
|
where case
|
|
when (month(invoice_date_created) >=4) then
|
|
(YEAR(invoice_date_created) = $a and MONTH(invoice_date_created) >= 4 ) or ( YEAR( invoice_date_created) = $b+1 and MONTH(invoice_date_created) <= 3 )
|
|
else
|
|
(YEAR(invoice_date_created) = $a-1 and MONTH(invoice_date_created) >= 4 ) or ( YEAR(invoice_date_created) = $b and MONTH(invoice_date_created) <= 3 )
|
|
end
|
|
GROUP BY months
|
|
ORDER BY FIELD(months,
|
|
'December', 'November', 'October',
|
|
'September', 'August', 'July',
|
|
'June', 'May', 'April',
|
|
'March', 'February', 'January') DESC, months ASC";
|
|
$query = $this->db->query($sql,array($a,$b));
|
|
|
|
return $query->result();
|
|
}
|
|
public function customer()
|
|
{
|
|
|
|
$sql="SELECT client_name,client_id FROM ip_clients
|
|
order by client_name;";
|
|
|
|
$query = $this->db->query($sql);
|
|
//print_r($a);
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
|
|
public function default_select()
|
|
{
|
|
$this->db->select('ip_invoice_item_amounts.*, ip_products.*, ip_invoice_items.*,
|
|
Sgst_item_tax_rates.tax_rate_percent AS Sgst_item_tax_rate_percent,
|
|
Sgst_item_tax_rates.tax_rate_name AS Sgst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Sgst_item_tax_rates.tax_rate_percent /100) AS Sgst_item_tax_rate_amount,Cgst_item_tax_rates.tax_rate_percent AS Cgst_item_tax_rate_percent,
|
|
Cgst_item_tax_rates.tax_rate_name AS Cgst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Cgst_item_tax_rates.tax_rate_percent /100) AS Cgst_item_tax_rate_amount,Igst_item_tax_rates.tax_rate_percent AS Igst_item_tax_rate_percent,
|
|
Igst_item_tax_rates.tax_rate_name AS Igst_item_tax_rate_name,(((ip_invoice_items.item_quantity * ip_invoice_items.item_price) - (ip_invoice_items.item_quantity * ip_invoice_items.item_discount_amount)) * Igst_item_tax_rates.tax_rate_percent /100) AS Igst_item_tax_rate_amount');
|
|
}
|
|
|
|
public function default_order_by()
|
|
{
|
|
$this->db->order_by('ip_invoice_items.item_order');
|
|
}
|
|
|
|
public function default_join()
|
|
{
|
|
$this->db->join('ip_invoice_item_amounts', 'ip_invoice_item_amounts.item_id = ip_invoice_items.item_id', 'left');
|
|
$this->db->join('ip_tax_rates AS Sgst_item_tax_rates', 'Sgst_item_tax_rates.tax_rate_id = ip_invoice_items.Sgst_item_tax_rate_id', 'left');
|
|
$this->db->join('ip_tax_rates AS Cgst_item_tax_rates', 'Cgst_item_tax_rates.tax_rate_id = ip_invoice_items.Cgst_item_tax_rate_id', 'left');
|
|
$this->db->join('ip_tax_rates AS Igst_item_tax_rates', 'Igst_item_tax_rates.tax_rate_id = ip_invoice_items.Igst_item_tax_rate_id', 'left');
|
|
$this->db->join('ip_products', 'ip_products.product_id = ip_invoice_items.item_product_id', 'left');
|
|
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*/
|
|
public function validation_rules()
|
|
{
|
|
return array(
|
|
'invoice_id' => array(
|
|
'field' => 'invoice_id',
|
|
'label' => trans('invoice'),
|
|
'rules' => 'required'
|
|
),
|
|
'item_sku' => array(
|
|
'field' => 'item_sku',
|
|
'label' => trans('item_sku'),
|
|
'rules' => 'required|unique'
|
|
),
|
|
'item_name' => array(
|
|
'field' => 'item_name',
|
|
'label' => trans('item_name'),
|
|
'rules' => 'required'
|
|
),
|
|
'item_description' => array(
|
|
'field' => 'item_description',
|
|
'label' => trans('description')
|
|
),
|
|
'item_quantity' => array(
|
|
'field' => 'item_quantity',
|
|
'label' => trans('quantity'),
|
|
'rules' => 'required'
|
|
),
|
|
'item_price' => array(
|
|
'field' => 'item_price',
|
|
'label' => trans('price'),
|
|
'rules' => 'required'
|
|
),
|
|
'item_tax_rate_id' => array(
|
|
'field' => 'item_tax_rate_id',
|
|
'label' => trans('item_tax_rate')
|
|
),
|
|
'item_product_id' => array(
|
|
'field' => 'item_product_id',
|
|
'label' => trans('original_product')
|
|
),
|
|
'item_date' => array(
|
|
'field' => 'item_date',
|
|
'label' => trans('item_date')
|
|
)
|
|
);
|
|
}
|
|
|
|
|
|
function get_product()
|
|
{
|
|
|
|
//$this->db->select('product_id,product_description');
|
|
|
|
$this->db->join('ip_units unit','unit.unit_id = ip_products.unit_id');
|
|
$this->db->from('ip_products');
|
|
$query = $this->db->get();
|
|
//print_r($query->result());
|
|
|
|
// print_r( $this->db->last_query());
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
function get_quotes($client_id)
|
|
{
|
|
|
|
$this->db->select('ip_quotes.quote_id,quote_number,quote_pono');
|
|
// $this->db->join('ip_units unit','unit.unit_id = ip_products.unit_id');
|
|
$this->db->from('ip_quotes');
|
|
$this->db->join('ip_quote_items','ip_quote_items.quote_id = ip_quotes.quote_id');
|
|
$this->db->where('client_id ',$client_id);
|
|
$this->db->where('item_status ',1);
|
|
$query = $this->db->get();
|
|
// print_r( $this->db->last_query());
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
// function get_quoteproduct($quote_id)
|
|
// {
|
|
|
|
// $this->db->select('item_name,item_description,item_quantity,item_balancequantity,item_suppliedquantity,item_price,unit_id,Sgst_item_tax_rate_id,Cgst_item_tax_rate_id,Igst_item_tax_rate_id,product_id,item_product_id');
|
|
// $this->db->from('ip_quote_items');
|
|
// $this->db->join('ip_quotes','ip_quote_items.quote_id = ip_quotes.quote_id');
|
|
// $this->db->join('ip_products','ip_quote_items.item_product_id = ip_products.product_id');
|
|
// $this->db->where('ip_quote_items.quote_id =', $quote_id);
|
|
// $query = $this->db->get();
|
|
// return $query->result();
|
|
// }
|
|
|
|
|
|
|
|
|
|
function get_quoteproduct($quote_id)
|
|
{
|
|
|
|
$this->db->select('item_name,item_description,item_quantity,item_balancequantity,item_suppliedquantity,item_price,unit_id,Sgst_item_tax_rate_id,Cgst_item_tax_rate_id,Igst_item_tax_rate_id,product_id,item_product_id,quote_pono,po_date_created');
|
|
$this->db->from('ip_quote_items');
|
|
$this->db->join('ip_quotes','ip_quote_items.quote_id = ip_quotes.quote_id');
|
|
$this->db->join('ip_products','ip_quote_items.item_product_id = ip_products.product_id');
|
|
$this->db->where('ip_quote_items.quote_id =', $quote_id);
|
|
$query = $this->db->get();
|
|
return $query->result();
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
function get_tax()
|
|
{
|
|
|
|
$this->db->select('*');
|
|
$this->db->from('ip_tax_rates');
|
|
$query = $this->db->get();
|
|
//print_r($query->result());
|
|
//print_r( $this->db->last_query());
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
/**
|
|
* @param null $id
|
|
* @param null $db_array
|
|
* @return int|null
|
|
*/
|
|
public function save($id = null, $db_array = null)
|
|
{
|
|
|
|
$id = parent::save($id, $db_array);
|
|
|
|
// echo $id;
|
|
// die();
|
|
|
|
$this->load->model('invoices/mdl_item_amounts');
|
|
|
|
$this->mdl_item_amounts->calculate($id);
|
|
|
|
$this->load->model('invoices/mdl_invoice_amounts');
|
|
|
|
if (is_object($db_array) && isset($db_array->invoice_id)) {
|
|
$this->mdl_invoice_amounts->calculate($db_array->invoice_id);
|
|
} elseif (is_array($db_array) && isset($db_array['invoice_id'])) {
|
|
$this->mdl_invoice_amounts->calculate($db_array['invoice_id']);
|
|
}
|
|
|
|
return $id;
|
|
}
|
|
|
|
/**
|
|
* @param int $item_id
|
|
*/
|
|
public function delete($item_id)
|
|
{
|
|
// Get item:
|
|
// the invoice id is needed to recalculate invoice amounts
|
|
// and the task id to update status if the item refers a task
|
|
$query = $this->db->get_where($this->table,
|
|
array('item_id' => $item_id));
|
|
if ($query->num_rows() == 0) {
|
|
return null;
|
|
}
|
|
|
|
$row = $query->row();
|
|
$invoice_id = $row->invoice_id;
|
|
|
|
// Delete the item
|
|
parent::delete($item_id);
|
|
|
|
// Delete the item amounts
|
|
$this->db->where('item_id', $item_id);
|
|
$this->db->delete('ip_invoice_item_amounts');
|
|
|
|
// Recalculate invoice amounts
|
|
$this->load->model('invoices/mdl_invoice_amounts');
|
|
$this->mdl_invoice_amounts->calculate($invoice_id);
|
|
return $row;
|
|
}
|
|
|
|
|
|
public function Check_invoice($invoice_id,$item_id)
|
|
{
|
|
$this->db->select('*');
|
|
$this->db->from('ip_invoice_items');
|
|
|
|
$this->db->where('item_product_id',$item_id);
|
|
$this->db->where('invoice_id',$invoice_id);
|
|
|
|
|
|
|
|
$query = $this->db->get();
|
|
//print_r( $this->db->last_query());
|
|
return $query->result();
|
|
}
|
|
|
|
|
|
|
|
// public function get_invoice_custom_values($source_id)
|
|
// {
|
|
|
|
// //echo $source_id;
|
|
// $this->db->select('*');
|
|
// $this->db->from('ip_invoice_custom');
|
|
// $this->db->where('ip_invoice_custom.invoice_id',$source_id);
|
|
// $query = $this->db->get();
|
|
// // print_r($this->db->last_query());
|
|
// return $query->result();
|
|
// }
|
|
|
|
|
|
public function SaveFinalHistory($final_productarray)
|
|
{
|
|
$this->db->trans_start();
|
|
$this->db->insert('T_FinalProduct_History',$final_productarray);
|
|
$insert_id = $this->db->affected_rows();
|
|
$this->db->trans_complete();
|
|
return true;
|
|
}
|
|
|
|
|
|
public function GetInvoiceDeatisl($invoice_id)
|
|
{
|
|
$this->db->select('invoice_number');
|
|
$this->db->from('ip_invoices');
|
|
$this->db->where('invoice_id',$invoice_id);
|
|
$query = $this->db->get();
|
|
// print_r( $this->db->last_query());
|
|
return $query->result();
|
|
|
|
}
|
|
|
|
|
|
|
|
public function UpdateFinalHistory($final_productarray,$invoice_id,$itemproductid)
|
|
{
|
|
$this->db->where('invoice_id', $invoice_id);
|
|
$this->db->where('ProductCode', $itemproductid);
|
|
$this->db->update('T_FinalProduct_History', $final_productarray);
|
|
return TRUE;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
} |