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"`
	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:"开票日期"`
	InvoiceOriginAmount float64   `json:"invoice_origin_amount" 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:"到款日期"`
	PaymentOriginAmount float64   `json:"payment_origin_amount" 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当日)"`
	UnitName            string    `json:"unit_name" description:"单位名称"`
}

// 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_origin_amount", "c.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.amount AS payment_amount", "d.origin_amount AS payment_origin_amount", "d.invoice_time AS payment_date", "d.pay_type", "b.currency_unit",
	}
	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, IFNULL(SUM(b.amount),0) AS amount_total, IFNULL(SUM(b.origin_amount),0) 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) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("IF(a.invoice_id=0,d.amount, b.amount) AS amount").
		Joins("LEFT JOIN contract_invoice AS b ON a.invoice_id = b.contract_invoice_id AND b.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("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) (result *InvoicePaymentSummary, err error) {
	sql := `SELECT * FROM invoice_payment_summary WHERE register_id = ? AND invoice_id= ? `
	err = global.DEFAULT_MYSQL.Raw(sql, registerId, invocieId).First(&result).Error
	return
}

type IncomeSummaryItem struct {
	CompanyName     string    `json:"company_name" description:"客户名称"`
	ContractType    int       `json:"contract_type" description:"是否为新客户: 2-否; 1-是"`
	InvoiceDate     time.Time `json:"invoice_time" description:"开票日期"`
	OriginAmount    float64   `json:"origin_amount" description:"原始金额"`
	Amount          float64   `json:"amount" description:"金额"`
	SellerName      string    `json:"seller_name" description:"销售名称"`
	FinalSellerId   int       `json:"final_seller_id" description:"最终销售ID"`
	SellerGroupId   int       `json:"seller_group_id" description:"销售分组ID"`
	SellerGroupName int       `json:"seller_group_name" description:"销售分组名称"`
	CurrencyUnit    string    `json:"currency_unit" description:"货币单位"`
	ServicesName    string    `json:"services" description:"套餐名称"`
}

// GetContractSummaryIncomeAmount 获取汇总金额合计信息
func GetContractSummaryIncomeAmount(condition string, pars []interface{}) (results []*IncomeSummaryItem, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("IF(a.invoice_id=0,d.amount, b.amount) AS amount,"+
			"IF(a.invoice_id=0,d.origin_amount, b.origin_amount) AS origin_amount,"+
			"IF(a.invoice_id=0,d.invoice_time, b.invoice_time) AS invoice_date,"+
			"IF(a.invoice_id=0,d.currency_unit, b.currency_unit) AS currency_unit,"+
			" c.contract_type,c.company_name,"+
			"IF(a.invoice_id=0,d.seller_name, b.seller_name) AS seller_name,"+
			"IF(a.invoice_id=0,d.seller_id, b.seller_id) AS final_seller_id,"+
			"IF(a.invoice_id=0,d.seller_group_id, b.seller_group_id) AS seller_group_id,"+
			"GROUP_CONCAT(s.title) AS services_name ").
		Joins("LEFT JOIN contract_invoice AS b ON a.invoice_id = b.contract_invoice_id AND b.is_deleted = 0 ").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_service AS s ON s.contract_register_id = c.contract_register_id").
		Where(condition, pars...).Group("id").Order("invoice_date ")
	//nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
	//	Select(" IFNULL( SUM( e.amount ), 0 ) ")

	err = query.Find(&results).Error
	return
}

