<?php
namespace App\Library;
use Illuminate\Support\Facades\Validator;
use Illuminate\Http\Request;
use DB;

class SalesforceAdminConsole
{
    //Create Customer
    public function salesforceTableDetail()
    {
        $query = "SELECT
        pg_class.relname AS table_name,
        pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS size,
        pg_total_relation_size(pg_class.oid) / (current_setting('block_size')::integer / 1024) AS num_blocks,
        pg_stat_user_tables.n_live_tup AS num_rows
        FROM
        pg_stat_user_tables
        JOIN
        pg_class ON pg_stat_user_tables.relid = pg_class.oid
        WHERE
        pg_class.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
        ORDER BY
        pg_total_relation_size(pg_class.oid) DESC";
        $result=DB::connection('pgsql')->SELECT($query);
        return $this->createTable($result);
    }
    //Create Customer
    public function salesforceTableRowCount()
    {
        $query="select  'tbl_beat_plan_retailers', count(*) from tbl_beat_plan_retailers union
        select  'tbl_customer', count(*) from tbl_customer union
        select  'tbl_contact', count(*) from tbl_contact union
        select  'tbl_collection', count(*) from tbl_collection union
        select  'tbl_company_bank_detail', count(*) from tbl_company_bank_detail union
        select  'tbl_company_profile', count(*) from tbl_company_profile union
        select  'tbl_countries', count(*) from tbl_countries union
        select  'tbl_customer_type', count(*) from tbl_customer_type union
        select  'tbl_delivery_method', count(*) from tbl_delivery_method union
        select  'tbl_customer_file', count(*) from tbl_customer_file union
        select  'failed_jobs', count(*) from failed_jobs union
        select  'migrations', count(*) from migrations union
        select  'password_resets', count(*) from password_resets union
        select  'personal_access_tokens', count(*) from personal_access_tokens union
        select  'tbl_user_auth', count(*) from tbl_user_auth union
        select  'tbl_admin', count(*) from tbl_admin union
        select  'tbl_expense', count(*) from tbl_expense union
        select  'tbl_departments', count(*) from tbl_departments union
        select  'tbl_expense_type', count(*) from tbl_expense_type union
        select  'tbl_expense_type_travel', count(*) from tbl_expense_type_travel union
        select  'tbl_activity', count(*) from tbl_activity union
        select  'tbl_announcement', count(*) from tbl_announcement union
        select  'tbl_beat_plan_customers', count(*) from tbl_beat_plan_customers union
        select  'tbl_gst_slab', count(*) from tbl_gst_slab union
        select  'tbl_leave_type', count(*) from tbl_leave_type union
        select  'tbl_module_access', count(*) from tbl_module_access union
        select  'tbl_module_feature_access', count(*) from tbl_module_feature_access union
        select  'tbl_modules', count(*) from tbl_modules union
        select  'tbl_notes', count(*) from tbl_notes union
        select  'tbl_order', count(*) from tbl_order union
        select  'tbl_leave', count(*) from tbl_leave union
        select  'tbl_order_status', count(*) from tbl_order_status union
        select  'tbl_payment_terms', count(*) from tbl_payment_terms union
        select  'tbl_order_item', count(*) from tbl_order_item union
        select  'tbl_payment_type', count(*) from tbl_payment_type union
        select  'tbl_price_book', count(*) from tbl_price_book union
        select  'tbl_performance_line_item', count(*) from tbl_performance_line_item union
        select  'tbl_price_book_entry', count(*) from tbl_price_book_entry union
        select  'tbl_products', count(*) from tbl_products union
        select  'tbl_product_family', count(*) from tbl_product_family union
        select  'tbl_profiles', count(*) from tbl_profiles union
        select  'tbl_return_products', count(*) from tbl_return_products union
        select  'tbl_returns', count(*) from tbl_returns union
        select  'tbl_roles', count(*) from tbl_roles union
        select  'tbl_sales_territory', count(*) from tbl_sales_territory union
        select  'tbl_states', count(*) from tbl_states union
        select  'tbl_stock_products', count(*) from tbl_stock_products union
        select  'tbl_stocks', count(*) from tbl_stocks union
        select  'tbl_unit_of_measurement', count(*) from tbl_unit_of_measurement union
        select  'tbl_user', count(*) from tbl_user union
        select  'tbl_sales_area', count(*) from tbl_sales_area union
        select  'tbl_target_line_item', count(*) from tbl_target_line_item union
        select  'tbl_target_type', count(*) from tbl_target_type union
        select  'tbl_terms_conditions', count(*) from tbl_terms_conditions union
        select  'tbl_target', count(*) from tbl_target union
        select  'tbl_user_permissions', count(*) from tbl_user_permissions union
        select  'tbl_user_profiles', count(*) from tbl_user_profiles union
        select  'tbl_visit_customer', count(*) from tbl_visit_customer union
        select  'tbl_visit_notes', count(*) from tbl_visit_notes union
        select  'tbl_visit_outcome', count(*) from tbl_visit_outcome union
        select  'tbl_visits', count(*) from tbl_visits union
        select  'users', count(*) from users union
        select  'tbl_performance', count(*) from tbl_performance union
        select  'tbl_attendance', count(*) from tbl_attendance union
        select  'tbl_beat_plan', count(*) from tbl_beat_plan union
        select  'user__c', count(*) from salesforce.user__c union
        select  'pricebookentry', count(*) from salesforce.pricebookentry union
        select  'pricebook2', count(*) from salesforce.pricebook2 union
        select  '_sf_event_log', count(*) from salesforce._sf_event_log union
        select  '_trigger_log', count(*) from salesforce._trigger_log union
        select  '_trigger_log_archive', count(*) from salesforce._trigger_log_archive union
        select  '_hcmeta', count(*) from salesforce._hcmeta union
        select  'announcement__c', count(*) from salesforce.announcement__c union
        select  'beat_plan__c', count(*) from salesforce.beat_plan__c union
        select  'performance_line_item__c', count(*) from salesforce.performance_line_item__c union
        select  'contact', count(*) from salesforce.contact union
        select  'account', count(*) from salesforce.account union
        select  'beat_plan_customers__c', count(*) from salesforce.beat_plan_customers__c union
        select  'collection__c', count(*) from salesforce.collection__c union
        select  'expense__c', count(*) from salesforce.expense__c union
        select  'performance__c', count(*) from salesforce.performance__c union
        select  'sales_area__c', count(*) from salesforce.sales_area__c union
        select  'orderitem', count(*) from salesforce.orderitem union
        select  'leave__c', count(*) from salesforce.leave__c union
        select  'target__c', count(*) from salesforce.target__c union
        select  'attendance__c', count(*) from salesforce.attendance__c union
        select  'order', count(*) from salesforce.order union
        select  'visit__c', count(*) from salesforce.visit__c union
        select  'product2', count(*) from salesforce.product2 union
        select  'sales_territory__c', count(*) from salesforce.sales_territory__c ORDER BY count DESC";
        $result=DB::connection('pgsql')->SELECT($query);
        return $this->createTable($result);
    }
    public function createTable($rowTable)
    {
        // render thead
        $thead = '<thead>
        <tr>';
        foreach ($rowTable[0] as $key => $value) {
            $thead .= '<th>' . $key . '</th>';
        }
        $thead .= '</tr>
        </thead>';

        // render tbody
        $tbody = '<tbody>';
        foreach ($rowTable as $key => $value) {
            $tbody .= '<tr>';
            foreach ($value as $v) {
                $tbody .= '<td>' . $v . '</td>';
            }
            $tbody .= '</tr>';
        }
        $tbody .= '</tbody>';
        // render table
        return '<table>' . $thead . $tbody . '</table>';
    }
}