fms_api/controller/census/seller.go

package census

import (
	"bytes"
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/go-playground/validator/v10"
	"github.com/shopspring/decimal"
	"github.com/tealeg/xlsx"
	"hongze/fms_api/controller/resp"
	"hongze/fms_api/global"
	"hongze/fms_api/models/base"
	"hongze/fms_api/models/crm"
	"hongze/fms_api/models/fms"
	crmService "hongze/fms_api/services/crm"
	"hongze/fms_api/utils"
	"net/http"
	"strconv"
	"strings"
	"time"
)

// SellerController 销售统计
type SellerController struct{}

// GroupInvoiceList
// @Title 销售组开票统计列表
// @Description 销售组开票统计列表
// @Param   StartDate		query	string	false	"开始日期"
// @Param   EndDate			query	string	false	"结束日期"
// @Param   SortField		query	int		false	"排序字段: 1-开票金额; 2-组别占比"
// @Param   SortType		query	int		false	"排序方式: 1-正序; 2-倒序"
// @Param   IsExport		query	int		false	"是否导出: 0-否; 1-是"
// @Success 200 {object} fms.CensusSellerGroupInvoiceItem
// @router /census/seller/group_invoice_list [get]
func (ct *SellerController) GroupInvoiceList(c *gin.Context) {
	var req fms.CensusSellerGroupInvoiceListReq
	if e := c.BindQuery(&req); e != nil {
		err, ok := e.(validator.ValidationErrors)
		if !ok {
			resp.FailData("参数解析失败", "Err:"+e.Error(), c)
			return
		}
		resp.FailData("参数解析失败", err.Translate(global.Trans), c)
		return
	}
	var departmentId int
	if req.SellerType == 1 {
		departmentId = crm.SellerDepartmentId
	}else if req.SellerType == 2 {
		departmentId = crm.RaiSellerDepartmentId
	}else if req.SellerType == 0 {
		resp.Fail("请选择销售类型", c)
		return
	}else {
		resp.Fail("请选择正确的销售类型", c)
		return
	}
	outCond := ` department_id = %d AND parent_id = 0 `
	outCond = fmt.Sprintf(outCond, departmentId)
	cond := ` (invoice_type = %d OR invoice_type = %d) AND a.is_deleted = 0 AND a.seller_group_id != 0 `
	cond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake)

	//adminCond := ` (invoice_type = %d OR invoice_type = %d) AND is_deleted = 0 AND seller_group_id != 0 `
	//adminCond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake)
	pars := make([]interface{}, 0)
	// 开票日期
	if req.StartDate != "" && req.EndDate != "" {
		st := fmt.Sprint(req.StartDate, " 00:00:00")
		ed := fmt.Sprint(req.EndDate, " 23:59:59")
		cond += ` AND (invoice_time BETWEEN '%s' AND '%s')`
		cond = fmt.Sprintf(cond, st, ed)

		//adminCond += ` AND (invoice_time BETWEEN '%s' AND '%s')`
		//adminCond = fmt.Sprintf(cond, st, ed)
	}

	if req.CompanyType == 1 {
		cond += ` AND b.contract_type = 1 `
		//historyCond += ` AND new_company = 1 `
	} else if req.CompanyType == 2 {
		cond += ` AND b.contract_type IN (2,3,4) `
		//historyCond += ` AND new_company = 0 `
	}

	page := new(base.Page)
	page.SetPageSize(req.PageSize)
	page.SetCurrent(req.Current)
	// 排序, 默认开票金额倒序
	sortFieldMap := map[int]string{0: "invoice_amount", 1: "invoice_amount", 2: "group_rate"}
	sortTypeMap := map[int]bool{0: false, 1: true, 2: false}
	page.AddOrderItem(base.OrderItem{Column: sortFieldMap[req.SortField], Asc: sortTypeMap[req.SortType]})
	if req.IsExport == 1 {
		page.SetPageSize(10000)
		page.SetCurrent(1)
	}

	// 查询开票金额总和(减少子查询)
	invOB := new(fms.ContractInvoice)
	sumCond := cond
	sumPars := make([]interface{}, 0)
	invSum, e := invOB.Sum("amount", sumCond, sumPars)
	if e != nil {
		resp.FailMsg("获取失败", "获取开票金额总和失败, Err: "+e.Error(), c)
		return
	}

	// 查询列表
	groupOB := new(crm.SysGroup)
	//totalCond := outCond
	//totalPars := make([]interface{}, 0)
	//total, e := groupOB.Count(totalCond, totalPars)
	//if e != nil {
	//	resp.FailMsg("获取失败", "获取销售组开票统计列表总数失败, Err: "+e.Error(), c)
	//	return
	//}
	//list, e := fms.GetCensusSellerGroupInvoicePageList(page, cond, outCond, pars, invSum)
	//if e != nil {
	//	resp.FailMsg("获取失败", "获取销售组开票统计列表失败, Err: "+e.Error(), c)
	//	return
	//}
	groupCond := ` department_id = %d AND parent_id = 0 `
	groupCond = fmt.Sprintf(groupCond, departmentId)
	groupPars := make([]interface{}, 0)
	groupList, e := groupOB.List(groupCond, groupPars)
	if e != nil {
		resp.FailMsg("获取失败", "获取组别列表失败, Err: "+e.Error(), c)
		return
	}
	//total := len(groupList)
	groupMap := make(map[int]*crm.SysGroup)
	groupIdSlice := make([]string,0)
	for i := range groupList {
		groupMap[groupList[i].GroupId] = groupList[i]
		groupIdSlice = append(groupIdSlice, strconv.Itoa(groupList[i].GroupId))
	}

	groupStr := strings.Join(groupIdSlice, ",")
	total, list, e := fms.GetCensusSellerGroupInvoicePageListV2(page, groupStr, cond, pars, invSum)
	if e != nil {
		resp.FailMsg("获取失败", "获取销售组开票统计列表失败, Err: "+e.Error(), c)
		return
	}
	for _, v := range list {
		if group, ok := groupMap[v.GroupId]; ok{
			v.GroupName = group.GroupName
		}
	}
	// 处理百分比, 乘100并保留两位小数
	mulNum := decimal.NewFromFloat(100)
	for i := range list {
		d := decimal.NewFromFloat(list[i].GroupRate)
		d = d.Mul(mulNum).Round(2)
		a, _ := d.Float64()
		list[i].GroupRate = a
	}

	// 是否导出
	if req.IsExport == 1 {
		ExportGroupInvoiceList(c, list, req)
		return
	}
	page.SetTotal(int64(total))
	baseData := new(base.BaseData)
	baseData.SetPage(page)
	baseData.SetList(list)
	resp.OkData("获取成功", baseData, c)
}

