hz_crm_api/models/fms/invoice_payment_summary.go

package fms

import (
	"github.com/beego/beego/v2/client/orm"
	"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"`
	ServiceProductId int       `gorm:"column:service_product_id" json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	CreateTime       time.Time `gorm:"autoCreateTime;column:create_time" json:"create_time" description:"创建时间"`
	ModifyTime       time.Time `gorm:"autoUpdateTime:milli;column:modify_time" json:"modify_time" description:"最后更新时间"`
}


type ContractRegisterSummary struct {
	SummaryId int `json:"summary_id" description:"汇总ID"`
	ContractRegister
}


// GetInvoicePaymentCensusPageList 获取商品到款统计列表-总数
func GetInvoicePaymentCensusPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (results []*ContractRegisterSummary, total int64, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT b.*, a.id as summary_id, IF(a.invoice_id >0, 1,0) as sort_invoice_id, IF(a.payment_id >0, 1,0) as sort_payment_id FROM invoice_payment_summary AS a 
			JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0
			LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0
			LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0
	WHERE  `
	if condition != "" {
		sql += condition
	}
	totalSQl := `SELECT COUNT(1) total FROM (`+sql+`) as a`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY sort_invoice_id DESC`
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&results)
	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-异常"`
	ServiceProductId int       `json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	ContractAmount   float64   `gorm:"column:contract_amount" json:"contract_amount" description:"合同金额"`
	InvoicedAmount   float64   `gorm:"column:invoiced_amount" json:"invoiced_amount" description:"开票金额"`
	CurrencyUnit     string    `gorm:"column:currency_unit" json:"currency_unit" description:"货币国际代码"`
	RMBRate          float64   `gorm:"column:rmb_rate" json:"rmb_rate" description:"人民币汇率(create_time当日)"`
}

// 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", "a.service_product_id", "b.company_name", "b.start_date", "b.end_date",
		"c.origin_amount AS invoice_amount", "c.invoice_time AS invoice_date", "IF(c.seller_id > 0, c.seller_id, d.seller_id) AS seller_id", "IF(c.seller_name = '' OR c.seller_name IS NULL, d.seller_name, c.seller_name) AS seller_name", "IF(c.seller_group_id > 0,c.seller_group_id,d.seller_group_id) AS seller_group_id",
		"IF(c.seller_group_name = '' OR c.seller_group_name IS NULL, d.seller_group_name, c.seller_group_name) AS seller_group_name", "d.origin_amount AS payment_amount", "d.invoice_time AS payment_date", "d.pay_type",
	}
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT `+strings.Join(fields, ",")+ ` FROM invoice_payment_summary AS a 
			JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0
			LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0
			LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0
	WHERE `
	sql += condition

	_,err = o.Raw(sql, pars).QueryRows(&results)

	return
}

// GetInvoicePaymentCensusSummaryDataIds 获取商品到款统计列表-汇总数据IDs
func GetInvoicePaymentCensusSummaryDataIds(condition string, pars []interface{}) (summaryIds []int, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT a.id AS summary_id FROM invoice_payment_summary AS a 
			JOIN contract_register AS b ON a.register_id = b.contract_register_id AND b.is_deleted = 0
			LEFT JOIN contract_invoice AS c ON a.invoice_id = c.contract_invoice_id AND c.is_deleted = 0
			LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0
	WHERE `
	sql += condition
	_,err = o.Raw(sql, pars).QueryRows(&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`
	}
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT IFNULL(SUM(b.amount),0) FROM invoice_payment_summary AS a JOIN contract_invoice AS b ON `+ joinCond+` AND b.is_deleted = 0 WHERE `
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&amountTotal)

	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`
	}

	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT b.currency_unit, b.invoice_type, SUM(b.amount) AS amount_total, SUM(b.origin_amount) AS origin_amount_total
			FROM invoice_payment_summary AS a JOIN contract_invoice AS b ON `+ joinCond+` AND b.is_deleted = 0 WHERE `
	sql += condition

	sql += ` GROUP BY b.currency_unit `
	_,err = o.Raw(sql, pars).QueryRows(&results)

	return
}


// GetContractSummaryInvoicePaymentAmount 获取汇总金额合计信息
func GetContractSummaryInvoicePaymentAmount(condition string, pars []interface{}) (amountTotal float64, err error) {
	joinCond := ` (a.invoice_id = b.contract_invoice_id OR a.payment_id = b.contract_invoice_id) `

	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT b.amount FROM invoice_payment_summary AS a JOIN contract_invoice AS b ON `+ joinCond+` AND b.is_deleted = 0 WHERE `
	sql += condition
	sql += ` GROUP BY id `

	nsql := `SELECT IFNULL( SUM( e.amount ), 0 ) FROM (`+sql+`) as e`


	err = o.Raw(nsql, pars).QueryRow(&amountTotal)

	return
}

type InvoiceSummary struct {
	SummaryId           int     `json:"summary_id" description:"汇总ID"`
	ContractAmountCount float64 `gorm:"column:contract_amount_count" json:"contract_amount_count" description:"合同金额"`
	InvoicedAmountCount float64 `gorm:"column:invoiced_amount_count" json:"invoiced_amount_count" description:"开票金额"`
	ContractRegister
}