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