<%
' +----------------------------------------------------------------------
' | POPASP [ ASP MVC ]
' +----------------------------------------------------------------------
' | Copyright (c) 2016 http://popasp.com All rights reserved.
' +----------------------------------------------------------------------
' | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
' +----------------------------------------------------------------------
' | Author: popasp <1737025626@qq.com>
' +----------------------------------------------------------------------

'在本类中已经使用了Replace作为方法名，所以在本类中不能再使用Replace函数了，请考虑使用正则的Replace方法吧。
Class POPASP_EXCEL
	'为了减轻数据库操作类的重量，特将相同代码写到了另外一个文件
	'POPASP_REPLACE_CLASS_CODE_POPASP'
	
	'如果为excel数据库，则删除sql改为修改sql，将每个字段值设为Null，则会将该行删除
	Function DeleteBySql(table,where)
		dim dict,sql
		table = getTableFromInput(table)
		where = getWhere( table,where )	
		set dict = POP_MVC.Dict.Clone( getTableStructure( table ) )
		sql = "UPDATE [" & table & "] SET " 
		for each key in dict
			sql = sql & "[" & key  & "]" & " = Null ,"
		next
		sql = mid( sql,1,len(sql)-1 )	'去掉末尾的逗号
		sql = sql & " WHERE " & where		
		lastSql = sql
		if isOnlySql then DeleteBySql = lastSql : Exit Function
		DeleteBySql = tool.Save( lastSql )
	End Function
	
	' 获取表的所有字段与字段类型
	Function getTableFields( tableName )
		set getTableFields = tool.getTableFields("SELECT TOP 1 * FROM [" & getTableName(tableName) & "]" )
	End Function
	
	Function InsertByExecute( table,sql )
		InsertByExecute = tool.Execute( sql )
	End Function
	
	' 向数据表中插入数据，data为Dictionary对象，其键名与字段名相对应，如果data含主键，须手动删除
	Public Function Insert(ByVal table,ByVal data)		
		table = getTableFromInput(table)		
		call handlerData( table,data , 0 ) '剔除不存在的字段		
		call set_increment_id( table,data )
		lastSql = tool.getInsertSql( table, data , getTableStructure( table ) )
		Insert = InsertByExecute( table , lastSql )
	End Function
	
	' 向数据表中添加数据
	Public Property Get Add()
		call parseOptions	'解析参数		
		if is_empty(parsedOptions("table")) then 
			Call setPoptsTable( parsedOptions )	
		end if
		
		if isEmpty( parsedOptions("data") ) Then
			set parsedOptions("data") = POP_MVC.Form2Dict()
		end If
		
		if ( not is_empty( parsedOptions("data") ) ) Then
			'剔除不存在的字段
			call handlerData( parsedOptions("table"),parsedOptions("data") , 0 )
			
			dim data
			set data = parsedOptions("data")
			call set_increment_id( parsedOptions("table"), data )
			set parsedOptions("data") = data
			lastSql = getInsertSql(parsedOptions)
			Add = InsertByExecute( parsedOptions("table") , lastSql )
		else 
			Call POP_MVC.error( "POPASP_EXCEL.Add" )
		end If		
		call ResumeOpts
	End Property
	
	' Replace，无则添加，有则修改，此时where无效，返回结果是最后添加或修改的ID
	Public Property Get [Replace]()
		On Error Resume Next
		dim popts,pk,max,findCount	
		call parseOptions	'解析参数		
		set popts = parsedOptions
		
		if is_empty(popts("table")) then
			Call setPoptsTable( popts )	
		end if
		
		pk = getPrikey( popts("table") )	'得到主键
		if popts("data").Exists( pk ) Then	'如果数据中存在主键，则需要从最大ID与是否存在该ID两方面判断
			set rs = getRS( "SELECT TOP 1 MAX(" & pk & ") AS theResult FROM [" & popts("table") & "]")
			rs.moveFirst
			max = rs("theResult")	'得到最大ID
			tool.closeRS rs
			set rs = getRS( "SELECT TOP 1 * FROM [" & popts("table") & "] WHERE " & pk & " = " & popts("data")(pk) )
			findCount = rs.RecordCount	'以该ID查找一行记录，找到为1，找不到为0
			tool.closeRS rs
			if max >= popts("data")(pk) then	'数据ID <= 最大ID 
				if findCount > 0 then	'如果能找到，则修改
					call options.Remove("where")	
					[Replace] = popts("data")(pk)	'返回实际修改的ID
					if Save() = 0 then
						[Replace] = 0
					end if
				else	'如果找不到，插入到数据ID的位置上。返回该数据ID
					[Replace] = Add()
				end if
			else	' 超出最大ID，则添加，如果数据表被全部删除，此时生成的ID并不是1
				call options("data").Remove(pk)
				[Replace] = Add()
			end if			
		else	'不存在主键，直接添加
			[Replace] = Add()
		end if		
		call L_( Me.db_type & " Replace" )
	End Property
	
	' 得到数据库中的所有表名，返回一个对象
	' 例： {"0":"about", "1":"category", "2":"contact", "3":"feedback", "4":"post", "5":"swiper", "6":"user"}
	Public Function getTables()
		on error resume next
		dim shm,arr,content
		dim filename
		' 如果已经保存了数据，直接返回
		if not isEmpty(dTables) Then
			set getTables = dTables
			Exit Function
		End If
		'如果对应文件不存在，则须先将数据生成json保存到文件中
		filename = tool.getDataFileName("_tables_")
		if Not is_empty( C_("APP_DEBUG") ) OR Not tool.file_exists( filename ) Then
			tool.excelConnectMode = 0			
			set tool.conn = nothing			
			call tool.initConn		
			Set shm = tool.conn.OpenSchema(20)
			shm.MoveFirst 
			Do While Not shm.EOF 
				If POP_MVC.String.iExists(shm("TABLE_TYPE"),"TABLE") Then
					POP_MVC.Arr.Push arr, LCase(POP_MVC.String.trim(shm("TABLE_NAME"),"'") )
				End If
				shm.MoveNext 
			Loop
			tool.closeRS shm
			set tool.conn = nothing
			tool.excelConnectMode = 1
			call tool.file_put_contents(filename, join(arr,",") )
		else	'从文件中取出数据
			content = POP_MVC.file_get_contents(tool.getFilePath(filename))
			content = mid(content,2)
			arr = split(content,",")
		End If
		set dTables = POP_MVC.Arr.toDict(arr)
		set getTables = dTables		
		Call L_("POPASP_EXCEL.getTables")
	End Function	
	
	'获取数据表结构
	Public Function getTableStructure( ByRef tableName )
		call getTables	
		set getTableStructure = tool.getTableStructure( getTableName(tableName),dTables,dTS,"SELECT TOP 1 * FROM [" & getTableName(tableName) & "]")
	End Function
	
	'
	Public Function getFindSql( byref popts )
		popts("top") = 1 : getFindSql = getSelectSql(popts)
	End Function
	
	Function getTableName( ByVal table_name )
		if table_name = "" Then table_name = tableName
		getTableName = tool.getExcelTableName( table_name )
	End Function
	
	Function set_increment_id( ByVal tableName , ByRef data )
		dim rs,sql
		call getPK()
		if not is_empty(prikey) Then
			sql = "SELECT max(" & prikey & ") as popasp_result FROM [" & tableName & "]" & " WHERE NOT ISNULL([" & prikey & "])"
			set rs = getRS(sql)			
			if Not rs.eof then
				data(prikey) = CInt( rs( "popasp_result" ) ) + 1
			else
				data(prikey) = 1
			end if
			set dict = nothing
			tool.closeRS rs
		End if
	End Function
	
End Class
%>