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

namespace  PostCardHitsuji.DataClient.DAO
{
    class IllustLayoutDao : AbsDaoHelper<IllustLayoutContentModel>
    {

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

        /// <summary>
        /// レイアウト一覧を取得
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public object selectLayout(object obj)
        {
            try
            {
                var essence = base.getDaoEssence();
                var model = inModel;

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" select ");
                sql.AppendLine("        ifnull(layout.id, -1)  id, ");
                sql.AppendLine("        layout.enable          enable, ");
                sql.AppendLine("        layout.layouttype      layouttype, ");
                sql.AppendLine("        layout.year            year, ");
                sql.AppendLine("        layout.layoutname      layoutname, ");
                sql.AppendLine("        layout.layoutvector    layoutvector ");

                sql.AppendLine("   from tbllayout layout");

                sql.AppendLine("  where ");
                sql.AppendLine("        layout.id is not null ");

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

                if (!string.IsNullOrEmpty(model.layoutType))
                    sql.AppendLine("    and layouttype = '" + base.toSafety(model.layoutType) + "' ");

                if (!string.IsNullOrEmpty(model.year))
                    sql.AppendLine("    and year = '" + base.toSafety(model.year) + "' ");

                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<IllustLayoutContentModel>();
                foreach (DataRow row in table.Rows)
                {
                    var daoModel = new IllustLayoutContentModel();
                    outModelList.Add(daoModel);
                    daoModel.Id = base.getFieldAsString(row["id"]);
                    daoModel.Enable = base.getFieldAsString(row["enable"]);
                    daoModel.layoutType = base.getFieldAsString(row["layouttype"]);
                    daoModel.year = base.getFieldAsString(row["year"]);
                    daoModel.layoutName = base.getFieldAsString(row["layoutname"]);
                    daoModel.layoutVector = base.getFieldAsString(row["layoutvector"]);

                }

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

        //人情報一覧データ
        public object selectSimple(object obj)
        {
            try
            {
                var essence = base.getDaoEssence();
                var model = inModel;

                StringBuilder sql = new StringBuilder();
                sql.AppendLine(" select ");
                sql.AppendLine("        ifnull(layout.id, -1)  id, ");
                sql.AppendLine("        layout.enable          enable, ");
                sql.AppendLine("        layout.layouttype      layouttype, ");
                sql.AppendLine("        layout.year            year, ");
                sql.AppendLine("        layout.layoutname      layoutname, ");
                sql.AppendLine("        layout.layoutvector    layoutvector, ");

                sql.AppendLine("        item.id        itemId, ");
                sql.AppendLine("        item.enable    itemEnable, ");
                sql.AppendLine("        item.itemname  itemName, ");
                sql.AppendLine("        item.itemtype  itemType, ");
                sql.AppendLine("        item.itemvertical itemVertical, ");
                sql.AppendLine("        item.width     itemWidth, ");
                sql.AppendLine("        item.width     itemHeight, ");
                sql.AppendLine("        item.height    itemName, ");
                sql.AppendLine("        item.x         itemX, ");
                sql.AppendLine("        item.y         itemY, ");
                sql.AppendLine("        item.fontname  fontname, ");
                sql.AppendLine("        item.fontsize  fontsize, ");
                sql.AppendLine("        item.fontcolor_r fontcolor_r, ");
                sql.AppendLine("        item.fontcolor_g fontcolor_g, ");
                sql.AppendLine("        item.fontcolor_b fontcolor_b, ");
                sql.AppendLine("        item.message   itemMessage, ");
                sql.AppendLine("        item.imagebuff itemImage ");
                
                sql.AppendLine("   from tbllayout layout");
                sql.AppendLine("   left join tbllayoutitem item");
                sql.AppendLine("     on layout.id = item.layoutid ");
                

                sql.AppendLine("  where ");
                sql.AppendLine("        layout.id is not null ");

                if (!string.IsNullOrEmpty(model.Id))
                    sql.AppendLine("    and layout.id = '" + base.toSafety(model.Id) + "' ");

                if (!string.IsNullOrEmpty(model.layoutType))
                    sql.AppendLine("    and layouttype = '" + base.toSafety(model.layoutType) + "' ");

                if (!string.IsNullOrEmpty(model.year))
                    sql.AppendLine("    and year = '" + base.toSafety(model.year) + "' ");

                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];

                var preId = "EMPTY";
                outModelList = new List<IllustLayoutContentModel>();
                foreach (DataRow row in table.Rows)
                {
                    if (!preId.Equals(ValueUtil.tryGetString(row["id"])))
                    {
                        var daoModel = new IllustLayoutContentModel();
                        outModelList.Add(daoModel);
                        daoModel.Id = base.getFieldAsString(row["id"]);
                        daoModel.Enable = base.getFieldAsString(row["enable"]);
                        daoModel.layoutType = base.getFieldAsString(row["layouttype"]);
                        daoModel.year = base.getFieldAsString(row["year"]);
                        daoModel.layoutName = base.getFieldAsString(row["layoutname"]);
                        daoModel.layoutVector = base.getFieldAsString(row["layoutvector"]);
                        preId = daoModel.Id;
                    }

                    if (base.getFieldAsString(row["itemId"]).Equals("")) continue;
                    
                    //.NetFramework4.0以降
                    //var latestModel = outModelList.Last<IllustLayoutContentModel>();
                    //3.5以前
                    var latestModel = outModelList[outModelList.Count - 1];
                    var msgModel= new MsgItemContentModel();
                    latestModel.MsgItemList.Add(msgModel);
                    msgModel.Id = base.getFieldAsString(row["itemId"]);
                    msgModel.Enable = base.getFieldAsString(row["itemEnable"]);
                    msgModel.Name = base.getFieldAsString(row["itemName"]);
                    msgModel.isMsgDirectVertical = true;
                    msgModel.width = int.Parse(base.getFieldAsString(row["itemWidth"]));
                    msgModel.height = int.Parse(base.getFieldAsString(row["itemHeight"]));
                    msgModel.x = int.Parse(base.getFieldAsString(row["itemX"]));
                    msgModel.y = int.Parse(base.getFieldAsString(row["itemY"]));
                    msgModel.fontName = base.getFieldAsString(row["fontname"]);
                    msgModel.size = base.getFieldAsString(row["fontsize"]);
                    var r = int.Parse(base.getFieldAsString(row["fontcolor_r"]));
                    var g = int.Parse(base.getFieldAsString(row["fontcolor_g"]));
                    var b = int.Parse(base.getFieldAsString(row["fontcolor_b"]));
                    msgModel.FontColor = System.Drawing.Color.FromArgb(r, g, b);
                    msgModel.message = base.getFieldAsString(row["itemMessage"]);
                }


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

        //updateLayout
        public int updateLayoutSimple(object obj)
        {
            var essence = base.getDaoEssence();
            var model = inModel;
            try
            {
                #region Insert tbllayout

                StringBuilder sqlLayout = new StringBuilder();
                sqlLayout.AppendLine(" update tbllayout set ");
                sqlLayout.AppendLine("    layoutname = ? "); //layoutname
                sqlLayout.AppendLine(" where id = ?; "); //id

                essence.setSql(sqlLayout.ToString());

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

                #endregion 

                #region Insert tbllayoutitem

                // 新規登録
                StringBuilder sqlItem = new StringBuilder();
                sqlItem.AppendLine(" update tbllayoutitem set ");
                sqlItem.AppendLine("    enable = ?, ");// enable
                sqlItem.AppendLine("    x = ?, ");//x
                sqlItem.AppendLine("    y = ?, ");//y
                sqlItem.AppendLine("    fontname = ?, ");//fontname
                sqlItem.AppendLine("    fontsize = ?, ");//fontsize
                sqlItem.AppendLine("    fontcolor_r = ?, ");//fontcolor_r
                sqlItem.AppendLine("    fontcolor_g = ?, ");//fontcolor_g
                sqlItem.AppendLine("    fontcolor_b = ? ");//fontcolor_b
                sqlItem.AppendLine(" where ");
                sqlItem.AppendLine("    id = ? ");
                sqlItem.AppendLine(" ; ");

                essence.setSql(sqlItem.ToString());

                //イラスト登録
                if (model.IllustItemList != null)
                foreach (IllustItemContentModel illust in model.IllustItemList)
                {
                    essence.addParam("@id", illust.Id);
                    essence.addParam("@enable", illust.Enable);
                    //essence.addParam("@layoutid", model.id);
                    essence.addParam("@itemname", illust.Name);
                    essence.addParam("@itemtype", IllustItemContentModel.ITEM_TYPE);
                    essence.addParam("@width", illust.width.ToString());
                    essence.addParam("@height", illust.height.ToString());
                    essence.addParam("@x", illust.x.ToString());
                    essence.addParam("@y", illust.y.ToString());
                    essence.addParam("@message", "");
                    essence.addParam("@imagebuff", Hitsuji.Framework.Utils.Base64ConvertUtil.imageToBlob(illust.illust));
                    essence.postUpdate(null);
                    essence.clearAllParams();
                }

                //メッセージ登録
                if (model.MsgItemList != null)
                foreach (MsgItemContentModel msg in model.MsgItemList)
                {
                    essence.addParam("@enable", msg.Enable);
                    essence.addParam("@x", msg.x.ToString());
                    essence.addParam("@y", msg.y.ToString());
                    essence.addParam("@fontname", msg.fontName);
                    essence.addParam("@fontsize", msg.size);
                    essence.addParam("@fontcolor_r", msg.FontColor.R.ToString());
                    essence.addParam("@fontcolor_g", msg.FontColor.G.ToString());
                    essence.addParam("@fontcolor_b", msg.FontColor.B.ToString());

                    essence.addParam("@id", msg.Id);

                    essence.postUpdate(null);
                    essence.clearAllParams();
                }                

                #endregion

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


        #region 宛名サンプルデータ挿入

        public void Init_ReseiverLayoutSampleData(object obj)
        {
            List<IllustLayoutContentModel> layoutList = new List<IllustLayoutContentModel>();
            var recordId = 0;
            for (var index = 1; index <= 10; index++)
            {
                //レイアウト
                var layout = new IllustLayoutContentModel();
                layoutList.Add(layout);
                layout.Id = index.ToString();
                layout.Enable = true.ToString().ToUpper();
                layout.layoutType = "RESEIVER_LAYOUT";
                layout.year = "0000";
                layout.layoutName = "宛名レイアウト[" + index.ToString() + "]";
                layout.layoutVector = "V";
                layout.MsgItemList = new List<MsgItemContentModel>();

                //メッセージアイテム

                #region 宛先アイテム
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_PRE1.Key, 169, 56, Global.GlobalSettings.RESEIVER_PRE1.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_PRE2.Key, 197, 56, Global.GlobalSettings.RESEIVER_PRE2.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_PRE3.Key, 225, 56, Global.GlobalSettings.RESEIVER_PRE3.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_POST1.Key, 254, 57, Global.GlobalSettings.RESEIVER_POST1.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_POST2.Key, 281, 57, Global.GlobalSettings.RESEIVER_POST2.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_POST3.Key, 308, 57, Global.GlobalSettings.RESEIVER_POST3.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_POST4.Key, 335, 57, Global.GlobalSettings.RESEIVER_POST4.Value, 12));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_NAME1.Key, 260, 114, Global.GlobalSettings.RESEIVER_NAME1.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_NAME2.Key, 230, 114, Global.GlobalSettings.RESEIVER_NAME2.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_NAME3.Key, 200, 114, Global.GlobalSettings.RESEIVER_NAME3.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_NAME4.Key, 170, 114, Global.GlobalSettings.RESEIVER_NAME4.Value, 12));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_SUFFIX1.Key, 260, 260, Global.GlobalSettings.RESEIVER_SUFFIX1.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_SUFFIX2.Key, 230, 260, Global.GlobalSettings.RESEIVER_SUFFIX2.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_SUFFIX3.Key, 200, 260, Global.GlobalSettings.RESEIVER_SUFFIX3.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_SUFFIX4.Key, 170, 260, Global.GlobalSettings.RESEIVER_SUFFIX4.Value, 12));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_ADDRESS1.Key, 357, 120, Global.GlobalSettings.RESEIVER_ADDRESS1.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_ADDRESS2.Key, 332, 120, Global.GlobalSettings.RESEIVER_ADDRESS2.Value, 12));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.RESEIVER_ADDRESS3.Key, 307, 120, Global.GlobalSettings.RESEIVER_ADDRESS3.Value, 12));
                #endregion
                #region 発送者アイテム
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_PRE1.Key, 23, 486, Global.GlobalSettings.SENDER_PRE1.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_PRE2.Key, 39, 486, Global.GlobalSettings.SENDER_PRE2.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_PRE3.Key, 55, 486, Global.GlobalSettings.SENDER_PRE3.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_POST1.Key, 74, 486, Global.GlobalSettings.SENDER_POST1.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_POST2.Key, 90, 486, Global.GlobalSettings.SENDER_POST2.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_POST3.Key, 106, 486, Global.GlobalSettings.SENDER_POST3.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_POST4.Key, 122, 486, Global.GlobalSettings.SENDER_POST4.Value, 10));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_NAME1.Key, 85, 280, Global.GlobalSettings.SENDER_NAME1.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_NAME2.Key, 67, 280, Global.GlobalSettings.SENDER_NAME2.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_NAME3.Key, 49, 280, Global.GlobalSettings.SENDER_NAME3.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_NAME4.Key, 31, 280, Global.GlobalSettings.SENDER_NAME4.Value, 10));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_ADDRESS1.Key, 146, 260, Global.GlobalSettings.SENDER_ADDRESS1.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_ADDRESS2.Key, 128, 260, Global.GlobalSettings.SENDER_ADDRESS2.Value, 10));
                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_ADDRESS3.Key, 110, 260, Global.GlobalSettings.SENDER_ADDRESS3.Value, 10));

                layout.MsgItemList.Add(createMsgItem(++recordId, index, Global.GlobalSettings.SENDER_TEL.Key, 7, 320, Global.GlobalSettings.SENDER_TEL.Value, 10));
                #endregion

                this.inModel = null;
                this.inModel = layout;
                updateLayoutSimple(null);

            }

        }
        private MsgItemContentModel createMsgItem(int id, 
                                                  int layoutId,
                                                  string name,
                                                  int x,
                                                  int y,
                                                  string message,
                                                  int fontSize)
        {
            string msgVector = "V";
            string itemType = "MESSAGE";

            MsgItemContentModel msgItem = new MsgItemContentModel();
            msgItem.Id = id.ToString();
            msgItem.layoutId = layoutId.ToString();
            msgItem.Enable = true.ToString().ToUpper();
            msgItem.Name = name;
            msgItem.message = message;
            msgItem.isMsgDirectVertical = true;
            msgItem.fontName = "Andalus";
            msgItem.size = fontSize.ToString();
            msgItem.FontColor = System.Drawing.Color.FromArgb(50, 60, 70);
            msgItem.x = x;
            msgItem.y = y;
            msgItem.width = 15;
            msgItem.height = 15;

            return msgItem;
        }

        #endregion
    }
}
