/*
   Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
   This file is licensed under the Apache License, Version 2.0 (the "License").
   You may not use this file except in compliance with the License. A copy of
   the License is located at
    http://aws.amazon.com/apache2.0/
   This file is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
   CONDITIONS OF ANY KIND, either express or implied. See the License for the
   specific language governing permissions and limitations under the License.
*/

package com.aws.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.UUID;

import com.aws.entities.WorkItem;
import org.springframework.stereotype.Component;

@Component
public class InjectWorkService {

    //Inject a new submission
    public String modifySubmission(String id, String desc, String status)
    {
        Connection c = null;
        int rowCount= 0;
        try {
            // Create a Connection object
            c =  ConnectionHelper.getConnection();

            //Use prepared statements to protected against SQL injection attacks
            //  PreparedStatement pstmt = null;
            PreparedStatement ps = null;


            String query = "update work set description = ?, status = ? where idwork = '" +id +"'";

            ps = c.prepareStatement(query);
            ps.setString(1, desc);
            ps.setString(2, status);
            ps.execute();
            return id;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            ConnectionHelper.close(c);
        }
        return null;
    }

    //Inject a new submission
    public String injestNewSubmission(WorkItem item)
    {
        Connection c = null;
        int rowCount= 0;
        try {

            // Create a Connection object
            c =  ConnectionHelper.getConnection();

            // Use a prepared statement
            PreparedStatement ps = null;

            //Convert rev to int
            String name = item.getName();
            String guide = item.getGuide();
            String description = item.getDescription();
            String status = item.getStatus();

            //generate the work item ID
            UUID uuid = UUID.randomUUID();
            String workId = uuid.toString();

            //Date conversion
            // Date date1 = new SimpleDateFormat("yyyy/mm/dd").parse(date);
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");
            LocalDateTime now = LocalDateTime.now();
            String sDate1 =   dtf.format(now);
            Date date1 = new SimpleDateFormat("yyyy/MM/dd").parse(sDate1);
            java.sql.Date sqlDate = new java.sql.Date( date1.getTime());

            //Inject a new Formstr template into the system
            String insert = "INSERT INTO work (idwork, username,date,description, guide, status, archive) VALUES(?,?, ?,?,?,?,?);";
            ps = c.prepareStatement(insert);
            ps.setString(1, workId);
            ps.setString(2, name);
            ps.setDate(3, sqlDate);
            ps.setString(4, description);
            ps.setString(5, guide );
            ps.setString(6, status );
            ps.setBoolean(7, false);
            ps.execute();
            return workId;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            ConnectionHelper.close(c);
        }
        return null;
    }
}

@@ -607,16 +607,79 @@
 tement;%0A
+import java.sql.SQLException;%0Aimport java.text.ParseException;%0A
 import j
@@ -954,32 +954,33 @@
 ervice %7B%0A%0A    //
+ 
 Inject a new sub
@@ -1060,20 +1060,16 @@
  status)
-%0A   
  %7B%0A     
@@ -1180,33 +1180,32 @@
 %0A            c =
- 
  ConnectionHelpe
@@ -1238,16 +1238,17 @@
       //
+ 
 Use prep
@@ -1266,99 +1266,8 @@
 ents
- to protected against SQL injection attacks%0A            //  PreparedStatement pstmt = null;
 %0A   
@@ -1305,17 +1305,16 @@
  null;%0A%0A
-%0A
         
@@ -1580,32 +1580,24 @@
        %7D
-%0A       
  catch (
 Exceptio
@@ -1580,32 +1580,35 @@
        %7D catch (
+SQL
 Exception e) %7B%0A 
@@ -1644,32 +1644,24 @@
 );%0A        %7D
-%0A       
  finally %7B%0A 
@@ -1738,24 +1738,25 @@
    %7D%0A%0A    //
+ 
 Inject a new
@@ -1819,20 +1819,16 @@
 em item)
-%0A   
  %7B%0A     
@@ -1948,17 +1948,16 @@
      c =
- 
  Connect
@@ -2080,16 +2080,17 @@
       //
+ 
 Convert 
@@ -2303,16 +2303,17 @@
       //
+ 
 generate
@@ -2433,16 +2433,17 @@
       //
+ 
 Date con
@@ -2454,84 +2454,8 @@
 ion%0A
-            // Date date1 = new SimpleDateFormat(%22yyyy/mm/dd%22).parse(date);%0A
     
@@ -2697,18 +2697,16 @@
 sDate1 =
-  
  dtf.for
@@ -2878,24 +2878,25 @@
       //
+ 
 Inject a
  new For
@@ -2891,29 +2891,14 @@
 ct a
- new Formstr template
+n item
  int
@@ -3402,16 +3402,17 @@
 workId;%0A
+%0A
         
@@ -3416,24 +3416,16 @@
    %7D
-%0A       
  catch (
 Exce
@@ -3420,16 +3420,36 @@
  catch (
+SQLException %7C Parse
 Exceptio
@@ -3497,24 +3497,16 @@
        %7D
-%0A       
  finally

Update InjectWorkService.java