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
{
///
/// Excel 파일에서 실데이터를 읽어 DB에 적재하는 서비스입니다.
///
public sealed class ExcelImportService
{
private readonly SqlConnectionFactory _connectionFactory;
private readonly ClusterMaintenanceService _clusterMaintenanceService;
///
/// Excel 적재 서비스 인스턴스를 생성합니다.
///
/// DB 연결을 제공하는 팩토리.
public ExcelImportService(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
_clusterMaintenanceService = new ClusterMaintenanceService(connectionFactory);
}
///
/// elections 테이블에서 사용 가능한 선거 연월 목록을 조회합니다.
///
/// 연월 문자열 목록(내림차순).
public List LoadElectionYears()
{
List years = new List();
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;
}
///
/// 지정한 폴더의 Excel 파일(.xlsx/.xlsm)을 읽어 실데이터를 DB에 적재합니다.
///
/// 읽어들일 Excel 파일 경로.
/// 적재 대상 선거 연월(예: "201804").
/// 적재 모드(현재 Real만 지원).
/// 진행 상황을 보고받을 프로그레스 리스너.
/// 파일이 없거나 지원 확장자가 아닐 때 발생.
/// 미지원 모드일 때 발생.
public void Import(string filePath, string electionYear, ImportMode mode, IProgress 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 candidateNames = new List();
using (ExcelEpplusReader reader = new ExcelEpplusReader())
{
reader.Open(filePath);
List sheets = new List(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, "완료"));
}
///
/// elections 테이블에 대상 연월이 없으면 1건을 삽입하여 존재를 보장합니다.
///
/// 선거 연월(예: "201804").
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();
}
}
///
/// 실데이터 적재 전 기존 poll_data 및 관련 테이블에서 대상 연월의 데이터를 정리합니다.
///
/// 선거 연월.
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();
}
}
///
/// 실데이터 Excel 시트 한 개를 파싱하여 poll_data 등 테이블에 적재합니다.
///
/// EPPlus로 읽은 시트.
/// 선거 연월.
/// 발견된 후보자명 누적 리스트(출력 가변).
private void ImportRealSheet(ExcelSheet sheet, string electionYear, List 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 cand = new Dictionary();
Dictionary candRatio = new Dictionary();
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++;
}
}
///
/// 레거시 표기에서 투표구 번호 텍스트를 추출합니다(예: "제1투" → "1").
///
/// 원본 텍스트.
/// 정규화된 투표구 번호 문자열.
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();
}
///
/// cand_name 테이블에 후보자명을 upsert(없으면 삽입, 있으면 유지)합니다.
///
/// 선거 연월.
/// 후보자명 목록.
private void UpsertCandidateNames(string electionYear, List 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();
}
}
}
}
}
}