실무에서 피벗을 과연 사용할까..? 했었지만 😊 응 사용해! 였다
SELECT B.EmpNm AS '사원명'
,A.PaymDt AS '지급년월일'
,MAX(CASE WHEN xxxx = 1001 THEN A.AMT
ELSE 0 END) AS '기본급'
,MAX(CASE WHEN xxxx = 1002 THEN A.AMT
ELSE 0 END) AS '상여'
,MAX(CASE WHEN xxxx = 1003 THEN A.AMT
ELSE 0 END) AS '제수당'
,MAX(CASE WHEN xxxx = 1004 THEN A.AMT
ELSE 0 END) AS '월차수당'
,MAX(CASE WHEN xxxx = 1005 THEN A.AMT
ELSE 0 END) AS '식대'
,MAX(CASE WHEN xxxx = 1006 THEN A.AMT
ELSE 0 END) AS '자가운전보조금'
,MAX(CASE WHEN xxxx = 1007 THEN A.AMT
ELSE 0 END) AS '야간근로수당'
,MAX(CASE WHEN xxxx = 2001 THEN A.AMT
ELSE 0 END) AS '기타수당'
,C.TaxAmt AS '과세'
,C.NoTaxAmt AS '비과세'
,C.xAmt AS '제출비과세'
,C.NoxAmt AS '미제출비과세'
,C.PTAmt AS '지급총액'
,C.DAmt AS '공제금액'
,C.BAmt AS '차인지급액'
,B.NAmt AS '국민연금'
,B.LI AS '장기요양보험'
,B.Hlt AS '건강보험'
,C.Incm AS '소득세'
,C.Rdx AS '지방소득세'
,C.RScTx AS '농특세'
,MAX(CASE WHEN D.xxxx = 5004 THEN D.AMT
ELSE 0 END) AS '고용보험'
,MAX(CASE WHEN D.xxxx = 5005 THEN D.AMT
ELSE 0 END) AS '학자금상환'
FROM Pt AS A
LEFT JOIN Empt AS B --사원 테이블
ON A.Ed = B.Ed
LEFT JOIN Px AS C -- 급여집계테이블
ON A.ECd = C.ECd
AND C.Pt = A.Pt
LEFT JOIN DT AS D
ON A.Ed = D.Ed
AND A.YM = D.YM
GROUP BY A.Pt, A.Ed, D.YM, D.Ed
HAVING A.YM = 202207 -- 당월 변경가능
자프링에서의 일종의 비즈니스 로직 클래스의 개념과 윈폼에서 컨트롤러의 역할이 비슷하지 않나 싶다
public string _filePath;
public SQLiteConnection _conn;
public string _searchDate, _empCd; // 검색년월, 사원번호
public Dictionary<int, string> dict; // 수당코드의 이름
public SqliteSource(string _filePath, string _searchDate, string _empCd)
{
// 생성자를 통하여 커넥션 시작
this._filePath = _filePath;
this._searchDate = _searchDate;
this._empCd = _empCd;
string _connectionString = string.Format(@"Data Source={0};Pooling=true;FailIfMissing=false;", _filePath);
this._conn = new SQLiteConnection(_connectionString);
this._conn.Open();
}
public SqliteSource(string _filePath, string _searchDate)
{
// 생성자를 통하여 커넥션 시작
this._filePath = _filePath;
this._searchDate = _searchDate;
string _connectionString = string.Format(@"Data Source={0};Pooling=true;FailIfMissing=false;", _filePath);
this._conn = new SQLiteConnection(_connectionString);
this._conn.Open();
}
public void close()
{
// 사용이 끝난 뒤 커넥션 닫기
this._conn.Close();
}
윈폼에서 매번 컨트롤러를 거쳐 커넥션을 열고 닫기를 관리하기 까다롭기에 생성자를 통하여 커넥션을 생성하고 열어주도록한다.
public DataTable toDatatable(string query)
{
// 쿼리를 데이터테이블로 변환하는 메서드
var cmd = new SQLiteCommand(query, _conn);
cmd.ExecuteNonQuery();
SQLiteDataReader sdr = cmd.ExecuteReader();
// list of name in current database
DataTable _ret = new DataTable();
_ret.Load(sdr);
sdr.Close();
return _ret;
}
반복적으로 쓰는 DataTable로 만들어 주는 메서드를 생성, 결합도를 낮춘다
최종으로는 이렇게 쿼리를 통해서 DataTable 로 바로 반환 할 수 있게 만듦. 🤢
이렇게 DataTable 추출이 완료되었다면 추출한 테이블을 토대로 양식에 맞게 엑셀에 옮겨주는 작업을 거쳐야 한다.
Nuget 패키지 설치 (마소가 직접 만들었나 보다)
템플릿 불러오기 - 참조
[TestClass()]
public class SqliteSourceTests
{
private static Excel.Workbook wb = null;
private static Excel.Application app = null;
private static Excel.Worksheet ws = null;
[TestMethod()]
public void SqliteSourceTest()
{
string _filePath = @"D:\tempTest.DB";
string _sDate = "202204";
SqliteSource sqliteSource = new SqliteSource(_filePath, _sDate);
//DataTable dt = sqliteSource.getExtraPay();
string _excelPath = @"{템플릿저장경로}";
int delim = _excelPath.LastIndexOf('\\');
string _templateName = _excelPath.Substring(delim + 1, _excelPath.LastIndexOf('.')-(delim+1));
string _templateDir = _excelPath.Substring(0, delim);
string _excelEndPath = string.Format(@"{0}\{1}\{2}\{4}_{3}"
, _templateDir
, 회사코드
, _sDate
, _templateName
, _sDate);
string _excelEndDir = _excelEndPath.Substring(0, _excelEndPath.LastIndexOf('\\'));
app = new Excel.Application();
wb = app.Workbooks.Open(_excelPath);
ws = wb.Worksheets["{템플릿워크시트이름}"] as Excel.Worksheet;
// 사전 정보 얻기
DataTable dt = sqliteSource.getCompany();
// 회사정보의 경우 무조건 보장
string companyName = dt.Rows[0]["NAME"].ToString();
string repName = dt.Rows[0]["REPST"].ToString();
int month = sqliteSource._searchMonth;
ws.Cells[1][2] = companyName;
ws.Cells[16][4] = string.Format("{0}년 {1}월", sqliteSource._searchYear, sqliteSource._searchMonth);
ws.Cells[26][4] = string.Format("* 대표자: {0}", repName);
if (File.Exists(_excelEndPath)) File.Delete(_excelEndPath);
DirectoryInfo di = new DirectoryInfo(_excelEndDir);
if (!di.Exists)
{
di.Create();
}
wb.SaveAs(_excelEndPath);
wb.Close();
app.Quit();
Debug.WriteLine("엑셀 파일 생성 완료");
}
}
Excel.Application()
을 통하여 애플리케이션을 열고 그안에서 템플릿을 열어온다✔ 주의
- 다음과 같이 엑셀이 주어질 때 프로그램에서 다루던 행과 열이 뒤바뀌어 표현됨을 인지하여야 한다
- 또한 0,0 에서 시작하던 배열과는 달리 엑셀은 [1,1] 부터 시작된다
- 즉, 회사명 같은 경우 일반적인 int[][] 배열에서 [1,0] 과 같다고 보면 된다
endPath
에서 확인해 볼 수 있다One line query
를 통하여 급여 정보를 모두 갖고 올 수 없다public void getExtraPay()
{
// 수당코드와 수당이름 매핑 후 저장
string query = "SELECT DISTINCT B.cD, B.NM FROM PDT AS A LEFT JOIN AT AS B ON A.AD = B.AD ORDER BY A.AD";
DataTable dt = toDatatable(query);
dict = dt.AsEnumerable()
.ToDictionary<DataRow, object, object>(row => row.Field<object>(0),
row => row.Field<object>(1));
}
가장 상단의 쿼리를 기준으로 딕셔너리에 있는 키값에 따라 회사별 수당항목을 불러올 수 있습니다
말그대로 제약조건을 위반한 경우일 수 있는데 제가 마주한 케이스는 null 값을 가지는 칼럼에 대한 제약조건을 걸지 않았다는 점입니다.
실제 sqlite dbms를 통해서 쿼리를 실행하였을 때는 이러한 에러가 없는 것을 보고 null 값 처리를 하지 않았다는 문제를 확인하게 됨
→ 우선 null
값을 가지는 칼럼을 확인하고 해당 필드에 ISNULL()
과 같이 NULL 값일 경우 다른 값으로 할당되도록 합니다
IFNULL({찾고자하는 필드}, {NULL일 경우 할당될 값})
을 사용합니다아래와 같은 템플릿이 있다 하였을 때
동적으로 사람 수에 따라 그 길이를 늘려주어야 한다
소스에서 급여 정보를 가져오는 dt = sqliteSource.getPayRoll()
에서 유동적으로 rows.count 의 개수에 따라 총 n-1개 만큼 row 를 늘려준다
기존 템플릿에는 하나의 row 를 남긴 채로 총 사람 수 n
이라 하였을 때, n-1
번 반복하면서 row 를 복사하여 붙여 넣어준다
for (int i = 1; i < dt.Rows.Count; i++) // 초기 템플릿은 하나를 갖고있음. 그렇다면 회사 인원에 따라 진행하기 때문에 1 부터 시작
{
// 초기 늘어날값 지정
Excel.Range selectRange = ws.Rows[9 + ":" + 10];
selectRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
// 아래 붙여넣기
Excel.Range dest = ws.Rows[9 + ":" + 10];
selectRange.Copy(dest);
}
여기까지 엑셀 틀은 마무리
먼저 시작위치를 정해야 하기 때문에 따로 Point
라는 명칭의 객체를 사용하여 현재 위치를 담아주었다. ( 🤔 int[] , 즉 배열로 선언하여도 되지만 언젠가 쓰일 다른 엑셀 프로젝트 재사용성 측면에서 따로 객체를 두는 것이 이점이 있을 것이라 판단 )
class Point
{
public int x { get; set; }
public int y { get; set; }
public Point(int y, int x)
{
this.x = x;
this.y = y;
}
}
우선 각 회사별로 세무관리 서비스에 등록한 수당명이 다르기에 해당하는 이름을 가졌는지 여부를 확인하고 그 값을 부여해야 한다
😒 ??? : 가령 야간수당의 경우 회사별로 야간 수당, 야간근로수당, 야간 근로 수당, 야간
이런식으로 제각각인 경우를 발견하였다
😊 해결 하고자 한다면, 위 경우에는 뽑아온 DataTable 의 컬럼명들 중에 "야간" 을 포함한 컬럼이 있는지 확인하고, 없다면 0을 반환하는 메서드를 따로 만들어 주었다
public int findCol(string[] names, string keyword, DataTable dt, int idx)
{
int ret = 0;
List<string> pos = names.AsEnumerable().Where(name => name.Contains(keyword)).ToList();
if(pos.Count > 0)
{ // 유망한 경우, 데이터테이블에서 해당 값을 모두 더해준다
pos.ForEach(key =>
{
ret += Convert.ToInt32(dt.Rows[idx][key]);
});
}
return ret;
}
돌고 돌아 드디어 엑셀에 값을 넣을 수 있게 되었다
// 값 채우기
Point start = new Point(1, 9); // 시작점, A9
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.Cells[start.y][start.x] = i + 1; // 순번
ws.Cells[start.y + 1][start.x] = dt.Rows[i]["사원명"].ToString(); // 사원명
ws.Cells[start.y + 1][start.x + 1] = dt.Rows[i]["담당업무"].ToString(); // 담당업무
ws.Cells[start.y + 2][start.x] = dt.Rows[i]["생년월일"].ToString(); // 생년월일
ws.Cells[start.y + 2][start.x + 1] = dt.Rows[i]["입사일"].ToString(); // 입사일
ws.Cells[start.y + 3][start.x] = dt.Rows[i]["호봉"].ToString(); // 호봉
ws.Cells[start.y + 4][start.x] = dt.Rows[i]["근로일수"].ToString(); // 근로일수
ws.Cells[start.y + 4][start.x + 1] = dt.Rows[i]["월근로시간"].ToString(); // 월근로시간
ws.Cells[start.y + 5][start.x] = dt.Rows[i]["기본급"].ToString(); // 기본급
ws.Cells[start.y + 6][start.x] = findCol(names, "직책", dt, i).ToString(); // 직책급
ws.Cells[start.y + 6][start.x + 1] = findCol(names, "직무", dt, i).ToString(); // 직무수당
ws.Cells[start.y + 7][start.x] = findCol(names, "자격", dt, i).ToString(); // 자격수당
ws.Cells[start.y + 7][start.x + 1] = findCol(names, "제수당", dt, i).ToString(); // 제수당
ws.Cells[start.y + 8][start.x] = findCol(names, "상여", dt, i).ToString(); // 상여금
ws.Cells[start.y + 8][start.x + 1] = findCol(names, "명절", dt, i).ToString(); // 명절수당
ws.Cells[start.y + 9][start.x] = findCol(names, "근속", dt, i).ToString(); // 근속수당
ws.Cells[start.y + 9][start.x + 1] = findCol(names, "기타", dt, i).ToString(); // 기타수당
ws.Cells[start.y + 10][start.x + 1] = findCol(names, "연장", dt, i).ToString(); // 연장수당
ws.Cells[start.y + 11][start.x + 1] = findCol(names, "야간", dt, i).ToString(); // 야간수당
ws.Cells[start.y + 12][start.x + 1] = findCol(names, "휴일", dt, i).ToString(); // 휴일수당
ws.Cells[start.y + 14][start.x] = findCol(names, "식대", dt, i).ToString(); // 식대
ws.Cells[start.y + 14][start.x + 1] = findCol(names, "자가운전", dt, i).ToString(); // 자가운전
ws.Cells[start.y + 16][start.x] = dt.Rows[i]["국민연금"].ToString(); // 국민연금
ws.Cells[start.y + 16][start.x + 1] = dt.Rows[i]["고용보험"].ToString(); // 고용보험
start.x += 2;
}
if (File.Exists(_excelEndPath+ ".xlsx")) File.Delete(_excelEndPath+".xlsx");
DirectoryInfo di = new DirectoryInfo(_excelEndDir);
if (!di.Exists)
{
di.Create();
}
wb.SaveAs(_excelEndPath);
wb.Close();
app.Quit();
sqliteSource.close();
Debug.WriteLine("엑셀 파일 생성 완료");
파일이 만약 있다면 삭제해주고, 디렉토리도 없다면 생성해준다
완료!
이제는 모듈화와 리팩토링을 거쳐서 윈폼 유효성 검사등만 남았네요