Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.Data
Imports System.Xml

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService
    'Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=:\Users\ANDROID-PC\Documents\Visual Studio 2010\WebSites\BiharGrivanceSubmit\App_Data\Database.mdf;Integrated Security=True;User Instance=True")
    Dim strcon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("dbconnection").ConnectionString)

    <WebMethod()> _
    Public Function checkUser(ByVal mobileNumber As String) As DataTable

        Dim query As String = String.Empty
        'query &= "INSERT INTO User_Registration_Detail (colName, colID, colPhone, "
        'query &= "                     colBranch, colCourse, coldblFee)  "
        'query &= "VALUES (@colName,@colID, @colPhone, @colBranch,@colCourse, @coldblFee)"

        query &= "SELECT        * from mobile_user_register where mobile = '" + mobileNumber + "'"

        '  Dim cmd1 As SqlCommand = New SqlCommand(query, strcon)
        ' strcon.Close()
        ' strcon.Open()
        ' Using reader As SqlDataReader = cmd1.ExecuteReader()
        'If reader.HasRows Then
        'Return True
        ' Else
        ' Return False
        '  End If
        ' End Using



        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "userDetailTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using


        '  Using conn As New SqlConnection("connectionStringHere")
        'Using comm As New SqlCommand()
        'With comm
        '.Connection = conn
        '  .CommandType = CommandType.Text
        '  .CommandText = query
        '  .Parameters.AddWithValue("@colName", strName)
        '  .Parameters.AddWithValue("@colID", strId)
        '  .Parameters.AddWithValue("@colPhone", strPhone)
        '  .Parameters.AddWithValue("@colBranch", strBranch)
        '  .Parameters.AddWithValue("@colCourse", strCourse)
        '  .Parameters.AddWithValue("@coldblFee", dblFee)
        '  End With
        '  Try
        '  conn.Open()
        'comm.ExecuteNonQuery()
        ' Catch(ex as SqlException)
        'MessageBox.Show(ex.Message.ToString(), "Error Message")
        '  End Try
        '  End Using
        '  End Using




        '        Using cmd As New SqlCommand("SELECT * FROM countrydetails")
        'Using sda As New SqlDataAdapter()
        'cmd.Connection = strcon
        ' sda.SelectCommand = cmd
        ' Using dt As New DataTable()
        'dt.TableName = "countrydetails"
        ' sda.Fill(dt)
        ' Return dt
        ' End Using
        ' End Using
        ' End Using
    End Function

    <WebMethod()> _
    Public Function registerUser(ByVal xmlString As String, ByVal tableName As String) As String
        Dim query As String = String.Empty
        Dim queryToCheckMobileNumber As String = String.Empty
        Dim queryToCheckMobileNumberToCheckMobileNumber As String = String.Empty
       
        Dim xmlDoc = New XmlDocument

        xmlDoc.LoadXml(xmlString)

        queryToCheckMobileNumber = "SELECT * from mobile_user_register where mobile = '" + xmlDoc.SelectSingleNode("//" + "mobile").InnerText + "'"

        Dim cmdForSelectData As SqlCommand = New SqlCommand(queryToCheckMobileNumber, strcon)
        strcon.Close()
        strcon.Open()
        Using reader As SqlDataReader = cmdForSelectData.ExecuteReader()
            If reader.HasRows Then
                reader.Read()
                Return reader("id").ToString()
            Else
                query &= "INSERT INTO mobile_user_register (username, mobile, email, "
                query &= "                     district_id, ulb_id,circle_id,ward_id,gender,adhar_number)  "
                query &= "VALUES ('" + xmlDoc.SelectSingleNode("//" + "username").InnerText + "','" + xmlDoc.SelectSingleNode("//" + "mobile").InnerText + "',"
                query &= "'" + xmlDoc.SelectSingleNode("//" + "email").InnerText + "', '" + xmlDoc.SelectSingleNode("//" + "district_id").InnerText + "',"
                query &= "'" + xmlDoc.SelectSingleNode("//" + "ulb_id").InnerText + "', '" + xmlDoc.SelectSingleNode("//" + "circle_id").InnerText + "', '" + xmlDoc.SelectSingleNode("//" + "ward_id").InnerText + "',"
                query &= "'" + xmlDoc.SelectSingleNode("//" + "gender").InnerText + "', '" + xmlDoc.SelectSingleNode("//" + "adhar_number").InnerText + "')"

                Dim cmd1 As SqlCommand = New SqlCommand(query, strcon)
                strcon.Close()
                strcon.Open()
                cmd1.ExecuteNonQuery()

                queryToCheckMobileNumberToCheckMobileNumber = "SELECT * from mobile_user_register where mobile = '" + xmlDoc.SelectSingleNode("//" + "mobile").InnerText + "'"

                Dim cmdForSelectData__ As SqlCommand = New SqlCommand(queryToCheckMobileNumberToCheckMobileNumber, strcon)
                strcon.Close()
                strcon.Open()
                Using reader__ As SqlDataReader = cmdForSelectData__.ExecuteReader()
                    If reader__.HasRows Then
                        reader__.Read()
                        Return reader__("id").ToString()
                        strcon.Close()
                    End If
                End Using
            End If

        End Using

        Return "0"

    End Function

    <WebMethod()> _
    Public Function getDistrict() As DataTable

        Dim query As String = String.Empty

        query = "SELECT         int_LevelDetailId as distId, nvch_LevelName as distName FROM M_Admin_LevelDetails"
        query &= " WHERE        (int_LevelId = '2') AND (int_DeletedFlag = 0)"
        query &= " ORDER BY nvch_LevelName"

        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "districtTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Function getULB(ByVal distId As String) As DataTable

        Dim query As String = String.Empty

        query = " SELECT        int_LevelDetailId AS ulbId, nvch_LevelName AS ulbName"
        query &= "         FROM M_Admin_LevelDetails "
        query &= "         WHERE        (int_LevelId = '3') AND (int_DeletedFlag = 0) AND (int_PldId = '" + distId + "')"
        query &= "         ORDER BY ulbName"

        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "ulbTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Function getCircle(ByVal ulbId As String) As DataTable

        Dim query As String = String.Empty

        query = " SELECT        int_LevelDetailId AS circleId, nvch_LevelName AS circleName"
        query &= "        FROM M_Admin_LevelDetails"
        query &= "        WHERE        (int_LevelId = '4') AND (int_PldId = '" + ulbId + "') AND (int_DeletedFlag = '0')"
        query &= "        ORDER BY circleName"

        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "circleTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Function getWard(ByVal circleId As String) As DataTable
        Dim query As String = String.Empty
        query = " SELECT        int_LevelDetailId AS wardId, nvch_LevelName AS wardName"
        query &= " FROM M_Admin_LevelDetails"
        query &= " WHERE        (int_LevelId = '5') AND (int_DeletedFlag = '0') AND (int_PldId = '" + circleId + "')"
        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "wardTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Function getComplain(ByVal serviceTypeId As String) As DataTable
        Dim query As String = String.Empty

        query = " SELECT        INT_COMPLAINTYPE_ID AS complainId, VCH_COMPLAINTYPE_NAME AS complainName"
        query &= " FROM M_COMPLAINTYPE  WHERE        (INT_SERVICETYPE_ID = '" + serviceTypeId + "') AND (INT_ESCALATION_LEVEL = '4') "

        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = strcon
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    dt.TableName = "complainTable"
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    <WebMethod()> _
    Public Function submitComplainDetail(ByVal xmlString As String, ByVal mobileNumber As String) As String
        Dim regId As Integer = 0
        Dim queryToGetLastRegistrationId As String = String.Empty
        Dim queryToCheckMobileNumber As String = String.Empty
        Dim queryToInsertComplain As String = String.Empty
        Dim VCH_NAME = "", VCH_PHONENO = "", VCH_EMAIL = "", INT_GENDER = "", VCH_ADDRESS = ""
        Dim INT_DISTRICTID, INT_ULBID, INT_CIRCLEID, INT_WARDID As Integer

        queryToCheckMobileNumber = "SELECT * from mobile_user_register where mobile = '" + mobileNumber + "'"
        queryToGetLastRegistrationId = "SELECT        MAX(INT_REGDID) AS regId FROM            T_COMPLAINT_REGISTRATION"

        Dim cmdForGettingMaxRegId As SqlCommand = New SqlCommand(queryToGetLastRegistrationId, strcon)
        strcon.Close()
        strcon.Open()
        Using reader1 As SqlDataReader = cmdForGettingMaxRegId.ExecuteReader()
            If reader1.HasRows Then
                reader1.Read()
                regId = Convert.ToInt32(reader1("regId").ToString()) + 1
            End If
        End Using


        Dim cmdForSelectData As SqlCommand = New SqlCommand(queryToCheckMobileNumber, strcon)
        strcon.Close()
        strcon.Open()
        Using reader As SqlDataReader = cmdForSelectData.ExecuteReader()
            If reader.HasRows Then
                reader.Read()
                VCH_NAME = reader("username").ToString()
                VCH_PHONENO = reader("mobile").ToString()
                VCH_EMAIL = reader("email").ToString()
                INT_GENDER = reader("gender").ToString()
                VCH_ADDRESS = reader("address").ToString()
                INT_DISTRICTID = reader("district_id").ToString()
                INT_ULBID = reader("ulb_id").ToString()
                INT_CIRCLEID = reader("circle_id").ToString()
                INT_WARDID = reader("ward_id").ToString()
            End If

            Dim queryToInsertCompaint As String = String.Empty

            xmlString = "<start><INT_SERVICETYPEID>45</INT_SERVICETYPEID><INT_COMPLAINTTYPEID>46</INT_COMPLAINTTYPEID></start>"

            Dim xmlDoc = New XmlDocument

            xmlDoc.LoadXml(xmlString)

            ' Dim regDate As Date = Date.Now()
            'Dim strDate As String = regDate.ToString("yyyy-MM-dd")

            Dim compId As String = xmlDoc.SelectSingleNode("//" + "INT_COMPLAINTTYPEID").InnerText
            Dim serviceType As String = xmlDoc.SelectSingleNode("//" + "INT_SERVICETYPEID").InnerText

            ' queryToInsertComplain = " INSERT INTO T_COMPLAINT_REGISTRATION"
            '  queryToInsertComplain &= " (INT_REGDID, INT_REGD_MODE, DTM_REGD_DATE, VCH_TOKENNO, INT_RECEIVEDBY, VCH_NAME, VCH_PHONENO, VCH_EMAIL, INT_GENDER, VCH_ADDRESS, "
            '  queryToInsertComplain &= " INT_DISTRICTID, INT_ULBID, INT_CIRCLEID, INT_WARDID, INT_SERVICETYPEID, INT_COMPLAINTTYPEID, INT_STATUSFLAG)"
            '  queryToInsertComplain &= " VALUES ('" + regId + "','5', '" + strDate + "', '121212', '5', '" + VCH_NAME + "', '" + VCH_PHONENO + "', '" + VCH_EMAIL + "', '" + Convert.ToInt32(INT_GENDER) + "', '" + VCH_ADDRESS + "', " + INT_DISTRICTID + ", " + INT_ULBID + ", " + INT_CIRCLEID + ", " + INT_WARDID + ", '" + serviceType + "', '" + compId + "', '0')"

            queryToInsertComplain = " INSERT INTO T_COMPLAINT_REGISTRATION"
            queryToInsertComplain &= " (INT_REGDID, INT_REGD_MODE, DTM_REGD_DATE, VCH_TOKENNO, INT_RECEIVEDBY, VCH_NAME, VCH_PHONENO, VCH_EMAIL, INT_GENDER, VCH_ADDRESS, "
            queryToInsertComplain &= " INT_DISTRICTID, INT_ULBID, INT_CIRCLEID, INT_WARDID, INT_SERVICETYPEID, INT_COMPLAINTTYPEID, INT_STATUSFLAG, INT_ANONYMOUS,INT_PUBLISH,INT_CALLTYPE)"
            queryToInsertComplain &= " VALUES (@INT_REGDID,@INT_REGD_MODE,@DTM_REGD_DATE,@VCH_TOKENNO,@INT_RECEIVEDBY,@VCH_NAME,@VCH_PHONENO,@VCH_EMAIL,@INT_GENDER,@VCH_ADDRESS,@INT_DISTRICTID"
            queryToInsertComplain &= " ,@INT_ULBID,@INT_CIRCLEID,@INT_WARDID,@INT_SERVICETYPEID,@INT_COMPLAINTTYPEID,@INT_STATUSFLAG, @INT_ANONYMOUS,@INT_PUBLISH,@INT_CALLTYPE)"

            Dim myTime As DateTime = DateTime.Now
            Dim tokenNo As String = myTime.Millisecond + 2000

            Using cmdForInsertComplain As New SqlCommand()
                With cmdForInsertComplain
                    .Connection = strcon
                    .CommandType = CommandType.Text
                    .CommandText = queryToInsertComplain
                    .Parameters.AddWithValue("@INT_REGDID", regId)
                    .Parameters.AddWithValue("@INT_REGD_MODE", 5)
                    .Parameters.AddWithValue("@DTM_REGD_DATE", Now())
                    .Parameters.AddWithValue("@VCH_TOKENNO", tokenNo)
                    .Parameters.AddWithValue("@INT_RECEIVEDBY", 5)
                    .Parameters.AddWithValue("@VCH_NAME", VCH_NAME)
                    .Parameters.AddWithValue("@VCH_PHONENO", VCH_PHONENO)
                    .Parameters.AddWithValue("@VCH_EMAIL", VCH_EMAIL)
                    .Parameters.AddWithValue("@INT_GENDER", INT_GENDER)
                    .Parameters.AddWithValue("@VCH_ADDRESS", VCH_ADDRESS)
                    .Parameters.AddWithValue("@INT_DISTRICTID", INT_DISTRICTID)
                    .Parameters.AddWithValue("@INT_ULBID", INT_ULBID)
                    .Parameters.AddWithValue("@INT_CIRCLEID", INT_CIRCLEID)
                    .Parameters.AddWithValue("@INT_WARDID", INT_WARDID)
                    .Parameters.AddWithValue("@INT_SERVICETYPEID", serviceType)
                    .Parameters.AddWithValue("@INT_COMPLAINTTYPEID", compId)
                    .Parameters.AddWithValue("@INT_STATUSFLAG", 0)
                    .Parameters.AddWithValue("@INT_ANONYMOUS", 0)
                    .Parameters.AddWithValue("@INT_PUBLISH", 0)
                    .Parameters.AddWithValue("@INT_CALLTYPE", 0)

                End With

                strcon.Close()
                strcon.Open()

                cmdForInsertComplain.ExecuteNonQuery()
                Return ""
            End Using
        End Using
    End Function

End Class