hongze_yb/models/tables/company_report_permission/custom_query.go

package company_report_permission

import (
	"fmt"
	"hongze/hongze_yb/global"
	"strings"
	"time"
)

type ReportChapterTypeIdList struct {
	ReportChapterTypeId uint64
}

// GetReportVarietyList
func GetReportVarietyList(userId uint64, reportType string) (list []*ReportChapterTypeIdList, err error) {
	var condition string
	whereVals := make([]interface{}, 0)

	if reportType != "" {
		condition += ` and cpcm.research_type = ? `
		whereVals = append(whereVals, reportType)
	}

	sql := ` SELECT cpcm.report_chapter_type_id FROM company_report_permission crp
            INNER JOIN  chart_permission_chapter_mapping cpcm ON crp.chart_permission_id = cpcm.chart_permission_id
            INNER JOIN wx_user wu ON wu.company_id = crp.company_id
            WHERE wu.user_id = ? `

	sql += condition
	sql += ` GROUP BY cpcm.report_chapter_type_id `
	err = global.DEFAULT_MYSQL.Raw(sql, userId, whereVals).Scan(&list).Error
	return
}

type ResearchReportTypeList struct {
	ResearchReportTypeId    uint64
	ResearchReportId        uint64
	ResearchReportTypeTitle string
	TypeId                  int
	Edit                    int8
	Trend                   string
	ReportChapterTypeKey    string
	ReportChapterTypeThumb  string
	BannerUrl               string
	ReportChapterTypeName   string
	Sort                    int
	EditImgUrl              string
	PauseStartTime          time.Time
	PauseEndTime            time.Time
	LastUpdatedTime         time.Time
}

// GetResearchReportType 获取研究报告的章节详情
func GetResearchReportType(researchReportId, userId uint64, reportType string) (list []*ResearchReportTypeList, err error) {
	var condition string
	whereVals := make([]interface{}, 0)

	//如果是周报，并且是H5页面
	if "week" == reportType && userId > 0 {
		condition += ` and rrt.edit=1 `
		reportChapterTypeList, tmpErr := GetReportVarietyList(userId, reportType)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		if len(reportChapterTypeList) > 0 {
			reportChapterTypeIdList := make([]string, 0)
			for _, v := range reportChapterTypeList {
				reportChapterTypeIdList = append(reportChapterTypeIdList, fmt.Sprint(v.ReportChapterTypeId))
			}
			condition += ` and rct.report_chapter_type_id in (` + strings.Join(reportChapterTypeIdList, ",") + `) `
			//whereVals = append(whereVals, strings.Join(reportChapterTypeIdList, ","))

		}
	}

	if strings.Contains("day,week", reportType) {
		condition += ` and rct.is_show=1 `
	}

	sql := `select
	rrt.research_report_type_id,
	rrt.research_report_id,
	rrt.research_report_type_title,
	rrt.type_id,
	rrt.edit,
	rrt.trend,
	rct.report_chapter_type_key,
	rct.report_chapter_type_thumb,
	rct.banner_url,
	rct.report_chapter_type_name,
	rct.sort,
	rct.edit_img_url,
	rct.pause_start_time,
	rct.pause_end_time,
	rrt.last_updated_time 
	from research_report_type rrt
	left JOIN  report_chapter_type rct on rct.report_chapter_type_id  = rrt.type_id
	where rrt.research_report_id  = ? `

	sql += condition
	sql += ` order by rct.sort,rrt.research_report_type_id `

	err = global.DEFAULT_MYSQL.Raw(sql, researchReportId, whereVals).Scan(&list).Error
	return
}

type PermissionName struct {
	ChartPermissionName string
	ResearchType        string
}

// GetPermissionNameByReportId
func GetPermissionNameByReportId(reportChapterTypeId uint64, researchType string) (item PermissionName, err error) {
	sql := `SELECT b.chart_permission_name,a.research_type FROM chart_permission_chapter_mapping AS a
INNER JOIN chart_permission AS b ON a.chart_permission_id=b.chart_permission_id
WHERE a.report_chapter_type_id = ? AND a.research_type=?' LIMIT 1 `
	err = global.DEFAULT_MYSQL.Raw(sql, reportChapterTypeId, researchType).Scan(&item).Error
	return
}