package gov.va.med.ars.client;

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

import org.apache.commons.io.FilenameUtils;

/**
* This class is used for writing files to DB and retrieving them from DB
* @version 1.0
*/


public class FileDBTransfer {

/**
 * This method inserts a Zip file into the database
 * @param fdb An instantiation of this class
 * @param cn Connection to database
 * @param CompID int, CompanyID
 * @param WeekNum int, week number for the report
 * @param FileName String, the file to be inserted to database
 * @exception IOException, FileNotFoundException, SQLException
*/	

void insertFile(FileDBTransfer fdb, Connection cn, String fileName)throws IOException, FileNotFoundException, SQLException
{
//variables
int FileLength;
FileInputStream fis;
String Query;
PreparedStatement pstmt;
	
	//prepare FileInputStream
	fis = fdb.getFileInputStream(fileName);
	FileLength= fdb.getFileLength(fileName);
	
	String type = FilenameUtils.getExtension(fileName);
	
	if (type==null)
	{
		type = "PDF";
	}
	
	//Build the insert statement
	//Create a parameterized query
	Query = ("insert into Claim_Attachments.dbo.ClaimAttachments( PROVIDER_NAME" +
      ",PROVIDER_IDENTIFIER_NUMBER" +
      ",PATIENT_FIRST_NAME" +
      ",PATIENT_LAST_NAME" +
      ",REPORT_CODE" +
      ",PATIENT_CONTROL_NUMBER" +
      ",MEDICAL_RECORD_NUMBER" +
      ",PATIENT_IDENTIFIER_NUMBER" +
      ",PROVIDER_NPI" +
      ",CLAIM_IDENTIFIER" +
      ",CLAIM_SERVICE_DATE" +
      ",PAYER_CLAIM_CONTROL_NUMBER" +
      ",ATTACHMENT_CONTROL_NUMBER" +
      ",LOINC_CODE" +
      ",STATUS" +
      ",ATTACHMENT_NAME" +
      ",ATTACHMENT_TYPE" +
      ",ATTACHMENT" +
      ",Ver) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
	pstmt = cn.prepareStatement(Query);
	pstmt.setString(1, "Provider1");
	pstmt.setString(2, "ABCD");
	pstmt.setString(3, "Vinod");
	pstmt.setString(4, "Panicker");
	pstmt.setString(5, "03");
	pstmt.setString(6, "7777");
	pstmt.setString(7, "1234");
	pstmt.setString(8, "5678");
	pstmt.setString(9, "ABCDEF");
	pstmt.setString(10, "CLAIM123");
	pstmt.setString(11, "01/02/2015");
	pstmt.setString(12, "11223344");
	pstmt.setString(13, "55667788");
	pstmt.setString(14, "ABCD");
	pstmt.setString(15, "A");
	pstmt.setString(16, fileName);
	pstmt.setString(17, type);
	pstmt.setBinaryStream(18, fis, FileLength); //method to insert a stream of bytes
	pstmt.setInt(19, 1);

	//execute the insert statement
	pstmt.executeUpdate();

}// end insertPdfFile

/**This method returns a resultset
 * @param Connection conn
 * @param String Query
 * @return ResultSet rs
*/

ResultSet getRS(Connection conn, String Query)throws SQLException
{
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(Query);
	return rs;

}//end getRS

/**This method returns a connection
 * @param String Driver
 * @param String URL
 * @param String pwd
 * @return Connection conn
 * @exception ClassNotFoundException
 * @exception SQLException
*/

Connection getConnection(String driver, String url, String login, String pwd)throws ClassNotFoundException, SQLException
{
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, login, pwd);
	return conn;
}//getConnection
/**This method returns a FileInputStream
 * @param String FileName
 * @return FileInputStream fis
 * @exception FileNotFoundException
*/

FileInputStream getFileInputStream(String FileName)throws FileNotFoundException
{
	File file = new File(FileName);
	FileInputStream fis = new FileInputStream(file);
	return fis;
}//getFileInputStream

/** This method gets file from database
 * @param String FileName
 * @param ResultSet rs
 * @exception OException
 * @exception FileNotFoundException
 * @exception SQLException
*/

void getBinaryFile(String FileName,ResultSet rs) throws IOException, FileNotFoundException, SQLException
{
	//Array to hold array of file bytes
	byte[] fileBytes; //an array of bytes

	if (rs.next()) //if there is a record
	{
		fileBytes = rs.getBytes(1); //create a byte array from contents of field
		OutputStream targetFile=  new FileOutputStream(FileName); // define the output stream
        
		targetFile.write(fileBytes); //write the array of bytes to file
		targetFile.close(); //close the File stream
	}//end if

}//getBinaryFile

/** This method gets length of a file to use in the setBinaryStream method
 * @param String FileName
 * @return int, the length of the file passed in
*/
int getFileLength (String FileName)
{
	File file = new File(FileName);
	return (int)file.length();
}//getFileLength

/** This method gets a text file from a text field in a database table
 * @param String FileName
 * @param ResultSet rs
 * @param String FieldName
 * @Exception IOException, FileNotFoundException, SQLException
*/


void getTextFile(String FileName,ResultSet rs, String FieldName)throws IOException, FileNotFoundException, SQLException
{

	if (rs.next())
	{	
		byte buf[] = rs.getBytes(FieldName);
		OutputStream targetFile=  new FileOutputStream(FileName);
	  	targetFile.write(buf);
		targetFile.close();
	}


}//getTextFile

}//end class
