Grails complex query

By | June 5th 2019 01:09:04 PM | viewed 128 times

Sql Query 1

import groovy.sql.Sql
 def dataSource
  def query =''
  def irAplcInstanceRowInstance = null
  groovy.sql.Sql sql = new Sql(dataSource)
  
   irAplcInstanceRowInstance = sql.rows("select distinct ap.id,ap.AUTH_USERS_ID,ap.F_NAME,ap.L_NAME,ap.DATE_OF_BIRTH,ap.IS_WRTN_SELT_DATE,ap.IS_VIVA_SELT_DATE,( select t.IS_EMP FROM AUTH_USERS t WHERE t.ID = ap.AUTH_USERS_ID) as is_emp\n" +
                    "\n,(select  m.ACCOUNT_LOCKED FROM AUTH_USERS m WHERE m.ID = ap.AUTH_USERS_ID) as is_lock\n" +
                    "\n,(select  n.CODE FROM AUTH_USERS n WHERE n.ID = ap.AUTH_USERS_ID) as code\n" +
                    "\n,(select  v.USERNAME FROM AUTH_USERS v WHERE v.ID = ap.AUTH_USERS_ID) as username\n" +
                    "\n from IR_APLC ap\n" +
                    "\n left join IR_APLC_EDUCATION ed on ap.AUTH_USERS_ID = ed.AUTH_USERS_ID\n" +
                    "\nleft join IR_APLC_EMPLOY_HSTY em on ap.AUTH_USERS_ID = em.AUTH_USERS_ID\n" +
                    "\n left join IR_APLC_CAR_APP_INFO ca on ap.AUTH_USERS_ID = ca.AUTH_USERS_ID\n" +
                    "\n left join IR_APLC_SPECIALIZ sq on ap.AUTH_USERS_ID = sq.AUTH_USERS_ID\n" +
                    "\nleft join IR_APLC_PROF_QLFN pq on ap.AUTH_USERS_ID = pq.AUTH_USERS_ID\n" +
                    "\n where 1=1 ${query} ")

Gorom ORM Query 2


 Map setParamsVal = new HashMap();
String query ="select id, empId from HrCrAssignment where assignmentSts=:assignmentSts";

        setParamsVal.put("assignmentSts",com.enums.AssignmentSts.ACTIVE);

        if(params?.ogG){

            query +=" and setnOrgMstGroup.id=:setnOrgMstGroupId";
            setParamsVal.put("setnOrgMstGroupId",Long.parseLong(params?.ogG));
        }

        if(params?.ogSG){

            query +=" and setnOrgMstSubg.id=:setnOrgMstSubgId";
            setParamsVal.put("setnOrgMstSubgId",Long.parseLong(params?.ogSG));
        }

        if(params?.ogC){

            query +=" and setnOrgMstComp.id=:setnOrgMstCompId";
            setParamsVal.put("setnOrgMstCompId",Long.parseLong(params?.ogC));
        }

        if(params?.ogL){

            query +=" and setnOrgMstLoc.id=:setnOrgMstLocId";
            setParamsVal.put("setnOrgMstLocId",Long.parseLong(params?.ogL));
        }

        if(params?.ogD){

            query +=" and setnOrgMstDiv.id=:setnOrgMstDivId";
            setParamsVal.put("setnOrgMstDivId",Long.parseLong(params?.ogD));
        }

        if(params?.ogS){

            query +=" and setnOrgMstSec.id=:setnOrgMstSecId";
            setParamsVal.put("setnOrgMstSecId",Long.parseLong(params?.ogS));
        }

        if(params?.ogSS){

            query +=" and setnOrgMstSubs.id=:setnOrgMstSubsId";
            setParamsVal.put("setnOrgMstSubsId",Long.parseLong(params?.ogSS));
        }

        if(params?.ogDpt){

            query +=" and setnOrgMstDept.id=:setnOrgMstDeptId"
            setParamsVal.put("setnOrgMstDeptId",Long.parseLong(params?.ogDpt));
        }

        if(params?.ogOPU){

            query +=" and setnOrgMstOptu.id=:setnOrgMstOptuId";
            setParamsVal.put("setnOrgMstOptuId",Long.parseLong(params?.ogOPU));
        }

        if(params?.ogLE){

            query +=" and setnOrgMstLegale.id=:setnOrgMstLegaleId";
            setParamsVal.put("setnOrgMstLegaleId",Long.parseLong(params?.ogLE));
        }

        if(params?.ogIV){

            query +=" and setnOrgMstInvto.id=:setnOrgMstInvtoId";
            setParamsVal.put("setnOrgMstInvtoId",Long.parseLong(params?.ogIV));
        }

        if(JSON.parse(params?.alkpDeg)[0]){

            query +=" and setnAlkpDeg.id IN(${designation})";

        }


        allEmpList = HrCrAssignment.executeQuery(query,setParamsVal);

