xps-server-go/repositories/user_repo.go

package repositories

import "C"
import (
    "fmt"
    "xorm.io/xorm"
    "xps/datamodel"
    "xps/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{})(*viewmodel.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 := &viewmodel.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
    }
}