package models

import (
	"LAPP_SJA_ME/db"
	"LAPP_SJA_ME/utils"
	"errors"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"strings"
	"xorm.io/core"
)

type Scripttab struct {
	Finr            int             `xorm:"pk comment('工厂号') Int" json:"scripttab-finr"`
	Script          string          `xorm:"pk comment('脚本') VARCHAR(50)" json:"scripttab-script"`
	Bez             string          `xorm:"comment('名称') VARCHAR(50)" json:"scripttab-bez"`
	Scripttable     string          `xorm:"comment('导入表') VARCHAR(40)" json:"scripttab-scripttable"`
	Scripttype      string          `xorm:"comment('脚本类型') VARCHAR(20)" json:"scripttab-scripttype"`
	Filepath        string          `xorm:"comment('文件路径') VARCHAR(50)" json:"scripttab-filepath"`
	Separator       string          `xorm:"comment('分隔符') VARCHAR(20)" json:"scripttab-separator"`
	Usehead         int             `xorm:"comment('使用列头') INT" json:"scripttab-usehead"`
	Cleartable      int             `xorm:"comment('清除表') INT" json:"scripttab-cleartable"`
	Startrow        int             `xorm:"comment('起始行') INT" json:"scripttab-startrow"`
	Startcol        int             `xorm:"comment('起始列') INT" json:"scripttab-startcol"`
	Appendnewrecord int             `xorm:"comment('附加新纪录') INT" json:"scripttab-appendnewrecord"`
	Updaterecord    int             `xorm:"comment('更新记录') INT" json:"scripttab-updaterecord"`
	Callspecfunc    int             `xorm:"comment('调用特殊功能') INT" json:"scripttab-callspecfunc"`
	Callfunction    string          `xorm:"comment('调用函数') VARCHAR(100)" json:"scripttab-callfunction"`
	Inputpara1      string          `xorm:"comment('输入参数1') VARCHAR(255)" json:"scripttab-inputpara1"`
	Para1type       int             `xorm:"comment('参数1类型') INT" json:"scripttab-para1type"`
	Para1descr      string          `xorm:"comment('参数1描述') VARCHAR(40)" json:"scripttab-para1descr"`
	Inputpara2      string          `xorm:"comment('输入参数2') VARCHAR(255)" json:"scripttab-inputpara2"`
	Para2type       int             `xorm:"comment('参数2类型') INT" json:"scripttab-para2type"`
	Para2descr      string          `xorm:"comment('参数2描述') VARCHAR(40)" json:"scripttab-para2descr"`
	Inputpara3      string          `xorm:"comment('输入参数3') VARCHAR(255)" json:"scripttab-inputpara3"`
	Para3type       int             `xorm:"comment('参数3类型') INT" json:"scripttab-para3type"`
	Para3descr      string          `xorm:"comment('参数3描述') VARCHAR(40)" json:"scripttab-para3descr"`
	Inputpara4      string          `xorm:"comment('输入参数4') VARCHAR(255)" json:"scripttab-inputpara4"`
	Para4type       int             `xorm:"comment('参数4类型') INT" json:"scripttab-para4type"`
	Para4descr      string          `xorm:"comment('参数4描述') VARCHAR(40)" json:"scripttab-para4descr"`
	Lastmodif       string          `xorm:"comment('最近一次更改时间') VARCHAR(14)" json:"scripttab-lastmodif"`
	Lastuser        string          `xorm:"comment('最近一次更改人') VARCHAR(20)" json:"scripttab-lastuser"`
	Credatuz        string          `xorm:"comment('创建时间') VARCHAR(14)" json:"scripttab-credatuz"`
	Valst           []Scriptitemtab `json:"valst" xorm:"-"`
}

func (t *Scripttab) TableName() string {
	return "scripttab"
}

// 清除string字段的右侧空格
func (t *Scripttab) Clipped() {
	utils.TrimStruct(t, *t)
}

