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');

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

ds = SELF.getAliases().get(GLOBAL.get('DATASTORE_NAME'));

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



wb = new HSSFWorkbook();

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


font_header = wb.createFont(); font_header.setFontHeightInPoints(16);
style1 = wb.createCellStyle(); style1.setFont(font_header);

font = wb.createFont(); font.setFontHeightInPoints(12); font.setFontName("Arial");
style_header = wb.createCellStyle(); style_header.setFont(font); style_header.setVerticalAlignment(3);

font = wb.createFont(); font.setFontHeightInPoints(13); font.setFontName("Consolas");
style_bold = wb.createCellStyle(); style_bold.setFont(font);


logger.debug('style_header.DISTRIBUTED: ' + style_header.DISTRIBUTED);


row = sheet.createRow(0);
row.setHeightInPoints(20);

cell = row.createCell(0);
cell.setCellValue("Потребность в материалах для производства на период с ... по ...");
cell.setCellStyle(style1);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,10));
sheet.createFreezePane(0, 2); 

row = sheet.createRow(1);
row.setHeightInPoints(30);
cell = row.createCell(0); cell.setCellValue("Продукция"); cell.setCellStyle(style_header); sheet.setColumnWidth(0, 8000);
cell = row.createCell(1); cell.setCellValue("Кол-во для производства"); cell.setCellStyle(style_header); sheet.setColumnWidth(1, 4000);
cell = row.createCell(2); cell.setCellValue("Материал"); cell.setCellStyle(style_header); sheet.setColumnWidth(2, 10000);
cell = row.createCell(3); cell.setCellValue("Кол-во НН"); cell.setCellStyle(style_header); sheet.setColumnWidth(3, 3000);
cell = row.createCell(4); cell.setCellValue("Кол-во Семенов"); cell.setCellStyle(style_header);  sheet.setColumnWidth(4, 3000);
cell = row.createCell(5); cell.setCellValue("Остаток \nНН"); cell.setCellStyle(style_header);  sheet.setColumnWidth(5, 3000);
cell = row.createCell(6); cell.setCellValue("Остаток \nСеменов"); cell.setCellStyle(style_header); sheet.setColumnWidth(6, 3000);
cell = row.createCell(7); cell.setCellValue("Дефицит"); cell.setCellStyle(style_header);  sheet.setColumnWidth(7, 3000);
cell = row.createCell(8); cell.setCellValue("Перемещено в производство"); cell.setCellStyle(style_header);  sheet.setColumnWidth(8, 3000);

previous_material = '---';
group_header = 0;

for (i = 0; i < ds.getRowCount(); i ++)
{

   if (previous_material != ds.getValue(i, 'a.name_active_material'))
     {
      i ++;
      previous_material = ds.getValue(i, 'a.name_active_material');
      group_header = 1;
     }
   row = sheet.createRow(3 + i);
   row.setHeightInPoints(15);
   
   cell = row.createCell(0);
   if (ds.getValue(i, 'b.name_active_prod') != null) cell.setCellValue(ds.getValue(i, 'b.name_active_prod'));
   cell = row.createCell(3);
   if (ds.getValue(i, 'b.q_proiz_nn') != null) cell.setCellValue(ds.getValue(i, 'b.q_proiz_nn'));
   cell = row.createCell(4);
   if (ds.getValue(i, 'b.q_proiz_semenov') != null) cell.setCellValue(ds.getValue(i, 'b.q_proiz_semenov'));
   
   if (group_header == 1)
     {
      cell = row.createCell(1);
      if (ds.getValue(i, 'a.q_proiz') != null) cell.setCellValue(ds.getValue(i, 'a.q_proiz')); cell.setCellStyle(style_bold);
      cell = row.createCell(2);
      if (ds.getValue(i, 'a.name_active_material') != null) cell.setCellValue(ds.getValue(i, 'a.name_active_material')); cell.setCellStyle(style_bold);
      cell = row.createCell(5);
      if (ds.getValue(i, 'a.ost_nn') != null) cell.setCellValue(ds.getValue(i, 'a.ost_nn')); cell.setCellStyle(style_bold);
      cell = row.createCell(6);
      if (ds.getValue(i, 'a.ost_semenov') != null) cell.setCellValue(ds.getValue(i, 'a.ost_semenov')); cell.setCellStyle(style_bold);
      cell = row.createCell(7);
      if (ds.getValue(i, 'defcit') != null) cell.setCellValue(ds.getValue(i, 'defcit')); cell.setCellStyle(style_bold);
      cell = row.createCell(8);
      if (ds.getValue(i, 'a.perem') != null) cell.setCellValue(ds.getValue(i, 'a.perem')); cell.setCellStyle(style_bold);
      group_header = 0;
     }
    
}

// формируем имя файла
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();
  java.awt.Desktop.getDesktop().open(f);
}
catch (e)
{
  logger.debug('Error creating file: ' + e);
}

// если сразу удалять файл, когда Excel запустится, он его может не найти (зависит от скорости компьютера)
//logger.debug('=== deleting file ' + f);
//f['delete']();
//logger.debug('=== deleted');


ret = '';
