package custom.energypro.vinnica.rolldecode;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRScriptletException;
import net.sf.jasperreports.engine.data.JRMapCollectionDataSource;
import system.LangLogic;
import system.PeriodLogic;
import com.uens.query.dao.SqlUtil;
public class rollDecodeDetailedScriptlet extends JRDefaultScriptlet {
private static final String F_GROUP = "GROUP";
private static final String F_TITLE = "TITLE";
private static final String F_DT = "DT";
private static final String F_KT = "KT";
private String sql_getSaldo = SqlUtil.loadSQL(getClass(), "getSaldo.sql");
private String sql_getDebt = SqlUtil.loadSQL(getClass(), "getDebt.sql");
private String sql_getCred = SqlUtil.loadSQL(getClass(), "getCred.sql");
public JRDataSource getSaldoDS(int renCode, int periodCode)
throws JRScriptletException {
Connection conn = (Connection) getParameterValue("REPORT_CONNECTION");
PreparedStatement st = null;
ResultSet rs = null;
Collection<Map<String, ?>> data = new ArrayList<Map<String, ?>>();
try {
st = conn.prepareStatement(sql_getSaldo);
st.setInt(1, renCode);
st.setInt(2, periodCode);
rs = st.executeQuery();
while (rs.next()) {
String statName = rs.getString(2);
String cat = rs.getString(4);
BigDecimal inDebt = rs.getBigDecimal(5);
BigDecimal inDebtVat = rs.getBigDecimal(6);
BigDecimal inCred = rs.getBigDecimal(7);
BigDecimal inCredVat = rs.getBigDecimal(8);
Map<String, Object> row = createRow(statName, " " + cat,
inDebt, inCred);
data.add(row);
if (inDebtVat.signum() != 0 || inCredVat.signum() != 0) {
row = createRow(statName, " ���", inDebtVat,
inCredVat);
data.add(row);
}
}
return new JRMapCollectionDataSource(data);
} catch (SQLException e) {
throw new JRScriptletException(e.getMessage(), e);
} finally {
SqlUtil.close(rs);
SqlUtil.close(st);
}
}
public JRDataSource getDebtDS(int renCode, int periodCode)
throws JRScriptletException {
Connection conn = (Connection) getParameterValue("REPORT_CONNECTION");
PreparedStatement st = null;
ResultSet rs = null;
Collection<Map<String, ?>> data = new ArrayList<Map<String, ?>>();
try {
st = conn.prepareStatement(sql_getDebt);
st.setInt(1, renCode);
st.setInt(2, periodCode);
st.setInt(3, renCode);
st.setInt(4, periodCode);
rs = st.executeQuery();
while (rs.next()) {
String statName = rs.getString(2);
String cat = rs.getString(4);
BigDecimal inDebt = rs.getBigDecimal(5);
BigDecimal inDebtVat = rs.getBigDecimal(6);
Map<String, Object> row = createRow(statName, " " + cat,
inDebt, null);
data.add(row);
if (inDebtVat.signum() != 0) {
row = createRow(statName, " ���", inDebtVat, null);
data.add(row);
}
}
return new JRMapCollectionDataSource(data);
} catch (SQLException e) {
throw new JRScriptletException(e.getMessage(), e);
} finally {
SqlUtil.close(rs);
SqlUtil.close(st);
}
}
public JRDataSource getCredDS(int renCode, int periodCode, boolean isAviso)
throws JRScriptletException {
Connection conn = (Connection) getParameterValue("REPORT_CONNECTION");
Integer avisoChannelType = (Integer) getParameterValue("avisoChannelType");
PreparedStatement st = null;
ResultSet rs = null;
Collection<Map<String, ?>> data = new ArrayList<Map<String, ?>>();
try {
String sql = SqlUtil.parse(sql_getCred, new String[] {
"?",
"?",
isAviso ? " = " + avisoChannelType : " <> "
+ avisoChannelType });
st = conn.prepareStatement(sql);
st.setInt(1, renCode);
st.setInt(2, periodCode);
rs = st.executeQuery();
while (rs.next()) {
String statName = rs.getString(2);
String cat = rs.getString(4);
BigDecimal inCred = rs.getBigDecimal(5);
BigDecimal inCredVat = rs.getBigDecimal(6);
Map<String, Object> row = createRow(statName, " " + cat,
null, inCred);
data.add(row);
if (inCredVat.signum() != 0) {
row = createRow(statName, " ���", null, inCredVat);
data.add(row);
}
}
return new JRMapCollectionDataSource(data);
} catch (SQLException e) {
throw new JRScriptletException(e.getMessage(), e);
} finally {
SqlUtil.close(rs);
SqlUtil.close(st);
}
}
private Map<String, Object> createRow(String group, String title,
BigDecimal inDebt, BigDecimal inCred) {
Map<String, Object> result = new HashMap<String, Object>();
result.put(F_GROUP, group);
result.put(F_TITLE, title);
result.put(F_DT, inDebt);
result.put(F_KT, inCred);
return result;
}
public String getMonthYearUA(int periodCode) throws JRScriptletException {
PeriodLogic pl = new PeriodLogic(
(Connection) getParameterValue("REPORT_CONNECTION"));
pl.loadPeriodByCode(periodCode);
return LangLogic.getMonthYearUA(pl.getStartDate());
}
}