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"` ServiceProductId int `gorm:"column:service_product_id" json:"service_product_id" description:"套餐类型:1ficc套餐,2权益套餐"` base.TimeBase } func (c *InvoicePaymentSummary) TableName() string { return "invoice_payment_summary" } func (c *InvoicePaymentSummary) Create() (err error) { err = global.DEFAULT_MYSQL.Create(c).Error return } // 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 } type ContractRegisterSummary struct { SummaryId int `json:"summary_id" description:"汇总ID"` ContractRegister } // GetInvoicePaymentCensusPageList 获取商品到款统计列表-总数 func GetInvoicePaymentCensusPageList(page base.IPage, condition string, pars []interface{}) (results []*ContractRegisterSummary, count int64, err error) { query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a"). 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"). 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.Count(&count) 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 } 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权益套餐"` } // 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", } 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("IFNULL(SUM(b.amount),0)"). 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 } // DeleteInvoicePaymentSummaryByRegisterId 根据合同登记ID删除汇总数据 func DeleteInvoicePaymentSummaryByRegisterId(registerId int) (err error) { sql := `DELETE FROM invoice_payment_summary WHERE register_id = ?` err = global.DEFAULT_MYSQL.Exec(sql, registerId).Error return } // DeleteInvoicePaymentSummaryByInvoiceIdAndPaymentId 根据开票到款ID删除汇总数据 func DeleteInvoicePaymentSummaryByInvoiceIdAndPaymentId(invoiceId, arriveId, registerId int) (err error) { sql := `DELETE FROM invoice_payment_summary WHERE invoice_id = ? AND payment_id = ? AND register_id = ?` err = global.DEFAULT_MYSQL.Exec(sql, invoiceId, arriveId, registerId).Error 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) ` query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a"). Select("b.amount"). Joins(fmt.Sprintf(" JOIN contract_invoice AS b ON %s AND b.is_deleted = 0 ", joinCond)). Where(condition, pars...).Group("id") nq := global.DEFAULT_MYSQL.Table("(?) AS e", query). Select(" IFNULL( SUM( e.amount ), 0 ) ") err = nq.Find(&amountTotal).Error return } // GetInvoicePaymentSummaryByRegisterIdAndInvoiceId 根据合同登记ID和开票ID获取汇总数据 func GetInvoicePaymentSummaryByRegisterIdAndInvoiceId(registerId,invocieId int) (results []*ContractRegisterSummary, err error) { sql := `SELECT * FROM invoice_payment_summary WHERE register_id = ? AND invoice_id= ? ` err = global.DEFAULT_MYSQL.Exec(sql, registerId, invocieId).Find(&results).Error return }