fms_api/models/fms/contract_invoice.go

package fms

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

// ContractInvoice 合同开票表
type ContractInvoice struct {
	ContractInvoiceId  int       `gorm:"primaryKey;column:contract_invoice_id" json:"contract_invoice_id" description:"开票ID"`
	ContractRegisterId int       `gorm:"column:contract_register_id" json:"contract_register_id" description:"登记ID"`
	ContractCode       string    `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	Amount             float64   `gorm:"column:amount" json:"amount" description:"换算后的金额(人民币)"`
	OriginAmount       float64   `gorm:"column:origin_amount" json:"origin_amount" description:"开票/到款金额"`
	CurrencyUnit       string    `gorm:"column:currency_unit" json:"currency_unit" description:"货币国际代码"`
	InvoiceType        int       `gorm:"column:invoice_type" json:"invoice_type" description:"类型: 1-开票登记; 2-到款登记"`
	InvoiceDate        time.Time `gorm:"column:invoice_time" json:"invoice_time" description:"开票日期/到款月"`
	SellerId           int       `gorm:"column:seller_id" json:"seller_id" description:"销售ID"`
	SellerName         string    `gorm:"column:seller_name" json:"seller_name" description:"销售名称"`
	SellerGroupId      int       `gorm:"column:seller_group_id" json:"seller_group_id" description:"销售分组ID"`
	SellerGroupName    string    `gorm:"column:seller_group_name" json:"seller_group_name" description:"销售分组名称"`
	SellerTeamId       int       `gorm:"column:seller_team_id" json:"seller_team_id" description:"销售小组ID"`
	SellerTeamName     string    `gorm:"column:seller_team_name" json:"seller_team_name" description:"销售小组名称"`
	PayType            int       `gorm:"column:pay_type" json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
	AdminId            int       `gorm:"column:admin_id" json:"admin_id" description:"操作人ID"`
	AdminName          string    `gorm:"column:admin_name" json:"admin_name" description:"操作人姓名"`
	Remark             string    `gorm:"column:remark" json:"remark" description:"备注信息"`
	IsDeleted          int       `gorm:"column:is_deleted" json:"is_deleted" description:"是否已删除: 0-正常; 1-已删除"`
	ServiceProductId   int       `gorm:"column:service_product_id" json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	IsPrePay           int       `gorm:"column:is_pre_pay" json:"is_pre_pay" description:"是否预付款: 0-不是; 1-是"`
	StartDate          time.Time `gorm:"column:start_date" json:"start_date" description:"约定开始时间"`
	EndDate            time.Time `gorm:"column:end_date" json:"end_date" description:"约定结束时间"`
	PreRegisterId      int       `gorm:"column:pre_register_id" json:"pre_register_id" description:"预登记ID"`
	base.TimeBase
}

func (c *ContractInvoice) TableName() string {
	return "contract_invoice"
}

// ContractInvoiceItem 合同开票/到款
type ContractInvoiceItem struct {
	ContractInvoiceId  int     `gorm:"column:contract_invoice_id" json:"contract_invoice_id" description:"开票ID"`
	ContractRegisterId int     `gorm:"column:contract_register_id" json:"contract_register_id" description:"登记ID"`
	ContractCode       string  `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	ContractStatus     int     `json:"contract_status" description:"合同状态: 1-已审批; 2-单章寄出; 3-已签回"`
	Amount             float64 `gorm:"column:amount" json:"amount" description:"换算金额"`
	OriginAmount       float64 `gorm:"column:origin_amount" json:"origin_amount" description:"开票/到款金额"`
	CurrencyUnit       string  `gorm:"column:currency_unit" json:"currency_unit" description:"货币国际代码"`
	UnitName           string  `json:"unit_name" description:"货币单位名称"`
	InvoiceType        int     `gorm:"column:invoice_type" json:"invoice_type" description:"类型: 1-开票登记; 2-到款登记;3-开票预登记;4-到款预登记"`
	InvoiceDate        string  `gorm:"column:invoice_time" json:"invoice_time" description:"开票日期/到款月"`
	SellerId           int     `gorm:"column:seller_id" json:"seller_id" description:"销售ID"`
	SellerName         string  `gorm:"column:seller_name" json:"seller_name" description:"销售名称"`
	PayType            int     `gorm:"column:pay_type" json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
	Remark             string  `gorm:"column:remark" json:"remark" description:"备注信息"`
	ServiceProductId   int     `gorm:"column:service_product_id" json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	IsPrePay           int     `gorm:"column:is_pre_pay" json:"is_pre_pay" description:"是否预付款: 0-不是; 1-是"`
	StartDate          string  `gorm:"column:start_date" json:"start_date" description:"约定开始时间"`
	EndDate            string  `gorm:"column:end_date" json:"end_date" description:"约定结束时间"`
	CreateTime         string  `gorm:"column:create_time" json:"create_time" description:"创建时间"`
	CompanyName        string  `gorm:"column:company_name" json:"company_name" description:"客户名称"`
}

func (c *ContractInvoice) Create() (err error) {
	err = global.DEFAULT_MYSQL.Create(c).Error
	return
}

func (c *ContractInvoice) AddInBatches(list []*ContractInvoice) (err error) {
	err = global.DEFAULT_MYSQL.CreateInBatches(list, len(list)).Error
	return
}

func (c *ContractInvoice) Update(updateCols []string) (err error) {
	err = global.DEFAULT_MYSQL.Model(c).Select(updateCols).Updates(c).Error
	return
}

func (c *ContractInvoice) Fetch(id int) (item *ContractInvoice, err error) {
	err = global.DEFAULT_MYSQL.Model(c).Where("is_deleted = 0 AND contract_invoice_id = ?", id).First(&item).Error
	return
}

