public String getBlIdsOfClient(FclAccountProfitabilityForm profitForm) {
StringBuilder query = new StringBuilder();
query.append(" (SELECT GROUP_CONCAT(fn1.id) FROM fcl_booking bkg1 ");
query.append(" LEFT JOIN fcl_file_number fn1 ON fn1.id = bkg1.fcl_file_number_id ");
query.append(" LEFT JOIN fcl_bl bl ON bl.fcl_file_number_id = fn1.id ");
query.append(" LEFT JOIN trade_route tr ON tr.`id`= bl.`trade_route_id` ");
query.append(" LEFT JOIN CLIENT shipper ON bkg1.`shipper` = shipper.`id` ");
query.append(" LEFT JOIN CLIENT forwarder ON bkg1.`forwarder` = forwarder.`id` ");
query.append(" LEFT JOIN CLIENT consignee ON bkg1.`consignee` = consignee.`id` ");
query.append(" LEFT JOIN CLIENT shipTo ON bkg1.`shipto` = shipTo.`id` ");
query.append(" LEFT JOIN CLIENT notifyPrty ON bkg1.`notify_party` = notifyPrty.`id` ");
query.append(" LEFT JOIN CLIENT bkgPrty ON bkg1.`booking_party` = bkgPrty.`id`");
query.append(
" LEFT JOIN CLIENT ms ON ms.`id`=bl.`master_shipper_id` AND tr.`bill_to`='Shipper' ");
query.append(
" LEFT JOIN CLIENT mc ON mc.`id`=bl.`master_consignee_id` AND tr.`bill_to`='Consignee' ");
query.append(
" LEFT JOIN CLIENT mf ON mf.`id`=bl.`master_forwarder_id` AND tr.`bill_to`='Forwarder' ");
query.append(" LEFT JOIN CLIENT ma ON ma.`id`=bl.`master_agent_id` AND tr.`bill_to`='Agent' ");
query.append(
" LEFT JOIN CLIENT mn ON mn.`id`=bl.`master_notify_party_id` AND tr.`bill_to`='NotifyParty' ");
query.append(
" LEFT JOIN CLIENT mt ON mt.id=bl.`third_party_id` AND tr.`bill_to`='ThirdParty' ");
query.append(
" LEFT JOIN CLIENT hs ON hs.`id`=bl.`house_shipper_id` AND tr.`bill_to`='Shipper' ");
query.append(
" LEFT JOIN CLIENT hc ON hc.`id`=bl.`house_consignee_id` AND tr.`bill_to`='Consignee' ");
query.append(" LEFT JOIN CLIENT hf ON hf.`id`=bl.`forwarder_id` AND tr.`bill_to`='Forwarder' ");
query.append(" LEFT JOIN CLIENT ha ON ha.`id`=bl.`house_agent_id` AND tr.`bill_to`='Agent' ");
query.append(
" LEFT JOIN CLIENT hn ON hn.`id`=bl.`house_notify_party_id` AND tr.`bill_to`='NotifyParty' ");
query.append(
" LEFT JOIN CLIENT ht ON ht.`id`=bl.`house_third_party_id` AND tr.`bill_to`='ThirdParty' ");
query.append(" WHERE (ms.`tradingPartner_id`=tp.id OR mc.`tradingPartner_id`=tp.id ");
query.append(" OR mf.`tradingPartner_id`=tp.id OR ma.`tradingPartner_id`=tp.id ");
query.append(" OR mn.`tradingPartner_id`=tp.id OR mt.`tradingPartner_id`=tp.id ");
query.append(" OR hs.`tradingPartner_id`=tp.id OR hc.`tradingPartner_id`=tp.id ");
query.append(" OR hf.`tradingPartner_id`=tp.id OR ha.`tradingPartner_id`=tp.id ");
query.append(" OR hn.`tradingPartner_id`=tp.id OR ht.`tradingPartner_id`=tp.id ");
query.append(
" OR ( shipper.`client_check` = 'on' AND shipper.`tradingPartner_id` = tp.`id` ) ");
query.append(
" OR ( forwarder.`client_check` = 'on' AND forwarder.`tradingPartner_id` = tp.`id` )");
query.append(
" OR ( consignee.`client_check` = 'on' AND consignee.`tradingPartner_id` = tp.`id`)");
query.append(" OR ( shipTo.`client_check` = 'on' AND shipTo.`tradingPartner_id` = tp.`id` )");
query.append(
" OR ( notifyPrty.`client_check` = 'on' AND notifyprty.`tradingPartner_id` = tp.`id`)");
query.append(" OR ( bkgPrty.`client_check` = 'on' AND bkgPrty.`tradingPartner_id` = tp.`id`)");
query.append(" ) AND ( fn1.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00");
query.append("' AND fn1.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59");
query.append("' )) ");
return query.toString();
}
public ActionForward accountProfitability(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws IOException, Exception {
FclAccountProfitabilityForm accountProfitabilityForm = (FclAccountProfitabilityForm) form;
RedirectingActionForward actionForward = null;
if (CLEAR.equalsIgnoreCase(accountProfitabilityForm.getButton())) {
request.getSession().removeAttribute(ACC_PROFIT_LIST);
request.getSession().setAttribute(ACC_PROFIT_FORM, new FclAccountProfitabilityForm());
return mapping.findForward(ACC_PROFIT_HOME);
} else if (SEARCH.equalsIgnoreCase(accountProfitabilityForm.getButton())) {
Collection<FclAccountProfitabilityModel> collection =
getSearchResult(accountProfitabilityForm);
Date d1 = new Date();
request.getSession().setAttribute(ACC_PROFIT_LIST, collection);
double diff = (new Date().getTime() - d1.getTime() + 800d) / 1000;
request.setAttribute("seconds", diff);
return mapping.findForward(ACC_PROFIT_HOME);
} else if (DOWNLOAD_EXCEL.equalsIgnoreCase(accountProfitabilityForm.getButton())) {
return excelBlReport(mapping, accountProfitabilityForm, request, response);
} else if (PRINT_PREVIEW.equalsIgnoreCase(accountProfitabilityForm.getButton())) {
Collection<FclAccountProfitabilityModel> collection =
getSearchResult(accountProfitabilityForm);
request.getSession().setAttribute(ACC_PROFIT_FORM, accountProfitabilityForm);
request.getSession().setAttribute(ACC_PROFIT_LIST, collection);
String url =
URLUTF8Encoder.encode(
ServerUtil.getLocalPath(request)
+ "/report/fcl/Booking/bookingSummary/ACC_PROFIT_REPORT.jsp?print="
+ TRUE);
actionForward =
new RedirectingActionForward(
"/HTML2PDF?TIME="
+ new Date().getTime()
+ "&url="
+ url
+ "&html_width=1250&height=850&landscapeWidth=SUMMARY_REPORT");
return actionForward;
}
return null;
}
protected String getAccountProfitabilityTableQuery(FclAccountProfitabilityForm profitForm) {
StringBuilder query = new StringBuilder();
query.append(
" SELECT tp.`id`,tp.account_name,tp.`account_type`,(SELECT GROUP_CONCAT(bkg.`id`)AS bkg_count ");
query.append(
" FROM fcl_booking bkg LEFT JOIN fcl_file_number fn1 ON fn1.id = bkg.fcl_file_number_id LEFT JOIN CLIENT shipper ON bkg.`shipper` = shipper.`id` ");
query.append(
" LEFT JOIN CLIENT forwarder ON bkg.`forwarder` = forwarder.`id` LEFT JOIN CLIENT consignee ");
query.append(
" ON bkg.`consignee` = consignee.`id` LEFT JOIN CLIENT shipTo ON bkg.`shipto` = shipTo.`id` ");
query.append(
" LEFT JOIN CLIENT notifyPrty ON bkg.`notify_party` = notifyPrty.`id` LEFT JOIN CLIENT bkgPrty ");
query.append(
" ON bkg.`booking_party` = bkgPrty.`id` WHERE (shipper.`client_check` = 'on' AND shipper.`tradingPartner_id`=tp.`id` ");
query.append(" OR forwarder.`client_check` = 'on' AND forwarder.`tradingPartner_id`=tp.`id` ");
query.append(" OR consignee.`client_check` = 'on' AND consignee.`tradingPartner_id` =tp.`id` ");
query.append(" OR shipTo.`client_check` = 'on' AND shipTo.`tradingPartner_id`=tp.`id` ");
query.append(
" OR notifyPrty.`client_check` = 'on' AND notifyprty.`tradingPartner_id` =tp.`id` ");
query.append(
" OR bkgPrty.`client_check` = 'on' AND bkgPrty.`tradingPartner_id` =tp.`id`)AND ( fn1.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00'");
query.append(" AND fn1.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59'");
query.append(" )) AS bkg_count, ");
query.append(getBlIdsOfClient(profitForm)).append(" as actual_charge, ");
query.append(" (SELECT SUM(c.amount) FROM fcl_charge c");
query.append(
" LEFT JOIN fcl_booking bk2 ON bk2.id=c.fcl_booking_id LEFT JOIN fcl_file_number fn2 ON fn2.id=bk2.fcl_file_number_id ");
query.append(
" JOIN trading_partner tp1 ON tp1.`id` = c.`account_id` WHERE (tp1.account_number=tp.`account_number`)AND( fn2.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00'");
query.append(" AND fn2.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59'");
query.append(" ) ");
query.append(")AS actual_cost,");
query.append(
" (SELECT COUNT(bkg.`id`)AS bkg_count FROM fcl_booking bkg LEFT JOIN fcl_file_number fn3 ON fn3.id=bkg.fcl_file_number_id LEFT JOIN CLIENT shipper ON bkg.`shipper` = shipper.`id` ");
query.append(
" LEFT JOIN CLIENT forwarder ON bkg.`forwarder` = forwarder.`id` LEFT JOIN CLIENT consignee ON bkg.`consignee` = consignee.`id` ");
query.append(
" LEFT JOIN CLIENT shipTo ON bkg.`shipto` = shipTo.`id` LEFT JOIN CLIENT notifyPrty ON bkg.`notify_party` = notifyPrty.`id` ");
query.append(" LEFT JOIN CLIENT bkgPrty ON bkg.`booking_party` = bkgPrty.`id` ");
query.append(" WHERE (shipper.`client_check` = 'on' AND shipper.`tradingPartner_id`=tp.`id` ");
query.append(" OR forwarder.`client_check` = 'on' AND forwarder.`tradingPartner_id`=tp.`id` ");
query.append(" OR consignee.`client_check` = 'on' AND consignee.`tradingPartner_id` =tp.`id` ");
query.append(" OR shipTo.`client_check` = 'on' AND shipTo.`tradingPartner_id`=tp.`id` ");
query.append(
" OR notifyPrty.`client_check` = 'on' AND notifyprty.`tradingPartner_id` =tp.`id` ");
query.append(
" OR bkgPrty.`client_check` = 'on' AND bkgPrty.`tradingPartner_id` =tp.`id`) AND ( fn3.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00'");
query.append(" AND fn3.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59'");
query.append(" )) AS bkg_cnt, ");
query.append(
" (SELECT COUNT(quot.id) FROM CLIENT c JOIN trade_route tr ON tr.`client_id`=c.`id` ");
query.append(
" JOIN fcl_quotation quot ON quot.`trade_route_id`=tr.`id` LEFT JOIN fcl_file_number fn4 ON fn4.id=quot.fcl_file_number_id WHERE (c.`tradingPartner_id`=tp.id)AND( fn4.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00'");
query.append(" AND fn4.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59'");
query.append(")) AS quot_count, ");
query.append(" (SELECT GROUP_CONCAT(quot.id) FROM CLIENT c JOIN trade_route tr ");
query.append(
" ON tr.`client_id` = c.`id` JOIN fcl_quotation quot ON quot.`trade_route_id` = tr.id LEFT JOIN fcl_file_number fn5 ON fn5.id=quot.fcl_file_number_id WHERE (c.`tradingPartner_id` = tp.id) AND (fn5.`created_date` >= '");
query.append(profitForm.getStartDate1()).append(" 00:00:00'");
query.append(" AND fn5.`created_date` <= '");
query.append(profitForm.getEndDate1()).append(" 23:59:59'");
query.append("))AS quot_ids, ");
query.append(" NULL AS container");
// query.append(" FROM fcl_booking bk LEFT JOIN fcl_container fc ON
// fc.`fcl_booking_id`=bk.`id` LEFT JOIN generic_code gc ON gc.`id`=fc.`unit_id` ");
query.append(" ,(SELECT CAST(GROUP_CONCAT(fc.unit_id) AS CHAR(250))container ");
query.append(" FROM fcl_booking bk LEFT JOIN fcl_container fc ON fc.`fcl_booking_id`=bk.`id` ");
query.append(
" LEFT JOIN generic_code gc ON gc.`id`=fc.`unit_id` WHERE bk.`id`IN(bkg_count)) AS container_id, ");
query.append(
" (SELECT SUM(gc.`field1`*fc.`quantity`) FROM fcl_file_number fn LEFT JOIN fcl_booking bk ON fn.`id`=bk.`fcl_file_number_id` ");
query.append(
" LEFT JOIN fcl_container fc ON fc.`fcl_booking_id`=bk.`id` LEFT JOIN generic_code gc ON gc.`id`=fc.`unit_id` ");
query.append(" WHERE bk.`id`IN(bkg_count)) AS TUE ");
query.append(" FROM trading_partner AS tp");
if (CommonUtil.isNotEmpty(profitForm.getId())) {
query.append(" WHERE tp.`id`=").append(escapeSpecialChar(profitForm.getId()));
}
query.append(" ORDER BY tp.`id` LIMIT ");
if (CommonUtil.isNotEmpty(profitForm.getLimit())) {
query.append(profitForm.getLimit());
} else {
query.append(" 50 ");
}
return query.toString();
}