xps-server-g5/pkg/system/repository/user_repo.go

package repository

import "C"
import (
    "fmt"
    "xorm.io/xorm"
    "xps/pkg/common"
    "xps/pkg/system/datamodel"
    "xps/pkg/system/viewmodel"
)

type UserRepo struct {
    engine *xorm.Engine
}

func NewUserRepo(engine *xorm.Engine) *UserRepo {
    return &UserRepo{
        engine: engine,
    }
}

func (d *UserRepo) GetById(ocId, groupId, userId int64) (*viewmodel.UserInfo, error) {
    sqlSelect := ` SELECT   A.gm_id,
                            A.oc_id,
                            A.group_id,
                            A.account_id,
                            B.account_name,
                            B.account_real_name,
                            B.account_staff_no,
                            B.account_phone,
                            B.account_type,
                            B.account_avatar,
                            B.account_photo,
                            B.account_loc,
                            B.status,
                            B.is_fixed,
                            C.group_name,
                            C.node_left,
                            C.node_right,
                            E.gmp_id,
                            E.position_id,
                            H.position_name,
                            F.gmpg_id,
                            F.grant_group_id,
                            G.group_name  AS grant_group_name  `
    sqlFrom := `   FROM  s_group_member                        AS  A `
    sqlFrom += `   LEFT  JOIN account                          AS  B  ON ( A.oc_id = B.oc_id AND A.account_id  = B.account_id  ) `
    sqlFrom += `   LEFT  JOIN s_group                          AS  C  ON ( A.oc_id = C.oc_id AND A.group_id    = C.group_id )    `
    sqlFrom += fmt.Sprintf(`   LEFT  JOIN s_group_member_position          AS  E  ON ( A.oc_id = E.oc_id AND A.account_id  = E.account_id AND E.group_id= %d )`, groupId)
    sqlFrom += fmt.Sprintf(`   LEFT  JOIN s_group_member_position_grant    AS  F  ON ( E.oc_id = F.oc_id AND E.account_id  = F.account_id AND E.position_id = F.position_id  AND F.group_id = %d )`, groupId)
    sqlFrom += `   LEFT  JOIN s_group                          AS  G  ON ( G.oc_id = F.oc_id AND G.group_id    = F.grant_group_id ) `
    sqlFrom += `   LEFT  JOIN s_position                       AS  H  ON ( A.oc_id = E.oc_id AND H.position_id = E.position_id    ) `

    sqlWhere := ` WHERE B.deleted_flag = 0 `
    sqlWhere += ` AND   A.oc_id      = ` + fmt.Sprintf("%d", ocId)
    sqlWhere += ` AND   A.account_id = ` + fmt.Sprintf("%d", userId)

    data := &viewmodel.UserInfo{}
    has, err := d.engine.SQL(sqlSelect + sqlFrom + sqlWhere).Limit(1).Get(data)
    if !has || err != nil {
        return nil, err
    } else {
        return data, nil
    }
}

func (d *UserRepo) GetPage(m map[string]interface{}) (*common.PageResult, error) {
    ocId := m["ocId"].(int64)
    limit := m["limit"].(int)
    page := m["page"].(int)

    sqlSelect := ` SELECT
                            DISTINCT(A.account_id),
                            A.gm_id,
                            A.oc_id,
                            A.group_id,
                            B.account_name,
                            B.account_real_name,
                            B.account_phone,
                            B.account_staff_no,
                            B.account_avatar,
                            B.status,
                            B.is_fixed,
                            C.group_name,
                            C.node_left,
                            C.node_right,
                            D.gmp_id,
                            D.position_id,
                            D.position_name,
                            E.gmpg_id,
                            E.grant_group_id,
                            F.group_name as grant_group_name  `
    sqlFrom := ` FROM       s_group_member                AS A
                 LEFT JOIN  account                       AS B ON (A.oc_id = B.oc_id AND A.account_id     = B.account_id )
                 LEFT JOIN  s_group                       AS C ON (A.oc_id = C.oc_id AND A.group_id       = C.group_id   )
                 LEFT JOIN  s_group_member_position_grant AS E ON (B.oc_id = E.oc_id AND B.account_id     = E.account_id )
                 LEFT JOIN  s_group                       AS F ON (E.oc_id = F.oc_id AND E.grant_group_id = F.group_id   )
                 LEFT JOIN (
                    SELECT
                        M.oc_id,
                        M.account_id,
                        M.gmp_id,
                        M.group_id,
                        M.position_id,
                        P.position_name,
                        N.gmpg_id,
                        N.grant_group_id,
                        GP.group_name as grant_group_name
                    FROM      s_group_member_position       AS M
                    LEFT JOIN s_group_member_position_grant AS N ON (
                            M.oc_id       = N.oc_id
                        AND M.account_id  = N.account_id
                        AND M.group_id    = N.group_id
                        AND M.position_id = N.position_id )
                    LEFT JOIN s_position AS P  ON(M.oc_id = P.oc_id  AND M.position_id    = P.position_id)
                    LEFT JOIN s_group    AS GP ON(M.oc_id = GP.oc_id AND N.grant_group_id = GP.group_id)
                )      AS  D ON (A.oc_id = D.oc_id AND A.account_id = D.account_id AND A.group_id = D.group_id) `
    sqlWhere := ` WHERE B.deleted_flag = 0 AND A.oc_id = ` + fmt.Sprintf("%d", ocId)

    if keywords, ok := m["keywords"]; ok {
        sqlWhere += " AND ( B.account_real_name LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    B.account_name      LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    B.account_phone     LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    D.position_name     LIKE '%" + fmt.Sprintf("%s", keywords) + "%' )"
    }

    nodeLeft, ok1 := m["nodeLeft"]
    nodeRight, ok2 := m["nodeRight"]
    if ok1 && ok2 {
        sqlWhere += ` AND C.node_left  >= ` + fmt.Sprintf("%d", nodeLeft)
        sqlWhere += ` AND C.node_right <= ` + fmt.Sprintf("%d", nodeRight)
    }

    sqlCount := `SELECT COUNT(*) `
    total, err := d.engine.SQL(sqlCount + sqlFrom + sqlWhere).Count(new(datamodel.GroupMember))
    if err != nil {
        return nil, err
    }

    sqlOrderBy := " ORDER BY A.account_id, A.group_id "
    pageStart := (page - 1) * limit
    sqlLimit := fmt.Sprintf(" LIMIT %d OFFSET %d ", limit, pageStart)

    datalist := make([]viewmodel.UserInfo, 0)
    err = d.engine.SQL(sqlSelect + sqlFrom + sqlWhere + sqlOrderBy + sqlLimit).Find(&datalist)
    if err != nil {
        return nil, err
    }

    pageResult := &common.PageResult{}
    pageResult.Data = datalist
    pageResult.Total = total
    pageResult.PageSize = limit
    pageResult.Page = page
    return pageResult, nil
}

