//设置package包名称以及导入依赖的类
package com.durooma.api.route

import java.sql.Date

import akka.http.scaladsl.marshallers.sprayjson.SprayJsonSupport
import com.durooma.api.model._
import com.durooma.db.Tables
import org.joda.time.DateTime
import org.joda.time.format.{DateTimeFormatter, ISODateTimeFormat}
import spray.json.{DefaultJsonProtocol, DeserializationException, JsString, JsValue, RootJsonFormat}


trait JsonSupport extends SprayJsonSupport with DefaultJsonProtocol {

  implicit object DateJsonFormat extends RootJsonFormat[DateTime] {

    private val parserISO : DateTimeFormatter = ISODateTimeFormat.dateTimeNoMillis()

    override def write(obj: DateTime) = JsString(parserISO.print(obj))

    override def read(json: JsValue) : DateTime = json match {
      case JsString(s) => parserISO.parseDateTime(s)
      case _ => throw DeserializationException("Invalid date format: " + json)
    }
  }

  implicit object SqlDateJsonFormat extends RootJsonFormat[Date] {

    override def write(obj: Date) = JsString(obj.toString)
    override def read(json: JsValue) = json match {
      case JsString(s) => Date.valueOf(s)
      case _ => throw DeserializationException("Invalid date format: " + json)
    }

  }

  implicit val userFormat = jsonFormat5(User.apply)
  implicit val userRegistrationFormat = jsonFormat5(UserRegistration.apply)
  implicit val accountFormat = jsonFormat4(Account.apply)
  implicit val accounBodyFormat = jsonFormat2(AccountBody.apply)
  implicit val labelFormat = jsonFormat3(Tables.LabelRow.apply)
  implicit val transactionFormat = jsonFormat8(Transaction.apply)
  implicit val transactionBodyFormat = jsonFormat7(TransactionBody.apply)
  implicit val sessionFormat = jsonFormat3(Session.apply)
  implicit val credentialsFormat = jsonFormat2(CustomCredentials.apply)

} 

//设置package包名称以及导入依赖的类
package dao

import javax.inject.Inject
import java.sql.Date

import play.api.db.slick.DatabaseConfigProvider
import slick.driver.JdbcProfile
import slick.driver.MySQLDriver.api._
import models.Account

import scala.concurrent.Future

class AccountDAO @Inject() (protected val databaseConfigProvider: DatabaseConfigProvider){
  private class AccountTable(tag: Tag) extends Table[Account] (tag, "Account"){
    def account_id = column[Long]("account_id", O.PrimaryKey, O.AutoInc)
    def user_id = column[Option[Long]]("user_id")
    def name = column[String]("name")
    def amount = column[Long]("amount")
    def category_id = column[Long]("category_id")
    def register_at = column[Date]("register_at")
    def payment_source_id = column[Option[Long]]("payment_source_id")
    def payment_destination_id = column[Option[Long]]("payment_destination_id")

    override def * = (
      account_id,
      user_id,
      name,
      amount,
      category_id,
      register_at,
      payment_source_id,
      payment_destination_id) <> (Account.tupled, Account.unapply)
  }

  val dbConfig = databaseConfigProvider.get[JdbcProfile]
  private val accounts = TableQuery[AccountTable]

  def list: Future[Seq[Account]] = {
    dbConfig.db.run(accounts.result)
  }
} 

//设置package包名称以及导入依赖的类
package slickORM.models

import java.sql.Date

import slick.ast.ColumnOption.PrimaryKey
import slick.driver.MySQLDriver.api._
import slick.lifted._
import tables._

class Channels(tag: Tag) extends Table[(Int, Int, Int, Int, String, Int, Date, Date)](tag, "t_shorturl_channel") {
  def id = column[Int]("id", PrimaryKey)
  def article = column[Int]("article_id")
  def channel = column[Int]("channel_id")
  def brand = column[Int]("brand_id")
  def url = column[String]("short_url")
  def status = column[Int]("status")
  def creationDate = column[Date]("ctime")
  def updatedTime = column[Date]("utime")

