home - projects - login - links

Project: ScoutsLint.be V1: Verjaardagskalender

Location: root /

File: Admin-verjaardag-importeer.asp

expand uitklappen
  1. <!--#include file="includes/admin-top.asp"-->
  2. <%
  3. Response.Buffer = true
  4.  
  5. dim strSheets(9)
  6. dim strSheetTak(9)
  7. dim strConnectionExcel, cnExcel, rsExcel, sqlExcel, rsJarigen, strExcel, action, myDirectory, myFiles, filefound, fileextension, i, errors, aantal
  8. dim  dag, maand, jaar, leeftijd, naam
  9.  
  10.  
  11. action = request.QueryString("action")
  12. if action = "" then
  13. 	action = "new"
  14. end if
  15.  
  16. select case action
  17. case "process"
  18. 	if request.Form("opslaan") = "opslaan" then
  19. 		Dim strFile, strKapoenen, strKws, strJojos, strJogis, strGidvers, strJins, strLeiding, strWiebels, strDroedels
  20. 		strFile = Request.Form("excelfile")
  21. 		strSheets(0) = Request.Form("kapoenen")
  22. 		strSheets(1) = Request.Form("kws")
  23. 		strSheets(2) = Request.Form("jojos")
  24. 		strSheets(3) = Request.Form("jogis")
  25. 		strSheets(4) = Request.Form("gidvers")
  26. 		strSheets(5) = Request.Form("jins")
  27. 		strSheets(6) = Request.Form("leiding")
  28. 		strSheets(7) = Request.Form("wiebels")
  29. 		strSheets(8) = Request.Form("droedels")
  30.  
  31. 		strSheetTak(0) = "Kapoenen"					' Naam van de tak die getoond wordt naast de verjaardag
  32. 		strSheetTak(1) = "KW's"
  33. 		strSheetTak(2) = "Jojo's"
  34. 		strSheetTak(3) = "Jonggidvers"
  35. 		strSheetTak(4) = "Gidvers"
  36. 		strSheetTak(5) = "Jins"
  37. 		strSheetTak(6) = "Leiding"
  38. 		strSheetTak(7) = "Wiebels"
  39. 		strSheetTak(8) = "Droedels"		
  40.  
  41. 		if strFile <> "" and strSheets(0) <> "" and strSheets(1) <> "" and strSheets(2) <> "" and strSheets(3) <> "" and strSheets(4) <> "" and strSheets(5) <> "" and strSheets(6) <> "" and strSheets(7) <> "" and strSheets(8) <> "" then
  42. 			' Alle velden ingevuld, kijken of de informatie ook 'bruikbaar' is
  43. 			'Connectie naar de excel-file
  44. 			strConnectionExcel = "DBQ=" & Server.MapPath("documenten/Ledenlijst/" & strFile) & "; DRIVER={Microsoft Excel Driver (*.xls)};"
  45. 			'Connectieobjecten
  46. 			Set cnExcel = Server.CreateObject("ADODB.Connection")
  47. 			'Recordset
  48. 			Set rsExcel = Server.CreateObject("ADODB.Recordset")
  49. 			'Connectie openen
  50. 			err.Clear
  51. 			on error resume next
  52. 			cnExcel.open strConnectionExcel
  53. 			if (err.number <> 0) then
  54. 				response.Write ("oops: " & err.Description)
  55. 				response.End
  56. 			end if
  57.  
  58. 			response.Write ("Excel-file: " & strFile & " ... <b> gevonden!</b><br /><br />")
  59.  
  60. 			'Tabbladen overlopen, kijken of ervan gelezen kan worden
  61. 			i = 0		
  62. 			do while (i < 9)
  63. 				'SQL statement
  64. 				sqlExcel = "SELECT naam, gebdat FROM [" & strSheets(i) & "$];"
  65. 				'Recordset openen - kijken of het hier al fout loopt
  66. 				err.Clear
  67. 				on error resume next				
  68. 				rsExcel.Open sqlExcel, cnExcel
  69.  
  70. 				if (err.number <> 0) then
  71. 					Response.Write ("De spreadsheet <b>" & strSheets(i) & "</b> (voor de " & strSheetTak(i) & ") bestaat niet.<br />")
  72. 					Response.Write ("Controlleer de naam van de spreadsheet, en probeer opnieuw.<br /><br />")
  73. 					Response.write ("Microsoft-error: <br /><em>")
  74. 					Response.Write (err.Description & "</em>")
  75. 					Response.End
  76. 				end if
  77.  
  78.  
  79. 				'Cellen overlopen
  80. 				aantal = 0				
  81. 				response.Write ("Proberen lezen uit tabblad <b>" & strSheetTak(i)) & " (" & strSheets(i) & ")</b><br />"
  82. 				response.Flush
  83. 				do while not rsExcel.eof
  84. 					if rsExcel("naam") <> "" and rsExcel("gebdat") <> "" then
  85. 						aantal = aantal + 1
  86. 					end if		
  87. 					rsExcel.movenext					
  88. 				loop	
  89. 				i = i + 1
  90. 				rsExcel.Close
  91. 				response.Write (" >> <b>" & aantal & "</b> leden gevonden.<br /><br />")				
  92. 				response.flush
  93. 			loop		
  94.  
  95. 			' Excel-informatie is correct (anders zou er een response.end opgeroepen worden), invoeren in MySQL
  96. 			i = 0
  97. 			do while (i < 9)
  98. 				'SQL statement
  99. 				sqlExcel = "SELECT naam, gebdat FROM [" & strSheets(i) & "$];"
  100. 				'Recordset openen
  101. 				rsExcel.Open sqlExcel, cnExcel
  102.  
  103. 				'Cellen overlopen
  104. 				response.Write ("<b><u>" & strSheetTak(i)) & "</u></b><br />"
  105. 				do while not rsExcel.eof
  106. 					if rsExcel("naam") <> "" and rsExcel("gebdat") <> "" then
  107. 						dag = day (rsExcel("gebdat"))
  108. 						maand = month (rsExcel("gebdat"))
  109. 						jaar = year (rsExcel("gebdat"))
  110. 						leeftijd = year(date()) - jaar
  111. 						naam = rsExcel ("naam")
  112.  
  113. 						'Gegevens Excel naar MySQL verplaatsen
  114. 						set rsJarigen=Server.CreateObject("ADODB.RecordSet")
  115. 						strSql="SELECT * FROM tblverjaardag where 1=1"
  116. 						rsJarigen.open strSql, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText
  117. 						rsJarigen.AddNew
  118. 						rsJarigen("verj_naam")=naam
  119. 						rsJarigen("verj_dag") = dag
  120. 						rsJarigen("verj_maand") = maand
  121. 						rsJarigen("verj_jaar") = jaar
  122. 						rsJarigen.Update			
  123. 					end if		
  124. 				rsExcel.movenext
  125. 				loop	
  126. 				i = i + 1
  127. 				rsExcel.Close
  128. 				Response.Write (strSheetTak(i-1) & " succesvol weggeschreven naar de database. <br /><br />")
  129. 				Response.Flush
  130. 			loop
  131.  
  132. 			Response.Write ("Alle takken zijn weggeschreven. ")
  133. 			Response.flush
  134. 		else
  135. 			response.Write ("Gelieve alle velden in te vullen. <br />Gebruik de 'back'-knop in je browser om terug te keren en alles in te vullen.")		
  136.  
  137. 		end if
  138. 	end if
  139. case "process2"	
  140. 	'Formulier ingevuld, gegevens verwerken
  141. 	strSheetTak(0) = "Kapoenen"					' Naam van de tak die getoond wordt naast de verjaardag
  142. 	strSheetTak(1) = "KW's"
  143. 	strSheetTak(2) = "Jojo's"
  144. 	strSheetTak(3) = "Jonggidvers"
  145. 	strSheetTak(4) = "Gidvers"
  146. 	strSheetTak(5) = "Jins"
  147. 	strSheetTak(6) = "Leiding"
  148. 	strSheetTak(7) = "Wiebels"
  149. 	strSheetTak(8) = "Droedels"
  150.  
  151. 	'Connectie naar de excel-file
  152. 	strConnectionExcel = "DBQ=" & Server.MapPath("Ledenlijst.xls") & "; DRIVER={Microsoft Excel Driver (*.xls)};"
  153.  
  154. 	'Connectieobjecten
  155. 	Set cnExcel = Server.CreateObject("ADODB.Connection")
  156. 	'Recordset
  157. 	Set rsExcel = Server.CreateObject("ADODB.Recordset")
  158. 	'Connectie openen
  159. 	cnExcel.open strConnectionExcel
  160.  
  161.  
  162.  
  163. 	Set rsExcel = nothing
  164. 	cnExcel.close
  165. 	Set cnExcel = nothing
  166. case "new"
  167. 	'Formulier voor het invullen van gegevens
  168. 	%>
  169. <h2>Gegevens importeren van Excel</h2>
  170. <h3>Algemeen</h3>
  171. <form name="frmExcel" method="post" action="Admin-verjaardag-importeer.asp?action=process" ID="Form1">
  172.  
  173. <p>Selecteer excel-file: 
  174. 	<select name="excelfile"> 
  175. 	<%
  176. 		Set MyDirectory=Server.CreateObject("Scripting.FileSystemObject")
  177. 		Set MyFiles=MyDirectory.GetFolder(Server.MapPath("documenten/Ledenlijst"))
  178. 		For each filefound in MyFiles.files
  179. 			fileextension = MyDirectory.GetExtensionName("documentne/Ledenlijst/" & filefound)
  180.  
  181. 			if fileextension = "xls" then					
  182. 				response.Write ("<option value='" & filefound.name & "'>" & filefound.name & "</option>")
  183. 			end if
  184. 		next
  185. 	%>
  186. 	</select>
  187. 	<br />
  188. 	<i>Deze bestanden worden gelezen uit de directory <b>[root]/documenten/Ledenlijst/</b></i>
  189. </p>
  190. <h3>Gegevens van Excel-file</h3>
  191. 	<table border=0>
  192. 		<tr>
  193. 			<td colspan=2 class="Body">Deze gegevens zijn <b>geschat</b>! <br />Kijk ze zeker na alvorens het importeren te beginnen.</td>
  194. 		</tr>
  195. 		<tr>
  196. 			<td class="Body">Naam tabblad kapoenen:  </td>
  197. 			<td><input type="text" name="kapoenen" value="KAPOENEN <% response.write(mid(year(date()) - 7, 3, 2) & "_" & mid(year(date()) - 6, 3, 2)) %>" length=30 ID="Text1"> </td>
  198. 		</tr>
  199. 		<tr>
  200. 			<td class="Body">Naam tabblad KW's:  </td>
  201. 			<td><input type="text" name="kws" value="KW_S <% response.write(mid(year(date()) - 9, 3, 2) & "_" & mid(year(date()) - 8, 3, 2)) %>" length=30 ID="Text2"> </td>
  202. 		</tr>
  203. 		<tr>
  204. 			<td class="Body">Naam tabblad Jojo's:  </td>
  205. 			<td><input type="text" name="jojos" value="JOJO_S <% response.write(mid(year(date()) - 11, 3, 2) & "_" & mid(year(date()) - 10, 3, 2)) %>" length=30 ID="Text3"> </td>
  206. 		</tr>
  207. 		<tr>
  208. 			<td class="Body">Naam tabblad jonggidvers:  </td>
  209. 			<td><input type="text" name="jogis" value="JONGGIDVERS <% response.write(mid(year(date()) - 13, 3, 2) & "_" & mid(year(date()) - 12, 3, 2)) %>" length=30 ID="Text4"> </td>
  210. 		</tr>
  211. 		<tr>
  212. 			<td class="Body">Naam tabblad gidvers:  </td>
  213. 			<td><input type="text" name="gidvers" value="GIDVERS <% response.write(mid(year(date()) - 15, 3, 2) & "_" & mid(year(date()) - 14, 3, 2)) %>" length=30 ID="Text5"> </td>
  214. 		</tr>
  215. 		<tr>
  216. 			<td class="Body">Naam tabblad jins:  </td>
  217. 			<td><input type="text" name="jins" value="JINS <% response.write(mid(year(date()) - 17, 3, 2) & "_" & mid(year(date()) - 16, 3, 2)) %>" length=30 ID="Text6"> </td>
  218. 		</tr>
  219. 		<tr>
  220. 			<td class="Body">Naam tabblad leiding:  </td>
  221. 			<td><input type="text" name="leiding" value="LEIDING <% response.write(mid(year(date()) - 18, 3, 2) & "_") %>" length=30 ID="Text7"> </td>
  222. 		</tr>
  223. 		<tr>
  224. 			<td class="Body">Naam tabblad wiebels:  </td>
  225. 			<td><input type="text" name="wiebels" value="WIEBELS" length=30 ID="Text8"> </td>
  226. 		</tr>
  227. 		<tr>
  228. 			<td class="Body">Naam tabblad droedels:  </td>
  229. 			<td><input type="text" name="droedels" value="DROEDELS" length=30 ID="Text9"> </td>
  230. 		</tr>
  231. 		<tr>
  232. 			<td class="Body" colspan=2 align="center"> <input type="submit" name="opslaan" value="opslaan"></td>
  233. 		</tr>
  234.  
  235. 	</table>
  236. </form>	
  237. 	<%
  238. case else
  239. 	Response.Write ("invalid query string")
  240. end select
  241.  
  242.  
  243.  
  244. %>
  245. <!--#include file="includes/admin-bottom.asp"-->