ERP_storage/models/Contract/Contract.go

package Contract

import (
	"ERP_storage/logs"
	"ERP_storage/models/Account"
	"fmt"
	"git.baozhida.cn/ERP_libs/lib"
	orm2 "github.com/beego/beego/v2/client/orm"
	"strconv"
	"strings"
	"sync"
	"time"

	_ "github.com/astaxie/beego/cache/redis"
	"github.com/beego/beego/v2/adapter/orm"
	_ "github.com/go-sql-driver/mysql"
)

// 合同
type Contract struct {
	Id                 int     `orm:"column(ID);size(11);auto;pk"`
	T_number           string  `orm:"size(256);null"`         // 合同编号
	T_customer         string  `orm:"size(256);null"`         // 客户名称
	T_customer_id      string  `orm:"size(256);null"`         // 客户id
	T_money            float32 `orm:"digits(12);decimals(2)"` // 合同金额
	T_discount         float32 `orm:"digits(12);decimals(2)"` // 合同优惠价
	T_project          string  `orm:"size(256);null"`         // 项目
	T_type             int     `orm:"size(2);default(1)"`     // 合同类型 1-销售合同 2-验证合同
	T_date             string  `orm:"size(256);null"`         // 签订时间
	T_out              int     `orm:"size(2);default(1)"`     // 出库状态  1-未出库 2-部分出库 3-已出库
	T_State            int     `orm:"size(2);default(1)"`     // 0 删除（伪删除）  1-已通过 2-未通过 3-待审核 审核状态
	T_pdf              string  `orm:"size(256);null"`         // 附件
	T_remark           string  `orm:"type(text);null"`        // 备注
	T_recoveries       string  `orm:"type(text);null"`        // 回款信息 回款时间,回款金额|回款时间,回款金额|
	T_recoveries_money float32 `orm:"digits(12);decimals(2)"` // 回款总金额
	T_invoice          string  `orm:"type(text);null"`        // 开票信息 开票时间,开票金额|开票时间,开票金额|
	T_invoice_money    float32 `orm:"digits(12);decimals(2)"` // 开票总金额
	T_start_date       string  `orm:"size(256);null"`         // 起始时间
	T_end_date         string  `orm:"size(256);null"`         // 终止时间

	T_submit   string `orm:"size(256);null"` // 合同提交人员
	T_approver string `orm:"size(256);null"` // 审批人

	CreateTime time.Time `orm:"column(create_time);type(timestamp);null;auto_now_add"` //auto_now_add 第一次保存时才设置时间
	UpdateTime time.Time `orm:"column(update_time);type(timestamp);null;auto_now"`     //auto_now 每次 model 保存时都会对时间自动更新
}

func (t *Contract) TableName() string {
	return "contract" // 数据库名称   // ************** 替换 FormulaList **************
}

type ContractDaoImpl struct {
	orm orm.Ormer
}

func NewContract(orm orm.Ormer) *ContractDaoImpl {
	return &ContractDaoImpl{orm: orm}
}

var Contract_list *sync.Map

func init() {
	//注册模型
	orm.RegisterModel(new(Contract))
	Contract_list = new(sync.Map)
}

type Contract_R struct {
	Id                 int
	T_number           string  // 合同编号
	T_customer         string  // 客户名称
	T_money            float32 // 合同金额
	T_discount         float32 // 合同优惠价
	T_type             int     // 合同类型 1-销售合同 2-验证合同
	T_date             string  // 签订时间
	T_out              int     // 出库状态  1-未出库 2-已部分出库 3-已全部出库
	T_submit           string  // 合同提交人员uuid
	T_submit_name      string  // 合同提交人员名称
	T_approver         string  // 合同审批人员uuid
	T_approver_name    string  // 合同审批人员名称
	T_pdf              string  // 附件
	T_State            int     // 1-已通过 2-未通过 3-待审核 审核状态
	T_recoveries_money float32 // 回款总金额
	T_invoice_money    float32 // 开票总金额

}