  def * =
    (id, article, channel, brand, url, status, creationDate, updatedTime)

} 

//设置package包名称以及导入依赖的类
package slickORM.models

import java.sql.Date

import slick.ast.ColumnOption.PrimaryKey
import slick.driver.MySQLDriver.api._

class Articles(tag: Tag) extends Table[(Int, String, Int, Int, String, Int, Date, Date)](tag, "t_shorturl_article") {
  def id = column[Int]("id", PrimaryKey)
  def title = column[String]("title")
  def adminId = column[Int]("admin_id")
  def topicId = column[Int]("topic_id")
  def url = column[String]("url")
  def status = column[Int]("status")
  def updatedTime = column[Date]("utime")
  def creationDate = column[Date]("ctime")

  def * =
    (id, title, adminId, topicId, url, status, updatedTime, creationDate)
} 

//设置package包名称以及导入依赖的类
package slickORM.models

import java.sql.Date

import slick.ast.ColumnOption.PrimaryKey
import slick.driver.MySQLDriver.api._
import slick.lifted._
import tables._

class Tags(tag: Tag) extends Table[(Int, String, String, String, Boolean, Date, Date, Int)](tag, "generic_tag") {
  def id = column[Int]("id", PrimaryKey)
  def tagX = column[String]("tag")
  def typeX = column[String]("type")
  def module = column[String]("module")
  def status = column[Boolean]("status")
  def creationDate = column[Date]("created")
  def updatedTime = column[Date]("updated")
  def parentId = column[Int]("parent_id")

  def * =
    (id, tagX, typeX, module, status, creationDate, updatedTime, parentId)
} 

//设置package包名称以及导入依赖的类
package lunatech.lunchplanner.models

import java.sql.Date
import java.util.UUID

case class MenuPerDay(
  uuid: UUID = UUID.randomUUID(),
  menuUuid: UUID,
  date: Date
)

case class MenuWithNamePerDay(
  uuid: UUID,
  menuUuid: UUID,
  menuDate: String,
  menuName: String,
  numberOfPeopleSignedIn: Int
)

case class MenuPerDayDietRestrictions(
  menuPerDayUuid: UUID,
  vegetarian: Int = 0,
  seaFoodRestriction: Int = 0,
  porkRestriction: Int = 0,
  beefRestriction: Int = 0,
  chickenRestriction: Int = 0,
  glutenRestriction: Int = 0,
  lactoseRestriction: Int = 0
)

case class MenuPerDayAttendant(name: String, otherRestrictions: String) 

//设置package包名称以及导入依赖的类
package lunatech.lunchplanner.persistence

import java.sql.Date
import java.util.UUID

import lunatech.lunchplanner.common.{ AcceptanceSpec, DBConnection, TestDatabaseProvider }
import lunatech.lunchplanner.models.{ Menu, MenuPerDay, MenuPerDayPerPerson, User, UserProfile }

import scala.concurrent.Await

class UserProfileTableSpec extends AcceptanceSpec with TestDatabaseProvider {

  implicit private val dbConnection = app.injector.instanceOf[DBConnection]

  private val newUser = User(UUID.randomUUID(), "Leonor Boga", "[email protected]")
  private val newUserProfile = UserProfile(userUuid = newUser.uuid, vegetarian = true)

  override def beforeAll {
    cleanDatabase()
    Await.result(UserTable.add(newUser), defaultTimeout)
  }

