easydo-echo_win7/handlers/sale_performance.go

package handlers

import (
	"fmt"
	"net/http"
	"time"

	"easydo-echo_win7/models"
	"easydo-echo_win7/services"
	"easydo-echo_win7/utils"

	"easydo-echo_win7/middleware"

	"github.com/labstack/echo/v4"
)

func Add_sale_performance_to_routes(e *echo.Echo) {
	group := e.Group("/salePerformance")
	group.Use(middleware.AuthMiddleware)
	group.POST("/getTotalPrice", salePerformanceGetTotalPrice)
	group.POST("/getEcharts", salePerformanceGetEcharts)
}

func salePerformanceGetTotalPrice(c echo.Context) error {
	var paramMap map[string]interface{}
	if err := c.Bind(&paramMap); err != nil {
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("参数解析失败", err.Error()))
	}
	tenantId := paramMap["tenantId"]

	now := time.Now()
	begin_date_by_year := time.Date(now.Year(), 1, 1, 0, 0, 0, 0, now.Location())
	end_date_by_year := time.Date(now.Year(), 12, 31, 0, 0, 0, 0, now.Location())
	begin_date_by_year_str := begin_date_by_year.Format(time.DateOnly)
	end_date_by_year_str := end_date_by_year.Format(time.DateOnly)
	begin_date_by_month := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
	end_date_by_month := time.Date(now.Year(), now.Month()+1, 1, 0, 0, 0, 0, now.Location()).AddDate(0, 0, -1)
	begin_date_by_month_str := begin_date_by_month.Format(time.DateOnly)
	end_date_by_month_str := end_date_by_month.Format(time.DateOnly)

	sale_performance := new(models.SalePerformance)

	sqlStr := fmt.Sprintf(`
	select ifnull(sum(sale_amount),0) as sale_amount from sale_plan 
	where begin_date = '%s' and end_date = '%s' and type = 'year' 
	and tenant_id =  '%s'`,
		begin_date_by_year_str, end_date_by_year_str, tenantId)
	row := services.MYSQL_DB.QueryRowx(sqlStr)
	sale_plan := new(models.SalePlan)
	err := services.JdbcClient.ScanRowToModel(row, sale_plan)
	if err != nil {
		utils.PrintSqlErr(err)
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
	}
	sale_performance.PlanSalePriceByYear = sale_plan.SaleAmount

	sqlStr = fmt.Sprintf(`
	select ifnull(sum(sale_amount),0) as sale_amount 
	from sale_plan 
	where begin_date = '%s' and end_date = '%s' 
	and type = 'month' and tenant_id =  '%s'`,
		begin_date_by_month_str, end_date_by_month_str, tenantId)
	row = services.MYSQL_DB.QueryRowx(sqlStr)
	sale_plan = new(models.SalePlan)
	err = services.JdbcClient.ScanRowToModel(row, sale_plan)
	if err != nil {
		utils.PrintSqlErr(err)
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
	}
	sale_performance.PlanSalePriceByMonth = sale_plan.SaleAmount

	sqlStr = fmt.Sprintf(`
	select ifnull(sum(actual_price),0) as actual_price 
	from sale_order 
	where order_date >= '%s' and order_date <= '%s'
	and tenant_id =  '%s'`,
		begin_date_by_year_str, end_date_by_year_str, tenantId)
	row = services.MYSQL_DB.QueryRowx(sqlStr)
	sale_order := new(models.SaleOrder)
	err = services.JdbcClient.ScanRowToModel(row, sale_order)
	if err != nil {
		utils.PrintSqlErr(err)
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
	}
	sale_performance.ActualSalePriceByYear = sale_order.ActualPrice

	sqlStr = fmt.Sprintf(`
	select ifnull(sum(actual_price),0) as actual_price 
	from sale_order 
	where order_date >= '%s' and order_date <= '%s'
	and tenant_id =  '%s'`,
		begin_date_by_month_str, end_date_by_month_str, tenantId)
	row = services.MYSQL_DB.QueryRowx(sqlStr)
	sale_order = new(models.SaleOrder)
	err = services.JdbcClient.ScanRowToModel(row, sale_order)
	if err != nil {
		utils.PrintSqlErr(err)
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
	}
	sale_performance.ActualSalePriceByMonth = sale_order.ActualPrice

	return c.JSON(http.StatusOK, sale_performance)
}

