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()
