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;
}
