package common

import (
	"dbweb/core"
	"dbweb/lib/safe"
	"dbweb/modules/common/recordview"
	"encoding/json"
	"fmt"
	"strconv"
	"strings"

	"sort"

	"github.com/linlexing/dbx/ddb"

	"github.com/linlexing/dbx/pageselect"
	"github.com/linlexing/dbx/scan"
	"github.com/linlexing/dbx/schema"
)

//Total 类
type Total struct{}

func init() {
	core.RegisterFun("total", new(Total), "_layout/blank")
}

//截前N位表达式
func leftChar(driver, express string, ty schema.DataType, iLen int64) string {
	switch driver {
	case "oci8":
		if ty == schema.TypeDatetime {
			return fmt.Sprintf("substr(to_char(%s,'yyyy-mm-dd HH24:MI:SS'),1,%d)", express, iLen)
		} else {
			return fmt.Sprintf("substr(%s,1,%d)", express, iLen)
		}
	case "mysql":
		if ty == schema.TypeDatetime {
			return "left(date_format(" + express + ",'%Y-%m-%d %H:%i:%s')," + strconv.FormatInt(iLen, 10) + ")"
		} else {
			return fmt.Sprintf("left(%s,%d)", express, iLen)
		}
	case "postgres":
		if ty == schema.TypeDatetime {
			return "left(to_char(" + express + ",'YYYY-MM-DD HH24:MI:SS')," + strconv.FormatInt(iLen, 10) + ")"
		} else {
			return fmt.Sprintf("left(%s,%d)", express, iLen)
		}
	case "sqlite3":
		if ty == schema.TypeDatetime {
			return fmt.Sprintf("substr(datetime(%s),1,%d)", express, iLen)
		} else {
			return fmt.Sprintf("substr(%s,1,%d)", express, iLen)
		}
	default:
		core.LOG.Panic("not impl")
		return ""
	}
}

