/**
* log Import events for importing files
* @param string $csv_file
* @param boolean $Pass
* @param string $Description
* @param string $ImportFlag
*/
function fun_Log_Import($csv_file, $Pass, $Description, $ImportFlag = 0)
{
global $conn, $intTimeStamp;
preg_match("/ftp:\\/\\/(.*)/i", $Site, $arMatch);
//just get the URL
$Site = $arMatch[1];
//record to log to mysql for web page monitoring
$HostID = 1;
$Name = "Import {$csv_file}";
$Description = "{$Description}";
$CategoryID = 1033;
$Success = $Pass;
//1=yes, 0=no
$DateCreated = $intTimeStamp;
$LastUpdated = $intTimeStamp;
//$ImportFlag = 1; //this is to notify the next stage that we are ready to move data
//check to see if we need to insert or update?
$query = "SELECT MonitorID FROM monitor WHERE (HostID='{$HostID}') AND (Name = '{$Name}')";
$field = "MonitorID";
$rsMonitorID = fun_SQLQuery2($query, $field);
if ($rsMonitorID) {
$query = "UPDATE monitor SET LastUpdated = '{$LastUpdated}', Success='{$Success}', ImportFlag='{$ImportFlag}', CategoryID='{$CategoryID}'\n\t\t\t WHERE (MonitorID='{$rsMonitorID}'); ";
} else {
$query = "INSERT INTO monitor (DateCreated, HostID, Name, Description, Success, CategoryID) \n\t\t\t VALUES ('{$DateCreated}', '{$HostID}', '{$Name}', '{$Description}', '{$Success}', '{$CategoryID}');";
}
//run the query
if ($query) {
fun_SQLQuery($query, $WantRC = 0, $WantLID = 1, $rtnRC = "", $rtnLID = "", $arVar = "");
$ImportID = $rtnLID;
}
return $ImportID;
}
function fun_Mysql_Optimize_Columns($DB, $Sample_Table, $Test_HowMany_Rows = 500, $New_Table = "", $New_Table_ID = "")
{
global $conn;
echo "\n\n<pre>\n";
echo "\n\n\n************************************* Starting Mysql Optimize *************************************\n\n";
//these didn't work, b/c sometimes I need it to skip and others dont need it
//1. problem 1 - no records to optimize - fixed
//2. problem 2 - not enough records to optimize correctly, will fix in mysql insert
// if ($Test_HowMany_Rows < 100)
// {
// $Test_HowMany_Rows = 300;
// }
// if ($rsTotal < $Test_HowMany_Rows)
// {
// $str = "Skipped rsTotal < TestHowManyRows($Test_HowMany_Rows)\n";
// echo "$str";
// return $str;
// }
//got to possible problems with this, on optimizing a small table that will possibly change the fields to being to small or the wrong type
//skip if no records to optimize
$query = "SELECT COUNT(*) as Total FROM `{$DB}`.`{$Sample_Table}`;";
$field = "Total";
$rsTotal = fun_SQLQuery2($query, $field);
echo "Enough rows to optimize???? - rowsTotal=({$rsTotal})\n";
echo "sample table query: {$query}\n";
if ($rsTotal < 1) {
$str = "Skipping Optimize\n**** Optimize END **** \n";
echo "{$str}";
return FALSE;
}
//debug
//die("\n\nin opto\n\n");
//get fields from db - SHOW COLUMNS FROM mytable FROM mydb;
$query = "SHOW COLUMNS FROM `{$DB}`.`{$Sample_Table}` FROM `{$DB}`;";
echo "{$query}\n";
$field = "Field";
$arColumns = fun_SQLQuery_Array($query, $field);
//get mysql info
$query = "SELECT * FROM `{$DB}` . `{$Sample_Table}` ORDER BY RAND() LIMIT 0,{$Test_HowMany_Rows}";
$result = mysqli_query($conn, $query) or die("Select Query failed : " . mysqli_error() . " :<br>\r\n {$query}\n");
$i = 0;
while ($row = mysqli_fetch_object($result)) {
foreach ($arColumns as $Column) {
$rsData = $row->{$Column};
//save the vars to array
if ($rsData) {
$arRow[$i][$Column]['Data'] = $rsData;
$arRow[$i][$Column]['DataType'] = fun_Mysql_Sample_Field_Data_Type($rsData, $rtnDataLength, $rtnZeroFill, $rtnLenth_After_Decimal);
$arRow[$i][$Column]['DataLenth'] = $rtnDataLength;
}
if ($rtnZeroFill != "") {
$arRow[$i][$Column]['ZeroFill'] = $rtnZeroFill;
}
if ($arRow[$i][$Column]['DataType'] == "Decimal") {
$arRow[$i][$Column]['Chars_After_Dec'] = $rtnLenth_After_Decimal;
}
}
$i++;
}
//end queryable rs
//debug
//print_r($arRow);
//move through columns
$NewDataType = "";
$NewDataLength = 0;
foreach ($arColumns as $Column) {
echo "\n\n\nColumn:{$Column}\n";
//move through all the test rows
$NewDataLengthTimes = 0;
for ($i = 0; $i < count($arRow); $i++) {
$cData = $arRow[$i][$Column]['Data'];
$cDataType = $arRow[$i][$Column]['DataType'];
$cDataLenth = $arRow[$i][$Column]['DataLenth'];
$cZeroFill = $arRow[$i][$Column]['ZeroFill'];
$cCharsADec = $arRow[$i][$Column]['Chars_After_Dec'];
if ($cDataType and $NewDataType != "Varchar") {
$NewDataType = $cDataType;
}
if ($NewDataType == "Varchar") {
$NewDataType = "Varchar";
}
if ($NewDataType == "Decimal") {
$NewDataType = "Decimal";
}
if ($NewCharsADec < $cCharsADec && $cCharsADec != "") {
$NewCharsADec = $cCharsADec;
}
if ($cDataType == "Integer" && $cZeroFill == 1) {
$NewZeroFill = 1;
}
if ($NewDataLength < $cDataLenth) {
$NewDataLength = $cDataLenth;
$NewDataLengthTimes = $NewDataLengthTimes + 1;
//singal that it could be a character(DataLength)
}
echo "{$i}:DataType:{$cDataType} DataLength:{$cDataLenth} ZeroFill:{$cZeroFill} CharAfterDec:{$cCharsADec} Char:{$NewDataLengthTimes} {$cData}\n";
}
//for each row for that column
if ($NewDataType == "Integer" && $NewCharsADec > 0) {
$NewDataType = "Decimal";
}
if ($NewDataType == "Varchar" && $NewDataLength > 250) {
$NewDataType = "Text";
}
if ($NewDataType == "") {
$NewDataType = "Varchar";
}
$ColumnType = fun_Mysql_Query_Column_Setup($NewDataType, $NewDataLength, $NewZeroFill, $NewCharsADec, $NewDataLengthTimes);
//$arSummary[$Column]['Data'] = "Summary";
//$arSummary[$Column]['DataType'] = $NewDataType;
//$arSummary[$Column]['DataLenth'] = $NewDataLength;
//$arSummary[$Column]['ZeroFill'] = $NewZeroFill;
//$arSummary[$Column]['Chars_After_Dec'] = $NewCharsADec;
$arSummary[$Column] = $ColumnType;
echo "00:DataType:{$NewDataType} DataLength:{$NewDataLength} ZeroFill:{$NewZeroFill} CharAfterDec:{$NewCharsADec} Char:{$NewDataLengthTimes} - {$ColumnType}\n";
$NewDataType = "";
$NewDataLength = "";
$NewZeroFill = "";
$NewCharsADec = "";
}
//end for each columns
echo "\n\n";
echo "Optimization Summary\n";
//Mysql Column Summary of what to do and how to optimize them
print_r($arSummary);
//1. Get primary key for skip
$query = "DESCRIBE `{$DB}`.`{$Sample_Table}`";
$result = mysqli_query($conn, $query) or die("Select Query failed : {$query}<br>\n" . mysqli_error() . " :<br>\n");
while ($row = mysqli_fetch_object($result)) {
$rsField = $row->Field;
$rsKey = $row->Key;
if (preg_match("/pri/i", $rsKey)) {
//get rid of the primary key
unset($arSummary[$rsField]);
}
}
//2.DATE -> UPDATE hostdb.import_sc_sales SET test=UNIX_TIMESTAMP(STR_TO_DATE(test, "%m/%d/%Y")) //2007-08-15 15:00:16
//update date first to integer format -before altering to mysql integer column type
foreach ($arSummary as $Column => $ColumnType) {
if (preg_match("/Date/", $ColumnType)) {
//$query = "UPDATE `$DB`.`$Sample_Table` SET $Column=UNIX_TIMESTAMP(STR_TO_DATE($Column, '%m/%d/%Y'))";
//echo "DatesToInteger:$Column -> $query\n";
//$result = mysqli_query($conn, $query) or die("Select Query failed : $query<br>\n" . mysqli_error() . " :<br>\n");
//using php to transform string to unixtimestamp
fun_Mysql_Optimize_Date($DB, $Sample_Table, $New_Table_ID, $Column, $OtherDBConn);
$arSummary[$Column] = "INTEGER NOT NULL DEFAULT 0";
}
}
//3.Setup Query
foreach ($arSummary as $field => $ColumnType) {
$arMysql[] = "MODIFY COLUMN `{$field}` {$ColumnType}";
}
print_r($arMysql);
// //are we working on current table or figuring out what table to make or fix
// if ($New_Table)
// {
// $Check_Table = $New_Table;
// }
// else
// {
// $Check_Table = $Sample_Table; //working same table as we are optimizing
// }
//
// //Are we working on a live table??
// $query = "SHOW TABLE STATUS FROM $DB WHERE (Name = '$Check_Table')";
// $field = "Name";
// $IsTable = fun_SQLQuery2($query, $field);
//
// if (!$IsTable) //make table if need be
// {
// //make table
// $query = "CREATE TABLE `$DB`.`$New_Table` ( `$New_Table_ID` int NOT NULL AUTO_INCREMENT, PRIMARY KEY(`$New_Table_ID`)) ENGINE = MYISAM;";
// echo "$query\n";
// mysqli_query($conn1, $query) or die("Select Query failed : " . mysqli_error() . " :<br>\r\n $query");
// }
//setup mysql query for altering current table
$csvColumns = implode(",", $arMysql);
echo "csvColumns: {$csvColumns}\n\n\n";
$query = "ALTER TABLE `{$DB}`.`{$Sample_Table}` {$csvColumns}";
echo "Modify:{$query}\n";
$result = mysqli_query($conn, $query) or die("Select Query failed : {$query}<br>\n" . mysqli_error() . " :<br>\n");
//print_r($arRow);
echo "************************************* The End - Mysql Optimize *************************************";
echo "</pre>\n";
//DEBUG
//die("\n die in opto \n");
return TRUE;
}
private function fun_getListingStatus($LN)
{
$query = "SELECT ST FROM nwmls.listing WHERE (LN = '{$LN}')";
$field = "LN";
$rsStatus = fun_SQLQuery2($query, $field, $OtherDBConn = "");
return $rsStatus;
}
