package custom.energypro.vinnica.nadhodzhennya_po_galuzyam;
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.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
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 com.uens.analysis.AnalysisProcessorImpl;
import com.uens.query.dao.SqlUtil;
public class nadhodzhennya_po_galuzyamScriptlet extends JRDefaultScriptlet {
private static final String F_CHILDREN = "CHILDREN";
private static final String F_CODE = "CODE";
private static final String F_NAME = "NAME";
private static final String F_OPLATA = "OPLATA";
private Collection<Map<String, ?>> data;
private Map<Integer, Map<String, Object>> code2Node;
private Integer pcKassa;
private Integer brInkassa;
@Override
public void beforeReportInit() throws JRScriptletException {
super.beforeReportInit();
loadData();
}
private void loadData() throws JRScriptletException {
data = new ArrayList<Map<String, ?>>();
code2Node = new HashMap<Integer, Map<String, Object>>();
String sql = SqlUtil.loadSQL(getClass(), "getDataByCG.sql");
PreparedStatement st = null;
ResultSet rs = null;
Connection conn = (Connection) getParameterValue("REPORT_CONNECTION");
Integer renCode = (Integer) getParameterValue("renCode");
Date startDate = (Date) getParameterValue("dateBegin");
Date endDate = (Date) getParameterValue("dateEnd");
String drSet = (String) getParameterValue("drSet");
String confData = (String) getParameterValue("REPORT_CONFIG_PARAM");
try {
pcKassa = new Integer(AnalysisProcessorImpl.getConfParam(confData, "channelKassa", "-1"));
brInkassa = new Integer(AnalysisProcessorImpl.getConfParam(confData, "brInkassa", "-1"));
} catch (Exception e) {
pcKassa = -1;
brInkassa = -1;
}
Integer[] virtCustomer = getVirtCustomerCG(conn);
setVariableValue("virtCustomerCode", virtCustomer[0]);
Integer virtCustCG = virtCustomer[1];
try {
sql = SqlUtil.parse(sql, new String[] { "?", "?", "?", drSet, "?",
"?", "?", "?", "?", "?", "?", drSet });
st = conn.prepareStatement(sql);
st.setInt(1, renCode);
st.setDate(2, new java.sql.Date(startDate.getTime()));
st.setDate(3, new java.sql.Date(endDate.getTime()));
st.setDate(4, new java.sql.Date(startDate.getTime()));
st.setDate(5, new java.sql.Date(endDate.getTime()));
st.setInt(6, pcKassa);
st.setDate(7, new java.sql.Date(startDate.getTime()));
st.setDate(8, new java.sql.Date(endDate.getTime()));
st.setInt(9, brInkassa);
st.setInt(10, virtCustCG);
rs = st.executeQuery();
while (rs.next()) {
int code = rs.getInt(1);
String name = rs.getString(2);
int parent = rs.getInt(3);
if (rs.wasNull()) {
parent = Integer.MIN_VALUE;
}
BigDecimal oplata = rs.getBigDecimal(4);
Collection<Map<String, ?>> parentChildren = null;
if (parent == Integer.MIN_VALUE) {
parentChildren = data;
} else {
Map<String, Object> parentNode = getNode(parent);
parentChildren = (Collection<Map<String, ?>>) parentNode
.get(F_CHILDREN);
}
Map<String, Object> newNode = getNode(code);
newNode.put(F_NAME, name);
newNode.put(F_OPLATA, oplata);
parentChildren.add(newNode);
}
cleanTree(data);
} catch (SQLException e) {
throw new JRScriptletException(e.getMessage(), e);
} finally {
SqlUtil.close(rs);
SqlUtil.close(st);
}
}
private void cleanTree(Collection<Map<String, ?>> parentChildren) {
List<Map<String, ?>> nodes2remove = new ArrayList<Map<String, ?>>();
for (Iterator<Map<String, ?>> iterator = parentChildren.iterator(); iterator
.hasNext();) {
Map<String, ?> node = iterator.next();
Collection<Map<String, ?>> children = (Collection<Map<String, ?>>) node
.get(F_CHILDREN);
if (children != null && children.size() > 0) {
cleanTree(children);
}
BigDecimal oplata = (BigDecimal) node.get(F_OPLATA);
if (children.size() == 0
&& (oplata == null || oplata.signum() == 0)) {
nodes2remove.add(node);
}
}
parentChildren.removeAll(nodes2remove);
}
private Map<String, Object> getNode(int code) {
Map<String, Object> node = code2Node.get(code);
if (node == null) {
node = new HashMap<String, Object>();
node.put(F_CODE, code);
node.put(F_CHILDREN, new ArrayList<Map<String, Object>>());
code2Node.put(code, node);
}
return node;
}
public JRDataSource getDS() {
return new JRMapCollectionDataSource(data);
}
public static Integer[] getVirtCustomerCG(Connection conn)
throws JRScriptletException {
String sql = "select cs.code, cs.CONSUMPTIONGRPCD from config c inner join epcustomer cs on cs.ACCOUNTNUMBER = substr(value, 1, posstr(value, '|') - 1) where c.NAME = 'Energypro.BankExtraction.VirtualCustomer' and cs.ISLASTSTATE = 1";
PreparedStatement st = null;
ResultSet rs = null;
try {
st = conn.prepareStatement(sql);
rs = st.executeQuery();
if (rs.next()) {
return new Integer[] { rs.getInt(1), rs.getInt(2) };
}
return null;
} catch (SQLException e) {
throw new JRScriptletException(e.getMessage(), e);
} finally {
try {
rs.close();
} catch (SQLException e) {
}
try {
st.close();
} catch (SQLException e) {
}
}
}
}