423 lines
20 KiB
PHP
Executable File
423 lines
20 KiB
PHP
Executable File
<?php
|
|
namespace App\Models;
|
|
use CodeIgniter\Model;
|
|
|
|
|
|
class SubscriptionModel extends Model
|
|
{
|
|
protected $table = 'subscription';
|
|
protected $primaryKey = 'sub_id';
|
|
protected $allowedFields = ['sub_id','invoice_id','scheme_id','customer_id','to_subscription','from_subscription','mode','created_on','created_by','updated_by','updated_on','business_id','status'];
|
|
|
|
|
|
|
|
public function getAllSubscribersWithNames($where)
|
|
{
|
|
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('subscription.*, books.title,books.short_code, books.isactive, CONCAT_WS(" ", customers.first_name, customers.last_name) as customer_name,(SELECT is_renew FROM subscription as sub WHERE subscription.sub_id = sub.is_renew LIMIT 1)as renewed');
|
|
$builder->join('books', 'books.book_id = subscription.scheme_id');
|
|
$builder->join('customers', 'customers.customer_id = subscription.customer_id');
|
|
$builder->where($where);
|
|
|
|
// $query = $this->db->getLastQuery();
|
|
// dd($query);
|
|
return $builder->get()->getResultArray();
|
|
}
|
|
public function getCustomerNameBySchemeName($schemeName)
|
|
{
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('customers.first_name, customers.last_name');
|
|
$builder->select('customer_addresses.address_1, customer_addresses.address_2');
|
|
$builder->join('customer_addresses', 'customer_addresses.customer_id = subscription.customer_id');
|
|
$builder->join('customers', 'customers.customer_id = subscription.customer_id');
|
|
$builder->join('schemes', 'schemes.scheme_id = subscription.scheme_id');
|
|
$builder->where('schemes.scheme_name', $schemeName);
|
|
|
|
$query = $builder->get();
|
|
|
|
if ($query->getNumRows() > 0) {
|
|
$row = $query->getRow();
|
|
return $row->first_name . ' ' . $row->last_name;
|
|
} else {
|
|
return 'Customer not found'; // You can return a default value or handle the case where the customer is not found.
|
|
}
|
|
}
|
|
|
|
public function getCustomerAddressesBySchemeName($schemeName)
|
|
{
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('CONCAT_WS(" ", customers.first_name, customers.last_name) as customer_name, customer_addresses.address_1, customer_addresses.address_2, customer_addresses.city, customer_addresses.state,customer_addresses.postal_code, customer_addresses.address_type,business.address,business.city,business.state,business.postal_code,business.country');
|
|
$builder->join('customers', 'customers.customer_id = subscription.customer_id');
|
|
$builder->join('customer_addresses', 'customer_addresses.customer_id = subscription.customer_id');
|
|
$builder->join('business', 'business.business_id = subscription.business_id');
|
|
$builder->join('books', 'books.book_id = subscription.scheme_id');
|
|
$builder->where('books.title', $schemeName);
|
|
|
|
$query = $builder->get();
|
|
|
|
$addresses = [];
|
|
|
|
if ($query->getNumRows() > 0) {
|
|
foreach ($query->getResult() as $row) {
|
|
$addressType = $row->address_type;
|
|
$addresses[$addressType] = [
|
|
'customer_name' => $row->customer_name,
|
|
'address_1' => $row->address_1,
|
|
'address_2' =>$row->address_2,
|
|
'city' => $row->city,
|
|
'state' => $row->state,
|
|
'postal_code'=>$row->postal_code,
|
|
];
|
|
}
|
|
} else {
|
|
$addresses = [
|
|
'billing' => [
|
|
'customer_name' => 'Customer not found',
|
|
'address_line' => 'Billing Address not found',
|
|
'city' => '',
|
|
'state' => '',
|
|
],
|
|
'shipping' => [
|
|
'customer_name' => 'Customer not found',
|
|
'address_line' => 'Shipping Address not found',
|
|
'city' => '',
|
|
'state' => '',
|
|
],
|
|
];
|
|
}
|
|
|
|
return $addresses;
|
|
}
|
|
public function getAllCustomerDetailsBySchemeName($schemeName)
|
|
{
|
|
// echo "addresses - Customer";
|
|
$currentDate = date('Y-m-d');
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('customers.customer_id, CONCAT(customers.first_name, " ", customers.last_name) as customer_name,customer_addresses.mobile_no, customer_addresses.address_1,customer_addresses.address_2, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code,business.title as business_name,business.address as business_address,business.city as business_city,business.state as business_state,business.postal_code as business_postal_code,books.title,from_subscription,to_subscription,business.title as business_name,business.mobile_no as business_mobile_no,states.state_name','subscription.membership_id');
|
|
$builder->join('customers', 'customers.customer_id = subscription.customer_id');
|
|
$builder->join('customer_addresses', 'customer_addresses.customer_id = customers.customer_id AND customer_addresses.address_type = 2','left');
|
|
$builder->join('business', 'business.business_id = subscription.business_id');
|
|
$builder->join('books', 'books.book_id = subscription.scheme_id');
|
|
$builder->join('states', 'states.state_short_name = customer_addresses.state');
|
|
$builder->where('books.title', $schemeName);
|
|
$builder->where('subscription.to_subscription >=', $currentDate);
|
|
// $builder->where('customer_addresses.address_type', 2); // Filter by address_type 2
|
|
$builder->orderBy('customers.customer_id', 'ASC'); // Order by customer_id to group by customers
|
|
|
|
// Fetch the query result
|
|
$query = $builder->get();
|
|
// $lastQuery = $this->db->getLastQuery();
|
|
// $finalQueryString = $lastQuery->getQuery();
|
|
// echo $finalQueryString;
|
|
|
|
$customerDetails = [];
|
|
|
|
if ($query->getNumRows() > 0) {
|
|
$currentCustomerID = null;
|
|
$shippingAddress = [];
|
|
|
|
foreach ($query->getResultArray() as $row) {
|
|
$customerID = $row['customer_id'];
|
|
|
|
if ($currentCustomerID !== $customerID) {
|
|
// New customer, add the previous customer's details (if any)
|
|
if (!empty($shippingAddress)) {
|
|
$customerDetails[] = $shippingAddress;
|
|
}
|
|
|
|
// Start a new customer's details
|
|
$shippingAddress = [
|
|
'customer_name' => $row['customer_name'],
|
|
'address_1' => $row['address_1'],
|
|
'address_2' => $row['address_2'],
|
|
'mobile_no'=>$row['mobile_no'],
|
|
'city' => $row['city'],
|
|
'state' => $row['state'],
|
|
'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
|
|
'postal_code' => $row['postal_code'],
|
|
'title' => $row['title'],
|
|
'to_subscription'=>$row['to_subscription'],
|
|
'company_name'=>$row['business_name'],
|
|
'company_mobile_no'=>$row['business_mobile_no'],
|
|
'company_address' => $row['business_address'],
|
|
'company_city' => $row['business_city'],
|
|
'company_state' => $row['business_state'],
|
|
'company_postal_code' => $row['business_postal_code'],
|
|
|
|
];
|
|
|
|
$currentCustomerID = $customerID;
|
|
}
|
|
}
|
|
|
|
// Add the last customer's details (if any)
|
|
if (!empty($shippingAddress)) {
|
|
$customerDetails[] = $shippingAddress;
|
|
}
|
|
}
|
|
|
|
return $customerDetails;
|
|
}
|
|
|
|
public function getAllExpiredCustomerDetailsByScheme($schemeName)
|
|
{
|
|
$currentDate = date('Y-m-d');
|
|
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('customers.customer_id, CONCAT(customers.first_name, " ", customers.last_name) as customer_name, customer_addresses.mobile_no, customer_addresses.address_1, customer_addresses.address_2, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code, business.title as business_name, books.title, from_subscription, to_subscription, business.mobile_no as business_mobile_no, states.state_name','subscription.membership_id');
|
|
$builder->join('customers', 'customers.customer_id = subscription.customer_id');
|
|
$builder->join('customer_addresses', 'customer_addresses.customer_id = customers.customer_id And customer_addresses.address_type = 2','left');
|
|
$builder->join('business', 'business.business_id = subscription.business_id');
|
|
$builder->join('books', 'books.book_id = subscription.scheme_id');
|
|
$builder->join('states', 'states.state_short_name = customer_addresses.state','left');
|
|
$builder->where('books.title', $schemeName);
|
|
$builder->where('subscription.to_subscription <', $currentDate); // Expired subscriptions only
|
|
// $builder->where('customer_addresses.address_type', 2); // Filter by address_type 2
|
|
$builder->orderBy('customers.customer_id', 'ASC'); // Order by customer_id to group by customers
|
|
$builder->groupBy('subscription.sub_id');
|
|
// Fetch the query result
|
|
$query = $builder->get();
|
|
|
|
$customerDetails = [];
|
|
|
|
if ($query->getNumRows() > 0) {
|
|
$currentCustomerID = null;
|
|
$shippingAddress = [];
|
|
|
|
foreach ($query->getResultArray() as $row) {
|
|
$customerID = $row['customer_id'];
|
|
|
|
if ($currentCustomerID !== $customerID) {
|
|
// New customer, add the previous customer's details (if any)
|
|
if (!empty($shippingAddress)) {
|
|
$customerDetails[] = $shippingAddress;
|
|
}
|
|
|
|
// Start a new customer's details
|
|
$shippingAddress = [
|
|
'customer_name' => $row['customer_name'],
|
|
'address_1' => $row['address_1'],
|
|
'address_2' => $row['address_2'],
|
|
'mobile_no' => $row['mobile_no'],
|
|
'city' => $row['city'],
|
|
'state' => $row['state'],
|
|
'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
|
|
'postal_code' => $row['postal_code'],
|
|
'title' => $row['title'],
|
|
'to_subscription' => $row['to_subscription'],
|
|
'company_name' => $row['business_name'],
|
|
'company_mobile_no' => $row['business_mobile_no'],
|
|
];
|
|
|
|
$currentCustomerID = $customerID;
|
|
}
|
|
}
|
|
|
|
// Add the last customer's details (if any)
|
|
if (!empty($shippingAddress)) {
|
|
$customerDetails[] = $shippingAddress;
|
|
}
|
|
}
|
|
|
|
return $customerDetails;
|
|
}
|
|
|
|
public function getQueryforSubscriptionDetailsBySchemeName($flag,$condition, $value,$selectedScheme){
|
|
|
|
$builder = $this->db->table('subscription as s');
|
|
|
|
$builder->select("
|
|
CONCAT_WS(' ', c.first_name, c.last_name) as customer_name,
|
|
c.customer_id, c.mobile_no, c.email, DATEDIFF(s.to_subscription, CURDATE()) AS remaining_days,
|
|
s.from_subscription, s.to_subscription, s.scheme_id, bs.title, s.membership_id,s.sub_id,bs.title,
|
|
i.invoice_id, i.invoice_date, i.shipping_address_id,
|
|
ca.customer_address_id, ca.address_1, ca.address_2, ca.city, ca.state, ca.postal_code, ca.address_type, st.state_name,
|
|
b.title as business_name, b.address as business_address, b.city as business_city,
|
|
b.state as business_state, b.postal_code as business_postal_code, b.mobile_no as business_mobile_no
|
|
");
|
|
|
|
$builder->join('customers as c', 's.customer_id = c.customer_id', 'left');
|
|
$builder->join('invoice as i', 's.invoice_id = i.invoice_id', 'left');
|
|
$builder->join('customer_addresses as ca', 'ca.customer_id = c.customer_id AND i.shipping_address_id = ca.customer_address_id AND ca.address_type = 2', 'left');//sir told to map the ID
|
|
// $builder->join('customer_addresses as ca', 'ca.customer_id = c.customer_id and ca.address_type = 2','left');
|
|
$builder->join('books as bs', 'bs.book_id = s.scheme_id', 'left');
|
|
$builder->join('states as st', 'st.state_short_name = ca.state', 'left');
|
|
$builder->join('business as b', 'b.business_id = s.business_id', 'left');
|
|
$builder->whereIn('bs.book_id', $selectedScheme);
|
|
if($flag){
|
|
$builder->where($condition, $value);
|
|
}
|
|
$builder->orderBy('s.to_subscription', 'asc');
|
|
$query = $builder->get();
|
|
$result = $query->getResultArray();
|
|
$lastQuery = $this->db->getLastQuery();
|
|
$finalQueryString = $lastQuery->getQuery();
|
|
// dd($result);
|
|
return $result;
|
|
}
|
|
public function getSubscriptionDetailsBySchemeName($selectedScheme, $downloadType){
|
|
|
|
$customerDetails = [];
|
|
$currentDate = date('Y-m-d');
|
|
|
|
if ($downloadType === 'active') {
|
|
$result1 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription >=', $currentDate,$selectedScheme);
|
|
$result2 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.isactive', 1,$selectedScheme);
|
|
$result3 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.from_subscription <=', $currentDate,$selectedScheme);
|
|
$approvedResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'i.status','Approved',$selectedScheme);
|
|
// Flatten the arrays for comparison
|
|
$flattened1 = array_map('serialize', $result1);
|
|
$flattened2 = array_map('serialize', $result2);
|
|
$flattened3 = array_map('serialize', $result3);
|
|
$flattened4 = array_map('serialize',$approvedResult);
|
|
|
|
// Find common elements
|
|
$commonElements0 = array_intersect($flattened1, $flattened2);
|
|
$commonElements1 = array_intersect($commonElements0, $flattened3);
|
|
$commonElements = array_intersect($commonElements1, $flattened4);
|
|
|
|
// Unserialize to get back the original array format
|
|
$result = array_map('unserialize', $commonElements);
|
|
|
|
}
|
|
if ($downloadType === 'expired') {
|
|
$expiredResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription <', $currentDate, $selectedScheme);
|
|
$activeResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription >=', $currentDate, $selectedScheme);
|
|
|
|
$activeCustomerIDs = array_column($activeResult, 'customer_id');
|
|
$filteredExpiredResult = array_filter($expiredResult, function($row) use ($activeCustomerIDs) {
|
|
return !in_array($row['customer_id'], $activeCustomerIDs);
|
|
});
|
|
|
|
$result = $filteredExpiredResult;
|
|
}
|
|
if ($downloadType === 'both') {
|
|
$result0 = $this->getQueryforSubscriptionDetailsBySchemeName(0,'','',$selectedScheme);
|
|
$result1 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.from_subscription <=', $currentDate,$selectedScheme);
|
|
$approvedResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'i.status','Approved',$selectedScheme);
|
|
|
|
|
|
|
|
$flattened1 = array_map('serialize', $result0);
|
|
$flattened2 = array_map('serialize', $result1);
|
|
$flattened3 = array_map('serialize', $approvedResult);
|
|
|
|
$commonElements0 = array_intersect($flattened1, $flattened2);
|
|
$commonElements = array_intersect($commonElements0, $flattened3);
|
|
|
|
$result = array_map('unserialize', $commonElements);
|
|
|
|
// dd($this->db->getLastQuery());
|
|
}
|
|
$customerDetails = [];
|
|
$processedCustomerIDs = [];
|
|
$subscriptionIDs = [];
|
|
|
|
|
|
// Process the results if not empty
|
|
if (!empty($result)) {
|
|
foreach ($result as $row) {
|
|
// Check if the customer ID has already been processed
|
|
// if (!in_array($row['customer_id'], $processedCustomerIDs)) { } // previous if condition
|
|
|
|
if (!in_array($row['sub_id'], $subscriptionIDs)) {
|
|
$customerDetails[] = [
|
|
'customer_id' => $row['customer_id'],
|
|
'invoice_id' => $row['invoice_id'],
|
|
'invoice_date' => $row['invoice_date'],
|
|
'customer_name' => $row['customer_name'],
|
|
'membership_id' =>$row['membership_id'],
|
|
'from_subscription' => $row['from_subscription'],
|
|
'address_1' => $row['address_1'],
|
|
'address_2' => $row['address_2'],
|
|
'mobile_no' => $row['mobile_no'],
|
|
'email' => $row['email'],
|
|
'city' => $row['city'],
|
|
'state' => $row['state'],
|
|
'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
|
|
'postal_code' => $row['postal_code'],
|
|
'title' => $row['title'],
|
|
'to_subscription' => $row['to_subscription'],
|
|
'company_name' => $row['business_name'],
|
|
'company_mobile_no' => $row['business_mobile_no'],
|
|
'company_address' => $row['business_address'],
|
|
'company_city' => $row['business_city'],
|
|
'company_state' => $row['business_state'],
|
|
'company_postal_code' => $row['business_postal_code'],
|
|
'membership_id' => $row['membership_id']
|
|
];
|
|
|
|
// Add the customer ID to the set of processed IDs
|
|
$processedCustomerIDs[] = $row['customer_id'];
|
|
}
|
|
}
|
|
|
|
usort($customerDetails, function($a, $b) {
|
|
return strtotime($a['invoice_date']) - strtotime($b['invoice_date']);
|
|
});
|
|
}
|
|
// echo $downloadType." == > ".count($customerDetails);die;
|
|
return $customerDetails;
|
|
}
|
|
|
|
|
|
public function getBooksByCategory($categoryName)
|
|
{
|
|
// $builder = $this->db->table('books');
|
|
// $builder->select('books.book_id, books.title');
|
|
// $builder->join('book_categories', 'books.book_id = book_categories.book_id', 'left');
|
|
// $builder->join('category', 'category.id = book_categories.category_id', 'left');
|
|
// $builder->where('LOWER(category.name)', strtolower($categoryName));
|
|
|
|
$builder = $this->db->table('books');
|
|
// $builder->select('GROUP_CONCAT(books.book_id ORDER BY books.book_id) AS book_ids');
|
|
$builder->select('GROUP_CONCAT(DISTINCT books.book_id ORDER BY books.book_id) AS book_ids');
|
|
$builder->select('books.short_code, books.title');
|
|
$builder->join('book_categories', 'books.book_id = book_categories.book_id', 'left');
|
|
$builder->join('category', 'category.id = book_categories.category_id', 'left');
|
|
$builder->join('invoiceitems', 'books.book_id = invoiceitems.product', 'left');
|
|
$builder->where('LOWER(category.name)', strtolower($categoryName));
|
|
$builder->where('invoiceitems.product IS NOT NULL', null, false);
|
|
$builder->groupBy('books.short_code');
|
|
|
|
$query = $builder->get();
|
|
return $query->getResult();
|
|
}
|
|
public function get_subscription_details_by_membership_id($membership_id)
|
|
{
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('subscription.from_subscription, subscription.to_subscription, subscription.scheme_id,books.price ,books.short_code');
|
|
$builder->join('books', 'subscription.scheme_id = books.book_id');
|
|
$builder->where('subscription.membership_id', $membership_id);
|
|
$builder->where('subscription.status',1);
|
|
$result = $builder->get()->getRowArray();
|
|
|
|
return $result;
|
|
}
|
|
public function check_existing_membership_id($customer_id){
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('membership_id');
|
|
$builder->where('customer_id', $customer_id);
|
|
$builder->where('isactive',1);
|
|
$builder->orderBy('sub_id', 'DESC');
|
|
$builder->limit(1);
|
|
$output = $builder->get()->getResult();
|
|
return $output;
|
|
}
|
|
|
|
public function updateData($data, $where)
|
|
{
|
|
$this->db->table('subscription')->update($data, $where);
|
|
$affected_rows = $this->db->affectedRows();
|
|
return $affected_rows;
|
|
}
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|