package graduation.portlet

import groovy.sql.GroovyRowResult
import org.springframework.transaction.annotation.Transactional

import java.sql.SQLException;

class CustomSqlService {

    def groovySql

    /**
     *
     * @param studentNumber
     * @return
     * @throws SQLException
     */

    @Transactional
    List<GroovyRowResult> getPersonByStudentNumber(final String studentNumber) throws SQLException {
        final String stmt = '''
select
  i.spriden_id, i.spriden_pidm, i.spriden_last_name, i.spriden_first_name, i.spriden_mi,
  c.sorlcur_degc_code, c.sorlcur_levl_code, c.sorlcur_coll_code, c.sorlcur_term_code_ctlg, c.SORLCUR_PROGRAM, c.SORLCUR_ACTIVITY_DATE,
  m.SORMAJR_MAJR_CODE_MAJOR, m.SORMAJR_DEGC_CODE, m.SORMAJR_SBGI_CODE, m.SORMAJR_DEGR_SEQ_NO,
  m1.STVMAJR_DESC
from spriden i
left join sorlcur c on i.spriden_pidm = c.SORLCUR_PIDM
left join sormajr m on i.spriden_pidm = m.SORMAJR_PIDM
left join stvmajr m1 on m.SORMAJR_MAJR_CODE_MAJOR = m1.STVMAJR_CODE
where SPRIDEN_ID LIKE :studentNumber
'''ddd

        return groovySql.rows(stmt, studentNumber: studentNumber)
    }

    /**
     *
     * @param pidm
     * @return
     * @throws SQLException
     */

    @Transactional
    List<GroovyRowResult> getMajorsForStudent(final long pidm) throws SQLException {
        final String stmt = '''
select m.*, mm.*
from SORMAJR m
left join SATURN.STVMAJR mm on mm.STVMAJR_CODE = m.SORMAJR_MAJR_CODE_MAJOR
where m.SORMAJR_PIDM = :pidm
'''

        return groovySql.rows(stmt, pidm: pidm)
    }

    /**
     *
     * @param studentId
     * @return
     * @throws SQLException
     */


    @Transactional
    GroovyRowResult getPersonByStudentId(final String studentId) throws SQLException {
        final sql = '''
select *
from SPRIDEN
where SPRIDEN_ID like :studentId
'''

        return groovySql.firstRow(sql, studentId: studentId)
    }
}