334 lines
17 KiB
C#
334 lines
17 KiB
C#
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();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|