Gorom ORM Query 3

PrlPayroll model mapping as follows

package com.payrol

import com.hrms.HrCrAssignment
import com.hrms.HrEmpDtl
import com.setting.SetnAlkp
import com.setting.SetnOrgMst

class PrlPayroll {

    Long id
    String empId
    Date fromDate
    Date toDate


    static belongsTo = [hrPrlPeriod:HrPrlPeriod,hrEmpDtl:HrEmpDtl,hrCrAssignment:HrCrAssignment]

    static hasMany = [prlPayrollDtl:PrlPayrollDtl]


    static constraints = {

        empId nullable: true
        fromDate nullable: true
        toDate nullable: true

        hrPrlPeriod nullable: true
        hrEmpDtl nullable: true

    }

    static mapping ={
        version false
        prlPayrollDtl cascade: 'all-delete-orphan'
    }
}

PrlPayrollDtl model mapping as follows

package com.payrol

import com.hrms.HrEmpDtl

class PrlPayrollDtl {

    Long id
    Float amount
    String note


    Date createDate
    Date updateDate
    HrEmpDtl createBy
    HrEmpDtl updateBy


    Integer isProcess = 0
    Integer isCommit = 0
    String processKey
    String processType
    Integer isApproved = 0


    static belongsTo = [prlPayroll:PrlPayroll,prlEmpPayrollEltDtl:PrlEmpPayrollEltDtl]

    static constraints = {

        amount nullable: true
        note nullable: true
        prlPayroll nullable: true
        prlEmpPayrollEltDtl nullable: true

        createDate nullable: true
        updateDate nullable: true
        createBy nullable: true
        updateBy nullable: true
        isCommit nullable: true
        isApproved nullable: true
        processKey nullable: true
        processType nullable: true
    }

