private void RemoveLink()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = $@"DROP PUBLIC DATABASE LINK ""{AppConfig.OracleRemoteLinkName}"""
});
}
private void CompilePkg()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = "ALTER PACKAGE ABT.PKG_UPDATE COMPILE SPECIFICATION"
});
}
private void CompilePkgBody()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = "ALTER PACKAGE ABT.PKG_UPDATE COMPILE BODY"
});
}
private void RemovePkg()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = "DROP PACKAGE ABT.PKG_UPDATE"
});
}
void Process(string signature, string obj, string comment)
{
msg($"[{DateFormat( DateTime.Now )}] {obj} обрабатывается...", logList);
var start_time = DateTime.Now;
List <OracleCommand> sqls = new List <OracleCommand>();
sqls.Add(new OracleCommand()
{
CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" SET {obj}=0 WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'",
});
sqls.Add(new OracleCommand()
{
CommandType = CommandType.StoredProcedure,
CommandText = $"ABT.ABT_REPLICATION.PROCESS_" + (obj == "PRICES_CACHE" ? "PRICES" : obj)
});
OracleDbService.ExecuteCommand(sqls);
sqls.Clear();
var time = Math.Round((DateTime.Now - start_time).TotalDays * 24 * 60 * 60).ToString();
sqls.Add(new OracleCommand()
{
CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" " +
$@"SET {obj}={time} WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'",
});
OracleDbService.ExecuteCommand(sqls);
msg($"[{DateFormat( DateTime.Now )}]{obj} обработано на {signature} за {time} сек", logList);
progressBar.Invoke(new Action(() => { progressBar.Value += 1; }));
}
void Download(string signature, string obj, string comment)
{
msg($"[{DateFormat( DateTime.Now )}] {obj} скачивается...", logList);
var start_time = DateTime.Now;
List <OracleCommand> sqls = new List <OracleCommand>();
sqls.Add(new OracleCommand()
{
CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" SET {obj}=0 WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'",
});
sqls.Add(new OracleCommand()
{
CommandType = CommandType.StoredProcedure,
CommandText = $"ABT.PKG_UPDATE.{obj}"
});
OracleDbService.ExecuteCommand(sqls);
sqls.Clear();
var time = Math.Round((DateTime.Now - start_time).TotalDays * 24 * 60 * 60).ToString();
sqls.Add(new OracleCommand()
{
CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" " +
$@"SET {obj}={time} WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'",
});
OracleDbService.ExecuteCommand(sqls);
msg($"[{DateFormat( DateTime.Now )}]{obj} скачалось за {time} сек", logList);
progressBar.Invoke(new Action(() => { progressBar.Value += 1; }));
}
private void CreateLink()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = $@"CREATE PUBLIC DATABASE LINK ""{AppConfig.OracleRemoteLinkName}""
CONNECT TO {AppConfig.OracleRemoteUser} IDENTIFIED BY ""{AppConfig.OracleRemotePassword}"" USING '{AppConfig.OracleRemoteLinkTNS}'"
});
}
private void CreatePkg()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText =
"CREATE OR REPLACE PACKAGE ABT.pkg_update IS \n" +
"\tPROCEDURE BAR_CODES; \n" +
"\tPROCEDURE CONTRACTORS; \n" +
"\tPROCEDURE COUNTRIES; \n" +
"\tPROCEDURE CUSTOMERS; \n" +
"\tPROCEDURE GOODS; \n" +
"\tPROCEDURE GROUPS; \n" +
"\tPROCEDURE GROUP_ITEMS; \n" +
"\tPROCEDURE ITEMS; \n" +
"\tPROCEDURE PRICES_CACHE; \n" +
"END pkg_update; \n"
});
}
private void CreatePkgBody()
{
OracleDbService.ExecuteCommand(new OracleCommand()
{
CommandText = "CREATE OR REPLACE PACKAGE BODY pkg_update IS \n" +
"PROCEDURE BAR_CODES IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_BAR_CODES;\n" +
$" INSERT INTO ABT.IMPORT_BAR_CODES SELECT * FROM ABT.UPDATE_BAR_CODES@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END BAR_CODES;\n" +
"PROCEDURE CONTRACTORS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_CONTRACTORS;\n" +
$" INSERT INTO ABT.IMPORT_CONTRACTORS SELECT * FROM ABT.UPDATE_CONTRACTORS@\"{AppConfig.OracleRemoteLinkName}\" WHERE ID_DISTRICT IN (SELECT ID FROM REF_DISTRICTS RD); COMMIT;\n"+
"END CONTRACTORS;\n" +
"PROCEDURE COUNTRIES IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_COUNTRIES;\n" +
$" INSERT INTO ABT.IMPORT_COUNTRIES SELECT * FROM ABT.UPDATE_COUNTRIES@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END COUNTRIES;\n" +
"PROCEDURE CUSTOMERS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_CUSTOMERS;\n" +
$" INSERT INTO ABT.IMPORT_CUSTOMERS SELECT * FROM ABT.UPDATE_CUSTOMERS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END CUSTOMERS;\n" +
"PROCEDURE GOODS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_GOODS;\n" +
$" INSERT INTO ABT.IMPORT_GOODS SELECT * FROM ABT.UPDATE_GOODS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END GOODS;\n" +
"PROCEDURE GROUPS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_GROUPS;\n" +
$" INSERT INTO ABT.IMPORT_GROUPS SELECT * FROM ABT.UPDATE_GROUPS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END GROUPS;\n" +
"PROCEDURE GROUP_ITEMS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_GROUP_ITEMS;\n" +
$" INSERT INTO ABT.IMPORT_GROUP_ITEMS SELECT * FROM ABT.UPDATE_GROUP_ITEMS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END GROUP_ITEMS;\n" +
"PROCEDURE ITEMS IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_ITEMS;\n" +
$" INSERT INTO ABT.IMPORT_ITEMS SELECT * FROM ABT.UPDATE_ITEMS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+
"END ITEMS;\n" +
"PROCEDURE PRICES_CACHE IS BEGIN\n" +
" DELETE FROM ABT.IMPORT_PRICES_CACHE;\n" +
$" INSERT INTO ABT.IMPORT_PRICES_CACHE SELECT * FROM ABT.UPDATE_PRICES_CACHE@\"{AppConfig.OracleRemoteLinkName}\" WHERE ID_PRICE_TYPE IN (SELECT ID FROM REF_PRICE_TYPES); COMMIT;"+
"END PRICES_CACHE;\n" +
"END pkg_update;"
});
}
