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

namespace 練習
{
	public partial class Project5 : 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 @StartKeyDay INT;
					DECLARE @EndKeyDay INT;
					DECLARE @OrderBySprvsnName nchar(200);
					--DECLARE @AccuseNoRoot char;
					SET @StartKeyDay = @start_date;
					SET @EndKeyDay = @end_date;
					SET @OrderBySprvsnName = '苗栗縣警察局苗栗分局,苗栗縣警察局竹南分局,苗栗縣警察局頭份分局,苗栗縣警察局大湖分局, 苗栗縣警察局通霄分局'

					-- 單位
					DECLARE @accuse_no varchar(50)
					SET @accuse_no = @accuse_no_root

					-- 驗證報表用欄位
					DECLARE @ExportDay INT;
					SET @ExportDay = @today

					--Begin Try drop table #VendorBase End Try Begin Catch End Catch

					--SET @AccuseNoRoot = '03B7';
					-- 排序依據 分局(苗栗, 竹南, 頭份, 大湖, 通宵), 違規日期, 違規時間
					SELECT ROW_NUMBER() OVER (ORDER BY CHARINDEX(B.[應到案處所], @OrderBySprvsnName), B.[灌檔年月], B.[繳費日]) AS '序',* 
					INTO #VendorBase FROM
					(
						SELECT 
							m.tkt_no AS '違規單號',
							case when(TRIM(m.driver) <> '')
							then
								driver
							else
								m.[owner]
							end AS '違規人',
							vil_dt AS '違規日期',
							case when( TRIM(rule2) <> '')
							then
								CONVERT(int,dbo.getvendormoney(TRIM(m.rule1), TRIM(m.vil_dt), '1')) + CONVERT(int,dbo.getvendormoney(TRIM(m.rule2), TRIM(m.vil_dt), '1'))
							else
								CONVERT(int,dbo.getvendormoney(TRIM(m.rule1), TRIM(m.vil_dt), '1'))
							end AS '原開單金額',
							case when(TRIM(rule2) <> '')
							then
								rule1 + '，' +rule2
							else
								rule1
							end AS '違反條例',
							--case when(c.tkt_no is not null)
							--then
							--	CONVERT(int,c.[money])
							--else
							--	''
							--end AS '結案金額',
							--case when(c.tkt_no is not null)
							--then
							--	c.closeDate
							--else
							--	''
							--end AS '結案日期',
							vpr.繳費日 AS '繳費日',
							vpr.已繳金額 AS '繳款總金額',
							case when(LEN(irr.irrDate) > 3) 
								then SUBSTRING(irr.irrDate, 1 , len(irr.irrDate) - 2) 
								else 
									'' 
								end AS '灌檔年月',
							REPLACE(sp.[name], '苗栗縣警察局', '') AS '應到案處所',
						--	m.sprvsn_no AS '處所代碼',
							ISNULL(vpr.收據號碼,'') AS '收據號碼',
							ISNULL(c.rsn,'') AS '備註'
						 FROM 
						(
							SELECT * FROM [攤販主檔]
							--UNION ALL
							--SELECT * FROM [攤販主檔100]
						) m
						--	FROM [攤販主檔] m
						LEFT JOIN (SELECT tkt_no, STRING_AGG(TRIM(p.pay_date),', ') WITHIN GROUP (ORDER BY p.pay_date DESC) as '繳費日',STRING_AGG(TRIM(p.recp_no),', ') WITHIN GROUP (ORDER BY p.pay_date DESC) as '收據號碼', SUM(p.money) as '已繳金額'
													FROM 攤販繳費記錄 AS p 
													WHERE [money] not like '-%' AND isdelete='0' AND pay_date >= @StartKeyDay AND pay_date <= @EndKeyDay
													GROUP BY tkt_no) vpr on vpr.tkt_no = m.tkt_no
						LEFT JOIN 攤販已結案檔 c on m.tkt_no=c.tkt_no AND closeDate <= @ExportDay
						LEFT JOIN sprvsn_no sp on sp.code = m.sprvsn_no
						LEFT JOIN (SELECT tkt_no,irrDate FROM irr GROUP BY tkt_no, irrDate) irr on irr.tkt_no = m.tkt_no
						-- 該區間有繳費紀錄 或 該區間 
						WHERE (TRIM(vpr.tkt_no) <> '' OR (c.[money] = '0' AND c.closeDate >= @StartKeyDay AND c.closeDate <= @EndKeyDay AND TRIM(c.rsn) <> '')) AND sp.unit_root like @accuse_no
						--WHERE get_date>=@StartKeyDay AND get_date<=@EndKeyDay 
						--ORDER BY CHARINDEX(sp.[name], @OrderBySprvsnName),m.vil_dt, m.vil_time
					) B


