<%@ WebService language="C#" class="AdvocateServer" %>

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Threading;
using System.Web.Services;
using System.Xml.Serialization;
using Mono.Data.SqliteClient;


public class Note
{
	private int id;
	private string format;
	private string author;
	private string title;
	private string body;
	private string links;
	private DateTime created;
	private DateTime modified;
		
	public int ID
	{
		get{ return id; }
		set{ id = value;}
	}
	public string Format
	{
		get{ return format; }
		set{ format = value; }
	}
	public string Author
	{
		get{ return author; }
		set{ author = value;}
	}
	public string Title
    {
        get{ return title; }
        set{ title = value; }
    }
    public string Body
    {
        get{ return body; }
        set{ body = value; }
    }
	public string Links
	{
		get{ return links; }
		set{ links = value;}
	}
	
	public DateTime Created
	{
		get{ return created; }
		set{ created = value; }
	}
	public DateTime Modified
	{
		get{ return modified; }
		set{ modified = value; }
	}
}


public class NoteList
{
	private Note[] notes;
	
	public NoteList( )
	{
	}
		
	public Note[] Notes
	{
		get{ return notes; }
		set{ notes = value; }
	}
	
}


public class AdvocateServer {

    private IDbConnection dbcon;
    private IDbCommand dbcmd;
	private string windowsURI;
	private string linuxURI;
	private string currentURI;

    public AdvocateServer( )
    {

		windowsURI = "URI=file:c:/temp/svn/Advocate/web/Advocate.db";
		linuxURI = "URI=file:/home/blursoft/public_html/devFarm/sprouts/web/Advocate.db";		           
		//
		// EDIT ME: set to indicate which system you're testing on
		//
		//currentURI = windowsURI;
		currentURI = linuxURI;

		dbcon = null;
		dbcmd = null;
		
    }

	private void OpenDatabase( )
	{
		dbcon = new SqliteConnection( currentURI );
        dbcon.Open();
		dbcmd = dbcon.CreateCommand();
	}
	
	private void CloseDatabase( )
	{
		if( dbcon.State == ConnectionState.Open )
			dbcon.Close( );
	}
	
	private string EscapeString( string s )
	{
		s = s.Replace("\"","\"\"");
		return s;
		
	}
	
	private DateTime DateFromString( string date )
	{
		string[] elements = date.Split( new Char[] {'-',' ',':'} );
		
		int year = Convert.ToInt32( elements[0] );
		int month = Convert.ToInt32( elements[1] );
		int day = Convert.ToInt32( elements[2] );
		int hour = Convert.ToInt32( elements[3] );
		int minute = Convert.ToInt32( elements[4] );
		int second = Convert.ToInt32( elements[5] );
		
		return new DateTime( year, month, day, hour, minute, second );
	}
	
    [WebMethod]
    public void AddNote( Note note )
    {
        OpenDatabase( );

		string body = EscapeString(note.Body);
		string title = EscapeString(note.Title);
        string created = note.Created.ToString("yyyy-MM-dd HH:mm:ss");
        string modified = note.Modified.ToString("yyyy-MM-dd HH:mm:ss");
        //string sql = "INSERT INTO Note VALUES (NULL,\""+author+"\",\""+body+"\",\""+title+"\")";
		string sql = "INSERT INTO Note VALUES (NULL,\""+note.Format+"\",\""+note.Author+"\",\""+title+"\",\""+body+"\",\""+note.Links+"\",\""+created+"\",\""+modified+"\")";
		/*
		string sql = "INSERT INTO Note VALUES (NULL, @author , @body , @title)";
        dbcmd.CommandText = sql;
		dbcmd.Parameters.Add("Author",OleDbType.WChar);
		dbcmd.Parameters[0].Value = author;
		dbcmd.Parameters.Add("Body",OleDbType.WChar);
		dbcmd.Parameters[1].Value = body;
		dbcmd.Parameters.Add("Title",OleDbType.WChar);
		dbcmd.Parameters[2].Value = title;
		*/
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery();
		
		
		CloseDatabase( );

    }


	[WebMethod]
	public void UpdateNote( Note note )
	{
		OpenDatabase( );
		note.Body = EscapeString( note.Body );
		string modified = note.Modified.ToString("yyyy-MM-dd HH:mm:ss");
		//string sql = "UPDATE Note SET Body=\""+note.Body+"\" WHERE NoteID=\""+note.ID+"\"";
		string sql = "UPDATE Note SET Body=\""+note.Body+"\",DateModified=\""+modified+"\" WHERE NoteID=\""+note.ID+"\"";
		
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery( );
		
		CloseDatabase( );

	}


