using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using PostCardHitsuji.DataClient.Model;

namespace  PostCardHitsuji.DataClient.DAO
{
    public class GroupDao : AbsDaoHelper<GroupContentModel>
    {

        private Dictionary<string, string> orderDictionary_ = new Dictionary<string, string>();
        public Dictionary<string, string> orderDictionary
        {
            get { return orderDictionary_; }
            set { orderDictionary_ = value; }
        }

        public int selectLatestId()
        {
            var essence = base.getDaoEssence();
            essence.setSql(" select ifnull(max(id), 0) as id from tblgroup where id is not null");
            DataTable table = ((DataSet)essence.postSelect(null)).Tables[0];

            var result = 0;
            foreach (DataRow row in table.Rows)
                result = int.Parse(base.getFieldAsString(row["id"]));
            return result;
        }

        public object selectSimple(object obj)
        {
            try
            {
                var essence = base.getDaoEssence();
                var model = inModel;

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" select ");
                sql.AppendLine("        id, ");
                sql.AppendLine("        enable, ");
                sql.AppendLine("        groupname ");
                sql.AppendLine("   from ");
                sql.AppendLine("        tblgroup ");
                sql.AppendLine("  where ");
                sql.AppendLine("        id is not null ");

                if (!string.IsNullOrEmpty(model.Id))
                    sql.AppendLine("    and id = '" + base.toSafety(model.Id) + "' ");
                if (!string.IsNullOrEmpty(model.Enable))
                    sql.AppendLine("    and enable = '" + base.toSafety(model.Enable.ToUpper()) + "' ");
                if (!string.IsNullOrEmpty(model.group))
                    sql.AppendLine("    and groupname = '" + base.toSafety(model.group) + "' ");

                if (orderDictionary_.Count > 0)
                {
                    sql.AppendLine(" order by ");
                    StringBuilder order = new StringBuilder();
                    foreach (KeyValuePair<string, string> kvp in orderDictionary_)
                    {
                        order.Append("■");
                        order.Append(kvp.Key);   //カラム
                        order.Append(" ");
                        order.Append(kvp.Value); //順（asc/desc）
                        order.Append("■");
                    }
                    string orderText
                        = order.ToString()
                            .Replace("■■", ", ")
                            .Replace("■", "");
                    sql.AppendLine(orderText);
                }

                essence.setSql(sql.ToString());

                var data = (DataSet)essence.postSelect(null);
                DataTable table = data.Tables[0];
                
                outModelList = new List<GroupContentModel>();
                foreach (DataRow row in table.Rows)
                {
                    GroupContentModel daoModel = new GroupContentModel();
                    outModelList.Add(daoModel);
                    daoModel.Id = base.getFieldAsString(row["id"]);
                    daoModel.Enable = base.getFieldAsString(row["enable"]);
                    daoModel.group = base.getFieldAsString(row["groupname"]);
                }

                return outModelList;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return null;
            }
        }

        public int insertSimple(object obj)
        {
            try
            {
                var essence = base.getDaoEssence();
                var model = inModel;

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" insert into tblgroup ");
                sql.AppendLine("  ( ");
                sql.AppendLine("     id, ");
                sql.AppendLine("     enable, ");
                sql.AppendLine("     groupname ");
                sql.AppendLine("  )values( ");
                sql.AppendLine("     ?, ");
                sql.AppendLine("     ?, ");
                sql.AppendLine("     ? ");
                sql.AppendLine("  ); ");

                essence.setSql(sql.ToString());

                essence.addParam("@id", model.Id);
                essence.addParam("@enable", model.Enable);
                essence.addParam("@groupname", model.group);
                
                return essence.postUpdate(null);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return -1;
            }
        }

        public int updateSimple(object obj)
        {
            try
            {
                var essence = base.getDaoEssence();
                var model = inModel;

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" update tblgroup set ");
                sql.AppendLine("     enable    = ?, ");
                sql.AppendLine("     groupname = ? ");
                sql.AppendLine("  where ");
                sql.AppendLine("     id = ? ");

                essence.setSql(sql.ToString());

                essence.addParam("@enable", model.Enable);
                essence.addParam("@groupname", model.group);
                essence.addParam("@id", model.Id);

                return essence.postUpdate(null);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return -1;
            }
        }

    }
}
