function newai_import_XLS($Columns)
{
global $_FILES, $_POST, $_GET, $db;
global $showlistfieldlist, $showlistfieldfilter, $primarykey, $primarykey_index, $uniquekey;
global $common_html, $html_etc;
global $return_sql_line;
global $columns;
global $showlistfieldlist_key;
global $foreignkey, $showFieldName, $tablename;
if (is_uploaded_file($_FILES['uploadfileXLS']['tmp_name'])) {
$uploadfile_self = $_FILES['uploadfileXLS']['tmp_name'];
$uploadfile_name = $_FILES['uploadfileXLS']['name'];
$checkFileType = substr($uploadfile_name, -3);
if ($checkFileType != "xls") {
print_nouploadfile("你上传的不是EXCEL格式的文件!");
exit;
}
//print $checkFileType;exit;
if (!is_dir("FileCache")) {
mkdir("FileCache");
}
$uploadfile_name = "FileCache/" . $uploadfile_name;
copy($_FILES['uploadfileXLS']['tmp_name'], $uploadfile_name);
if (is_file("../../Framework/PHPExcelParser4/readExcel.php")) {
require_once "../../Framework/PHPExcelParser4/readExcel.php";
} else {
if (is_file("../DANDIAN/PHPExcelParser4/readExcel.php")) {
require_once "../DANDIAN/PHPExcelParser4/readExcel.php";
} else {
require_once "../Framework/PHPExcelParser4/readExcel.php";
}
}
$a = new ReadExcel($uploadfile_name);
$tmp = $a->read();
//按列读取的数据,转换为按行读取的数据
$MainData = $tmp[0];
$ColumnNumber = sizeof(array_values($MainData));
if ($MainData[$ColumnNumber - 1][0] == '错误信息') {
--$ColumnNumber;
}
for ($i = 0; $i < $ColumnNumber; $i++) {
$ColumnArray = $MainData[$i];
for ($ii = 0; $ii < sizeof($ColumnArray); $ii++) {
$ContentArray[$ii][$i] = $ColumnArray[$ii];
//$ContentArray[$ii][$i] = str_replace("，",",", $ContentArray[$ii][$i]);
}
}
$first_row_array = $ContentArray[0];
$first_row_array_chinese = $first_row_array;
//print_r($first_row_array);exit;
// changchang008@gmail.com at 2012-2-7
$sql = "select fieldname,chinese,english from systemlang where tablename='" . $tablename . "'";
$rs = $db->Execute($sql);
$rs_a = $rs->GetArray();
$fieldchinesename_to_fieldname = array();
foreach ($rs_a as $row) {
if (in_array($row[fieldname], $columns)) {
$fieldchinesename_to_fieldname[$row[chinese]] = $row[fieldname];
}
}
foreach ($first_row_array as $key => $vo) {
if (isset($fieldchinesename_to_fieldname[$vo])) {
$first_row_array[$key] = $fieldchinesename_to_fieldname[$vo];
}
}
for ($iii = 0; $iii < sizeof($first_row_array); $iii++) {
$first_row_array[$iii] = TRIM($first_row_array[$iii]);
}
//得到字段过滤信息列表
$newstring = array();
$newstring1 = array();
//需关联的字段
$newstring2 = array();
//需过滤的字段
$showlistfieldlistArray = explode(',', $showlistfieldlist);
$showlistfieldfilterArray = explode(',', $showlistfieldfilter);
//可以允许导入的列表
for ($i = 0; $i < sizeof($showlistfieldlistArray); $i++) {
if ($showlistfieldlistArray[$i] != '') {
$ElementTableField = $showlistfieldlistArray[$i];
$ElementFieldName = $Columns[$ElementTableField];
array_push($newstring, $ElementFieldName);
$filterItemArray = explode(":", $showlistfieldfilterArray[$i]);
if ($filterItemArray[0] == "tablefilter" || $filterItemArray[0] == "tablefiltercolor" || $filterItemArray[0] == "radiofilter" || $filterItemArray[0] == "zhujima" || $filterItemArray[0] == "system_datetime") {
$newstring1[$ElementFieldName] = $showlistfieldfilterArray[$i];
}
if ($filterItemArray[0] == "input" && $filterItemArray[1] == "name") {
$newstring2[$ElementFieldName] = $showlistfieldfilterArray[$i];
}
}
}
//$result = array_intersect ($newstring, $first_row_array);
$result = array();
foreach ($first_row_array as $row) {
if (in_array($row, $newstring)) {
$result[] = $row;
}
}
if (sizeof($result) == 0) {
print "<script language='javascript'>alert('没有可导入的列，请检查Excel的列头');window.history.back(-1);</script>";
exit;
}
//自动递增的主键
if (!empty($primarykey_index)) {
$sql = "select max({$primarykey_index}) as max from {$tablename}";
$rs = $db->Execute($sql);
$rs_a = $rs->GetArray();
$primarykey_value = intval($rs_a[0][max]);
}
// 须要检查的唯一key
$uniquekeyArray = explode(',', $uniquekey);
foreach ($uniquekeyArray as $key => $row) {
$uniquekeyArray[$key] = $columns[$row];
}
//数据行检测
$Insert_RIGHT = 0;
$Insert_ERROR = 0;
$is_error = false;
$ChildTableCacheArray = array();
//缓存关联到的数据
for ($i = 1; $i < sizeof($ContentArray); $i++) {
$line_array = $ContentArray[$i];
$line_array_text = join("','", $line_array);
$newline_array = array();
//以前的SIZEOF用的是line_array变量，后变为first_row_array，用于对应一些没有发生的变化
$uniquekey_error_sig = false;
$uniquekey_error_info = '';
$uniquekey_Array = array();
for ($j = 0; $j < sizeof($first_row_array); $j++) {
$convertFirstRow = array_flip($first_row_array);
//主键不能为空
$j_prikey = -1;
$j_prikey = $convertFirstRow[$primarykey_index];
if ($j_prikey > -1) {
if (TRIM($line_array[$j_prikey]) == '') {
$line_array[$j_prikey] = ++$primarykey_value;
//$uniquekey_error_sig = true;
//$is_error = true;
//$uniquekey_error_info .= '<'.$ContentArray[0][$j_prikey].">列数据不能为空、";
//break;
}
}
//索引键不能重复
$sql_where = '';
$uniquekey_realname = '';
foreach ($uniquekeyArray as $key => $value) {
if ($value != '') {
$j_prikey = $convertFirstRow[$value];
$sql_where .= " and {$value} ='" . $line_array[$j_prikey] . "'";
$uniquekey_realname .= $ContentArray[0][$j_prikey] . ",";
}
}
if ($sql_where != '') {
$exists_sql_text = "select count(*) as num from {$tablename} where 1=1" . $sql_where;
$rs = $db->Execute($exists_sql_text);
$rs_a = $rs->GetArray();
if ($rs_a[0][num] != 0) {
$uniquekey_error_sig = true;
$is_error = true;
$uniquekey_error_info .= '<' . $uniquekey_realname . ">列数据不能和数据库中的数据重复";
break;
}
}
//对每格数据进行处理
$ColumnName = $first_row_array[$j];
$in_array = in_array($ColumnName, $result);
if ($in_array) {
if (array_key_exists($ColumnName, $newstring1)) {
$ChildTableArray = explode(":", $newstring1[$ColumnName]);
if ($ChildTableArray[0] == 'zhujima') {
$srcFieldName = $Columns[$ChildTableArray[1]];
$key = array_search($srcFieldName, $first_row_array);
$line_array[$j] = 汉字转拼音首字母($line_array[$key]);
} else {
if ($ChildTableArray[0] == 'system_datetime') {
if ($line_array[$j] == '') {
$line_array[$j] = date("Y-m-d H:i:s");
} else {
if (strtotime($line_array[$j]) == -1) {
$line_array[$j] = date("Y-m-d H:i:s");
}
}
} else {
$ChildTableName = $ChildTableArray[1];
if ($ChildTableCacheArray[$ChildTableName][$line_array[$j]] == '' && $line_array[$j] != '') {
$ChildColumns = returntablecolumn($ChildTableName);
$ChildTableFieldValue = $ChildColumns[$ChildTableArray[2]];
$ChildTableFieldName = $ChildColumns[$ChildTableArray[3]];
$realvalue = returntablefield($ChildTableName, $ChildTableFieldName, $line_array[$j], $ChildTableFieldValue);
if ($realvalue == '') {
$uniquekey_error_sig = true;
$is_error = true;
$uniquekey_error_info .= '<' . $first_row_array_chinese[$j] . ">列在关联表" . $ChildTableName . "中找不到对应的数据";
break;
}
$ChildTableCacheArray[$ChildTableName][$line_array[$j]] = $realvalue;
} else {
$realvalue = $ChildTableCacheArray[$ChildTableName][$line_array[$j]];
}
$line_array[$j] = $realvalue;
}
}
}
if (array_key_exists($ColumnName, $newstring2)) {
$line_array[$j] = str_replace("\r", "", $line_array[$j]);
$line_array[$j] = str_replace("\n", "", $line_array[$j]);
$line_array[$j] = str_replace("'", "", $line_array[$j]);
//$line_array[$j]=str_replace("\"","",$line_array[$j]);
$line_array[$j] = str_replace("\\", "", $line_array[$j]);
$line_array[$j] = str_replace("/", "", $line_array[$j]);
$line_array[$j] = str_replace(",", "", $line_array[$j]);
$line_array[$j] = str_replace("#", "", $line_array[$j]);
}
/*
if(function_exists('FK_'.$tablename.'_'.$ColumnName)){
$line_array[$j] = call_user_func('FK_'.$tablename.'_'.$ColumnName,$line_array[$j]);
}
*/
$ResultFieldCode = $line_array[$j];
array_push($newline_array, htmlentitiesUser($ResultFieldCode));
}
}
//分析结束
if ($uniquekey_error_sig) {
$ContentArray[$i][FK_error_info] = $uniquekey_error_info;
$Insert_ERROR += 1;
} else {
if (!empty($primarykey_index) && !in_array($primarykey_index, $result)) {
$newline_array[] = ++$primarykey_value;
$result_add = ',' . $primarykey_index;
}
$insert_sql_text = "insert into " . $tablename . "(" . join(',', $result) . $result_add . ") values('" . join("','", $newline_array) . "')";
//exit($insert_sql_text);
$rs = $db->Execute($insert_sql_text);
if ($rs->EOF) {
$Insert_RIGHT += 1;
} else {
$Insert_ERROR += 1;
$ContentArray[$i][FK_error_info] = $insert_sql_text;
}
}
}
if ($is_error) {
if (is_file("../../Framework/PHPExcelParser4/WriteExcel/class.writeexcel_workbook.inc.php")) {
require_once "../../Framework/PHPExcelParser4/WriteExcel/class.writeexcel_workbook.inc.php";
require_once "../../Framework/PHPExcelParser4/WriteExcel/class.writeexcel_worksheet.inc.php";
} else {
if (is_file("../DANDIAN/PHPExcelParser4/WriteExcel/class.writeexcel_workbook.inc.php")) {
require_once "../DANDIAN/PHPExcelParser4/WriteExcel/class.writeexcel_workbook.inc.php";
require_once "../DANDIAN/PHPExcelParser4/WriteExcel/class.writeexcel_worksheet.inc.php";
} else {
require_once "../Framework/PHPExcelParser4/WriteExcel/class.writeexcel_workbook.inc.php";
require_once "../Framework/PHPExcelParser4/WriteExcel/class.writeexcel_worksheet.inc.php";
}
}
if (!is_dir("FileCache")) {
mkdir("FileCache");
}
$fname = "FileCache/导入失败记录.xls";
@unlink($fname);
$workbook =& new writeexcel_workbook($fname);
$worksheet1 =& $workbook->addworksheet('Sheet1');
# Frozen panes
$worksheet1->freeze_panes(1, 0);
# 1 row
$header =& $workbook->addformat();
$header->set_color('white');
$header->set_align('center');
$header->set_align('vcenter');
$header->set_pattern();
$header->set_fg_color('green');
$center =& $workbook->addformat();
$center->set_align('center');
$center->set_align('vcenter');
$header->set_pattern();
//列出字段的EXCEL列,通过此指定列宽度
$LitterArray = explode(',', 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z');
$LeftArray = array();
$LitterSize = sizeof($LitterArray);
for ($i = 0; $i < 10; $i++) {
if ($i == 0) {
$Little = '';
} else {
$Little = $LitterArray[$i - 1];
}
for ($ii = 0; $ii < $LitterSize; $ii++) {
$PartLitte = $LitterArray[$ii];
$Left = $Little . $PartLitte;
array_push($LeftArray, $Left);
}
}
//宽度值形成列表
$title_row_array = $ContentArray[0];
$title_row_array[] = '错误信息';
$TEMPVALUE = sizeof($title_row_array);
$LastValue = $LeftArray[$TEMPVALUE - 1];
for ($i = 0; $i < sizeof($title_row_array); $i++) {
//宽度值形成列表
$LastValue = $LeftArray[$i];
$LENGHT_VALUE = strlen($title_row_array[$i]);
$LENGHT_VALUE > 50 ? $LENGHT_VALUE = 50 : '';
$worksheet1->set_column($LastValue . ":" . $LastValue, $LENGHT_VALUE);
}
//写标题
for ($i = 0; $i < sizeof($title_row_array); $i++) {
//行列内容
$Element = $title_row_array[$i];
$worksheet1->write_string(0, $i, $Element, $header);
}
//写内容
$m = 0;
foreach ($ContentArray as $vo) {
if (!empty($vo['FK_error_info'])) {
$i = 0;
foreach ($vo as $row) {
$worksheet1->write_string($m + 1, $i, $row, $center);
$i++;
}
++$m;
}
}
$workbook->close();
$down_error_file = "<a href='./FileCache/导入失败记录.xls'>请点击此处下载导入错误的记录，修改正确后重新导入！</a>";
}
page_css('数据导入');
$Insert_Text = "新增数据成功:{$Insert_RIGHT} 条 失败:{$Insert_ERROR} 条";
//返回值链接判断,如果是回到首页,则直接用链接,否则则直接返回
global $returnmodel;
if ($returnmodel == "import_default") {
$returnmodel_TEXT = "history.back();";
} else {
$returnmodel_TEXT = "location='?action={$returnmodel}'";
}
print "\n\t\t\t\t<style type='text/css'>.style1 {\n\t\t\t\tcolor: #FFFFFF;\n\t\t\t\tfont-weight: bold;\n\t\t\t\tfont-family: Arial, Helvetica, sans-serif;\n\t\t\t\tfont-size: 14px;\n\t\t\t\t}\n\t\t\t\t</style>\n\t\t\t\t<BR><BR>\n\t\t\t\t<table width='450' border='0' align='center' cellpadding='0' cellspacing='0' class='small' style='border:1px solid #006699;'>\n\t\t\t\t<tr><td height='110' align='middle' colspan=2 bgcolor='#E0F2FC'>\n\t\t\t\t<font color=red >" . $common_html['common_html']['importsuccess'] . "<BR><BR>{$Insert_Text}<BR><BR>{$down_error_file}<BR><BR><input type=button accesskey='c' name='cancel' value=' 点击返回 ' class=SmallButton onClick=\"{$returnmodel_TEXT}\" title='快捷键:ALT+c'></font>\n\t\t\t\t</td></tr></table>";
unlink($uploadfile_name);
exit;
} else {
print_nouploadfile();
}
}
function return_sql_line_mysql($fields)
{
global $showlistfieldlist, $showlistfieldfilter;
global $group_filter;
global $_GET, $_POST;
global $primarykey, $columns;
global $_SESSION, $SUNSHINE_USER_NAME_VAR;
//print_R($columns);exit;
$primarykeyindex = $columns[$primarykey];
$tablename = $fields['table']['name'];
$primarykey = $fields['table']['primarykey'];
$uniquekey = $fields['table']['uniquekey'];
$list = explode(',', $showlistfieldlist);
$type = explode(',', $showlistfieldfilter);
$uniquekey_array = explode(',', $uniquekey);
$columns = returntablecolumn($tablename);
$columnsInfor = returntablecolumnInfor($tablename);
$primarykey_index = $columns[$primarykey];
for ($i = 0; $i < sizeof($list); $i++) {
$index = $list[$i];
$temp_array[$i] = $columns[$index];
$temp_type[(string) $columns[$index]] = $type[$i];
}
$temp = "" . join(',', $temp_array) . "";
$temp_insert = array();
// var_dump($list);//by cwf
// var_dump($temp_type);//by cwf
foreach ($temp_array as $list) {
$filterArray = explode(":", $temp_type[$list]);
switch ($filterArray[0]) {
case 'password':
$temp_post = "'" . MD5($_POST[$list]) . "'";
break;
case 'datetime0':
if ($_POST[$list] == '') {
$temp_post = 'null';
} else {
$temp_post = "'" . $_POST[$list] . "'";
}
break;
case 'autoincrement':
//print "autoincrement";
//print_R($_GET);
//print_R($_POST);
global $db, $tablename;
$MartField = "AUTO_INCREMENT_" . $list;
$MartFieldValue = $_POST[$MartField];
$PageActionFieldArray = explode('_', $_GET['action']);
$PageActionFieldValue = $PageActionFieldArray[0];
if ($MartFieldValue != "" && $PageActionFieldValue == "add") {
if ($fields['realtable'] != '') {
$sql12 = "select max({$list}) as NUM from " . $fields['realtable'];
} else {
$sql12 = "select max({$list}) as NUM from {$tablename}";
}
//print $sql12;//exit;
$rs12 = $db->Execute($sql12);
$number12 = $rs12->fields['NUM'];
$number12 += 1;
//$temp_post = format_auto_increment($number12);
$temp_post = $number12;
$_POST[$MartField] = $number12;
//exit;
}
break;
case 'input':
//获取表单中元素的原始值及修改后的值
if ($filterArray[1] == 'name') {
$_POST[$list] = str_replace("'", "", $_POST[$list]);
$_POST[$list] = str_replace("\"", "", $_POST[$list]);
$_POST[$list] = str_replace("\\", "", $_POST[$list]);
$_POST[$list] = str_replace("/", "", $_POST[$list]);
$_POST[$list] = str_replace("&", "", $_POST[$list]);
$_POST[$list] = str_replace(",", "", $_POST[$list]);
}
default:
// $temp_post="'".htmlspecialchars($_POST[$list],ENT_QUOTES)."'";
$temp_post = "'" . htmlspecialchars($_POST[$list], ENT_QUOTES, gb2312) . "'";
//edit by cwf源码中没有添加编码可选项，无法识别中文，添加gb2312后，可以识别中文
//htmlentitiesUser($_POST[$list])
// var_dump($temp_post);//by cwf
}
array_push($temp_insert, $temp_post);
}
$temp_insert_text = join(',', $temp_insert);
$temp_var = join(",\$", $temp_array);
$temp_var = "\${$primarykey_index},\$" . $temp_var;
$insert_sql = "insert into {$tablename} ({$temp}) values({$temp_insert_text})";
$uniquekey_select = array();
$uniquekey_select_get = array();
foreach ($uniquekey_array as $list) {
$temp_index_name = $columns[$list];
array_push($uniquekey_select, "{$columns[$list]}='" . htmlentitiesUser($_POST[$temp_index_name]) . "'");
array_push($uniquekey_select_get, "{$columns[$list]}='" . htmlentitiesUser($_GET[$temp_index_name]) . "'");
}
$uniquekey_sql = "" . join(' and ', $uniquekey_select) . "";
//print_R($uniquekey_select);
$uniquekey_sql_get = "" . join(' and ', $uniquekey_select_get) . "";
global $departprivte;
//用户角色级别权限判断，如果是用户自身的记录则可以修改，
//如果不是，那么沿用PRIVATE的权限进行管理
//此区域部分在NEWAI_SQL部分有所复制
if ($departprivte != "") {
$departprivteSQLArray = array();
$departprivteArray = explode('::', $departprivte);
//print_R($departprivteArray);
for ($i = 0; $i < sizeof($departprivteArray); $i++) {
$privText = $departprivteArray[$i];
$privTextArray = explode(':', $privText);
switch ($privTextArray[0]) {
case 'user':
$ColumnIndex1 = $privTextArray[1];
$USER_NAME = $_SESSION[$SUNSHINE_USER_NAME_VAR];
$ColumnName1 = $columns[$ColumnIndex1];
$_POST[$ColumnName1];
if ($ColumnName1 != "" && $_POST[$ColumnName1] != "") {
if ($USER_NAME == $_POST[$ColumnName1]) {
$SYSTEM_PRIVATE_USER_DEFINE_CONTROL = 0;
} else {
$SYSTEM_PRIVATE_USER_DEFINE_CONTROL = 1;
}
} else {
$SYSTEM_PRIVATE_USER_DEFINE_CONTROL = 0;
}
break;
}
//end swtich
}
//end for
}
//exit;
//print $SYSTEM_PRIVATE_USER_DEFINE_CONTROL;
//print $fields['value'][$ColumnName1];
//print_R($fields['value']);
//-------------------------------------------------------------------
//print_R($fields['USER_PRIVATE']);
$temp_update = array();
foreach ($temp_array as $list) {
////判断是否要进行字段操作 -- 开始
//用户定义角色权限，是否为只读(可写)选项
//如用用户定义可写，那么重新调整为可写， 如果非自身记录，则沿用系统设定
if ($ColumnName1 != "") {
$SYSTEM_PRIVATE_USER_DEFINE_CONTROL == 0 ? $fields['USER_PRIVATE'][$list] = '' : '';
}
//print $SYSTEM_PRIVATE_USER_DEFINE_CONTROL;
//print $fields['USER_PRIVATE'][$list]."<BR>";
if ($fields['USER_PRIVATE'][$list] != "") {
} else {
//去除readonlymulti:45:5后面的参数部分
$MODEL_ARRAY = explode(':', $temp_type[$list]);
switch ($MODEL_ARRAY[0]) {
case 'password':
//print strlen($_POST[$list]);
if (strlen($_POST[$list]) == 32) {
$temp_post = "{$list}='{$_POST[$list]}'";
} else {
$temp_post = "{$list}='" . MD5($_POST[$list]) . "'";
}
array_push($temp_update, $temp_post);
break;
case 'readonlymulti':
//不对数据库进行操作的字段类型
break;
case 'readonly':
//不对数据库进行操作的字段类型
break;
case 'autoincrement':
//print "autoincrement";
//print_R($_GET);
//print_R($_POST);
global $db, $tablename;
$MartField = "AUTO_INCREMENT_" . $list;
$MartFieldValue = $_POST[$MartField];
$PageActionFieldArray = explode('_', $_GET['action']);
$PageActionFieldValue = $PageActionFieldArray[0];
if ($MartFieldValue != "" && $PageActionFieldValue == "add") {
$sql12 = "select max({$list}) as NUM from {$tablename}";
//print $sql12;//exit;
$rs12 = $db->Execute($sql12);
$number12 = $rs12->fields[NUM];
$number12 += 1;
$number12 = format_auto_increment($number12);
//exit;
array_push($temp_update, $temp_post);
}
break;
case 'content':
$temp_post = "{$list}='" . $_POST[$list] . "'";
array_push($temp_update, $temp_post);
break;
case 'binaryfile':
if (file_exists($_FILES[$list]["tmp_name"])) {
$filename = $_FILES[$list]["name"];
$filename_array = explode('.', $filename);
$filepath = "attachment/" . $_GET[$primarykeyindex] . "." . $filename_array[sizeof($filename_array) - 1];
file_exists($filepath) ? unlink($filepath) : '';
copy($_FILES[$list]["tmp_name"], $filepath);
$temp_post = "{$list}='{$filepath}'";
array_push($temp_update, $temp_post);
}
break;
default:
$temp_post = "{$list}='" . htmlspecialchars($_POST[$list], ENT_QUOTES, gb2312) . "'";
//edit by cwf源码中没有添加编码可选项，无法识别中文，添加gb2312后，可以识别中文
//$temp_post="$list='".htmlentitiesUser($_POST[$list])."'";
//$temp_post="'".htmlspecialchars($_POST[$list],ENT_QUOTES)."'";
array_push($temp_update, $temp_post);
}
//end switch
}
//判断是否要进行字段操作 -- 结束
}
//print_R($temp_update);exit;
//print_r($_GET);
$filter_foreign_index = '';
//Begin if group_filter begin
if ($group_filter != '') {
$group_filter_array = explode(',', $group_filter);
for ($i = 0; $i < sizeof($group_filter_array); $i++) {
$group_filter_array_temp = explode(':', $group_filter_array[$i]);
$index_name = $columns["" . $group_filter_array_temp[0] . ""];
if ($_GET[$index_name] == '') {
} else {
if ($_GET[$index_name] != '' && $index_name == "birthday") {
$BirthdayValueArray = explode('-', $_GET[$index_name]);
//print_R($BirthdayValueArray);
if (sizeof($BirthdayValueArray) == 2) {
$date1 = Date("Y") - $BirthdayValueArray[0];
$date2 = Date("Y") - $BirthdayValueArray[1];
$counter_index = $i;
$Birthday_SQL = "EXTRACT( YEAR FROM {$index_name}) >= '{$date2}' and EXTRACT( YEAR FROM {$index_name}) <= '{$date1}'";
$filter_foreign_arrray[$i] = $Birthday_SQL;
}
} else {
$counter_index = $i;
//判断外来变量限制是否为数组形式
$VALUE_GET_ARRAY = explode(',', $_GET[$index_name]);
$AMOUNT_VALUE_GET_ARRAY = sizeof($VALUE_GET_ARRAY);
//为>1时，输入条件为两个，进行组建
if ($AMOUNT_VALUE_GET_ARRAY > 1) {
$filter_foreign_arrray[$i] = "({$index_name}='" . join("' or {$index_name}='", $VALUE_GET_ARRAY) . "')";
} else {
//print $index_name;
//print_R($columnsInfor[$index_name]->type);
global $SYSTEM_DB_TYPE;
if ($SYSTEM_DB_TYPE == "PGSQL") {
$字段类型 = $columnsInfor[$index_name]->type;
if (substr($字段类型, 0, 3) == 'int') {
//INT类型不能使用like
$filter_foreign_arrray[$i] = "{$index_name} = '{$_GET[$index_name]}'";
} else {
//字符类型,可以使用like
$filter_foreign_arrray[$i] = "{$index_name} like '%{$_GET[$index_name]}%'";
}
} else {
$字段类型 = $columnsInfor[$index_name]->type;
//记不起使用like的用途是什么了,现在恢复到=判断方式阶段
//$filter_foreign_arrray[$i]="$index_name like '%$_GET[$index_name]%'";
//现在恢复到=判断方式阶段
$filter_foreign_arrray[$i] = "{$index_name} = '" . $_GET[$index_name] . "'";
//默认MYSQL数据库
}
//学校信息较验生成
//if($_GET['学院名称']!="") {
// $LastArray = $filter_foreign_arrray;
//}
//else $LastArray = array();
}
}
}
}
//end for
//print $addsql2;
//学校信息较验判断
//if(sizeof($LastArray)>0) $filter_foreign_arrray = $LastArray;
if (sizeof($filter_foreign_arrray) <= 1) {
$filter_foreign_index = $filter_foreign_arrray[$counter_index];
} else {
$filter_foreign_index = join(' and ', $filter_foreign_arrray);
}
if (strlen($filter_foreign_index) == 0) {
$filter_foreign_index_body = '';
$filter_foreign_index = '';
} else {
$filter_foreign_index_body = " and " . $filter_foreign_index;
$filter_foreign_index = "where " . $filter_foreign_index;
}
}
//附加SQL语句,用于WHERE判断语句之间,用户自定义SQL的加入。
global $addsql2;
if ($addsql2 != "" && $filter_foreign_index != "") {
$filter_foreign_index_body = $filter_foreign_index_body . " and " . $addsql2;
$filter_foreign_index = $filter_foreign_index . " and " . $addsql2;
} else {
if ($addsql2 != "" && $filter_foreign_index == "") {
$filter_foreign_index_body = $addsql2;
$filter_foreign_index = "where " . $addsql2;
} else {
}
}
//print $filter_foreign_index;exit;
//Begin if -- hidden_field begin
global $hidden_field, $SUNSHINE_USER_NAME_VAR, $SUNSHINE_USER_DEPT_VAR, $SUNSHINE_USER_ID_VAR, $_SESSION;
if ($hidden_field != '') {
$hidden_field_array = explode(',', $hidden_field);
foreach ($hidden_field_array as $list) {
$circle_temp = explode(':', $list);
//print_R($circle_temp);
switch ($circle_temp[0]) {
case 'parent':
$temp_temp = $columns['' . $circle_temp[1] . ''];
$value = $_GET[$temp_temp];
if ($value == '') {
$value = 0;
}
if ($columns['' . $circle_temp[1] . ''] != '') {
$cirlce_mode_array_temp[++$i - 1] = "{$temp_temp}='{$value}'";
}
unset($temp_temp);
//unset($value);
break;
case 'user':
$temp_temp = $columns['' . $circle_temp[1] . ''];
//print_R($circle_temp[2]);
switch ($circle_temp[2]) {
case 'name':
$user_value = $_SESSION[$SUNSHINE_USER_NAME_VAR];
$cirlce_mode_array_temp[++$i - 1] = $temp_temp != '' ? "{$temp_temp}='{$user_value}'" : '';
break;
case 'department':
$user_value = $_SESSION[$SUNSHINE_USER_DEPT_VAR];
$cirlce_mode_array_temp[++$i - 1] = $temp_temp != '' ? "({$temp_temp}='{$user_value}' or {$temp_temp}='0')" : '';
break;
case 'id':
$user_value = $_SESSION[$SUNSHINE_USER_ID_VAR];
$cirlce_mode_array_temp[++$i - 1] = $temp_temp != '' ? "{$temp_temp}='{$user_value}'" : '';
break;
case 'get':
$user_value = $_GET[$temp_temp];
$cirlce_mode_array_temp[++$i - 1] = $temp_temp != '' ? "{$temp_temp}='{$user_value}'" : '';
break;
}
unset($temp_temp);
break;
case 'fixed':
$temp_temp = $columns['' . $circle_temp[1] . ''];
if ($temp_temp != '') {
$cirlce_mode_array_temp[++$i - 1] = "{$temp_temp}!='" . $circle_temp[2] . "'";
}
unset($temp_temp);
break;
case 'fixed_equal':
$temp_temp = $columns['' . $circle_temp[1] . ''];
if ($temp_temp != '') {
$cirlce_mode_array_temp[++$i - 1] = "{$temp_temp}='" . $circle_temp[2] . "'";
}
unset($temp_temp);
break;
case 'markadd':
case 'markread':
//print_R($circle_temp);
$markread_field = $columns['' . $circle_temp[1] . ''];
$markread_value_add = $circle_temp[2];
$markread_value_add = $markread_value_add == 1 ? 1 : 0;
if ($markread_field != '') {
$cirlce_mode_array_temp[++$i - 1] = "{$primarykey_index}='{$_GET[$primarykey_index]}'";
}
break;
case 'primarykey':
$cirlce_mode_array_temp[++$i - 1] = "{$primarykey_index}='{$_GET[$primarykey_index]}'";
break;
case 'calendar_begin':
if ($_GET['calendar_type'] == '') {
$_GET['calendar_type'] = 'day';
}
$datetime = returncalendar_dateline($_GET['calendar_type']);
$timeline = date("Y-m-d H:i:s", $datetime['begin']);
$calendar_begin_field = $columns['' . $circle_temp[1] . ''];
$cirlce_mode_array_temp[++$i - 1] = "{$calendar_begin_field}>'{$timeline}'";
break;
case 'calendar_end':
if ($_GET['calendar_type'] == '') {
$_GET['calendar_type'] = 'day';
}
$datetime = returncalendar_dateline($_GET['calendar_type']);
$timeline = date("Y-m-d H:i:s", $datetime['end']);
$calendar_end_field = $columns['' . $circle_temp[1] . ''];
$cirlce_mode_array_temp[++$i - 1] = "{$calendar_end_field}<'{$timeline}'";
break;
case 'calendar_type':
$calendar_type_field = $columns['' . $circle_temp[1] . ''];
break;
case 'calendar_content':
$calendar_content_field = $columns['' . $circle_temp[1] . ''];
break;
case 'link':
//$linkurl_html="<a href=\"?$value=$_GET[$value]>$columns[''.$circle_temp[1].''];
break;
}
}
print_R($cirlce_mode_array_temp);
exit;
if (sizeof($cirlce_mode_array_temp) >= 1) {
$filter_foreign_index = join(" and ", $cirlce_mode_array_temp);
$filter_foreign_index_body = " and " . $filter_foreign_index;
}
}
//End if -- hidden_field end
//print $filter_foreign_index_body;
//print $uniquekey_sql;
//客户资源部分用户权限说明--开始
$returnCustomerSQL = returnCustomerSQL();
//print $returnCustomerSQL;exit;
//客户资源部分用户权限说明--结束
//print $filter_foreign_index;
//决定是否进行WHERE语句包含
if ($hidden_field != '' && sizeof($cirlce_mode_array_temp) >= 1) {
//print $filter_foreign_index;
$filter_foreign_index = "where " . $filter_foreign_index;
if ($returnCustomerSQL != "") {
$filter_foreign_index = $filter_foreign_index . " and " . $returnCustomerSQL;
}
//print $filter_foreign_index;
} else {
if ($returnCustomerSQL != "") {
//print $filter_foreign_index;
$substr = substr(trim($filter_foreign_index), 0, 5);
if ($substr == "where") {
$filter_foreign_index = $filter_foreign_index . " and " . $returnCustomerSQL;
} else {
$filter_foreign_index = "where " . $returnCustomerSQL;
}
}
}
//print $filter_foreign_index;exit;
//2009-12-9日增加,主要用于客户前台页面SQL语句定制
global $SYSTEM_ADD_SQL;
if ($SYSTEM_ADD_SQL != "") {
$substr = substr(trim($filter_foreign_index), 0, 5);
if ($substr == "where") {
$filter_foreign_index .= " " . $SYSTEM_ADD_SQL . " ";
} else {
$filter_foreign_index = "where 1=1 " . $SYSTEM_ADD_SQL . " ";
}
}
//print $filter_foreign_index;exit;
//求和函数：开始
global $UserSumFunction;
$sumIndexName = $columns[$UserSumFunction];
//求和函数：结束
$temp_select_sql = "" . join(',', $temp_array) . "";
$temp_select_sql = "{$primarykey_index}," . $temp_select_sql;
$temp_update_sql = join(",", $temp_update);
//$temp_update_sql=substr($temp_update_sql,0,strlen($temp_update_sql)-1);
$temp_array_get = explode('_', $_GET['action']);
if ($temp_array_get[0] == 'init' || $temp_array_get[0] == 'export') {
$uniquekey_sql_ = "select {$temp_select_sql} from {$tablename} {$filter_foreign_index}";
// order by $primarykey_index DESC
$uniquekey_sql = "select {$temp_select_sql} from {$tablename} {$filter_foreign_index}";
$uniquekey_sql_num = "select count({$primarykey_index}) as num from {$tablename} {$filter_foreign_index}";
$uniquekey_sql_num_get = "select count({$primarykey_index}) as num from {$tablename} {$filter_foreign_index}";
$uniquekey_sql_sum_get = "select sum(___) as sum from {$tablename} {$filter_foreign_index}";
$uniquekey_sql_search = "select {$temp_select_sql} from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' {$SYSTEM_ADD_SQL} " . $filter_foreign_index_body . "";
//print $uniquekey_sql_search;exit;
$uniquekey_sql_num_search = "select count({$primarykey_index}) as num from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' {$SYSTEM_ADD_SQL} " . $filter_foreign_index_body . "";
$uniquekey_sql_sum_search = "select sum(___) as sum from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' {$SYSTEM_ADD_SQL} " . $filter_foreign_index_body . "";
//得到where后面的语句
$action_array = explode('_', $_GET['action']);
if ($action_array[2] == 'search') {
$uniquekey_sql_search_array = explode("where ", $uniquekey_sql_search);
$where_sql = " from {$tablename} where " . $uniquekey_sql_search_array[1];
} else {
$uniquekey_sql_array = explode("where ", $uniquekey_sql);
$where_sql = " from {$tablename} where " . $uniquekey_sql_array[1];
}
} else {
$uniquekey_sql_ = "select {$temp_select_sql} from {$tablename} where {$uniquekey_sql} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_num = "select count({$primarykey_index}) as num from {$tablename} where {$uniquekey_sql} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_get = "select {$temp_select_sql} from {$tablename} where {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_num_get = "select count({$primarykey_index}) as num from {$tablename} where {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_sum_get = "select sum(___) as sum from {$tablename} where {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_search = "select {$temp_select_sql} from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' and {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_num_search = "select count({$primarykey_index}) as num from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' and {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
$uniquekey_sql_sum_search = "select sum(___) as sum from {$tablename} where " . trim($_GET['searchfield']) . " like '%" . trim($_GET['searchvalue']) . "%' and {$uniquekey_sql_get} {$SYSTEM_ADD_SQL} ";
}
//某些字段需关联查询
if (trim($_GET['searchvalue']) != '') {
$key = array_search(trim($_GET['searchfield']), $fields['name']);
$filter_array = explode(":", $type[$key]);
if ($filter_array[0] == 'tablefiltercolor' || $filter_array[0] == 'tablefilter') {
global $db;
$foreigncolumns = returntablecolumn($filter_array[1]);
$insql = "select " . $foreigncolumns[$filter_array[2]] . " from " . $filter_array[1] . " where " . $foreigncolumns[$filter_array[3]] . " like '%" . trim($_GET['searchvalue']) . "%'";
$uniquekey_sql_search_array = explode(" where ", $uniquekey_sql_search);
$otherwhereArray = explode(" and ", $uniquekey_sql_search_array[1]);
unset($otherwhereArray[0]);
$otherwhere = join(" and ", $otherwhereArray);
if ($otherwhere != '') {
$otherwhere = " and " . $otherwhere;
}
$uniquekey_sql_search = $uniquekey_sql_search_array[0] . " where " . trim($_GET['searchfield']) . " in (" . $insql . ") {$otherwhere} {$SYSTEM_ADD_SQL}";
$uniquekey_sql_search_array = explode(" where ", $uniquekey_sql_num_search);
$uniquekey_sql_num_search = $uniquekey_sql_search_array[0] . " where " . trim($_GET['searchfield']) . " in (" . $insql . ") {$otherwhere} {$SYSTEM_ADD_SQL}";
$uniquekey_sql_search_array = explode(" where ", $uniquekey_sql_sum_search);
$uniquekey_sql_sum_search = $uniquekey_sql_search_array[0] . " where " . trim($_GET['searchfield']) . " in (" . $insql . ") {$otherwhere} {$SYSTEM_ADD_SQL}";
}
}
if (strlen($filter_foreign_index) > 6) {
//$markread_value_add=1:$markread_value_add=0;
$markread_sql = "update {$tablename} set {$markread_field}='{$markread_value_add}' {$filter_foreign_index}";
$markadd_sql = "update {$tablename} set {$markread_field}={$markread_field}+1 {$filter_foreign_index}";
$markread_sql_addusername = "******";
}
if ($hidden_field != '') {
$add_index_sql = $filter_foreign_index;
} else {
$add_index_sql = "where {$primarykey_index}='{$_GET[$primarykey_index]}'";
}
$update_sql = "update {$tablename} set {$temp_update_sql} {$add_index_sql}";
$delete_sql = "delete from {$tablename} {$add_index_sql}";
global $delete_attribute;
global $_GET;
if (isset($delete_attribute) && $delete_attribute != "") {
$array_temp = explode('_', $_GET['returnmodel']);
$delete_attribute_array = explode(':', $delete_attribute);
//print $delete_attribute;
$delete_index = $delete_attribute_array[0];
//print_R($delete_attribute_array);
$delete_attribute_value = $delete_attribute_array[2];
$update_fixed = $columns[$delete_index];
$update_fixed_field_sql = "update {$tablename} set {$update_fixed}='{$delete_attribute_value}' {$add_index_sql}";
}
$select_sql = "select {$temp_select_sql} from {$tablename} {$add_index_sql}";
$search_sql = "select * from {$tablename} where \$_POST[search_field] like '%{$_POST['search_value']}%'";
$return_sql_line['insert_sql'] = $insert_sql;
$return_sql_line['temp_var'] = $temp_var;
$return_sql_line['update_sql'] = $update_sql;
$return_sql_line['markread_sql'] = $markread_sql;
$return_sql_line['markadd_sql'] = $markadd_sql;
$return_sql_line['markread_sql_addusername'] = $markread_sql_addusername;
$return_sql_line['delete_sql'] = $delete_sql;
$return_sql_line['search_sql'] = $search_sql;
$return_sql_line['where_sql'] = $where_sql;
$return_sql_line['select_sql'] = $select_sql;
//print $uniquekey_sql_;
$return_sql_line['uniquekey_sql'] = $uniquekey_sql_;
$return_sql_line['uniquekey_sql_get'] = $uniquekey_sql;
$return_sql_line['uniquekey_sql_num'] = $uniquekey_sql_num;
$return_sql_line['uniquekey_sql_num_get'] = $uniquekey_sql_num_get;
$return_sql_line['uniquekey_sql_sum_get'] = $uniquekey_sql_sum_get;
$return_sql_line['update_fixed_field_sql'] = $update_fixed_field_sql;
$return_sql_line['uniquekey_sql_search'] = $uniquekey_sql_search;
$return_sql_line['uniquekey_sql_num_search'] = $uniquekey_sql_num_search;
$return_sql_line['uniquekey_sql_sum_search'] = $uniquekey_sql_sum_search;
//return fields
$return_sql_line['calendar_begin_field'] = $calendar_begin_field;
$return_sql_line['calendar_end_field'] = $calendar_end_field;
$return_sql_line['calendar_type_field'] = $calendar_type_field;
$return_sql_line['calendar_content_field'] = $calendar_content_field;
return $return_sql_line;
}