  "A UserProfile table" must {
    "add a new user profile" in {
      val result = Await.result(UserProfileTable.insertOrUpdate(newUserProfile), defaultTimeout)
      result mustBe true
    }

    "get user profile by user uuid" in {
      val result = Await.result(UserProfileTable.getByUserUUID(newUser.uuid), defaultTimeout)
      result mustBe Some(newUserProfile)
    }

    "get all user profiles" in {
      val result = Await.result(UserProfileTable.getAll, defaultTimeout)
      result mustBe Seq(newUserProfile)
    }

    "remove user profile" in {
      val result = Await.result(UserProfileTable.remove(newUser.uuid), defaultTimeout)
      result mustBe 1
    }

    "give summary of diet restrictions by menuPerDay" in {
      val newMenu = Menu(name = "Main menu")
      val newMenuPerDay = MenuPerDay(menuUuid = newMenu.uuid, date = new Date(99999999))
      val newMenuPerDayPerPerson = MenuPerDayPerPerson(menuPerDayUuid = newMenuPerDay.uuid, userUuid = newUser.uuid)

      Await.result(UserProfileTable.insertOrUpdate(newUserProfile), defaultTimeout)
      Await.result(MenuTable.add(newMenu), defaultTimeout)
      Await.result(MenuPerDayTable.add(newMenuPerDay), defaultTimeout)
      Await.result(MenuPerDayPerPersonTable.add(newMenuPerDayPerPerson), defaultTimeout)

      val result = Await.result(UserProfileTable.getRestrictionsByMenuPerDay(newMenuPerDay.uuid), defaultTimeout)
      result mustBe Vector((1, 0, 0, 0, 0, 0, 0))
    }
  }
} 

//设置package包名称以及导入依赖的类
package utilities

import java.sql.{Timestamp,Date,Time}
import org.joda.time.{DateTime,LocalDate,LocalTime,DateTimeZone}
import org.joda.time.format._

object DateHelper {
  def dateTimeToSqlTimestamp: DateTime => Timestamp = { dt => new Timestamp(dt.getMillis) }
  def sqlTimestampToDateTime: Timestamp => DateTime = { ts => new DateTime(ts.getTime) }
  def localDateToSqlDate: LocalDate => Date = { ld => new Date(ld.toDateTimeAtStartOfDay(DateTimeZone.UTC).getMillis) }
  def sqlDateToLocalDate: Date => LocalDate = { d  => new LocalDate(d.getTime) }
  def localTimeToSqlTime: LocalTime => Time = { lt => new Time(lt.toDateTimeToday.getMillis) }
  def sqlTimeToLocalTime: Time => LocalTime = { t  => new LocalTime(t, DateTimeZone.UTC) }
  
  def dateToString(date:java.util.Date, format:String = "yyyy-MM-dd HH:mm:ss"):String = {
    import java.text._
    val sdf = new SimpleDateFormat(format)
    sdf.format(date)
  }
  def stringToDate(datestr:String, format:String = "yyyy-MM-dd HH:mm:ss"):java.util.Date = {
    import java.text._
    val sdf = new SimpleDateFormat(format)
    sdf.parse(datestr)
  }
} 

//设置package包名称以及导入依赖的类
package forms

import java.sql.Date

import play.api.data.Form
import play.api.data.Forms._

object ProfileForm {

  
  case class Data(
    accountName: Option[String],
    userName: Option[String],
    email: Option[String],
    birthDay: Option[Date],
    openBirthDay: Option[Int],
    comment: Option[String],
    residence: Option[String],
    openResidence: Option[Int],
    openHistory: Option[Int]
  )
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date

import slick.driver.PostgresDriver.api._

class User(tag: Tag) extends Table[(Int, String, String, String, String, Date, Char, String, String)](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def username = column[String]("username")
  def address = column[String]("address")
  def university = column[String]("university")
  def dateOfBirth = column[Date]("date_of_birth")
  def gender = column[Char]("gender")
  def country = column[String]("country")
  def password = column[String]("password")

  def * = (id, name, username, address, university, dateOfBirth, gender, country, password)
} 

//设置package包名称以及导入依赖的类
package ch03

import java.sql.Date
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}

/**
  * Created by Giancarlo on 9/10/2016.
  */
