<commit_before>package org.openlmis.UiUtils;

import java.sql.*;
import java.util.Properties;
import java.io.*;

public class DBWrapper {

    String baseUrl, dbUrl, dbUser, dbPassword;

    public DBWrapper() throws IOException {

        final Properties props = new Properties();

        System.out.println(System.getProperty("user.dir") + "/src/main/resources/config.properties");
        props.load(new FileInputStream(System.getProperty("user.dir") + "/src/main/resources/config.properties"));
        baseUrl = props.getProperty("baseUrl");
        dbUrl = props.getProperty("dbUrl");
        dbUser = props.getProperty("dbUser");
        dbPassword = props.getProperty("dbPassword");
    }

    public ResultSet dbConnection(String Query, String indicator) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;


        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException cnfe) {
            System.out.println("Could not find the JDBC driver!");
            System.exit(1);
        }

        String url = dbUrl;
        String user = dbUser;
        String password = <PASSWORD>;

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            if (indicator.equalsIgnoreCase("select")) {
                rs = st.executeQuery(Query);

                if (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            } else {
                st.executeUpdate(Query);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {

            return rs;

        }
    }

    public void insertUser(String userName, String password) throws SQLException, IOException {
        boolean flag = false;
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select userName from users;", "select");
        if (rs.next()) {
            if (rs.getString(1).contains(userName)) {
                flag = true;
            }
        }
        if (flag) {
            dbwrapper.dbConnection("delete from users where userName like('"+userName+"');", "alter");
        }
        dbwrapper.dbConnection("INSERT INTO users\n" +
                "  (id, userName, password, facilityId) VALUES\n" +
                "  (200, '"+userName+"', '"+password+"', null);", "alter");

    }

    public void insertFacility() throws IOException , SQLException
    {

        DBWrapper dbWrapper=new DBWrapper();
        ResultSet rs = dbWrapper.dbConnection("Select code from facilities;", "select");

        if (rs.next()) {

            dbWrapper.dbConnection("delete from facilities;", "alter");


        }
        dbWrapper.dbConnection("INSERT INTO facilities\n" +
                "(code, name, description, gln, mainPhone, fax, address1, address2, geographicZoneId, typeId, catchmentPopulation, latitude, longitude, altitude, operatedById, coldStorageGrossCapacity, coldStorageNetCapacity, suppliesOthers, sdp, hasElectricity, online, hasElectronicScc, hasElectronicDar, active, goLiveDate, goDownDate, satellite, comment, dataReportable) values\n" +
                "('F1756','Village Dispensary','IT department','G7645',9876234981,'fax','A','B',1,1,333,22.1,1.2,3.3,2,9.9,6.6,'TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','11/11/12','11/11/1887','TRUE','fc','TRUE'),\n" +
                "('F1757','Central Hospital','IT department','G7646',9876234981,'fax','A','B',1,2,333,22.3,1.2,3.3,3,9.9,6.6,'TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','11/11/12','11/11/2012','TRUE','fc','TRUE');\n", "alter");

    }


    public void allocateFacilityToUser() throws IOException {
        DBWrapper dbwrapper = new DBWrapper();
        dbwrapper.dbConnection("update users set facilityId = (Select id from facilities order by modifiedDate DESC limit 1) where id=200;", "alter");

    }


    public void deleteData() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        dbwrapper.dbConnection("delete from role_rights;", "alter");
        dbwrapper.dbConnection("delete from role_assignments;", "alter");
        dbwrapper.dbConnection("delete from roles;", "alter");
        dbwrapper.dbConnection("delete from facility_approved_products;", "alter");
        dbwrapper.dbConnection("delete from program_products;", "alter");
        dbwrapper.dbConnection("DELETE FROM requisition_line_items;", "alter");
        dbwrapper.dbConnection("delete from products;", "alter");
        dbwrapper.dbConnection("delete from users where userName like('User%');", "alter");
        dbwrapper.dbConnection("delete from requisition;", "alter");
        dbwrapper.dbConnection("delete from programs_supported;", "alter");
        dbwrapper.dbConnection("delete from supervisory_nodes;", "alter");
        dbwrapper.dbConnection("delete from facilities;", "alter");
        dbwrapper.dbConnection("delete from program_rnr_columns;", "alter");
    }


    public void insertRoles() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select id from roles;", "select");

        if (rs.next()) {
            dbwrapper.dbConnection("delete from role_rights;", "alter");
            dbwrapper.dbConnection("delete from role_assignments;", "alter");
            dbwrapper.dbConnection("delete from roles;", "alter");
        }
        dbwrapper.dbConnection("INSERT INTO roles\n" +
                " (id, name, description) VALUES\n" +
                " (2, 'store in-charge', ''),\n" +
                " (3, 'district pharmacist', '');", "alter");

    }

    public void insertRoleRights() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select roleId from role_rights;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from role_rights;", "alter");
            dbwrapper.dbConnection("delete from role_assignments;", "alter");
            dbwrapper.dbConnection("delete from roles;", "alter");

        }

        dbwrapper.dbConnection("INSERT INTO role_rights\n" +
                "  (roleId, rightId) VALUES\n" +
                "  (2, 'VIEW_REQUISITION'),\n" +
                "  (2, 'CREATE_REQUISITION'),\n" +
                "  (3, 'VIEW_REQUISITION'),\n" +
                "  (3, 'UPLOADS'),\n" +
                "  (3, 'MANAGE_FACILITY'),\n" +
                "  (3, 'CONFIGURE_RNR');", "alter");
    }

    public void insertSupervisoryNodes() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select facilityId from supervisory_nodes;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from supervisory_nodes;", "alter");

        }
        dbwrapper.dbConnection("INSERT INTO supervisory_nodes\n" +
                "  (parentId, facilityId, name, code, approvalPoint) VALUES\n" +
                "  (null, (SELECT id FROM facilities WHERE code = 'F1756'), 'Node 1', 'N1', true);", "alter");
    }

    public void insertRoleAssignment() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select userId from role_assignments;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from role_assignments;", "alter");

        }
        dbwrapper.dbConnection("INSERT INTO role_assignments (userId, roleId, programId, supervisoryNodeId) VALUES (1, 3, 1, null), (200, 2, 1, null),\n" +
                "  (200, 2, 1, (SELECT id from supervisory_nodes WHERE code = 'N1'));", "alter");
    }

    public void insertProducts() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select id from products;", "select");

        if (rs.next()) {
            dbwrapper.dbConnection("delete from facility_approved_products;", "alter");
            dbwrapper.dbConnection("delete from program_products;", "alter");
            dbwrapper.dbConnection("delete from products;", "alter");<commit_msg>#0|Manjyot|Altered FT because of changes made by devs in users.sql file i.e. removed isapprovalpoint from supervisory_nodes table<commit_after>package org.openlmis.UiUtils;

import java.sql.*;
import java.util.Properties;
import java.io.*;

public class DBWrapper {

    String baseUrl, dbUrl, dbUser, dbPassword;

    public DBWrapper() throws IOException {

        final Properties props = new Properties();

        System.out.println(System.getProperty("user.dir") + "/src/main/resources/config.properties");
        props.load(new FileInputStream(System.getProperty("user.dir") + "/src/main/resources/config.properties"));
        baseUrl = props.getProperty("baseUrl");
        dbUrl = props.getProperty("dbUrl");
        dbUser = props.getProperty("dbUser");
        dbPassword = <PASSWORD>.getProperty("dbPassword");
    }

    public ResultSet dbConnection(String Query, String indicator) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;


        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException cnfe) {
            System.out.println("Could not find the JDBC driver!");
            System.exit(1);
        }

        String url = dbUrl;
        String user = dbUser;
        String password = <PASSWORD>;

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            if (indicator.equalsIgnoreCase("select")) {
                rs = st.executeQuery(Query);

                if (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            } else {
                st.executeUpdate(Query);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {

            return rs;

        }
    }

    public void insertUser(String userName, String password) throws SQLException, IOException {
        boolean flag = false;
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select userName from users;", "select");
        if (rs.next()) {
            if (rs.getString(1).contains(userName)) {
                flag = true;
            }
        }
        if (flag) {
            dbwrapper.dbConnection("delete from users where userName like('"+userName+"');", "alter");
        }
        dbwrapper.dbConnection("INSERT INTO users\n" +
                "  (id, userName, password, facilityId) VALUES\n" +
                "  (200, '"+userName+"', '"+password+"', null);", "alter");

    }

    public void insertFacility() throws IOException , SQLException
    {

        DBWrapper dbWrapper=new DBWrapper();
        ResultSet rs = dbWrapper.dbConnection("Select code from facilities;", "select");

        if (rs.next()) {

            dbWrapper.dbConnection("delete from facilities;", "alter");


        }
        dbWrapper.dbConnection("INSERT INTO facilities\n" +
                "(code, name, description, gln, mainPhone, fax, address1, address2, geographicZoneId, typeId, catchmentPopulation, latitude, longitude, altitude, operatedById, coldStorageGrossCapacity, coldStorageNetCapacity, suppliesOthers, sdp, hasElectricity, online, hasElectronicScc, hasElectronicDar, active, goLiveDate, goDownDate, satellite, comment, dataReportable) values\n" +
                "('F1756','Village Dispensary','IT department','G7645',9876234981,'fax','A','B',1,1,333,22.1,1.2,3.3,2,9.9,6.6,'TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','11/11/12','11/11/1887','TRUE','fc','TRUE'),\n" +
                "('F1757','Central Hospital','IT department','G7646',9876234981,'fax','A','B',1,2,333,22.3,1.2,3.3,3,9.9,6.6,'TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','TRUE','11/11/12','11/11/2012','TRUE','fc','TRUE');\n", "alter");

    }


    public void allocateFacilityToUser() throws IOException {
        DBWrapper dbwrapper = new DBWrapper();
        dbwrapper.dbConnection("update users set facilityId = (Select id from facilities order by modifiedDate DESC limit 1) where id=200;", "alter");

    }


    public void deleteData() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        dbwrapper.dbConnection("delete from role_rights;", "alter");
        dbwrapper.dbConnection("delete from role_assignments;", "alter");
        dbwrapper.dbConnection("delete from roles;", "alter");
        dbwrapper.dbConnection("delete from facility_approved_products;", "alter");
        dbwrapper.dbConnection("delete from program_products;", "alter");
        dbwrapper.dbConnection("DELETE FROM requisition_line_items;", "alter");
        dbwrapper.dbConnection("delete from products;", "alter");
        dbwrapper.dbConnection("delete from users where userName like('User%');", "alter");
        dbwrapper.dbConnection("delete from requisition;", "alter");
        dbwrapper.dbConnection("delete from programs_supported;", "alter");
        dbwrapper.dbConnection("delete from supervisory_nodes;", "alter");
        dbwrapper.dbConnection("delete from facilities;", "alter");
        dbwrapper.dbConnection("delete from program_rnr_columns;", "alter");
    }


    public void insertRoles() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select id from roles;", "select");

        if (rs.next()) {
            dbwrapper.dbConnection("delete from role_rights;", "alter");
            dbwrapper.dbConnection("delete from role_assignments;", "alter");
            dbwrapper.dbConnection("delete from roles;", "alter");
        }
        dbwrapper.dbConnection("INSERT INTO roles\n" +
                " (id, name, description) VALUES\n" +
                " (2, 'store in-charge', ''),\n" +
                " (3, 'district pharmacist', '');", "alter");

    }

    public void insertRoleRights() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select roleId from role_rights;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from role_rights;", "alter");
            dbwrapper.dbConnection("delete from role_assignments;", "alter");
            dbwrapper.dbConnection("delete from roles;", "alter");

        }

        dbwrapper.dbConnection("INSERT INTO role_rights\n" +
                "  (roleId, rightId) VALUES\n" +
                "  (2, 'VIEW_REQUISITION'),\n" +
                "  (2, 'CREATE_REQUISITION'),\n" +
                "  (3, 'VIEW_REQUISITION'),\n" +
                "  (3, 'UPLOADS'),\n" +
                "  (3, 'MANAGE_FACILITY'),\n" +
                "  (3, 'CONFIGURE_RNR');", "alter");
    }

    public void insertSupervisoryNodes() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select facilityId from supervisory_nodes;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from supervisory_nodes;", "alter");

        }
        dbwrapper.dbConnection("INSERT INTO supervisory_nodes\n" +
                "  (parentId, facilityId, name, code) VALUES\n" +
                "  (null, (SELECT id FROM facilities WHERE code = 'F1756'), 'Node 1', 'N1');", "alter");
    }

    public void insertRoleAssignment() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select userId from role_assignments;", "select");

        if (rs.next()) {

            dbwrapper.dbConnection("delete from role_assignments;", "alter");

        }
        dbwrapper.dbConnection("INSERT INTO role_assignments (userId, roleId, programId, supervisoryNodeId) VALUES (1, 3, 1, null), (200, 2, 1, null),\n" +
                "  (200, 2, 1, (SELECT id from supervisory_nodes WHERE code = 'N1'));", "alter");
    }

    public void insertProducts() throws SQLException, IOException {
        DBWrapper dbwrapper = new DBWrapper();
        ResultSet rs = dbwrapper.dbConnection("Select id from products;", "select");

        if (rs.next()) {
            dbwrapper.dbConnection("delete from facility_approved_products;", "alter");
            dbwrapper.dbConnection("delete from program_products;", "alter");
            dbwrapper.dbConnection("delete from products;", "alter");
{'fraction_non_alphanumeric': 0.1398340747865817, 'fraction_numerical': 0.016652639172778646, 'mean_word_length': 2.7703989120580235, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5107396', 'n_tokens_mistral': 4836, 'n_tokens_neox': 4502, 'n_words': 1059}