func salePerformanceGetEcharts(c echo.Context) error {
	var paramMap map[string]interface{}
	if err := c.Bind(&paramMap); err != nil {
		return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("参数解析失败", err.Error()))
	}
	sale_performance := new(models.SalePerformance)

	search_type := paramMap["type"]
	begin_date := paramMap["beginDate"]
	end_date := paramMap["endDate"]
	tenantId := paramMap["tenantId"]
	if search_type == "month" {
		sqlStr := fmt.Sprintf(`
		select left(begin_date,7) as datestr, ifnull(sum(sale_amount),0) as price 
		from sale_plan where begin_date >= '%s' and end_date <= '%s' 
		and tenant_id =  '%s'
		and type = 'month' group by begin_date`,
			begin_date, end_date, tenantId)
		rows, err := services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day := new(models.SalePriceByDay)
		result, err := services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		plan_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.PlanList = &plan_list

		sqlStr = fmt.Sprintf(`
		select left(order_date,7) as datestr, ifnull(sum(actual_price),0) as price 
		from sale_order where order_date >= '%s' and order_date <= '%s' 
		and tenant_id =  '%s'
		group by order_date`,
			begin_date, end_date, tenantId)
		rows, err = services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day = new(models.SalePriceByDay)
		result, err = services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		actual_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.ActualList = &actual_list
	} else if search_type == "quarter" {
		sqlStr := fmt.Sprintf(`
		select CONCAT(YEAR(begin_date), '-Q', QUARTER(begin_date)) as datestr, ifnull(sum(sale_amount),0) as price 
		from sale_plan where begin_date >= '%s' and end_date <= '%s' 
		and tenant_id =  '%s'
		and type = 'quarter' group by QUARTER(begin_date),YEAR(begin_date)`,
			begin_date, end_date, tenantId)
		rows, err := services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day := new(models.SalePriceByDay)
		result, err := services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		plan_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.PlanList = &plan_list

		sqlStr = fmt.Sprintf(`
		select CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)) as datestr, ifnull(sum(actual_price),0) as price 
		from sale_order where order_date >= '%s' and order_date <= '%s' 
		and tenant_id =  '%s'
		group by QUARTER(order_date),YEAR(order_date)`,
			begin_date, end_date, tenantId)
		rows, err = services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day = new(models.SalePriceByDay)
		result, err = services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		actual_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.ActualList = &actual_list
	} else if search_type == "year" {
		sqlStr := fmt.Sprintf(`
		select YEAR(begin_date) as datestr, ifnull(sum(sale_amount),0) as price 
		from sale_plan where begin_date >= '%s' and end_date <= '%s' 
		and tenant_id =  '%s'
		and type = 'year' group by YEAR(begin_date)`,
			begin_date, end_date, tenantId)
		rows, err := services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day := new(models.SalePriceByDay)
		result, err := services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		plan_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.PlanList = &plan_list

		sqlStr = fmt.Sprintf(`
		select YEAR(order_date) as datestr, ifnull(sum(actual_price),0) as price 
		from sale_order where order_date >= '%s' and order_date <= '%s' 
		and tenant_id =  '%s'
		group by YEAR(order_date)`,
			begin_date, end_date, tenantId)
		rows, err = services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day = new(models.SalePriceByDay)
		result, err = services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		actual_list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.ActualList = &actual_list
	} else if search_type == "date" {
		sqlStr := fmt.Sprintf(`
		select order_date as date, ifnull(sum(actual_price),0) as price 
		from sale_order where order_date >= '%s' and order_date <= '%s' 
		and tenant_id =  '%s'
		group by order_date`,
			begin_date, end_date, tenantId)
		rows, err := services.MYSQL_DB.Queryx(sqlStr)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		sale_price_by_day := new(models.SalePriceByDay)
		result, err := services.JdbcClient.ScanRowToList(rows, sale_price_by_day)
		if err != nil {
			utils.PrintSqlErr(err)
			return c.JSON(http.StatusInternalServerError, utils.ErrorResponse("系统错误", ""))
		}
		list := utils.ConvertInterface[[]models.SalePriceByDay](result)
		sale_performance.ActualList = &list
	}

	return c.JSON(http.StatusOK, sale_performance)
}