type Contract_Detail struct {
	Id                    int
	T_number              string
	T_customer            string  // 客户名称
	T_money               float32 // 合同金额
	T_discount            float32 // 合同优惠价
	T_project             string  // 项目
	T_type                int     // 合同类型 1-销售合同 2-验证合同
	T_date                string  // 签订时间
	T_submit              string  // 合同提交人员uuid
	T_submit_name         string  // 合同提交人员名称
	T_approver            string  // 审批人uuid
	T_approver_name       string  // 审批人名称
	T_out                 int     // 出库状态  1-未出库 2-已部分出库 3-已全部出库
	T_State               int     // 1-已通过 2-未通过 3-待审核 审核状态
	T_remark              string  // 备注
	T_pdf                 string  // 附件
	T_start_date          string  // 起始时间
	T_end_date            string  // 终止时间
	T_Product             []ContractProduct_R
	T_recoveries          []ContractFinance_R // 回款信息 回款时间,回款金额|回款时间,回款金额|
	T_no_recoveries_money float32             // 未回款金额
	T_invoice             []ContractFinance_R // 开票信息 开票时间,开票金额|开票时间,开票金额|
	T_no_invoice_money    float32             // 未开票金额
}
type ContractFinance_R struct {
	T_date  string  // 时间
	T_money float32 // 金额）
}

type Contract_Verify struct {
	Id             int
	T_number       string
	T_money        float32 // 合同金额
	T_discount     float32 // 合同优惠价
	T_date         string  // 签订时间
	T_start_date   string  // 起始时间
	T_end_date     string  // 终止时间
	T_verify_state int     // 1-未签约 2-已作废 3-已签约 4-即将到期
	T_recoveries   string  // 回款信息
	T_invoice      string  // 开票信息
}
type ContractMoney_Stat struct {
	T_date             int
	T_discount         float32
	T_recoveries_money float32 // 回款信息
}

type ContractNum_Stat struct {
	T_date int
	T_num  int
}

func ContractToContractFinance_R(T_detail string) (r []ContractFinance_R, money float32) {

	if len(T_detail) == 0 {
		return
	}
	detailList := strings.Split(strings.Trim(T_detail, "|"), "|")
	for _, detail := range detailList {
		T_date := strings.Split(detail, ",")[0]
		T_money := strings.Split(detail, ",")[1]
		temp := float32(lib.StringToFloat64TwoDecimal(T_money))
		money += temp
		r = append(r, ContractFinance_R{
			T_date:  T_date,
			T_money: temp,
		})

	}
	return r, money
}

func ContractToContract_R(t Contract) (r Contract_R) {
	// 1-未出库 2-已部分出库 3-已全部出库
	r.T_out = t.T_out
	r.Id = t.Id
	r.T_number = t.T_number
	r.T_customer = t.T_customer
	r.T_money = t.T_money
	r.T_discount = t.T_discount
	r.T_type = t.T_type
	r.T_date = t.T_date
	r.T_State = t.T_State
	r.T_pdf = t.T_pdf
	r.T_submit = t.T_submit
	r.T_submit_name = Account.Read_User_T_name_Get(t.T_submit)
	r.T_approver = t.T_approver
	r.T_approver_name = Account.Read_User_T_name_Get(t.T_approver)
	r.T_recoveries_money = t.T_recoveries_money
	r.T_invoice_money = t.T_invoice_money
	return r
}

func ContractToContract_Detail(t Contract, productList []ContractProduct_R) (r Contract_Detail) {
	// 1-未出库 2-已部分出库 3-已全部出库
	r.T_out = t.T_out
	r.Id = t.Id
	r.T_number = t.T_number
	r.T_customer = t.T_customer
	r.T_money = t.T_money
	r.T_discount = t.T_discount
	r.T_project = t.T_project
	r.T_type = t.T_type
	r.T_date = t.T_date
	r.T_State = t.T_State
	r.T_remark = t.T_remark
	r.T_pdf = t.T_pdf
	r.T_submit = t.T_submit
	r.T_submit_name = Account.Read_User_T_name_Get(t.T_submit)
	r.T_approver = t.T_approver
	r.T_approver_name = Account.Read_User_T_name_Get(t.T_approver)
	r.T_Product = productList
	r.T_start_date = t.T_start_date
	r.T_end_date = t.T_end_date

	recoveriesList, recoveries := ContractToContractFinance_R(t.T_recoveries)
	r.T_recoveries = recoveriesList
	r.T_no_recoveries_money = t.T_money - recoveries

	invoiceList, invoice := ContractToContractFinance_R(t.T_invoice)
	r.T_invoice = invoiceList
	r.T_no_invoice_money = t.T_money - invoice

	return r
}

