Index: itignore =================================================================== --- .gitignore (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,38 +1,0 @@ -target/ -!.mvn/wrapper/maven-wrapper.jar -!**/src/main/**/target/ -!**/src/test/**/target/ - -### IntelliJ IDEA ### -.idea/modules.xml -.idea/jarRepositories.xml -.idea/compiler.xml -.idea/libraries/ -*.iws -*.iml -*.ipr - -### Eclipse ### -.apt_generated -.classpath -.factorypath -.project -.settings -.springBeans -.sts4-cache - -### NetBeans ### -/nbproject/private/ -/nbbuild/ -/dist/ -/nbdist/ -/.nb-gradle/ -build/ -!**/src/main/**/build/ -!**/src/test/**/build/ - -### VS Code ### -.vscode/ - -### Mac OS ### -.DS_Store Index: dea/.gitignore =================================================================== --- .idea/.gitignore (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,8 +1,0 @@ -# Default ignored files -/shelf/ -/workspace.xml -# Editor-based HTTP Client requests -/httpRequests/ -# Datasource local storage ignored files -/dataSources/ -/dataSources.local.xml Index: dea/encodings.xml =================================================================== --- .idea/encodings.xml (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,7 +1,0 @@ - - - - - - - Index: dea/misc.xml =================================================================== --- .idea/misc.xml (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,15 +1,0 @@ - - - - - - - - - - Index: README.md =================================================================== --- README.md (revision a7fc79bef101831853105028eefbd71280287fbf) +++ README.md (revision a7fc79bef101831853105028eefbd71280287fbf) @@ -0,0 +1,69 @@ +

Laravel Logo

+ +

+Build Status +Total Downloads +Latest Stable Version +License +

+ +## About Laravel + +Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experience to be truly fulfilling. Laravel takes the pain out of development by easing common tasks used in many web projects, such as: + +- [Simple, fast routing engine](https://laravel.com/docs/routing). +- [Powerful dependency injection container](https://laravel.com/docs/container). +- Multiple back-ends for [session](https://laravel.com/docs/session) and [cache](https://laravel.com/docs/cache) storage. +- Expressive, intuitive [database ORM](https://laravel.com/docs/eloquent). +- Database agnostic [schema migrations](https://laravel.com/docs/migrations). +- [Robust background job processing](https://laravel.com/docs/queues). +- [Real-time event broadcasting](https://laravel.com/docs/broadcasting). + +Laravel is accessible, powerful, and provides tools required for large, robust applications. + +## Learning Laravel + +Laravel has the most extensive and thorough [documentation](https://laravel.com/docs) and video tutorial library of all modern web application frameworks, making it a breeze to get started with the framework. + +You may also try the [Laravel Bootcamp](https://bootcamp.laravel.com), where you will be guided through building a modern Laravel application from scratch. + +If you don't feel like reading, [Laracasts](https://laracasts.com) can help. Laracasts contains over 2000 video tutorials on a range of topics including Laravel, modern PHP, unit testing, and JavaScript. Boost your skills by digging into our comprehensive video library. + +## Laravel Sponsors + +We would like to extend our thanks to the following sponsors for funding Laravel development. If you are interested in becoming a sponsor, please visit the [Laravel Partners program](https://partners.laravel.com). + +### Premium Partners + +- **[Vehikl](https://vehikl.com/)** +- **[Tighten Co.](https://tighten.co)** +- **[WebReinvent](https://webreinvent.com/)** +- **[Kirschbaum Development Group](https://kirschbaumdevelopment.com)** +- **[64 Robots](https://64robots.com)** +- **[Curotec](https://www.curotec.com/services/technologies/laravel/)** +- **[Cyber-Duck](https://cyber-duck.co.uk)** +- **[DevSquad](https://devsquad.com/hire-laravel-developers)** +- **[Jump24](https://jump24.co.uk)** +- **[Redberry](https://redberry.international/laravel/)** +- **[Active Logic](https://activelogic.com)** +- **[byte5](https://byte5.de)** +- **[OP.GG](https://op.gg)** + +## Contributing + +Thank you for considering contributing to the Laravel framework! The contribution guide can be found in the [Laravel documentation](https://laravel.com/docs/contributions). + +## Code of Conduct + +In order to ensure that the Laravel community is welcoming to all, please review and abide by the [Code of Conduct](https://laravel.com/docs/contributions#code-of-conduct). + +## Security Vulnerabilities + +If you discover a security vulnerability within Laravel, please send an e-mail to Taylor Otwell via [taylor@laravel.com](mailto:taylor@laravel.com). All security vulnerabilities will be promptly addressed. + +## License + +The Laravel framework is open-sourced software licensed under the [MIT license](https://opensource.org/licenses/MIT). +# crime-tracker + + Index: m.xml =================================================================== --- pom.xml (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,25 +1,0 @@ - - - 4.0.0 - - org.example - crime_tracker - 1.0-SNAPSHOT - - - 21 - 21 - UTF-8 - - - - - org.postgresql - postgresql - 42.7.1 - - - - Index: c/main/java/org/example/CrimeTracker.java =================================================================== --- src/main/java/org/example/CrimeTracker.java (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,663 +1,0 @@ -package org.example; - -import java.sql.*; -import java.util.Scanner; -import java.util.Arrays; -import java.util.InputMismatchException; - -public class CrimeTracker { - private static final String DB_URL = "jdbc:postgresql://localhost:9999/db_202425z_va_prj_crime_tracker"; - private static final String DB_USER = "db_202425z_va_prj_crime_tracker_owner"; - private static final String DB_PASSWORD = "a0cef1f880fd"; - - - private static class UserSession { - String role; - long peId; - long badgeNumber; - String fullName; - - boolean isLoggedIn() { - return role != null; - } - } - - private static UserSession currentUser = new UserSession(); - - public static void main(String[] args) { - Scanner scanner = new Scanner(System.in); - - try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) { - int loginAttempts = 0; - final int MAX_ATTEMPTS = 3; - - while (!currentUser.isLoggedIn() && loginAttempts < MAX_ATTEMPTS) { - if (authenticateUser(conn, scanner)) { - System.out.printf("Welcome, %s! (Badge #%d - %s)%n", - currentUser.fullName, currentUser.badgeNumber, - currentUser.role.toUpperCase()); - showMainMenu(conn, scanner); - currentUser = new UserSession(); - } else { - loginAttempts++; - if (loginAttempts < MAX_ATTEMPTS) { - System.out.printf("Login failed. %d attempts remaining.%n", MAX_ATTEMPTS - loginAttempts); - } - } - } - - if (!currentUser.isLoggedIn()) { - System.out.println("Too many failed login attempts. Exiting."); - } else { - System.out.println("Thank you for using Police Database System!"); - } - - } catch (SQLException e) { - System.err.println("Database connection failed: " + e.getMessage()); - e.printStackTrace(); - } finally { - scanner.close(); - } - } - - private static boolean authenticateUser(Connection conn, Scanner scanner) { - System.out.println("\nLOGIN"); - System.out.println("-".repeat(18)); - System.out.print("Enter Badge Number: "); - - long badgeNumber; - try { - badgeNumber = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid badge number. Must be a number."); - scanner.nextLine(); - return false; - } - - scanner.nextLine(); - System.out.print("Enter Password: "); - String password = scanner.nextLine(); - - String policemanQuery = """ - SELECT 'policeman' as role, pm.pe_id, p.first_name || ' ' || p.last_name as full_name - FROM policeman pm - JOIN people p ON pm.pe_id = p.pe_id - WHERE pm.badge_no = ? AND pm.p_password = ? - """; - - String officerQuery = """ - SELECT 'officer' as role, o.pe_id, p.first_name || ' ' || p.last_name as full_name - FROM officer o - JOIN people p ON o.pe_id = p.pe_id - WHERE o.o_badge_no = ? AND o.o_password = ? - """; - - try (PreparedStatement pstmt = conn.prepareStatement(policemanQuery)) { - pstmt.setLong(1, badgeNumber); - pstmt.setString(2, password); - - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - currentUser.role = rs.getString("role"); - currentUser.peId = rs.getLong("pe_id"); - currentUser.badgeNumber = badgeNumber; - currentUser.fullName = rs.getString("full_name"); - return true; - } - } - } catch (SQLException e) { - System.err.println("Policeman login error: " + e.getMessage()); - } - - try (PreparedStatement pstmt = conn.prepareStatement(officerQuery)) { - pstmt.setLong(1, badgeNumber); - pstmt.setString(2, password); - - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - currentUser.role = rs.getString("role"); - currentUser.peId = rs.getLong("pe_id"); - currentUser.badgeNumber = badgeNumber; - currentUser.fullName = rs.getString("full_name"); - return true; - } - } - } catch (SQLException e) { - System.err.println("Officer login error: " + e.getMessage()); - } - - System.out.println("Invalid badge number or password."); - return false; - } - - private static void showMainMenu(Connection conn, Scanner scanner) { - while (true) { - System.out.println("\n" + "=".repeat(50)); - System.out.printf("Logged in: %s | Badge: #%d | Role: %s%n", - currentUser.fullName, currentUser.badgeNumber, - currentUser.role.toUpperCase()); - System.out.println("=".repeat(50)); - System.out.println("1. View Case Statistics by Station"); - System.out.println("2. View Crime Type Analysis"); - System.out.println("3. View Police Performance"); - System.out.println("4. View Evidence Summary"); - System.out.println("5. Calculate Case Duration"); - System.out.println("6. Create New Crime Case"); - System.out.println("7. Transfer Case Between Stations"); - System.out.println("8. View All People"); - System.out.println("9. View All Cases"); - System.out.println("0. Logout"); - System.out.print("Choose an option: "); - - int choice; - try { - choice = scanner.nextInt(); - scanner.nextLine(); - } catch (InputMismatchException e) { - System.out.println("Invalid input. Please enter a number."); - scanner.nextLine(); - continue; - } - - if (choice == 0) { - System.out.println("Logged out successfully!"); - break; - } - - try { - switch (choice) { - case 1 -> viewCaseStatisticsByStation(conn); - case 2 -> viewCrimeTypeAnalysis(conn); - case 3 -> viewPolicePerformance(conn); - case 4 -> viewEvidenceSummary(conn); - case 5 -> calculateCaseDuration(conn, scanner); - case 6 -> createNewCrimeCase(conn, scanner); - case 7 -> transferCaseBetweenStations(conn, scanner); - case 8 -> viewAllPeople(conn); - case 9 -> viewAllCases(conn); - default -> System.out.println("Invalid choice. Try again."); - } - } catch (SQLException e) { - System.err.println("Database error: " + e.getMessage()); - } - - System.out.print("\nPress Enter to continue..."); - scanner.nextLine(); - } - } - - private static void viewCaseStatisticsByStation(Connection conn) throws SQLException { - String query = "SELECT * FROM case_statistics_by_station ORDER BY city, station_address"; - printSimpleResultSet(conn, query, "Case Statistics by Station"); - } - - private static void viewCrimeTypeAnalysis(Connection conn) throws SQLException { - String query = "SELECT * FROM crime_type_analysis ORDER BY total_cases DESC"; - printSimpleResultSet(conn, query, "Crime Type Analysis"); - } - - private static void viewPolicePerformance(Connection conn) throws SQLException { - String query = "SELECT * FROM police_performance ORDER BY role_type, cases_managed DESC NULLS LAST"; - printSimpleResultSet(conn, query, "Police Performance"); - } - - private static void viewEvidenceSummary(Connection conn) throws SQLException { - String query = "SELECT * FROM evidence_summary ORDER BY total_evidence DESC"; - printSimpleResultSet(conn, query, "Evidence Summary"); - } - - private static void viewAllPeople(Connection conn) throws SQLException { - String query = """ - SELECT pe_id, last_name, first_name - FROM people - ORDER BY last_name, first_name - """; - printSimpleResultSet(conn, query, "All People"); - } - - private static void viewAllCases(Connection conn) throws SQLException { - String query = """ - SELECT cc.c_id, cc.c_name, - TO_CHAR(cc.opening_date, 'YYYY-MM-DD') as opening_date, - cc.c_status, ps.p_address as station, sia.city - FROM crime_case cc - JOIN police_station ps ON cc.p_id = ps.p_id - JOIN sector_of_interal_affairs sia ON ps.s_id = sia.s_id - ORDER BY cc.opening_date DESC - """; - printSimpleResultSet(conn, query, "All Crime Cases"); - } - - private static void printSimpleResultSet(Connection conn, String query, String title) throws SQLException { - try (Statement stmt = conn.createStatement(); - ResultSet rs = stmt.executeQuery(query)) { - - System.out.println("\n" + title.toUpperCase()); - System.out.println("-".repeat(Math.max(title.length() + 20, 80))); - - ResultSetMetaData metaData = rs.getMetaData(); - int columnCount = metaData.getColumnCount(); - - for (int i = 1; i <= columnCount; i++) { - String columnName = metaData.getColumnName(i); - System.out.printf("%-20s", truncate(columnName, 19)); - } - System.out.println(); - - for (int i = 0; i < columnCount; i++) { - System.out.print("-".repeat(20)); - } - System.out.println(); - - int rowCount = 0; - boolean hasData = false; - - while (rs.next()) { - hasData = true; - for (int i = 1; i <= columnCount; i++) { - String value = rs.getString(i); - System.out.printf("%-20s", truncate(value, 19)); - } - System.out.println(); - rowCount++; - } - - if (!hasData) { - System.out.println("ℹNo data found."); - } else { - System.out.printf("%nTotal records: %d%n", rowCount); - } - } catch (SQLException e) { - System.err.println("Error executing query: " + e.getMessage()); - if (e.getMessage().contains("scrollable")) { - System.out.println("This is a display issue - data exists but can't be formatted properly."); - } - } - } - - private static void calculateCaseDuration(Connection conn, Scanner scanner) throws SQLException { - System.out.print("Enter Case ID: "); - long caseId; - try { - caseId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid case ID. Please enter a number."); - scanner.nextLine(); - return; - } - scanner.nextLine(); - - String query = "SELECT calculate_case_duration(?) AS duration"; - try (PreparedStatement pstmt = conn.prepareStatement(query)) { - pstmt.setLong(1, caseId); - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - int duration = rs.getInt("duration"); - System.out.printf("Case %d Duration: %d days %n", caseId, duration); - } else { - System.out.printf("No duration found for case ID: %d%n", caseId); - } - } - } - } - - private static void createNewCrimeCase(Connection conn, Scanner scanner) throws SQLException { - System.out.println("\nCREATE NEW CRIME CASE"); - System.out.println("-".repeat(25)); - - System.out.print("Enter Case Name: "); - String caseName = scanner.nextLine(); - - System.out.print("Enter Police Station ID: "); - long stationId; - try { - stationId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid station ID."); - scanner.nextLine(); - return; - } - - long policemanId = currentUser.peId; - System.out.printf("Using your ID: %d (Badge #%d)%n", - policemanId, currentUser.badgeNumber); - - System.out.print("Enter Victim ID: "); - long victimId; - try { - victimId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid victim ID."); - scanner.nextLine(); - return; - } - - System.out.print("Enter Witness ID: "); - long witnessId; - try { - witnessId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid witness ID."); - scanner.nextLine(); - return; - } - - scanner.nextLine(); - System.out.print("Enter Statement Description: "); - String description = scanner.nextLine(); - - System.out.print("Enter Incident Timestamp (YYYY-MM-DD HH:MM:SS): "); - String timestampStr = scanner.nextLine(); - Timestamp timestamp; - try { - timestamp = Timestamp.valueOf(timestampStr); - } catch (IllegalArgumentException e) { - System.out.println("Invalid timestamp format. Using current time."); - timestamp = new Timestamp(System.currentTimeMillis()); - } - - System.out.print("Enter Incident Place: "); - String place = scanner.nextLine(); - - System.out.print("Enter Crime Type IDs (comma-separated, e.g., 1,2,3): "); - String typesInput = scanner.nextLine(); - long[] crimeTypes; - try { - crimeTypes = Arrays.stream(typesInput.split(",")) - .map(String::trim) - .mapToLong(Long::parseLong) - .toArray(); - } catch (NumberFormatException e) { - System.out.println("Invalid crime type IDs. Using default [1]."); - crimeTypes = new long[]{1}; - } - - if (crimeTypes.length == 0) { - System.out.println("No crime types specified. Aborting."); - return; - } - - System.out.println("\nCASE SUMMARY"); - System.out.println("-".repeat(25)); - System.out.printf("Case Name: %s%n", caseName); - System.out.printf("Station ID: %d%n", stationId); - System.out.printf("Policeman: %s ID: %d%n", - currentUser.fullName, policemanId); - System.out.printf("Victim ID: %d%n", victimId); - System.out.printf("Witness ID: %d%n", witnessId); - System.out.printf("Description: %s%n", truncate(description, 50)); - System.out.printf("Timestamp: %s%n", timestamp); - System.out.printf("Location: %s%n", place); - System.out.printf("Crime Types: %s%n", Arrays.toString(crimeTypes)); - - System.out.print("\nCreate this case? (y/n): "); - String confirm = scanner.nextLine().trim().toLowerCase(); - - if (!confirm.equals("y") && !confirm.equals("yes")) { - System.out.println("Case creation cancelled."); - return; - } - - String call = "CALL create_crime_case(?, ?, ?, ?, ?, ?, ?, ?, ?)"; - try (CallableStatement cstmt = conn.prepareCall(call)) { - cstmt.setString(1, caseName); - cstmt.setLong(2, stationId); - cstmt.setLong(3, policemanId); - cstmt.setLong(4, victimId); - cstmt.setLong(5, witnessId); - cstmt.setString(6, description); - cstmt.setTimestamp(7, timestamp); - cstmt.setString(8, place); - Integer[] crimeTypeIntegers = new Integer[crimeTypes.length]; - for (int i = 0; i < crimeTypes.length; i++) { - crimeTypeIntegers[i] = (int) crimeTypes[i]; - } - Array array = conn.createArrayOf("bigint", crimeTypeIntegers); - cstmt.setArray(9, array); - - cstmt.execute(); - System.out.println("\nNew crime case created successfully!"); - System.out.printf("Case assigned to you (Badge #%d)%n", currentUser.badgeNumber); - - } catch (SQLException e) { - System.err.println("Error creating crime case: " + e.getMessage()); - if (e.getMessage().contains("does not exist")) { - System.out.println("Hint: Check that station, victim, and witness IDs exist."); - } - } - } - - private static void transferCaseBetweenStations(Connection conn, Scanner scanner) throws SQLException { - System.out.println("\nTRANSFER CASE BETWEEN STATIONS"); - System.out.println("-".repeat(35)); - - System.out.print("Enter Case ID: "); - long caseId; - try { - caseId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid case ID. Please enter a number."); - scanner.nextLine(); - return; - } - - System.out.print("Enter New Station ID: "); - long newStationId; - try { - newStationId = scanner.nextLong(); - } catch (InputMismatchException e) { - System.out.println("Invalid station ID. Please enter a number."); - scanner.nextLine(); - return; - } - scanner.nextLine(); - - if (!validateCaseExists(conn, caseId)) { - System.out.printf("Case ID %d does not exist.%n", caseId); - return; - } - - if (!validateStationExists(conn, newStationId)) { - System.out.printf("Station ID %d does not exist.%n", newStationId); - return; - } - - if (!hasValidVictimsAndWitnesses(conn)) { - System.out.println("Error: No valid victims or witnesses found in the system."); - System.out.println("Hint: You need to have at least one person marked as victim and one as witness."); - return; - } - - conn.setAutoCommit(false); - try { - String getOldStationSql = "SELECT p_id FROM crime_case WHERE c_id = ?"; - long oldStationId; - try (PreparedStatement pstmt = conn.prepareStatement(getOldStationSql)) { - pstmt.setLong(1, caseId); - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - oldStationId = rs.getLong(1); - } else { - throw new SQLException("Case not found during transfer"); - } - } - } - - String updateCaseSql = "UPDATE crime_case SET p_id = ? WHERE c_id = ?"; - try (PreparedStatement pstmt = conn.prepareStatement(updateCaseSql)) { - pstmt.setLong(1, newStationId); - pstmt.setLong(2, caseId); - int rowsUpdated = pstmt.executeUpdate(); - if (rowsUpdated == 0) { - throw new SQLException("Failed to update case"); - } - } - - String getPolicemanSql = "SELECT pe_id FROM policeman WHERE p_id = ? LIMIT 1"; - long policemanId; - try (PreparedStatement pstmt = conn.prepareStatement(getPolicemanSql)) { - pstmt.setLong(1, newStationId); - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - policemanId = rs.getLong(1); - } else { - String fallbackSql = "SELECT pe_id FROM policeman LIMIT 1"; - try (Statement stmt = conn.createStatement(); - ResultSet fallbackRs = stmt.executeQuery(fallbackSql)) { - if (fallbackRs.next()) { - policemanId = fallbackRs.getLong(1); - } else { - throw new SQLException("No policemen found in the system"); - } - } - } - } - } - - long victimId = getFirstVictimId(conn); - long witnessId = getFirstWitnessId(conn); - - String insertStatementSql = """ - INSERT INTO statements (s_id, statement_date, description, incident_timestamp, - incident_place, c_id, pe_id, victim_pe_id, witness_pe_id) - VALUES ( - (SELECT COALESCE(MAX(s_id), 0) + 1 FROM statements), - CURRENT_DATE, - 'Case transferred from station ' || ? || ' to station ' || ?, - CURRENT_TIMESTAMP, - 'Case transfer between stations', - ?, ?, ?, ? - ) - """; - - try (PreparedStatement pstmt = conn.prepareStatement(insertStatementSql)) { - pstmt.setLong(1, oldStationId); - pstmt.setLong(2, newStationId); - pstmt.setLong(3, caseId); - pstmt.setLong(4, policemanId); - pstmt.setLong(5, victimId); - pstmt.setLong(6, witnessId); - - int rowsInserted = pstmt.executeUpdate(); - if (rowsInserted == 0) { - throw new SQLException("Failed to create transfer statement"); - } - } - - conn.commit(); - System.out.printf("Case %d successfully transferred from station %d to station %d!%n", - caseId, oldStationId, newStationId); - - showTransferDetails(conn, caseId); - - } catch (SQLException e) { - conn.rollback(); - System.err.println("Error transferring case: " + e.getMessage()); - } finally { - conn.setAutoCommit(true); - } - } - - private static void showTransferDetails(Connection conn, long caseId) throws SQLException { - String query = """ - SELECT - cc.c_name as case_name, - TO_CHAR(cc.opening_date, 'YYYY-MM-DD') as opened_date, - ps.p_address as current_station, - sia.city as city, - TO_CHAR(s.statement_date, 'YYYY-MM-DD HH24:MI') as transfer_time, - LEFT(s.description, 60) as transfer_note, - p.first_name || ' ' || p.last_name as policeman_name - FROM crime_case cc - JOIN police_station ps ON cc.p_id = ps.p_id - JOIN sector_of_interal_affairs sia ON ps.s_id = sia.s_id - JOIN statements s ON cc.c_id = s.c_id - JOIN policeman pm ON s.pe_id = pm.pe_id - JOIN people p ON pm.pe_id = p.pe_id - WHERE cc.c_id = ? AND s.description LIKE '%transferred%' - ORDER BY s.statement_date DESC - LIMIT 1 - """; - - try (PreparedStatement pstmt = conn.prepareStatement(query)) { - pstmt.setLong(1, caseId); - try (ResultSet rs = pstmt.executeQuery()) { - if (rs.next()) { - System.out.println("\nTransfer Confirmation:"); - System.out.println("-".repeat(25)); - System.out.printf("Case: %s%n", rs.getString("case_name")); - System.out.printf("Opened: %s%n", rs.getString("opened_date")); - System.out.printf("Current Station: %s, %s%n", rs.getString("current_station"), rs.getString("city")); - System.out.printf("Transfer Time: %s%n", rs.getString("transfer_time")); - System.out.printf("Handled by: %s%n", rs.getString("policeman_name")); - System.out.printf("Note: %s%n", rs.getString("transfer_note")); - } else { - System.out.println("ℹTransfer details not available yet."); - } - } - } - } - - private static boolean validateCaseExists(Connection conn, long caseId) throws SQLException { - String query = "SELECT c_id FROM crime_case WHERE c_id = ?"; - try (PreparedStatement pstmt = conn.prepareStatement(query)) { - pstmt.setLong(1, caseId); - try (ResultSet rs = pstmt.executeQuery()) { - boolean exists = rs.next(); - if (exists) { - System.out.printf("Case ID %d found%n", caseId); - } - return exists; - } - } - } - - private static boolean validateStationExists(Connection conn, long stationId) throws SQLException { - String query = "SELECT p_id FROM police_station WHERE p_id = ?"; - try (PreparedStatement pstmt = conn.prepareStatement(query)) { - pstmt.setLong(1, stationId); - try (ResultSet rs = pstmt.executeQuery()) { - boolean exists = rs.next(); - if (exists) { - System.out.printf("Station ID %d found%n", stationId); - } - return exists; - } - } - } - - private static boolean hasValidVictimsAndWitnesses(Connection conn) throws SQLException { - return getFirstVictimId(conn) != -1 && getFirstWitnessId(conn) != -1; - } - - private static long getFirstVictimId(Connection conn) throws SQLException { - String query = "SELECT pe_id FROM victim LIMIT 1"; - try (Statement stmt = conn.createStatement(); - ResultSet rs = stmt.executeQuery(query)) { - if (rs.next()) { - long victimId = rs.getLong(1); - System.out.printf("Using victim ID %d%n", victimId); - return victimId; - } - return -1; - } - } - - private static long getFirstWitnessId(Connection conn) throws SQLException { - String query = "SELECT pe_id FROM witness LIMIT 1"; - try (Statement stmt = conn.createStatement(); - ResultSet rs = stmt.executeQuery(query)) { - if (rs.next()) { - long witnessId = rs.getLong(1); - System.out.printf("Using witness ID %d%n", witnessId); - return witnessId; - } - return -1; - } - } - - private static String truncate(String str, int maxLength) { - if (str == null) return "NULL"; - return str.length() > maxLength ? str.substring(0, maxLength) + "..." : str; - } -} Index: c/main/java/org/example/examples =================================================================== --- src/main/java/org/example/examples (revision cc0c50b7d4a0ef4c18130b022a48481e67bd4859) +++ (revision ) @@ -1,23 +1,0 @@ -LOGIN -officer: -Enter Badge Number: 222223 -Enter Password o1_123 - -Policeman: -Enter Badge Number: 122323 -Enter Password p1_123 - -CREATE NEW CRIME CASE -Enter Case Name: Bank Robbery -Enter Police Station ID: 1 -Using your ID: 13 (Badge #222223) -Enter Victim ID: 6 -Enter Witness ID: 7 -Enter Statement Description: Description for Bank Robbery -Enter Incident Timestamp (YYYY-MM-DD HH:MM:SS): 2025-08-01 14:50:20 -Enter Incident Place: Central Bank -Enter Crime Type IDs (comma-separated, e.g., 1,2,3): 1 - -TRANSFER CASE BETWEEN STATIONS: -Enter Case ID: 7 -Enter New Station ID: 4