CP1251

var version = java.lang.System.getProperty("java.version");
if (version.startsWith("1.8.0")) {
    load("nashorn:mozilla_compat.js");
}

importPackage(java.io);
importPackage(org.apache.poi.hssf.usermodel);
importPackage(org.apache.poi.hssf.util);
importPackage(org.apache.log4j);

logger = LogManager.getLogger('Logger');

// имя грида передаем через глобальную переменную

grid = SELF.getAliases().get(GLOBAL.get('GRID_NAME'));

if (grid == null)
{
  logger.debug('Can\'t find grid ' + GLOBAL.get('GRID_NAME'));
  shit.happens();
}

wb = new HSSFWorkbook();

sheet = wb.createSheet('_');
ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight(1);
ps.setFitWidth(1);

// стиль <сверху и снизу толcтая линия>, заполнение ячейки с переносом строки
style1 = wb.createCellStyle();
style1.setBorderBottom(style1.BORDER_THICK);
style1.setBorderLeft(style1.BORDER_THIN);
style1.setBorderRight(style1.BORDER_THIN);
style1.setBorderTop(style1.BORDER_THICK);
style1.setVerticalAlignment(style1.VERTICAL_CENTER);
style1.setAlignment(style1.ALIGN_JUSTIFY);

// стиль <сверху и снизу толcтая линия>, выравнивание по центру
style11 = wb.createCellStyle();
style11.setBorderBottom(style1.BORDER_THICK);
style11.setBorderLeft(style1.BORDER_THIN);
style11.setBorderRight(style1.BORDER_THIN);
style11.setBorderTop(style1.BORDER_THICK);
style11.setVerticalAlignment(style1.VERTICAL_CENTER);
style11.setAlignment(style1.ALIGN_CENTER);

// стиль <тонкие линии>, выравнивание влево
style2 = wb.createCellStyle();
style2.setBorderBottom(style2.BORDER_THIN);
style2.setBorderLeft(style2.BORDER_THIN);
style2.setBorderRight(style2.BORDER_THIN);
style2.setBorderTop(style2.BORDER_THIN);
style2.setVerticalAlignment(style2.VERTICAL_CENTER);
style2.setAlignment(style2.ALIGN_LEFT);

// стиль <тонкие линии>, выравнивание вправо
style3 = wb.createCellStyle();
style3.setBorderBottom(style3.BORDER_THIN);
style3.setBorderLeft(style3.BORDER_THIN);
style3.setBorderRight(style3.BORDER_THIN);
style3.setBorderTop(style3.BORDER_THIN);
style3.setVerticalAlignment(style3.VERTICAL_CENTER);
style3.setAlignment(style3.ALIGN_RIGHT);

// стиль <тонкие линии>, выравнивание вправо, красный цвет фона
style3_red = wb.createCellStyle();
style3_red.setBorderBottom(style3.BORDER_THIN);
style3_red.setBorderLeft(style3.BORDER_THIN);
style3_red.setBorderRight(style3.BORDER_THIN);
style3_red.setBorderTop(style3.BORDER_THIN);
style3_red.setVerticalAlignment(style3.VERTICAL_CENTER);
style3_red.setAlignment(style3.ALIGN_RIGHT);
style3_red.setFillForegroundColor(org.apache.poi.ss.usermodel.IndexedColors.RED.getIndex());
style3_red.setFillPattern(style3.SOLID_FOREGROUND);

row = sheet.createRow(0);
row.setHeightInPoints(40);
row2 = sheet.createRow(1);
row2.setHeightInPoints(20);

// заголовки

// это одинаково для всех месяцев

cell = row.createCell(0);
cell.setCellValue('ID');
cell.setCellStyle(style1);
cell = row.createCell(1);
cell.setCellValue('Название');
cell.setCellStyle(style1);

// дальше кол-во столбцов = кол-ву месяцев за выбранный период * 3

df = java.text.SimpleDateFormat("MMMM yyyy");
cal1 = java.util.Calendar.getInstance();
cal1.setTime(SELF.getAliases().get(GLOBAL.get('DAT1')).getValue());
cal2 = java.util.Calendar.getInstance();
cal2.setTime(SELF.getAliases().get(GLOBAL.get('DAT1')).getValue());
// logger.debug('Date_field.getValue() type: ' + DAT1.getValue().getClass());  // java.sql.Timestamp
stamp = DAT1.getValue();
d = new java.util.Date(stamp.getTime());
start_month = df.format(d);

