public static void ExecNonQuerySp(string inName, OracleParameter[] inParms)
{
using (var vav = new VAVEntities())
{
vav.Database.Connection.Open();
DbCommand cmd = vav.Database.Connection.CreateCommand();
cmd.CommandText = inName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(inParms);
cmd.ExecuteNonQuery();
}
}
/*empty table Iss_def */
public static string Delete_Iss_Def()
{
var result = "Success";
using (var Db = new VAVEntities())
{
try
{
Db.Database.Connection.Open();
DbCommand cmd = Db.Database.Connection.CreateCommand();
cmd.CommandText = "vav.DeleteIssDef";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
OracleParameter outPar = new OracleParameter();
outPar.ParameterName = "@Result";
outPar.DbType = DbType.String;
outPar.Size = 400;
outPar.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outPar);
cmd.ExecuteNonQuery();
var ret = (string)cmd.Parameters["@Result"].Value;
result = ret.Split('!').FirstOrDefault() == "Success" ? "Success" : "Fail";
}
catch (Exception ex)
{
result = "Fail" + ex.Message;
}
finally
{
Db.Database.Connection.Close();
}
return(result);
}
}
/// <summary>
/// Sync data from GeniusDB and hosted in .144 which is the transfer DB
/// </summary>
/// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/>
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
@"config\Sync-SdLocalRefinery.xml");
var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath));
// Set Connection string
settingManager.Init(string.Empty);//初始化 mapping
strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", settingManager.SourceDb.Type,
settingManager.SourceDb.Conn);
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", settingManager.DestinationDb.Type,
settingManager.DestinationDb.Conn);
var lastSyncTime = new DateTime(1900, 1, 1);
var currentSyncTime = startTime.AddHours(8);
using (var vavEntities = new VAVEntities())
{
var date =
vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value.AddHours(-0.5);
}
}
try
{
using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime))
{
dataSync.PostTaskExecuted +=
(sender, e) =>
strInfo.AppendFormat
("Post sync task {0} is executed.\n",
e.TaskName);
dataSync.Init();
var sourceTable = settingManager.SourceTableName.Split(',');
strInfo.Append(dataSync.SyncEachTableFromMaxMtime(sourceTable, "CreateDate"));
//strInfo.Append(dataSync.Sync(sourceTable));
}
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
logEntity.ENDTIME = DateTime.UtcNow;
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
/// <summary>
/// Sync china local data from sql server to oracle
/// </summary>
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.Now;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
@"config\China-local-sync.xml");
var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath));
// Set Connection string
settingManager.Init(string.Empty);//初始化 mapping
strInfo.AppendFormat("Bond-misc-sync.xml data Sync begin at {0}(System Time)\n", DateTime.Now);
strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", settingManager.SourceDb.Type,
settingManager.SourceDb.Conn);
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", settingManager.DestinationDb.Type,
settingManager.DestinationDb.Conn);
var lastSyncTime = new DateTime(1900, 1, 1);
var currentSyncTime = DateTime.Now;
using (var vavEntities = new VAVEntities())
{
var date =
vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value.AddHours(-1.5);
}
}
strInfo.AppendFormat("Last successfully sync time : {0}(System Time).\n", lastSyncTime);
try
{
using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime))
{
dataSync.TableSynched +=
(sender, e) =>
strInfo.AppendFormat
("{0} rows have been synchronized from {1} to {2}.\n",
e.NumOfRowsSynched, e.Source, e.Dest);
dataSync.PostTaskExecuted +=
(sender, e) =>
strInfo.AppendFormat
("Post sync task {0} is executed.\n",
e.TaskName);
dataSync.Init();
var sourceTable = settingManager.SourceTableName.Split(',');
dataSync.Sync(sourceTable);
}
strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count());
var endTime = DateTime.Now;
strInfo.AppendFormat("Synchronization completed at {0}.\n", endTime);
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
logEntity.ENDTIME = DateTime.Now;
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
/// <summary>
/// Sync data from GeniusDB and hosted in .144 which is the transfer DB
/// </summary>
/// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/>
protected override void ExecuteInternal(JobExecutionContext context)
{
if (DateTime.Now.Hour < 10 || DateTime.Now.Hour > 17)
{
return;
}
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
var lastSyncTime = new DateTime(2017, 06, 1);
using (var vavEntities = new VAVEntities())
{
var date =
vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value.AddHours(8);
}
}
try
{
string localBondCon = ConfigurationManager.AppSettings["143FileDB"];
var ds = new DataSet();
strInfo.Append("Query 143DB assertID\n");
using (OracleConnection conn = new OracleConnection(localBondCon))
{
conn.Open();
OracleDataAdapter command = new OracleDataAdapter("SELECT ASSETID FROM BONDDELETE where ISVALID=1 and UPDATE_TIME>'" + lastSyncTime.ToString("dd-MMM-yyyy hh:mm:ss tt") + "'", conn);
command.Fill(ds, "ds");
conn.Close();
if (ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0)
{
return;
}
strInfo.Append("Need to delete assertID:" + ds.Tables[0].Rows.Count + " \n");
}
string proBondCon = ConfigurationManager.AppSettings["ProBondDB"];
using (OracleConnection conn = new OracleConnection(proBondCon))
{
strInfo.Append("Delete 143DB assertID:\n");
conn.Open();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strInfo.Append("ASSETID=" + ds.Tables[0].Rows[i][0] + ": \n");
int result;
string sql = "delete GOVCORP_ASSET where ASSETID='" + ds.Tables[0].Rows[i][0] + "'";
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
result = cmd.ExecuteNonQuery();
}
strInfo.Append("GOVCORP_ASSET " + result + " rows deleted. ");
sql = "delete EJVBOND where ASSET_ID='" + ds.Tables[0].Rows[i][0] + "'";
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
result = cmd.ExecuteNonQuery();
}
strInfo.Append("EJVBOND " + result + " rows deleted.\n\n");
}
conn.Close();
strInfo.Append("Completed!\n");
}
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
logEntity.ENDTIME = DateTime.UtcNow;
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
/// <summary>
/// Execute the actual job. The job data map will already have been
/// applied as object property values by execute. The contract is
/// exactly the same as for the standard Quartz execute method.
/// </summary>
/// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/>
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime, JOBTYPE = JobType
};
var strInfo = new StringBuilder();
string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
@"config\dsos-new-increment-sync.xml");
var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath));
// Set Connection string
//var entityConnectionString =
// ConfigurationManager.ConnectionStrings["VAVEntities"].ConnectionString;
//var connectionStringBuilder = new EntityConnectionStringBuilder(entityConnectionString);
//var DestinationDbConn = connectionStringBuilder.ProviderConnectionString;
settingManager.Init(string.Empty);//初始化 mapping
strInfo.AppendFormat("DSOS Sync begin at {0}\n", startTime);
strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", settingManager.SourceDb.Type,
settingManager.SourceDb.Conn);
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", settingManager.DestinationDb.Type,
settingManager.DestinationDb.Conn);
// Get Last successfully date time.
var lastSyncTime = new DateTime(2015, 3, 1);
using (var vavEntities = new VAVEntities())
{
var date =
vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value;
//Add Buffer
lastSyncTime = lastSyncTime.AddHours(-1.5);
}
}
strInfo.AppendFormat("Last successfully sync time : {0}(Eastern Standard Time).\n", startTime);
try
{
List <string> tables;
//if (hour >= 12 && hour <= 14) //sync iss_def
//{
// //Delete iss_def
// strInfo.AppendFormat("Delete iss_def at {0}.\n", DateTime.UtcNow.ToGMT8String());
// var re = SyncUtil.Delete_Iss_Def();
// strInfo.AppendFormat("Delete iss_def at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), re);
// tables = GetDsosSrcTablesToSync().ToList();
//}
//else
//{
// tables = GetDsosSrcTablesToSync().ToList();
// var s = tables.Single(t => t == "govcorp..iss_def");
// tables.Remove(s);
//}
tables = settingManager.TableMappings.Select(t => t.Source).ToList();
using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, startTime))
{
dataSync.TableSynched +=
(sender, e) =>
strInfo.AppendFormat
("{0} rows have been synchronized from {1} view in DSOS to {2} table in VAV DB.\n",
e.NumOfRowsSynched, e.Source, e.Dest);
dataSync.PostTaskExecuted +=
(sender, e) =>
strInfo.AppendFormat
("Post sync task {0} is executed.\n",
e.TaskName);
dataSync.Init();
dataSync.Sync(tables);
}
strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count());
strInfo.AppendFormat("Synchronization completed at {0}.\n", DateTime.UtcNow.ToGMT8String());
//Update Bond Info
strInfo.AppendFormat("Update Bond Info en&cn start at {0}.\n", DateTime.UtcNow.ToGMT8String());
//var result = SyncUtil.UpdateBondInfo(lastSyncTime, currentSyncTime);
var endTime = DateTime.UtcNow;
//strInfo.AppendFormat("Update Bond Info en&cn completed at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), result);
////Rebuild Index
//strInfo.AppendFormat("Rebuild Index at {0}.\n", DateTime.UtcNow.ToGMT8String());
//var result1 = SolrClient.RebuildIndex("full");
//strInfo.AppendFormat("Rebuild Index completed at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), result1);
logEntity.ENDTIME = endTime;
//logEntity.JobStatus = (result == "Success" && result1 == "Success") ? JobStatus.Success : JobStatus.Fail;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
logEntity.ENDTIME = DateTime.UtcNow;
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
