com.se.common.CommonFunctions.java Source code

Java tutorial

Introduction

Here is the source code for com.se.common.CommonFunctions.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.se.common;

import java.sql.SQLException;
import java.util.ArrayList;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.se.Exceptions.*;
import com.se.RelatedFeaturesNew.RelatedFeatures;
import com.se.connection.SessionUtil;

/**
 * 
 * @author Hanan_Sabry
 */
public class CommonFunctions {
    static Session session;
    static {
        session = SessionUtil.getSession();
    }

    public static long getComId(String pn, int manId) throws PartNumberException {
        try {
            SQLQuery aa = session.createSQLQuery("SELECT COM_ID FROM CM.XLP_SE_COMPONENT " + " WHERE MAN_ID = "
                    + manId + " AND COM_PARTNUM = '" + pn + "'");
            long comId = Long.parseLong(aa.uniqueResult().toString());
            return comId;
        } catch (Exception ex) {
            throw new PartNumberException();
        }
    }

    public static int getPlId(String plName) throws PlException {
        if (plName == null) {
            throw new PlException();
        } else {
            try {
                SQLQuery aa = session.createSQLQuery(
                        "select pl_Id from cm.xlp_se_pl where lower(pl_name) = lower('" + plName + "')");
                int plId = Integer.parseInt(aa.uniqueResult().toString());
                return plId;
            } catch (Exception ex) {
                throw new PlException();
            }
        }
    }

    public static int getVendorId(String vendorName) throws VendorException {
        if (vendorName == null || vendorName.equals("")) {
            return 0;
        } else {
            try {
                SQLQuery aa = session.createSQLQuery(
                        "select man_id from CM.XLP_SE_MANUFACTURER where MAN_NAME = '" + vendorName + "'");
                int vendorId = Integer.parseInt(aa.uniqueResult().toString());
                return vendorId;
            } catch (Exception ex) {
                throw new VendorException();
            }
        }
    }

    public static Object[] getParaFeatureId(String fetName, int plId) throws FeatureNameException {
        if (fetName == null) {
            throw new FeatureNameException("Not Valid SE Feature");
        } else {
            try {
                //            SELECT * FROM CM.XLP_SE_FEATURE WHERE FET_NAME = 'Logic Family' AND pl_id = 2314
                SQLQuery aa = session
                        .createSQLQuery("SELECT FET_ID, COL_NM FROM CM.XLP_SE_FEATURE WHERE FET_NAME = '" + fetName
                                + "' AND pl_id = " + plId);
                Object[] c = (Object[]) aa.uniqueResult();
                //            long fetId = Integer.parseInt(c[0].toString());
                //            String colnm = c[1].toString();
                return c;
            } catch (Exception ex) {
                ex.printStackTrace();
                throw new FeatureNameException("Not Valid SE Feature");
                //            try{
                //               int fetId = getPkgFeatureId(fetName);
                //               return fetId;
                //            }catch(Exception e){
                //               throw new FeatureNameException("Error While validating Feature Name");
                //            }
            }
        }
    }

    public static int getPkgFeatureId(String fetName) throws FeatureNameException {
        if (fetName == null) {
            throw new FeatureNameException("There is not feature");
        } else {
            try {
                SQLQuery aa = session.createSQLQuery(
                        "SELECT fet_id FROM CM.TBL_PKG_FEATURES WHERE FET_NAME = '" + fetName + "'");
                int fetId = Integer.parseInt(aa.uniqueResult().toString());
                return fetId;
            } catch (Exception ex) {
                throw new FeatureNameException("Error While validating Feature Name");
            }
        }
    }

    public static int getPlType(int plid) throws PlException {
        try {
            SQLQuery aa = session.createSQLQuery("SELECT pl_type FROM cm.xlp_se_pl WHERE pl_id = " + plid);
            int plType = Integer.parseInt(aa.uniqueResult().toString());
            return plType;
        } catch (Exception ex) {
            throw new PlException();
        }
    }

    public static int getValId(String fetVal, String fetName, int plId, boolean updated)
            throws FeatureValueException {
        try {
            SQLQuery aa = session.createSQLQuery(
                    "select cm.GET_FINAL_ID(" + plId + ", '" + fetName + "', '" + fetVal + "') from dual");
            int fetValId = Integer.parseInt(aa.uniqueResult().toString());
            if (fetValId == 0 && !updated) {
                throw new FeatureValueException("Reject, Feature Value is not Found");
            } else if (fetValId == 0 && updated) {
                throw new ApprovedValueException();
            }
            return fetValId;
        } catch (Exception ex) {
            throw new FeatureValueException("Reject, Feature Value is not Found");
        }
    }