func ContractToContract_Verify(t Contract) (r Contract_Verify) {
	r.Id = t.Id
	r.T_number = t.T_number
	r.T_money = t.T_money
	r.T_discount = t.T_discount
	r.T_date = t.T_date
	r.T_start_date = t.T_start_date
	r.T_end_date = t.T_end_date
	nowTime := time.Now()
	now := time.Date(nowTime.Year(), nowTime.Month(), nowTime.Day(), 0, 0, 0, 0, time.Local)
	end, _ := lib.DateStrToTime(t.T_end_date)
	now2Month := now.AddDate(0, 2, 0)

	// 1-未签约 2-已作废 3-已签约 4-即将到期
	state := 0
	if len(t.T_end_date) == 0 {
		state = 1
	}
	// 结束时间<今天 已过期
	if end.Before(now) {
		state = 2
	} else {
		// 结束时间<今天+2月 即将到期 && 结束时间>今天
		if end.Before(now2Month) || end.Equal(now) {
			state = 4
		}
		// 结束时间>今天+2月 已签约
		if end.After(now2Month) || end.Equal(now2Month) {
			state = 3
		}
	}

	r.T_verify_state = state
	r.T_recoveries = t.T_recoveries
	r.T_invoice = t.T_invoice

	return r
}

// 添加
func (dao *ContractDaoImpl) Add_Contract(r Contract) (id int64, err error) {
	id, err = dao.orm.Insert(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return id, err
}

// 获取 ById
func (dao *ContractDaoImpl) Read_Contract_ByT_number(T_number string) (r Contract, err error) {
	qs := dao.orm.QueryTable(new(Contract))

	err = qs.Filter("T_number", T_number).Filter("T_State__gt", 0).One(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return
}

func (dao *ContractDaoImpl) Read_Contract_ById(Id int) (r Contract, err error) {
	qs := dao.orm.QueryTable(new(Contract))
	err = qs.Filter("Id", Id).One(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return
}

// 修改
func (dao *ContractDaoImpl) Update_Contract(m Contract, cols ...string) error {
	_, err := dao.orm.Update(&m, cols...)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return err
	}
	return nil
}

// 删除
func (dao *ContractDaoImpl) Delete_Contract(v Contract) error {
	v.T_State = 0
	_, err := dao.orm.Update(&v, "T_State")
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return err
}

// 获取列表
func (dao *ContractDaoImpl) Read_Contract_List(T_submit, T_name string, T_state, T_out, page, page_z int) (r_ []Contract_R, cnt int64) {
	// 也可以直接使用 Model 结构体作为表名
	qs := dao.orm.QueryTable(new(Contract))
	var offset int64
	if page <= 1 {
		offset = 0
	} else {
		offset = int64((page - 1) * page_z)
	}

	// 过滤
	cond := orm.NewCondition()
	cond1 := cond.And("T_State__gt", 0).And("T_type", 1)
	if T_state > 0 {
		cond1 = cond1.And("T_State", T_state)
	}

	if len(T_name) > 0 {
		cond1 = cond1.AndCond(cond.Or("T_number__icontains", T_name).Or("T_customer__icontains", T_name))
	}

	if len(T_submit) > 0 {
		cond1 = cond1.And("T_submit", T_submit)
	}

	// T_out == 1 查询未出库或部分出库数据
	if T_out == 1 {
		cond1 = cond1.And("T_out__in", []int{1, 2})
	}

	// 查询
	var r []Contract
	_, err := qs.Limit(page_z, offset).SetCond((*orm2.Condition)(cond1)).OrderBy("-Id").All(&r)

	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	cnt, err = qs.SetCond((*orm2.Condition)(cond1)).Count()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	for _, v := range r {
		r_ = append(r_, ContractToContract_R(v))
	}

	return r_, cnt
}

// 获取列表
func (dao *ContractDaoImpl) Read_VerifyContract_List(T_name, T_customer_id string, page, page_z int) (r_ []Contract_Verify, cnt int64) {
	// 也可以直接使用 Model 结构体作为表名
	qs := dao.orm.QueryTable(new(Contract))
	var offset int64
	if page <= 1 {
		offset = 0
	} else {
		offset = int64((page - 1) * page_z)
	}

	// 过滤
	cond := orm.NewCondition()
	cond1 := cond.And("T_State__gt", 0).And("T_type", 2)
	if len(T_customer_id) > 0 {
		cond1 = cond1.And("T_customer_id", T_customer_id)
	}
	if len(T_name) > 0 {
		cond1 = cond1.AndCond(cond.Or("T_number__icontains", T_name).Or("T_customer__icontains", T_name))
	}

	// 查询
	var r []Contract
	var err error
	if page_z == 9999 || len(T_customer_id) == 0 {
		_, err = qs.SetCond((*orm2.Condition)(cond1)).OrderBy("-T_end_date").All(&r)
	} else {
		_, err = qs.Limit(page_z, offset).SetCond((*orm2.Condition)(cond1)).OrderBy("-T_end_date").All(&r)
	}

	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	cnt, err = qs.SetCond((*orm2.Condition)(cond1)).Count()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	for _, v := range r {
		r_ = append(r_, ContractToContract_Verify(v))
	}

	return r_, cnt
}

func (dao *ContractDaoImpl) Read_Contract_List_For_RecoveriesAndInvoice(T_name string, T_recoveries_state, T_invoice_state, page, page_z int) (r_ []Contract_R, cnt int64) {
	// 也可以直接使用 Model 结构体作为表名
	qs := dao.orm.QueryTable(new(Contract))
	var offset int64
	if page <= 1 {
		offset = 0
	} else {
		offset = int64((page - 1) * page_z)
	}

	// 过滤
	cond := orm2.NewCondition()
	cond = cond.And("T_State", 1)
	// 回款状态 1 未回款 2 部分回款 3 全部回款
	// 开票状态 1 未开票 2 部分开票 3 全部开票
	if len(T_name) > 0 {
		cond = cond.AndCond(cond.Or("T_number__icontains", T_name).Or("T_customer__icontains", T_name))
	}
	qs = qs.SetCond(cond)

	if T_recoveries_state == 1 {
		qs = qs.FilterRaw("t_recoveries_money", " = 0")
	}
	if T_recoveries_state == 2 {
		qs = qs.FilterRaw("t_recoveries_money", " < t_discount").FilterRaw("t_recoveries_money", " > 0")
	}
	if T_recoveries_state == 3 {
		qs = qs.FilterRaw("t_recoveries_money", " = t_discount")
	}

	if T_invoice_state == 1 {
		qs = qs.FilterRaw("t_invoice_money", " = 0")
	}
	if T_invoice_state == 2 {
		qs = qs.FilterRaw("t_invoice_money", " < t_discount").FilterRaw("t_invoice_money", " > 0")
	}
	if T_invoice_state == 3 {
		qs = qs.FilterRaw("t_invoice_money", " = t_discount")
	}
	cond = qs.GetCond()
	// 查询
	var r []Contract
	_, err := qs.Limit(page_z, offset).SetCond(cond).OrderBy("-Id").All(&r)

	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	cnt, err = qs.SetCond(cond).Count()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	for _, v := range r {
		r_ = append(r_, ContractToContract_R(v))
	}

	return r_, cnt
}

// 获取最新的验证合同
func (dao *ContractDaoImpl) Read_VerifyContract_Newest(T_customer_id string) (r_ Contract) {
	// 也可以直接使用 Model 结构体作为表名
	qs := dao.orm.QueryTable(new(Contract))

	// 过滤
	cond := orm.NewCondition()
	cond1 := cond.And("T_State__gt", 0).And("T_type", 2).And("T_customer_id", T_customer_id)

	// 查询
	var r []Contract
	_, err := qs.SetCond((*orm2.Condition)(cond1)).OrderBy("-T_end_date").All(&r)

	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}

	if len(r) == 0 {
		return
	}

	return r[0]
}

// 获取验证合同
func (dao *ContractDaoImpl) Read_Contract_ByT_date(T_customer_id, T_start_date, T_end_date string) (r Contract, err error) {
	qs := dao.orm.QueryTable(new(Contract))

	err = qs.Filter("T_customer_id", T_customer_id).Filter("T_start_date", T_start_date).Filter("T_end_date", T_end_date).
		Filter("T_type", 2).Filter("T_State__gt", 0).One(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return
}

// 按年统计合同金额，汇款金额
func (dao *ContractDaoImpl) Stat_ContractMoney_ByYear(T_type int) ([]ContractMoney_Stat, float32, float32) {
	var stat []ContractMoney_Stat
	var discount, recoveries float32

	sql := "SELECT YEAR(t_date) AS t_date, SUM(t_discount) AS t_discount, SUM(t_recoveries_money) AS t_recoveries_money FROM contract WHERE t__state = 1"
	if T_type > 0 {
		sql += " AND t_type = " + strconv.Itoa(T_type)
	}
	sql += " GROUP BY YEAR(t_date)"

	_, err := dao.orm.Raw(sql).QueryRows(&stat)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0, 0
	}

	sqlSum := "SELECT SUM(t_discount),SUM(t_recoveries_money) FROM contract WHERE t__state = 1"
	if T_type > 0 {
		sqlSum += " AND t_type = " + strconv.Itoa(T_type)
	}
	err = dao.orm.Raw(sqlSum).QueryRow(&discount, &recoveries)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0, 0
	}

	return stat, discount, recoveries

}
func (dao *ContractDaoImpl) Stat_ContractNum_ByYear(T_type int) ([]ContractNum_Stat, int64) {
	var stat []ContractNum_Stat
	var count int64

	sql := "SELECT YEAR(t_date) AS t_date, COUNT(ID) AS t_num  FROM contract WHERE t__state = 1"
	if T_type > 0 {
		sql += " AND t_type = " + strconv.Itoa(T_type)
	}
	sql += " GROUP BY YEAR(t_date)"

	_, err := dao.orm.Raw(sql).QueryRows(&stat)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0
	}

	sqlCount := "SELECT COUNT(ID) FROM contract WHERE t__state = 1"
	if T_type > 0 {
		sqlCount += " AND t_type = " + strconv.Itoa(T_type)
	}
	err = dao.orm.Raw(sqlCount).QueryRow(&count)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0
	}

	return stat, count

}