// ExportGroupInvoiceList 导出销售组开票统计列表
func ExportGroupInvoiceList(c *gin.Context, list []*fms.CensusSellerGroupInvoiceItem, req fms.CensusSellerGroupInvoiceListReq) {
	// 生成Excel文件
	xlsxFile := xlsx.NewFile()
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}
	style.Alignment = alignment
	style.ApplyAlignment = true

	sheetName := "销售组开票统计"
	sheet, err := xlsxFile.AddSheet(sheetName)
	if err != nil {
		resp.FailData("新增Sheet失败", "Err:"+err.Error(), c)
		return
	}

	// 存在筛选则前两行显示时间信息
	if req.StartDate != "" && req.EndDate != "" {
		timeData := fmt.Sprintf("时间：%s至%s", req.StartDate, req.EndDate)
		rowA := sheet.AddRow()
		cellAA := rowA.AddCell()
		cellAA.SetString("销售统计表")
		cellAA.SetStyle(style)
		rowB := sheet.AddRow()
		rowB.AddCell().SetString(timeData)
		// 第三行空出
		sheet.AddRow()
	}

	// 数据表头
	rowTitle := []string{"排名", "销售组别", "收入金额（元）", "组别占比"}
	titleRow := sheet.AddRow()
	for i := range rowTitle {
		v := titleRow.AddCell()
		v.SetString(rowTitle[i])
		v.SetStyle(style)
	}

	// 填充数据
	for k, v := range list {
		dataRow := sheet.AddRow()
		dataRow.AddCell().SetString(fmt.Sprint(k + 1))            // 排名
		dataRow.AddCell().SetString(v.GroupName)                  // 销售组别
		dataRow.AddCell().SetString(fmt.Sprint(v.InvoiceAmount))  // 开票金额
		dataRow.AddCell().SetString(fmt.Sprint(v.GroupRate, "%")) // 组别占比
	}

	// 输出文件
	var buffer bytes.Buffer
	_ = xlsxFile.Write(&buffer)
	content := bytes.NewReader(buffer.Bytes())
	randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
	fileName := sheetName + randStr + ".xlsx"

	c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName))
	c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content)
}

