public function getData(&$node)
{
$mag_id = _getRequestParamValue("magid");
$query1 = "SELECT l1.contract_number,\r\n l2.vendor_id AS vendor_id_checkbook_vendor_history,\r\n l3.legal_name AS legal_name_checkbook_vendor,\r\n l1.description,\r\n l5.agency_name AS agency_name_checkbook_agency,\r\n l5.agency_id AS agency_id_checkbook_agency,\r\n l656.award_method_name AS award_method_name_checkbook_award_method,\r\n l1.document_version,\r\n l1.tracking_number,\r\n l1.number_responses,\r\n l1.number_solicitation,\r\n l1.maximum_spending_limit,\r\n l1.board_approved_award_no,\r\n l1.original_contract_amount,\r\n l444.document_code AS document_code_checkbook_ref_document_code,\r\n l1040.date AS date_chckbk_dat_id_effctv_bgn_date_id_chckbk_hstr_mstr_agrmnt_0,\r\n l1124.date AS date_chckbk_date_id_effctv_end_dat_id_chckbk_hstr_mstr_agrmnt_1,\r\n l1208.date AS date_chckbk_date_id_rgstrd_date_id_chckbk_histr_master_agrmnt_2,\r\n rat.agreement_type_name\r\n FROM {history_master_agreement} AS l1\r\n LEFT OUTER JOIN {vendor_history} AS l2 ON l2.vendor_history_id = l1.vendor_history_id\r\n LEFT OUTER JOIN {vendor} AS l3 ON l3.vendor_id = l2.vendor_id\r\n LEFT OUTER JOIN {ref_agency_history} AS l4 ON l4.agency_history_id = l1.agency_history_id\r\n LEFT OUTER JOIN {ref_agency} AS l5 ON l5.agency_id = l4.agency_id\r\n LEFT OUTER JOIN {ref_document_code} AS l444 ON l444.document_code_id = l1.document_code_id\r\n LEFT OUTER JOIN {ref_award_method} AS l656 ON l656.award_method_id = l1.award_method_id\r\n LEFT OUTER JOIN {ref_date} AS l1040 ON l1040.date_id = l1.effective_begin_date_id\r\n LEFT OUTER JOIN {ref_date} AS l1124 ON l1124.date_id = l1.effective_end_date_id\r\n LEFT OUTER JOIN {ref_date} AS l1208 ON l1208.date_id = l1.registered_date_id\r\n LEFT OUTER JOIN {ref_agreement_type} AS rat ON l1.agreement_type_id = rat.agreement_type_id\r\n WHERE l1.original_master_agreement_id = " . $mag_id . "\r\n AND l1.latest_flag = 'Y'\r\n ";
$results1 = _checkbook_project_execute_sql_by_data_source($query1, _get_default_datasource());
$node->data = $results1;
if (_get_current_datasource() == _get_default_datasource()) {
$query2 = "select rfed_amount from {agreement_snapshot} where original_agreement_id = " . $mag_id . "\r\n \t\tand master_agreement_yn = 'Y' and latest_flag = 'Y'";
$results2 = _checkbook_project_execute_sql_by_data_source($query2, _get_default_datasource());
$spent_amount = 0;
foreach ($results2 as $row) {
$spent_amount += $row["rfed_amount"];
}
$node->spent_amount = $spent_amount;
$node->original_contract_amount = $node->data[0]['original_contract_amount'];
$node->maximum_spending_limit = $node->data[0]['maximum_spending_limit'];
$query3 = "SELECT COUNT(*) AS total_child_contracts\r\n\t FROM {history_agreement}\r\n\t WHERE master_agreement_id = " . $mag_id . "\r\n\t AND latest_flag = 'Y'";
$results3 = _checkbook_project_execute_sql_by_data_source($query3, _get_current_datasource());
$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, \r\n \t\t\tcount(distinct fms_contract_number) as num_associated_contracts, sum(check_amount) as spent_amount\r\n\t\t\t\tFROM {oge_contract_vendor_level} a\r\n\t\t\t\tJOIN (select distinct contract_number from {history_agreement} where master_agreement_id = " . $mag_id . ") b\r\n\t\t\t\tON a.fms_contract_number = b.contract_number\r\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\r\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_spending_limit = $row['current_amount'];
$node->total_child_contracts = $row['num_associated_contracts'];
}
$node->data_source_amounts_differ = ContractUtil::masterAgreementAmountsDiffer($mag_id);
}
}
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);
}
}
<tr>
<th></th>
</tr>
</thead>
<tbody>
<?php
$count = 0;
if (preg_match("/newwindow/", current_path())) {
$new_window = "/newwindow";
} else {
$new_window = "";
}
foreach ($node->data as $contract) {
$contract_number = $contract['contract_number'];
$q1 = "SELECT DISTINCT COUNT(sub_contract_id) AS sub_vendor_count FROM subcontract_details\n WHERE contract_number = '" . $contract_number . "'\n AND latest_flag = 'Y'\n LIMIT 1";
$subcontract_details = _checkbook_project_execute_sql_by_data_source($q1);
if ($count % 2 == 0) {
$class = "odd";
} else {
$class = "even";
}
$first = '';
if ($count == 0) {
$first = "first-item clickOnLoad";
}
if (preg_match("/newwindow/", current_path())) {
$child_contract_link = $contract['contract_number'];
} else {
$child_contract_link = "<a\n href='/panel_html/contract_transactions/contract_details/agid/" . $contract["original_agreement_id"] . "/doctype/" . $contract["document_code@checkbook:ref_document_code"] . "'\n class='bottomContainerReload'>" . $contract['contract_number'] . "</a>";
}
?>
if (_getRequestParamValue("doctype") == "RCT1") {
$vendor_link = '/contracts_revenue_landing' . $status . '/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_id_checkbook_vendor_history'] . '?expandBottomCont=true';
$agency_link = '/contracts_revenue_landing' . $status . '/year/' . _getCurrentYearID() . $datasource . '/yeartype/B/agency/' . $node->data[0]['agency_id_checkbook_agency'] . '?expandBottomCont=true';
} else {
if (_is_mwbe_vendor(_getRequestParamValue("agid"))) {
$vendor_link = '/contracts_landing' . $status . '/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_id_checkbook_vendor_history'] . '/dashboard/mp?expandBottomCont=true';
} else {
$vendor_link = '/contracts_landing' . $status . '/year/' . _getCurrentYearID() . '/yeartype/B/vendor/' . $node->data[0]['vendor_id_checkbook_vendor_history'] . '?expandBottomCont=true';
}
$agency_link = '/contracts_landing' . $status . '/year/' . _getCurrentYearID() . $datasource . '/yeartype/B/agency/' . $node->data[0]['agency_id_checkbook_agency'] . '?expandBottomCont=true';
}
$spending_link = "/spending/transactions/agid/" . _getRequestParamValue("agid") . $datasource . "/newwindow";
if (_getRequestParamValue("datasource") != "checkbook_oge") {
$contract_number = $node->data[0]['contract_number'];
$querySubVendorCount = "SELECT COUNT(DISTINCT vendor_id) AS sub_vendor_count FROM sub_agreement_snapshot\n WHERE contract_number = '" . $contract_number . "'\n AND latest_flag = 'Y'\n LIMIT 1";
$results3 = _checkbook_project_execute_sql_by_data_source($querySubVendorCount, _get_current_datasource());
$res->data = $results3;
$total_subvendor_count = $res->data[0]['sub_vendor_count'];
}
?>
<div class="contract-details-heading <?php
echo $oge_class;
?>
">
<div class="contract-id">
<h2 class="contract-title">Contract ID: <span
class="contract-number"><?php
echo $node->data[0]['contract_number'];
?>
</span></h2>
<?php
public static function getLatestMwbeCategoryBySpendingVendorByTransactionYear($vendor_id, $year_id = null, $year_type = null)
{
if ($year_id == null) {
$year_id = _getRequestParamValue('year');
}
if ($year_type == null) {
$year_type = _getRequestParamValue('yeartype');
}
$query = "SELECT vendor_id, agency_id, year_id, type_of_year, minority_type_id, is_prime_or_sub\r\n FROM spending_vendor_latest_mwbe_category\r\n WHERE minority_type_id IN (2,3,4,5,9)";
$query .= isset($vendor_id) ? " AND vendor_id = " . $vendor_id : "";
$query .= " AND year_id =" . $year_id . "\r\n AND type_of_year ='" . $year_type . "'\r\n GROUP BY vendor_id, agency_id, year_id, type_of_year, minority_type_id, is_prime_or_sub LIMIT 1";
$results = _checkbook_project_execute_sql_by_data_source($query, 'checkbook');
if ($results[0]['minority_type_id'] != '') {
return $results[0]['minority_type_id'];
} else {
return false;
}
}
static function getPrimeVendorEthinictyTitle($vendor_id, $domain, $is_prime_or_sub = "P")
{
switch ($domain) {
case "spending":
$ethnicity_id = SpendingUtil::getLatestMwbeCategoryByVendor($vendor_id, null, null, null, $is_prime_or_sub);
if ($ethnicity_id > 0) {
$title = " <br/><span class=\"second-line\">M/WBE Category: " . MappingUtil::getMinorityCategoryById($ethnicity_id) . "</span>";
}
break;
case "contracts":
$ethnicity_id = ContractUtil::getLatestMwbeCategoryByVendor($vendor_id, null, null, null, $is_prime_or_sub);
if (!$ethnicity_id) {
$query = "SELECT year_id, minority_type_id\n FROM contract_vendor_latest_mwbe_category\n WHERE vendor_id = " . $vendor_id . " AND is_prime_or_sub = '" . $is_prime_or_sub . "'" . " ORDER BY year_id DESC " . " LIMIT 1 ";
$results = _checkbook_project_execute_sql_by_data_source($query, 'checkbook');
if ($results) {
$ethnicity_id = $results[0]['minority_type_id'];
}
}
if ($ethnicity_id != 7 && $ethnicity_id != 11) {
$title = " <br/><span class=\"second-line\">M/WBE Category: " . MappingUtil::getMinorityCategoryById($ethnicity_id) . "</span>";
}
break;
}
return $title;
}
public function initializeAmounts()
{
$master_agreement_id = $this->getAgreementId();
$query = "SELECT";
switch ($this->getDataSource()) {
case "checkbook_oge":
$query .= " sum(original_amount) original_amount\n , sum(current_amount) current_amount\n , sum(check_amount) as spent_amount\n FROM {oge_contract_vendor_level} a\n JOIN\n (\n SELECT distinct contract_number\n FROM {history_agreement}\n WHERE master_agreement_id = " . $master_agreement_id . "\n ) b ON a.fms_contract_number = b.contract_number\n\t\t\t\t LEFT JOIN\n\t\t\t\t (\n\t\t\t\t SELECT sum(check_amount) as check_amount\n\t\t\t\t , contract_number\n\t\t\t\t , vendor_id\n\t\t\t\t FROM {disbursement_line_item_details} GROUP BY 2,3\n\t\t\t\t ) c ON b.contract_number = c.contract_number AND a.vendor_id = c.vendor_id limit 1";
break;
default:
$query .= " l1.contract_number,\n l1.original_contract_amount as original_amount,\n l1.maximum_spending_limit as current_amount,\n l2.spent_amount as spent_amount\n FROM history_master_agreement AS l1\n JOIN\n (\n SELECT rfed_amount as spent_amount, original_agreement_id\n FROM agreement_snapshot_expanded\n WHERE original_agreement_id = " . $master_agreement_id . "\n AND master_agreement_yn = 'Y'\n AND status_flag = 'A'\n ORDER BY fiscal_year DESC LIMIT 1\n ) l2 ON l1.original_master_agreement_id = l2.original_agreement_id\n WHERE l1.original_master_agreement_id = " . $master_agreement_id . "\n AND l1.latest_flag = 'Y'";
break;
}
$results = _checkbook_project_execute_sql_by_data_source($query, $this->getDataSource());
foreach ($results as $row) {
$this->setOriginalAmount($row['original_amount']);
$this->setCurrentAmount($row['current_amount']);
$this->setSpentAmount($row['spent_amount']);
}
}
