package contract import ( "bytes" "encoding/json" "fmt" "github.com/gin-gonic/gin" "github.com/go-playground/validator/v10" "github.com/shopspring/decimal" "github.com/tealeg/xlsx" "hongze/fms_api/controller/resp" "hongze/fms_api/global" "hongze/fms_api/models/base" "hongze/fms_api/models/crm" "hongze/fms_api/models/fms" "hongze/fms_api/models/system" "hongze/fms_api/services/alarm_msg" crmService "hongze/fms_api/services/crm" fmsService "hongze/fms_api/services/fms" "hongze/fms_api/utils" "net/http" "os" "path" "strconv" "strings" "time" ) // RegisterController 合同登记 type RegisterController struct{} // List // @Title 合同登记列表 // @Description 合同登记列表 // @Param Keyword query string false "关键词" // @Param StartDate query string false "合同开始日期" // @Param EndDate query string false "合同结束日期" // @Param ServiceType query int false "套餐类型" // @Param ContractType query int false "合同类型" // @Param RegisterStatus query int false "登记状态" // @Success 200 {object} fms.ContractRegisterItem // @router /contract/register/list [get] func (rg *RegisterController) List(c *gin.Context) { var req fms.ContractRegisterListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } cond := `1 = 1` pars := make([]interface{}, 0) // 合同编号/客户姓名/销售/实际使用方 if req.Keyword != "" { kw := "%" + req.Keyword + "%" cond += ` AND (company_name LIKE ? OR contract_code LIKE ? OR seller_name LIKE ? OR actual_company_name LIKE ?)` pars = append(pars, kw, kw, kw, kw) } if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (create_time BETWEEN ? AND ?)` pars = append(pars, st, ed) } if req.ContractType != 0 { cond += ` AND contract_type = ?` pars = append(pars, req.ContractType) } if req.RegisterStatus != 0 { cond += ` AND register_status = ?` pars = append(pars, req.RegisterStatus) } // 套餐筛选 if req.ServiceType != 0 { registerIds, e := fms.GetContractRegisterIdsByTempId(req.ServiceType) if e != nil { resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) return } if len(registerIds) > 0 { cond += ` AND contract_register_id IN ?` pars = append(pars, registerIds) } else { cond += ` AND 1 = 2` } } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) page.AddOrderItem(base.OrderItem{Column: "create_time", Asc: false}) total, list, e := fms.GetContractRegisterItemPageList(page, cond, pars) if e != nil { resp.FailMsg("获取失败", "获取合同登记列表失败, Err: "+e.Error(), c) return } registerIds := make([]int, 0) for i := range list { registerIds = append(registerIds, list[i].ContractRegisterId) } serviceMap := make(map[int]string, 0) invoiceMap := make(map[int][]*fms.ContractInvoiceItem, 0) paymentMap := make(map[int][]*fms.ContractInvoiceItem, 0) if len(registerIds) > 0 { // 获取服务套餐 servicesNameList, e := fms.GetContractRegisterServicesNameByRegisterIds(registerIds) if e != nil { resp.FailMsg("获取失败", "获取套餐拼接字符串失败, Err: "+e.Error(), c) return } for i := range servicesNameList { serviceMap[servicesNameList[i].ContractRegisterId] = servicesNameList[i].ServicesName } // 获取开票/到款列表 invoiceCond := `contract_register_id IN ?` invoicePars := make([]interface{}, 0) invoicePars = append(invoicePars, registerIds) invoiceList, e := fms.GetContractInvoiceItemList(invoiceCond, invoicePars) if e != nil { resp.FailMsg("获取失败", "获取开票/到款列表失败, Err: "+e.Error(), c) return } for i := range invoiceList { if invoiceMap[invoiceList[i].ContractRegisterId] == nil { invoiceMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoiceItem, 0) } if paymentMap[invoiceList[i].ContractRegisterId] == nil { paymentMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoiceItem, 0) } if invoiceList[i].InvoiceType == fms.ContractInvoiceTypeMake { invoiceMap[invoiceList[i].ContractRegisterId] = append(invoiceMap[invoiceList[i].ContractRegisterId], invoiceList[i]) } if invoiceList[i].InvoiceType == fms.ContractInvoiceTypePay { paymentMap[invoiceList[i].ContractRegisterId] = append(paymentMap[invoiceList[i].ContractRegisterId], invoiceList[i]) } } } respList := make([]*fms.ContractRegisterList, 0) for i := range list { v := new(fms.ContractRegisterList) v.ContractRegisterItem = list[i] v.ServicesName = serviceMap[list[i].ContractRegisterId] v.InvoiceList = invoiceMap[list[i].ContractRegisterId] v.PaymentList = paymentMap[list[i].ContractRegisterId] respList = append(respList, v) } page.SetTotal(total) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(respList) resp.OkData("获取成功", baseData, c) } // Add // @Title 新增合同登记 // @Description 新增合同登记 // @Param request body fms.ContractRegisterAddReq true "type json string" // @Success 200 string "操作成功" // @router /contract/register/add [post] func (rg *RegisterController) Add(c *gin.Context) { req := new(fms.ContractRegisterAddReq) err := c.ShouldBind(&req) if err != nil { errs, ok := err.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+err.Error(), c) return } resp.FailData("参数解析失败", errs.Translate(global.Trans), c) return } claims, _ := c.Get("adminInfo") adminInfo := claims.(*system.SysAdmin) // 日期校验 startDate, e := time.ParseInLocation(utils.FormatDate, req.StartDate, time.Local) if e != nil { resp.FailMsg("合同开始日期格式有误", "合同开始日期格式有误, Err: "+e.Error(), c) return } endDate, e := time.ParseInLocation(utils.FormatDate, req.EndDate, time.Local) if e != nil { resp.FailMsg("合同结束日期格式有误", "合同结束日期格式有误, Err: "+e.Error(), c) return } signDate, _ := time.ParseInLocation(utils.FormatDate, "0000-00-00", time.Local) if req.SignDate != "" { signDateTime, e := time.ParseInLocation(utils.FormatDate, req.SignDate, time.Local) if e != nil { resp.FailMsg("合同签订日期格式有误", "合同签订日期格式有误, Err: "+e.Error(), c) return } signDate = signDateTime } // 是否存在相同合同编号的登记 ob := new(fms.ContractRegister) existCond := `contract_code = ?` existPars := make([]interface{}, 0) existPars = append(existPars, req.ContractCode) exist, e := ob.FetchByCondition(existCond, existPars) if e != nil && e != utils.ErrNoRow { resp.FailMsg("操作失败", "获取相同登记号失败, Err: "+e.Error(), c) return } if exist != nil && exist.ContractRegisterId > 0 { resp.Fail("合同编号已存在", c) return } // 货币及汇率 rateList, e := fmsService.GetTodayCurrencyRateList() if e != nil { resp.FailMsg("操作失败", "获取今日货币汇率失败, Err: "+e.Error(), c) return } var rate float64 for i := range rateList { if req.CurrencyUnit == rateList[i].Code { rate = rateList[i].RMBRate break } } if rate <= 0 { resp.FailMsg("操作失败", "货币汇率信息有误", c) return } nowTime := time.Now().Local() ob.ContractCode = req.ContractCode ob.RelateContractCode = req.RelateContractCode ob.CrmContractId = req.CrmContractId ob.ContractSource = req.ContractSource ob.CompanyName = req.CompanyName ob.ActualCompanyName = req.ActualCompanyName ob.ProductId = req.ProductId ob.SellerId = req.SellerId ob.SellerName = req.SellerName ob.ContractType = req.ContractType ob.ContractAmount = req.ContractAmount ob.CurrencyUnit = req.CurrencyUnit ob.RMBRate = rate ob.StartDate = startDate ob.EndDate = endDate ob.SignDate = signDate ob.AgreedPayTime = req.AgreedPayTime ob.ContractStatus = req.ContractStatus ob.RegisterStatus = fms.ContractRegisterStatusIng ob.Remark = req.Remark ob.ServiceRemark = req.ServiceRemark ob.HasPayment = req.HasPayment ob.NewCompany = req.NewCompany ob.Set() // 存在代付的直接完成登记, 且不允许进行开票/到款登记 if req.HasPayment == 1 { ob.RegisterStatus = fms.ContractRegisterStatusComplete } // 套餐信息 serviceList, e := fmsService.HandleContractServiceAndDetail(req.ProductId, req.Services, true) if e != nil { resp.FailMsg("操作失败", "获取合同套餐详情失败, Err: "+e.Error(), c) return } // 新增合同及套餐 if e = fms.CreateContractRegisterAndServices(ob, serviceList); e != nil { resp.FailMsg("操作失败", "新增合同及套餐失败, Err: "+e.Error(), c) return } // 操作日志 go func() { opData := "" opDataByte, e := json.Marshal(req) if e != nil { return } opData = string(opDataByte) logItem := new(fms.ContractRegisterLog) logItem.ContractRegisterId = ob.ContractRegisterId logItem.AdminId = int(adminInfo.AdminId) logItem.AdminName = adminInfo.RealName logItem.OpData = opData logItem.OpType = fms.ContractRegisterOpTypeSave logItem.CreateTime = nowTime logItem.Remark = req.Remark if e = logItem.Create(); e != nil { return } }() resp.Ok("操作成功", c) } // Edit // @Title 编辑合同登记 // @Description 编辑合同登记 // @Param request body fms.ContractRegisterEditReq true "type json string" // @Success 200 string "操作成功" // @router /contract/register/edit [post] func (rg *RegisterController) Edit(c *gin.Context) { req := new(fms.ContractRegisterEditReq) err := c.ShouldBind(&req) if err != nil { errs, ok := err.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+err.Error(), c) return } resp.FailData("参数解析失败", errs.Translate(global.Trans), c) return } claims, _ := c.Get("adminInfo") adminInfo := claims.(*system.SysAdmin) // 日期校验 startDate, e := time.ParseInLocation(utils.FormatDate, req.StartDate, time.Local) if e != nil { resp.FailMsg("合同开始日期格式有误", "合同开始日期格式有误, Err: "+e.Error(), c) return } endDate, e := time.ParseInLocation(utils.FormatDate, req.EndDate, time.Local) if e != nil { resp.FailMsg("合同结束日期格式有误", "合同结束日期格式有误, Err: "+e.Error(), c) return } signDate, _ := time.ParseInLocation(utils.FormatDate, "0000-00-00", time.Local) if req.SignDate != "" { signDateTime, e := time.ParseInLocation(utils.FormatDate, req.SignDate, time.Local) if e != nil { resp.FailMsg("合同签订日期格式有误", "合同签订日期格式有误, Err: "+e.Error(), c) return } signDate = signDateTime } ob := new(fms.ContractRegister) item, e := ob.Fetch(req.ContractRegisterId) if e != nil { if e == utils.ErrNoRow { resp.Fail("登记记录不存在或已被删除", c) return } resp.FailMsg("操作失败", "获取合同登记信息失败, Err:"+e.Error(), c) return } // 是否存在相同合同编号的登记 existCond := `contract_code = ?` existPars := make([]interface{}, 0) existPars = append(existPars, req.ContractCode) exist, e := ob.FetchByCondition(existCond, existPars) if e != nil && e != utils.ErrNoRow { resp.FailMsg("操作失败", "获取相同登记号失败, Err: "+e.Error(), c) return } if exist != nil && exist.ContractRegisterId > 0 && exist.ContractRegisterId != item.ContractRegisterId { resp.Fail("合同编号已存在", c) return } originHasPayment := item.HasPayment updateCols := []string{ "ContractCode", "RelateContractCode", "CrmContractId", "ContractSource", "CompanyName", "ActualCompanyName", "SellerId", "SellerName", "ContractType", "ContractAmount", "StartDate", "EndDate", "SignDate", "AgreedPayTime", "ContractStatus", "RegisterStatus", "Remark", "ServiceRemark", "HasPayment", "NewCompany", "ModifyTime", } nowTime := time.Now().Local() item.ContractCode = req.ContractCode item.RelateContractCode = req.RelateContractCode item.CrmContractId = req.CrmContractId item.ContractSource = req.ContractSource item.CompanyName = req.CompanyName item.ActualCompanyName = req.ActualCompanyName item.SellerId = req.SellerId item.SellerName = req.SellerName item.ContractType = req.ContractType item.ContractAmount = req.ContractAmount item.StartDate = startDate item.EndDate = endDate item.SignDate = signDate item.AgreedPayTime = req.AgreedPayTime item.ContractStatus = req.ContractStatus item.RegisterStatus = fms.ContractRegisterStatusIng item.Remark = req.Remark item.ServiceRemark = req.ServiceRemark item.HasPayment = req.HasPayment item.NewCompany = req.NewCompany item.ModifyTime = nowTime // 存在代付的直接完成登记, 且不允许进行开票/到款登记 if req.HasPayment == 1 { item.RegisterStatus = fms.ContractRegisterStatusComplete updateCols = append(updateCols, "RegisterStatus") } // 开票到款信息 invoiceOB := new(fms.ContractInvoice) invoiceCond := `contract_register_id = ?` invoicePars := make([]interface{}, 0) invoicePars = append(invoicePars, req.ContractRegisterId) invoiceList, e := invoiceOB.List(invoiceCond, invoicePars, "") if e != nil { resp.FailMsg("操作失败", "获取合同开票到款列表失败, Err: "+e.Error(), c) return } invoiceUpdateCols := make([]string, 0) // 开票到款操作类型: 0-无; 1-更新; 2-删除; invoiceHandleType := 0 if originHasPayment == 0 && req.HasPayment == 1 { // 若从无代付修改为有代付, 则删除无代付期间新增的所有开票到款登记 invoiceHandleType = 2 } else { // 修改了货币单位后，同步更新汇率及开票到款的换算金额 if req.CurrencyUnit != item.CurrencyUnit { rateList, e := fmsService.GetTodayCurrencyRateList() if e != nil { resp.FailMsg("操作失败", "获取货币列表及汇率失败, Err: "+e.Error(), c) return } var rate float64 for i := range rateList { if rateList[i].Code == req.CurrencyUnit { rate = rateList[i].RMBRate break } } if rate < 0 { resp.FailMsg("操作失败", "货币汇率有误", c) return } item.CurrencyUnit = req.CurrencyUnit item.RMBRate = rate updateCols = append(updateCols, "CurrencyUnit", "RMBRate") // 调整开票到款换算后的金额, 保留两位小数 for i := range invoiceList { invoiceList[i].CurrencyUnit = req.CurrencyUnit a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceList[i].OriginAmount/rate), 64) invoiceList[i].Amount = a } invoiceUpdateCols = append(invoiceUpdateCols, "CurrencyUnit", "Amount") invoiceHandleType = 1 } } // 套餐信息 serviceList, e := fmsService.HandleContractServiceAndDetail(req.ProductId, req.Services, true) if e != nil { resp.FailMsg("操作失败", "获取合同套餐详情失败, Err: "+e.Error(), c) return } // 更新合同登记、套餐、开票到款 if e = fms.UpdateContractRegister(item, updateCols, serviceList, invoiceList, invoiceUpdateCols, invoiceHandleType); e != nil { resp.FailMsg("操作失败", "更新合同及套餐失败, Err: "+e.Error(), c) return } // 校验金额-是否修改状态 go fmsService.CheckContractRegisterAmount(item.ContractRegisterId) // 操作日志 go func() { opData := "" opDataByte, e := json.Marshal(req) if e != nil { return } opData = string(opDataByte) logItem := new(fms.ContractRegisterLog) logItem.ContractRegisterId = item.ContractRegisterId logItem.AdminId = int(adminInfo.AdminId) logItem.AdminName = adminInfo.RealName logItem.OpData = opData logItem.OpType = fms.ContractRegisterOpTypeEdit logItem.CreateTime = nowTime logItem.Remark = req.Remark if e = logItem.Create(); e != nil { return } }() resp.Ok("操作成功", c) } // Del // @Title 删除合同登记 // @Description 删除合同登记 // @Param request body fms.ContractRegisterDelReq true "type json string" // @Success 200 string "操作成功" // @router /contract/register/del [post] func (rg *RegisterController) Del(c *gin.Context) { req := new(fms.ContractRegisterDelReq) err := c.ShouldBind(&req) if err != nil { errs, ok := err.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+err.Error(), c) return } resp.FailData("参数解析失败", errs.Translate(global.Trans), c) return } claims, _ := c.Get("adminInfo") adminInfo := claims.(*system.SysAdmin) ob := new(fms.ContractRegister) item, e := ob.Fetch(req.ContractRegisterId) if e != nil { if e == utils.ErrNoRow { resp.Fail("合同登记不存在或已被删除", c) return } resp.FailMsg("获取合同登记失败", "Err:"+e.Error(), c) return } nowTime := time.Now().Local() item.IsDeleted = 1 item.ModifyTime = nowTime updateCols := []string{"IsDeleted", "ModifyTime"} if e = item.Update(updateCols); e != nil { resp.FailMsg("操作失败", "更新合同登记失败, Err:"+e.Error(), c) return } // 删除对应的开票到款登记 go func() { if e = fms.DeleteContractInvoicesByRegisterId(item.ContractRegisterId); e != nil { alarm_msg.SendAlarmMsg("删除合同登记后, 删除开票到款记录失败, ErrMsg: "+err.Error(), 3) } }() // 操作日志 go func() { opData := "" opDataByte, e := json.Marshal(req) if e != nil { return } opData = string(opDataByte) logItem := new(fms.ContractRegisterLog) logItem.ContractRegisterId = req.ContractRegisterId logItem.AdminId = int(adminInfo.AdminId) logItem.AdminName = adminInfo.RealName logItem.OpData = opData logItem.OpType = fms.ContractRegisterOpTypeDel logItem.CreateTime = nowTime if e = logItem.Create(); e != nil { return } }() resp.Ok("操作成功", c) } // Detail // @Title 合同登记详情 // @Description 合同登记详情 // @Param ContractRegisterId query int false "合同登记ID" // @Success 200 {object} fms.ContractRegisterDetail // @router /contract/register/detail [get] func (rg *RegisterController) Detail(c *gin.Context) { var req fms.ContractRegisterDetailReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } result := new(fms.ContractRegisterDetail) // 合同登记信息 item, e := fms.GetContractRegisterItemById(req.ContractRegisterId) if e != nil { resp.FailData("获取失败", "获取合同登记详情失败, Err:"+e.Error(), c) return } result.ContractRegisterItem = item // 套餐信息 serviceList, e := fmsService.GetContractServiceAndDetail(req.ContractRegisterId) if e != nil { resp.FailData("获取失败", "获取合同套餐信息失败, Err: "+e.Error(), c) return } result.ServiceList = serviceList // 开票/到款信息 invoiceCond := `contract_register_id = ?` invoicePars := make([]interface{}, 0) invoicePars = append(invoicePars, req.ContractRegisterId) invoiceList, e := fms.GetContractInvoiceItemList(invoiceCond, invoicePars) if e != nil { resp.FailData("获取失败", "获取合同开票/到款信息失败, Err: "+e.Error(), c) return } result.InvoiceList = make([]*fms.ContractInvoiceItem, 0) result.PaymentList = make([]*fms.ContractInvoiceItem, 0) for i := range invoiceList { if invoiceList[i].InvoiceType == fms.ContractInvoiceTypeMake { result.InvoiceList = append(result.InvoiceList, invoiceList[i]) continue } if invoiceList[i].InvoiceType == fms.ContractInvoiceTypePay { result.PaymentList = append(result.PaymentList, invoiceList[i]) } } // 合同登记进度 logCond := `contract_register_id = ?` logPars := make([]interface{}, 0) logPars = append(logPars, req.ContractRegisterId) logList, e := fms.GetContractRegisterLogItemList(logCond, logPars) if e != nil { resp.FailData("获取失败", "获取合同登记进度失败, Err: "+e.Error(), c) return } result.Logs = logList resp.OkData("获取成功", result, c) } // UpdateStatus // @Title 修改合同状态 // @Description 修改合同状态 // @Param request body fms.ContractRegisterUpdateStatusReq true "type json string" // @Success 200 string "操作成功" // @router /contract/register/update_status [post] func (rg *RegisterController) UpdateStatus(c *gin.Context) { req := new(fms.ContractRegisterUpdateStatusReq) err := c.ShouldBind(&req) if err != nil { errs, ok := err.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+err.Error(), c) return } resp.FailData("参数解析失败", errs.Translate(global.Trans), c) return } claims, _ := c.Get("adminInfo") adminInfo := claims.(*system.SysAdmin) ob := new(fms.ContractRegister) item, e := ob.Fetch(req.ContractRegisterId) if e != nil { if e == utils.ErrNoRow { resp.Fail("合同登记不存在或已被删除", c) return } resp.FailMsg("获取合同登记失败", "Err:"+e.Error(), c) return } nowTime := time.Now().Local() item.ContractStatus = req.ContractStatus item.ModifyTime = nowTime updateCols := []string{"ContractStatus", "ModifyTime"} if e = item.Update(updateCols); e != nil { resp.FailMsg("操作失败", "更新合同登记失败, Err:"+e.Error(), c) return } // 校验金额-是否修改状态 go fmsService.CheckContractRegisterAmount(req.ContractRegisterId) // 操作日志 go func() { opData := "" opDataByte, e := json.Marshal(req) if e != nil { return } opData = string(opDataByte) logItem := new(fms.ContractRegisterLog) logItem.ContractRegisterId = req.ContractRegisterId logItem.AdminId = int(adminInfo.AdminId) logItem.AdminName = adminInfo.RealName logItem.OpData = opData logItem.OpType = fms.ContractRegisterOpTypeStatus logItem.CreateTime = nowTime if e = logItem.Create(); e != nil { return } }() resp.Ok("操作成功", c) } // Invoice // @Title 开票/到款登记 // @Description 开票/到款登记 // @Param request body fms.ContractInvoiceSaveReq true "type json string" // @Success 200 string "操作成功" // @router /contract/register/invoice [post] func (rg *RegisterController) Invoice(c *gin.Context) { req := new(fms.ContractInvoiceSaveReq) err := c.ShouldBind(&req) if err != nil { errs, ok := err.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+err.Error(), c) return } resp.FailData("参数解析失败", errs.Translate(global.Trans), c) return } claims, _ := c.Get("adminInfo") adminInfo := claims.(*system.SysAdmin) registerOB := new(fms.ContractRegister) item, e := registerOB.Fetch(req.ContractRegisterId) if e != nil { if e == utils.ErrNoRow { resp.Fail("合同登记不存在或已被删除", c) return } resp.FailMsg("获取合同登记失败", "Err:"+e.Error(), c) return } // 合同存在代付不允许开票/到款登记 if item.HasPayment == 1 { resp.Fail("合同存在代付不允许添加开票/到款登记", c) return } noChangeInvoiceIds := make([]int, 0) newInvoice := make([]*fms.ContractInvoice, 0) if len(req.AmountList) > 0 { // 合同有效时长(计算付款方式) dayDiff := item.EndDate.Sub(item.StartDate).Hours() / 24 // 获取销售分组信息 sellerList, e := crmService.GetSellerDepartmentListWithGroupAndTeam() if e != nil { resp.FailData("获取销售失败", "Err:"+e.Error(), c) return } sellerMap := make(map[int]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerId] = sellerList[i] } for i := range req.AmountList { if req.AmountList[i].Amount <= 0 { resp.Fail("登记金额有误", c) return } if req.AmountList[i].InvoiceDate == "" { resp.Fail("请选择日期", c) return } // 开票登记销售必填 if req.InvoiceType == fms.ContractInvoiceTypeMake && req.AmountList[i].SellerId <= 0 { resp.Fail("请选择销售", c) return } t, e := time.ParseInLocation(utils.FormatDate, req.AmountList[i].InvoiceDate, time.Local) if e != nil { resp.FailData("日期格式有误", "Err:"+e.Error(), c) return } if req.AmountList[i].InvoiceId > 0 { noChangeInvoiceIds = append(noChangeInvoiceIds, req.AmountList[i].InvoiceId) continue } if req.AmountList[i].InvoiceId == 0 { v := &fms.ContractInvoice{ ContractRegisterId: req.ContractRegisterId, ContractCode: item.ContractCode, Amount: req.AmountList[i].Amount, OriginAmount: req.AmountList[i].OriginAmount, CurrencyUnit: item.CurrencyUnit, InvoiceType: req.InvoiceType, InvoiceDate: t, AdminId: int(adminInfo.AdminId), AdminName: adminInfo.RealName, Remark: req.AmountList[i].Remark, } v.Set() // 开票登记-销售信息 if req.InvoiceType == fms.ContractInvoiceTypeMake { sellerItem := sellerMap[req.AmountList[i].SellerId] if sellerItem == nil { resp.Fail("销售信息异常", c) return } v.SellerId = sellerItem.SellerId v.SellerName = sellerItem.SellerName v.SellerGroupId = sellerItem.GroupId v.SellerGroupName = sellerItem.GroupName v.SellerTeamId = sellerItem.TeamId v.SellerTeamName = sellerItem.TeamName } // 到款登记-付款方式 if req.InvoiceType == fms.ContractInvoiceTypePay { v.PayType = fmsService.CalculateContractPaymentType(req.AmountList[i].Amount, item.ContractAmount, dayDiff) } newInvoice = append(newInvoice, v) } } } // 获取原有的登记信息 invoiceCond := `contract_register_id = ? AND invoice_type = ?` invoicePars := make([]interface{}, 0) invoicePars = append(invoicePars, req.ContractRegisterId, req.InvoiceType) originInvoiceList, e := fms.GetContractInvoiceItemList(invoiceCond, invoicePars) if e != nil { resp.FailMsg("获取失败", "获取开票/到款列表失败, Err: "+e.Error(), c) return } // 比对原有和现有的登记信息 logList := make([]*fms.ContractRegisterLog, 0) opData := "" opDataByte, e := json.Marshal(req) if e != nil { return } opData = string(opDataByte) opType := fms.ContractRegisterOpTypeInvoice if req.InvoiceType == fms.ContractInvoiceTypePay { opType = fms.ContractRegisterOpTypePayment } nowTime := time.Now().Local() // 需要删除的记录 deleteInvoiceIds := make([]int, 0) for i := range originInvoiceList { if !utils.InArrayByInt(noChangeInvoiceIds, originInvoiceList[i].ContractInvoiceId) { deleteInvoiceIds = append(deleteInvoiceIds, originInvoiceList[i].ContractInvoiceId) logList = append(logList, &fms.ContractRegisterLog{ ContractRegisterId: req.ContractRegisterId, AdminId: int(adminInfo.AdminId), AdminName: adminInfo.RealName, OpData: opData, OpType: opType, CreateTime: nowTime, AmountRemark: fmt.Sprint("删除", fms.ContractInvoiceKeyNameMap[opType], "金额", originInvoiceList[i].Amount, "元"), }) } } // 新增的记录 if len(newInvoice) > 0 { newAmount := decimal.NewFromFloat(0).Round(2) for i := range newInvoice { a := decimal.NewFromFloat(newInvoice[i].Amount).Round(2) newAmount = newAmount.Add(a) } ia, _ := newAmount.Round(2).Float64() logList = append(logList, &fms.ContractRegisterLog{ ContractRegisterId: req.ContractRegisterId, AdminId: int(adminInfo.AdminId), AdminName: adminInfo.RealName, OpData: opData, OpType: opType, CreateTime: nowTime, AmountRemark: fmt.Sprint("新增", fms.ContractInvoiceKeyNameMap[opType], "金额", ia, "元"), }) } // 删除并新增登记 ob := new(fms.ContractInvoice) if e := ob.DeleteAndCreateNewInvoice(req.ContractRegisterId, req.InvoiceType, deleteInvoiceIds, newInvoice); e != nil { resp.FailData("日期格式有误", "Err:"+e.Error(), c) return } // 校验金额-是否修改状态 go fmsService.CheckContractRegisterAmount(req.ContractRegisterId) // 操作日志 go func() { logOB := new(fms.ContractRegisterLog) if e := logOB.AddInBatches(logList); e != nil { return } }() resp.Ok("操作成功", c) } // Export // @Title 合同登记-导出 // @Description 合同登记-导出 // @Param Keyword query string false "关键词" // @Param StartDate query string false "合同开始日期" // @Param EndDate query string false "合同结束日期" // @Param ServiceType query int false "套餐类型" // @Param ContractType query int false "合同类型" // @Param RegisterStatus query int false "登记状态" // @Success 200 string "操作成功" // @router /contract/register/export [get] func (rg *RegisterController) Export(c *gin.Context) { var req fms.ContractRegisterListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } cond := `1 = 1` pars := make([]interface{}, 0) // 合同编号/客户姓名/销售/实际使用方 if req.Keyword != "" { kw := "%" + req.Keyword + "%" cond += ` AND (company_name LIKE ? OR contract_code LIKE ? OR seller_name LIKE ? OR actual_company_name LIKE ?)` pars = append(pars, kw, kw, kw, kw) } if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (create_time BETWEEN ? AND ?)` pars = append(pars, st, ed) } if req.ContractType != 0 { cond += ` AND contract_type = ?` pars = append(pars, req.ContractType) } if req.RegisterStatus != 0 { cond += ` AND register_status = ?` pars = append(pars, req.RegisterStatus) } if req.ServiceType != 0 { registerIds, e := fms.GetContractRegisterIdsByTempId(req.ServiceType) if e != nil { resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) return } if len(registerIds) > 0 { cond += ` AND contract_register_id IN ?` pars = append(pars, registerIds) } else { cond += ` AND 1 = 2` } } // 获取列表数据 cr := new(fms.ContractRegister) list, e := cr.List(cond, pars) if e != nil { resp.FailData("获取合同列表失败", "Err:"+e.Error(), c) return } if len(list) == 0 { resp.Fail("无有效数据可导出", c) return } registerIds := make([]int, 0) for i := range list { registerIds = append(registerIds, list[i].ContractRegisterId) } // 获取小套餐品种 cpCond := `product_id = ? AND permission_name <> ?` cpPars := make([]interface{}, 0) cpPars = append(cpPars, crm.CompanyProductFicc, crm.ChartPermissionStrategyName) cp := new(crm.ChartPermission) permissionList, e := cp.List(cpCond, cpPars) if e != nil { resp.FailData("获取小套餐品种失败", "Err:"+e.Error(), c) return } permissionLen := len(permissionList) permissionNameIdMap := make(map[string]int) for i := range permissionList { permissionNameIdMap[permissionList[i].PermissionName] = permissionList[i].ChartPermissionId } // 获取套餐服务 serviceList, e := fms.GetContractServiceTemplateMapByProductId(crm.CompanyProductFicc) if e != nil { resp.FailData("获取套餐服务失败", "Err:"+e.Error(), c) return } // 除大小套餐外的其他套餐(下面的导出需要以此动态处理) otherService := make([]*fms.ContractServiceTemplateItem, 0) for i := range serviceList { if serviceList[i].Title != "FICC大套餐" && serviceList[i].Title != "FICC小套餐" { otherService = append(otherService, serviceList[i]) } } otherServiceLen := len(otherService) // 套餐/开票/到款列表 serviceMap := make(map[int][]*fms.ContractService) serviceChartPermissionsMap := make(map[int][]int) invoiceMap := make(map[int][]*fms.ContractInvoice) paymentMap := make(map[int][]*fms.ContractInvoice) maxInvoice := 0 maxPayment := 0 if len(registerIds) > 0 { // 获取套餐信息 csCond := `contract_register_id IN ?` csPars := make([]interface{}, 0) csPars = append(csPars, registerIds) cs := new(fms.ContractService) serviceList, e := cs.List(csCond, csPars) if e != nil { resp.FailData("获取合同套餐列表失败", "Err:"+e.Error(), c) return } for i := range serviceList { cid := serviceList[i].ContractRegisterId if serviceMap[cid] == nil { serviceMap[cid] = make([]*fms.ContractService, 0) } serviceMap[cid] = append(serviceMap[cid], serviceList[i]) // 小套餐权限 if serviceChartPermissionsMap[cid] == nil { serviceChartPermissionsMap[cid] = make([]int, 0) } if serviceList[i].ChartPermissionIds != "" { ids := utils.JoinStr2IntArr(serviceList[i].ChartPermissionIds, ",") serviceChartPermissionsMap[cid] = append(serviceChartPermissionsMap[cid], ids...) } } // 获取开票/到款详情, 并取最大的开票/到款数(用于动态扩展第二列表头) ci := new(fms.ContractInvoice) invoiceList, e := ci.List(csCond, csPars, "") if e != nil { resp.FailData("获取开票/到款列表失败", "Err:"+e.Error(), c) return } for k := range invoiceList { cid := invoiceList[k].ContractRegisterId if invoiceMap[cid] == nil { invoiceMap[cid] = make([]*fms.ContractInvoice, 0) } if paymentMap[cid] == nil { paymentMap[cid] = make([]*fms.ContractInvoice, 0) } if invoiceList[k].InvoiceType == fms.ContractInvoiceTypeMake { invoiceMap[cid] = append(invoiceMap[cid], invoiceList[k]) continue } if invoiceList[k].InvoiceType == fms.ContractInvoiceTypePay { paymentMap[cid] = append(paymentMap[cid], invoiceList[k]) continue } } // 取最大开票/到款数 for j := range invoiceMap { if len(invoiceMap[j]) > maxInvoice { maxInvoice = len(invoiceMap[j]) } } for p := range paymentMap { if len(paymentMap[p]) > maxPayment { maxPayment = len(paymentMap[p]) } } } // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheetName := "财务列表" sheet, err := xlsxFile.AddSheet(sheetName) if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } // 1行表头 titleRow := sheet.AddRow() titleRow.SetHeight(40) // 1行1列-右合并两格 cell1 := titleRow.AddCell() cell1.HMerge = 3 cell1.SetString("FICC客户签约表 2022") cell1.SetStyle(style) // 右增两列空白格用于第一列合并, 否则后续单元格合并会有问题 titleRow.AddCell().SetString("") titleRow.AddCell().SetString("") titleRow.AddCell().SetString("") // 1行2列 cell2 := titleRow.AddCell() cell2.SetString("FICC大套餐") cell2.SetStyle(style) // 1行3列-右合并小套餐数 if permissionLen >= 1 { cell3 := titleRow.AddCell() cell3.HMerge = permissionLen - 1 cell3.SetString("FICC小套餐") cell3.SetStyle(style) // 同上右增单元格小套餐数-1的空白单元格用于合并 for i := 0; i < permissionLen-1; i++ { titleRow.AddCell().SetString("") } } for i := range otherService { cellOther := titleRow.AddCell() cellOther.SetString(otherService[i].Title) cellOther.SetStyle(style) } // 第二行表头 titleRow2 := sheet.AddRow() titleRow2.SetHeight(60) row2Title := make([]string, 0) row2Title = append(row2Title, "客户名称", "新客户\n0-是\n1-否", "合同类型\n续约-0\n新增-1\n代付-2\n补充协议-3", "销售", "FICC大套餐") for i := range permissionList { row2Title = append(row2Title, permissionList[i].PermissionName) } // 其他套餐 for i := range otherService { row2Title = append(row2Title, otherService[i].Title) } row2Title = append(row2Title, "套餐备注", "开始时间", "到期时间", "2022年合同金额", "金额单位", "约定付款时间", "签订日", "合同状态", "合同编号", "合规备注") // 设置表头 for i := range row2Title { v := titleRow2.AddCell() v.SetString(row2Title[i]) v.SetStyle(style) } // 第二行表头-开票/收款(动态添加) invoiceTitle := []string{"开票日", "开票金额", "销售", "备注"} for i := 0; i < maxInvoice; i++ { n := i + 1 for ii := range invoiceTitle { c := titleRow2.AddCell() t := fmt.Sprintf("%s%d", invoiceTitle[ii], n) c.SetString(t) c.SetStyle(style) row2Title = append(row2Title, t) } } paymentTitle := []string{"到款日", "到款金额", "备注"} for i := 0; i < maxPayment; i++ { n := i + 1 for ii := range paymentTitle { c := titleRow2.AddCell() t := fmt.Sprintf("%s%d", paymentTitle[ii], n) c.SetString(t) c.SetStyle(style) row2Title = append(row2Title, t) } } // 此处取第二行标题NameKeyMap, 后面的动态匹配 row2NameKeyMap := make(map[string]int) for i := range row2Title { row2NameKeyMap[row2Title[i]] = i } newCompanyMap := map[int]string{0: "1", 1: "0"} contractTMap := map[int]int{ fms.ContractTypeNew: 1, fms.ContractTypeRenew: 0, fms.ContractTypeAgentPay: 2, // 代付合同 fms.ContractTypePlus: 3, // 补充协议 } for _, v := range list { k := -1 // 套餐匹配用 dataRow := sheet.AddRow() dataRow.SetHeight(20) k += 4 dataRow.AddCell().SetString(v.CompanyName) dataRow.AddCell().SetString(newCompanyMap[v.NewCompany]) dataRow.AddCell().SetString(fmt.Sprint(contractTMap[v.ContractType])) dataRow.AddCell().SetString(v.SellerName) // 大套餐 k += 1 col4Name := row2Title[k] svList := serviceMap[v.ContractRegisterId] col4 := "" if svList != nil && len(svList) > 0 { for isv := range svList { if svList[isv].Title == col4Name { col4 = "是" break } } } dataRow.AddCell().SetString(col4) // 小套餐 serviceChartPermissionIds := serviceChartPermissionsMap[v.ContractRegisterId] for i := 0; i < permissionLen; i++ { k += 1 colName := row2Title[k] chartPermissionId := permissionNameIdMap[colName] if utils.InArray(chartPermissionId, serviceChartPermissionIds) { dataRow.AddCell().SetString("是") } else { dataRow.AddCell().SetString("") } } // 除大小套餐外的其他套餐(处理方式其实跟上面的大套餐一样, 只是中间隔了小套餐按照顺序要这么处理=_=!) for i := 0; i < otherServiceLen; i++ { k += 1 otherColName := row2Title[k] otherCol := "" if svList != nil && len(svList) > 0 { for isv := range svList { if svList[isv].Title == otherColName { otherCol = "是" break } } } dataRow.AddCell().SetString(otherCol) } // 其他信息 dataRow.AddCell().SetString(v.ServiceRemark) // 套餐备注 dataRow.AddCell().SetString(utils.TimeTransferString("2006/01/02", v.StartDate)) // 开始时间 dataRow.AddCell().SetString(utils.TimeTransferString("2006/01/02", v.EndDate)) // 到期时间 dataRow.AddCell().SetString(fmt.Sprint("￥", v.ContractAmount)) // 2022年合同金额 dataRow.AddCell().SetString(v.CurrencyUnit) // 货币单位 dataRow.AddCell().SetString(v.AgreedPayTime) // 约定付款时间 dataRow.AddCell().SetString(utils.TimeTransferString("2006/01/02", v.SignDate)) // 签订日 dataRow.AddCell().SetString(fms.ContractStatusKeyNameMap[v.ContractStatus]) // 合同状态 dataRow.AddCell().SetString(v.ContractCode) // 合同编号 dataRow.AddCell().SetString(v.Remark) // 合规备注 // 开票/到款信息 ivList := invoiceMap[v.ContractRegisterId] ivListLen := len(ivList) if ivList != nil && len(ivList) > 0 { for ia := 0; ia < maxInvoice; ia++ { if ia < ivListLen { dataRow.AddCell().SetString(utils.TimeTransferString("2006/01/02", ivList[ia].InvoiceDate)) // 开票日 dataRow.AddCell().SetString(fmt.Sprint(ivList[ia].Amount)) // 开票金额 dataRow.AddCell().SetString(ivList[ia].SellerName) // 销售名称 dataRow.AddCell().SetString(ivList[ia].Remark) // 开票备注 } else { // 这里要把不够的填充为空 dataRow.AddCell().SetString("") dataRow.AddCell().SetString("") dataRow.AddCell().SetString("") dataRow.AddCell().SetString("") } } } pyList := paymentMap[v.ContractRegisterId] pyListLen := len(pyList) if pyList != nil && pyListLen > 0 { for ib := 0; ib < maxPayment; ib++ { if ib < pyListLen { dataRow.AddCell().SetString(utils.TimeTransferString("2006/01/02", pyList[ib].InvoiceDate)) // 收款日 dataRow.AddCell().SetString(fmt.Sprint(pyList[ib].Amount)) // 收款金额 dataRow.AddCell().SetString(pyList[ib].Remark) // 收款备注 } else { // 已经是最后的几列了其实可以不用填充空, 万一后面要加列此处还是填充上吧 dataRow.AddCell().SetString("") dataRow.AddCell().SetString("") dataRow.AddCell().SetString("") } } } } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := sheetName + randStr + ".xlsx" c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName)) c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content) } // InvoiceList // @Title 开票/到款列表 // @Description 开票/到款列表 // @Param InvoiceType query int false "类型: 1-开票登记; 2-到款登记" // @Param ContractCode query string false "合同编号" // @Param StartDate query string false "开始日期" // @Param EndDate query string false "结束日期" // @Param MinAmount query float64 false "开票金额区间-最小值" // @Param MaxAmount query float64 false "开票金额区间-最大值" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Success 200 {object} fms.ContractInvoiceItem // @router /contract/register/invoice_list [get] func (rg *RegisterController) InvoiceList(c *gin.Context) { var req fms.ContractInvoiceListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } pageSize := req.PageSize pageIndex := req.Current if pageSize <= 0 { pageSize = utils.PageSize20 } if pageIndex <= 0 { pageIndex = 1 } cond := `invoice_type = ?` pars := make([]interface{}, 0) pars = append(pars, req.InvoiceType) // 合同编号 if req.ContractCode != "" { kw := fmt.Sprint("%", req.ContractCode, "%") pars = append(pars, kw) // 开票列表同时模糊查询销售名称 if req.InvoiceType == fms.ContractInvoiceTypeMake { cond += ` AND (contract_code LIKE ? OR seller_name LIKE ?)` pars = append(pars, kw) } else { cond += ` AND contract_code LIKE ?` } } if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (invoice_time BETWEEN ? AND ?)` pars = append(pars, st, ed) } if req.MinAmount > 0 { cond += ` AND amount >= ?` pars = append(pars, req.MinAmount) } if req.MaxAmount > 0 { cond += ` AND amount <= ?` pars = append(pars, req.MaxAmount) } // 货币列表 currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c) return } unitMap := make(map[string]string) currencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) for i := range currencyList { unitMap[currencyList[i].Code] = currencyList[i].UnitName currencyTotals = append(currencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) } page := new(base.Page) page.SetPageSize(pageSize) page.SetCurrent(pageIndex) page.AddOrderItem(base.OrderItem{Column: "invoice_time", Asc: false}) if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } total, list, e := fms.GetContractInvoiceItemPageList(page, cond, pars) if e != nil { resp.FailMsg("获取失败", "获取合同开票/到款列表失败, Err: "+e.Error(), c) return } page.SetTotal(total) for i := range list { list[i].UnitName = unitMap[list[i].CurrencyUnit] } // 分币种合计金额 var amountTotal float64 sumList, e := fms.GetInvoiceListCurrencySum(cond, pars, "currency_unit") if e != nil { resp.FailMsg("获取失败", "获取开票/到款列表合计金额失败, Err: "+e.Error(), c) return } sumMap := make(map[string]float64) for i := range sumList { amountTotal += sumList[i].AmountTotal sumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal } for i := range currencyTotals { currencyTotals[i].Amount = sumMap[currencyTotals[i].Code] } respData := &fms.InvoiceListRespData{ Page: page, List: list, AmountTotal: amountTotal, CurrencyTotal: currencyTotals, } // 是否导出 if req.IsExport == 1 { ExportInvoiceList(c, req, respData) return } resp.OkData("获取成功", respData, c) } // ExportInvoiceList 导出开票/到款列表 func ExportInvoiceList(c *gin.Context, req fms.ContractInvoiceListReq, results *fms.InvoiceListRespData) { list := make([]*fms.ContractInvoiceItem, 0) if val, ok := results.List.([]*fms.ContractInvoiceItem); ok { list = val } else { resp.Fail("列表数据有误", c) return } if len(list) == 0 { resp.Fail("列表数据为空", c) return } listName := "开票" if req.InvoiceType == fms.ContractInvoiceTypePay { listName = "到款" } // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheet, err := xlsxFile.AddSheet(fmt.Sprintf("%s列表", listName)) if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } // 前两行显示合计金额, 第三行空出与列表数据隔一行 rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString(fmt.Sprintf("已开票合计金额（换算后）：%.2f（元）", results.AmountTotal)) rowBData := "已开票金额：" for _, v := range results.CurrencyTotal { rowBData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName) } rowB := sheet.AddRow() rowB.AddCell().SetString(rowBData) sheet.AddRow() // 列表数据表头 titles := []string{"合同编号", fmt.Sprintf("%s金额", listName), "金额单位", "换算金额（元）", fmt.Sprintf("%s日期", listName), "销售", "备注"} titleRow := sheet.AddRow() titleRow.SetHeight(40) for i := range titles { c := titleRow.AddCell() c.SetString(titles[i]) c.SetStyle(style) } // 单元格赋值 for _, v := range list { dataRow := sheet.AddRow() dataRow.SetHeight(20) dataRow.AddCell().SetString(v.ContractCode) // 合同编号 dataRow.AddCell().SetString(fmt.Sprint(v.OriginAmount)) // 开票金额 dataRow.AddCell().SetString(v.UnitName) // 金额单位 dataRow.AddCell().SetString(fmt.Sprint(v.Amount)) // 换算金额(元) dataRow.AddCell().SetString(v.InvoiceDate) // 开票日 dataRow.AddCell().SetString(v.SellerName) // 销售 dataRow.AddCell().SetString(v.Remark) // 备注 } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := fmt.Sprintf("%s列表_%s.xlsx", listName, randStr) c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName)) c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content) } // Import // @Title 合同登记-导入 // @Description 合同登记-导入 // @Success 200 string "操作成功" // @router /contract/register/import [post] func (rg *RegisterController) Import(c *gin.Context) { h, err := c.FormFile("File") if err != nil { resp.FailData("获取文件失败", "Err:"+err.Error(), c) return } ext := path.Ext(h.Filename) if ext != ".xlsx" && ext != ".xls" { resp.Fail("请上传Excel文件", c) return } uploadDir := "static/xls" err = os.MkdirAll(uploadDir, 766) if err != nil { resp.FailData("存储目录创建失败", "Err:"+err.Error(), c) return } uploadPath := uploadDir + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + h.Filename err = c.SaveUploadedFile(h, uploadPath) if err != nil { resp.FailData("保存本地文件失败", "Err:"+err.Error(), c) return } defer func() { _ = os.Remove(uploadPath) }() xlFile, err := xlsx.OpenFile(uploadPath) if err != nil { resp.FailData("打开文件失败", "Err:"+err.Error(), c) return } // 获取所有已登记，根据合同编号去重 contractCodeArr := make([]string, 0) registerOB := new(fms.ContractRegister) registerCond := `` registerPars := make([]interface{}, 0) registerList, e := registerOB.List(registerCond, registerPars) if e != nil { resp.FailData("获取合同登记列表失败", "Err:"+e.Error(), c) return } for i := range registerList { contractCodeArr = append(contractCodeArr, registerList[i].ContractCode) } // 获取所有销售Map sellerList, e := crmService.GetSellerDepartmentListWithGroupAndTeam() if e != nil { resp.FailData("获取销售列表失败", "Err:"+e.Error(), c) return } sellerMap := make(map[string]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerName] = sellerList[i] } // 获取品种权限Map chartPermissionNameIdMap := make(map[string]int) cpCond := `product_id = ?` cpPars := make([]interface{}, 0) cpPars = append(cpPars, crm.CompanyProductFicc) cp := new(crm.ChartPermission) permissionList, e := cp.List(cpCond, cpPars) if e != nil { resp.FailData("获取权限列表失败", "Err:"+e.Error(), c) return } for i := range permissionList { chartPermissionNameIdMap[permissionList[i].PermissionName] = permissionList[i].ChartPermissionId } // 获取所有套餐模板 serviceTempCond := `` serviceTempPars := make([]interface{}, 0) serviceTempOB := new(fms.ContractServiceTemplate) serviceTempList, e := serviceTempOB.List(serviceTempCond, serviceTempPars) if e != nil { resp.FailData("获取套餐模板列表失败", "Err:"+e.Error(), c) return } serviceTempNameMap := make(map[string]*fms.ContractServiceTemplate) for i := range serviceTempList { serviceTempNameMap[serviceTempList[i].Title] = serviceTempList[i] } // 获取货币列表及汇率(汇率为导入日的汇率) rateList, e := fmsService.GetTodayCurrencyRateList() if e != nil { resp.FailData("获取货币列表及汇率失败", "Err:"+e.Error(), c) return } rateMap := make(map[string]float64) for i := range rateList { rateMap[rateList[i].Code] = rateList[i].RMBRate } titleMap := make(map[int]string) newIds := make([]int, 0) newCompanyArr := []string{"0", "1"} newCompanyMap := map[string]int{"0": 1, "1": 0} contractTypeArr := []string{"0", "1", "2", "3"} contractTypeMap := map[string]int{ "0": fms.ContractTypeRenew, "1": fms.ContractTypeNew, "2": fms.ContractTypeAgentPay, "3": fms.ContractTypePlus, } // 更新登记状态 defer func() { if len(newIds) > 0 { go func() { for i := range newIds { fmsService.CheckContractRegisterAmount(newIds[i]) } }() } }() invoiceMax := 3 checkDate := time.Date(1900, 1, 1, 0, 0, 0, 0, time.Local) for _, sheet := range xlFile.Sheets { // 遍历行读取 maxRow := sheet.MaxRow for i := 0; i < maxRow; i++ { // 第二行开始读取表头 if i == 1 { row := sheet.Row(i) cells := row.Cells for k, cell := range cells { text := cell.String() titleMap[k] = text // 只检验前面部分表头, 后面很多动态列 if k == 0 { if text != "客户名称" { resp.Fail("【客户名称】列名称有误, 请参考模板导入", c) return } } if k == 1 { if !strings.Contains(text, "新客户") { resp.Fail("【新客户】列名称有误, 请参考模板导入", c) return } } if k == 2 { if !strings.Contains(text, "合同类型") { resp.Fail("【合同类型】列名称有误, 请参考模板导入", c) return } } if k == 3 { if text != "销售" { resp.Fail("【销售】列名称有误, 请参考模板导入", c) return } } if k == 4 { if text != "FICC大套餐" { resp.Fail("【FICC大套餐】列名称有误, 请参考模板导入", c) return } } } } // 数据行 if i >= 2 { row := sheet.Row(i) cells := row.Cells // 登记信息 rowRegister := new(fms.ContractRegister) // 套餐 rowServices := make([]*fms.ContractService, 0) // 开票/到款 rowInvoices := make([]*fms.ContractInvoice, 0) for ir := 0; ir < invoiceMax; ir++ { rowInvoices = append(rowInvoices, new(fms.ContractInvoice)) } rowPayments := make([]*fms.ContractInvoice, 0) for ir := 0; ir < invoiceMax; ir++ { rowPayments = append(rowPayments, new(fms.ContractInvoice)) } // 小套餐权限 rowChartPermissionIdArr := make([]string, 0) isSkip := false for k, cell := range cells { v := utils.TrimStr(cell.String()) // 客户名称 if k == 0 { if v == "" { resp.Fail(fmt.Sprintf("第%d行客户名称不可为空, 请按模板导入", i+1), c) return } rowRegister.CompanyName = v continue } // 是否新客户 if k == 1 { if !utils.InArrayByStr(newCompanyArr, v) { resp.Fail(fmt.Sprintf("第%d行新客户有误, 请按模板导入", i+1), c) return } rowRegister.NewCompany = newCompanyMap[v] continue } // 合同类型 if k == 2 { if !utils.InArrayByStr(contractTypeArr, v) { resp.Fail(fmt.Sprintf("第%d行合同类型有误, 请按模板导入", i+1), c) return } rowRegister.ContractType = contractTypeMap[v] if rowRegister.ContractType == 0 { resp.Fail(fmt.Sprintf("第%d行合同类型匹配有误, 请按模板导入", i+1), c) return } continue } // 销售 if k == 3 { if v == "" { resp.Fail(fmt.Sprintf("第%d行销售名称不可为空, 请按模板导入", i+1), c) return } sellerItem := sellerMap[v] if sellerItem == nil { resp.Fail(fmt.Sprintf("第%d行销售名称与系统销售不匹配, 请核对名称后导入", i+1), c) return } rowRegister.SellerId = sellerItem.SellerId rowRegister.SellerName = sellerItem.SellerName continue } // FICC大套餐 if k == 4 { if v == "是" { tempItem := serviceTempNameMap[titleMap[k]] if tempItem == nil { resp.Fail(fmt.Sprintf("第%d行套餐名称不匹配, 请按模板导入", i+1), c) return } cs := &fms.ContractService{ ProductId: crm.CompanyProductFicc, ServiceTemplateId: tempItem.ServiceTemplateId, Title: tempItem.Title, Value: tempItem.Value, TableValue: tempItem.TableValue, ChartPermissionId: tempItem.ChartPermissionId, } cs.Set() rowServices = append(rowServices, cs) } continue } // FICC小套餐 if k >= 5 && k <= 25 { // 小套餐权限 if v == "是" { rowChartPermissionIdArr = append(rowChartPermissionIdArr, strconv.Itoa(chartPermissionNameIdMap[titleMap[k]])) } continue } // 其他类型套餐 if k >= 26 && k <= 32 { if v == "是" { tempItem := serviceTempNameMap[titleMap[k]] if tempItem == nil { resp.Fail(fmt.Sprintf("第%d行套餐名称不匹配, 请按模板导入", i+1), c) return } cs := &fms.ContractService{ ProductId: crm.CompanyProductFicc, ServiceTemplateId: tempItem.ServiceTemplateId, Title: tempItem.Title, Value: tempItem.Value, TableValue: tempItem.TableValue, ChartPermissionId: tempItem.ChartPermissionId, } cs.Set() rowServices = append(rowServices, cs) } continue } // 套餐备注 if k == 33 { rowRegister.ServiceRemark = v continue } // 开始时间/到期时间 if k == 34 { // 转换失败可能是因为格式为Excel日期格式, 读取出来会是一串数字, 将其转换成日期字符串再处理 va := cell.Value if va == "" { resp.Fail(fmt.Sprintf("第%d行开始时间不可为空, 请按模板导入", i+1), c) return } startDate, e := time.ParseInLocation("2006/01/02", va, time.Local) if e != nil { d := utils.ConvertToFormatDay(va, "2006/01/02") startDate, e = time.ParseInLocation("2006/01/02", d, time.Local) if e != nil { resp.Fail(fmt.Sprintf("第%d行开始时间格式转换有误, 请按模板导入", i+1), c) return } } // 转换后的日期小于1900-01-01表示当前生成的日期是有问题的 if startDate.Before(checkDate) { resp.Fail(fmt.Sprintf("第%d行开始时间格式有误, 请按模板导入", i+1), c) return } rowRegister.StartDate = startDate continue } if k == 35 { va := cell.Value if va == "" { resp.Fail(fmt.Sprintf("第%d行到期时间不可为空, 请按模板导入", i+1), c) return } endDate, e := time.ParseInLocation("2006/01/02", va, time.Local) if e != nil { d := utils.ConvertToFormatDay(va, "2006/01/02") endDate, e = time.ParseInLocation("2006/01/02", d, time.Local) if e != nil { resp.Fail(fmt.Sprintf("第%d行到期时间格式转换有误, 请按模板导入", i+1), c) return } } if endDate.Before(checkDate) { resp.Fail(fmt.Sprintf("第%d行到期时间格式有误, 请按模板导入", i+1), c) return } rowRegister.EndDate = endDate continue } // 合同金额 if k == 36 { amountStr := v amount, e := strconv.ParseFloat(amountStr, 64) if e != nil { resp.Fail(fmt.Sprintf("第%d行合同金额有误, 请按模板导入", i+1), c) return } rowRegister.ContractAmount = amount continue } // 金额单位 if k == 37 { rate := rateMap[v] if rate <= 0 { resp.Fail(fmt.Sprintf("第%d行金额单位有误, 请按模板导入", i+1), c) return } rowRegister.CurrencyUnit = v rowRegister.RMBRate = rate continue } // 约定付款日期 if k == 38 { rowRegister.AgreedPayTime = v continue } // 签订日 if k == 39 { va := cell.Value if va == "" { continue } signDate, e := time.ParseInLocation("2006/01/02", va, time.Local) if e != nil { d := utils.ConvertToFormatDay(va, "2006/01/02") signDate, e = time.ParseInLocation("2006/01/02", d, time.Local) if e != nil { resp.Fail(fmt.Sprintf("第%d行签订日格式转换有误, 请按模板导入", i+1), c) return } } if signDate.Before(checkDate) { resp.Fail(fmt.Sprintf("第%d行签订日格式有误, 请按模板导入", i+1), c) return } rowRegister.SignDate = signDate continue } // 合同状态 if k == 40 { rowRegister.ContractStatus = fms.ContractStatusNameKeyMap[v] if rowRegister.ContractStatus == 0 { resp.Fail(fmt.Sprintf("第%d行合同状态不匹配, 请按模板导入", i+1), c) return } continue } // 合同编号 if k == 41 { rowContractCode := v if rowContractCode == "" { resp.Fail(fmt.Sprintf("第%d行合同编号不可为空, 请按模板导入", i+1), c) return } if utils.InArrayByStr(contractCodeArr, rowContractCode) { // 此合同已登记, 跳过本行的读取 isSkip = true break } rowRegister.ContractCode = rowContractCode continue } // 合规备注 if k == 42 { rowRegister.Remark = v continue } // 开票列表 k2 := 42 for ir := 0; ir < invoiceMax; ir++ { n := ir + 1 // 开票日 k2 += 1 if k == k2 { if v != "" { va := cell.Value invoiceDate, e := time.ParseInLocation("2006/01/02", va, time.Local) if e != nil { d := utils.ConvertToFormatDay(va, "2006/01/02") invoiceDate, e = time.ParseInLocation("2006/01/02", d, time.Local) if e != nil { resp.Fail(fmt.Sprintf("第%d行开票时间%d格式转换有误, 请按模板导入", i+1, n), c) return } } if invoiceDate.Before(checkDate) { resp.Fail(fmt.Sprintf("第%d行开票时间%d格式有误, 请按模板导入", i+1, n), c) return } rowInvoices[ir].InvoiceDate = invoiceDate rowInvoices[ir].ContractCode = rowRegister.ContractCode rowInvoices[ir].InvoiceType = fms.ContractInvoiceTypeMake } continue } // 开票金额 k2 += 1 if k == k2 { if v != "" { amountStr := v amount, e := strconv.ParseFloat(amountStr, 64) if e != nil { resp.Fail(fmt.Sprintf("第%d行开票金额%d有误, 请按模板导入", i+1, n), c) return } rowInvoices[ir].Amount = amount } continue } // 开票销售 k2 += 1 if k == k2 { if v != "" { sellerItem := sellerMap[v] if sellerItem == nil { resp.Fail(fmt.Sprintf("第%d行开票销售名称与系统销售不匹配, 请核对名称后导入", i+1), c) return } rowInvoices[ir].SellerId = sellerItem.SellerId rowInvoices[ir].SellerName = sellerItem.SellerName rowInvoices[ir].SellerGroupId = sellerItem.GroupId rowInvoices[ir].SellerGroupName = sellerItem.GroupName rowInvoices[ir].SellerTeamId = sellerItem.TeamId rowInvoices[ir].SellerTeamName = sellerItem.TeamName } continue } // 备注 k2 += 1 if k == k2 { if v != "" { rowInvoices[ir].Remark = v } continue } } // 到款列表 for ir := 0; ir < invoiceMax; ir++ { n := ir + 1 // 到款日 k2 += 1 if k == k2 { if v != "" { va := cell.Value invoiceDate, e := time.ParseInLocation("2006/01/02", va, time.Local) if e != nil { d := utils.ConvertToFormatDay(va, "2006/01/02") invoiceDate, e = time.ParseInLocation("2006/01/02", d, time.Local) if e != nil { resp.Fail(fmt.Sprintf("第%d行到款时间%d格式转换有误, 请按模板导入", i+1, n), c) return } } if invoiceDate.Before(checkDate) { resp.Fail(fmt.Sprintf("第%d行到款时间%d格式有误, 请按模板导入", i+1, n), c) return } rowPayments[ir].InvoiceDate = invoiceDate rowPayments[ir].ContractCode = rowRegister.ContractCode rowPayments[ir].InvoiceType = fms.ContractInvoiceTypePay } continue } // 到款金额 k2 += 1 if k == k2 { if v != "" { amountStr := v amount, e := strconv.ParseFloat(amountStr, 64) if e != nil { resp.Fail(fmt.Sprintf("第%d行到款金额%d有误, 请按模板导入", i+1, n), c) return } // 付款方式 dayDiff := rowRegister.EndDate.Sub(rowRegister.StartDate).Hours() / 24 contractAmount := rowRegister.ContractAmount payType := fmsService.CalculateContractPaymentType(amount, contractAmount, dayDiff) rowPayments[ir].Amount = amount rowPayments[ir].PayType = payType } continue } // 备注 k2 += 1 if k == k2 { if v != "" { rowPayments[ir].Remark = v } continue } } } if isSkip { continue } // 小套餐 if len(rowChartPermissionIdArr) > 0 { // 说明有小套餐 tempItem := serviceTempNameMap["FICC小套餐"] if tempItem == nil { resp.Fail(fmt.Sprintf("第%d行小套餐名称不匹配, 请按模板导入", i+1), c) return } rowChartPermissionIds := strings.Join(rowChartPermissionIdArr, ",") cs := &fms.ContractService{ ProductId: crm.CompanyProductFicc, ServiceTemplateId: tempItem.ServiceTemplateId, Title: tempItem.Title, Value: tempItem.Value, TableValue: tempItem.TableValue, ChartPermissionId: tempItem.ChartPermissionId, ChartPermissionIds: rowChartPermissionIds, } cs.Set() rowServices = append(rowServices, cs) } // 如果导入的最后一条合同编号为空并且合同编号之后的字段均为空 // excel这个包读行的时候不会再往后面没数据的地方读取, 所以此处需要重新判断一次 if rowRegister.ContractCode == "" { resp.Fail(fmt.Sprintf("第%d行合同编号为空", i+1), c) return } rowRegister.RegisterStatus = fms.ContractRegisterStatusIng lastInvoices := make([]*fms.ContractInvoice, 0) // 过滤信息不完整的开票到款 for l := range rowInvoices { if rowInvoices[l].ContractCode != "" && rowInvoices[l].SellerId > 0 { lastInvoices = append(lastInvoices, rowInvoices[l]) } } for l := range rowPayments { if rowPayments[l].ContractCode != "" { lastInvoices = append(lastInvoices, rowPayments[l]) } } // 新增登记、套餐、开票到款信息 newId, e := fms.CreateImportContractRegister(rowRegister, rowServices, lastInvoices) if e != nil { resp.FailData(fmt.Sprintf("第%d行导入失败", i+1), "新增导入登记失败, Err: "+e.Error(), c) return } newIds = append(newIds, newId) } } } resp.Ok("操作成功", c) } // CurrencyList // @Title 货币单位列表 // @Description 货币单位列表 // @Success 200 {object} fms.CurrencyUnitItem // @router /contract/register/currency_list [get] func (rg *RegisterController) CurrencyList(c *gin.Context) { list, e := fmsService.GetTodayCurrencyRateList() if e != nil { resp.FailData("获取失败", "获取今日货币汇率列表失败, Err: "+e.Error(), c) return } resp.OkData("获取成功", list, c) }