<%@ Language=VBScript %>
<!--#INCLUDE file="ADOVBS.inc"-->
<%
Function getDateTimeFormat(dateTime)
dim monthA, dayA, yearA
monthA = month(dateTime)
if Len(monthA) = 1 then
	monthA = "0" & monthA
end if

dayA = day(dateTime)
if len(dayA) = 1 then
	dayA = "0" & dayA
end if
	
yearA = year(dateTime)

getDateTimeFormat = yearA & "-" & monthA & "-" & dayA & " " & time
End Function

sub sendError(errors)
	dim objErr
	dim str_c
	dim strrr
	for each objErr in errors
		str_c=str_c&"Description: "
		str_c=str_c&objErr.Description  
	  
		str_c=str_c&"Native error: "
		str_c=str_c&objErr.NativeError 
		  
		str_c=str_c&"SQL state: "
		str_c=str_c&objErr.SQLState
	next
	if Len(str_c) > 1 then
		strrr="uti_console.asp?mensaje=" & "El Estado de la conexion antes del insert: \n" & str_c 
		Response.Redirect(strrr)
	end if
end sub

dim str_accion,str_proceso, str_usuario, str_fecha_captura, userMail
dim lon_folio, str_nombre, str_password, int_edad, int_sexo, str_sexo,str_departamento, str_puesto,str_categoria
dim int_dia_apl, int_mes_apl, int_ano_apl, str_fecha_aplica,str_hora, str_empresa1, str_empresa2, str_empresa3
dim str_puesto1, str_puesto2, str_puesto3, str_inicio1, str_inicio2, str_inicio3, str_fin1, str_fin2, str_fin3
dim int_experiencia, str_escolar1, str_escolar2, str_escolar3, str_institucion1,str_institucion2,str_institucion3
dim str_titulo1,str_titulo2,str_titulo3, str_promedio1,str_promedio2,str_promedio3, str_ortografia, str_ingles
dim str_comunicacion,str_comentarios, str_estatus, int_turno, str_division, str_turno, candidateName, candidatePass
dim notificated
dim sqlQuery, rs, conn , str_empresa

notificated = 0
str_empresa=Request.Cookies("sieps")("empresa")
str_usuario=Request.Cookies("sieps")("usuario_clave")

str_accion = Request.form("ACCION")
lon_folio = Request.Form("FOLIO")
str_estatus = Request.form("ESTATUS")

str_nombre = replace(replace(Request.Form("TXT_NOMBRE"),"'"," "),chr(34)," ")
str_password = replace(replace(Request.Form("TXT_PASSWORD"),"'"," "),chr(34)," ")
int_edad = replace(replace(Request.Form("TXT_EDAD"),"'"," "),chr(34)," ")

if not isnumeric(int_edad) then
	int_edad=0
end if
int_sexo = request.Form("RDO_SEXO")
if int_sexo = "2" then
	str_sexo="Mujer"
else
	str_sexo="Hombre"
end if
str_division=Request.Form("SEL_DIVISION")
str_departamento=Request.Form("SEL_DEPARTAMENTO")

str_puesto=Request.Form("SEL_PUESTO")

str_categoria=Request.Form("SEL_CATEGORIA")
str_turno=Request.Form("SEL_TURNO")
int_dia_apl = Request.Form("CBO_DIA_REA")
int_mes_apl = Request.Form("CBO_MES_REA")
int_ano_apl = Request.Form("CBO_ANO_REA")

str_empresa1 = replace(replace(Request.Form("TXT_EMPRESA1"),"'"," "),chr(34)," ")
str_empresa2 = replace(replace(Request.Form("TXT_EMPRESA2"),"'"," "),chr(34)," ")
str_empresa3 = replace(replace(Request.Form("TXT_EMPRESA3"),"'"," "),chr(34)," ")
str_puesto1 = replace(replace(Request.Form("TXT_PUESTO1"),"'"," "),chr(34)," ")
str_puesto2 = replace(replace(Request.Form("TXT_PUESTO2"),"'"," "),chr(34)," ")
str_puesto3 = replace(replace(Request.Form("TXT_PUESTO3"),"'"," "),chr(34)," ")
str_inicio1 = replace(replace(Request.Form("TXT_INICIO1"),"'"," "),chr(34)," ")
str_inicio2 = replace(replace(Request.Form("TXT_INICIO2"),"'"," "),chr(34)," ")
str_inicio3 = replace(replace(Request.Form("TXT_INICIO3"),"'"," "),chr(34)," ")
str_fin1 = replace(replace(Request.Form("TXT_FIN1"),"'"," "),chr(34)," ")
str_fin2 = replace(replace(Request.Form("TXT_FIN2"),"'"," "),chr(34)," ")
str_fin3 = replace(replace(Request.Form("TXT_FIN3"),"'"," "),chr(34)," ")
int_experiencia = replace(replace(Request.Form("TXT_EXPERIENCIA"),"'"," "),chr(34)," ")
if not isnumeric(int_experiencia) then
	int_experiencia=0
