public void _01_125_482_农产品类(String STAT_YEAR, String STAT_MONTH) throws Exception {
List<Map<String, Object>> list = findByIndexName1(0, STAT_YEAR + STAT_MONTH);
for (Map<String, Object> map : list) {
if (map != null) {
int row =
jdbc.update(
"update "
+ tableName
+ " set NMZYHZS_NCP_BNLJ=?,NMZYHZS_NCP_BYLJ=?,NMZYHZS_NCP_SNTQ=?,NMZYHZS_NCP_YOY=?,NMZYHZS_NCP_MOM=? where ENT_CODE_DISTRICT_MAP_ID=? and STAT_MONTH=?",
new Object[] {
StringUitl.obj2doule(map.get("本年_TJ")),
StringUitl.obj2doule(map.get("本月_TJ")),
StringUitl.obj2doule(map.get("去年_TJ")),
StringUitl.obj2doule(map.get("同比")),
StringUitl.obj2doule(map.get("环比")),
map.get("ENT_CODE_DISTRICT_MAP_ID"),
STAT_YEAR + STAT_MONTH
});
System.err.println(
"【"
+ STAT_YEAR
+ STAT_MONTH
+ "】【"
+ map.get("ENT_CODE_DISTRICT_MAP_ID")
+ "】【"
+ row
+ "】");
}
}
}
@Override
protected Workbook indexExcel(Map<String, String> params, Workbook excel) {
List<Map<String, Object>> list = service.financialIndexData_02(params);
Sheet sheet = excel.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellValue(params.get("statMonth") + reportName);
row = sheet.getRow(2);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
sheet = PoiUtil.copyLastRow(sheet, false);
row = sheet.getRow(sheet.getLastRowNum() - 1);
row.getCell(0).setCellValue(map.get("ENT_NAME").toString());
row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1")));
row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1")));
row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2")));
row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2")));
row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3")));
row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3")));
}
}
sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
return excel;
}
@Override
protected Table indexPdfDoc(
Map<String, String> params, Font titleFont, Font minFont, Font haderFont, Font contextFont)
throws DocumentException {
List<Map<String, Object>> list = service.financialIndexData_02(params);
Table table =
ITextUtil.loadTable(
7, new int[] {16, 14, 14, 14, 14, 14, 14}, 95, Element.ALIGN_CENTER, true, 1);
// 设置表头
table.addCell(
ITextUtil.loadHeaderCell(
params.get("statMonth") + getReportName(),
titleFont,
7,
1,
new float[] {0, 0, 0, 0},
null,
null));
table.addCell(
ITextUtil.loadCell(
"表号：002 \n单位:万元 ",
minFont,
true,
Element.ALIGN_RIGHT,
Element.ALIGN_MIDDLE,
new float[] {0, 0, 0, 0},
null,
null,
7,
1));
table.addCell(ITextUtil.loadHeaderCell("地区", haderFont, 1, 2));
table.addCell(ITextUtil.loadHeaderCell("利润总额", haderFont, 2, 1));
table.addCell(ITextUtil.loadHeaderCell("资产总额", haderFont, 2, 1));
table.addCell(ITextUtil.loadHeaderCell("所有者权益", haderFont, 2, 1));
table.addCell(ITextUtil.loadHeaderCell("本期", haderFont, 1, 1));
table.addCell(ITextUtil.loadHeaderCell("同比(%)", haderFont, 1, 1));
table.addCell(ITextUtil.loadHeaderCell("本期", haderFont, 1, 1));
table.addCell(ITextUtil.loadHeaderCell("同比(%)", haderFont, 1, 1));
table.addCell(ITextUtil.loadHeaderCell("本期", haderFont, 1, 1));
table.addCell(ITextUtil.loadHeaderCell("同比(%)", haderFont, 1, 1));
table.endHeaders();
// 计算
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
table.addCell(ITextUtil.loadContextCell(map.get("ENT_NAME").toString(), contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TJ1"), 2) + "", contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TB1"), 2) + "", contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TJ2"), 2) + "", contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TB2"), 2) + "", contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TJ3"), 2) + "", contextFont));
table.addCell(
ITextUtil.loadContextCell(StringUitl.obj2doule(map.get("TB3"), 2) + "", contextFont));
}
}
return table;
}
private List<Map<String, Object>> findByIndexName2(String STAT_MONTH) {
int[] indexids = null;
if (StringUitl.string2Int(STAT_MONTH) / 100 >= 2016) {
indexids = index_2016;
} else {
indexids = index_2015;
}
String indesIdSql = "";
for (int i = 0; i < indexids.length; i++) {
indesIdSql += indexids[i] + ((i == (indexids.length - 1)) ? "" : ",");
}
StringBuffer sbSql = new StringBuffer();
sbSql.append("select x.ENT_CODE_DISTRICT_MAP_ID,x.TJ_SYS_ENT_CODE,x.REGION_NAME, \n");
sbSql.append(
" ifnull(本月_TJ,0) 本月_TJ,ifnull(本年_TJ,0) 本年_TJ,ifnull(去年_TJ,0) 去年_TJ,ifnull(上月_TJ,0) 上月_TJ, \n");
sbSql.append(
" ifnull(((Y.本年_TJ-Y.去年_TJ)/Y.去年_TJ*100),0) 同比,ifnull(((Y.本月_TJ-Y.上月_TJ)/Y.上月_TJ*100),0) 环比 from ( \n");
sbSql.append(
" select a.ENT_BASEINFO_ID ,b.ENT_CODE_DISTRICT_MAP_ID,b.TJ_SYS_ENT_CODE,b.REGION_NAME from ( \n");
sbSql.append(" SELECT t.ENT_BASEINFO_ID,t.REGION_NAME,t.ENT_NAME_CN,t.COMPANY_CODE, \n");
sbSql.append(
" (case when ENT_NAME_CN='广东' then 1 when ENT_NAME_CN='广东地市社汇总' then 2 when ENT_NAME_CN='广东省直属企业' then 3 else 9999 end) REGION_NO \n");
sbSql.append(" FROM ims_ent_baseinfo t \n");
sbSql.append(
" where t.COMPANY_CODE in ('044','044200') or t.COMPANY_CODE like '0440__' or t.RPT_COMPANY_CODE='1910000000' \n");
sbSql.append(" )a left join ( \n");
sbSql.append(" select * from ENT_CODE_DISTRICT_MAP \n");
sbSql.append(" )b on a.COMPANY_CODE=b.TJ_SYS_ENT_CODE \n");
sbSql.append(")x left join ( \n");
sbSql.append(
" select ENT_BASEINFO_ID,sum(本月_TJ)本月_TJ,sum(本年_TJ)本年_TJ,sum(去年_TJ)去年_TJ,sum(上月_TJ)上月_TJ from ( \n");
sbSql.append(
" SELECT T.ENT_BASEINFO_ID, T.CURR_MONTH_FACT 本月_TJ, T.TILL_CURR_MONTH_TOTAL 本年_TJ, \n");
sbSql.append(
" IFNULL((SELECT A.TILL_CURR_MONTH_TOTAL FROM ENT_BASICINFO_STAT_ZYHZS A WHERE A.ENT_BASEINFO_ID=T.ENT_BASEINFO_ID AND A.STAT_MONTH=(T.STAT_MONTH-100) AND A.IND_ID=T.IND_ID),0) 去年_TJ, \n");
sbSql.append(
" IFNULL((SELECT A.TILL_CURR_MONTH_TOTAL FROM ENT_BASICINFO_STAT_ZYHZS A WHERE A.ENT_BASEINFO_ID=T.ENT_BASEINFO_ID AND A.STAT_MONTH=DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(CAST(T.STAT_MONTH AS CHAR),'01'),'%Y%m%d'),INTERVAL 1 MONTH),'%Y%m') AND A.IND_ID=T.IND_ID),0) 上月_TJ \n");
sbSql.append(" FROM ENT_BASICINFO_STAT_ZYHZS T \n");
sbSql.append(" WHERE T.ENT_BASEINFO_ID IN( \n");
sbSql.append(" SELECT t.ENT_BASEINFO_ID FROM ims_ent_baseinfo t \n");
sbSql.append(
" where t.COMPANY_CODE in ('044','044200') or t.COMPANY_CODE like '0440__' or t.RPT_COMPANY_CODE='1910000000' \n");
sbSql.append(" ) \n");
sbSql.append(" AND T.IND_ID in (");
sbSql.append(indesIdSql);
sbSql.append(") and t.STAT_MONTH=? \n");
sbSql.append(" )a group by ENT_BASEINFO_ID \n");
sbSql.append(")y on x.ENT_BASEINFO_ID=y.ENT_BASEINFO_ID");
List<Map<String, Object>> list = jdbc.queryForList(sbSql.toString(), new Object[] {STAT_MONTH});
Map<String, Object> map_044900 = null; // 广东省地市社
Map<String, Object> map = null;
Iterator<Map<String, Object>> it = list.iterator();
String TJ_SYS_ENT_CODE = "";
double 地市社_本年_TJ = 0, 地市社_本月_TJ = 0, 地市社_上月_TJ = 0, 地市社_去年_TJ = 0, 地市社_同比 = 0, 地市社_环比 = 0;
while (it.hasNext()) {
map = it.next();
TJ_SYS_ENT_CODE = StringUitl.obj2Str(map.get("TJ_SYS_ENT_CODE"));
if (map.get("TJ_SYS_ENT_CODE").equals("044900")) {
map_044900 = map;
it.remove();
}
if (TJ_SYS_ENT_CODE.startsWith("0440") && TJ_SYS_ENT_CODE.length() == 6) {
地市社_本年_TJ += StringUitl.mapKey2doule(map, "本年_TJ");
地市社_本月_TJ += StringUitl.mapKey2doule(map, "本月_TJ");
地市社_上月_TJ += StringUitl.mapKey2doule(map, "上月_TJ");
地市社_去年_TJ += StringUitl.mapKey2doule(map, "去年_TJ");
}
}
map_044900.put("本月_TJ", new BigDecimal(地市社_本月_TJ).setScale(2, BigDecimal.ROUND_HALF_UP));
map_044900.put("本年_TJ", new BigDecimal(地市社_本年_TJ).setScale(2, BigDecimal.ROUND_HALF_UP));
map_044900.put("去年_TJ", new BigDecimal(地市社_去年_TJ).setScale(2, BigDecimal.ROUND_HALF_UP));
map_044900.put("上月_TJ", new BigDecimal(地市社_上月_TJ).setScale(2, BigDecimal.ROUND_HALF_UP));
if (地市社_去年_TJ != 0) {
地市社_同比 = (地市社_本年_TJ - 地市社_去年_TJ) / 地市社_去年_TJ * 100;
}
map_044900.put("同比", new BigDecimal(地市社_同比).setScale(2, BigDecimal.ROUND_HALF_UP));
if (地市社_上月_TJ != 0) {
地市社_环比 = (地市社_本年_TJ - 地市社_上月_TJ) / 地市社_上月_TJ * 100;
}
map_044900.put("环比", new BigDecimal(地市社_环比).setScale(2, BigDecimal.ROUND_HALF_UP));
if (map_044900 != null && map_044900.size() > 0) list.add(map_044900);
return list;
}