Files
poll-recruit/Recruit/Lib/DBLib.asp
2025-10-01 09:29:34 +09:00

857 lines
26 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<%
'###########################################################################################
'정규식을 이용하여 문자열을 치환하는 함수
'###########################################################################################
Public Function eregi_replace(ByVal pattern, ByVal replacestr, ByVal text)
Dim eregObj
'** Create regular expression
Set eregObj = New RegExp
eregObj.Pattern = pattern '** Set Pattern(패턴 설정)
eregObj.IgnoreCase = True '** Set Case Insensitivity(대소문자 구분 여부)
eregObj.Global = True '** Set All Replace(전체 문서에서 검색)
eregi_replace = eregObj.Replace(text, replacestr) '** Replace String
End Function
'###########################################################################################
'데이터입력시 쿼리에 영향주는 문자 제거
'###########################################################################################
Function checkValue(inWord)
inWord = eregi_replace("'", "´", inWord)
inWord = eregi_replace("'", "&#039;", inWord)
inWord = eregi_replace("--", "", inWord)
inWord = eregi_replace("\@variable ", "", inWord)
inWord = eregi_replace("\@@variable ", "", inWord)
inWord = eregi_replace("\+", "", inWord)
inWord = eregi_replace("print ", "", inWord)
inWord = eregi_replace("set ", "", inWord)
inWord = eregi_replace("union ", "", inWord)
inWord = eregi_replace("insert ", "", inWord)
inWord = eregi_replace("select ", "", inWord)
inWord = eregi_replace("update ", "", inWord)
inWord = eregi_replace("create ", "", inWord)
inWord = eregi_replace("drop ", "", inWord)
inWord = eregi_replace("openrowset ", "", inWord)
inWord = eregi_replace("exec ", "", inWord)
inWord = eregi_replace("§", "", inWord)
inWord = eregi_replace("∑", "", inWord)
inWord = eregi_replace("∮", "", inWord)
checkValue = inWord
End Function
'###########################################################################################
'값 찾아서 가져오기
'###########################################################################################
Function getValue(fieldNm)
Dim TB,SQL
Dim rsField
If checkField(fieldNm,TB_NAME) Then
TB=TB_NAME
ElseIf checkField(fieldNm,TB_NAME1) Then
TB=TB_NAME1
ElseIf checkField(fieldNm,TB_NAME2) Then
TB=TB_NAME2
End If
DBConnect()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select " & fieldNm & " from " & TB & " WHERE [KEY]='" & Request.Form("IDKEY") & "'"
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
getValue=""
else
getValue = rsField(0)
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'Project DB SQL 쿼리 실행
'###########################################################################################
Function ExecuteSQL(SQL)
If SQL="" then
ExecuteSQL = true
Exit Function
end If
ExecuteSQL=TExecuteSQL(SQL)
End Function
'###########################################################################################
'설문 DB SQL 쿼리 실행
'###########################################################################################
Function ExecuteSQL1(SQL)
If SQL="" then
ExecuteSQL1 = true
Exit Function
end If
ExecuteSQL1=TExecuteSQL1(SQL)
End Function
'###########################################################################################
'설문 DB SQL 쿼리 실행 트랜잭션 제거
'###########################################################################################
Function ExecuteSQL2(SQL)
If SQL="" then
ExecuteSQL2 = true
Exit Function
end If
DBConnect1()
DBConn.Execute SQL
DBClose()
ExecuteSQL2 = True
End Function
'###########################################################################################
'Project DB SQL 쿼리 실행
'###########################################################################################
Function TExecuteSQL(SQL)
If SQL="" then
TExecuteSQL = true
Exit Function
end If
On Error Resume Next
DBConnect()
DBConn.BeginTrans
DBConn.Execute SQL
If DBConn.Errors.Count > 0 Then
Response.Write SQL & "<BR>"
for each objErr in DBConn.Errors
response.write("<p>")
response.write("Description: ")
response.write(objErr.Description & "<br>")
response.write("Help context: ")
response.write(objErr.HelpContext & "<br>")
response.write("Help file: ")
response.write(objErr.HelpFile & "<br>")
response.write("Native error: ")
response.write(objErr.NativeError & "<br>")
response.write("Error number: ")
response.write(objErr.Number & "<br>")
response.write("Error source: ")
response.write(objErr.Source & "<br>")
response.write("SQL state: ")
response.write(objErr.SQLState & "<br>")
response.write("</p>")
Next
TExecuteSQL = False
DBConn.RollbackTrans
DBClose()
Else
TExecuteSQL = True
DBConn.CommitTrans
DBClose()
End IF
End Function
'###########################################################################################
'설문 DB SQL 쿼리 실행
'###########################################################################################
Function TExecuteSQL1(SQL)
If SQL="" then
TExecuteSQL1 = true
Exit Function
end If
DBConnect1()
DBConn.BeginTrans
On Error Resume Next
DBConn.Execute SQL
If DBConn.Errors.Count > 0 Then
Response.Write SQL & "<BR>"
for each objErr in DBConn.Errors
response.write("<p>")
response.write("Description: ")
response.write(objErr.Description & "<br>")
response.write("Help context: ")
response.write(objErr.HelpContext & "<br>")
response.write("Help file: ")
response.write(objErr.HelpFile & "<br>")
response.write("Native error: ")
response.write(objErr.NativeError & "<br>")
response.write("Error number: ")
response.write(objErr.Number & "<br>")
response.write("Error source: ")
response.write(objErr.Source & "<br>")
response.write("SQL state: ")
response.write(objErr.SQLState & "<br>")
response.write("</p>")
next
TExecuteSQL1 = False
DBConn.RollbackTrans
DBClose()
Else
TExecuteSQL1 = True
DBConn.CommitTrans
DBClose()
End IF
End Function
'###########################################################################################
'Table내의 필드이름 확인
'###########################################################################################
Function checkField(fieldNm,tableNm)
Dim Tvars,SQL
Dim rsField
Tvars = replace(tableNm,"[","")
Tvars = replace(Tvars,"]","")
DBConnect()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = '" & Trim(Tvars) &"' and column_name='" & trim(fieldNm) & "'"
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
checkField=false
else
checkField=true
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'Table내의 필드이름 확인
'복수응답, 속성 필드 이름 가져오기
'###########################################################################################
Function getFieldNames(fieldNm,tableNm)
Dim Tvars,SQL
Dim rsField
Tvars = replace(tableNm,"[","")
Tvars = replace(Tvars,"]","")
DBConnect()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = '" & Trim(Tvars) &"' and column_name LIKE '" & trim(fieldNm) & "_%'"
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
getFieldNames=""
else
getFieldNames = RsField.GetString(2)
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'Table내의 필드이름 확인
'복수응답, 속성 필드 이름 가져오기
'###########################################################################################
Function getFieldNames1(fieldNm,tableNm)
Dim Tvars,SQL
Dim rsField
Tvars = replace(tableNm,"[","")
Tvars = replace(Tvars,"]","")
DBConnect1()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = '" & Trim(Tvars) &"' and column_name LIKE '" & trim(fieldNm) & "#_%' ESCAPE '#' "
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
getFieldNames1=""
else
getFieldNames1 = RsField.GetString(2)
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'Table내의 필드이름 확인
'복수응답, 속성 필드 이름 가져오기
'###########################################################################################
Function getFieldNameLike(fieldNm,tableNm)
Dim Tvars,SQL
Dim rsField
Tvars = replace(tableNm,"[","")
Tvars = replace(Tvars,"]","")
DBConnect()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = '" & Trim(Tvars) &"' and column_name LIKE '" & trim(fieldNm) & "_%'"
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
getFieldNameLike=""
else
getFieldNameLike = RsField(0)
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'Table내의 필드이름 확인
'복수응답, 속성 필드 이름 가져오기
'###########################################################################################
Function getFieldNameLike1(fieldNm,tableNm)
Dim Tvars,SQL
Dim rsField
Tvars = replace(tableNm,"[","")
Tvars = replace(Tvars,"]","")
DBConnect1()
Set rsField = Server.CreateObject("ADODB.Recordset")
SQL = "select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = '" & Trim(Tvars) &"' and column_name LIKE '" & trim(fieldNm) & "_%'"
RsField.Open SQL,DBConn,3,1,1
If RsField.Eof or RsField.Bof Then
getFieldNameLike1=""
else
getFieldNameLike1 = RsField(0)
End if
RsField.Close
DBClose()
End Function
'###########################################################################################
'쿼리 결과를 리턴
'###########################################################################################
Public Function getRecordset(ByVal SQL,ByRef RecordSet)
If DBFlag=False Then
DBConnect()
End If
Set Recordset = Server.CreateObject("ADODB.Recordset")
Recordset.Open SQL,DBConn,3,1,1
getRecordset = Recordset
End Function
'###########################################################################################
'쿼리 결과를 리턴 2 스트링 형태로 리턴
'Row는 vbCR 로 Split 하여 사용하고 Col은 vbTab으로 Split 하여 사용
'단순 리스트 조회의 경우 사용 권장
'###########################################################################################
Public Function getRecordString(ByVal SQL)
Dim Recordset
If DBFlag=False Then
DBConnect()
End If
Set Recordset = Server.CreateObject("ADODB.Recordset")
Recordset.Open SQL,DBConn,3,1,1
If Recordset.Eof Or Recordset.Bof Then
getRecordString = ""
Else
getRecordString = Recordset.GetString(2)
End if
Recordset.Close
Set Recordset = Nothing
DBClose()
End Function
'###########################################################################################
'쿼리 결과를 리턴 2 배열 형태로 리턴
'단순 리스트 조회의 경우 사용 권장
'###########################################################################################
Public Function getRecordArray(ByVal SQL)
Dim Recordset
If DBFlag=False Then
DBConnect()
End If
Set Recordset = Server.CreateObject("ADODB.Recordset")
Recordset.Open SQL,DBConn,3,1,1
If Recordset.Eof Or Recordset.Bof Then
getRecordArray = null
Else
getRecordArray = Recordset.getRows
End If
Recordset.Close
Set Recordset = Nothing
DBClose()
End Function
'###########################################################################################
'쿼리 결과를 리턴, 맨 앞의 데이터 첫번째 행 데이터만 가져옴
'단순 리스트 조회의 경우 사용 권장
'###########################################################################################
Public Function getRecordDataCOL(ByVal SQL)
Dim Recordset
If DBFlag=False Then
DBConnect()
End If
Set Recordset = Server.CreateObject("ADODB.Recordset")
Recordset.Open SQL,DBConn,3,1,1
If Recordset.Eof Or Recordset.Bof Then
getRecordDataCOL = null
Else
getRecordDataCOL = Recordset(0)
End If
Recordset.Close
Set Recordset = Nothing
DBClose()
End Function
'###########################################################################################
'XML에 저장하는 함수
'DataSTR은 SQ1=1&SQ1_1=2 이런식으로 "&" 로 데이터를 저장한다.
'###########################################################################################
Function SaveXML(DataSTR)
Dim objXML
Set objXML = New XMLClass
If SURVEYSTATUS<>"3" Then
On Error Resume Next
End IF
Dim VSTR,TSTR
Dim iCnt
If FileChecker(XMLFolder) Then
objXML.Load XMLFolder
End If
If DataSTR="" Then
SaveXML = False
Else
VSTR = Split(DataSTR & DB2STR,DB2STR)
For iCnt=0 To UBound(VSTR)
If VarType(VSTR(iCnt))<>0 And VarType(VSTR(iCnt))<>1 And VSTR(iCnt)<>"" Then
TSTR = Split(VSTR(iCnt),DB1STR)
objXML.SetValue TSTR(0),checkValue(TSTR(1))
End IF
Next
objXML.Save XMLFolder
SaveXML = True
End IF
Set objXML = Nothing
If Err.Number>0 Then
SaveXML = False
End IF
End Function
'###########################################################################################
'로그파일 읽어오기
'###########################################################################################
Function LoadStream(FilePath)
Dim oFSO,wFile
'Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set oFSO = Server.CreateObject("ADODB.Stream")
oFSO.CharSet = "utf-8"
oFSO.Mode = 3
oFSO.Type = 2
oFSO.Open
oFSO.LoadFromFile FilePath
LoadStream = oFSO.ReadText
oFSO.Close
Set oFSO = Nothing
End Function
'###########################################################################################
'Table List 갯수 카운트
'TABLENAME : 테이블명
'FIELDNAME : 키워드 필드
'SearchSQL : 검색 SQL 형태
'###########################################################################################
Function TableCount(TABLENAME,FIELDNAME,SearchSQL)
Dim MLSQL,LCount,IDFIELD
MLSQL = ""
IDFIELD= ""
If FIELDNAME="" Then
IDFIELD = "IDX"
Else
IDFIELD = FIELDNAME
End IF
If SearchSQL="" Then
MLSQL = "SELECT COUNT(["&IDFIELD&"]) FROM ["&TABLENAME&"]"
Else
MLSQL = "SELECT COUNT(["&IDFIELD&"]) FROM ["&TABLENAME&"] WHERE " & SearchSQL
End If
LCount=getRecordDataCOL(MLSQL)
If isNull(LCount) Then
TableCount=0
Else
TableCount=CLng(LCount)
End If
End Function
'###########################################################################################
'List
'TABLENAME : 테이블명
'FIELDNAME : 키워드 필드
'PAGE_SIZE : 화면에 보여질 글자 수
'CU_PAGE : 현재 PAGE 넘버
'LIST_KEY : 정렬용 필드명
'ORDER_TYPE : 정렬 형태
'SearchSQL : 검색 SQL 형태
'###########################################################################################
Function TableList(TABLENAME,FIELDARRAY,PAGE_SIZE,CU_PAGE,LIST_KEY,ORDER_TYPE,SearchSQL)
Dim MLSQL,pagesize,cupage,listkey,ordertype
Dim PSQL,PCnt
MLSQL = ""
If PAGE_SIZE="" Then
pagesize=10
Else
pagesize = CLng(PAGE_SIZE)
End If
If CU_PAGE="" Then
cupage=1
Else
cupage = CLng(CU_PAGE)
End If
If LIST_KEY="" Then
listkey = "IDX"
Else
listkey = LIST_KEY
End If
If ORDER_TYPE="" Then
ordertype = "DESC"
Else
ordertype = ORDER_TYPE
End If
If VarType(FIELDARRAY) = 8192 or VarType(FIELDARRAY) = 8204 or VarType(FIELDARRAY) = 8209 then
PSQL = "SELECT TOP " & pagesize
For PCnt = LBound(FIELDARRAY) To UBound(FIELDARRAY)
PSQL = PSQL & "["& FIELDARRAY(PCnt) & "],"
Next
PSQL = Left(PSQL,Len(PSQL)-1)
Else
PSQL = "SELECT TOP " & pagesize & "[" & LISTKEY & "]"
End If
If CLng(cupage)=1 Then
If SearchType="" And SearchSQL="" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"] ORDER BY [" & listkey & "] " & ordertype
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE " & SearchSQL
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
Else
If SearchType="" And SearchSQL="" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE [MIDX] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " ["& FIELDARRAY(0) &"] FROM ["& TABLENAME &"] ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")"
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE " & SearchSQL
MLSQL = MLSQL & " AND ["& FIELDARRAY(0) &"] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " [" & FIELDARRAY(0) & "] FROM ["& TABLENAME &"] WHERE " & SearchSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")"
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
End If
TableList = getRecordArray(MLSQL)
End Function
'###########################################################################################
'List
'TABLENAME : 테이블명
'FIELDNAME : 키워드 필드
'PAGE_SIZE : 화면에 보여질 글자 수
'CU_PAGE : 현재 PAGE 넘버
'LIST_KEY : 정렬용 필드명
'ORDER_TYPE : 정렬 형태
'SearchSQL : 검색 SQL 형태
'###########################################################################################
Function TableListJS(TABLENAME,FIELDARRAY,PAGE_SIZE,CU_PAGE,LIST_KEY,ORDER_TYPE,SearchSQL)
Dim MLSQL,pagesize,cupage,listkey,ordertype
Dim PSQL,PCnt
MLSQL = ""
If PAGE_SIZE="" Then
pagesize=10
Else
pagesize = CLng(PAGE_SIZE)
End If
If CU_PAGE="" Then
cupage=1
Else
cupage = CLng(CU_PAGE)
End If
If LIST_KEY="" Then
listkey = "IDX"
Else
listkey = LIST_KEY
End If
If ORDER_TYPE="" Then
ordertype = "DESC"
Else
ordertype = ORDER_TYPE
End If
If VarType(FIELDARRAY) = 8192 or VarType(FIELDARRAY) = 8204 or VarType(FIELDARRAY) = 8209 then
PSQL = "SELECT TOP " & pagesize
For PCnt = LBound(FIELDARRAY) To UBound(FIELDARRAY)
PSQL = PSQL & "["& FIELDARRAY(PCnt) & "],"
Next
PSQL = Left(PSQL,Len(PSQL)-1)
Else
PSQL = "SELECT TOP " & pagesize & "[" & LISTKEY & "]"
End If
If CLng(cupage)=1 Then
If SearchType="" And SearchSQL="" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"] ORDER BY [" & listkey & "] " & ordertype
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE " & SearchSQL
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
Else
If SearchType="" And SearchSQL="" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE [MIDX] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " ["& FIELDARRAY(0) &"] FROM ["& TABLENAME &"] ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")"
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"]"
MLSQL = MLSQL & " WHERE " & SearchSQL
MLSQL = MLSQL & " AND ["& FIELDARRAY(0) &"] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " [" & FIELDARRAY(0) & "] FROM ["& TABLENAME &"] WHERE " & SearchSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")"
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
End If
Dim Rs
Call getRecordSet(MLSQL,Rs)
TableListJS = RecordsetToJSON(Rs).jsString
Rs.Close
End Function
'###########################################################################################
'List
'TABLENAME : 테이블명
'FIELDNAME : 키워드 필드
'PAGE_SIZE : 화면에 보여질 글자 수
'CU_PAGE : 현재 PAGE 넘버
'LIST_KEY : 정렬용 필드명
'ORDER_TYPE : 정렬 형태
'SearchSQL : 검색 SQL 형태
'###########################################################################################
Function TableListJO(TABLENAME,FIELDARRAY,PAGE_SIZE,CU_PAGE,LIST_KEY,ORDER_TYPE,SearchSQL,responsibleProject)
Dim MLSQL,pagesize,cupage,listkey,ordertype
Dim PSQL,PCnt
MLSQL = ""
If PAGE_SIZE="" Then
pagesize=10
Else
pagesize = CLng(PAGE_SIZE)
End If
If CU_PAGE="" Then
cupage=1
Else
cupage = CLng(CU_PAGE)
End If
If LIST_KEY="" Then
listkey = "IDX"
Else
listkey = LIST_KEY
End If
If ORDER_TYPE="" Then
ordertype = "DESC"
Else
ordertype = ORDER_TYPE
End If
Dim QCTEAM
'##### QC 팀
IF Session("department") <> "S201" THEN
QCTEAM = " and department <> 'S201' "
ELSE
QCTEAM = ""
END IF
If VarType(FIELDARRAY) = 8192 or VarType(FIELDARRAY) = 8204 or VarType(FIELDARRAY) = 8209 then
PSQL = "SELECT TOP " & pagesize
For PCnt = LBound(FIELDARRAY) To UBound(FIELDARRAY)
PSQL = PSQL & "["& FIELDARRAY(PCnt) & "],"
Next
PSQL = PSQL &"isnull((select username from capi.dbo.Member where userid = a.userid),'') as [username]," '##### 등록자 이름
PSQL = PSQL &"isnull((select T_name from project2.dbo.v_Department where code = a.department), a.department) as [depart]," '##### 그룹명
PSQL = Left(PSQL,Len(PSQL)-1)
Else
PSQL = "SELECT TOP " & pagesize & "[" & LISTKEY & "]"
End If
If CLng(cupage)=1 Then
If SearchType="" And SearchSQL="" AND responsibleProject = "NONE" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"] as a "
IF responsibleProject <> "NONE" and responsibleProject <> "" THEN MLSQL = MLSQL & " WHERE SUBSTRING(code,0,charindex('_',code)) IN ("&responsibleProject&")" & QCTEAM
MLSQL = MLSQL & "ORDER BY [" & listkey & "] " & ordertype
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"] as a "
IF SearchSQL <> "" then MLSQL = MLSQL & " WHERE " & SearchSQL
IF responsibleProject <> "NONE" and responsibleProject <> "" THEN
IF SearchSQL <> "" THEN MLSQL = MLSQL & " and "
MLSQL = MLSQL & " SUBSTRING(code,0,charindex('_',code)) IN ("&responsibleProject&")" & QCTEAM
END IF
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
Else
If SearchType="" And SearchSQL="" AND responsibleProject = "NONE" Then
MLSQL = PSQL & " FROM ["& TABLENAME &"] as a "
MLSQL = MLSQL & " WHERE [ID] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " ["& FIELDARRAY(0) &"] FROM ["& TABLENAME &"] as a ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")" & QCTEAM
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
Else
MLSQL = PSQL & " FROM ["& TABLENAME &"] as a "
MLSQL = MLSQL & " WHERE " & SearchSQL
IF responsibleProject <> "NONE" and responsibleProject <> "" THEN
IF SearchSQL <> "" THEN MLSQL = MLSQL & " and "
MLSQL = MLSQL & " SUBSTRING(code,0,charindex('_',code)) IN ("&responsibleProject&")" & QCTEAM
END IF
MLSQL = MLSQL & " AND ["& FIELDARRAY(0) &"] NOT IN ("
MLSQL = MLSQL & " SELECT TOP " & (cupage-1) * pagesize & " [" & FIELDARRAY(0) & "] FROM ["& TABLENAME &"] as a WHERE " & SearchSQL
IF responsibleProject <> "NONE" and responsibleProject <> "" THEN
IF SearchSQL <> "" THEN MLSQL = MLSQL & " and "
MLSQL = MLSQL & " SUBSTRING(code,0,charindex('_',code)) IN ("&responsibleProject&")" & QCTEAM
END IF
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE & ")"
MLSQL = MLSQL & " ORDER BY [" & LISTKEY & "] " & ORDERTYPE
End If
End If
Dim Rs
Call getRecordSet(MLSQL,Rs)
Set TableListJO = RecordsetToJSON(Rs)
Rs.Close
End Function
Function responsible_Project(name)
Dim ProjectLists : ProjectLists = ""
Dim PSQL
PSQL = "select p_no from project2.dbo.person where p_name='"&name&"' and p_resign='0' and krc_out_date = ''"
Dim Rs,Rs2
Call getRecordSet(PSQL ,Rs)
IF Rs.eof or Rs.bof THEN
responsible_Project = ""
ELSE
PSQL = "select p_sn from project2.dbo.T_PROJECT_METHOD where p_rsch_1_a = '"&Rs("p_no")&"' OR p_rsch_2_a = '"&Rs("p_no")&"' OR p_rsch_3_a = '"&Rs("p_no")&"' OR p_rsch_4_a = '"&Rs("p_no")&"' OR p_rsch_1_b = '"&Rs("p_no")&"' OR p_rsch_2_b = '"&Rs("p_no")&"' OR p_rsch_3_b = '"&Rs("p_no")&"' OR p_rsch_4_b = '"&Rs("p_no")&"' OR p_rsch_1_c = '"&Rs("p_no")&"' OR p_rsch_2_c = '"&Rs("p_no")&"' OR p_rsch_3_c = '"&Rs("p_no")&"' OR p_rsch_4_c = '"&Rs("p_no")&"' OR p_sv_1 = '"&Rs("p_no")&" ' OR p_sv_2 = '"&Rs("p_no")&"' OR p_sv_3 = '"&Rs("p_no")&"' OR p_sv_4 = '"&Rs("p_no")&"' OR p_code_1 = '"&Rs("p_no")&"' OR p_code_2 = '"&Rs("p_no")&"' OR p_code_3 = '"&Rs("p_no")&"' OR p_code_4 = '"&Rs("p_no")&"' OR p_verify_1 = '"&Rs("p_no")&"' OR p_verify_2 = '"&Rs("p_no")&"' OR p_verify_3 = '"&Rs("p_no")&"' OR p_verify_4 = '"&Rs("p_no")&"' OR p_dp_1 = '"&Rs("p_no")&"' OR p_dp_2 = '"&Rs("p_no")&"' OR p_dp_3 = '"&Rs("p_no")&"' OR p_dp_4 = '"&Rs("p_no")&"' OR p_it_1 = '"&Rs("p_no")&"' OR p_it_2 = '"&Rs("p_no")&"' OR p_it_3 = '"&Rs("p_no")&"' OR p_it_4 = '"&Rs("p_no")&"' OR p_supp_1 = '"&Rs("p_no")&"' OR p_supp_2 = '"&Rs("p_no")&"' OR p_supp_3 = '"&Rs("p_no")&"' OR p_supp_4 = '"&Rs("p_no")&"'"
Call getRecordSet(PSQL ,Rs2)
IF Rs2.eof or Rs2.bof then
responsible_Project = ""
ELSE
while not Rs2.eof
IF ProjectLists <> "" THEN ProjectLists = ProjectLists & ","
ProjectLists = ProjectLists & "'"&Rs2("p_sn")&"'"
Rs2.movenext : wend
responsible_Project = ProjectLists
END IF
END IF
End Function
%>