fms_api/models/fms/invoice_payment_summary.go

package fms

import (
	"fmt"
	"hongze/fms_api/global"
	"hongze/fms_api/models/base"
	"strings"
	"time"
)

// InvoicePaymentSummary 开票到款汇总表
type InvoicePaymentSummary struct {
	Id         int `gorm:"primaryKey;column:id" json:"id" description:"汇总ID"`
	RegisterId int `gorm:"column:register_id" json:"register_id" description:"登记ID"`
	InvoiceId  int `gorm:"column:invoice_id" json:"invoice_id" description:"开票ID"`
	PaymentId  int `gorm:"column:payment_id" json:"payment_id" description:"到款ID"`
	base.TimeBase
}

func (c *InvoicePaymentSummary) TableName() string {
	return "invoice_payment_summary"
}

// DeleteAndCreate 删除并新增汇总
func (c *InvoicePaymentSummary) DeleteAndCreate(registerId int, summaryList []*InvoicePaymentSummary) (err error) {
	tx := global.DEFAULT_MYSQL.Begin()
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()

	sql := `DELETE FROM invoice_payment_summary WHERE register_id = ?`
	tx.Exec(sql, registerId)
	if len(summaryList) > 0 {
		err = tx.CreateInBatches(summaryList, len(summaryList)).Error
		if err != nil {
			return
		}
	}
	return
}

// GetInvoicePaymentCensusPageList 获取商品到款统计列表-总数
func GetInvoicePaymentCensusPageList(page base.IPage, condition string, pars []interface{}) (results []*ContractRegister, registerIds []int, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("b.*").
		Joins("JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Where(condition, pars...).
		Group("a.register_id")
	if len(page.GetOrderItemsString()) > 0 {
		query = query.Order(page.GetOrderItemsString())
	}
	err = query.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&results).Error
	if err != nil {
		return
	}
	queryCount := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("a.register_id").
		Joins("JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Where(condition, pars...).
		Group("a.register_id")
	queryCount.Find(&registerIds)

	//summaryQuery := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
	//	Select("a.id").
	//	Joins("JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0").
	//	Joins("LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0").
	//	Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
	//	Where(condition, pars...)
	//summaryQuery.Find(&summaryIds)
	//err = summaryQuery.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&summaryPageIds).Error
	//Group("a.register_id")
	return
}

type InvoicePaymentSummaryItem struct {
	SummaryId       int       `json:"summary_id" description:"汇总ID"`
	RegisterId      int       `json:"register_id" description:"登记ID"`
	CompanyName     string    `json:"company_name" description:"客户名称"`
	NewCompany      int       `json:"new_company" description:"是否为新客户: 0-否; 1-是"`
	StartDate       time.Time `json:"start_date" description:"合同开始日期"`
	EndDate         time.Time `json:"end_date" description:"合同结束日期"`
	InvoiceId       int       `json:"invoice_id" description:"开票ID"`
	InvoiceDate     time.Time `json:"invoice_time" description:"开票日期"`
	InvoiceAmount   float64   `json:"invoice_amount" description:"开票金额"`
	SellerId        int       `json:"seller_id" description:"销售ID"`
	SellerName      string    `json:"seller_name" description:"销售名称"`
	SellerGroupId   int       `json:"seller_group_id" description:"销售组别ID"`
	SellerGroupName string    `json:"seller_group_name" description:"销售组别名称"`
	PaymentId       int       `json:"payment_id" description:"到款ID"`
	PaymentDate     time.Time `json:"payment_date" description:"到款日期"`
	PaymentAmount   float64   `json:"payment_amount" description:"到款金额"`
	PayType         int       `json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
}

// GetInvoicePaymentCensusSummaryData 获取商品到款统计列表-汇总数据
func GetInvoicePaymentCensusSummaryData(condition string, pars []interface{}) (results []*InvoicePaymentSummaryItem, err error) {
	fields := []string{"a.id AS summary_id", "a.register_id", "a.invoice_id", "a.payment_id", "b.company_name", "b.start_date", "b.end_date",
		"c.origin_amount AS invoice_amount", "c.invoice_time AS invoice_date", "c.seller_id", "c.seller_name", "c.seller_group_id",
		"c.seller_group_name", "d.origin_amount AS payment_amount", "d.invoice_time AS payment_date", "d.pay_type",
	}
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select(strings.Join(fields, ",")).
		Joins("JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Where(condition, pars...)
	query.Find(&results)
	return
}

// GetInvoicePaymentCensusSummaryDataIds 获取商品到款统计列表-汇总数据IDs
func GetInvoicePaymentCensusSummaryDataIds(condition string, pars []interface{}) (summaryIds []int, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("a.id AS summary_id").
		Joins("JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Where(condition, pars...)
	query.Find(&summaryIds)
	return
}

// GetContractSummaryInvoicePaymentAmountTotal 获取汇总金额合计信息
func GetContractSummaryInvoicePaymentAmountTotal(condition string, pars []interface{}, amountType int) (amountTotal float64, err error) {
	joinCond := `a.invoice_id = b.contract_invoice_id`
	if amountType == 2 {
		joinCond = `a.payment_id = b.contract_invoice_id`
	}
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("SUM(b.amount)").
		Joins(fmt.Sprintf("JOIN contract_invoice AS b ON %s AND b.is_deleted = 0", joinCond)).
		Where(condition, pars...)
	err = query.Find(&amountTotal).Error
	return
}

// GetSummaryListCurrencySum 获取汇总分货币合计
func GetSummaryListCurrencySum(condition string, pars []interface{}, amountType int) (results []*InvoiceListCurrencySum, err error) {
	joinCond := `a.invoice_id = b.contract_invoice_id`
	if amountType == 2 {
		joinCond = `a.payment_id = b.contract_invoice_id`
	}
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("b.currency_unit, b.invoice_type, SUM(b.amount) AS amount_total, SUM(b.origin_amount) AS origin_amount_total").
		Joins(fmt.Sprintf("JOIN contract_invoice AS b ON %s AND b.is_deleted = 0", joinCond)).
		Where(condition, pars...).
		Group("b.currency_unit")
	err = query.Find(&results).Error
	return
}