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
}

type ContractRegisterRaiData struct {
	CompanyName   string  `gorm:"column:company_name" json:"company_name" description:"客户名称"`
	ContractCode  string  `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	RaiSellerName string  `gorm:"column:rai_seller_name" json:"rai_seller_name" description:"CRM系统-权益销售名称"`
	RaiSellerId   int     `gorm:"column:rai_seller_id" json:"rai_seller_id"  description:"CRM系统-权益销售ID"`
	Amount        float64 `json:"amount" description:"分配金额"`
	InvoiceTime   string  `json:"invoice_time" description:"开票日期"`
	InvoiceType   int     `json:"invoice_type" description:"1-开票登记; 2-到款登记"`
	ContractType  int     `gorm:"column:contract_type" json:"contract_type" description:"合同类型: 1-新签; 2-续约; 3-代付; 4-补充协议"`
}

// 列表
func GetContractRegisterList(condition string, pars []interface{}, startSize, pageSize int) (items []*ContractRegisterRaiData, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT 
    		a.company_name,
			a.rai_seller_name,
			a.contract_code,
			b.amount,
			b.invoice_time 
		 FROM contract_register  as a 
         INNER JOIN contract_invoice as b ON a.contract_register_id = b.contract_register_id  
         WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	if startSize+pageSize > 0 {
		sql += ` LIMIT ?,?  `
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	} else {
		_, err = o.Raw(sql, pars).QueryRows(&items)
	}
	return
}

// 获取开票金额与未到账金额的统计
func GetContractRegisterAmountList(condition string, pars []interface{}) (items []*ContractRegisterSummary, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT
			SUM( invoiced_amount ) AS invoiced_amount,
			SUM( payment_amount ) AS payment_amount FROM contract_register  as a  WHERE 1= 1  `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 列表
func GetContractInvoiceList(condition string, pars []interface{}, startSize, pageSize int) (items []*ContractInvoice, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT * FROM contract_invoice  as a  WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	if startSize+pageSize > 0 {
		sql += ` LIMIT ?,?  `
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	} else {
		_, err = o.Raw(sql, pars).QueryRows(&items)
	}
	return
}

// 获取数量
func GetContractInvoiceCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("fms")
	sqlCount := ` SELECT COUNT(1) AS count  FROM contract_register as a
                  INNER JOIN contract_invoice as b ON a.contract_register_id = b.contract_register_id  
                  WHERE 1= 1  `
	if condition != "" {
		sqlCount += condition
	}
	err = o.Raw(sqlCount, pars).QueryRow(&count)
	return
}

// 获取开票金额与未到账金额的统计
func GetContractInvoiceAmountCount(condition string, pars []interface{}) (amount float64, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT
			SUM( amount ) AS amount FROM contract_register  as a
			INNER JOIN contract_invoice as b ON a.contract_register_id = b.contract_register_id  
			WHERE 1= 1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&amount)
	return
}

// GetContractRegisterListByStartDate  根据起始日期的时间段筛选合同登记信息。
func GetContractRegisterListByStartDate(startDate, endDate string) (results []*ContractRegisterRaiData, err error) {
	o := orm.NewOrmUsingDB("fms")
	sql := `SELECT 
			a.rai_seller_id,
			a.contract_type,
			b.amount,
			b.invoice_time,
			b.invoice_type
		 FROM contract_register  as a 
         INNER JOIN contract_invoice as b ON a.contract_register_id = b.contract_register_id    AND a.is_deleted = 0  AND  b.invoice_time >= ?  AND  b.invoice_time <= ?    `
	_, err = o.Raw(sql, startDate, endDate).QueryRows(&results)
	return
}