object CreateDataFrame {
  def main(args:Array[String]){
    val conf = new SparkConf()
      .setMaster("local[2]")
      .setAppName("DataFrames from RDD")
      .set("spark.executor.memory","2g")

    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    import sqlContext.implicits._

    val format = new java.text.SimpleDateFormat("dd/MM/yyyy")

    val rdd = sc.textFile("c:/Spark/data/03-IntroductionToSpark/Retail/OnlineRetail.csv")
      .map(line=>line.split(","))
      .map(fields=>(fields(0),fields(1),fields(2),fields(3).toInt,new Date(format.parse(fields(4)).getTime()),fields(5).toFloat,fields(6),fields(7)))

    // Providing the names for the columns
    val df = rdd.toDF("InvoiceNum","Skus","Desc","Qty","Date","uPrice","cID","Country")

    df.printSchema()

    val franceDF = df.select("InvoiceNum","Desc","Qty","Country").where(df("Country") === "France")
    franceDF.show(5)

    df.registerTempTable("Invoices")
    df.sqlContext.sql("SELECT COUNT(*) as total FROM Invoices WHERE Country='France'").show()
  }
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date

import akka.http.scaladsl.marshallers.sprayjson.SprayJsonSupport
import spray.json.DefaultJsonProtocol

case class BookSearch(
  title: Option[String] = None,
  releaseDate: Option[Date] = None,
  categoryId: Option[Long] = None,
  author: Option[String] = None
)

// JSON format for our Book model, to convert to and from JSON
trait BookSearchJson extends SprayJsonSupport with DefaultJsonProtocol {
  import services.FormatService._

  implicit val bookSearchFormat = jsonFormat4(BookSearch.apply)
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date

import akka.http.scaladsl.marshallers.sprayjson.SprayJsonSupport
//import slick.driver.PostgresDriver.api._
import slick.driver.MySQLDriver.api._
import spray.json.DefaultJsonProtocol

// Our Book model
case class Book(
  id: Option[Long] = None,
  title: String,
  releaseDate: Date,
  categoryId: Long,
  quantity: Int,
  author: String
)

// JSON format for our Book model, to convert to and from JSON
trait BookJson extends SprayJsonSupport with DefaultJsonProtocol {
  import services.FormatService._

  implicit val bookFormat = jsonFormat6(Book.apply)
}

// Slick table mapped to our Book model
trait BookTable {

  class Books(tag: Tag) extends Table[Book](tag, "books") {
    def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
    def title = column[String]("title")
    def releaseDate = column[Date]("release_date")
    def categoryId = column[Long]("category_id")
    def quantity = column[Int]("quantity")
    def author = column[String]("author")

    def * = (id, title, releaseDate, categoryId, quantity, author) <> ((Book.apply _).tupled, Book.unapply)
  }

