388 lines
12 KiB
Plaintext
Executable File
388 lines
12 KiB
Plaintext
Executable File
<?php
|
||
/*********************************************************
|
||
程序说明：
|
||
功能说明：从文件导入信息到数据库。
|
||
|
||
调用关系：调用: header.inc
|
||
被调用:
|
||
|
||
变量说明：
|
||
|
||
作者:
|
||
|
||
修改注释：
|
||
NO.1
|
||
姓名:
|
||
时间:2004-02-25
|
||
说明:修复原来忽略导入文件第一行的bug。方法：设$data_style[ignore_lines]=0
|
||
*********************************************************/
|
||
set_time_limit(3600);
|
||
require("../../inc/header.inc");
|
||
$DEBUG = 0;
|
||
$insert_result=2;
|
||
if ($DEBUG) echo "<br>sel_name = $sel_name<br>";
|
||
/*
|
||
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
|
||
INTO TABLE tbl_name
|
||
[FIELDS
|
||
[TERMINATED BY '\t']
|
||
[OPTIONALLY] ENCLOSED BY '']
|
||
[ESCAPED BY '\\' ]]
|
||
[LINES TERMINATED BY '\n']
|
||
[IGNORE number LINES]
|
||
[(col_name,...)]
|
||
|
||
例:
|
||
LOAD DATA LOCAL INFILE '/tmp/phpBGhA33'
|
||
INTO TABLE OMC_Def_test
|
||
FIELDS
|
||
TERMINATED BY ','
|
||
ENCLOSED BY '\"'
|
||
ESCAPED BY '\\'(可省略)
|
||
LINES
|
||
TERMINATED BY '\n'
|
||
IGNORE 1 LINES
|
||
*/
|
||
|
||
|
||
$remark_style=array(
|
||
'sign' =>'#',
|
||
'fields_by' =>',',
|
||
'lines_by' =>'\n',
|
||
'fields_seek' =>'1'
|
||
);
|
||
|
||
$data_style=array(
|
||
'fields_by' =>',',
|
||
'enclosed_by'=>'\"',
|
||
'escaped_by' =>'\\',
|
||
'lines_by' =>'\n',
|
||
'ignore_lines' =>'0',
|
||
'mode' =>'REPLACE'
|
||
);
|
||
echo "test<br>";
|
||
//get load out information
|
||
$info_sql = "SELECT * FROM $syscsvconf_table WHERE key_menu_id='$key_menu_id'";
|
||
if($DEBUG) echo "<br>info_sql = $info_sql";//调试用
|
||
$result = mysqli_query($pubConn,$info_sql);
|
||
$info_rows = mysqli_fetch_array($result);
|
||
$server_name = $info_rows[server_name];
|
||
$db_name = $info_rows[db_name];
|
||
$table_name = $info_rows[table_name];
|
||
$where_find = $info_rows[where_find];
|
||
$sys_id = $info_rows[sys_id];
|
||
$obj_id = $info_rows[obj_id];
|
||
$if_enable = $info_rows[if_enable];
|
||
$if_enable =0;
|
||
$if_subsys = $info_rows[if_subsys];
|
||
$menu_name = $info_rows[menu_name];
|
||
$menu_name_2 = $info_rows[menu_name_2];
|
||
$log_name = "Upload--"."'$server_name' '$db_name' '$table_name'";
|
||
/*
|
||
$sel_field_list= mysql_list_fields($db_name,$table_name);
|
||
$sel_field_num = mysql_num_fields($sel_field_list);
|
||
if($DEBUG) echo "<br>sel_field_num=$sel_field_num";
|
||
for($i=0;$i<$sel_field_num;$i++){
|
||
$sel_field_name = mysql_field_name($sel_field_list, $i);
|
||
if($sel_field_name=='mflag'){
|
||
$if_enable=1;
|
||
break;
|
||
}
|
||
}
|
||
*/
|
||
|
||
if($DEBUG) echo "<br>if_enable=$if_enable";
|
||
|
||
if($DEBUG) echo "<br>if_subsys=$if_subsys";
|
||
if($if_subsys>0){
|
||
$subsys_num = omc_sys_id_info($sys_id,subsys_num);
|
||
echo "<br>subsys_num=$subsys_num";
|
||
}
|
||
$load_file_info=array(
|
||
'name' =>$textfile,
|
||
'server'=>$server_name,
|
||
'db' =>$db_name,
|
||
'table' =>$table_name
|
||
);
|
||
|
||
if(isset($textfile) && $textfile != "none"){
|
||
if($DEBUG) echo "<br>LOAD DATA file -> $textfile<br>";
|
||
if (($table_name == 'paramConf') ||($table_name == 'subsParamConf_0')||($table_name == 'subsParamConf_1')||($table_name == 'logAttrDef_0')||($table_name == 'logAttrDef_1')){
|
||
$del_sql = "DELETE FROM $table_name";
|
||
if($DEBUG) echo "<br>del_sql = $del_sql";
|
||
$del_result = mysqli_query($pubConn,$del_sql);
|
||
if($DEBUG) echo "<br>del_result=$del_result";
|
||
|
||
$insert_sql = "LOAD DATA INFILE '$textfile'
|
||
INTO TABLE $table_name
|
||
FIELDS TERMINATED BY ','
|
||
LINES TERMINATED BY '\n'
|
||
";
|
||
if($DEBUG) echo "<br>insert_sql=$insert_sql";
|
||
$insert_result=mysqli_query($pubConn,$insert_sql);
|
||
$log_name="UPLOAD $db_name-$table_name";
|
||
if($DEBUG) echo "<br>insert_result=$insert_result";
|
||
if ($insert_result){
|
||
$log_name=$log_name." successful.";
|
||
$update_sql="UPDATE $syscsvconf_table SET last_upload = CURRENT_TIMESTAMP WHERE key_menu_id='$key_menu_id'";
|
||
mysqli_query($pubConn,$update_sql);
|
||
}
|
||
else
|
||
$log_name=$log_name." fail.";
|
||
insertLog("$log_name");
|
||
}
|
||
//elseif (omc_chk_load_in($load_file_info,$remark_style) > 0){
|
||
else{
|
||
//empty existed table
|
||
if($DEBUG) $drop_sql="DROP TABLE IF EXISTS load_temp";
|
||
$drop_result=mysqli_query($pubConn,$drop_sql);
|
||
|
||
$create_sql="CREATE TABLE IF NOT EXISTS load_temp SELECT * FROM $table_name LIMIT 0,0";
|
||
if($DEBUG) echo "<br>create_sql=$create_sql";
|
||
$create_result=mysqli_query($pubConn,$create_sql);
|
||
|
||
//如果建表成功,把文件数据导入临时表
|
||
if($create_result>0){
|
||
$load_sql="LOAD DATA LOCAL INFILE '$textfile' $data_style[mode]
|
||
INTO TABLE load_temp
|
||
FIELDS
|
||
TERMINATED BY '$data_style[fields_by]'
|
||
ENCLOSED BY '$data_style[enclosed_by]'
|
||
LINES
|
||
TERMINATED BY '$data_style[lines_by]'
|
||
IGNORE $data_style[ignore_lines] LINES
|
||
";
|
||
if($DEBUG) echo "<br>load_sql=$load_sql";
|
||
$load_result=mysqli_query($pubConn,$load_sql);
|
||
if($DEBUG) echo "load_resul[$load_result] <br>";
|
||
if(0)
|
||
{
|
||
echo mysqli_error($pubConn);
|
||
echo "load_sql[$load_sql] load_resul[$load_result] <br>";
|
||
exit();
|
||
}
|
||
//如果导入成功,把临时表作相应改变
|
||
if($load_result > 0){
|
||
//删除空记录
|
||
$sel_field_list= mysql_list_fields($db_name,$table_name);
|
||
$sel_field_num = mysql_num_fields($sel_field_list);
|
||
$sel_all_field='';
|
||
for($i=0;$i<$sel_field_num;$i++){
|
||
$sel_field_name = mysql_field_name($sel_field_list, $i);
|
||
if($i==$sel_field_num-1)
|
||
{
|
||
$sel_all_field.=$sel_field_name."=''";
|
||
}
|
||
else
|
||
{
|
||
$sel_all_field.=$sel_field_name."='' AND ";
|
||
}
|
||
}
|
||
$del_sql = " DELETE FROM load_temp WHERE ".$sel_all_field;
|
||
if ($DEBUG) echo "<br>del_sql=$del_sql";
|
||
$del_result = mysqli_query($pubConn,$del_sql);
|
||
|
||
//如果是参数表
|
||
if($if_subsys > 0){
|
||
$update_sql="UPDATE load_temp SET key_subsys_id=$subsys_num";
|
||
}elseif($if_enable > 0){
|
||
$sel_field_list= mysql_list_fields($db_name,$table_name);
|
||
$sel_field_num = mysql_num_fields($sel_field_list);
|
||
if($DEBUG) echo "<br>sel_field_num=$sel_field_num";
|
||
$set_all_field='';
|
||
for($i=0;$i<$sel_field_num;$i++){
|
||
$sel_field_name = mysql_field_name($sel_field_list, $i);
|
||
if(substr($sel_field_name,0,1)=='E'){
|
||
$set_field=substr($sel_field_name,2);
|
||
$set_all_field=$set_all_field.",".$sel_field_name."=".$set_field;
|
||
}
|
||
}
|
||
$set_total_field="mflag='1'".$set_all_field;
|
||
$update_sql="UPDATE load_temp SET ".$set_total_field;
|
||
if($DEBUG) echo "<br>update_sql=$update_sql";
|
||
}
|
||
if($if_subsys > 0 || $if_enable > 0){
|
||
$update_result=mysqli_query($pubConn,$update_sql);
|
||
}else{
|
||
$update_result=1;
|
||
}
|
||
if(0) echo "update_result[$update_result]<br>";
|
||
//echo "<br>update_sql=$update_sql";
|
||
//如果修改成功,把临时表数据一条条插入相应表
|
||
if($update_result > 0){
|
||
$select_sql="SELECT * FROM load_temp";
|
||
$select_result=mysqli_query($pubConn,$select_sql);
|
||
$sel_field_num = mysql_num_fields($select_result);
|
||
$select_rows=mysqli_fetch_array($select_result);
|
||
|
||
do{
|
||
$sel_val='';
|
||
for($i=0;$i<$sel_field_num;$i++){
|
||
$select_rows[$i]=str_replace("'","\'",$select_rows[$i]);
|
||
if($i==$sel_field_num-1){
|
||
if($i==0){
|
||
$sel_val="'".$select_rows[$i]."'";
|
||
}else{
|
||
$sel_val=$sel_val."'".$select_rows[$i]."'";
|
||
}
|
||
}else{
|
||
if($i==0){
|
||
$sel_val="'".$select_rows[$i]."'".",";
|
||
}else{
|
||
$sel_val=$sel_val."'".$select_rows[$i]."'".",";
|
||
}
|
||
}
|
||
//echo "<br>sel_val=$sel_val";
|
||
}
|
||
$replace_sql="REPLACE $table_name VALUES($sel_val)";
|
||
if ($DEBUG) echo "<br>replace_sql=$replace_sql";
|
||
$replace_result=mysqli_query($pubConn,$replace_sql);
|
||
}while($select_rows=mysqli_fetch_array($select_result));
|
||
|
||
if($table_name=='pre_mo_tariff'||$table_name=='pre_mt_tariff'){
|
||
$tmpSql=" SELECT bill_id,tariff_id FROM $table_name";
|
||
if($DEBUG)echo "<br>tmpSql=$tmpSql";
|
||
$tmpRes = @mysqli_query($pubConn,$tmpSql);
|
||
$tmpRow = @mysqli_fetch_array($tmpRes);
|
||
echo mysqli_error($pubConn);
|
||
|
||
do
|
||
{
|
||
$tmp_bill_id = $tmpRow[bill_id];
|
||
$tmp_tariff_id = $tmpRow[tariff_id];
|
||
|
||
for($count=0;$count<strlen($tmp_bill_id);$count++){
|
||
if($tmp_bill_id[$count]<'0'||$tmp_bill_id[$count]>'9'){
|
||
if($tmp_bill_id[$count]!='B'&&$tmp_bill_id[$count]!='b'&&$tmp_bill_id[$count]!='C'&&$tmp_bill_id[$count]!='c'){
|
||
$failFlag='1';
|
||
$delSql=" DELETE FROM $table_name WHERE tariff_id='$tmp_tariff_id' AND bill_id='$tmp_bill_id'";
|
||
//echo "<br>$delSql";
|
||
$delRes = @mysqli_query($pubConn,$delSql);
|
||
echo mysqli_error($pubConn);
|
||
echo "<SCRIPT LANGUAGE=\"JavaScript\">";
|
||
echo "alert(\"The prefix $tmp_bill_id of tariff $tmp_tariff_id is error\")";
|
||
echo "</SCRIPT>";
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
}while($tmpRow = @mysqli_fetch_array($tmpRes));
|
||
if($failFlag=='1'){
|
||
echo "<SCRIPT LANGUAGE=\"JavaScript\">";
|
||
echo "alert(\"Other prefixs is success\")";
|
||
echo "</SCRIPT>";
|
||
}
|
||
}
|
||
|
||
//如果导入成功,给出成功上载标志
|
||
if($replace_result>0){
|
||
echo "<br><font color=\"#0000FF\">$strOperateDatabaseSuccess</font>";
|
||
$input_success=1;
|
||
//如果需要通知底层
|
||
if($if_enable>0){
|
||
$command_type='1';
|
||
$send_result=send_shm_module($obj_id,$command_type);//设置共享内存
|
||
if($send_result<0){
|
||
$update_sql = "UPDATE $table_name set mflag = '0'";
|
||
//echo "<br>update_sql=$update_sql";
|
||
$result=mysqli_query($pubConn,$update_sql);
|
||
}//end if($send_result<0)
|
||
|
||
}//end ifif($if_enable>0)
|
||
|
||
}//end if($replace_result>0)
|
||
else{
|
||
echo "replace err<br>";
|
||
echo mysqli_error($pubConn);
|
||
}
|
||
|
||
}//end if($update_result>0)
|
||
|
||
}//end if($load_result>0)
|
||
}//end if($create_result>0)
|
||
|
||
if($input_success==0){
|
||
echo "<br><font color=\"#FF0000\">$strOperateDatabaseFailure</font>";
|
||
echo mysqli_error($pubConn);
|
||
exit();
|
||
}
|
||
$drop_sql="DROP TABLE IF EXISTS load_temp";
|
||
$drop_result=mysqli_query($pubConn,$drop_sql);
|
||
if($input_success>0){
|
||
//edit log_name
|
||
$log_name=$log_name." successful.";
|
||
//update last download time
|
||
$update_sql="UPDATE $syscsvconf_table SET last_upload = CURRENT_TIMESTAMP WHERE key_menu_id='$key_menu_id'";
|
||
mysqli_query($pubConn,$update_sql);
|
||
}else{
|
||
$log_name=$log_name." fail.";
|
||
echo "<br><font color=\"#FF0000\">$strOperateDatabaseFailure</font>";
|
||
echo mysqli_error($pubConn);
|
||
}
|
||
insertLog("$log_name");
|
||
//@unlink($textfile);
|
||
//}else{
|
||
// echo "<font color=\"#FF0000\">$strDataUploadError</font>";
|
||
// $log_name=$log_name." $strDataUploadError.";
|
||
// insertLog("$log_name");
|
||
}
|
||
}elseif(isset($textfile)){
|
||
echo "<br><font color=\"#FF0000\" size=\"3\"><b>Upload csv file failed, File isn't exist or file is empty!</b></font>";
|
||
}
|
||
|
||
?>
|
||
<body leftmargin="15" rightmargin="10" onload="javascript:adjust();" onresize="javascript:adjust();" style="overflow:hidden;overflow-x:hidden;overflow-y:hidden;">
|
||
|
||
<p><font face="Arial, Helvetica, sans-serif"><font size="+1">
|
||
<?php
|
||
|
||
adjust_head();
|
||
adjust_title_head();
|
||
|
||
//标题
|
||
echo "$strUploadTo ";
|
||
if($selectedLanguage=='eng'){
|
||
echo "$menu_name_2";
|
||
}elseif ($selectedLanguage == 'chn'){
|
||
echo "$menu_name";
|
||
}
|
||
echo "</font><br><br>";
|
||
/*
|
||
if($load_sys_type==0){//如果是TS_OMC
|
||
echo "<br><font color=\"#FF0000\">$strNoteUploadParameter</font><br>";
|
||
}
|
||
*/
|
||
if ($insert_result == 1){
|
||
echo "<br><font color=\"#0000FF\">$strUploadSucc</font><br>";
|
||
}
|
||
elseif ($insert_result == 0){
|
||
echo "<br><font color=\"#0000FF\">$strUploadUnSucc</font><br>";
|
||
}
|
||
?>
|
||
<form method ="POST" action="<?php echo"$PHP_SELF?sel_name=$sel_name";?>" enctype="multipart/form-data">
|
||
<table border="0" cellpadding="0" cellspacing="0" >
|
||
<tr>
|
||
<td width="40%">
|
||
<input type="hidden" name="MAX_FILE_SIZE" value="20971520"><!-- MAX_FILE_SIZE = 20M -->
|
||
<input type="file" name="textfile" class="button">
|
||
<input type="Hidden" name="key_menu_id" value="<?php echo "$key_menu_id"; ?>">
|
||
</td>
|
||
<td width="60%">
|
||
<input type="submit" value="<?php echo"$strSubmit";?>" name="input" class="button">
|
||
<input type="button" value="<?php echo"$strReturn";?>" name="back" onclick=javascript:location.href="./load_csv_list.php?<?php echo "sel_name=$sel_name" ?>" class="button">
|
||
</td>
|
||
</tr>
|
||
</table>
|
||
<input type="hidden" name="load_note" value="<?php echo "$load_note";?>">
|
||
<input type="hidden" name="sel_name" value="<?php echo "$sel_name";?>">
|
||
</form>
|
||
|
||
<?php
|
||
adjust_title_tail();
|
||
adjust_content_head();
|
||
?>
|
||
</body>
|
||
</html>
|
||
|
||
|