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

namespace  PostCardHitsuji.DataClient.DAO
{
    public class AddressDao : AbsDaoHelper<AddressContentModel>
    {

        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  tbladdress  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("        pre1 || pre2 || pre3 postalPre, ");
                sql.AppendLine("        post1 || post2 || post3 || post4 postalPost, ");
                sql.AppendLine("        pre1, ");
                sql.AppendLine("        pre2, ");
                sql.AppendLine("        pre3, ");
                sql.AppendLine("        post1, ");
                sql.AppendLine("        post2, ");
                sql.AppendLine("        post3, ");
                sql.AppendLine("        post4, ");
                sql.AppendLine("        address1, ");
                sql.AppendLine("        address2, ");
                sql.AppendLine("        pre1 || pre2 || pre3 || '-' || ");
                sql.AppendLine("         post1 || post2 || post3 || post4 POSTNUMBER ");
                sql.AppendLine("   from ");
                sql.AppendLine("        tbladdress ");
                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) + "' ");
                if (!string.IsNullOrEmpty(model.pre1))
                    sql.AppendLine("    and pre1 = '" + base.toSafety(model.pre1) + "' ");
                if (!string.IsNullOrEmpty(model.pre2))
                    sql.AppendLine("    and pre2 = '" + base.toSafety(model.pre2) + "' ");
                if (!string.IsNullOrEmpty(model.pre3))
                    sql.AppendLine("    and pre3 = '" + base.toSafety(model.pre3) + "' ");
                if (!string.IsNullOrEmpty(model.post1))
                    sql.AppendLine("    and post1 = '" + base.toSafety(model.post1) + "' ");
                if (!string.IsNullOrEmpty(model.post2))
                    sql.AppendLine("    and post2 = '" + base.toSafety(model.post2) + "' ");
                if (!string.IsNullOrEmpty(model.post3))
                    sql.AppendLine("    and post3 = '" + base.toSafety(model.post3) + "' ");
                if (!string.IsNullOrEmpty(model.post4))
                    sql.AppendLine("    and post4 = '" + base.toSafety(model.post4) + "' ");
                if (!string.IsNullOrEmpty(model.address2))
                    sql.AppendLine("    and address1 = '" + base.toSafety(model.address1) + "' ");
                if (!string.IsNullOrEmpty(model.address2))
                    sql.AppendLine("    and address2 = '" + base.toSafety(model.address2) + "' ");
                
                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<AddressContentModel>();
                foreach (DataRow row in table.Rows)
                {
                    AddressContentModel daoModel = new AddressContentModel();
                    outModelList.Add(daoModel);
                    setRowToModel(row, daoModel);
                }

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

        /// <summary>
        /// Field -> モデル へセット
        /// </summary>
        /// <param name="row">詰め元のフィールドを持つDataRow</param>
        /// <param name="model">詰め先のモデル</param>
        private void setRowToModel(DataRow row, AddressContentModel model)
        {
            model.Id =        base.getFieldAsString(row["id"]);
            model.Enable =    base.getFieldAsString(row["enable"]);
            model.postalPre = base.getFieldAsString(row["pre1"]) +
                              base.getFieldAsString(row["pre2"]) +
                              base.getFieldAsString(row["pre3"]);
            model.postalPost = base.getFieldAsString(row["post1"] ) +
                               base.getFieldAsString(row["post2"] ) +
                               base.getFieldAsString(row["post3"] ) +
                               base.getFieldAsString(row["post4"] );
            model.address1 = base.getFieldAsString(row["address1"]);
            model.address2 = base.getFieldAsString(row["address2"]);
        }

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

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" insert into tbladdress values");
            sql.AppendLine("  ( ");
            sql.AppendLine("     ?,");  //id
            sql.AppendLine("     ?, "); //enable
            sql.AppendLine("     ?, "); //pre1
            sql.AppendLine("     ?, "); //pre2
            sql.AppendLine("     ?, "); //pre3
            sql.AppendLine("     ?, "); //post1
            sql.AppendLine("     ?, "); //post2
            sql.AppendLine("     ?, "); //post3
            sql.AppendLine("     ?, "); //post4
            sql.AppendLine("     ?, "); //address1
            sql.AppendLine("     ?  "); //address2
            sql.AppendLine("  ); ");

            essence.setSql(sql.ToString());

            essence.addParam("@id", model.Id);
            essence.addParam("@enable", model.Enable);
            essence.addParam("@pre1", model.pre1);
            essence.addParam("@pre2", model.pre2);
            essence.addParam("@pre3", model.pre3);
            essence.addParam("@post1", model.post1);
            essence.addParam("@post2", model.post2);
            essence.addParam("@post3", model.post3);
            essence.addParam("@post4", model.post4);
            essence.addParam("@address1", model.address1);
            essence.addParam("@address2", model.address2);

            return essence.postUpdate(null);
        }

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

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" update tbladdress set ");
            sql.AppendLine("     enable = ?, ");
            sql.AppendLine("     pre1   = ?, ");
            sql.AppendLine("     pre2   = ?, ");
            sql.AppendLine("     pre3   = ?, ");
            sql.AppendLine("     post1  = ?, ");
            sql.AppendLine("     post2  = ?, ");
            sql.AppendLine("     post3  = ?, ");
            sql.AppendLine("     post4  = ?, ");
            sql.AppendLine("     address1 = ?, ");
            sql.AppendLine("     address2 = ? ");
            sql.AppendLine("  where ");
            sql.AppendLine("     id = ? ");

            essence.setSql(sql.ToString());

            essence.addParam("@enable", model.Enable);
            essence.addParam("@pre1", model.pre1);
            essence.addParam("@pre2", model.pre2);
            essence.addParam("@pre3", model.pre3);
            essence.addParam("@post1", model.post1);
            essence.addParam("@post2", model.post2);
            essence.addParam("@post3", model.post3);
            essence.addParam("@post4", model.post4);
            essence.addParam("@address1", model.address1);
            essence.addParam("@address2", model.address2);
            essence.addParam("@id", model.Id);

            return essence.postUpdate(null);
        }

    }
}
