1213 lines
77 KiB
PHP
1213 lines
77 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* exportrequests.inc.php
|
|
*
|
|
* Autor: Marc Vollmann
|
|
*
|
|
=======================================================================*/
|
|
|
|
|
|
// Include the module to convert the result of the database search to an array
|
|
include_once ("../include/inc_dbfields2array.inc.php");
|
|
include_once ("../include/dbglobal.inc.php");
|
|
|
|
function cmp_rowArray($a, $b) {
|
|
if ($a['jb_id'] == $b['jb_id']) return 0;
|
|
return ($a['jb_id'] > $b['jb_id']) ? 1 : -1;
|
|
}
|
|
|
|
|
|
$rowArray = array(); // Matrix of n elements (rows) and associative keys (cols) like row[0]['fieldName']
|
|
|
|
$sqlquery = "";
|
|
if ($supersetFieldString != "") :
|
|
|
|
// ********************
|
|
// * ONLY HEADQUARTER *
|
|
// ********************
|
|
if ($userTypeName == "hq") :
|
|
|
|
// **** Selection of the customers for the list ****
|
|
if ($f_exp_category == "1") :
|
|
|
|
// Check for status-filter of the current row (new, modified, ...)
|
|
$whereClause = "";
|
|
if ($f_status_filter == "1") : $whereClause = " AND cmp.cmp_modify_status = '1'"; endif;
|
|
if ($f_status_filter == "2") : $whereClause = " AND cmp.cmp_modify_status = '2'"; endif;
|
|
if ($f_status_filter == "3") : $whereClause = " AND (cmp.cmp_modify_status = '1' OR cmp.cmp_modify_status = '2')"; endif;
|
|
|
|
// Filter
|
|
// Export no cash payers if requested by global parameter
|
|
if (EXPORT_FILTER_NO_CASHPAYER == "1" && CSC_ID_PAYER_CASH != "" && is_numeric(CSC_ID_PAYER_CASH)) :
|
|
$whereClause .= " AND (isnull(cs.csc_id_payer) OR cs.csc_id_payer != '" . CSC_ID_PAYER_CASH . "')";
|
|
endif;
|
|
// Export no debitors having an EID with LENGTH(EID) > 6
|
|
if (EXPORT_FILTER_EID_LENGTH != "" && is_numeric(EXPORT_FILTER_EID_LENGTH) && EXPORT_FILTER_EID_LENGTH > "0") :
|
|
$whereClause .= " AND LENGTH(TRIM(cs.cs_eid)) <= " . EXPORT_FILTER_EID_LENGTH . " ";
|
|
endif;
|
|
|
|
|
|
if (mcIsSet($orderClause) == "") : $orderClause = "cmp.cmp_comp"; endif;
|
|
|
|
$sqlquery = "SELECT " . $supersetFieldString
|
|
. " FROM company AS cmp LEFT JOIN tax AS tx ON cmp.tx_id = tx.tx_id"
|
|
. " LEFT JOIN branch AS br ON cmp.br_id = br.br_id,"
|
|
. " customer AS cs LEFT JOIN costcenter AS csc ON cs.csc_id = csc.csc_id"
|
|
. " LEFT JOIN costcenteraddress AS cscad ON csc.csc_id = cscad.csc_id AND cscad.adt_id = '2'"
|
|
. " LEFT JOIN address AS adre ON cscad.ad_id = adre.ad_id"
|
|
. " LEFT JOIN headquarters AS hq ON cs.hq_id = hq.hq_id,"
|
|
. " employee AS emp, user AS usr, address AS ad"
|
|
. " WHERE cmp.cmp_id = cs.cmp_id AND"
|
|
. " cmp.cmp_type = '0' AND"
|
|
. " cs.cs_eid != '' AND"
|
|
. " cs.cs_admin = emp.emp_id AND"
|
|
. " emp.usr_id = usr.usr_id AND"
|
|
. " cmp.ad_id = ad.ad_id AND"
|
|
. " usr.hq_id = $hq_id" . $whereClause
|
|
. " ORDER BY " . $orderClause;
|
|
/*
|
|
$sqlquery = "SELECT " . $supersetFieldString
|
|
. " FROM company AS cmp LEFT JOIN tax AS tx ON cmp.tx_id = tx.tx_id"
|
|
. " LEFT JOIN branch AS br ON cmp.br_id = br.br_id,"
|
|
. " customer AS cs LEFT JOIN costcenter AS csc ON cs.csc_id = csc.csc_id"
|
|
. " LEFT JOIN costcenteraddress AS cscad ON csc.csc_id = cscad.csc_id AND cscad.adt_id = '2'"
|
|
. " LEFT JOIN address AS adre ON cscad.ad_id = adre.ad_id,"
|
|
. " employee AS emp, user AS usr, address AS ad"
|
|
. " WHERE cmp.cmp_id = cs.cmp_id AND"
|
|
. " cmp.cmp_type = '0' AND"
|
|
. " cs.cs_eid != '' AND"
|
|
. " cs.cs_admin = emp.emp_id AND"
|
|
. " emp.usr_id = usr.usr_id AND"
|
|
. " cmp.ad_id = ad.ad_id AND"
|
|
. " usr.hq_id = $hq_id" . $whereClause
|
|
. " ORDER BY " . $orderClause;
|
|
*/
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// **** Selection of the couriers for the list ****
|
|
if ($f_exp_category == "2") :
|
|
|
|
// Check for status-filter of the current row (new, modified, ...)
|
|
$whereClause = "";
|
|
if ($f_status_filter == "1") : $whereClause = " AND cmp.cmp_modify_status = '1'"; endif;
|
|
if ($f_status_filter == "2") : $whereClause = " AND cmp.cmp_modify_status = '2'"; endif;
|
|
if ($f_status_filter == "3") : $whereClause = " AND (cmp.cmp_modify_status = '1' OR cmp.cmp_modify_status = '2')"; endif;
|
|
|
|
// Export NO driver if defined
|
|
if (EXPORT_FILTER_NO_DRIVER == "1") :
|
|
$whereClause .= " AND (isnull(cr.cr_id_parent) OR cr.cr_id_parent = '0')";
|
|
endif;
|
|
|
|
if (mcIsSet($orderClause) == "") : $orderClause = "cmp.cmp_comp"; endif;
|
|
|
|
$sqlquery = "SELECT " . $supersetFieldString
|
|
. " FROM company AS cmp, courier AS cr, user AS usr, address AS ad"
|
|
. " WHERE cmp.cmp_id = cr.cmp_id AND"
|
|
. " cr.usr_id = usr.usr_id AND"
|
|
. " cmp.ad_id = ad.ad_id AND"
|
|
. " usr.hq_id = $hq_id" . $whereClause
|
|
. " ORDER BY " . $orderClause;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// **** Selection of the vehicles for the list ****
|
|
if ($f_exp_category == "3") :
|
|
|
|
// Check for status-filter of the current row (new, modified, ...)
|
|
$whereClause = "";
|
|
if ($f_status_filter == "1") : $whereClause = " AND crvh.crvh_modify_status = '1'"; endif;
|
|
if ($f_status_filter == "2") : $whereClause = " AND crvh.crvh_modify_status = '2'"; endif;
|
|
if ($f_status_filter == "3") : $whereClause = " AND (crvh.crvh_modify_status = '1' OR crvh.crvh_modify_status = '2' OR crvh.crvh_modify_status = '3')"; endif;
|
|
|
|
if (mcIsSet($orderClause) == "") : $orderClause = "crvh.crvh_sid"; endif;
|
|
|
|
$sqlquery = "SELECT " . $supersetFieldString
|
|
. " FROM couriervehicle AS crvh, courier AS cr"
|
|
. " LEFT JOIN courier AS cr_parent ON cr.cr_id_parent = cr_parent.cr_id "
|
|
. " WHERE cr.cr_id = crvh.cr_id AND"
|
|
. " cr.hq_id = $hq_id" . $whereClause
|
|
. " ORDER BY " . $orderClause;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// ***************
|
|
// $f_exp_category == "5|6|7|8" will be routed to $f_exp_category == "4"
|
|
// "5" : Export with jb_storno = "NULL|0|1|2" only
|
|
// "6" : Export with jb_storno = "3" only
|
|
// "7" : Export collected business volume ("current" cash flow)
|
|
// "8" : Carrier business volume
|
|
// ***************
|
|
if ($f_exp_category >= "4" && $f_exp_category <= "8") :
|
|
$fromClauseRelevantCostcenter = "jb.csc_id_payer"; // IMPORTANT !!!! ("jb.csc_id_payer", "jb.csc_id_related")
|
|
// $fieldClausePriceFormular = $priceFormular; // IMPORTANT !!!! ("$priceFormular" id defined in "glob_defs.inc.php")
|
|
$fieldClausePriceFormular = "jb.jb_totalprice"; // jb.jb_totalprice
|
|
$fieldClauseCmpInv2Hq = "cmp2.cmp_inv2hq, "; // Invoice to headquarters before delivery to paying customer or directly to the customer
|
|
$fieldClauseCmpSingleInvoice = "cmp2.cmp_inv2hq AS cmp_single_invoice, "; // One invoice for each job or not
|
|
$fieldClauseCashMode = "jb.csc_id_payer AS cash, "; // CASH mode ("bar" or not)
|
|
$fromClausePriceTable = "tourservice AS trs"; // IMPORTANT !!!!
|
|
$priceTableAlias = "trs"; // IMPORTANT !!!!
|
|
$whereClauseDatetimeField = "jb.jb_finishtime"; // IMPORTANT !!!!
|
|
$whereClauseJbStatus = "jb.jb_status = '2'"; // IMPORTANT !!!!
|
|
$whereClauseExportField = "jb.jb_export_time"; // IMPORTANT !!!!
|
|
// !!!! $whereClauseExportField has to be defined before !!!!
|
|
$whereClauseRelevantExportTime = " (ISNULL(" . $whereClauseExportField . ") OR (" . $whereClauseExportField . " = '0000-00-00 00:00:00')) AND "; // IMPORTANT !!!!
|
|
$whereClauseIncomplete = " (ISNULL(jb.jb_incomplete) OR (jb.jb_incomplete = '0')) AND "; // IMPORTANT !!!!
|
|
$whereClauseStorno = "";
|
|
$whereClausePrice = "";
|
|
$whereClauseFilterExport = "";
|
|
// Constants
|
|
$constProductDescription = getParameterValue("0", "EXPORT_CONST_PRODUCT", $hq_id);
|
|
if ($constProductDescription == "") : $constProductDescription = "K "; endif;
|
|
$constPriceSpecial = getParameterValue("0", "EXPORT_CONST_PRICE_SPECIAL", $hq_id);
|
|
if ($constPriceSpecial == "") : $constPriceSpecial = "FP"; endif;
|
|
$constContactStaffer = getParameterValue("0", "EXPORT_CONST_STAFFER", $hq_id);
|
|
if ($constContactStaffer == "") : $constContactStaffer = ""; endif;
|
|
$constContactPerson = getParameterValue("0", "EXPORT_CONST_CONTACT_PERSON", $hq_id);
|
|
if ($constContactPerson == "") : $constContactPerson = ""; endif;
|
|
|
|
// Add fields for paying costcenter address data by parameter
|
|
$parExportAddPayingCostcenteraddressDataAdt1 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_1", $hq_id);
|
|
if ($parExportAddPayingCostcenteraddressDataAdt1 == "") : $parExportAddPayingCostcenteraddressDataAdt1 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_1", "0"); endif;
|
|
$parExportAddPayingCostcenteraddressDataAdt2 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_2", $hq_id);
|
|
if ($parExportAddPayingCostcenteraddressDataAdt2 == "") : $parExportAddPayingCostcenteraddressDataAdt2 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_2", "0"); endif;
|
|
$parExportAddPayingCostcenteraddressDataAdt3 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_3", $hq_id);
|
|
if ($parExportAddPayingCostcenteraddressDataAdt3 == "") : $parExportAddPayingCostcenteraddressDataAdt3 = getParameterValue("0", "EXPORT_ADD_PAYING_CSCAD_DATA_ADT_3", "0"); endif;
|
|
// Add fields for master data company name and address by parameter
|
|
$parExportAddCustomerMasterDataAddressAndCompanyName = getParameterValue("0", "EXPORT_ADD_CS_MASTER_DATA", $hq_id);
|
|
if ($parExportAddCustomerMasterDataAddressAndCompanyName == "") : $parExportAddCustomerMasterDataAddressAndCompanyName = getParameterValue("0", "EXPORT_ADD_CS_MASTER_DATA", "0"); endif;
|
|
endif;
|
|
$useCategory4 = FALSE;
|
|
|
|
if ($f_exp_category == "5") :
|
|
$whereClauseStorno = " AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1') ";
|
|
if ($specialJbPayment != "" && is_numeric($specialJbPayment)) :
|
|
$whereClauseStorno .= " AND jb.jb_payment = '" . $specialJbPayment . "' ";
|
|
else :
|
|
$whereClauseStorno .= " AND jb.jb_payment IN ('0', '1', '2') ";
|
|
endif;
|
|
$whereClausePrice = " HAVING price > 0 ";
|
|
$useCategory4 = TRUE;
|
|
endif;
|
|
|
|
if ($f_exp_category == "6") :
|
|
$whereClauseStorno = " AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '3') ";
|
|
if ($specialJbPayment != "" && is_numeric($specialJbPayment)) :
|
|
$whereClauseStorno .= " AND jb.jb_payment = '" . $specialJbPayment . "' ";
|
|
else :
|
|
$whereClauseStorno .= " AND jb.jb_payment IN ('0', '1', '2') ";
|
|
endif;
|
|
$whereClausePrice = " HAVING price < 0 ";
|
|
$useCategory4 = TRUE;
|
|
endif;
|
|
|
|
if ($f_exp_category == "7") :
|
|
$fromClauseRelevantCostcenter = "jb.csc_id_related";
|
|
$fieldClausePriceFormular = "SUM(jbp.jbp_price)";
|
|
$fromClausePriceTable = "jobpayment AS jbp";
|
|
$priceTableAlias = "jbp";
|
|
$whereClauseDatetimeField = "jbp.jbp_bookingtime"; // Redefine relevant datetime field. "jbp_bookingtime" ist the timestamp the endcustomer had paid
|
|
$whereClauseJbStatus = "jb.jb_status IN ('0','1','2','8','9')"; // Redefine relevant job status
|
|
$whereClauseExportField = "jbp.jbp_export_time"; // Redefine relevant export field
|
|
// !!!! $whereClauseExportField has to be defined before !!!!
|
|
$whereClauseRelevantExportTime = ""; // IMPORTANT !!!! (An job can be exported for generation debit and/or credit notes, but a credit note for an end customer can be created after this!)
|
|
$whereClauseIncomplete = ""; // Job state "incomplete" is NOT relevant regarding the values paid by the endcustomer
|
|
$whereClauseStorno = " AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') ";
|
|
// $whereClausePrice = " HAVING price > 0 ";
|
|
$useCategory4 = TRUE;
|
|
endif;
|
|
|
|
if ($f_exp_category == "8") :
|
|
// Constants
|
|
$constProductDescription = getParameterValue("0", "EXPORT_CAT_08_CONST_PRODUCT", $hq_id);
|
|
if ($constProductDescription == "") : $constProductDescription = "M3"; endif;
|
|
$constPriceSpecial = getParameterValue("0", "EXPORT_CAT_08_CONST_PRICE_SPECIAL", $hq_id);
|
|
if ($constPriceSpecial == "") : $constPriceSpecial = "FP"; endif;
|
|
$constContactStaffer = getParameterValue("0", "EXPORT_CAT_08_CONST_STAFFER", $hq_id);
|
|
if ($constContactStaffer == "") : $constContactStaffer = ""; endif;
|
|
$constContactPerson = getParameterValue("0", "EXPORT_CAT_08_CONST_CONTACT_PERSON", $hq_id);
|
|
if ($constContactPerson == "") : $constContactPerson = ""; endif;
|
|
$constInvoiceToHeadquarters = getParameterValue("0", "EXPORT_CAT_08_CONST_INV2HQ", $hq_id);
|
|
if ($constInvoiceToHeadquarters == "") : $constInvoiceToHeadquarters = "1"; endif;
|
|
$constSingleInvoicePerJob = getParameterValue("0", "EXPORT_CAT_08_CONST_SINGLE_INV", $hq_id);
|
|
if ($constSingleInvoicePerJob == "") : $constSingleInvoicePerJob = "2"; endif;
|
|
// Clauses
|
|
$fieldClauseCmpInv2Hq = "'" . $constInvoiceToHeadquarters . "' AS cmp_inv2hq, "; // Invoice to headquarters before delivery to paying customer in every case
|
|
$fieldClauseCmpSingleInvoice = "'" . $constSingleInvoicePerJob . "' AS cmp_single_invoice, "; // One invoice for each job in every case
|
|
$fieldClauseCashMode = "' ' AS cash, '0' AS jbpc_id, "; // CASH mode ("bar" or not) AND jbpc_id
|
|
$fromClauseRelevantCostcenter = "jb.csc_id_related";
|
|
$whereClauseStorno = " AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') ";
|
|
$whereClauseStorno .= " AND jb.jb_cr_price > 0 ";
|
|
if ($specialJbPayment != "" && is_numeric($specialJbPayment)) :
|
|
$whereClauseStorno .= " AND jb.jb_payment = '" . $specialJbPayment . "' ";
|
|
else :
|
|
$whereClauseStorno .= " AND jb.jb_payment = '3' ";
|
|
endif;
|
|
$whereClauseStorno .= " AND EXISTS (SELECT * FROM jobpayment AS jbp2 WHERE jbp2.jb_id = jb.jb_id AND jbp2.jbpc_id != '0') ";
|
|
$whereClauseStorno .= " AND (NOT isnull(jb.cr_id)) AND jb.cr_id != '0' ";
|
|
$useCategory4 = TRUE;
|
|
endif;
|
|
|
|
// Filter for export according to calculator jobs ("sub-jobs") being credit notes
|
|
$constFilterCalcNoBookingNote = getParameterValue("0", "EXPORT_FILTER_CALCULATOR_NO_BOOKINGNOTES", "0");
|
|
if ($constFilterCalcNoBookingNote == "1") :
|
|
$whereClauseFilterExport = " AND ((isnull(jb.jb_id_parent) OR jb.jb_id_parent = '0' OR jb.jb_id_parent = '-1') OR (jb.jb_id_parent > '0' AND jb.jb_totalprice * jb2.jb_totalprice > 0))";
|
|
endif;
|
|
|
|
// Selection of the invoices for the list
|
|
// Only jobs WITH cr_id AND cr_sid will be exported !!!
|
|
if ($f_exp_category == "4" || $useCategory4) :
|
|
// Cash
|
|
$select_clause = "SELECT jb.jb_id, " .
|
|
"jb.cr_id, " .
|
|
"jb.csc_id_related, " .
|
|
"jb.hq_id_exec, " .
|
|
"jb.hq_id_sales, " .
|
|
"CONCAT(' ', RIGHT(jb.cr_sid, 3)) AS cr_sid, " .
|
|
"cr_parent.cr_id AS cr_id_parent, " .
|
|
"CONCAT(' ', RIGHT(cr_parent.cr_sid, 3)) AS cr_sid_parent, " .
|
|
"CONCAT(SUBSTRING(jb.jb_ordertime, 1, 4), " .
|
|
"SUBSTRING(jb.jb_ordertime, 6, 2), ".
|
|
"SUBSTRING(jb.jb_ordertime, 9, 2)) AS orderdate, " .
|
|
"SUBSTRING(jb.jb_ordertime, 12, 5) AS ordertime, " .
|
|
"CONCAT(SUBSTRING(jb.jb_finishtime, 1, 4), " .
|
|
"SUBSTRING(jb.jb_finishtime, 6, 2), ".
|
|
"SUBSTRING(jb.jb_finishtime, 9, 2)) AS finishdate, " .
|
|
"SUBSTRING(jb.jb_finishtime, 12, 5) AS finishtime, " .
|
|
"'" . EXPORT_HQ_KEY . " ' AS cmp_no, " .
|
|
"'" . $constProductDescription . "' AS prod_descr, " .
|
|
"'" . $constContactStaffer . "' AS contact_staffer, " .
|
|
"'" . $constContactPerson . "' AS contact_person, " .
|
|
"' ' AS VAT_special, " .
|
|
"RIGHT(cr.cr_eid, 5) AS cr_eid, " .
|
|
"RIGHT(cr_parent.cr_eid, 5) AS cr_eid_parent, " .
|
|
"jb.jb_invtext, " .
|
|
"jb.jb_invtext_cr, " .
|
|
"jb.jb_freetext_1, " .
|
|
"jb.jb_freetext_2, " .
|
|
"jb.jb_freetext_3, " .
|
|
"jb.jb_postage, " .
|
|
$fieldClauseCmpInv2Hq .
|
|
$fieldClauseCmpSingleInvoice .
|
|
$fieldClausePriceFormular . " AS price, " .
|
|
"'" . $constPriceSpecial . "' AS price_special, " .
|
|
$fieldClauseCashMode .
|
|
"jb.jb_sales_tax_rate AS tax_rate, " .
|
|
"jb.jb_sales_tax_rate_sign AS tax_rate_sign, " .
|
|
"jb.vht_id, " .
|
|
"jb.jb_cr_filter, " .
|
|
"jb.jb_cr_filter_opt, " .
|
|
"jb.jb_type, " .
|
|
"jb.jb_longhaul, " .
|
|
"jb.jb_longhaul_export, " .
|
|
"jb.jb_id_parent, " .
|
|
"(jb.jb_totalprice * 1.19) AS gross_price, " .
|
|
"jb.jb_cr_price, " .
|
|
"jb.jb_hq_id_exec_price, " .
|
|
"jb.jb_subtotalprice, " .
|
|
"jb.jb_markup, " .
|
|
"jb.jb_cr_subprice, " .
|
|
"'0' AS special_price_01, " .
|
|
"'0' AS special_price_02, " .
|
|
"jb.jb_tourdata, " .
|
|
"jb.jb_service, " .
|
|
"jb.jb_commission_no, " .
|
|
"tr.tr_commission_no, " .
|
|
"SUBSTRING_INDEX(jb.jb_tourdata, '|', -1) AS country_codes, " .
|
|
"hq.hq_mnemonic, " .
|
|
"cr_hq.hq_mnemonic AS cr_hq_mnemonic, " .
|
|
"cr_parent_hq.hq_mnemonic AS cr_parent_hq_mnemonic, " .
|
|
"'" . EXPORT_CONST_01 . "' AS const_01, " .
|
|
"'" . EXPORT_CONST_02 . "' AS const_02, " .
|
|
"'" . EXPORT_CONST_03 . "' AS const_03, " .
|
|
"gdc_toll.gdc_content AS jb_toll, " .
|
|
"gdc_third_country.gdc_content AS jb_third_country ";
|
|
|
|
// Invoice
|
|
$select_clause2 = $select_clause . ", " .
|
|
"cs.cs_eid AS cs_eid1, " .
|
|
"cs.cs_eid AS cs_eid2, " .
|
|
"cs_hq.hq_mnemonic AS cs_hq_mnemonic, " .
|
|
"cs.csc_id AS csc_id_root, " .
|
|
"csc.csc_name ";
|
|
if ($parExportAddPayingCostcenteraddressDataAdt1 == "1") :
|
|
$select_clause2 .= ", " .
|
|
"cscad1.cscad_comp AS cscad1_comp, " .
|
|
"cscad1.cscad_comp2 AS cscad1_comp2, " .
|
|
"cscad1.cscad_comp3 AS cscad1_comp3, " .
|
|
"cscad1.cscad_comp4 AS cscad1_comp4, " .
|
|
"ad_cscad1.ad_street AS ad_cscad1_street, " .
|
|
"cscad1.cscad_hsno AS ad_cscad1_hsno, " .
|
|
"ad_cscad1.ad_zipcode AS ad_cscad1_zipcode, " .
|
|
"ad_cscad1.ad_city AS ad_cscad1_city ";
|
|
endif;
|
|
if ($parExportAddPayingCostcenteraddressDataAdt2 == "1") :
|
|
$select_clause2 .= ", " .
|
|
"cscad2.cscad_comp AS cscad2_comp, " .
|
|
"cscad2.cscad_comp2 AS cscad2_comp2, " .
|
|
"cscad2.cscad_comp3 AS cscad2_comp3, " .
|
|
"cscad2.cscad_comp4 AS cscad2_comp4, " .
|
|
"ad_cscad2.ad_street AS ad_cscad2_street, " .
|
|
"cscad2.cscad_hsno AS ad_cscad2_hsno, " .
|
|
"ad_cscad2.ad_zipcode AS ad_cscad2_zipcode, " .
|
|
"ad_cscad2.ad_city AS ad_cscad2_city ";
|
|
endif;
|
|
if ($parExportAddPayingCostcenteraddressDataAdt3 == "1") :
|
|
$select_clause2 .= ", " .
|
|
"cscad3.cscad_comp AS cscad3_comp, " .
|
|
"cscad3.cscad_comp2 AS cscad3_comp2, " .
|
|
"cscad3.cscad_comp3 AS cscad3_comp3, " .
|
|
"cscad3.cscad_comp4 AS cscad3_comp4, " .
|
|
"ad_cscad3.ad_street AS ad_cscad3_street, " .
|
|
"cscad3.cscad_hsno AS ad_cscad3_hsno, " .
|
|
"ad_cscad3.ad_zipcode AS ad_cscad3_zipcode, " .
|
|
"ad_cscad3.ad_city AS ad_cscad3_city ";
|
|
endif;
|
|
if ($parExportAddCustomerMasterDataAddressAndCompanyName == "1") :
|
|
$select_clause2 .= ", " .
|
|
"cmp2.cmp_comp AS cmp2_comp, " .
|
|
"cmp2.cmp_comp2 AS cmp2_comp2, " .
|
|
"cmp2.cmp_comp3 AS cmp2_comp3, " .
|
|
"cmp2.cmp_comp4 AS cmp2_comp4, " .
|
|
"ad_mstrdt.ad_street AS ad_mstrdt_street, " .
|
|
"cmp2.cmp_hsno AS ad_mstrdt_hsno, " .
|
|
"ad_mstrdt.ad_zipcode AS ad_mstrdt_zipcode, " .
|
|
"ad_mstrdt.ad_city AS ad_mstrdt_city ";
|
|
endif;
|
|
|
|
|
|
// Cash
|
|
$from_clause = "FROM headquarters AS hq, job AS jb " .
|
|
" LEFT JOIN courier AS cr ON jb.cr_id=cr.cr_id " .
|
|
" LEFT JOIN headquarters AS cr_hq ON cr.hq_id = cr_hq.hq_id " .
|
|
" LEFT JOIN courier AS cr_parent ON cr.cr_id_parent = cr_parent.cr_id " .
|
|
" LEFT JOIN headquarters AS cr_parent_hq ON cr_parent.hq_id = cr_parent_hq.hq_id " .
|
|
" LEFT JOIN costcenter AS csc2 ON " . $fromClauseRelevantCostcenter . "=csc2.csc_id " .
|
|
($parExportAddPayingCostcenteraddressDataAdt1 == "1" ? " LEFT JOIN costcenteraddress AS cscad1 ON cscad1.csc_id=csc2.csc_id AND cscad1.adt_id='1' LEFT JOIN address AS ad_cscad1 ON ad_cscad1.ad_id=cscad1.ad_id " : "") .
|
|
($parExportAddPayingCostcenteraddressDataAdt2 == "1" ? " LEFT JOIN costcenteraddress AS cscad2 ON cscad2.csc_id=csc2.csc_id AND cscad2.adt_id='2' LEFT JOIN address AS ad_cscad2 ON ad_cscad2.ad_id=cscad2.ad_id " : "") .
|
|
($parExportAddPayingCostcenteraddressDataAdt3 == "1" ? " LEFT JOIN costcenteraddress AS cscad3 ON cscad3.csc_id=csc2.csc_id AND cscad3.adt_id='3' LEFT JOIN address AS ad_cscad3 ON ad_cscad3.ad_id=cscad3.ad_id " : "") .
|
|
" LEFT JOIN customer AS cs2 ON cs2.cs_id=csc2.cs_id " .
|
|
" LEFT JOIN company AS cmp2 ON cmp2.cmp_id=cs2.cmp_id " .
|
|
($parExportAddCustomerMasterDataAddressAndCompanyName == "1" ? " LEFT JOIN address AS ad_mstrdt ON ad_mstrdt.ad_id=cmp2.ad_id " : "") .
|
|
" LEFT JOIN job AS jb2 ON jb.jb_id_parent=jb2.jb_id " .
|
|
" LEFT JOIN tour AS tr ON tr.jb_id = jb.jb_id AND tr.tr_sort = '1' " .
|
|
" LEFT JOIN address AS ad ON ad.ad_id = tr.ad_id " .
|
|
" LEFT JOIN genericdatacontainer AS gdc_toll ON gdc_toll.gdc_obj_id = jb.jb_id AND gdc_toll.gdc_obj_type = 'jb' AND gdc_toll.gdc_gen_fieldname = 'jb_toll' " .
|
|
" LEFT JOIN genericdatacontainer AS gdc_third_country ON gdc_third_country.gdc_obj_id = jb.jb_id AND gdc_third_country.gdc_obj_type = 'jb' AND gdc_third_country.gdc_gen_fieldname = 'jb_third_country', " .
|
|
" " . $fromClausePriceTable . " ";
|
|
|
|
// Invoice
|
|
$from_clause2 = $from_clause . ", " .
|
|
"costcenter AS csc, " .
|
|
"customer AS cs, " .
|
|
"headquarters AS cs_hq ";
|
|
|
|
// Cash
|
|
$where_clause = "WHERE jb.hq_id = '$hq_id' AND " .
|
|
" jb.hq_id = hq.hq_id AND " .
|
|
" (" . $whereClauseJbStatus . ") AND " .
|
|
" (jb.csc_id_payer = '-1') AND " .
|
|
$whereClauseRelevantExportTime .
|
|
$whereClauseIncomplete .
|
|
" (jb.jb_id = " . $priceTableAlias . ".jb_id) ";
|
|
// Invoice
|
|
$where_clause2 = "WHERE jb.hq_id = '$hq_id' AND " .
|
|
" jb.hq_id = hq.hq_id AND " .
|
|
" (" . $whereClauseJbStatus . ") AND " .
|
|
" (jb.csc_id_payer != '-1') AND " .
|
|
$whereClauseRelevantExportTime .
|
|
$whereClauseIncomplete .
|
|
" (jb.jb_id = " . $priceTableAlias . ".jb_id) AND ";
|
|
// Change (end)customer data to related data (e.g. market)
|
|
if ($fromClauseRelevantCostcenter == "jb.csc_id_payer") :
|
|
$where_clause2 .= " (trs.csc_id = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.hq_id = cs_hq.hq_id) ";
|
|
elseif($fromClauseRelevantCostcenter == "jb.csc_id_related") :
|
|
$where_clause2 .= " (jb.csc_id_related = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.hq_id = cs_hq.hq_id) ";
|
|
else :
|
|
$where_clause2 .= " (" . $priceTableAlias . ".csc_id = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.hq_id = cs_hq.hq_id) ";
|
|
endif;
|
|
|
|
// Filter export according to only one specified customer
|
|
$f_status_filter_remember = "";
|
|
if ($f_cs_eid_filter != ""):
|
|
$where_clause2 .= " AND (cs.cs_eid = '" . $f_cs_eid_filter . "') ";
|
|
$f_status_filter_remember = $f_status_filter;
|
|
$f_status_filter = "2"; // If customer is specified then only export invoice data and no cash data
|
|
endif;
|
|
|
|
// Filter export according specified vehicle types (if NO type is specified then this filter is not be used)
|
|
$f_vht_filter_len = count($f_vht_filter);
|
|
if ($f_vht_filter_len > 0) :
|
|
$whereClauseFilterVehicleType = "";
|
|
for ($i = 0; $i < $f_vht_filter_len; $i++) :
|
|
if ($whereClauseFilterVehicleType != "") :
|
|
$whereClauseFilterVehicleType .= " OR ";
|
|
endif;
|
|
$whereClauseFilterVehicleType .= " cr.vht_id_inv = '" . $f_vht_filter[$i] . "'";
|
|
endfor;
|
|
if ($whereClauseFilterVehicleType != "") :
|
|
$whereClauseFilterVehicleType = " AND (" . $whereClauseFilterVehicleType . ") ";
|
|
$where_clause .= $whereClauseFilterVehicleType;
|
|
$where_clause2 .= $whereClauseFilterVehicleType;
|
|
endif;
|
|
endif;
|
|
|
|
$goupby_clause = "GROUP BY " . $priceTableAlias . ".jb_id, " . $priceTableAlias . ".csc_id " . $whereClausePrice; // $priceTableAlias =!= "trs" by default
|
|
|
|
$orderby_clause = "ORDER BY " . $priceTableAlias . ".jb_id, " . $priceTableAlias . ".csc_id ";
|
|
|
|
// Only jobs WITH cr_id AND cr_sid
|
|
// $where_clause .= " AND (NOT isnull(jb.cr_id)) AND jb.cr_id != '0' AND (NOT isnull(jb.cr_sid)) AND jb.cr_sid != '' ";
|
|
// $where_clause2 .= " AND (NOT isnull(jb.cr_id)) AND jb.cr_id != '0' AND (NOT isnull(jb.cr_sid)) AND jb.cr_sid != '' ";
|
|
|
|
// Storno and export filter
|
|
$where_clause .= $whereClauseStorno . $whereClauseFilterExport;
|
|
$where_clause2 .= $whereClauseStorno . $whereClauseFilterExport;
|
|
|
|
// Date-range
|
|
// "jb_finishtime" = <time the courier has finished the job in the past> or "jb_booktime" = <current timestamp>, etc.
|
|
if ($fromDateRange != "") :
|
|
$where_clause .= " AND " . $whereClauseDatetimeField . " >= '$fromDateRange' "; // $whereClauseDatetimeField =!= jb.jb_finishtime per default (defined above) !!!!
|
|
$where_clause2 .= " AND " . $whereClauseDatetimeField . " >= '$fromDateRange' ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause .= " AND " . $whereClauseDatetimeField . " <= '$toDateRange' ";
|
|
$where_clause2 .= " AND " . $whereClauseDatetimeField . " <= '$toDateRange' ";
|
|
endif;
|
|
|
|
// Cash or both
|
|
if ($f_status_filter == "0" || $f_status_filter == "1"):
|
|
$sqlquery = $select_clause . $from_clause . $where_clause . $goupby_clause . $orderby_clause;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
if ($f_status_filter == "1") :
|
|
$rowArray = convertRowToArray($result);
|
|
else :
|
|
$rowArray1 = convertRowToArray($result);
|
|
endif;
|
|
endif;
|
|
// Invoice or both
|
|
if ($f_status_filter == "0" || $f_status_filter == "2"):
|
|
$sqlquery2 = $select_clause2 . $from_clause2 . $where_clause2 . $goupby_clause . $orderby_clause;
|
|
|
|
$result = $db->query($sqlquery2);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
if ($f_status_filter == "2") :
|
|
$rowArray = convertRowToArray($result);
|
|
else :
|
|
$rowArray2 = convertRowToArray($result);
|
|
endif;
|
|
endif;
|
|
|
|
if ($f_status_filter == "0") :
|
|
$rowArray = array_merge($rowArray1, $rowArray2);
|
|
usort($rowArray, "cmp_rowArray");
|
|
endif;
|
|
|
|
// Reset $f_status_filter if special customer was selected
|
|
if ($f_status_filter_remember != "") :
|
|
$f_status_filter = $f_status_filter_remember;
|
|
$f_status_filter_remember = "";
|
|
endif;
|
|
endif;
|
|
|
|
|
|
// **** Selection of collected business volume ("current" cash flow) ***
|
|
if ($f_exp_category == "9" && $f_cs_eid_filter != "") :
|
|
|
|
$jbpCsId = getFieldValueFromId("customer","cs_eid",$f_cs_eid_filter,"cs_id"); // Get "cs_id" from "cs_eid"
|
|
$tmpGroups = getFieldValueFromId("customer","cs_id",$jbpCsId,"cs_group"); // Get "grp_id" from "cs_id"
|
|
$tmpGroups = spliti(",", $tmpGroups);
|
|
$jbpCsGrpId = $tmpGroups[1];
|
|
// Get constants, particularly depending on customer GROUP parameters
|
|
if ($jbpCsId != "" && is_numeric($jbpCsId)) :
|
|
$constCat_01 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_01_CS", $jbpCsId, "0");
|
|
$constCat_02 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_02_CS", $jbpCsId, "0");
|
|
$constCat_03 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_03_CS", $jbpCsId, "0");
|
|
$constCat_04 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_04_CS", $jbpCsId, "0");
|
|
$constCat_05 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_05_CS", $jbpCsId, "0");
|
|
endif;
|
|
if ($jbpCsGrpId != "" && is_numeric($jbpCsGrpId)) :
|
|
if ($constCat_01 == "") : $constCat_01 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_01", $jbpCsGrpId, $hq_id); endif;
|
|
if ($constCat_02 == "") : $constCat_02 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_02", $jbpCsGrpId, $hq_id); endif;
|
|
if ($constCat_03 == "") : $constCat_03 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_03", $jbpCsGrpId, $hq_id); endif;
|
|
if ($constCat_04 == "") : $constCat_04 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_04", $jbpCsGrpId, $hq_id); endif;
|
|
if ($constCat_05 == "") : $constCat_05 = getObjectBasedParameterValue("EXPORT_CAT_09_CONST_05", $jbpCsGrpId, $hq_id); endif;
|
|
endif;
|
|
|
|
$jbpcModeFilterWhereClause = "";
|
|
if ($cat09_paymentTypesRequested != "") :
|
|
$jbpcModeFilterWhereClause = " AND jbpc.jbpc_mode IN (" . $cat09_paymentTypesRequested . ") ";
|
|
endif;
|
|
|
|
$sqlquery = "SELECT jbpc.jbpc_id,"
|
|
. " '" . EXPORT_HQ_KEY . " ' AS cmp_no,"
|
|
. " CONCAT(SUBSTRING(NOW(), 1, 4), " . "SUBSTRING(NOW(), 6, 2), ". "SUBSTRING(NOW(), 9, 2)) AS currentdate,"
|
|
. " CONCAT(SUBSTRING(jbpc.jbpc_bookingdate, 1, 4), " . "SUBSTRING(jbpc.jbpc_bookingdate, 6, 2), ". "SUBSTRING(jbpc.jbpc_bookingdate, 9, 2)) AS bookingdate,"
|
|
. " jbpc.jbpc_mode,"
|
|
. " jbpc.jbpc_price AS price,"
|
|
. " jbpc.jbpc_invtext,"
|
|
. " cs.cs_eid AS cs_eid1,"
|
|
. " cs.cs_eid AS cs_eid2,"
|
|
. " '' AS invtext,"
|
|
. " '' AS freetext_1,"
|
|
. " '' AS freetext_2,"
|
|
. " '' AS freetext_3,"
|
|
. " '' AS postage,"
|
|
. " '' AS tax_rate,"
|
|
. " '' AS tax_rate_sign,"
|
|
. " '' AS cr_price,"
|
|
. " '' AS subtotalprice,"
|
|
. " '' AS markup,"
|
|
. " '' AS cr_subprice,"
|
|
. " '" . $constCat_01 . "' AS const_901,"
|
|
. " '" . $constCat_02 . "' AS const_902,"
|
|
. " '" . $constCat_03 . "' AS const_903,"
|
|
. " '" . $constCat_04 . "' AS const_904,"
|
|
. " '" . $constCat_05 . "' AS const_905,"
|
|
. " '" . EXPORT_CAT_09_CONST_VAT_SPECIAL . "' AS VAT_special,"
|
|
. " '" . EXPORT_CAT_09_CONST_VHT_ID . "' AS vht_id,"
|
|
. " '" . EXPORT_CAT_09_CONST_PRODUCT . "' AS prod_descr,"
|
|
. " '" . EXPORT_CAT_09_CONST_PRICE_SPECIAL . "' AS price_special"
|
|
. " FROM jobpaymentcollection AS jbpc, customer AS cs"
|
|
. " WHERE cs.cs_id = '" . $jbpCsId . "' AND"
|
|
. " cs.cs_id = jbpc.cs_id AND"
|
|
. " (ISNULL(jbpc.jbpc_export_time) OR jbpc.jbpc_export_time = '0000-00-00 00:00:00') AND"
|
|
. " EXISTS (SELECT * FROM jobpayment AS jbp2, job AS jb2 WHERE jbp2.jbpc_id = jbpc.jbpc_id AND jbp2.jb_id = jb2.jb_id) "
|
|
. $jbpcModeFilterWhereClause
|
|
. " ORDER BY jbpc.jbpc_id";
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
endif; // Headquarters only
|
|
|
|
|
|
|
|
// *****************
|
|
// * ONLY CUSTOMER *
|
|
// *****************
|
|
if ($userTypeName == "cs") :
|
|
|
|
// Get status of the customer according to being meta customer
|
|
// If not then export only data of the current customer else export all data (group customer AND meta customer) and ignore costcenter hierarchy
|
|
$isMetaCs = "0";
|
|
if (existsEntry("customer",array("cs_id_parent",$customerId))) :
|
|
$isMetaCs = "1";
|
|
endif;
|
|
|
|
// Selection of the costcenters
|
|
if ($f_exp_category == "100") :
|
|
|
|
if (mcIsSet($orderClause) == "") : $orderClause = "csc.csc_name"; endif;
|
|
$sqlquery = "SELECT " . $supersetFieldString
|
|
. " FROM customer AS cs, costcenter AS csc, costcenteraddress AS cscad, address AS ad"
|
|
. " WHERE cs.cs_id = '$customerId' AND"
|
|
. " cs.cs_id = csc.cs_id AND"
|
|
. " csc.csc_id = cscad.csc_id AND"
|
|
. " cscad.ad_id = ad.ad_id "
|
|
. " ORDER BY " . $orderClause;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// Selection of jobs
|
|
if ($f_exp_category == "110") :
|
|
|
|
$select_clause = "SELECT jb.jb_id, " .
|
|
"jb.cr_sid, " .
|
|
"CONCAT(SUBSTRING(jb.jb_ordertime, 1, 4), " .
|
|
"SUBSTRING(jb.jb_ordertime, 6, 2), ".
|
|
"SUBSTRING(jb.jb_ordertime, 9, 2)) AS orderdate, " .
|
|
"SUBSTRING(jb.jb_ordertime, 12, 5) AS ordertime, " .
|
|
"CONCAT(SUBSTRING(jb.jb_finishtime, 9, 2), " .
|
|
"SUBSTRING(jb.jb_finishtime, 6, 2), ".
|
|
"SUBSTRING(jb.jb_finishtime, 3, 2)) AS finishdate, " .
|
|
"SUBSTRING(jb.jb_finishtime, 12, 5) AS finishtime, " .
|
|
"CONCAT(SUBSTRING(jb.jb_booktime, 1, 4), " .
|
|
"SUBSTRING(jb.jb_booktime, 6, 2), ".
|
|
"SUBSTRING(jb.jb_booktime, 9, 2)) AS bookdate, " .
|
|
"SUBSTRING(jb.jb_booktime, 12, 5) AS booktime, " .
|
|
"jb.jb_invtext, " .
|
|
"jb.jb_freetext_1, " .
|
|
$priceFormular . " AS price, " .
|
|
"(jb.jb_totalprice * (1 + (jb.jb_sales_tax_rate / 100))) AS gross_price, " .
|
|
"jb.csc_id_payer AS cash, " .
|
|
"jb.jb_sales_tax_rate AS tax_rate, " .
|
|
"jb.jb_sales_tax_rate_sign AS tax_rate_sign, " .
|
|
"cs.cs_eid, " .
|
|
"csc.csc_name, " .
|
|
"tr.tr_comp, " .
|
|
"tr.tr_comp2, " .
|
|
"tr.tr_hsno, " .
|
|
getDBFields("address") . "," .
|
|
getDBFields("vehicletype") . "," .
|
|
getDBFields("ad_street_hsno") . "," .
|
|
"tr.tr_person, " .
|
|
"tr.tr_commission_no, " .
|
|
"tr.tr_signname, " .
|
|
"tr.tr_cs_freetext, " .
|
|
"CONCAT(ad2.ad_street,' ',tr2.tr_hsno,', ',ad2.ad_zipcode) AS target_address, " .
|
|
"tr2.tr_comp AS tr2_comp, " .
|
|
"tr2.tr_comp2 AS tr2_comp2, " .
|
|
"ad2.ad_street AS ad2_street, " .
|
|
"tr2.tr_hsno AS tr2_hsno, " .
|
|
"ad2.ad_zipcode AS ad2_zipcode, " .
|
|
"ad2.ad_city AS ad2_city, " .
|
|
"tr2.tr_person AS tr2_person, " .
|
|
"tr2.tr_commission_no AS tr2_commission_no, " .
|
|
"tr2.tr_signname, " .
|
|
"tr2.tr_cs_freetext AS tr2_cs_freetext, " .
|
|
"inv.inv_no, " .
|
|
"CONCAT(SUBSTRING(inv.inv_datetime, 9, 2), " .
|
|
"SUBSTRING(inv.inv_datetime, 6, 2), ".
|
|
"SUBSTRING(inv.inv_datetime, 3, 2)) AS invdate, " .
|
|
"SUBSTRING(inv.inv_datetime, 12, 5) AS invtime, " .
|
|
"usr.usr_firstname, " .
|
|
"usr.usr_name ";
|
|
|
|
$jobListNumOfHops = getParameterValue("0", "JOBLIST_NUM_OF_HOPS", "0");
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 3; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$select_clause .= "," .
|
|
"CONCAT(ad" . $j . ".ad_street,' ',tr" . $j . ".tr_hsno,', ',ad" . $j . ".ad_zipcode) AS target_address_" . $j . ", " .
|
|
"tr" . $j . ".tr_comp AS tr" . $j . "_comp, " .
|
|
"tr" . $j . ".tr_comp2 AS tr" . $j . "_comp2, " .
|
|
"ad" . $j . ".ad_street AS ad" . $j . "_street, " .
|
|
"tr" . $j . ".tr_hsno AS tr" . $j . "_hsno, " .
|
|
"ad" . $j . ".ad_zipcode AS ad" . $j . "_zipcode, " .
|
|
"ad" . $j . ".ad_city AS ad" . $j . "_city, " .
|
|
"tr" . $j . ".tr_person AS tr" . $j . "_person, " .
|
|
"tr" . $j . ".tr_signname AS tr" . $j . "_signname, " .
|
|
"tr" . $j . ".tr_commission_no AS tr" . $j . "_commission_no, " .
|
|
"tr" . $j . ".tr_cs_freetext AS tr" . $j . "_cs_freetext ";
|
|
endfor;
|
|
endif;
|
|
|
|
|
|
$from_clause = "FROM tourservice AS trs, tour AS tr, address AS ad, metatype AS vht, costcenter AS csc, customer AS cs, job AS jb ";
|
|
// Price
|
|
// $from_clause .= " LEFT JOIN tourservice AS trs ON trs.jb_id = jb.jb_id AND trs.csc_id = csc.csc_id AND csc.cs_id = cs.cs_id ";
|
|
// Target address (only 2nd stop)
|
|
$from_clause .= " LEFT JOIN tour AS tr2 ON tr2.jb_id = jb.jb_id AND tr2.tr_sort = '2' ";
|
|
$from_clause .= " LEFT JOIN address AS ad2 ON ad2.ad_id = tr2.ad_id ";
|
|
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 3; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$from_clause .= " LEFT JOIN tour AS tr" . $j . " ON tr" . $j . ".jb_id = jb.jb_id AND tr" . $j . ".tr_sort = '" . $j . "' ";
|
|
$from_clause .= " LEFT JOIN address AS ad" . $j . " ON ad" . $j . ".ad_id = tr" . $j . ".ad_id ";
|
|
endfor;
|
|
endif;
|
|
|
|
$from_clause .= " LEFT JOIN invoice AS inv ON inv.jb_id = jb.jb_id ";
|
|
$from_clause .= " LEFT JOIN user AS usr ON usr.usr_id = jb.emp_id ";
|
|
|
|
$where_clause = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause .= "jb.jb_id = trs.jb_id AND trs.csc_id = csc.csc_id AND ";
|
|
$where_clause .= "csc.cs_id = cs.cs_id AND cs.cs_id = '" . $customerId . "' AND ";
|
|
$where_clause .= "((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR " .
|
|
"(jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
|
|
$where_clause .= "(jb.vht_id = vht.mt_sort AND vht.mt_type = 'vehicletype') AND ";
|
|
$where_clause .= "jb.jb_id = tr.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause .= " AND jb.jb_finishtime >= '$fromDateRange' ";
|
|
// $where_clause .= " AND jb.jb_booktime >= '$fromDateRange' ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause .= " AND jb.jb_finishtime <= '$toDateRange' ";
|
|
// $where_clause .= " AND jb.jb_booktime <= '$toDateRange' ";
|
|
endif;
|
|
|
|
$goupby_clause = "GROUP BY trs.jb_id, trs.csc_id ";
|
|
|
|
$orderby_clause = "ORDER BY jb.jb_ordertime";
|
|
|
|
|
|
if ($isMetaCs == "1") :
|
|
$where_clause2 = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause2 .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause2 .= "jb.jb_id = trs.jb_id AND trs.csc_id = csc.csc_id AND ";
|
|
$where_clause2 .= "csc.cs_id = cs.cs_id AND cs.cs_id_parent = '" . $customerId . "' AND ";
|
|
|
|
$where_clause2 .= "(jb.vht_id = vht.mt_sort AND vht.mt_type = 'vehicletype') AND ";
|
|
$where_clause2 .= "jb.jb_id = tr.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause2 .= " AND jb.jb_finishtime >= '$fromDateRange' ";
|
|
// $where_clause2 .= " AND jb.jb_booktime >= '$fromDateRange' ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause2 .= " AND jb.jb_finishtime <= '$toDateRange' ";
|
|
// $where_clause2 .= " AND jb.jb_booktime <= '$toDateRange' ";
|
|
endif;
|
|
|
|
$orderby_clause2 = "ORDER BY orderdate, ordertime";
|
|
endif;
|
|
|
|
|
|
// Generate SQL statement
|
|
if ($isMetaCs == "0") :
|
|
$sqlquery = $select_clause . $from_clause . $where_clause . $goupby_clause . $orderby_clause;
|
|
else :
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION ("
|
|
. $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") " . $orderby_clause2;
|
|
endif;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// Selection of jobs and their services (calculator items)
|
|
if ($f_exp_category == "115") :
|
|
|
|
$select_clause = "SELECT jbc.jb_id, " .
|
|
" jbc.tr_sort, " .
|
|
" jbc.srv_name, " .
|
|
" jbc.srvt_name, " .
|
|
" jbc.jbc_amount, " .
|
|
" jbc.jbc_price, " .
|
|
" jbc.jbc_totalprice ";
|
|
|
|
$from_clause = "FROM jobcalculator AS jbc, job AS jb, costcenter AS csc, customer AS cs ";
|
|
|
|
$where_clause = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause .= "csc.cs_id = cs.cs_id AND cs.cs_id = '" . $customerId . "' AND ";
|
|
$where_clause .= "((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR " .
|
|
"(jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
$where_clause .= "jb.jb_id = jbc.jb_id ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause .= " AND jb.jb_finishtime >= '$fromDateRange' ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause .= " AND jb.jb_finishtime <= '$toDateRange' ";
|
|
endif;
|
|
|
|
$goupby_clause = "";
|
|
|
|
$orderby_clause = "ORDER BY jbc.jb_id, jbc.tr_sort";
|
|
|
|
|
|
if ($isMetaCs == "1") :
|
|
$where_clause2 = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause2 .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause2 .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause2 .= "csc.cs_id = cs.cs_id AND cs.cs_id_parent = '" . $customerId . "' AND ";
|
|
$where_clause2 .= "jb.jb_id = jbc.jb_id ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause2 .= " AND jb.jb_finishtime >= '$fromDateRange' ";
|
|
// $where_clause2 .= " AND jb.jb_booktime >= '$fromDateRange' ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause2 .= " AND jb.jb_finishtime <= '$toDateRange' ";
|
|
// $where_clause2 .= " AND jb.jb_booktime <= '$toDateRange' ";
|
|
endif;
|
|
|
|
$orderby_clause2 = "ORDER BY jb_id, tr_sort";
|
|
endif;
|
|
|
|
|
|
// Generate SQL statement
|
|
if ($isMetaCs == "0") :
|
|
$sqlquery = $select_clause . $from_clause . $where_clause . $goupby_clause . $orderby_clause;
|
|
else :
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION ("
|
|
. $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") " . $orderby_clause2;
|
|
endif;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// Selection of jobs and their services (calculator items)
|
|
if ($f_exp_category == "120") :
|
|
|
|
// xxxx_clause : Jobs having at least one entry in "jobcalculator"
|
|
// xxxx_clause_B : Jobs having NO entry in "jobcalculator"
|
|
|
|
$select_clause = "SELECT jb.jb_id, " .
|
|
" jb.jb_ordertime, " .
|
|
" jb.jb_warningtime, " .
|
|
" jb.jb_finishtime, " .
|
|
" jb.cr_sid, " .
|
|
" gdc_jb_tourname.gdc_content AS gdc_jb_tourname, " .
|
|
" cs.cs_eid, " .
|
|
" cmp.cmp_comp, " .
|
|
" cmp.cmp_comp2, " .
|
|
" cmp.cmp_comp3, " .
|
|
" cmp.cmp_comp4, ";
|
|
|
|
$select_clause_B = $select_clause .
|
|
" '1' AS tr_sort, " .
|
|
" '' AS tr_comp, " .
|
|
" '' AS tr_person, " .
|
|
" '' AS tr_commission_no, " .
|
|
" '' AS tr_finishtime, " .
|
|
" '' AS ad_street, " .
|
|
" '' AS tr_hsno, " .
|
|
" '' AS ad_zipcode, " .
|
|
" '' AS ad_city, " .
|
|
" '' AS gdc_arr_tour_content, " .
|
|
" '' AS gdc_tr_info_0, " .
|
|
" '' AS gdc_tr_info_1, " .
|
|
" '' AS srv_name, " .
|
|
" '' AS srvt_name, " .
|
|
" '1' AS jbc_amount, " .
|
|
" jb.jb_totalprice AS jbc_price, " .
|
|
" jb.jb_totalprice AS jbc_totalprice ";
|
|
|
|
$select_clause_C = $select_clause .
|
|
" '1' AS tr_sort, " .
|
|
" '' AS tr_comp, " .
|
|
" '' AS tr_person, " .
|
|
" '' AS tr_commission_no, " .
|
|
" '' AS tr_finishtime, " .
|
|
" '' AS ad_street, " .
|
|
" '' AS tr_hsno, " .
|
|
" '' AS ad_zipcode, " .
|
|
" '' AS ad_city, " .
|
|
" '' AS gdc_arr_tour_content, " .
|
|
" '' AS gdc_tr_info_0, " .
|
|
" '' AS gdc_tr_info_1, " .
|
|
" jbc.srv_name, " .
|
|
" jbc.srvt_name, " .
|
|
" jbc.jbc_amount, " .
|
|
" jbc.jbc_price, " .
|
|
" jbc.jbc_totalprice ";
|
|
|
|
$select_clause .= " tr.tr_sort, " .
|
|
" tr.tr_comp, " .
|
|
" tr.tr_person, " .
|
|
" tr.tr_commission_no, " .
|
|
" tr.tr_finishtime, " .
|
|
" ad.ad_street, " .
|
|
" tr.tr_hsno, " .
|
|
" ad.ad_zipcode, " .
|
|
" ad.ad_city, " .
|
|
" LEFT(gdc_arr_tour.gdc_content,14) AS gdc_arr_tour_content, " .
|
|
" gdc_tr_info_0.gdc_content AS gdc_tr_info_0, " .
|
|
" gdc_tr_info_1.gdc_content AS gdc_tr_info_1, " .
|
|
" jbc.srv_name, " .
|
|
" jbc.srvt_name, " .
|
|
" jbc.jbc_amount, " .
|
|
" jbc.jbc_price, " .
|
|
" jbc.jbc_totalprice ";
|
|
|
|
// GDC (job)
|
|
$fromClauseGdcJob = " LEFT JOIN genericdatacontainer AS gdc_jb_tourname ON gdc_jb_tourname.gdc_obj_id = jb.jb_id AND gdc_jb_tourname.gdc_obj_type = 'jb' AND gdc_jb_tourname.gdc_gen_fieldname = 'tourname' ";
|
|
|
|
// GDC (tour)
|
|
$fromClauseGdcTour = " LEFT JOIN genericdatacontainer AS gdc_arr_tour ON gdc_arr_tour.gdc_obj_id = tr.tr_id AND gdc_arr_tour.gdc_obj_type = 'tr' AND gdc_arr_tour.gdc_gen_fieldname = 'arr_tour' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc_tr_info_0 ON gdc_tr_info_0.gdc_obj_id = tr.tr_id AND gdc_tr_info_0.gdc_obj_type = 'tr' AND gdc_tr_info_0.gdc_gen_fieldname = 'info_0' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc_tr_info_1 ON gdc_tr_info_1.gdc_obj_id = tr.tr_id AND gdc_tr_info_1.gdc_obj_type = 'tr' AND gdc_tr_info_1.gdc_gen_fieldname = 'info_1' ";
|
|
|
|
// $from_clause = "FROM costcenter AS csc, customer AS cs, company AS cmp, job AS jb LEFT JOIN tour AS tr ON jb.jb_id = tr.jb_id LEFT JOIN jobcalculator AS jbc ON tr.jb_id = jbc.jb_id AND tr.tr_sort = jbc.tr_sort ";
|
|
$from_clause = "FROM costcenter AS csc, customer AS cs, company AS cmp, job AS jb " . $fromClauseGdcJob;
|
|
$from_clause_B = $from_clause . " LEFT JOIN tour AS tr ON jb.jb_id = tr.jb_id AND tr.tr_sort = '1' LEFT JOIN address AS ad ON tr.ad_id = ad.ad_id " . $fromClauseGdcTour;
|
|
$from_clause_C = $from_clause . " LEFT JOIN tour AS tr ON jb.jb_id = tr.jb_id AND tr.tr_sort = '1' LEFT JOIN address AS ad ON tr.ad_id = ad.ad_id " . $fromClauseGdcTour . ", jobcalculator AS jbc ";
|
|
$from_clause .= ", tour AS tr LEFT JOIN address AS ad ON tr.ad_id = ad.ad_id " . $fromClauseGdcTour . ", jobcalculator AS jbc ";
|
|
|
|
$where_clause = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause .= "csc.cs_id = cs.cs_id AND cs.cs_id = '" . $customerId . "' AND ";
|
|
$where_clause .= "((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR " .
|
|
"(jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
$where_clause .= "cs.cmp_id = cmp.cmp_id AND ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause .= " jb.jb_finishtime >= '$fromDateRange' AND ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause .= " jb.jb_finishtime <= '$toDateRange' AND ";
|
|
endif;
|
|
|
|
$where_clause_B = $where_clause . "(NOT EXISTS (SELECT * FROM jobcalculator AS jbc WHERE jbc.jb_id = jb.jb_id)) ";
|
|
$where_clause_C = $where_clause . "jb.jb_id = jbc.jb_id AND jbc.tr_sort = '0' ";
|
|
$where_clause .= "jb.jb_id = tr.jb_id AND tr.jb_id = jbc.jb_id AND tr.tr_sort = jbc.tr_sort ";
|
|
|
|
$goupby_clause = "";
|
|
|
|
$orderby_clause = "ORDER BY jb_id";
|
|
|
|
|
|
if ($isMetaCs == "1") :
|
|
$where_clause2 = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause2 .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause2 .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause2 .= "csc.cs_id = cs.cs_id AND cs.cs_id_parent = '" . $customerId . "' AND ";
|
|
$where_clause2 .= "cs.cmp_id = cmp.cmp_id AND ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause2 .= " jb.jb_finishtime >= '$fromDateRange' AND ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause2 .= " jb.jb_finishtime <= '$toDateRange' AND ";
|
|
endif;
|
|
|
|
$where_clause2_B = $where_clause2 . "(NOT EXISTS (SELECT * FROM jobcalculator AS jbc WHERE jbc.jb_id = jb.jb_id)) ";
|
|
$where_clause2_C = $where_clause2 . "jb.jb_id = jbc.jb_id AND jbc.tr_sort = '0' ";
|
|
$where_clause2 .= "jb.jb_id = tr.jb_id AND tr.jb_id = jbc.jb_id AND tr.tr_sort = jbc.tr_sort ";
|
|
|
|
$orderby_clause2 = "ORDER BY jb_id, tr_sort";
|
|
endif;
|
|
|
|
|
|
// Generate SQL statement
|
|
if ($isMetaCs == "0") :
|
|
// $sqlquery = $select_clause . $from_clause . $where_clause . $goupby_clause . $orderby_clause;
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C. $where_clause_C . $goupby_clause . ") " . $orderby_clause;
|
|
else :
|
|
// $sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION ("
|
|
// . $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") " . $orderby_clause2;
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause2_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C . $where_clause_C . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C . $where_clause2_C . $goupby_clause . ") ". $orderby_clause2;
|
|
endif;
|
|
// echo $sqlquery; die();
|
|
// writeToFile("../log/export_xxxx.log", $sqlquery);
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
|
|
// All jobs of a customer with stations and their services (calculator items) including job based services and tour based services.
|
|
// Output one service entry (calculator item) per one row (!!!!)
|
|
if ($f_exp_category == "125") :
|
|
|
|
// xxxx_clause : Jobs having at least one entry "tour based" in "jobcalculator"
|
|
// xxxx_clause_B : Jobs having NO entry in "jobcalculator"
|
|
// xxxx_clause_C : Jobs having at least one entry "job based" in "jobcalculator"
|
|
|
|
// $jobListNumOfHops = getParameterValue("0", "JOBLIST_NUM_OF_HOPS", "0");
|
|
$jobListNumOfHops = "2";
|
|
|
|
$select_clause = "SELECT jb.jb_id, " .
|
|
" jb.jb_ordertime, " .
|
|
" jb.jb_warningtime, " .
|
|
" jb.jb_finishtime, " .
|
|
" jb.cr_sid, " .
|
|
" gdc_jb_tourname.gdc_content AS gdc_jb_tourname, " .
|
|
" hq.hq_mnemonic, " .
|
|
" hq.hq_name, " .
|
|
" cs.cs_eid, " .
|
|
" cmp.cmp_comp, " .
|
|
" cmp.cmp_comp2, " .
|
|
" cmp.cmp_comp3, " .
|
|
" cmp.cmp_comp4 ";
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$select_clause .= "," .
|
|
" tr" . $j . ".tr_sort AS tr" . $j . "_sort, " .
|
|
" tr" . $j . ".tr_comp AS tr" . $j . "_comp, " .
|
|
" tr" . $j . ".tr_person AS tr" . $j . "_person, " .
|
|
" tr" . $j . ".tr_commission_no AS tr" . $j . "_commission_no, " .
|
|
" tr" . $j . ".tr_finishtime AS tr" . $j . "_finishtime, " .
|
|
" CAST(LEFT(gdc" . $j . "_arr_tour.gdc_content,14) AS DATETIME) AS tr" . $j . "_arrivaltime, " .
|
|
" gdc" . $j . "_tr_info_0.gdc_content AS gdc" . $j . "_tr_info_0, " .
|
|
" gdc" . $j . "_tr_info_1.gdc_content AS gdc" . $j . "_tr_info_1 ";
|
|
endfor;
|
|
endif;
|
|
|
|
$select_clause_B = $select_clause . ", " .
|
|
" '' AS srv1_name, " .
|
|
" '' AS srvt1_name, " .
|
|
" '' AS jbc1_amount, " .
|
|
" jb.jb_totalprice AS jbc1_price, " .
|
|
" jb.jb_totalprice AS jbc1_totalprice ";
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 2; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$select_clause_B .= "," .
|
|
" '' AS srv" . $j . "_name, " .
|
|
" '' AS srvt" . $j . "_name, " .
|
|
" '' AS jbc" . $j . "_amount, " .
|
|
" '' AS jbc" . $j . "_price, " .
|
|
" '' AS jbc" . $j . "_totalprice ";
|
|
endfor;
|
|
endif;
|
|
|
|
$select_clause_C = $select_clause . ", " .
|
|
" jbc.srv_name AS srv1_name, " .
|
|
" jbc.srvt_name AS srvt1_name, " .
|
|
" jbc.jbc_amount AS jbc1_amount, " .
|
|
" jbc.jbc_price AS jbc1_price, " .
|
|
" jbc.jbc_totalprice AS jbc1_totalprice ";
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 2; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$select_clause_C .= "," .
|
|
" '' AS srv" . $j . "_name, " .
|
|
" '' AS srvt" . $j . "_name, " .
|
|
" '' AS jbc" . $j . "_amount, " .
|
|
" '' AS jbc" . $j . "_price, " .
|
|
" '' AS jbc" . $j . "_totalprice ";
|
|
endfor;
|
|
endif;
|
|
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
|
|
$select_clause .= "," .
|
|
" jbc" . $j . ".srv_name AS srv" . $j . "_name, " .
|
|
" jbc" . $j . ".srvt_name AS srvt" . $j . "_name, " .
|
|
" jbc" . $j . ".jbc_amount AS jbc" . $j . "_amount, " .
|
|
" jbc" . $j . ".jbc_price AS jbc" . $j . "_price, " .
|
|
" jbc" . $j . ".jbc_totalprice AS jbc" . $j . "_totalprice ";
|
|
endfor;
|
|
endif;
|
|
|
|
// GDC (job)
|
|
$fromClauseGdcJob = " LEFT JOIN genericdatacontainer AS gdc_jb_tourname ON gdc_jb_tourname.gdc_obj_id = jb.jb_id AND gdc_jb_tourname.gdc_obj_type = 'jb' AND gdc_jb_tourname.gdc_gen_fieldname = 'tourname' ";
|
|
|
|
// GDC (tour)
|
|
$fromClauseGdcTour = array();
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
$fromClauseGdcTour[$j] = " LEFT JOIN genericdatacontainer AS gdc" . $j . "_arr_tour ON gdc" . $j . "_arr_tour.gdc_obj_id = tr" . $j . ".tr_id AND gdc" . $j . "_arr_tour.gdc_obj_type = 'tr' AND gdc" . $j . "_arr_tour.gdc_gen_fieldname = 'arr_tour' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc" . $j . "_tr_info_0 ON gdc" . $j . "_tr_info_0.gdc_obj_id = tr" . $j . ".tr_id AND gdc" . $j . "_tr_info_0.gdc_obj_type = 'tr' AND gdc" . $j . "_tr_info_0.gdc_gen_fieldname = 'info_0' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc" . $j . "_tr_info_1 ON gdc" . $j . "_tr_info_1.gdc_obj_id = tr" . $j . ".tr_id AND gdc" . $j . "_tr_info_1.gdc_obj_type = 'tr' AND gdc" . $j . "_tr_info_1.gdc_gen_fieldname = 'info_1' ";
|
|
endfor;
|
|
endif;
|
|
|
|
// $from_clause = "FROM costcenter AS csc, customer AS cs, company AS cmp, job AS jb LEFT JOIN tour AS tr ON jb.jb_id = tr.jb_id LEFT JOIN jobcalculator AS jbc ON tr.jb_id = jbc.jb_id AND tr.tr_sort = jbc.tr_sort ";
|
|
$from_clause = "FROM costcenter AS csc, customer AS cs, company AS cmp, headquarters AS hq, job AS jb " . $fromClauseGdcJob;
|
|
$from_clause_B .= $from_clause;
|
|
$from_clause_C .= $from_clause;
|
|
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
$from_clause_B .= " LEFT JOIN tour AS tr" . $j . " ON jb.jb_id = tr" . $j . ".jb_id AND tr" . $j . ".tr_sort = '" . $j . "' " . $fromClauseGdcTour[$j];
|
|
$from_clause_C .= " LEFT JOIN tour AS tr" . $j . " ON jb.jb_id = tr" . $j . ".jb_id AND tr" . $j . ".tr_sort = '" . $j . "' " . $fromClauseGdcTour[$j];
|
|
$from_clause .= ", tour AS tr" . $j . " " . $fromClauseGdcTour[$j] . ", jobcalculator AS jbc" . $j . " ";
|
|
endfor;
|
|
endif;
|
|
$from_clause_C .= ", jobcalculator AS jbc ";
|
|
|
|
$where_clause = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause .= "csc.cs_id = cs.cs_id AND cs.cs_id = '" . $customerId . "' AND ";
|
|
$where_clause .= "((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR " .
|
|
"(jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
$where_clause .= "cs.cmp_id = cmp.cmp_id AND ";
|
|
$where_clause .= "jb.hq_id = hq.hq_id AND ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause .= " jb.jb_finishtime >= '$fromDateRange' AND ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause .= " jb.jb_finishtime <= '$toDateRange' AND ";
|
|
endif;
|
|
|
|
$where_clause_B = $where_clause . "(NOT EXISTS (SELECT * FROM jobcalculator AS jbc WHERE jbc.jb_id = jb.jb_id)) ";
|
|
$where_clause_C = $where_clause . "jb.jb_id = jbc.jb_id AND jbc.tr_sort = '0' ";
|
|
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
$where_clause .= "jb.jb_id = tr" . $j . ".jb_id AND tr" . $j . ".jb_id = jbc" . $j . ".jb_id AND tr" . $j . ".tr_sort = jbc" . $j . ".tr_sort ";
|
|
if ($j < $jobListNumOfHops) : $where_clause .= " AND "; endif;
|
|
endfor;
|
|
endif;
|
|
|
|
$goupby_clause = "";
|
|
|
|
$orderby_clause = "ORDER BY jb_id";
|
|
|
|
|
|
if ($isMetaCs == "1") :
|
|
$where_clause2 = "WHERE jb.jb_status = '2' AND ";
|
|
$where_clause2 .= "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND ";
|
|
$where_clause2 .= "jb.csc_id_payer = csc.csc_id AND ";
|
|
$where_clause2 .= "csc.cs_id = cs.cs_id AND cs.cs_id_parent = '" . $customerId . "' AND ";
|
|
$where_clause2 .= "cs.cmp_id = cmp.cmp_id AND ";
|
|
|
|
if ($fromDateRange != "") :
|
|
$where_clause2 .= " jb.jb_finishtime >= '$fromDateRange' AND ";
|
|
endif;
|
|
if ($toDateRange != "") :
|
|
$where_clause2 .= " jb.jb_finishtime <= '$toDateRange' AND ";
|
|
endif;
|
|
|
|
$where_clause2_B = $where_clause2 . "(NOT EXISTS (SELECT * FROM jobcalculator AS jbc WHERE jbc.jb_id = jb.jb_id)) ";
|
|
$where_clause2_C = $where_clause2 . "jb.jb_id = jbc.jb_id AND jbc.tr_sort = '0' ";
|
|
|
|
if ($jobListNumOfHops != "") :
|
|
for ($j = 1; $j <= $jobListNumOfHops; $j++) :
|
|
$where_clause2 .= "jb.jb_id = tr" . $j . ".jb_id AND tr" . $j . ".jb_id = jbc" . $j . ".jb_id AND tr" . $j . ".tr_sort = jbc" . $j . ".tr_sort ";
|
|
if ($j < $jobListNumOfHops) : $where_clause2 .= " AND "; endif;
|
|
endfor;
|
|
endif;
|
|
|
|
$orderby_clause2 = "ORDER BY jb_id, tr_sort";
|
|
endif;
|
|
|
|
|
|
// Generate SQL statement
|
|
if ($isMetaCs == "0") :
|
|
// $sqlquery = $select_clause . $from_clause . $where_clause . $goupby_clause . $orderby_clause;
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C. $where_clause_C . $goupby_clause . ") " . $orderby_clause;
|
|
else :
|
|
// $sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION ("
|
|
// . $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") " . $orderby_clause2;
|
|
$sqlquery = "(" . $select_clause . $from_clause . $where_clause . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause . $from_clause . $where_clause2 . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_B . $from_clause_B . $where_clause2_B . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C . $where_clause_C . $goupby_clause . ") UNION "
|
|
. "(" . $select_clause_C . $from_clause_C . $where_clause2_C . $goupby_clause . ") ". $orderby_clause2;
|
|
endif;
|
|
// echo $sqlquery; die();
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$rowArray = convertRowToArray($result);
|
|
endif;
|
|
|
|
endif; // Only customers
|
|
|
|
endif;
|
|
?>
|