# Collection of code snippets by Arne Vajhøj 
# (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) 
from java.io import FileOutputStream
from java.util import ArrayList

from javax.persistence import Persistence

from org.apache.poi.ss.usermodel import IndexedColors, FillPatternType
from org.apache.poi.ss.util import CellRangeAddress
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.xddf.usermodel.chart import XDDFDataSourcesFactory, LegendPosition, ChartTypes

import AutoMap
import OrdersJPA
import OrderLinesJPA
import Orders
import OrderLines

# read from database
emf = Persistence.createEntityManagerFactory('orders')
em = emf.createEntityManager()
jpadata = em.createQuery('SELECT DISTINCT o FROM OrdersJPA AS o JOIN FETCH o.orderLines ol', OrdersJPA).getResultList()
em.close()
emf.close()
# convert
mo = AutoMap(OrdersJPA, Orders, True)
mol = AutoMap(OrderLinesJPA, OrderLines, True)
data = ArrayList()
for ojpa in jpadata:
    o = Orders()
    mo.convert(ojpa, o)
    for oljpa in ojpa.orderLines:
        ol = OrderLines()
        mol.convert(oljpa, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to Excel
def createCell(rowobj, colix, style):
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c

workbook = XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat('#,##0.00'))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue('Order')
createCell(headerrow, 1, headerstyle).setCellValue('Customer')
createCell(headerrow, 2, headerstyle).setCellValue('Status')
createCell(headerrow, 3, headerstyle).setCellValue('Total')
row = row + 1
for o in data:
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice.doubleValue())
    row = row + 1
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue('Total')
createCell(datarow, 3, amountstyle).setCellFormula('SUM(D%d:D%d)' % (row - data.size() + 1, row))
row = row + 1
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, None, None)
piedata.setVaryColors(True)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = FileOutputStream('orders2.xlsx')
workbook.write(os)
os.close()
workbook.close()
#
print('db2excel done')