    static mapping ={
        version false
    }
}


 for(List eachEmpSalaryPayHeadDtlAmount : allEmpSalaryPayHeadDtlAmount){

            PrlPayroll prlPayrollInstance;

            if(params?.type=='process' && params?.processVal.toInteger()==0 && params?.commitVal.toInteger()==0){

                prlPayrollInstance = new PrlPayroll();
                prlPayrollInstance.setEmpId(eachEmpSalaryPayHeadDtlAmount[0]?.empId)
                prlPayrollInstance.setHrEmpDtl(HrEmpDtl.findByAuthUsers(AuthUsers.findByUsername(eachEmpSalaryPayHeadDtlAmount[0]?.empId)))
                prlPayrollInstance.setHrCrAssignment(HrCrAssignment.findById(eachEmpSalaryPayHeadDtlAmount[0]?.hrCrAssignmentId))
                prlPayrollInstance.setHrPrlPeriod(HrPrlPeriod.findById(Long.parseLong(params?.pPrd)))


            }else if(params?.type=='reprocess' && params?.processVal.toInteger()==1 && params?.commitVal.toInteger()==0){

                prlPayrollInstance = PrlPayroll.findByEmpIdAndHrCrAssignment(eachEmpSalaryPayHeadDtlAmount[0]?.empId,HrCrAssignment.findById(eachEmpSalaryPayHeadDtlAmount[0]?.hrCrAssignmentId));

            }

            for( Map eachHead : eachEmpSalaryPayHeadDtlAmount){

                PrlPayrollDtl prlPayrollDtlInstance;


                if(params?.type=='process' && params?.processVal.toInteger()==0 && params?.commitVal.toInteger()==0){


                    prlPayrollDtlInstance = new PrlPayrollDtl();
                    prlPayrollDtlInstance.setCreateBy(HrEmpDtl.findByAuthUsers(AuthUsers.findByUsername(eachHead?.empId)))
                    prlPayrollDtlInstance.setCreateDate(new DateAndTime().currentGmtDateAndTime())

                }else if(params?.type=='reprocess' && params?.processVal.toInteger()==1 && params?.commitVal.toInteger()==0){

                    prlPayrollDtlInstance = PrlPayrollDtl.findByProcessTypeAndProcessKeyAndPrlEmpPayrollEltDtlAndPrlPayroll(params?.pt,params?.processkey,eachHead?.prlEmpPayrollEltDtlInstance,prlPayrollInstance);
                    prlPayrollDtlInstance.setUpdateBy(HrEmpDtl.findByAuthUsers(AuthUsers.findByUsername(eachHead?.empId)))
                    prlPayrollDtlInstance.setUpdateDate(new DateAndTime().currentGmtDateAndTime())

                }

                prlPayrollDtlInstance.setPrlEmpPayrollEltDtl(eachHead?.prlEmpPayrollEltDtlInstance)
                prlPayrollDtlInstance.setAmount(eachHead?.amount)
                prlPayrollDtlInstance.setIsProcess(mapValue.isProcess.toInteger());
                prlPayrollDtlInstance.setIsCommit(mapValue.isCommit.toInteger());
                prlPayrollDtlInstance.setProcessKey(processKey)
                prlPayrollDtlInstance.setProcessType(params?.pt);


                prlPayrollInstance.addToPrlPayrollDtl(prlPayrollDtlInstance)

            }


            try{

                prlPayrollInsert = prlPayrollInstance.save(flush:true);

            }catch(Exception ex){
                ex.printStackTrace();
                println(ex);
                failTotalCount ++;
            }

Gorom ORM Query 4 with pagination

            if(circularId && circularId !=null){
                query +=" and circular.id = "+ circularId+" "
            }

            if(instituteType && instituteType !=null){
                query +="and apmtBy ='"+ instituteType.toString()+"' "
            }

            if(apmtInstitute && apmtInstitute !=null){
                query +="and institute.id = "+ apmtInstitute.toLong()+" "
            }

            if(jobType && jobType !=null){
                query +="and jobType.id = "+ jobType.toLong()+" "
            }

            if(apmtLevel && apmtLevel !=null){
                query +="and apmtLevel.id = "+ apmtLevel.toLong()+" "
            }

            if(division && division !=null){
                query +="and division.id = "+ division.toLong()+" "
            }

            if(district && district !=null){
                query +="and district.id = "+ district.toLong()+" "
            }

            if(aplcNo && aplcNo !=null){
                query +="and applicantNo = '"+ aplcNo.toString()+"'"
            }

            query +=" order by id desc"

            def lst=Teacher.executeQuery(query,[max: params.max, offset: params?.offset == null ? 0 : params.offset])
			
            def totalCount = Teacher.executeQuery(query).size()
			
            [teacherInstanceList: lst, teacherInstanceTotal: totalCount]

Gorom ORM Query 5 with pagination

webMenuInstanceList = WebMenu.createCriteria().list (params){
                and{
                    if(params?.menuType){

                        eq("menuType",params?.menuType)
                    }


                    if(mtitle){
                        like("menuTitle",'%'+mtitle.capitalize()+'%')
                    }
                }

                order("menuTitle", "asc")
                order("id", "desc")
            }
	def totalCount = webMenuInstanceList.count()		
			
bONEandALL