type InvoiceSummary struct {
	SummaryId           int     `json:"summary_id" description:"汇总ID"`
	PaymentAmountCount  float64 `gorm:"column:payment_amount_count" json:"payment_amount_count" description:"到款金额"`
	InvoicedAmountCount float64 `gorm:"column:invoiced_amount_count" json:"invoiced_amount_count" description:"开票金额"`
	InvoiceOrigin       float64 `gorm:"column:invoice_origin" json:"invoice_origin" description:"开票原始金额"`
	PaymentOrigin       float64 `gorm:"column:payment_origin" json:"payment_origin" description:"到款原始金额"`
	ServiceProductId    int     `json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	ContractRegister
}

// GetInvoiceCensusPageList 获取未开票统计列表-总数
func GetInvoiceCensusPageList(page base.IPage, condition string, pars []interface{}) (results []*InvoiceSummary, count int64, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_register AS b").
		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, COALESCE(SUM( c.origin_amount ),0) AS invoiced_amount_count,COALESCE(SUM( d.origin_amount ),0) AS payment_amount_count ").
		Joins("left JOIN invoice_payment_summary AS a 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("contract_register_id")

	nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
		Select("*").Where(" (e.invoiced_amount_count <> e.contract_amount AND e.contract_amount <> 0) OR (e.contract_amount = 0 AND e.invoiced_amount_count <> e.payment_amount_count)")
	nq.Count(&count)
	if len(page.GetOrderItemsString()) > 0 {
		nq = nq.Order(page.GetOrderItemsString())
	}
	err = nq.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&results).Error
	if err != nil {
		return
	}
	return
}

// GetNoPaymentCensusPageList 获取开票未到款统计计列表-总数
func GetNoPaymentCensusPageList(page base.IPage, condition string, pars []interface{}) (results []*InvoiceSummary, 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, COALESCE(SUM( c.amount ),0) AS invoiced_amount_count,COALESCE(SUM( d.amount ),0) AS payment_amount_count "+
			" ,c.invoice_time ", "c.origin_amount AS summary_invoice_amount", "d.origin_amount AS summary_payment_amount", "a.invoice_id", "a.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...).Group("id")

	nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
		Select("*").Where(" e.invoiced_amount_count <> 0 AND e.invoice_id <> 0 AND ( e.summary_invoice_amount > e.summary_payment_amount OR e.summary_payment_amount IS NULL ) ")
	nq.Count(&count)
	if len(page.GetOrderItemsString()) > 0 {
		nq = nq.Order(page.GetOrderItemsString())
	}
	err = nq.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&results).Error
	if err != nil {
		return
	}
	return
}

// NotInvoicePaymentCensusResp 未开票到款到款统计响应体
type NotInvoicePaymentCensusResp struct {
	DataList                []*NotPaymentCensusItem     `json:"data_list"`
	InvoiceTotal            float64                     `json:"invoice_total" description:"开票总金额(换算后)"`
	PaymentTotal            float64                     `json:"payment_total" description:"到款总金额(换算后)"`
	InvoiceCurrencyTotal    []*InvoiceListCurrencyTotal `json:"invoice_currency_total" description:"开票-分币种总金额"`
	PaymentCurrencyTotal    []*InvoiceListCurrencyTotal `json:"payment_currency_total" description:"到款-分币种总金额"`
	NotInvoiceTotal         float64                     `json:"not_invoice_total" description:"未开票总金额(换算后)"`
	NotInvoiceCurrencyTotal []*InvoiceListCurrencyTotal `json:"not_invoice_currency_total" description:"未开票-分币种总金额"`
	NotPaymentTotal         float64                     `json:"not_payment_total" description:"未开票总金额(换算后)"`
	NotPaymentCurrencyTotal []*InvoiceListCurrencyTotal `json:"not_payment_currency_total" description:"未开票-分币种总金额"`
}

type IncomeSummaryItemList []*IncomeSummaryItem

func (c IncomeSummaryItemList) Len() int {
	return len(c)
}

func (c IncomeSummaryItemList) Less(i, j int) bool {
	return c[i].InvoiceDate.Before(c[j].InvoiceDate)
}

func (c IncomeSummaryItemList) Swap(i, j int) {
	c[i], c[j] = c[j], c[i]
}

type NotInvoicePaymentSummaryItem struct {
	SummaryId                    int       `json:"summary_id" description:"汇总ID"`
	RegisterId                   int       `json:"register_id" description:"登记ID"`
	NewCompany                   int       `json:"new_company" description:"是否为新客户: 0-否; 1-是"`
	InvoiceId                    int       `json:"invoice_id" description:"开票ID"`
	InvoiceDate                  time.Time `json:"invoice_time" description:"开票日期"`
	InvoiceAmount                float64   `json:"invoice_amount" description:"开票金额"`
	InvoiceOriginAmount          float64   `json:"invoice_origin_amount" description:"开票原始金额"`
	SellerGroupId                int       `json:"seller_group_id" description:"销售组别ID"`
	SellerGroupName              string    `json:"seller_group_name" description:"销售组别名称"`
	RaiSellerGroupId             int       `json:"rai_seller_group_id" description:"销售组别ID"`
	RaiSellerGroupName           string    `json:"rai_seller_group_name" description:"销售组别名称"`
	PaymentId                    int       `json:"payment_id" description:"到款ID"`
	PaymentDate                  time.Time `json:"payment_date" description:"到款日期"`
	PaymentAmount                float64   `json:"payment_amount" description:"到款金额"`
	PaymentOriginAmount          float64   `json:"payment_origin_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当日)"`
	PaymentAmountTotal           float64   `gorm:"column:payment_amount_total" json:"payment_amount_total" description:"合同金额"`
	InvoiceAmountTotal           float64   `gorm:"column:invoice_amount_total" json:"invoice_amount_total" description:"开票金额"`
	NotInvoicedAmountOriginTotal float64   ` json:"not_invoiced_amount_origin_total" description:"未开票金额"`
	NotInvoicedAmountTotal       float64   ` json:"not_invoiced_amount_total" description:"未开票转换金额"`
	SellerType                   string    `json:"seller_type" description:"销售类型"`
	ContractRegister
}

// GetNotInvoicePaymentCensusSummaryData 获取商品到款统计列表-汇总数据
func GetNotInvoicePaymentCensusSummaryData(condition string, pars []interface{}) (results []*NotInvoicePaymentSummaryItem, err error) {
	fields := []string{"a.id AS summary_id", "a.register_id", "a.invoice_id", "a.payment_id", "a.service_product_id", "b.*",
		"c.origin_amount AS invoice_amount", "c.invoice_time AS invoice_date", "d.origin_amount AS payment_amount", "d.invoice_time AS payment_date",
		"d.pay_type", "COALESCE(SUM( c.origin_amount ),0) AS invoice_amount_total", "COALESCE(SUM( d.origin_amount ),0) AS payment_amount_total",
	}
	query := global.DEFAULT_MYSQL.Table("contract_register AS b").
		Select(strings.Join(fields, ",")).
		Joins("left JOIN invoice_payment_summary AS a 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,service_product_id")
	query.Find(&results)
	return
}

// GetNoInvoicePaymentCensusData
func GetNoInvoicePaymentCensusData(condition string, pars []interface{}) (results []*NotInvoicePaymentSummaryItem, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_register AS b").
		Select("*").
		Where(condition, pars...).Group("contract_register_id")
	query.Find(&results)
	return
}

// GetInvoiceCensusList 获取未开票统计列表
func GetInvoiceCensusList(condition string, pars []interface{}) (results []*InvoiceSummary, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_register AS b").
		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, COALESCE(SUM( c.origin_amount ),0) AS invoiced_amount_count,COALESCE(SUM( d.origin_amount ),0) AS payment_amount_count,a.service_product_id,c.origin_amount AS invoice_origin,d.origin_amount AS payment_origin ").
		Joins("left JOIN invoice_payment_summary AS a 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("contract_register_id")

	nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
		Select("*").Where(" (e.invoiced_amount_count <> e.contract_amount AND e.contract_amount <> 0) OR (e.contract_amount = 0 AND e.invoiced_amount_count <> e.payment_amount_count) ")

	err = nq.Find(&results).Error
	if err != nil {
		return
	}
	return
}

// UpdateInvoicePaymentSummaryByRegisterId
func UpdateInvoicePaymentSummaryByRegisterId(newId int, registerIds []int) (err error) {
	sql := `UPDATE invoice_payment_summary SET register_id = ? WHERE register_id IN (?) `
	err = global.DEFAULT_MYSQL.Exec(sql, newId, registerIds).Error
	return
}

// GetContractSummaryPaymentIncomeAmount 获取汇总金额合计信息-到款收入统计
func GetContractSummaryPaymentIncomeAmount(condition string, pars []interface{}) (results []*IncomeSummaryItem, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("d.amount AS amount,d.invoice_time AS invoice_date,"+
			" c.contract_type,c.company_name,d.seller_name,"+
			" d.seller_id AS final_seller_id, d.seller_group_id AS seller_group_id").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Where(condition, pars...).Group("id").Order("invoice_date ")
	//nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
	//	Select(" IFNULL( SUM( e.amount ), 0 ) ")

	err = query.Find(&results).Error
	return
}

type IncomeSummaryRespItem struct {
	CompanyName   string  `json:"company_name" description:"客户名称"`
	ContractType  int     `json:"contract_type" description:"是否为新客户: 2-否; 1-是"`
	InvoiceDate   string  `json:"invoice_time" description:"开票日期"`
	OriginAmount  float64 `json:"origin_amount" description:"原始金额"`
	Amount        float64 `json:"amount" description:"金额"`
	SellerName    string  `json:"seller_name" description:"销售名称"`
	FinalSellerId int     `json:"final_seller_id" description:"最终销售ID"`
	UnitName      string  `json:"unit_name" description:"货币单位名称"`
	ServicesName  string  `json:"services_name" description:"套餐名称"`
}

// GetContractSummaryPaymentIncomeAmountPage 获取汇总金额合计信息-到款收入统计
func GetContractSummaryPaymentIncomeAmountPage(condition string, pars []interface{}, page *base.Page) (results []*IncomeSummaryItem, count int64, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("d.origin_amount, d.amount AS amount,d.currency_unit, d.invoice_time AS invoice_date,"+
			" c.contract_type,c.company_name,d.seller_name,"+
			" d.seller_id AS final_seller_id").
		Joins("JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Where(condition, pars...).Group("id")
	//nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
	//	Select(" IFNULL( SUM( e.amount ), 0 ) ")
	query.Count(&count)

	query = global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("d.origin_amount, d.amount AS amount,d.currency_unit, d.invoice_time AS invoice_date,"+
			" c.contract_type,c.company_name,d.seller_name,"+
			" d.seller_id AS final_seller_id, GROUP_CONCAT(s.title) AS services_name ").
		Joins("JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_service AS s ON s.contract_register_id = c.contract_register_id").
		Where(condition, pars...).Group("id")
	if len(page.GetOrderItemsString()) > 0 {
		query = query.Order(page.GetOrderItemsString())
	}
	err = query.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&results).Error
	return
}

// GetContractSummaryIncomeAmountPage 获取汇总金额合计信息-开票到款收入统计
func GetContractSummaryIncomeAmountPage(condition string, pars []interface{}, page *base.Page) (results []*IncomeSummaryItem, count int64, err error) {
	query := global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("IF(a.invoice_id=0,d.amount, b.amount) AS amount,"+
			"IF(a.invoice_id=0,d.origin_amount, b.origin_amount) AS origin_amount,"+
			"IF(a.invoice_id=0,d.invoice_time, b.invoice_time) AS invoice_date,"+
			"IF(a.invoice_id=0,d.currency_unit, b.currency_unit) AS currency_unit,"+
			" c.contract_type,c.company_name,"+
			"IF(a.invoice_id=0,d.seller_name, b.seller_name) AS seller_name,"+
			"IF(a.invoice_id=0,d.seller_id, b.seller_id) AS final_seller_id,"+
			"IF(a.invoice_id=0,d.seller_group_id, b.seller_group_id) AS seller_group_id").
		Joins("LEFT JOIN contract_invoice AS b ON a.invoice_id = b.contract_invoice_id AND b.is_deleted = 0 ").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Where(condition, pars...).Group("id")
	//nq := global.DEFAULT_MYSQL.Table("(?) AS e", query).
	//	Select(" IFNULL( SUM( e.amount ), 0 ) ")
	query.Count(&count)

	query = global.DEFAULT_MYSQL.Table("invoice_payment_summary AS a").
		Select("IF(a.invoice_id=0,d.amount, b.amount) AS amount,"+
			"IF(a.invoice_id=0,d.origin_amount, b.origin_amount) AS origin_amount,"+
			"IF(a.invoice_id=0,d.invoice_time, b.invoice_time) AS invoice_date,"+
			"IF(a.invoice_id=0,d.currency_unit, b.currency_unit) AS currency_unit,"+
			" c.contract_type,c.company_name,"+
			"IF(a.invoice_id=0,d.seller_name, b.seller_name) AS seller_name,"+
			"IF(a.invoice_id=0,d.seller_id, b.seller_id) AS final_seller_id,"+
			"IF(a.invoice_id=0,d.seller_group_id, b.seller_group_id) AS seller_group_id,"+
			"GROUP_CONCAT(s.title) AS services_name ").
		Joins("LEFT JOIN contract_invoice AS b ON a.invoice_id = b.contract_invoice_id AND b.is_deleted = 0 ").
		Joins("LEFT JOIN contract_invoice AS d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Joins("JOIN contract_register AS c ON a.register_id = c.contract_register_id AND c.is_deleted = 0").
		Joins("LEFT JOIN contract_service AS s ON s.contract_register_id = c.contract_register_id").
		Where(condition, pars...).Group("id")
	if len(page.GetOrderItemsString()) > 0 {
		query = query.Order(page.GetOrderItemsString())
	}
	err = query.Limit(int(page.GetPageSize())).Offset(int(page.Offset())).Find(&results).Error
	return
}

// GetPaymentCensusSummaryDataIds 获取商品到款统计列表-汇总数据IDs
func GetPaymentCensusSummaryDataIds(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 d ON a.payment_id = d.contract_invoice_id AND d.is_deleted = 0").
		Where(condition, pars...)
	query.Find(&summaryIds)
	return
}