for (i = 0, months = 0; cal2.after(cal1); i ++, months ++)
{
  logger.debug('Month ' + (i + 1) + ': ' + df.format(d));
  cell = row.createCell(2 + i*3);
  cell.setCellValue(df.format(d));
  cell.setCellStyle(style11);
  cell = row.createCell(2 + i*3 + 1);
  cell.setCellStyle(style11);
  cell = row.createCell(2 + i*3 + 2);
  cell.setCellStyle(style11);

  // объединение ячеек
  sheet.addMergedRegion(new CellRangeAddress(
              0, //first row (0-based)
              0, //last row  (0-based)
              2 + i*3, //first column (0-based)
              2 + i*3 + 2  //last column  (0-based)
  ));


  cell = row2.createCell(2 + i*3);
  cell.setCellValue('Заказано');
  cell.setCellStyle(style2);

  cell = row2.createCell(2 + i*3 + 1);
  cell.setCellValue('Поставлено');
  cell.setCellStyle(style2);

  cell = row2.createCell(2 + i*3 + 2);
  cell.setCellValue('Разница');
  cell.setCellStyle(style2);

  cal1.add(java.util.Calendar.MONTH, 1);
  d = cal1.getTime();
}

// имя листа

cal1.add(java.util.Calendar.MONTH, -1);
d = cal1.getTime();
end_month = df.format(d);

if (months > 1)
  wb.setSheetName(0, start_month + ' - ' + end_month);
else
  wb.setSheetName(0, 'Заказы за ' + start_month);


// передаем в базу список ID_ACTIVE

sql = 'begin\n';
for (i = 0; i < grid.getRowCount(); i ++) sql += '\ninsert into par values (' + grid.getValue(i, 2).intValue() +');';
sql += '\n? := \'OK\';\nend;\n';

logger.debug('\n------------------------ SQL --------------------\n' + sql + '\n------------------------ end of SQL -------------');

DBE.setQuery(sql);
DBE.execute();

// формируем запрос 
sql = 'select id_active, name_active';
df = java.text.SimpleDateFormat("YYYY_MM");
dfd = java.text.SimpleDateFormat("YYYY.MM.d");

cal1.setTime(DAT1.getValue());
for (i = 0; i < months; i ++)
{
  d = cal1.getTime();
  sql += ', sum(qz_' + df.format(d) + ') qz_' + df.format(d);
  sql += ', sum(qp_' + df.format(d) + ') qp_' + df.format(d);
  sql += ', sum(qp_' + df.format(d) + ') - sum(qz_' + df.format(d) + ') diff_' + df.format(d);
  cal1.add(java.util.Calendar.MONTH, 1);
}

sql += '\n  from (\n';
sql += ' select active.id_active, active.name_active';

cal1.setTime(DAT1.getValue());
for (i = 0; i < months; i ++)
{
  d = cal1.getTime();
  sql += '\n , case when head_zakaz.date_sd between to_date(\'' + dfd.format(d) + '\', \'YYYY.MM.DD\')';
  sql += '\n                              and last_day(to_date(\'' + dfd.format(d) + '\', \'YYYY.MM.DD\'))';
  sql += '\n       then spc_zakaz.q1_spc ';
  sql += '\n       else 0          ';
  sql += '\n     end qz_' + df.format(d);
  sql += '\n , case when head_post.date_sd between to_date(\'' + dfd.format(d) + '\', \'YYYY.MM.DD\')';
  sql += '\n                              and last_day(to_date(\'' + dfd.format(d) + '\', \'YYYY.MM.DD\'))';
  sql += '\n       then spc_post.q1_spc ';
  sql += '\n       else 0          ';
  sql += '\n     end qp_' + df.format(d);
  cal1.add(java.util.Calendar.MONTH, 1);
}
sql += '\n';
sql += '\n  from active, head head_zakaz, spc spc_zakaz, head head_post, spc spc_post';
sql += '\n where active.id_active in (select id from par)';
sql += '\n   and active.id_active = head_zakaz.id_active';
sql += '\n   and head_zakaz.id_head = spc_zakaz.id_head';
sql += '\n   and head_zakaz.id_type_doc = 9';
//sql += '\n   and head.accept = 1';
sql += '\n   and head_zakaz.date_sd between to_date (\'' + N_DAT1.getValue() + '\', \'dd.mm.yyyy\') and to_date(\'' + N_DAT2.getValue() + '\', \'dd.mm.yyyy\') ';
sql += '\n   and active.id_active = spc_post.id_active';
sql += '\n   and head_post.id_head = spc_post.id_head';
sql += '\n   and head_post.id_type_doc = 78';
sql += '\n   and head_post.accept = 1';
sql += '\n   and head_post.date_sd between to_date (\'' + N_DAT1.getValue() + '\', \'dd.mm.yyyy\') and to_date(\'' + N_DAT2.getValue() + '\', \'dd.mm.yyyy\') ';