end if
str_escolar1 = replace(replace(Request.Form("TXT_ESCOLAR1"),"'"," "),chr(34)," ")
str_escolar2 = replace(replace(Request.Form("TXT_ESCOLAR2"),"'"," "),chr(34)," ")
str_escolar3 = replace(replace(Request.Form("TXT_ESCOLAR3"),"'"," "),chr(34)," ")
str_institucion1 = replace(replace(Request.Form("TXT_INSTITUCION1"),"'"," "),chr(34)," ")
str_institucion2 = replace(replace(Request.Form("TXT_INSTITUCION2"),"'"," "),chr(34)," ")
str_institucion3 = replace(replace(Request.Form("TXT_INSTITUCION3"),"'"," "),chr(34)," ")
str_titulo1 = replace(replace(Request.Form("TXT_TITULO1"),"'"," "),chr(34)," ")
str_titulo2 = replace(replace(Request.Form("TXT_TITULO2"),"'"," "),chr(34)," ")
str_titulo3 = replace(replace(Request.Form("TXT_TITULO3"),"'"," "),chr(34)," ")
str_promedio1 = replace(replace(Request.Form("TXT_PROMEDIO1"),"'"," "),chr(34)," ")
str_promedio2 = replace(replace(Request.Form("TXT_PROMEDIO2"),"'"," "),chr(34)," ")
str_promedio3 = replace(replace(Request.Form("TXT_PROMEDIO3"),"'"," "),chr(34)," ")
str_ortografia = replace(replace(Request.Form("TXT_ORTOGRAFIA"),"'"," "),chr(34)," ")
str_ingles = replace(replace(Request.Form("TXT_INGLES"),"'"," "),chr(34)," ")
str_comunicacion = replace(replace(Request.Form("TXT_COMUNICACION"),"'"," "),chr(34)," ")
str_comentarios = LEFT(replace(replace(Request.Form("TXT_COMENTARIOS"),"'"," "),chr(34)," "),250)
	
' Se convierten las fechas
if Len(int_mes_apl) = 1 then
	int_mes_apl = "0" & int_mes_apl	
end if

if Len(int_dia_apl) = 1 then
	int_dia_apl = "0" & int_dia_apl
end if

str_fecha_aplica= int_ano_apl & "-"& int_mes_apl &"-"& int_dia_apl 
str_hora = replace(replace(Request.Form("TXT_HORA"),"'"," "),chr(34)," ")

str_fecha_aplica = str_fecha_aplica & " " & TimeValue(str_hora)
str_fecha_captura = getDateTimeFormat(now)

str_proceso= Request.Form("PROCESO")
userMail = Request.Form("userMail")

str_bd = application("bd_sieps")
set conn = server.CreateObject ("ADODB.Connection")
conn.Mode=adModeReadWrite
set rs = server.CreateObject ("ADODB.Recordset")
set rsTmp = server.CreateObject ("ADODB.Recordset")

conn.Open str_bd