func (dao *ContractDaoImpl) Stat_ContractMoney_ByMonth(T_type, T_year int) ([]ContractMoney_Stat, float32, float32) {
	//var stat []ContractMoney_Stat
	var stat []ContractMoney_Stat
	var discount, recoveries float32

	sql := "SELECT MONTH(t_date) AS t_date, SUM(t_discount) AS t_discount, SUM(t_recoveries_money) AS t_recoveries_money FROM contract WHERE t__state = 1" +
		" AND t_date BETWEEN '" + strconv.Itoa(T_year) + "-01-01' AND '" + strconv.Itoa(T_year) + "-12-31'"
	if T_type > 0 {
		sql += " AND t_type = " + strconv.Itoa(T_type)
	}
	sql += " GROUP BY MONTH(t_date)"
	fmt.Println(sql)

	_, err := dao.orm.Raw(sql).QueryRows(&stat)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0, 0
	}

	sqlSum := "SELECT SUM(t_discount),SUM(t_recoveries_money) FROM contract WHERE t__state = 1" +
		" AND t_date BETWEEN '" + strconv.Itoa(T_year) + "-01-01' AND '" + strconv.Itoa(T_year) + "-12-31'"
	if T_type > 0 {
		sqlSum += " AND t_type = " + strconv.Itoa(T_type)
	}
	err = dao.orm.Raw(sqlSum).QueryRow(&discount, &recoveries)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0, 0
	}

	return stat, discount, recoveries

}
func (dao *ContractDaoImpl) Stat_ContractNum_ByMonth(T_type, T_year int) ([]ContractNum_Stat, int64) {
	var stat []ContractNum_Stat
	var count int64

	sql := "SELECT MONTH(t_date) AS t_date, COUNT(ID) AS t_num FROM contract WHERE t__state = 1" +
		" AND t_date BETWEEN '" + strconv.Itoa(T_year) + "-01-01' AND '" + strconv.Itoa(T_year) + "-12-31'"
	if T_type > 0 {
		sql += " AND t_type = " + strconv.Itoa(T_type)
	}
	sql += " GROUP BY MONTH(t_date)"

	_, err := dao.orm.Raw(sql).QueryRows(&stat)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0
	}

	sqlCount := "SELECT COUNT(ID) FROM contract WHERE t__state = 1" +
		" AND t_date BETWEEN '" + strconv.Itoa(T_year) + "-01-01' AND '" + strconv.Itoa(T_year) + "-12-31'"
	if T_type > 0 {
		sqlCount += " AND t_type = " + strconv.Itoa(T_type)
	}
	err = dao.orm.Raw(sqlCount).QueryRow(&count)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return stat, 0
	}

	return stat, count

}

// 获取全部
func Read_Contract_All_Map() {
	o := orm.NewOrm()
	var r []Contract
	qs := o.QueryTable(new(Contract))
	_, err := qs.All(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}

	for _, v := range r {
		Contract_list.Store(v.T_number, v)
	}

}
func Read_Contract_Get(T_number string) Contract {
	// 有先加入 给全部人发消息
	v, ok := Contract_list.Load(T_number) /*如果确定是真实的,则存在,否则不存在 */
	if ok {
		return v.(Contract)
	}
	return Contract{}
}