//增
func (t *Scripttab) Add() error {
	e := db.Eloquent.Master()
	countrole := new(Scripttab)
	affw, err := e.Table("scripttab").ID(core.PK{t.Finr, t.Script}).Count(countrole)
	if err != nil {
		return err
	}
	if affw > 0 {
		return errors.New("数据已经存在！")
	}
	_, err = e.Table("scripttab").Insert(t)
	if err != nil {
		return err
	}
	//批量删除
	_, err = e.Table("scriptitemtab").Where("finr = ? and script = ?", t.Finr, t.Script).Delete(&Scriptitemtab{})
	if err != nil {
		return err
	}
	//批量插入
	alldata := make([]Scriptitemtab, 0)
	for _, v := range t.Valst {
		onedata := Scriptitemtab{}
		onedata.Finr = t.Finr
		onedata.Script = t.Script
		onedata.Tabmapcol = v.Tabmapcol
		onedata.Colno = v.Colno
		onedata.Rowno = v.Rowno
		onedata.Colfieldtype = v.Colfieldtype
		onedata.Colname = v.Colname
		onedata.Rowname = v.Rowname
		onedata.Colformat = v.Colformat
		onedata.Defaultvalue = v.Defaultvalue
		alldata = append(alldata, onedata)
	}
	_, err = e.Table("scriptitemtab").Insert(&alldata)
	if err != nil {
		return err
	}
	return nil
}

//删
func (t *Scripttab) Del() bool {
	e := db.Eloquent.Master()
	_, err := e.ID(core.PK{t.Finr, t.Script}).Delete(&Scripttab{})
	if err != nil {
		return false
	}
	countrole := new(Scriptitemtab)
	affw, _ := e.Table("scriptitemtab").Where("finr = ? and script = ?", t.Finr, t.Script).Count(countrole)
	if affw > 0 {
		_, err = e.Where("finr = ? and script = ?", t.Finr, t.Script).Delete(&Scriptitemtab{})
		if err != nil {
			return false
		}
	}
	return true
}

//改
func (t *Scripttab) Update() bool {
	e := db.Eloquent.Master()
	_, err := e.ID(core.PK{t.Finr, t.Script}).Cols("usehead","scripttype","startcol","startrow","scripttable","cleartable", "appendnewrecord", "updaterecord", "callspecfunc").Update(t)
	if err != nil {
		return false
	}
	//批量删除
	_, err = e.Table("scriptitemtab").Where("finr = ? and script = ?", t.Finr, t.Script).Delete(&Scriptitemtab{})
	if err != nil {
		return false
	}
	//批量插入
	alldata := make([]Scriptitemtab, 0)
	for _, v := range t.Valst {
		onedata := Scriptitemtab{}
		onedata.Finr = t.Finr
		onedata.Script = t.Script
		onedata.Tabmapcol = v.Tabmapcol
		onedata.Colno = v.Colno
		onedata.Rowno = v.Rowno
		onedata.Colfieldtype = v.Colfieldtype
		onedata.Colname = v.Colname
		onedata.Rowname = v.Rowname
		onedata.Colformat = v.Colformat
		onedata.Defaultvalue = v.Defaultvalue
		alldata = append(alldata, onedata)
	}
	_, err = e.Table("scriptitemtab").Insert(&alldata)
	if err != nil {
		return false
	}
	return true
}

//查
func (t *Scripttab) SelectOne() (Scripttab, error) {
	e := db.Eloquent.Master()
	var data Scripttab
	_, err := e.ID(core.PK{t.Finr, t.Script}).Get(&data)
	if err != nil {
		return data, err
	}
	data.Clipped()
	var datalist []Scriptitemtab
	err = e.Where("finr = ? and script = ?", t.Finr, t.Script).Find(&datalist)
	if err != nil {
		return data, err
	}
	for k, _ := range datalist {
		datalist[k].Clipped()
	}
	data.Valst = datalist
	return data, nil
}