'sendMensajeToConsole("El folio: " & lon_folio &", El passwd: "&str_password&"', nombre: "&str_nombre&", edad: "&int_edad&", sexo: "&str_sexo&", mail: "&userMail)
dim idPrueba
dim x
dim qryField, qryValue
SELECT CASE str_accion
	CASE "aceptar"
		if str_proceso="alta" then ' si es una alta						
			
			lon_folio=0
			sqlQuery = "SELECT max(soli_folio) as mayor FROM solicitud"
			'set rs=conn.Execute(sqlQuery)
			rs.Open sqlQuery, conn,adOpenStatic,adLockReadOnly,1
			do until rs.EOF
				lon_folio= rs.Fields("mayor")
				rs.MoveNext
			loop
			rs.Close			
						
			lon_folio = lon_folio + 1			

			str_estatus="Solicitud"
			
			conn.BeginTrans
						
			sqlQuery = "INSERT INTO SOLICITUD ("
			sqlQuery = sqlQuery & "Soli_Folio, Soli_password, Soli_idx, Soli_Nombre, Soli_Edad,Soli_Sexo, "', "
			sqlQuery = sqlQuery & "Soli_Fecha_Aplicacion, Soli_Hora, Turn_Numero, Soli_Estatus, Empr_Clave, Soli_Empresa, "
			sqlQuery = sqlQuery & "Divi_Clave, Depa_Clave, Pues_Clave, Soli_Anos_Experiencia, Soli_Empresa_1, Soli_Empresa_2, "
			sqlQuery = sqlQuery & "Soli_Empresa_3,Soli_Puesto_1,Soli_Puesto_2,Soli_Puesto_3,Soli_Ano_Inicial_1,Soli_Ano_Inicial_2, "
			sqlQuery = sqlQuery & "Soli_Ano_Inicial_3, Soli_Ano_Final_1, Soli_Ano_Final_2, Soli_Ano_Final_3,Soli_Escolar_1,Soli_Escolar_2, "
			sqlQuery = sqlQuery & "Soli_Escolar_3,Soli_Fecha_Titulo_1,Soli_Fecha_Titulo_2,Soli_Fecha_Titulo_3,Soli_Instituto_1,Soli_Instituto_2, " 
			sqlQuery = sqlQuery & "Soli_Instituto_3,Soli_Promedio_1,Soli_Promedio_2,Soli_Promedio_3,Soli_Ortografia,Soli_Toefl, "
			sqlQuery = sqlQuery & "Soli_Comunicacion,Soli_Comentarios,Usua_Clave, Fecha_Captura,Cate_Clave,mail_solicitante)" ',Soli_Usuario_Aplico, "',Soli_Fecha_Aplico, "', "

			sqlQuery = sqlQuery & "VALUES ("
			sqlQuery = sqlQuery & lon_folio &",'"&str_password&"', '','"&str_nombre&"',"&int_edad&",'"&str_sexo&"', #"
			sqlQuery = sqlQuery & str_fecha_aplica&"#, '"&str_hora&"',"&str_turno&",'"&str_estatus&"','"&str_empresa&"', '', '"
			sqlQuery = sqlQuery & str_division&"','"&str_departamento&"','"&str_puesto&"',"&int_experiencia&",'"&str_empresa1&"','"&str_empresa2&"', '"
			sqlQuery = sqlQuery & str_empresa3&"','"&str_puesto1&"','"&str_puesto2&"','"&str_puesto3&"','"&str_inicio1 & "','"&str_inicio2&"', '"
			sqlQuery = sqlQuery & str_inicio3&"','"&str_fin1&"','"&str_fin2&"','"&str_fin3 & "','" & str_escolar1 & "','" & str_escolar2 & "', '"
			sqlQuery = sqlQuery & str_escolar3 & "','" & str_titulo1 & "','" & str_titulo2 & "','" & str_titulo3 & "','" & str_institucion1 & "','" & str_institucion2 & "', '" 
			sqlQuery = sqlQuery & str_institucion3 & "','"&str_promedio1 & "','" & str_promedio2 & "','" & str_promedio3 & "','" & str_ortografia & "','" & str_ingles & "', '" 
			sqlQuery = sqlQuery & str_comunicacion & "','" & str_comentarios & "','" & str_usuario & "',#"&str_fecha_captura&"#,'" & str_categoria & "', '"&userMail&"')"

