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}
import com.itextpdf.tool.xml.{XMLWorkerFontProvider, XMLWorkerHelper}
import org.scalatest.FunSuite
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 {
  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=54412
    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
    /* def sum_discipline_year(rows: List[Member]) = 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)).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=>
              (name.last_name.getOrElse(""),
                name.first_name.getOrElse(""),
                name.middle_name.getOrElse(""))
                .toString().replaceAll(","," ")}
          } </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) =>
                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)).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.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
    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!")
  }
}}