File name
Commit message
Commit date
File name
Commit message
Commit date
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;
namespace AdbRecordCompare
{
public partial class Form1 : Form
{
private MySqlConnection KBManagerConnection;
private MySqlConnection slaveConnection;
private MySqlConnection compareConnection;
public Form1()
{
InitializeComponent();
// DataGridViewCellFormatting 이벤트 핸들러 추가
dataGridView1.CellFormatting += DataGridView_CellFormatting;
// 라디오 버튼 CheckedChanged 이벤트 핸들러 연결
radioAll.CheckedChanged += radioButton_CheckedChanged;
radioTen.CheckedChanged += radioButton_CheckedChanged;
radioHundred.CheckedChanged += radioButton_CheckedChanged;
radioTenAndHundred.CheckedChanged += radioButton_CheckedChanged;
}
//==========================================================================================
//======================================= 버튼 이벤트 =======================================
private void btnSearch_Click(object sender, EventArgs e)
{
string KBManagerServer = "dt11.kabead.com";
string ADBServer = "dt88.kabead.com";
string[] servers = { "dt11.kabead.com", "dt22.kabead.com", "dt44.kabead.com", "dt55.kabead.com", "dt66.kabead.com", "dt77.kabead.com", "dt88.kabead.com" };
string KBManagerConnectionString = $"Server={KBManagerServer};Database=kbmanager;User Id=root;Password=js4188..$!;SslMode=none;";
string slaveConnectionString = $"Server={ADBServer};Database=ordcsth_adb;User Id=root;Password=js4188..$!;SslMode=none;";
int totalTables = 0;
int totalRecords = 0;
int checkedTables = 0;
int checkedRecords = 0;
int tenOverCount = 0;
int hundredOverCount = 0;
tenOver.Text = "0";
hundredOver.Text = "0";
dataGridView1.Rows.Clear(); // 데이터 조회 전 DataGridView를 초기화
try
{
using (KBManagerConnection = new MySqlConnection(KBManagerConnectionString))
using (slaveConnection = new MySqlConnection(slaveConnectionString))
{
KBManagerConnection.Open();
slaveConnection.Open();
if (cmbServerList.Text == "전체조회")
{
foreach (var compareServer in servers)
{
string realdbip = GetRealDbIp(compareServer);
if (string.IsNullOrEmpty(realdbip)) continue;
// 현재 조회 중인 DB IP를 라벨에 설정
DataBaseName.Text = compareServer;
string compareConnectionString = $"Server={compareServer};User Id=root;Password=js4188..$!;SslMode=none;";
using (compareConnection = new MySqlConnection(compareConnectionString))
{
compareConnection.Open();
// 데이터 조회 및 누적 처리
PerformDataRetrieval(compareConnection, realdbip, ADBServer, ref totalTables, ref totalRecords, ref checkedTables, ref checkedRecords, ref tenOverCount, ref hundredOverCount);
}
}
}
else
{
// 개별 서버 조회
string compareServer = cmbServerList.Text;
string realdbip = GetRealDbIp(compareServer);
if (string.IsNullOrEmpty(realdbip)) return;
// 현재 조회 중인 DB IP를 라벨에 설정
DataBaseName.Text = compareServer;
string compareConnectionString = $"Server={compareServer};User Id=root;Password=js4188..$!;SslMode=none;";
using (compareConnection = new MySqlConnection(compareConnectionString))
{
compareConnection.Open();
// 데이터 조회 및 누적 처리
PerformDataRetrieval(compareConnection, realdbip, ADBServer, ref totalTables, ref totalRecords, ref checkedTables, ref checkedRecords, ref tenOverCount, ref hundredOverCount);
}
}
// 최종적으로 누적된 테이블 수와 레코드 수를 UI에 업데이트
TableCount.Text = $"{checkedTables}/{totalTables}";
RecordCount.Text = $"{checkedRecords}/{totalRecords}";
tenOver.Text = tenOverCount.ToString();
hundredOver.Text = hundredOverCount.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void PerformDataRetrieval(MySqlConnection compareConnection, string realdbip, string ADBServer, ref int totalTables, ref int totalRecords, ref int checkedTables, ref int checkedRecords, ref int tenOverCount, ref int hundredOverCount)
{
// 각 서버의 테이블 수 계산
string countQuery = $@"
SELECT COUNT(*) FROM `kbmanager`.`access` AS a
WHERE
a.`pggubun` IN ('00', '09', '11')
AND a.`hidden` = 0
AND a.`dbname` LIKE 'carcs_%'
AND a.`dbname` NOT IN (
'carcs_kabeadcarcs', 'carcs_english', 'carcs_kabead',
'carcs_kabeadcarcs01', 'carcs_kabeadcarcs02', 'carcs_kabeadcarcs03',
'carcs_kabeadcarcs04', 'carcs_kabeadcarcs05', 'carcs_kabeadcarcs06',
'carcs_kabeadcarcs10', 'carcs_kabeadcarcs11', 'carcs_kabead1111',
'carcs_kabeadvn_car', 'carcs_thecarpet2002', 'carcs_woobo',
'carcs_test11', 'carcs_test1234', 'carcs_mnsict',
'carcs_hukio7', 'carcs_gongim7874', 'carcs_ksc0793',
'carcs_kjcjeong', 'carcs_adbtest' , 'carcs_jcar3233' , 'carcs_leemingi88'
)
AND a.`dbip` = '{realdbip}';
";
MySqlCommand countCommand = new MySqlCommand(countQuery, KBManagerConnection);
int serverTables = Convert.ToInt32(countCommand.ExecuteScalar());
totalTables += serverTables;
//각 테이블의 레코드 수 계산
string KBManagerQuery = countQuery.Replace("COUNT(*)", "a.`code`, a.`dbname`, a.`offname`, a.`dbip`");
MySqlCommand KBManagerCommand = new MySqlCommand(KBManagerQuery, KBManagerConnection);
MySqlDataReader KBManagerReader = KBManagerCommand.ExecuteReader();
while (KBManagerReader.Read())
{
string dbname = KBManagerReader["dbname"].ToString();
int serverRecords = GetTableRecordCount(compareConnection, dbname, "fixsale_d")
+ GetTableRecordCount(compareConnection, dbname, "fixsale_dw")
+ GetTableRecordCount(compareConnection, dbname, "fixsale_m");
totalRecords += serverRecords;
}
KBManagerReader.Close();
//각 테이블의 데이터 처리
KBManagerReader = KBManagerCommand.ExecuteReader();
while (KBManagerReader.Read())
{
string code = KBManagerReader["code"].ToString();
string dbip = KBManagerReader["dbip"].ToString();
string dbname = KBManagerReader["dbname"].ToString();
string offname = KBManagerReader["offname"].ToString();
if (IsDbVersionValid(compareConnection, dbname, 98))
{
int fixsaleDCount = GetTableRecordCount(compareConnection, dbname, "fixsale_d") + GetTableRecordCount(compareConnection, dbname, "fixsale_dw");
int fixsaleMCount = GetTableRecordCount(compareConnection, dbname, "fixsale_m");
int adbFixsaleDCount = GetTableRecordCountByCode(slaveConnection, "adb_fixsale_d", code);
int adbFixsaleMCount = GetTableRecordCountByCode(slaveConnection, "adb_fixsale_m", code);
int fixsaleDDifference = fixsaleDCount - adbFixsaleDCount;
int fixsaleMDifference = fixsaleMCount - adbFixsaleMCount;
// 10 이상 100 미만 차이 카운트
if (fixsaleDDifference >= 10 && fixsaleDDifference < 100) tenOverCount++;
//if (fixsaleMDifference >= 10 && fixsaleMDifference < 100) tenOverCount++;
// 100 이상 차이 카운트
if (fixsaleDDifference >= 100) hundredOverCount++;
//if (fixsaleMDifference >= 100) hundredOverCount++;
checkedTables++;
checkedRecords += fixsaleDCount + fixsaleMCount;
// 데이터 그리드 뷰에 행 추가
dataGridView1.Rows.Add(dbip, dbname, offname, code, ADBServer, fixsaleMDifference, fixsaleDDifference);
// UI 업데이트
TableCount.Text = $"{checkedTables}/{totalTables}";
RecordCount.Text = $"{checkedRecords}/{totalRecords}";
Application.DoEvents();
}
}
KBManagerReader.Close();
}
private string GetRealDbIp(string compareServer)
{
switch (compareServer)
{
case "dt11.kabead.com": return "dt01.kabead.com";
case "dt22.kabead.com": return "dt02.kabead.com";
case "dt44.kabead.com": return "dt04.kabead.com";
case "dt55.kabead.com": return "dt05.kabead.com";
case "dt66.kabead.com": return "dt06.kabead.com";
case "dt77.kabead.com": return "dt07.kabead.com";
case "dt88.kabead.com": return "dt08.kabead.com";
default: return null;
}
}
private int GetTableRecordCount(MySqlConnection connection, string dbName, string tableName)
{
string query = $@"SELECT COUNT(1) FROM `{dbName}`.`{tableName}` ";
MySqlCommand command = new MySqlCommand(query, connection);
return Convert.ToInt32(command.ExecuteScalar());
}
private int GetTableRecordCountByCode(MySqlConnection connection, string tableName, string code)
{
string query = $"SELECT COUNT(1) FROM `{tableName}` WHERE `accessCode` = @code";
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@code", code);
return Convert.ToInt32(command.ExecuteScalar());
}
private bool IsDbVersionValid(MySqlConnection connection, string dbName, int requiredVersion)
{
string query = $"SELECT COUNT(1) FROM `{dbName}`.`setting` WHERE setname = 'dbversion' AND setdata >= @version";
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@version", requiredVersion);
return Convert.ToInt32(command.ExecuteScalar()) > 0;
}
private void btnExcel_Click(object sender, EventArgs e)
{
// 현재 선택된 탭에 따라 해당 GridView를 선택
DataGridView selectedGridView = dataGridView1;
if (selectedGridView == null || selectedGridView.Rows.Count == 0)
{
MessageBox.Show("데이터가 없습니다.");
return;
}
using (SaveFileDialog saveFileDialog = new SaveFileDialog())
{
saveFileDialog.Filter = "Excel Files|*.xlsx";
saveFileDialog.Title = "Save as Excel File";
saveFileDialog.FileName = DateTime.Now.ToString("yyyyMMddhhmm") + "ADB비교.xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
// EPPlus 라이브러리
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excel = new ExcelPackage())
{
ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Sheet1");
for (int i = 0; i < selectedGridView.Columns.Count; i++)
{
worksheet.Cells[1, i + 1].Value = selectedGridView.Columns[i].HeaderText;
worksheet.Cells[1, i + 1].Style.Font.Bold = true;
}
for (int i = 0; i < selectedGridView.Rows.Count; i++)
{
for (int j = 0; j < selectedGridView.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1].Value = selectedGridView.Rows[i].Cells[j].Value?.ToString();
}
}
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
// 저장
FileInfo excelFile = new FileInfo(saveFileDialog.FileName);
excel.SaveAs(excelFile);
MessageBox.Show("Excel 파일이 성공적으로 저장되었습니다.");
}
}
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.Close();
}
//==========================================================================================
//=========================================== 이벤트 =======================================
private void DataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
// 이 이벤트는 모든 셀에 대해 호출되므로 특정 컬럼을 필터링
DataGridView grid = sender as DataGridView;
if (grid.Columns[e.ColumnIndex].Name == "RecordDifferenceDetail") // "레코드 차이" 컬럼 이름
{
if (e.Value != null && int.TryParse(e.Value.ToString(), out int difference))
{
if (difference >= 100)
{
e.CellStyle.BackColor = System.Drawing.Color.Red;
e.CellStyle.ForeColor = System.Drawing.Color.White;
}
else if (difference >= 10)
{
e.CellStyle.BackColor = System.Drawing.Color.Orange;
e.CellStyle.ForeColor = System.Drawing.Color.Black;
}
}
}
}
private void radioButton_CheckedChanged(object sender, EventArgs e)
{
// 라디오 버튼 체크 여부 확인
RadioButton rb = sender as RadioButton;
if (rb.Checked)
{
ApplyFilter(rb.Name);
}
}
private void ApplyFilter(string filterType)
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
int difference = 0;
if (int.TryParse(row.Cells["RecordDifferenceDetail"].Value?.ToString(), out difference))
{
switch (filterType)
{
case "radioTen":
row.Visible = difference >= 10 && difference < 100;
break;
case "radioTenAndHundred":
row.Visible = difference >= 10 ;
break;
case "radioHundred":
row.Visible = difference >= 100;
break;
case "radioAll":
default:
row.Visible = true;
break;
}
}
}
}
}
}