20230203

public void B_TableMarge()
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn();

string repTN = "DEPT_BK R";
string xbbTN = "DEPT X";

col1 = "DEPTNO";

whereON =
"R." + col1 + " = X." + col1;

string whereDEL_R = "R." + col1;
string whereDEL_X = "X." + col1;

colNum = 1;//★テーブルごとに設定★

//全件数カウント
//chkCNT = CNT(repTN, xbbTN);
CNT_DateCheck(repTN, xbbTN);

DEL(repTN, xbbTN, chkCNT, whereDEL_R, whereDEL_X);

/* dt = TableMinus_CNT();

if (dt.Rows[0][0].ToString() == "0")
{
Console.WriteLine("差分なし");
}
else
{
//MARGE実行(差分抽出→項目更新→日付更新)
MRG(repTN, xbbTN, colNum, chkCNT);

//削除
DEL(repTN, xbbTN, chkCNT, whereDEL_R, whereDEL_X);

INS();
}*/
}

public int CNT_DateCheck(string repTN, string xbbTN)
{
DataTable dt = new DataTable();
Console.WriteLine("■件数カウント中...");

//複製テーブル全件数
strSql = "select count(*) from " + repTN; ;
dt = DBA(strSql); //SQL実行
Rcount = dt.Rows[0][0].ToString();
Console.WriteLine(repTN + " 全" + Rcount + "行");

//元テーブル全件数
strSql = "select count(*) from " + xbbTN; ;
dt = DBA(strSql); //SQL実行
Xcount = dt.Rows[0][0].ToString();
Console.WriteLine(xbbTN + " 全" + Xcount + "行\r\n");

if (Rcount == Xcount)
{
Console.WriteLine("結果：行数一致\r\n");
}
else
{
Console.WriteLine("結果：行数差分あり\r\n");
chkCNT = 1;
}

Console.WriteLine("■更新日を確認中...\r\n");

//rep
strSql = "select MAX(KOUSHINBI) from " + repTN; ;
Console.WriteLine(strSql);
dt = DBA(strSql);

string str_repMaxUP = dt.Rows[0][0].ToString();
DateTime repMaxUP = DateTime.Parse(str_repMaxUP);
Console.WriteLine("レプリカ：" + dt.Rows[0][0] + "\r\n");

//xbb
strSql = "select MAX(KOUSHINBI) from " + xbbTN; ;
Console.WriteLine(strSql);
dt = DBA(strSql);

string str_xbbMaxUP = dt.Rows[0][0].ToString();
DateTime xbbMaxUP = DateTime.Parse(str_xbbMaxUP);
Console.WriteLine("元テーブル：" + dt.Rows[0][0] + "\r\n");

if (repMaxUP < xbbMaxUP || chkCNT == 1)
{
Console.WriteLine("結果：要更新" + "\r\n");

MRG(repTN, xbbTN, repMaxUP, xbbMaxUP);

}
else
{
Console.WriteLine("結果：更新不要" + "\r\n");
}

return chkCNT;
}

public void MRG(string repTN, string xbbTN, DateTime repMaxUP, DateTime xbbMaxUP)
{
DataTable dt = new DataTable();
int i = 1;

Console.WriteLine("■merge開始(更新・追加)...");

//差分抽出
//dt = TableMinus();
//strSql = "select * from " + repTN; ;
//Console.WriteLine(strSql + "\r\n");
//dt = DBA(strSql);

//if (dt.Rows.Count >= 1 || chkCNT == 1)
//{
// Console.WriteLine("実行...\r\n");

strSql_MRG =
"merge into " + repTN +
" using " + xbbTN +
" on (" + whereON + ")" +
" when matched then update set ";

string strSql_MRG2 =
" where X.KOUSHINBI > to_date('" + repMaxUP + "','YYYY/MM/DD HH24:MI:SS')" +
" when not matched then insert (";

string strSql_MRG3 = " values (";

foreach (DataColumn dc in dt.Columns)
{
switch (colNum)
{
case 1:
if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1)
{
Console.WriteLine(i + ":" + dc.ColumnName + " ON句条件は更新不可");

if (i == dt.Columns.Count)
{
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName;
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName;
}
else
{
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName + ",";
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName + ",";
}
}
else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...");

if (i == dt.Columns.Count)
{
strSql_MRG = strSql_MRG + "R." + dc.ColumnName + " = X." + dc.ColumnName;
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName;
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName;
}
else
{
strSql_MRG = strSql_MRG + "R." + dc.ColumnName + " = X." + dc.ColumnName + ",";
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName + ",";
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName + ",";
}
}
break;

case 2:
if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1 || dc.ColumnName == col2)
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新不可\r\n");
}
else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");
}
break;
}
i++; //次の列へ
}

strSql = strSql_MRG + strSql_MRG2 + ")" + strSql_MRG3 + ")";
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);

//日付(KOUSHINBI)を更新
//strSql_MRG =
// "merge into " + repTN +
// " using " + xbbTN +
// " on (" + whereON + ")" +
// " when matched then update set" +
// " R.KOUSHINBI = X.KOUSHINBI" +
// " where R.KOUSHINBI between " +
// "to_date('" + repMaxUP + "', 'YYYY/MM/DD HH24:MI:SS')" +
// " and " +
// "to_date('" +xbbMaxUP + "', 'YYYY/MM/DD HH24:MI:SS')";

strSql_MRG =
"merge into " + repTN +
" using " + xbbTN + " on (" + whereON + ")" +
" when matched then update set" +
" R.KOUSHINBI = X.KOUSHINBI" +
" where X.KOUSHINBI " +
"> to_date('" + repMaxUP + "', 'YYYY/MM/DD HH24:MI:SS')";

strSql = strSql_MRG;
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);

Console.WriteLine("完了\r\n");
//}

//else
//{
// Console.WriteLine("更新なし\r\n");
//}
}

public void DEL(string repTN, string xbbTN, int chkCNT, string whereDEL_R, string whereDEL_X)
{
DataTable dt = new DataTable();
int i = 1;

Console.WriteLine("■削除行の確認...");

//差分抽出
//dt = TableMinus();

strSql_DEL =
"select count(*) from " + repTN +
" where (" + whereDEL_R + ")" +
" not in (select " + whereDEL_X + " from " + xbbTN + ")";

strSql = strSql_DEL;
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);

if (dt.Rows[0][0].ToString() == "0")
{
Console.WriteLine("結果：削除なし\r\n");
}
else
{
Console.WriteLine("結果：delete実行...\r\n");

strSql_DEL =
"delete from " + repTN +
" where (" + whereDEL_R + ")" +
" not in (select " + whereDEL_X + " from " + xbbTN + ")";

strSql = strSql_DEL;
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);
}
}