// InvoiceList
// @Title 销售开票统计列表
// @Description 销售开票统计列表
// @Param   GroupId			query	int		false	"组别ID"
// @Param   StartDate		query	string	false	"开始日期"
// @Param   EndDate			query	string	false	"结束日期"
// @Param   SortField		query	int		false	"排序字段: 1-开票金额; 2-组别占比; 3-全员占比"
// @Param   SortType		query	int		false	"排序方式: 1-正序; 2-倒序"
// @Param   IsExport		query	int		false	"是否导出: 0-否; 1-是"
// @Success 200 {object} fms.CensusSellerInvoiceListReq
// @router /census/seller/invoice_list [get]
func (ct *SellerController) InvoiceList(c *gin.Context) {
	var req fms.CensusSellerInvoiceListReq
	if e := c.BindQuery(&req); e != nil {
		err, ok := e.(validator.ValidationErrors)
		if !ok {
			resp.FailData("参数解析失败", "Err:"+e.Error(), c)
			return
		}
		resp.FailData("参数解析失败", err.Translate(global.Trans), c)
		return
	}
	var departmentId int
	if req.SellerType == 1 {
		departmentId = crm.SellerDepartmentId
	}else if req.SellerType == 2 {
		departmentId = crm.RaiSellerDepartmentId
	}else if req.SellerType == 0 {
		resp.Fail("请选择销售类型", c)
		return
	}else {
		resp.Fail("请选择正确的销售类型", c)
		return
	}


	pars := make([]interface{}, 0)
	adminPars := make([]interface{}, 0)
	outCond := ` a.department_id = %d `
	outCond = fmt.Sprintf(outCond, departmentId)
	adminCond := ` department_id = %d `
	adminCond = fmt.Sprintf(adminCond, departmentId)
	totalCond := ` department_id = %d `
	totalCond = fmt.Sprintf(totalCond, departmentId)
	totalPars := make([]interface{}, 0)

	if !req.ShowResign {
		adminCond += ` AND enabled = 0 `
	}
	if req.GroupId > 0 {
		// 筛选组别时, 查询当前组别的下级组(因为admin表存的group_id, 有三级的存的是子ID, 只有二级的存的才是父ID =_=!)
		groupCond := `parent_id = ?`
		groupPars := make([]interface{}, 0)
		groupPars = append(groupPars, req.GroupId)
		groupOB := new(crm.SysGroup)
		groupList, e := groupOB.List(groupCond, groupPars)
		if e != nil {
			resp.FailMsg("获取失败", "获取组别下级组列表失败, Err: "+e.Error(), c)
			return
		}
		groupIds := make([]int, 0)
		groupIds = append(groupIds, req.GroupId)
		for i := range groupList {
			groupIds = append(groupIds, groupList[i].GroupId)
		}
		outCond += ` AND a.group_id IN (?) `
		pars = append(pars, groupIds)
		adminCond += ` AND group_id IN (?) `
		adminPars = append(adminPars, groupIds)
		totalCond += ` AND group_id IN (?) `
		totalPars = append(totalPars, groupIds)
	}



	sumCond := ` (invoice_type = ? OR invoice_type = ? ) AND a.is_deleted = 0 AND a.seller_id != 0 `
	sumPars := make([]interface{}, 0)
	sumPars = append(sumPars, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake)
	cond := ` (c.invoice_type = %d OR c.invoice_type = %d) AND c.is_deleted = 0 AND c.seller_id != 0 `
	inCond := ` (invoice_type = %d OR invoice_type = %d) AND is_deleted = 0 AND seller_id != 0 `
	cond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake)
	inCond = fmt.Sprintf(inCond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake)

	if req.CompanyType == 1 {
		cond += ` AND b.contract_type = 1 `
		//historyCond += ` AND new_company = 1 `
	} else if req.CompanyType == 2 {
		cond += ` AND b.contract_type IN (2,3,4) `
		//historyCond += ` AND new_company = 0 `
	}

	// 开票日期
	if req.StartDate != "" && req.EndDate != "" {
		st := fmt.Sprint(req.StartDate, " 00:00:00")
		ed := fmt.Sprint(req.EndDate, " 23:59:59")
		cond += ` AND (c.invoice_time BETWEEN '%s' AND '%s')`
		inCond += ` AND (invoice_time BETWEEN '%s' AND '%s')`
		cond = fmt.Sprintf(cond, st, ed)
		inCond = fmt.Sprintf(inCond, st, ed)
		sumCond += ` AND (invoice_time BETWEEN ? AND ?)`
		sumPars = append(sumPars, st, ed)
	}

	page := new(base.Page)
	page.SetPageSize(req.PageSize)
	page.SetCurrent(req.Current)
	// 排序, 默认开票金额倒序
	sortFieldMap := map[int]string{0: "invoice_amount", 1: "invoice_amount", 2: "group_rate", 3: "seller_rate"}
	sortTypeMap := map[int]bool{0: false, 1: true, 2: false}
	page.AddOrderItem(base.OrderItem{Column: sortFieldMap[req.SortField], Asc: sortTypeMap[req.SortType]})
	if req.IsExport == 1 {
		page.SetPageSize(10000)
		page.SetCurrent(1)
	}

	// 查询开票金额总和(减少子查询)
	invOB := new(fms.ContractInvoice)
	invSum, e := invOB.Sum("amount", sumCond, sumPars)
	if e != nil {
		resp.FailMsg("获取失败", "获取开票金额总和失败, Err: "+e.Error(), c)
		return
	}

	// 查询列表
	adminOB := new(crm.Admin)
	//total, e := adminOB.Count(totalCond, totalPars)
	//if e != nil {
	//	resp.FailMsg("获取失败", "获取销售开票统计列表总数失败, Err: "+e.Error(), c)
	//	return
	//}
	//list, e := fms.GetCensusSellerInvoicePageList(page, cond, outCond, pars, invSum)
	//if e != nil {
	//	resp.FailMsg("获取失败", "获取销售开票统计列表失败, Err: "+e.Error(), c)
	//	return
	//}

	adminList, e := adminOB.List(adminCond, adminPars)
	if e != nil {
		resp.FailMsg("获取失败", "获取销售列表失败, Err: "+e.Error(), c)
		return
	}
	//total := len(adminList)
	adminIdSlice := make([]string,0)
	sellerMap := make(map[int]*crm.Admin)
	for i := range adminList {
		sellerMap[adminList[i].AdminId] = adminList[i]
		adminIdSlice = append(adminIdSlice, strconv.Itoa(adminList[i].AdminId))
	}


	adminStr := strings.Join(adminIdSlice, ",")

	total, list, e := fms.GetCensusSellerInvoicePageListV2(page, adminStr, inCond, cond, pars, invSum)
	if e != nil {
		resp.FailMsg("获取失败", "获取销售开票统计列表失败, Err: "+e.Error(), c)
		return
	}

	for _, v := range list {
		if admin, ok := sellerMap[v.SellerId]; ok{
			v.GroupName = admin.GroupName
			v.SellerName = admin.RealName
			v.GroupId = admin.GroupId
		}
	}
	// 分组信息, list的groupId可能是三级的ID, 要转为对应的二级
	groupMap, e := crmService.GetSellerTeamGroupMap()
	if e != nil {
		resp.FailMsg("获取失败", "获取组别对应信息失败, Err: "+e.Error(), c)
		return
	}

	mulNum := decimal.NewFromFloat(100)
	for i := range list {
		g := groupMap[list[i].GroupId]
		if g != nil {
			list[i].GroupId = g.GroupId
			list[i].GroupName = g.GroupName
		}
		// 处理百分比, 乘100并保留两位小数
		d := decimal.NewFromFloat(list[i].GroupRate)
		d = d.Mul(mulNum).Round(2)
		a, _ := d.Float64()
		list[i].GroupRate = a
		d2 := decimal.NewFromFloat(list[i].SellerRate)
		d2 = d2.Mul(mulNum).Round(2)
		a2, _ := d2.Float64()
		list[i].SellerRate = a2
	}

	// 是否导出
	if req.IsExport == 1 {
		ExportInvoiceList(c, list, req)
		return
	}
	page.SetTotal(int64(total))
	baseData := new(base.BaseData)
	baseData.SetPage(page)
	baseData.SetList(list)
	resp.OkData("获取成功", baseData, c)
}

