private String getQueries(String queryName){
HashMap queries = new HashMap();
StringBuffer insertQuery = new StringBuffer("insert into Cb_VOUCHER_INFO(KEYNO,ACCOUNTNO,FYEAR,TRUSTTYPE");
insertQuery.append(" ,VOUCHERTYPE,PARTYTYPE,EMP_PARTY_CODE,PREPAREDBY,details,preperation_dt,ecpfacno,");
insertQuery.append(" eregion,pfidFlag,EMPLOYEENAME,TRANSACTIONTYPE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?");
insertQuery.append(" ,?)");
queries.put("insertQuery",insertQuery);
StringBuffer insertDtQuery = new StringBuffer("insert into Cb_VOUCHER_DETAILS(KEYNO,ACCOUNTHEAD,MONTH_YEAR,");
insertDtQuery.append(" credit,Details,debit,chequeno) values (?,?,?,?,?,?,?)");
queries.put("insertDtQuery",insertDtQuery);
StringBuffer selectQuery = new StringBuffer("select VOUCHER_DT voucher_dt,to_char(preperation_dt,");
selectQuery.append(" 'dd/Mon/YYYY') preperation_dt,voucherNo,KEYNO,BANKNAME,FYEAR,Decode(VOUCHERTYPE");
selectQuery.append(" ,'P','Payment','R','Receipt','C','Contra','') VOUCHERTYPE,Decode(PARTYTYPE,'E',");
selectQuery.append(" 'Employee','P','Party','B','Bank','') PARTYTYPE,( select case when ");
selectQuery.append(" voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' else ");
selectQuery.append(" sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = ");
selectQuery.append(" voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,Cb_BANK_INFO bank where bank.ACCOUNTNO");
selectQuery.append(" = voucher.ACCOUNTNO and upper(BANKNAME) like upper(?) and FYEAR like ? and");
selectQuery.append(" VOUCHERTYPE like ? and upper(voucher.ACCOUNTNO) like upper(?) ");
queries.put("selectQuery",selectQuery);
StringBuffer selectAppQuery = new StringBuffer("select to_char(VOUCHER_DT,'dd/Mon/YYYY') voucher_dt,");
selectAppQuery.append(" to_char(preperation_dt,'dd/Mon/YYYY') preperation_dt,voucherNo,KEYNO,BANKNAME,");
selectAppQuery.append(" FYEAR,Decode(VOUCHERTYPE,'P','Payment','R','Receipt','C','Contra','') VOUCHERTYPE");
selectAppQuery.append(" ,Decode(PARTYTYPE,'E','Employee','P','Party','B','Bank','') PARTYTYPE ,( select ");
selectAppQuery.append(" case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' else ");
selectAppQuery.append(" sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno=");
selectAppQuery.append(" voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,Cb_BANK_INFO bank where ");
selectAppQuery.append(" bank.ACCOUNTNO = voucher.ACCOUNTNO and upper(BANKNAME) like upper(trim(?))and");
selectAppQuery.append(" FYEAR like ? and upper(VOUCHERTYPE) like upper(?) and voucherno is null and ");
selectAppQuery.append(" upper(voucher.ACCOUNTNO) like upper(?) and upper(voucher.keyno) like upper(?) ");
selectAppQuery.append(" order by voucher.ENTEREDDT ");
queries.put("selectAppQuery",selectAppQuery);
StringBuffer reportQuery = new StringBuffer(" SELECT Nvl(TRANSACTIONTYPE,'C') TRANSACTIONTYPE,");
reportQuery.append(" Nvl(voucher.voucherNo,' ') voucherNo,voucher.accountNo accountNo,keyno, bankname,");
reportQuery.append(" fyear,DECODE (vouchertype,'P', 'Payment','R', 'Receipt','C', 'Contra',' ') ");
reportQuery.append(" vouchertype,Nvl(partytype,' ') partytype,DECODE ( voucher.trusttype,'I', ");
reportQuery.append(" 'IAAI ECPF','N', 'NAA ECPF','A', 'AAI EPF',' ') trusttype,(CASE WHEN partytype =");
reportQuery.append(" 'E' and voucher.PFIDFLAG='true' THEN ecpfacno WHEN partytype = 'B' THEN (SELECT ");
reportQuery.append(" bankname FROM Cb_bank_info WHERE accountno = emp_party_code) else emp_party_code END )");
reportQuery.append(" emp_party_code,(CASE WHEN partytype = 'E' and voucher.PFIDFLAG='true' THEN ");
reportQuery.append(" employeename WHEN partytype = 'E' THEN (SELECT employeename FROM ");
reportQuery.append(" employee_personal_info WHERE pensionno = emp_party_code) ELSE emp_party_code ");
reportQuery.append(" END ) partyDetails, Nvl(preparedby,' ') preparedby, Nvl(voucherno,' ') voucherno,");
reportQuery.append(" Nvl(checkedby,' ') checkedby, Nvl(approvedby,' ') approvedby,Nvl(details,' ') details");
reportQuery.append(" ,Nvl(to_char(VOUCHER_DT,'dd/Mon/YYYY'),' ') VOUCHER_DT,APPROVAL,to_char(preperation_dt");
reportQuery.append(" ,'dd/Mon/YYYY') preperation_dt FROM Cb_VOUCHER_INFO voucher, Cb_bank_info bank WHERE ");
reportQuery.append(" bank.accountno = voucher.accountno AND keyno = ? ");
queries.put("reportQuery",reportQuery);
StringBuffer reportDtQuery = new StringBuffer("SELECT voucher.ACCOUNTHEAD ACCOUNTHEAD,acc.PARTICULAR");
reportDtQuery.append(" PARTICULAR,MONTH_YEAR,Nvl(details,' ') details,Nvl(credit,0.0) credit,Nvl(debit,0.0)");
reportDtQuery.append(" debit,Nvl(chequeno,'0') chequeno FROM Cb_voucher_details voucher, Cb_ACCOUNTCODE_INFO acc");
reportDtQuery.append(" WHERE voucher.ACCOUNTHEAD = acc.ACCOUNTHEAD AND keyno = ? ");
queries.put("reportDtQuery",reportDtQuery);
StringBuffer updateQuery = new StringBuffer("update Cb_VOUCHER_INFO set VOUCHER_DT=?,CHECKEDBY=?,APPROVAL=?");
updateQuery.append(" ,APPROVEDBY=?,voucherno=? where KEYNO=?");
queries.put("updateQuery",updateQuery);
StringBuffer bankOpenBalQuery = new StringBuffer("select (case when Nvl((select amountType from ");
bankOpenBalQuery.append(" bankopeningbal_info where accountno = ? and openeddate <= ?),'CR') = 'DR' then");
bankOpenBalQuery.append(" (NVl((select amount from bankopeningbal_info where accountno = ? and openeddate ");
bankOpenBalQuery.append(" <= ?), 0) + NVL(ncredit, 0) - Nvl(ndebit, 0)) else (NVl((select amount from ");
bankOpenBalQuery.append(" bankopeningbal_info where accountno = ? and openeddate <= ?), 0) - NVL(ncredit,");
bankOpenBalQuery.append(" 0) + Nvl(ndebit, 0)) end) OpeningBanace, (select bankname from bank_info where");
bankOpenBalQuery.append(" accountno = ?) bankname, decode((select amountType from bankopeningbal_info");
bankOpenBalQuery.append(" where accountno = ? and openeddate <= ?), 'DR', 'Dr.', 'Cr.') amountType from");
bankOpenBalQuery.append(" (select sum(decode(?||'C',info.accountno||info.vouchertype,nvl(credit, 0), ");
bankOpenBalQuery.append(" nvl(debit, 0))) nDebit,sum(decode(?||'C',info.accountno||info.vouchertype,");
bankOpenBalQuery.append(" nvl(debit, 0), nvl(credit, 0))) nCredit from Cb_voucher_details dt,(select keyno,");
bankOpenBalQuery.append(" info.accountno, vouchertype, voucher_dt,emp_party_code from Cb_VOUCHER_INFO");
bankOpenBalQuery.append(" info,bankopeningbal_info openbal where (info.accountno = ? or ");
bankOpenBalQuery.append(" (info.emp_party_code = ? and info.vouchertype = 'C')) and voucher_dt is not");
bankOpenBalQuery.append(" null and info.voucher_dt < ? and info.accountno = openbal.accountno(+) and ");
bankOpenBalQuery.append("info.voucher_dt >= openbal.openeddate) info where dt.keyno = info.keyno)");
queries.put("bankOpenBalQuery",bankOpenBalQuery);
StringBuffer bankReconcileOpenBalQuery = new StringBuffer("select (case when Nvl((select amountType from ");
bankReconcileOpenBalQuery.append(" cb_bankopeningbal_info where accountno = ? and openeddate <= ?),'CR') = 'DR' then");
bankReconcileOpenBalQuery.append(" (NVl((select amount from cb_bankopeningbal_info where accountno = ? and openeddate ");
bankReconcileOpenBalQuery.append(" <= ?), 0) + NVL(ncredit, 0) - Nvl(ndebit, 0)) else (NVl((select amount from ");
bankReconcileOpenBalQuery.append(" cb_bankopeningbal_info where accountno = ? and openeddate <= ?), 0) - NVL(ncredit,");
bankReconcileOpenBalQuery.append(" 0) + Nvl(ndebit, 0)) end) OpeningBanace, (select bankname from CB_BANK_INFO where");
bankReconcileOpenBalQuery.append(" accountno = ?) bankname, decode((select amountType from cb_bankopeningbal_info");
bankReconcileOpenBalQuery.append(" where accountno = ? and openeddate <= ?), 'DR', 'Dr.', 'Cr.') amountType from");
bankReconcileOpenBalQuery.append(" (select sum(decode(?||'C',info.accountno||info.vouchertype,nvl(credit, 0), ");
bankReconcileOpenBalQuery.append(" nvl(debit, 0))) nDebit,sum(decode(?||'C',info.accountno||info.vouchertype,");
bankReconcileOpenBalQuery.append(" nvl(debit, 0), nvl(credit, 0))) nCredit from Cb_voucher_details dt,(select keyno,");
bankReconcileOpenBalQuery.append(" info.accountno, vouchertype, voucher_dt,emp_party_code from Cb_VOUCHER_INFO");
bankReconcileOpenBalQuery.append(" info,cb_bankopeningbal_info openbal where (info.accountno = ? or ");
bankReconcileOpenBalQuery.append(" (info.emp_party_code = ? and info.vouchertype = 'C')) and voucher_dt is not");
bankReconcileOpenBalQuery.append(" null and info.voucher_dt < ? and info.accountno = openbal.accountno(+) and ");
bankReconcileOpenBalQuery.append("info.voucher_dt >= openbal.openeddate) info where dt.keyno = info.keyno)");
queries.put("bankReconcileOpenBalQuery",bankReconcileOpenBalQuery);
StringBuffer bankBookQuery = new StringBuffer("SELECT info.keyno, dt.accounthead, InitCap(acc.particular)");
bankBookQuery.append(" particular, decode(?,emp_party_code,debit,credit) credit,decode(?,emp_party_code,");
bankBookQuery.append(" credit,debit) debit, InitCap(Nvl(info.DETAILS,' ')) details, vouchertype,");
bankBookQuery.append(" InitCap(partyname) partyname,info.voucherno,to_char(info.voucher_dt,'dd/Mon/YYYY')");
bankBookQuery.append(" voucher_dt,Nvl(chequeno,'0') chequeno FROM Cb_voucher_details dt,(SELECT DETAILS,");
bankBookQuery.append(" keyno, vouchertype,(CASE WHEN partytype = 'E' THEN (SELECT employeename FROM ");
bankBookQuery.append(" employee_personal_info WHERE pensionno = emp_party_code) WHEN vouchertype = 'C'");
bankBookQuery.append(" AND emp_party_code != ? THEN (SELECT bankname FROM bank_info WHERE accountno =");
bankBookQuery.append(" emp_party_code) WHEN partytype = 'B' THEN (SELECT bankname FROM bank_info WHERE ");
bankBookQuery.append(" accountno = info.accountno) ELSE emp_party_code END ) partyname, ? accountno,");
bankBookQuery.append(" voucherno,voucher_dt,emp_party_code,partytype FROM Cb_VOUCHER_INFO info WHERE ");
bankBookQuery.append(" APPROVAL='Y' and (accountno = ? OR (vouchertype = 'C' AND emp_party_code = ?))");
bankBookQuery.append(" and voucherno is not null and voucher_dt between ? and ?) info,accountcode_info");
bankBookQuery.append(" acc WHERE info.keyno = dt.keyno and acc.ACCOUNTHEAD = dt.accounthead order by ");
bankBookQuery.append(" info.voucher_dt,info.voucherno");
queries.put("bankBookQuery",bankBookQuery);
StringBuffer editQuery = new StringBuffer("select *,( select case when voucher.vouchertype='P' then ");
editQuery.append(" sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from ");
editQuery.append(" Cb_voucher_details dt where dt.keyno=voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,");
editQuery.append(" BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and KEYNO=?");
queries.put("editQuery",editQuery);
StringBuffer updateVoucherQry = new StringBuffer("update Cb_VOUCHER_INFO set ACCOUNTNO=?,FYEAR=?,TRUSTTYPE=?");
updateVoucherQry.append(" ,PARTYTYPE=?,EMP_PARTY_CODE=?,PREPAREDBY=?,details=?,TRANSACTIONTYPE=? where KEYNO=?");
queries.put("updateVoucherQry",updateVoucherQry);
StringBuffer deleteDtQuery = new StringBuffer(" delete from Cb_voucher_details where KEYNO=?");
queries.put("deleteDtQuery",deleteDtQuery);
StringBuffer deleteVQuery = new StringBuffer("delete from Cb_VOUCHER_INFO where INSTR(upper(?),upper(KEYNO)) > 0");
queries.put("deleteVQuery",deleteVQuery);
StringBuffer deleteVDTQuery = new StringBuffer(" delete from Cb_voucher_details where INSTR(upper(?),upper(KEYNO)) > 0");
queries.put("deleteVDTQuery",deleteVDTQuery);
StringBuffer voucherNoQuery = new StringBuffer("SELECT voucher.vouchertype|| 'V/'|| vinfo.fyear||");
voucherNoQuery.append(" '/'|| SUBSTR (vinfo.accountno, LENGTH (vinfo.accountno) - 4, 5)|| '/'|| LPAD");
voucherNoQuery.append(" ( (SELECT CASE WHEN (? >= TO_DATE ('01/nov/2009', 'dd-mm-yy') AND hseries<500 )");
voucherNoQuery.append(" THEN 500 WHEN ? >= TO_DATE ('01/nov/2009', 'dd-mm-yy') THEN to_number(hseries)");
voucherNoQuery.append(" WHEN lseries < 500 THEN lseries END series from ( SELECT (SELECT MAX");
voucherNoQuery.append(" (TO_NUMBER (NVL (SUBSTR (voucherno,INSTR (voucherno, '/', -1) + 1 ),0))) no1");
voucherNoQuery.append(" FROM Cb_VOUCHER_INFO info ,(SELECT vouchertype FROM Cb_VOUCHER_INFO info where keyno");
voucherNoQuery.append(" = ?) vinfo WHERE voucherno IS NOT NULL and vinfo.vouchertype = info.vouchertype");
voucherNoQuery.append(" AND NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1), 0) < 500) lseries,");
voucherNoQuery.append(" (SELECT Nvl(MAX (NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1),0)),0)");
voucherNoQuery.append(" no1 FROM Cb_VOUCHER_INFO info ,(SELECT vouchertype FROM Cb_VOUCHER_INFO info where ");
voucherNoQuery.append(" keyno = ?) vinfo WHERE voucherno IS NOT NULL and vinfo.vouchertype = ");
voucherNoQuery.append(" info.vouchertype AND NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1), 0)");
voucherNoQuery.append(" >= 500) hseries FROM DUAL)) + 1, 4, 0) FROM (SELECT SUBSTR (fyear, 3) fyear,");
voucherNoQuery.append(" vouchertype, bankcode,info.accountno accountno FROM Cb_VOUCHER_INFO info, Cb_bank_info");
voucherNoQuery.append(" bank WHERE info.accountno = bank.accountno AND keyno = ?) vinfo, Cb_VOUCHER_INFO");
voucherNoQuery.append(" voucher WHERE voucher.vouchertype = vinfo.vouchertype AND UPPER (NVL ");
voucherNoQuery.append(" (voucher.voucherno, ' ')) != 'REJECTED' GROUP BY voucher.vouchertype, bankcode,");
voucherNoQuery.append(" vinfo.fyear, vinfo.accountno");
queries.put("voucherNoQuery",voucherNoQuery);
StringBuffer keyNoGenQuery = new StringBuffer("select to_char(to_date(?,'dd/mon/yyyy'),'ddmmyy')||");
keyNoGenQuery.append(" Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where ");
keyNoGenQuery.append(" to_char(to_date(?,'dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)");
queries.put("keyNoGenQuery",keyNoGenQuery);
StringBuffer ledgerQuery = new StringBuffer("SELECT TO_CHAR (voucher_dt, 'dd/Mon/YYYY') vdate, ");
ledgerQuery.append(" info.accountno accno,bank.bankcode || ' ' || bank.bankname bank, info.details ");
ledgerQuery.append(" particulars,decode(vouchertype,'P','Payment','R','Receipt','Contra') vtype, ");
ledgerQuery.append(" voucherno NO, case when vouchertype='P' then SUM (NVL (dt.debit, 0))-SUM (NVL");
ledgerQuery.append(" (dt.credit, 0)) else 0 end dt,case when vouchertype='R' then SUM (NVL (dt.credit,");
ledgerQuery.append(" 0))-SUM (NVL (dt.debit, 0)) when vouchertype='C' then SUM (NVL (dt.credit, 0)) else");
ledgerQuery.append(" 0 end cr FROM Cb_VOUCHER_INFO info, Cb_voucher_details dt, Cb_bank_info bank WHERE voucherno");
ledgerQuery.append(" IS NOT NULL AND voucherno != 'REJECTED' AND bank.accountno = info.accountno AND");
ledgerQuery.append(" info.keyno = dt.keyno and voucher_dt between decode(?,'','01-JAN-1000',?) and ");
ledgerQuery.append(" decode(?,'','31-JAN-9999',?) and voucherType like ? AND UPPER (bank.bankcode)");
ledgerQuery.append(" LIKE UPPER (?) AND UPPER (bank.trusttype) LIKE UPPER (?) GROUP BY voucher_dt,");
ledgerQuery.append(" info.accountno,bank.bankcode,bank.bankname,info.details,vouchertype,voucherno ");
ledgerQuery.append(" ORDER BY voucher_dt, voucherno");
queries.put("ledgerQuery",ledgerQuery);
StringBuffer accNosQuery = new StringBuffer("SELECT accountno from Cb_bank_info where accountcode = ? ");
queries.put("accNosQuery",accNosQuery);
StringBuffer genLedgerQuery = new StringBuffer("Select decode(vinfo.TRUSTTYPE,'N','NAA ECPF','I',");
genLedgerQuery.append(" 'IAAI ECPF','A','AAI EPF') trusttype,vdt.accounthead || ' ' || ainfo.particular");
genLedgerQuery.append(" Accountcode,to_char(vinfo.voucher_dt, 'dd/Mon/YYYY') vdate,(binfo.accountcode ");
genLedgerQuery.append(" || ' ' || binfo.bankname) bank, trim(vinfo.details) details, decode(VOUCHERTYPE,");
genLedgerQuery.append(" 'R', 'Receipt', 'P', 'Payment', 'C', 'Contra') vtype, vinfo.voucherno,");
genLedgerQuery.append(" decode(vinfo.vouchertype, 'P', sum(vdt.debit) - sum(vdt.credit)) debit,");
genLedgerQuery.append(" decode(vinfo.vouchertype, 'R', sum(vdt.credit) - sum(vdt.debit)) credit,");
genLedgerQuery.append(" (select sum(credit) - sum(debit) from ((select info.accounthead ACCOUNTHEAD,");
genLedgerQuery.append(" TRUSTTYPE trusttype, Nvl(decode(amounttype, 'CR', amount, 'DR', 0), 0) credit, ");
genLedgerQuery.append(" Nvl(decode(amounttype, 'CR', 0, 'DR', amount), 0) debit from Cb_accountcode_info ");
genLedgerQuery.append(" info, Cb_accountcode_details dt where upper(trim(TRUSTTYPE)) like upper(trim(?))");
genLedgerQuery.append(" and info.accounthead = dt.accounthead and info.opendate <= decode(?, '',");
genLedgerQuery.append(" '01/Jan/1000', ?)) union all (select dt.accounthead ACCOUNTHEAD, info.trusttype");
genLedgerQuery.append(" trusttype, sum(credit) credit, sum(debit) debit from Cb_VOUCHER_INFO info,");
genLedgerQuery.append(" Cb_voucher_details dt, accountcode_info ainfo where dt.keyno = info.keyno and");
genLedgerQuery.append(" ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype)) like ");
genLedgerQuery.append(" upper(trim(?)) and info.voucher_dt < decode(?, '', '01/Jan/1000', ?) and ");
genLedgerQuery.append(" info.voucher_dt >= decode(ainfo.opendate,'','01/jan/1000', ainfo.opendate) ");
genLedgerQuery.append(" group by dt.accounthead, info.trusttype, ainfo.particular)) where ACCOUNTHEAD ");
genLedgerQuery.append(" = vdt.accounthead and trusttype = vinfo.TRUSTTYPE ) openingBal,(CASE WHEN ");
genLedgerQuery.append(" partytype = 'E' and vinfo.PFIDFLAG='true' THEN EMPLOYEENAME WHEN partytype = ");
genLedgerQuery.append(" 'E' THEN (SELECT EMPLOYEENAME FROM employee_personal_info WHERE PENSIONNO = ");
genLedgerQuery.append(" emp_party_code) WHEN partytype = 'B' THEN (SELECT bankname FROM Cb_bank_info ");
genLedgerQuery.append(" WHERE accountno = emp_party_code) else emp_party_code END ) partyname,(select");
genLedgerQuery.append(" nvl(nvl(chequeno,'--') from Cb_voucher_details where keyno=''||vinfo.keyno||'' and rownum<2),'--') chequeNo from ");
genLedgerQuery.append(" Cb_VOUCHER_INFO vinfo, Cb_voucher_details vdt, Cb_bank_info binfo, Cb_accountcode_info ");
genLedgerQuery.append(" ainfo where ainfo.accounthead = vdt.accounthead and binfo.accountno = ");
genLedgerQuery.append(" vinfo.ACCOUNTNO and vdt.keyno = vinfo.keyno and vinfo.keyno in (select keyno");
genLedgerQuery.append(" from Cb_VOUCHER_INFO info where info.voucherno is not null) and upper(trim(");
genLedgerQuery.append(" vinfo.trustType)) like upper(trim(?)) and upper(trim(vdt.accounthead)) like");
genLedgerQuery.append(" upper(trim(?)) and upper(trim(vinfo.vouchertype)) like upper(trim(?)) and ");
genLedgerQuery.append(" vinfo.voucher_dt between decode(?, '', '01/Jan/1000', ?) and decode(?, '', ");
genLedgerQuery.append(" '01/Jan/3000', ?) group by vinfo.voucherno, vinfo.voucher_dt, binfo.accountcode,");
genLedgerQuery.append(" binfo.bankname, vinfo.details, vinfo.TRUSTTYPE, VOUCHERTYPE, vdt.accounthead,");
genLedgerQuery.append(" ainfo.particular,partytype,vinfo.PFIDFLAG,EMPLOYEENAME,emp_party_code,vinfo.keyno");
genLedgerQuery.append(" order by vdt.accounthead, vinfo.voucher_dt");
queries.put("genLedgerQuery",genLedgerQuery);
StringBuffer trailBalQuery = new StringBuffer("select decode(trusttype, 'N', 'NAA ECPF', 'I', 'IAAI ECPF'");
trailBalQuery.append(" , 'A', 'AAI EPF') trusttype,Decode(type,'','--','A','Asset','L','Liability','I',");
trailBalQuery.append(" 'Income','E','Expenditure','T','Inter Trust','U','Inter Unit','C','Current Asset'");
trailBalQuery.append(" ,'B','Current Liability','R','Inter Unit Remittance A/C') type, upper(acc) Accountcode,");
trailBalQuery.append(" sum(dr) openBaldebit, sum(cr) openBalcredit, sum(debit) debit, sum(credit) credit");
trailBalQuery.append(" from ((select ainfo.type type, dt.accounthead || ' ' || ainfo.particular acc,");
trailBalQuery.append(" info.trusttype trusttype, 0 cr, 0 dr, sum(credit) credit, sum(debit) debit from");
trailBalQuery.append(" Cb_VOUCHER_INFO info, Cb_voucher_details dt, Cb_accountcode_info ainfo where dt.keyno =");
trailBalQuery.append(" info.keyno and ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype))");
trailBalQuery.append(" like upper(trim(?)) and info.voucher_dt between decode(?, '', '01/jan/1000',?)");
trailBalQuery.append(" and decode(?, '', '01/jan/3000', ?) group by dt.accounthead, info.trusttype,");
trailBalQuery.append(" ainfo.particular, ainfo.type) union all (select type, ACCOUNTHEAD acc,");
trailBalQuery.append(" trusttype trusttype, sum(credit),sum(debit), 0 credit, 0 debit from ((select");
trailBalQuery.append(" info.type type, info.accounthead || ' ' || info.particular ACCOUNTHEAD,");
trailBalQuery.append(" TRUSTTYPE trusttype, Nvl(decode(amounttype, 'CR', amount, 'DR', 0), 0) credit,");
trailBalQuery.append(" Nvl(decode(amounttype, 'CR', 0, 'DR', amount), 0) debit from Cb_accountcode_info");
trailBalQuery.append(" info, Cb_accountcode_details dt where upper(trim(TRUSTTYPE)) like upper(trim(?)) and");
trailBalQuery.append(" info.accounthead = dt.accounthead and info.opendate <= decode(?, '', '01/Jan/1000', ?))");
trailBalQuery.append(" union all (select ainfo.type type, dt.accounthead || ' ' || ainfo.particular ");
trailBalQuery.append(" ACCOUNTHEAD, info.trusttype trusttype, sum(credit) credit, sum(debit) debit ");
trailBalQuery.append(" from Cb_VOUCHER_INFO info,Cb_voucher_details dt,Cb_accountcode_info ainfo where dt.keyno");
trailBalQuery.append(" = info.keyno and ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype))");
trailBalQuery.append(" like upper(trim(?)) and info.voucher_dt < decode(?, '', '01/Jan/1000',?) and ");
trailBalQuery.append(" info.voucher_dt >= decode(ainfo.opendate,'','01/jan/1000',ainfo.opendate) group");
trailBalQuery.append(" by dt.accounthead, info.trusttype,ainfo.particular, ainfo.type)) group by type,");
trailBalQuery.append(" ACCOUNTHEAD,trusttype )) group by trusttype, type, acc order by trusttype, type,");
trailBalQuery.append(" acc");
queries.put("trailBalQuery",trailBalQuery);
StringBuffer ePaymentsSearchQuery = new StringBuffer("select keyno,bankname,FYEAR,decode(vinfo.TRUSTTYPE,");
ePaymentsSearchQuery.append(" 'N','NAA ECPF','I','IAAI ECPF','A','AAI EPF') TRUSTTYPE,decode(PARTYTYPE,'P','Party'");
ePaymentsSearchQuery.append(" ,'E','Employee','B','Bank') PARTYTYPE,DETAILS,VOUCHERTYPE,VOUCHERNO,");
ePaymentsSearchQuery.append(" to_char(voucher_dt,'dd/Mon/YYYY') voucher_dt from Cb_VOUCHER_INFO vinfo,bank_info");
ePaymentsSearchQuery.append(" binfo where binfo.accountno = vinfo.accountno and VOUCHER_DT between decode(?,''");
ePaymentsSearchQuery.append(" ,'01/Jan/1000',?) and decode(?,'','01/Jan/3000',?) and VOUCHERTYPE = 'P' and APPROVAL='Y'");
queries.put("ePaymentsSearchQuery",ePaymentsSearchQuery);
StringBuffer ePaymentsQuery = new StringBuffer(" select * from Cb_VOUCHER_INFO where INSTR(upper(?),");
ePaymentsQuery.append(" upper(KEYNO)) > 0");
queries.put("ePaymentsQuery",ePaymentsQuery);
StringBuffer accountOpenBalQuery = new StringBuffer("select ainfo.ACCOUNTHEAD|| ' ' || ainfo.particular ACCOUNTHEAD ,");
accountOpenBalQuery.append(" decode(TRUSTTYPE,'N','NAA ECPF','I','IAAI ECPF','A','AAI EPF','AIN') TRUSTTYPE,");
accountOpenBalQuery.append(" Nvl((case when OPENDATE <= ? then decode(AMOUNTTYPE, 'CR', AMOUNT, -AMOUNT) ");
accountOpenBalQuery.append(" else 0 end) + Nvl((select sum(det.credit) - sum(det.debit) from Cb_voucher_details ");
accountOpenBalQuery.append(" det,Cb_VOUCHER_INFO info where info.keyno =det.keyno and info.voucher_dt > ? ");
accountOpenBalQuery.append(" and ainfo.accounthead = det.accounthead and details.trusttype = ");
accountOpenBalQuery.append(" info.trusttype),0),0) openbal,particular from accountcode_details details, ");
accountOpenBalQuery.append(" accountcode_info ainfo where ainfo.accounthead = details.accounthead(+) and ");
accountOpenBalQuery.append(" (trusttype ='' or trusttype like ?) and ainfo.ACCOUNTHEAD like ? order by ainfo.accounthead, trusttype ");
queries.put("accountOpenBalQuery",accountOpenBalQuery);
StringBuffer voucherQuery = new StringBuffer("select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
voucherQuery.append(" ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
voucherQuery.append(" VOUCHERNO,decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit)) credit, ");
voucherQuery.append(" decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit)) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
voucherQuery.append(" and info.keyno = details.keyno and RECONCILE_STATUS='N' and ACCOUNTNO=? group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO");
queries.put("voucherQuery",voucherQuery);
StringBuffer unReconcilevoucherQuery = new StringBuffer("select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
unReconcilevoucherQuery.append(" decode(?,ACCOUNTNO,ACCOUNTNO,EMP_PARTY_CODE)ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
unReconcilevoucherQuery.append(" VOUCHERNO,(case when (vouchertype='C') then (decode(?, emp_party_code, sum(debit), sum(credit))) else(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))) end) credit, ");
unReconcilevoucherQuery.append(" (case when (vouchertype='C') then decode(?, emp_party_code, sum(credit), sum(debit)) else (decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) end) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
unReconcilevoucherQuery.append(" and info.keyno = details.keyno and (RECONCILE_STATUS='N' or(vouchertype='C' and (ACCOUNTFLAG='N' or EMPACCOUNTFLAG='N')) ) and (ACCOUNTNO=? or (vouchertype = 'C' AND emp_party_code =?)) group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE,emp_party_code order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO");
queries.put("unReconcilevoucherQuery",unReconcilevoucherQuery);
StringBuffer ReconcilevoucherQuery = new StringBuffer("select sum(credit)bankbookcredit,sum(debit)bankbookdebit from(select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
ReconcilevoucherQuery.append(" ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
ReconcilevoucherQuery.append(" VOUCHERNO,(case when (vouchertype='C') then (decode(?, emp_party_code, sum(debit), sum(credit))) else(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))) end) credit, ");
ReconcilevoucherQuery.append(" (case when (vouchertype='C') then decode(?, emp_party_code, sum(credit), sum(debit)) else (decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) end) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
ReconcilevoucherQuery.append(" and info.keyno = details.keyno and (RECONCILE_STATUS='N' or(vouchertype='C' and (ACCOUNTFLAG='N' or EMPACCOUNTFLAG='N')) ) and (ACCOUNTNO=? or (vouchertype = 'C' AND emp_party_code =?)) group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE,emp_party_code order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO)");
queries.put("ReconcilevoucherQuery",ReconcilevoucherQuery);
StringBuffer reportQuery1 = new StringBuffer(" SELECT TRANSACTIONTYPE transtype,decode(TRANSACTIONTYPE,'C','Cheque','D','DD','I','Internal transfer','B',Decode(VOUCHERTYPE,'P','Direct Debit','Direct Credit'),'S','Cash') TRANSACTIONTYPE,");
reportQuery1.append(" Nvl(voucher.voucherNo,' ') voucherNo,voucher.accountNo accountNo,keyno, bankname,bank.accountcode bankaccountcode,nvl(OTHERMODULELINK,'N')OTHERMODULELINK,");
reportQuery1.append(" fyear,VOUCHERTYPE vtype,DECODE (vouchertype,'P', 'Payment','A','Adjustment','R', 'Receipt','C', 'Contra','N','Recovery Note',' ') ");
reportQuery1.append(" vouchertype,Nvl(partytype,' ') partytype,voucher.trusttype trusttype,");
reportQuery1.append(" (CASE WHEN partytype ='E' THEN ecpfacno WHEN partytype = 'B' THEN (SELECT bankname FROM CB_BANK_INFO WHERE accountno = emp_party_code) else emp_party_code END ) emp_party_code,emp_party_code partyDt,nvl(EMPNO,' ')EMPNO,(CASE WHEN partytype='E' and voucher.PFIDFLAG='true' THEN employeename WHEN partytype='E' THEN (SELECT employeename FROM episfin.employeeinfo WHERE EMPLNUMBER= voucher.EMPNO) when partytype='R' then (select employeename from employee_personal_info where medicalno=voucher.medicalno) ELSE emp_party_code end) partyDetails,(case when PARTYTYPE='E' then (SELECT designationdesc FROM episfin.employeeinfo employee,episfin.staffdesignation desig WHERE employee.DESIGNATIONCD=desig.DESIGNATIONCD and EMPLNUMBER= voucher.EMPNO) when PARTYTYPE='R' then (select DESEGNATION from employee_personal_info where MEDICALNO=voucher.medicalno) else '--' end)designation, ");
reportQuery1.append(" Nvl(USERNAME,' ') preparedby, Nvl(voucherno,' ') voucherno, (case when partytype='E' then (select BACNO from episfin.employeeinfo where EMPLNUMBER=voucher.EMPNO) when partytype='R' and voucher.TRUSTTYPE='MEDICAL' then (select ACCOUNTNO from employee_personal_info where MEDICALNO=voucher.MEDICALNO) else '--' end)empbankaccountno, ");
reportQuery1.append(" Nvl(checkedby,' ') checkedby, Nvl(approvedby,' ') approvedby,Nvl(details,' ') details");
reportQuery1.append(" ,Nvl(to_char(VOUCHER_DT,'dd/Mon/YYYY'),' ') VOUCHER_DT,APPROVAL,to_char(preperation_dt");
reportQuery1.append(" ,'dd/Mon/YYYY') preperation_dt,NVL(STATIONNAME,'')STATIONNAME,nvl(UNIT_NAME,' ')fullunitName,nvl(CANCEL_STATUS,'--')CANCEL_STATUS,nvl(to_char(CANCEL_VOUCHER_DATE,'dd/Mon/yyyy'),'--')CANCEL_VOUCHER_DATE,nvl(CANCEL_REMARKS,'--') CANCEL_REMARKS,nvl(TYPEOF_TRAVEL,'')TYPEOF_TRAVEL,nvl(PARTYNAME,' ')HOSPITAL_CODE,nvl(MEDICAL_TYPE,' ')MEDICAL_TYPE,nvl(LIMIT,' ')LIMIT,nvl(PAYABLE_PARTY,'E')PAYABLE_PARTY,decode('Y',(case when RELEASE_DATE is null and VOUCHERTYPE='P' then 'N' when RELEASE_DATE is not null and VOUCHERTYPE='P' then 'Y' end),'Paid',(case when RELEASE_DATE is null and VOUCHERTYPE='R' then 'N' when RELEASE_DATE is not null and VOUCHERTYPE='R' then 'Y' end),'Received', CANCEL_STATUS,'Canceled',APPROVAL,'Approved',(case when CHECKEDDT is null then 'N' else 'Y' end),'Checked','Fresh')voucherstatus,PREPERATION_NO preperationNO FROM Cb_VOUCHER_INFO voucher, CB_BANK_INFO bank,epis_user,epis_unitmaster unit,cb_party_info party WHERE USERID(+) = preparedby and voucher.STATIONNAME=unit.UNITCODE(+) and ");
reportQuery1.append(" bank.accountno = voucher.accountno and voucher.HOSPITAL_CODE=party.PARTYCODE(+) AND upper(trim(keyno)) = upper(trim(?)) ");
queries.put("reportQuery1",reportQuery1);
StringBuffer reportDtQuery1 = new StringBuffer("SELECT voucher.rowid,voucher.ACCOUNTHEAD ACCOUNTHEAD,acc.PARTICULAR");
reportDtQuery1.append(" PARTICULAR,MONTH_YEAR,Nvl(details,' ') details,Nvl(credit,0.0) credit,Nvl(debit,0.0)");
reportDtQuery1.append(" debit,(case when(debit>credit) then (debit-credit) else (credit-debit) end)amount,Nvl(chequeno,'0') chequeno,Nvl(Regard,' ') Regard,NVL(EMPACCOUNTNO,' ')EMPACCOUNTNO,'00000' ifsccode FROM Cb_voucher_details voucher, CB_ACCOUNTCODE_INFO acc");
reportDtQuery1.append(" WHERE voucher.ACCOUNTHEAD = acc.ACCOUNTHEAD AND upper(trim(keyno)) = upper(trim(?)) ");
queries.put("reportDtQuery1",reportDtQuery1);
StringBuffer retiremedicalQuery= new StringBuffer("select nvl(dt.amount,0)amount,nvl(personal.accountno,'--')EMPACCOUNTNO,nvl(personal.ifsccode, '000000')ifsccode from cb_medical_dt dt,cb_voucher_info info,employee_personal_info personal where info.keyno=dt.keyno and dt.medicalno=personal.medicalno and info.partytype='N' and info.keyno=?");
queries.put("retiremedicalQuery",retiremedicalQuery);
return queries.get(queryName).toString();
}
Thursday, May 9, 2013
generateTextFile
public VoucherInfo generateTextFile(VoucherInfo info11)throws Exception
{
VoucherInfo info=new VoucherInfo();
log.info("VoucherDAO : generateTextFile enter method");
String message="";
Connection con=null;
ResultSet rs=null,rs1=null;
PreparedStatement pst=null,pst1=null;
String accountno="";
DecimalFormat df1 = new DecimalFormat("000000.00");
DecimalFormat df2 = new DecimalFormat("############.00");
DecimalFormat df3 = new DecimalFormat("000000000000.00");
DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
Date date = new Date();
String sysdate=dateFormat.format(date);
try{
String str="";
String withoutstr="";
String totalstr="";
StringBuffer displayString=new StringBuffer();
String displaytext="";
int kk=0;
// String newLine = String.format("%n");
// String text = "First line" + newLine + "Second line";
String sline=SystemUtils.LINE_SEPARATOR;
double totAmt=0.0;
BufferedWriter output = null;
String line_separator = "\r\n";
FileWriter file=null;
con=DBUtility.getMultipleConnection(info11.getDepartment());
pst = con.prepareStatement(getQueries("reportQuery1"));
pst.setString(1, info11.getKeyNo()); //reportDtQuery
pst.executeQuery();
rs=pst.executeQuery();
while(rs.next())
{
info = new VoucherInfo();
accountno=StringUtility.checknull(rs.getString("accountno"));
info.setEmpbankaccountno(rs.getString("empbankaccountno"));
info.setPartyType(StringUtility.checknull(rs.getString("partytype")));
log.info("rs.getString(accountno) : "+rs.getString("accountno"));
}
if(rs!=null)
rs.close();
if(pst!=null)
pst.close();
String dtQuery="";
if(!info.getPartyType().equals("N"))
dtQuery=getQueries("reportDtQuery1");
if(info.getPartyType().equals("N"))
dtQuery=getQueries("retiremedicalQuery");
pst = con.prepareStatement(dtQuery);
pst.setString(1, info11.getKeyNo()); //reportDtQuery
pst.executeQuery();
rs1=pst.executeQuery();
String fName="";
log.info("info11.getKeyNo()"+info11.getKeyNo());
String empaccountno="";
while(rs1.next()) {
empaccountno="";
/*info.setAmount(df2.format(rs1.getDouble("debit")));*/
if(accountno.equals("")) {
file=new FileWriter(info11.getPath()+"\\ALL.txt");
fName="ALL.txt";
}
else
if(accountno.equals("11084236539")) {
//SBI
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(info.getEmpbankaccountno());
if(info.getPartyType().equals("M") ||info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(rs1.getString("EMPACCOUNTNO"));
info.setTextFormat(""+StringUtility.checknull(empaccountno)+"`"+StringUtility.checknull(rs1.getString("ifsccode"))+"`"+sysdate+"``"+(rs1.getDouble("amount"))+"``"+info11.getDepartment()+"_PAYMENT");
displaytext=info.getTextFormat();
displaytext+=line_separator;
str+=displaytext;
if(!info.getPartyType().equals("N"))
info.setAmount(rs1.getString("debit"));
if(info.getPartyType().equals("N"))
info.setAmount(rs1.getString("amount"));
file =new FileWriter(info11.getPath()+"\\SBI.txt");
fName="SBI.txt";
}
else if(accountno.equals("91103150000018")) {
log.info("i getTextFormat nfo :------------------------"+accountno);
// Syndicate Bank
if(!info.getPartyType().equals("M")&& !info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(info.getEmpbankaccountno());
if(info.getPartyType().equals("M") || info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(rs1.getString("EMPACCOUNTNO"));
info.setTextFormat("CH "+StringUtility.checknull(empaccountno)+" C 000000"+(rs1.getDouble("amount"))+" "+StringUtility.checknull(rs1.getString("ifsccode"))+" "+info11.getDepartment()+"_PAYMENT ");
displaytext=info.getTextFormat();
displaytext+=line_separator;
str+=displaytext;
if(!info.getPartyType().equals("N"))
info.setAmount(rs1.getString("debit"));
if(info.getPartyType().equals("N"))
info.setAmount(rs1.getString("amount"));
log.info("i getTextFormat nfo :------------------------"+info.getTextFormat());
file =new FileWriter(info11.getPath()+"\\SYNDICATEBANK.txt");
fName="SYNDICATEBANK.txt";
}
if(!info.getPartyType().equals("N"))
totAmt+=(rs1.getDouble("debit")-rs1.getDouble("credit"));
if(info.getPartyType().equals("N"))
totAmt+=rs1.getDouble("amount");
}
if(accountno.equals("91103150000018")) {
displaytext="CH 91103150000018 D "+df3.format(totAmt)+" 00000000 "+info11.getDepartment()+"_AMOUNT ";
displaytext+=line_separator;
totalstr+=displaytext;
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
{
withoutstr="CH "+StringUtility.checknull(empaccountno)+" C 000000"+df3.format(totAmt)+" 00000000 "+info11.getDepartment()+"_PAYMENT ";
withoutstr+=line_separator;
}
}
else if(accountno.equals("11084236539")) {
displaytext="11084236539`10648`"+sysdate+"`"+df2.format(totAmt)+"``"+info11.getDepartment()+"_AMOUNT`";
displaytext+=line_separator;
totalstr+=displaytext;
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
{
withoutstr=""+StringUtility.checknull(empaccountno)+"`00000`"+sysdate+"``"+df2.format(totAmt)+"``"+info11.getDepartment()+"_PAYMENT";
withoutstr+=line_separator;
}
}
displayString.append(totalstr);
if(info.getPartyType().equals("M") || info.getPartyType().equals("N"))
displayString.append(str);
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
displayString.append(withoutstr);
file.write(displayString.toString());
file.close();
info.setPath(info11.getPath()+"\\"+fName);
/* if(textFormat.equals("1")) {
if(bankId.equals("2")) {
//log.info("Syndicate bank");
info.setTextSynb("CH "+rs.getString("BACNO")+" C 000000"+df1.format(rs.getDouble("NETAMOUNT"))+" 00000000 SALARY AMOUNT ");
}
if(bankId.equals("1")) {
//30141222500`00000`29/05/2012``14185.00`SALARAY`SALARY PAYMENTS`
//log.info("SBI");
info.setTextSynb(""+rs.getString("BACNO")+"`00000`"+sysdate+"``"+df2.format(rs.getDouble("NETAMOUNT"))+"`SALARAY`SALARY PAYMENTS`");
}
}
*/
message="Release Date Updated Successfully";
}
catch(Exception e)
{
e.printStackTrace();
}
finally {
try {
if(rs1!=null)
rs1.close();
if(pst1!=null)
pst1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//ending
log.info("the message is..."+message);
return info;
}
{
VoucherInfo info=new VoucherInfo();
log.info("VoucherDAO : generateTextFile enter method");
String message="";
Connection con=null;
ResultSet rs=null,rs1=null;
PreparedStatement pst=null,pst1=null;
String accountno="";
DecimalFormat df1 = new DecimalFormat("000000.00");
DecimalFormat df2 = new DecimalFormat("############.00");
DecimalFormat df3 = new DecimalFormat("000000000000.00");
DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
Date date = new Date();
String sysdate=dateFormat.format(date);
try{
String str="";
String withoutstr="";
String totalstr="";
StringBuffer displayString=new StringBuffer();
String displaytext="";
int kk=0;
// String newLine = String.format("%n");
// String text = "First line" + newLine + "Second line";
String sline=SystemUtils.LINE_SEPARATOR;
double totAmt=0.0;
BufferedWriter output = null;
String line_separator = "\r\n";
FileWriter file=null;
con=DBUtility.getMultipleConnection(info11.getDepartment());
pst = con.prepareStatement(getQueries("reportQuery1"));
pst.setString(1, info11.getKeyNo()); //reportDtQuery
pst.executeQuery();
rs=pst.executeQuery();
while(rs.next())
{
info = new VoucherInfo();
accountno=StringUtility.checknull(rs.getString("accountno"));
info.setEmpbankaccountno(rs.getString("empbankaccountno"));
info.setPartyType(StringUtility.checknull(rs.getString("partytype")));
log.info("rs.getString(accountno) : "+rs.getString("accountno"));
}
if(rs!=null)
rs.close();
if(pst!=null)
pst.close();
String dtQuery="";
if(!info.getPartyType().equals("N"))
dtQuery=getQueries("reportDtQuery1");
if(info.getPartyType().equals("N"))
dtQuery=getQueries("retiremedicalQuery");
pst = con.prepareStatement(dtQuery);
pst.setString(1, info11.getKeyNo()); //reportDtQuery
pst.executeQuery();
rs1=pst.executeQuery();
String fName="";
log.info("info11.getKeyNo()"+info11.getKeyNo());
String empaccountno="";
while(rs1.next()) {
empaccountno="";
/*info.setAmount(df2.format(rs1.getDouble("debit")));*/
if(accountno.equals("")) {
file=new FileWriter(info11.getPath()+"\\ALL.txt");
fName="ALL.txt";
}
else
if(accountno.equals("11084236539")) {
//SBI
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(info.getEmpbankaccountno());
if(info.getPartyType().equals("M") ||info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(rs1.getString("EMPACCOUNTNO"));
info.setTextFormat(""+StringUtility.checknull(empaccountno)+"`"+StringUtility.checknull(rs1.getString("ifsccode"))+"`"+sysdate+"``"+(rs1.getDouble("amount"))+"``"+info11.getDepartment()+"_PAYMENT");
displaytext=info.getTextFormat();
displaytext+=line_separator;
str+=displaytext;
if(!info.getPartyType().equals("N"))
info.setAmount(rs1.getString("debit"));
if(info.getPartyType().equals("N"))
info.setAmount(rs1.getString("amount"));
file =new FileWriter(info11.getPath()+"\\SBI.txt");
fName="SBI.txt";
}
else if(accountno.equals("91103150000018")) {
log.info("i getTextFormat nfo :------------------------"+accountno);
// Syndicate Bank
if(!info.getPartyType().equals("M")&& !info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(info.getEmpbankaccountno());
if(info.getPartyType().equals("M") || info.getPartyType().equals("N"))
empaccountno=StringUtility.checknull(rs1.getString("EMPACCOUNTNO"));
info.setTextFormat("CH "+StringUtility.checknull(empaccountno)+" C 000000"+(rs1.getDouble("amount"))+" "+StringUtility.checknull(rs1.getString("ifsccode"))+" "+info11.getDepartment()+"_PAYMENT ");
displaytext=info.getTextFormat();
displaytext+=line_separator;
str+=displaytext;
if(!info.getPartyType().equals("N"))
info.setAmount(rs1.getString("debit"));
if(info.getPartyType().equals("N"))
info.setAmount(rs1.getString("amount"));
log.info("i getTextFormat nfo :------------------------"+info.getTextFormat());
file =new FileWriter(info11.getPath()+"\\SYNDICATEBANK.txt");
fName="SYNDICATEBANK.txt";
}
if(!info.getPartyType().equals("N"))
totAmt+=(rs1.getDouble("debit")-rs1.getDouble("credit"));
if(info.getPartyType().equals("N"))
totAmt+=rs1.getDouble("amount");
}
if(accountno.equals("91103150000018")) {
displaytext="CH 91103150000018 D "+df3.format(totAmt)+" 00000000 "+info11.getDepartment()+"_AMOUNT ";
displaytext+=line_separator;
totalstr+=displaytext;
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
{
withoutstr="CH "+StringUtility.checknull(empaccountno)+" C 000000"+df3.format(totAmt)+" 00000000 "+info11.getDepartment()+"_PAYMENT ";
withoutstr+=line_separator;
}
}
else if(accountno.equals("11084236539")) {
displaytext="11084236539`10648`"+sysdate+"`"+df2.format(totAmt)+"``"+info11.getDepartment()+"_AMOUNT`";
displaytext+=line_separator;
totalstr+=displaytext;
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
{
withoutstr=""+StringUtility.checknull(empaccountno)+"`00000`"+sysdate+"``"+df2.format(totAmt)+"``"+info11.getDepartment()+"_PAYMENT";
withoutstr+=line_separator;
}
}
displayString.append(totalstr);
if(info.getPartyType().equals("M") || info.getPartyType().equals("N"))
displayString.append(str);
if(!info.getPartyType().equals("M") && !info.getPartyType().equals("N"))
displayString.append(withoutstr);
file.write(displayString.toString());
file.close();
info.setPath(info11.getPath()+"\\"+fName);
/* if(textFormat.equals("1")) {
if(bankId.equals("2")) {
//log.info("Syndicate bank");
info.setTextSynb("CH "+rs.getString("BACNO")+" C 000000"+df1.format(rs.getDouble("NETAMOUNT"))+" 00000000 SALARY AMOUNT ");
}
if(bankId.equals("1")) {
//30141222500`00000`29/05/2012``14185.00`SALARAY`SALARY PAYMENTS`
//log.info("SBI");
info.setTextSynb(""+rs.getString("BACNO")+"`00000`"+sysdate+"``"+df2.format(rs.getDouble("NETAMOUNT"))+"`SALARAY`SALARY PAYMENTS`");
}
}
*/
message="Release Date Updated Successfully";
}
catch(Exception e)
{
e.printStackTrace();
}
finally {
try {
if(rs1!=null)
rs1.close();
if(pst1!=null)
pst1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//ending
log.info("the message is..."+message);
return info;
}
getFinYears()
public List getFinYears() {
String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR)+((Calendar.MONTH>2)?1:0));
String[] yearArray = StringUtility.getFinYears("2003",year);
Bean years = null;
int len = yearArray.length;
List finYears = new ArrayList();
for(int i=0;i<len;i++ ){
years = new Bean(yearArray[i],yearArray[i]);
finYears.add(years);
}
return finYears;
}
String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR)+((Calendar.MONTH>2)?1:0));
String[] yearArray = StringUtility.getFinYears("2003",year);
Bean years = null;
int len = yearArray.length;
List finYears = new ArrayList();
for(int i=0;i<len;i++ ){
years = new Bean(yearArray[i],yearArray[i]);
finYears.add(years);
}
return finYears;
}
Voucher.java
package com.epis.action.cashbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionMessage;
import org.apache.struts.action.ActionMessages;
import org.apache.struts.validator.DynaValidatorActionForm;
import com.epis.action.CommonServiceAction;
import com.epis.bean.cashbook.BankBook;
import com.epis.bean.cashbook.BankMasterInfo;
import com.epis.bean.cashbook.VoucherDetails;
import com.epis.bean.cashbook.VoucherInfo;
import com.epis.services.cashbook.BankMasterService;
import com.epis.services.cashbook.VoucherService;
import com.epis.utilities.Log;
import com.epis.utilities.StringUtility;
import com.epis.utilities.UserTracking;
public class Voucher extends CommonServiceAction {
/**
* Method search
*
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
public ActionForward search(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
ActionMessages errors = (ActionMessages)request.getAttribute("org.apache.struts.action.ERROR");
this.setStation(request,response);
try {
if(errors == null)
errors = new ActionMessages();
DynaValidatorActionForm dyna = (DynaValidatorActionForm) form;
VoucherInfo info = new VoucherInfo();
BeanUtils.copyProperties(info,dyna);
request.setAttribute("VoucherList", VoucherService.getInstance().search(info));
HttpSession session = request.getSession();
session.setAttribute("finYearList", VoucherService.getInstance().getFinYears());
} catch (IllegalAccessException e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
} catch (InvocationTargetException e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
} catch (Exception e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return mapping.getInputForward();
}
/**
* Method fwdtoNew
*
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
* @throws Exception
*/
public ActionForward fwdtoNew(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
DynaValidatorActionForm dyna = (DynaValidatorActionForm)form;
String voucherType = (String) dyna.get("voucherType");
ActionForward forward = null;
this.setStation(request,response);
if("P".equals(voucherType)){
System.out.println("---1---------");
forward = mapping.findForward("paymentNew");
}else if("R".equals(voucherType)){
System.out.println("----2--------");
forward = mapping.findForward("receiptNew");
}
return forward;
}
Log log = new Log(Voucher.class);
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
VoucherInfo info = new VoucherInfo();
VoucherService service = VoucherService.getInstance();
log.info("Voucher : service : Entering Method");
String dispatch = null;
String redirect = null;
this.setStation(request,response);
if ("addVoucher".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setAccountNo(request.getParameter("accountNo"));
info.setBankName(request.getParameter("bankName"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType(request.getParameter("vouchertype"));
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
info.setDesignation(request.getParameter("edesignation"));
info.setTransactionType(request.getParameter("transType"));
String detailRecords[] = request
.getParameterValues("detailRecords");
int length = detailRecords.length;
List voucherDts = new ArrayList();
VoucherDetails voucherDt = null;
for (int i = 0; i < length; i++) {
StringTokenizer st = new StringTokenizer(detailRecords[i], "|");
if (st.hasMoreTokens()) {
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(st.nextToken());
voucherDt.setMonthYear(st.nextToken());
voucherDt.setDetails(st.nextToken());
voucherDt.setChequeNo(st.nextToken());
voucherDt.setDebit(Double.parseDouble(st.nextToken()));
voucherDt.setCredit(Double.parseDouble(st.nextToken()));
}
voucherDts.add(voucherDt);
}
info.setVoucherDetails(voucherDts);
if (!"C".equals(info.getVoucherType())) {
info.setPartyType(request.getParameter("party"));
if ("E".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("epfid"));
// added three new parameters cpfaccno, region, pfidFlag
info.setEmpCpfacno(request.getParameter("ecpfacno"));
info.setEmpRegion(request.getParameter("eregion"));
info.setPfidFlag(request.getParameter("pfidFlag"));
info.setEmployeeName(request.getParameter("eName"));
log.info("pfid flag" + info.getPfidFlag());
} else if ("P".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("pName"));
}
} else {
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
}
try {
String keyNo = service.addVoucherRecord(info);
redirect = "./jsp/cashbook/voucher/Voucher.jsp?accountNo="
+ info.getAccountNo() + "&&type="
+ info.getVoucherType() + "&&keyNo=" + keyNo+"&&bankName="+info.getBankName();
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("searchRecords".equals(request.getParameter("method"))) {
info.setBankName(request.getParameter("bankName") == null ? ""
: request.getParameter("bankName"));
info.setFinYear(request.getParameter("year") == null ? "" : request
.getParameter("year"));
info
.setVoucherType(request.getParameter("voucherType") == null ? ""
: request.getParameter("voucherType"));
info.setPartyType(request.getParameter("partyType") == null ? ""
: request.getParameter("partyType"));
info.setAccountNo(request.getParameter("accountNo") == null ? ""
: request.getParameter("accountNo"));
info.setKeyNo(request.getParameter("keyNo") == null ? "" : request
.getParameter("keyNo"));
info.setVoucherNo(request.getParameter("vNo") == null ? ""
: request.getParameter("vNo"));
info.setVoucherDt(request.getParameter("vDate") == null ? ""
: request.getParameter("vDate"));
info.setPreparedDt(request.getParameter("prepDate") == null ? ""
: request.getParameter("prepDate"));
try {
List dataList = service.searchRecords(info, "");
request.setAttribute("dataList", dataList);
} catch (Exception e) {
log.printStackTrace(e);
}
dispatch = "./jsp/cashbook/voucher/VoucherSearch.jsp?keyNo="
+ info.getKeyNo() + "&&type=" + info.getVoucherType();
} else if ("getReport".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
try {
info = service.getReport(info);
request.setAttribute("info", info);
if ("approve".equals(request.getParameter("type"))) {
BankBook book = service.getOpenBalance(info.getAccountNo(),
"01/Jan/9999");
request.setAttribute("book", book);
dispatch = "./jsp/cashbook/voucher/ApprovedVoucher.jsp";
} else if ("Contra".equals(info.getVoucherType())) {
dispatch = "./jsp/cashbook/voucher/ContraVoucherReport.jsp";
} else {
dispatch = "./jsp/cashbook/voucher/VoucherReport.jsp";
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getVoucherAppRecords"
.equals(request.getParameter("method"))) {
info.setBankName(request.getParameter("bankName") == null ? ""
: request.getParameter("bankName"));
info.setFinYear(request.getParameter("year") == null ? "" : request
.getParameter("year"));
info
.setVoucherType(request.getParameter("voucherType") == null ? ""
: request.getParameter("voucherType"));
info.setPartyType(request.getParameter("partyType") == null ? ""
: request.getParameter("partyType"));
info.setPreparedDt(request.getParameter("prepDate") == null ? ""
: request.getParameter("prepDate"));
info.setKeyNo(request.getParameter("prepNo") == null ? "" : request
.getParameter("prepNo"));
try {
List dataList = service.searchRecords(info, "A");
request.setAttribute("dataList", dataList);
dispatch = "./jsp/cashbook/voucher/VoucherApproval.jsp";
if (request.getParameter("type") != null
&& "app".equals(request.getParameter("type"))) {
dispatch += "?&&keyNo="
+ (request.getParameter("keyNo") == null ? ""
: request.getParameter("keyNo"));
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getVoucherApproval".equals(request.getParameter("method"))) {
try {
info.setVoucherDt(request.getParameter("voucherDate"));
info.setKeyNo(request.getParameter("keyNo"));
info.setCheckedBy(request.getParameter("checkedby"));
info.setApprovedBy(request.getParameter("approved"));
info.setStatus(request.getParameter("approvalstatus"));
service.updateApprovalVoucher(info);
redirect = "./Voucher?method=getVoucherAppRecords&&keyNo="
+ info.getKeyNo() + "&&type=app";
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getBankBook".equals(request.getParameter("method"))) {
// info.setAccountNo(request.getParameter("accountNo"));
info.setAccountHead(request.getParameter("accountCode"));
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
try {
List book = service.getBankBook(info);
request.setAttribute("book", book);
if(request.getParameter("reportType").equals("html")){
dispatch = "./jsp/cashbook/bankbook/BankBook.jsp?fromDate="
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&accountHead="+
info.getAccountHead()+"&&reportType="+request.getParameter("reportType")
+"&&reportbal="+request.getParameter("reportbal");
}else{
dispatch = "./jsp/cashbook/bankbook/BankBookExcel.jsp?fromDate="
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&accountHead="+
info.getAccountHead()+"&&reportType="+request.getParameter("reportType")
+"&&reportbal="+request.getParameter("reportbal");
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("editVoucher".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
try {
VoucherInfo editInfo = service.getReport(info);
request.setAttribute("einfo", editInfo);
if ("C".equals(request.getParameter("type"))) {
dispatch = "./jsp/cashbook/voucher/ContraVoucherEdit.jsp";
} else {
dispatch = "./jsp/cashbook/voucher/VoucherEdit.jsp";
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("updateVoucher".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setDetails(request.getParameter("voucherDetails"));
info.setVoucherType(request.getParameter("vouchertype"));
info.setTransactionType(request.getParameter("transType"));
String detailRecords[] = request
.getParameterValues("detailRecords");
int length = detailRecords.length;
List voucherDts = new ArrayList();
VoucherDetails voucherDt = null;
for (int i = 0; i < length; i++) {
StringTokenizer st = new StringTokenizer(detailRecords[i], "|");
if (st.hasMoreTokens()) {
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(st.nextToken());
voucherDt.setMonthYear(st.nextToken());
voucherDt.setDetails(st.nextToken());
voucherDt.setChequeNo(st.nextToken());
voucherDt.setDebit(Double.parseDouble(st.nextToken()));
voucherDt.setCredit(Double.parseDouble(st.nextToken()));
}
voucherDts.add(voucherDt);
}
info.setVoucherDetails(voucherDts);
if (!"C".equals(info.getVoucherType())) {
info.setPartyType(request.getParameter("party"));
if ("E".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("epfid"));
} else if ("P".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("pName"));
}
} else {
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
}
try {
service.updateVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType="
+ request.getParameter("vouchertype");
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("deleteVoucher".equals(request.getParameter("method"))) {
String error = null;
try {
String keynos = request.getParameter("keynos");
String codes = keynos.substring(0, keynos.length() - 1);
service.deleteVoucher(codes);
HttpSession session = request.getSession();
UserTracking.write((String) session.getAttribute("userid"),
codes, "D", "CB", "", "Voucher");
} catch (Exception e) {
e.printStackTrace();
error = "Record(s) can not be deleted";
}
info.setKeyNo("");
List dataList;
try {
dataList = service.searchRecords(info, "A");
request.setAttribute("dataList", dataList);
} catch (Exception e) {
e.printStackTrace();
}
dispatch = "./jsp/cashbook/voucher/VoucherApproval.jsp?error="
+ error;
} else if ("addContraRecord".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType("C");
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
VoucherDetails voucherDt = new VoucherDetails();
voucherDt.setAccountHead(request.getParameter("accHead"));
voucherDt.setMonthYear(request.getParameter("monthYear"));
voucherDt.setDetails(request.getParameter("details"));
voucherDt.setChequeNo(request.getParameter("chequeNo"));
voucherDt.setCredit(Double.parseDouble(request.getParameter("amount")));
List voucherDts = new ArrayList();
voucherDts.add(voucherDt);
info.setVoucherDetails(voucherDts);
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
try {
String keyNo = service.addVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType=C"+ "&&keyNo=" + keyNo;
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getLedgerAccount".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setBankCode(request.getParameter("bankCode"));
info.setTrustType(request.getParameter("trusttype"));
info.setAccountHead(request.getParameter("accountCode"));
info.setVoucherType(request.getParameter("voucherType"));
try {
List dataList = service.getLedgerAccount(info);
request.setAttribute("book", dataList);
dispatch = "./jsp/cashbook/voucher/LedgerAccount.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate();
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("updateContraRecord".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setKeyNo(request.getParameter("keyNo"));
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType("C");
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
VoucherDetails voucherDt = null;
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(request.getParameter("accHead"));
voucherDt.setMonthYear(request.getParameter("monthYear"));
voucherDt.setDetails(request.getParameter("details"));
voucherDt.setChequeNo(request.getParameter("chequeNo"));
voucherDt.setDebit(Double.parseDouble(request
.getParameter("amount")));
List voucherDts = new ArrayList();
voucherDts.add(voucherDt);
info.setVoucherDetails(voucherDts);
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
try {
service.updateVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType="
+ request.getParameter("vouchertype");
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("getGenLedger".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setTrustType(request.getParameter("trusttype"));
info.setAccountHead(request.getParameter("accHead"));
info.setVoucherType(request.getParameter("voucherType"));
try {
Map ledger = service.getGenLedger(info);
Map accounts = service.getAccountHeadsOpeningBalance(info);
request.setAttribute("ledger", ledger);
request.setAttribute("accounts",accounts );
if("html".equals(request.getParameter("reportType"))){
dispatch = "./jsp/cashbook/generalledger/GenLedgerAccount.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()
+"&&reportContent="+request.getParameter("reportContent")
+"&&reportbal="+request.getParameter("reportbal");
}else{
dispatch = "./jsp/cashbook/generalledger/GenLedgerAccountExcel.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()
+"&&reportContent="+request.getParameter("reportContent")
+"&&reportbal="+request.getParameter("reportbal");
}
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("getTrialBalance".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setTrustType(request.getParameter("trusttype"));
String reportType = request.getParameter("reportType");
try {
Map trialbalance = service.getTrialBalance(info);
request.setAttribute("trailBalance", trialbalance);
if("html".equals(request.getParameter("report"))){
dispatch = "./jsp/cashbook/trialbalance/TrialBalance.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&reportType="+reportType;
}else {
dispatch = "./jsp/cashbook/trialbalance/TrialBalanceExcel.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&reportType="+reportType;
}
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("ePaymentSearch".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
try {
List voucherList = service.ePaymentSearch(info);
request.setAttribute("voucherList", voucherList);
dispatch = "./jsp/cashbook/voucher/EPaymentTransfersParam.jsp";
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("ePaymentTransfers".equals(request.getParameter("method"))) {
String keynos = request.getParameter("keynos");
try {
service.ePaymentTransfers(keynos);
dispatch = "./Voucher?method=ePaymentSearch";
} catch (Exception e) {
log.printStackTrace(e);
}
}
log.info("Voucher : service : Leaving Method");
if (redirect != null) {
response.sendRedirect(redirect);
} else if (dispatch != null) {
RequestDispatcher rd = request.getRequestDispatcher(dispatch);
rd.forward(request, response);
}
}
public ActionForward unreconcileParam(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
ActionForward forward = null;
this.setStation(request,response);
forward = mapping.findForward("unreconcileParam");
try {
request.setAttribute("banks",BankMasterService.getInstance().search(new BankMasterInfo()));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return forward;
}
public ActionForward bankReconciliation(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)
{
ActionForward forward=null;
forward=mapping.findForward("bankreconcileStatement");
this.setStation(request,response);
try{
request.setAttribute("banks",BankMasterService.getInstance().search(new BankMasterInfo()));
}
catch(Exception e)
{
e.printStackTrace();
}
return forward;
}
public ActionForward bankreconcileReport(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)
{
ActionForward forward=null;
VoucherInfo info=new VoucherInfo();
BankBook book=new BankBook();
String voucherDt=StringUtility.checknull(request.getParameter("voucherDt"));
String accountNo =StringUtility.checknull(request.getParameter("accountNumber"));
this.setStation(request,response);
ActionMessages errors = new ActionMessages();
forward = mapping.findForward("bankreconcileReport");
try {
info=VoucherService.getInstance().bankreconcileReport(voucherDt,accountNo);
book=VoucherService.getInstance().getBankReconcileOpenBalance(accountNo,voucherDt);
request.setAttribute("bankBook",book);
request.setAttribute("voucherbean",info);
} catch (Exception e) {
errors.add("Report",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return forward;
}
public ActionForward unreconcileVouchers(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
DynaValidatorActionForm dyna = (DynaValidatorActionForm) form;
List voucherlist = new ArrayList();
String voucherDt=dyna.getString("voucherDt");
String accountNo = dyna.getString("accountNo");
ActionMessages errors = new ActionMessages();
ActionForward forward = mapping.findForward("unreconcileVouchers");
this.setStation(request,response);
try {
voucherlist=VoucherService.getInstance().unreconcileVouchers(voucherDt,accountNo);
request.setAttribute("voucherlist",voucherlist);
} catch (Exception e) {
errors.add("Report",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return forward;
}
public ActionForward genTextFile(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
log.info("this is calling for text file creation...");
String uploadpath=request.getSession().getServletContext().getRealPath("/")+""+"uploads\\BankFiles";
log.info("uploadpath ::::"+uploadpath);
String keyno=StringUtility.checknull(request.getParameter("keyno"));
String department=StringUtility.checknull(request.getParameter("department"));
VoucherInfo infol=new VoucherInfo();
VoucherInfo info2=new VoucherInfo();
String message="";
StringBuffer sb=new StringBuffer();
try{
infol.setPath(uploadpath);
infol.setKeyNo(keyno);
infol.setDepartment(department);
info2=VoucherService.getInstance().generateTextFile(infol);
String path=info2.getPath();
String filenames=path.toString();
log.info("filenames : "+filenames);
response.setHeader("Content-Type", "text/plain");
response.setHeader("Content-Length", String.valueOf(new File(path).length()));
response.setHeader("Content-Disposition","attachment;filename="+filenames);
log.info("Path : "+path);
File file = new File(path);
FileInputStream fileIn = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
byte[] outputByte = new byte[1024];
// copy binary contect to output stream
while(fileIn.read(outputByte, 0, 1024) != -1)
{
out.write(outputByte, 0, 1024);
}
fileIn.close();
//message=info1.getPath();
out.close();
}
catch(Exception e)
{
System.out.println(e.toString());
e.printStackTrace();
}
return mapping.findForward("null");
}
}
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionMessage;
import org.apache.struts.action.ActionMessages;
import org.apache.struts.validator.DynaValidatorActionForm;
import com.epis.action.CommonServiceAction;
import com.epis.bean.cashbook.BankBook;
import com.epis.bean.cashbook.BankMasterInfo;
import com.epis.bean.cashbook.VoucherDetails;
import com.epis.bean.cashbook.VoucherInfo;
import com.epis.services.cashbook.BankMasterService;
import com.epis.services.cashbook.VoucherService;
import com.epis.utilities.Log;
import com.epis.utilities.StringUtility;
import com.epis.utilities.UserTracking;
public class Voucher extends CommonServiceAction {
/**
* Method search
*
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
public ActionForward search(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
ActionMessages errors = (ActionMessages)request.getAttribute("org.apache.struts.action.ERROR");
this.setStation(request,response);
try {
if(errors == null)
errors = new ActionMessages();
DynaValidatorActionForm dyna = (DynaValidatorActionForm) form;
VoucherInfo info = new VoucherInfo();
BeanUtils.copyProperties(info,dyna);
request.setAttribute("VoucherList", VoucherService.getInstance().search(info));
HttpSession session = request.getSession();
session.setAttribute("finYearList", VoucherService.getInstance().getFinYears());
} catch (IllegalAccessException e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
} catch (InvocationTargetException e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
} catch (Exception e) {
errors.add("search",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return mapping.getInputForward();
}
/**
* Method fwdtoNew
*
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
* @throws Exception
*/
public ActionForward fwdtoNew(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
DynaValidatorActionForm dyna = (DynaValidatorActionForm)form;
String voucherType = (String) dyna.get("voucherType");
ActionForward forward = null;
this.setStation(request,response);
if("P".equals(voucherType)){
System.out.println("---1---------");
forward = mapping.findForward("paymentNew");
}else if("R".equals(voucherType)){
System.out.println("----2--------");
forward = mapping.findForward("receiptNew");
}
return forward;
}
Log log = new Log(Voucher.class);
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
VoucherInfo info = new VoucherInfo();
VoucherService service = VoucherService.getInstance();
log.info("Voucher : service : Entering Method");
String dispatch = null;
String redirect = null;
this.setStation(request,response);
if ("addVoucher".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setAccountNo(request.getParameter("accountNo"));
info.setBankName(request.getParameter("bankName"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType(request.getParameter("vouchertype"));
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
info.setDesignation(request.getParameter("edesignation"));
info.setTransactionType(request.getParameter("transType"));
String detailRecords[] = request
.getParameterValues("detailRecords");
int length = detailRecords.length;
List voucherDts = new ArrayList();
VoucherDetails voucherDt = null;
for (int i = 0; i < length; i++) {
StringTokenizer st = new StringTokenizer(detailRecords[i], "|");
if (st.hasMoreTokens()) {
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(st.nextToken());
voucherDt.setMonthYear(st.nextToken());
voucherDt.setDetails(st.nextToken());
voucherDt.setChequeNo(st.nextToken());
voucherDt.setDebit(Double.parseDouble(st.nextToken()));
voucherDt.setCredit(Double.parseDouble(st.nextToken()));
}
voucherDts.add(voucherDt);
}
info.setVoucherDetails(voucherDts);
if (!"C".equals(info.getVoucherType())) {
info.setPartyType(request.getParameter("party"));
if ("E".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("epfid"));
// added three new parameters cpfaccno, region, pfidFlag
info.setEmpCpfacno(request.getParameter("ecpfacno"));
info.setEmpRegion(request.getParameter("eregion"));
info.setPfidFlag(request.getParameter("pfidFlag"));
info.setEmployeeName(request.getParameter("eName"));
log.info("pfid flag" + info.getPfidFlag());
} else if ("P".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("pName"));
}
} else {
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
}
try {
String keyNo = service.addVoucherRecord(info);
redirect = "./jsp/cashbook/voucher/Voucher.jsp?accountNo="
+ info.getAccountNo() + "&&type="
+ info.getVoucherType() + "&&keyNo=" + keyNo+"&&bankName="+info.getBankName();
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("searchRecords".equals(request.getParameter("method"))) {
info.setBankName(request.getParameter("bankName") == null ? ""
: request.getParameter("bankName"));
info.setFinYear(request.getParameter("year") == null ? "" : request
.getParameter("year"));
info
.setVoucherType(request.getParameter("voucherType") == null ? ""
: request.getParameter("voucherType"));
info.setPartyType(request.getParameter("partyType") == null ? ""
: request.getParameter("partyType"));
info.setAccountNo(request.getParameter("accountNo") == null ? ""
: request.getParameter("accountNo"));
info.setKeyNo(request.getParameter("keyNo") == null ? "" : request
.getParameter("keyNo"));
info.setVoucherNo(request.getParameter("vNo") == null ? ""
: request.getParameter("vNo"));
info.setVoucherDt(request.getParameter("vDate") == null ? ""
: request.getParameter("vDate"));
info.setPreparedDt(request.getParameter("prepDate") == null ? ""
: request.getParameter("prepDate"));
try {
List dataList = service.searchRecords(info, "");
request.setAttribute("dataList", dataList);
} catch (Exception e) {
log.printStackTrace(e);
}
dispatch = "./jsp/cashbook/voucher/VoucherSearch.jsp?keyNo="
+ info.getKeyNo() + "&&type=" + info.getVoucherType();
} else if ("getReport".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
try {
info = service.getReport(info);
request.setAttribute("info", info);
if ("approve".equals(request.getParameter("type"))) {
BankBook book = service.getOpenBalance(info.getAccountNo(),
"01/Jan/9999");
request.setAttribute("book", book);
dispatch = "./jsp/cashbook/voucher/ApprovedVoucher.jsp";
} else if ("Contra".equals(info.getVoucherType())) {
dispatch = "./jsp/cashbook/voucher/ContraVoucherReport.jsp";
} else {
dispatch = "./jsp/cashbook/voucher/VoucherReport.jsp";
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getVoucherAppRecords"
.equals(request.getParameter("method"))) {
info.setBankName(request.getParameter("bankName") == null ? ""
: request.getParameter("bankName"));
info.setFinYear(request.getParameter("year") == null ? "" : request
.getParameter("year"));
info
.setVoucherType(request.getParameter("voucherType") == null ? ""
: request.getParameter("voucherType"));
info.setPartyType(request.getParameter("partyType") == null ? ""
: request.getParameter("partyType"));
info.setPreparedDt(request.getParameter("prepDate") == null ? ""
: request.getParameter("prepDate"));
info.setKeyNo(request.getParameter("prepNo") == null ? "" : request
.getParameter("prepNo"));
try {
List dataList = service.searchRecords(info, "A");
request.setAttribute("dataList", dataList);
dispatch = "./jsp/cashbook/voucher/VoucherApproval.jsp";
if (request.getParameter("type") != null
&& "app".equals(request.getParameter("type"))) {
dispatch += "?&&keyNo="
+ (request.getParameter("keyNo") == null ? ""
: request.getParameter("keyNo"));
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getVoucherApproval".equals(request.getParameter("method"))) {
try {
info.setVoucherDt(request.getParameter("voucherDate"));
info.setKeyNo(request.getParameter("keyNo"));
info.setCheckedBy(request.getParameter("checkedby"));
info.setApprovedBy(request.getParameter("approved"));
info.setStatus(request.getParameter("approvalstatus"));
service.updateApprovalVoucher(info);
redirect = "./Voucher?method=getVoucherAppRecords&&keyNo="
+ info.getKeyNo() + "&&type=app";
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getBankBook".equals(request.getParameter("method"))) {
// info.setAccountNo(request.getParameter("accountNo"));
info.setAccountHead(request.getParameter("accountCode"));
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
try {
List book = service.getBankBook(info);
request.setAttribute("book", book);
if(request.getParameter("reportType").equals("html")){
dispatch = "./jsp/cashbook/bankbook/BankBook.jsp?fromDate="
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&accountHead="+
info.getAccountHead()+"&&reportType="+request.getParameter("reportType")
+"&&reportbal="+request.getParameter("reportbal");
}else{
dispatch = "./jsp/cashbook/bankbook/BankBookExcel.jsp?fromDate="
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&accountHead="+
info.getAccountHead()+"&&reportType="+request.getParameter("reportType")
+"&&reportbal="+request.getParameter("reportbal");
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("editVoucher".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
try {
VoucherInfo editInfo = service.getReport(info);
request.setAttribute("einfo", editInfo);
if ("C".equals(request.getParameter("type"))) {
dispatch = "./jsp/cashbook/voucher/ContraVoucherEdit.jsp";
} else {
dispatch = "./jsp/cashbook/voucher/VoucherEdit.jsp";
}
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("updateVoucher".equals(request.getParameter("method"))) {
info.setKeyNo(request.getParameter("keyNo"));
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setDetails(request.getParameter("voucherDetails"));
info.setVoucherType(request.getParameter("vouchertype"));
info.setTransactionType(request.getParameter("transType"));
String detailRecords[] = request
.getParameterValues("detailRecords");
int length = detailRecords.length;
List voucherDts = new ArrayList();
VoucherDetails voucherDt = null;
for (int i = 0; i < length; i++) {
StringTokenizer st = new StringTokenizer(detailRecords[i], "|");
if (st.hasMoreTokens()) {
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(st.nextToken());
voucherDt.setMonthYear(st.nextToken());
voucherDt.setDetails(st.nextToken());
voucherDt.setChequeNo(st.nextToken());
voucherDt.setDebit(Double.parseDouble(st.nextToken()));
voucherDt.setCredit(Double.parseDouble(st.nextToken()));
}
voucherDts.add(voucherDt);
}
info.setVoucherDetails(voucherDts);
if (!"C".equals(info.getVoucherType())) {
info.setPartyType(request.getParameter("party"));
if ("E".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("epfid"));
} else if ("P".equals(info.getPartyType())) {
info.setEmpPartyCode(request.getParameter("pName"));
}
} else {
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
}
try {
service.updateVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType="
+ request.getParameter("vouchertype");
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("deleteVoucher".equals(request.getParameter("method"))) {
String error = null;
try {
String keynos = request.getParameter("keynos");
String codes = keynos.substring(0, keynos.length() - 1);
service.deleteVoucher(codes);
HttpSession session = request.getSession();
UserTracking.write((String) session.getAttribute("userid"),
codes, "D", "CB", "", "Voucher");
} catch (Exception e) {
e.printStackTrace();
error = "Record(s) can not be deleted";
}
info.setKeyNo("");
List dataList;
try {
dataList = service.searchRecords(info, "A");
request.setAttribute("dataList", dataList);
} catch (Exception e) {
e.printStackTrace();
}
dispatch = "./jsp/cashbook/voucher/VoucherApproval.jsp?error="
+ error;
} else if ("addContraRecord".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType("C");
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
VoucherDetails voucherDt = new VoucherDetails();
voucherDt.setAccountHead(request.getParameter("accHead"));
voucherDt.setMonthYear(request.getParameter("monthYear"));
voucherDt.setDetails(request.getParameter("details"));
voucherDt.setChequeNo(request.getParameter("chequeNo"));
voucherDt.setCredit(Double.parseDouble(request.getParameter("amount")));
List voucherDts = new ArrayList();
voucherDts.add(voucherDt);
info.setVoucherDetails(voucherDts);
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
try {
String keyNo = service.addVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType=C"+ "&&keyNo=" + keyNo;
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("getLedgerAccount".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setBankCode(request.getParameter("bankCode"));
info.setTrustType(request.getParameter("trusttype"));
info.setAccountHead(request.getParameter("accountCode"));
info.setVoucherType(request.getParameter("voucherType"));
try {
List dataList = service.getLedgerAccount(info);
request.setAttribute("book", dataList);
dispatch = "./jsp/cashbook/voucher/LedgerAccount.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate();
} catch (Exception e) {
log.printStackTrace(e);
}
} else if ("updateContraRecord".equals(request.getParameter("method"))) {
HttpSession session = request.getSession();
info.setKeyNo(request.getParameter("keyNo"));
info.setAccountNo(request.getParameter("accountNo"));
info.setFinYear(request.getParameter("year"));
info.setTrustType(request.getParameter("trusttype"));
info.setVoucherType("C");
info.setEnteredBy((String) session.getAttribute("userid"));
info.setPreparedBy((String) session.getAttribute("userid"));
info.setDetails(request.getParameter("voucherDetails"));
info.setPreparedDt(request.getParameter("prepDate"));
VoucherDetails voucherDt = null;
voucherDt = new VoucherDetails();
voucherDt.setAccountHead(request.getParameter("accHead"));
voucherDt.setMonthYear(request.getParameter("monthYear"));
voucherDt.setDetails(request.getParameter("details"));
voucherDt.setChequeNo(request.getParameter("chequeNo"));
voucherDt.setDebit(Double.parseDouble(request
.getParameter("amount")));
List voucherDts = new ArrayList();
voucherDts.add(voucherDt);
info.setVoucherDetails(voucherDts);
info.setPartyType("B");
info.setEmpPartyCode(request.getParameter("contraAccountNo"));
try {
service.updateVoucherRecord(info);
redirect = "./Voucher?method=searchRecords&&accountNo="
+ info.getAccountNo() + "&&voucherType="
+ request.getParameter("vouchertype");
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("getGenLedger".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setTrustType(request.getParameter("trusttype"));
info.setAccountHead(request.getParameter("accHead"));
info.setVoucherType(request.getParameter("voucherType"));
try {
Map ledger = service.getGenLedger(info);
Map accounts = service.getAccountHeadsOpeningBalance(info);
request.setAttribute("ledger", ledger);
request.setAttribute("accounts",accounts );
if("html".equals(request.getParameter("reportType"))){
dispatch = "./jsp/cashbook/generalledger/GenLedgerAccount.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()
+"&&reportContent="+request.getParameter("reportContent")
+"&&reportbal="+request.getParameter("reportbal");
}else{
dispatch = "./jsp/cashbook/generalledger/GenLedgerAccountExcel.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()
+"&&reportContent="+request.getParameter("reportContent")
+"&&reportbal="+request.getParameter("reportbal");
}
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("getTrialBalance".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
info.setTrustType(request.getParameter("trusttype"));
String reportType = request.getParameter("reportType");
try {
Map trialbalance = service.getTrialBalance(info);
request.setAttribute("trailBalance", trialbalance);
if("html".equals(request.getParameter("report"))){
dispatch = "./jsp/cashbook/trialbalance/TrialBalance.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&reportType="+reportType;
}else {
dispatch = "./jsp/cashbook/trialbalance/TrialBalanceExcel.jsp?fromDate"
+ info.getFromDate() + "&&toDate=" + info.getToDate()+"&&reportType="+reportType;
}
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("ePaymentSearch".equals(request.getParameter("method"))) {
info.setFromDate(request.getParameter("fromDate"));
info.setToDate(request.getParameter("toDate"));
try {
List voucherList = service.ePaymentSearch(info);
request.setAttribute("voucherList", voucherList);
dispatch = "./jsp/cashbook/voucher/EPaymentTransfersParam.jsp";
} catch (Exception e) {
log.printStackTrace(e);
}
}else if ("ePaymentTransfers".equals(request.getParameter("method"))) {
String keynos = request.getParameter("keynos");
try {
service.ePaymentTransfers(keynos);
dispatch = "./Voucher?method=ePaymentSearch";
} catch (Exception e) {
log.printStackTrace(e);
}
}
log.info("Voucher : service : Leaving Method");
if (redirect != null) {
response.sendRedirect(redirect);
} else if (dispatch != null) {
RequestDispatcher rd = request.getRequestDispatcher(dispatch);
rd.forward(request, response);
}
}
public ActionForward unreconcileParam(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
ActionForward forward = null;
this.setStation(request,response);
forward = mapping.findForward("unreconcileParam");
try {
request.setAttribute("banks",BankMasterService.getInstance().search(new BankMasterInfo()));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return forward;
}
public ActionForward bankReconciliation(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)
{
ActionForward forward=null;
forward=mapping.findForward("bankreconcileStatement");
this.setStation(request,response);
try{
request.setAttribute("banks",BankMasterService.getInstance().search(new BankMasterInfo()));
}
catch(Exception e)
{
e.printStackTrace();
}
return forward;
}
public ActionForward bankreconcileReport(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)
{
ActionForward forward=null;
VoucherInfo info=new VoucherInfo();
BankBook book=new BankBook();
String voucherDt=StringUtility.checknull(request.getParameter("voucherDt"));
String accountNo =StringUtility.checknull(request.getParameter("accountNumber"));
this.setStation(request,response);
ActionMessages errors = new ActionMessages();
forward = mapping.findForward("bankreconcileReport");
try {
info=VoucherService.getInstance().bankreconcileReport(voucherDt,accountNo);
book=VoucherService.getInstance().getBankReconcileOpenBalance(accountNo,voucherDt);
request.setAttribute("bankBook",book);
request.setAttribute("voucherbean",info);
} catch (Exception e) {
errors.add("Report",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return forward;
}
public ActionForward unreconcileVouchers(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
DynaValidatorActionForm dyna = (DynaValidatorActionForm) form;
List voucherlist = new ArrayList();
String voucherDt=dyna.getString("voucherDt");
String accountNo = dyna.getString("accountNo");
ActionMessages errors = new ActionMessages();
ActionForward forward = mapping.findForward("unreconcileVouchers");
this.setStation(request,response);
try {
voucherlist=VoucherService.getInstance().unreconcileVouchers(voucherDt,accountNo);
request.setAttribute("voucherlist",voucherlist);
} catch (Exception e) {
errors.add("Report",new ActionMessage("errors",e.getMessage()));
}
saveErrors(request,errors);
return forward;
}
public ActionForward genTextFile(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {
log.info("this is calling for text file creation...");
String uploadpath=request.getSession().getServletContext().getRealPath("/")+""+"uploads\\BankFiles";
log.info("uploadpath ::::"+uploadpath);
String keyno=StringUtility.checknull(request.getParameter("keyno"));
String department=StringUtility.checknull(request.getParameter("department"));
VoucherInfo infol=new VoucherInfo();
VoucherInfo info2=new VoucherInfo();
String message="";
StringBuffer sb=new StringBuffer();
try{
infol.setPath(uploadpath);
infol.setKeyNo(keyno);
infol.setDepartment(department);
info2=VoucherService.getInstance().generateTextFile(infol);
String path=info2.getPath();
String filenames=path.toString();
log.info("filenames : "+filenames);
response.setHeader("Content-Type", "text/plain");
response.setHeader("Content-Length", String.valueOf(new File(path).length()));
response.setHeader("Content-Disposition","attachment;filename="+filenames);
log.info("Path : "+path);
File file = new File(path);
FileInputStream fileIn = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
byte[] outputByte = new byte[1024];
// copy binary contect to output stream
while(fileIn.read(outputByte, 0, 1024) != -1)
{
out.write(outputByte, 0, 1024);
}
fileIn.close();
//message=info1.getPath();
out.close();
}
catch(Exception e)
{
System.out.println(e.toString());
e.printStackTrace();
}
return mapping.findForward("null");
}
}
CommonServiceAction.java
package com.epis.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.actions.DispatchAction;
import com.epis.utilities.ConnectionInfo;
public class CommonServiceAction extends DispatchAction{
protected void setStation(HttpServletRequest request,HttpServletResponse response){
System.out.println("CommonAction::setStation");
ConnectionInfo conninfo=new ConnectionInfo(request);
}
}
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.actions.DispatchAction;
import com.epis.utilities.ConnectionInfo;
public class CommonServiceAction extends DispatchAction{
protected void setStation(HttpServletRequest request,HttpServletResponse response){
System.out.println("CommonAction::setStation");
ConnectionInfo conninfo=new ConnectionInfo(request);
}
}
ConnectionInfo.java
package com.epis.utilities;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
public class ConnectionInfo {
public ConnectionInfo(HttpServletRequest req)
{
HttpSession station=req.getSession();
if(station!=null)
{
System.out.println(station.getId());
String dept=station.getAttribute("station").toString();
setInfo(dept);
}
}
private static String local = null;
public static String getInfo() {
return local;
}
public static void setInfo(String value) {
local=value;
}
}
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
public class ConnectionInfo {
public ConnectionInfo(HttpServletRequest req)
{
HttpSession station=req.getSession();
if(station!=null)
{
System.out.println(station.getId());
String dept=station.getAttribute("station").toString();
setInfo(dept);
}
}
private static String local = null;
public static String getInfo() {
return local;
}
public static void setInfo(String value) {
local=value;
}
}
Subscribe to:
Posts (Atom)