  protected val books = TableQuery[Books]
} 

//设置package包名称以及导入依赖的类
package dao

import java.sql.Date
import javax.inject.Inject

import models.{IncomeOutcome, User}
import play.api.db.slick.DatabaseConfigProvider
import play.db.NamedDatabase
import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile
import slick.driver.SQLiteDriver.api._
import slick.lifted.{ForeignKeyQuery, ProvenShape}
import utils.Const

import scala.concurrent.{ExecutionContext, Future}

class IncomeOutcomeDAO @Inject()(@NamedDatabase(Const.DbName) dbConfigProvider: DatabaseConfigProvider,
                                 val userDAO: UserDAO)(implicit executionContext: ExecutionContext) {
  private val dbConfig: DatabaseConfig[JdbcProfile] = dbConfigProvider.get[JdbcProfile]
  // initialisation of foreign keys in SQLite
  dbConfig.db.run(DBIO.seq(sqlu"PRAGMA foreign_keys = ON;")).map { _ => () }

  private val incomesOutcomes: TableQuery[IncomeOutcomeTable] = TableQuery[IncomeOutcomeTable]

  def insert(incomeOutcome: IncomeOutcome): Future[Unit] = {
    dbConfig.db.run(incomesOutcomes += incomeOutcome).map { _ => () }
  }

  def findAll(userEmail: String): Future[Seq[IncomeOutcome]] = {
    dbConfig.db.run(incomesOutcomes.join(userDAO.users).on(_.userId === _.id).filter(_._2.email === userEmail)
      .map(_._1).result)
  }

  private class IncomeOutcomeTable(tag: Tag) extends Table[IncomeOutcome](tag, "income_outcome") {
    def id: Rep[Int] = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def userId: Rep[Int] = column[Int]("userId")
    def date: Rep[Date] = column[Date]("date")
    def income: Rep[Double] = column[Double]("income")
    def outcome: Rep[Double] = column[Double]("outcome")

    // A reified foreign key relation to an user that can be navigated to create a join
    // n to one relationship
    def user: ForeignKeyQuery[userDAO.UserTable, User] = {
      // when an user is deleted, his income and outcome values are also deleted (same with update)
      foreignKey("user_FK", userId, userDAO.users)(_.id, onDelete = ForeignKeyAction.Cascade,
        onUpdate = ForeignKeyAction.Cascade)
    }

    def * : ProvenShape[IncomeOutcome] = {
      (date, income, outcome, userId) <> ((IncomeOutcome.apply _).tupled, IncomeOutcome.unapply)
    }
  }
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date
import java.util.Calendar
import javax.inject.{Inject, Singleton}

import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import slick.driver.JdbcProfile
import slick.driver.MySQLDriver.api._

@Singleton()
class MemberDataAccess @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
	val members = TableQuery[MembersTable]

	def getList = {
		db.run(members.result)
	}

	def create = {
		db.run(members.schema.create)
	}

	def insert = {
		db.run(members += Member(1, "admin", "12345", "Genius", "[email protected]", new java.sql.Date(Calendar.getInstance().getTimeInMillis)))
	}
}

case class Member(mid: Int, userid: String, password: String, nickname: String, email: String, regdate:Date)

class MembersTable(tag: Tag) extends Table[Member](tag, "MEMBERS") {
	def mid = column[Int]("MID", O.PrimaryKey, O.AutoInc)
	def userid = column[String]("USERID")
	def password = column[String]("PASSWORD")
	def nickname = column[String]("NICKNAME")
	def email = column[String]("EMAIL")
	def regdate = column[Date]("REGDATE")
	def * = (mid, userid, password, nickname, email, regdate) <> ((Member.apply _).tupled, Member.unapply)
} 

//设置package包名称以及导入依赖的类
import java.io.File
import java.sql.Date
import java.text.SimpleDateFormat
import com.github.tototoshi.csv._

object FECData {

  val DataDirectory = "./data/"

  private val dateParser = new SimpleDateFormat("DD-MMM-YY")

  private def load(fileName:String):FECData = {
    val reader = CSVReader.open(new File(DataDirectory + fileName))
    val transactions = for { 
      row <- reader.iteratorWithHeaders
      id = None
      candidate = row("candidate")
      contributor = row("contributor_name")
      state = row("contributor_state")
      occupation = row("contributor_occupation") match {
        case "" => None
        case v => Some(v)
      }
      amount = (row("amount").toDouble*100).toInt
      date = new Date(dateParser.parse(row("date")).getTime)
    } yield Transaction(id, candidate, contributor, state, occupation, amount, date)
    
    new FECData(transactions)
  }

  def loadAll:FECData = load("us.csv")

  def loadOhio:FECData = load("ohio.csv")


}

class FECData(val transactions:Iterator[Transaction]) 

//设置package包名称以及导入依赖的类
import java.sql.Date

import scala.slick.driver.MySQLDriver.simple._

object Tables {

