using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;

namespace LYB.DAL.Helpers
{
    public class ComposerCommands
    {
        public static string cmdLoad = "SELECT composername, composerid FROM composer WHERE composerid=@composerid";
        public static string cmdLoadAll = "SELECT composername, composerid FROM composer";
        public static string cmdSave = "UPDATE composer SET " +
                                       " composername=@composername " +
                                       "WHERE composerid=@composerid";
        public static string cmdInsert = "INSERT INTO composer (composerid,composername) " +
                                         "VALUES (@composerid,@composername)";
        public static string cmdDelete = "DELETE FROM composer WHERE composerid=@composerid";
        public static string cmdGetNextComposerID = "SELECT MAX(composerid)AS highestid FROM composer";

    }
    public class CompositionCommands
    {
        public static string cmdLoad = "SELECT compositionname, keywords, compositionid, composerid, durationminutes, commentbeforecomposer, commentaftercomposer FROM composition WHERE compositionid=@compositionid";
        public static string cmdLoadAll = "SELECT compositionname, keywords, compositionid FROM composition";
        public static string cmdSave = "UPDATE composition SET " +
                                       " compositionname=@compositionname, " +
                                       " composerid=@composerid, " +
                                       " durationminutes=@durationminutes, " +
                                       " commentbeforecomposer=@commentbeforecomposer, " +
                                       " commentaftercomposer=@commentaftercomposer, " +
                                       " keywords=@keywords " +
                                       "WHERE compositionid=@compositionid";
        public static string cmdInsert = "INSERT INTO composition (compositionid,keywords,compositionname,composerid,durationminutes,commentbeforecomposer,commentaftercomposer) " +
                                         "VALUES (@compositionid,@keywords,@compositionname,@composerid,@durationminutes,@commentbeforecomposer,@commentaftercomposer)";
        public static string cmdDelete = "DELETE FROM composition WHERE compositionid=@compositionid";
        public static string cmdGetNextCompositionID = "SELECT MAX(compositionid)AS highestid FROM composition";

    }
    public class HeadingCommands
    {
        public static string cmdLoad = "SELECT headingtext, headingfontfamily, headingfontsize, headingid FROM heading WHERE headingid=@headingid";
        public static string cmdLoadAll = "SELECT headingtext, headingid FROM heading";
        public static string cmdSave = "UPDATE heading SET " +
                                       " headingtext=@headingtext, " +
                                       " headingfontfamily=@headingfontfamily, " +
                                       " headingfontsize=@headingfontsize " +
                                       "WHERE headingid=@headingid";
        public static string cmdInsert = "INSERT INTO heading (headingid,headingtext,headingfontfamily,headingfontsize) " +
                                         "VALUES (@headingid,@headingtext,@headingfontfamily,@headingfontsize)";
        public static string cmdDelete = "DELETE FROM heading WHERE headingid=@headingid";
        public static string cmdGetNextHeadingID = "SELECT MAX(headingid)AS highestid FROM heading";

    }
    public class DocumentListCommands
    {
        public static string cmdLoad = "SELECT groupid, documentlistid, headingid, compositionid, sortorder FROM documentlist WHERE documentlistid=@documentlistid";
        public static string cmdLoadAll = "SELECT g.groupname, c.compositionname, h.headingtext, dl.documentlistid FROM documentlist dl INNER JOIN [group] g ON g.groupid = dl.groupid LEFT OUTER JOIN composition c ON c.compositionid = dl.compositionid LEFT OUTER JOIN heading h ON h.headingid = dl.headingid ";
        public static string cmdSave1 = "UPDATE documentlist SET " +
                                       " groupid=@groupid, " +
                                       " compositionid=@compositionid, " +
                                       " sortorder=@sortorder " +
                                       "WHERE documentlistid=@documentlistid";
        public static string cmdSave2 = "UPDATE documentlist SET " +
                                       " groupid=@groupid, " +
                                       " headingid=@headingid, " +
                                       " sortorder=@sortorder " +
                                       "WHERE documentlistid=@documentlistid";
        public static string cmdInsert1 = "INSERT INTO documentlist (documentlistid,groupid,compositionid,sortorder) " +
                                         "VALUES (@documentlistid,@groupid,@compositionid,@sortorder)";
        public static string cmdInsert2 = "INSERT INTO documentlist (documentlistid,groupid,headingid,sortorder) " +
                                         "VALUES (@documentlistid,@groupid,@headingid,@sortorder)";
        public static string cmdDelete = "DELETE FROM documentlist WHERE documentlistid=@documentlistid";
        public static string cmdGetNextDocumentListID = "SELECT MAX(documentlistid)AS highestid FROM documentlist";

    }
}