'sendMensajeToConsole("El query: "&sqlQuery)
			
			On Error Resume Next		
			conn.Execute sqlQuery,,adCmdText Or adExecuteNoRecords
			sendError(conn.Errors)
			
			' Insert Las pruebas correspondientes al puesto
			' 1ro Conocer las pruebas que corresponden al puesto seleccionado.
			sqlQuery = "SELECT prue_clave FROM puestos_prueba WHERE pues_clave='"&str_puesto&"'"

			On Error Resume Next
			rs.Open sqlQuery, conn,adOpenStatic,adLockReadOnly,1
			sendError(conn.Errors)
			x = 0
			do until rs.EOF
				x = x + 1
				idPrueba = rs.Fields("prue_clave")
				qryField = qryField & "prue_clave_" & x				
				qryField = qryField & ","
				qryValue = qryValue & "'" & idPrueba
				qryValue = qryValue	& "',"
				rs.MoveNext				
			loop
			'rs.Close
			qryField = Left(qryField,Len(qryField)-1)
			qryValue = Left(qryValue,Len(qryValue)-1)
				
			sqlQuery = "INSERT INTO pruebas_seleccionadas (soli_folio, "
			sqlQuery = sqlQuery & qryField & " )"
			sqlQuery = sqlQuery & " VALUES ("&lon_folio&", "
			sqlQuery = sqlQuery & qryValue & " )"

'sendMensajeToConsole("El query: "&sqlQuery &" pues_clave: "&str_puesto)

			On Error Resume Next		
			conn.Execute sqlQuery,,adCmdText Or adExecuteNoRecords
			sendError(conn.Errors)
			
			conn.CommitTrans
			
			' PRECAUTION!!! Que ocurre si el mail no es valido???
			' Now try to send mail.
			notificated = sendEmail(userMail)
							
			sqlQuery = "UPDATE SOLICITUD SET "
			sqlQuery = sqlQuery & "notificado = "&notificated
			sqlQuery = sqlQuery & " WHERE soli_folio = " & lon_folio
			
			On Error Resume Next		
			conn.Execute sqlQuery,,adCmdText Or adExecuteNoRecords			
			sendError(conn.Errors)
			
		elseif str_proceso="cambio" then  ' si es un cambio
			conn.BeginTrans
			sqlQuery = "UPDATE solicitud SET "
			sqlQuery = sqlQuery & "soli_nombre ='" & str_nombre & "', soli_password ='" & str_password & "',soli_edad =" & int_edad 
			sqlQuery = sqlQuery & ", soli_sexo ='" & str_sexo & "', soli_fecha_aplicacion='" & str_fecha_aplica & "', turn_numero=" & str_turno 
			sqlQuery = sqlQuery & ", soli_hora='" & str_hora & "', divi_clave='" & str_division & "', depa_clave ='" & str_departamento 
			sqlQuery = sqlQuery & "', pues_clave='" & str_puesto & "', cate_clave='" & str_categoria & "', soli_anos_experiencia=" & int_experiencia 
			sqlQuery = sqlQuery & ", soli_empresa_1='" & str_empresa1 & "', soli_empresa_2='" & str_empresa2 & "', soli_empresa_3='" & str_empresa3 
			sqlQuery = sqlQuery & "', soli_puesto_1='" & str_puesto1 & "', soli_puesto_2='" & str_puesto2 & "', soli_puesto_3='" & str_puesto3
			sqlQuery = sqlQuery & "', soli_ano_inicial_1='" & str_inicio1 & "', soli_ano_inicial_2='" & str_inicio2 & "', soli_ano_inicial_3='" & str_inicio3 
			sqlQuery = sqlQuery & "', soli_ano_final_1='" & str_fin1 & "', soli_ano_final_2='" & str_fin2 & "', soli_ano_final_3='" & str_fin3 
			sqlQuery = sqlQuery & "', soli_escolar_1='" & str_escolar1 & "', soli_escolar_2='" & str_escolar2 & "', soli_escolar_3='" & str_escolar3 
			sqlQuery = sqlQuery & "', soli_fecha_titulo_1='" & str_titulo1 & "', soli_fecha_titulo_2='" & str_titulo2 & "', soli_fecha_titulo_3='" & str_titulo3 
			sqlQuery = sqlQuery & "', soli_instituto_1='" & str_institucion1 & "', soli_instituto_2='" & str_institucion2 & "', soli_instituto_3='" & str_institucion3 
			sqlQuery = sqlQuery & "', soli_promedio_1='" & str_promedio1 & "', soli_promedio_2='" & str_promedio2 & "', soli_promedio_3='" & str_promedio3 
			sqlQuery = sqlQuery & "', soli_ortografia='" & str_ortografia & "', soli_toefl='" & str_ingles & "', soli_comunicacion='" & str_comunicacion 
			sqlQuery = sqlQuery & "', soli_comentarios='" & str_comentarios & "', usua_clave='" & str_usuario & "', fecha_captura='" & str_fecha_captura 
			sqlQuery = sqlQuery & "', mail_solicitante='"&userMail&"' WHERE soli_folio =" & lon_folio
			conn.Execute sqlQuery,,1
			conn.CommitTrans
			
			
			' PRECAUTION!!! Que ocurre si el mail no es valido???
			' Now try to send mail.
			notificated = sendEmail(userMail)
							
			sqlQuery = "UPDATE SOLICITUD SET "
			sqlQuery = sqlQuery & "notificado = "&notificated
			sqlQuery = sqlQuery & " WHERE soli_folio = " & lon_folio
			
			On Error Resume Next
			conn.Execute sqlQuery,,adCmdText Or adExecuteNoRecords			
			sendError(conn.Errors)
		end if
	CASE "eliminar"
			conn.BeginTrans

			' Eliminar todos los registros donde este relacionado la solicitud
			deleteAllRegisters(lon_folio)
						
			conn.CommitTrans
