using Antlr.Runtime;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

namespace 練習
{
    public partial class Project23 : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                string unit_type = "u.[name]";
                unit_type = "u.[nickname]";
                cmd.CommandText = @"DECLARE @StartDay int
                SET @StartDay = 1120101
                DECLARE @EndDay int
                SET @EndDay = 1120431

                SELECT u.nickName as '單位',
                 SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and accuse_tp='1' then 1 else 0 end) as '一條(攔停)',
                 SUM(case when (rule2!='' and accuse_tp='1') then 1 else 0 end) as '二條(攔停)',
                 '0' as '三條(攔停)',
                 '0' as '四條(攔停)',
                 SUM(case when (rule2 is NULL or rule2='') and (rule1!='') and accuse_tp='2' then 1 else 0 end) as '一條(逕舉)',
                 SUM(case when (rule2!='') and accuse_tp='2'  then 1 else 0 end) as '二條(逕舉)',
                 '0' as '三條(逕舉)',
                 '0' as '四條(逕舉)',
                 SUM(case when (rule2 is NULL or rule2='') and (rule1!='') and accuse_tp='8' then 1 else 0 end) as '一條(違警)',
                 SUM(case when (rule2!='') and accuse_tp='8' then 1 else 0 end) as '二條(違警)',
                 '0' as '三條(違警)',
                 '0' as '四條(違警)',
                 SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end) as '一條(合計)',
                 SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8')  then 1 else 0 end) as '二條(合計)',
                 '0' as '三條(合計)',
                 '0' as '四條(合計)',
                 (SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) +
                 (SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8')  then 1 else 0 end)) as '總張數',
                 (SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) +
                 ((SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) *2) as '法條數'
                FROM (
                select tkt_no, rule1, rule2, accuse_tp, accuse_no
                from police where TRIM(in_date) >= @StartDay AND TRIM(in_date) <= @EndDay
                 UNION ALL
                select tkt_no, rule1, rule2, accuse_tp, accuse_no
                from 攤販主檔 where TRIM(key_date) >= @StartDay AND TRIM(key_date) <= @EndDay
                ) A
                left join unit u on u.code = A.accuse_no
                group by u.nickName
                UNION ALL
                SELECT '合計' as '單位',
                 SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and accuse_tp='1' then 1 else 0 end) as '一條(攔停)',
                 SUM(case when (rule2!='' and accuse_tp='1') then 1 else 0 end) as '二條(攔停)',
                 '0' as '三條(攔停)',
                 '0' as '四條(攔停)',
                 SUM(case when (rule2 is NULL or rule2='') and (rule1!='') and accuse_tp='2' then 1 else 0 end) as '一條(逕舉)',
                 SUM(case when (rule2!='') and accuse_tp='2'  then 1 else 0 end) as '二條(逕舉)',
                 '0' as '三條(逕舉)',
                 '0' as '四條(逕舉)',
                 SUM(case when (rule2 is NULL or rule2='') and (rule1!='') and accuse_tp='8' then 1 else 0 end) as '一條(違警)',
                 SUM(case when (rule2!='') and accuse_tp='8' then 1 else 0 end) as '二條(違警)',
                 '0' as '三條(違警)',
                 '0' as '四條(違警)',
                 SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end) as '一條(合計)',
                 SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8')  then 1 else 0 end) as '二條(合計)',
                 '0' as '三條(合計)',
                 '0' as '四條(合計)',
                 (SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) +
                 (SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8')  then 1 else 0 end)) as '總張數',
                 (SUM(case when ((rule2 is NULL or rule2='') and (rule1!='')) and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) +
                 ((SUM(case when (rule2!='') and (accuse_tp='1' or accuse_tp='2' or accuse_tp='8') then 1 else 0 end)) *2) as '法條數'
                FROM (
                select tkt_no, rule1, rule2, accuse_tp, accuse_no
                from police where TRIM(in_date) >= @StartDay AND TRIM(in_date) <= @EndDay
                 UNION ALL
                select tkt_no, rule1, rule2, accuse_tp, accuse_no
                from 攤販主檔 where TRIM(key_date) >= @StartDay AND TRIM(key_date) <= @EndDay
                ) A
                left join unit u on u.code = A.accuse_no ";
                cmd.Parameters.AddWithValue("@start_date", "1120101");
                cmd.Parameters.AddWithValue("@end_date", "1120431");
                cmd.Parameters.AddWithValue("@accuse", "%");
                cmd.Parameters.AddWithValue("@type", unit_type);
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, "data1");
                //777
                if (ds.Tables["data1"].Rows.Count > 0)
                {
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    ExcelPackage ep = new ExcelPackage();
                    ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("加強取締重大交通違規");
                    sheet.Cells[1, 1, 1, 19].Value = "9-6苗栗縣警察局111年8月違規案件張數與法條數統計表";
                    sheet.Cells[1, 1, 1, 19].Merge = true;
                    sheet.Cells[1, 1].Style.Font.Size = 15;
                    sheet.Cells[2, 1, 2, 19].Value = "製表日期：" + "111年9月6日";
                    sheet.Cells[2, 1, 2, 19].Merge = true;
                    sheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

                    //標題列  
                    sheet.Cells[3, 1].Value = "單位名稱";
                    sheet.Cells[3, 1, 4, 1].Merge = true;
                    sheet.Cells[3, 2].Value = "攔停張數";
                    sheet.Cells[3, 2, 3, 5].Merge = true;   
                    sheet.Cells[4, 2].Value = "一條";
                    sheet.Cells[4, 3].Value = "二條";
                    sheet.Cells[4, 4].Value = "三條";
                    sheet.Cells[4, 5].Value = "四條";
                    sheet.Cells[3, 6].Value = "逕舉張數";
                    sheet.Cells[3, 6, 3, 9].Merge = true;
                    sheet.Cells[4, 6].Value = "一條";
                    sheet.Cells[4, 7].Value = "二條";
                    sheet.Cells[4, 8].Value = "三條";
                    sheet.Cells[4, 9].Value = "四條";
                    sheet.Cells[3, 10].Value = "違警張數";
                    sheet.Cells[3, 10, 3, 13].Merge = true;
                    sheet.Cells[4, 10].Value = "一條";
                    sheet.Cells[4, 11].Value = "二條";
                    sheet.Cells[4, 12].Value = "三條";
                    sheet.Cells[4, 13].Value = "四條";
                    sheet.Cells[3, 14].Value = "張數合計";
                    sheet.Cells[3, 14, 3, 17].Merge = true;
                    sheet.Cells[4, 14].Value = "一條";
                    sheet.Cells[4, 15].Value = "二條";
                    sheet.Cells[4, 16].Value = "三條";
                    sheet.Cells[4, 17].Value = "四條";
                    sheet.Cells[3, 18].Value = "總張數";
                    sheet.Cells[3, 18, 4, 18].Merge = true;
                    
                    sheet.Cells[3, 19].Value = "法條數";
                    sheet.Cells[3, 19, 4, 19].Merge = true;
                    

                    Dictionary<string, int> unit_title = new Dictionary<string, int>
                    {
                        {"苗栗分局", 5},
                        {"竹南分局", 6},
                        {"頭份分局", 7},
                        {"通霄分局", 8},
                        {"大湖分局", 9},
                        {"保安隊", 10},
                        {"交通隊", 11},
                        {"竹南拖吊場",12},
                        {"苗栗拖吊場",13},
                        {"合計", 14},
                    };

                    foreach (DataRow data_row in ds.Tables["data1"].Rows)
                    {
                        if (unit_title.ContainsKey(data_row["單位"].ToString()))
                        {
                            int row = unit_title[data_row["單位"].ToString()];
                                
                            sheet.Cells[row, 1].Value = data_row["單位"].ToString();
                            sheet.Cells[row, 2].Value = int.Parse(data_row["一條(攔停)"].ToString());
                            sheet.Cells[row, 3].Value = int.Parse(data_row["二條(攔停)"].ToString());
                            sheet.Cells[row, 4].Value = int.Parse(data_row["三條(攔停)"].ToString());
                            sheet.Cells[row, 5].Value = int.Parse(data_row["四條(攔停)"].ToString());
                            sheet.Cells[row, 6].Value = int.Parse(data_row["一條(逕舉)"].ToString());
                            sheet.Cells[row, 7].Value = int.Parse(data_row["二條(逕舉)"].ToString());
                            sheet.Cells[row, 8].Value = int.Parse(data_row["三條(逕舉)"].ToString());
                            sheet.Cells[row, 9].Value = int.Parse(data_row["四條(逕舉)"].ToString());
                            sheet.Cells[row, 10].Value = int.Parse(data_row["一條(違警)"].ToString());
                            sheet.Cells[row, 11].Value = int.Parse(data_row["二條(違警)"].ToString());
                            sheet.Cells[row, 12].Value = int.Parse(data_row["三條(違警)"].ToString());
                            sheet.Cells[row, 13].Value = int.Parse(data_row["四條(違警)"].ToString());
                            sheet.Cells[row, 14].Value = int.Parse(data_row["一條(合計)"].ToString());
                            sheet.Cells[row, 15].Value = int.Parse(data_row["二條(合計)"].ToString());
                            sheet.Cells[row, 16].Value = int.Parse(data_row["三條(合計)"].ToString());
                            sheet.Cells[row, 17].Value = int.Parse(data_row["四條(合計)"].ToString());
                            sheet.Cells[row, 18].Value = int.Parse(data_row["總張數"].ToString());
                            sheet.Cells[row, 19].Value = int.Parse(data_row["法條數"].ToString());

                            row++;
                        }
                    }



                    //寬度設定
                    for (int i = 1; i <= 19; i++)
                    {
                        sheet.Column(i).AutoFit(6);
                    }
                    sheet.Column(1).AutoFit(11);
                    sheet.Column(18).AutoFit(8);
                    sheet.Column(19).AutoFit(8);

                    //高度設定
                    for (int i = 5; i <= 14; i++)
                    {
                        sheet.Row(i).Height = 25;
                    }

                    sheet.Cells[3, 1, 14, 19].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells[3, 1, 14, 19].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells[3, 1, 14, 19].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[3, 1, 14, 19].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[3, 1, 14, 19].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[3, 1, 14, 19].Style.Border.Right.Style = ExcelBorderStyle.Thin;




                    byte[] b = ep.GetAsByteArray();
                    string filename = "加強取締重大交通違規.xlsx";
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                    Response.Charset = "";
                    Response.ContentType = "application/excel";
                    Response.BinaryWrite(b);
                    Response.Flush();
                    Response.End();
                }
            }
        }

    }
}