SHARE
TWEET

SQL Sample

josh401 Feb 12th, 2016 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. class Db_query extends CI_Model {
  4.  
  5.     function __construct() {
  6.        
  7.         // Call the Model constructor
  8.         parent::__construct();
  9.     }
  10.    
  11.     // **************************************
  12.     // Dashboard Tab
  13.     // **************************************
  14.    
  15.     // Get counts for admin panel dashboard
  16.     function get_counts_for_admin_dashboard() {
  17.        
  18.         $sql = "SELECT
  19.                 (SELECT COUNT(*) FROM admin_table) AS count_total_users,
  20.                 (SELECT COUNT(*) FROM admin_table WHERE user_status = 'active') AS count_active_users,
  21.                 (SELECT COUNT(*) FROM admin_table WHERE user_status = 'inactive') AS count_inactive_users,
  22.                
  23.                 (SELECT COUNT(*) FROM company_profile) AS count_total_companies,
  24.                 (SELECT COUNT(*) FROM company_profile WHERE status = 'active') AS count_total_active_companies,
  25.                 (SELECT COUNT(*) FROM company_profile WHERE status = 'holding') AS count_comps_held,
  26.                
  27.                 (SELECT COUNT(*) FROM product_profile) AS count_total_products,
  28.                 (SELECT COUNT(*) FROM product_profile WHERE prod_status = 'active') AS count_total_active_products,
  29.                 (SELECT COUNT(*) FROM product_profile WHERE prod_status = 'holding') AS count_prods_held"
  30.         ;
  31.         $query = $this->db->query($sql);
  32.        
  33.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  34.     }
  35.    
  36.     // Query companies with revisions for admin panel dashboard
  37.     function comps_awaiting_revisions() {
  38.        
  39.         $sql = "SELECT * FROM revisions WHERE prod_or_comp = 'comp' AND approved = 'N' GROUP BY prod_comp_id";
  40.         $query = $this->db->query($sql);
  41.        
  42.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  43.     }
  44.    
  45.     // Query products with revisions for admin panel dashboard
  46.     function prods_awaiting_revisions() {
  47.        
  48.         $sql = "SELECT * FROM revisions WHERE prod_or_comp = 'prod' AND approved = 'N' GROUP BY prod_comp_id";
  49.         $query = $this->db->query($sql);
  50.        
  51.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  52.     }
  53.    
  54.    
  55.    
  56.    
  57.     // Query all user profiles for awaiting gisr approval
  58.     function users_awaiting_approval() {
  59.        
  60.         $sql = "SELECT * FROM admin_table WHERE approved = 'N' OR user_status = 'inactive'";
  61.         $query = $this->db->query($sql);
  62.        
  63.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  64.     }
  65.    
  66.    
  67.    
  68.     // Query all companies
  69.     function query_all_comps() {
  70.        
  71.         $sql = "SELECT * FROM company_profile WHERE status = 'active' ORDER BY firm_name ASC";
  72.         $query = $this->db->query($sql);
  73.        
  74.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  75.     }
  76.    
  77.     // Query all products by company id
  78.     function query_all_prods_by_comp_id($comp_id = 0) {
  79.        
  80.         $sql = "SELECT * FROM product_profile WHERE company_id = '$comp_id' AND prod_status = 'active' ORDER BY product_name ASC";
  81.         $query = $this->db->query($sql);
  82.        
  83.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  84.     }
  85.    
  86.     // Query single company by company id
  87.     function query_single_comp_by_comp_id($comp_id = 0) {
  88.        
  89.         $sql = "SELECT * FROM company_profile WHERE company_id = '$comp_id' ORDER BY company_id desc LIMIT 0, 5000";
  90.         $query = $this->db->query($sql);
  91.        
  92.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  93.     }
  94.    
  95.    
  96.    
  97.    
  98.    
  99.     // Query all company profiles for awaiting gisr approval
  100.     function comps_awaiting_approval() {
  101.        
  102.         $sql = "SELECT company_id, firm_name FROM company_profile WHERE status = 'awaiting_approval'";
  103.         $query = $this->db->query($sql);
  104.        
  105.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  106.     }
  107.    
  108.     // Query all companies held by user
  109.     function comps_held_by_user() {
  110.        
  111.         $sql = "SELECT * FROM company_profile_held";
  112.         $query = $this->db->query($sql);
  113.        
  114.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  115.     }
  116.    
  117.     // Query deactivated companies
  118.     function deactivated_companies() {
  119.        
  120.         $sql = "SELECT * FROM company_profile WHERE status = 'inactive' ORDER BY firm_name";
  121.         $query = $this->db->query($sql);
  122.        
  123.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  124.     }
  125.    
  126.    
  127.    
  128.    
  129.     // Query all product profiles for awaiting gisr approval
  130.     function prods_awaiting_approval() {
  131.        
  132.         $sql = "SELECT product_id, company_id, product_name FROM product_profile WHERE prod_status = 'awaiting_approval'";
  133.         $query = $this->db->query($sql);
  134.        
  135.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  136.     }
  137.    
  138.     // Query all products held by user
  139.     function prods_held_by_user() {
  140.        
  141.         $sql = "SELECT * FROM product_profile_held";
  142.         $query = $this->db->query($sql);
  143.        
  144.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  145.     }
  146.    
  147.     // Query deactivated products
  148.     function deactivated_products() {
  149.        
  150.         $sql = "SELECT pp.product_name AS product_name, pp.product_id AS product_id, pp.company_id AS company_id, pp.prod_status_reason AS status_reason, cp.firm_name AS firm_name
  151.                 FROM product_profile pp
  152.                 LEFT JOIN company_profile cp ON pp.company_id = cp.company_id
  153.                 WHERE pp.prod_status = 'inactive' ORDER BY pp.product_name"
  154.         ;
  155.         $query = $this->db->query($sql);
  156.        
  157.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  158.     }
  159.    
  160.    
  161.    
  162.     // Query bookmarked companies
  163.     function bm_comps() {
  164.        
  165.         $sql = "SELECT * FROM admin_table";
  166.         $query = $this->Comman_crud->custom_query($sql);
  167.        
  168.         $count = 0;
  169.         foreach($query as $key => $value) {
  170.            
  171.             $array = isset($value->bm_comps) ? @unserialize($value->bm_comps) : array();
  172.             $count = !empty($array) ? $count + count($array) : $count;
  173.         }
  174.        
  175.         return $count;
  176.     }
  177.     // Query bookmarked products
  178.     function bm_prods() {
  179.        
  180.         $sql = "SELECT * FROM admin_table";
  181.         $query = $this->Comman_crud->custom_query($sql);
  182.        
  183.         $count = 0;
  184.         foreach($query as $key => $value) {
  185.            
  186.             $array = isset($value->bm_prods) ? @unserialize($value->bm_prods) : array();
  187.             $count = !empty($array) ? $count + count($array) : $count;
  188.         }
  189.        
  190.         return $count;
  191.     }
  192.     // Query bookmarked company comparisons
  193.     function bm_comps_compare() {
  194.        
  195.         $sql = "SELECT * FROM admin_table";
  196.         $query = $this->Comman_crud->custom_query($sql);
  197.        
  198.         $count = 0;
  199.         foreach($query as $key => $value) {
  200.            
  201.             $array = isset($value->bm_comps_compare) ? @unserialize($value->bm_comps_compare) : array();
  202.             $count = !empty($array) ? $count + count($array) : $count;
  203.         }
  204.        
  205.         return $count;
  206.     }
  207.     // Query bookmarked product comparisons
  208.     function bm_prods_compare() {
  209.        
  210.         $sql = "SELECT * FROM admin_table";
  211.         $query = $this->Comman_crud->custom_query($sql);
  212.        
  213.         $count = 0;
  214.         foreach($query as $key => $value) {
  215.            
  216.             $array = isset($value->bm_prods_compare) ? @unserialize($value->bm_prods_compare) : array();
  217.             $count = !empty($array) ? $count + count($array) : $count;
  218.         }
  219.        
  220.         return $count;
  221.     }
  222.     // Query bookmarked advanced searches
  223.     function bm_adv_search() {
  224.        
  225.         $sql = "SELECT * FROM admin_table";
  226.         $query = $this->Comman_crud->custom_query($sql);
  227.        
  228.         $count = 0;
  229.         foreach($query as $key => $value) {
  230.            
  231.             $array = isset($value->bm_adv_search) ? @unserialize($value->bm_adv_search) : array();
  232.             $count = !empty($array) ? $count + count($array) : $count;
  233.         }
  234.        
  235.         return $count;
  236.     }
  237.    
  238.    
  239.    
  240.    
  241.     // Query all company profiles for add/edit comps prods dropdown selector (if member)
  242.     function list_survey_user_assigned_comp_profile() {
  243.        
  244.         // Get user id
  245.         $user = $this->session->userdata('user');
  246.         $user_id = isset($user['id']) ? $user['id'] : '';
  247.        
  248.         $sql = "SELECT cp.* FROM company_profile cp LEFT JOIN admin_table at on cp.company_id = at.assigned_comp WHERE at.user_id = $user_id AND cp.status <> 'inactive' GROUP BY cp.firm_name";
  249.         $query = $this->db->query($sql);
  250.        
  251.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  252.     }
  253.    
  254.     // Query all company profiles for add/edit comps prods dropdown selector
  255.     function list_survey_admin_comp_profiles() {
  256.        
  257.         $sql = "SELECT * FROM company_profile WHERE 1 = 1 AND status <> 'inactive' ORDER BY firm_name asc, company_id desc LIMIT 0, 5000";
  258.         $query = $this->db->query($sql);
  259.        
  260.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  261.     }
  262.    
  263.     // Query all product profiles for add/edit comps prods dropdown selector
  264.     function list_survey_get_prod_profiles($company_id = 0) {
  265.        
  266.         $sql = "SELECT company_id, product_id, product_name FROM product_profile WHERE company_id = $company_id AND prod_status <> 'inactive' ORDER BY product_name asc, product_id desc LIMIT 0, 5000";
  267.         $query = $this->db->query($sql);
  268.        
  269.         // If no product found (or product is held); check product_profile_held db table
  270.         if($query->num_rows() == 0) {
  271.             $sql = "SELECT company_id, product_id, product_name FROM product_profile_held WHERE company_id = $company_id AND prod_status <> 'inactive' ORDER BY product_name asc, product_id desc LIMIT 0, 5000";
  272.             $query = $this->db->query($sql);
  273.         }
  274.        
  275.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  276.     }
  277.    
  278.     // Query all products by company for product at_a_glance summary page
  279.     function search_at_a_glace_product_summary($company_id = '', $product_family_id = '') {
  280.        
  281.         // STATEMENT REVISED: This was revised to remove the produt_family_prod_lookup table reference
  282.         $sql = "SELECT cp.*, prod.*, reseach_cp.firm_name AS hq_name, reseach_cp.headquarters AS hq_loc"
  283.              . " FROM product_profile prod"
  284.              . " LEFT JOIN company_profile cp ON prod.company_id = cp.company_id"
  285.              . " LEFT JOIN product_family_list pfl ON pfl.product_family_id = prod.product_family_id"
  286.              . " LEFT JOIN company_profile reseach_cp ON reseach_cp.company_id = pfl.research_company_id"
  287.              . " WHERE prod.prod_status <> 'inactive'"
  288.              . " AND cp.company_id = " . $company_id
  289.              . " AND prod.product_family_id = ". $product_family_id
  290.              . " ORDER BY prod.product_name ASC"
  291.         ;
  292.         $query = $this->db->query($sql);
  293.        
  294.         if ($query->num_rows() > 0) { $output = $query->result(); $query->free_result(); return $output; } else { return false; }
  295.     }
  296. }
RAW Paste Data
Top