public static ReportCashflowForecastData[] selectAllLines(
ConnectionProvider connectionProvider,
String dateFormat,
String language,
String bankaccount,
String dateMax,
String order,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (B.CODEBANK || '/' || B.CODEBRANCH || B.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACCOUNT,"
+ " DATEPLANNED, "
+ " ISRECEIPT, "
+ " COALESCE(mt.msgtext,m.msgtext) as IsReceiptMessage, "
+ " DP.C_DEBT_PAYMENT_ID, "
+ " BP.NAME AS BPARTNER,"
+ " DP.DESCRIPTION, "
+ " I.DOCUMENTNO AS INVOICENO, "
+ " TO_CHAR(I.DATEINVOICED,TO_CHAR(?)) AS DATEINVOICED,"
+ " c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id) AS AMOUNT,"
+ " (CASE WHEN DP.C_Invoice_ID IS NOT NULL"
+ " THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesInvoice/Payment_Relation.html' WHEN 'N' THEN 'PurchaseInvoice/Payment_Relation.html' END) "
+ " ELSE"
+ " CASE WHEN DP.C_Order_ID IS NOT NULL"
+ " THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesOrder/Payment_Relation.html' WHEN 'N' THEN 'PurchaseOrder/Payment_Relation.html' END)"
+ " ELSE"
+ " CASE WHEN DP.C_Settlement_Generate_ID IS NOT NULL"
+ " THEN 'Settlement/CreatedPayments_Relation.html'"
+ " END "
+ " END"
+ " END) AS URL,"
+ " (SELECT SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS BALANCE "
+ " FROM C_BANKSTATEMENTLINE BL,"
+ " C_BANKSTATEMENT BS"
+ " WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
+ " AND BS.PROCESSED='Y' "
+ " AND BS.STATEMENTDATE <= NOW()"
+ " AND BS.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID ) as initialbalance"
+ " FROM C_DEBT_PAYMENT_V DP LEFT JOIN C_INVOICE I ON DP.C_INVOICE_ID = I.C_INVOICE_ID,"
+ " AD_MESSAGE M LEFT JOIN AD_MESSAGE_TRL MT ON m.ad_message_id = mt.ad_message_id "
+ " and mt.ad_language = ?,"
+ " C_BPARTNER BP,"
+ " C_BANKACCOUNT BA,"
+ " C_BANK B "
+ " WHERE BA.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID"
+ " AND C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND BA.C_BANK_ID = B.C_BANK_ID "
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND BP.C_BPARTNER_ID = DP.C_BPARTNER_ID"
+ " AND 2=2";
strSql =
strSql
+ ((bankaccount == null || bankaccount.equals(""))
? ""
: " AND DP.C_BANKACCOUNT_ID = ? ");
strSql =
strSql
+ " AND M.VALUE = (CASE DP.ISRECEIPT WHEN 'Y' THEN 'Income' WHEN 'N' THEN 'Payments' END)"
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " AND 1=1";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql = strSql + " ORDER BY ";
strSql = strSql + ((order == null || order.equals("")) ? "" : order);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, language);
if (bankaccount != null && !(bankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bankaccount);
}
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (order != null && !(order.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ReportCashflowForecastData objectReportCashflowForecastData =
new ReportCashflowForecastData();
objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
objectReportCashflowForecastData.dateplanned =
UtilSql.getDateValue(result, "dateplanned", "dd-MM-yyyy");
objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
objectReportCashflowForecastData.isreceiptmessage =
UtilSql.getValue(result, "isreceiptmessage");
objectReportCashflowForecastData.cDebtPaymentId =
UtilSql.getValue(result, "c_debt_payment_id");
objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
objectReportCashflowForecastData.initialbalance =
UtilSql.getValue(result, "initialbalance");
objectReportCashflowForecastData.rownum = Long.toString(countRecord);
objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportCashflowForecastData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ReportCashflowForecastData objectReportCashflowForecastData[] =
new ReportCashflowForecastData[vector.size()];
vector.copyInto(objectReportCashflowForecastData);
return (objectReportCashflowForecastData);
}
public static ReportCashflowForecastData[] select(
ConnectionProvider connectionProvider,
String language,
String dateMax,
String bankaccount,
String adUserClient,
String adUserOrg,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT AD_COLUMN_IDENTIFIER(TO_CHAR('C_BankAccount'), TO_CHAR(BANKACCOUNTID), TO_CHAR(?)) as BANKACCOUNT, "
+ " INITIALBALANCE,"
+ " TRUNC(NOW()) AS CURRENTDATE, "
+ " INCOME, "
+ " PAYMENT, "
+ " INCOME-PAYMENT AS INCPAY, "
+ " INITIALBALANCE+INCOME-PAYMENT AS "
+ " FINALSUMMARY, C_BANKACCOUNT_ID, "
+ " '' AS C_DEBT_PAYMENT_ID, "
+ " '' AS DATEPLANNED, "
+ " '' AS BPARTNER,"
+ " '' AS DESCRIPTION, "
+ " '' AS INVOICENO, "
+ " '' AS DATEINVOICED,"
+ " '' AS AMOUNT,"
+ " '' AS ISRECEIPT,"
+ " '' AS URL,"
+ " '' AS ISRECEIPTMESSAGE FROM ( "
+ " SELECT BA.C_BANKACCOUNT_ID AS BANKACCOUNTID,"
+ " SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS INITIALBALANCE,"
+ " COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
+ " FROM C_DEBT_PAYMENT_V DP"
+ " WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND ISRECEIPT = 'Y'"
+ " AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " AND 1=1";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql =
strSql
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " ),0) AS INCOME,"
+ " -COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
+ " FROM C_DEBT_PAYMENT_V DP"
+ " WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND ISRECEIPT='N'"
+ " AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " AND 2=2";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql =
strSql
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " ),0) AS PAYMENT,"
+ " BA.C_BANKACCOUNT_ID"
+ " FROM C_BANKSTATEMENTLINE BL,"
+ " C_BANKSTATEMENT BS,"
+ " C_BANKACCOUNT BA"
+ " WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
+ " AND BA.C_BANKACCOUNT_ID = BS.C_BANKACCOUNT_ID"
+ " AND BS.PROCESSED='Y' "
+ " AND BS.STATEMENTDATE <= NOW()"
+ " AND 3=3";
strSql =
strSql
+ ((bankaccount == null || bankaccount.equals(""))
? ""
: " AND BA.C_BANKACCOUNT_ID = ? ");
strSql = strSql + " AND BA.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ")" + " AND BA.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql =
strSql
+ ") "
+ " GROUP BY BA.C_BANKACCOUNT_ID, ba.c_Currency_ID, ba.ad_client_id, ba.ad_org_id "
+ " ) AAA "
+ " ORDER BY 1";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, language);
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (bankaccount != null && !(bankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bankaccount);
}
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ReportCashflowForecastData objectReportCashflowForecastData =
new ReportCashflowForecastData();
objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
objectReportCashflowForecastData.initialbalance =
UtilSql.getValue(result, "initialbalance");
objectReportCashflowForecastData.currentdate =
UtilSql.getDateValue(result, "currentdate", "dd-MM-yyyy");
objectReportCashflowForecastData.income = UtilSql.getValue(result, "income");
objectReportCashflowForecastData.payment = UtilSql.getValue(result, "payment");
objectReportCashflowForecastData.incpay = UtilSql.getValue(result, "incpay");
objectReportCashflowForecastData.finalsummary = UtilSql.getValue(result, "finalsummary");
objectReportCashflowForecastData.cBankaccountId =
UtilSql.getValue(result, "c_bankaccount_id");
objectReportCashflowForecastData.cDebtPaymentId =
UtilSql.getValue(result, "c_debt_payment_id");
objectReportCashflowForecastData.dateplanned = UtilSql.getValue(result, "dateplanned");
objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
objectReportCashflowForecastData.isreceiptmessage =
UtilSql.getValue(result, "isreceiptmessage");
objectReportCashflowForecastData.rownum = Long.toString(countRecord);
objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportCashflowForecastData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ReportCashflowForecastData objectReportCashflowForecastData[] =
new ReportCashflowForecastData[vector.size()];
vector.copyInto(objectReportCashflowForecastData);
return (objectReportCashflowForecastData);
}
public static ReportDebtPaymentData[] selectNoBpartner(
ConnectionProvider connectionProvider,
String cCurrencyConv,
String adLanguage,
String adUserClient,
String adUserOrg,
String cBpartnerId,
String dateFrom,
String dateTo,
String AmountFrom,
String AmountTo,
String paymentRule,
String isReceipt,
String status,
String isSettle,
String cbankaccount,
String ord,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACC,"
+ " (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE "
+ " CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE "
+ " CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END) AS ACCOUNTSTR,"
+ " (CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END) AS C_INVOICE_ID, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END) AS C_DEBT_PAYMENT_ID, B.NAME AS BPARTNER, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END) AS INVOICE, DP.DATEPLANNED,"
+ " DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,"
+ " (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END) AS DEBTCANCEL, (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END) AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE,"
+ " C_CURRENCY_CONVERT(DP.AMOUNT, DP.C_CURRENCY_ID, ?, TO_DATE(I.DATEINVOICED), NULL, I.AD_CLIENT_ID, I.AD_ORG_ID) AS CONVAMOUNT,"
+ " C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, "
+ " C_CURRENCY_ISOSYM(?) AS CONVISOSYM, "
+ " (CASE C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, "
+ " DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) WHEN 'I' THEN AD_MESSAGE_GET2('DPR_INVALID',?) WHEN 'C' THEN AD_MESSAGE_GET2('DPR_CONCILIATED',?) WHEN 'W' THEN AD_MESSAGE_GET2('DPR_CANCELLED',?) WHEN 'A' THEN AD_MESSAGE_GET2('DPR_SETTLE',?) WHEN "
+ " 'P' THEN AD_MESSAGE_GET2('DPR_PENDING',?) END) AS STATUS,"
+ " DP.DESCRIPTION, AD_MESSAGE_GET2(DP.ISPAID, ?) AS ISPAID, "
+ " (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END) AS INVOICE_TYPE, "
+ " (CASE s2.SETTLEMENTTYPE WHEN 'I' THEN '../ManualSettlement/CreatePayment_Relation.html' ELSE '../Settlement/CreatedPayments_Relation.html' END) AS PATH, COALESCE(RLT2.NAME, RL2.NAME) AS DPSTATUS, URS.NAME AS SALESREPNAME"
+ " FROM C_DEBT_PAYMENT DP left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID"
+ " left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID"
+ " left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID"
+ " left join C_BANKACCOUNT BA on DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " left join C_BANK BANK ON BA.C_BANK_ID = BANK.C_BANK_ID"
+ " left join C_INVOICE I on DP.C_INVOICE_ID = I.C_INVOICE_ID left join AD_USER URS on I.SALESREP_ID = URS.AD_USER_ID,"
+ " AD_REF_LIST RL left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID"
+ " AND RLT.AD_LANGUAGE = ?,"
+ " AD_REF_LIST RL2 left join AD_REF_LIST_TRL RLT2 on RL2.AD_REF_LIST_ID = RLT2.AD_REF_LIST_ID"
+ " AND RLT2.AD_LANGUAGE = ?,"
+ " C_CURRENCY C"
+ " WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID "
+ " AND DP.PAYMENTRULE = RL.VALUE"
+ " AND RL.AD_REFERENCE_ID = '195'"
+ " AND DP.STATUS = RL2.VALUE"
+ " AND RL2.AD_REFERENCE_ID = '800070'"
+ " AND DP.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND DP.AD_Org_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ") " + " AND DP.IsActive='Y'" + " AND DP.IsValid='Y'";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND dp.C_BPARTNER_ID IN " + cBpartnerId);
strSql =
strSql
+ ((dateFrom == null || dateFrom.equals(""))
? ""
: " AND dp.Dateplanned >= to_date(?) ");
strSql =
strSql
+ ((dateTo == null || dateTo.equals("")) ? "" : " AND dp.Dateplanned < to_date(?) ");
strSql =
strSql
+ ((AmountFrom == null || AmountFrom.equals(""))
? ""
: " AND dp.amount >= TO_NUMBER(?) ");
strSql =
strSql
+ ((AmountTo == null || AmountTo.equals(""))
? ""
: " AND dp.amount <= TO_NUMBER(?) ");
strSql =
strSql
+ ((paymentRule == null || paymentRule.equals("")) ? "" : " AND dp.paymentrule = ? ");
strSql = strSql + ((isReceipt == null || isReceipt.equals("")) ? "" : " AND dp.isreceipt=? ");
strSql = strSql + ((status == null || status.equals("")) ? "" : " AND dp.status=? ");
strSql =
strSql
+ ((isSettle == null || isSettle.equals(""))
? ""
: " AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) IN"
+ isSettle);
strSql =
strSql
+ ((cbankaccount == null || cbankaccount.equals(""))
? ""
: " AND DP.C_BANKACCOUNT_ID = ? ");
strSql =
strSql
+ " GROUP BY (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL), (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END),"
+ " CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END), B.NAME, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END), DP.DATEPLANNED, DP.AMOUNT,"
+ " DP.C_CURRENCY_ID, I.AD_CLIENT_ID, I.AD_ORG_ID, I.DATEINVOICED,"
+ " DP.WRITEOFFAMT, C.ISO_CODE, (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END), (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END), RLT.NAME, RL.NAME, RLT2.NAME, RL2.NAME, DP.C_Settlement_Cancel_ID,"
+ " DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID, DP.DESCRIPTION, (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END),s2.SETTLEMENTTYPE, DP.STATUS, URS.NAME"
+ " ORDER BY ";
strSql = strSql + ((ord == null || ord.equals("")) ? "" : ord);
strSql = strSql + ",STATUS, DP.DATEPLANNED";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (cBpartnerId != null && !(cBpartnerId.equals(""))) {}
if (dateFrom != null && !(dateFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFrom);
}
if (dateTo != null && !(dateTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTo);
}
if (AmountFrom != null && !(AmountFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountFrom);
}
if (AmountTo != null && !(AmountTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountTo);
}
if (paymentRule != null && !(paymentRule.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paymentRule);
}
if (isReceipt != null && !(isReceipt.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, isReceipt);
}
if (status != null && !(status.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, status);
}
if (isSettle != null && !(isSettle.equals(""))) {}
if (cbankaccount != null && !(cbankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cbankaccount);
}
if (ord != null && !(ord.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ReportDebtPaymentData objectReportDebtPaymentData = new ReportDebtPaymentData();
objectReportDebtPaymentData.bankacc = UtilSql.getValue(result, "BANKACC");
objectReportDebtPaymentData.accountstr = UtilSql.getValue(result, "ACCOUNTSTR");
objectReportDebtPaymentData.cInvoiceId = UtilSql.getValue(result, "C_INVOICE_ID");
objectReportDebtPaymentData.cDebtPaymentId = UtilSql.getValue(result, "C_DEBT_PAYMENT_ID");
objectReportDebtPaymentData.bpartner = UtilSql.getValue(result, "BPARTNER");
objectReportDebtPaymentData.invoice = UtilSql.getValue(result, "INVOICE");
objectReportDebtPaymentData.dateplanned =
UtilSql.getDateValue(result, "DATEPLANNED", "dd-MM-yyyy");
objectReportDebtPaymentData.amount = UtilSql.getValue(result, "AMOUNT");
objectReportDebtPaymentData.writeoffamt = UtilSql.getValue(result, "WRITEOFFAMT");
objectReportDebtPaymentData.currency = UtilSql.getValue(result, "CURRENCY");
objectReportDebtPaymentData.debtcancel = UtilSql.getValue(result, "DEBTCANCEL");
objectReportDebtPaymentData.debtgenerate = UtilSql.getValue(result, "DEBTGENERATE");
objectReportDebtPaymentData.paymentrule = UtilSql.getValue(result, "PAYMENTRULE");
objectReportDebtPaymentData.convamount = UtilSql.getValue(result, "CONVAMOUNT");
objectReportDebtPaymentData.convsym = UtilSql.getValue(result, "CONVSYM");
objectReportDebtPaymentData.convisosym = UtilSql.getValue(result, "CONVISOSYM");
objectReportDebtPaymentData.status = UtilSql.getValue(result, "STATUS");
objectReportDebtPaymentData.description = UtilSql.getValue(result, "DESCRIPTION");
objectReportDebtPaymentData.ispaid = UtilSql.getValue(result, "ISPAID");
objectReportDebtPaymentData.invoiceType = UtilSql.getValue(result, "INVOICE_TYPE");
objectReportDebtPaymentData.path = UtilSql.getValue(result, "PATH");
objectReportDebtPaymentData.dpstatus = UtilSql.getValue(result, "DPSTATUS");
objectReportDebtPaymentData.salesrepname = UtilSql.getValue(result, "SALESREPNAME");
objectReportDebtPaymentData.rownum = Long.toString(countRecord);
objectReportDebtPaymentData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportDebtPaymentData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ReportDebtPaymentData objectReportDebtPaymentData[] = new ReportDebtPaymentData[vector.size()];
vector.copyInto(objectReportDebtPaymentData);
return (objectReportDebtPaymentData);
}
public static DocInOutData[] selectRegistro(
ConnectionProvider connectionProvider,
String client,
String id,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT I.AD_CLIENT_ID, I.AD_ORG_ID, I.C_BPARTNER_ID, I.AD_ORGTRX_ID, I.C_PROJECT_ID,"
+ " I.C_CAMPAIGN_ID, I.C_ACTIVITY_ID, I.USER1_ID, I.USER2_ID, I.C_COSTCENTER_ID, I.DOCUMENTNO, I.DATEACCT,"
+ " I.C_DOCTYPE_ID, I.C_CHARGE_ID, I.CHARGEAMT, I.POSTED, I.MOVEMENTDATE, I.C_BPARTNER_LOCATION_ID"
+ " FROM M_INOUT I"
+ " WHERE AD_Client_ID=? "
+ " AND M_INOUT_ID=?";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, client);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, id);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
DocInOutData objectDocInOutData = new DocInOutData();
objectDocInOutData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectDocInOutData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectDocInOutData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectDocInOutData.adOrgtrxId = UtilSql.getValue(result, "AD_ORGTRX_ID");
objectDocInOutData.cProjectId = UtilSql.getValue(result, "C_PROJECT_ID");
objectDocInOutData.cCampaignId = UtilSql.getValue(result, "C_CAMPAIGN_ID");
objectDocInOutData.cActivityId = UtilSql.getValue(result, "C_ACTIVITY_ID");
objectDocInOutData.user1Id = UtilSql.getValue(result, "USER1_ID");
objectDocInOutData.user2Id = UtilSql.getValue(result, "USER2_ID");
objectDocInOutData.cCostcenterId = UtilSql.getValue(result, "C_COSTCENTER_ID");
objectDocInOutData.documentno = UtilSql.getValue(result, "DOCUMENTNO");
objectDocInOutData.dateacct = UtilSql.getDateValue(result, "DATEACCT", "dd-MM-yyyy");
objectDocInOutData.cDoctypeId = UtilSql.getValue(result, "C_DOCTYPE_ID");
objectDocInOutData.cChargeId = UtilSql.getValue(result, "C_CHARGE_ID");
objectDocInOutData.chargeamt = UtilSql.getValue(result, "CHARGEAMT");
objectDocInOutData.posted = UtilSql.getValue(result, "POSTED");
objectDocInOutData.movementdate =
UtilSql.getDateValue(result, "MOVEMENTDATE", "dd-MM-yyyy");
objectDocInOutData.cBpartnerLocationId = UtilSql.getValue(result, "C_BPARTNER_LOCATION_ID");
objectDocInOutData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectDocInOutData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
DocInOutData objectDocInOutData[] = new DocInOutData[vector.size()];
vector.copyInto(objectDocInOutData);
return (objectDocInOutData);
}
public static RptCBpartnerData[] select(
ConnectionProvider connectionProvider,
String adLanguage,
String cBpartnerId,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT BP.C_BPARTNER_ID, BP.VALUE, AD_MESSAGE_GET2(BP.ISACTIVE, ?) AS ISACTIVE, TO_DATE(BP.CREATED) AS CREATED, TO_DATE(BP.UPDATED) AS UPDATED, "
+ " BP.NAME, BP.NAME2, G.NAME AS GROUPNAME, BP.TAXID, '' AS ACCOUNTNO, AD_MESSAGE_GET2(BP.ISEMPLOYEE, ?) AS ISEMPLOYEE, AD_MESSAGE_GET2(BP.ISSALESREP, ?) AS ISSALESREP, "
+ " ISCUSTOMER, ISVENDOR, '' AS SEQNO, '' AS CASCADE, '' AS DISCOUNT, '' AS QTY, '' AS CLASS_TREE"
+ " FROM C_BPARTNER BP, C_BP_GROUP G"
+ " WHERE BP.C_BP_GROUP_ID = G.C_BP_GROUP_ID";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND BP.C_BPARTNER_ID IN " + cBpartnerId);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (cBpartnerId != null && !(cBpartnerId.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
RptCBpartnerData objectRptCBpartnerData = new RptCBpartnerData();
objectRptCBpartnerData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id");
objectRptCBpartnerData.value = UtilSql.getValue(result, "value");
objectRptCBpartnerData.isactive = UtilSql.getValue(result, "isactive");
objectRptCBpartnerData.created = UtilSql.getDateValue(result, "created", "dd-MM-yyyy");
objectRptCBpartnerData.updated = UtilSql.getDateValue(result, "updated", "dd-MM-yyyy");
objectRptCBpartnerData.name = UtilSql.getValue(result, "name");
objectRptCBpartnerData.name2 = UtilSql.getValue(result, "name2");
objectRptCBpartnerData.groupname = UtilSql.getValue(result, "groupname");
objectRptCBpartnerData.taxid = UtilSql.getValue(result, "taxid");
objectRptCBpartnerData.accountno = UtilSql.getValue(result, "accountno");
objectRptCBpartnerData.isemployee = UtilSql.getValue(result, "isemployee");
objectRptCBpartnerData.issalesrep = UtilSql.getValue(result, "issalesrep");
objectRptCBpartnerData.iscustomer = UtilSql.getValue(result, "iscustomer");
objectRptCBpartnerData.isvendor = UtilSql.getValue(result, "isvendor");
objectRptCBpartnerData.seqno = UtilSql.getValue(result, "seqno");
objectRptCBpartnerData.cascade = UtilSql.getValue(result, "cascade");
objectRptCBpartnerData.discount = UtilSql.getValue(result, "discount");
objectRptCBpartnerData.qty = UtilSql.getValue(result, "qty");
objectRptCBpartnerData.classTree = UtilSql.getValue(result, "class_tree");
objectRptCBpartnerData.position = Long.toString(countRecord);
objectRptCBpartnerData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectRptCBpartnerData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
RptCBpartnerData objectRptCBpartnerData[] = new RptCBpartnerData[vector.size()];
vector.copyInto(objectRptCBpartnerData);
return (objectRptCBpartnerData);
}
public static ReportProjectProgressData[] select(
ConnectionProvider connectionProvider,
String adLanguage,
String adUserClient,
String adUserOrg,
String dateStartFrom,
String dateStartTo,
String dateContractFrom,
String dateContractTo,
String dateEndingFrom,
String dateEndingTo,
String cProjectId,
String cProjectStatus,
String cBPartnerId,
String projectResponsible,
String olderFirst,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT"
+ " PR.C_PROJECT_ID AS PROJECTID, PR.NAME AS PROJECTNAME, PR.DESCRIPTION AS PROJECTDESCRIPTION, PR.NOTE AS PROJECTCOMMENTS,"
+ " REFLISTV.NAME AS PROJECTSTATUS, BPCUSTOMER.NAME AS CUSTOMERNAME, BPRESPONSIBLE.NAME AS PERSONINCHARGE,"
+ " PR.STARTDATE AS STARTINGDATE, PR.DATECONTRACT AS CONTRACTDATE, PR.DATEFINISH AS ENDINGDATE,"
+ " '' AS DAYSELAPSED, '' AS PROJECTCONTRACTDURATION, '' AS DAYSDELAYED,"
+ " '' AS TIMEBURNED, ''AS COMPLETIONPERC, '' AS CUMDAYSDELAYED,"
+ " PRP.C_PROJECTPHASE_ID AS PHASEID, PRP.NAME AS PHASENAME, PRP.STARTDATE AS PHASESTARTINGDATE, PRP.DATECONTRACT AS PHASECONTRACTDATE, '' AS PHASECONTRACTDURATION, "
+ " AD_MESSAGE_GET2(PRP.ISCOMPLETE,?) AS PHASECOMPLETE, PRP.ISCOMPLETE AS PHASECOMP, PRP.ENDDATE AS PHASEENDINGDATE, '' AS PHASEDAYSDELAYED,"
+ " PRT.C_PROJECTTASK_ID AS TASKID, PRT.NAME AS TASKNAME, PRT.STARTDATE AS TASKSTARTINGDATE, PRT.DATECONTRACT AS TASKCONTRACTDATE, '' AS TASKCONTRACTDURATION,"
+ " AD_MESSAGE_GET2(PRT.ISCOMPLETE,?) AS TASKCOMPLETE, PRT.ISCOMPLETE AS TASKCOMP, PRT.ENDDATE AS TASKENDINGDATE, '' AS TASKDAYSDELAYED"
+ " FROM AD_REF_LIST_V REFLISTV, C_BPARTNER BPCUSTOMER, C_PROJECT PR"
+ " LEFT JOIN C_BPARTNER BPRESPONSIBLE ON PR.RESPONSIBLE_ID = BPRESPONSIBLE.C_BPARTNER_ID"
+ " LEFT JOIN C_PROJECTPHASE PRP ON PR.C_PROJECT_ID = PRP.C_PROJECT_ID"
+ " LEFT JOIN C_PROJECTTASK PRT ON PRP.C_PROJECTPHASE_ID = PRT.C_PROJECTPHASE_ID"
+ " WHERE PR.C_BPARTNER_ID = BPCUSTOMER.C_BPARTNER_ID"
+ " AND REFLISTV.AD_REFERENCE_ID = '800002'"
+ " AND REFLISTV.AD_LANGUAGE = ?"
+ " AND REFLISTV.VALUE = PR.PROJECTSTATUS"
+ " AND PR.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ")" + " AND PR.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ")" + " AND 1=1";
strSql =
strSql
+ ((dateStartFrom == null || dateStartFrom.equals(""))
? ""
: " AND PR.STARTDATE >= TO_DATE(?) ");
strSql =
strSql
+ ((dateStartTo == null || dateStartTo.equals(""))
? ""
: " AND PR.STARTDATE < TO_DATE(?) ");
strSql =
strSql
+ ((dateContractFrom == null || dateContractFrom.equals(""))
? ""
: " AND PR.DATECONTRACT >= TO_DATE(?) ");
strSql =
strSql
+ ((dateContractTo == null || dateContractTo.equals(""))
? ""
: " AND PR.DATECONTRACT < TO_DATE(?) ");
strSql =
strSql
+ ((dateEndingFrom == null || dateEndingFrom.equals(""))
? ""
: " AND PR.DATEFINISH >= TO_DATE(?) ");
strSql =
strSql
+ ((dateEndingTo == null || dateEndingTo.equals(""))
? ""
: " AND PR.DATEFINISH < TO_DATE(?) ");
strSql =
strSql
+ ((cProjectId == null || cProjectId.equals("")) ? "" : " AND PR.C_PROJECT_ID = ? ");
strSql =
strSql
+ ((cProjectStatus == null || cProjectStatus.equals(""))
? ""
: " AND PR.PROJECTSTATUS IN" + cProjectStatus);
strSql =
strSql
+ ((cBPartnerId == null || cBPartnerId.equals(""))
? ""
: " AND PR.C_BPARTNER_ID = ? ");
strSql =
strSql
+ ((projectResponsible == null || projectResponsible.equals(""))
? ""
: " AND PR.RESPONSIBLE_ID = ? ");
strSql = strSql + " ORDER BY PR.NAME";
strSql = strSql + ((olderFirst == null || olderFirst.equals("")) ? "" : olderFirst);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (dateStartFrom != null && !(dateStartFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateStartFrom);
}
if (dateStartTo != null && !(dateStartTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateStartTo);
}
if (dateContractFrom != null && !(dateContractFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateContractFrom);
}
if (dateContractTo != null && !(dateContractTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateContractTo);
}
if (dateEndingFrom != null && !(dateEndingFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateEndingFrom);
}
if (dateEndingTo != null && !(dateEndingTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateEndingTo);
}
if (cProjectId != null && !(cProjectId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cProjectId);
}
if (cProjectStatus != null && !(cProjectStatus.equals(""))) {}
if (cBPartnerId != null && !(cBPartnerId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cBPartnerId);
}
if (projectResponsible != null && !(projectResponsible.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, projectResponsible);
}
if (olderFirst != null && !(olderFirst.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ReportProjectProgressData objectReportProjectProgressData = new ReportProjectProgressData();
objectReportProjectProgressData.projectid = UtilSql.getValue(result, "projectid");
objectReportProjectProgressData.projectname = UtilSql.getValue(result, "projectname");
objectReportProjectProgressData.projectdescription =
UtilSql.getValue(result, "projectdescription");
objectReportProjectProgressData.projectcomments =
UtilSql.getValue(result, "projectcomments");
objectReportProjectProgressData.projectstatus = UtilSql.getValue(result, "projectstatus");
objectReportProjectProgressData.customername = UtilSql.getValue(result, "customername");
objectReportProjectProgressData.personincharge = UtilSql.getValue(result, "personincharge");
objectReportProjectProgressData.startingdate =
UtilSql.getDateValue(result, "startingdate", "dd-MM-yyyy");
objectReportProjectProgressData.contractdate =
UtilSql.getDateValue(result, "contractdate", "dd-MM-yyyy");
objectReportProjectProgressData.endingdate =
UtilSql.getDateValue(result, "endingdate", "dd-MM-yyyy");
objectReportProjectProgressData.dayselapsed = UtilSql.getValue(result, "dayselapsed");
objectReportProjectProgressData.projectcontractduration =
UtilSql.getValue(result, "projectcontractduration");
objectReportProjectProgressData.daysdelayed = UtilSql.getValue(result, "daysdelayed");
objectReportProjectProgressData.timeburned = UtilSql.getValue(result, "timeburned");
objectReportProjectProgressData.completionperc = UtilSql.getValue(result, "completionperc");
objectReportProjectProgressData.cumdaysdelayed = UtilSql.getValue(result, "cumdaysdelayed");
objectReportProjectProgressData.phaseid = UtilSql.getValue(result, "phaseid");
objectReportProjectProgressData.phasename = UtilSql.getValue(result, "phasename");
objectReportProjectProgressData.phasestartingdate =
UtilSql.getDateValue(result, "phasestartingdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasecontractdate =
UtilSql.getDateValue(result, "phasecontractdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasecontractduration =
UtilSql.getValue(result, "phasecontractduration");
objectReportProjectProgressData.phasecomplete = UtilSql.getValue(result, "phasecomplete");
objectReportProjectProgressData.phasecomp = UtilSql.getValue(result, "phasecomp");
objectReportProjectProgressData.phaseendingdate =
UtilSql.getDateValue(result, "phaseendingdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasedaysdelayed =
UtilSql.getValue(result, "phasedaysdelayed");
objectReportProjectProgressData.taskid = UtilSql.getValue(result, "taskid");
objectReportProjectProgressData.taskname = UtilSql.getValue(result, "taskname");
objectReportProjectProgressData.taskstartingdate =
UtilSql.getDateValue(result, "taskstartingdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskcontractdate =
UtilSql.getDateValue(result, "taskcontractdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskcontractduration =
UtilSql.getValue(result, "taskcontractduration");
objectReportProjectProgressData.taskcomplete = UtilSql.getValue(result, "taskcomplete");
objectReportProjectProgressData.taskcomp = UtilSql.getValue(result, "taskcomp");
objectReportProjectProgressData.taskendingdate =
UtilSql.getDateValue(result, "taskendingdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskdaysdelayed =
UtilSql.getValue(result, "taskdaysdelayed");
objectReportProjectProgressData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportProjectProgressData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ReportProjectProgressData objectReportProjectProgressData[] =
new ReportProjectProgressData[vector.size()];
vector.copyInto(objectReportProjectProgressData);
return (objectReportProjectProgressData);
}
/** Select Organization */
public static OrganizationData[] select(
ConnectionProvider connectionProvider,
String paramLanguage,
String adUserClient,
String adOrgClient,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT "
+ " AD_OrgInfo.AD_Client_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Client_ID IS NULL THEN '' ELSE (TO_CHAR(client.Name) ) END) AS AD_Client_IDR, "
+ " AD_OrgInfo.AD_Org_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Org_ID IS NULL THEN '' ELSE (TO_CHAR(org.Name) ) END) AS AD_Org_IDR, "
+ " COALESCE(AD_OrgInfo.IsActive, 'N') AS IsActive, "
+ " AD_OrgInfo.C_Location_ID, "
+ " (CASE WHEN AD_OrgInfo.C_Location_ID IS NULL THEN '' ELSE (TO_CHAR(loc.Address1) || ' - ' || TO_CHAR(loc.Address2) || ' - ' || TO_CHAR(loc.Postal) || ' - ' || TO_CHAR(loc.City) || ' - ' || TO_CHAR(reg.Name) || ' - ' || TO_CHAR((CASE WHEN countryTrl.Name IS NULL THEN TO_CHAR(country.Name) ELSE TO_CHAR(countryTrl.Name) END)) ) END) AS C_Location_IDR, "
+ " AD_OrgInfo.DUNS, "
+ " AD_OrgInfo.TaxID, "
+ " AD_OrgInfo.C_BPartner_ID, "
+ " (CASE WHEN AD_OrgInfo.C_BPartner_ID IS NULL THEN '' ELSE (TO_CHAR(bpart.Name) ) END) AS C_BPartner_IDR, "
+ " AD_OrgInfo.Logo, "
+ " COALESCE(AD_OrgInfo.Reference_Order, 'N') AS Reference_Order, "
+ " '' AS TR_BGCOLOR , "
+ " AD_OrgInfo.Created, "
+ " AD_OrgInfo.CreatedBy, "
+ " AD_OrgInfo.Updated, "
+ " AD_OrgInfo.UpdatedBy, ? AS LANGUAGE "
+ " FROM "
+ " AD_OrgInfo "
+ " left join (select AD_Client_ID, Name from AD_Client) client on (AD_OrgInfo.AD_Client_ID = client.AD_Client_ID) "
+ " left join (select AD_Org_ID, Name from AD_Org) org on (AD_OrgInfo.AD_Org_ID = org.AD_Org_ID) "
+ " left join (select C_Location_ID, Address1, Address2, Postal, City, C_Region_ID, C_Country_ID from C_Location) loc on (AD_OrgInfo.C_Location_ID = loc.C_Location_ID) "
+ " left join (select C_Region_ID, Name from C_Region) reg on (loc.C_Region_ID = reg.C_Region_ID) "
+ " left join (select C_Country_ID, Name from C_Country) country on (loc.C_Country_ID = country.C_Country_ID) "
+ " left join (select C_Country_ID,AD_Language, Name from C_Country_TRL) countryTrl on (country.C_Country_ID = countryTrl.C_Country_ID and countryTrl.AD_Language = ?) "
+ " left join (select C_BPartner_ID, Name from C_BPartner) bpart on (AD_OrgInfo.C_BPartner_ID = bpart.C_BPartner_ID)"
+ " WHERE "
+ " AD_OrgInfo.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND AD_OrgInfo.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ")";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
OrganizationData objectOrganizationData = new OrganizationData();
objectOrganizationData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectOrganizationData.adClientIdr = UtilSql.getValue(result, "AD_CLIENT_IDR");
objectOrganizationData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectOrganizationData.adOrgIdr = UtilSql.getValue(result, "AD_ORG_IDR");
objectOrganizationData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectOrganizationData.cLocationId = UtilSql.getValue(result, "C_LOCATION_ID");
objectOrganizationData.cLocationIdr = UtilSql.getValue(result, "C_LOCATION_IDR");
objectOrganizationData.duns = UtilSql.getValue(result, "DUNS");
objectOrganizationData.taxid = UtilSql.getValue(result, "TAXID");
objectOrganizationData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectOrganizationData.cBpartnerIdr = UtilSql.getValue(result, "C_BPARTNER_IDR");
objectOrganizationData.logo = UtilSql.getValue(result, "LOGO");
objectOrganizationData.referenceOrder = UtilSql.getValue(result, "REFERENCE_ORDER");
objectOrganizationData.trBgcolor = UtilSql.getValue(result, "TR_BGCOLOR");
objectOrganizationData.created = UtilSql.getDateValue(result, "CREATED", "dd-MM-yyyy");
objectOrganizationData.createdby = UtilSql.getValue(result, "CREATEDBY");
objectOrganizationData.updated = UtilSql.getDateValue(result, "UPDATED", "dd-MM-yyyy");
objectOrganizationData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectOrganizationData.language = UtilSql.getValue(result, "LANGUAGE");
objectOrganizationData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectOrganizationData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
OrganizationData objectOrganizationData[] = new OrganizationData[vector.size()];
vector.copyInto(objectOrganizationData);
return (objectOrganizationData);
}
