gd_management/impl/accounting/bill_export_excel.go

package accounting

import (
	"context"
	"gd_management/apis"
	"gd_management/errors"
	"encoding/json"
	"fmt"
	"os"
	"strconv"
	"strings"
	"time"

	"gd_management/common.in/config"
	"gd_management/common.in/utils"

	"git.getensh.com/common/gopkgsv2/id"

	"github.com/aliyun/aliyun-oss-go-sdk/oss"
	"github.com/astaxie/beego/orm"
	"github.com/tealeg/xlsx"
	"go.uber.org/zap"
)

const defaultLine = 10

func AddSpaceRow(row *xlsx.Row, num int) {
	if num == 0 {
		num = defaultLine
	}
	allTitleStyle := xlsx.NewStyle()
	allFill := *xlsx.NewFill("solid", "FFFFFF", "FFFFFF")
	allTitleStyle.Fill = allFill
	for i := 0; i < num; i++ {
		row.AddCell().SetStyle(allTitleStyle)
	}
}

func BillExportExcel(ctx context.Context, req *apis.BillExportExcelReq, reply *apis.BillExportExcelReply) error {
	o := orm.NewOrm()

	if req.MerchantId == 0 || req.Month == "" || req.BillId == 0 {
		return errors.ArgsError
	}
	//sql := "select t1.id as bill_detail_id,t1.month,t5.data_api_name,t1.merchant_data_api_id,t2.alias,t1.total_count,t1.valid_count,t1.charge_count,t1.amount,t2.unit_price,t1.remark from t_gd_bill_detail as t1 left join t_gd_merchant_data_api t2 on t1.merchant_data_api_id=t2.id left join t_gd_data_api_query_type as t4 on t4.id=t2.query_type_id left join t_gd_data_api as t5 on t5.id=t4.data_api_id where t1.month=? and t2.merchant_id=?"
	sql := "select t1.id as bill_detail_id,t1.month,t5.data_api_name,t1.merchant_data_api_id,t1.alias,t1.total_count,t1.valid_count,t1.charge_count,t1.amount,t1.unit_price,t1.remark from t_gd_bill_detail as t1 left join t_gd_merchant_data_api t2 on t1.merchant_data_api_id=t2.id left join t_gd_data_api_query_type as t4 on t4.id=t2.query_type_id left join t_gd_data_api as t5 on t5.id=t4.data_api_id where t1.month=? and t1.merchant_id=?"

	_, err := o.Raw(sql, req.Month, req.MerchantId).QueryRows(&reply.List)
	if err != nil {
		l.Error("func",
			zap.String("call", "MerchantConsumeList"),
			zap.String("args", utils.MarshalJsonString(req)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}

	name := ""

	sql1 := "select merchant_name from t_gd_merchants where id=?"

	err = o.Raw(sql1, req.MerchantId).QueryRow(&name)
	if err != nil {
		l.Error("func",
			zap.String("call", sql1),
			zap.String("args", utils.MarshalJsonString(req)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}

	// 1 生成excel
	// 生成一个新的文件
	id, _ := id.GetUniqueID()
	fileName := fmt.Sprintf("field-download-%d-%s.xlsx", id, name+"对账单("+req.Month+")")

	file := xlsx.NewFile()
	allFill := *xlsx.NewFill("solid", "FFFFFF", "FFFFFF")
	allTitleStyle := xlsx.NewStyle()
	allTitleStyle.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
	}
	allTitleFont := *xlsx.NewFont(14, "微软雅黑")
	allTitleFont.Bold = true
	allTitleStyle.Font = allTitleFont
	allTitleStyle.Fill = allFill
	//allTitleStyle.Fill.BgColor = "rgb(255, 255, 255)"
	// 添加sheet页

	sheet, _ := file.AddSheet(name + "对账单(" + req.Month + ")")
	sheet.SetColWidth(1, 1, 10)
	sheet.SetColWidth(2, 2, 25)
	sheet.SetColWidth(3, 5, 15)
	sheet.SetColWidth(6, 6, 17)
	sheet.SetColWidth(7, 7, 18)

	// 插入标题
	allTitleRow := sheet.AddRow()
	allTitle := allTitleRow.AddCell()
	for i := 0; i < 6; i++ {
		allTitleRow.AddCell().SetStyle(allTitleStyle)
	}
	allTitle.HMerge = 6
	allTitleRow2 := sheet.AddRow()
	for i := 0; i < 6; i++ {
		allTitleRow2.AddCell().SetStyle(allTitleStyle)
	}
	allTitle.VMerge = 1
	allTitle.Value = name + "对账单(" + req.Month + ")"
	allTitle.SetStyle(allTitleStyle)
	allTitleRow.SetHeight(30)

	AddSpaceRow(allTitleRow, 0)
	AddSpaceRow(allTitleRow2, defaultLine+1)
	// 插入表头
	titleRow := sheet.AddRow()
	titleRow.SetHeight(20)

	titleStyle := xlsx.NewStyle()
	titleStyle.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
	}
	titleFont := *xlsx.NewFont(12, "微软雅黑")
	titleStyle.Font = titleFont
	titleStyle.ApplyFill = true
	titleFill := *xlsx.NewFill("solid", "BDD7EE", "DEDEDE")
	titleStyle.Fill = titleFill
	titleStyle.ApplyBorder = true
	titleBorder := *xlsx.NewBorder("thin", "thin", "thin", "thin")
	titleStyle.Border = titleBorder

	cell1 := titleRow.AddCell()
	cell1.Value = "统计时间"
	cell1.SetStyle(titleStyle)

	cell2 := titleRow.AddCell()
	cell2.Value = "产品"
	cell2.SetStyle(titleStyle)

	cell3 := titleRow.AddCell()
	cell3.Value = "总查次"
	cell3.SetStyle(titleStyle)

	cell4 := titleRow.AddCell()
	cell4.Value = "有效查次"
	cell4.SetStyle(titleStyle)

	cell5 := titleRow.AddCell()
	cell5.Value = "实际计费查次"
	cell5.SetStyle(titleStyle)

	cell6 := titleRow.AddCell()
	cell6.Value = "单价"
	cell6.SetStyle(titleStyle)

	cell7 := titleRow.AddCell()
	cell7.Value = "消费金额"
	cell7.SetStyle(titleStyle)
	AddSpaceRow(titleRow, 0)

	for index, _ := range reply.List {
		if reply.List[index].Remark != "" {
			tmp := []apis.BillDetailRemarkInfo{}
			err = json.Unmarshal([]byte(reply.List[index].Remark), &tmp)
			if err == nil {
				for index1, _ := range tmp {
					tmp[index1].Alias = reply.List[index].Alias
					tmp[index1].DataApiName = reply.List[index].DataApiName
				}
				reply.RemarkList = append(reply.RemarkList, tmp...)
			}
		}
	}

	remark := ""
	err = o.Raw("select remark,amount,balance,bill_create_time from t_gd_bill where id=?", req.BillId).QueryRow(&remark, &reply.Amount, &reply.Balance, &reply.BillCreateTime)
	if err != nil {
		l.Error("func",
			zap.String("call", "BillDetailList"),
			zap.String("args", utils.MarshalJsonString(req)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}

	if remark != "" {
		json.Unmarshal([]byte(remark), &reply.BillRemarkList)
	}

	apiStyle := xlsx.NewStyle()
	apiStyle.Alignment = xlsx.Alignment{
		Horizontal: "left",
		Vertical:   "center",
	}
	apiFont := *xlsx.NewFont(10, "微软雅黑")
	apiStyle.Font = apiFont
	apiStyle.ApplyBorder = true
	apiStyle.Border = titleBorder

	monthStyle := xlsx.NewStyle()
	monthStyle.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
	}
	monthStyle.Font = apiFont
	monthStyle.ApplyBorder = true
	monthStyle.Border = titleBorder

	numStyle := xlsx.NewStyle()
	numStyle.Alignment = xlsx.Alignment{
		Horizontal: "right",
		Vertical:   "center",
	}
	numStyle.Font = apiFont
	numStyle.ApplyBorder = true
	numStyle.Border = titleBorder

	num1Style := xlsx.NewStyle()
	num1Style.Alignment = xlsx.Alignment{
		Horizontal: "right",
		Vertical:   "center",
	}
	num1Style.Font = apiFont

	num1Style.ApplyBorder = true
	num1Style.Border = titleBorder

	for _, v := range reply.List {
		dataRow := sheet.AddRow()
		dataRow.SetHeight(20)
		month := dataRow.AddCell()
		month.Value = v.Month
		month.SetStyle(monthStyle)
		dataApiName := dataRow.AddCell()
		if len(v.Alias) > 0 {
			dataApiName.Value = v.Alias
		} else {
			dataApiName.Value = v.DataApiName
		}
		dataApiName.SetStyle(apiStyle)
		totalCount := dataRow.AddCell()
		totalCount.SetInt(v.TotalCount)
		totalCount.SetStyle(num1Style)
		validCount := dataRow.AddCell()
		validCount.SetInt(v.ValidCount)
		validCount.SetStyle(num1Style)
		chargeCount := dataRow.AddCell()
		chargeCount.SetInt(v.ChargeCount)
		chargeCount.SetStyle(num1Style)
		unitPrice := dataRow.AddCell()
		unitPrice.Value = fmt.Sprintf("%.2f", v.UnitPrice)
		unitPrice.SetStyle(numStyle)
		unitPrice.NumFmt = `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`
		amount := dataRow.AddCell()
		amount.Value = fmt.Sprintf("%.2f", v.Amount)
		amount.SetStyle(numStyle)
		amount.NumFmt = `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`
		AddSpaceRow(dataRow, 0)
	}

	r1 := sheet.AddRow()

	AddSpaceRow(r1, defaultLine+7)
	num3Style := xlsx.NewStyle()
	num3Style.Alignment = xlsx.Alignment{
		Horizontal: "right",
		Vertical:   "center",
	}
	num3Style.Font = apiFont
	num3Style.ApplyBorder = true
	num3Style.Fill = allFill
	infoStyle := xlsx.NewStyle()
	infoStyle.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
	}

	infoStyle.Font = apiFont
	infoStyle.Fill = allFill
	infoStyle.ApplyBorder = true
	//infoStyle.Border = titleBorder

	info := sheet.AddRow()
	for i := 0; i < 5; i++ {
		tmpStyle := xlsx.NewStyle()
		tmpStyle.Fill = allFill
		info.AddCell().SetStyle(tmpStyle)
	}
	sumAmountName := info.AddCell()
	sumAmountName.Value = "合计"
	sumAmountName.SetStyle(infoStyle)
	sumAmount := info.AddCell()
	sumAmount.Value = fmt.Sprintf("%.2f", reply.Amount)
	sumAmount.NumFmt = `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`
	sumAmount.SetStyle(num3Style)
	AddSpaceRow(info, 0)

	info2 := sheet.AddRow()
	for i := 0; i < 5; i++ {
		tmpStyle := xlsx.NewStyle()
		tmpStyle.Fill = allFill
		info2.AddCell().SetStyle(tmpStyle)
	}
	balanceName := info2.AddCell()
	balanceName.Value = "账户余额"
	balanceName.SetStyle(infoStyle)
	balance := info2.AddCell()
	balance.Value = fmt.Sprintf("%.2f", reply.Balance)
	balance.NumFmt = `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`
	balance.SetStyle(num3Style)
	AddSpaceRow(info2, 0)

	if (len(reply.RemarkList) + len(reply.BillRemarkList)) > 0 {
		r2 := sheet.AddRow()
		AddSpaceRow(r2, defaultLine+7)
		/*r2Title := r2.AddCell()
		r2Title.HMerge = 6
		r2Title.Value = ""
		r2Title.SetStyle(allTitleStyle)
		AddSpaceRow(r2, 0)*/
		r3 := sheet.AddRow()
		/*AddSpaceRow(r3, 6)
		r3Title := r3.AddCell()
		r3Title.HMerge = 6
		r3Title.Value = ""
		r3Title.SetStyle(allTitleStyle)*/
		AddSpaceRow(r3, defaultLine+7)
		// 插入表头
		remarkRow := sheet.AddRow()
		remarkRow.SetHeight(15)

		cell10 := remarkRow.AddCell()
		for i := 0; i < 6; i++ {
			remarkRow.AddCell().SetStyle(titleStyle)
		}
		cell10.HMerge = 6
		cell10.Value = "备注"
		cell10.SetStyle(titleStyle)
		AddSpaceRow(remarkRow, 0)
		remarkStyle := xlsx.NewStyle()
		remarkStyle.Alignment = xlsx.Alignment{
			Horizontal: "left",
			Vertical:   "center",
		}
		remarkFont := *xlsx.NewFont(10, "arial")
		remarkStyle.Font = remarkFont
		remarkStyle.Fill = allFill
		remarkStyle.ApplyBorder = true
		remarkStyle.Border = titleBorder

		timeStyle := xlsx.NewStyle()
		timeStyle.Alignment = xlsx.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		}
		timeFont := *xlsx.NewFont(10, "arial")
		timeStyle.Font = timeFont
		timeStyle.Fill = allFill
		timeStyle.ApplyBorder = true
		timeStyle.Border = titleBorder

		for i := 0; i < len(reply.RemarkList); i++ {
			dataRow := sheet.AddRow()
			dataRow.SetHeight(15)
			remark1 := dataRow.AddCell()
			for i := 0; i < 5; i++ {
				dataRow.AddCell().SetStyle(remarkStyle)

			}
			remark1.HMerge = 5
			if reply.RemarkList[i].Alias != "" {
				if reply.RemarkList[i].After < reply.RemarkList[i].Befor {
					remark1.Value = reply.RemarkList[i].Alias + "产品核减" + strconv.Itoa(reply.RemarkList[i].Befor-reply.RemarkList[i].After) + "次(核定前" + strconv.Itoa(reply.RemarkList[i].Befor) + "次，核定后" + strconv.Itoa(reply.RemarkList[i].After) + "次)"
				} else {
					remark1.Value = reply.RemarkList[i].Alias + "产品增加" + strconv.Itoa(reply.RemarkList[i].After-reply.RemarkList[i].Befor) + "次(核定前" + strconv.Itoa(reply.RemarkList[i].Befor) + "次，核定后" + strconv.Itoa(reply.RemarkList[i].After) + "次)"
				}
			} else {
				if reply.RemarkList[i].After < reply.RemarkList[i].Befor {
					remark1.Value = reply.RemarkList[i].DataApiName + "产品核减" + strconv.Itoa(reply.RemarkList[i].Befor-reply.RemarkList[i].After) + "次(核定前" + strconv.Itoa(reply.RemarkList[i].Befor) + "次，核定后" + strconv.Itoa(reply.RemarkList[i].After) + "次)"
				} else {
					remark1.Value = reply.RemarkList[i].DataApiName + "产品增加" + strconv.Itoa(reply.RemarkList[i].After-reply.RemarkList[i].Befor) + "次(核定前" + strconv.Itoa(reply.RemarkList[i].Befor) + "次，核定后" + strconv.Itoa(reply.RemarkList[i].After) + "次)"
				}
			}
			remark1.SetStyle(remarkStyle)
			timeT := dataRow.AddCell()
			timeT.Value = reply.RemarkList[i].Time
			timeT.SetStyle(timeStyle)
			AddSpaceRow(dataRow, 0)
		}

		for i := 0; i < len(reply.BillRemarkList); i++ {
			dataRow := sheet.AddRow()
			billRemark := dataRow.AddCell()
			for i := 0; i < 5; i++ {
				dataRow.AddCell().SetStyle(remarkStyle)
			}
			billRemark.HMerge = 5
			billRemark.Value = reply.BillRemarkList[i]
			billRemark.SetStyle(remarkStyle)
			blank := dataRow.AddCell()
			blank.SetStyle(remarkStyle)
			AddSpaceRow(dataRow, 0)
		}
	}

	spaceRow := sheet.AddRow()
	AddSpaceRow(spaceRow, defaultLine+7)

	endStyle := xlsx.NewStyle()
	endStyle.Alignment = xlsx.Alignment{
		Horizontal: "right",
		Vertical:   "center",
	}
	endStyle.Font = allTitleFont
	endStyle.Fill = allFill

	r4 := sheet.AddRow()
	for i := 0; i < 5; i++ {
		tmpStyle := xlsx.NewStyle()
		tmpStyle.Fill = allFill
		r4.AddCell().SetStyle(tmpStyle)
	}
	r4Title := r4.AddCell()
	r4Title.HMerge = 1
	r4Title.Value = "成都深云智能科技有限公司"
	r4Title.SetStyle(endStyle)
	AddSpaceRow(r4, defaultLine+1)
	r5 := sheet.AddRow()
	for i := 0; i < 5; i++ {
		tmpStyle := xlsx.NewStyle()
		tmpStyle.Fill = allFill
		r5.AddCell().SetStyle(tmpStyle)
	}
	r5Title := r5.AddCell()
	r5Title.HMerge = 1
	s, _ := time.Parse("2006-01-02", reply.BillCreateTime)
	r5Title.Value = strconv.Itoa(s.Year()) + "年" + strconv.Itoa(int(s.Month())) + "月" + strconv.Itoa(s.Day()) + "日"
	r5Title.SetStyle(endStyle)
	AddSpaceRow(r5, defaultLine+1)

	for i := 0; i < 20; i++ {
		space := sheet.AddRow()
		AddSpaceRow(space, defaultLine+7)
	}

	err = file.Save(fileName)
	if err != nil {
		return err
	}

	// 3 upload to oss ,得到文件地址
	reply.Url, err = UploadOss(fileName, fileName)

	// 删除本地文件
	go os.Remove(fileName)

	return nil
}

func ossGetUrl() string {
	endpoint := strings.Replace(config.Conf.Oss.EndPoint, "https://", "", -1)
	return "https://" + config.Conf.Oss.Bucket + "." + endpoint
}

// UploadOss 上传本地文件到oss
func UploadOss(source, file string) (string, error) {
	client, err := oss.New(config.Conf.Oss.EndPoint, config.Conf.Oss.AccessKeyId, config.Conf.Oss.AccessKeySecret)
	if err != nil {
		by, _ := json.Marshal(config.Conf.Oss)
		l.Error("oss",
			zap.String("call", "New"),
			zap.String("params", string(by)),
			zap.String("error", err.Error()))
		return "", err
	}

	bucket, err := client.Bucket(config.Conf.Oss.Bucket)
	if err != nil {
		l.Error("oss",
			zap.String("call", "Bucket"),
			zap.String("params", config.Conf.Oss.Bucket),
			zap.String("error", err.Error()))
		return "", err
	}

	err = bucket.PutObjectFromFile(file, source)
	if err != nil {
		l.Error("oss",
			zap.String("call", "PutObjectFromFile"),
			zap.String("params", source),
			zap.String("error", err.Error()))
		return "", err
	}

	filePath := ossGetUrl() + "/" + file

	return filePath, nil
}