// ExportInvoiceList 导出销售开票统计列表
func ExportInvoiceList(c *gin.Context, list []*fms.CensusSellerInvoiceItem, req fms.CensusSellerInvoiceListReq) {
	// 生成Excel文件
	xlsxFile := xlsx.NewFile()
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}
	style.Alignment = alignment
	style.ApplyAlignment = true

	sheetName := "销售开票统计"
	sheet, err := xlsxFile.AddSheet(sheetName)
	if err != nil {
		resp.FailData("新增Sheet失败", "Err:"+err.Error(), c)
		return
	}

	// 存在筛选则前两行显示时间信息
	if req.StartDate != "" && req.EndDate != "" {
		timeData := fmt.Sprintf("时间：%s至%s", req.StartDate, req.EndDate)
		rowA := sheet.AddRow()
		cellAA := rowA.AddCell()
		cellAA.SetString("销售统计表")
		cellAA.SetStyle(style)
		rowB := sheet.AddRow()
		rowB.AddCell().SetString(timeData)
		// 第三行空出
		sheet.AddRow()
	}

	// 数据表头
	rowTitle := []string{"排名", "销售员", "销售组别", "收入金额（元）", "小组占比", "全员占比"}
	titleRow := sheet.AddRow()
	for i := range rowTitle {
		v := titleRow.AddCell()
		v.SetString(rowTitle[i])
		v.SetStyle(style)
	}

	// 填充数据
	for k, v := range list {
		dataRow := sheet.AddRow()
		dataRow.AddCell().SetString(fmt.Sprint(k + 1))             // 排名
		dataRow.AddCell().SetString(v.SellerName)                  // 销售员
		dataRow.AddCell().SetString(v.GroupName)                   // 销售组别
		dataRow.AddCell().SetString(fmt.Sprint(v.InvoiceAmount))   // 开票金额
		dataRow.AddCell().SetString(fmt.Sprint(v.GroupRate, "%"))  // 小组占比
		dataRow.AddCell().SetString(fmt.Sprint(v.SellerRate, "%")) // 全员占比
	}

	// 输出文件
	var buffer bytes.Buffer
	_ = xlsxFile.Write(&buffer)
	content := bytes.NewReader(buffer.Bytes())
	randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
	fileName := sheetName + randStr + ".xlsx"

	c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName))
	c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content)
}