298 lines
13 KiB
PHP
Executable File
298 lines
13 KiB
PHP
Executable File
<?php
|
|
|
|
namespace App\Models;
|
|
|
|
use CodeIgniter\Model;
|
|
|
|
|
|
class CustomerModel extends Model
|
|
{
|
|
protected $table = 'customers';
|
|
protected $primaryKey = 'customer_id ';
|
|
// protected $allowedFields = ['customer_id ','first_name','city','last_name','email','mobile_no','country','state','postal_code' ,'address','profile_picture','date_of_birth','gender','mode','isactive','business_id'];
|
|
protected $allowedFields = ['customer_id ','first_name','city','last_name','email','mobile_no','country','state','postal_code' ,'address','profile_picture','date_of_birth','gender','mode','type','isactive','business_id','created_by','updated_by'];
|
|
|
|
public function insertCustomer($data)
|
|
{
|
|
return $this->insert($data);
|
|
}
|
|
public function getCustomerNamesByBusiness($business_id)
|
|
{
|
|
$this->builder()->select('customer_id,CONCAT_WS(" ", first_name, last_name) as full_name', false);
|
|
$this->builder()->where('business_id ', $business_id); // Filter by business_id
|
|
$query = $this->builder()->get(); // Use findAll() instead of get()
|
|
return ($query->getResult());
|
|
}
|
|
|
|
// $customerNames = [];
|
|
|
|
// foreach ($query->getResult() as $row) {
|
|
// $customerNames[] = [ $row->full_name ];
|
|
// }
|
|
// print_r($customerNames);
|
|
// die();
|
|
|
|
|
|
|
|
// public function getCustomerIdByName($customerName, $businessId)
|
|
// {
|
|
// $customer = $this->builder()
|
|
// ->select('customer_id')
|
|
// ->where('CONCAT(first_name, " ", last_name)', $customerName)
|
|
// ->where('business_id', $businessId)
|
|
// ->get()
|
|
// ->getRow();
|
|
|
|
// if ($customer) {
|
|
// return $customer->customer_id;
|
|
// }
|
|
|
|
// return null; // Customer not found
|
|
// }
|
|
|
|
|
|
|
|
|
|
|
|
public function insertAddress($addressData) {
|
|
$this->db->table('customer_addresses')->insert($addressData);
|
|
return $this->db->insertID(); // Return the last inserted ID
|
|
}
|
|
|
|
public function updateAddress($id, $addressData, $addressType) {
|
|
|
|
|
|
if ($addressType == 'b') {
|
|
$addressTypeValue = 1;
|
|
} else {
|
|
$addressTypeValue = 2;
|
|
}
|
|
$isActive = 1;
|
|
|
|
// Check if an active address of the specified type already exists for the customer
|
|
$existingAddress = $this->db->table('customer_addresses')
|
|
->where('customer_id', $id)
|
|
->where('isactive', $isActive)
|
|
->where('address_type', $addressTypeValue)
|
|
->get()
|
|
->getRow();
|
|
|
|
// If an active address of the specified type exists, update it
|
|
if ($existingAddress) {
|
|
$this->db->table('customer_addresses')
|
|
->where('customer_id', $id)
|
|
->where('address_type', $addressTypeValue)
|
|
->update($addressData);
|
|
return $id; // Return the customer ID
|
|
} else {
|
|
// If no active address of the specified type exists, insert a new one
|
|
$addressData['customer_id'] = $id;
|
|
$addressData['address_type'] = $addressTypeValue;
|
|
$addressData['isactive'] = $isActive;
|
|
$this->db->table('customer_addresses')->insert($addressData);
|
|
return $this->db->insertID(); // Return the last inserted ID
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public function insertAddressBatch($addressesDataArray) {
|
|
$this->db->table('customer_addresses')->insertBatch($addressesDataArray);
|
|
return $this->db->insertID(); // Note: insertID() might not be applicable for batch inserts
|
|
}
|
|
|
|
public function saveAddressDetails($data){
|
|
$i = 0;
|
|
$statement = [];
|
|
foreach ($data as $row) {
|
|
$id = $row['customer_address_id'];
|
|
if($id != ''){
|
|
unset($row['customer_address_id']); // Remove the id from the data to avoid updating it
|
|
unset($row['created_by']); // bcoz here data Updating here.
|
|
$this->db->table('customer_addresses')->where('customer_address_id', $id)->update($row);// Update the row with the specified id
|
|
$affectedRows = $this->db->affectedRows();
|
|
$statement[$i] = "address - ".$id." ". ($affectedRows ? " Updated":" Nothing to Updated");
|
|
}else{
|
|
$this->db->table('customer_addresses')->insert($row);
|
|
$insertID = $this->db->insertID();
|
|
$statement[$i] = "address - ".$insertID." Inserted";
|
|
}
|
|
$i++;
|
|
}
|
|
return $statement;
|
|
}
|
|
|
|
|
|
public function inactiveMissingAddressDetails($where,$missingValues){
|
|
$dataToUpdate = ['isactive' => 0];
|
|
$this->db->table('customer_addresses')->where($where)->whereIn('customer_address_id',$missingValues)->update($dataToUpdate);
|
|
}
|
|
public function getInvoicesByCustomerId($customer_id)
|
|
{
|
|
return $this->db->table('invoice')
|
|
->select('invoice_number, subtotal,total_amount, invoice_date,DATE_FORMAT(invoice_date, "%d/%m/%Y") AS formatted_invoice_date,invoice_type')
|
|
->where('customer_id', $customer_id)
|
|
->where('invoice.invoice_type = "1"')
|
|
->get()
|
|
->getResult();
|
|
// print_r($this->db->getLastQuery());die;
|
|
}
|
|
// public function getSubscriberDataByCustomerId($customerId)
|
|
// {
|
|
// // Default invoice type is 2 (subscription).
|
|
// $where = ['subscription.customer_id'=> $customerId,'invoice.invoice_type' => 2,'subscription.isactive' => 1];
|
|
// // Assuming 'subscription' is the table name where subscriber data is stored.
|
|
// $builder = $this->db->table('subscription');
|
|
// $builder->join('books', 'books.book_id = subscription.scheme_id');
|
|
// $builder->join('invoice', 'subscription.customer_id = invoice.customer_id', 'left');
|
|
// // Define the columns you want to select from the subscription table.
|
|
// $builder->select('subscription.*,books.title,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_subscription,DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_subscription,DATEDIFF(subscription.to_subscription, subscription.from_subscription) as subscription_in_days');
|
|
// // Add a where condition to filter results based on customer ID.
|
|
// $builder->where($where);
|
|
// $builder->groupBy('subscription.sub_id');
|
|
// // Execute the query and return the result as an array of objects.
|
|
// return $builder->get()->getResult();
|
|
// }
|
|
|
|
public function getSubscriberDataByCustomerId($customerId)
|
|
{
|
|
// Default invoice type is 2 (subscription).
|
|
$where = ['subscription.customer_id'=> $customerId,'invoice.invoice_type' => 2];
|
|
$builder = $this->db->table('subscription');
|
|
$builder->select('subscription.sub_id,subscription.scheme_id,subscription.customer_id,subscription.invoice_id,subscription.isactive,invoice.invoice_number,invoice.status,books.title, subscription.from_subscription,subscription.to_subscription,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_subscription, DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_subscription, DATEDIFF(subscription.to_subscription, subscription.from_subscription) as subscription_in_days');
|
|
$builder->join('books', 'books.book_id = subscription.scheme_id', 'left');
|
|
$builder->join('invoice', 'subscription.invoice_id = invoice.invoice_id', 'left');
|
|
$builder->where($where);
|
|
// $builder->groupBy('subscription.sub_id');
|
|
return $builder->get()->getResult();
|
|
// print_r($this->db->getLastQuery());die;
|
|
}
|
|
|
|
public function saveGroupDetails($data){
|
|
$id = $data['group_id'];
|
|
if($id != ''){
|
|
unset($data['group_id']); // Remove the id from the data to avoid updating it
|
|
unset($data['created_by']); // bcoz here data Updating here.
|
|
if ($this->db->table('customer_groups')->where('group_id', $id)->update($data)) {
|
|
$affectedRows = $this->db->affectedRows();
|
|
$statement['success'] = 'Customer Group has been updated successfully.';
|
|
$statement['error'] = "";
|
|
$statement['log'] = "Customer Group: has been updated successfully. Updated Customer Group ID = " .$id." Affected Rows = ".$affectedRows;
|
|
} else {
|
|
$statement['success'] = "";
|
|
$statement['error'] = 'Customer group update failed. Please try again.';
|
|
$statement['log'] = "Customer Group: Err Failed to update ID = " .$id ;
|
|
}
|
|
}else{
|
|
unset($data['updated_by']);
|
|
if ($this->db->table('customer_groups')->insert($data)) {
|
|
$insertID = $this->db->insertID();
|
|
$statement['success'] = 'Customer Group has been Inserted successfully.';
|
|
$statement['error'] = "";
|
|
$statement['log'] = "Customer Group : has been added successfully. Inserted ID = " .$insertID ;
|
|
} else {
|
|
$statement['success'] = "";
|
|
$statement['error'] = "Customer could not be added. Please try again..";
|
|
$statement['log'] = "Customer: Err could not be added. Please try again.";
|
|
}
|
|
}
|
|
return $statement;
|
|
}
|
|
|
|
public function getGroupDetails()
|
|
{
|
|
return $this->db->table('customer_groups as CG' )
|
|
->join('users as U1', 'U1.user_id = CG.created_by', 'left')
|
|
->join('users as U2', 'U2.user_id = CG.updated_by', 'left')
|
|
->select('CG.group_id,CG.group_name,CG.created_on,CG.created_by,CG.updated_on,CG.updated_by,DATE_FORMAT(CG.created_on, "%d/%m/%Y %h:%i %p") AS formatted_created_on,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name,CG.isactive')
|
|
->where(['CG.group_name != ' => 'EXPIRYDATE'])
|
|
// ->where(['CG.isactive' => 1])
|
|
->get()
|
|
->getResultArray();
|
|
}
|
|
|
|
public function storeCustomerAddresses($billingAddress, $shippingAddress)
|
|
{
|
|
// $this->db->transStart(); // Begin transaction
|
|
|
|
// Insert billing address
|
|
if(!empty($billingAddress['billing_address1'])){
|
|
$billingData = [
|
|
'customer_id' => $billingAddress['customer_id'],
|
|
'first_name'=> $billingAddress['first_name'],
|
|
'last_name'=> $billingAddress['last_name'],
|
|
'address_1' => $billingAddress['billing_address1'],
|
|
'address_2' => $billingAddress['billing_address2'],
|
|
'city' => $billingAddress['billing_city'],
|
|
'state' => $billingAddress['billing_state'],
|
|
'country' => $billingAddress['billing_country'],
|
|
'postal_code' => $billingAddress['billing_pincode'],
|
|
'address_type' => 1 // Billing address type
|
|
];
|
|
$this->db->table('customer_addresses')->insert($billingData);
|
|
$billing_addr_id = $this->db->insertID();
|
|
}else{
|
|
$billing_addr_id = NULL;
|
|
}
|
|
|
|
if(!empty($shippingAddress['shipping_address1'])){
|
|
// Insert shipping address
|
|
$shippingData = [
|
|
'customer_id' => $shippingAddress['customer_id'],
|
|
'first_name'=> $shippingAddress['first_name'],
|
|
'last_name'=> $shippingAddress['last_name'],
|
|
'address_1' => $shippingAddress['shipping_address1'],
|
|
'address_2' => $shippingAddress['shipping_address2'],
|
|
'city' => $shippingAddress['shipping_city'],
|
|
'state' => $shippingAddress['shipping_state'],
|
|
'country' => $shippingAddress['shipping_country'],
|
|
'postal_code' => $shippingAddress['shipping_pincode'],
|
|
'address_type' => 2 // Shipping address type
|
|
];
|
|
$this->db->table('customer_addresses')->insert($shippingData);
|
|
$shipping_addr_id = $this->db->insertID();
|
|
}else{
|
|
$shipping_addr_id = NULL;
|
|
}
|
|
return ["billing_addr_id"=>$billing_addr_id,"shipping_addr_id"=>$shipping_addr_id]; // Return true indicating successful insertion
|
|
}
|
|
public function getType(){
|
|
return $this->db->table('customers as C' )
|
|
->select('C.type as value')
|
|
->groupBy('C.type')
|
|
->get()
|
|
->getResultArray();
|
|
}
|
|
public function getMode(){
|
|
return $this->db->table('customers as C' )
|
|
->select('C.mode as value')
|
|
->where(['C.mode != ' => ''])
|
|
->groupBy('C.mode')
|
|
->get()
|
|
->getResultArray();
|
|
}
|
|
public function getCategory(){
|
|
return $this->db->table('category as C' )
|
|
->select('C.name as value')
|
|
->where(['C.name != ' => '','C.isactive' => 1])
|
|
->groupBy('C.name')
|
|
->get()
|
|
->getResultArray();
|
|
}
|
|
|
|
|
|
public function get_customer_details_by_customer_id($customer_id)
|
|
{
|
|
$builder = $this->db->table('customers'); // Specify the main table
|
|
$builder->select('customers.customer_id, customers.mobile_no,customers.first_name, customers.last_name,customers.mobile_no,customer_addresses.address_1, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code, customer_addresses.country',customer);
|
|
$builder->join('customer_addresses', 'customers.customer_id = customer_addresses.customer_id');
|
|
$builder->where('customers.customer_id', $customer_id);
|
|
|
|
$result = $builder->get()->getRowArray(); // Fetch the first matching record as an associative array
|
|
|
|
return $result;
|
|
}
|
|
}
|
|
?>
|