package main import ( "encoding/json" "errors" "fmt" "go.uber.org/zap" "gorm.io/gorm" util "jygit.jydev.jianyu360.cn/data_processing/common_utils" "jygit.jydev.jianyu360.cn/data_processing/common_utils/log" "time" ) // dealAllDataAccountOrderChangeRecord 处理归集后-存量业绩表更表-dwd_f_account_order_change func dealAllDataAccountOrderChangeRecord() { // 数据分为二部分;1、业绩变更表-jianyu.order_sale_record //2、退款表 -data_analysis.refund_record log.Info("dealAllDataAccountOrderChangeRecord ", zap.String("处理归集后的业绩变更表", "处理开始")) allDataOrderSaleRecordForAccountOrderChange() //业绩变更表数据处理 allDataRefundRecordForAccountOrderChange() //退款表数据处理 log.Info("dealAllDataAccountOrderChangeRecord ", zap.String("处理归集后的业绩变更表", "处理完毕")) } // allDataOrderSaleRecordForAccountOrderChange 业绩变更表数据处理-order_sale_record func allDataOrderSaleRecordForAccountOrderChange() { defer func() { if r := recover(); r != nil { log.Info("Panic recovered", zap.Any("reason", r)) } log.Info("aaaaaaaaaaaa") }() var total int64 JianyuDB.Debug().Model(&OrderSaleRecord{}).Where("state IN ?", []string{"1", "-1"}).Where("autoUpdate < ?", "2024-10-16").Count(&total) log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Any("OrderSaleRecord 总数是", total)) rowsPerPage := 100 // 每页的数量 totalPages := (int(total) / rowsPerPage) + 1 //总页数 for page := 0; page < totalPages; page++ { offset := page * rowsPerPage rows, err := JianyuDB.Debug().Model(&OrderSaleRecord{}).Where("state IN ?", []string{"1", "-1"}).Where("autoUpdate < ?", "2024-10-16").Order("id desc").Offset(offset).Limit(rowsPerPage).Rows() defer func() { err = rows.Close() if err != nil { log.Info("Err rows.Close", zap.Error(err)) } }() log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Any("current page ", page)) for rows.Next() { var order_sale_record OrderSaleRecord // 原来业绩表更 var account_order_change DwdFAccountOrderChange // 归集后的业绩变更表 var data_exOrder DataexportOrder //原来的订单表 var account_order DwdFAccountOrder //归集后的订单表 var user1 DwdFUserbaseBaseinfo //个人用户表 //var returnRecord ReturnMoneyRecord //原来的回款表 var returnRecords []ReturnMoneyRecord //原来的回款表 var contract Contract //合同表 // ScanRows 方法用于将一行记录扫描至结构体 err = JianyuDB.ScanRows(rows, &order_sale_record) if err != nil { log.Info("allDataOrderSaleRecordForAccountOrderChange,ScanRows err ", zap.Error(err)) } //订单编号不为空 if order_sale_record.Ordercode != "" { // 业务逻辑... account_order_change.OrderCode = order_sale_record.Ordercode if order_sale_record.SalerName == "-" { account_order_change.SalerName = "运营" } else { account_order_change.SalerName = order_sale_record.SalerName } account_order_change.SalerDept = order_sale_record.SalerDept // 原来订单表 err = AnalysisDB.Model(DataexportOrder{}).Where("order_code =? ", order_sale_record.Ordercode).First(&data_exOrder).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Error(err)) } // 处理后的订单归集表 err = AnalysisDB.Model(DwdFAccountOrder{}).Where("order_code =? ", order_sale_record.Ordercode).First(&account_order).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Error(err)) } // 合同 err = AnalysisDB.Where("order_code = ? ", data_exOrder.OrderCode).First(&contract).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Error(err)) } account_order_change.CompanyName = data_exOrder.CompanyName if len(data_exOrder.UserID) > 20 { // 个人身份 err = JianyuSubjectDB.Where("userid = ? ", data_exOrder.UserID).First(&user1).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Error(err)) } account_order_change.UserRegtime = user1.LRegistedate } else { // 企业身份 //企业身份 rs := new(struct { PositionId int `gorm:"column:position_id"` LRegistedate *time.Time `gorm:"column:l_registedate"` }) if data_exOrder.UserID != "" { sql := fmt.Sprintf(`SELECT im.position_id,ub.l_registedate FROM (SELECT userid,position_id FROM Jianyu_subjectdb.dwd_f_userbase_id_mapping WHERE position_id = %s) im LEFT JOIN Jianyu_subjectdb.dwd_f_userbase_baseinfo ub ON im.userid=ub.userid`, data_exOrder.UserID) err = JianyuSubjectDB.Raw(sql).Scan(&rs).Error if err != nil { log.Info("处理用户订单表数据", zap.String("查询企业用户注册时间失败", data_exOrder.UserID)) } account_order_change.UserRegtime = rs.LRegistedate } } // account_order_change.CreateTime = data_exOrder.CreateTime account_order_change.SaleTime = order_sale_record.StatisticsTime account_order_change.ChangeReason = order_sale_record.ChangeReason account_order_change.OriginalPrice = data_exOrder.OriginalPrice filter := data_exOrder.Filter filterMap := make(map[string]interface{}) err := json.Unmarshal([]byte(filter), &filterMap) if err != nil { log.Info("allDataOrderSaleRecordForAccountOrderChange, filter.json.Unmarshal", zap.Error(err)) } account_order_change.ContractMoney = util.IntAll(filterMap["originalAmount"]) account_order_change.Commission = data_exOrder.Commission account_order_change.ProceduresMoney = data_exOrder.ProceduresMoney account_order_change.ReceivableAmount = account_order_change.ContractMoney - account_order_change.Commission - account_order_change.ProceduresMoney account_order_change.ChangeValue = order_sale_record.ChangeValue //累计已收 if data_exOrder.IsBackstageOrder == 0 { account_order_change.TotalReceived = util.IntAll(filterMap["originalAmount"]) } else if data_exOrder.IsBackstageOrder == 1 { err = AnalysisDB.Where("order_code = ? ", data_exOrder.OrderCode).Find(&returnRecords).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataOrderSaleRecordForAccountOrderChange", zap.Error(err)) } totalMoney := 0 if len(returnRecords) > 0 { for _, v := range returnRecords { totalMoney += v.ReturnMoney } } account_order_change.TotalReceived = totalMoney } // account_order_change.ProductType = data_exOrder.ProductType if data_exOrder.ProductType == "大会员" || data_exOrder.ProductType == "大会员-子账号" || data_exOrder.ProductType == "大会员-补充包" { switch util.IntAll(filterMap["level"]) { case 1: account_order_change.DataSpec = "专家版" case 2: account_order_change.DataSpec = "智慧版" case 3: account_order_change.DataSpec = "商机版" case 4: account_order_change.DataSpec = "试用版" case 5: account_order_change.DataSpec = "定制版" case 6: account_order_change.DataSpec = "商机版2.0" case 7: account_order_change.DataSpec = "专家版2.0" } } else { account_order_change.DataSpec = data_exOrder.DataSpec } //订单状态 if data_exOrder.RefundStatus == 1 || data_exOrder.RefundStatus == 2 { account_order_change.OrderStatus = "已完成" } else { switch data_exOrder.OrderStatus { case 1: account_order_change.OrderStatus = "已完成" case -1: account_order_change.OrderStatus = "逻辑删除" case -2: account_order_change.OrderStatus = "已取消" case -3: account_order_change.OrderStatus = "已取消" case 0: account_order_change.OrderStatus = "未完成" } } // refund_status 回款状态 if data_exOrder.IsBackstageOrder == 0 { if account_order.OrderStatus == "已支付" { account_order_change.RefundStatus = "全额回款" } else { account_order_change.RefundStatus = "未回款" } } else if data_exOrder.IsBackstageOrder == 1 { switch data_exOrder.ReturnStatus { case 1: account_order_change.ReturnStatus = "全额回款" case 2: account_order_change.ReturnStatus = "部分回款" case 0: account_order_change.ReturnStatus = "未回款" } } //退款状态 switch data_exOrder.RefundStatus { case 1: account_order_change.ReturnStatus = "全额回款" case 2: account_order_change.ReturnStatus = "部分回款" case 0: account_order_change.ReturnStatus = "未回款" } //付费类型 vip_type if data_exOrder.ProductType == "大会员" || data_exOrder.ProductType == "大会员-子账号" || data_exOrder.ProductType == "大会员-补充包" { //付费类型 VipType switch util.IntAll(filterMap["recordPayType"]) { case 1: account_order_change.VipType = "购买" case 2: account_order_change.VipType = "续费" case 3: account_order_change.VipType = "升级" case 4: account_order_change.VipType = "试用" } } else { switch data_exOrder.VipType { case 0: account_order_change.VipType = "购买" case 1: account_order_change.VipType = "续费" case 2: account_order_change.VipType = "升级" case 3: account_order_change.VipType = "试用" } } // account_order_change.UserPhone = data_exOrder.UserPhone account_order_change.UserID = data_exOrder.UserID account_order_change.VipStarttime = data_exOrder.VipStarttime account_order_change.VipEndtime = data_exOrder.VipEndtime //合同 if contract.ContractStatus == 1 { account_order_change.ContractStatus = "已签协议" } else if contract.ContractStatus == 0 { account_order_change.ContractStatus = "未签协议" } account_order_change.ContractCode = contract.ContractCode account_order_change.ContractTime = contract.ContractTime //签约主体 if data_exOrder.SigningSubject == "h01" { account_order_change.SigningSubject = "北京剑鱼信息技术有限公司" } else if data_exOrder.SigningSubject == "h02" { account_order_change.SigningSubject = "北京拓普丰联信息科技股份有限公司" } account_order_change.OrderChannel = data_exOrder.OrderChannel account_order_change.DistributionChannel = data_exOrder.DistributionChannel if data_exOrder.IsBackstageOrder == 0 { account_order_change.IsBackstageOrder = "否" } else if data_exOrder.IsBackstageOrder == 1 { account_order_change.IsBackstageOrder = "是" } //付款方式 switch data_exOrder.PayWay { case "ali", "ali_app", "ali_pc": //ali、ali_app、ali_pc 处理为“支付宝” account_order_change.PayWay = "支付宝" case "wx", "wx_app", "wx_js", "wx_pc": // wx、wx_app、wx_js、wx_pc 处理为“微信” account_order_change.PayWay = "微信" case "transferAccounts": account_order_change.PayWay = "对公转账" default: account_order_change.PayWay = data_exOrder.PayWay } // account_order_change.OrderSaleRecordID = order_sale_record.ID //生成订单数据 err = AnalysisDB.Create(&account_order_change).Error if err != nil { log.Info("allDataOrderSaleRecordForAccountOrderChange Create ", zap.Error(err)) } } } } log.Info("allDataOrderSaleRecordForAccountOrderChange 迭代结束", zap.Int64("数据总量", total)) } // allDataRefundRecordForAccountOrderChange 退款表数据处理 -refund_record func allDataRefundRecordForAccountOrderChange() { defer func() { if r := recover(); r != nil { log.Info("Panic recovered", zap.Any("reason", r)) } log.Info("aaaaaaaaaaaa") }() var total int64 AnalysisDB.Debug().Model(&RefundRecord{}).Where("refund_time < ?", "2024-10-16").Count(&total) log.Info("allDataRefundRecordForAccountOrderChange", zap.Any("RefundRecord 退款表 总数是", total)) rowsPerPage := 100 // 每页的数量 totalPages := (int(total) / rowsPerPage) + 1 //总页数 for page := 0; page < totalPages; page++ { offset := page * rowsPerPage rows, err := AnalysisDB.Debug().Model(&RefundRecord{}).Where("refund_time < ?", "2024-10-16").Order("id desc").Offset(offset).Limit(rowsPerPage).Rows() defer func() { err = rows.Close() if err != nil { log.Info("Err rows.Close", zap.Error(err)) } }() log.Info("allDataRefundRecordForAccountOrderChange", zap.Any("current page ", page)) for rows.Next() { var refund_record RefundRecord //原来退款表 var order_sale_record OrderSaleRecord // 原来业绩变更表 var account_order_change DwdFAccountOrderChange // 归集后的业绩变更表 var data_exOrder DataexportOrder //原来的订单表 var account_order DwdFAccountOrder //归集后的订单表 var user1 DwdFUserbaseBaseinfo //个人用户表 //var returnRecord ReturnMoneyRecord //原来的回款表 var returnRecords []ReturnMoneyRecord //原来的回款表 var contract Contract //合同表 // ScanRows 方法用于将一行记录扫描至结构体 err = AnalysisDB.ScanRows(rows, &refund_record) if err != nil { log.Info("allDataRefundRecordForAccountOrderChange,ScanRows err ", zap.Error(err)) } //退款表订单编号不为空 if refund_record.OrderCode != "" { // 业务逻辑... // 1.原来订单表 err = AnalysisDB.Model(DataexportOrder{}).Where("order_code =? ", refund_record.OrderCode).First(&data_exOrder).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataRefundRecordForAccountOrderChange", zap.Error(err)) } //2.业绩变更表 err = JianyuDB.Debug().Where("ordercode = ? ", refund_record.OrderCode).Order("id desc").First(&order_sale_record).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataRefundRecordForAccountOrderChange", zap.Error(err)) } //3. 处理后的订单归集表 err = AnalysisDB.Model(DwdFAccountOrder{}).Where("order_code =? ", refund_record.OrderCode).First(&account_order).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataRefundRecordForAccountOrderChange", zap.Error(err)) } //4. 合同 err = AnalysisDB.Where("order_code = ? ", refund_record.OrderCode).First(&contract).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("dealAllDataOrderSaleRecord", zap.Error(err)) } //订单编号 account_order_change.OrderCode = refund_record.OrderCode if order_sale_record.SalerName == "-" { account_order_change.SalerName = "运营" } else { account_order_change.SalerName = order_sale_record.SalerName } account_order_change.SalerDept = order_sale_record.SalerDept account_order_change.CompanyName = data_exOrder.CompanyName //公司名称 if len(data_exOrder.UserID) > 20 { // 个人身份 err = JianyuSubjectDB.Where("userid = ? ", data_exOrder.UserID).First(&user1).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataRefundRecordForAccountOrderChange", zap.Error(err)) } account_order_change.UserRegtime = user1.LRegistedate } else { // 企业身份 //企业身份 rs := new(struct { PositionId int `gorm:"column:position_id"` LRegistedate *time.Time `gorm:"column:l_registedate"` }) if data_exOrder.UserID != "" { sql := fmt.Sprintf(`SELECT im.position_id,ub.l_registedate FROM (SELECT userid,position_id FROM Jianyu_subjectdb.dwd_f_userbase_id_mapping WHERE position_id = %s) im LEFT JOIN Jianyu_subjectdb.dwd_f_userbase_baseinfo ub ON im.userid=ub.userid`, data_exOrder.UserID) err = JianyuSubjectDB.Raw(sql).Scan(&rs).Error if err != nil { log.Info("处理用户订单表数据", zap.String("查询企业用户注册时间失败", data_exOrder.UserID)) } account_order_change.UserRegtime = rs.LRegistedate } } // account_order_change.CreateTime = data_exOrder.CreateTime account_order_change.SaleTime = refund_record.RefundTime //业绩统计日期 account_order_change.ChangeReason = refund_record.RefundReason //变更类型 account_order_change.OriginalPrice = data_exOrder.OriginalPrice //标准售价 filter := data_exOrder.Filter filterMap := make(map[string]interface{}) err := json.Unmarshal([]byte(filter), &filterMap) if err != nil { log.Info("allDataRefundRecordForAccountOrderChange, filter.json.Unmarshal", zap.Error(err)) } account_order_change.ContractMoney = util.IntAll(filterMap["originalAmount"]) // 合同金额 account_order_change.Commission = data_exOrder.Commission //佣金 account_order_change.ProceduresMoney = data_exOrder.ProceduresMoney account_order_change.ReceivableAmount = account_order_change.ContractMoney - account_order_change.Commission - account_order_change.ProceduresMoney account_order_change.ChangeValue = 0 - refund_record.RefundMoney //业绩变动额 //累计已收 if data_exOrder.IsBackstageOrder == 0 { account_order_change.TotalReceived = util.IntAll(filterMap["originalAmount"]) } else if data_exOrder.IsBackstageOrder == 1 { err = AnalysisDB.Where("order_code = ? ", data_exOrder.OrderCode).Find(&returnRecords).Error if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) { log.Info("allDataRefundRecordForAccountOrderChange", zap.Error(err)) } totalMoney := 0 if len(returnRecords) > 0 { for _, v := range returnRecords { totalMoney += v.ReturnMoney } } account_order_change.TotalReceived = totalMoney } // account_order_change.ProductType = data_exOrder.ProductType if data_exOrder.ProductType == "大会员" || data_exOrder.ProductType == "大会员-子账号" || data_exOrder.ProductType == "大会员-补充包" { switch util.IntAll(filterMap["level"]) { case 1: account_order_change.DataSpec = "专家版" case 2: account_order_change.DataSpec = "智慧版" case 3: account_order_change.DataSpec = "商机版" case 4: account_order_change.DataSpec = "试用版" case 5: account_order_change.DataSpec = "定制版" case 6: account_order_change.DataSpec = "商机版2.0" case 7: account_order_change.DataSpec = "专家版2.0" } } else { account_order_change.DataSpec = data_exOrder.DataSpec } //订单状态 if data_exOrder.RefundStatus == 1 || data_exOrder.RefundStatus == 2 { account_order_change.OrderStatus = "已支付" } else { switch data_exOrder.OrderStatus { case 1: account_order_change.OrderStatus = "已支付" case -1: account_order_change.OrderStatus = "逻辑删除" case -2: account_order_change.OrderStatus = "已取消" case -3: account_order_change.OrderStatus = "已退款" case 0: account_order_change.OrderStatus = "未支付" } } // refund_status 回款状态 if data_exOrder.IsBackstageOrder == 0 { if account_order.OrderStatus == "已支付" { account_order_change.RefundStatus = "全额回款" } else { account_order_change.RefundStatus = "未回款" } } else if data_exOrder.IsBackstageOrder == 1 { switch data_exOrder.ReturnStatus { case 1: account_order_change.ReturnStatus = "全额回款" case 2: account_order_change.ReturnStatus = "部分回款" case 0: account_order_change.ReturnStatus = "未回款" } } //退款状态 switch data_exOrder.RefundStatus { case 1: account_order_change.ReturnStatus = "全额回款" case 2: account_order_change.ReturnStatus = "部分回款" case 0: account_order_change.ReturnStatus = "未回款" } //付费类型 vip_type if data_exOrder.ProductType == "大会员" || data_exOrder.ProductType == "大会员-子账号" || data_exOrder.ProductType == "大会员-补充包" { //付费类型 VipType switch util.IntAll(filterMap["recordPayType"]) { case 1: account_order_change.VipType = "购买" case 2: account_order_change.VipType = "续费" case 3: account_order_change.VipType = "升级" case 4: account_order_change.VipType = "试用" } } else { switch data_exOrder.VipType { case 0: account_order_change.VipType = "购买" case 1: account_order_change.VipType = "续费" case 2: account_order_change.VipType = "升级" case 3: account_order_change.VipType = "试用" } } // account_order_change.UserPhone = data_exOrder.UserPhone account_order_change.UserID = data_exOrder.UserID account_order_change.VipStarttime = data_exOrder.VipStarttime account_order_change.VipEndtime = data_exOrder.VipEndtime //合同 if contract.ContractStatus == 1 { account_order_change.ContractStatus = "已签协议" } else if contract.ContractStatus == 0 { account_order_change.ContractStatus = "未签协议" } account_order_change.ContractCode = contract.ContractCode account_order_change.ContractTime = contract.ContractTime //签约主体 if data_exOrder.SigningSubject == "h01" { account_order_change.SigningSubject = "北京剑鱼信息技术有限公司" } else if data_exOrder.SigningSubject == "h02" { account_order_change.SigningSubject = "北京拓普丰联信息科技股份有限公司" } account_order_change.OrderChannel = data_exOrder.OrderChannel account_order_change.DistributionChannel = data_exOrder.DistributionChannel if data_exOrder.IsBackstageOrder == 0 { account_order_change.IsBackstageOrder = "否" } else if data_exOrder.IsBackstageOrder == 1 { account_order_change.IsBackstageOrder = "是" } //付款方式 switch data_exOrder.PayWay { case "ali", "ali_app", "ali_pc": //ali、ali_app、ali_pc 处理为“支付宝” account_order_change.PayWay = "支付宝" case "wx", "wx_app", "wx_js", "wx_pc": // wx、wx_app、wx_js、wx_pc 处理为“微信” account_order_change.PayWay = "微信" case "transferAccounts": account_order_change.PayWay = "对公转账" default: account_order_change.PayWay = data_exOrder.PayWay } // account_order_change.RefundRecordID = refund_record.ID //退款表ID //生成订单数据 err = AnalysisDB.Create(&account_order_change).Error if err != nil { log.Info("allDataRefundRecordForAccountOrderChange Create ", zap.Error(err)) } } } } log.Info("allDataRefundRecordForAccountOrderChange 迭代结束", zap.Int64("数据总量", total)) }