using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using ABT.ViewModel.ABTWWW;
using ABT.Utility;
using Newtonsoft.Json;
using ABT.Common.Service;
using ABTWCF;
using ABT.Entity;
using System.Data.SqlClient;
using ABTWCF.Models;
using System.Web.Mvc;
using System.Data;
using ABT.Business.ID;
using ABT.Common.Utility;
using System.Web;
using DocumentFormat.OpenXml.Wordprocessing;

namespace ABTWCF
{
    // 注意: 您可以使用 [重構] 功能表上的 [重新命名] 命令同時變更程式碼、svc 和組態檔中的類別名稱 "CreditCard_WCF"。
    // 注意: 若要啟動 WCF 測試用戶端以便測試此服務，請在 [方案總管] 中選取 CreditCard_WCF.svc 或 CreditCard_WCF.svc.cs，然後開始偵錯。
    public class CreditCard_WCF : ABT.BaseBusiness.BaseBusiness, ICreditCard_WCF
    {
        CommonService _commonService = new CommonService();

        public Paging<ModelCreditCardMain> Paging(SearchModelBase model)
        {
            string sql = string.Empty;
			if (model.LangId.IsNullOrEmpty())
            {
                model.LangId = "1";
            }
            var Paging = new Paging<ModelCreditCardMain>();
            var onepagecnt = model.Limit;

            sql = $"select * from ModelCreditCardMain where Lang_ID=@Lang_ID";
            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@Lang_ID", model.LangId));
            Paging.rows = base.SearchListPage<ModelCreditCardMain>(sql, model.Offset, model.Limit, " order by " + model.Sort);
            Paging.total = base.SearchCount(sql);
            Paging.cnt = Paging.total;

            return Paging;
        }

        public List<sys_Hsien> GetHsien()
        {
            return _commonService.GetGeneralList<sys_Hsien>("1=1  order by SortOrder").ToList();
        }

        public ModelCreditCardMain GetModelCreditCardMain(string ID)
        {
            string sql = @"select * from ModelCreditCardMain where ID=@ID";
            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@ID", ID));

            return base.GetObject<ModelCreditCardMain>(sql);
        }



        /// <summary>
        /// 刪除Main資料
        /// </summary>
        /// <param name="idlist"></param>
        /// <param name="delaccount"></param>
        /// <param name="langid"></param>
        /// <param name="account"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string Delete(string[] idlist, string delaccount, string langid, string account, string username)
        {
            string sql = string.Empty;
            string rstr = string.Empty;
            var r = 0;
            //檢查是否在使用中

            sql = "select * from Menu where ModelID=23 and ModelItemID in (select ListItem from dbo.SplitList(',',@idlist))";
            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@idlist", string.Join(",", idlist)));

            if (base.SearchCount(sql) > 0)
            {
                return "刪除失敗,刪除的項目使用中..";
            }



            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {
                        //delete  ModelMessageMain _OK
                        sql = @"delete ModelCreditCardMain where ID in (select ListItem from dbo.SplitList(',',@idlist))";
                        base.Parameter.Clear();
                        base.Parameter.Add(new SqlParameter("@idlist", string.Join(",", idlist)));
                        base.ExeNonQuery(sql, tran);

                        //delete VerifyData_OK
                        sql = @"delete VerifyData where ModelID=23 and ModelMainID in (select ListItem from dbo.SplitList(',',@idlist))";
                        base.Parameter.Clear();
                        base.Parameter.Add(new SqlParameter("@idlist", string.Join(",", idlist)));
                        base.ExeNonQuery(sql, tran);



                        for (var idx = 0; idx < idlist.Length; idx++)
                        {
                            //刪除PageIndexItem
                            List<CreditCard_Preferential_store> olditem = _commonService.GetGeneralList<CreditCard_Preferential_store>("ModelID=@ModelID", new Dictionary<string, string>() { { "ModelID", idlist[idx] } }, tran)
                                                           .ToList();

                            sql = @"delete CreditCard_Preferential_store where ModelID=@ModelID";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ModelID", idlist[idx]));
                            base.ExeNonQuery(sql, tran);
                        }

                        //update sort order
                        sql = @"with cte as
                                    (
                                    select 
                                     ROW_NUMBER()OVER(order by sort) as ROW,t.ID
                                     from ModelCreditCardMain t where t.Lang_ID=1
                                    )
                                    update s set s.Sort = t.ROW from cte  t
                                    left join ModelCreditCardMain s on t.ID = s.ID";
                        base.Parameter.Clear();
                        base.ExeNonQuery(sql, tran);

                        tran.Commit();
                        rstr = "刪除成功";

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex, "刪除模組異常,error:" + ex.ToString().NewLineReplace());
                        tran.Rollback();
                        rstr = "刪除失敗";
                    }