					-- 總表結果
					SELECT  
						序, 違規單號, 違規人, 違規日期, 原開單金額, 違反條例, 繳費日, 繳款總金額, 灌檔年月, 應到案處所, 收據號碼, 備註
					FROM(
						SELECT 序 as 'seq', CONVERT(nchar,序) as '序',違規單號, 違規人, 違規日期, 原開單金額, 違反條例, 
						繳費日,
						case when(繳款總金額 > 0) then CONVERT(nchar,繳款總金額)else '' end as '繳款總金額',
						灌檔年月, 應到案處所, 收據號碼, 備註
						FROM #VendorBase
						UNION ALL
						SELECT COUNT(序) + 1 as 'seq', '合計' as '序','' as '違規單號', '' AS '違規人', '' AS '違規日期', CONVERT(nchar,SUM(原開單金額)) AS '原開單金額', 
						'' AS '違反條例',  '' AS '繳費日', CONVERT(nchar,SUM(繳款總金額)) AS '繳款總金額', '' AS '灌檔年月', '' AS '應到案處所', 
						'' AS '收據號碼', '' AS '備註'
						FROM #VendorBase
					) A
					ORDER BY seq";
				cmd.Parameters.AddWithValue("@start_date", "1120101");
				cmd.Parameters.AddWithValue("@end_date", "1120531");
				cmd.Parameters.AddWithValue("@today", "1120531");
				cmd.Parameters.AddWithValue("@accuse_no_root", "%");
                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, 2, 1, 12].Value = "苗栗縣警察局";
                    sheet.Cells[1, 2, 1, 12].Merge = true;
                    sheet.Cells[2, 2, 2, 12].Value = "行人、慢車、道路障礙案件明細表(繳費日)";
                    sheet.Cells[2, 3, 2, 12].Merge = true;
                    sheet.Cells[3, 2, 3, 12].Value = "統計期間：" + "1120101" + "至" + "1120531";
                    sheet.Cells[3, 3, 3, 12].Merge = true;
                    sheet.Cells[1, 2, 2, 12].Style.Font.Bold = true;
                    sheet.Cells[1, 2, 3, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells[1, 2, 3, 12].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells[2, 1].Value = "列印單位：";
                    sheet.Cells[2, 1, 2, 2].Merge = true;
                    sheet.Cells[3, 1].Value = "列印人員：" + Session["UserName"];
                    sheet.Cells[3, 1,3 ,2].Merge = true;

                    //標題列
                    sheet.Cells[4, 1].Value = "序";
                    sheet.Cells[4, 2].Value = "違規單號";
                    sheet.Cells[4, 3].Value = "違規人";
                    sheet.Cells[4, 4].Value = "違規日期";
                    sheet.Cells[4, 5].Value = "原開單金額";
                    sheet.Cells[4, 6].Value = "違反條例";
                    sheet.Cells[4, 7].Value = "繳費日";
                    sheet.Cells[4, 8].Value = "繳款總金額";
                    sheet.Cells[4, 9].Value = " 灌檔年月";
                    sheet.Cells[4, 10].Value = "應到案處所";
                    sheet.Cells[4, 11].Value = "收據號碼";
                    sheet.Cells[4, 12].Value = "備註";


					for (int row = 0; row < ds.Tables["data1"].Rows.Count; row++)
					{

						sheet.Cells[row + 5, 1].Value = ds.Tables["data1"].Rows[row] ["序"].ToString();
						sheet.Cells[row + 5, 2].Value = ds.Tables["data1"].Rows[row]["違規單號"].ToString();
						sheet.Cells[row + 5, 3].Value = ds.Tables["data1"].Rows[row]["違規人"].ToString();
                        sheet.Cells[row + 5, 3].Style.WrapText = true;
                        sheet.Cells[row + 5, 4].Value = ds.Tables["data1"].Rows[row]["違規日期"].ToString();
						sheet.Cells[row + 5, 5].Value = ds.Tables["data1"].Rows[row]["原開單金額"].ToString();
						sheet.Cells[row + 5, 6].Value = ds.Tables["data1"].Rows[row]["違反條例"].ToString();
                        sheet.Cells[row + 5, 6].Style.WrapText = true;
                        sheet.Cells[row + 5, 7].Value = ds.Tables["data1"].Rows[row]["繳費日"].ToString();
						sheet.Cells[row + 5, 8].Value = ds.Tables["data1"].Rows[row]["繳款總金額"].ToString();
						sheet.Cells[row + 5, 9].Value = ds.Tables["data1"].Rows[row]["灌檔年月"].ToString();
						sheet.Cells[row + 5, 10].Value = ds.Tables["data1"].Rows[row]["應到案處所"].ToString();
						sheet.Cells[row + 5, 11].Value = ds.Tables["data1"].Rows[row]["收據號碼"].ToString();
						sheet.Cells[row + 5, 12].Value = ds.Tables["data1"].Rows[row]["備註"].ToString();
                        sheet.Cells[row + 5, 12].Style.WrapText = true;

                        sheet.Cells[4, 1, row + 5, 12].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                        sheet.Cells[4, 1, row + 5, 12].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        sheet.Cells[4, 1, row + 5, 12].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                        sheet.Cells[4, 1, row + 5, 12].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        sheet.Cells[4, 1, row + 5, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                        sheet.Cells[4, 1, row + 5, 12].Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
                    }
					
					
                    //寬度設定
                    sheet.Column(1).AutoFit(4.7);
                    sheet.Column(2).AutoFit(11);
                    sheet.Column(3).AutoFit(11);
                    sheet.Column(4).AutoFit(9);
                    sheet.Column(5).AutoFit(11);
                    sheet.Column(6).AutoFit(10);
                    sheet.Column(7).AutoFit(8);
                    sheet.Column(8).AutoFit(11);
                    sheet.Column(9).AutoFit(9);
                    sheet.Column(10).AutoFit(11);
                    sheet.Column(11).AutoFit(9);
                    sheet.Column(12).AutoFit(11);

                    sheet.PrinterSettings.RepeatRows = sheet.Cells["4:4"];
                    sheet.PrinterSettings.PaperSize = ePaperSize.A4;
                    sheet.PrinterSettings.Orientation = eOrientation.Landscape;

                    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();
				}
			}
		}
	}
}