fms_api/controller/census/invoice_payment.go

package census

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

// InvoicePaymentController 商品到款统计
type InvoicePaymentController struct{}

// List
// @Title 商品到款统计列表
// @Description 商品到款统计列表
// @Param   Keyword			query	string	false	"关键词"
// @Param   SellGroupId		query	int		false	"销售组别ID"
// @Param   ServiceType		query	int		false	"套餐类型"
// @Param   StartDate		query	string	false	"合同开始日期"
// @Param   EndDate			query	string	false	"合同结束日期"
// @Param   TimeType		query	int		false	"时间类型: 1-开票时间; 2-到款时间"
// @Param   HasInvoice		query	int		false	"是否已开票: 0-否; 1-是"
// @Param   HasPayment		query	int		false	"是否已到款: 0-否; 1-是"
// @Param   IsExport		query	int		false	"是否导出: 0-否; 1-是"
// @Success 200 {object} fms.ContractRegisterItem
// @router /census/invoice_payment/list [get]
func (ct *InvoicePaymentController) List(c *gin.Context) {
	var req fms.InvoicePaymentCensusListReq
	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
	}

	cond := `1 = 1`
	pars := make([]interface{}, 0)
	// 合同编号/客户姓名/销售
	if req.Keyword != "" {
		kw := "%" + req.Keyword + "%"
		cond += ` AND b.company_name LIKE ? OR a.seller_name LIKE ?`
		pars = append(pars, kw, kw)
	}
	if req.SellGroupId > 0 {
		cond += ` AND a.seller_group_id = ?`
		pars = append(pars, req.SellGroupId)
	}
	// 套餐筛选
	if req.ServiceType != 0 {
		registerIds, e := fms.GetContractRegisterIdsByTempId(req.ServiceType)
		if e != nil {
			resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c)
			return
		}
		if len(registerIds) > 0 {
			cond += ` AND b.contract_register_id IN ?`
			pars = append(pars, registerIds)
		} else {
			cond += ` AND 1 = 2`
		}
	}
	// 开票到款日期
	if req.TimeType > 0 && req.StartDate != "" && req.EndDate != "" {
		st := fmt.Sprint(req.StartDate, " 00:00:00")
		ed := fmt.Sprint(req.EndDate, " 23:59:59")
		cond += ` AND a.invoice_type = ? AND (a.invoice_time BETWEEN ? AND ?)`
		pars = append(pars, req.TimeType, st, ed)
	}
	// 已开票
	if req.HasInvoice == 1 && req.HasPayment == 0 {
		cond += ` AND a.invoice_type = 1`
	}
	// 已到款
	if req.HasInvoice == 0 && req.HasPayment == 1 {
		cond += ` AND a.invoice_type = 2`
	}

	page := new(base.Page)
	page.SetPageSize(req.PageSize)
	page.SetCurrent(req.Current)
	page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false})
	page.AddOrderItem(base.OrderItem{Column: "b.contract_register_id", Asc: true})
	if req.IsExport == 1 {
		page.SetPageSize(10000)
		page.SetCurrent(1)
	}

	list, allRegisterIds, e := fms.GetInvoicePaymentCensusPageList(page, cond, pars)
	if e != nil {
		resp.FailMsg("获取失败", "获取商品到款统计列表失败, Err: "+e.Error(), c)
		return
	}
	total := int64(len(allRegisterIds))
	registerIds := make([]int, 0)
	for i := range list {
		registerIds = append(registerIds, list[i].ContractRegisterId)
	}

	results := new(fms.InvoicePaymentCensusResp)
	if len(registerIds) > 0 {
		// 获取开票到款列表
		ivCond := `contract_register_id IN ?`
		ivPars := make([]interface{}, 0)
		ivPars = append(ivPars, registerIds)
		iv := new(fms.ContractInvoice)
		invoiceList, e := iv.List(ivCond, ivPars, "")
		if e != nil {
			resp.FailMsg("获取失败", "获取开票到款列表失败, Err: "+e.Error(), c)
			return
		}
		// 取出开票、到款
		invoiceMap := make(map[int][]*fms.ContractInvoice, 0)
		paymentMap := make(map[int][]*fms.ContractInvoice, 0)
		paymentIds := make([]int, 0)
		for i := range invoiceList {
			if invoiceMap[invoiceList[i].ContractRegisterId] == nil {
				invoiceMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0)
			}
			if paymentMap[invoiceList[i].ContractRegisterId] == nil {
				paymentMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0)
			}
			if invoiceList[i].InvoiceType == fms.ContractInvoiceTypeMake {
				invoiceMap[invoiceList[i].ContractRegisterId] = append(invoiceMap[invoiceList[i].ContractRegisterId], invoiceList[i])
			}
			if invoiceList[i].InvoiceType == fms.ContractInvoiceTypePay {
				paymentMap[invoiceList[i].ContractRegisterId] = append(paymentMap[invoiceList[i].ContractRegisterId], invoiceList[i])
				paymentIds = append(paymentIds, invoiceList[i].ContractInvoiceId)
			}
		}

		// 合同套餐
		contractServiceCond := `contract_register_id IN ?`
		contractServicePars := make([]interface{}, 0)
		contractServicePars = append(contractServicePars, registerIds)
		contractServiceOB := new(fms.ContractService)
		contractServiceList, e := contractServiceOB.List(contractServiceCond, contractServicePars)
		if e != nil {
			resp.FailMsg("获取失败", "获取合同套餐列表失败, Err:"+e.Error(), c)
			return
		}
		contractServiceMap := make(map[int][]*fms.ContractService, 0)
		servicesNameMap := make(map[int][]string, 0)
		for i := range contractServiceList {
			if contractServiceMap[contractServiceList[i].ContractRegisterId] == nil {
				contractServiceMap[contractServiceList[i].ContractRegisterId] = make([]*fms.ContractService, 0)
			}
			contractServiceMap[contractServiceList[i].ContractRegisterId] = append(contractServiceMap[contractServiceList[i].ContractRegisterId], contractServiceList[i])
			servicesNameMap[contractServiceList[i].ContractRegisterId] = append(servicesNameMap[contractServiceList[i].ContractRegisterId], contractServiceList[i].Title)
		}

		// 到款套餐分配
		serviceAmountMap := make(map[int][]*fms.ContractPaymentServiceAmount, 0)
		if len(paymentIds) > 0 {
			serviceAmountCond := `contract_payment_id IN ?`
			serviceAmountPars := make([]interface{}, 0)
			serviceAmountPars = append(serviceAmountPars, paymentIds)
			serviceAmountOB := new(fms.ContractPaymentServiceAmount)
			serviceAmountList, e := serviceAmountOB.List(serviceAmountCond, serviceAmountPars)
			if e != nil {
				resp.FailMsg("获取失败", "获取到款套餐分配列表失败, Err:"+e.Error(), c)
				return
			}
			for i := range serviceAmountList {
				if serviceAmountMap[serviceAmountList[i].ContractRegisterId] == nil {
					serviceAmountMap[serviceAmountList[i].ContractRegisterId] = make([]*fms.ContractPaymentServiceAmount, 0)
				}
				serviceAmountMap[serviceAmountList[i].ContractRegisterId] = append(serviceAmountMap[serviceAmountList[i].ContractRegisterId], serviceAmountList[i])
			}
		}

		// 整合响应数据
		respList := make([]*fms.InvoicePaymentCensusItem, 0)
		for i := range list {
			v := new(fms.InvoicePaymentCensusItem)
			v.ContractRegisterId = list[i].ContractRegisterId
			v.CompanyName = list[i].CompanyName
			v.NewCompany = list[i].NewCompany
			v.StartDate = list[i].StartDate.Format(utils.FormatDate)
			v.EndDate = list[i].EndDate.Format(utils.FormatDate)
			svList := servicesNameMap[list[i].ContractRegisterId]
			v.ServicesName = strings.Join(svList, ",")
			// 格式化(合并)开票到款数据
			v.InvoicePaymentList = fmsService.MergeInvoiceList2InvoicePaymentCensusInfo(invoiceMap[list[i].ContractRegisterId], paymentMap[list[i].ContractRegisterId],
				contractServiceMap[list[i].ContractRegisterId], serviceAmountMap[list[i].ContractRegisterId])
			respList = append(respList, v)
		}

		// 开票到款金额合计(换算后)
		amountTotalCond := `contract_register_id IN ?`
		amountTotalPars := make([]interface{}, 0)
		amountTotalPars = append(amountTotalPars, allRegisterIds)
		amountTotalList, e := fms.GetContractInvoiceAmountTotal(cond, pars)
		if e != nil {
			resp.FailMsg("获取失败", "获取开票到款金额合计失败, Err:"+e.Error(), c)
			return
		}
		amountTotalMap := make(map[int]float64)
		for i := range amountTotalList {
			amountTotalMap[amountTotalList[i].InvoiceType] = amountTotalList[i].TotalAmount
		}
		invoiceTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypeMake]), 64)
		paymentTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypePay]), 64)

		// 分币种金额统计
		currencyOB := new(fms.CurrencyUnit)
		currencyCond := `enable = 1`
		currencyPars := make([]interface{}, 0)
		currencyList, e := currencyOB.List(currencyCond, currencyPars)
		if e != nil {
			resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c)
			return
		}
		unitMap := make(map[string]string)
		invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
		paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
		for i := range currencyList {
			unitMap[currencyList[i].Code] = currencyList[i].UnitName
			invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{
				Name:     currencyList[i].Name,
				UnitName: currencyList[i].UnitName,
				Code:     currencyList[i].Code,
				FlagImg:  currencyList[i].FlagImg,
			})
			paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{
				Name:     currencyList[i].Name,
				UnitName: currencyList[i].UnitName,
				Code:     currencyList[i].Code,
				FlagImg:  currencyList[i].FlagImg,
			})
		}
		sumList, e := fms.GetInvoiceListCurrencySum(amountTotalCond, amountTotalPars, "currency_unit, invoice_type")
		if e != nil {
			resp.FailMsg("获取失败", "获取商品到款统计货币合计金额失败, Err: "+e.Error(), c)
			return
		}
		invoiceSumMap := make(map[string]float64)
		paymentSumMap := make(map[string]float64)
		for i := range sumList {
			if sumList[i].InvoiceType == fms.ContractInvoiceTypeMake {
				invoiceSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal
				continue
			}
			if sumList[i].InvoiceType == fms.ContractInvoiceTypePay {
				paymentSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal
			}
		}
		for i := range invoiceCurrencyTotals {
			a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64)
			invoiceCurrencyTotals[i].Amount = a
		}
		for i := range paymentCurrencyTotals {
			a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64)
			paymentCurrencyTotals[i].Amount = a
		}

		results.DataList = respList
		results.InvoiceTotal = invoiceTotal
		results.PaymentTotal = paymentTotal
		results.InvoiceCurrencyTotal = invoiceCurrencyTotals
		results.PaymentCurrencyTotal = paymentCurrencyTotals
	}

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