//--------------------- кусок на случай отсутствия в документах этих активов -------------
sql += '\n union';
sql += '\n select id_active, name_active';
cal1.setTime(DAT1.getValue());
for (i = 0; i < months; i ++)
{
  d = cal1.getTime();
  sql += '\n ,  0 qz_' + df.format(d);
  sql += '\n ,  0 qp_' + df.format(d);
  cal1.add(java.util.Calendar.MONTH, 1);
}
sql += '\n from active where id_active in (select id from par)';
//--------------------- кусок на случай отсутствия в документах этих активов -------------


sql += '\n)\n group by id_active, name_active';
sql += '\n order by name_active';

logger.debug('\n------------------------ SQL --------------------\n' + sql + '\n------------------------ end of SQL -------------');

// делаем запрос

ZZZ.setSql(sql);
ZZZ.retrieve();

logger.debug('ZZZ has retrieved ' + ZZZ.getRowCount() + ' rows');


// переносим строки отчета в Excel


for (i = 0; i < ZZZ.getRowCount(); i ++)
{
   ZZZ.setCurrentRow(i);
   row = sheet.createRow(2 + i);
   row.setHeightInPoints(15);
   cell = row.createCell(0);
   cell.setCellValue(ZZZ.getValue('id_active').intValue());
   cell.setCellStyle(style2);
   cell = row.createCell(1);
   cell.setCellValue(ZZZ.getValue('name_active'));
   cell.setCellStyle(style2);

   cal1.setTime(DAT1.getValue());

   for (j = 0; j < months; j ++)
   {
     d = cal1.getTime();
     cell = row.createCell(2 + j*3);
     cell.setCellValue(ZZZ.getValue('qz_' + df.format(d)).doubleValue());
     cell.setCellStyle(style3);
     cell = row.createCell(2 + j*3 + 1);
     cell.setCellValue(ZZZ.getValue('qp_' + df.format(d)).doubleValue());
     cell.setCellStyle(style3);
     cell = row.createCell(2 + j*3 + 2);
     cell.setCellValue(ZZZ.getValue('diff_' + df.format(d)).doubleValue());
     logger.debug('diff: ' + ZZZ.getValue('diff_' + df.format(d)).doubleValue());
     if (ZZZ.getValue('diff_' + df.format(d)).doubleValue() < 0)
       {
         logger.debug('set RED');
         cell.setCellStyle(style3_red);
       }
     else
       {
         logger.debug('set not RED');
         cell.setCellStyle(style3);
       }
     cal1.add(java.util.Calendar.MONTH, 1);
   }

}


// автоподгонка ширины столбцов

for (i = 0; i < months + 2; i ++) 
{
 sheet.autoSizeColumn(i*3);
 sheet.autoSizeColumn(i*3 + 1);
 sheet.autoSizeColumn(i*3 + 2);
}

// Установка области печати

wb.setPrintArea(0, 0, grid.getVisibleColumnCount() - 1, 0, grid.getRowCount());

// формируем имя файла

str = java.text.DateFormat.getDateTimeInstance().format(new java.util.Date);
str = str.replaceAll(':', '_');
str = str.replaceAll(' ', '_');
f = new File(java.lang.System.getProperty('java.io.tmpdir') + 'tmp_' + str + '.xls');

// записываем файл
try
{
  logger.debug('creating file ' + f.getAbsoluteFile());
  fileOut = new FileOutputStream(f.getAbsoluteFile());
  wb.write(fileOut);
  fileOut.close();
  //logger.debug('TO_PRINT_OR_NOT_TO_PRINT: ' + TO_PRINT_OR_NOT_TO_PRINT_HASH);
  //    if  (TO_PRINT_OR_NOT_TO_PRINT_HASH == 'print')
  //  	java.awt.Desktop.getDesktop().print(f);
  //else
    	java.awt.Desktop.getDesktop().open(f);
}
catch (e)
{
  logger.debug('Error creating file: ' + e);
}
f['delete']();


ret = '';