using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web;
using System.Web.Http;
using ExcelDataReader;
using Wade.Business;
using Wade.Common;
using Wade.Model;
using System.Runtime.InteropServices;
using Newtonsoft.Json;
using System.Drawing.Design;
using System.Web.Http.Cors;
using System.ComponentModel;
using ClosedXML.Excel;

namespace WebAPI.Controllers
{
    public class Message
    {
        public String outNoticemsg, outErrmsg, outCustmsg;
        public int code, Status;
    }
   
    public class 核稿歷史
    {
        public String case_volume { get; set; }
        public String case_type_zh_tw { get; set; }
        public String proc_status_zh_tw { get; set; }
        public String ctrl_proc_zh_tw { get; set; }
        public String audit_cn_name { get; set; }
        public String audit_time { get; set; }
        public String remark { get; set; }
        public String audit_type_id { get; set; }
        public String audit_type_zh_cn { get; set; }
        public String to_cn_name { get; set; }
        public String finish_date { get; set; }
        public String score { get; set; }

    }
    public class MessageList
    {
        public List<Message> msglist; //報價規則
        public List<核稿歷史> CurHislist; //核稿評分歷史
        public int CurHislist_Count; //核稿評分歷史
        public int ListCode //回傳代碼
            , ErrCount; //錯誤筆數
        public String Listmsg; //回傳訊息
    }
    public class AllowCORSAttribute : System.Web.Mvc.ActionFilterAttribute
    {
        public override void OnActionExecuting(System.Web.Mvc.ActionExecutingContext filterContext)
        {
            filterContext.RequestContext.HttpContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            filterContext.RequestContext.HttpContext.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type");
            filterContext.RequestContext.HttpContext.Response.AddHeader("Access-Control-Allow-Methods", "OPTIONS,POST");

            base.OnActionExecuting(filterContext);
        }
    }
    public class ValuesController : ApiController
    {
        //Message Outmsg;
        MessageList Outmsg;
        public ValuesController()
        {
            Outmsg = new MessageList();
        }
        private CtrlBaseInfo BSS = new CtrlBaseInfo();
        private List<Dictionary<string, string>> FCCate = new List<Dictionary<string, string>>();
        private String u_user_id;

        [Route("Get_cur_his")]
        [HttpPost]
        [HttpGet]
        public MessageList Get_cur_his(String Get_cur_his_id)
        {
            String connString = ConfigurationManager.ConnectionStrings["eflow_cur"].ConnectionString;
            List<核稿歷史> Hislist = new List<核稿歷史>();
            try
            {
                using (SqlConnection Conn = new SqlConnection(connString))
                {
                    核稿歷史 hisT = new 核稿歷史();
                    SqlCommand cmd = new SqlCommand("", Conn);
                    cmd.CommandText = @"
                    select audit_cn_name,c.audit_time,remark,audit_type_id,e.audit_type_zh_cn,c.to_cn_name,d.case_volume,c.to_cn_name,d.case_type_zh_tw,d.proc_status_zh_tw,d.ctrl_proc_zh_tw,Convert(varchar(10) ,d.finish_date, 111) as finish_date ,c.score from p_eflow a
                    left join s_flow_activity b on a.eflow_id=b.obj_id
                    left join s_flow_history c on a.eflow_id=c.obj_id
                    left join evw_p_proc_info d on a.proc_id=d.proc_id 
					left join s_audit_type e on c.audit_type_id=e.audit_id 
                    where a.eflow_id=@eflow_id
					order by audit_time desc
                    ";
                    cmd.Parameters.AddWithValue("@eflow_id", Get_cur_his_id);
                    DataSet ds = GetSqlToDs(cmd);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        hisT = new 核稿歷史()
                        {
                            case_volume = dr["case_volume"].ToString(),
                            audit_time = dr["audit_time"].ToString(),
                            audit_cn_name = dr["audit_cn_name"].ToString(),
                            to_cn_name = dr["to_cn_name"].ToString(),
                            remark = dr["remark"].ToString(),
                            audit_type_id = dr["audit_type_id"].ToString(),
                            audit_type_zh_cn = dr["audit_type_zh_cn"].ToString(),
                            case_type_zh_tw = dr["case_type_zh_tw"].ToString(),
                            proc_status_zh_tw = dr["proc_status_zh_tw"].ToString(),
                            ctrl_proc_zh_tw = dr["ctrl_proc_zh_tw"].ToString(),
                            finish_date = dr["finish_date"].ToString(),
                            score = dr["score"].ToString()
                        };
                        Hislist.Add(hisT);
                    }
                }
                Outmsg.CurHislist = Hislist;
                Outmsg.CurHislist_Count = Hislist.Count;
                Outmsg.ListCode = 0;
                Outmsg.Listmsg = "上傳成功";
            }
            catch (Exception e)
            {
                Outmsg.ListCode = 1;
                Outmsg.Listmsg = "Exception:" + e.StackTrace;
            }
            return Outmsg;
        }

        public string WriteTsv<T>(IEnumerable<T> data)
        {
            StringBuilder output = new StringBuilder();
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            PropertyDescriptor desc = TypeDescriptor.GetProperties(typeof(MessageList)).Find("Name", true);

            foreach (PropertyDescriptor prop in props)
            {
                output.Append(prop.DisplayName); // header
                output.Append(",");
            }
            output.AppendLine();
            foreach (T item in data)
            {
                foreach (PropertyDescriptor prop in props)
                {
                    output.Append(prop.Converter.ConvertToString(
                         prop.GetValue(item)));
                    output.Append(",");
                }
                output.AppendLine();
            }
            return output.ToString();
        }


