<% Server.ScriptTimeOut = 60 * 5 Dim SQL,Rcset,Rcset2 Dim COMMAND : COMMAND = Request("COMMAND") Dim ObjCmd : set ObjCmd = Server.CreateObject("ADODB.Command") Dim JSON Dim iCnt,cCnt Dim Field Dim CODE,QCODE,IDKEY,SURVEYTYPE CODE = request("CODE") QCODE = request("QCODE") IDKEY = request("IDKEY") SURVEYTYPE = request("SURVEYTYPE") Set Rcset = Server.CreateObject("ADODB.RecordSet") Set Rcset2 = Server.CreateObject("ADODB.RecordSet") Call DBConnect2() SELECT CASE COMMAND CASE "PRJINFO" response.ContentType = "application/json" SQL = " select JSON_QUERY(PROJECTJSON , '$.must') as ix from kon.dbo.PROJECT with(nolock) where PKEY = '"& CODE &"' " Rcset.Open SQL,DBConn,3,1,1 Response.Write Rcset("ix") Response.end CASE "GROUP" response.ContentType = "application/json" With ObjCmd .ActiveConnection = DBConn .CommandText = "kon.dbo.question_group" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@PKEY", adVarWChar, adParamInput, 20) .Parameters.Append .CreateParameter("@GID", adVarWChar, adParamInput, 100) .Parameters("@PKEY") = CODE .Parameters("@GID") = REQUEST("GROUP") Set Rcset = .execute End With While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end CASE "ROTA" SQL = " SELECT DISTINCT STUFF(( select ',' + QID from kon.dbo.QUESTION where PKEY = '" & CODE & "' and JSON_VALUE(QUESTIONJSON ,'$.rota') = '" & request("ROTA") & "' order by newID() FOR XML PATH('')),1,1,'') AS ROTA " Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN ELSE response.write Rcset(0) END IF CASE "GETROTAPAGE" response.write getRotationEndPage(request("ROTA"),CODE) CASE "QINFO" ' 문항 정보 - 저장 프로시저' response.ContentType = "application/json" With ObjCmd .ActiveConnection = DBConn .CommandText = "kon.dbo.nowpage_qinfo" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@PKEY", adVarWChar, adParamInput, 20) .Parameters.Append .CreateParameter("@IDKEY", adVarWChar, adParamInput, 100) .Parameters.Append .CreateParameter("@QID", adVarWChar, adParamInput, 20) .Parameters("@PKEY") = CODE .Parameters("@IDKEY") = IDKEY .Parameters("@QID") = QCODE Set Rcset = .execute End With While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end CASE "LINFO" ' LANGUAGE 정보 ' SQL = " select LTYPE,LCODE,[text] from kon.dbo.LANGUAGE with(nolock) where PKEY = '"&CODE&"' and QID = '"&QCODE&"' and CCODE = '"& REQUEST("LANGUAGE") &"' FOR JSON AUTO " Rcset.Open SQL,DBConn,3,1,1 While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end CASE "LINFOLIST" ' LANGUAGE 정보 -변환용 ' SQL = " select QID,LCODE,[text] from kon.dbo.LANGUAGE with(nolock) where PKEY = '"&CODE&"' and QID in (" & REQUEST("QLIST")& ") and CCODE = '"& REQUEST("LANGUAGE") &"' and LTYPE in ('single','multi','scale') FOR JSON AUTO " Rcset.Open SQL,DBConn,3,1,1 While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end CASE "LINFOLISTALL" ' LANGUAGE 정보 - 그룹 문항 변경용 ' SQL = " select QID,LTYPE,LCODE,[text] from kon.dbo.LANGUAGE with(nolock) where PKEY = '"&CODE&"' and QID in (" & REQUEST("QLIST")& ") and CCODE = '"& REQUEST("LANGUAGE") &"' FOR JSON AUTO " Rcset.Open SQL,DBConn,3,1,1 While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end CASE "GETDATA" ' 사용자가 입력한 데이터 가져오기 ' response.write ReadUTF_File("/log/"&CODE&"/JSON/"&IDKEY&".json") CASE "GETDATA_DB" response.ContentType = "application/json" SQL = "" SQL = SQL &" SELECT json_query(N'{'+STRING_AGG('""'+DV.[KEY]+'"":""'+DV.[VALUE]+'""', ',')+'}') as USERDATA"&vbcrlf SQL = SQL &" FROM [Kon].[dbo].[DATA] DA with(nolock)"&vbcrlf SQL = SQL &" cross apply openjson(DA.[DATJSON]) DJ"&vbcrlf SQL = SQL &" cross apply openjson(DJ.[VALUE]) DV"&vbcrlf SQL = SQL &" where PKEY='"&CODE&"' and [IDKEY] ='" & IDKEY & "'"&vbcrlf Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN response.write "[]" ELSE response.write "[" & Rcset(0) & "]" END IF Response.end CASE "ERROR_REPORT" SQL = "INSERT INTO kon.dbo.ERROR values('"&CODE&"','"& Request("IDKEY") &"', '"&QCODE&"', '"& Replace(Request("ERRSTRING"),"'","´") &"', getDate() )" DBConn.Execute SQL CASE "GETLINFO" response.ContentType = "application/json" With ObjCmd .ActiveConnection = DBConn .CommandText = "kon.dbo.question_label" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@PKEY", adVarWChar, adParamInput, 20) .Parameters.Append .CreateParameter("@QARR", adVarWChar, adParamInput, 20) .Parameters("@PKEY") = CODE .Parameters("@QARR") = REQUEST("QLIST") Set Rcset = .execute End With While Not Rcset.eof response.write Rcset(0) Rcset.movenext WEND Response.end IF Rcset.RecordCount > 0 THEN While Not Rcset.eof response.write Rcset(0) Rcset.movenext : Wend ELSE Response.Write "{}" END IF response.end CASE "SETDATA" ' 사용자가 입력한 데이터 입력하기 ' SAVEJSON "/log/" & CODE & "/JSON/" & IDKEY &".json" , REQUEST("DAT") CASE "ATTENDEE" response.ContentType = "application/json" SQL = " select USER_INFO_JSON from kon.dbo.KON_GANG_TB with(nolock) where PKEY_CD = '"&CODE&"' and USER_ID = '" & Request("ID") & "' and USER_PW = '"& Request("PW") &"' " Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN response.write "[]" ELSE response.write "[" & Rcset("USER_INFO_JSON") & "]" END IF Response.end CASE "GET_CATEGORY_CODE" '시, 구/ 브랜드, 모델 같은 카테고리별 하위 코드 반환' response.ContentType = "application/json" DIM MAINCODE : MAINCODE = request("MAINCODE") DIM CODEDEPTH : CODEDEPTH = request("CODEDEPTH") IF CODEDEPTH = "" THEN 'CODEDEPTH가 없으면 가장 상위 DEPTH로 설정' CODEDEPTH = "0" END IF IF MAINCODE = "" THEN 'MAINCODE가 없으면 MAIN 코드 리스트 반환' SQL = " SELECT MAIN_CODE1, MAIN_CODE2, MAIN_NAME FROM CATEGORY_CODE_LIST WHERE PKEY ='" & CODE & "' AND QID='" & Request("QID") & "' AND CODE_DEPTH = " & CODEDEPTH & " GROUP BY MAIN_CODE1, MAIN_CODE2, MAIN_NAME ORDER BY MAIN_CODE1; " ELSE SQL = " SELECT SUB_CODE1, SUB_CODE2, SUB_NAME FROM CATEGORY_CODE_LIST WHERE PKEY ='" & CODE & "' AND QID='" & Request("QID") & "' AND CODE_DEPTH = " & CODEDEPTH & " AND MAIN_CODE1 = " & MAINCODE & " GROUP BY SUB_CODE1, SUB_CODE2, SUB_NAME ORDER BY SUB_CODE1; " END IF Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN response.write "[]" ELSE response.write RecordToJson(Rcset) END IF Response.end CASE "WORD_LIST" '오픈문항과 문항내용 일치 시 value로 반환' DIM TEXT : TEXT = request("TEXT") response.ContentType = "application/json" SQL = "SELECT word_value FROM Wordtable where pkey='"& CODE &"' and LOWER(replace(word_text,' ','')) = '"& TEXT &"'" Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN response.write "[]" ELSE response.write RecordToJson(Rcset) END IF Response.end CASE "LISTDATA" response.ContentType = "application/json" SQL = " select USER_INFO_JSON from kon.dbo.KON_LIST_TB with(nolock) where PKEY_CD = '"&CODE&"' and IDKEY = '" & Request("ID") & "' " Rcset.Open SQL,DBConn,3,1,1 IF Rcset.eof or Rcset.bof THEN response.write "[]" ELSE response.write "[" & Rcset("USER_INFO_JSON") & "]" END IF Response.end CASE "CHECK_QUOTA" DIM QSORDER : QSORDER = request("QSORDER") SQL = "SELECT distinct QJ.[KEY] AS QID FROM [QUOTA] QT cross apply openjson([QUOTAJSON]) QJ where pkey = '" & CODE & "' and QSORDER = " & QSORDER Rcset.Open SQL,DBConn,3,1,1 IF Rcset.RecordCount > 0 THEN DIM QSQL : QSQL = "" DIM KSQL : KSQL = "" DIM TSQL : TSQL = "" QSQL = QSQL & " select sum(case when IS_USE = 1 then cnt else 0 end) as [cnt], sum(case when IS_USE = 1 then COM else 0 end) as [com]" & vbcrlf QSQL = QSQL & " from (" & vbcrlf QSQL = QSQL & " SELECT QUOTAJSON, CNT,COUNT(DT.IDKEY) AS [com], IS_USE " & vbcrlf QSQL = QSQL & " FROM [QUOTA] QT " & vbcrlf KSQL = KSQL & " INNER JOIN [DATA] DK ON QT.PKEY = DK.PKEY AND DK.IDKEY = '" & IDKEY & "'" & vbcrlf TSQL = TSQL & " LEFT JOIN [DATA] DT ON QT.PKEY = DT.PKEY AND DT.Status = '10' AND DT.SURVEY_TYPE != 'TEST'" & vbcrlf While Not Rcset.eof KSQL = KSQL & " AND case when JSON_VALUE(DK.[DATJSON], '$." & Rcset("QID") & "." & Rcset("QID") & "') != '' then JSON_VALUE(DK.[DATJSON], '$." & Rcset("QID") & "." & Rcset("QID") & "') else JSON_VALUE(QT.QUOTAJSON, '$." & Rcset("QID") & "') end = JSON_VALUE(QT.QUOTAJSON, '$." & Rcset("QID") & "')" & vbcrlf TSQL = TSQL & " AND JSON_VALUE(DT.[DATJSON], '$." & Rcset("QID") & "." & Rcset("QID") & "') = JSON_VALUE(QT.QUOTAJSON, '$." & Rcset("QID") & "') " & vbcrlf Rcset.movenext : Wend QSQL = QSQL & KSQL & TSQL QSQL = QSQL & " WHERE QT.PKEY = '" & CODE & "' AND QT.QSORDER = " & QSORDER QSQL = QSQL & " group by QUOTAJSON, CNT, IS_USE" & vbcrlf QSQL = QSQL & " ) a" Rcset2.Open QSQL,DBConn,3,1,1 IF Cint(Rcset2("cnt")) <= Cint(Rcset2("com")) THEN Response.Write "2" ELSE Response.Write "1" END IF ELSE Response.Write "1" END IF Response.end ' 권민서 전체부수 제어 쿼리 추가 CASE "ENDCount" SQL = "SELECT ISNULL(COUNT(*),0) as cnt FROM Kon.dbo.[DATA] WHERE Status = '10' AND SURVEY_TYPE != 'TEST' AND PKEY ='" & CODE & "';" Rcset.Open SQL, DBConn, 3, 1, 1 IF Rcset.eof OR Rcset.bof THEN Response.Write "0" ELSE Response.Write Rcset("cnt") END IF Response.End CASE ELSE END SELECT call DBClose() %>