public function getData(&$node) { $contract_num = _getRequestParamValue("contract"); $version_num = _getRequestParamValue("version"); $query1 = "SELECT\n vh.vendor_id,\n l1.fms_parent_contract_number AS parent_contract_number,\n l1.vendor_customer_code,\n l1.contract_number,\n l1.vendor_legal_name AS legal_name_checkbook_vendor,\n l1.vendor_id vendor_vendor,\n l1.description,\n l1.document_agency_name AS agency_name_checkbook_agency,\n l1.document_agency_id AS agency_id_checkbook_agency,\n l1.award_method_name AS award_method_name_checkbook_award_method,\n l1.document_version,\n l1.tracking_number,\n l1.board_award_number AS board_approved_award_no,\n l1.original_maximum_amount AS original_contract_amount,\n l1.revised_maximum_amount AS maximum_spending_limit,\n l444.document_code AS document_code_checkbook_ref_document_code,\n l1.start_date AS date_chckbk_dat_id_effctv_bgn_date_id_chckbk_hstr_mstr_agrmnt_0,\n l1.end_date AS date_chckbk_date_id_effctv_end_dat_id_chckbk_hstr_mstr_agrmnt_1\n FROM pending_contracts AS l1\n LEFT OUTER JOIN ref_document_code AS l444 ON l444.document_code_id = l1.document_code_id\n LEFT JOIN {vendor} v ON l1.vendor_id = v.vendor_id\n LEFT JOIN (SELECT vendor_id, MAX(vendor_history_id) AS vendor_history_id\n FROM {vendor_history} WHERE miscellaneous_vendor_flag::BIT = 0 ::BIT GROUP BY 1) vh ON v.vendor_id = vh.vendor_id\n WHERE l1.contract_number = '" . $contract_num . "' AND document_version = " . $version_num; $results1 = _checkbook_project_execute_sql($query1); $node->data = $results1; $parent_contract_number = $node->data[0]['parent_contract_number']; if (!empty($parent_contract_number)) { $mag_details = _get_master_agreement_details_by_parent_contract_number($parent_contract_number); $node->original_master_agreement_id = $mag_details['original_master_agreement_id']; $node->contract_number = $mag_details['contract_number']; $node->document_code = $mag_details['document_code@checkbook:ref_document_code']; $node->contract_number = $parent_contract_number; } }
public function getData(&$node) { $ag_id = _getRequestParamValue("agid"); $query1 = "SELECT l1.contract_number, a.master_contract_number,\n l2.vendor_id AS vendor_id_checkbook_vendor_history,\n l529.legal_name AS legal_name_checkbook_vendor,\n l1.description,\n l531.agency_name AS agency_name_checkbook_agency,\n l531.agency_id AS agency_id_checkbook_agency,\n l1071.award_method_name AS award_method_name_checkbook_award_method,\n l1.document_version,\n l1.tracking_number,\n l1.number_responses,\n l1.number_solicitation,\n l1.maximum_contract_amount,\n l1.brd_awd_no,\n l1.original_contract_amount,\n l903.document_code AS document_code_checkbook_ref_document_code,\n l1237.date AS date_chckbk_date_id_effctv_begin_date_id_chckbk_histor_agrmnt_0,\n l1318.date AS date_checkbk_date_id_effctv_end_date_id_chckbk_history_agrmnt_1,\n l1399.date AS date_chckbk_date_id_rgstrd_date_id_checkbook_history_agreemnt_2,\n rat.agreement_type_name\n FROM history_agreement AS l1\n LEFT OUTER JOIN agreement_snapshot AS a ON l1.master_agreement_id = a.master_agreement_id\n LEFT OUTER JOIN vendor_history AS l2 ON l2.vendor_history_id = l1.vendor_history_id\n LEFT OUTER JOIN vendor AS l529 ON l529.vendor_id = l2.vendor_id\n LEFT OUTER JOIN ref_agency_history AS l530 ON l530.agency_history_id = l1.agency_history_id\n LEFT OUTER JOIN ref_agency AS l531 ON l531.agency_id = l530.agency_id\n LEFT OUTER JOIN ref_document_code AS l903 ON l903.document_code_id = l1.document_code_id\n LEFT OUTER JOIN ref_award_method AS l1071 ON l1071.award_method_id = l1.award_method_id\n LEFT OUTER JOIN ref_date AS l1237 ON l1237.date_id = l1.effective_begin_date_id\n LEFT OUTER JOIN ref_date AS l1318 ON l1318.date_id = l1.effective_end_date_id\n LEFT OUTER JOIN ref_date AS l1399 ON l1399.date_id = l1.registered_date_id\n LEFT OUTER JOIN {ref_agreement_type} AS rat ON l1.agreement_type_id = rat.agreement_type_id\n WHERE l1.original_agreement_id = " . $ag_id . "\n AND l1.latest_flag = 'Y'\n "; $query2 = "select rfed_amount from history_agreement where original_agreement_id = " . $ag_id . " and latest_flag = 'Y' limit 1"; $results1 = _checkbook_project_execute_sql_by_data_source($query1, _get_current_datasource()); $node->data = $results1; $magid = _get_master_agreement_id(); if (!empty($magid)) { $magdetails = _get_master_agreement_details($magid); $node->magid = $magid; $node->document_code = $magdetails['document_code@checkbook:ref_document_code']; $node->contract_number = $magdetails['contract_number']; } if (_get_current_datasource() == _get_default_datasource()) { $results2 = _checkbook_project_execute_sql_by_data_source($query2, _get_current_datasource()); $spent_amount = 0; foreach ($results2 as $row) { $spent_amount += $row["rfed_amount"]; } $node->spent_amount = $spent_amount; $query3 = "SELECT COUNT(*) AS total_child_contracts\n\t FROM {history_agreement}\n\t WHERE master_agreement_id = " . $magid . "\n\t AND latest_flag = 'Y'"; $results3 = _checkbook_project_execute_sql($query3); $total_child_contracts = 0; foreach ($results3 as $row) { $total_child_contracts += $row["total_child_contracts"]; } $node->total_child_contracts = $total_child_contracts; } else { $query2 = "select sum(original_amount) original_amount, sum(current_amount) current_amount,\n \t\t\t sum(check_amount) as spent_amount\n\t\t\t\tFROM {oge_contract_vendor_level} a\n\t\t\t\tJOIN (select distinct contract_number from {history_agreement} where agreement_id = " . $ag_id . ") b\n\t\t\t\tON a.fms_contract_number = b.contract_number\n\t\t\t\tLEFT JOIN (SELECT sum(check_amount) as check_amount, contract_number, vendor_id FROM {disbursement_line_item_details} group by 2,3) c\n\t\t\t\tON b.contract_number = c.contract_number AND a.vendor_id = c.vendor_id limit 1"; $results2 = _checkbook_project_execute_sql_by_data_source($query2, _get_current_datasource()); foreach ($results2 as $row) { $node->spent_amount = $row['spent_amount']; $node->original_contract_amount = $row['original_amount']; $node->maximum_contract_amount = $row['current_amount']; $node->total_child_contracts = $row['num_associated_contracts']; } $node->data_source_amounts_differ = ContractUtil::childAgreementAmountsDiffer($ag_id); } }
</h4> <?php //TODO temp fix move bottom code to separate custom preprocess function $contract_num = _getRequestParamValue("contract"); $version_num = _getRequestParamValue("version"); $queryVendorDetails = "SELECT\n vh.vendor_id,\n rb.business_type_code,\n p.vendor_id vendor_vendor,\n l444.document_code,\n va.address_id,\n p.vendor_legal_name AS vendor_name,\n a.address_line_1,\n a.address_line_2,\n a.city, a.state, a.zip, a.country,\n (CASE WHEN (rb.business_type_code = 'MNRT' OR rb.business_type_code = 'WMNO') THEN 'Yes' ELSE 'NO' END) AS mwbe_vendor,\n (CASE WHEN rm.minority_type_id in (4,5) then 'Asian American' ELSE rm.minority_type_name END)AS ethnicity\n\t FROM {pending_contracts} p\n\t LEFT JOIN {vendor} v ON p.vendor_id = v.vendor_id\n\t LEFT JOIN (SELECT vendor_id, MAX(vendor_history_id) AS vendor_history_id\n\t FROM {vendor_history} WHERE miscellaneous_vendor_flag::BIT = 0 ::BIT GROUP BY 1) vh ON v.vendor_id = vh.vendor_id\n\t LEFT JOIN {vendor_address} va ON vh.vendor_history_id = va.vendor_history_id\n\t LEFT JOIN {address} a ON va.address_id = a.address_id\n\t LEFT JOIN {ref_address_type} ra ON va.address_type_id = ra.address_type_id\n\t LEFT JOIN {vendor_business_type} vb ON vh.vendor_history_id = vb.vendor_history_id\n\t LEFT JOIN {ref_business_type} rb ON vb.business_type_id = rb.business_type_id\n\t LEFT JOIN {ref_minority_type} rm ON vb.minority_type_id = rm.minority_type_id\n\t LEFT JOIN {ref_document_code} AS l444 ON l444.document_code_id = p.document_code_id\n\t WHERE p.contract_number = '" . $contract_num . "'" . " AND p.document_version =" . $version_num; $results1 = _checkbook_project_execute_sql($queryVendorDetails); $node->data = $results1; foreach ($node->data as $key => $value) { if ($value['business_type_code'] == "MNRT" || $value['business_type_code'] == "WMNO") { $node->data[0]["mwbe_vendor"] = "Yes"; } } if ($node->data[0]["vendor_id"]) { $queryVendorCount = "SELECT COUNT(*) AS total_contracts_sum FROM {agreement_snapshot} WHERE latest_flag= 'Y' AND vendor_id =" . $node->data[0]["vendor_id"]; $results2 = _checkbook_project_execute_sql($queryVendorCount); foreach ($results2 as $row) { $total_cont += $row['total_contracts_sum']; } $vendor_link = '/contracts_landing/status/A/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_id'] . '?expandBottomCont=true'; if ($node->data[0]["mwbe_vendor"] == "Yes") { $vendor_link = '/contracts_landing/status/A/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_id'] . '/dashboard/mp?expandBottomCont=true'; } } else { $total_cont = 0; if ($node->data[0]['document_code'] == 'RCT1') { $vendor_link = '/contracts_pending_rev_landing/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_vendor'] . '?expandBottomCont=true'; } else { $vendor_link = '/contracts_pending_exp_landing/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_vendor'] . '?expandBottomCont=true'; } }
function get_top_nav_records_count($urlParamMap, $default_params, $table) { $where_filters = array(); foreach ($urlParamMap as $param => $value) { if (_getRequestParamValue($param) != null) { $where_filters[] = _widget_build_sql_condition(' a1.' . $value, _getRequestParamValue($param)); } } foreach ($default_params as $param => $value) { $where_filters[] = _widget_build_sql_condition(' a1.' . $param, $value); } if (count($where_filters) > 0) { $where_filter = ' where ' . implode(' and ', $where_filters); } $sql = 'select count(*) count from ' . $table . ' a1 ' . $where_filter; $data = _checkbook_project_execute_sql($sql); return $data[0]['count']; }
/** * Returns the legend displayed in the Sub Vendors (M/WBE) dashboard for the "Sub Spending by M/WBE Share" visualization * @param $year * @param $yeartype * @return string */ static function getSubMWBENYCLegend($year, $yeartype) { $where_filter = "where year_id = {$year} and type_of_year = '{$yeartype}' "; $sql = 'select rm.minority_type_id, rm.minority_type_name , sum(total_spending_amount) total_spending from aggregateon_subven_spending_coa_entities a1 join ref_minority_type rm on rm.minority_type_id = a1.minority_type_id ' . $where_filter . ' group by rm.minority_type_id, rm.minority_type_name '; $spending_rows = _checkbook_project_execute_sql($sql); foreach ($spending_rows as $row) { switch ($row['minority_type_id']) { case '2': $mwbe_spending_sub += $row['total_spending']; break; case '3': $mwbe_spending_sub += $row['total_spending']; break; case '4': $mwbe_spending_sub += $row['total_spending']; break; case '5': $mwbe_spending_sub += $row['total_spending']; break; case '7': $non_mwbe_spending_sub += $row['total_spending']; break; case '9': $mwbe_spending_sub += $row['total_spending']; break; } } $mwbe_share = custom_number_formatter_format($mwbe_spending_sub / ($non_mwbe_spending_sub + $mwbe_spending_sub) * 100, 1, null, '%'); $mwbe_spending = custom_number_formatter_format($mwbe_spending_sub, 2, '$'); $non_mwbe = custom_number_formatter_format($non_mwbe_spending_sub, 2, '$'); return '<div class="chart-nyc-legend"> <div class="legend-title"><span>NYC Total M/WBE</span></div> <div class="legend-item"><span>M/WBE Share: ' . $mwbe_share . ' </span></div> <div class="legend-item"><span>M/WBE Spending: ' . $mwbe_spending . ' </span></div> <div class="legend-item"><span>Non M/WBE: ' . $non_mwbe . '</span></div> </div> '; }
static function getCurrentSubMWBEApplicableFilters($domain) { switch ($domain) { case "spending": $table = "aggregateon_subven_spending_coa_entities"; $urlParamMap = array("year" => "year_id", "yeartype" => "type_of_year", "agency" => "agency_id", "subvendor" => "vendor_id", "vendor" => "prime_vendor_id", "category" => "spending_category_id"); $where_filters = array(); foreach ($urlParamMap as $param => $value) { if (_getRequestParamValue($param) != null) { $where_filters[] = _widget_build_sql_condition(' a1.' . $value, _getRequestParamValue($param)); } } if (count($where_filters) > 0) { $where_filter = ' where ' . implode(' and ', $where_filters); } $sql = 'select a1.minority_type_id from ' . $table . ' a1 ' . $where_filter . ' group by a1.minority_type_id '; $data = _checkbook_project_execute_sql($sql); break; case "contracts": $table = "aggregateon_subven_contracts_cumulative_spending"; $urlParamMap = array("year" => "fiscal_year_id", "agency" => "agency_id", "yeartype" => "type_of_year", "awdmethod" => "award_method_id", "vendor" => "prime_vendor_id", "subvendor" => "vendor_id", "status" => "status_flag", "csize" => "award_size_id", "cindustry" => "industry_type_id"); $where_filters = array(); foreach ($urlParamMap as $param => $value) { if (_getRequestParamValue($param) != null) { $where_filters[] = _widget_build_sql_condition(' a1.' . $value, _getRequestParamValue($param)); } } if (count($where_filters) > 0) { $where_filter = ' where ' . implode(' and ', $where_filters); } //$where_filter .= ' and rd.document_code in (' . ContractUtil::getCurrentPageDocumentIds() . ') '; $sql = 'select a1.minority_type_id from {' . $table . '} a1 join {ref_document_code} rd on a1.document_code_id = rd.document_code_id ' . $where_filter . ' group by a1.minority_type_id'; $data = _checkbook_project_execute_sql($sql); break; } $applicable_minority_types = array(); foreach ($data as $row) { $applicable_minority_types[] = $row['minority_type_id']; } return $applicable_minority_types; }
/** * Function to return the records count of the user request * @return Record */ private function getRecordCount() { if (!isset($this->recordCount)) { $query = $this->jobDetails['data_command']; $from_position = strpos($query, 'FROM'); $order_by_position = strpos($query, 'ORDER BY'); $from_part = substr($query, $from_position, $order_by_position - $from_position); $query = "SELECT COUNT(*) as record_count " . $from_part; $db_name = "main"; $data_source = "checkbook"; $results = _checkbook_project_execute_sql($query, $db_name, $data_source); $this->recordCount = $results[0]["record_count"]; } return $this->recordCount; }