using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Hitsuji.Framework.Utils;
using PostCardHitsuji.DataClient.Model;

namespace  PostCardHitsuji.DataClient.DAO
{
    public class PersonDao : AbsDaoHelper<PersonContentModel>
    {

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

        /// <summary>
        /// 最新のＩＤ番号を取得
        /// </summary>
        /// <returns></returns>
        public int selectLatestId()
        {
            var essence = base.getDaoEssence();
            essence.setSql("select ifnull(max(id), 0) as id from tblperson  where id is not null");
            DataTable table = ((DataSet)essence.postSelect(null)).Tables[0];

            //１record
            int 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 ")
                    .AppendLine("       tblperson.id id,")
                    .AppendLine("       tblperson.whois whois,")
                    .AppendLine("       tblperson.enable,")
                    .AppendLine("       tblperson.postalpre1,")
                    .AppendLine("       tblperson.postalpre2,")
                    .AppendLine("       tblperson.postalpre3,")
                    .AppendLine("       tblperson.postalsuffix1,")
                    .AppendLine("       tblperson.postalsuffix2,")
                    .AppendLine("       tblperson.postalsuffix3,")
                    .AppendLine("       tblperson.postalsuffix4,")
                    .AppendLine("       tblperson.name1,")
                    .AppendLine("       tblperson.name2,")
                    .AppendLine("       tblperson.name3,")
                    .AppendLine("       tblperson.name4,")
                    .AppendLine("       tblperson.namesuffix1,")
                    .AppendLine("       tblperson.namesuffix2,")
                    .AppendLine("       tblperson.namesuffix3,")
                    .AppendLine("       tblperson.namesuffix4,")
                    .AppendLine("       tblperson.address1,")
                    .AppendLine("       tblperson.address2,")
                    .AppendLine("       tblperson.address3,")
                    .AppendLine("       tblperson.tel,")
                    .AppendLine("       tblperson.memo,")
                    .AppendLine("       tblperson.layoutid,")
                    .AppendLine("       tblperson.groupid,")
                    .AppendLine("       grp.groupname,")
                    .AppendLine("       sender.id       sender_id,")
                    .AppendLine("       sender.postalpre1  sender_Pre1,")
                    .AppendLine("       sender.postalpre2  sender_Pre2,")
                    .AppendLine("       sender.postalpre3  sender_Pre3,")
                    .AppendLine("       sender.postalsuffix1 sender_suffix1,")
                    .AppendLine("       sender.postalsuffix2 sender_suffix2,")
                    .AppendLine("       sender.postalsuffix3 sender_suffix3,")
                    .AppendLine("       sender.postalsuffix4 sender_suffix4,")
                    .AppendLine("       sender.name1    sender_name1,")
                    .AppendLine("       sender.name2    sender_name2,")
                    .AppendLine("       sender.name3    sender_name3,")
                    .AppendLine("       sender.name4    sender_name4,")
                    .AppendLine("       sender.address1 sender_address1,")
                    .AppendLine("       sender.address2 sender_address2,")
                    .AppendLine("       sender.address3 sender_address3,")
                    .AppendLine("       sender.tel      sender_tel")
                    .AppendLine("  from tblperson")
                    .AppendLine("  left join tbllayout layout")
                    .AppendLine("    on tblperson.layoutid = layout.id")
                    .AppendLine("  left join tblgroup grp")
                    .AppendLine("    on tblperson.groupid = grp.id")
                    .AppendLine("  left join tblperson sender")
                    .AppendLine("    on tblperson.senderid = sender.id")
                    .AppendLine("  where ")
                    .AppendLine("        tblperson.id is not null ");

                if (!model.Enable.Equals(""))
                    sql.Append("  and tblperson.enable = '").Append(model.Enable).AppendLine("' ");

                if (!model.whoIs.Equals(""))
                    sql.Append("  and tblperson.whois = '").Append(model.whoIs).AppendLine("' ");

                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<PersonContentModel>();
                foreach (DataRow row in table.Rows)
                {
                    PersonContentModel pModel = new PersonContentModel(PersonContentModel.PersonTypes.Reseiver);
                    outModelList.Add(pModel);
                    pModel.Id = base.getFieldAsString(row["id"]);
                    pModel.whoIs = base.getFieldAsString(row["whois"]);
                    pModel.Enable = base.getFieldAsString(row["enable"]);
                    pModel.groupId = base.getFieldAsString(row["groupid"]);
                    pModel.groupName = base.getFieldAsString(row["groupname"]);
                    pModel.postalPre = base.getFieldAsString(row["postalpre1"]) +
                                       base.getFieldAsString(row["postalpre2"]) +
                                       base.getFieldAsString(row["postalpre3"]);
                    pModel.postalPost = base.getFieldAsString(row["postalsuffix1"]) +
                                        base.getFieldAsString(row["postalsuffix2"]) +
                                        base.getFieldAsString(row["postalsuffix3"]) +
                                        base.getFieldAsString(row["postalsuffix4"]);
                    pModel.name1 = base.getFieldAsString(row["name1"]);
                    pModel.name2 = base.getFieldAsString(row["name2"]);
                    pModel.name3 = base.getFieldAsString(row["name3"]);
                    pModel.name4 = base.getFieldAsString(row["name4"]);
                    pModel.namePost1 = base.getFieldAsString(row["namesuffix1"]);
                    pModel.namePost2 = base.getFieldAsString(row["namesuffix2"]);
                    pModel.namePost3 = base.getFieldAsString(row["namesuffix3"]);
                    pModel.namePost4 = base.getFieldAsString(row["namesuffix4"]);
                    pModel.address1 = base.getFieldAsString(row["address1"]);
                    pModel.address2 = base.getFieldAsString(row["address2"]);
                    pModel.address3 = base.getFieldAsString(row["address3"]);
                    pModel.tel = base.getFieldAsString(row["tel"]);
                    pModel.memo = base.getFieldAsString(row["memo"]);
                    pModel.layoutId = base.getFieldAsString(row["layoutid"]);

                    pModel.Sender.Id = base.getFieldAsString(row["sender_id"]);
                    pModel.Sender.postalPre = base.getFieldAsString(row["sender_pre1"]) +
                                              base.getFieldAsString(row["sender_pre2"]) +
                                              base.getFieldAsString(row["sender_pre3"]);
                    pModel.Sender.postalPost = base.getFieldAsString(row["sender_suffix1"]) +
                                               base.getFieldAsString(row["sender_suffix2"]) +
                                               base.getFieldAsString(row["sender_suffix3"]) +
                                               base.getFieldAsString(row["sender_suffix4"]);
                    pModel.Sender.name1 = base.getFieldAsString(row["sender_name1"]);
                    pModel.Sender.name2 = base.getFieldAsString(row["sender_name2"]);
                    pModel.Sender.name3 = base.getFieldAsString(row["sender_name3"]);
                    pModel.Sender.name4 = base.getFieldAsString(row["sender_name4"]);
                    pModel.Sender.address1 = base.getFieldAsString(row["sender_address1"]);
                    pModel.Sender.address2 = base.getFieldAsString(row["sender_address2"]);
                    pModel.Sender.address3 = base.getFieldAsString(row["sender_address3"]);
                    pModel.Sender.tel = base.getFieldAsString(row["sender_tel"]);
                }

                return outModelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

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

                StringBuilder sql = new StringBuilder();
                sql.AppendLine("insert into tblperson values");
                sql.AppendLine("(");
                sql.AppendLine("    ?,"); //id
                sql.AppendLine("    ?,");//whois
                sql.AppendLine("    ?,");//enable
                sql.AppendLine("    ?,");//groupid
                sql.AppendLine("    ?,");//postalpre1
                sql.AppendLine("    ?,");//postalpre2
                sql.AppendLine("    ?,");//postalpre3
                sql.AppendLine("    ?,");//postalsuffix1
                sql.AppendLine("    ?,");//postalsuffix2
                sql.AppendLine("    ?,");//postalsuffix3
                sql.AppendLine("    ?,");//postalsuffix4
                sql.AppendLine("    ?,");//address1
                sql.AppendLine("    ?,");//address2
                sql.AppendLine("    ?,");//address3
                sql.AppendLine("    ?,");//name1
                sql.AppendLine("    ?,");//name2
                sql.AppendLine("    ?,");//name3
                sql.AppendLine("    ?,");//name4
                sql.AppendLine("    ?,");//namesuffix1
                sql.AppendLine("    ?,");//namesuffix2
                sql.AppendLine("    ?,");//namesuffix3
                sql.AppendLine("    ?,");//namesuffix4
                sql.AppendLine("    ?,");//tel
                sql.AppendLine("    ?,");//memo
                sql.AppendLine("    ?,");//senderid
                sql.AppendLine("    ?");//layoutid
                sql.AppendLine(");");

                essence.setSql(sql.ToString());

                essence.clearAllParams();
                essence.addParam("@id", model.Id);
                essence.addParam("@whois", model.whoIs);
                essence.addParam("@enable", model.Enable);
                essence.addParam("@groupid", ValueUtil.tryGetString(model.groupId, "0"));
                essence.addParam("@postalpre1", model.pre1);
                essence.addParam("@postalpre2", model.pre2);
                essence.addParam("@postalpre3", model.pre3);
                essence.addParam("@postalsuffix1", model.post1);
                essence.addParam("@postalsuffix2", model.post2);
                essence.addParam("@postalsuffix3", model.post3);
                essence.addParam("@postalsuffix4", model.post4);
                essence.addParam("@address1", model.address1);
                essence.addParam("@address2", model.address2);
                essence.addParam("@address3", model.address3);
                essence.addParam("@name1", model.name1);
                essence.addParam("@name2", model.name2);
                essence.addParam("@name3", model.name3);
                essence.addParam("@name4", model.name4);
                essence.addParam("@namesuffix1", model.namePost1);
                essence.addParam("@namesuffix2", model.namePost2);
                essence.addParam("@namesuffix3", model.namePost3);
                essence.addParam("@namesuffix4", model.namePost4);
                essence.addParam("@tel", model.tel);
                essence.addParam("@memo", model.memo);
                essence.addParam("@senderid", ValueUtil.tryGetString(model.Sender.Id, "0"));
                essence.addParam("@layoutid", ValueUtil.tryGetString(model.layoutId, "0"));

                essence.postUpdate(null);

                return 1;
            }
            catch (Exception ex)
            {
                essence.rollback();
                throw ex;
            }
        }

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

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" update tblperson set ");
                sql.AppendLine("    whois         = ?,");
                sql.AppendLine("    enable        = ?,");
                sql.AppendLine("    groupid       = ?,");
                sql.AppendLine("    postalpre1    = ?,");
                sql.AppendLine("    postalpre2    = ?,");
                sql.AppendLine("    postalpre3    = ?,");
                sql.AppendLine("    postalsuffix1 = ?,");
                sql.AppendLine("    postalsuffix2 = ?,");
                sql.AppendLine("    postalsuffix3 = ?,");
                sql.AppendLine("    postalsuffix4 = ?,");
                sql.AppendLine("    address1      = ?,");
                sql.AppendLine("    address2      = ?,");
                sql.AppendLine("    address3      = ?,");
                sql.AppendLine("    name1         = ?,");
                sql.AppendLine("    name2         = ?,");
                sql.AppendLine("    name3         = ?,");
                sql.AppendLine("    name4         = ?,");
                sql.AppendLine("    namesuffix1   = ?,");
                sql.AppendLine("    namesuffix2   = ?,");
                sql.AppendLine("    namesuffix3   = ?,");
                sql.AppendLine("    namesuffix4   = ?,");
                sql.AppendLine("    tel           = ?,");
                sql.AppendLine("    memo          = ?,");
                sql.AppendLine("    senderid      = ?");
                sql.AppendLine("  where ");
                sql.AppendLine("     id = ? ");

                essence.setSql(sql.ToString());

                essence.clearAllParams();
                essence.addParam("@whois", model.whoIs);
                essence.addParam("@enable", model.Enable);
                essence.addParam("@groupid", model.groupId);
                essence.addParam("@postalpre1", model.pre1);
                essence.addParam("@postalpre2", model.pre2);
                essence.addParam("@postalpre3", model.pre3);
                essence.addParam("@postalsuffix1", model.post1);
                essence.addParam("@postalsuffix2", model.post2);
                essence.addParam("@postalsuffix3", model.post3);
                essence.addParam("@postalsuffix4", model.post4);
                essence.addParam("@address1", model.address1);
                essence.addParam("@address2", model.address2);
                essence.addParam("@address3", model.address3);
                essence.addParam("@name1", model.name1);
                essence.addParam("@name2", model.name2);
                essence.addParam("@name3", model.name3);
                essence.addParam("@name4", model.name4);
                essence.addParam("@namesuffix1", model.namePost1);
                essence.addParam("@namesuffix2", model.namePost2);
                essence.addParam("@namesuffix3", model.namePost3);
                essence.addParam("@namesuffix4", model.namePost4);
                essence.addParam("@tel", model.tel);
                essence.addParam("@memo", model.memo);
                essence.addParam("@senderid", model.Sender.Id);

                essence.addParam("@id", model.Id);
                essence.postUpdate(null);

                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

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

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" update tblperson set ");
                sql.AppendLine("    layoutid = ?");
                sql.AppendLine("  where ");
                sql.AppendLine("     id = ? ");

                essence.setSql(sql.ToString());

                essence.clearAllParams();
                essence.addParam("@layoutid", model.layoutId);
                essence.addParam("@id", model.Id);
                var count = essence.postUpdate(null);

                return count;//essence.postUpdate(null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