    public static int getMaskValId(String fetVal, boolean updated) throws FeatureValueException {
        try {
            SQLQuery aa = session.createSQLQuery("select cm.GET_MSK_MSTR_ID('" + fetVal + "') from dual");
            int fetValId = Integer.parseInt(aa.uniqueResult().toString());
            if (fetValId == 0 && !updated) {
                throw new FeatureValueException("Reject, Feature Value is not Found");
            } else if (fetValId == 0 && updated) {
                throw new ApprovedValueException();
            }
            return fetValId;
        } catch (Exception ex) {
            throw new FeatureValueException("Reject, Feature Value is not Found");
        }
    }

    public static int getFamCrossValId(String fetVal, boolean updated) throws FeatureValueException {
        try {
            SQLQuery aa = session.createSQLQuery("select cm.GET_FAM_ID_Fam('" + fetVal + "') from dual");
            int fetValId = Integer.parseInt(aa.uniqueResult().toString());
            if (fetValId == 0 && !updated) {
                throw new FeatureValueException("Reject, Feature Value is not Found");
            } else if (fetValId == 0 && updated) {
                throw new ApprovedValueException();
            }
            return fetValId;
        } catch (Exception ex) {
            throw new FeatureValueException("Reject, Feature Value is not Found");
        }
    }

    public static int getGenValId(String fetVal, boolean updated) throws FeatureValueException {
        try {
            SQLQuery aa = session.createSQLQuery("select importer.get_generic_id('" + fetVal + "') from dual");
            int fetValId = Integer.parseInt(aa.uniqueResult().toString());
            if (fetValId == 0 && !updated) {
                throw new FeatureValueException("Reject, Feature Value is not Found");
            } else if (fetValId == 0 && updated) {
                throw new ApprovedValueException();
            }
            return fetValId;
        } catch (Exception ex) {
            throw new FeatureValueException("Reject, Feature Value is not Found");
        }
    }

    public static int getFamilyId(String fetVal, int vendorId, boolean updated) throws FeatureValueException {
        try {
            SQLQuery aa = session
                    .createSQLQuery("select CM.GET_SERIES_ID('" + fetVal + "', " + vendorId + ") from dual");
            int fetValId = Integer.parseInt(aa.uniqueResult().toString());
            if (fetValId == 0 && !updated) {
                throw new FeatureValueException("Reject, Feature Value is not Found");
            } else if (fetValId == 0 && updated) {
                throw new ApprovedValueException();
            }
            return fetValId;
        } catch (Exception ex) {
            throw new FeatureValueException("Reject, Feature Value is not Found");
        }
    }

    public static String getFetVal(String fetName, int plId, long comId) throws NotValidRelatedFeatureException {
        String query = "";
        try {
            if (fetName.equalsIgnoreCase("Mask")) {
                query = "SELECT CM.GET_MSK_PART_BY_MSK_ID(GET_MSK_ID_BY_COM_ID(" + comId + ")) FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Family Cross")) {
                query = "SELECT CM.GET_FAM_NAME(CM.GET_FAMILY_ID(" + comId + ")) FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Generic")) {
                query = "SELECT IMPORTER.GET_GEN_BY_COM_ID(" + comId + ") FROM DUAL";
            } else if (fetName.equalsIgnoreCase("family")) {
                query = "SELECT CM.GET_FAMILY_NAME(CM.GET_FAMILY_ID(" + comId + ")) FROM DUAL";
            } else {
                query = "SELECT CM.GET_FET_VALUE(" + plId + ", CM.GET_FET_ID(" + plId + ", '" + fetName + "'), "
                        + comId + ")FROM DUAL";
            }

