Files
KEPS/Keps.WinForms/Services/ExcelImportService.cs

334 lines
17 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

using Keps.WinForms.Infrastructure;
using Keps.WinForms.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
namespace Keps.WinForms.Services
{
/// <summary>
/// Excel 파일에서 실데이터를 읽어 DB에 적재하는 서비스입니다.
/// </summary>
public sealed class ExcelImportService
{
private readonly SqlConnectionFactory _connectionFactory;
private readonly ClusterMaintenanceService _clusterMaintenanceService;
/// <summary>
/// Excel 적재 서비스 인스턴스를 생성합니다.
/// </summary>
/// <param name="connectionFactory">DB 연결을 제공하는 팩토리.</param>
public ExcelImportService(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
_clusterMaintenanceService = new ClusterMaintenanceService(connectionFactory);
}
/// <summary>
/// elections 테이블에서 사용 가능한 선거 연월 목록을 조회합니다.
/// </summary>
/// <returns>연월 문자열 목록(내림차순).</returns>
public List<string> LoadElectionYears()
{
List<string> years = new List<string>();
using (SqlConnection connection = _connectionFactory.Open())
using (SqlCommand command = new SqlCommand(LegacySqlCatalog.ElectionYears, connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
years.Add(Convert.ToString(reader[0]));
}
}
return years;
}
/// <summary>
/// 지정한 폴더의 Excel 파일(.xlsx/.xlsm)을 읽어 실데이터를 DB에 적재합니다.
/// </summary>
/// <param name="filePath">읽어들일 Excel 파일 경로.</param>
/// <param name="electionYear">적재 대상 선거 연월(예: "201804").</param>
/// <param name="mode">적재 모드(현재 Real만 지원).</param>
/// <param name="progress">진행 상황을 보고받을 프로그레스 리스너.</param>
/// <exception cref="InvalidOperationException">파일이 없거나 지원 확장자가 아닐 때 발생.</exception>
/// <exception cref="NotSupportedException">미지원 모드일 때 발생.</exception>
public void Import(string filePath, string electionYear, ImportMode mode, IProgress<ImportProgress> progress)
{
if (string.IsNullOrWhiteSpace(filePath) || !File.Exists(filePath))
{
throw new InvalidOperationException("유효한 Excel 파일을 선택하세요.");
}
string extension = Path.GetExtension(filePath);
if (string.Equals(extension, ".xls", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("현재 버전은 EPPlus 4.5.3.3 기준으로 작성되어 .xlsx/.xlsm만 지원합니다. 기존 .xls 파일은 .xlsx로 변환 후 다시 시도하세요.");
}
if (!string.Equals(extension, ".xlsx", StringComparison.OrdinalIgnoreCase)
&& !string.Equals(extension, ".xlsm", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("지원되는 Excel 파일(.xlsx/.xlsm)을 선택하세요.");
}
progress.Report(new ImportProgress(5, "기존 데이터 정리"));
EnsureElectionExists(electionYear);
if (mode == ImportMode.Real)
{
CleanupReal(electionYear);
}
else
{
throw new NotSupportedException("시뮬레이션 엑셀 적재는 지원하지 않습니다.");
}
progress.Report(new ImportProgress(10, Path.GetFileName(filePath) + " 열기"));
List<string> candidateNames = new List<string>();
using (ExcelEpplusReader reader = new ExcelEpplusReader())
{
reader.Open(filePath);
List<ExcelSheet> sheets = new List<ExcelSheet>(reader.ReadSheets());
for (int sheetIndex = 0; sheetIndex < sheets.Count; sheetIndex++)
{
ExcelSheet sheet = sheets[sheetIndex];
progress.Report(new ImportProgress(10 + (sheetIndex * 60 / Math.Max(1, sheets.Count)), "시트 처리 중: " + sheet.Name));
ImportRealSheet(sheet, electionYear, candidateNames);
}
}
UpsertCandidateNames(electionYear, candidateNames);
progress.Report(new ImportProgress(80, "군집 테이블 재구성"));
_clusterMaintenanceService.RebuildRealClusters(electionYear);
progress.Report(new ImportProgress(100, "완료"));
}
/// <summary>
/// elections 테이블에 대상 연월이 없으면 1건을 삽입하여 존재를 보장합니다.
/// </summary>
/// <param name="electionYear">선거 연월(예: "201804").</param>
private void EnsureElectionExists(string electionYear)
{
using (SqlConnection connection = _connectionFactory.Open())
using (SqlCommand command = new SqlCommand("INSERT INTO elections(ele_ym) SELECT TOP 1 @eleYm WHERE NOT EXISTS (SELECT 1 FROM elections WHERE ele_ym = @eleYm);", connection))
{
command.Parameters.AddWithValue("@eleYm", electionYear);
command.ExecuteNonQuery();
}
}
/// <summary>
/// 실데이터 적재 전 기존 poll_data 및 관련 테이블에서 대상 연월의 데이터를 정리합니다.
/// </summary>
/// <param name="electionYear">선거 연월.</param>
private void CleanupReal(string electionYear)
{
using (SqlConnection connection = _connectionFactory.Open())
using (SqlCommand command = new SqlCommand("DELETE FROM [poll_data] WHERE ele_ym = @eleYm", connection))
{
command.Parameters.AddWithValue("@eleYm", electionYear);
command.ExecuteNonQuery();
}
}
/// <summary>
/// 실데이터 Excel 시트 한 개를 파싱하여 poll_data 등 테이블에 적재합니다.
/// </summary>
/// <param name="sheet">EPPlus로 읽은 시트.</param>
/// <param name="electionYear">선거 연월.</param>
/// <param name="candidateNames">발견된 후보자명 누적 리스트(출력 가변).</param>
private void ImportRealSheet(ExcelSheet sheet, string electionYear, List<string> candidateNames)
{
int voteColumn = sheet.FindHeaderColumn("투표수", 128);
int totalColumn = sheet.FindHeaderColumn("계", 128);
if (voteColumn <= 0 || totalColumn <= 0 || totalColumn <= voteColumn)
{
throw new InvalidOperationException(
string.Format(
"Excel 시트 '{0}'에서 필수 헤더를 찾을 수 없습니다. '투표수'와 '계' 헤더를 확인하세요.",
sheet.Name));
}
int candidateStartColumn = voteColumn + 1;
int candidateEndColumn = totalColumn - 1;
if (candidateEndColumn < candidateStartColumn)
{
throw new InvalidOperationException(
string.Format(
"Excel 시트 '{0}'에서 후보자 득표 컬럼을 찾을 수 없습니다. '투표수'와 '계' 사이에 후보자 컬럼이 있는지 확인하세요.",
sheet.Name));
}
if (candidateNames.Count == 0)
{
for (int column = candidateStartColumn; column <= candidateEndColumn; column++)
{
string candidateName = LegacyValueHelper.CleanString(sheet.GetValue(1, column));
if (!string.IsNullOrWhiteSpace(candidateName))
{
candidateNames.Add(candidateName);
}
}
}
int row = 2;
while (true)
{
string areaName = LegacyValueHelper.CleanString(sheet.GetValue(row, 1));
if (string.IsNullOrWhiteSpace(areaName) || areaName == "합계")
{
break;
}
string districtCode = LegacyValueHelper.CleanString(sheet.GetValue(row, 3));
string districtLabel = LegacyValueHelper.CleanString(sheet.GetValue(row, 4));
string districtName = "[" + districtCode + "]" + districtLabel;
string siGunGu = LegacyValueHelper.CleanString(sheet.GetValue(row, 2));
string eupMyeonDong = LegacyValueHelper.CleanString(sheet.GetValue(row, 5));
string pollingPlaceNo = ExtractLegacyPollingPlaceNo(LegacyValueHelper.CleanString(sheet.GetValue(row, 6)));
int eligibleVoterCount = LegacyValueHelper.ToInt(sheet.GetValue(row, 7));
int electorateCount = LegacyValueHelper.ToInt(sheet.GetValue(row, 7));
int voteCount = LegacyValueHelper.ToInt(sheet.GetValue(row, 8));
int totalCandidateVotes = LegacyValueHelper.ToInt(sheet.GetValue(row, totalColumn));
Dictionary<string, string> cand = new Dictionary<string, string>();
Dictionary<string, string> candRatio = new Dictionary<string, string>();
int candidateNo = 1;
for (int column = candidateStartColumn; column <= candidateEndColumn; column++)
{
int vote = LegacyValueHelper.ToInt(sheet.GetValue(row, column));
decimal ratio = totalCandidateVotes == 0 ? 0m : Math.Round((decimal)vote / totalCandidateVotes * 100, 2, MidpointRounding.AwayFromZero);
cand["cand_" + candidateNo] = vote.ToString();
candRatio["cand_ratio_" + candidateNo] = ratio.ToString("0.00");
candidateNo++;
}
string buildingName = LegacyValueHelper.CleanString(sheet.GetValue(row, totalColumn + 3)).Replace("'", "'");
string address1 = LegacyValueHelper.CleanString(sheet.GetValue(row, totalColumn + 4)).Replace("'", "'");
string note = LegacyValueHelper.CleanString(sheet.GetValue(row, totalColumn + 5));
string duplicateFlag = LegacyValueHelper.CleanString(sheet.GetValue(row, totalColumn + 6));
string areaDetail = LegacyValueHelper.CleanString(sheet.GetValue(row, totalColumn + 8));
string candJson = JsonHelper.Serialize(cand);
string candRatioJson = JsonHelper.Serialize(candRatio);
using (SqlConnection connection = _connectionFactory.Open())
using (SqlCommand command = new SqlCommand(@"
INSERT INTO poll_data
(
ele_ym, 선거명, 시도명, 선거구명, 시군구, 읍면동, 투표구명,
t_유권자수, 선거인수, 투표인수, cand, cand_ratio, 후보별득표수계,
투표소_중복여부, 관할구역, 건물명, 주소1, 비고_기타
)
VALUES
(
@eleYm, @electionName, @areaName, @districtName, @siGunGu, @eupMyeonDong, @pollingPlaceNo,
@eligibleVoterCount, @electorateCount, @voteCount, @cand, @candRatio, @totalCandidateVoteCount,
@duplicateFlag, @areaDetail, @buildingName, @address1, @note
)", connection))
{
command.Parameters.Add(new SqlParameter("@eleYm", SqlDbType.VarChar, 6) { Value = electionYear ?? string.Empty });
command.Parameters.Add(new SqlParameter("@electionName", SqlDbType.VarChar, 50) { Value = electionYear ?? string.Empty });
command.Parameters.Add(new SqlParameter("@areaName", SqlDbType.VarChar, 50) { Value = areaName ?? string.Empty });
command.Parameters.Add(new SqlParameter("@districtName", SqlDbType.VarChar, 50) { Value = districtName ?? string.Empty });
command.Parameters.Add(new SqlParameter("@siGunGu", SqlDbType.VarChar, 50) { Value = siGunGu ?? string.Empty });
command.Parameters.Add(new SqlParameter("@eupMyeonDong", SqlDbType.VarChar, 50) { Value = eupMyeonDong ?? string.Empty });
command.Parameters.Add(new SqlParameter("@pollingPlaceNo", SqlDbType.VarChar, 16) { Value = pollingPlaceNo ?? string.Empty });
command.Parameters.Add(new SqlParameter("@eligibleVoterCount", SqlDbType.Int) { Value = eligibleVoterCount });
command.Parameters.Add(new SqlParameter("@electorateCount", SqlDbType.Int) { Value = electorateCount });
command.Parameters.Add(new SqlParameter("@voteCount", SqlDbType.Int) { Value = voteCount });
command.Parameters.Add(new SqlParameter("@cand", SqlDbType.VarChar, -1) { Value = candJson ?? string.Empty });
command.Parameters.Add(new SqlParameter("@candRatio", SqlDbType.VarChar, -1) { Value = candRatioJson ?? string.Empty });
command.Parameters.Add(new SqlParameter("@totalCandidateVoteCount", SqlDbType.Int) { Value = totalCandidateVotes });
command.Parameters.Add(new SqlParameter("@duplicateFlag", SqlDbType.VarChar, 50) { Value = duplicateFlag ?? string.Empty });
command.Parameters.Add(new SqlParameter("@areaDetail", SqlDbType.VarChar, 50) { Value = areaDetail ?? string.Empty });
command.Parameters.Add(new SqlParameter("@buildingName", SqlDbType.VarChar, 500) { Value = buildingName ?? string.Empty });
command.Parameters.Add(new SqlParameter("@address1", SqlDbType.VarChar, 500) { Value = address1 ?? string.Empty });
command.Parameters.Add(new SqlParameter("@note", SqlDbType.VarChar, 200) { Value = note ?? string.Empty });
try
{
command.ExecuteNonQuery();
}
catch (SqlException)
{
foreach (SqlParameter p in command.Parameters)
{
string val = p.Value == null || p.Value == DBNull.Value ? string.Empty : p.Value.ToString();
Debug.WriteLine(string.Format("PARAM {0} Len={1} Val={2}", p.ParameterName, val.Length, val.Length > 120 ? val.Substring(0, 120) : val));
}
throw;
}
}
row++;
}
}
/// <summary>
/// 레거시 표기에서 투표구 번호 텍스트를 추출합니다(예: "제1투" → "1").
/// </summary>
/// <param name="pollingPlaceText">원본 텍스트.</param>
/// <returns>정규화된 투표구 번호 문자열.</returns>
private static string ExtractLegacyPollingPlaceNo(string pollingPlaceText)
{
if (string.IsNullOrWhiteSpace(pollingPlaceText))
{
return "0";
}
string value = pollingPlaceText.Trim();
if (!value.EndsWith("투"))
{
return "0";
}
string right3 = value.Length <= 3 ? value : value.Substring(value.Length - 3, 3);
string left2 = right3.Length <= 2 ? right3 : right3.Substring(0, 2);
return left2.Replace("제", string.Empty).Trim();
}
/// <summary>
/// cand_name 테이블에 후보자명을 upsert(없으면 삽입, 있으면 유지)합니다.
/// </summary>
/// <param name="electionYear">선거 연월.</param>
/// <param name="candidateNames">후보자명 목록.</param>
private void UpsertCandidateNames(string electionYear, List<string> candidateNames)
{
if (candidateNames == null || candidateNames.Count == 0)
{
return;
}
using (SqlConnection connection = _connectionFactory.Open())
{
using (SqlCommand deleteCommand = new SqlCommand("DELETE FROM cand_name WHERE ele_ym = @eleYm", connection))
{
deleteCommand.Parameters.AddWithValue("@eleYm", electionYear);
deleteCommand.ExecuteNonQuery();
}
for (int i = 0; i < candidateNames.Count; i++)
{
using (SqlCommand insertCommand = new SqlCommand("INSERT INTO cand_name(ele_ym, title, cand_no, cand_name, 당이름) VALUES(@eleYm, @eleYm, @candNo, @candName, @candName)", connection))
{
insertCommand.Parameters.AddWithValue("@eleYm", electionYear);
insertCommand.Parameters.AddWithValue("@candNo", (i + 1).ToString());
insertCommand.Parameters.AddWithValue("@candName", candidateNames[i]);
insertCommand.ExecuteNonQuery();
}
}
}
}
}
}