//Post 业务
func (t *Total) Post(p *core.ElementHandleArgs) {
	param := recordview.GetCallParam(p)

	if param == nil {
		p.RenderError("闲置时间太长，已经超时，请关闭本窗口重新打开")
		return
	}
	fromElement := param.FromRecordView(p.DB)
	fromRecordViewParam := fromElement.Params

	dataDB := core.LoadOuterDB(p.DB, param.Select.DBName)
	q主栏 := p.Req.PostFormValue("主栏")
	q宾栏 := p.Req.PostFormValue("宾栏")
	q主栏Express := p.Req.PostFormValue("主栏Express")
	q宾栏Express := p.Req.PostFormValue("宾栏Express")
	q主栏Value := p.Req.PostFormValue("主栏Value")
	q宾栏Value := p.Req.PostFormValue("宾栏Value")
	q汇总指标 := p.Req.PostFormValue("汇总指标")
	SQL := ""
	宾栏指标 := ""
	主栏指标 := ""
	汇总指标Type := schema.TypeFloat
	if col := fromRecordViewParam.FindColumn(q汇总指标); col != nil {
		汇总指标Type = col.Type
	}
	columnTypes := []*scan.ColumnType{
		&scan.ColumnType{Name: 主栏指标, Type: schema.TypeString}}
	switch q主栏Express {
	case "(无)":
		主栏指标 = q主栏
		if col := fromRecordViewParam.FindColumn(主栏指标); col != nil {
			columnTypes[0].Type = col.Type
		}
	case "截前N位":
		iLen, err := strconv.ParseInt(q主栏Value, 10, 64)
		if err != nil {
			core.LOG.Panic(err)
		}
		field := fromRecordViewParam.FindColumn(q主栏)
		if field == nil {
			core.LOG.Panic("field:" + q主栏 + " can't found")
		}
		主栏指标 = leftChar(dataDB.DriverName(), q主栏, field.Type, iLen)
	case "表达式":
		主栏指标 = q主栏Value
	}
	if q宾栏 != "(合计)" {
		switch q宾栏Express {
		case "(无)":
			宾栏指标 = q宾栏
		case "截前N位":
			iLen, err := strconv.ParseInt(q宾栏Value, 10, 64)
			if err != nil {
				core.LOG.Panic(err)
			}
			field := fromRecordViewParam.FindColumn(q宾栏)
			if field == nil {
				core.LOG.Panic("field:" + q宾栏 + " can't found")
			}
			宾栏指标 = leftChar(dataDB.DriverName(), q宾栏, field.Type, iLen)
		case "表达式":
			宾栏指标 = q宾栏Value
		}
	}
	KeyFields := fromRecordViewParam.UniqueField
	KeyStr := []string{}
	switch dataDB.DriverName() {
	case "oci8", "mysql", "sqlite3", "postgres":
		for _, v := range KeyFields {
			KeyStr = append(KeyStr, fmt.Sprintf("wholesql.%s=交叉汇总.%[1]s", v))
		}

	default:
		core.LOG.Panic("not impl")
	}
	附加条件 := fmt.Sprintf("exists(select 1 from (%s) 交叉汇总 where %s)", param.Select.DataSQL(), strings.Join(KeyStr, " and "))
	if q宾栏 == "(合计)" {
		if q汇总指标 == "(记录数)" {
			SQL = fmt.Sprintf(`
				select
					%s as %s,
					count(*) as 记录数
				from (%s) 交叉汇总
				group by %[1]s`, 主栏指标, q主栏, param.Select.DataSQL())
			columnTypes = append(columnTypes, &scan.ColumnType{Name: "记录数", Type: schema.TypeInt})
		} else {
			SQL = fmt.Sprintf(`
				select 
					%s as %s,
					sum(%s(%s,0)) as %[4]s 
				from (%[5]s) 交叉汇总 
				group by %[1]s`, 主栏指标, q主栏,
				pageselect.Find(dataDB.DriverName()).IsNull(), q汇总指标, param.Select.DataSQL())
			columnTypes = append(columnTypes, &scan.ColumnType{Name: q汇总指标, Type: 汇总指标Type})
		}

	} else {
		var 宾栏分组 []string
		var 值 string
		宾栏指标名称 := map[string]bool{}
		strSql := fmt.Sprintf("select %s from (%s) 交叉汇总 group by %[1]s order by %[1]s",
			宾栏指标, param.Select.DataSQL())
		宾栏分组值, err := ddb.ScanStrings(dataDB, strSql)
		if err != nil {
			core.LOG.Panic(err)
		}
		sort.Strings(宾栏分组值)

		i := 0
		if q汇总指标 == "(记录数)" {
			宾栏分组 = []string{"count(*) as 记录数"}
			columnTypes = append(columnTypes, &scan.ColumnType{Name: "记录数", Type: schema.TypeInt})
		} else {
			宾栏分组 = []string{fmt.Sprintf("count(*) as 记录数,\nsum(%s(%s,0)) as %[2]s",
				pageselect.Find(dataDB.DriverName()).IsNull(), q汇总指标)}
			columnTypes = append(columnTypes, &scan.ColumnType{Name: q汇总指标, Type: 汇总指标Type})
		}
		for _, oneStr := range 宾栏分组值 {
			tmpStr := oneStr
			//截取最后26字节数据
			if len(tmpStr) > 26 {
				tmpStr = tmpStr[len(tmpStr)-26:]
			}
			Tmp_名称 := fmt.Sprintf("%s_%s", string([]rune(q宾栏)[0]), tmpStr)
			order := 1
			for _, ok := 宾栏指标名称[Tmp_名称]; ok; order++ {
				Tmp_名称 = fmt.Sprintf("%s_%s%d", string([]rune(q宾栏)[0]), tmpStr, order)
			}
			宾栏指标名称[Tmp_名称] = true
			//最多200个宾栏
			i++
			if i >= 200 {
				break
			}
			field := fromRecordViewParam.FindColumn(q宾栏)
			if field == nil {
				core.LOG.Panic("field:" + q宾栏 + " can't found")
			}
			switch field.Type {
			case schema.TypeString, schema.TypeDatetime:
				值 = safe.SignString(oneStr)
			case schema.TypeInt, schema.TypeFloat:
				值 = oneStr
			default:
				core.LOG.Panic("field:" + q宾栏 + " datatype:" + field.Type.String() + " error")
			}

			汇总指标 := q汇总指标
			if 汇总指标 == "(记录数)" {
				汇总指标 = "1"
				columnTypes = append(columnTypes, &scan.ColumnType{Name: strings.ToUpper(Tmp_名称), Type: schema.TypeInt})
			} else {
				columnTypes = append(columnTypes, &scan.ColumnType{Name: strings.ToUpper(Tmp_名称), Type: 汇总指标Type})
			}
			switch dataDB.DriverName() {
			case "oci8", "mysql", "sqlite3", "postgres":
				if 值 == "''" {
					宾栏分组 = append(宾栏分组, fmt.Sprintf("sum((case when %s is null then %s else 0 end)) as \"%s\"", 宾栏指标, 汇总指标, strings.ToUpper(Tmp_名称)))
				} else {
					宾栏分组 = append(宾栏分组, fmt.Sprintf("sum((case when %s=%s then %s else 0 end)) as \"%s\"", 宾栏指标, 值, 汇总指标, strings.ToUpper(Tmp_名称)))
				}
			default:
				core.LOG.Panic("not impl")
			}
		}
		SQL = fmt.Sprintf(`
				select 
					%s as %s,
					%s 
				from (%s) 交叉汇总 
				group by %[1]s`, 主栏指标, q主栏, strings.Join(宾栏分组, ",\n"), param.Select.DataSQL())
	}
	transQuery := map[string]interface{}{
		"_tq_title":     fmt.Sprintf("%s 交叉汇总中 %s={{.%s}}的记录", fromElement.Label, 主栏指标, q主栏),
		"_tq_condition": fmt.Sprintf("(%s) and {{if .%s}}%s={{P .%[2]s}}{{else}}%[3]s is null{{end}}", 附加条件, q主栏, 主栏指标),
	}
	transQueryBys, err := json.Marshal(transQuery)
	if err != nil {
		core.LOG.Panic(err)
	}
	recordViewParam := recordview.RecordViewParam{
		DB:          param.Select.DBName,
		Sql:         SQL,
		ManualPage:  false,
		TableName:   "", //fromRecordViewParam.TableName,
		UniqueField: []string{q主栏},
		Columns:     nil,
		AllColumns:  columnTypes,
		Processes: []*recordview.RecordViewProcess{
			&recordview.RecordViewProcess{
				Name:        "源数据",
				ElementName: param.FromElement,
				BindRecord:  true,
				TransQuery:  string(transQueryBys),
			},
			&recordview.RecordViewProcess{
				Name:        "导出数据",
				ElementName: "export",
				BindRecord:  false,
				WithSql:     true,
			},
		},
	}

	eleName := fmt.Sprintf("%s-交叉汇总", param.FromElement)
	if err := core.LoadModel(core.ModelElementName).Set(&ModelRecordViewElement{
		ModelElementNoParam: core.ModelElementNoParam{
			Name:       eleName,
			Label:      "",
			Category:   "交叉汇总",
			Controller: CtrlRecordViewName,
			Pub:        core.False,
			Owner:      core.ElementOwnerAutoCreate,
			UserName:   p.User.Name,
			Dept:       p.User.Dept.Code,
			NewWindow:  core.False,
			HelpText:   "",
		},
		Params: recordViewParam,
	}); err != nil {
		core.LOG.Panic(err)
	}

	//刷新缓存
	core.RemoveElementFromCache(eleName)
	p.Redirect(p.User.Sign("/" + eleName))
}
func (t *Total) Get(p *core.ElementHandleArgs) {
	param := recordview.GetCallParam(p)

	if param == nil {
		p.RenderError("闲置时间太长，已经超时，请关闭本窗口重新打开")
		return
	}
	fromElement := param.FromRecordView(p.DB)
	fromRecordViewParam := fromElement.Params
	if len(fromRecordViewParam.AllColumns) == 0 {
		p.RenderError(fmt.Sprintf("%s的字段类型没有设置，不能进行交叉汇总", fromElement.Name))
	}
	p.More["FromElement"] = fromElement
	//需要取出当前有效的字段
	//BuildSQL 比DataSQL性能更快
	cols, err := ddb.Columns(core.LoadOuterDB(p.DB, param.Select.DBName), param.Select.BuildSQL())
	if err != nil {
		core.LOG.Panic(err)
	}
	columnTypes := []*scan.ColumnType{}
	for _, one := range cols {
		if ct := fromRecordViewParam.FindColumn(one); ct == nil {
			columnTypes = append(columnTypes, &scan.ColumnType{
				Name: one,
				Type: schema.TypeString,
			})
		} else {
			columnTypes = append(columnTypes, ct)
		}

	}
	p.More["Columns"] = columnTypes
	p.HTML()

}