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

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

try { ds = SELF.getAliases().get(GLOBAL.get('DATASTORE_NAME')); } catch(e) {};
date1 = GLOBAL.get('DATE1');
date2 = GLOBAL.get('DATE2');


logger.debug('DS: ' + GLOBAL.get('DATASTORE_NAME'));
logger.debug('DATE1: ' + date1);
logger.debug('DATE2: ' + date2);

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("Потребность в материалах для производства на период с " + date1 + " по " + date2);
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, 9000);
cell = row.createCell(1); cell.setCellValue("Кол-во для производства"); cell.setCellStyle(style_header); sheet.setColumnWidth(1, 4000);
cell = row.createCell(2); cell.setCellValue("Н.Новгород"); sheet.setColumnWidth(2, 2500);
cell = row.createCell(3); cell.setCellValue("Семенов"); sheet.setColumnWidth(3, 2500);
cell = row.createCell(4); cell.setCellValue("Материал"); cell.setCellStyle(style_header); sheet.setColumnWidth(4, 10000);
cell = row.createCell(5); cell.setCellValue("Остаток Семенов"); cell.setCellStyle(style_header); sheet.setColumnWidth(5, 4000);
cell = row.createCell(6); cell.setCellValue("Дефицит"); cell.setCellStyle(style_header); sheet.setColumnWidth(6, 4000);
cell = row.createCell(7); cell.setCellValue("Перемещено в пр-во"); cell.setCellStyle(style_header); sheet.setColumnWidth(7, 4000);

previous_mat_name = '---999---';
group_header = 0;
log_debug = 0;

logger.debug('ds size: ' + ds.getRowCount());

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

   if (previous_mat_name != ds.getValue(i, 'mat_name'))
     {
      previous_mat_name = ds.getValue(i, 'mat_name');
      group_header = 1;
     }

   row = sheet.createRow(3 + i);
   row.setHeightInPoints(15);

   //logger.debug('row:  ' + i + ' q2: ' + ds.getValue(i, 'q2') + ', q4: ' + ds.getValue(i, 'q4'));
   
   if (group_header == 1)
     {
       cell = row.createCell(1);
       if (ds.getValue(i, 'q3') != null) cell.setCellValue(ds.getValue(i, 'q3'));
       if (ds.getValue(i, 'mat_name') != null) cell.setCellStyle(style_bold);

       
       cell = row.createCell(2);
       if (ds.getValue(i, 'q1') != null) { cell.setCellValue(ds.getValue(i, 'q1')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(3);
       if (ds.getValue(i, 'q2') != null) { cell.setCellValue(ds.getValue(i, 'q2')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(4);
       if (ds.getValue(i, 'mat_name') != null) cell.setCellValue(ds.getValue(i, 'mat_name'));
       cell = row.createCell(5);
       if (ds.getValue(i, 'q4') != null) { cell.setCellValue(ds.getValue(i, 'q4')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(6);
       if (ds.getValue(i, 'q5') != null) { cell.setCellValue(ds.getValue(i, 'q5')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(7);
       if (ds.getValue(i, 'q6') != null) { cell.setCellValue(ds.getValue(i, 'q6')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       group_header = 0;
     }
   else
     {
       cell = row.createCell(0);
       if (ds.getValue(i, 'prod_name') != null) cell.setCellValue(ds.getValue(i, 'prod_name'));
       cell = row.createCell(2);
       if (ds.getValue(i, 'q1') != null) cell.setCellValue(ds.getValue(i, 'q1'));
       cell = row.createCell(3);
       if (ds.getValue(i, 'q2') != null) cell.setCellValue(ds.getValue(i, 'q2'));
       cell = row.createCell(5);
       if (ds.getValue(i, 'q4') != null) { cell.setCellValue(ds.getValue(i, 'q4')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(6);
       if (ds.getValue(i, 'q5') != null) { cell.setCellValue(ds.getValue(i, 'q5')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
       cell = row.createCell(7);
       if (ds.getValue(i, 'q6') != null) { cell.setCellValue(ds.getValue(i, 'q6')); /*cell.setCellType(cell.CELL_TYPE_NUMERIC);*/ }
      }
    
}

// формируем имя файла
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 = '';
