Option Explicit On 
Option Strict On

Imports System.Data
Imports System.Data.SqlClient

Public Class frmMgr
	Inherits System.Web.UI.Page
	Protected WithEvents DG_Census As System.Web.UI.WebControls.DataGrid
	Protected WithEvents DA_AllCensus As System.Data.SqlClient.SqlDataAdapter
	Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
	Protected WithEvents DS_AllCensus1 As AllSaints.DS_AllCensus
	Protected WithEvents lnkExport As System.Web.UI.WebControls.LinkButton
	Protected WithEvents Conn As System.Data.SqlClient.SqlConnection
	Protected WithEvents Counter1 As AllSaints.Counter
#Region " Web Form Designer Generated Code "

	'This call is required by the Web Form Designer.
	<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
		Me.Conn = New System.Data.SqlClient.SqlConnection()
		Me.DA_AllCensus = New System.Data.SqlClient.SqlDataAdapter()
		Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
		Me.DS_AllCensus1 = New AllSaints.DS_AllCensus()
		CType(Me.DS_AllCensus1, System.ComponentModel.ISupportInitialize).BeginInit()
		'
		'Conn
		'
		Me.Conn.ConnectionString = "data source=GALAXY;initial catalog=Online_allsaints;persist security info=False;u" & _
		"ser id=sa;workstation id=GALAXY;packet size=4096"
		'
		'DA_AllCensus
		'
		Me.DA_AllCensus.SelectCommand = Me.SqlSelectCommand1
		Me.DA_AllCensus.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "GetAllCensus", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("ID", "ID"), New System.Data.Common.DataColumnMapping("FName1", "FName1"), New System.Data.Common.DataColumnMapping("LName", "LName"), New System.Data.Common.DataColumnMapping("Address1", "Address1"), New System.Data.Common.DataColumnMapping("Phone1", "Phone1"), New System.Data.Common.DataColumnMapping("Email1", "Email1"), New System.Data.Common.DataColumnMapping("PlaceofEmployment1", "PlaceofEmployment1"), New System.Data.Common.DataColumnMapping("PlaceofEmployment2", "PlaceofEmployment2"), New System.Data.Common.DataColumnMapping("PlacementTitle1", "PlacementTitle1"), New System.Data.Common.DataColumnMapping("PlacementTitle2", "PlacementTitle2"), New System.Data.Common.DataColumnMapping("Talents1", "Talents1"), New System.Data.Common.DataColumnMapping("AllSaintsGraduate1", "AllSaintsGraduate1"), New System.Data.Common.DataColumnMapping("GraduateDate1", "GraduateDate1"), New System.Data.Common.DataColumnMapping("MaritalStatus1", "MaritalStatus1"), New System.Data.Common.DataColumnMapping("MaritalStatus2", "MaritalStatus2"), New System.Data.Common.DataColumnMapping("FName2", "FName2"), New System.Data.Common.DataColumnMapping("MaidenName", "MaidenName"), New System.Data.Common.DataColumnMapping("Address2", "Address2"), New System.Data.Common.DataColumnMapping("Phone2", "Phone2"), New System.Data.Common.DataColumnMapping("Email2", "Email2"), New System.Data.Common.DataColumnMapping("Talents2", "Talents2"), New System.Data.Common.DataColumnMapping("AllSaintsGraduate2", "AllSaintsGraduate2"), New System.Data.Common.DataColumnMapping("GraduateDate2", "GraduateDate2"), New System.Data.Common.DataColumnMapping("IsArchive", "IsArchive"), New System.Data.Common.DataColumnMapping("ReasonToLeave", "ReasonToLeave"), New System.Data.Common.DataColumnMapping("IP", "IP"), New System.Data.Common.DataColumnMapping("PostDate", "PostDate")})})
		'
		'SqlSelectCommand1
		'
		Me.SqlSelectCommand1.CommandText = "[GetAllCensus]"
		Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
		Me.SqlSelectCommand1.Connection = Me.Conn
		Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
		'
		'DS_AllCensus1
		'
		Me.DS_AllCensus1.DataSetName = "DS_AllCensus"
		Me.DS_AllCensus1.Locale = New System.Globalization.CultureInfo("en-US")
		Me.DS_AllCensus1.Namespace = "http://www.tempuri.org/DS_AllCensus.xsd"
		CType(Me.DS_AllCensus1, System.ComponentModel.ISupportInitialize).EndInit()

	End Sub

	Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
		'CODEGEN: This method call is required by the Web Form Designer
		'Do not modify it using the code editor.
		InitializeComponent()
		If Conn.State = ConnectionState.Closed Then
			Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
		End If
	End Sub

