package customerService import ( "app.yhyue.com/moapp/jybase/common" "app.yhyue.com/moapp/jybase/mongodb" "app.yhyue.com/moapp/jybase/redis" "encoding/json" "errors" "fmt" "io/ioutil" "log" "net/http" "net/url" "qmx_admin/src/config" "strings" "time" ) const ( BigmemberUserPowerTable = "bigmember_service_user" PACKNAME = "数据流包" PowerCacheDb = "other" DataPacketUsedNum = "dataPacketUsed_%s_%d" RedisCacheDb = "other" ) func FindOnePartner(uid string, id int) (res map[string]interface{}) { log.Println("service加载中。。。。。。。") return nil } // 查询商机管理企业账户余额 func getCurrEntCount(entName, entPhone string) int { current, ok := config.JyqyfwMgo.FindOne("user", map[string]interface{}{"phone": entPhone, "username": entName}) if current == nil || !ok { return 0 } plan, planOk := (*current)["plan"].(map[string]interface{}) if !planOk { return 0 } return common.IntAll(plan["current"]) } func FindExportLogList(phoneIn, userNameIn, entNameIn string, startTimeIn, endTimeIn, pageNum, pageSize int) (returnList []map[string]interface{}, totalCount int) { // 根据用户昵称模糊查询出用户mongoid 及手机号 // 根据用户id查询 datapack_record nick1List := []string{} nick2List := []string{} nickPhoneList := []interface{}{} nickIdList := []interface{}{} masterIdList := []interface{}{} masterPreList := []string{} //entNameIdList := []interface{}{} if userNameIn != "" { // 模糊查询出用户列表 userTmpRs, ok := config.MQFW.Find("user", map[string]interface{}{"s_nickname": map[string]interface{}{ "$regex": userNameIn, }}, `{"createtime":-1}`, nil, false, -1, -1) if ok && userTmpRs != nil && len(*userTmpRs) > 0 { for _, v := range *userTmpRs { if common.ObjToString(v["s_phone"]) != "" { nick1List = append(nick1List, "?") nickPhoneList = append(nickPhoneList, common.ObjToString(v["s_phone"])) } else if common.ObjToString(v["s_m_phone"]) != "" { nick1List = append(nick1List, "?") nickPhoneList = append(nickPhoneList, common.ObjToString(v["s_m_phone"])) } nick2List = append(nick2List, "?") nickIdList = append(nickIdList, mongodb.BsonIdToSId(v["_id"])) } } else { log.Println("根据昵称没有查询到用户信息") return } } if entNameIn != "" { // 根据企业名称模糊查询出主账户id 列表 // 处理查询条件 masterTmpRs, ok := config.MQFW.Find("member", map[string]interface{}{"entname": map[string]interface{}{ "$regex": entNameIn, }}, `{"createtime":-1}`, nil, false, -1, -1) if ok && masterTmpRs != nil && len(*masterTmpRs) > 0 { for _, tmp := range *masterTmpRs { masterIdList = append(masterIdList, tmp["userid"]) masterPreList = append(masterPreList, "?") } } } searchStrList := []string{} searchValueList := []interface{}{} datapackSql := "" entnicheSql := "" // 查询数据 处理sql // 由于单日限量包和数据流量包线上没有企业 所以查询条件有企业时不用再查datapacket_record了 // 单日限量包和数据流量包线上 // 企业名称为空时不用走这段,企业名称不为空时查询到了master_id 才走这段 if entNameIn == "" || (entNameIn != "" && len(masterIdList) > 0) { // 处理手机号或者昵称条件匹配到的用户id // 查询参数包含手机号时 // 根据手机号查询出mongo库用户id 用于查询 datapacket_record tmpFilterList := []string{} if len(masterIdList) > 0 { // 拼接master_id 占位符 tmpFilterList = append(tmpFilterList, "type = 1") tmpFilterList = append(tmpFilterList, fmt.Sprintf("master_id in (%s)", strings.Join(masterPreList, ","))) // 拼接 master_id 值 searchValueList = append(searchValueList, masterIdList...) } if phoneIn != "" || userNameIn != "" { searchMgId := "" if phoneIn != "" { userRs, ok := config.MQFW.FindOne("user", map[string]interface{}{"s_phone": phoneIn}) if ok && userRs != nil && len(*userRs) > 0 { searchMgId = mongodb.BsonIdToSId((*userRs)["_id"]) nick2List = append(nick2List, "?") nickIdList = append(nickIdList, searchMgId) } else { userRs, oks := config.MQFW.FindOne("user", map[string]interface{}{"s_m_phone": phoneIn}) if oks && userRs != nil && len(*userRs) > 0 { searchMgId = mongodb.BsonIdToSId((*userRs)["_id"]) nick2List = append(nick2List, "?") nickIdList = append(nickIdList, searchMgId) } else { log.Println("未找到对应信息1") } } } preStr := strings.Join(nick2List, ",") // 根据手机号或者用户昵称查询到了用户信息 才会拼接其他条件 if len(nickIdList) >= 0 { // 处理时间条件存在 if startTimeIn != 0 { tmpFilterList = append(tmpFilterList, "date>?") searchValueList = append(searchValueList, startTimeIn) } if endTimeIn != 0 { tmpFilterList = append(tmpFilterList, "date 0 { tmpFilterSql = fmt.Sprintf(" where %s ", strings.Join(tmpFilterList, " and ")) } datapackSql = fmt.Sprintf("(SELECT id AS log_id,'' AS ent_id,useid AS user_id,master_id AS master_id,date AS exportDate,type AS data_from,export_num AS export_num,'2' AS source,'' AS phone,'' AS ent_name,'' AS admin_phone,isSenior as isSenior FROM datapacket_record %s) ", tmpFilterSql) searchStrList = append(searchStrList, datapackSql) } } else { // 处理时间条件存在 if startTimeIn != 0 { tmpFilterList = append(tmpFilterList, "date>?") searchValueList = append(searchValueList, startTimeIn) } if endTimeIn != 0 { tmpFilterList = append(tmpFilterList, "date 0 { tmpFilterSql = fmt.Sprintf(" where %s ", strings.Join(tmpFilterList, " and ")) } datapackSql = fmt.Sprintf("(SELECT id AS log_id,'' AS ent_id,useid AS user_id,master_id AS master_id,date AS exportDate,type AS data_from,export_num AS export_num,'2' AS source,'' AS phone ,'' AS ent_name,'' as admin_phone,isSenior as isSenior FROM datapacket_record %s) ", tmpFilterSql) searchStrList = append(searchStrList, datapackSql) } } // 数据流量包线下的条件参数处理 tmpFilterList2 := []string{} // 处理手机号或者昵称条件匹配到的用户id // 根据昵称匹配到了用户 拿到了用户手机号才能去查商机管理表 if userNameIn != "" { if len(nickPhoneList) > 0 { if startTimeIn != 0 { tmpFilterList2 = append(tmpFilterList2, "UNIX_TIMESTAMP( a.export_time )>?") searchValueList = append(searchValueList, startTimeIn) } if endTimeIn != 0 { tmpFilterList2 = append(tmpFilterList2, "UNIX_TIMESTAMP( a.export_time ) 0 { tmpFilterSql2 = fmt.Sprintf(" and %s ", strings.Join(tmpFilterList2, " and ")) } // 数据流量包(线下) entnicheSql = fmt.Sprintf("(SELECT a.id AS log_id, a.ent_id AS ent_id,a.user_id AS user_id,'' AS master_id,UNIX_TIMESTAMP( a.export_time ) AS exportDate,0 AS data_from,a.export_num AS export_num,'1' AS source,a.phone as phone ,b.NAME AS ent_name,b.phone as admin_phone ,'' as isSenior FROM entniche_export_log AS a,entniche_info AS b WHERE a.ent_id = b.id %s)", tmpFilterSql2) searchStrList = append(searchStrList, entnicheSql) } } else { if startTimeIn != 0 { tmpFilterList2 = append(tmpFilterList2, "UNIX_TIMESTAMP( a.export_time )>?") searchValueList = append(searchValueList, startTimeIn) } if endTimeIn != 0 { tmpFilterList2 = append(tmpFilterList2, "UNIX_TIMESTAMP( a.export_time ) 0 { tmpFilterSql2 = fmt.Sprintf(" and %s ", strings.Join(tmpFilterList2, " and ")) } // 数据流量包(线下) entnicheSql = fmt.Sprintf("(SELECT a.id AS log_id, a.ent_id AS ent_id,a.user_id AS user_id,'' AS master_id,UNIX_TIMESTAMP( a.export_time ) AS exportDate,0 AS data_from,a.export_num AS export_num,'1' AS source,a.phone as phone,b.NAME AS ent_name,b.phone as admin_phone,'' as isSenior FROM entniche_export_log AS a,entniche_info AS b WHERE a.ent_id = b.id %s)", tmpFilterSql2) searchStrList = append(searchStrList, entnicheSql) } // 拼接sql集合 searchSql := strings.Join(searchStrList, ` UNION ALL`) b := fmt.Sprintf("SELECT * FROM (%s) AS alldata ORDER BY exportDate DESC LIMIT ?,? ", searchSql) // 返回totalCount totalCount = config.JysqlDB.QueryCount(fmt.Sprintf("SELECT count(*) FROM (%s) AS alldata ", searchSql), searchValueList...) // 追加翻页参数 searchValueList = append(searchValueList, pageNum, pageSize) // 替换单日限量包id c := fmt.Sprintf("SELECT log_id,user_id,exportDate,export_num,source,ent_id,ent_name,phone,data_from,admin_phone,master_id,isSenior,"+ "CASE data_from "+ "WHEN 0 THEN '数据流量包(线下充值)'"+ "WHEN 2 THEN '数据流量包(线上充值)'"+ "WHEN 1 THEN (SELECT CASE s_serviceid "+ "WHEN 18 THEN '单日限量包(200条)'"+ "WHEN 17 THEN '单日限量包(100条)'"+ "END "+ "FROM bigmember_service_user "+ "WHERE s_userid = master_id AND ( s_serviceid = 17 OR s_serviceid = 18 ) "+ "AND exportDate <= UNIX_TIMESTAMP( l_endtime ) AND exportDate > UNIX_TIMESTAMP( l_starttime ) LIMIT 0,1 ) "+ "END AS pay_way "+ "FROM (%s) AS finalData", b) //查询 // 补充字段 支付方式 处理单日限量包 用户微信昵称 单日限量包和数据流量包线上需要补充 用户手机号 resultList := config.JysqlDB.Query(c, searchValueList...) if resultList == nil || len(*resultList) == 0 { log.Println("未查询到结果") return } //格式化返回内容 returnList = make([]map[string]interface{}, 0, pageSize) for _, row := range *resultList { // 根据类型查询 mongo 库 // 商机管理的根据手机号查询mongo库 获取用户微信昵称 source 0商机管理的数据 1 datapack的数据 if row["source"] == "1" { userData, ok := config.MQFW.FindOne("user", map[string]interface{}{"s_phone": row["phone"]}) if ok && userData != nil && len(*userData) > 0 { row["nickname"] = (*userData)["s_nickname"] } else { userDatas, oks := config.MQFW.FindOne("user", map[string]interface{}{"s_m_phone": row["phone"]}) if oks && userDatas != nil && len(*userDatas) > 0 { row["nickname"] = (*userDatas)["s_nickname"] } else { log.Println("未找到对应信息2") } } // 商机管理查询用户余额 leftCount := getCurrEntCount(common.ObjToString(row["ent_name"]), common.ObjToString(row["admin_phone"])) row["leftCount"] = leftCount } else { // 单日限量包和数据流量包线上的根据用户id查询mongo库 获取用户微信昵称和手机号 data, ok := config.MQFW.FindOne("user", map[string]interface{}{"_id": mongodb.StringTOBsonId(common.ObjToString(row["user_id"]))}) if ok && data != nil && len(*data) > 0 { row["nickname"] = (*data)["s_nickname"] if common.ObjToString((*data)["s_phone"]) == "" { row["phone"] = (*data)["s_m_phone"] } else { row["phone"] = (*data)["s_phone"] } row["nickname"] = (*data)["s_nickname"] } if row["pay_way"] == nil { row["pay_way"] = "每日限量包" } // 单日限量包 当日限额 if common.IntAll(row["data_from"]) == 1 { // 查主账号余额 dayUse := redis.GetInt(RedisCacheDb, fmt.Sprintf(DataPacketUsedNum, common.ObjToString(row["master_id"]), time.Now().Day())) todayLimit := GetDailyNum(common.ObjToString(row["master_id"])) row["leftCount"] = todayLimit - dayUse // 补充公司名称 根据master_id 去member表里 mongo 查数据 companyRs, ok := config.MQFW.FindOne("member", map[string]interface{}{ "userid": row["master_id"], }) if ok && companyRs != nil && len(*companyRs) > 0 { row["ent_name"] = (*companyRs)["entname"] } else { log.Println("未在member 表里查询到数据") } } else { // 数据流量包(线上) 调用中台接口 //balance := 0 //剩余量 resourceType := "" if common.IntAll(row["isSenior"]) == 1 { resourceType = "标准字段包" } else { resourceType = "高级字段包" } //调用资源中台服务获取剩余量 resMap, err := CommonPost(config.SysConfigs.FindBalance, url.Values{ "accountId": []string{common.ObjToString(row["user_id"])}, //账户标识* }) if err != nil { return } listObj, ok := resMap["data"].([]interface{}) if !ok { log.Println("packAccountQuery 请求中台返回数据结构异常") return } for _, v := range listObj { obj, ok := v.(map[string]interface{}) if !ok { log.Println("packAccountQuery 请求中台返回数据结构异常") return } if common.ObjToString(obj["resourceType"]) == resourceType { row["leftCount"] = common.IntAll(obj["number"]) break } } } } returnList = append(returnList, row) } return } func GetDailyNum(masterId string) (dailyNum int) { serviceList := config.JysqlDB.Find(BigmemberUserPowerTable, map[string]interface{}{"s_userid": masterId, "i_status": 0}, "DISTINCT(s_serviceid),i_frequency", "", -1, -1) if serviceList != nil && len(*serviceList) != 0 { for _, item := range *serviceList { serviceid := common.IntAll(item["s_serviceid"]) if serviceid == 17 || serviceid == 18 { //每日数据包 dailyNum = common.IntAll(item["i_frequency"]) } } } return } func packAccountQuery(accountId string) (total int, err error) { resMap, err := CommonPost(config.SysConfigs.FindBalance, url.Values{ "accountId": []string{accountId}, //账户标识* }) if err != nil { return } listObj, ok := resMap["data"].([]interface{}) if !ok { return total, fmt.Errorf("packAccountQuery 请求中台返回数据结构异常") } for _, obj := range listObj { packMap := common.ObjToMap(obj) if packMap == nil || len(*packMap) == 0 { continue } if packType := common.ObjToString((*packMap)["name"]); packType != PACKNAME { continue } total = common.IntAll((*packMap)["number"]) //总量 } return } func CommonPost(path string, param url.Values) (map[string]interface{}, error) { //param["appId"] = []string{config.JyApiConfig.AppId} //公共appid res, err := http.PostForm(path, param) if err != nil { return nil, err } defer res.Body.Close() bs, _ := ioutil.ReadAll(res.Body) resMap := map[string]interface{}{} err = json.Unmarshal(bs, &resMap) if err != nil { return nil, fmt.Errorf("%s 请求中台返回内容异常 %s", path, string(bs)) } if common.IntAll(resMap["code"]) != 1 { return nil, fmt.Errorf("%s 请求中台请求出错 %v", path, resMap["message"]) } return resMap, nil } func FindExportLog(id, source int) (result ExportLog, err error) { //result := ExportLog{} if source == 1 { //查询商机管理数据导出记录表 exportLog := config.JysqlDB.FindOne("entniche_export_log", map[string]interface{}{"id": id}, ``, "") if exportLog != nil && len(*exportLog) > 0 { //查询条件处理 str := common.ObjToString((*exportLog)["filter"]) filterMap := map[string]interface{}{} err := json.Unmarshal([]byte(str), &filterMap) if err != nil { return result, err } filter := "" if common.ObjToString(filterMap["FilterId"]) != "" { //查询搜索条件 queryMap, ok := config.MQFW.FindById("export_search", common.ObjToString(filterMap["FilterId"]), nil) if queryMap == nil && !ok { return result, errors.New("查询export_search出错") } _filter, _ := json.Marshal(queryMap) filter = string(_filter) } else { queryMap := map[string]interface{}{} for k, v := range filterMap { if k == "Area" { queryMap["area"] = v } if k == "Buyerclass" { queryMap["buyerclass"] = v } if k == "Time" { queryMap["publishtime"] = v } } _filter, _ := json.Marshal(queryMap) filter = string(_filter) } userId := common.Int64All((*exportLog)["user_id"]) //exportTime := common.ObjToString((*exportLog)["export_time"]) phone := common.ObjToString((*exportLog)["phone"]) //查询用户信息 userData, ok := config.MQFW.FindOne("user", map[string]interface{}{"s_phone": phone}) if ok && userData != nil && len(*userData) > 0 { (*exportLog)["userName"] = (*userData)["s_nickname"] //(*exportLog)["email"] = (*userData)["s_email"] } else { userDatas, oks := config.MQFW.FindOne("user", map[string]interface{}{"s_m_phone": phone}) if oks && userDatas != nil && len(*userDatas) > 0 { (*exportLog)["userName"] = (*userDatas)["s_nickname"] //(*exportLog)["email"] = (*userDatas)["s_email"] } } //查询用户每日导出限额 todayLimit, exportNums := 0, 0 limitData := config.JysqlDB.FindOne("entniche_export_limit", map[string]interface{}{"user_id": userId}, ``, "") if limitData != nil && len(*limitData) > 0 { //查询企业信息 entData := config.JysqlDB.FindOne("entniche_info", map[string]interface{}{"id": common.IntAll((*exportLog)["ent_id"])}, `name,phone`, "") if entData != nil && len(*entData) > 0 { (*exportLog)["entName"] = (*entData)["name"] entPhone := common.ObjToString((*entData)["phone"]) //根据企业名称和企业手机号查询商机管理企业剩余条数 (*exportLog)["balance"] = getCurrEntCount(common.ObjToString((*entData)["name"]), entPhone) } todayLimit = common.IntAll((*limitData)["data_limit"]) //每日限额 exportNums = common.IntAll((*limitData)["export_nums"]) //今日已导出 } result = ExportLog{ UserName: common.ObjToString((*exportLog)["userName"]), Email: common.ObjToString((*exportLog)["export_mail"]), Phone: common.ObjToString((*exportLog)["phone"]), CompanyName: common.ObjToString((*exportLog)["entName"]), PayWay: 1, DataType: "高级字段包", Balance: common.IntAll((*exportLog)["balance"]), ExportNum: common.IntAll((*exportLog)["export_num"]), RepetitionNum: common.IntAll((*exportLog)["export_num"]) - common.IntAll((*exportLog)["deduct_num"]), DeductNum: common.IntAll((*exportLog)["deduct_num"]), DayLimit: todayLimit, DayExportNum: exportNums, Filter: filter, DownloadUrl: config.SysConfigs.JyWebDomain + common.ObjToString((*exportLog)["download_url"]), } return result, nil } } else { //查询流量包导出记录 packExportLog := config.JysqlDB.FindOne("datapacket_record", map[string]interface{}{"id": id}, ``, "") if packExportLog != nil && len(*packExportLog) > 0 { userId := common.ObjToString((*packExportLog)["useid"]) //user表数据id queryId := common.ObjToString((*packExportLog)["query_id"]) //查询用户信息 userData, oks := config.MQFW.FindById("user", userId, nil) if userData != nil && len(*userData) > 0 && oks { (*packExportLog)["userName"] = (*userData)["s_nickname"] if common.ObjToString((*userData)["s_phone"]) == "" { (*packExportLog)["phone"] = (*userData)["s_m_phone"] } else { (*packExportLog)["phone"] = (*userData)["s_phone"] } } //查询搜索条件 queryData, ok := config.MQFW.FindById("export_search", queryId, nil) if queryData == nil && !ok { return result, errors.New("查询export_search出错") } _filter, _ := json.Marshal(queryData) filter := string(_filter) if common.IntAll((*packExportLog)["type"]) == 1 { //单日限量包 todayLimit := 0 balance := 0 //todayExport := 0 //查询用户单日限量额度 data := config.JysqlDB.SelectBySql("SELECT s.s_count_month,u.i_frequency FROM bigmember_service_user u LEFT JOIN bigmember_service s ON u.s_serviceid = s.id WHERE u.s_userid = ? and (s.id = 17 or s.id = 18)", userId) if data != nil && len(*data) > 0 { todayLimit = common.IntAll((*data)[0]["i_frequency"]) //计算今日已导出 //frequency = common.IntAll((*data)[0]["i_frequency"]) //今日剩余数量 //todayExport = todayLimit - frequency //今日已导出数量 } //今日已导出数量 dayUse := redis.GetInt(RedisCacheDb, fmt.Sprintf(DataPacketUsedNum, common.ObjToString((*packExportLog)["master_id"]), time.Now().Day())) balance = todayLimit - dayUse //查询企业名称 companyName := "" entInfo, ok := config.MQFW.FindOne("member", map[string]interface{}{"userid": userId}) if entInfo != nil && len(*entInfo) > 0 && ok { companyName = common.ObjToString((*entInfo)["entname"]) } result = ExportLog{ UserName: common.ObjToString((*packExportLog)["userName"]), Email: common.ObjToString((*packExportLog)["mail"]), Phone: common.ObjToString((*packExportLog)["phone"]), CompanyName: companyName, PayWay: 2, //DataType: "高级字段包", Balance: balance, ExportNum: common.IntAll((*packExportLog)["export_num"]), RepetitionNum: common.IntAll((*packExportLog)["export_num"]) - common.IntAll((*packExportLog)["deduct_num"]), DeductNum: common.IntAll((*packExportLog)["deduct_num"]), DayLimit: todayLimit, DayExportNum: dayUse, DownloadUrl: config.SysConfigs.JyWebDomain + common.ObjToString((*packExportLog)["path"]), Filter: filter, } if common.IntAll((*packExportLog)["isSenior"]) == 1 { result.DataType = "标准字段包" } else { result.DataType = "高级字段包" } return result, nil } else { //数据流量包 balance := 0 //剩余量 resourceType := "" if common.IntAll((*packExportLog)["isSenior"]) == 1 { resourceType = "标准字段包" } else { resourceType = "高级字段包" } //调用资源中台服务获取剩余量 resMap, err := CommonPost(config.SysConfigs.FindBalance, url.Values{ "accountId": []string{userId}, //账户标识* }) if err != nil { return result, err } listObj, ok := resMap["data"].([]interface{}) if !ok { return result, fmt.Errorf("packAccountQuery 请求中台返回数据结构异常") } for _, v := range listObj { obj, ok := v.(map[string]interface{}) if !ok { return result, fmt.Errorf("packAccountQuery 请求中台返回数据结构异常") } if common.ObjToString(obj["resourceType"]) == resourceType { balance = common.IntAll(obj["number"]) break } } result = ExportLog{ UserName: common.ObjToString((*packExportLog)["userName"]), Email: common.ObjToString((*packExportLog)["mail"]), Phone: common.ObjToString((*packExportLog)["phone"]), CompanyName: "", PayWay: 3, DataType: resourceType, Balance: balance, ExportNum: common.IntAll((*packExportLog)["export_num"]), RepetitionNum: common.IntAll((*packExportLog)["export_num"]) - common.IntAll((*packExportLog)["deduct_num"]), DeductNum: common.IntAll((*packExportLog)["deduct_num"]), DayLimit: 0, DayExportNum: 0, DownloadUrl: config.SysConfigs.JyWebDomain + common.ObjToString((*packExportLog)["path"]), Filter: filter, } return result, nil } } } return result, errors.New("查询数据出错") }