1313 lines
61 KiB
C#
1313 lines
61 KiB
C#
using FrontendWebApi.Models;
|
||
using Microsoft.AspNetCore.Mvc;
|
||
using Repository.BackendRepository.Interface;
|
||
using Repository.FrontendRepository.Interface;
|
||
using System.Collections.Generic;
|
||
using System.Threading.Tasks;
|
||
using System;
|
||
using System.IO;
|
||
using static FrontendWebApi.Models.Bill;
|
||
using Microsoft.Extensions.Logging;
|
||
using System.Text.Json;
|
||
using Newtonsoft.Json.Linq;
|
||
using System.Security.Cryptography;
|
||
using WkHtmlToPdfDotNet.Contracts;
|
||
using WkHtmlToPdfDotNet;
|
||
using Microsoft.AspNetCore.Hosting;
|
||
using Microsoft.AspNetCore.SignalR;
|
||
using Microsoft.Extensions.Configuration;
|
||
using Serilog.Core;
|
||
using static FrontendWebApi.ApiControllers.TenantBillController;
|
||
using System.Reflection;
|
||
using Microsoft.CodeAnalysis.CSharp.Syntax;
|
||
using System.Data.SqlTypes;
|
||
using System.Linq;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using NPOI.Util;
|
||
using System.Globalization;
|
||
using System.Net.Http;
|
||
using System.Net.Http.Headers;
|
||
using System.Text;
|
||
using System.IO.Compression;
|
||
|
||
|
||
|
||
|
||
|
||
// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
|
||
|
||
namespace FrontendWebApi.ApiControllers
|
||
{
|
||
//[Route("api/[controller]")]
|
||
//[ApiController]
|
||
//public class TenantBillController
|
||
public class TenantBillController : MyBaseApiController<TenantBillController>
|
||
{
|
||
private readonly IBackendRepository backendRepository;
|
||
private readonly ILogger<TenantBillController> _logger;
|
||
private readonly IConfiguration Configuration;
|
||
private IWebHostEnvironment _webHostEnvironment;
|
||
private readonly IConverter _converter;
|
||
|
||
|
||
const string TenantListtable = "archive_electric_meter_tenant_list";
|
||
const string TenantBilltable = "archive_electric_meter_tenant_bill";
|
||
|
||
|
||
public TenantBillController(IBackendRepository backendRepository, IFrontendRepository frontendRepository, ILogger<TenantBillController> logger,
|
||
IConfiguration configuration,
|
||
IWebHostEnvironment webHostEnvironment,
|
||
IConverter converter)
|
||
{
|
||
this.backendRepository = backendRepository;
|
||
this._logger = logger;
|
||
Configuration = configuration;
|
||
_webHostEnvironment = webHostEnvironment;
|
||
_converter = converter;
|
||
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<List<TenantList>>> GetTenantList()
|
||
{
|
||
ApiResult<List<TenantList>> apiResult = new ApiResult<List<TenantList>>();
|
||
List<TenantList> tenantList = new List<TenantList>();
|
||
try
|
||
{
|
||
var sqlString = $"SELECT tenant_guid,list_id,tenant_name,bill_perKWH,bill_perRCV " +
|
||
$"from {TenantListtable} order by created_at";
|
||
|
||
tenantList = await backendRepository.GetAllAsync<TenantList>(sqlString);
|
||
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = tenantList;
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "讀取不到用戶資料。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> AddOneTenantList([FromBody] TenantList tl)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
try
|
||
{
|
||
var tenant_guid = Guid.NewGuid();
|
||
var tenant_name = tl.tenant_name;
|
||
var bill_perKWH = tl.bill_perKWH;
|
||
var bill_perRCV = tl.bill_perRCV;
|
||
var created_at = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
||
|
||
var sqlString = $"INSERT INTO {TenantListtable} (tenant_guid,list_id,tenant_name, bill_perKWH, bill_perRCV, created_by,created_at) " +
|
||
$"VALUES ('{tenant_guid}','{tl.list_id}','{tenant_name}', {bill_perKWH}, {bill_perRCV},'{tl.created_by}', '{created_at}')";
|
||
|
||
|
||
await backendRepository.ExecuteSql(sqlString);
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "新增成功";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "新增用戶失敗。";
|
||
if (exception.Message.Contains($" for key 'PRIMARY'"))
|
||
{
|
||
apiResult.Code = "0001";
|
||
apiResult.Msg = "已有相同用戶。";
|
||
}
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> UpdateOneTenantList([FromBody] TenantList tl)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
try
|
||
{
|
||
var updated_at = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
||
var sqlString = $"UPDATE {TenantListtable} SET " +
|
||
$"`tenant_name` = '{tl.tenant_name}', " +
|
||
$"`bill_perKWH` = {tl.bill_perKWH}, " +
|
||
$"`bill_perRCV` = {tl.bill_perRCV}, " +
|
||
$"`updated_at` = '{updated_at}' " +
|
||
$"WHERE `tenant_guid` = '{tl.tenant_guid}'";
|
||
|
||
|
||
await backendRepository.ExecuteSql(sqlString);
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "修改用戶成功";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "修改失敗。";
|
||
if (exception.Message.Contains($"a foreign key constraint"))
|
||
{
|
||
apiResult.Code = "0001";
|
||
apiResult.Msg = "水電報表仍有該用戶，無法修改名稱。";
|
||
}
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> DelOneTenantList([FromBody] TenantList tl)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
try
|
||
{
|
||
var sqlString = $"delete from {TenantListtable} WHERE tenant_guid = '{tl.tenant_guid}' ";
|
||
|
||
await backendRepository.ExecuteSql(sqlString);
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "刪除成功";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "刪除用戶失敗。";
|
||
if (exception.Message.Contains($"a foreign key constraint"))
|
||
{
|
||
apiResult.Code = "0001";
|
||
apiResult.Msg = "水電報表仍有該用戶，無法刪除。";
|
||
}
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<List<TenantBill>>> GetTenantBill([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<List<TenantBill>> apiResult = new ApiResult<List<TenantBill>>();
|
||
List<TenantBill> tenantBill = new List<TenantBill>();
|
||
try
|
||
{
|
||
string tableType = tb.tableType;
|
||
string building_tag = tb.building_tag;
|
||
string ElecOrWater = tableType == "elec" ? "E4" : "W1";
|
||
string sqlString = null;
|
||
#region 檢查設備是否沒被刪除且正在連接
|
||
|
||
string checkDataSql = $@"SELECT device_number,device_name_tag,full_name
|
||
FROM device
|
||
WHERE device_name_tag = 'E4' and deleted = 0 and is_link = 1 or device_name_tag = 'W1' and deleted = 0 and is_link = 1; ";
|
||
var existData = await backendRepository.GetAllAsync<Device>(checkDataSql);
|
||
string checkDataSql2 = $@"select * from archive_electric_meter_tenant_bill; ";
|
||
var existData2 = await backendRepository.GetAllAsync<TenantBill>(checkDataSql2);
|
||
var commonValues = existData.Select(a => a.device_number)
|
||
.Intersect(existData2.Select(b => b.device_number))
|
||
.ToList();
|
||
if (commonValues.Any())
|
||
{
|
||
string updateSql = $@"UPDATE archive_electric_meter_tenant_bill
|
||
SET deleted = 0
|
||
WHERE device_number IN ('{string.Join("', '", commonValues)}')";
|
||
await backendRepository.ExecuteSql(updateSql);
|
||
}
|
||
var newValues = existData.Select(a => a.device_number)
|
||
.Except(existData2.Select(b => b.device_number))
|
||
.ToList();
|
||
if (newValues.Any())
|
||
{
|
||
string insertSql = $@"INSERT INTO archive_electric_meter_tenant_bill (device_number, device_name_tag, created_at)
|
||
SELECT device_number, device_name_tag, NOW()
|
||
FROM device
|
||
WHERE device_number IN ('{string.Join("', '", newValues)}')";
|
||
await backendRepository.ExecuteSql(insertSql);
|
||
}
|
||
var deletedValues = existData2.Select(a => a.device_number)
|
||
.Except(existData.Select(b => b.device_number))
|
||
.ToList();
|
||
if (deletedValues.Any())
|
||
{
|
||
string updateSql = $@"UPDATE archive_electric_meter_tenant_bill
|
||
SET deleted = 1
|
||
WHERE device_number IN ('{string.Join("', '", deletedValues)}')";
|
||
await backendRepository.ExecuteSql(updateSql);
|
||
}
|
||
|
||
#endregion
|
||
|
||
|
||
if (building_tag == "D2")
|
||
{
|
||
sqlString =
|
||
$"SELECT bill_id,a.device_number,b.full_name,start_timestamp,end_timestamp,result,bill,tenant_name,tenant_guid " +
|
||
$"from {TenantBilltable} a join device b on a.device_number =b.device_number " +
|
||
$"where device_building_tag = 'D1' and a.device_name_tag = '{ElecOrWater}' and a.deleted = 0 || device_building_tag = 'D2' and a.device_name_tag = '{ElecOrWater}' and a.deleted = 0";
|
||
}
|
||
else
|
||
{
|
||
sqlString =
|
||
$"SELECT bill_id,a.device_number,b.full_name,start_timestamp,end_timestamp,result,bill,tenant_name,tenant_guid " +
|
||
$"from {TenantBilltable} a join device b on a.device_number = b.device_number " +
|
||
$"where device_building_tag = '{building_tag}' and a.device_name_tag = '{ElecOrWater}' and a.deleted = 0";
|
||
}
|
||
|
||
|
||
tenantBill = await backendRepository.GetAllAsync<TenantBill>(sqlString);
|
||
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Data = tenantBill;
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "讀取水電參考報表失敗。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> UpdateTenantBill([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
try
|
||
{
|
||
string bill_per = tb.tableType == "elec" ? "bill_perKWH" : "bill_perRCV";
|
||
var updated_at = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
||
var start_timestamp = tb.start_timestamp;
|
||
var end_timestamp = tb.end_timestamp;
|
||
string sqlString = null;
|
||
string result = "0";
|
||
|
||
if (string.IsNullOrEmpty(tb.start_timestamp) || string.IsNullOrEmpty(tb.end_timestamp) || string.IsNullOrEmpty(tb.tenant_guid))
|
||
{
|
||
sqlString = $@"
|
||
UPDATE {TenantBilltable}
|
||
SET tenant_name = (SELECT tenant_name FROM archive_electric_meter_tenant_list WHERE tenant_guid = @tenant_guid),
|
||
start_timestamp = @start_timestamp,
|
||
end_timestamp = @end_timestamp,
|
||
result = 0,
|
||
bill = 0,
|
||
updated_at = @updated_at,
|
||
tenant_guid = @tenant_guid
|
||
WHERE device_number = @device_number";
|
||
|
||
await backendRepository.ExecuteSql(sqlString, new { tb.tenant_guid, start_timestamp, end_timestamp, updated_at, tb.device_number });
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "資料填寫不完整";
|
||
return apiResult;
|
||
}
|
||
else
|
||
{
|
||
DateTime date1 = DateTime.Parse(start_timestamp);
|
||
DateTime date2 = DateTime.Parse(end_timestamp);
|
||
int monthsApart = ((date2.Year - date1.Year) * 12) + date2.Month - date1.Month;
|
||
|
||
string checkTabelSql = @"
|
||
SELECT TABLE_NAME
|
||
FROM INFORMATION_SCHEMA.TABLES
|
||
WHERE TABLE_SCHEMA LIKE 'ibms_dome_%' AND TABLE_NAME LIKE 'archive_electric_water_meter_day_%'";
|
||
var existTable = await backendRepository.GetAllAsync<string>(checkTabelSql);
|
||
|
||
List<string> howManyMonth = Enumerable.Range(0, monthsApart + 1)
|
||
.Select(i => $"archive_electric_water_meter_day_{date1.AddMonths(i):yyyyMM}")
|
||
.ToList();
|
||
var existMonth = existTable.Intersect(howManyMonth).ToList();
|
||
|
||
if (existMonth.Count == 0)
|
||
{
|
||
sqlString = $@"
|
||
UPDATE {TenantBilltable}
|
||
SET tenant_name = (SELECT tenant_name FROM archive_electric_meter_tenant_list WHERE tenant_guid = @tenant_guid),
|
||
start_timestamp = @start_timestamp,
|
||
end_timestamp = @end_timestamp,
|
||
result = 0,
|
||
bill = 0,
|
||
updated_at = @updated_at,
|
||
tenant_guid = @tenant_guid
|
||
WHERE device_number = @device_number";
|
||
|
||
await backendRepository.ExecuteSql(sqlString, new { tb.tenant_guid, start_timestamp, end_timestamp, updated_at, tb.device_number });
|
||
}
|
||
else
|
||
{
|
||
var monthQueries = existMonth.Select(month => $@"
|
||
SELECT start_timestamp, device_number, sub_result
|
||
FROM {month}
|
||
WHERE device_number = @device_number");
|
||
|
||
string month = string.Join(" UNION ALL ", monthQueries);
|
||
string checkDataSql = $@"
|
||
SELECT SUM(sub_result)
|
||
FROM ({month}) combined_result
|
||
WHERE start_timestamp BETWEEN @start_timestamp AND @end_timestamp";
|
||
var existData = await backendRepository.GetAllAsync<string>(checkDataSql, new { tb.device_number, start_timestamp, end_timestamp });
|
||
|
||
if (existData.Count != 0)
|
||
{
|
||
result = $@"
|
||
SELECT ROUND(SUM(sub_result),2)
|
||
FROM ({month}) combined_result
|
||
WHERE start_timestamp BETWEEN @start_timestamp AND @end_timestamp";
|
||
}
|
||
|
||
sqlString = $@"
|
||
UPDATE {TenantBilltable}
|
||
SET tenant_name = (SELECT tenant_name FROM archive_electric_meter_tenant_list WHERE tenant_guid = @tenant_guid),
|
||
start_timestamp = @start_timestamp,
|
||
end_timestamp = @end_timestamp,
|
||
result = ({result}),
|
||
bill = ROUND(result * (SELECT {bill_per} FROM {TenantListtable} WHERE tenant_guid = @tenant_guid)),
|
||
updated_at = @updated_at,
|
||
tenant_guid = @tenant_guid
|
||
WHERE device_number = @device_number";
|
||
|
||
await backendRepository.ExecuteSql(sqlString, new { tb.tenant_guid, start_timestamp, end_timestamp, updated_at, tb.device_number });
|
||
}
|
||
}
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "修改水電參考報表失敗。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return apiResult;
|
||
|
||
}
|
||
|
||
apiResult = await CheckDay(tb);
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> CheckDay([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
|
||
if (!string.IsNullOrEmpty(tb.start_timestamp) && !string.IsNullOrEmpty(tb.end_timestamp) && !string.IsNullOrEmpty(tb.tenant_guid))
|
||
{
|
||
try
|
||
{
|
||
string sqlString = $@"
|
||
SELECT * FROM {TenantBilltable}
|
||
WHERE tenant_guid = @tenant_guid AND deleted = 0";
|
||
|
||
var tenantBill = await backendRepository.GetAllAsync<TenantBill>(sqlString, new { tb.tenant_guid });
|
||
|
||
if (tenantBill.Count > 1)
|
||
{
|
||
if (tenantBill.Any(t => t.start_timestamp != tb.start_timestamp || t.end_timestamp != tb.end_timestamp))
|
||
{
|
||
apiResult.Code = "0001";
|
||
apiResult.Msg = "該用戶設備的起訖日期不一致，是否將所有設備起訖日期調為一致?";
|
||
return apiResult;
|
||
}
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "修改成功且該用戶的所有設備日期一致";
|
||
}
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "確認日期失敗。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "修改成功且不執行CheckDay";
|
||
}
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "修改成功且該用戶的所有設備日期一致";
|
||
return apiResult;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<ApiResult<string>> ChangeDay([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
List<TenantBill> tenantBill = new List<TenantBill>();
|
||
string sqlString = "";
|
||
string sWhere = "";
|
||
|
||
try
|
||
{
|
||
if (tb.tenant_guid != null)
|
||
{
|
||
sqlString = $@"
|
||
SELECT * FROM {TenantBilltable}
|
||
WHERE tenant_guid = @tenant_guid AND deleted = 0";
|
||
}
|
||
else
|
||
{
|
||
if (tb.building_tag != null)
|
||
{
|
||
sWhere = "AND device_building_tag = @building_tag";
|
||
}
|
||
sqlString = $@"
|
||
SELECT * FROM archive_electric_meter_tenant_bill a
|
||
JOIN device d ON a.device_number = d.device_number AND d.deleted = 0
|
||
WHERE a.deleted = 0 {sWhere}";
|
||
}
|
||
|
||
tenantBill = await backendRepository.GetAllAsync<TenantBill>(sqlString, tb);
|
||
|
||
var updateTasks = tenantBill
|
||
.Where(t => t.start_timestamp != tb.start_timestamp || t.end_timestamp != tb.end_timestamp)
|
||
.Select(t =>
|
||
{
|
||
t.start_timestamp = tb.start_timestamp;
|
||
t.end_timestamp = tb.end_timestamp;
|
||
t.tableType = t.device_name_tag == "E4" ? "elec" : "water";
|
||
return UpdateTenantBill(t);
|
||
});
|
||
|
||
await Task.WhenAll(updateTasks);
|
||
|
||
apiResult.Code = "0000";
|
||
apiResult.Msg = "修改成功";
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "更換日期失敗。";
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
}
|
||
|
||
return apiResult;
|
||
}
|
||
|
||
|
||
[HttpPost]
|
||
public async Task<IActionResult> OutputTenantBill([FromBody] TenantBill tb)
|
||
{
|
||
List<OutputBill> outputBill = new List<OutputBill>();
|
||
|
||
try
|
||
{
|
||
//List<string> buildings = tb.building_tag_list;
|
||
//string building_tag = "";
|
||
//foreach (var item in buildings)
|
||
//{
|
||
// if (item == buildings[0])
|
||
// {
|
||
// building_tag = item == "D2" ? $@"device_building_tag = 'D1' and a.deleted = 0 || device_building_tag = '{item}' and a.deleted = 0" : $@"device_building_tag = '{item}' and a.deleted = 0";
|
||
// }
|
||
// else
|
||
// {
|
||
// building_tag += item == "D2" ? $@"|| device_building_tag = 'D1' and a.deleted = 0 || device_building_tag = '{item}' and a.deleted = 0" : $@"|| device_building_tag = '{item}' and a.deleted = 0";
|
||
// }
|
||
//}
|
||
// string checkDataSql = $@"select * from archive_electric_meter_tenant_bill a
|
||
// join device c on a.device_number = c.device_number
|
||
// WHERE tenant_name is not null and tenant_guid is not null and tenant_name != '' and tenant_guid !=''
|
||
//AND ({building_tag})";
|
||
|
||
string checkDataSql = $@"select * from archive_electric_meter_tenant_bill a
|
||
WHERE tenant_name is not null and tenant_guid is not null and tenant_name != '' and tenant_guid !=''
|
||
AND a.deleted = 0";
|
||
var existData = await backendRepository.GetAllAsync<TenantBill>(checkDataSql);
|
||
if (existData.Count != 0)
|
||
{
|
||
string sqlString =
|
||
$@"SELECT
|
||
tenant_name,
|
||
tenant_guid,
|
||
start_timestamp,
|
||
end_timestamp,
|
||
bill_perKWH,
|
||
bill_perRCV,
|
||
elec_result,
|
||
water_result,
|
||
elec_bill,
|
||
water_bill,
|
||
(elec_bill + water_bill) AS total_bill
|
||
FROM (
|
||
SELECT
|
||
a.tenant_name,
|
||
a.tenant_guid,
|
||
MIN(NULLIF(a.start_timestamp, '')) AS start_timestamp,
|
||
MAX(NULLIF(a.end_timestamp, '')) AS end_timestamp,
|
||
bill_perKWH,
|
||
bill_perRCV,
|
||
SUM(CASE WHEN a.device_name_tag = 'E4' THEN result ELSE 0 END) AS elec_result,
|
||
SUM(CASE WHEN a.device_name_tag = 'W1' THEN result ELSE 0 END) AS water_result,
|
||
ROUND(bill_perKWH * SUM(CASE WHEN a.device_name_tag = 'E4' THEN result ELSE 0 END)) AS elec_bill,
|
||
ROUND(bill_perRCV * SUM(CASE WHEN a.device_name_tag = 'W1' THEN result ELSE 0 END)) AS water_bill
|
||
FROM archive_electric_meter_tenant_bill a
|
||
JOIN archive_electric_meter_tenant_list b ON a.tenant_guid = b.tenant_guid
|
||
JOIN device c ON a.device_number = c.device_number
|
||
GROUP BY a.tenant_name
|
||
) AS subquery_alias;";
|
||
|
||
outputBill = await backendRepository.GetAllAsync<OutputBill>(sqlString);
|
||
string deviceSql = @$"select a.tenant_guid,d.full_name from archive_electric_meter_tenant_bill a join device d on a.device_number = d.device_number";
|
||
var device = await backendRepository.GetAllAsync<TenantBill>(deviceSql);
|
||
|
||
string filePath = CreateOutputForm(outputBill, device);
|
||
|
||
byte[] file = System.IO.File.ReadAllBytes(filePath);
|
||
|
||
|
||
return new FileContentResult(file, "application/pdf")
|
||
{
|
||
FileDownloadName = "水電報表.pdf"
|
||
};
|
||
}
|
||
else
|
||
{
|
||
var data = new { Code = "0001", Msg = "還沒有選擇用戶，無法匯出檔案。" };
|
||
return StatusCode(400, data);
|
||
}
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
var data = new { Code = "9999", Msg = "匯出失敗。" };
|
||
return StatusCode(500, data);
|
||
}
|
||
}
|
||
|
||
public string CreateOutputForm(List<OutputBill> outputBill, List<TenantBill> device)
|
||
{
|
||
try
|
||
{
|
||
string htmlStr = this.getOutputFormHtmlStr(outputBill, device);
|
||
|
||
string filepath = Configuration.GetValue<string>("FilePath:OutputForm");
|
||
if (!Directory.Exists(filepath))
|
||
{
|
||
Directory.CreateDirectory(filepath);
|
||
_logger.LogInformation("file CreateOutputForm path: " + filepath);
|
||
}
|
||
var nowString = DateTime.UtcNow.AddHours(8).ToString("yyyyMMdd_HHmmss");
|
||
|
||
//Random r1 = new System.Random();
|
||
//string r2 = r1.Next(0, 999).ToString("0000");
|
||
//string r2 = RandomNumberGenerator.GetInt32(0, 999).ToString("0000");
|
||
|
||
filepath += $"{nowString}_水電報表.pdf";
|
||
|
||
var doc = new HtmlToPdfDocument()
|
||
{
|
||
GlobalSettings = {
|
||
ColorMode = ColorMode.Color,
|
||
//Orientation = Orientation.Landscape,
|
||
PaperSize = PaperKind.A4,
|
||
Out = filepath,
|
||
Margins = new MarginSettings
|
||
{
|
||
Unit = Unit.Millimeters,
|
||
Top = 10,
|
||
Bottom = 10,
|
||
Right = 0,
|
||
Left = 0
|
||
},
|
||
},
|
||
Objects = {
|
||
new ObjectSettings() {
|
||
HtmlContent=htmlStr,
|
||
WebSettings = { DefaultEncoding = "utf-8"},
|
||
FooterSettings = new FooterSettings() {
|
||
Center = "第 [page] 頁 共 [topage] 頁",
|
||
FontName = "DFKai-sb",
|
||
},
|
||
LoadSettings = new LoadSettings() {
|
||
JSDelay = 1000,
|
||
StopSlowScript = false,
|
||
BlockLocalFileAccess = false,
|
||
DebugJavascript = true
|
||
}
|
||
//HeaderSettings = { FontSize = 9, Right = "Page [page] of [toPage]", Line = true, Spacing = 2.812 }
|
||
}
|
||
}
|
||
};
|
||
|
||
// _converter.Warning += ConvertWarning;
|
||
|
||
_converter.Convert(doc);
|
||
//IntPtr converter = _converter.CreateConverter(doc);
|
||
//_converter.Tools.DestroyConverter();
|
||
return filepath;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw new Exception(ex.ToString());
|
||
}
|
||
}
|
||
|
||
private string getOutputFormHtmlStr(List<OutputBill> outputBill, List<TenantBill> device)
|
||
{
|
||
|
||
try
|
||
{
|
||
string path = Configuration.GetValue<string>("FilePath:OutputFormTemplate");
|
||
string image = Configuration.GetValue<string>("FilePath:Image");
|
||
|
||
//string path = _webHostEnvironment.ContentRootPath + "\\StaticFiles\\import.html";
|
||
if (!System.IO.File.Exists(path))
|
||
{
|
||
return "";
|
||
}
|
||
//string cssroot = _webHostEnvironment.ContentRootPath + "\\StaticFiles\\css\\";
|
||
string htmlStr = System.IO.File.ReadAllText(path);
|
||
//string vendorscss = System.IO.File.ReadAllText(cssroot + "vendors.bundle.css");
|
||
//string appBundelCss = System.IO.File.ReadAllText(cssroot + "app.bundle.css");
|
||
//string skinmasterCss = System.IO.File.ReadAllText(cssroot + "skins\\skin-master.css");
|
||
string bill = "";
|
||
foreach (var item in outputBill)
|
||
{
|
||
var devices = device.Where(x => x.tenant_guid == item.tenant_guid).Select(x => x.full_name).ToList();
|
||
string deviceList = string.Join(" , ", devices);
|
||
|
||
// 設備列表長度閥值，原因為設備如果超過2頁A4會發生內容重疊問題
|
||
int maxLength = 1050;
|
||
if (deviceList.Length > maxLength)
|
||
{
|
||
deviceList = deviceList.Substring(0, maxLength) + "...";
|
||
}
|
||
|
||
bill += @$" <div class='container a4-page'>
|
||
<div class='header'>
|
||
<img src='{image}' alt='Taipei Dome Logo' width='150'>
|
||
<h2>水電費用明細</h2>
|
||
</div>
|
||
<div class='statistics'>
|
||
<h3>費用資訊</h3>
|
||
<p><strong>用戶: </strong>{item.tenant_name}</p>
|
||
<p><strong>起訖時間: </strong>{item.start_timestamp} ~ {item.end_timestamp}</p>
|
||
<table class='br'>
|
||
<tr>
|
||
<td><strong>用電量: </strong>{item.elec_result}度</td>
|
||
<td><strong>單價: </strong>{item.bill_perKWH}元/度</td>
|
||
<td><strong>電費總計: </strong>{item.elec_bill.ToString("#,##0")}元</td>
|
||
</tr>
|
||
<tr>
|
||
<td><strong>用水量: </strong>{item.water_result}度</td>
|
||
<td><strong>單價: </strong>{item.bill_perRCV}元/度</td>
|
||
<td><strong>水費總計: </strong>{item.water_bill.ToString("#,##0")}元</td>
|
||
</tr>
|
||
</table>
|
||
<p class='warnTxt'>
|
||
<strong>總計(費用)：各電/水量加總 * 單價(四捨五入)</strong>
|
||
</p>
|
||
</div>
|
||
<div class='total'>
|
||
<div class='total-area'>
|
||
<h3>總計金額</h3>
|
||
<div class='total-box'>
|
||
<span class='total-money'>{item.total_bill.ToString("#,##0")}</span>元
|
||
</div>
|
||
</div>
|
||
<div class='total-area'>
|
||
<h3>設備列表</h3>
|
||
<div>
|
||
{deviceList}
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>";
|
||
|
||
|
||
|
||
|
||
}
|
||
|
||
htmlStr = htmlStr.Replace("{{bill}}", bill);
|
||
|
||
|
||
return htmlStr;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw new Exception(ex.ToString());
|
||
}
|
||
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<IActionResult> OutputTenantBillCSV([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
var fileDateName = DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss");
|
||
var waterMeterFileName = $"水電報表_水錶_{fileDateName}.csv";
|
||
var electricMeterFileName = $"水電報表_電錶_{fileDateName}.csv";
|
||
var zipFileName = $"水電報表_{fileDateName}.zip";
|
||
|
||
try
|
||
{
|
||
#region 撈數據
|
||
string dateRangeSQL = $@"SELECT MIN(CAST(start_timestamp AS DATETIME)) start_timestamp,
|
||
MAX(CAST(end_timestamp AS DATETIME)) end_timestamp
|
||
FROM archive_electric_meter_tenant_bill WHERE deleted = 0;";
|
||
var dateRange = await backendRepository.GetOneAsync<TenantBill>(dateRangeSQL);
|
||
List<string> howManyMonth = new List<string>();
|
||
List<string> existMonth = new List<string>();
|
||
DateTime date1 = DateTime.Parse(dateRange.start_timestamp);
|
||
DateTime date2 = DateTime.Parse(dateRange.end_timestamp);
|
||
string month = "";
|
||
int monthsApart = ((date2.Year - date1.Year) * 12) + date2.Month - date1.Month;
|
||
|
||
// 找出資料庫存在的水電月份表
|
||
string checkTabelSql = $@"SELECT TABLE_NAME
|
||
FROM INFORMATION_SCHEMA.TABLES
|
||
WHERE TABLE_SCHEMA like 'ibms_dome_%' and TABLE_NAME like 'archive_electric_water_meter_day_%'";
|
||
var existTable = await backendRepository.GetAllAsync<string>(checkTabelSql);
|
||
|
||
for (var i = 0; i <= monthsApart; i++)
|
||
{
|
||
howManyMonth.Add($@"archive_electric_water_meter_day_{date1.AddMonths(i).ToString("yyyyMM")}");
|
||
}
|
||
foreach (var item in existTable)
|
||
{
|
||
foreach (var temp in howManyMonth)
|
||
{
|
||
if (item == temp)
|
||
{
|
||
existMonth.Add(temp);
|
||
}
|
||
}
|
||
}
|
||
// 判斷是否有資料
|
||
if (existMonth.Count == 0)
|
||
{
|
||
var msg = new { Code = "0001", Msg = "還沒有選擇用戶，無法匯出檔案。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
else
|
||
{
|
||
month = $@" SELECT device_number, start_timestamp, sub_result
|
||
FROM {existMonth[0]}";
|
||
for (var i = 1; i < existMonth.Count; i++)
|
||
{
|
||
// 要有空格
|
||
month += $@" UNION ALL
|
||
SELECT device_number, start_timestamp, sub_result
|
||
FROM {existMonth[i]}";
|
||
}
|
||
}
|
||
// 查詢SQL
|
||
var sql = @$"
|
||
-- 删除臨時表
|
||
DROP TEMPORARY TABLE IF EXISTS temp_combined_data;
|
||
|
||
-- 建立臨時表
|
||
CREATE TEMPORARY TABLE temp_combined_data (
|
||
device_number VARCHAR(255),
|
||
start_timestamp DATETIME,
|
||
sub_result decimal(15,3)
|
||
);
|
||
|
||
-- 插入數據
|
||
INSERT INTO temp_combined_data (device_number, start_timestamp, sub_result)
|
||
{month};
|
||
|
||
-- 優化后的查詢
|
||
SELECT
|
||
c.tenant_name,
|
||
b.device_name_tag,
|
||
a.device_number,
|
||
d.full_name,
|
||
a.start_timestamp AS date,
|
||
CASE
|
||
WHEN b.device_name_tag = 'E4' THEN c.bill_perKWH
|
||
WHEN b.device_name_tag = 'W1' THEN c.bill_perRCV
|
||
END AS bill_per,
|
||
ROUND(a.sub_result, 2) sub_result,
|
||
ROUND(CASE
|
||
WHEN b.device_name_tag = 'E4' THEN c.bill_perKWH
|
||
WHEN b.device_name_tag = 'W1' THEN c.bill_perRCV
|
||
END * ROUND(a.sub_result, 2)) AS total_bill,
|
||
b.start_timestamp,
|
||
b.end_timestamp,
|
||
d.device_building_tag building_tag
|
||
FROM
|
||
temp_combined_data a
|
||
JOIN
|
||
archive_electric_meter_tenant_bill b ON a.device_number COLLATE utf8mb4_unicode_ci = b.device_number AND b.deleted = 0
|
||
JOIN
|
||
archive_electric_meter_tenant_list c ON b.tenant_guid = c.tenant_guid
|
||
JOIN
|
||
device d ON d.device_number = a.device_number COLLATE utf8mb4_unicode_ci
|
||
WHERE
|
||
a.start_timestamp BETWEEN b.start_timestamp AND b.end_timestamp
|
||
ORDER BY b.device_name_tag,tenant_name,a.device_number,date;
|
||
|
||
-- 删除臨時表
|
||
DROP TEMPORARY TABLE IF EXISTS temp_combined_data;";
|
||
|
||
var outputBillExcel = await backendRepository.GetAllAsync<OutputBillExcel>(sql);
|
||
#endregion
|
||
|
||
// 分別篩選水錶和電錶的數據
|
||
var waterMeterData = outputBillExcel.Where(x => x.device_name_tag == "W1" && x.building_tag == tb.building_tag).ToList();
|
||
var electricMeterData = outputBillExcel.Where(x => x.device_name_tag == "E4" && x.building_tag == tb.building_tag).ToList();
|
||
|
||
// 檢查是否有水錶或電錶數據
|
||
//if (waterMeterData.Count == 0 && electricMeterData.Count == 0)
|
||
//{
|
||
// var msg = new { Code = "0002", Msg = "該棟沒有可匯出的水錶或電錶數據。" };
|
||
// return StatusCode(400, msg);
|
||
//}
|
||
|
||
if (tb.tableType == "elec" && electricMeterData.Count == 0)
|
||
{
|
||
var msg = new { Code = "0002", Msg = "該棟沒有可匯出的電錶數據。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
else if (tb.tableType == "water" && waterMeterData.Count == 0)
|
||
{
|
||
var msg = new { Code = "0002", Msg = "該棟沒有可匯出的水錶數據。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
|
||
// 生成CSV文件
|
||
string waterMeterCsv = null;
|
||
string electricMeterCsv = null;
|
||
if (waterMeterData.Count > 0)
|
||
{
|
||
waterMeterCsv = GenerateCsv(waterMeterData);
|
||
}
|
||
if (electricMeterData.Count > 0)
|
||
{
|
||
electricMeterCsv = GenerateCsv(electricMeterData);
|
||
}
|
||
|
||
// 返回CSV文件
|
||
if (tb.tableType == "elec" && electricMeterCsv != null)
|
||
{
|
||
using (var electricMemoryStream = new MemoryStream())
|
||
{
|
||
using (var streamWriter = new StreamWriter(electricMemoryStream, Encoding.UTF8))
|
||
{
|
||
streamWriter.Write(electricMeterCsv);
|
||
}
|
||
return File(electricMemoryStream.ToArray(), "text/csv", electricMeterFileName);
|
||
}
|
||
}
|
||
else if (tb.tableType == "water" && waterMeterCsv != null)
|
||
{
|
||
using (var waterMemoryStream = new MemoryStream())
|
||
{
|
||
using (var streamWriter = new StreamWriter(waterMemoryStream, Encoding.UTF8))
|
||
{
|
||
streamWriter.Write(waterMeterCsv);
|
||
}
|
||
return File(waterMemoryStream.ToArray(), "text/csv", electricMeterFileName);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
var msg = new { Code = "0003", Msg = "無效的表格類型。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
|
||
|
||
|
||
//// 創建ZIP檔案
|
||
//using (var zipMemoryStream = new MemoryStream())
|
||
//{
|
||
// using (var archive = new ZipArchive(zipMemoryStream, ZipArchiveMode.Create, true))
|
||
// {
|
||
// if (waterMeterData.Count > 0)
|
||
// {
|
||
// var waterEntry = archive.CreateEntry(waterMeterFileName);
|
||
// using (var entryStream = waterEntry.Open())
|
||
// using (var streamWriter = new StreamWriter(entryStream, Encoding.UTF8))
|
||
// {
|
||
// streamWriter.Write(waterMeterCsv);
|
||
// }
|
||
// }
|
||
|
||
// if (electricMeterData.Count > 0)
|
||
// {
|
||
// var electricEntry = archive.CreateEntry(electricMeterFileName);
|
||
// using (var entryStream = electricEntry.Open())
|
||
// using (var streamWriter = new StreamWriter(entryStream, Encoding.UTF8))
|
||
// {
|
||
// streamWriter.Write(electricMeterCsv);
|
||
// }
|
||
// }
|
||
// }
|
||
|
||
// zipMemoryStream.Seek(0, SeekOrigin.Begin);
|
||
// return File(zipMemoryStream.ToArray(), "application/zip", zipFileName);
|
||
//}
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。 Msg: " + exception.Message;
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Json(apiResult);
|
||
}
|
||
}
|
||
|
||
|
||
private string GenerateCsv(List<OutputBillExcel> data)
|
||
{
|
||
StringBuilder csv = new StringBuilder();
|
||
|
||
// 添加CSV標題行
|
||
csv.AppendLine("用戶,設備代碼,設備名稱,日期,用電單價(元/度),當日用電(kWh),電費(元),起訖時間");
|
||
|
||
// 添加數據行
|
||
foreach (var item in data)
|
||
{
|
||
csv.AppendLine($"{item.tenant_name},{item.device_number},{item.full_name},{item.date.ToString("yyyy/MM/dd")},{item.bill_per},{item.sub_result},{item.total_bill},{item.start_timestamp}~{item.end_timestamp}");
|
||
}
|
||
|
||
return csv.ToString();
|
||
}
|
||
|
||
|
||
[HttpPost]
|
||
public async Task<IActionResult> OutputTenantBillExcel([FromBody] TenantBill tb)
|
||
{
|
||
ApiResult<string> apiResult = new ApiResult<string>();
|
||
var fileDateName = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss");
|
||
var fileName = "水電報表_" + fileDateName + ".xlsx";
|
||
|
||
XSSFWorkbook workbook = new XSSFWorkbook();
|
||
try
|
||
{
|
||
string dateRangeSQL = $@"SELECT MIN(CAST(start_timestamp AS DATETIME)) start_timestamp,
|
||
MAX(CAST(end_timestamp AS DATETIME)) end_timestamp
|
||
FROM archive_electric_meter_tenant_bill WHERE deleted = 0;";
|
||
var dateRange = await backendRepository.GetOneAsync<TenantBill>(dateRangeSQL);
|
||
List<string> howManyMonth = new List<string>();
|
||
List<string> existMonth = new List<string>();
|
||
DateTime date1 = DateTime.Parse(dateRange.start_timestamp);
|
||
DateTime date2 = DateTime.Parse(dateRange.end_timestamp);
|
||
string month = "";
|
||
int monthsApart = ((date2.Year - date1.Year) * 12) + date2.Month - date1.Month;
|
||
|
||
// 找出資料庫存在的水電月份表
|
||
string checkTabelSql = $@"SELECT TABLE_NAME
|
||
FROM INFORMATION_SCHEMA.TABLES
|
||
WHERE TABLE_SCHEMA like 'ibms_dome_%' and TABLE_NAME like 'archive_electric_water_meter_day_%'";
|
||
var existTable = await backendRepository.GetAllAsync<string>(checkTabelSql);
|
||
|
||
for (var i = 0; i <= monthsApart; i++)
|
||
{
|
||
howManyMonth.Add($@"archive_electric_water_meter_day_{date1.AddMonths(i).ToString("yyyyMM")}");
|
||
}
|
||
foreach (var item in existTable)
|
||
{
|
||
foreach (var temp in howManyMonth)
|
||
{
|
||
if (item == temp)
|
||
{
|
||
existMonth.Add(temp);
|
||
}
|
||
}
|
||
}
|
||
// 判斷是否有資料
|
||
if (existMonth.Count == 0)
|
||
{
|
||
var msg = new { Code = "0001", Msg = "還沒有選擇用戶，無法匯出檔案。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
else
|
||
{
|
||
month = $@" SELECT device_number, start_timestamp, sub_result
|
||
FROM {existMonth[0]}";
|
||
for (var i = 1; i < existMonth.Count; i++)
|
||
{
|
||
// 要有空格
|
||
month += $@" UNION ALL
|
||
SELECT device_number, start_timestamp, sub_result
|
||
FROM {existMonth[i]}";
|
||
}
|
||
}
|
||
// 查詢SQL
|
||
var sql = @$"
|
||
-- 删除臨時表
|
||
DROP TEMPORARY TABLE IF EXISTS temp_combined_data;
|
||
|
||
-- 建立臨時表
|
||
CREATE TEMPORARY TABLE temp_combined_data (
|
||
device_number VARCHAR(255),
|
||
start_timestamp DATETIME,
|
||
sub_result decimal(15,3)
|
||
);
|
||
|
||
-- 插入數據
|
||
INSERT INTO temp_combined_data (device_number, start_timestamp, sub_result)
|
||
{month};
|
||
|
||
-- 優化后的查詢
|
||
SELECT
|
||
c.tenant_name,
|
||
b.device_name_tag,
|
||
a.device_number,
|
||
d.full_name,
|
||
a.start_timestamp AS date,
|
||
CASE
|
||
WHEN b.device_name_tag = 'E4' THEN c.bill_perKWH
|
||
WHEN b.device_name_tag = 'W1' THEN c.bill_perRCV
|
||
END AS bill_per,
|
||
ROUND(a.sub_result, 2) sub_result,
|
||
ROUND(CASE
|
||
WHEN b.device_name_tag = 'E4' THEN c.bill_perKWH
|
||
WHEN b.device_name_tag = 'W1' THEN c.bill_perRCV
|
||
END * ROUND(a.sub_result, 2)) AS total_bill,
|
||
b.start_timestamp,
|
||
b.end_timestamp,
|
||
d.device_building_tag building_tag
|
||
FROM
|
||
temp_combined_data a
|
||
JOIN
|
||
archive_electric_meter_tenant_bill b ON a.device_number COLLATE utf8mb4_unicode_ci = b.device_number AND b.deleted = 0
|
||
JOIN
|
||
archive_electric_meter_tenant_list c ON b.tenant_guid = c.tenant_guid
|
||
JOIN
|
||
device d ON d.device_number = a.device_number COLLATE utf8mb4_unicode_ci
|
||
WHERE
|
||
a.start_timestamp BETWEEN b.start_timestamp AND b.end_timestamp
|
||
ORDER BY b.device_name_tag,tenant_name,a.device_number,date;
|
||
|
||
-- 删除臨時表
|
||
DROP TEMPORARY TABLE IF EXISTS temp_combined_data;";
|
||
|
||
var outputBillExcel = await backendRepository.GetAllAsync<OutputBillExcel>(sql);
|
||
|
||
|
||
#region export file progress
|
||
#region excel設定
|
||
IFont font12 = workbook.CreateFont();
|
||
font12.FontName = "新細明體";
|
||
font12.FontHeightInPoints = 12;
|
||
ICellStyle style12 = workbook.CreateCellStyle();
|
||
style12.SetFont(font12);
|
||
style12.Alignment = HorizontalAlignment.Center;
|
||
style12.VerticalAlignment = VerticalAlignment.Center;
|
||
IFont font12Times = workbook.CreateFont();
|
||
font12Times.FontName = "Times New Roman";
|
||
font12Times.FontHeightInPoints = 12;
|
||
IFont font18 = workbook.CreateFont();
|
||
font18.FontName = "新細明體";
|
||
font18.FontHeightInPoints = 18;
|
||
font18.IsBold = true;
|
||
ICellStyle styleTitle18 = workbook.CreateCellStyle();
|
||
styleTitle18.SetFont(font18);
|
||
styleTitle18.Alignment = HorizontalAlignment.Center;
|
||
styleTitle18.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLeft12 = workbook.CreateCellStyle();
|
||
styleLeft12.SetFont(font12);
|
||
styleLeft12.Alignment = HorizontalAlignment.Left;
|
||
styleLeft12.VerticalAlignment = VerticalAlignment.Center;
|
||
ICellStyle styleLine12 = workbook.CreateCellStyle();
|
||
styleLine12.SetFont(font12);
|
||
styleLine12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
styleLine12.VerticalAlignment = VerticalAlignment.Center;
|
||
styleLine12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
styleLine12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
ICellStyle stylein12 = workbook.CreateCellStyle();
|
||
stylein12.SetFont(font12Times);
|
||
stylein12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
|
||
stylein12.VerticalAlignment = VerticalAlignment.Center;
|
||
stylein12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
stylein12.WrapText = true;
|
||
#endregion
|
||
//var data = he.OrderByDescending(x => x.deviceName).ThenBy(x => x.type).ToList();
|
||
var data = outputBillExcel.Where(x => x.building_tag == $"{tb.building_tag}").ToList();
|
||
|
||
if (data.Count > 0)
|
||
{
|
||
string lastDeviceItem = string.Empty;
|
||
string lastDevice = string.Empty;
|
||
string lastType = string.Empty;
|
||
string type = data[0].device_name_tag;
|
||
string typeName = type == "E4" ? "電錶" : "水錶";
|
||
string bill_per = type == "E4" ? "用電單價(元/度)" : "用水單價(元/度)";
|
||
string sub_result = type == "E4" ? "當日用電( kWh)" : "當日用水( m³)";
|
||
string total_bill = type == "E4" ? "電費(元)" : "水費(元)";
|
||
|
||
int RowPosition = 0;
|
||
IRow row;
|
||
ISheet sheet;
|
||
#region set cell
|
||
sheet = workbook.CreateSheet($"{typeName}");
|
||
row = sheet.CreateRow(RowPosition);
|
||
for (var i = 0; i < 8; i++)
|
||
{
|
||
sheet.SetColumnWidth(i, 4 * 160 * 12);
|
||
}
|
||
|
||
ICell cell = row.CreateCell(0);
|
||
cell = row.CreateCell(0);
|
||
cell.SetCellValue("用戶");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(1);
|
||
cell.SetCellValue("設備代碼");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(2);
|
||
cell.SetCellValue("設備名稱");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(3);
|
||
cell.SetCellValue("日期");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(4);
|
||
cell.SetCellValue(bill_per);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(5);
|
||
cell.SetCellValue(sub_result);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(6);
|
||
cell.SetCellValue(total_bill);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(7);
|
||
cell.SetCellValue("起訖時間");
|
||
cell.CellStyle = styleLine12;
|
||
#endregion
|
||
|
||
foreach (var d in data)
|
||
{
|
||
if (RowPosition == 0 && lastType == "")
|
||
{
|
||
lastType = d.device_name_tag; //第一次不用建立 sheet;
|
||
}
|
||
if (d.device_name_tag != lastType)
|
||
{
|
||
RowPosition = 0;
|
||
type = d.device_name_tag;
|
||
lastType = type;
|
||
typeName = type == "E4" ? "電錶" : "水錶";
|
||
bill_per = type == "E4" ? "用電單價(元/度)" : "用水單價(元/度)";
|
||
sub_result = type == "E4" ? "當日用電( kWh)" : "當日用水( m³)";
|
||
total_bill = type == "E4" ? "電費(元)" : "水費(元)";
|
||
sheet = workbook.CreateSheet($"{typeName}");
|
||
|
||
#region set cell
|
||
row = sheet.CreateRow(RowPosition);
|
||
for (var i = 0; i < 8; i++)
|
||
{
|
||
sheet.SetColumnWidth(i, 4 * 160 * 12);
|
||
}
|
||
cell = row.CreateCell(0);
|
||
cell.SetCellValue("用戶");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(1);
|
||
cell.SetCellValue("設備代碼");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(2);
|
||
cell.SetCellValue("設備名稱");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(3);
|
||
cell.SetCellValue("日期");
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(4);
|
||
cell.SetCellValue(bill_per);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(5);
|
||
cell.SetCellValue(sub_result);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(6);
|
||
cell.SetCellValue(total_bill);
|
||
cell.CellStyle = styleLine12;
|
||
cell = row.CreateCell(7);
|
||
cell.SetCellValue("起訖時間");
|
||
cell.CellStyle = styleLine12;
|
||
#endregion
|
||
RowPosition = 1;
|
||
}
|
||
else
|
||
{
|
||
RowPosition += 1;
|
||
}
|
||
|
||
row = sheet.CreateRow(RowPosition);
|
||
for (var i = 0; i < 8; i++)
|
||
{
|
||
cell = row.CreateCell(i);
|
||
if (i == 0)
|
||
{
|
||
cell.SetCellValue(d.tenant_name);
|
||
}
|
||
if (i == 1)
|
||
{
|
||
cell.SetCellValue(d.device_number);
|
||
}
|
||
if (i == 2)
|
||
{
|
||
cell.SetCellValue(d.full_name);
|
||
}
|
||
if (i == 3)
|
||
{
|
||
cell.SetCellValue(d.date.ToString("yyyy/MM/dd"));
|
||
}
|
||
if (i == 4)
|
||
{
|
||
cell.SetCellValue(d.bill_per);
|
||
}
|
||
if (i == 5)
|
||
{
|
||
cell.SetCellValue(d.sub_result.ToString());
|
||
}
|
||
if (i == 6)
|
||
{
|
||
cell.SetCellValue(d.total_bill.ToString());
|
||
}
|
||
if (i == 7)
|
||
{
|
||
cell.SetCellValue($"{d.start_timestamp}~{d.end_timestamp}");
|
||
}
|
||
|
||
cell.CellStyle = style12;
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
var msg = new { Code = "0001", Msg = "還沒有選擇用戶，無法匯出檔案。" };
|
||
return StatusCode(400, msg);
|
||
}
|
||
#endregion
|
||
|
||
}
|
||
catch (Exception exception)
|
||
{
|
||
apiResult.Code = "9999";
|
||
apiResult.Msg = "系統內部錯誤，請聯絡管理者。 Msg: " + exception.Message;
|
||
Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
|
||
return Json(apiResult);
|
||
}
|
||
|
||
var ms = new NpoiMemoryStream
|
||
{
|
||
AllowClose = false
|
||
};
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Seek(0, SeekOrigin.Begin);
|
||
Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");
|
||
|
||
return File(ms, "application/vnd.ms-excel", fileName);
|
||
}
|
||
}
|
||
} |