func (d *UserRepo) GetList(m map[string]interface{}) ([]viewmodel.UserInfo, error) {
    ocId := m["ocId"].(int64)
    sqlSelect := ` SELECT   DISTINCT(A.account_id),
                            A.gm_id,
                            A.oc_id,
                            A.group_id,
                            B.account_name,
                            B.account_real_name,
                            B.account_phone,
                            B.account_staff_no,
                            B.account_avatar,
                            B.status,
                            B.is_fixed,
                            C.group_name,
                            C.node_left,
                            C.node_right,
                            D.gmp_id,
                            D.position_id,
                            D.position_name,
                            E.gmpg_id,
                            E.grant_group_id,
                            F.group_name as grant_group_name  `
    sqlFrom := `  FROM      s_group_member                AS A
                  LEFT JOIN account                       AS B ON (A.oc_id = B.oc_id AND A.account_id     = B.account_id)
                  LEFT JOIN s_group                       AS C ON (A.oc_id = C.oc_id AND A.group_id       = C.group_id  )
                  LEFT JOIN s_group_member_position_grant AS E ON (B.oc_id = E.oc_id AND B.account_id     = E.account_id)
                  LEFT JOIN s_group                       AS F ON (E.oc_id = F.oc_id AND E.grant_group_id = F.group_id  )
                  LEFT JOIN (
                      SELECT
                          M.oc_id,
                          M.account_id,
                          M.gmp_id,
                          M.group_id,
                          M.position_id,
                          P.position_name,
                          N.gmpg_id,
                          N.grant_group_id,
                          GP.group_name as grant_group_name
                      FROM      s_group_member_position       AS M
                      LEFT JOIN s_group_member_position_grant AS N ON (
                              M.oc_id       = N.oc_id
                          AND M.account_id  = N.account_id
                          AND M.group_id    = N.group_id
                          AND M.position_id = N.position_id )
                    LEFT JOIN s_position AS P  ON(M.oc_id = P.oc_id  AND M.position_id    = P.position_id)
                    LEFT JOIN s_group    AS GP ON(M.oc_id = GP.oc_id AND N.grant_group_id = GP.group_id)
                 )  AS  D ON (A.oc_id = D.oc_id AND A.account_id = D.account_id AND A.group_id = D.group_id) `
    sqlWhere := ` WHERE B.deleted_flag = 0 AND A.oc_id = ` + fmt.Sprintf("%d", ocId)

    if keywords, ok := m["keywords"]; ok {
        sqlWhere += " AND ( B.account_real_name LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    B.account_name      LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    B.account_phone     LIKE '%" + fmt.Sprintf("%s", keywords) + "%'  "
        sqlWhere += " OR    D.position_name     LIKE '%" + fmt.Sprintf("%s", keywords) + "%' )"
    }
    nodeLeft, ok1 := m["nodeLeft"]
    nodeRight, ok2 := m["nodeRight"]
    if ok1 && ok2 {
        sqlWhere += ` AND C.node_left  >= ` + fmt.Sprintf("%d", nodeLeft)
        sqlWhere += ` AND C.node_right <= ` + fmt.Sprintf("%d", nodeRight)
    }
    sqlOrderBy := " ORDER BY A.account_id, A.group_id "

    datalist := make([]viewmodel.UserInfo, 0)
    err := d.engine.SQL(sqlSelect + sqlFrom + sqlWhere + sqlOrderBy).Find(&datalist)
    if err != nil {
        return nil, err
    } else {
        return datalist, nil
    }
}