  class Transactions(tag:Tag)
  extends Table[Transaction](tag, "transactions") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def candidate = column[String]("candidate")
    def contributor = column[String]("contributor")
    def contributorState = column[String]("contributor_state", O.DBType("VARCHAR(2)"))
    def contributorOccupation = column[Option[String]]("contributor_occupation")
    def amount = column[Long]("amount")
    def date = column[Date]("date")
    def * = (id.?, candidate, contributor, 
      contributorState, contributorOccupation, amount, date) <> (
      Transaction.tupled, Transaction.unapply)
  }

  val transactions = TableQuery[Transactions]

} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date

import play.api.libs.json._
import play.api.libs.functional.syntax._

case class Game(id: Option[Int], homeTeamId: Int, visitorTeamId: Int, date: Date)

object Game {
  implicit val gameReads: Reads[Game] = (
      (JsPath \ "id").readNullable[Int] and
      (JsPath \ "homeTeamId").read[Int] and
      (JsPath \ "visitorTeamId").read[Int] and
      (JsPath \ "date").read[Date]
  )(Game.apply _)

  implicit val gameWrites: Writes[Game] = (
      (JsPath \ "id").writeNullable[Int] and
      (JsPath \ "homeTeamId").write[Int] and
      (JsPath \ "visitorTeamId").write[Int] and
      (JsPath \"date").write[Date]
    )(unlift(Game.unapply))
} 

//设置package包名称以及导入依赖的类
package dao

import models.{Game, Team}
import slick.driver.H2Driver.api._
import slick.lifted.{ForeignKeyQuery, Tag}
import java.sql.Date

class Games(tag: Tag) extends Table[Game](tag, "games") {
  def id: Rep[Int] = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def homeTeamId: Rep[Int] = column[Int]("home_team_id")
  def visitorTeamId: Rep[Int] = column[Int]("visitor_team_id")
  def date: Rep[Date] = column[Date]("date")
  def * = (id.?, homeTeamId, visitorTeamId, date) <> ((Game.apply _).tupled, Game.unapply)
  def homeTeamFk: ForeignKeyQuery[Teams, Team] = foreignKey("home_team", homeTeamId, Teams.objects)(_.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Cascade)
  def visitorTeamFk: ForeignKeyQuery[Teams, Team] = foreignKey("visitor_team", visitorTeamId, Teams.objects)(_.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Cascade)

}

object Games {
  val objects: TableQuery[Games] = TableQuery[Games]
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Date

import org.joda.time.DateTime
import org.junit.runner.RunWith
import org.scalatest.junit.JUnitRunner
import org.scalatestplus.play._
import play.api.libs.json.Json
import play.api.test._
import play.api.test.Helpers._

@RunWith(classOf[JUnitRunner])
class GameSpec extends PlaySpec {
  "Game" must {
    "be able to converted to from valid JSON containing all of its data" in {
      val expectedGame = Game(Some(1), 1, 2, new Date(DateTime.parse("2018-01-01").toDate.getTime))
      val gameJsonString =
        """{
          |  "id": 1,
          |  "homeTeamId": 1,
          |  "visitorTeamId": 2,
          |  "date": "2018-01-01T00:00:00.00Z"
          |   }""".stripMargin
      val actualGame = Json.parse(gameJsonString).as[Game]
      actualGame.date.compareTo(expectedGame.date) mustEqual 0

    }
  }
  it should {
    "be able to be converted to from valid JSON containing all of its data except for the id" in {
      val expectedGame = Game(None, 1, 2, new Date(DateTime.parse("2018-01-01").toDate.getTime))
      val gameJsonString =
        """{
          |  "homeTeamId": 1,
          |  "visitorTeamId": 2,
          |  "date": "2018-01-01T00:00:00.000Z"
          |}""".stripMargin
      val actualGame = Json.parse(gameJsonString).as[Game]
      actualGame.date.compareTo(expectedGame.date) mustEqual 0
      actualGame.visitorTeamId mustEqual expectedGame.visitorTeamId
      actualGame.homeTeamId mustEqual expectedGame.homeTeamId
    }
  }
}