//分页
func (t *Scripttab) GetPage(pageSize int, pageIndex int) ([]Scripttab, int, error) {
	data := make([]Scripttab, 0)
	e := db.Eloquent.Master()
	table := e.Table("scripttab").Where("finr = ? ", t.Finr)
	where := "where finr = " + "'" + utils.ValueToString(t.Finr, "") + "'"
	if !utils.ValueIsEmpty(t.Script) {
		table = table.And("script = ?", t.Script)
		where += " and script = " + "'" + t.Script + "'"
	}
	if !utils.ValueIsEmpty(t.Scripttype) {
		table = table.And("scripttype = ?", t.Scripttype)
		where += " and scripttype = " + "'" + t.Scripttype + "'"
	}
	Offset := (pageIndex - 1) * pageSize
	err := e.SQL("SELECT TOP " + utils.ValueToString(pageSize, "") + " scripttab.* FROM scripttab " + where + " AND (convert(varchar(10),finr)+convert(varchar(40),script) NOT IN (SELECT TOP " + utils.ValueToString(Offset, "") + " convert(varchar(10),finr)+convert(varchar(40),script) FROM scripttab " + where + " ORDER BY script DESC)) ORDER BY script DESC").Find(&data)
	pcount := new(Scripttab)
	count, err := table.Count(pcount)
	if err != nil {
		return data, 0, err
	}
	for k, _ := range data {
		data[k].Clipped()
	}
	return data, int(count), nil
}

type TablePk struct {
	TabName string
	Colname string
}
// 一维表导入表功能
func (t *Scripttab) ReadXlsx(path string) bool {
	e := db.Eloquent.Master()

	xlsx, err := excelize.OpenFile(path)
	if err != nil {
		return false
	}
	rows := xlsx.GetRows("Sheet1")

	//第一步:获取表名称
	tablename := t.Scripttable

	//新增新纪录
	if t.Appendnewrecord == 1 {
		//第二步构建sql语句
		for startrow, row := range rows {
			//起始行导入
			if (startrow + 1) <= t.Startrow {
				continue
			}
			keystr := ""
			valstr := ""

			//构造map
			tem := map[int]interface{}{}
			for k, v := range row {
				key := k + 1
				val := v
				tem[key] = val
			}

			//第三步：拼接
			for _, vv := range t.Valst {
				keystr += vv.Tabmapcol + ","
				val, ok := tem[vv.Colno]
				if ok {
					valstr += "'" + utils.ValueToString(val, "") + "'" + ","
				} else {
					if !utils.ValueIsEmpty(vv.Defaultvalue) {
						valstr += "' " + utils.ValueToString(vv.Defaultvalue, "") + " '" + ","
					} else {
						//根据数据类型添加默认值
						if vv.Colfieldtype == 1{
							valstr += "'0'" + ","
						}else if vv.Colfieldtype == 5{
							valstr += "'0.0'" + ","
						}else{
							valstr += "' '" + ","
						}

					}

				}
			}
			//去掉最后边的逗号
			keystr = strings.Trim(keystr, ",")
			valstr = strings.Trim(valstr, ",")
			//2.开始导入
			_, err := e.Exec("INSERT INTO " + tablename + " (" + keystr + ") values (" + valstr + ")")
			if err != nil {
				fmt.Printf("err is :%v", err)
				continue
			}
		}
	}

	//更新数据表
	if t.Updaterecord == 1 {
		TablePk := make([]TablePk,0)
		//获取当前导入表的主键
		err := e.SQL("SELECT TABLE_NAME as tabname,COLUMN_NAME as colname FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=?", t.Scripttable).Find(&TablePk)
		if err != nil {
			return false
		}
		temPk := make(map[string]interface{})
		for _,v:= range TablePk{
			key := v.Colname
			temPk[key] = v
		}
		//第二步构建sql语句
		for startrow, row := range rows {
			//起始行导入
			if (startrow + 1) <= t.Startrow {
				continue
			}
			wherestr := ""
			valstr := ""

			//构造map
			tem := map[int]interface{}{}
			for k, v := range row {
				key := k + 1
				val := v
				tem[key] = val
			}

			//第三步：拼接
			for _, vv := range t.Valst {
				_,res:=temPk[vv.Tabmapcol]
				val, ok := tem[vv.Colno]
				if ok {
					if res{
						wherestr += vv.Tabmapcol + "= '" + utils.ValueToString(val, "") + "'" + " AND "
					}
					valstr += vv.Tabmapcol + "= '" + utils.ValueToString(val, "") + "'" + ","
				} else {
					if res{
						wherestr += vv.Tabmapcol + "= '" + utils.ValueToString(vv.Defaultvalue, "") + "'" + " AND "
					}
					if !utils.ValueIsEmpty(vv.Defaultvalue) {
						valstr +=vv.Tabmapcol + "= '" + utils.ValueToString(vv.Defaultvalue, "") + " '" + ","
					}

				}
			}
			//去掉最后边的逗号
			wherestr = strings.Trim(wherestr, " AND ")
			valstr = strings.Trim(valstr, ",")
			//2.开始导入
			//更新
			_, err = e.Exec("UPDATE " + tablename + " SET " + valstr + " WHERE " + wherestr )
			if err != nil {
				fmt.Printf("err is :%v", err)
				continue
			}
		}


	}
	//清除数据表
	if t.Cleartable == 1{
		//先删除
		_, err := e.Exec("delete from " + tablename )
		if err != nil {
			fmt.Printf("err is :%v", err)
		}
	}
	return true
}