                    return rstr;
                }
            }
        }

        public Paging<CreditCard_Preferential_store> PagingItem(string modelid, CreditCardSearchModel model)
        {
            var Paging = new Paging<CreditCard_Preferential_store>();
            var data = new List<CreditCard_Preferential_store>();

            if (modelid.IsNullOrEmpty())
            {
                return Paging;
            }
            
            string sql = @"select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0";

            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@ModelID", modelid));
            Paging.cnt = base.SearchCount(sql);

            if (model.BankCode.IsNullOrEmpty() == false)
            {
                sql += " and bank_name=@bank_name";
                base.Parameter.Add(new SqlParameter("@bank_name", model.BankCode));
            }

            if (model.City != null)
            {
                sql += " and hsien_id=@hsien_id";
                base.Parameter.Add(new SqlParameter("@hsien_id", model.City));
            }
            if (!string.IsNullOrEmpty(model.products))
            {
                sql += " and products like @products";
                base.Parameter.Add(new SqlParameter("@products", "%" + model.products + "%"));
            }

            if (model.Enabled != null)
            {
                sql += " and Enabled=@Enabled";
                base.Parameter.Add(new SqlParameter("@Enabled", model.Enabled));
            }

            if (!model.PublicshDateFrom.IsNullOrEmpty())
            {
                sql += " and (pref_start_date >=@PublicshDateFrom or pref_start_date is Null)";
                base.Parameter.Add(new SqlParameter("@PublicshDateFrom", model.PublicshDateFrom));
            }

            if (!model.PublicshDateTo.IsNullOrEmpty())
            {
                sql += " and (pref_start_date <=@PublicshDateTo or pref_start_date is Null)";
                base.Parameter.Add(new SqlParameter("@PublicshDateTo", model.PublicshDateTo));
            }


            if (!string.IsNullOrEmpty(model.IsRange))
            {
                if (model.IsRange == "1")
                {
                    sql += " and (StDate <=@IsRange or StDate is null or StDate='') and (EdDate >=@IsRange or EdDate is null or EdDate='')";
                    base.Parameter.Add(new SqlParameter("@IsRange", DateTime.Now.Date));
                }
                else
                {
                    sql += " and ((StDate >@IsRange ) or (EdDate <@IsRange ))";
                    base.Parameter.Add(new SqlParameter("@IsRange", DateTime.Now.Date));
                }
            }

            data = base.SearchListPage<CreditCard_Preferential_store>(sql, model.Offset, model.Limit, " order by " + model.Sort).ToList();

            Paging.total = base.SearchCount(sql);



            var modelverifydata = _commonService.GetGeneralList<VerifyData>("ModelID = 23 and ModelMainID =@ModelMainID"
                                                                , new Dictionary<string, string>() { { "ModelMainID", modelid } });
            foreach (var d in data)
            {
                var isrange = false;
                if (d.StDate == null && d.EdDate == null) { isrange = true; }
                else if (d.StDate != null && d.EdDate == null)
                {
                    if (DateTime.Now >= d.StDate.Value)
                    {
                        isrange = true;
                    }
                }
                else if (d.StDate == null && d.EdDate != null)
                {
                    if (DateTime.Now <= d.EdDate.Value.AddDays(1))
                    {
                        isrange = true;
                    }
                }
                else if (d.StDate != null && d.EdDate != null)
                {
                    if (DateTime.Now >= d.StDate.Value && DateTime.Now <= d.EdDate.Value.AddDays(1))
                    {
                        isrange = true;
                    }
                }

                List<ABT.Entity.sys_Hsien> sys_Hsiens = _commonService.GetGeneralList<ABT.Entity.sys_Hsien>().ToList();
                List<ABT.Entity.sys_Bank> sys_Banks = _commonService.GetGeneralList<ABT.Entity.sys_Bank>().ToList();
                var gname = sys_Hsiens.Where(v => v.HsienID == d.hsien_id);
                var bname = sys_Banks.Where(v => v.BankCode == d.bank_code);
                var vdata = modelverifydata.Where(v => v.ModelItemID == d.ItemID);
                Paging.rows.Add(new CreditCard_Preferential_store()
                {
                    preferential_during = d.pref_start_date.Value.ToString("yyyy/MM/dd") + "~" + d.pref_end_date.Value.ToString("yyyy/MM/dd"),
                    ItemID = d.ItemID,
                    products = d.products.IsNullOrEmpty()?d.preferential_content:d.products,
                    Enabled = d.Enabled,
                    IsRange = isrange == true ? "是" : "否",
                    hsien_id = gname.Count() > 0 ? gname.First().HsienName : "無分類",
                    BankName = bname.Count() > 0 ? bname.First().BankName : "無分類",
                    VerifyStr = vdata.Count() == 0 ? "審核中" : vdata.First().VerifyStatus == 0 ? "審核中" : (vdata.First().VerifyStatus == 1 ? "已通過" : "未通過"),
                });

            }
            return Paging;
        }
        public string DeleteData(string[] idlist, string delaccount, string account, string username)
        {
            string sql = string.Empty;
            string rstr = string.Empty;
            var r = 0;


            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {
                        //delete  ModelMessageMain _OK
                        //sql = @"delete trust_deed where ItemID in (select ListItem from dbo.SplitList(',',@idlist))";
                        //base.Parameter.Clear();
                        //base.Parameter.Add(new SqlParameter("@idlist", string.Join(",", idlist)));
                        //base.ExeNonQuery(sql, tran);

                        //delete VerifyData_OK
                        //sql = @"delete VerifyData where ModelID=17 and ModelMainID in (select ListItem from dbo.SplitList(',',@idlist))";
                        //base.Parameter.Clear();
                        //base.Parameter.Add(new SqlParameter("@idlist", string.Join(",", idlist)));
                        //base.ExeNonQuery(sql, tran);
                        for (var idx = 0; idx < idlist.Length; idx++)
                        {

                            var entity = new CreditCard_Preferential_store();
                            var olditem = _commonService.GetGeneral<CreditCard_Preferential_store>("ItemID=@ItemID", new Dictionary<string, string>() { { "ItemID", idlist[idx] } }, tran);
                            //ModelID = GetModelID(ModelID, olditem.ModelID.Value);
                            int modelid = olditem.ModelID.Value;
                            entity.ItemID = int.Parse(idlist[idx]);


                            sql = @"select * from VerifyData where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ModelID", 23));
                            base.Parameter.Add(new SqlParameter("@ModelMainID", olditem.ModelID.Value));
                            base.Parameter.Add(new SqlParameter("@ModelItemID", olditem.ItemID));

                            List<VerifyData> hasvdata = base.SearchList<VerifyData>(sql, tran);

                            var bank_name = getbank_name(olditem.bank_code);
                            if (!bank_name.IsNullOrEmpty())
                            {
                                bank_name = bank_name + " - ";
                            }
                            if (hasvdata.Count() == 0)
                            {
                                base.InsertObject(new VerifyData()
                                {
                                    ModelID = 23,
                                    ModelItemID = olditem.ItemID,
                                    ModelName = olditem.products.IsNullOrEmpty()? bank_name+ olditem.preferential_content : bank_name+olditem.products,
                                    ModelMainID = olditem.ModelID.Value,
                                    VerifyStatus = 0,
                                    ModelStatus = 3,
                                    UpdateDateTime = DateTime.Now,
                                    UpdateUser = username,
                                    UpdateAccount = account,
                                    LangID = 1
                                }, tran);
                            }
                            else
                            {
                                sql = @"update VerifyData set VerifyStatus=0,ModelName=@ModelName,ModelStatus=@ModelStatus,VerifyDateTime=Null,VerifyUser=''
                                                        ,VerifyName='',UpdateDateTime=GetDate(),UpdateUser=@UpdateUser,UpdateAccount=@UpdateAccount
                                          where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                                base.Parameter.Clear();
                                base.Parameter.Add(new SqlParameter("@ModelStatus", 3));
                                base.Parameter.Add(new SqlParameter("@ModelName", olditem.products.IsNullOrEmpty() ? bank_name+ olditem.preferential_content : bank_name+olditem.products));
                                base.Parameter.Add(new SqlParameter("@UpdateUser", username));
                                base.Parameter.Add(new SqlParameter("@ModelID", 23));
                                base.Parameter.Add(new SqlParameter("@UpdateAccount", account));
                                base.Parameter.Add(new SqlParameter("@ModelMainID", olditem.ModelID.Value));
                                base.Parameter.Add(new SqlParameter("@ModelItemID", olditem.ItemID));
                                base.ExeNonQuery(sql, tran);
                            }



                            sql = $@"update CreditCard_Preferential_store set Del=1 ,UpdateUser=@UpdateUser,UpdateTime=GetDate()
                                        where ItemID=@ItemID";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ItemID", idlist[idx]));
                            base.Parameter.Add(new SqlParameter("@UpdateName", username));
                            base.Parameter.Add(new SqlParameter("@UpdateUser", account));
                            base.ExeNonQuery(sql, tran);




                        }

                        tran.Commit();
                        rstr = "刪除成功";

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex, "刪除保證業務模組異常,error:" + ex.ToString().NewLineReplace());

                        tran.Rollback();
                        rstr = "刪除失敗";
                    }

                    return rstr;
                }
            }
        }
        public string DeleteItem(string[] idlist, SqlTransaction tran)
        {
            try
            {
                string sql = string.Empty;

                var r = 0;

                var modelid = -1;
                for (var idx = 0; idx < idlist.Length; idx++)
                {
                    var entity = new CreditCard_Preferential_store();
                    var olditem = _commonService.GetGeneral<CreditCard_Preferential_store>("ItemID=@ItemID", new Dictionary<string, string>() { { "ItemID", idlist[idx] } });
                    modelid = olditem.ModelID.Value;
                    entity.ItemID = int.Parse(idlist[idx]);

                    sql = $@"delete CreditCard_Preferential_store where ItemID=@ItemID";
                    base.Parameter.Clear();
                    base.Parameter.Add(new SqlParameter("@ItemID", idlist[idx]));
                    r = base.ExeNonQuery(sql, tran);

                    if (r <= 0)
                    {
                        var loginfo = "刪除項目失敗:ID=" + idlist[idx].NewLineReplace();
                        loginfo = Microsoft.Security.Application.Sanitizer.GetSafeHtmlFragment(loginfo);
                        logger.Info(loginfo);
                    }
                    //else
                    //{
                    //    sql = "delete VerifyData where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                    //    base.Parameter.Clear();
                    //    base.Parameter.Add(new SqlParameter("@ModelID", 23));
                    //    base.Parameter.Add(new SqlParameter("@ModelMainID", modelid));
                    //    base.Parameter.Add(new SqlParameter("@ModelItemID", idlist[idx]));
                    //    base.ExeNonQuery(sql,tran);
                    //}
                }
                var rstr = "";
                if (r >= 0)
                {
                    rstr = "刪除成功";
                }
                else
                {
                    rstr = "刪除失敗";
                }



                return rstr;
            }
            catch (Exception ex)
            {
                logger.Error(ex, "刪除項目異常，error:" + ex.ToString().NewLineReplace());
                return "刪除失敗";
            }
        }

        public CreditCardViewModel GetModelByID(string modelid, string itemid)
        {
            CreditCardViewModel model = new CreditCardViewModel();
            model.creditCard_Preferential_Store = _commonService.GetHisEntity<CreditCard_Preferential_store>("ItemID", itemid);

            ModelCreditCardMain modelCreditCardMain = _commonService.GetGeneral<ModelCreditCardMain>("ID=@ID", new Dictionary<string, string>() { { "ID", modelid } });
            model.ModelName = modelCreditCardMain.Name;

            if (model.creditCard_Preferential_Store.ItemID > 0)
            {
                model.creditCard_Preferential_Store.CreateDept = model.creditCard_Preferential_Store.CreateUser == null ? "" : _commonService.GetDeptByUserID(model.creditCard_Preferential_Store.CreateUser);
                model.creditCard_Preferential_Store.CreateUser = model.creditCard_Preferential_Store.CreateUser == null ? "" : _commonService.GetUsersByID(model.creditCard_Preferential_Store.CreateUser).UserName;
                model.creditCard_Preferential_Store.UpdateDept = model.creditCard_Preferential_Store.UpdateUser == null ? "" : _commonService.GetDeptByUserID(model.creditCard_Preferential_Store.UpdateUser);
                model.creditCard_Preferential_Store.UpdateUser = model.creditCard_Preferential_Store.UpdateUser == null ? "" : _commonService.GetUsersByID(model.creditCard_Preferential_Store.UpdateUser).UserName;

                model.VerifyData = _commonService.GetVerifyData("23", model.creditCard_Preferential_Store.ModelID.Value.ToString(), model.creditCard_Preferential_Store.ItemID.ToString());

                if (model.VerifyData != null && model.VerifyData.ModelID > 0)
                {
                    model.VerifyData.VerifyStatusName = model.VerifyData.VerifyStatus == 0 ? "審核中" : (model.VerifyData.VerifyStatus == 1 ? "已通過" : "未通過");
                    model.VerifyData.VerifyDept = _commonService.GetDeptByUserID(model.VerifyData.VerifyUser);

                }
                else
                {
                    model.VerifyData = new VerifyData();
                }
            }
            else
            {
                model.creditCard_Preferential_Store = new CreditCard_Preferential_store()
                {
                    ModelID = int.Parse(modelid),
                    ItemID = int.Parse(itemid)
                };
                model.VerifyData = new VerifyData();

                model.ModelName = modelCreditCardMain.Name;
            }
            return model;
        }

        /// <summary>
        /// 新增Item
        /// </summary>
        /// <param name="model"></param>
        /// <param name="LangId"></param>
        /// <param name="account"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public string CreateItem(CreditCardViewModel model, string LangId, string account, string userName)
        {
            string sql = string.Empty;

            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {
                        model.creditCard_Preferential_Store.CreateUser = account;
                        model.creditCard_Preferential_Store.CreateTime = DateTime.Now;

                        if (model.creditCard_Preferential_Store.EdDateStr.IsNullOrEmpty() == false)
                        {
                            model.creditCard_Preferential_Store.EdDate = DateTime.Parse(model.creditCard_Preferential_Store.EdDateStr);
                        }
                        if (model.creditCard_Preferential_Store.StDateStr.IsNullOrEmpty() == false)
                        {
                            model.creditCard_Preferential_Store.StDate = DateTime.Parse(model.creditCard_Preferential_Store.StDateStr);
                        }

                        if (model.creditCard_Preferential_Store.pref_end_dateStr.IsNullOrEmpty() == false)
                        {
                            model.creditCard_Preferential_Store.pref_end_date = DateTime.Parse(model.creditCard_Preferential_Store.pref_end_dateStr);
                        }

                        if (model.creditCard_Preferential_Store.pref_start_dateStr.IsNullOrEmpty() == false)
                        {
                            model.creditCard_Preferential_Store.pref_start_date = DateTime.Parse(model.creditCard_Preferential_Store.pref_start_dateStr);
                        }
                        model.creditCard_Preferential_Store.Enabled = true;

                        var bank_name = getbank_name(model.creditCard_Preferential_Store.bank_code);
                        if (!bank_name.IsNullOrEmpty())
                        {
                            bank_name = bank_name + " - ";
                        }

                        var r = base.InsertObject(model.creditCard_Preferential_Store, tran);



                        sql = "delete VerifyData where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                        base.Parameter.Clear();
                        base.Parameter.Add(new SqlParameter("@ModelID", 23));
                        base.Parameter.Add(new SqlParameter("@ModelMainID", model.creditCard_Preferential_Store.ModelID.Value));
                        base.Parameter.Add(new SqlParameter("@ModelItemID", model.creditCard_Preferential_Store.ItemID));
                        base.ExeNonQuery(sql, tran);


                        base.InsertObject(new VerifyData()
                        {
                            ModelID = 23,
                            ModelItemID = model.creditCard_Preferential_Store.ItemID,
                            ModelName = model.creditCard_Preferential_Store.products.IsNullOrEmpty()? bank_name+ model.creditCard_Preferential_Store.preferential_content : bank_name+model.creditCard_Preferential_Store.products,
                            ModelMainID = model.creditCard_Preferential_Store.ModelID.Value,
                            VerifyStatus = 0,
                            ModelStatus = 1,
                            UpdateDateTime = DateTime.Now,
                            UpdateUser = userName,
                            UpdateAccount = account,
                            LangID = int.Parse(LangId)
                        }, tran);


                        tran.Commit();
                        return "新增成功";
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex, "新增項目異常,error:" + ex.ToString().NewLineReplace());

                        tran.Rollback();
                        return "新增失敗";
                    }
                }
            }
        }

        /// <summary>
        /// 更新Item
        /// </summary>
        /// <param name="model"></param>
        /// <param name="LangId"></param>
        /// <param name="account"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public string UpdateItem(CreditCardViewModel model, string LangId, string account, string userName)
        {
            string sql = string.Empty;

            var olddata = _commonService.GetGeneral<CreditCard_Preferential_store>("ItemID=@ItemID", new Dictionary<string, string>() { { "ItemID", model.creditCard_Preferential_Store.ItemID.ToString() } });

            
            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {

                        olddata.category = model.creditCard_Preferential_Store.category;
                        olddata.hsien_id = model.creditCard_Preferential_Store.hsien_id;
                        olddata.bank_code = model.creditCard_Preferential_Store.bank_code;
                        olddata.username = model.creditCard_Preferential_Store.username;
                        olddata.usertel = model.creditCard_Preferential_Store.usertel;
                        olddata.products = model.creditCard_Preferential_Store.products;
                        olddata.preferential_content = model.creditCard_Preferential_Store.preferential_content;
                        olddata.note = model.creditCard_Preferential_Store.note;
                        olddata.UpdateUser = account;
                        olddata.UpdateTime = DateTime.Now;
                        olddata.Enabled = true;
                        olddata.IsVerift = false;

                        if (model.creditCard_Preferential_Store.EdDateStr.IsNullOrEmpty() == false)
                        {
                            olddata.EdDate = DateTime.Parse(model.creditCard_Preferential_Store.EdDateStr);
                        }
                        else
                        {
                            olddata.EdDate = null;
                        }

                        if (model.creditCard_Preferential_Store.StDateStr.IsNullOrEmpty() == false)
                        {
                            olddata.StDate = DateTime.Parse(model.creditCard_Preferential_Store.StDateStr);
                        }
                        else
                        {
                            olddata.StDate = null;
                        }

                        if (model.creditCard_Preferential_Store.pref_end_dateStr.IsNullOrEmpty() == false)
                        {
                            olddata.pref_end_date = DateTime.Parse(model.creditCard_Preferential_Store.pref_end_dateStr);
                        }
                        else
                        {
                            olddata.pref_end_date = null;
                        }

                        if (model.creditCard_Preferential_Store.pref_start_dateStr.IsNullOrEmpty() == false)
                        {
                            olddata.pref_start_date = DateTime.Parse(model.creditCard_Preferential_Store.pref_start_dateStr);
                        }
                        else
                        {
                            olddata.pref_start_date = null;
                        }

                        var r = base.UpdateObject(olddata, tran);

                        if (r)
                        {
                            sql = "select * from VerifyData where ModelID=23 and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ModelMainID", olddata.ModelID.Value));
                            base.Parameter.Add(new SqlParameter("@ModelItemID", olddata.ItemID));
                            var hasvdata = base.SearchList<VerifyData>(sql, tran);

                            var bank_name = getbank_name(olddata.bank_code);
                            if (!bank_name.IsNullOrEmpty())
                            {
                                bank_name = bank_name + " - ";
                            }
                            if (hasvdata.Count() == 0)
                            {
                                

                                base.InsertObject(new VerifyData()
                                {
                                    ModelID = 23,
                                    ModelItemID = olddata.ItemID,
                                    ModelName = olddata.products.IsNullOrEmpty() ? bank_name+ olddata.preferential_content : bank_name+ olddata.products,
                                    ModelMainID = olddata.ModelID.Value,
                                    VerifyStatus = 0,
                                    ModelStatus = 2,
                                    UpdateDateTime = DateTime.Now,
                                    UpdateUser = userName,
                                    UpdateAccount = account,
                                    LangID = int.Parse(LangId)
                                });
                            }
                            else
                            {
                                sql = @"update VerifyData set VerifyStatus=0,ModelStatus=2,VerifyDateTime=Null,VerifyUser=''
                                                             ,VerifyName='',ModelName=@ModelName,UpdateDateTime=GetDate(),UpdateUser=@UpdateUser,UpdateAccount=@UpdateAccount
                                        where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID ";
                                base.Parameter.Clear();
                                base.Parameter.Add(new SqlParameter("@ModelName", olddata.products.IsNullOrEmpty() ? bank_name+olddata.preferential_content : bank_name+olddata.products));
                                base.Parameter.Add(new SqlParameter("@UpdateUser", userName));
                                base.Parameter.Add(new SqlParameter("@UpdateAccount", account));
                                base.Parameter.Add(new SqlParameter("@ModelID", 23));
                                base.Parameter.Add(new SqlParameter("@ModelMainID", olddata.ModelID.Value));
                                base.Parameter.Add(new SqlParameter("@ModelItemID", olddata.ItemID));
                                base.ExeNonQuery(sql, tran);

                            }
                        }

                        tran.Commit();
                        return "修改成功";
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex, "更新項目異常,error:" + ex.ToString().NewLineReplace());

                        tran.Rollback();
                        return "修改失敗";
                    }
                }
            }
        }

        public List<SelectListItem> GetBankByCity(string HSIEN_ID)
        {
            base.Parameter.Clear();
            string sql = @"SELECT [Organ_ID] as BankCode, [BankName], [BankType], UCBID FROM  v_Card_Org";
            if (!string.IsNullOrEmpty(HSIEN_ID))
            {
                sql += " WHERE [HsienID] = @HsienID ";
                base.Parameter.Add(new SqlParameter("@HsienID", HSIEN_ID));
            }

            List<sys_Bank> sys_Banks = base.SearchList<sys_Bank>(sql);

            sys_Banks.Insert(0, new sys_Bank() { BankCode = "", BankName = "全部" });

            return sys_Banks.Select(t => new SelectListItem() { Value = t.BankCode, Text = t.BankName }).ToList();

        }

        #region 前台
        public Paging<CreditCard_Preferential_store> GetPreferentialData(string category, string city, string bank, int nowpage, int showCount, ref int total)
        {
            string sql = @"select 
	                        case when category ='1' then '店內活動' else '店外活動' end as category,
	                        s.HsienName,
	                        a.BankName+'<br/>'+isnull(t.usertel,'') as BankName,
	                        t.products,
	                        t.preferential_content,
	                        convert(varchar,t.pref_start_date,111)+'~'+convert(varchar,t.pref_end_date,111) as during,
	                        t.note
                         from CreditCard_Preferential_store t
                         left join sys_Hsien s on t.hsien_id = s.HsienID
                         left join sys_Bank a on t.bank_code= a.BankCode
                         where category=@category ";

            base.Parameter.Clear();

            base.Parameter.Add(new SqlParameter("@category", category));

            if (city.IsNullOrEmpty() == false)
            {
                sql += " and hsien_id=@hsien_id";
                base.Parameter.Add(new SqlParameter("@hsien_id", city));
            }
            if (bank.IsNullOrEmpty() == false)
            {
                sql += " and a.BankCode=@bank";
                base.Parameter.Add(new SqlParameter("@bank", bank));
            }

            sql += @" and t.IsVerift = 1 and t.Enabled =1
                             and  isnull(t.StDate,'1999/1/1') <= convert(date,GetDate()) 
                             and isnull(t.EdDate,'9999/12/31') >= convert(date,GetDate()) 
                            ";



            Paging<CreditCard_Preferential_store> result = new Paging<CreditCard_Preferential_store>();
            result.total = base.SearchCount(sql);
            result.rows = base.SearchListPage<CreditCard_Preferential_store>(sql, (nowpage - 1) * showCount, showCount, " order by pref_start_date");

            return result;
        }

        #endregion

        #region Import
        /// <summary>
        /// 檢查 Excel 必填欄位與正確性
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        /// <remarks >zita 2014/1/6</remarks>
        public DataTable CheckExcelRequiredFields(DataTable importTable, string modelid, out bool IsDirty)
        {
            DataTable newDataRows = importTable.Clone();
            try
            {
                List<string> sAllCases = new List<string>();
                List<string> sAllCasesLines = new List<string>();
                IsDirty = false;

                //取得所有優惠。
                string sql = "select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0";
                base.Parameter.Clear();
                base.Parameter.Add(new SqlParameter("@ModelID", modelid));
                sAllCases = base.SearchList<string>(sql);

                //取得excel資料。
                IEnumerable<string> importCaseIDs = importTable.AsEnumerable().GroupBy(line => line.Field<String>(0)).Select(grp => grp.First().Field<string>(0).ToString()).Where(d => d.Trim().ToUpper() != "END"); //取得匯入檔案中所有的案件代碼。

                sAllCasesLines = base.SearchList<string>(sql);

                importTable.Columns.Add("Error", typeof(string));
                importTable.Columns.Add("number", typeof(int));

                newDataRows.Columns.Add("category");  //類別
                newDataRows.Columns.Add("hsien_id");
                newDataRows.Columns.Add("products");
                newDataRows.Columns.Add("pref_start_date");
                newDataRows.Columns.Add("pref_end_date");
                newDataRows.Columns.Add("username");
                newDataRows.Columns.Add("usertel");
                newDataRows.Columns.Add("bank_name");
                newDataRows.Columns.Add("preferential_content");
                newDataRows.Columns.Add("note");
                newDataRows.Columns.Add("StDate");
                newDataRows.Columns.Add("EdDate");
                newDataRows.Columns.Add("ModelID");
                newDataRows.Columns.Add("Error");
                newDataRows.Columns.Add("number");

                int index = 0;
                DataRow drEnd = null;
                bool bReachEnd = false;
                StringBuilder sbErrors = new StringBuilder();


                foreach (DataRow row in importTable.Rows)
                {
                    if (bReachEnd)
                    {
                        break;
                    }

                    //到達匯入檔案結尾，不再檢查。
                    if (row[0].ToString().ToUpper().Trim() == "END")
                    {
                        drEnd = row;
                        bReachEnd = true;
                        continue;
                    }

                    string category = row[0].ToString().Trim();               //類別
                    string hsien_id = row[1].ToString().Trim();               //地區
                    string products = row[2].ToString().Trim();               //產品名稱
                    string pref_start_date = row[3].ToString().Trim();        //優惠開始時間
                    string pref_end_date = row[4].ToString().Trim();          //優惠結束時間
                    string username = row[5].ToString().Trim();               //聯絡人
                    string usertel = row[6].ToString().Trim();                //聯絡電話
                    string bank_name = row[7].ToString().Trim();              //農漁會名稱
                    string preferential_content = row[8].ToString().Trim();   //優惠內容
                    string note = row[9].ToString().Trim();                   //備註
                    string StDate = row[10].ToString().Trim();                //顯示開始時間
                    string EdDate = row[11].ToString().Trim();                //顯示結束時間

                    row.BeginEdit();


                    DataRow datarow = newDataRows.NewRow();
                    datarow["category"] = category;                           //類別
                    datarow["hsien_id"] = hsien_id;                           //地區
                    datarow["products"] = products;                           //產品名稱
                    datarow["pref_start_date"] = pref_start_date;             //優惠開始時間
                    datarow["pref_end_date"] = pref_end_date;                 //優惠結束時間
                    datarow["username"] = username;                           //聯絡人
                    datarow["usertel"] = usertel;                             //聯絡電話
                    datarow["bank_name"] = bank_name;                         //農漁會名稱
                    datarow["preferential_content"] = preferential_content;   //優惠內容
                    datarow["note"] = note;                                   //備註
                    datarow["StDate"] = StDate;                               //顯示開始時間
                    datarow["EdDate"] = EdDate;                               //顯示結束時間
                    datarow["ModelID"] = modelid;


                    //1. 檢查「優惠時間」格式是否正確。
                    if (!UtilityHelper.ValidateDateTime(pref_start_date, "yyyMMdd"))
                    {
                        sbErrors.AppendLine("優惠開始日期格式應為YYYMMDD");
                        IsDirty = true;
                    }

                    if (!UtilityHelper.ValidateDateTime(pref_end_date, "yyyMMdd"))
                    {
                        sbErrors.AppendLine("優惠結束日期格式應為YYYMMDD");
                        IsDirty = true;
                    }

                    //2. 檢查「顯示時間」格式是否正確。
                    if (!StDate.IsNullOrEmpty())
                    {
                        if (!UtilityHelper.ValidateDateTime(StDate, "yyyMMdd"))
                        {
                            sbErrors.AppendLine("顯示開始日期格式應為YYYMMDD");
                            IsDirty = true;
                        }
                    }

                    if (!EdDate.IsNullOrEmpty())
                    {
                        if (!UtilityHelper.ValidateDateTime(EdDate, "yyyMMdd"))
                        {
                            sbErrors.AppendLine("顯示結束日期格式應為YYYMMDD");
                            IsDirty = true;
                        }
                    }

                    if (hsien_id.IsNullOrEmpty())
                    {
                        sbErrors.AppendLine("地區 必須填寫！");
                        IsDirty = true;
                    }

                    if (bank_name.IsNullOrEmpty())
                    {
                        sbErrors.AppendLine("農漁會名稱 必須填寫！");
                        IsDirty = true;
                    }

                    if (username.IsNullOrEmpty())
                    {
                        sbErrors.AppendLine("聯絡人 必須填寫！");
                        IsDirty = true;
                    }
                    else if (username.Length > 20)
                    {
                        sbErrors.AppendLine("聯絡人 限制輸入20字內！");
                        IsDirty = true;
                    }

                    if (usertel.IsNullOrEmpty())
                    {
                        sbErrors.AppendLine("聯絡人電話 必須填寫！");
                        IsDirty = true;
                    }
                    else if (usertel.Length > 100)
                    {
                        sbErrors.AppendLine("聯絡人電話 限制輸入100字內！");
                        IsDirty = true;
                    }

                    if (products.Length > 500)
                    {
                        sbErrors.AppendLine("產品名稱 限制輸入500字內");
                        IsDirty = true;
                    }

                    if (preferential_content.Length > 500)
                    {
                        sbErrors.AppendLine("優惠內容 限制輸入500字內");
                        IsDirty = true;
                    }

                    if (note.Length > 250)
                    {
                        sbErrors.AppendLine("備註 限制輸入250字內");
                        IsDirty = true;
                    }

                    if (!sbErrors.ToString().IsNullOrEmpty())
                    {
                        //寫入資料檢查的結果。
                        var cindex = ++index;
                        datarow["Error"] = sbErrors.ToString();
                        datarow["number"] = cindex;
                        newDataRows.Rows.Add(datarow);

                        sbErrors.Clear();

                        continue;
                    }

                    //3. 檢查資料是否已存在。
                    sql = $@"select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0 
                                and category=@category and hsien_id=@hsien_id
                                and replace(REPLACE(isnull(products,''),char(10),''),char(13),'')=@products and pref_start_date=@pref_start_date and pref_end_date=@pref_end_date
                                and username=@username and usertel=@usertel and bank_code=@bank_name and StDate=@StDate and EdDate=@EdDate";

                    var nhsien_id = getAreaID(hsien_id);
                    var nbank_code = getbank_code(bank_name);
                    var ncategory = getTypeID(category);

                    base.Parameter.Clear();
                    base.Parameter.Add(new SqlParameter("@ModelID", modelid));
                    base.Parameter.Add(new SqlParameter("@category", ncategory));
                    base.Parameter.Add(new SqlParameter("@hsien_id", nhsien_id));
                    base.Parameter.Add(new SqlParameter("@products", products.Replace("\n","")));
                    base.Parameter.Add(new SqlParameter("@pref_start_date", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(pref_start_date.ToString().Substring(0, 3)) + 1911, pref_start_date.ToString().Substring(3, 2), pref_start_date.ToString().Substring(5, 2)))));
                    base.Parameter.Add(new SqlParameter("@pref_end_date", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(pref_end_date.ToString().Substring(0, 3)) + 1911, pref_end_date.ToString().Substring(3, 2), pref_end_date.ToString().Substring(5, 2)))));
                    base.Parameter.Add(new SqlParameter("@username", username));
                    base.Parameter.Add(new SqlParameter("@usertel", usertel));
                    base.Parameter.Add(new SqlParameter("@bank_name", nbank_code));
                    base.Parameter.Add(new SqlParameter("@StDate", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(StDate.ToString().Substring(0, 3)) + 1911, StDate.ToString().Substring(3, 2), StDate.ToString().Substring(5, 2)))));
                    base.Parameter.Add(new SqlParameter("@EdDate", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(EdDate.ToString().Substring(0, 3)) + 1911, EdDate.ToString().Substring(3, 2), EdDate.ToString().Substring(5, 2)))));

                    sAllCasesLines = base.SearchList<string>(sql);
                    if (sAllCasesLines.Count() > 0)
                    {
                        sbErrors.AppendLine("資料已存在");
                        IsDirty = true;
                    }

                    var nindex = ++index;

                    //寫入資料檢查的結果。
                    row["Error"] = sbErrors.ToString();
                    row["number"] = nindex;

                    datarow["Error"] = sbErrors.ToString();
                    datarow["number"] = nindex;
                    newDataRows.Rows.Add(datarow);

                    sbErrors.Clear();

                    if (!string.IsNullOrEmpty(row["Error"].ToString()))
                    {
                        IsDirty = true;
                    }
                    row.EndEdit();
                }

                //移除END列。
                if (drEnd != null)
                {
                    importTable.Rows.Remove(drEnd);
                }
            }
            catch (Exception ex)
            {
                IsDirty = true;
                logger.Error(ex, "刪除異常,error:" + ex.ToString().NewLineReplace());
            }

            return newDataRows;
        }

        public bool InsertDataToDB(DataTable theTable, string modelid, string loginID, string loginName)
        {
            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {
                        foreach (DataRow row in theTable.Rows)
                        {

                            if (row["Error"].ToString() == "")
                            {
                                //string sql = @"INSERT INTO CreditCard_Preferential_store ([hsien_id],[products],[preferential_content],[pref_start_date],[pref_end_date],[note],[username],[usertel],[ModelID]
                                //                ,[Lang_ID],[IsVerift],[StDate],[EdDate],[category],[bank_name],[Enabled],[CreateUser],[CreateTime])
                                //                VALUES (@hsien_id,@products,@preferential_content,@pref_start_date,@pref_end_date,@note,@username,@usertel,@ModelID
                                //                ,@Lang_ID,@IsVerift,@StDate,@EdDate,@category,@bank_name,@Enabled,@CreateUser,@CreateTime)";


                                base.Parameter.Clear();
                                var bank_name = row["bank_name"].ToString();
                                var bank_code = getbank_code(bank_name);
                                var areaID = getAreaID(row["hsien_id"].ToString());
                                var catagory = getTypeID(row["category"].ToString());

                                var pk = base.InsertObject(new CreditCard_Preferential_store()
                                {
                                    hsien_id = areaID,
                                    products = row["products"].ToString(),
                                    preferential_content = row["preferential_content"].ToString(),
                                    pref_start_date = DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(row["pref_start_date"].ToString().Substring(0, 3)) + 1911, row["pref_start_date"].ToString().Substring(3, 2), row["pref_start_date"].ToString().Substring(5, 2))),
                                    pref_end_date = DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(row["pref_end_date"].ToString().Substring(0, 3)) + 1911, row["pref_end_date"].ToString().Substring(3, 2), row["pref_end_date"].ToString().Substring(5, 2))),
                                    note = row["note"].ToString(),
                                    username = row["username"].ToString(),
                                    usertel = row["usertel"].ToString(),
                                    ModelID = int.Parse(modelid),
                                    Lang_ID = 1,
                                    IsVerift = false,
                                    StDate = DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(row["StDate"].ToString().Substring(0, 3)) + 1911, row["StDate"].ToString().Substring(3, 2), row["StDate"].ToString().Substring(5, 2))),
                                    EdDate = DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(row["EdDate"].ToString().Substring(0, 3)) + 1911, row["EdDate"].ToString().Substring(3, 2), row["EdDate"].ToString().Substring(5, 2))),
                                    category = catagory.ToString(),
                                    bank_code = bank_code,
                                    Enabled = true,
                                    CreateUser = loginID,
                                    CreateTime = DateTime.Now
                                });

                                var products = row["products"].ToString();
                                var preferential_content = row["preferential_content"].ToString();

                                if (!bank_name.IsNullOrEmpty())
                                {
                                    bank_name = bank_name + " - ";
                                }

                                base.InsertObject(new VerifyData()
                                {
                                    ModelID = 23,
                                    ModelItemID = (int)pk,
                                    ModelName = products.IsNullOrEmpty()? bank_name+preferential_content : bank_name+ products,
                                    ModelMainID = 1,
                                    VerifyStatus = 0,
                                    ModelStatus = 1,
                                    UpdateDateTime = DateTime.Now,
                                    UpdateUser = loginName,
                                    UpdateAccount = loginID,
                                    LangID = 1
                                }, tran);
                            }

                            
                        }

                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return false;
                    }
                }
            }

        }

        public DataTable CheckExcelRequiredExit(DataTable importTable, string modelid, out bool IsDirty)
        {

            DataTable newDataRows = importTable.Clone();
            try
            {
                List<string> sAllCases = new List<string>();
                List<string> sAllCasesLines = new List<string>();
                IsDirty = false;

                //取得所有優惠。
                string sql = "select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0";
                base.Parameter.Clear();
                base.Parameter.Add(new SqlParameter("@ModelID", modelid));
                sAllCases = base.SearchList<string>(sql);

                //取得excel資料。
                IEnumerable<string> importCaseIDs = importTable.AsEnumerable().GroupBy(line => line.Field<String>(0)).Select(grp => grp.First().Field<string>(0).ToString()).Where(d => d.Trim().ToUpper() != "END"); //取得匯入檔案中所有的案件代碼。

                sAllCasesLines = base.SearchList<string>(sql);

                importTable.Columns.Add("Error", typeof(string));
                importTable.Columns.Add("number", typeof(int));

                newDataRows.Columns[0].ColumnName = "category";  //類別
                newDataRows.Columns[1].ColumnName = "hsien_id";
                newDataRows.Columns[2].ColumnName = "products";
                newDataRows.Columns[3].ColumnName = "pref_start_date";
                newDataRows.Columns[4].ColumnName = "pref_end_date";
                newDataRows.Columns[5].ColumnName = "username";
                newDataRows.Columns[6].ColumnName = "usertel";
                newDataRows.Columns[7].ColumnName = "bank_name";
                newDataRows.Columns[8].ColumnName = "preferential_content";
                newDataRows.Columns[9].ColumnName = "note";
                newDataRows.Columns[10].ColumnName = "StDate";
                newDataRows.Columns[11].ColumnName = "EdDate";
                newDataRows.Columns.Add("ModelID");
                newDataRows.Columns.Add("Error");
                newDataRows.Columns.Add("number");


                int index = 0;
                DataRow drEnd = null;
                bool bReachEnd = false;
                StringBuilder sbErrors = new StringBuilder();

                foreach (DataRow row in importTable.Rows)
                {
                    if (bReachEnd)
                    {
                        break;
                    }
                    //到達匯入檔案結尾，不再檢查。

                    string category = row[0].ToString().Trim();               //類別
                    string hsien_id = row[1].ToString().Trim();               //地區
                    string products = row[2].ToString().Trim();               //產品名稱
                    string pref_start_date = row[3].ToString().Trim();        //優惠開始時間
                    string pref_end_date = row[4].ToString().Trim();          //優惠結束時間
                    string username = row[5].ToString().Trim();               //聯絡人
                    string usertel = row[6].ToString().Trim();                //聯絡電話
                    string bank_name = row[7].ToString().Trim();              //農漁會名稱
                    string preferential_content = row[8].ToString().Trim();   //優惠內容
                    string note = row[9].ToString().Trim();                   //備註
                    string StDate = row[10].ToString().Trim();                //顯示開始時間
                    string EdDate = row[11].ToString().Trim();                //顯示結束時間

                    row.BeginEdit();
                    if (row[0].ToString().ToUpper().Trim() == "END")
                    {
                        drEnd = row;
                        bReachEnd = true;
                    }
                    else
                    {
                        DataRow datarow = newDataRows.NewRow();
                        datarow["category"] = category;                           //類別
                        datarow["hsien_id"] = hsien_id;                           //地區
                        datarow["products"] = products;                           //產品名稱
                        datarow["pref_start_date"] = pref_start_date;             //優惠開始時間
                        datarow["pref_end_date"] = pref_end_date;                 //優惠結束時間
                        datarow["username"] = username;                           //聯絡人
                        datarow["usertel"] = usertel;                             //聯絡電話
                        datarow["bank_name"] = bank_name;                         //農漁會名稱
                        datarow["preferential_content"] = preferential_content;   //優惠內容
                        datarow["note"] = note;                                   //備註
                        datarow["StDate"] = StDate;                               //顯示開始時間
                        datarow["EdDate"] = EdDate;                               //顯示結束時間
                        datarow["ModelID"] = modelid;


                        //1. 檢查「優惠時間」格式是否正確。
                        if (!UtilityHelper.ValidateDateTime(pref_start_date, "yyyMMdd"))
                        {
                            sbErrors.AppendLine("優惠開始日期格式應為YYYMMDD");
                            IsDirty = true;
                        }

                        if (!UtilityHelper.ValidateDateTime(pref_end_date, "yyyMMdd"))
                        {
                            sbErrors.AppendLine("優惠結束日期格式應為YYYMMDD");
                            IsDirty = true;
                        }

                        //2. 檢查「顯示時間」格式是否正確。
                        if (!StDate.IsNullOrEmpty())
                        {
                            if (!UtilityHelper.ValidateDateTime(StDate, "yyyMMdd"))
                            {
                                sbErrors.AppendLine("顯示開始日期格式應為YYYMMDD");
                                IsDirty = true;
                            }
                        }

                        if (!EdDate.IsNullOrEmpty())
                        {
                            if (!UtilityHelper.ValidateDateTime(EdDate, "yyyMMdd"))
                            {
                                sbErrors.AppendLine("顯示結束日期格式應為YYYMMDD");
                                IsDirty = true;
                            }
                        }
                        if (!sbErrors.ToString().IsNullOrEmpty())
                        {
                            //寫入資料檢查的結果。
                            var cindex = ++index;
                            datarow["Error"] = sbErrors.ToString();
                            datarow["number"] = cindex;
                            newDataRows.Rows.Add(datarow);

                            sbErrors.Clear();

                            continue;
                        }

                        //3. 檢查資料是否已存在。
                        sql = $"select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0 " +
                                    "and category=@category and hsien_id=@hsien_id " +
                                    "and replace(REPLACE(isnull(products,''),char(10),''),char(13),'')=@products and pref_start_date=@pref_start_date and pref_end_date=@pref_end_date " +
                                    "and username=@username and usertel=@usertel and bank_code=@bank_name and StDate=@StDate and EdDate=@EdDate";

                        var nhsien_id = getAreaID(hsien_id);
                        var nbank_code = getbank_code(bank_name);
                        var ncategory = getTypeID(category);

                        base.Parameter.Clear();
                        base.Parameter.Add(new SqlParameter("@ModelID", modelid));
                        base.Parameter.Add(new SqlParameter("@category", ncategory));
                        base.Parameter.Add(new SqlParameter("@hsien_id", nhsien_id));
                        base.Parameter.Add(new SqlParameter("@products", products.Replace("\n","")));
                        base.Parameter.Add(new SqlParameter("@pref_start_date", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(pref_start_date.ToString().Substring(0, 3)) + 1911, pref_start_date.ToString().Substring(3, 2), pref_start_date.ToString().Substring(5, 2)))));
                        base.Parameter.Add(new SqlParameter("@pref_end_date", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(pref_end_date.ToString().Substring(0, 3)) + 1911, pref_end_date.ToString().Substring(3, 2), pref_end_date.ToString().Substring(5, 2)))));
                        base.Parameter.Add(new SqlParameter("@username", username));
                        base.Parameter.Add(new SqlParameter("@usertel", usertel));
                        base.Parameter.Add(new SqlParameter("@bank_name", nbank_code));
                        base.Parameter.Add(new SqlParameter("@StDate", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(StDate.ToString().Substring(0, 3)) + 1911, StDate.ToString().Substring(3, 2), StDate.ToString().Substring(5, 2)))));
                        base.Parameter.Add(new SqlParameter("@EdDate", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(EdDate.ToString().Substring(0, 3)) + 1911, EdDate.ToString().Substring(3, 2), EdDate.ToString().Substring(5, 2)))));

                        sAllCasesLines = base.SearchList<string>(sql);
                        if (sAllCasesLines.Count() <= 0)
                        {
                            sbErrors.AppendLine("資料不存在");
                            IsDirty = true;
                        }

                        //寫入資料檢查的結果。
                        row["Error"] = sbErrors.ToString();
                        row["number"] = ++index;
                        var nindex = ++index;

                        
                        datarow["Error"] = sbErrors.ToString();
                        datarow["number"] = nindex;
                        newDataRows.Rows.Add(datarow);

                        sbErrors.Clear();

                        if (!string.IsNullOrEmpty(row["Error"].ToString()))
                        {
                            IsDirty = true;
                        }
                        row.EndEdit();

                        //移除END列。
                        if (drEnd != null)
                        {
                            newDataRows.Rows.Remove(drEnd);
                        }
                    }

                }


            }
            catch (Exception ex)
            {

                IsDirty = true;

            }

            return newDataRows;
        }

        public bool DeleteDataToDB(DataTable theTable, string modelid, string loginID, string username)
        {
            using (SqlConnection connection = base.OpenConnection())
            {
                using (SqlTransaction tran = base.GetTransaction(connection))
                {
                    try
                    {
                        foreach (DataRow row in theTable.Rows)
                        {
                            base.Parameter.Clear();
                            var bank_name = row["bank_name"].ToString();
                            var bank_code = getbank_code(bank_name);
                            var areaID = getAreaID(row["hsien_id"].ToString());
                            var catagory = getTypeID(row["category"].ToString());

                            string sql = @"select * from CreditCard_Preferential_store t where ModelID=@ModelID and isnull(Del,0) = 0 
                                and category=@category and hsien_id=@hsien_id
                                and replace(REPLACE(isnull(products,''),char(10),''),char(13),'')=@products and pref_start_date=@pref_start_date and pref_end_date=@pref_end_date
                                and username=@username and usertel=@usertel and bank_code=@bank_code ";

                            SqlParameter[] parms1 = new SqlParameter[] {
                                new SqlParameter("@ModelID", modelid),
                                new SqlParameter("@category",catagory),
                                new SqlParameter("@hsien_id",areaID),
                                new SqlParameter("@products",row["products"].ToString().Replace("\n","")),
                                new SqlParameter("@pref_start_date",DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00",int.Parse(row["pref_start_date"].ToString().Substring(0,3))+1911, row["pref_start_date"].ToString().Substring(3,2), row["pref_start_date"].ToString().Substring(5,2)))),
                                new SqlParameter("@pref_end_date", DateTime.Parse(string.Format("{0}/{1}/{2} 00:00:00", int.Parse(row["pref_end_date"].ToString().Substring(0, 3)) + 1911, row["pref_end_date"].ToString().Substring(3, 2), row["pref_end_date"].ToString().Substring(5, 2)))),
                                new SqlParameter("@username",row["username"].ToString()),
                                new SqlParameter("@usertel",row["usertel"].ToString()),
                                new SqlParameter("@bank_code",bank_code)
                            };

                            base.Parameter.AddRange(parms1);
                            string ItemID = base.ExecuteScalar(sql, "0", tran).ToString();


                            sql = @"select * from VerifyData where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ModelID", 23));
                            base.Parameter.Add(new SqlParameter("@ModelMainID", 1));
                            base.Parameter.Add(new SqlParameter("@ModelItemID", ItemID));

                            List<VerifyData> hasvdata = base.SearchList<VerifyData>(sql, tran);
                            if (hasvdata.Count() == 0)
                            {
                                if (!bank_name.IsNullOrEmpty())
                                {
                                    bank_name = bank_name + " - ";
                                }


                                base.InsertObject(new VerifyData()
                                {
                                    ModelID = 23,
                                    ModelItemID = int.Parse(ItemID),
                                    ModelName = row["products"].ToString().IsNullOrEmpty() ? bank_name+ row["preferential_content"].ToString() : bank_name+ row["products"].ToString(),
                                    ModelMainID = 1,
                                    VerifyStatus = 0,
                                    ModelStatus = 3,
                                    UpdateDateTime = DateTime.Now,
                                    UpdateUser = username,
                                    UpdateAccount = loginID,
                                    LangID = 1
                                }, tran);
                            }
                            else
                            {
                                sql = @"update VerifyData set VerifyStatus=0,ModelStatus=@ModelStatus,VerifyDateTime=Null,VerifyUser=''
                                                        ,VerifyName='',UpdateDateTime=GetDate(),UpdateUser=@UpdateUser,UpdateAccount=@UpdateAccount
                                          where ModelID=@ModelID and ModelMainID=@ModelMainID and ModelItemID=@ModelItemID";
                                base.Parameter.Clear();
                                base.Parameter.Add(new SqlParameter("@ModelStatus", 3));
                                base.Parameter.Add(new SqlParameter("@UpdateUser", username));
                                base.Parameter.Add(new SqlParameter("@ModelID", 23));
                                base.Parameter.Add(new SqlParameter("@UpdateAccount", loginID));
                                base.Parameter.Add(new SqlParameter("@ModelMainID", 1));
                                base.Parameter.Add(new SqlParameter("@ModelItemID", ItemID));
                                base.ExeNonQuery(sql, tran);
                            }

                            sql = $@"update CreditCard_Preferential_store set Del=1,UpdateUser=@UpdateUser
                                        where ItemID=@ItemID ";
                            base.Parameter.Clear();
                            base.Parameter.Add(new SqlParameter("@ItemID", ItemID));
                            base.Parameter.Add(new SqlParameter("@UpdateUser", loginID));
                            base.ExeNonQuery(sql, tran);

                        }
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        return false;
                    }
                }
            }
        }
        public string getbank_code(string bank_name)
        {
            var sql = $"select Organ_id from v_Card_Org where BankName=@BankName";

            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@BankName", bank_name));

            var bankID = base.GetObject<string>(sql);

            return bankID;
        }
        public string getbank_name(string bank_code)
        {
            var sql = $"select BankName from v_Card_Org where Organ_id=@Organ_id";

            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@Organ_id", bank_code));

            var bankID = base.GetObject<string>(sql);

            return bankID;
        }

        public string getAreaID(string AreaName)
        {
            var sql = $"select HsienID from sys_Hsien where HsienName=@HsienName";
            base.Parameter.Clear();
            base.Parameter.Add(new SqlParameter("@HsienName", AreaName));

            var AreaID = base.GetObject<string>(sql);

            return AreaID;
        }

        public int getTypeID(string TypeName)
        {
            if (TypeName == "店內活動")
            {
                return 1;
            }
            else if (TypeName == "店外活動")
            {

                return 2;
            }


            return 0;
        }
        #endregion
    }
}