            //         System.out.println(query);
            SQLQuery aa = session.createSQLQuery(query);
            String value = aa.uniqueResult() == null ? null : aa.uniqueResult().toString();
            return value;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new NotValidRelatedFeatureException("Can't Get Feature Value");
        }
    }

    public static String getFetVal(String fetName, String plName, long comId)
            throws NotValidRelatedFeatureException {
        String query = "";
        try {
            if (fetName.equalsIgnoreCase("Mask")) {
                query = "SELECT CM.GET_MSK_PART_BY_MSK_ID(GET_MSK_ID_BY_COM_ID(" + comId + ")) FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Family Cross")) {
                query = "SELECT CM.GET_FAM_NAME(CM.GET_FAMILY_ID(" + comId + ")) FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Generic")) {
                query = "SELECT IMPORTER.GET_GEN_BY_COM_ID(" + comId + ") FROM DUAL";
            } else if (fetName.equalsIgnoreCase("family")) {
                query = "SELECT CM.GET_FAMILY_NAME(CM.GET_FAMILY_ID(" + comId + ")) FROM DUAL";
            } else {
                query = "SELECT CM.GET_FET_VALUE(CM.GET_PL_ID('" + plName + "'), " + "CM.GET_FET_ID(CM.GET_PL_ID('"
                        + plName + "'), '" + fetName + "'), " + comId + ")FROM DUAL";
            }

            //         System.out.println(query);
            SQLQuery aa = session.createSQLQuery(query);
            String value = aa.uniqueResult() == null ? null : aa.uniqueResult().toString();
            return value;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new NotValidRelatedFeatureException("Can't Get Feature Value");
        }
    }

    public static String getFeatureName(long fetId) throws FeatureNameException {
        //      GET_FET_NAME
        try {
            SQLQuery aa = session.createSQLQuery("SELECT CM.GET_FET_NAME(" + fetId + ") from dual");
            String fetName = aa.uniqueResult().toString();
            return fetName;
        } catch (Exception ex) {
            throw new FeatureNameException("Feature not found");
        }
    }

    public static String getFeatureValueByID(String fetValId) throws FeatureValueException {
        //      GET_FET_NAME
        try {
            SQLQuery aa = session.createSQLQuery("SELECT CM.GET_FETVAL(" + fetValId + ") from dual");
            String fetName = aa.uniqueResult().toString();
            return fetName;
        } catch (Exception ex) {
            throw new FeatureValueException();
        }
    }

    public static String getFeatureName(String fetId) throws FeatureNameException {
        //      GET_FET_NAME
        try {
            if (fetId.equalsIgnoreCase(RelatedFeatures.MASK_FET_ID)) {
                return RelatedFeatures.MASK_FET_ID;
            } else if (fetId.equalsIgnoreCase(RelatedFeatures.FAM_FET_ID)) {
                return RelatedFeatures.FAM_FET_ID;
            } else if (fetId.equalsIgnoreCase(RelatedFeatures.FAMILY_FET_ID)) {
                return RelatedFeatures.FAMILY_FET_ID;
            } else if (fetId.equalsIgnoreCase(RelatedFeatures.GEN_FET_ID)) {
                return RelatedFeatures.GEN_FET_ID;
            }
            SQLQuery aa = session.createSQLQuery("SELECT CM.GET_FET_NAME(" + fetId + ") from dual");
            String fetName = aa.uniqueResult().toString();
            return fetName;
        } catch (Exception ex) {
            throw new FeatureNameException("Feature not found");
        }
    }

    public static String getPlName(int plId) throws PlException {
        try {
            SQLQuery aa = session.createSQLQuery("select pl_name from cm.xlp_se_pl where pl_id = " + plId);
            String plName = aa.uniqueResult().toString();
            return plName;
        } catch (Exception ex) {
            throw new PlException();
        }
    }

    public static String getManName(int manId) throws VendorException {
        if (manId == 0) {
            return "";
        }
        try {
            SQLQuery aa = session
                    .createSQLQuery("select man_name from cm.XLP_SE_MANUFACTURER where man_id = " + manId);
            String manName = aa.uniqueResult().toString();
            return manName;
        } catch (Exception ex) {
            throw new VendorException();
        }
    }

    public static String getFetValById(String fetName, String fetValid) throws NotValidRelatedFeatureException {
        String query = "";
        try {
            if (fetName.equalsIgnoreCase("Mask")) {
                query = "SELECT CM.GET_MSK_PART_BY_MSK_ID(" + fetValid + ") FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Family Cross")) {
                query = "SELECT CM.GET_FAM_NAME(" + fetValid + ") FROM DUAL";
            } else if (fetName.equalsIgnoreCase("Generic")) {
                query = "SELECT IMPORTER.GET_GEN_BY_GEN_ID(" + fetValid + ") FROM DUAL";
            } else if (fetName.equalsIgnoreCase("family")) {
                query = "SELECT CM.GET_FAMILY_NAME(" + fetValid + ") FROM DUAL";
            } else {
                query = "SELECT CM.GET_FETVAL(" + fetValid + ")FROM DUAL";
            }

            //         System.out.println(query);
            SQLQuery aa = session.createSQLQuery(query);
            String value = aa.uniqueResult() == null ? null : aa.uniqueResult().toString();
            return value;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new NotValidRelatedFeatureException("Can't Get Feature Value");
        }
    }

    public static void main(String[] args) throws NotValidRelatedFeatureException {
        getFetVal("Mask", 2515, 30556064);
    }

    public static ArrayList<String> getPlFeatures(int plId, Session session) {
        SQLQuery cr = session.createSQLQuery("SELECT fet_name FROM CM.XLP_SE_FEATURE WHERE lower(fet_name)"
                + " not in('pin count','supplier package','product_url','family','life cycle','rohs','standard package name','vendor') "
                + " and pl_id= " + plId + "and COL_NM is not null order by FET_EXPERTSHEETORDER asc");

        @SuppressWarnings("unchecked")
        ArrayList<String> fets = (ArrayList<String>) cr.list();
        return fets;
    }
}