    [WebMethod]
    public Note GetNoteByTitle( string title )
    {
       
		Note n = null;
        OpenDatabase( );
		string sql = "select * from Note where Title=\""+title+"\"";
		dbcmd.CommandText = sql;
		IDataReader reader = dbcmd.ExecuteReader();
		
		while(reader.Read())
		{
			n = new Note( );
			n.ID = reader.GetInt32( 0 );
			n.Format = reader.GetString( 1 );
			n.Author = reader.GetString( 2 );
			n.Title = reader.GetString( 3 );
			n.Body = reader.GetString( 4 );
			n.Links = reader.GetString( 5 );
			n.Created = DateFromString( reader.GetString( 6 ));
			n.Modified = DateFromString( reader.GetString( 7 ));
		}
				
		CloseDatabase( );
        if( reader != null )
		{
			reader.Close();
        }
				
		return n;

    }


	[WebMethod]
    public Note GetNoteByID( int id )
    {
       
		Note n = null;
        OpenDatabase( );
		string sql = "select * from Note where NoteID=\""+id+"\"";
		dbcmd.CommandText = sql;
		IDataReader reader = dbcmd.ExecuteReader();
		
		while(reader.Read())
		{
			n = new Note();
			n.ID = reader.GetInt32( 0 );
			n.Format = reader.GetString( 1 );
			n.Author = reader.GetString( 2 );
			n.Title = reader.GetString( 3 );
			n.Body = reader.GetString( 4 );
			n.Links = reader.GetString( 5 );
			n.Created = DateFromString( reader.GetString( 6 ));
			n.Modified = DateFromString( reader.GetString( 7 ));
		}
				
		CloseDatabase( );
        if( reader != null )
		{
			reader.Close();
        }
				
		return n;

    }

	
	[WebMethod]
	public NoteList SearchByString( string query )
	{
		NoteList list = null;
		Note[] notes;
		string[] keywords = query.Split( new Char[] {' '} );
		string sql = "select * from Note";
		ArrayList noteslist;
		
		OpenDatabase( );
		dbcmd.CommandText = sql;
		
		IDataReader reader = dbcmd.ExecuteReader();
		
		noteslist = new ArrayList();
		
		while( reader.Read() )
		{
			Note n = new Note( );
			n.ID = reader.GetInt32( 0 );
			n.Format = reader.GetString( 1 );
			n.Author = reader.GetString( 2 );
			n.Title = reader.GetString( 3 );
			n.Body = reader.GetString( 4 );
			n.Links = reader.GetString( 5 );
			n.Created = DateFromString( reader.GetString( 6 ));
			n.Modified = DateFromString( reader.GetString( 7 ));
			noteslist.Add( n );
		}
		
		
		if( reader != null )
		{
			reader.Close();
        }
		CloseDatabase( );
		
		
		
		if( noteslist.Count != 0 )
		{
			list = new NoteList( );
			notes = new Note[ noteslist.Count ];
				
			IEnumerator iter = noteslist.GetEnumerator( );
			int i = 0;
			
			while( iter.MoveNext())
			{
				notes[i] = (Note)iter.Current;
				i++;
			}
			list.Notes = notes;
		}
		
		
		return list;
		
	}
	
	
    [WebMethod]
    public void DeleteNote( int noteID )
    {
   		OpenDatabase();
		string sql = "DELETE FROM Note WHERE NoteID=\""+noteID+"\"";
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery();
		CloseDatabase(); 
    }


    [WebMethod]
    public NoteList GetNoteList( )
    {
    	Note[] notes = null;
		NoteList list = null;
		string sql = "select * from Note";
		ArrayList noteslist;
		
		OpenDatabase( );
		dbcmd.CommandText = sql;
		
		IDataReader reader = dbcmd.ExecuteReader();
		
		noteslist = new ArrayList();
		
		while( reader.Read() )
		{
			Note n = new Note( );
			n.ID = reader.GetInt32( 0 );
			n.Format = reader.GetString( 1 );
			n.Author = reader.GetString( 2 );
			n.Title = reader.GetString( 3 );
			n.Body = reader.GetString( 4 );
			n.Links = reader.GetString( 5 );
			n.Created = DateFromString( reader.GetString( 6 ));
			n.Modified = DateFromString( reader.GetString( 7 ));
			noteslist.Add( n );
		}
		
		
		if( reader != null )
		{
			reader.Close();
        }
		CloseDatabase( );
		
		
		
		if( noteslist.Count != 0 )
		{
			list = new NoteList( );
			notes = new Note[ noteslist.Count ];
				
			IEnumerator iter = noteslist.GetEnumerator( );
			int i = 0;
			
			while( iter.MoveNext())
			{
				notes[i] = (Note)iter.Current;
				i++;
			}
			list.Notes = notes;
		}
		
		
		return list;
		
    }



}