842 lines
38 KiB
PHP
Executable File
842 lines
38 KiB
PHP
Executable File
<?php
|
||
|
||
namespace App\Models;
|
||
|
||
use App\Controllers\Books;
|
||
use CodeIgniter\Model;
|
||
|
||
|
||
class InvoiceModel extends Model
|
||
{
|
||
protected $table = 'invoice';
|
||
protected $primaryKey = 'invoice_id';
|
||
// protected $allowedFields = ['invoice_id','invoice_number','customer_id','invoice_date','event_id','business_id','created_on','business_id'];
|
||
protected $allowedFields = ['invoice_id', 'invoice_number', 'customer_id', 'notes', 'invoice_date', 'due_date', 'subtotal', 'tax', 'dis_type', 'discount', 'shipping_charge', 'shipping_label', 'total_amount', 'payment_status', 'order_number', 'invoice_type', 'payment_method', 'event_id', 'business_id', 'shipping_address_id', 'billing_address_id', 'created_on', 'created_by', 'updated_on', 'updated_by', 'category', 'isactive', 'billing_address', 'shipping_address', 'status', 'payment_note', 'exact_total_amount'];
|
||
|
||
public function saveInvoiceItemDetails($data)
|
||
{
|
||
$i = 0;
|
||
$statement = [];
|
||
foreach ($data as $row) {
|
||
$id = isset($row['invoice_child_id']) ? $row['invoice_child_id'] : '';
|
||
if ($id != '') {
|
||
unset($row['invoice_child_id']); // Remove the id from the data to avoid updating it
|
||
unset($row['created_by']); // bcoz here data are Updating here.
|
||
|
||
$this->db->table('invoiceitems')->where('invoice_child_id', $id)->update($row); // Update the row with the specified id
|
||
$affectedRows = $this->db->affectedRows();
|
||
$statement[$i] = "Invoice Item - " . $id . " " . $affectedRows ? " Updated" : " Not Updated";
|
||
} else {
|
||
if (!empty($row['updated_by'])) {
|
||
unset($row['updated_by']); // bcoz here data are inserting here.
|
||
}
|
||
$this->db->table('invoiceitems')->insert($row);
|
||
$insertID = $this->db->insertID();
|
||
$statement[$i] = "Invoice Item - " . $insertID . " Inserted";
|
||
}
|
||
}
|
||
return $statement;
|
||
}
|
||
|
||
|
||
public function inactiveMissingInvoiceItemDetails($where, $missingValues)
|
||
{
|
||
$dataToUpdate = ['isactive' => 0];
|
||
$this->db->table('invoiceitems')->where($where)->whereIn('invoice_child_id', $missingValues)->update($dataToUpdate);
|
||
}
|
||
|
||
public function deleteMissingInvoiceItemDetails($where, $missingValues)
|
||
{
|
||
$this->db->table('invoiceitems')
|
||
->where($where)
|
||
->whereIn('invoice_child_id', $missingValues)
|
||
->delete();
|
||
}
|
||
|
||
public function updateData($table, $data, $where)
|
||
{
|
||
$this->db->table($table)->update($data, $where);
|
||
$affected_rows = $this->db->affectedRows();
|
||
return $affected_rows;
|
||
}
|
||
|
||
public function InactiveSubscriptionDraftDetails($where, $update_by_id)
|
||
{
|
||
$result = $this->getJoinedData($where);
|
||
$inactive_invoice_ids = array();
|
||
foreach ($result as $item) {
|
||
$inactive_invoice_ids[] = $item['invoice_id'];
|
||
}
|
||
|
||
if (count($inactive_invoice_ids) > 0) {
|
||
$this->db->table('subscription')
|
||
->whereIn('invoice_id', $inactive_invoice_ids)
|
||
->set('isactive', 0)
|
||
->set('updated_by', $update_by_id)
|
||
->update();
|
||
}
|
||
|
||
return $inactive_invoice_ids;
|
||
}
|
||
|
||
public function deleteSubscriptionDraftDetails($where, $update_by_id)
|
||
{
|
||
$result = $this->getJoinedData($where);
|
||
$delete_invoice_ids = array();
|
||
foreach ($result as $item) {
|
||
$delete_invoice_ids[] = $item['invoice_id'];
|
||
}
|
||
if (count($delete_invoice_ids) > 0) {
|
||
$this->db->table('subscription')
|
||
->whereIn('invoice_id', $delete_invoice_ids)
|
||
->delete();
|
||
}
|
||
return $delete_invoice_ids;
|
||
}
|
||
|
||
## check the customer the scheme already Exist
|
||
public function existsSubscriptionDetails($where)
|
||
{
|
||
$result = $this->getJoinedData($where);
|
||
if (count($result) > 0) {
|
||
return 1;
|
||
} else {
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
public function getSubscriptionInvoiceDetail($where)
|
||
{
|
||
$result = $this->getJoinedData($where);
|
||
// print_r($result);die;
|
||
if (!empty($result)) {
|
||
foreach ($result as $i => $item) {
|
||
$invoiceId = $item['invoice_id'];
|
||
$schemes = [];
|
||
$invoiceItems = $this->getInvoiceItems($invoiceId, '');
|
||
foreach ($invoiceItems as $j => $child) {
|
||
if (strtolower($child->category_name) == strtolower('Membership')) {
|
||
$schemes[] = [
|
||
'scheme_name' => $child->title,
|
||
'scheme_code' => !empty($child->short_code) ? $child->short_code : $child->title,
|
||
];
|
||
}
|
||
}
|
||
$result[$i]['scheme_name'] = !empty($schemes) ? implode(", ", array_column($schemes, 'scheme_name')) : '';
|
||
$result[$i]['scheme_code'] = !empty($schemes) ? implode(", ", array_column($schemes, 'scheme_code')) : '';
|
||
|
||
$result[$i]['invoice_items_details'] = $invoiceItems;
|
||
}
|
||
}
|
||
return $result;
|
||
}
|
||
|
||
public function getJoinedData($where, $orderby = [])
|
||
{
|
||
// dd($where);
|
||
$query = $this->db->table($this->table . ' as I')
|
||
->join('customers as C', 'C.customer_id = I.customer_id', 'left')
|
||
->join('subscription as S', 'S.invoice_id = I.invoice_id', 'left')
|
||
->join('events as E', 'E.event_id = I.event_id', 'left')
|
||
->join('business as B', 'B.business_id = I.business_id', 'left')
|
||
->join('users as U1', 'U1.user_id = I.created_by', 'left')
|
||
->join('users as U2', 'U2.user_id = I.updated_by', 'left')
|
||
->select('I.invoice_id,I.wp_api_order_id,I.invoice_number, I.customer_id,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name ,C.mobile_no, I.invoice_date, I.due_date, I.subtotal, I.tax, I.discount, I.total_amount,I.status, I.payment_status, I.order_number, I.payment_method,I.invoice_type, I.event_id,E.event_name, I.business_id,B.title as business_name,DATE_FORMAT(I.created_on, "%d/%m/%Y") as created_on_format,I.created_on, I.created_by,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,DATE_FORMAT(I.updated_on, "%d/%m/%Y") as updated_on_format, I.updated_by,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name, I.isactive,C.email as customer_email,C.mobile_no as customer_mobile,I.shipping_address_id,I.shipping_address,I.billing_address_id,I.billing_address,B.address as business_address,B.city as business_city,B.state as business_state,B.postal_code as business_postal_code,B.email as business_email,B.mobile_no as business_mobile_no,S.from_subscription,S.to_subscription,DATEDIFF(S.to_subscription, S.from_subscription) as subscription_in_days,S.scheme_id,(SELECT is_renew FROM subscription as sub WHERE S.sub_id = sub.is_renew LIMIT 1) as renewed')
|
||
->where($where);
|
||
// if (!empty($orderby)) {
|
||
// $query->orderBy($orderby[0], $orderby[1]);
|
||
// } else {
|
||
// $query->orderBy('invoice_id', 'DESC');
|
||
// }
|
||
$query->orderBy('I.invoice_date', 'DESC');
|
||
$query->orderBy('I.invoice_id', 'DESC');
|
||
// $query->groupBy('I.invoice_id');
|
||
$resultArray = $query->get()->getResultArray();
|
||
|
||
|
||
// Print the last executed query
|
||
//echo $this->db->getLastQuery();die;
|
||
|
||
return $resultArray;
|
||
}
|
||
|
||
|
||
## subscription_inactive
|
||
public function subscription_inactive()
|
||
{
|
||
$now = date('Y-m-d');
|
||
$result = $this->db->table('subscription as S')
|
||
->select('S.sub_id, S.scheme_id, S.customer_id, S.business_id,S.invoice_id,S.mode,S.from_subscription, S.to_subscription, S.is_renew, S.isactive')
|
||
->where("S.isactive", 1)
|
||
->where("S.to_subscription < ", $now)
|
||
->get()
|
||
->getResultArray();
|
||
// echo "<pre>";print_r($result);echo "</pre>";die;
|
||
$returnMessages = [];
|
||
$i = 0;
|
||
$j = 0;
|
||
if (!empty($result)) {
|
||
foreach ($result as $row) {
|
||
// $this->db->table('invoiceitems')->where(['invoice_id' => $row['invoice_id']])->update(['from_subscription' => NULL,'to_subscription' => NULL]);//first child
|
||
$this->db->table('subscription')->where(['sub_id' => $row['sub_id']])->update(['isactive' => 0]); //second child
|
||
$affectedRows = $this->db->affectedRows();
|
||
if ($affectedRows) {
|
||
$returnMessages['message'] = "Success for sub_id: " . $row['sub_id'];
|
||
$returnMessages['success_rating'] = $i++;
|
||
} else {
|
||
$returnMessages['message'] = 'Failed to update subscription with sub_id ' . $row['sub_id'] . '. No rows were affected.';
|
||
$returnMessages['error_rating'] = $j++;
|
||
}
|
||
}
|
||
return $returnMessages;
|
||
// return implode("\n", $returnMessages);
|
||
} else {
|
||
return "No data";
|
||
}
|
||
}
|
||
|
||
public function getDetailForApproveNotifications($where)
|
||
{
|
||
$result = $this->getJoinedData($where);
|
||
if (!empty($result)) {
|
||
foreach ($result as $object) {
|
||
$invoiceId = $object['invoice_id'];
|
||
$items = $this->getInvoiceItems($invoiceId, '');
|
||
}
|
||
} else {
|
||
$items = [];
|
||
}
|
||
$data['invoice'] = $result;
|
||
$data['item'] = $items;
|
||
return $data;
|
||
}
|
||
|
||
public function getData($table, $where = null)
|
||
{
|
||
$query = $this->db->table($table);
|
||
if ($where) {
|
||
$query->where($where);
|
||
}
|
||
return $query->get()->getResult();
|
||
}
|
||
|
||
// public function getInvoiceData($id)
|
||
// {
|
||
// // Fetch invoice data
|
||
// return $this->db->table('invoice')
|
||
// ->where('invoice_id', $id)
|
||
// ->join('customers as C','C.customer_id= invoice.customer_id','left')
|
||
// ->join('business as B','B.business_id = invoice.business_id','left')
|
||
// ->join('customer_addresses as CA', 'CA.customer_id = invoice.customer_id AND CA.customer_address_id = invoice.shipping_address_id', 'left')
|
||
// ->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,DATE_FORMAT(invoice.due_date, "%d/%m/%Y") AS formatted_due_date ,concat(C.first_name," ",C.last_name) as customer_name,C.mobile_no as customer_mobile')
|
||
// ->select('B.title as company_name,B.business_logo,B.terms as company_terms,B.address as company_address,B.city as company_city,B.state as company_state,B.postal_code as company_postal_code,B.email as company_email,B.mobile_no as company_mobile_no')
|
||
// // ->select('COALESCE(NULLIF(states.state_name, ""), A.state) AS customer_bill_state')
|
||
// // ->select(' customer_bill_country')
|
||
// ->get()
|
||
// ->getResult();
|
||
|
||
// }
|
||
public function getInvoiceData($id)
|
||
{
|
||
// return $this->db->table('invoice')
|
||
// ->where('invoice_id', $id)
|
||
// ->join('customer_addresses as A','A.customer_address_id=invoice.shipping_address_id ','left')
|
||
// ->select('invoice.*,A.*')
|
||
// ->get()
|
||
// ->getResult();
|
||
|
||
// Fetch invoice data
|
||
return $this->db->table('invoice')
|
||
->where('invoice_id', $id)
|
||
->join('customers as C', 'C.customer_id= invoice.customer_id', 'left')
|
||
->join('business as B', 'B.business_id = invoice.business_id', 'left')
|
||
->join('customer_addresses as A', 'A.customer_address_id=invoice.billing_address_id AND A.address_type = 1', 'left')
|
||
->join('customer_addresses as S', 'S.customer_address_id=invoice.shipping_address_id AND S.address_type = 2', 'left')
|
||
->join('states', 'states.state_short_name = A.state AND A.country = "IN"', 'left')
|
||
->join('countries', 'countries.country_short_name = A.country', 'left')
|
||
->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,DATE_FORMAT(invoice.due_date, "%d/%m/%Y") AS formatted_due_date ,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name,C.mobile_no,C.email,A.address_1, A.address_2,A.postal_code,A.city, A.state,C.mobile_no as customer_mobile')
|
||
->select('B.title as company_name,B.business_logo,B.terms as company_terms,B.address as company_address,B.city as company_city,B.state as company_state,B.postal_code as company_postal_code,B.email as company_email,B.mobile_no as company_mobile_no')
|
||
->select('COALESCE(NULLIF(states.state_name, ""), A.state) AS customer_bill_state')
|
||
->select('COALESCE(NULLIF(states.state_name, ""), S.state) AS customer_ship_state')
|
||
->select('CONCAT_WS(" ", A.first_name, A.last_name) as customer_biller_name,concat(A.address_1," ", A.address_2) as customer_bill_address,A.postal_code as customer_bill_postal_code ,A.city as customer_bill_city, countries.country_name as customer_bill_country,A.country as bcountry,A.state as bstate,A.customer_address_id as baddr_id,A.email as bemail,A.mobile_no as bmobile')
|
||
->select('CONCAT_WS(" ", S.first_name, S.last_name) as customer_shipper_name,concat(S.address_1," ", S.address_2) as customer_ship_address,S.postal_code as customer_ship_postal_code ,S.city as customer_ship_city, countries.country_name as customer_ship_country,S.country as scountry,S.state as sstate,S.customer_address_id as saddr_id,S.email as semail,S.mobile_no as smobile')
|
||
->get()
|
||
->getResult();
|
||
}
|
||
// this function Also Used For Approve Notification.
|
||
public function getInvoiceItems($id, $stringflag)
|
||
{
|
||
|
||
if ($stringflag == 'groupby') {
|
||
$select = ' B.*, GROUP_CONCAT(C.name SEPARATOR \',\') as name,invoiceitems.*';
|
||
$group_by = 'invoiceitems.invoice_child_id';
|
||
} else {
|
||
$select = 'invoiceitems.*, B.*, C.name as category_name';
|
||
$group_by = "";
|
||
}
|
||
|
||
$data = $this->db->table('invoiceitems')
|
||
->where(['invoice_id' => $id, 'invoiceitems.isactive' => 1])
|
||
->join('books as B', 'B.book_id = invoiceitems.product', 'left')
|
||
->join('book_categories as BC', 'BC.book_id = invoiceitems.product', 'left')
|
||
->join('category as C', 'C.id = BC.category_id', 'left')
|
||
->select($select)
|
||
->groupBy($group_by) // Fixed the variable name here
|
||
->get()
|
||
->getResult();
|
||
//->orderBy("book_img_id", "asc") // Order by book_img_id in ascending order
|
||
//->limit(1,0)
|
||
|
||
|
||
// print_r($this->db->getLastQuery());die;
|
||
// print_r($data);die("ends -- here");
|
||
return $data;
|
||
}
|
||
|
||
|
||
public function getMembershipListForCustomer($category, $id)
|
||
{
|
||
$result = $this->db->table('subscription as S')
|
||
->join('customers as C', 'C.customer_id = S.customer_id', 'left')
|
||
->join('books as BK', 'BK.book_id = S.scheme_id', 'left')
|
||
->join('book_categories as BC', 'BC.book_id = S.scheme_id', 'left')
|
||
->join('category as CM', 'CM.id = BC.category_id', 'left')
|
||
->join('invoice as I', 'I.invoice_id = S.invoice_id', 'left')
|
||
->select('S.customer_id,BK.short_code,S.membership_id,S.status,I.status as invoice_status ,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name, S.sub_id, S.scheme_id, DATE_FORMAT(S.from_subscription, "%d/%m/%Y") AS formatted_from_subscription, DATE_FORMAT(S.to_subscription, "%d/%m/%Y") AS formatted_to_subscription, S.from_subscription, S.to_subscription,S.isactive,S.is_renew, S.created_on, CM.name ,BK.title, BK.book_id,(SELECT is_renew FROM subscription as sub WHERE S.sub_id = sub.is_renew LIMIT 1)as renewed')
|
||
->where('LOWER(CM.name)', strtolower($category))
|
||
->where('S.customer_id', $id)
|
||
->orderBy('S.to_subscription', 'ASC')
|
||
->groupBy('S.sub_id')
|
||
->get()
|
||
->getResultArray();
|
||
return $result;
|
||
}
|
||
|
||
public function getProductImgs($productId)
|
||
{
|
||
$data = $this->db->table('book_images')
|
||
->where(['book_id' => $productId, 'book_images.isactive' => 1])
|
||
// ->join('books as B', 'B.book_id = invoiceitems.product', 'left')
|
||
// ->join('book_images as BI', 'BI.book_id = invoiceitems.product', 'left')
|
||
->select('book_images.*')
|
||
//->orderBy("book_img_id", "asc") // Order by book_img_id in ascending order
|
||
//->limit(1,0)
|
||
->get()
|
||
->getResult();
|
||
|
||
// print_r($this->db->getLastQuery());
|
||
return $data;
|
||
}
|
||
|
||
|
||
public function getCategoryBooks($type)
|
||
{
|
||
|
||
|
||
// $where = ($type == 1) ? ['LOWER(category.name) !=' => strtolower('Membership')] : ['LOWER(category.name)' => strtolower('Membership')];
|
||
|
||
// $data = $this->db->table('books')
|
||
// ->select('books.book_id,books.business_id,books.wp_api_product_id,books.title,books.publication_date,books.publisher,books.author,books.genre,books.language,books.description,books.short_description,books.page_count,books.price,books.isbn_code,books.tax,books.sku,books.category,books.isactive')
|
||
// ->join('book_categories', 'book_categories.book_id= books.book_id', 'left')
|
||
// ->join('category','category.id=book_categories.category_id')
|
||
// ->where($where)
|
||
// ->groupBy('books.book_id')
|
||
// ->get()
|
||
// ->getResult();
|
||
// print_r($this->db->getLastQuery());die;
|
||
$data = $this->db->table('books')
|
||
->select('books.book_id,books.business_id,books.wp_api_product_id,books.title,books.publication_date,books.publisher,books.author,books.genre,books.language,books.description,books.short_description,books.page_count,books.price,books.isbn_code,books.tax,books.sku,books.category,books.isactive')
|
||
->join('book_categories', 'book_categories.book_id = books.book_id and book_categories.isactive = 1', 'left')
|
||
->join('category', 'category.id = book_categories.category_id', 'left');
|
||
|
||
if ($type == 1) {
|
||
$data->where("LOWER(category.name) != 'membership' OR category.id IS NULL");
|
||
} else {
|
||
$data->where('LOWER(category.name)', 'membership');
|
||
}
|
||
|
||
$data = $data->groupBy('books.book_id')
|
||
->get()
|
||
->getResult();
|
||
|
||
return $data;
|
||
}
|
||
|
||
public function insertupdateSubscriptionData($data, $invoice_status)
|
||
{
|
||
if (!empty($data)) {
|
||
$invoice_id = $data['invoice_id'];
|
||
$customer_id = $data['customer_id'];
|
||
$scheme_id = $data['scheme_id'];
|
||
$data['isactive'] = $invoice_status === 'Approved' ? 1 : 0;
|
||
$where = ['invoice_id' => $invoice_id]; // invoice id refer with srinivasan // initally invoice id, scheme id and customer id
|
||
$query = $this->db->table('subscription')->select('sub_id')->where($where)->get()->getRow();
|
||
|
||
if (!empty($query)) {
|
||
unset($data['created_by']);
|
||
return $this->db->table('subscription')->where('sub_id', $query->sub_id)->update($data);
|
||
} else {
|
||
unset($data['updated_by']);
|
||
return $this->db->table('subscription')->insert($data);
|
||
}
|
||
}
|
||
return false;
|
||
}
|
||
|
||
// ***********************REPORT**********************
|
||
|
||
public function get_general_invoice_data($f_date = null, $t_date = null)
|
||
{
|
||
// Fetch invoice data
|
||
$query = $this->db->table('invoice')
|
||
->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date, customers.*, COUNT(invoiceitems.product) as item_count')
|
||
->where('invoice.isactive', 1)
|
||
|
||
->where('invoiceitems.isactive', 1);
|
||
|
||
// Add date range filter if provided
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$query->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
|
||
$result = $query->join('customers', 'customers.customer_id = invoice.customer_id', 'left')
|
||
->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
|
||
->join('books', 'books.book_id = invoiceitems.product', 'left')
|
||
->orderBy("invoice.invoice_date", "DESC")
|
||
->groupBy('invoice.invoice_id') // Assuming invoice_id is the primary key of the invoice table
|
||
->get()
|
||
->getResult();
|
||
|
||
return $result;
|
||
}
|
||
|
||
|
||
public function get_mem_invoice_data($f_date = null, $t_date = null)
|
||
{
|
||
// Fetch invoice data
|
||
$query = $this->db->table('invoice')
|
||
->select('invoice.*, customers.*, COUNT(invoiceitems.product) as item_count,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,subscription.from_subscription,subscription.to_subscription,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_date,DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_date')
|
||
->join('subscription', 'subscription.invoice_id = invoice.invoice_id', 'left')
|
||
->where('invoice.isactive', 1)
|
||
->where('invoice.invoice_type', 2)
|
||
->where('invoiceitems.isactive', 1);
|
||
|
||
// Add date range filter if provided
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$query->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
|
||
$result = $query->join('customers', 'customers.customer_id = invoice.customer_id', 'left')
|
||
->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
|
||
->join('books', 'books.book_id = invoiceitems.product', 'left')
|
||
->groupBy('invoice.invoice_id') // Assuming invoice_id is the primary key of the invoice table
|
||
->get()
|
||
->getResult();
|
||
|
||
return $result;
|
||
}
|
||
|
||
// public function itemwise_report_data($f_date = null, $t_date = null)
|
||
// {
|
||
// // Fetch invoice data
|
||
// $query = $this->db->table('invoice')
|
||
// ->select('invoice.*, books.* , COUNT(invoiceitems.product) as item_count , sum(invoiceitems.product * invoiceitems.unit_price) as item_cost')
|
||
// ->where('invoice.isactive', 1)
|
||
// ->where('books.isactive', 1);
|
||
|
||
// // Add date range filter if provided
|
||
// if ($f_date !== null && $t_date !== null) {
|
||
// $query->where('invoice.invoice_date >=', $f_date)
|
||
// ->where('invoice.invoice_date <=', $t_date);
|
||
// }
|
||
|
||
// $result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
|
||
// ->join('books', 'books.book_id = invoiceitems.product', 'left')
|
||
// ->groupBy('books.book_id')
|
||
// ->get()
|
||
// ->getResult();
|
||
|
||
// return $result;
|
||
// }
|
||
public function itemwise_report_data($f_date = null, $t_date = null)
|
||
{
|
||
|
||
// Fetch invoice data
|
||
$query = $this->db->table('invoice')
|
||
->select('books.publishers_code, COUNT(invoiceitems.product) as publisher_item_count, books.title as book_name, COUNT(invoiceitems.product) as item_count, SUM(invoiceitems.quantity * invoiceitems.unit_price) as total_cost ,DATE_FORMAT(books.publication_date, "%d/%m/%Y") AS book_publication_date');
|
||
|
||
$query->where('invoice.isactive', 1)
|
||
->where('invoice.invoice_type', 1)
|
||
->where('books.isactive', 1);
|
||
|
||
// Add date range filter if provided
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$newf_date = date("Y-m-d", strtotime($f_date));
|
||
$newt_date = date("Y-m-d", strtotime($t_date));
|
||
$query->where('books.publication_date >=', $newf_date)
|
||
->where('books.publication_date <=', $newt_date);
|
||
}
|
||
|
||
|
||
$result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
|
||
->join('books', 'books.book_id = invoiceitems.product', 'left')
|
||
->groupBy('books.publishers_code, books.title')
|
||
->get()
|
||
->getResult();
|
||
// print_r($result);
|
||
|
||
// // Group by publisher_code and include books
|
||
// var_dump($result);
|
||
// die();
|
||
$groupedResult = [];
|
||
foreach ($result as $row) {
|
||
$publisherCode = $row->publishers_code;
|
||
if (!isset($groupedResult[$publisherCode])) {
|
||
$groupedResult[$publisherCode] = (object)[
|
||
'publisher_code' => $publisherCode,
|
||
'publisher_item_count' => 0,
|
||
'publisher_total_cost' => 0,
|
||
'books' => [],
|
||
'book_name' => '',
|
||
|
||
];
|
||
}
|
||
|
||
// Add book information to the grouped result
|
||
$groupedResult[$publisherCode]->publisher_item_count += $row->item_count;
|
||
$groupedResult[$publisherCode]->publisher_total_cost += $row->total_cost;
|
||
$groupedResult[$publisherCode]->books[] = (object)[
|
||
'book_name' => $row->book_name,
|
||
'item_count' => $row->item_count,
|
||
'total_cost' => $row->total_cost,
|
||
'book_publication_date' => $row->book_publication_date,
|
||
];
|
||
}
|
||
|
||
return array_values($groupedResult);
|
||
}
|
||
|
||
|
||
public function getInvoiceIdByMd5($md5Hash)
|
||
{
|
||
$result = $this->db->table($this->table)
|
||
->select('invoice_id')
|
||
->get()
|
||
->getResult();
|
||
|
||
foreach ($result as $row) {
|
||
if (md5($row->invoice_id) === $md5Hash) {
|
||
return $row->invoice_id;
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
// public function getExpiredCustomers($f_date = null, $t_date = null)
|
||
// {
|
||
// $now = date('Y-m-d');
|
||
// $futureDate = date('Y-m-d', strtotime($now . ' +30 days'));
|
||
|
||
// $query = $this->db->table('subscription as S'); // Define $query here
|
||
|
||
// if ($f_date !== null && $t_date !== null) {
|
||
// $query->where('S.to_subscription >=', $f_date)
|
||
// ->where('S.to_subscription <=', $t_date);
|
||
// }
|
||
|
||
// $result = $query
|
||
// ->select('S.customer_id,S.sub_id, S.from_subscription, S.to_subscription, C.first_name, C.last_name, C.email, C.mobile_no, S.scheme_id,B.short_code')
|
||
// ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
|
||
// ->join('books as B', 'B.book_id = S.scheme_id', 'left')
|
||
// ->where('S.isactive', 1)
|
||
// ->where('S.to_subscription <', $futureDate)
|
||
// ->get()
|
||
// ->getResultArray();
|
||
// // print_r($result);
|
||
// // echo "<pre>";
|
||
// // echo $this->db->getLastQuery();
|
||
// // echo "</pre>";die;
|
||
// echo $this->db->getLastQuery();
|
||
// die();
|
||
|
||
// return $result;
|
||
|
||
// }
|
||
public function getExpiredCustomers($f_date = null, $t_date = null)
|
||
{
|
||
// Set timezone for accurate date calculation
|
||
date_default_timezone_set('Asia/Kolkata');
|
||
|
||
// Calculate the date 30 days from now
|
||
$futureDate = date('Y-m-d', strtotime('+30 days'));
|
||
|
||
$query = $this->db->table('subscription as S');
|
||
|
||
if (!empty($f_date) && !empty($t_date)) {
|
||
$query->where('S.to_subscription >=', $f_date)
|
||
->where('S.to_subscription <=', $t_date);
|
||
} else {
|
||
$query->where('S.to_subscription >=', date('Y-m-d'))
|
||
->where('S.to_subscription <=', $futureDate);
|
||
}
|
||
|
||
$subquery = $this->db->table('subscription as S2')
|
||
->select('S.sub_id')
|
||
->where('S2.customer_id = S.customer_id')
|
||
->where('S.sub_id = S2.is_renew');
|
||
|
||
$result = $query
|
||
->select('S.customer_id, S.sub_id, S.from_subscription, S.to_subscription,
|
||
C.first_name, C.last_name, C.email, C.mobile_no,
|
||
S.scheme_id, B.short_code, S.membership_id')
|
||
->join('customers as C', 'C.customer_id = S.customer_id', 'left')
|
||
->join('books as B', 'B.book_id = S.scheme_id', 'left')
|
||
->where('S.isactive', 1)
|
||
->whereNotIn('S.sub_id', $subquery)
|
||
->orderBy('S.to_subscription', 'ASC')
|
||
->get();
|
||
|
||
|
||
// Debugging: output the generated SQL query
|
||
// echo $this->db->getLastQuery();
|
||
// die();
|
||
|
||
// Fetch results as an associative array
|
||
return $result->getResultArray();
|
||
}
|
||
public function getActiveMembers()
|
||
{
|
||
$result =
|
||
$this->db->table('subscription as S')
|
||
->select('S.customer_id, S.sub_id, S.from_subscription, S.to_subscription,
|
||
C.first_name, C.last_name, C.email, C.mobile_no,
|
||
S.scheme_id, B.short_code, S.membership_id')
|
||
->join('customers as C', 'C.customer_id = S.customer_id', 'left')
|
||
->join('books as B', 'B.book_id = S.scheme_id', 'left')
|
||
->where('S.isactive', 1)
|
||
->where('S.to_subscription>now()')
|
||
->orderBy('S.to_subscription', 'ASC')
|
||
->get();
|
||
return $result->getResultArray();
|
||
}
|
||
|
||
public function itemwise_report_data_with_publish_code($f_date = null, $t_date = null)
|
||
{
|
||
// Fetch invoice data
|
||
$query = $this->db->table('invoice')
|
||
->select('invoice.*, books.* , COUNT(invoiceitems.product) as item_count , sum(invoiceitems.product * invoiceitems.unit_price) as item_cost')
|
||
->where('invoice.isactive', 1)
|
||
->where('books.isactive', 1)
|
||
->where('books.publishers_code', 'VJB'); // Additional condition for publish_code
|
||
|
||
// Add date range filter if provided
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$query->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
|
||
$result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
|
||
->join('books', 'books.book_id = invoiceitems.product', 'left')
|
||
->groupBy('books.book_id')
|
||
->get()
|
||
->getResult();
|
||
// print_r($result);die;
|
||
|
||
return $result;
|
||
}
|
||
|
||
public function userwise_eventwise_report($f_date = null, $t_date = null) {
|
||
$builder = $this->db->table('invoice');
|
||
|
||
$builder->select([
|
||
'users.user_id',
|
||
'users.first_name',
|
||
'users.role',
|
||
'events.event_name',
|
||
'COUNT(invoice.created_by) AS books_sold',
|
||
'ABS(SUM(invoice.exact_total_amount)) AS total_amount',
|
||
'invoice.payment_method',
|
||
// 'DATE(invoice.invoice_date) AS invoice_date' // Convert to DATE
|
||
'invoice.invoice_date'
|
||
]);
|
||
|
||
$builder->join('users', 'users.user_id = invoice.created_by');
|
||
$builder->join('events', 'events.event_id = invoice.event_id');
|
||
$builder->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id');
|
||
$builder->join('books', 'books.book_id = invoiceitems.product');
|
||
|
||
$builder->where('invoice.event_id <>', 0);
|
||
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$builder->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
|
||
$builder->groupBy([
|
||
'invoice_date',
|
||
'users.user_id',
|
||
'events.event_name'
|
||
]);
|
||
|
||
// Order by the converted date
|
||
$builder->orderBy('invoice_date', 'DESC');
|
||
|
||
$query = $builder->get();
|
||
|
||
$result = $query->getResultArray();
|
||
// dd($result);
|
||
return $result;
|
||
}
|
||
|
||
|
||
public function getPaymentReport($f_date = null, $t_date = null)
|
||
{
|
||
|
||
$builder = $this->db->table('invoice');
|
||
$builder->select('invoice.invoice_number, customers.first_name, invoice.total_amount, invoice.payment_status, invoice.payment_method, invoice.invoice_date');
|
||
$builder->join('customers', 'invoice.customer_id = customers.customer_id', 'inner');
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$builder->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
$builder->where('invoice.status', 'Approved');
|
||
$query = $builder->get();
|
||
$result1 = $query->getResultArray();
|
||
$builder->select('payment_method, SUM(exact_total_amount) as total_amount');
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$builder->where('invoice.invoice_date >=', $f_date)
|
||
->where('invoice.invoice_date <=', $t_date);
|
||
}
|
||
$builder->groupBy('payment_method');
|
||
$query2 = $builder->get();
|
||
$paymentMethodTotals = $query2->getResultArray();
|
||
$paymentMethodMap = [];
|
||
foreach ($paymentMethodTotals as $row) {
|
||
$paymentMethodMap[$row['payment_method']] = $row['total_amount'];
|
||
}
|
||
$result2 = $paymentMethodMap;
|
||
// log_message('info',json_encode($results));
|
||
// dd($results);
|
||
$results = [
|
||
'result1' => $result1,
|
||
'result2' => $result2
|
||
];
|
||
return $results;
|
||
}
|
||
public function itemwise_report_data_with_payment_method($f_date = null, $t_date = null)
|
||
{
|
||
$builder = $this->db->table('invoice');
|
||
$builder->select('books.publishers_code,
|
||
COUNT(invoiceitems.product) AS publisher_item_count,
|
||
books.title AS book_name,
|
||
COUNT(invoiceitems.product) AS item_count,
|
||
SUM(invoiceitems.quantity * invoiceitems.unit_price) AS total_cost,
|
||
invoice.payment_method,
|
||
DATE_FORMAT(invoice.invoice_date, "%d-%m-%Y") AS invoice_date');
|
||
$builder->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left');
|
||
$builder->join('books', 'books.book_id = invoiceitems.product', 'left');
|
||
$builder->where('invoice.isactive', 1);
|
||
$builder->where('invoice.invoice_type', 1);
|
||
$builder->where('books.isactive', 1);
|
||
if ($f_date !== null && $t_date !== null) {
|
||
$builder->where('invoice_date >=', $f_date)
|
||
->where('invoice_date <=', $t_date);
|
||
}
|
||
$builder->groupBy(['books.publishers_code', 'books.title', 'invoice.payment_method']);
|
||
|
||
$query = $builder->get();
|
||
$results = $query->getResultArray();
|
||
|
||
$groupedResult = [];
|
||
foreach ($results as $row) {
|
||
// Use a unique key for each combination of publisher and payment method
|
||
$publisherCode = $row['publishers_code'];
|
||
$paymentMethod = $row['payment_method'];
|
||
$uniqueKey = $publisherCode . '-' . $paymentMethod; // Unique key for grouping
|
||
|
||
// Initialize the group if it does not exist
|
||
if (!isset($groupedResult[$uniqueKey])) {
|
||
$groupedResult[$uniqueKey] = (object)[
|
||
'publisher_code' => $publisherCode,
|
||
'publisher_item_count' => 0,
|
||
'publisher_total_cost' => 0,
|
||
'books' => [],
|
||
'payment_method' => $paymentMethod,
|
||
];
|
||
}
|
||
|
||
// Update the group’s cumulative values
|
||
$groupedResult[$uniqueKey]->publisher_item_count += $row['publisher_item_count'];
|
||
$groupedResult[$uniqueKey]->publisher_total_cost += $row['total_cost'];
|
||
|
||
// Add the book-specific information to the books array
|
||
$groupedResult[$uniqueKey]->books[] = (object)[
|
||
'book_name' => $row['book_name'],
|
||
'item_count' => $row['item_count'],
|
||
'total_cost' => $row['total_cost'],
|
||
'invoice_date' => $row['invoice_date'],
|
||
];
|
||
}
|
||
|
||
return array_values($groupedResult);
|
||
}
|
||
|
||
|
||
public function updateInvoiceStatus($invoiceId, $voidReason)
|
||
{
|
||
// Assuming 'invoices' is the name of your table
|
||
$builder = $this->db->table('invoice');
|
||
|
||
// Define the data to be updated
|
||
$data = [
|
||
'status' => 'Void', // Set the status to 'Void'
|
||
'reason' => $voidReason, // Set the void reason
|
||
// You can add more fields to update here if needed
|
||
];
|
||
|
||
// Set the where condition to identify the invoice by its ID
|
||
$builder->where('invoice_id', $invoiceId);
|
||
|
||
// Perform the update and check if successful
|
||
$updated = $builder->update($data);
|
||
|
||
return $updated;
|
||
}
|
||
public function updateInvoiceCancelStatus($invoiceIds, $cancelReason)
|
||
{
|
||
// Assuming 'invoices' is the name of your table
|
||
$builder = $this->db->table('invoice');
|
||
|
||
// Define the data to be updated
|
||
$data = [
|
||
'status' => 'Cancelled', // Set the status to 'Canceled'
|
||
'reason' => $cancelReason, // Set the cancel reason
|
||
// You can add more fields to update here if needed
|
||
];
|
||
|
||
// Set the where condition to identify the invoices by their IDs
|
||
$builder->where('invoice_id', $invoiceIds);
|
||
|
||
// Perform the update and check if successful
|
||
$updated = $builder->update($data);
|
||
|
||
return $updated;
|
||
}
|
||
public function getActiveSchemes()
|
||
{
|
||
$builder = $this->db->table($this->table . ' as I');
|
||
$builder->select('books.short_code');
|
||
$builder->join('subscription', 'subscription.invoice_id = I.invoice_id');
|
||
$builder->join('invoiceitems', 'invoiceitems.invoice_id = I.invoice_id');
|
||
$builder->join('books', 'invoiceitems.product = books.book_id');
|
||
$builder->groupBy('short_code');
|
||
|
||
$query = $builder->get();
|
||
$results = $query->getResultArray();
|
||
|
||
return $results;
|
||
}
|
||
}
|