package com.hmkcode

;

import java.io._
import java.nio.charset.Charset
import java.sql.{Connection, DriverManager}

import com.itextpdf.text.pdf.PdfWriter
import com.itextpdf.text.{Document, FontFactory, PageSize}
import com.itextpdf.tool.xml.{XMLWorkerFontProvider, XMLWorkerHelper}
import org.scalatest.FunSuite
import org.slf4j.LoggerFactory
import scalikejdbc.{DB, _}


/**
  * Created by ksenya on 08.02.16.
  */
//noinspection ScalaDeprecation

case class Member(post: Option[String],
                  rate: String,
                  part: Option[Int],
                  discilineName: Option[String],
                  faculty: Option[String],
                  department: Int,
                  cipherGroup: Option[String],
                  disciplineIndex: Option[String],
                  course: String,
                  fo: Option[String],
                  trainingPeriod: Option[String],
                  nameWork: String,
                  contingentVolume: Int,
                  calculationHours: Int,
                  term: Int)

case class Full_name(last_name: Option[String],
                     first_name: Option[String],
                     middle_name: Option[String])

class ReportTest extends FunSuite {
  val logger = LoggerFactory.getLogger (classOf [ReportTest])

  test ("A Scala XML generation test") {
    Class.forName ("oracle.jdbc.driver.OracleDriver")
    val c: Connection = DriverManager.getConnection ("jdbc:oracle:thin:@//[192.168.11.36][1521]/student", "portal_owner", "portal_owner")
    val person_id = 73566
    val study_year = "2015/2016"
    DB (c).readOnly { implicit session =>
      val members =
        sql"""SELECT genericvac1_.title AS post,
              employeety2_.наименования AS rate,
              genericvac1_.part AS part,
              payloaddis4_.discipline AS disciplineName,
              payloaddis4_.faculty AS faculty,
              genericvac1_.DEPARTMENT_ID as department,
              payloaddis4_.group_codes AS cipherGroup,
              payloaddis4_.discipline_index AS disciplineIndex,
              resource3_.course AS course,
              payloaddis4_.study_form AS fo,
              payloaddis4_.study_length AS trainingPeriod,
              tasktype5_.аббревиатура AS nameWork,
              resource3_.term AS term,
              assignment0_.payload AS calculationHours,
              assignment0_.qty AS contingentVolume
              FROM w_assignments assignment0_
              INNER JOIN w_generic_vacancies genericvac1_ ON assignment0_.employee_id=genericvac1_.id
              INNER JOIN и_типы_долж employeety2_ ON genericvac1_.TYPE=employeety2_.наим_сокр
              INNER JOIN W_Resource resource3_ ON assignment0_.resource_id=resource3_.id
              LEFT OUTER JOIN w_payload_discipline payloaddis4_ ON resource3_.payload_dis_id=payloaddis4_.id
              INNER JOIN и_виды_работ tasktype5_ ON resource3_.task_type_id=tasktype5_.ид
              WHERE genericvac1_.id IN (SELECT employee6_.id
              FROM w_generic_vacancies employee6_
              WHERE employee6_.vac_type='employee'
              AND employee6_.person_id= $person_id
              AND employee6_.study_year=$study_year)
              ORDER BY genericvac1_.id, payloaddis4_.discipline, payloaddis4_.study_form, payloaddis4_.study_length,
              resource3_.course, payloaddis4_.group_codes, tasktype5_.аббревиатура"""
          .map { rs => Member (
            rs.stringOpt ("post"),
            rs.string ("rate"),
            rs.intOpt ("part"),
            rs.stringOpt ("disciplineName"),
            rs.stringOpt ("faculty"),
            rs.int ("department"),
            rs.stringOpt ("cipherGroup"),
            rs.stringOpt ("disciplineIndex"),
            rs.string ("course"),
            rs.stringOpt ("fo"),
            rs.stringOpt ("trainingPeriod"),
            rs.string ("nameWork"),
            rs.int ("contingentVolume"),
            rs.int ("calculationHours"),
            rs.int ("term"))
          }.list.apply ()

      val department_group = members.groupBy (group => group.department)

      def sumYear(rows: List[Member]) = rows.map (row => row.calculationHours).sum

      val full_name_person =
        sql"""select LAST_NAME, FIRST_NAME, MIDDLE_NAME
             from IIAS_OWNER.WV_PERSONS
             where id=$person_id"""
          .map { rs => Full_name (
            rs.stringOpt ("LAST_NAME"),
            rs.stringOpt ("FIRST_NAME"),
            rs.stringOpt ("MIDDLE_NAME"))
          }.list.apply ()

      val doc = <html>
      <head>
        <meta charset="utf-8"/>
        <link href="style.css" rel="stylesheet" type="text/css"/>
      </head>
      <body>
        <h3 align="center">УЧЕБНАЯ НАГРУЗКА ПРЕПОДАВАТЕЛЯ НА {study_year} УЧ. ГОД</h3>
        <p>Ф.И.О. преподавателя:
          {
            full_name_person.map { name =>
              s"${name.first_name.getOrElse ("")} ${name.middle_name.getOrElse ("")} ${name.last_name.getOrElse ("")}"
            }
          } </p>
       { department_group.map { case (depart, dat) =>
        <p>КАФЕДРА:
                {sql"""SELECT "НАИМЕНОВАНИЕ"
                       FROM IIAS_OWNER.WV_DEPARTMENTS
                       where "ИД"=$depart"""
          .map { rs => rs.string ("наименование")
          }.list.apply ()}
        </p>
        <table border="1" class="jewel">
          <tr>
            <td>Исполнение должности</td>
            <td>Наименование дисциплины</td>
            <td>Фа-  куль- тет </td>
            <td>Шифр группы </td>
            <td>Индекс дисцип-лины</td>
            <td>Курс  </td>
            <td>Ф.О  </td>
            <td>Срок обуче-ния </td>
            <td>Вид ра- бот</td>
            <td>Объ- ем кон- тин- ген- та </td>
            <td>Рас-чет ча- сов </td>
            <td>1  сем</td>
            <td>2  сем</td>
            <td>  год</td>
          </tr>{
            val data = dat.groupBy (record => (record.post.getOrElse (""), record.rate, record.part.getOrElse ("")))
            data.map {
              case (name, rows) =>
                val discGroup = rows.groupBy (discipline_group =>
                  (discipline_group.discilineName,
                    discipline_group.faculty,
                    discipline_group.cipherGroup,
                    discipline_group.disciplineIndex,
                    discipline_group.course,
                    discipline_group.fo,
                    discipline_group.trainingPeriod))

                println (s"size=${discGroup.size}");

                discGroup.map { case (disciplonegroup, row_) =>
                  var sum_one_sem = 0
                  var sum_two_sem = 0
                  var sum_year = 0
                  row_.map { row =>
                    val (post, rate, part) = name
                    var one_sem = 0
                    var two_sem = 0
                    var year = 0
                  <tr>
                    {if (row eq rows.head) <td rowspan={(rows.length + discGroup.size).toString}>{post}, {rate}, {part}</td>}
                    <td>{row.discilineName.getOrElse ("")}</td>
                    <td>{row.faculty.getOrElse ("")}</td>
                    <td>{row.cipherGroup.getOrElse ("")}</td>
                    <td>{row.disciplineIndex.getOrElse ("")}</td>
                    <td>{row.course}</td>
                    <td>{row.fo.getOrElse ("")}</td>
                    <td>{row.trainingPeriod.getOrElse ("")}</td>
                    <td>{row.nameWork}</td>
                    <td>{row.contingentVolume}</td>
                    <td>{row.calculationHours}</td>
                    {if ( {
                    row.term
                  } == 1)
                    one_sem = {
                      row.calculationHours
                    };
                  sum_one_sem += one_sem;
                      <td> {one_sem}</td>
                    }
                    {if ( {
                    row.term
                  } == 2)
                    two_sem = {
                      row.calculationHours
                    };
                  sum_two_sem += two_sem;
                      <td> {two_sem}</td>
                    }
                    {year = {
                    row.calculationHours
                  };
                  sum_year += year;
                    <td>{row.calculationHours}</td>
                    }
                  </tr>
                  } ++ {
                <tr>
                 <th>Итого: </th>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <td></td>
                  <th>{sum_one_sem}</th>
                  <th>{sum_two_sem}</th>
                  <th>{sum_year}</th>
              </tr>}}
            }}
        </table>
      }}
        <p>Данные подготовлены в ИИАС "Учебный процесс"</p>
      </body>
    </html>

      val document: Document = new Document (PageSize.A4.rotate ())
      val writer: PdfWriter = PdfWriter.getInstance (document, new FileOutputStream ("/tmp/out.pdf"))
      document.open

      val fontProvider: XMLWorkerFontProvider = new XMLWorkerFontProvider (XMLWorkerFontProvider.DONTLOOKFORFONTS)
      fontProvider.register ("/usr/share/fonts/truetype/dejavu/DejaVuSerif.ttf")
      FontFactory.setFontImp (fontProvider)

      val is: InputStream = new ByteArrayInputStream (doc.toString ().getBytes (Charset.forName ("UTF-8")))
      XMLWorkerHelper.getInstance ().parseXHtml (writer, document, is, Charset.forName ("UTF-8"), fontProvider)
      document.close

      println ("PDF Created!")
    }
  }
}