I am very much new to PHP in fact this is my first project. All I want is to change the SQL server address from below code, we have created new database and we need to change the address to pointing new database to load below insert data in to new database, its a replication of previous database there are no changes at all. (e.g. tables, SPROC's etc)
Kindly someone help me to identify the SQL address in below code, so I can change the in my production.
Thanks,
D
Code: Select all
<?php
/**
* Project:
* Author:
* File: upload_process.php
* Purpose: Simple FORM POST upload processing script - Upload code: Processes the upload, creates the users folder,
* moves php upload data to users folder.
*
* Inputs: upload.php
* Outputs:
*
* ChangeLog:
*
*/
session_start();
include("include/config.php");
// define variables from session
$username = $_SESSION[username];
$db = $_SESSION[db];
$user_type = $_SESSION[user_type];
// user must be authenticated to see this page
requireLogin( $username );
// show the web page header
HTMLheader();
//print_r($_POST);
if (empty($_POST))
{
echo "<center>Error: No form data was submitted - contact your systems administrator";
HTMLfooter();
$uploadstatus = "ERR EMPTY";
die();
}
$targetns = "d:/budget/uploads/$username"; // full path without trailing slash.. could be done differently, maybe later :)
$target = 'd:' . '/budget/uploads/' . $username . "/"; // full path with trailing slash
echo "<a href=index.php>Budget Interface - Home</a> > <a href=upload.php><b>Upload</b></a><br><br>";
echo "<center>
<h2>Upload Excel Data</h2><br>";
// obtain values from upload form
$bu_code = $_POST['bu_code'];
$upload_type = $_POST['upload_type']; // Can be 'bundle' or 'top_entries'
if ($upload_type == "bundle" && !$bu_code)
{
echo "Sorry, you did not select a BU Code... <A HREF=\"javascript:history.go(-1)\">Go back</a>";
HTMLfooter();
die();
}
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "";
}
// create the folder if it doesn't exist
if (!is_dir($targetns))
{
mkdir($targetns);
}
if (!$_FILES['file']['name'])
{
echo "<br>No file uploaded... perhaps you didn't select a file or it had issues uploading... <br><br><a href=upload.php>Back</a>";
HTMLfooter();
die();
}
if (function_exists("date_default_timezone_set") and function_exists("date_default_timezone_get"))
@date_default_timezone_set(@date_default_timezone_get());
$datetime = date("ymd-Hi");
$newfileloc = $target . $datetime . "_" . $_FILES["file"]["name"];
$filename = $datetime . "_" . $_FILES["file"]["name"];
$failfileloc = $targetns . "/failed";
if (file_exists($newfileloc))
{
echo "<b>Warning!</b> file $newfileloc already exists. <br><br><a href=upload.php>Back</a>";
$uploadstatus = "ERR EXISTS";
$process=0;
}
else
{
// move the uploaded file from temp to real location
move_uploaded_file($_FILES["file"]["tmp_name"], $newfileloc); //$target . $_FILES["file"]["name"]);
$uploadstatus = "OK";
$process=1;
}
echo "</center>";
/* ---------------------------------------------------------------------------------------------------
Process the actual upload at this point
---------------------------------------------------------------------------------------------------- */
if ($process == 1)
{
if ($upload_type == bundle)
{
// check for last version for business unit
$sql = "SELECT MAX(VERSION)+1 AS VERSION FROM UPLOAD WHERE BU_CODE = '$bu_code'";
$res = sqlsrv_query($conn, $sql);
if (sqlsrv_fetch($res) === false)
{
echo "Query failed: Could not retrieve VERSION from the database<br>";
echo "Debug: $sql<br>";
die(print_r(sqlsrv_errors(), true));
}
$new_version = sqlsrv_get_field($res, 0);
//die("new_version: $new_version\n");
// DR090902: Added this to prevent the upload_id being 0 if the above record is not found
// if no version, make it start from 1
if (!$new_version)
{
$new_version=1;
}
sqlsrv_free_stmt($res);
}
// Set remote_ip
$remote_ip = $_SERVER['REMOTE_ADDR'];
// Set browser type
$browser_type=null;
//$browser_type = $_SERVER['HTTP_USER_AGENT'];
// Set upload date (or use GETDATE() in sql statement)
if (function_exists("date_default_timezone_set") and function_exists("date_default_timezone_get"))
@date_default_timezone_set(@date_default_timezone_get());
$upload_date = date("d-M-y H:i"); // date format dd-MMM-yy hh:mm
/* Begin transaction. */
if (sqlsrv_begin_transaction($conn) === false)
{
echo "Could not begin transaction<br>";
die(print_r(sqlsrv_errors(), true));
}
// Insert record into the UPLOAD table
switch ($upload_type)
{
case "bundle":
// Simon: Using GETDATE() instead of $upload_date
//$sql = "INSERT INTO UPLOAD (BU_CODE, VERSION, REMOTE_IP, BROWSER_TYPE, FILE_NAME, UPLOADED_BY, UPLOAD_DATE, UPLOAD_ERROR, STATUS_CODE)
// VALUES ('$bu_code','$new_version','$remote_ip','$browser_type','$filename','$username','$upload_date','$uploadstatus','U')";
$sql = "INSERT INTO UPLOAD (BU_CODE, VERSION, REMOTE_IP, BROWSER_TYPE, FILE_NAME, UPLOADED_BY, UPLOAD_DATE, UPLOAD_ERROR, STATUS_CODE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
SELECT SCOPE_IDENTITY() AS UPLOAD_ID";
$val = array ($bu_code, $new_version, $remote_ip, $browser_type, $filename, $username, $upload_date, $uploadstatus, 'U');
break;
case "top_entries":
$sql = "INSERT INTO TOP_UPLOAD (REMOTE_IP, BROWSER_TYPE, FILE_NAME, UPLOADED_BY, UPLOAD_DATE, UPLOAD_ERROR)
VALUES (?, ?, ?, ?, ?, ?);
SELECT SCOPE_IDENTITY() AS UPLOAD_ID";
$val = array ($remote_ip, $browser_type, $filename, $username, $upload_date, $uploadstatus);
break;
default:
$errmsg = "Unknown upload type, aborting upload";
Rollback($conn, "No sql statement", $errmsg);
}
$res = sqlsrv_query($conn, $sql, $val);
if ($res === false)
{
$errmsg = "Query failed: Could not insert UPLOAD record into database";
Rollback($conn, $sql, $errmsg);
}
// Retrieve upload_id from upload table
if (sqlsrv_next_result($res))
{
while ($row = sqlsrv_fetch_object($res))
{
$upload_id = $row->UPLOAD_ID;
}
}
else
{
$errmsg = "Error retrieving UPLOAD_ID, aborting upload";
Rollback($conn, "Retrieving UPLOAD_ID", $errmsg);
}
sqlsrv_free_stmt ($res);
/* ---------------------------------------------------------------------------------------------------
This is where we process the XLS to SQL data
---------------------------------------------------------------------------------------------------- */
// remove the time limit
set_time_limit(0);
$imports_allowed = 1;
// if ($username == "z-drea" || $username == "z-salexander" || $username == "salexander" || $username == "drea") // for debugging purposes
if ($imports_allowed)
{
// get filename extension and convert to lowercase
///$file_ext = explode(".", $newfileloc);
///$file_ext = strtolower($file_ext[1]);
$file_ext = strtolower(substr(strrchr($newfileloc,"."),1));
if ($file_ext == "xls")
{
// vars can be used --->
// $bu_code
// $upload_id
// insert XLS into SQL table
sqlsrv_configure("WarningsReturnAsErrors", 1); // turn sqlsrv debug mode on
// Store number of rows inserted for each table
$rows_inserted = array();
switch ($upload_type)
{
case "bundle":
/* -------- INSERT AD50 records from DataUpload tab -------- */
$tab = "AD50";
$sql = "INSERT INTO AD50
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, FOR_FY, FOR_YTD, FOR_YTG, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR,
BUD_MAY, BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC, PLAN1, PLAN2
FROM [DataUpload$] WHERE REC_TYPE = \"AD50\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT CAPEX records from DataUpload tab -------- */
$tab = "CAPEX";
$sql = "INSERT INTO $tab
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, CAPEX_TYPE, FOR_FY, FOR_YTD, FOR_YTG, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR,
BUD_APR, BUD_MAY, BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC
FROM [DataUpload$] WHERE REC_TYPE = \"CAPEX\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT CAT_SALES records from DataUpload tab -------- */
$tab = "CAT_SALES";
$sql = "INSERT INTO $tab
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, TYPE, CLIENT, SERVICE, LOCATION_MINE, MINE_LIFE_CYCLE, CONTRACT_VALUE,
FOR_FY, FOR_YTD, FOR_YTG, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR, BUD_MAY,
BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC, IMPACT_POST_BUD
FROM [DataUpload$] WHERE REC_TYPE = \"CAT_SALES\" AND ACCOUNT <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT COS records from DataUpload tab -------- */
$tab = "COS";
$sql = "INSERT INTO COS
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT EMP_NAME, POS_CODE, GO_TO_MKT, SALES_OBJ, DED_SALE, BUD_FY
FROM [DataUpload$] WHERE REC_TYPE = \"COS\" AND EMP_NAME <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT INTERCOMPANY records from DataUpload tab -------- */
$tab = "INTERCOMPANY";
$sql = "INSERT INTO $tab
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, CLIENT, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR,
BUD_MAY, BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC
FROM [DataUpload$] WHERE REC_TYPE = \"INTERCOMPANY\" AND ACCOUNT <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT IT_DETAIL records from DataUpload tab -------- */
$tab = "IT_DETAIL";
$sql = "INSERT INTO IT_DETAIL
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, REF, SERVICE, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR,
BUD_MAY, BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC
FROM [DataUpload$] WHERE REC_TYPE = \"IT_DETAIL\" AND ACCOUNT <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT PERS_CHARGES records from DataUpload tab -------- */
$tab = "PERS_CHARGES";
$sql = "INSERT INTO $tab
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, CATEGORY, TYPE, [POSITION], FOR_FY, FOR_YTD, FOR_YTG,
BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR, BUD_MAY, BUD_JUN,
BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC
FROM [DataUpload$] WHERE REC_TYPE = \"PERS_CHARGES\" AND ACCOUNT <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
/* -------- INSERT SALES_TARGET records from DataUpload tab -------- */
$tab = "SALES_TARGET";
$sql = "INSERT INTO $tab
SELECT $upload_id AS UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT ACCOUNT, TYPE, BUD_FY, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR, BUD_MAY,
BUD_JUN, BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC
FROM [DataUpload$] WHERE REC_TYPE = \"SALES_TARGET\" AND ACCOUNT <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
break;
case "top_entries":
/* -------- INSERT TOP_ENTRIES records from DataUpload tab -------- */
$tab = "TOP_ENTRIES";
$sql = "INSERT INTO TOP_ENTRIES
SELECT $upload_id AS TOP_UPLOAD_ID, * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=$newfileloc;',
'SELECT BU_CODE, ACCOUNT, DATA_TYPE, DETAIL1, DETAIL2, HIS_TOT, FOR_FY, FOR_YTD, FOR_YTG,
BUD_FY, BUD_TOT, BUD_JAN, BUD_FEB, BUD_MAR, BUD_APR, BUD_MAY, BUD_JUN,
BUD_JUL, BUD_AUG, BUD_SEP, BUD_OCT, BUD_NOV, BUD_DEC, PLAN1, PLAN2
FROM [DataUpload$] WHERE DATA_TYPE <> \"\"')";
$rows_inserted[$tab] = InsertRecords($conn, $tab, $sql);
break;
default:
$errmsg = "Unknown upload type, aborting upload";
Rollback($conn, "No sql statement", $errmsg);
}
/* -------- Commit transaction when all uploads have succeeded -------- */
//If commit fails, roll back the transaction.
if (sqlsrv_commit($conn) === false)
{
$errmsg = "Commit failed - rolling back...<br>";
Rollback($conn, "sqlsrv_commit", $errmsg);
}
}
else
{
echo "Sorry, your file is not a valid XLS file.";
HTMLfooter();
exit;
}
//unlink($newfileloc); // remove the file
}
else
{
echo "Sorry, database imports have currently been disabled by the System Administrator<br>";
}
}
/* // Routine for failed imports
if ($process == 0)
{
// create failed folder
if (!is_dir($failfileloc))
{
mkdir($failfileloc);
}
// move file to failed folder, well copy and delete it....
if (!copy($failfileloc . "/" . $filename))
{
echo "Failed to copy $filename...\n";
}
if (!unlink($newfileloc))
{
echo "Failed to delete $filename...\n";
}
} */
// audit trail
$audit_what = "uploaded $newfileloc";
$audit_detail = "status: $uploadstatus";
audit($username,$audit_what,$audit_detail);
// Display record counts
echo "<center>Thank you, upload completed.<br><br><b><u>Records inserted:</u><b><br><table>";
foreach ($rows_inserted as $table => $records)
{
echo "<tr><td>$table:</td>
<td align=right>$records</td></tr>";
}
$total_records = array_sum($rows_inserted);
echo "<tr><td><b>Total:</td><td align=right>$total_records</b></td></tr>
</table><br><a href=index.php>Back</a></center>";
// show the web page footer
HTMLfooter();
/* ------------- Error Handling Functions --------------*/
function DisplayWarnings()
{
$warnings = sqlsrv_errors(SQLSRV_ERR_WARNINGS);
if (!is_null($warnings))
{
foreach( $warnings as $warning )
{
echo "Warning: ".$warning['message']."<br>";
}
}
}
function DisplayErrors()
{
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
foreach( $errors as $error )
{
echo "Error: ".$error['message']."<br>";
}
}
function InsertRecords($conn, $tab, $sql)
{
$res = sqlsrv_query($conn, $sql);
if ($res === false)
{
$errmsg = "Query failed: Error inserting $tab records - rolling back";
Rollback($conn, $sql, $errmsg);
}
return sqlsrv_rows_affected($res);
}
function Rollback($conn, $sql, $errmsg)
{
echo "<center><a href=\"javascript:history.go(-1)\"><b>Go Back</b></a></center><br><br>";
echo "$errmsg<br>";
//echo "Debug: $sql<br>";
DisplayWarnings();
DisplayErrors();
sqlsrv_rollback($conn);
HTMLfooter();
die(print_r(sqlsrv_errors(), true));
}
?>