package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/go-xorm/xorm" //xorm原版
    "github.com/shopspring/decimal"
    "github.com/sirupsen/logrus"
    "os"

    //"github.com/xormplus/xorm" //xorm升级版
    "time"
    "xorm.io/core"
)

var engine *xorm.Engine

func init() {
    var err error
    param := "root:root@tcp(127.0.0.1:3306)/ts?charset=utf8&parseTime=true&loc=Local"
    engine, err = xorm.NewEngine("mysql", param)
    if err != nil {
        logrus.Panicf("数据库连接错误，%v", err)
    }

    //设置日志显示
    engine.ShowSQL(true)
    engine.SetLogLevel(core.LOG_DEBUG)
    //存日志文件
    f, err := os.Create("sql.log")
    if err != nil {
        println()
    }
    engine.SetLogger(xorm.NewSimpleLogger(f))

    //使用syslog

    //设置连接池
    engine.SetMaxOpenConns(3)
    engine.SetMaxIdleConns(1)
    engine.SetConnMaxLifetime(12 * time.Hour)

    // 设置缓存
    cacher := xorm.NewLRUCacher(xorm.NewMemoryStore(), 1000)
    engine.SetDefaultCacher(cacher)

    //测试连接
    engine.Ping()

}

//教师详细信息
type Detail struct {
    Id        int64
    Email     string
    Addr      string
    Tel       string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//学生
type Student struct {
    Id        int64
    Name      string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
    Num       int64     `json:"num"`
}

//教师
type Teacher struct {
    Id        int64
    Name      string
    DetailId  int64     `xorm:"index notnull"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
    Grade     int64     `json:"grade"`
}

//课程
type Course struct {
    Id        int64
    Name      string
    TeacherId int64     `xorm:"index not null"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//成绩表
type Performance struct {
    Id        int64
    CourseId  int64 `xorm:"index notnull"`
    StudentId int64 `xorm:"index notnull"`
    Score     decimal.Decimal
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

type TeacherDetail struct {
    Teacher `xorm:"extends"`
    Detail  `xorm:"extends"`
}

//func (TeacherDetail) TableName() string {
//  //指定使用该结构体对象 进行数据库查询时，使用的表名
//  return "teacher"
//}

type CourseTeacher struct {
    Course  `xorm:"extends"`
    Teacher `xorm:"extends"`
}

func (CourseTeacher) TableName() string {
    return "course"
}

func syncStruct() {
    //将结构体同步到数据库
    err := engine.Sync2(new(Detail), new(Student), new(Teacher), new(Course), new(Performance))
    if err != nil {
        logrus.Panicf("同步到数据库失败，%v", err)
    }

}

func insertSata() {
    //插入基础数据
    detail1 := &Detail{Id: 1, Tel: "卡卡卡卡卡", Addr: "卡卡卡", Email: "kakaka@sina.com"}
    detail2 := &Detail{Id: 2, Tel: "11111111", Addr: "木叶村", Email: "kai@sina.com"}
    stu1 := &Student{Id: 1, Name: "佐助"}
    stu2 := &Student{Id: 2, Name: "鸣人"}
    stu3 := &Student{Id: 3, Name: "小樱"}
    stu4 := &Student{Id: 4, Name: "雏田"}
    tcher1 := &Teacher{Id: 1, Name: "卡卡西", DetailId: 1}
    tcher2 := &Teacher{Id: 2, Name: "凯", DetailId: 2}
    course1 := &Course{Name: "疾风手里剑", TeacherId: 1}
    course2 := &Course{Name: "基本体术", TeacherId: 2}
    perf1 := &Performance{CourseId: 1, StudentId: 1, Score: decimal.NewFromFloat(100)}
    perf2 := &Performance{CourseId: 1, StudentId: 2, Score: decimal.NewFromFloat(60)}
    perf3 := &Performance{CourseId: 1, StudentId: 3, Score: decimal.NewFromFloat(80)}

    engine.Insert(detail1, detail2, stu1, stu2, stu3, stu4, tcher1, tcher2, course1, course2, perf1, perf2, perf3)

}

func queryTable() {
    //1.单条数据查询
    theOne := &Student{Id: 1}
    _, _ = engine.Get(theOne)
    theOther := &Student{}
    _, _ = engine.Id(1).Get(theOther)
    fmt.Println(theOther)
    //条件查询
    student1 := &Student{}
    _, _ = engine.Where("id=?", 2).Get(student1)
    fmt.Println("student1=", student1)
    //单表多条查询
    stuArr1 := make([]Student, 0)
    engine.Find(&stuArr1)
    logrus.Infof("查询学生结构体数组：%v", stuArr1)

    stuMap1 := make(map[int64]Student)
    engine.Find(&stuMap1)
    logrus.Infof("查询学生Map:%v", stuMap1)

    //一对一查询
    teacherDetail := make([]TeacherDetail, 0)
    engine.Table("teacher").
        //    Cols("teacher.*","detail.*").
        Select("teacher.*,detail.*").
        Join("LEFT", "detail", "teacher.detail_id=detail.id").Find(&teacherDetail)
    logrus.Infof("查询一对一（教师，教师详情）:%v", teacherDetail)
    //一对多查询
    CourseTchers := make([]CourseTeacher, 0) //声明数组
    engine.Join("LEFT", "teacher", "course.teacher_id=teacher.id").Find(&CourseTchers)
    logrus.Infof("查一对多(N课程：1老师)：%v", CourseTchers)
    //多对多查询(N课程：N学生)
    performs := make([]Performance, 0)
    engine.Join("LEFT", "course", "performance.course_id=course.id").
        Join("LEFT", "student", "performance.student_id=student.id").
        Find(&performs)
    logrus.Infof("查詢多對多（N课程，N学生）：%v", performs)
    /*
        [{1 1 1 100 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
        {2 1 2 60 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
        {3 1 3 80 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}]
    */
    //不定义自己的集合
    newStu := new(Student)
    rows, err := engine.Rows(newStu)
    if err != nil {
        fmt.Println("err=", err)
    }
    defer rows.Close()
    for rows.Next() {
        _ = rows.Scan(newStu)
        logrus.Infof("newStu:%v", newStu)
    }
    /*
       INFO[0000] newStu:&{1 佐助 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
       INFO[0000] newStu:&{2 鸣人 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
       INFO[0000] newStu:&{3 小樱 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
       INFO[0000] newStu:&{5 日向宁次 2020-06-24 13:12:35 +0800 CST 2020-06-24 13:12:35 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
       INFO[0000] newStu:&{6 日向宁次 2020-06-24 13:18:40 +0800 CST 2020-06-24 13:18:40 +0800 CST 0001-01-01 00:00:00 +0000 UTC}

    */
    //执行查询sql
    sql1 := "select * from student where id=?"
    queryRet, _ := engine.Query(sql1, 1)
    logrus.Infof("使用sql查询结果:%v", queryRet)

    //where使用
    stu := new(Student)
    engine.Where("name=?", "jack").Get(stu)
    logrus.Infof("where查询：", stu)
    stu2 := &Student{
        Id: 10,
    }
    engine.Get(stu2)
    logrus.Infof("stu2=:", stu2)
    //exist

    var has bool
    has, err = engine.SQL("select * from student where name = ?", "test1").Exist()
    logrus.Infof("exists=:", has)
    //find方法
    stu_s := make([]Student, 0)
    stu_s2 := make([]*Student, 0)
    err = engine.Find(&stu_s)
    err = engine.Where("num> 0 or num=?", 5).Find(&stu_s2)
    fmt.Println(err)
    logrus.Infof("stu_s=:", stu_s)
    logrus.Infof("stu_s2=:", stu_s2)
    //limit使用
    tea := make([]Teacher, 0)
    engine.Where("grade=?", 0).Find(&tea)
    logrus.Infof("tea=", tea)
    st9 := make([]Student, 0)
    //内容长度，起始数据
    engine.Limit(3, 1).Find(&st9)
    logrus.Infof("limit=", st9)
    for _, v := range st9 {
        fmt.Println(v)
    }
    //查询单个字段可使用切片
    var names []string
    engine.Table("student").Cols("name").Find(&names)
    logrus.Infof("取出姓名=", names)
    //count 统计
    //    var s2 Student
    total, err := engine.Table("student").Count()
    logrus.Infof("total=", total)

    //迭代读取
    s3 := new(Student)
    rows, _ = engine.Where("id >?", 1).Rows(s3)
    if err != nil {
    }
    defer rows.Close()
    for rows.Next() {
        err = rows.Scan(s3)
        fmt.Println("s3=", s3)
    }
    //&{3 rose 2020-06-27 18:41:10 +0800 CST 2020-06-27 18:41:10 +0800 CST 0001-01-01 00:00:00 +0000 UTC 5}
    //sum求和
    sum, _ := engine.Sum(new(Student), "num")
    //42
    logrus.Infof("sum=", sum)
    fmt.Println(sum)
    var st Student
    b, err := engine.SQL("select * from student where id=?", 3).Get(&st)
    if err != nil {
        logrus.Infof("err=", err)
        return
    }
    if !b {
        logrus.Infof("id=3不存在")
        return
    }
    fmt.Println("id=3的数据", st)
    var st1 Student
    b, err = engine.Table("student").Where("id=?", 3).Get(&st1)
    logrus.Infof("st1=", st1)
    /*
       {3 rose 2020-06-27 18:41:10 +0800 CST 2020-06-27 18:41:10 +0800 CST 0001-01-01 00:00:00 +0000 UTC 5})

    */
    r, _ := engine.QueryString("select * from student ")
    logrus.Infof("r=", r)
    fmt.Println()
    //for _, v := range r {
    //    fmt.Println(v)
    //}
    /*
        [map[created_at:2020-06-27T18:41:10+08:00 deleted_at: id:1 name:日向宁次 num:34 updated_at:2020-06-27T18:41:10+08:00] map[crea
        ted_at:2020-06-27T18:41:10+08:00 deleted_at: id:2 name:kkk num:3 updated_at:2020-06-27T18:41:10+08:00] map[created_at:2020-06-27T18:41:10+08:00 deleted_at: id:3 name:ros
        e num:5 updated_at:2020-06-27T18:41:10+08:00]])

    */
    r1, _ := engine.QueryString("select * from student ")
    logrus.Infof("r1=", r1)
    //fmt.Println()
    //for _, v := range r1 {
    //    fmt.Println(v)
    //}
    /*
       [map[created_at:2020-06-27T18:41:10+08:00 deleted_at: id:1 name:日向宁次 num:34 updated_at:2020-06-27T18:41:10+08:00] map[crea
       ted_at:2020-06-27T18:41:10+08:00 deleted_at: id:2 name:kkk num:3 updated_at:2020-06-27T18:41:10+08:00] map[created_at:2020-06-27T18:41:10+08:00 deleted_at: id:3 name:ros
       e num:5 updated_at:2020-06-27T18:41:10+08:00]])
    */

    records, err3 := engine.Table("student").Limit(1).QuerySliceString()
    if err3 != nil {
        logrus.Infof("err=", err3)
    }
    logrus.Infof("records=", records)

}

func insertData() {

    sql2 := "insert into student (name,created_at,updated_at) values (?,now(),now())"
    ret, _ := engine.Exec(sql2, "日向宁次")
    lastInsertId, _ := ret.LastInsertId()
    effectedRows, _ := ret.RowsAffected()
    logrus.Infof("执行sql命令结果,插入id：%v，影响行数：%v",
        lastInsertId, effectedRows)

    //插入一条数据
    var stu1 Student
    stu1.Name = "kkk"
    stu1.CreatedAt = time.Now()
    stu1.UpdatedAt = time.Now()
    engine.Insert(stu1)
    engine.InsertOne(stu1) //也可以
    //批量插入数据
    /*
        stu := make([]Student, 2)
        stu[0].Name = "jack"
        stu[0].CreatedAt = time.Now()
        stu[0].UpdatedAt = time.Now()
        stu[1].Name = "rose"
        stu[1].CreatedAt = time.Now()
        stu[1].UpdatedAt = time.Now()
        engine.Insert(stu)
    */

}

func updateData() {
    //条件更新
    var s1 Student
    s2 := Student{
        Num: 0,
    }
    s1.Id = 2
    _, err := engine.Update(s2, s1)
    //更新字段为默认值
    //_, err := engine.Table(new(Student)).Where("id=?",2).Cols("num").Update(Student{})
    if err != nil {
        logrus.Infof("err=", err)
    }
    //自增
    //stu := &Student{}
    //b, err := engine.Id(3).Incr("num", 10).Update(stu)
    ////自减
    //b, err = engine.Id(3).Incr("num", -10).Update(stu)
    //if err != nil {
    //    logrus.Infof("err=", err)
    //}

    //logrus.Infof("b=", b)

}
func deleteData() {
    //软删除 设置deleted_at时间
    //    engine.Delete(&Student{
    //        Id: 13,
    //    })
    //删除数据
    //var stu Student
    //engine.Id(13).Unscoped().Delete(&stu)
    ////删除num=99
    //engine.Where("num=?",99).Unscoped().Delete(&stu)
    //执行sql删除
    //1
    //sql:="delete from student where id=?"
    //res,_:=engine.Exec(sql,11)
    //logrus.Infof("res=",res)
    //2.
    //engine.SQL(sql,10).Exec()

}

//事务
func tra() {
    //模拟转账
    var money int64
    money = 100
    var s1 Student
    //减账
    session := engine.NewSession()
    defer session.Close()
    session.Begin()
    _, err := session.Id(9).Get(&s1)
    if err != nil {
        session.Rollback()
        return
    }
    _, err = session.Update(&Student{Num: s1.Num - money}, &Student{Id: 9})
    if err != nil {
        session.Rollback()
        return
    }
    //加账
    var s2 Student
    _, err = session.Id(8).Get(&s2)
    if err != nil {
        session.Rollback()
        return
    }
    _, err = session.Update(&Student{Num: s2.Num + money}, &Student{Id: 8})
    if err != nil {
        session.Rollback()
        return
    }
    err = session.Commit()
    if err != nil {
        return
    }
}

//数据导出
func dumpData() {
    err := engine.DumpAllToFile("a.sql")
    logrus.Infof("err=", err)

}

//数据导入
func importData() {
    _, err := engine.ImportFile("a.sql")
    if err != nil {
        logrus.Infof("err=", err)
    }

}

//查询结果导出
//orm查询结果集支持导出csv、tsv、xml、json、xlsx、yaml、html七种文件格式
func importance() {
    //err := engine.Sql("select * from student").Query().SaveAsXLSX("1.xlsx", []string{"id", "name", "counts", "orders", "createtime", "pid", "lastupdatetime", "status"}, 0777)
    //
    //if err != nil {
    //    t.Fatal(err)
    //}
}

type domain struct {
}
type Param struct {
    ActivityId []int64  `json:"activity_id"`
    Sex        []string `json:"sex"`
    Num        []int64  `json:"num"`
    Name       string   `json:"name"`
}

func dtQuery() {
    var persons []domain
    var param Param
    session := engine.Where("1=1")
    if param.ActivityId != nil {
        session = session.And("activity_id = ?", param.ActivityId)
    }
    if param.Sex != nil {
        session = session.And("sex = ?", param.Sex)
    }
    if param.Num != nil {
        session = session.And("num = ?", param.Num)
    }
    if param.Name != "" {
        name := "%" + param.Name + "%"
        session = session.And("name like ?", name)
    }

    _ = session.OrderBy("create_time desc").Limit(10, 0).Find(&persons)
}