END SELECT

' clean up
If rs.State = adStateOpen then
	rs.Close
End If
If conn.State = adStateOpen then
	conn.Close
End If
Set rs = Nothing
Set conn = Nothing

Response.Redirect("registro_listado.asp")

Function sendEmail(toMail)
	dim ret
	dim msg
	msg = ""
	ret = 1
	
	'IMPORTANTE. Estos parametros se configuran cuando es un servidor de correo remoto. 
	' Esto es fuera del servidor web. Si el servicio de mail recide en el mismo servidor, no se configuran
	
	set cdoConfig = CreateObject("CDO.Configuration")
	With cdoConfig.Fields 
		.Item(application("itemSendUsing")) = application("sendUsing")
		'Name or IP of Remote SMTP Server
        .Item(application("itemNameServer")) = application("smtpServer")
		'Server port (typically 25)
        .Item(application("itemNamePort")) = application("smtpPort")
		.Item(application("itemAuthenticate")) = application("authenticate")
		.Item(application("itemSendUserName")) = application("sendUserName")
		.Item(application("itemSendPassword")) = application("sendPassword")
        .Update 
    End With 
	
	' el texto del mensaje es una constante de application scope
	msg = application("msgNotificacion")
	msg = Replace(msg,"%candidateName%",str_nombre)
	msg = Replace(msg,"%candidatePasswd%",str_password)
	
	'set objMessage = server.CreateObject("CDONTS.NewMail")
	set objMessage = CreateObject("CDO.Message")
	
	With objMessage
        Set .Configuration = cdoConfig
        .From = "evaluacion@sieps.com.mx"
        .To = toMail
        .Subject = "Notificación de evaluación SiEPs"
        .HTMLBody = msg
    End With 
	
	on error Resume Next
	objMessage.Send	
	if Err<>0 then
		ret = 0 'Hay error
	end if	
	
	set objMessage = nothing
	set cdoMessage = Nothing  
	sendEmail = ret
end Function

sub sendMensajeToConsole(mensaje)
	dim strrr
	strrr="uti_console.asp?mensaje=" & mensaje
	Response.Redirect(strrr)
end sub

sub deleteAllRegisters(solicitudeFolio)
	dim aux
	aux = "DELETE FROM conclusion_sintesis WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_16pfa WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_16pfb WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_16pfc WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_auto_descripcion WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_beta WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_caq WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_catell WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_domino WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_figs WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_forapre WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_gordon WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_invtipo WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_ipv WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_kostick WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_kuder_p WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_kuder_v WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_lifo WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_luscher WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_mmpi WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_mmpi2 WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_mmpia WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1						
			aux = "DELETE FROM dat_ortografia WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_terman WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_valores_personales WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM dat_wonderlic WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM matrices_avanzado WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM matrices_basico WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM pruebas_seleccionadas WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1
			aux = "DELETE FROM solicitud_reporte WHERE soli_folio =" & solicitudeFolio			
			conn.Execute aux,,1			
			aux = "DELETE FROM solicitud WHERE soli_folio =" & solicitudeFolio
			conn.Execute aux,,1
end sub
%>
