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(®isterIds) 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:"开票换算金额"` InvoiceOriginAmount float64 `json:"invoice_origin_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:"到款换算金额"` PaymentOriginAmount float64 `json:"payment_origin_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老客户"` SellerIds string `json:"seller_ids" form:"seller_ids" description:"销售IDs"` 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 { SellerIds string `json:"seller_ids" form:"seller_ids" description:"销售IDs"` 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:"关键词"` IsExport int `json:"is_export" form:"is_export" description:"是否导出: 0-否; 1-是"` 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 IncomeChartListReq 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未续约"` ServiceTypes string `json:"service_types" form:"service_types" description:"套餐类型"` IncomeType int `json:"income_type" form:"income_type" description:"收入类型 0开票收入 1到款收入"` } // 获取最新的开票或到款日期 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:"开票换算金额"` InvoiceOriginAmount float64 `json:"invoice_origin_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:"到款换算金额"` PaymentOriginAmount float64 `json:"payment_origin_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权益销售"` NotPaymentOriginAmount float64 `json:"not_payment_origin_amount" description:"开票未到款金额"` 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 AS a 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 c.* 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 ) 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 } 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未续约"` ServiceTypes string `json:"service_types" form:"service_types" description:"套餐类型"` IncomeType int `json:"income_type" form:"income_type" description:"收入类型 0开票收入 1到款收入"` SortType string `json:"sort_type" form:"sort_type" description:"如何排序，是正序还是倒序，枚举值：asc 正序,desc 倒叙"` Keyword string `json:"keyword" form:"keyword" description:"搜索关键字"` base.PageReq } // 获取最新的到款日期 func GetLatestPaymentIncome(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 AND a.invoice_type IN (2,4) ` sql += condition sql += `ORDER BY a.invoice_time DESC ` err = global.DEFAULT_MYSQL.Raw(sql, pars...).First(&result).Error return } // 获取最早的到款日期 func GetEarliestPaymentIncome(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 AND a.invoice_type IN (2,4) ` sql += condition sql += `ORDER BY a.invoice_time ASC ` err = global.DEFAULT_MYSQL.Raw(sql, pars...).First(&result).Error return }