��ѧۧݧ�ӧ�� ާ֧ߧ֧էا֧�

��ѧۧݧ�ӧ�� ާ֧ߧ֧էا֧� - ��֧էѧܧ�ڧ��ӧѧ�� - /home/rickpfrv/clients.dominiquerickard.com/app/Models/Clients_model.php

��ѧ٧ѧ�
<?php namespace App\Models; class Clients_model extends Crud_model { protected $table = null; function __construct() { $this->table = 'clients'; parent::__construct($this->table); } function get_details($options = array()) { $clients_table = $this->db->prefixTable('clients'); $projects_table = $this->db->prefixTable('projects'); $users_table = $this->db->prefixTable('users'); $invoices_table = $this->db->prefixTable('invoices'); $invoice_payments_table = $this->db->prefixTable('invoice_payments'); $invoice_items_table = $this->db->prefixTable('invoice_items'); $taxes_table = $this->db->prefixTable('taxes'); $client_groups_table = $this->db->prefixTable('client_groups'); $lead_status_table = $this->db->prefixTable('lead_status'); $where = ""; $id = get_array_value($options, "id"); if ($id) { $where .= " AND $clients_table.id=$id"; } $custom_field_type = "clients"; $leads_only = get_array_value($options, "leads_only"); if ($leads_only) { $custom_field_type = "leads"; $where .= " AND $clients_table.is_lead=1"; } $status = get_array_value($options, "status"); if ($status) { $where .= " AND $clients_table.lead_status_id='$status'"; } $source = get_array_value($options, "source"); if ($source) { $where .= " AND $clients_table.lead_source_id='$source'"; } $owner_id = get_array_value($options, "owner_id"); if ($owner_id) { $where .= " AND $clients_table.owner_id=$owner_id"; } $created_by = get_array_value($options, "created_by"); if ($created_by) { $where .= " AND $clients_table.created_by=$created_by"; } $show_own_clients_only_user_id = get_array_value($options, "show_own_clients_only_user_id"); if ($show_own_clients_only_user_id) { $where .= " AND ($clients_table.created_by=$show_own_clients_only_user_id OR $clients_table.owner_id=$show_own_clients_only_user_id)"; } if (!$id && !$leads_only) { //only clients $where .= " AND $clients_table.is_lead=0"; } $group_id = get_array_value($options, "group_id"); if ($group_id) { $where .= " AND FIND_IN_SET('$group_id', $clients_table.group_ids)"; } $quick_filter = get_array_value($options, "quick_filter"); if ($quick_filter) { $where .= $this->make_quick_filter_query($quick_filter, $clients_table, $projects_table, $invoices_table, $taxes_table, $invoice_payments_table, $invoice_items_table); } //prepare custom fild binding query $custom_fields = get_array_value($options, "custom_fields"); $custom_field_query_info = $this->prepare_custom_field_query_string($custom_field_type, $custom_fields, $clients_table); $select_custom_fieds = get_array_value($custom_field_query_info, "select_string"); $join_custom_fieds = get_array_value($custom_field_query_info, "join_string"); $invoice_value_calculation_query = "(SUM" . $this->_get_invoice_value_calculation_query($invoices_table) . ")"; $this->db->query('SET SQL_BIG_SELECTS=1'); $invoice_value_select = "IFNULL(invoice_details.invoice_value,0)"; $payment_value_select = "IFNULL(invoice_details.payment_received,0)"; $sql = "SELECT $clients_table., CONCAT($users_table.first_name, ' ', $users_table.last_name) AS primary_contact, $users_table.id AS primary_contact_id, $users_table.image AS contact_avatar, project_table.total_projects, $payment_value_select AS payment_received $select_custom_fieds, IF((($invoice_value_select > $payment_value_select) AND ($invoice_value_select - $payment_value_select) <0.05), $payment_value_select, $invoice_value_select) AS invoice_value, (SELECT GROUP_CONCAT($client_groups_table.title) FROM $client_groups_table WHERE FIND_IN_SET($client_groups_table.id, $clients_table.group_ids)) AS client_groups, $lead_status_table.title AS lead_status_title, $lead_status_table.color AS lead_status_color, owner_details.owner_name, owner_details.owner_avatar FROM $clients_table LEFT JOIN $users_table ON $users_table.client_id = $clients_table.id AND $users_table.deleted=0 AND $users_table.is_primary_contact=1 LEFT JOIN (SELECT client_id, COUNT(id) AS total_projects FROM $projects_table WHERE deleted=0 GROUP BY client_id) AS project_table ON project_table.client_id= $clients_table.id LEFT JOIN (SELECT client_id, SUM(payments_table.payment_received) as payment_received, $invoice_value_calculation_query as invoice_value FROM $invoices_table LEFT JOIN (SELECT $taxes_table. FROM $taxes_table) AS tax_table ON tax_table.id = $invoices_table.tax_id LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table2 ON tax_table2.id = $invoices_table.tax_id2 LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table3 ON tax_table3.id = $invoices_table.tax_id3 LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id=$invoices_table.id AND $invoices_table.deleted=0 AND $invoices_table.status='not_paid' LEFT JOIN (SELECT invoice_id, SUM(total) AS invoice_value FROM $invoice_items_table WHERE deleted=0 GROUP BY invoice_id) AS items_table ON items_table.invoice_id=$invoices_table.id AND $invoices_table.deleted=0 AND $invoices_table.status='not_paid' WHERE $invoices_table.status='not_paid' GROUP BY $invoices_table.client_id ) AS invoice_details ON invoice_details.client_id= $clients_table.id LEFT JOIN $lead_status_table ON $clients_table.lead_status_id = $lead_status_table.id LEFT JOIN (SELECT $users_table.id, CONCAT($users_table.first_name, ' ', $users_table.last_name) AS owner_name, $users_table.image AS owner_avatar FROM $users_table WHERE $users_table.deleted=0 AND $users_table.user_type='staff') AS owner_details ON owner_details.id=$clients_table.owner_id $join_custom_fieds WHERE $clients_table.deleted=0 $where"; return $this->db->query($sql); } private function make_quick_filter_query($filter, $clients_table, $projects_table, $invoices_table, $taxes_table, $invoice_payments_table, $invoice_items_table) { $query = ""; if ($filter == "has_open_projects" \|\| $filter == "has_completed_projects" \|\| $filter == "has_any_hold_projects") { $status = "open"; if ($filter == "has_completed_projects") { $status = "completed"; } else if ($filter == "has_any_hold_projects") { $status = "hold"; } $query = " AND $clients_table.id IN(SELECT $projects_table.client_id FROM $projects_table WHERE $projects_table.deleted=0 AND $projects_table.status='$status') "; } else if ($filter == "has_unpaid_invoices" \|\| $filter == "has_overdue_invoices" \|\| $filter == "has_partially_paid_invoices") { $now = get_my_local_time("Y-m-d"); $invoice_value_calculation_query = $this->_get_invoice_value_calculation_query($invoices_table); $invoice_value_calculation = "TRUNCATE($invoice_value_calculation_query,2)"; $invoice_where = " AND $invoices_table.status !='draft' AND $invoices_table.status!='cancelled' AND IFNULL(payments_table.payment_received,0)<=0"; if ($filter == "has_overdue_invoices") { $invoice_where = " AND $invoices_table.status !='draft' AND $invoices_table.status!='cancelled' AND $invoices_table.due_date<'$now' AND TRUNCATE(IFNULL(payments_table.payment_received,0),2)<$invoice_value_calculation"; } else if ($filter == "has_partially_paid_invoices") { $invoice_where = " AND IFNULL(payments_table.payment_received,0)>0 AND IFNULL(payments_table.payment_received,0)<$invoice_value_calculation"; } $query = " AND $clients_table.id IN( SELECT $invoices_table.client_id FROM $invoices_table LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table ON tax_table.id = $invoices_table.tax_id LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table2 ON tax_table2.id = $invoices_table.tax_id2 LEFT JOIN (SELECT $taxes_table.* FROM $taxes_table) AS tax_table3 ON tax_table3.id = $invoices_table.tax_id3 LEFT JOIN (SELECT invoice_id, SUM(amount) AS payment_received FROM $invoice_payments_table WHERE deleted=0 GROUP BY invoice_id) AS payments_table ON payments_table.invoice_id = $invoices_table.id LEFT JOIN (SELECT invoice_id, SUM(total) AS invoice_value FROM $invoice_items_table WHERE deleted=0 GROUP BY invoice_id) AS items_table ON items_table.invoice_id = $invoices_table.id WHERE $invoices_table.deleted=0 $invoice_where ) "; } return $query; } function get_primary_contact($client_id = 0, $info = false) { $users_table = $this->db->prefixTable('users'); $sql = "SELECT $users_table.id, $users_table.first_name, $users_table.last_name FROM $users_table WHERE $users_table.deleted=0 AND $users_table.client_id=$client_id AND $users_table.is_primary_contact=1"; $result = $this->db->query($sql); if ($result->resultID->num_rows) { if ($info) { return $result->getRow(); } else { return $result->getRow()->id; } } } function add_remove_star($project_id, $user_id, $type = "add") { $clients_table = $this->db->prefixTable('clients'); $action = " CONCAT($clients_table.starred_by,',',':$user_id:') "; $where = " AND FIND_IN_SET(':$user_id:',$clients_table.starred_by) = 0"; //don't add duplicate if ($type != "add") { $action = " REPLACE($clients_table.starred_by, ',:$user_id:', '') "; $where = ""; } $sql = "UPDATE $clients_table SET $clients_table.starred_by = $action WHERE $clients_table.id=$project_id $where"; return $this->db->query($sql); } function get_starred_clients($user_id) { $clients_table = $this->db->prefixTable('clients'); $sql = "SELECT $clients_table.id, $clients_table.company_name FROM $clients_table WHERE $clients_table.deleted=0 AND FIND_IN_SET(':$user_id:',$clients_table.starred_by) ORDER BY $clients_table.company_name ASC"; return $this->db->query($sql); } function delete_client_and_sub_items($client_id) { $clients_table = $this->db->prefixTable('clients'); $general_files_table = $this->db->prefixTable('general_files'); $users_table = $this->db->prefixTable('users'); //get client files info to delete the files from directory $client_files_sql = "SELECT * FROM $general_files_table WHERE $general_files_table.deleted=0 AND $general_files_table.client_id=$client_id; "; $client_files = $this->db->query($client_files_sql)->getResult(); //delete the client and sub items //delete client $delete_client_sql = "UPDATE $clients_table SET $clients_table.deleted=1 WHERE $clients_table.id=$client_id; "; $this->db->query($delete_client_sql); //delete contacts $delete_contacts_sql = "UPDATE $users_table SET $users_table.deleted=1 WHERE $users_table.client_id=$client_id; "; $this->db->query($delete_contacts_sql); //delete the project files from directory $file_path = get_general_file_path("client", $client_id); foreach ($client_files as $file) { delete_app_files($file_path, array(make_array_of_file($file))); } return true; } function is_duplicate_company_name($company_name, $id = 0) { $result = $this->get_all_where(array("company_name" => $company_name, "is_lead" => 0, "deleted" => 0)); if (count($result->getResult()) && $result->getRow()->id != $id) { return $result->getRow(); } else { return false; } } function get_leads_kanban_details($options = array()) { $clients_table = $this->db->prefixTable('clients'); $lead_source_table = $this->db->prefixTable('lead_source'); $users_table = $this->db->prefixTable('users'); $events_table = $this->db->prefixTable('events'); $notes_table = $this->db->prefixTable('notes'); $estimates_table = $this->db->prefixTable('estimates'); $general_files_table = $this->db->prefixTable('general_files'); $estimate_requests_table = $this->db->prefixTable('estimate_requests'); $where = ""; $status = get_array_value($options, "status"); if ($status) { $where .= " AND $clients_table.lead_status_id='$status'"; } $owner_id = get_array_value($options, "owner_id"); if ($owner_id) { $where .= " AND $clients_table.owner_id='$owner_id'"; } $source = get_array_value($options, "source"); if ($source) { $where .= " AND $clients_table.lead_source_id='$source'"; } $search = get_array_value($options, "search"); if ($search) { $search = $this->db->escapeString($search); $where .= " AND $clients_table.company_name LIKE '%$search%'"; } $users_where = "$users_table.client_id=$clients_table.id AND $users_table.deleted=0 AND $users_table.user_type='lead'"; $this->db->query('SET SQL_BIG_SELECTS=1'); $sql = "SELECT $clients_table.id, $clients_table.company_name, $clients_table.sort, IF($clients_table.sort!=0, $clients_table.sort, $clients_table.id) AS new_sort, $clients_table.lead_status_id, $clients_table.owner_id, (SELECT $users_table.image FROM $users_table WHERE $users_where AND $users_table.is_primary_contact=1) AS primary_contact_avatar, (SELECT COUNT($users_table.id) FROM $users_table WHERE $users_where) AS total_contacts_count, (SELECT COUNT($events_table.id) FROM $events_table WHERE $events_table.deleted=0 AND $events_table.client_id=$clients_table.id) AS total_events_count, (SELECT COUNT($notes_table.id) FROM $notes_table WHERE $notes_table.deleted=0 AND $notes_table.client_id=$clients_table.id) AS total_notes_count, (SELECT COUNT($estimates_table.id) FROM $estimates_table WHERE $estimates_table.deleted=0 AND $estimates_table.client_id=$clients_table.id) AS total_estimates_count, (SELECT COUNT($general_files_table.id) FROM $general_files_table WHERE $general_files_table.deleted=0 AND $general_files_table.client_id=$clients_table.id) AS total_files_count, (SELECT COUNT($estimate_requests_table.id) FROM $estimate_requests_table WHERE $estimate_requests_table.deleted=0 AND $estimate_requests_table.client_id=$clients_table.id) AS total_estimate_requests_count, $lead_source_table.title AS lead_source_title, CONCAT($users_table.first_name, ' ', $users_table.last_name) AS owner_name FROM $clients_table LEFT JOIN $lead_source_table ON $clients_table.lead_source_id = $lead_source_table.id LEFT JOIN $users_table ON $users_table.id = $clients_table.owner_id AND $users_table.deleted=0 AND $users_table.user_type='staff' WHERE $clients_table.deleted=0 AND $clients_table.is_lead=1 $where ORDER BY new_sort ASC"; return $this->db->query($sql); } function get_search_suggestion($search = "", $options = array()) { $clients_table = $this->db->prefixTable('clients'); $where = ""; $show_own_clients_only_user_id = get_array_value($options, "show_own_clients_only_user_id"); if ($show_own_clients_only_user_id) { $where .= " AND ($clients_table.created_by=$show_own_clients_only_user_id OR $clients_table.owner_id=$show_own_clients_only_user_id)"; } $search = $this->db->escapeString($search); $sql = "SELECT $clients_table.id, $clients_table.company_name AS title FROM $clients_table WHERE $clients_table.deleted=0 AND $clients_table.is_lead=0 AND $clients_table.company_name LIKE '%$search%' $where ORDER BY $clients_table.company_name ASC LIMIT 0, 10"; return $this->db->query($sql); } function count_total_clients($show_own_clients_only_user_id = "") { $clients_table = $this->db->prefixTable('clients'); $where = ""; if ($show_own_clients_only_user_id) { $where .= " AND $clients_table.created_by=$show_own_clients_only_user_id"; } $sql = "SELECT COUNT($clients_table.id) AS total FROM $clients_table WHERE $clients_table.deleted=0 AND $clients_table.is_lead=0 $where"; return $this->db->query($sql)->getRow()->total; } }

| ver. 1.4 | Github | . | PHP 7.4.33 | ��֧ߧ֧�ѧ�ڧ� ��ѧߧڧ��: 0.1 | proxy | phpinfo | ��ѧ��ۧܧ