using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web;
using System.Web.Http;
using WebAPI.Models;
using static WebAPI.Models.CommonModel;
using static WebAPI.Models.CurHisModel;

namespace WebAPI.Controllers
{

    public class Cur_hisController : ApiController
    {

        //Message Outmsg;
        MessageList Outmsg;

        public Cur_hisController()
        {
            Outmsg = new MessageList();

        }
        #region 核稿歷史
        /// <summary>
        /// 取得歷史明細
        /// </summary>
        /// <param name="Get_cur_his_id"></param>
        /// <returns></returns>
        [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)
                    {
                        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();
        }

        /// <summary>
        /// 下載歷史明細EXCEL
        /// </summary>
        /// <param name="trids"></param>
        /// <returns></returns>
        [Route("Download_cur_hiss_Excel")]
        [HttpPost]
        [HttpGet]
        public HttpResponse Download_cur_hiss_Excel(String trids)
        {
            String eflowids = trids;
            MessageList msg = Get_cur_hiss_for_Excel(trids);
            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 = 3, curcell = 1, Rownum = 1;

                    foreach (核稿歷史Excel chis in msg.CurHislist_Excel)
                    {
                        ClosedXML.Excel.IXLCells cells = worksheet.Row(curRow).Cells();
                        curcell = 1;
                        worksheet.Cell(curRow, curcell).Value = Rownum; Rownum++;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.case_volume;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.Charge_date;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.create_time;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.ctrl_proc_zh_cn;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.pic_UserCName;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.revise_curEmpName;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.Reject_Count;


                        worksheet.Range(curRow - 1, curcell, curRow - 1, curcell).InsertColumnsAfter(chis.Submit_audit_time.Count * 2);//插入第N次 遞交 退件欄位
                        for (int i = 0; i < chis.Submit_audit_time.Count; i++)
                        {
                            curcell++;
                            worksheet.Cell(curRow - 1, curcell).Value = "第" + (i + 1) + "次遞交日";
                            worksheet.Cell(curRow, curcell).Value = chis.Submit_audit_time[i];
                            curcell++;
                            worksheet.Cell(curRow - 1, curcell).Value = "第" + (i + 1) + "次退件日";
                            worksheet.Cell(curRow, curcell).Value = chis.Reject_audit_time[i];
                        }
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.int_doc_date;
                        curcell++;
                        worksheet.Cell(curRow, curcell).Value = chis.Audit_day;
                        curRow++;
                    }

                    //worksheet.Columns().AdjustToContents();
                    //worksheet.Rows().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();
                }
            }
            catch (Exception e)
            {
                result.Content = new StringContent(e.Message);
            }
            return response;
        }
        /// <summary>
        /// 取得多個ID時歷史資料
        /// </summary>
        /// <param name="trids"></param>
        /// <returns></returns> 
        public MessageList Get_cur_hiss_for_Excel(String trids)
        {
            String connString = ConfigurationManager.ConnectionStrings["eflow_cur"].ConnectionString;
            HttpRequest Req = HttpContext.Current.Request;
            List<核稿歷史Excel> Hislist = new List<核稿歷史Excel>();
            try
            {

                String[] strids = String.IsNullOrEmpty(Req.Form["trids"]) ? "".Split(',') : Req.Form["trids"].Split(',');
                using (SqlConnection Conn = new SqlConnection(connString))
                {
                    核稿歷史Excel hisT = new 核稿歷史Excel();
                    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);
                        }
                    }

                    #region Sql語句設定
                    String Cte = String.Format(@";with cte as(
				    select a.case_id,a.eflow_id,a.is_submit,a.pic_user_id,a.proc_id,b.status,d.ctrl_proc_zh_tw,d.case_volume,d.case_type_zh_tw,d.proc_status_zh_tw, 
				    Convert(varchar(10) ,d.finish_date, 111) as finish_date,d.first_doc_date,d.int_doc_date,d.create_time,f.charge_date
				    , case when audit_id='reject' then 1 when audit_id='submit' then 2 else 0 end  as IsReject ,c.audit_time 
				    ,e.audit_type_zh_cn,g.pic_UserCName,h.revise_curEmpName,d.ctrl_proc_zh_cn
				    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 
					left join evw_p_case_info f  on d.case_id=f.case_id
					left join evw_p_proc_pic_list g on g.obj_id=d.proc_id
					left join evw_p_revise_user_list h on h.obj_id=d.proc_id 
                    where a.eflow_id IN ({0}) 
					) ", string.Join(",", parameters)),
                    MaxCount = @"select max(count) as  maxCount from
					(
					select count(case when IsReject=1 then 1 else null end) as count,max(audit_time) as Max_audit_time
					,MIN(audit_time) as Min_audit_time,eflow_id
					from cte 
					group by eflow_id  
					) aa
                    ",
                    Result = @"select create_time,eflow_id,count,case_volume,proc_id,first_doc_date,Convert(varchar(10),int_doc_date, 111) as int_doc_date 
					 ,Audit_day,charge_date,pic_UserCName,revise_curEmpName,ctrl_proc_zh_cn
                    @auditString
					from (
					select ROW_NUMBER()   OVER(PARTITION BY cte.eflow_id,cte.IsReject ORDER BY audit_time) as ROWID,cte.create_time,cte.eflow_id, cte2.count,cte.case_volume,cte.proc_id,
					cte.first_doc_date,int_doc_date,DATEDIFF(Day, first_doc_date,int_doc_date) as Audit_day,cte.audit_time,cte.IsReject,charge_date,pic_UserCName,revise_curEmpName,ctrl_proc_zh_cn
                    from cte
                    left join ( select count(case when IsReject=1 then 1 else null end) as count, max(audit_time) as Max_audit_time, MIN(audit_time) as Min_audit_time, eflow_id from cte 
                    group by eflow_id) as cte2 on cte.eflow_id = cte2.eflow_id
					)  aa
                    group by create_time, eflow_id, count, case_volume, proc_id, first_doc_date, int_doc_date, Audit_day, charge_date, pic_UserCName, revise_curEmpName,ctrl_proc_zh_cn"
                    ;

                    #endregion
                    StringBuilder auditString = new StringBuilder(""); int auditCount = 10;
                    cmd.CommandText = Cte + MaxCount;
                    DataSet ds = GetSqlToDs(cmd);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        auditCount = Convert.ToInt16(dr["maxCount"]);
                    }
                    auditCount = 7;//test 動態完成待處理 

                    for (int i = 1; i <= auditCount; i++)
                    {
                        auditString.Append("," + "max(case when ROWID=" + i + " and aa.IsReject=1 then aa.audit_time end) as t" + i);
                        auditString.Append("," + "max(case when ROWID=" + i + " and aa.IsReject=2 then aa.audit_time end) as s" + i);
                    }

                    cmd.CommandText = (Cte + Result).Replace("@auditString", auditString.ToString());
                    ds = GetSqlToDs(cmd);

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        hisT = new 核稿歷史Excel()
                        {
                            case_volume = dr["case_volume"].ToString(),
                            create_time = dr["create_time"].ToString(),
                            Reject_Count = dr["count"].ToString(),
                            proc_id = dr["proc_id"].ToString(),
                            first_doc_date = dr["first_doc_date"].ToString(),
                            int_doc_date = dr["int_doc_date"].ToString(),
                            eflow_id = dr["eflow_id"].ToString(),
                            Charge_date = dr["charge_date"].ToString(),
                            pic_UserCName = dr["pic_UserCName"].ToString(),
                            Audit_day = dr["Audit_day"].ToString(),
                            revise_curEmpName = dr["revise_curEmpName"].ToString(),
                            ctrl_proc_zh_cn = dr["ctrl_proc_zh_cn"].ToString()
                        };
                        try
                        {
                            for (int i = 1; i <= auditCount; i++)
                            {
                                hisT.Reject_audit_time.Add((dr["t" + i] == null ? "" : dr["t" + i].ToString()));
                                hisT.Submit_audit_time.Add((dr["s" + i] == null ? "" : dr["s" + i].ToString()));
                            }
                            Hislist.Add(hisT);
                        }
                        catch
                        {
                        }
                    }

                }
                Outmsg.CurHislist_Excel = Hislist;
                Outmsg.ListCode = 0;
                Outmsg.Listmsg = "上傳成功";
            }
            catch (Exception e)
            {
                Outmsg.ListCode = 1;
                Outmsg.Listmsg = "Exception:" + e.StackTrace;
            }
            return Outmsg;
        }

        /// <summary>
        /// 取得多個ID時歷史資料
        /// </summary>
        /// <param name="trids"></param>
        /// <returns></returns>
        [Route("Get_cur_hiss")]
        [HttpPost]
        [HttpGet]
        //[EnableCors(origins: "*", headers: "*", methods: "*")]
        //public MessageList Get_cur_hiss([FromBody] CurHisParam chp)
        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;
            try
            {

                isCount = (!String.IsNullOrEmpty(Req.Form["isCount"])) ? Boolean.Parse(Req.Form["isCount"]) : false;
                String[] strids = String.IsNullOrEmpty(Req.Form["trids"]) ? "".Split(',') : 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 = "";

                    if (isCount)
                    {
                        fields = @" eflow_id,count(1) as Count,count(case when audit_id='reject' then 1 end) as rejectCount";
                        Groups = @" group by a.eflow_id";
                    }
                    else
                    {
                        Groups = " order by audit_time desc";
                    }
                    cmd.CommandText = String.Format(@"
                    select @fields
                    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 
                    ", string.Join(",", parameters));
                    cmd.CommandText = cmd.CommandText.Replace("@fields", fields).Replace("@Group", Groups);
                    DataSet ds = GetSqlToDs(cmd);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (isCount)
                        {
                            hisT = new 核稿歷史()
                            {
                                eflow_id = dr["eflow_id"].ToString(),
                                Count = Convert.ToInt32(dr["count"])
                            };
                        }
                        else
                        {
                            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;
        }

        [Route("Get_cur_hiss_test")]
        [HttpPost]
        [HttpGet]
        [HttpOptions]
        //[EnableCors(origins: "*", headers: "*", methods: "*")]
        //public MessageList Get_cur_hiss([FromBody] CurHisParam chp)
        public MessageList Get_cur_hiss_test([FromBody] List<CurHisParam> chp)
        {
            String connString = ConfigurationManager.ConnectionStrings["eflow_cur"].ConnectionString;
            HttpRequest Req = HttpContext.Current.Request;
            List<核稿歷史> Hislist = new List<核稿歷史>();
            Boolean isCount = false;
            try
            {

                isCount = (!String.IsNullOrEmpty(Req.Form["isCount"])) ? Boolean.Parse(Req.Form["isCount"]) : false;
                String[] strids = String.IsNullOrEmpty(Req.Form["trids"]) ? "".Split(',') : 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 = "";

                    if (isCount)
                    {
                        fields = @" eflow_id,count(1) as Count";
                        Groups = @" group by a.eflow_id";
                    }
                    else
                    {
                        Groups = " order by audit_time desc";
                    }
                    cmd.CommandText = String.Format(@"
                    select @fields
                    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 
                    ", string.Join(",", parameters));
                    cmd.CommandText = cmd.CommandText.Replace("@fields", fields).Replace("@Group", Groups);
                    DataSet ds = GetSqlToDs(cmd);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (isCount)
                        {
                            hisT = new 核稿歷史()
                            {
                                eflow_id = dr["eflow_id"].ToString(),
                                Count = Convert.ToInt32(dr["count"])
                            };
                        }
                        else
                        {
                            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;
        }
        #endregion

    }
}