// 删除
func (c *ContractInvoice) Delete() (err error) {
	err = global.DEFAULT_MYSQL.Delete(c).Error
	return
}

func (c *ContractInvoice) Sum(field, condition string, pars []interface{}) (total float64, err error) {
	totalList := make([]float64, 0)
	err = global.DEFAULT_MYSQL.Table("contract_invoice AS a").Joins("INNER JOIN contract_register AS b ON a.contract_register_id = b.contract_register_id ").
		Where("a.is_deleted = 0").
		Where(condition, pars...).
		Pluck(field, &totalList).Error
	for i := range totalList {
		total += totalList[i]
	}
	return
}

func (c *ContractInvoice) List(condition string, pars []interface{}, orderRule string) (list []*ContractInvoice, err error) {
	list = make([]*ContractInvoice, 0)
	query := global.DEFAULT_MYSQL.Model(c).
		Where("is_deleted = 0").
		Where(condition, pars...)
	if orderRule != "" {
		query.Order(orderRule)
	} else {
		query.Order("contract_invoice_id ASC")
	}
	err = query.Find(&list).Error
	return
}

func (c *ContractInvoice) PageList(page base.IPage, condition string, pars []interface{}) (count int64, results []*ContractInvoice, err error) {
	results = make([]*ContractInvoice, 0)
	query := global.DEFAULT_MYSQL.Model(c).
		Where("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
	return
}

// DeleteAndCreateNewInvoice 删除并新增登记
func (c *ContractInvoice) DeleteAndCreateNewInvoice(contractRegisterId, invoiceType int, deleteInvoiceIds []int, invoices []*ContractInvoice) (err error) {
	tx := global.DEFAULT_MYSQL.Begin()
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()

	if len(deleteInvoiceIds) > 0 {
		err = tx.Model(c).
			Where("contract_register_id = ? AND invoice_type = ? AND contract_invoice_id IN (?)", contractRegisterId, invoiceType, deleteInvoiceIds).
			UpdateColumn("is_deleted", 1).Error
		if err != nil {
			return
		}
	}
	if len(invoices) > 0 {
		err = tx.CreateInBatches(invoices, len(invoices)).Error
		if err != nil {
			return
		}
	}
	return
}

// ContractInvoiceSaveReq 合同开票-请求体
type ContractInvoiceSaveReq struct {
	ContractRegisterId int                        `json:"contract_register_id" binding:"required,gte=1" description:"登记ID"`
	InvoiceType        int                        `json:"invoice_type" binding:"oneof=1 2 3" description:"类型: 1-开票登记; 2-到款登记; 3-预到款登记"`
	AmountList         []*ContractInvoiceSaveItem `json:"amount_list"`
}

// ContractInvoiceSaveItem 合同开票数据
type ContractInvoiceSaveItem struct {
	ServiceProductId int     `json:"service_product_id" binding:"oneof=1 2" description:"套餐类型：1ficc套餐，2权益套餐"`
	InvoiceId        int     `json:"invoice_id" description:"开票ID"`
	OriginAmount     float64 `json:"origin_amount" description:"开票(到款)金额"`
	Amount           float64 `json:"amount" description:"换算后的金额"`
	InvoiceDate      string  `json:"invoice_date" description:"开票日期/到款月"`
	Remark           string  `json:"remark" description:"备注"`
	SellerId         int     `json:"seller_id" description:"销售ID"`
}

// GetContractInvoiceItemList 获取开票到款列表
func GetContractInvoiceItemList(condition string, pars []interface{}) (itemList []*ContractInvoiceItem, err error) {
	list := make([]*ContractInvoice, 0)
	err = global.DEFAULT_MYSQL.Model(ContractInvoice{}).
		Where("is_deleted = 0").
		Where(condition, pars...).
		Order("contract_invoice_id ASC").
		Find(&list).Error
	if err != nil {
		return
	}
	itemList = formatContractInvoice2ItemList(list)
	return
}

// formatContractInvoice2ItemList 格式化ContractInvoice
func formatContractInvoice2ItemList(list []*ContractInvoice) (itemList []*ContractInvoiceItem) {
	itemList = make([]*ContractInvoiceItem, 0)
	for i := range list {
		itemList = append(itemList, &ContractInvoiceItem{
			ContractInvoiceId:  list[i].ContractInvoiceId,
			ContractRegisterId: list[i].ContractRegisterId,
			ContractCode:       list[i].ContractCode,
			Amount:             list[i].Amount,
			OriginAmount:       list[i].OriginAmount,
			CurrencyUnit:       list[i].CurrencyUnit,
			InvoiceType:        list[i].InvoiceType,
			InvoiceDate:        utils.TimeTransferString(utils.FormatDate, list[i].InvoiceDate),
			SellerId:           list[i].SellerId,
			SellerName:         list[i].SellerName,
			PayType:            list[i].PayType,
			Remark:             list[i].Remark,
			ServiceProductId:   list[i].ServiceProductId,
			IsPrePay:           list[i].IsPrePay,
			StartDate:          list[i].StartDate.Format(utils.FormatDate),
			EndDate:            list[i].StartDate.Format(utils.FormatDate),
			CreateTime:         utils.TimeTransferString(utils.FormatDateTime, list[i].CreateTime),
		})
	}
	return
}

// ContractInvoiceListReq 合同开票/到款登记列表请求体
type ContractInvoiceListReq struct {
	InvoiceType      int     `json:"invoice_type" form:"invoice_type" binding:"oneof=1 2" description:"类型: 1-开票登记; 2-到款登记"`
	SellerIds        string  `json:"seller_ids" form:"seller_ids" description:"销售ID， 多个id用逗号拼接"`
	Keyword          string  `json:"keyword" form:"keyword" binding:"omitempty" description:"搜索关键字"`
	StartDate        string  `json:"start_date" form:"start_date" binding:"omitempty,datetime=2006-01-02" description:"开始日期"`
	EndDate          string  `json:"end_date" form:"end_date" binding:"omitempty,datetime=2006-01-02" description:"结束日期"`
	MinAmount        float64 `json:"min_amount" form:"min_amount" description:"开票金额区间-最小值"`
	MaxAmount        float64 `json:"max_amount" form:"max_amount" description:"开票金额区间-最大值"`
	IsExport         int     `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"`
	ServiceProductId int     `json:"service_product_id" form:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	base.PageReq
}

// GetContractInvoiceItemPageList 获取合同开票/到款列表-分页
func GetContractInvoiceItemPageList(page base.IPage, condition string, pars []interface{}) (count int64, results []*ContractInvoiceItem, err error) {
	list := make([]*ContractInvoiceRespItem, 0)
	query := global.DEFAULT_MYSQL.Table("contract_invoice AS a").
		Select("a.*, b.company_name").
		Joins("JOIN contract_register b ON a.contract_register_id = b.contract_register_id").
		Where("a.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(&list).Error
	if err != nil {
		return
	}
	for i := range list {
		results = append(results, formatContractInvoice2Item(list[i]))
	}
	return
}

// formatContractInvoice2Item 格式化ContractInvoiceItem
func formatContractInvoice2Item(item *ContractInvoiceRespItem) (formatItem *ContractInvoiceItem) {
	formatItem = new(ContractInvoiceItem)
	formatItem.ContractInvoiceId = item.ContractInvoiceId
	formatItem.ContractRegisterId = item.ContractRegisterId
	formatItem.ContractCode = item.ContractCode
	formatItem.Amount = item.Amount
	formatItem.OriginAmount = item.OriginAmount
	formatItem.CurrencyUnit = item.CurrencyUnit
	formatItem.InvoiceType = item.InvoiceType
	formatItem.InvoiceDate = utils.TimeTransferString(utils.FormatDate, item.InvoiceDate)
	formatItem.SellerId = item.SellerId
	formatItem.SellerName = item.SellerName
	formatItem.PayType = item.PayType
	formatItem.Remark = item.Remark
	formatItem.IsPrePay = item.IsPrePay
	formatItem.ServiceProductId = item.ServiceProductId
	formatItem.CreateTime = utils.TimeTransferString(utils.FormatDateTime, item.CreateTime)
	formatItem.CompanyName = item.CompanyName
	return
}

// DeleteContractInvoicesByRegisterId 根据合同登记ID删除开票/到款记录
func DeleteContractInvoicesByRegisterId(registerId int) (err error) {
	sql := `UPDATE contract_invoice SET is_deleted = 1 WHERE contract_register_id = ?`
	err = global.DEFAULT_MYSQL.Exec(sql, registerId).Error
	return
}

// InvoicePaymentCensusListReq 商品到款统计列表请求体
type InvoicePaymentCensusListReq struct {
	Keyword      string `json:"keyword" form:"keyword" binding:"omitempty" description:"关键词"`
	SellerIds    string `json:"seller_ids" form:"seller_ids" description:"销售ID，多个ID之间用逗号拼接"`
	ServiceTypes string `json:"service_types" form:"service_types" description:"套餐类型, 多个套餐之间用逗号拼接"`
	StartDate    string `json:"start_date" form:"start_date" binding:"omitempty,datetime=2006-01-02" description:"开始日期"`
	EndDate      string `json:"end_date" form:"end_date" binding:"omitempty,datetime=2006-01-02" description:"结束日期"`
	HasInvoice   string `json:"has_invoice" form:"has_invoice" description:"是否已开票: 0-否; 1-是; 空-全部"`
	HasPayment   string `json:"has_payment" form:"has_payment" description:"是否已到款: 0-否; 1-是; 空-全部"`
	IsExport     int    `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"`
	ListParam    int    `json:"list_param" form:"list_param" description:"套餐类型: 0-全部; 1-ficc; 2-权益"`
	SortParam    string `json:"sort_param" form:"sort_param" description:"排序字段参数，用来排序的字段， 枚举值：'invoice_time':开票日 、 'payment_date':到款日"`
	SortType     string `json:"sort_type" form:"sort_type" description:"如何排序，是正序还是倒序，枚举值：asc 正序,desc 倒叙"`
	TimeType     int    `json:"time_type" form:"time_type" description:"时间类型: 1-开票时间; 2-到款时间；3-开票时间&到款时间"`
	base.PageReq
}

// GetInvoicePaymentCensusPageListV2 获取商品到款统计列表-分页
func GetInvoicePaymentCensusPageListV2(page base.IPage, condition string, pars []interface{}) (results []*ContractRegister, registerIds []int, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_invoice AS a").
		Select("b.*").
		Joins("JOIN contract_register AS b ON a.contract_register_id = b.contract_register_id").
		Where("a.is_deleted = 0 AND b.is_deleted = 0").
		Where(condition, pars...).
		Group("b.contract_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("contract_invoice AS a").
		Select("b.contract_register_id").
		Joins("JOIN contract_register AS b ON a.contract_register_id = b.contract_register_id").
		Where("a.is_deleted = 0 AND b.is_deleted = 0").
		Where(condition, pars...).
		Group("b.contract_register_id")
	queryCount.Find(&registerIds)
	return
}

// InvoicePaymentCensusResp 商品到款统计响应体
type InvoicePaymentCensusResp struct {
	DataList             []*InvoicePaymentCensusItem `json:"data_list"`
	InvoiceTotal         float64                     `json:"invoice_total" description:"开票总金额(换算后)"`
	PaymentTotal         float64                     `json:"payment_total" description:"到款总金额(换算后)"`
	AmountTotal          float64                     `json:"amount_total" description:"合计总金额(换算后)"`
	InvoiceCurrencyTotal []*InvoiceListCurrencyTotal `json:"invoice_currency_total" description:"开票-分币种总金额"`
	PaymentCurrencyTotal []*InvoiceListCurrencyTotal `json:"payment_currency_total" description:"到款-分币种总金额"`
}

// InvoicePaymentCensusItem 商品到款统计信息
type InvoicePaymentCensusItem struct {
	SummaryId          int                         `json:"summary_id" description:"汇总ID"`
	ContractRegisterId int                         `json:"contract_register_id" description:"登记ID"`
	CompanyName        string                      `json:"company_name" description:"客户名称"`
	NewCompany         int                         `json:"new_company" description:"是否为新客户: 0-否; 1-是"`
	StartDate          string                      `json:"start_date" description:"合同开始日期"`
	EndDate            string                      `json:"end_date" description:"合同结束日期"`
	ServicesName       string                      `json:"services_name" description:"套餐信息字符串拼接"`
	ContractType       int                         `json:"contract_type" description:"合同类型: 0-无 1-新签; 2-续约"`
	ActualPayCompanies string                      `json:"actual_pay_companies" description:"该合同关联的所有代付方, 英文逗号拼接"`
	InvoicePaymentList []*InvoicePaymentCensusInfo `json:"invoice_payment_list" description:"开票到款列表"`
	ContractCode       string                      `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	ContractAmount     float64                     `gorm:"column:contract_amount" json:"contract_amount" description:"合同金额"`
	UnitName           string                      `gorm:"unit_name" json:"unit_name" description:"单位名称"`
	ProductIds         string                      `gorm:"column:product_ids" json:"product_ids" description:"产品ID：1-FICC; 2-权益, 如果两者都有，则用英文逗号拼接"`
	NotInvoiceTotal    float64                     `json:"not_invoice_total" description:"未开票总金额(换算后)"`
}

// InvoicePaymentCensusInfo 开票到款统计信息
type InvoicePaymentCensusInfo struct {
	InvoiceId         int                                 `json:"invoice_id" description:"开票ID"`
	InvoiceDate       string                              `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       string                              `json:"payment_date" description:"到款日期"`
	PaymentAmount     float64                             `json:"payment_amount" description:"到款金额"`
	PayType           int                                 `json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
	ServiceAmountList []*ContractPaymentServiceAmountItem `json:"service_amount_list" description:"到款套餐金额分配信息"`
	SellerType        string                              `json:"seller_type" description:"销售类型：1ficc销售，2权益销售"`
}

// ContractInvoiceAmountTotal 开票到款金额合计信息
type ContractInvoiceAmountTotal struct {
	InvoiceType int     `json:"invoice_type" description:"类型: 1-开票; 2-到款"`
	TotalAmount float64 `json:"total_amount" description:"金额合计"`
}

// GetContractInvoiceAmountTotal 获取开票到款金额合计信息
func GetContractInvoiceAmountTotal(condition string, pars []interface{}) (results []*ContractInvoiceAmountTotal, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_invoice").
		Select("invoice_type, SUM(amount) AS total_amount").
		Where("is_deleted = 0").
		Where(condition, pars...).
		Group("invoice_type")
	err = query.Find(&results).Error
	return
}

// UpdatePaymentPayTypeReq 到款登记-修改付款方式请求体
type UpdatePaymentPayTypeReq struct {
	ContractPaymentId int `json:"contract_payment_id" binding:"required,gte=1" description:"到款登记ID"`
	PayType           int `json:"pay_type" binding:"oneof=1 2 3 4" description:"付款方式: 1-年付; 2-半年付; 3-季付; 4-次付"`
}

// CensusSellerGroupInvoiceListReq 销售组开票统计列表请求体
type CensusSellerGroupInvoiceListReq struct {
	StartDate  string `json:"start_date" form:"start_date" binding:"omitempty,datetime=2006-01-02" description:"开始日期"`
	EndDate    string `json:"end_date" form:"end_date" binding:"omitempty,datetime=2006-01-02" description:"结束日期"`
	SortField  int    `json:"sort_field" form:"sort_field" description:"排序字段: 1-开票金额; 2-组别占比"`
	SortType   int    `json:"sort_type" form:"sort_type" description:"排序方式: 1-正序; 2-倒序"`
	IsExport   int    `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"`
	SellerType int    `json:"seller_type" form:"seller_type" description:"销售类型：1ficc销售，2权益销售"`
	CompanyType int    `json:"company_type" form:"company_type" description:"客户类型 0全部 1新客户 2老客户"`
	base.PageReq
}

// CensusSellerGroupInvoiceItem 销售组开票统计数据
type CensusSellerGroupInvoiceItem struct {
	GroupId       int     `json:"group_id" description:"分组ID"`
	GroupName     string  `json:"group_name" description:"分组名称"`
	InvoiceAmount float64 `json:"invoice_amount" description:"开票金额"`
	GroupRate     float64 `json:"group_rate" description:"分组占比"`
}

// GetCensusSellerGroupInvoicePageList 获取销售组开票统计列表-分页
func GetCensusSellerGroupInvoicePageList(page base.IPage, condition, outCond string, pars []interface{}, amountSum float64) (results []*CensusSellerGroupInvoiceItem, err error) {
	tableName := `weekly_report.sys_group`
	if global.CONFIG.Serve.RunMode == "debug" {
		tableName = `test_v2_weekly_report.sys_group`
	}
	sql := `SELECT a.group_id, a.group_name, b.invoice_amount, b.group_rate FROM %s AS a LEFT JOIN `
	sql += ` (
				SELECT
					seller_group_id,
					seller_group_name,
					SUM(amount) AS invoice_amount,
					SUM(amount) / %f AS group_rate
				FROM
					contract_invoice
				WHERE
					%s
				GROUP BY
					seller_group_id
			) AS b ON a.group_id = b.seller_group_id`
	sql += ` WHERE %s ORDER BY %s LIMIT %d,%d`
	sql = fmt.Sprintf(sql, tableName, amountSum, condition, outCond, page.GetOrderItemsString(), page.Offset(), page.GetPageSize())
	err = global.DEFAULT_MYSQL.Raw(sql, pars...).Scan(&results).Error
	return
}

// CensusSellerInvoiceListReq 销售开票统计列表请求体
type CensusSellerInvoiceListReq struct {
	GroupId    int    `json:"group_id" form:"group_id" description:"销售组别ID"`
	StartDate  string `json:"start_date" form:"start_date" binding:"omitempty,datetime=2006-01-02" description:"开始日期"`
	EndDate    string `json:"end_date" form:"end_date" binding:"omitempty,datetime=2006-01-02" description:"结束日期"`
	SortField  int    `json:"sort_field" form:"sort_field" description:"排序字段: 1-开票金额; 2-小组占比; 3-全员占比"`
	SortType   int    `json:"sort_type" form:"sort_type" description:"排序方式: 1-正序; 2-倒序"`
	IsExport   int    `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"`
	SellerType int    `json:"seller_type" form:"seller_type" description:"销售类型：1ficc销售，2权益销售"`
	ShowResign bool   `json:"show_resign" form:"show_resign" description:"是否显示离职销售"`
	CompanyType int    `json:"company_type" form:"company_type" description:"客户类型 0全部 1新客户 2老客户"`
	base.PageReq
}

// CensusSellerInvoiceItem 销售组开票统计数据
type CensusSellerInvoiceItem struct {
	SellerId      int     `json:"seller_id" description:"销售ID"`
	SellerName    string  `json:"seller_name" description:"销售名称"`
	GroupId       int     `json:"group_id" description:"分组ID"`
	GroupName     string  `json:"group_name" description:"分组名称"`
	InvoiceAmount float64 `json:"invoice_amount" description:"开票金额"`
	GroupRate     float64 `json:"group_rate" description:"分组占比"`
	SellerRate    float64 `json:"seller_rate" description:"全员占比"`
}

// GetCensusSellerInvoicePageList 获取销售开票统计列表-分页
func GetCensusSellerInvoicePageList(page base.IPage, condition, outCond string, pars []interface{}, amountSum float64) (results []*CensusSellerInvoiceItem, err error) {
	tableName := `weekly_report.admin`
	if global.CONFIG.Serve.RunMode == "debug" {
		tableName = `test_v2_weekly_report.admin`
	}
	sql := `SELECT a.admin_id AS seller_id, a.real_name AS seller_name, a.group_id, b.invoice_amount, b.group_rate, b.seller_rate FROM %s AS a LEFT JOIN `
	sql += ` (
				SELECT c.seller_id, c.seller_group_id, SUM(c.amount) AS invoice_amount,
				SUM(c.amount) / (
					SELECT SUM(amount) FROM contract_invoice WHERE seller_group_id = c.seller_group_id AND %s
				) AS group_rate,
				SUM(c.amount) / %f AS seller_rate
				FROM contract_invoice AS c
				WHERE %s
				GROUP BY c.seller_id
			) AS b ON a.admin_id = b.seller_id `
	sql += ` WHERE %s ORDER BY %s LIMIT %d,%d`
	sql = fmt.Sprintf(sql, tableName, condition, amountSum, condition, outCond, page.GetOrderItemsString(), page.Offset(), page.GetPageSize())
	err = global.DEFAULT_MYSQL.Raw(sql, pars...).Scan(&results).Error
	return
}

// InvoiceListRespData 开票/到款列表响应体
type InvoiceListRespData struct {
	Page          *base.Page                  `json:"page"`
	List          interface{}                 `json:"list"`
	AmountTotal   float64                     `json:"amount_total"`
	CurrencyTotal []*InvoiceListCurrencyTotal `json:"currency_total"`
}

// InvoiceListCurrencyTotal 开票/到款列表分币种合计信息
type InvoiceListCurrencyTotal struct {
	Name     string  `json:"name" description:"货币名称"`
	UnitName string  `json:"unit_name" description:"单位名称"`
	Code     string  `json:"code" description:"国际代码"`
	Amount   float64 `json:"amount" description:"金额"`
	FlagImg  string  `json:"flag_img" description:"国旗图标"`
}

// InvoiceListCurrencySum 开票/到款列表分币种总和
type InvoiceListCurrencySum struct {
	CurrencyUnit      string  `json:"currency_unit" description:"货币代码"`
	InvoiceType       int     `json:"invoice_type" description:"开票类型：1-开票; 2-到款"`
	AmountTotal       float64 `json:"amount_total" description:"换算后合计金额"`
	OriginAmountTotal float64 `json:"origin_amount_total" description:"原合计金额"`
}

// GetInvoiceListCurrencySum 获取开票/到款分货币合计
func GetInvoiceListCurrencySum(condition string, pars []interface{}, groupRule string) (results []*InvoiceListCurrencySum, err error) {
	query := global.DEFAULT_MYSQL.Table("contract_invoice AS a").
		Select("a.currency_unit, a.invoice_type, SUM(a.amount) AS amount_total, SUM(a.origin_amount) AS origin_amount_total, b.company_name").
		Joins("JOIN contract_register b ON a.contract_register_id = b.contract_register_id").
		Where("a.is_deleted = 0").
		Where(condition, pars...).
		Group(groupRule)
	err = query.Find(&results).Error
	return
}

// PreRegisterListReq 预登记列表请求体
type PreRegisterListReq struct {
	Keyword string `json:"keyword" form:"keyword" binding:"omitempty" description:"关键词"`
	base.PageReq
}

// ContractInvoiceDetailItem 预登记列表详情用-invoice_time换register_time
type ContractInvoiceDetailItem struct {
	ContractInvoiceId  int     `gorm:"column:contract_invoice_id" json:"contract_invoice_id" description:"开票ID"`
	ContractRegisterId int     `gorm:"column:contract_register_id" json:"contract_register_id" description:"登记ID"`
	ContractCode       string  `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	ContractStatus     int     `json:"contract_status" description:"合同状态: 1-已审批; 2-单章寄出; 3-已签回"`
	OriginAmount       float64 `gorm:"column:amount" json:"amount" description:"开票/到款金额"`
	CurrencyUnit       string  `gorm:"column:currency_unit" json:"currency_unit" description:"货币国际代码"`
	UnitName           string  `json:"unit_name" description:"货币单位名称"`
	InvoiceType        int     `gorm:"column:invoice_type" json:"invoice_type" description:"类型: 1-开票登记; 2-到款登记;3-开票预登记;4-到款预登记"`
	InvoiceDate        string  `gorm:"column:register_date" json:"register_date" description:"开票日期/到款月"`
	SellerId           int     `gorm:"column:seller_id" json:"seller_id" description:"销售ID"`
	SellerName         string  `gorm:"column:seller_name" json:"seller_name" description:"销售名称"`
	PayType            int     `gorm:"column:pay_type" json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
	Remark             string  `gorm:"column:remark" json:"remark" description:"备注信息"`
	ServiceProductId   int     `gorm:"column:service_product_id" json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	IsPrePay           int     `gorm:"column:is_pre_pay" json:"is_pre_pay" description:"是否预付款: 0-不是; 1-是"`
	StartDate          string  `gorm:"column:start_date" json:"start_date" description:"约定开始时间"`
	EndDate            string  `gorm:"column:end_date" json:"end_date" description:"约定结束时间"`
	CreateTime         string  `gorm:"column:create_time" json:"create_time" description:"创建时间"`
	PreRegisterId      int     `gorm:"column:pre_register_id" json:"pre_register_id" description:"预登记ID"`
	HasInvoiceSeller   int     `json:"has_invoice_seller" description:"对应的开票记录有没有销售 0没有 1有"`
}

type InvoiceDetailItem struct {
	ContractInvoice
	HasInvoiceSeller int `json:"has_invoice_seller" description:"对应的开票记录有没有销售 0没有 1有"`
}

// GetContractInvoiceDetailItemList 预登记列表详情用-获取开票到款列表
func GetContractInvoiceDetailItemList(condition string, pars []interface{}) (itemList []*ContractInvoiceDetailItem, err error) {
	list := make([]*InvoiceDetailItem, 0)
	err = global.DEFAULT_MYSQL.Table("contract_invoice AS a").
		Select("a.*,IF(p.seller_id > 0, 1, 0) AS has_invoice_seller ").
		Joins("JOIN contract_pre_register AS p ON p.invoice_id = a.contract_invoice_id OR p.arrive_id = a.contract_invoice_id ").
		Where("is_deleted = 0").
		Where(condition, pars...).
		Order("contract_invoice_id ASC").
		Scan(&list).Error
	if err != nil {
		return
	}
	itemList = formatContractInvoice2DetailItemList(list)
	return
}

// formatContractInvoice2ItemList 预登记列表详情用-格式化ContractInvoice
func formatContractInvoice2DetailItemList(list []*InvoiceDetailItem) (itemList []*ContractInvoiceDetailItem) {
	itemList = make([]*ContractInvoiceDetailItem, 0)
	for i := range list {
		itemList = append(itemList, &ContractInvoiceDetailItem{
			ContractInvoiceId:  list[i].ContractInvoiceId,
			ContractRegisterId: list[i].ContractRegisterId,
			ContractCode:       list[i].ContractCode,
			OriginAmount:       list[i].OriginAmount,
			CurrencyUnit:       list[i].CurrencyUnit,
			InvoiceType:        list[i].InvoiceType,
			InvoiceDate:        utils.TimeTransferString(utils.FormatDate, list[i].InvoiceDate),
			SellerId:           list[i].SellerId,
			SellerName:         list[i].SellerName,
			PayType:            list[i].PayType,
			Remark:             list[i].Remark,
			ServiceProductId:   list[i].ServiceProductId,
			IsPrePay:           list[i].IsPrePay,
			StartDate:          list[i].StartDate.Format(utils.FormatDate),
			EndDate:            list[i].StartDate.Format(utils.FormatDate),
			CreateTime:         utils.TimeTransferString(utils.FormatDateTime, list[i].CreateTime),
			PreRegisterId:      list[i].PreRegisterId,
			HasInvoiceSeller:   list[i].HasInvoiceSeller,
		})
	}
	return
}

type ContractInvoiceRespItem struct {
	ContractInvoice
	CompanyName string `gorm:"column:company_name" json:"company_name" description:"客户名称"`
}

func (c *ContractInvoice) FetchByRegisterId(registerId int) (items []*ContractInvoice, err error) {
	err = global.DEFAULT_MYSQL.Model(c).Where("is_deleted = 0 AND contract_register_id = ?", registerId).Scan(&items).Error
	return
}

// GetDuplicateContractInvoiceDetailItemListWithType 预登记列表详情用-获取开票到款列表
func GetDuplicateContractInvoiceDetailItemListWithType(companyName, startDate, endDate string, contractType, hasPayment int) (itemList []*ContractInvoiceDetailItem, err error) {
	list := make([]*InvoiceDetailItem, 0)
	err = global.DEFAULT_MYSQL.Table("contract_invoice as a ").
		Select(" a.*,IF(p.seller_id > 0, 1, 0) AS has_invoice_seller  ").
		Joins(" JOIN contract_register as b ON a.contract_register_id = b.contract_register_id ").
		Joins("JOIN contract_pre_register AS p ON p.invoice_id = a.contract_invoice_id OR p.arrive_id = a.contract_invoice_id ").
		Where(fmt.Sprintf("a.is_deleted = 0 AND b.company_name = '%s' AND b.start_date= '%s' AND b.end_date= '%s'"+
			" AND b.is_deleted=0 AND (a.invoice_type=3 OR a.invoice_type=4) "+
			"AND b.contract_type=? AND b.has_payment=?", companyName, startDate, endDate), contractType, hasPayment).
		Order("a.contract_invoice_id ASC").
		Find(&list).Error
	if err != nil {
		return
	}
	itemList = formatContractInvoice2DetailItemList(list)
	return
}

func GetContractInvoiceByRegisterIdAndType(registerId int) (items *ContractInvoice, err error) {
	err = global.DEFAULT_MYSQL.Model(ContractInvoice{}).Where("is_deleted = 0 AND contract_register_id = ? AND invoice_type = 2", registerId).Order("create_time DESC").First(&items).Error
	return
}

type DupInvoice struct {
	ContractInvoice
	TemplateIds string
}

// GetDuplicateContractInvoiceDetailItemList 预登记列表详情用-获取开票到款列表
func GetDuplicateContractInvoiceDetailItemList(companyName, startDate, endDate string, contractType, hasPayment int) (list []*DupInvoice, err error) {
	err = global.DEFAULT_MYSQL.Table("contract_invoice as a ").
		Select(" a.*,GROUP_CONCAT(DISTINCT c.service_template_id ORDER BY c.service_template_id ) AS template_ids ").
		Joins(" JOIN contract_register as b ON a.contract_register_id = b.contract_register_id ").
		Joins("JOIN contract_service AS c ON a.contract_register_id = c.contract_register_id").
		Where(fmt.Sprintf("a.is_deleted = 0 AND b.company_name = '%s' AND b.start_date= '%s' AND b.end_date= '%s' AND b.is_deleted=0 AND (a.invoice_type=3 OR a.invoice_type=4) AND b.contract_type=? AND b.has_payment=?", companyName, startDate, endDate), contractType, hasPayment).
		Group("a.contract_invoice_id").
		Order("a.contract_invoice_id ASC").
		Find(&list).Error
	if err != nil {
		return
	}
	return
}

type IncomeListReq struct {
	SellerIds   string `json:"seller_ids" form:"seller_ids" description:"销售ID，多个ID之间用逗号拼接"`
	StartDate   string `json:"start_date" form:"start_date" binding:"omitempty,datetime=2006-01" description:"开始日期"`
	EndDate     string `json:"end_date" form:"end_date" binding:"omitempty,datetime=2006-01" description:"结束日期"`
	IsExport    int    `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"`
	ListParam   string `json:"list_param" form:"list_param" description:"套餐类型: 0-月度; 1-季度; 2-半年度；3-年度；4-月度累计"`
	CompanyType int    `json:"company_type" form:"company_type" description:"客户类型 0全部 1新客户 2老客户 3未续约"`
}

// 获取最新的开票或到款日期
func GetLatestIncome(condition string, pars []interface{}) (result *ContractInvoice, err error) {
	sql := `SELECT
	a.* 
FROM
	contract_invoice AS a
	INNER JOIN contract_register AS b ON a.contract_register_id = b.contract_register_id 
WHERE
	a.is_deleted = 0 `
	sql += condition
	sql += `ORDER BY a.invoice_time DESC  `
	err = global.DEFAULT_MYSQL.Raw(sql, pars...).First(&result).Error
	return
}

// NotPaymentCensusItem 商品到款统计信息
type NotPaymentCensusItem struct {
	SummaryId          int                             `json:"summary_id" description:"汇总ID"`
	ContractRegisterId int                             `json:"contract_register_id" description:"登记ID"`
	CompanyName        string                          `json:"company_name" description:"客户名称"`
	NewCompany         int                             `json:"new_company" description:"是否为新客户: 0-否; 1-是"`
	StartDate          string                          `json:"start_date" description:"合同开始日期"`
	EndDate            string                          `json:"end_date" description:"合同结束日期"`
	ServicesName       string                          `json:"services_name" description:"套餐信息字符串拼接"`
	ContractType       int                             `json:"contract_type" description:"合同类型: 0-无 1-新签; 2-续约"`
	ActualPayCompanies string                          `json:"actual_pay_companies" description:"该合同关联的所有代付方, 英文逗号拼接"`
	InvoicePaymentList []*NotPaymentCensusInfo         `json:"invoice_payment_list" description:"开票到款列表"`
	ContractCode       string                          `gorm:"column:contract_code" json:"contract_code" description:"合同编号"`
	ContractAmount     float64                         `gorm:"column:contract_amount" json:"contract_amount" description:"合同金额"`
	UnitName           string                          `gorm:"unit_name" json:"unit_name" description:"单位名称"`
	ProductIds         string                          `gorm:"column:product_ids" json:"product_ids" description:"产品ID：1-FICC; 2-权益, 如果两者都有，则用英文逗号拼接"`
	NotInvoiceTotal    float64                         `json:"not_invoice_total" description:"未开票总金额(换算后)"`
	NotInvoiceList     []*NotInvoicePaymentSummaryItem `json:"not_invoice_list" description:"未开票invoice列表"`
}

// NotPaymentCensusInfo 开票到款统计信息
type NotPaymentCensusInfo struct {
	InvoiceId         int                                 `json:"invoice_id" description:"开票ID"`
	InvoiceDate       string                              `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       string                              `json:"payment_date" description:"到款日期"`
	PaymentAmount     float64                             `json:"payment_amount" description:"到款金额"`
	PayType           int                                 `json:"pay_type" description:"付款方式：0-无；1-年付；2-半年付；3-季付；4-次付；5-异常"`
	ServiceAmountList []*ContractPaymentServiceAmountItem `json:"service_amount_list" description:"到款套餐金额分配信息"`
	SellerType        int                                 `json:"seller_type" description:"销售类型：1ficc销售，2权益销售"`
	NotPaymentAmount  float64                             `json:"not_payment_amount" description:"开票未到款金额"`
	ServiceProductId  int                                 `json:"service_product_id" description:"套餐类型：1ficc套餐，2权益套餐"`
	UnitName          string                              `json:"unit_name" description:"单位名称"`
}

// GetCensusSellerGroupInvoicePageListV2 获取销售组开票统计列表-分页
func GetCensusSellerGroupInvoicePageListV2(page base.IPage, groupStr, condition string, pars []interface{}, amountSum float64) (count int, results []*CensusSellerGroupInvoiceItem, err error) {
	sql := ` 
				SELECT
					seller_group_id AS group_id,
					SUM(amount) AS invoice_amount,
					SUM(amount) / %f AS group_rate
				FROM
					contract_invoice AS a
				INNER JOIN contract_register AS b ON a.contract_register_id = b.contract_register_id 
				WHERE
					%s
				AND seller_group_id IN (` + groupStr + `)
				GROUP BY
					seller_group_id  ORDER BY %s LIMIT %d,%d
			`

	sqlCount := ` select count(1) as count from (SELECT * 
				FROM
					contract_invoice
				WHERE
					%s
				AND seller_group_id IN (` + groupStr + `)
				GROUP BY
					seller_group_id  )as a
			`
	sqlCount = fmt.Sprintf(sqlCount, condition)
	err = global.DEFAULT_MYSQL.Raw(sqlCount, pars...).Find(&count).Error

	sql = fmt.Sprintf(sql, amountSum, condition, page.GetOrderItemsString(), page.Offset(), page.GetPageSize())
	err = global.DEFAULT_MYSQL.Raw(sql, pars...).Scan(&results).Error
	return
}

// GetCensusSellerInvoicePageList 获取销售开票统计列表-分页
func GetCensusSellerInvoicePageListV2(page base.IPage, adminStr, inCond, condition string, pars []interface{}, amountSum float64) (count int, results []*CensusSellerInvoiceItem, err error) {
	//sql := `SELECT a.admin_id AS seller_id, a.real_name AS seller_name, a.group_id, b.invoice_amount, b.group_rate, b.seller_rate FROM %s AS a LEFT JOIN `
	sql := ` SELECT c.seller_id , SUM(c.amount) AS invoice_amount,
				SUM(c.amount) / (
					SELECT SUM(amount) FROM contract_invoice WHERE seller_group_id = c.seller_group_id AND %s
				) AS group_rate,
				SUM(c.amount) / %f AS seller_rate
				FROM contract_invoice AS c 
				INNER JOIN contract_register AS b ON c.contract_register_id = b.contract_register_id 
				WHERE %s
				AND c.seller_id IN (` + adminStr + `)
				GROUP BY c.seller_id ORDER BY %s LIMIT %d,%d `

	sqlCount := ` select count(1) as count from (SELECT * 
				FROM contract_invoice AS c
				WHERE %s
				AND c.seller_id IN (` + adminStr + `) GROUP BY c.seller_id ) as a
				 `
	sqlCount = fmt.Sprintf(sqlCount, condition)
	err = global.DEFAULT_MYSQL.Raw(sqlCount, pars...).Find(&count).Error

	//query := global.DEFAULT_MYSQL.Table("contract_invoice").
	//	Where(condition, pars...).Where("AND seller_id IN (`+adminStr+`)")
	//query.Count(&count)

	sql = fmt.Sprintf(sql, inCond, amountSum, condition, page.GetOrderItemsString(), page.Offset(), page.GetPageSize())
	err = global.DEFAULT_MYSQL.Raw(sql, pars...).Scan(&results).Error
	return
}

// GetInvoiceListByFix
func GetInvoiceListByFix() (results []*ContractInvoice, err error) {
	sql := `SELECT * FROM contract_invoice WHERE invoice_type = 2 AND seller_id = 0 `
	err = global.DEFAULT_MYSQL.Raw(sql).Find(&results).Error
	return
}

// GetDuplicateContractInvoiceDetailItemList 补录合同用-获取开票到款列表
func GetDuplicateContractInvoiceDetailItemListForSupplement(companyName, startDate, endDate string, contractType, hasPayment int) (list []*DupInvoice, err error) {
	err = global.DEFAULT_MYSQL.Table("contract_invoice as a ").
		Select(" a.*,GROUP_CONCAT(DISTINCT c.service_template_id ORDER BY c.service_template_id ) AS template_ids ").
		Joins(" JOIN contract_register as b ON a.contract_register_id = b.contract_register_id ").
		Joins("JOIN contract_service AS c ON a.contract_register_id = c.contract_register_id").
		Where(fmt.Sprintf("a.is_deleted = 0 AND b.company_name = '%s' AND b.start_date= '%s' AND b.end_date= '%s' AND b.is_deleted=0 AND b.contract_type=? AND b.has_payment=?", companyName, startDate, endDate), contractType, hasPayment).
		Group("a.contract_register_id").
		Order("a.contract_invoice_id ASC").
		Find(&list).Error
	if err != nil {
		return
	}
	return
}

func GetInvoiceCountByRegisterId(registerId int) (total int64, err error) {
	err = global.DEFAULT_MYSQL.Table("contract_invoice as a ").
		Where("is_deleted = 0 AND contract_register_id = ?", registerId).Count(&total).Error
	return
}