DayPilot Gantt Chart for ASP.NET

/*
Copyright © 2010 - 2013 Annpoint, s.r.o.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

-------------------------------------------------------------------------

NOTE: Reuse requires the following acknowledgement (see also NOTICE):
This product includes DayPilot (http://www.daypilot.org) developed by Annpoint, s.r.o.
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Web;
using Util;

/// <summary>
/// Summary description for DataManager
/// </summary>
public class DataManager
{

    #region Helper methods
    private string ConnectionString
    {
        get { return Db.ConnectionString(); }
    }

    private DbProviderFactory Factory
    {
        get { return Db.Factory(); }
    }

    private DbConnection CreateConnection()
    {
        DbConnection connection = Factory.CreateConnection();
        connection.ConnectionString = ConnectionString;
        return connection;
    }

    private DbCommand CreateCommand(string text)
    {
        DbCommand command = Factory.CreateCommand();
        command.CommandText = text;
        command.Connection = CreateConnection();

        return command;
    }

    private DbCommand CreateCommand(string text, DbConnection connection)
    {
        DbCommand command = Factory.CreateCommand();
        command.CommandText = text;
        command.Connection = connection;

        return command;
    }

    private void AddParameterWithValue(DbCommand cmd, string name, object value)
    {
        var parameter = Factory.CreateParameter();
        parameter.Direction = ParameterDirection.Input;
        parameter.ParameterName = name;
        parameter.Value = value;
        cmd.Parameters.Add(parameter);
    }

    private int GetIdentity(DbConnection c)
    {
        var cmd = CreateCommand(Db.IdentityCommand(), c);
        return Convert.ToInt32(cmd.ExecuteScalar());
    }

    private DbDataAdapter CreateDataAdapter(string select)
    {
        DbDataAdapter da = Factory.CreateDataAdapter();
        da.SelectCommand = CreateCommand(select);
        return da;
    }

    #endregion


    public DataRow GetAssignment(int id)
    {
        var da = CreateDataAdapter("select * from [Assignment] where [Assignment].[AssignmentId] = @id");
        AddParameterWithValue(da.SelectCommand, "id", id);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count == 1)
        {
            return dt.Rows[0];
        }
        return null;
    }


    public DataTable GetAssignments()
    {
        DataTable dt = new DataTable();
        var da = CreateDataAdapter("select * from [Assignment]");
        da.Fill(dt);
        return dt;
    }

    public void UpdateAssignment(int id, string note, int duration, DateTime start)
    {
        using (var con = CreateConnection())
        {
            con.Open();

            var cmd = CreateCommand("update [Assignment] set [AssignmentNote] = @note, [AssignmentStart] = @start, [AssignmentEnd] = @end, [AssignmentDuration] = @duration where [AssignmentId] = @id", con);
            AddParameterWithValue(cmd, "id", id);
            AddParameterWithValue(cmd, "note", note);
            AddParameterWithValue(cmd, "start", start);
            AddParameterWithValue(cmd, "end", start.AddDays(duration));
            AddParameterWithValue(cmd, "duration", duration);
            cmd.ExecuteNonQuery();
        }
    }

    public void DeleteAssignment(int id)
    {
        using (var con = CreateConnection())
        {
            con.Open();

            var cmd = CreateCommand("delete from [Assignment] where [AssignmentId] = @id", con);
            AddParameterWithValue(cmd, "id", id);
            cmd.ExecuteNonQuery();
        }
    }


    public void CreateAssignment(DateTime start, int duration, string note)
    {
        DateTime zero = new DateTime(2000, 1, 1);
        using (DbConnection con = CreateConnection())
        {
            con.Open();

            var cmd = CreateCommand("insert into [Assignment] ([AssignmentDuration], [AssignmentNote], [AssignmentStart], [AssignmentEnd]) values (@duration, @note, @start, @end)", con);
            AddParameterWithValue(cmd, "duration", duration);
            AddParameterWithValue(cmd, "note", note);
            AddParameterWithValue(cmd, "start", start);
            AddParameterWithValue(cmd, "end", start.AddDays(duration));

            cmd.ExecuteNonQuery();

        }
    }


    public string GetUserConfig(string userId, string key)
    {
        var da = CreateDataAdapter("select * from [UserConfig] where [UserId] = @id and [UserConfigKey] = @key order by [UserConfigId]");
        AddParameterWithValue(da.SelectCommand, "id", userId);
        AddParameterWithValue(da.SelectCommand, "key", key);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            return (string) dt.Rows[dt.Rows.Count - 1]["UserConfigValue"];
        }
        return null;
    }

    public void SetUserConfig(string userId, string key, string value)
    {
        // TODO transaction
        string old = GetUserConfig(userId, key);
        if (old == null)
        {
            using (DbConnection con = CreateConnection())
            {
                con.Open();
                var cmd = CreateCommand("insert into [UserConfig] ([UserId], [UserConfigKey], [UserConfigValue]) values (@id, @key, @value)", con);
                AddParameterWithValue(cmd, "id", userId);
                AddParameterWithValue(cmd, "key", key);
                AddParameterWithValue(cmd, "value", value);
                cmd.ExecuteNonQuery();
            }
            
        }
        else
        {
            using (DbConnection con = CreateConnection())
            {
                con.Open();
                var cmd = CreateCommand("update [UserConfig] set [UserId] = @id, [UserConfigKey] = @key, [UserConfigValue] = @value", con);
                AddParameterWithValue(cmd, "id", userId);
                AddParameterWithValue(cmd, "key", key);
                AddParameterWithValue(cmd, "value", value);
                cmd.ExecuteNonQuery();
            }
        }

    }

}