151 lines
6.0 KiB
C#
151 lines
6.0 KiB
C#
using Microsoft.AspNetCore.Http;
|
|
using Npgsql;
|
|
using Server.SQL;
|
|
using Server.System;
|
|
using System.Globalization;
|
|
|
|
namespace Server.Git
|
|
{
|
|
public class ExcelSQL
|
|
{
|
|
List<sheet> sheets;
|
|
|
|
public ExcelSQL(List<sheet> sheets)
|
|
{
|
|
this.sheets = sheets;
|
|
}
|
|
|
|
public void DataUpdate()
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(STATICS.EXCEL_SQL_URL))
|
|
{
|
|
try
|
|
{
|
|
// 데이터베이스 연결 열기
|
|
connection.Open();
|
|
|
|
// 쿼리 작성 및 실행
|
|
//모든 쿼리 삭제
|
|
string query;
|
|
string header1 = "CREATE TABLE IF NOT EXISTS excel.";
|
|
string header2 = "INSERT INTO excel.";
|
|
string newTableQuery;
|
|
string tableDatas;
|
|
#region 모든 테이블 삭제
|
|
query = "DROP SCHEMA excel CASCADE;" +
|
|
"CREATE SCHEMA excel AUTHORIZATION manager;";
|
|
ExecuteNonQuery(connection, query);
|
|
#endregion
|
|
#region 테이블 세팅
|
|
query = "";
|
|
for (int n = 0; n < sheets.Count; n++)
|
|
{
|
|
//초기화
|
|
newTableQuery = "";
|
|
tableDatas = "(";
|
|
|
|
#region 신규 테이블 생성
|
|
newTableQuery += header1;
|
|
newTableQuery += sheets[n].name + "(";
|
|
for (int m = 0; m < sheets[n].variable.Count; m++)
|
|
{
|
|
if (sheets[n].type[m] == "long" && sheets[n].variable[m] == "index")
|
|
{
|
|
newTableQuery += "index SERIAL PRIMARY KEY";
|
|
tableDatas += "index";
|
|
continue;
|
|
}
|
|
if (m != 0)
|
|
{
|
|
newTableQuery += ",\n";
|
|
tableDatas += ", ";
|
|
}
|
|
|
|
switch (sheets[n].type[m])
|
|
{
|
|
case "int":
|
|
newTableQuery += $"{sheets[n].variable[m]} INT ";
|
|
break;
|
|
case "string":
|
|
newTableQuery += $"{sheets[n].variable[m]} VARCHAR(255) ";
|
|
break;
|
|
case "enum":
|
|
newTableQuery += $"{sheets[n].variable[m]} INT2 ";
|
|
break;
|
|
case "text":
|
|
newTableQuery += $"{sheets[n].variable[m]} TEXT ";
|
|
break;
|
|
case "time":
|
|
newTableQuery += $"{sheets[n].variable[m]} timestamp ";
|
|
break;
|
|
}
|
|
tableDatas += sheets[n].variable[m];
|
|
}
|
|
newTableQuery += ");\n";
|
|
tableDatas += ") VALUES ";
|
|
bool isStart = true;
|
|
query += newTableQuery;
|
|
|
|
#endregion
|
|
#region 신규 테이블 데이터 올리기
|
|
|
|
query += header2;
|
|
query += sheets[n].name;
|
|
query += tableDatas;
|
|
foreach(KeyValuePair<long, Dictionary<string, object>> pair in sheets[n].dicViewer)
|
|
{
|
|
if (isStart)
|
|
{
|
|
isStart = !isStart;
|
|
}
|
|
else
|
|
{
|
|
query += ", ";
|
|
}
|
|
query += "(";
|
|
for (int m = 0; m < sheets[n].variable.Count; m++)
|
|
{
|
|
switch (sheets[n].type[m])
|
|
{
|
|
case "int":
|
|
case "enum":
|
|
query += $"{pair.Value[sheets[n].variable[m]]}";
|
|
break;
|
|
case "string":
|
|
case "text":
|
|
query += $"'{pair.Value[sheets[n].variable[m]]}'";
|
|
break;
|
|
case "time":
|
|
query += $"'{((DateTime)pair.Value[sheets[n].variable[m]]).ToString("yyyy-MM-dd HH:mm:ss")}'";
|
|
break;
|
|
}
|
|
if(m != sheets[n].variable.Count -1)
|
|
{
|
|
query += ", ";
|
|
}
|
|
}
|
|
query += ")";
|
|
}
|
|
#endregion
|
|
}
|
|
ExecuteNonQuery(connection, query);
|
|
|
|
#endregion
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine($"Error: {ex.Message}");
|
|
}
|
|
}
|
|
}
|
|
private void ExecuteNonQuery(NpgsqlConnection connection, string query)
|
|
{
|
|
using (NpgsqlCommand command = new NpgsqlCommand(query, connection))
|
|
{
|
|
Console.WriteLine(query);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
}
|