type TabData struct {
	Tabmapcol string
	Coltype   string
}

//新增数据（根据表明查询）
func (t *Scripttab) SelectArr() ([]Scriptitemtab, error) {
	e := db.Eloquent.Master()
	datalist := make([]TabData, 0)
	data := make([]Scriptitemtab, 0)
	err := e.SQL("SELECT COLUMN_NAME as tabmapcol,DATA_TYPE as coltype FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=?", t.Scripttable).Find(&datalist)
	if err != nil {
		return nil, err
	}
	for _, v := range datalist {
		dataone := Scriptitemtab{}
		dataone.Tabmapcol = v.Tabmapcol
		if v.Coltype == "int" {
			dataone.Colfieldtype = 1
		} else if v.Coltype == "decimal" {
			dataone.Colfieldtype = 5
		}else{
			dataone.Colfieldtype = 8
		}
		dataone.Clipped()
		data = append(data, dataone)
	}
	return data, err
}


// 二维表导入表功能
func (t *Scripttab) ReadDoubleXlsx(path string) bool {
	e := db.Eloquent.Master()

	xlsx, err := excelize.OpenFile(path)
	if err != nil {
		return false
	}
	rows := xlsx.GetRows("Sheet1")

	//第一步:获取表名称
	tablename := t.Scripttable
	//定义一个数组，记录attrcode,并且与k关联起来
	tem := map[int]string{}

	//新增新纪录
	if t.Appendnewrecord == 1 {
		//第二步构建sql语句
		for startrow, row := range rows {
			if t.Startrow == (startrow + 1) {
				//记录映射规则,并且与k关联起来
				for kk, vv := range row {
					if (kk+1) >= t.Startcol{
						tem[kk] = vv
					}
				}
				continue
			}

			//起始行导入
			if (startrow + 1) <= t.Startrow {
				continue
			}
			//构造map
			temrow := map[int]interface{}{}

			for k, v := range row {
				//记录表头
				if k < t.Startcol{
					key := k + 1
					val := v
					temrow[key] = val
					continue
				}

				keystr := ""
				valstr := ""
				isempty := false
				//第三步：拼接
				for _, vv := range t.Valst {

					//记录字段
					keystr += vv.Tabmapcol + ","
					val, ok := temrow[vv.Colno]
					if ok && vv.Rowno == 0{
						//表头对应关系
						valstr += "'" + utils.ValueToString(val, "") + "'" + ","
					} else if vv.Colno == 0 && vv.Rowno >0{
						//映射关系对应
						valstr += "'" + utils.ValueToString(tem[k], "") + "'" + ","
					}else if vv.Rowno >0 && vv.Colno >0 {
						//记录值
						if utils.ValueIsEmpty(v) || v == "-"{
							isempty = true
						}
						valstr += "'" + utils.ValueToString(v, "") + "'" + ","
					}else{
						if !utils.ValueIsEmpty(vv.Defaultvalue) {
							valstr += "' " + utils.ValueToString(vv.Defaultvalue, "") + " '" + ","
						} else {
							//根据数据类型添加默认值
							if vv.Colfieldtype == 1{
								valstr += "'0'" + ","
							}else if vv.Colfieldtype == 5{
								valstr += "'0.0'" + ","
							}else{
								valstr += "' '" + ","
							}

						}

					}

				}
				if isempty{
					continue
				}
				//去掉最后边的逗号
				keystr = strings.Trim(keystr, ",")
				valstr = strings.Trim(valstr, ",")
				//sql := "INSERT INTO " + tablename + " (" + keystr + ") values (" + valstr + ")"
				//fmt.Printf("sql is :%v",sql)
				//2.开始导入
				_, err := e.Exec("INSERT INTO " + tablename + " (" + keystr + ") values (" + valstr + ")")
				if err != nil {
					fmt.Printf("err is :%v", err)
					continue
				}

			}

		}
	}

	//更新数据表
	if t.Updaterecord == 1 {
		TablePk := make([]TablePk,0)
		//获取当前导入表的主键
		err := e.SQL("SELECT TABLE_NAME as tabname,COLUMN_NAME as colname FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=?", t.Scripttable).Find(&TablePk)
		if err != nil {
			return false
		}
		temPk := make(map[string]interface{})
		for _,v:= range TablePk{
			key := v.Colname
			temPk[key] = v
		}
		//第二步构建sql语句
		for startrow, row := range rows {

			if t.Startrow == (startrow + 1) {
				//记录映射规则,并且与k关联起来
				for kk, vv := range row {
					if (kk+1) >= t.Startcol{
						tem[kk] = vv
					}
				}
			}

			//起始行导入
			if (startrow + 1) <= t.Startrow {
				continue
			}

			//构造map
			temrow := map[int]interface{}{}

			for k, v := range row {
				//记录表头
				if k < t.Startcol {
					key := k + 1
					val := v
					temrow[key] = val
					continue
				}
				wherestr := ""
				valstr := ""
				isempty := false
				//第三步：拼接
				for _, vv := range t.Valst {

					_,res:=temPk[vv.Tabmapcol]
					val, ok := temrow[vv.Colno]
					if ok && vv.Rowno == 0 {
						if res{
							wherestr += vv.Tabmapcol + "= '" + utils.ValueToString(val, "") + "'" + " AND "
						}
						valstr += vv.Tabmapcol + "= '" + utils.ValueToString(val, "") + "'" + ","
					} else if vv.Colno == 0 && vv.Rowno >0{
						if res{
							wherestr += vv.Tabmapcol + "= '" + utils.ValueToString(vv.Defaultvalue, "") + "'" + " AND "
						}
						valstr +=  vv.Tabmapcol + "= '" +  utils.ValueToString(tem[k], "") + "'" + ","
					}else if vv.Rowno >0 && vv.Colno >0 {
						if utils.ValueIsEmpty(v) || v == "-"{
							isempty = true
						}
						valstr += vv.Tabmapcol + "= '" +  utils.ValueToString(v, "") + "'" + ","
					}else{
						if res{
							wherestr += vv.Tabmapcol + "= '" + utils.ValueToString(vv.Defaultvalue, "") + "'" + " AND "
						}
						if !utils.ValueIsEmpty(vv.Defaultvalue) {
							valstr +=vv.Tabmapcol + "= '" + utils.ValueToString(vv.Defaultvalue, "") + " '" + ","
						}
					}

				}
				if isempty {
					continue
				}
				//去掉最后边的逗号
				wherestr = strings.Trim(wherestr, " AND ")
				valstr = strings.Trim(valstr, ",")
				//2.开始导入
				//更新
				_, err = e.Exec("UPDATE " + tablename + " SET " + valstr + " WHERE " + wherestr )
				if err != nil {
					fmt.Printf("err is :%v", err)
					continue
				}

			}

		}

	}
	//清除数据表
	if t.Cleartable == 1{
		//先删除
		_, err := e.Exec("delete from " + tablename )
		if err != nil {
			fmt.Printf("err is :%v", err)
		}
	}
	return true
}