using System;
using System.Collections.Generic;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using Wellan.Data;
using Wellan.Common;

/// <summary>
/// 加班費明細 的摘要描述
/// </summary>
public class 加班費明細 : ExcelReportBase
{
    public 加班費明細()
    {
    }

    protected override void Init()
    {
        base.Init();
        titleStyleCenterNoBorder.SetFont(bodyFont);
        bodyStyleCenterNoBorder.SetFont(bodyFont);
        bodyStyleLeftNoBorder.SetFont(bodyFont8PT);
        bodyNumberStyleNoBorder.SetFont(bodyFont);
        titleStyleRightNoBorder.SetFont(bodyFont);
        titleStyleLeftNoBorder.SetFont(bodyFont);
    }

    public override MemoryStream 產生報表()
    {
        #region 報表抬頭

        var row = 0;

        excel.MergeCell(row, 0, row, 15, BorderStyle.None);
        excel.SetCellValue(row, 0, 公司名稱, headerStyle);
        excel.SetRowHeight(row, 20);
        row++;
        excel.MergeCell(row, 0, row, 15, BorderStyle.None);
        excel.SetCellValue(row, 0, string.Format("承辦{0}", 客戶名稱), headerStyle);
        excel.SetRowHeight(row, 20);
        row++;
        excel.MergeCell(row, 0, row, 15, BorderStyle.None);
        excel.SetCellValue(row, 0, string.Format("『{0}』", 專案名稱), headerStyle);
        excel.SetRowHeight(row, 20);
        row++;
        excel.MergeCell(row, 0, row, 15, BorderStyle.None);
        excel.SetCellValue(row, 0, string.Format("{0}【{1}{2}】", 報表名稱, 年度, 月份), headerStyle);
        excel.SetRowHeight(row, 20);
        row++;

        excel.MergeCell(row, 4, row, 14, BorderStyle.None);
        excel.SetCellValue(row, 4, "加班時數 / 金額", titleStyleCenterNoBorder);
        row++;

        excel.SetCellValue(row, 0, "序號", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 1, "站別", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 2, "人員姓名", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 3, "薪資", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 4, string.Format("前段*{0}", 報表參數["前段"]), titleStyleCenterNoBorder);
        excel.SetCellValue(row, 5, "小計", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 6, string.Format("後段*{0}", 報表參數["後段"]), titleStyleCenterNoBorder);
        excel.SetCellValue(row, 7, "小計", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 8, string.Format("休息日再延長*{0}", 報表參數["休息日延長"]), titleStyleCenterNoBorder);
        excel.SetCellValue(row, 9, "小計", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 10, string.Format("假日*{0}", 報表參數["假日"]), titleStyleCenterNoBorder);
        excel.SetCellValue(row, 11, "小計", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 12, string.Format("例假日再延長*{0}", 報表參數["例假日延長"]), titleStyleCenterNoBorder);
        excel.SetCellValue(row, 13, "小計", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 14, "請領金額", titleStyleCenterNoBorder);
        excel.SetCellValue(row, 15, "備註", titleStyleCenterNoBorder);
        excel.SetRowHeight(row, 24);
        row++;

        #endregion

        #region 報表資料

        int beginRow = row;
        int serial = 1;
        int SST01_SUM = 0;
        int SST02_SUM = 0;
        int SST03_SUM = 0;
        int SST21_SUM = 0;
        int SST22_SUM = 0;

        foreach (DataRow r in 資料來源.Tables[0].Rows)
        {
            var 月薪 = WDAC.GetInt32Value(r["SSP00"]) + WDAC.GetInt32Value(r["SSP02"]);
            var 加班費合計 = WDAC.GetInt32Value(r["SSP06"])
                + WDAC.GetInt32Value(r["SSP07"])
                + WDAC.GetInt32Value(r["SSP08"])
                + WDAC.GetInt32Value(r["SSP13"])
                + WDAC.GetInt32Value(r["SSP14"]);

            excel.SetCellValue(row, 0, serial, bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 1, WDAC.GetStringValue(r["SSBRHX"]), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 2, WDAC.GetStringValue(r["BPNME"]), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 3, 月薪, bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 5, WDAC.GetInt32Value(r["SSP06"]), bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 7, WDAC.GetInt32Value(r["SSP07"]), bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 9, WDAC.GetInt32Value(r["SSP13"]), bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 11, WDAC.GetInt32Value(r["SSP08"]), bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 13, WDAC.GetInt32Value(r["SSP14"]), bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 14, 加班費合計, bodyNumberStyleNoBorder);
            excel.SetCellValue(row, 15, WDAC.GetStringValue(r["SSC02"]), bodyStyleLeftNoBorder);

            if (以時分顯示時數)
            {
                excel.SetCellValue(row, 4, string.Format("{0:#00}:{1:00}", r["SST01"], r["SST01M"]), bodyStyleCenterNoBorder);
                excel.SetCellValue(row, 6, string.Format("{0:#00}:{1:00}", r["SST02"], r["SST02M"]), bodyStyleCenterNoBorder);
                excel.SetCellValue(row, 8, string.Format("{0:#00}:{1:00}", r["SST21"], r["SST21M"]), bodyStyleCenterNoBorder);
                excel.SetCellValue(row, 10, string.Format("{0:#00}:{1:00}", r["SST03"], r["SST03M"]), bodyStyleCenterNoBorder);
                excel.SetCellValue(row, 12, string.Format("{0:#00}:{1:00}", r["SST22"], r["SST22M"]), bodyStyleCenterNoBorder);
            }
            else
            {
                excel.SetCellValue(row, 4, WDAC.GetInt32Value(r["SST01"]), bodyNumberStyleNoBorder);
                excel.SetCellValue(row, 6, WDAC.GetInt32Value(r["SST02"]), bodyNumberStyleNoBorder);
                excel.SetCellValue(row, 8, WDAC.GetInt32Value(r["SST21"]), bodyNumberStyleNoBorder);
                excel.SetCellValue(row, 10, WDAC.GetInt32Value(r["SST03"]), bodyNumberStyleNoBorder);
                excel.SetCellValue(row, 12, WDAC.GetInt32Value(r["SST22"]), bodyNumberStyleNoBorder);
            }

            SST01_SUM += WDAC.GetInt32Value(r["SST01"]) * 60 + WDAC.GetInt32Value(r["SST01M"]);
            SST02_SUM += WDAC.GetInt32Value(r["SST02"]) * 60 + WDAC.GetInt32Value(r["SST02M"]);
            SST03_SUM += WDAC.GetInt32Value(r["SST03"]) * 60 + WDAC.GetInt32Value(r["SST03M"]);
            SST21_SUM += WDAC.GetInt32Value(r["SST21"]) * 60 + WDAC.GetInt32Value(r["SST21M"]);
            SST22_SUM += WDAC.GetInt32Value(r["SST22"]) * 60 + WDAC.GetInt32Value(r["SST22M"]);

            row++;
            serial++;
        }

        #endregion

        #region 合計及表尾批示
        excel.MergeCell(row, 0, row, 1, BorderStyle.None);
        excel.SetCellValue(row, 0, "合計", titleStyleLeftNoBorder);

        excel.SetCellFormula(row, 5, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 5), excel.RCtoA1(row - 1, 5)), bodyNumberStyleNoBorder);
        excel.SetCellFormula(row, 7, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 7), excel.RCtoA1(row - 1, 7)), bodyNumberStyleNoBorder);
        excel.SetCellFormula(row, 9, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 9), excel.RCtoA1(row - 1, 9)), bodyNumberStyleNoBorder);
        excel.SetCellFormula(row, 11, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 11), excel.RCtoA1(row - 1, 11)), bodyNumberStyleNoBorder);
        excel.SetCellFormula(row, 13, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 13), excel.RCtoA1(row - 1, 13)), bodyNumberStyleNoBorder);
        excel.SetCellFormula(row, 14, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 14), excel.RCtoA1(row - 1, 14)), bodyNumberStyleNoBorder);

        if (以時分顯示時數)
        {
            excel.SetCellValue(row, 4, MinuteToTime(SST01_SUM), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 6, MinuteToTime(SST02_SUM), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 8, MinuteToTime(SST21_SUM), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 10, MinuteToTime(SST03_SUM), bodyStyleCenterNoBorder);
            excel.SetCellValue(row, 12, MinuteToTime(SST22_SUM), bodyStyleCenterNoBorder);
        }
        else
        {
            excel.SetCellFormula(row, 4, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 4), excel.RCtoA1(row - 1, 4)), bodyNumberStyleNoBorder);
            excel.SetCellFormula(row, 6, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 6), excel.RCtoA1(row - 1, 6)), bodyNumberStyleNoBorder);
            excel.SetCellFormula(row, 8, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 8), excel.RCtoA1(row - 1, 8)), bodyNumberStyleNoBorder);
            excel.SetCellFormula(row, 10, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 10), excel.RCtoA1(row - 1, 10)), bodyNumberStyleNoBorder);
            excel.SetCellFormula(row, 12, String.Format("SUM({0}:{1})", excel.RCtoA1(beginRow, 12), excel.RCtoA1(row - 1, 12)), bodyNumberStyleNoBorder);
        }
        row++;

        excel.MergeCell(row, 0, row, 5, BorderStyle.None);
        excel.SetCellValue(row, 0, "承辦人：", titleStyleLeftNoBorder);
        excel.MergeCell(row, 6, row, 10, BorderStyle.None);
        excel.SetCellValue(row, 6, "業務主管：", titleStyleLeftNoBorder);
        excel.MergeCell(row, 11, row, 15, BorderStyle.None);
        excel.SetCellValue(row, 11, "公司章：", titleStyleLeftNoBorder);

        #endregion

        excel.SetColumnWidth(0, 6);
        excel.SetColumnWidth(1, 10);
        excel.SetColumnWidth(2, 10);
        excel.SetColumnWidth(3, 10);
        excel.SetColumnWidth(4, 10);
        excel.SetColumnWidth(5, 10);
        excel.SetColumnWidth(6, 10);
        excel.SetColumnWidth(7, 10);
        excel.SetColumnWidth(8, 10);
        excel.SetColumnWidth(9, 10);
        excel.SetColumnWidth(10, 10);
        excel.SetColumnWidth(11, 10);
        excel.SetColumnWidth(12, 10);
        excel.SetColumnWidth(13, 10);
        excel.SetColumnWidth(14, 10);
        excel.SetColumnWidth(15, 50);

        MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
        return ms;
    }

}