using ClosedXML.Excel;
using ExcelDataReader;
using Microsoft.International.Formatters;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Http;
using WebAPI.Models;
using static WebAPI.Models.CommonModel;

namespace WebAPI.Controllers
{
    public class FeeOfferConfigController : ApiController
    {
        //Message Outmsg;
        MessageList Outmsg;
        public FeeOfferConfigController()
        {
            if(Outmsg==null)Outmsg = new MessageList();
        }
        private String u_user_id;

    
        #region 報價規則
        [Route("GetFeeOfferConfigList")]
        [HttpPost]

        public MessageList GetFeeOfferConfigList(Boolean ischeck)
        {
            DataSet ds;
            //this.u_user_id = Update_user_id;
            List<Message> mlist = new List<Message>();
            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);
                        }
                    }
                    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 = "上傳成功";
                    }
                }
                else
                {
                    Outmsg.ListCode = 1;
                    Outmsg.Listmsg = "請上傳檔案";
                }
            }
            catch (Exception e)
            {
                Outmsg.ListCode = 1;
                Outmsg.Listmsg = "Exception:" + e.StackTrace;
            }


            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>";
        }

        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"] == "是" || item["is_shibao"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"] == "是" || item["is_gean"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"] == "是" || item["is_item_fee"] == "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"] == "是" || item["is_shibao"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"] == "是" || item["is_gean"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"] == "是" || item["is_item_fee"] == "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"] == "是" || item["is_shibao"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_gean", item["is_gean"] == "是" || item["is_gean"] == "Y" ? 1 : 0);
            cmd.Parameters.AddWithValue("@is_item_fee", item["is_item_fee"] == "是" || item["is_item_fee"] == "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 where isnull(case_status_zh_tw,'') <> '';
                            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;
        }
 
        public String Get_tax_type(String Name)
        {
            String Rvalue = "";
            switch (Name)
            {
                case "内含":
                    Rvalue = "IN"; break;
                case "外加":
                    Rvalue = "OUT"; break;
                default:
                    break;
            }
            return Rvalue;
        } 
        #endregion

    }
}