        [Route("Download_cur_hiss_Excel")]
        [HttpPost]
        [HttpGet]
        [EnableCors(origins: "*", headers: "*", methods: "*")]
        public HttpResponse Download_cur_hiss_Excel(String trids)
        {
            MessageList msg = Get_cur_hiss(trids,false);
            String TmpPath = HttpContext.Current.Server.MapPath(@"~" + "/Template/核稿歷史樣板.xlsx");
            HttpResponse response = System.Web.HttpContext.Current.Response;
            HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
            try
            {
                var ms = new MemoryStream();
                using (XLWorkbook wb = new XLWorkbook(TmpPath))
                {
                    IXLWorksheet worksheet = wb.Worksheet(1);
                    int curRow = 2, curcell = 1;

                    foreach (核稿歷史 chis in msg.CurHislist)
                    {
                        ClosedXML.Excel.IXLCells cells = worksheet.Row(curRow).Cells();
                        curcell = 1;
                        worksheet.Cell(curRow, curcell).Value = chis.case_volume;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.case_type_zh_tw;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.audit_cn_name;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.audit_time;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.remark;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.audit_type_zh_cn;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.to_cn_name;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.ctrl_proc_zh_tw;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.proc_status_zh_tw;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.finish_date;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.score;
                        //案號 案件類型    辦理人員 辦理時間    辦理意見 辦理結果    交付人員 處理事項    科目 處理事項狀態  完成日期 核稿總分

                        curRow++;
                    }

                    worksheet.Columns().AdjustToContents();
                    wb.SaveAs(ms);
                    response.Clear();
                    response.AddHeader("Content-Disposition", "attachment; filename=" + "RecordExport_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

                    ms.WriteTo(response.OutputStream);
                    ms.Close();
                    response.End();
                }

                //result.Content = new StringContent(WriteTsv(msg.CurHislist));

                //result.Content = new StreamContent(ms); 
                //result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); 

            }
            catch (Exception e)
            {
                result.Content = new StringContent(e.Message);
            }


            //result.Content.Headers.ContentType.CharSet = "BIG-5";
            //result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            //{ 
            //    DispositionType = "UTF-8"
            //}; //attachment will force download
            //result.Content.Headers.ContentDisposition.FileName = "RecordExport_"+ DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            //result.Content.Headers.Add("CodePage", Encoding.ASCII.CodePage.ToString());
            return response;
        }
        [Route("Get_cur_his_test")]
        [HttpPost]
        [HttpGet]
        [EnableCors(origins: "*", headers: "*", methods: "*")]
        public MessageList Get_cur_hiss(String trids)
        {
            String connString = ConfigurationManager.ConnectionStrings["eflow_cur"].ConnectionString;
            HttpRequest Req = HttpContext.Current.Request;
            List<核稿歷史> Hislist = new List<核稿歷史>();
            Boolean isCount = false;
            //List<Table_Data> Table_Data=new List<Table_Data> (); 
            try
            {
                if (!String.IsNullOrEmpty(Req.Form["isCount"]))
                    isCount = Boolean.Parse(Req.Form["isCount"]);
                String trids2 = Req.Form["trids"];
                String[] strids = Req.Form["trids"].Split(',');
                using (SqlConnection Conn = new SqlConnection(connString))
                {
                    核稿歷史 hisT = new 核稿歷史();
                    SqlCommand cmd = new SqlCommand("", Conn);

                    String Tid = "";
                    var parameters = new string[strids.Length];
                    for (int i = 0; i < strids.Length; i++)
                    {
                        Tid = strids[i];
                        if (!String.IsNullOrEmpty(Tid))
                        {
                            parameters[i] = string.Format("@id{0}", i);
                            cmd.Parameters.AddWithValue(parameters[i], Tid);
                        }
                    }
                    String fields = @"eflow_id,audit_cn_name,c.audit_time,remark,audit_type_id,e.audit_type_zh_cn,d.case_volume,c.to_cn_name,d.case_type_zh_tw,d.proc_status_zh_tw,d.ctrl_proc_zh_tw
                    , Convert(varchar(10), d.finish_date, 111) as finish_date ,c.score"
                            Groups= "group by a.eflow_id";

                    cmd.CommandText = String.Format(@"
                    select  from p_eflow a
                    left join s_flow_activity b on a.eflow_id=b.obj_id
                    left join s_flow_history c on a.eflow_id=c.obj_id
                    left join evw_p_proc_info d on a.proc_id=d.proc_id 
					left join s_audit_type e on c.audit_type_id=e.audit_id 
                    where a.eflow_id IN ({0}) 
                    @Group
					order by audit_time desc
                    ", string.Join(",", parameters));
                    cmd.Parameters.AddWithValue("@fields", "");
                    cmd.Parameters.AddWithValue("@Group", "");
                    DataSet ds = GetSqlToDs(cmd);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        hisT = new 核稿歷史()
                        {
                            case_volume = dr["case_volume"].ToString(),
                            audit_time = dr["audit_time"].ToString(),
                            audit_cn_name = dr["audit_cn_name"].ToString(),
                            to_cn_name = dr["to_cn_name"].ToString(),
                            remark = dr["remark"].ToString(),
                            audit_type_id = dr["audit_type_id"].ToString(),
                            audit_type_zh_cn = dr["audit_type_zh_cn"].ToString(),
                            case_type_zh_tw = dr["case_type_zh_tw"].ToString(),
                            proc_status_zh_tw = dr["proc_status_zh_tw"].ToString(),
                            ctrl_proc_zh_tw = dr["ctrl_proc_zh_tw"].ToString(),
                            finish_date = dr["finish_date"].ToString(),
                            score = dr["score"].ToString()
                        };
                        Hislist.Add(hisT);
                    }
                }
                Outmsg.CurHislist = Hislist;
                Outmsg.ListCode = 0;
                Outmsg.Listmsg = "上傳成功";
            }
            catch (Exception e)
            {
                Outmsg.ListCode = 1;
                Outmsg.Listmsg = "Exception:" + e.StackTrace;
            }
            return Outmsg;
        }

        #region MyRegion

        #endregion
        [Route("getList")]
        [HttpPost]

        public MessageList GetList(Boolean ischeck)
        {
            DataSet ds;
            //this.u_user_id = Update_user_id;
            try
            {
                //ReadExcelSTreamTest(null);
                HttpRequest Req = HttpContext.Current.Request;
                HttpPostedFile f;
                if (Req.Files.Count > 0)
                {
                    foreach (String FileName in Req.Files.Keys)
                    {
                        u_user_id = Req.Form["Update_user_id"];
                        f = Req.Files[FileName];
                        using (var stream = f.InputStream)
                        {
                            ds = ExcelToRead(stream);
                            Outmsg.msglist = CheckImport(ds, ischeck);
                        }
                    }
                }
                else
                {
                    Outmsg.ListCode = 1;
                    Outmsg.Listmsg = "請上傳檔案";
                }
            }
            catch (Exception e)
            {
                Outmsg.ListCode = 1;
                Outmsg.Listmsg = "Exception:" + e.StackTrace;
            }
            List<Message> mlist = Outmsg.msglist.AsEnumerable().Where(a => a.code == 1).ToList();

            if (ischeck)
            {
                Outmsg.ListCode = 2;

                if (mlist.Count > 0)
                {
                    Outmsg.ErrCount = mlist.Count;
                    Outmsg.Listmsg = "尚有錯誤資料,請修正完所有錯誤資料後再上傳檢查 筆數:" + Outmsg.ErrCount;
                }
                else
                {
                    Outmsg.Listmsg = "測試正常";
                }
            }
            else
            {
                Outmsg.ListCode = 0;
                Outmsg.Listmsg = "上傳成功";
            }

            return Outmsg;
        }

        //[Route("CheckList")]
        //[HttpPost]

        //public Message CheckList()
        //{
        //    DataSet ds;
        //    try
        //    {
        //        HttpRequest Req = HttpContext.Current.Request;
        //        HttpPostedFile f;
        //        if (Req.Files.Count > 0)
        //        {
        //            foreach (String FileName in Req.Files.Keys)
        //            {
        //                u_user_id = Req.Form["Update_user_id"];
        //                f = Req.Files[FileName];
        //                using (var stream = f.InputStream)
        //                {
        //                    ds = ExcelToRead(stream);
        //                    Outmsg.outErrmsg = CheckImport(ds,true);
        //                }
        //            }
        //        }
        //        else
        //        {
        //            Outmsg.code = 1;
        //            Outmsg.outErrmsg = "請上傳檔案";
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        Outmsg.code = 1;
        //        Outmsg.outErrmsg += "\n" + "Exception:" + e.StackTrace;
        //    }

        //    Outmsg.code = String.IsNullOrEmpty(Outmsg.outErrmsg) ? 0 : 1;
        //    return Outmsg;
        //}
        private DataSet ExcelToRead(Stream st)
        {
            DataSet ds;
            IExcelDataReader reader = null;
            reader = ExcelReaderFactory.CreateBinaryReader(st, new ExcelReaderConfiguration()
            { FallbackEncoding = Encoding.GetEncoding("UTF-8") });
            using (reader)
            {
                ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                {

                    UseColumnDataType = false,
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        //設定讀取資料時是否忽略標題
                        UseHeaderRow = false
                    }
                });
            }
            return ds;
        }

        /// <summary>
        /// 轉換id from METADATA 
        /// </summary>
        /// <param name="MetaData"></param>
        /// <param name="ids"></param>
        /// <param name="drRow"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        private String SetIdFromMetaData(ref DataSet MetaData, ref Dictionary<String, String> ids, DataRow drRow, ref String errmsg, ref String Noticemsg)
        {
            DataRow drTemp;
            String FieldName = "", FieldValue = "", AddFieldName = "";
            #region MetaData Set

            FieldName = "案件類型"; AddFieldName = "case_type_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[0].AsEnumerable().Where(p => p.Field<String>("case_type_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!(String.IsNullOrWhiteSpace(FieldValue))) errmsg += "(無" + FieldName + ":" + FieldValue + "),";
            }

            FieldName = "申請類型"; AddFieldName = "apply_type_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[1].AsEnumerable().Where(p => p.Field<String>("apply_type_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!(String.IsNullOrWhiteSpace(FieldValue))) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }

            FieldName = "處理事項"; AddFieldName = "ctrl_proc_id"; FieldValue = drRow[FieldName].ToString();
            String case_type_id = ids["case_type_id"];
            drTemp = MetaData.Tables[2].AsEnumerable().Where(p => p.Field<String>("ctrl_proc_zh_tw") == ToTraditional(FieldValue) && p.Field<String>("case_type_id") == case_type_id).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!String.IsNullOrWhiteSpace(FieldValue)) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "業務類型"; AddFieldName = "business_type_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[3].AsEnumerable().Where(p => p.Field<String>("business_type_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!(String.IsNullOrWhiteSpace(FieldValue))) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "程序名稱"; AddFieldName = "fc_cate_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[4].AsEnumerable().Where(p => p.Field<String>("text_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!String.IsNullOrWhiteSpace(FieldValue)) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "客戶名稱(中)"; AddFieldName = "customer_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[5].AsEnumerable().Where(p => p.Field<String>("customer_name") == drRow[FieldName].ToString() || (p.Field<String>("customer_name_en") == drRow["客戶名稱(英)"].ToString() && !string.IsNullOrEmpty(drRow["客戶名稱(英)"].ToString()))).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                errmsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "案件階段"; AddFieldName = "case_status_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[6].AsEnumerable().Where(p => p.Field<String>("case_status_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!String.IsNullOrWhiteSpace(FieldValue)) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "科目"; AddFieldName = "fee_cate_workitem"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[7].AsEnumerable().Where(p => p.Field<String>("text_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp["value"].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                errmsg += "(無" + FieldName + ":" + FieldValue + "),";
            }

            FieldName = "稅別"; AddFieldName = "fo_tax_rate_name"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[8].AsEnumerable().Where(p => p.Field<String>("text_zh_tw") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp["value"].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!String.IsNullOrWhiteSpace(FieldValue)) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "幣別"; AddFieldName = "currency_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[9].AsEnumerable().Where(p => p.Field<String>("currency_id") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp[AddFieldName].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                errmsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            FieldName = "國家(地區)"; AddFieldName = "country_id"; FieldValue = drRow[FieldName].ToString();
            drTemp = MetaData.Tables[10].AsEnumerable().Where(p => p.Field<String>("country_id") == ToTraditional(FieldValue)).FirstOrDefault();
            if (drTemp != null) ids.Add(AddFieldName, drTemp["country_id"].ToString());
            else
            {
                ids.Add(AddFieldName, "");
                if (!String.IsNullOrWhiteSpace(FieldValue)) Noticemsg += "(無" + FieldName + ":" + FieldValue + "),";
            }
            #endregion
            return errmsg;
        }
        private List<Message> CheckImport(DataSet ds, Boolean isCheck)
        {
            String errmsg = "", msg = "";
            List<Message> OutmsgLIst = new List<Message>();
            String connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            Message msgt;
            try
            {
                var table = ds.Tables[0];
                DataRow drh = table.Rows[0];
                for (int i = 0; i < drh.ItemArray.Length; i++)
                    table.Columns[i].ColumnName = ToTraditional(drh[i].ToString());

                Dictionary<String, String> ids = new Dictionary<String, String>(), Temps = new Dictionary<String, String>();
                DataRow drRow;
                using (SqlConnection Conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand("", Conn);
                    DataSet Metadata = GetMetaData(ref cmd);
                    for (int row = 1; row < table.Rows.Count; row++)
                    {
                        ids.Clear(); Temps.Clear();
                        drRow = table.Rows[row];
                        errmsg = ""; msg = "";
                        #region MetaData Set
                        SetIdFromMetaData(ref Metadata, ref ids, drRow, ref errmsg, ref msg);
                        msgt = new Message() { outCustmsg = drRow["客戶名稱(中)"].ToString(), outErrmsg = errmsg, outNoticemsg = msg };
                        #endregion
                        if (!string.IsNullOrEmpty(errmsg)) msgt.code = 1;
                        else msgt.code = 0;

                        Temps.Add("case_type_id", ids["case_type_id"]);
                        Temps.Add("business_type_id", ids["business_type_id"]);
                        Temps.Add("ctrl_proc_id", ids["ctrl_proc_id"]);
                        Temps.Add("fc_cate_id", ids["fc_cate_id"]);
                        Temps.Add("apply_type_id", ids["apply_type_id"]);
                        Temps.Add("customer_id", ids["customer_id"]);
                        Temps.Add("case_status_id", ids["case_status_id"]);
                        Temps.Add("fee_cate_workitem", ids["fee_cate_workitem"]);
                        Temps.Add("fee_type_zh_tw", drRow["工作項目(繁體)"].ToString());
                        Temps.Add("tax_id_name", ids["fo_tax_rate_name"]);
                        Temps.Add("currency_id", ids["currency_id"].ToString());
                        Temps.Add("country_id", ids["country_id"].ToString());
                        Temps.Add("update_user_id", u_user_id);

                        String config_id = Guid.NewGuid().ToString().ToUpper(), fee_type_id = Guid.NewGuid().ToString().ToUpper();
                        Temps.Add("config_id", config_id);
                        Temps.Add("fee_type_id", fee_type_id);
                        //Temps.Add("fee_cate_workitem", Get_fee_cate_workitem(drRow["科目"].ToString()));
                        //Temps.Add("country_id", drRow["國家(地區)"].ToString());
                        Temps.Add("zh_tw", drRow["工作項目(繁體)"].ToString());
                        Temps.Add("en_us", drRow["工作項目(英文)"].ToString());
                        Temps.Add("amount", drRow["未稅金額"].ToString() == "" ? "0" : drRow["未稅金額"].ToString());
                        Temps.Add("basic_fee_small", drRow["官費未稅金額\n(小實體)"].ToString() == "" ? "0" : drRow["官費未稅金額\n(小實體)"].ToString());
                        Temps.Add("basic_fee_micro", drRow["官費未稅金額\n(微實體)"].ToString() == "" ? "0" : drRow["官費未稅金額\n(微實體)"].ToString());
                        Temps.Add("fee_from", drRow["最小金額\n(區間值)"].ToString() == "" ? "0" : drRow["最小金額\n(區間值)"].ToString());
                        Temps.Add("fee_to", drRow["最大金額\n(區間值)"].ToString() == "" ? "0" : drRow["最大金額\n(區間值)"].ToString());
                        Temps.Add("is_item_fee", drRow["是否按項收費"].ToString());
                        Temps.Add("item_exceed", drRow["基礎項數"].ToString() == "" ? "0" : drRow["基礎項數"].ToString());
                        Temps.Add("item_exceed_unit", drRow["計費項名稱"].ToString());
                        Temps.Add("item_fee", drRow["每項費用"].ToString() == "" ? "0" : drRow["每項費用"].ToString());
                        Temps.Add("item_fee_small", drRow["每項費用(小實體)"].ToString() == "" ? "0" : drRow["每項費用(小實體)"].ToString());
                        Temps.Add("item_fee_micro", drRow["每項費用(微實體)"].ToString() == "" ? "0" : drRow["每項費用(微實體)"].ToString());
                        Temps.Add("item_unit", drRow["計費項單位"].ToString());
                        Temps.Add("is_shibao", drRow["實報實銷"].ToString());
                        Temps.Add("is_gean", drRow["個案報價"].ToString());
                        //Temps.Add("tax_id_name", Get_Tax_id(drRow["稅別"].ToString()));
                        Temps.Add("tax_type_name", Get_tax_type(drRow["內含/外加"].ToString()));
                        Temps.Add("config_type_name", drRow["費用帶出時間"].ToString());

                        DataSet ds_IsExistsFeeWorkItem = IsExistsFeeWorkItem(ref cmd, Temps);
                        if (isCheck)
                        {
                            if (msgt.outNoticemsg.Length > 0) msgt.outNoticemsg = msgt.outNoticemsg.Remove(msgt.outNoticemsg.Length - 1);
                            if (msgt.outErrmsg.Length > 0) msgt.outErrmsg = msgt.outErrmsg.Remove(msgt.outErrmsg.Length - 1);
                            if (ds_IsExistsFeeWorkItem.Tables[0].Rows.Count == 0)
                            {
                                //AddFeeWorkItem(ref cmd, ref Temps);
                            }
                            else
                            {
                                msgt.outNoticemsg = "{工作項目已存在不新增}," + msgt.outNoticemsg;
                                Temps["seq"] = ds_IsExistsFeeWorkItem.Tables[0].Rows[0]["seq"].ToString();
                                Temps["fee_type_id"] = ds_IsExistsFeeWorkItem.Tables[0].Rows[0]["fee_type_id"].ToString();
                            }

                            DataSet ds_IsExistsFeeOfferConfig = IsExistsFeeOfferConfig(ref cmd, Temps);
                            if (ds_IsExistsFeeOfferConfig.Tables[0].Rows.Count == 0)
                            {
                                //AddFeeOfferConfig(ref cmd, Temps);
                            }
                            else
                            {
                                msgt.outNoticemsg = "{報價規則已存在不新增}," + msgt.outNoticemsg;
                                Temps["config_id"] = ds_IsExistsFeeOfferConfig.Tables[0].Rows[0]["config_id"].ToString();
                            }
                            OutmsgLIst.Add(msgt);
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(errmsg)) // 有錯誤訊息即跳過
                            {
                                msgt.code = 1;
                                continue;
                            }

                            if (ds_IsExistsFeeWorkItem.Tables[0].Rows.Count == 0)
                                AddFeeWorkItem(ref cmd, ref Temps);
                            else
                            {
                                Temps["seq"] = ds_IsExistsFeeWorkItem.Tables[0].Rows[0]["seq"].ToString();
                                Temps["fee_type_id"] = ds_IsExistsFeeWorkItem.Tables[0].Rows[0]["fee_type_id"].ToString();
                            }

                            DataSet ds_IsExistsFeeOfferConfig = IsExistsFeeOfferConfig(ref cmd, Temps);
                            if (ds_IsExistsFeeOfferConfig.Tables[0].Rows.Count == 0)
                                AddFeeOfferConfig(ref cmd, Temps);
                            else
                                Temps["config_id"] = ds_IsExistsFeeOfferConfig.Tables[0].Rows[0]["config_id"].ToString();
                            AddFeeOfferConfigInfo(ref cmd, Temps);

                        }
                    }
                }
            }
            catch (Exception e)
            {
                errmsg += "\n" + "Exception:" + e.StackTrace;
                msgt = new Message() { outCustmsg = "Error", outErrmsg = errmsg, outNoticemsg = "" };
                OutmsgLIst.Clear();
                OutmsgLIst.Add(msgt);
            }
            return OutmsgLIst;
        }
        private String AppendMsg(ref String sourceMsg, String title, String msg)
        {
            return sourceMsg = sourceMsg + "<br>" + title + "<br>" + msg + "<br>";
        }
        #region Test Func

        //private void ReadExcelSTreamTest(Stream st)
        //{
        //    String connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        //    SqlConnection Conn = new SqlConnection(connString);
        //    String errmsg = "";
        //    DataSet ds;
        //    List<String> errmsgs = new List<string>();
        //    String AppPath = AppDomain.CurrentDomain.BaseDirectory;


        //    string file = Path.Combine(AppPath, "臺灣銀行股份有限公司.xls");
        //    if (File.Exists(file))
        //    {
        //        var extension = Path.GetExtension(file).ToLower();
        //        using (var stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        //        //using (var stream = st)
        //        {
        //            string strSQL = ""; Boolean InsertSuccess = false;

        //            //判斷格式套用讀取方法
        //            IExcelDataReader reader = null;
        //            if (extension == ".xls")
        //            {
        //                Console.WriteLine(" => XLS格式");
        //                reader = ExcelReaderFactory.CreateBinaryReader(stream, new ExcelReaderConfiguration()
        //                {
        //                    FallbackEncoding = Encoding.GetEncoding("UTF-8")
        //                });
        //            }
        //            try
        //            {
        //                using (reader)
        //                {
        //                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
        //                    {

        //                        UseColumnDataType = false,
        //                        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
        //                        {
        //                            //設定讀取資料時是否忽略標題
        //                            UseHeaderRow = false
        //                        }
        //                    });


        //                    //把 DataSet 顯示出來
        //                    var table = ds.Tables[0];
        //                    SqlCommand cmd = new SqlCommand("", Conn);
        //                    //DataSet dst = GetMetaData(cmd);
        //                    Conn.Open();
        //                    SqlDataReader dr;
        //                    DataRow drh = table.Rows[0];
        //                    for (int i = 0; i < drh.ItemArray.Length; i++)
        //                        table.Columns[i].ColumnName = ToTraditional(drh[i].ToString());

        //                    Dictionary<String, String> ids = new Dictionary<String, String>(), Temps = new Dictionary<String, String>();
        //                    string str = string.Format("SELECT case_type_id,case_type_zh_tw FROM dbo.i_case_type; SELECT apply_type_id,apply_type_zh_tw FROM dbo.i_apply_type;" +
        //                        "SELECT ctrl_proc_id,ctrl_proc_zh_tw FROM dbo.i_ctrl_proc where  is_enabled=1 and is_config=1;" +
        //                        "SELECT business_type_id,business_type_zh_tw FROM dbo.i_business_type where isnull(business_type_zh_tw,'') <> ''; SELECT fc_cate_id,text_zh_tw FROM dbo.i_fc_category where is_enabled=1; " +
        //                        "select customer_id,customer_name_en,customer_name  from c_customer a where  is_enabled=1 and isnull(customer_name,'') <> '';SELECT case_status_id,case_status_zh_tw FROM i_case_status;", new object[0]);
        //                    cmd.CommandText = str;

        //                    DataSet MetaData = GetSqlToDs(cmd);
        //                    DataRow drTemp, drRow;
        //                    for (int row = 1; row < table.Rows.Count; row++)
        //                    {
        //                        ids.Clear(); Temps.Clear();
        //                        drRow = table.Rows[row];
        //                        drTemp = MetaData.Tables[0].AsEnumerable().Where(p => p.Field<String>("case_type_zh_tw") == drRow["案件類型"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("case_type_id", drTemp["case_type_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("case_type_id", "");// errmsg += "無該案件類型:" + drRow["案件類型"].ToString() + ",";
        //                        }

        //                        drTemp = MetaData.Tables[1].AsEnumerable().Where(p => p.Field<String>("apply_type_zh_tw") == drRow["申請類型"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("apply_type_id", drTemp["apply_type_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("apply_type_id", ""); //errmsg += "無該申請類型:" + drRow["申請類型"].ToString() + ",";
        //                        }

        //                        drTemp = MetaData.Tables[2].AsEnumerable().Where(p => p.Field<String>("ctrl_proc_zh_tw") == drRow["處理事項"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("ctrl_proc_id", drTemp["ctrl_proc_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("ctrl_proc_id", ""); //errmsg += "無該處理事項:" + drRow["處理事項"].ToString() + ",";
        //                        }

        //                        drTemp = MetaData.Tables[3].AsEnumerable().Where(p => p.Field<String>("business_type_zh_tw") == drRow["業務類型"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("business_type_id", drTemp["business_type_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("business_type_id", ""); //errmsg += "無該業務類型:" + drRow["業務類型"].ToString() + ",";
        //                        }

        //                        drTemp = MetaData.Tables[4].AsEnumerable().Where(p => p.Field<String>("text_zh_tw") == drRow["程序名稱"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("fc_cate_id", drTemp["fc_cate_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("fc_cate_id", ""); //errmsg += "無該程序名稱:" + drRow["程序名稱"].ToString() + ",";
        //                        }

        //                        drTemp = MetaData.Tables[5].AsEnumerable().Where(p => p.Field<String>("customer_name") == drRow["客戶名稱(中)"].ToString() || p.Field<String>("customer_name_en") == drRow["客戶名稱(英)"].ToString() ).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("customer_id", drTemp["customer_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("customer_id", ""); errmsg += "無該客戶名稱:" + drRow["客戶名稱(中)"].ToString() + ",";
        //                        }
        //                        drTemp = MetaData.Tables[6].AsEnumerable().Where(p => p.Field<String>("case_status_zh_tw") == drRow["案件階段"].ToString()).FirstOrDefault();
        //                        if (drTemp != null) ids.Add("case_status_id", drTemp["case_status_id"].ToString());
        //                        else
        //                        {
        //                            ids.Add("case_status_id", ""); //errmsg += "無該案件階段:" + drRow["案件階段"].ToString() + ",";
        //                        }
        //                        if (String.IsNullOrEmpty(ids["customer_id"])) continue;// 該行無識別客戶即跳過

        //                        Temps.Add("case_type_id", ids["case_type_id"]);
        //                        Temps.Add("business_type_id", ids["business_type_id"]);
        //                        Temps.Add("ctrl_proc_id", ids["ctrl_proc_id"]);
        //                        Temps.Add("fc_cate_id", ids["fc_cate_id"]);
        //                        Temps.Add("apply_type_id", ids["apply_type_id"]);
        //                        Temps.Add("customer_id", ids["customer_id"]);
        //                        Temps.Add("case_status_id", ids["case_status_id"]);
        //                        Temps.Add("fee_type_zh_tw", drRow["工作項目(繁體)"].ToString());
        //                        Temps.Add("update_user_id", u_user_id);

        //                        String config_id = Guid.NewGuid().ToString().ToUpper(), fee_type_id = Guid.NewGuid().ToString().ToUpper();
        //                        Temps.Add("config_id", config_id);
        //                        Temps.Add("fee_type_id", fee_type_id);
        //                        Temps.Add("fee_cate_workitem", Get_fee_cate_workitem(drRow["科目"].ToString()));
        //                        Temps.Add("country_id", drRow["國家(地區)"].ToString());
        //                        Temps.Add("zh_tw", drRow["工作項目(繁體)"].ToString());
        //                        Temps.Add("en_us", drRow["工作項目(英文)"].ToString());
        //                        Temps.Add("amount", drRow["未稅金額"].ToString() == "" ? "0" : drRow["未稅金額"].ToString());
        //                        Temps.Add("currency_id", drRow["幣別"].ToString());
        //                        Temps.Add("basic_fee_small", drRow["官費未稅金額\n(小實體)"].ToString() == "" ? "0" : drRow["官費未稅金額\n(小實體)"].ToString());
        //                        Temps.Add("basic_fee_micro", drRow["官費未稅金額\n(微實體)"].ToString() == "" ? "0" : drRow["官費未稅金額\n(微實體)"].ToString());
        //                        Temps.Add("fee_from", drRow["最小金額\n(區間值)"].ToString() == "" ? "0" : drRow["最小金額\n(區間值)"].ToString());
        //                        Temps.Add("fee_to", drRow["最大金額\n(區間值)"].ToString() == "" ? "0" : drRow["最大金額\n(區間值)"].ToString());
        //                        Temps.Add("is_item_fee", drRow["是否按項收費"].ToString());
        //                        Temps.Add("item_exceed", drRow["基礎項數"].ToString() == "" ? "0" : drRow["基礎項數"].ToString());
        //                        Temps.Add("item_exceed_unit", drRow["計費項名稱"].ToString());
        //                        Temps.Add("item_fee", drRow["每項費用"].ToString() == "" ? "0" : drRow["每項費用"].ToString());
        //                        Temps.Add("item_fee_small", drRow["每項費用(小實體)"].ToString() == "" ? "0" : drRow["每項費用(小實體)"].ToString());
        //                        Temps.Add("item_fee_micro", drRow["每項費用(微實體)"].ToString() == "" ? "0" : drRow["每項費用(微實體)"].ToString());
        //                        Temps.Add("item_unit", drRow["計費項單位"].ToString());
        //                        Temps.Add("is_shibao", drRow["實報實銷"].ToString());
        //                        Temps.Add("is_gean", drRow["個案報價"].ToString());
        //                        Temps.Add("tax_id_name", Get_Tax_id(drRow["稅別"].ToString()));
        //                        Temps.Add("tax_type_name", Get_tax_type(drRow["內含/外加"].ToString()));
        //                        Temps.Add("config_type_name", drRow["費用帶出時間"].ToString());

        //                        DataSet ds_IsExistsFeeWorkItem = IsExistsFeeWorkItem(ref cmd, Temps);
        //                        if (ds_IsExistsFeeWorkItem.Tables[0].Rows.Count == 0)
        //                        {
        //                            AddFeeWorkItem(ref cmd, ref Temps);
        //                        }
        //                        else
        //                        {
        //                            Temps["seq"] = ds_IsExistsFeeWorkItem.Tables[0].Rows[0]["seq"].ToString();
        //                        }
        //                        DataSet ds_IsExistsFeeOfferConfig = IsExistsFeeOfferConfig(ref cmd, Temps);
        //                        if (ds_IsExistsFeeOfferConfig.Tables[0].Rows.Count == 0)
        //                        {
        //                            InsertSuccess = AddFeeOfferConfig(ref cmd, Temps);
        //                        }
        //                        else
        //                        {
        //                            Temps["config_id"] = ds_IsExistsFeeOfferConfig.Tables[0].Rows[0]["config_id"].ToString();
        //                        }
        //                        AddFeeOfferConfigInfo(ref cmd, Temps);
        //                        errmsg += errmsg + "\n"; ;
        //                    }
        //                }
        //            }
        //            catch (Exception e)
        //            {
        //                errmsg += "\n" + "Exception:" + e.StackTrace;
        //            }
        //        }
        //        Conn.Close();
        //    }
        //}
        #endregion
        private DataSet IsExistsFeeWorkItem(ref SqlCommand cmd, Dictionary<string, string> item)
        {
            cmd.CommandText = @" SELECT fee_type_id,seq FROM i_fee_type WHERE is_work_item=1 AND case_type_id=@case_type_id
                AND apply_type_id=@apply_type_id AND business_type_id=@business_type_id        
                AND fee_cate_workitem=@fee_cate_workitem AND country_id=@country_id AND fc_cate_id=@fc_cate_id      
                AND (fee_type_zh_tw=dbo.f_GB2BIG(@fee_type_zh_tw,1)) ";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString() == "" ? "ALL" : item["case_type_id"].ToString());
            cmd.Parameters.AddWithValue("@business_type_id", item["business_type_id"].ToString() == "" ? "ALL" : item["business_type_id"].ToString());
            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString() == "" ? "ALL" : item["country_id"].ToString());
            cmd.Parameters.AddWithValue("@fc_cate_id", item["fc_cate_id"].ToString());
            cmd.Parameters.AddWithValue("@fee_type_zh_tw", item["fee_type_zh_tw"].ToString());
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString() == "" ? "ALL" : item["apply_type_id"].ToString());
            cmd.Parameters.AddWithValue("@fee_cate_workitem", item["fee_cate_workitem"].ToString());
            DataSet ds = GetSqlToDs(cmd);
            return ds;
        }
        private DataSet IsExistsFeeOfferConfig(ref SqlCommand cmd, Dictionary<string, string> item)
        {
            cmd.CommandText = @" SELECT config_id, case_type_id,apply_type_id,business_type_id, country_id, customer_id FROM dbo.s_fee_offer_config 
                WHERE case_type_id=@case_type_id AND apply_type_id=@apply_type_id AND business_type_id=@business_type_id
                AND customer_id=@customer_id AND country_id=@country_id ";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString());
            cmd.Parameters.AddWithValue("@business_type_id", item["business_type_id"].ToString());
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString());
            cmd.Parameters.AddWithValue("@customer_id", item["customer_id"].ToString());
            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString());
            DataSet ds = GetSqlToDs(cmd);

            return ds;
        }


        private void UpdateAndInsertFeeWorkItem(ref SqlCommand cmd, ref Dictionary<string, string> item)
        {
            cmd.CommandText = @"DECLARE @seq Int;    
                    SELECT @seq = ISNULL(MAX(seq),0)+1 FROM dbo.i_fee_type WHERE is_work_item=1;
                    MERGE INTO i_fee_type a
                   USING(select @case_type_id as case_type_id, @business_type_id as business_type_id,@country_id as country_id, @apply_type_id as apply_type_id, @fc_cate_id as fc_cate_id
                   ,@fee_type_zh_tw as fee_type_zh_tw, @fee_cate_workitem as fee_cate_workitem) b
                     ON  a.case_type_id = b.case_type_id and a.business_type_id = b.business_type_id and a.country_id = b.country_id and a.apply_type_id = b.apply_type_id
                   and a.fc_cate_id = b.fc_cate_id  and a.fee_type_zh_tw = b.fee_type_zh_tw   and a.fee_cate_workitem = b.fee_cate_workitem and a.is_work_item=1
                   WHEN MATCHED THEN fee_from=@fee_from,fee_to=@fee_to,currency_id=@currency_id,basic_fee_small=@basic_fee_small,basic_fee_micro=@basic_fee_micro,item_exceed=@item_exceed
                    ,item_exceed_unit=@item_exceed_unit,item_fee=@item_fee,item_fee_small=@item_fee_small,item_fee_micro=@item_fee_micro,item_unit=@item_unit
                    ,ctrl_proc_id=@ctrl_proc_id,remark = 'testupdate'
                    ,update_user_id=@update_user_id,update_time = getdate()
                   WHEN NOT MATCHED BY TARGET THEN 
                 INSERT INTO dbo.i_fee_type  
                 (fee_type_id,case_type_id,business_type_id,country_id,apply_type_id, 
                fee_type_zh_cn,fee_type_en_us,fee_type_zh_tw,currency_id, 
                basic_fee,basic_fee_small,basic_fee_micro,fee_from,fee_to,is_item_fee,item_exceed,item_exceed_unit,item_fee,item_fee_small,item_fee_micro,item_unit,is_shibao, 
                is_gean,seq,fc_cate_id,fee_cate_workitem,update_user_id,update_time,fee_class,is_work_item,is_enabled,tmp_ctrl_proc_id) OUTPUT Inserted.seq 
                VALUES(@fee_type_id,@case_type_id,@business_type_id,@country_id,@apply_type_id,dbo.f_GB2BIG(@fee_type_zh_tw,0),@en_us,@zh_tw,@currency_id, 
                 @amount,@basic_fee_small,@basic_fee_micro,@fee_from,@fee_to,@is_item_fee,@item_exceed,@item_exceed_unit,@item_fee,@item_fee_small,@item_fee_micro,@item_unit,@is_shibao, 
                @is_gean,@seq,@fc_cate_id,@fee_cate_workitem,@update_user_id,GETDATE(),'A',1,1,@ctrl_proc_id);
                    ";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@fee_type_id", item["fee_type_id"].ToString());
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString() == "" ? "ALL" : item["case_type_id"]);
            cmd.Parameters.AddWithValue("@ctrl_proc_id", item["ctrl_proc_id"].ToString());
            cmd.Parameters.AddWithValue("@business_type_id", item["business_type_id"].ToString() == "" ? "ALL" : item["business_type_id"]);
            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString() == "" ? "ALL" : item["country_id"]);
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString() == "" ? "ALL" : item["apply_type_id"]);
            cmd.Parameters.AddWithValue("@fee_type_zh_tw", item["zh_tw"].ToString());
            cmd.Parameters.AddWithValue("@en_us", item["en_us"].ToString());
            cmd.Parameters.AddWithValue("@currency_id", item["currency_id"].ToString());
            cmd.Parameters.AddWithValue("@basic_fee_small", Convert.ToDouble(item["basic_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@basic_fee_micro", Convert.ToDouble(item["basic_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@fee_from", Convert.ToDouble(item["fee_from"].ToString()));
            cmd.Parameters.AddWithValue("@fee_to", Convert.ToDouble(item["fee_to"].ToString()));
            cmd.Parameters.AddWithValue("@amount", Convert.ToDouble(item["amount"].ToString()));
            cmd.Parameters.AddWithValue("@item_exceed", item["item_exceed"].ToString());
            cmd.Parameters.AddWithValue("@item_exceed_unit", item["item_exceed_unit"].ToString());
            cmd.Parameters.AddWithValue("@item_fee", Convert.ToDouble(item["item_fee"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_small", Convert.ToDouble(item["item_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_micro", Convert.ToDouble(item["item_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@item_unit", item["item_unit"].ToString());
            cmd.Parameters.AddWithValue("@is_shibao", item["is_shibao"].IsEquivalent("是") || item["is_shibao"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"].IsEquivalent("是") || item["is_gean"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"].IsEquivalent("是") || item["is_item_fee"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@fc_cate_id", item["fc_cate_id"].ToString());
            cmd.Parameters.AddWithValue("@fee_cate_workitem", item["fee_cate_workitem"].ToString());
            cmd.Parameters.AddWithValue("@update_user_id", item["update_user_id"].ToString());

            DataTable dt = InsertSqlToDr(cmd);
            item["seq"] = dt.Rows[0]["seq"].ToString();
            item["fee_type_id"] = dt.Rows[0]["fee_type_id"].ToString();

        }
        private void AddFeeWorkItem(ref SqlCommand cmd, ref Dictionary<string, string> item)
        {
            cmd.CommandText = "DECLARE @seq Int;               " +
                " SELECT @seq = ISNULL(MAX(seq),0)+1 FROM dbo.i_fee_type WHERE is_work_item=1;                " +
                "INSERT INTO dbo.i_fee_type                       " +
                " (fee_type_id,case_type_id,business_type_id,country_id,apply_type_id,                         " +
                "fee_type_zh_cn,fee_type_en_us,fee_type_zh_tw,currency_id,                         " +
                "basic_fee,basic_fee_small,basic_fee_micro,fee_from,fee_to,is_item_fee,item_exceed,item_exceed_unit,item_fee,item_fee_small,item_fee_micro,item_unit,is_shibao,  " +
                "is_gean,seq,fc_cate_id,fee_cate_workitem,update_user_id,update_time,fee_class,is_work_item,is_enabled,tmp_ctrl_proc_id) OUTPUT Inserted.seq " +
                "VALUES(@fee_type_id,@case_type_id,@business_type_id,@country_id,@apply_type_id,dbo.f_GB2BIG(@zh_tw,0),@en_us,@zh_tw,@currency_id," +
                " @amount,@basic_fee_small,@basic_fee_micro,@fee_from,@fee_to,@is_item_fee,@item_exceed,@item_exceed_unit,@item_fee,@item_fee_small,@item_fee_micro,@item_unit,@is_shibao,  " +
                "@is_gean,@seq,@fc_cate_id,@fee_cate_workitem,@update_user_id,GETDATE(),'A',1,1,@ctrl_proc_id);                    ";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@fee_type_id", item["fee_type_id"].ToString());
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString() == "" ? "ALL" : item["case_type_id"]);
            cmd.Parameters.AddWithValue("@ctrl_proc_id", item["ctrl_proc_id"].ToString());
            cmd.Parameters.AddWithValue("@business_type_id", item["business_type_id"].ToString() == "" ? "ALL" : item["business_type_id"]);
            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString() == "" ? "ALL" : item["country_id"]);
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString() == "" ? "ALL" : item["apply_type_id"]);
            cmd.Parameters.AddWithValue("@zh_tw", item["zh_tw"].ToString());
            cmd.Parameters.AddWithValue("@en_us", item["en_us"].ToString());
            cmd.Parameters.AddWithValue("@currency_id", item["currency_id"].ToString());
            cmd.Parameters.AddWithValue("@basic_fee_small", Convert.ToDouble(item["basic_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@basic_fee_micro", Convert.ToDouble(item["basic_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@fee_from", Convert.ToDouble(item["fee_from"].ToString()));
            cmd.Parameters.AddWithValue("@fee_to", Convert.ToDouble(item["fee_to"].ToString()));
            cmd.Parameters.AddWithValue("@amount", Convert.ToDouble(item["amount"].ToString()));
            cmd.Parameters.AddWithValue("@item_exceed", item["item_exceed"].ToString());
            cmd.Parameters.AddWithValue("@item_exceed_unit", item["item_exceed_unit"].ToString());
            cmd.Parameters.AddWithValue("@item_fee", Convert.ToDouble(item["item_fee"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_small", Convert.ToDouble(item["item_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_micro", Convert.ToDouble(item["item_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@item_unit", item["item_unit"].ToString());
            cmd.Parameters.AddWithValue("@is_shibao", item["is_shibao"].IsEquivalent("是") || item["is_shibao"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"].IsEquivalent("是") || item["is_gean"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"].IsEquivalent("是") || item["is_item_fee"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@fc_cate_id", item["fc_cate_id"].ToString());
            cmd.Parameters.AddWithValue("@fee_cate_workitem", item["fee_cate_workitem"].ToString());
            cmd.Parameters.AddWithValue("@update_user_id", item["update_user_id"].ToString());

            DataTable dt = InsertSqlToDr(cmd);
            item["seq"] = dt.Rows[0]["seq"].ToString();

        }

        private Boolean AddFeeOfferConfig(ref SqlCommand cmd, Dictionary<string, string> item)
        {
            cmd.CommandText = @"INSERT INTO dbo.s_fee_offer_config(config_id,case_type_id,business_type_id,country_id,apply_type_id,customer_id,remarks,update_user_id,update_time,is_enabled)  OUTPUT Inserted.config_id " +
                "               VALUES(@config_id,@case_type_id,@business_type_id,@country_id,@apply_type_id,@customer_id,@remarks,@update_user_id,GETDATE(),1);                    ";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@config_id", item["config_id"].ToString());
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString());
            cmd.Parameters.AddWithValue("@business_type_id", item["business_type_id"].ToString());
            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString());
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString());
            cmd.Parameters.AddWithValue("@fc_cate_id", item["fc_cate_id"].ToString());
            cmd.Parameters.AddWithValue("@customer_id", item["customer_id"].ToString());
            cmd.Parameters.AddWithValue("@update_user_id", item["update_user_id"].ToString());


            cmd.Parameters.AddWithValue("@remarks", "系統導入");

            DataTable dt = InsertSqlToDr(cmd);
            return dt.Rows.Count > 0;

        }
        private Boolean AddFeeOfferConfigInfo(ref SqlCommand cmd, Dictionary<string, string> item)
        {
            cmd.CommandText = @"INSERT INTO dbo.s_fee_offer_config_info
                (config_info_id,config_id,ctrl_proc_id,fee_type_id,config_type,amount,currency_id,tax_id,tax_type,seq, fee_from, fee_to,item_fee_small,item_fee_micro, item_exceed_unit,item_unit,is_shibao,is_gean,is_item_fee,item_exceed,item_fee,review_stage,is_export,remark1)  OUTPUT Inserted.config_info_id  
                VALUES(NEWID(),@config_id,@ctrl_proc_id,@fee_type_id,@config_type,@amount,@currency_id,@tax_id,@tax_type,@seq,@fee_from, @fee_to,@item_fee_small,@item_fee_micro, @item_exceed_unit,@item_unit,@is_shibao,@is_gean,@is_item_fee,@item_exceed,@item_fee,@review_stage,@is_export,@remark1); ";


            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@config_id", item["config_id"].ToString());
            cmd.Parameters.AddWithValue("@ctrl_proc_id", item["ctrl_proc_id"].ToString());
            cmd.Parameters.AddWithValue("@case_type_id", item["case_type_id"].ToString());
            cmd.Parameters.AddWithValue("@apply_type_id", item["apply_type_id"].ToString());
            cmd.Parameters.AddWithValue("@fee_type_id", item["fee_type_id"].ToString());
            cmd.Parameters.AddWithValue("@config_type", "AD");
            cmd.Parameters.AddWithValue("@tax_id", item["tax_id_name"].ToString());
            cmd.Parameters.AddWithValue("@tax_type", item["tax_type_name"].ToString());

            cmd.Parameters.AddWithValue("@country_id", item["country_id"].ToString());
            cmd.Parameters.AddWithValue("@zh_tw", item["zh_tw"].ToString());
            cmd.Parameters.AddWithValue("@en_us", item["en_us"].ToString());
            cmd.Parameters.AddWithValue("@currency_id", item["currency_id"].ToString());
            cmd.Parameters.AddWithValue("@basic_fee_small", Convert.ToDouble(item["basic_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@basic_fee_micro", Convert.ToDouble(item["basic_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@fee_from", Convert.ToDouble(item["fee_from"].ToString()));
            cmd.Parameters.AddWithValue("@fee_to", Convert.ToDouble(item["fee_to"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_small", Convert.ToDouble(item["item_fee_small"].ToString()));
            cmd.Parameters.AddWithValue("@item_fee_micro", Convert.ToDouble(item["item_fee_micro"].ToString()));
            cmd.Parameters.AddWithValue("@amount", Convert.ToDouble(item["amount"].ToString()));
            cmd.Parameters.AddWithValue("@item_exceed", item["item_exceed"].ToString());
            cmd.Parameters.AddWithValue("@item_exceed_unit", item["item_exceed_unit"].ToString());
            cmd.Parameters.AddWithValue("@item_fee", Convert.ToDouble(item["item_fee"].ToString()));
            cmd.Parameters.AddWithValue("@item_unit", item["item_unit"].ToString());
            cmd.Parameters.AddWithValue("@is_shibao", item["is_shibao"].IsEquivalent("是") || item["is_shibao"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"].IsEquivalent("是") || item["is_gean"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"].IsEquivalent("是") || item["is_item_fee"].IsEquivalent("Y") ? 1 : 0);
            cmd.Parameters.AddWithValue("@review_stage", item["case_status_id"].ToString());

            cmd.Parameters.AddWithValue("@is_export", 1);
            cmd.Parameters.AddWithValue("@fc_cate_id", item["fc_cate_id"].ToString());
            cmd.Parameters.AddWithValue("@seq", item["seq"].ToString());
            //cmd.Parameters.AddWithValue("@remark1", "系統導入");
            cmd.Parameters.AddWithValue("@remark1", "soltest");

            DataTable dt = InsertSqlToDr(cmd);
            return dt.Rows.Count > 0;
        }
        private void DeleteFeeOfferConfig(ref SqlCommand cmd, Dictionary<string, string> item)
        {
            cmd.CommandText = " DELETE dbo.s_fee_offer_config WHERE config_id=@config_id;";

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@config_id", item["config_id"].ToString());
            cmd.ExecuteNonQuery();
        }


        private DataSet GetMetaData(ref SqlCommand cmd)
        {
            string str = @"SELECT case_type_id,case_type_zh_tw FROM dbo.i_case_type; SELECT apply_type_id,apply_type_zh_tw FROM dbo.i_apply_type
                            SELECT ctrl_proc_id,case_type_id,ctrl_proc_zh_tw FROM dbo.i_ctrl_proc where  is_enabled=1;
                            SELECT business_type_id,business_type_zh_tw FROM dbo.i_business_type where isnull(business_type_zh_tw,'') <> ''; SELECT fc_cate_id,text_zh_tw FROM dbo.i_fc_category where is_enabled=1; 
                            select customer_id,customer_name_en,customer_name  from c_customer a where  is_enabled=1 and isnull(customer_name,'') <> '';SELECT case_status_id,case_status_zh_tw FROM i_case_status;
                            SELECT dictionary_id,value,text_zh_tw FROM dbo.s_dictionary WHERE dictionary_name = 'fee_cate_workitem';  SELECT dictionary_id,value,text_zh_tw  FROM dbo.s_dictionary WHERE dictionary_name ='fo_tax_rate_name';
                            select currency_id,currency_name_zh_tw from i_currency a;select country_id,country_zh_tw from i_country a";
            cmd.CommandText = str;
            DataSet MetaData = GetSqlToDs(cmd);
            return MetaData;
        }
        private DataSet GetSqlToDs(SqlCommand cmd)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            da.Fill(ds);
            return ds;
        }
        private DataTable InsertSqlToDr(SqlCommand cmd)
        {

            DataTable dt = new DataTable();
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            dt.Load(cmd.ExecuteReader());
            cmd.Connection.Close();
            return dt;
        }
        //public String Get_fee_cate_workitem(String FeeName)
        //{
        //    String Rvalue = "";
        //    switch (FeeName)
        //    {
        //        case "官費":
        //            Rvalue = "O"; break;
        //        case "本所服務費":
        //            Rvalue = "A"; break;
        //        case "複代理人服務費":
        //            Rvalue = "T"; break;
        //        default:
        //            break;
        //    }
        //    return Rvalue;
        //}
        public String Get_tax_type(String Name)
        {
            String Rvalue = "";
            switch (Name)
            {
                case "内含":
                    Rvalue = "IN"; break;
                case "外加":
                    Rvalue = "OUT"; break;
                default:
                    break;
            }
            return Rvalue;
        }
        //public String Get_Tax_id(String Name)
        //{
        //    String Rvalue = "";
        //    switch (Name)
        //    {
        //        case "執行業務所得":
        //            Rvalue = "TaxT01"; break;
        //        case "營業稅":
        //            Rvalue = "TaxT02"; break;
        //        default:
        //            break;
        //    }
        //    return Rvalue;
        //}


        #region 簡轉繁
        internal const int LOCALE_SYSTEM_DEFAULT = 0x0800;
        internal const int LCMAP_SIMPLIFIED_CHINESE = 0x02000000;
        internal const int LCMAP_TRADITIONAL_CHINESE = 0x04000000;

        [DllImport("kernel32", CharSet = CharSet.Auto, SetLastError = true)]
        internal static extern int LCMapString(int Locale, int dwMapFlags, string lpSrcStr, int cchSrc, [Out] string lpDestStr, int cchDest);
        public static string ToSimplified(string pSource)
        {
            String tTarget = new String(' ', pSource.Length);
            int tReturn = LCMapString(LOCALE_SYSTEM_DEFAULT, LCMAP_SIMPLIFIED_CHINESE, pSource, pSource.Length, tTarget, pSource.Length);
            return tTarget;
        }
        public static string ToTraditional(string pSource)
        {
            String tTarget = new String(' ', pSource.Length);
            int tReturn = LCMapString(LOCALE_SYSTEM_DEFAULT, LCMAP_TRADITIONAL_CHINESE, pSource, pSource.Length, tTarget, pSource.Length);
            return tTarget;
        }
        #endregion
    }
}