#End Region

	Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		If Not Page.IsPostBack Then
			Counter1.PageID = 256
			DA_AllCensus.Fill(DS_AllCensus1)
			DG_Census.DataSource = New DataView(DS_AllCensus1.Tables(0))
			DG_Census.DataBind()
		End If
	End Sub

	'**************************************************************************
	'	Data gird has the selection change now
	'**************************************************************************
	Private Sub DG_Census_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DG_Census.SelectedIndexChanged

	End Sub

	'**************************************************************************
	'	Data gird has a new item created now
	'**************************************************************************
	Private Sub DG_Census_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DG_Census.ItemCreated
		If e.Item.ItemType = ListItemType.Footer Then
			Dim btnDel As New Button()
			With btnDel
				.ID = "DeleteSelected"
				.Attributes.Add("onclick", "return confirm('Are you sure ?');")
				.CommandName = "DeleteAllSelected"
				.Text = "Delete Selected"
				.Font.Name = "Verdana"
				.ToolTip = "Click this to delete all selected census records"
			End With
			e.Item.Cells(1).Controls.Add(btnDel)
		End If

		If e.Item.ItemType = ListItemType.Pager Then
			If (DS_AllCensus1 Is Nothing) AndAlso DS_AllCensus1.Tables(0).Rows.Count < DG_Census.PageSize Then
				e.Item.Visible = False
			End If
		End If
	End Sub

	'**************************************************************************
	'	Get this page Data to display is now 
	'**************************************************************************
	Private Sub DG_Census_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DG_Census.PageIndexChanged
		DG_Census.CurrentPageIndex = e.NewPageIndex
		DA_AllCensus.Fill(DS_AllCensus1)
		DG_Census.DataSource = New DataView(DS_AllCensus1.Tables(0), "", CType(ViewState("SortBy"), String) & CType(ViewState("Direction"), String), DataViewRowState.CurrentRows)
		DG_Census.DataBind()
	End Sub

	'**************************************************************************
	'	Data gird sorting is now cliched
	'**************************************************************************
	Private Sub DG_Census_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DG_Census.SortCommand
		DA_AllCensus.Fill(DS_AllCensus1)
		ViewState("SortBy") = e.SortExpression
		If CType(ViewState("Direction"), String) = "" Then
			ViewState("Direction") = " ASC "
		ElseIf CType(ViewState("Direction"), String).Trim = "ASC" Then
			ViewState("Direction") = " DESC "
		Else
			ViewState("Direction") = " ASC "
		End If
		DG_Census.DataSource = New DataView(DS_AllCensus1.Tables(0), "", e.SortExpression & CType(ViewState("Direction"), String), DataViewRowState.CurrentRows)
		DG_Census.DataBind()
	End Sub

	'**************************************************************************
	'	A child command has been fired up by a control in the Data gird
	'**************************************************************************
	Private Sub DG_Census_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DG_Census.ItemCommand
		If e.CommandName = "DeleteAllSelected" Then
			Dim dgi As DataGridItem
			For Each dgi In DG_Census.Items
				If dgi.ItemType = ListItemType.Item Or dgi.ItemType = ListItemType.AlternatingItem Then
					Dim cb As CheckBox = CType(dgi.Cells(0).Controls(1), CheckBox)
					If cb.Checked Then
						Dim cmd As New SqlCommand()
						With cmd
							.CommandText = "Delete from as_Census where ID=" & CType(cb.ToolTip, Integer)
							.CommandType = CommandType.Text
							If Conn.State = ConnectionState.Closed Then Conn.Open()
							.Connection = Conn
							.ExecuteNonQuery()
						End With
					End If
				End If
			Next
			DA_AllCensus.Fill(DS_AllCensus1)
			DG_Census.DataSource = New DataView(DS_AllCensus1.Tables(0), "", CType(ViewState("SortBy"), String) & CType(ViewState("Direction"), String), DataViewRowState.CurrentRows)
			DG_Census.DataBind()
		Else
			'Response.Write(e.CommandName)
		End If

	End Sub

	'**************************************************************************
	'	A Export of census data 
	'**************************************************************************
	Private Sub lnkExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkExport.Click
		Response.ClearHeaders()
		Response.AddHeader("Content-Type", "text/csv")
		Response.AddHeader("Content-Disposition", "attachment; filename=CensusData.csv")
		DA_AllCensus.Fill(DS_AllCensus1)

		Dim dr As DataRow
		Dim dc As DataColumn
		Dim objText As Object
		Dim sText As String
		Dim SensusID As Integer = 0
		Dim dtc As DataColumn
		For Each dtc In DS_AllCensus1.Tables(0).Columns
			Response.Write(dtc.ColumnName() & ",")
		Next
		Response.Write(vbCrLf)

		Dim cmd As New SqlCommand()
		Dim dtr As SqlDataReader

		'now write the each every record by traversing columns
		For Each dr In DS_AllCensus1.Tables(0).Rows
			For Each dc In DS_AllCensus1.Tables(0).Columns
				objText = dr.Item(dc)
				'get the id of this sensus if the column is [ID]
				If dc.ColumnName.Trim.ToUpper = "ID" Then
					SensusID = CType(objText, Integer)
				End If

				If IsDBNull(objText) Then
					Response.Write(",")
				Else
					sText = CType(objText, String)
					sText = Replace(sText, Chr(13), "\n")
					sText = Replace(sText, Chr(10), "\r")
					Response.Write(Chr(34) & CType(sText, String) & Chr(34) & ",")
				End If
			Next

			'now load the childeren information attached with this census
			With cmd
				.CommandText = "SELECT * FROM as_Children WHERE CensusID=" & SensusID
				.CommandType = CommandType.Text
				.Connection = Conn
				If Conn.State = ConnectionState.Closed Then Conn.Open()
				dtr = .ExecuteReader
			End With

			While dtr.Read()
				If Not IsDBNull(dtr.Item("ChildName")) Then
					Response.Write(Chr(34))
					Response.Write(dtr.Item("ChildName"))
					Response.Write(Chr(34))
				End If
				Response.Write(",")
				If Not IsDBNull(dtr.Item("Age")) Then
					Response.Write(Chr(34))
					Response.Write(dtr.Item("Age"))
					Response.Write(Chr(34))
				End If
				Response.Write(",")

				If Not IsDBNull(dtr.Item("School")) Then
					Response.Write(Chr(34))
					Response.Write(dtr.Item("School"))
					Response.Write(Chr(34))
				End If
				Response.Write(",")
			End While
			dtr.Close()
			Response.Write(vbCrLf)
		Next
		Response.End()
	End Sub

End Class