// ExportInvoicePaymentCensusList 导出商品到款统计列表
func ExportInvoicePaymentCensusList(c *gin.Context, results *fms.InvoicePaymentCensusResp) {
	list := results.DataList
	if len(list) == 0 {
		resp.Fail("列表数据为空", c)
		return
	}

	// 生成Excel文件
	xlsxFile := xlsx.NewFile()
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}
	style.Alignment = alignment
	style.ApplyAlignment = true

	sheet, err := xlsxFile.AddSheet("商品到款统计")
	if err != nil {
		resp.FailData("新增Sheet失败", "Err:"+err.Error(), c)
		return
	}
	_ = sheet.SetColWidth(1, 1, 30)
	_ = sheet.SetColWidth(3, 3, 30)

	// 前三行-开票金额合计
	rowA := sheet.AddRow()
	cellAA := rowA.AddCell()
	cellAA.SetString(fmt.Sprintf("已开票合计金额（换算后）：%.2f（元）", results.InvoiceTotal))
	rowBData := "已开票金额："
	for _, v := range results.InvoiceCurrencyTotal {
		rowBData += fmt.Sprintf("%s%.2f(%s)  ", v.Name, v.Amount, v.UnitName)
	}
	rowB := sheet.AddRow()
	rowB.AddCell().SetString(rowBData)
	sheet.AddRow()

	// 四至六-到款金额合计
	rowD := sheet.AddRow()
	cellDA := rowD.AddCell()
	cellDA.SetString(fmt.Sprintf("已到款合计金额（换算后）：%.2f（元）", results.PaymentTotal))
	rowEData := "已到款金额："
	for _, v := range results.PaymentCurrencyTotal {
		rowEData += fmt.Sprintf("%s%.2f(%s)  ", v.Name, v.Amount, v.UnitName)
	}
	rowE := sheet.AddRow()
	rowE.AddCell().SetString(rowEData)
	sheet.AddRow()

	// 表头, 套餐动态获取
	rowTitle := []string{"序号", "客户名称", "是否新客户", "合同有效期", "开票日", "开票金额", "到款日", "到款金额", "付款方式", "销售",
		"组别"}
	serviceTempCond := ``
	serviceTempPars := make([]interface{}, 0)
	serviceTempOB := new(fms.ContractServiceTemplate)
	serviceTempList, e := serviceTempOB.List(serviceTempCond, serviceTempPars)
	if e != nil {
		resp.FailData("获取套餐模板列表失败", "Err:"+e.Error(), c)
		return
	}
	for i := range serviceTempList {
		rowTitle = append(rowTitle, serviceTempList[i].Title)
	}

	titleRow := sheet.AddRow()
	for i := range rowTitle {
		v := titleRow.AddCell()
		v.SetString(rowTitle[i])
		v.SetStyle(style)
	}

	newCompanyMap := map[int]string{0: "否", 1: "是"}
	for k, v := range list {
		dataRow := sheet.AddRow()
		// 前四个单元格根据每行开票到款条数向下合并
		l := len(v.InvoicePaymentList)
		mergeRowNum := l - 1
		// 序号
		sortNum := k + 1
		colA := dataRow.AddCell()
		colA.VMerge = mergeRowNum
		colA.SetString(fmt.Sprint(sortNum))
		// 客户名称
		colB := dataRow.AddCell()
		colB.VMerge = mergeRowNum
		colB.SetString(v.CompanyName)
		// 是否新客户
		colC := dataRow.AddCell()
		colC.VMerge = mergeRowNum
		colC.SetString(newCompanyMap[v.NewCompany])
		// 合同有效期
		colD := dataRow.AddCell()
		colD.VMerge = mergeRowNum
		colD.SetString(fmt.Sprint(v.StartDate, "至", v.EndDate))
		// 开票到款信息
		for k2, v2 := range v.InvoicePaymentList {
			rowData := []string{
				v2.InvoiceDate,                                // 开票日
				fmt.Sprint(v2.InvoiceAmount),                  // 开票金额
				v2.PaymentDate,                                // 到款日
				fmt.Sprint(v2.PaymentAmount),                  // 到款金额
				fms.ContractPaymentPayTypeNameMap[v2.PayType], // 付款方式
				v2.SellerName,                                 // 销售
				v2.SellerGroupName,                            // 组别
			}
			// 套餐金额信息
			for i := range serviceTempList {
				sa := ""
				for s2 := range v2.ServiceAmountList {
					if v2.ServiceAmountList[s2].ServiceTemplateId == serviceTempList[i].ServiceTemplateId {
						sa = fmt.Sprint(v2.ServiceAmountList[s2].Amount)
						break
					}
				}
				rowData = append(rowData, sa)
			}
			// 首行开票到款
			if k2 == 0 {
				for i := range rowData {
					dataRow.AddCell().SetString(rowData[i])
				}
				continue
			}
			// 其他行开票到款, 加四列空的单元格用于合并
			dataRowExtra := sheet.AddRow()
			for i := 0; i < 4; i++ {
				dataRowExtra.AddCell()
			}
			for i := range rowData {
				dataRowExtra.AddCell().SetString(rowData[i])
			}
		}
	}

	// 输出文件
	var buffer bytes.Buffer
	_ = xlsxFile.Write(&buffer)
	content := bytes.NewReader(buffer.Bytes())
	randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
	fileName := "商品到款统计_" + 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)
}