GET

package org.jleaf.pos2.bo.vehicle;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
import org.jleaf.core.annotation.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.CriteriaHelper;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.pos2.dao.VehicleDao;
import org.jleaf.pos2.entity.Vehicle;
import org.jleaf.pos2.entity.VehicleGroup;
import org.jleaf.pos2.entity.VehicleType;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Business Transactions: Get Vehicle List
 *
 * @author Henokh Marjie Rivaldy , 26 Juli 2016
 */

@Service
@InfoIn(value = {
        @Info(name = "keyword", description = "keyword", type = String.class),
        @Info(name = "limit", description = "limit", type = Long.class),
        @Info(name = "offset", description = "offset", type = Long.class),
        @Info(name = "active", description = "active", type = String.class)})
@InfoOut(value = {
        @Info(name = "vehicleList", description = "vehicle list (vehicleCode, licenseNo, ownerName, chassisNo, engineNo, vehicleGroupCode, vehicleTypeCode, yearMade, customerName, address1, address2, address3, phone1, phone2, activeDateTime, nonActiveDateTime , version , createDateTime, createUsername , updateDateTime , updateUsername)")})
@ErrorList(errorKeys = {})
public class GetVehicleList extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    VehicleDao vehicleDao;

    @Override
    public String getDescription() {
        return "Get Vehicle List";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {

        ValidationUtil.valDtoContainsKey(inputDto, "keyword");
        ValidationUtil.valDtoContainsKey(inputDto, "limit");
        ValidationUtil.valDtoContainsKey(inputDto, "offset");
        ValidationUtil.valDtoContainsKey(inputDto, "active");

        String keyword = inputDto.getString("keyword");
        String active = inputDto.getString("active");

        List<Object[]>result=null;

//      SELECT  A.vehicle_code, A.license_no, A.owner_name,  -- vehicleCode, licenseNo, ownerName
//          A.chassis_no, A.engine_no, A.vehicle_group_code,  --chassisNo, engineNo, vehicleGroupCode
//          B.vehicle_group_name, A.vehicle_type_code, C.vehicle_type_name,     -- vehicleGroupName (tidak ada), vehicleTypeCode, vehicleTypeName (tidak ada),
//          A.year_made, A.customer_name, A.address1,  -- yearMade, customerName, address1,
//          A.address2, A.address3, A.phone1, -- address2, address3, phone1,
//          A.phone2, A.active, A.version -- phone2, active, version
//      FROM m_vehicle A
//      JOIN m_vehicle_group B ON A.vehicle_group_code = B.vehicle_group_code
//      JOIN m_vehicle_type C ON A.vehicle_type_code = C.vehicle_type_code


        QueryBuilder builder = new QueryBuilder();
        builder.add(" SELECT    A.vehicle_code, A.license_no, A.owner_name, ")
            .add("  A.chassis_no, A.engine_no, A.vehicle_group_code,   ")
            .add("  B.vehicle_group_name, A.vehicle_type_code, C.vehicle_type_name,  ")
            .add("  A.year_made, A.customer_name, A.address1, ")
            .add("  A.address2, A.address3, A.phone1, ")
            .add("  A.phone2, A.active, A.version ")
            .add(" FROM ").add(Vehicle.TABLE_NAME).add(" A ")
            .add(" JOIN ").add(VehicleGroup.TABLE_NAME).add(" B ON A.vehicle_group_code = B.vehicle_group_code ")
            .add(" JOIN ").add(VehicleType.TABLE_NAME).add(" A.vehicle_type_code = C.vehicle_type_code ")
            .add(" WHERE true ")

            .addIfNotEmpty(keyword, new StringBuilder()
                .append(" AND (")
                    .append( CriteriaHelper.likeExpressionIgnoreCase(keyword, " a.owner_name ")  )
                    .append(" OR ").append( CriteriaHelper.likeExpressionIgnoreCase(keyword, " a.license_no ") )
                    .append(" OR ").append( CriteriaHelper.likeExpressionIgnoreCase(keyword, " a.customer_name ") )
                    .append(") ").toString())

            .addIfNotEmpty(active, "  AND " + CriteriaHelper.equalsExpressionIgnoreCase(active, "a.active"))
            .add(" ORDER BY a.vehicle_code ASC ")
            .addIfNotNull( inputDto.get("limit") , " LIMIT  "+inputDto.get("limit"))
            .addIfNotNull( inputDto.get("offset") , " OFFSET "+inputDto.get("offset"))
            ;



        Query q = vehicleDao.createNativeQuery(builder.toString());

        result = q.getResultList();

        return new Dto().putList(
                "vehicleList",
                DtoUtil.createDtoListFromArray(result,
                        "vehicleCode", "licenseNo", "ownerName",
                        "chassisNo", "engineNo", "vehicleGroupCode",
                        "vehicleGroupName", "vehicleTypeCode","vehicleTypeName",
                        "yearMade", "customerName", "address1",
                        "address2", "address3", "phone1",
                        "phone2", "active", "activeDatetime",
                        "nonActiveDatetime" , "version" , "createDatetime",
                        "createUsername" , "updateDatetime" , "updateUsername"));

    }

}