public void ImportDataTable(Cells c, out int total)
{
DataTable datatable = ImportWorkSheet(c);
total = datatable.Rows.Count;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
foreach (DataRow dr in datatable.Rows)
{
string sql1 = CreateInsertSql(dr);
using (OracleCommand cmd = new OracleCommand(sql1, conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
conn.Close();
throw;
}
}
}
public void ExecuteMax()
{
try
{
using (conn = new OracleConnection(ConfigurationManager.AppSettings["mergeData"]))
{
conn.Open();
tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxChemicalIndustryTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyGasTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyOilTable";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public static void ExecCinema_MT(int attid)
{
Console.WriteLine(DateTime.Now.ToString(_formatString));
//OracleConnection conn, OracleTransaction tran
string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl)));User Id=settle;Password=settle;";
OracleConnection conn = new OracleConnection(conStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
var paramArray = new[]
{
new OracleParameter("@p_attachmentId", OracleDbType.Int32)
{
Value = attid
},
//new OracleParameter("@QueryDateType", OracleDbType.Varchar2) { Value = queryDateType },
//new OracleParameter("@OrgType", OracleDbType.Varchar2) { Value = orgType },
//new OracleParameter("@TrustType", OracleDbType.NVarchar2) { Value = trustType },
// new OracleParameter("@Total", OracleDbType.Int32,ParameterDirection.Output),
//new OracleParameter("@cur",OracleDbType.RefCursor,ParameterDirection.Output)
};
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CHECKCinema_MT_BYattID";
cmd.CommandTimeout = 90;
cmd.Parameters.AddRange(paramArray);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ee)
{
tran.Rollback();
Console.WriteLine(ee.Message);
}
finally {
conn.Close();
}
Console.WriteLine(DateTime.Now.ToString(_formatString));
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string SQL = SQLStringList[i].ToString();//獲取SQL語句
if (SQL.Trim().Length > 1)
{
cmd.CommandText = SQL;
num = cmd.ExecuteNonQuery();
}
tx.Commit(); //提交事務
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
}
return(num); //返回執行結果數量
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();//事務回滾
throw new Exception(E.Message);
}
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
string connStr = ConfigurationManager.AppSettings["mergeData"];
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
strInfo.AppendFormat("Source [Email:{0},Title:{1}]\n", ConfigurationManager.AppSettings["LongZhongSenderFilter"], ConfigurationManager.AppSettings["LongZhongTitleFilter"]);
strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr);
var lastSyncTime = Convert.ToDateTime("2016-11-16");
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
//ToGMT8
lastSyncTime = date.Value.AddHours(8);
}
}
try
{
#region 执行数据同步程序
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"];
string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"];
string attFetcherSavingPath = ConfigurationManager.AppSettings["LongZhongSavingPath"];
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
/*Filter example. If there are more filters , please write like below.*/
string[] senders = ConfigurationManager.AppSettings["LongZhongSenderFilter"].Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
var attFetcherTitleFilter = new TitleFilter();
var titleRegex = new Regex(ConfigurationManager.AppSettings["LongZhongTitleFilter"], RegexOptions.Compiled);
attFetcherTitleFilter.SetRule(titleRegex);
attFetcher.AppendFilter(attFetcherTitleFilter);
attFetcher.Execute(lastSyncTime);
/*Get the saved attachment names.*/
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
//List<string> attachmentFileNames = Directory.GetFiles(@"C:\DataFeedApp\Scheduler\CnE\LongZhong").ToList<string>();
foreach (var tempattachname in attachmentFileNames)
{
LongZhongExcelManager manager = new LongZhongExcelManager();
manager.GetCellsByFirstSheet(tempattachname, strInfo);
}
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
if (attachmentFileNames.Count > 0)
{
MergeData merge = new MergeData();
merge.ExecuteLongZhongExcel(conn, tran);
tran.Commit();
strInfo.Append("Execute Procedure : CREATEMAXENERGYYIELDTABLE, CREATEMAXCHEMISTRYOUTPUTTABLE \r\n");
strInfo.Append("Insert Into Table : GDT_CHEMISTRYOUTPUTMAX, GDT_ENERGYYIELDMAX \r\n");
}
else
{
strInfo.Append("No files found\r\n");
}
#endregion
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
//merge data from temp to persistence
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
conn.Dispose();
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
var strInfo = new StringBuilder();
#region 执行数据同步程序
if (DateTime.Now.Hour <= 15 || DateTime.Now.Hour >= 22)
{
strInfo.Append("Only this time(15:00Am-10:00Pm) point synchronous;");
return;
}
#endregion
string connStr = ConfigurationManager.AppSettings["mergeData"].ToString();
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n" + "\r\n", connStr);
var lastSyncTime = new DateTime(2016, 9, 02);
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
//ToGMT8
lastSyncTime = date.Value.AddHours(8);
}
}
try
{
#region 执行数据同步程序
var ch = new ChinaJci();
var sbSync = new StringBuilder();
ch.SyncChinaJciData(ref sbSync, lastSyncTime.ToString("yyyy-MM-dd"));
strInfo.AppendFormat(sbSync.ToString());
#endregion
MergeData merge = new MergeData();
merge.Execute(conn, tran);
tran.Commit();
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
}
}
private void IQCReCheck(DataTable dtpub)
{
int m = 0;
string states = "";
if (checktype == "NGToOK")
{
states = "OK";
}
else if (checktype == "OKToNG")
{
states = "NG";
}
else if (checktype == "NGTo特采")
{
states = "OK";
}
string remarks = "", noteid = "";
if (checktype == "NGTo特采")
{
remarks = txtnoteid.Text + checktype + txtreason.Text;
noteid = txtnoteid.Text;
}
else
{
remarks = checktype + txtreason.Text;
}
DataTable dt = dtpub;
DataTable dtalready = new DataTable();
SqlConnection conn = new SqlConnection(DbAccess.connSql);
//OracleConnection orac = new OracleConnection(DbAccess.connOral);
Oracle.ManagedDataAccess.Client.OracleConnection orac = new Oracle.ManagedDataAccess.Client.OracleConnection(DbAccess.connOral);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (orac.State == ConnectionState.Closed)
{
orac.Open();
}
SqlTransaction tran1 = conn.BeginTransaction();
//OracleTransaction oratran = orac.BeginTransaction();
Oracle.ManagedDataAccess.Client.OracleTransaction oratran = orac.BeginTransaction();
try
{
Int32 org_id = 0, item_id = 0, tran_id = 0;
Int64 recepid = 0;
string org_idnew = "";
int instockqty = 0;
int restenableqty = int.Parse(txtqty.Text);
string msg = "", barcodemsg = "", msgcurrent = "", barcodemsgcurrent = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
int alreadyqty = 0;
org_idnew = dt.Rows[j]["ORGANIZATION_CODE"].ToString();
org_id = Int32.Parse(dt.Rows[j]["ship_to_org_id"].ToString());
item_id = Int32.Parse(dt.Rows[j]["INVENTORY_ITEM_ID"].ToString());
tran_id = Int32.Parse(dt.Rows[j]["transaction_id"].ToString());
//recepid = Int32.Parse(dt.Rows[0]["receipt_num"].ToString());
recepid = Int64.Parse(dt.Rows[0]["receipt_num"].ToString());
//判断当前行可检验数量大于界面录入数量,则把所有数量录入到第一笔.
if (int.Parse(dt.Rows[j]["primary_quantity"].ToString()) >= restenableqty)
{
//则把用户输入的检验数量作为此行的检验数
instockqty = restenableqty;
//20150312新增
//x表示还在接口表中的数量
//int x = ERPinterface(tran_id.ToString());
//用临时变量记录此次操作数量
int x = alreadyqty;
//y表示还可处理的数量
int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x;
if (y >= restenableqty)
{
instockqty = restenableqty;
}
else if (y > 0 && y < restenableqty)
{
instockqty = y;
}
else
{
continue;
}
//20150312新增
barcodemsg = barcodemsg + insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, Int32.Parse(dt.Rows[j]["primary_quantity"].ToString()),
Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1);
if (barcodemsg.IndexOf("成功") >= 0)
{
if (states == "OK")
{
msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
else
{
msg = msg + msgcurrent;
txtqty.Text = "0";
break;
}
}
else if (states == "NG")
{
msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
else
{
msg = msg + msgcurrent;
txtqty.Text = "0";
break;
}
}
}
else
{
//防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔
continue;
}
}
//如果当前行的数量小于录入数量,则把当前行先进行检验通过该行的数量,同时把总共要检验的数量-第一笔已录的=作为剩下的可录入数量，再进行下一次循环
else
{
instockqty = int.Parse(dt.Rows[j]["primary_quantity"].ToString());
//20150312新增
//x表示还在接口表中的数量
//int x = ERPinterface(tran_id.ToString());
//用临时变量记录此次操作数量
try
{
DataRow[] rwalready = dtalready.Select(" id='" + tran_id + "'");
if (rwalready.Length > 0)
{
for (int z = 0; z < rwalready.Length; z++)
{
alreadyqty = alreadyqty + int.Parse(rwalready[z]["qty"].ToString());
}
}
}
catch
{
dtalready.Columns.Add("id", typeof(string));
dtalready.Columns.Add("qty", typeof(int));
}
int x = alreadyqty;
//y表示还可处理的数量
int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x;
if (y >= restenableqty)
{
instockqty = restenableqty;
}
else if (y > 0 && y < restenableqty)
{
instockqty = y;
}
else
{
continue;
}
//20150312新增
barcodemsgcurrent = insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, instockqty,
Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1);
barcodemsg = barcodemsg + barcodemsgcurrent;
if (barcodemsgcurrent.IndexOf("成功") >= 0)
{
if (states == "OK")
{
msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
msg = msg + msgcurrent;
restenableqty = restenableqty - instockqty;
txtqty.Text = restenableqty.ToString();
DataRow newRow;
newRow = dtalready.NewRow();
newRow["id"] = tran_id;
newRow["qty"] = instockqty.ToString();
dtalready.Rows.Add(newRow);
continue;
}
else if (states == "NG")
{
msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
msg = msg + msgcurrent;
restenableqty = restenableqty - instockqty;
txtqty.Text = restenableqty.ToString();
DataRow newRow;
newRow = dtalready.NewRow();
newRow["id"] = tran_id;
newRow["qty"] = instockqty.ToString();
dtalready.Rows.Add(newRow);
continue;
}
}
else
{
//防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔
continue;
}
}
}
if (msg.IndexOf("Error") >= 0)
{
this.lblinfo.ForeColor = Color.Red;
lblinfo.Text = msg + ";ERP写入失败!";
tran1.Rollback();
oratran.Rollback();
}
else if (barcodemsg.IndexOf("失败") >= 0)
{
lblinfo.ForeColor = Color.Red;
lblinfo.Text = barcodemsg + ";ERP写入失败!";
tran1.Rollback();
oratran.Rollback();
}
else
{
lblinfo.ForeColor = Color.Blue;
lblinfo.Text = "改判成功" + barcodemsg + ";" + msg;
txtqty.Text = "";
this.txtreason.Text = "";
tran1.Commit();
oratran.Commit();
}
}
catch (Exception ex)
{
lblinfo.Text = ex.ToString();
tran1.Rollback();
oratran.Rollback();
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
string connStr = ConfigurationManager.AppSettings["mergeData"];
var conn = new OracleConnection(connStr);
conn.Open();
var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var strInfo = new StringBuilder();
string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
@"config\MySteel-data-sync.xml");
var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath));
// Set Connection string
var destinationDbConn = string.Empty;
settingManager.Init(destinationDbConn);//初始化 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);
DateTime lastSyncTime;
var currentSyncTime = DateTime.Now;
using (var cmd = new OracleCommand("SELECT max(dLastAccess) FROM RTMS_TABLEDATA ", conn))
{
object obj = cmd.ExecuteScalar();
lastSyncTime = Convert.ToDateTime(obj.ToString());
strInfo.AppendFormat("Last successfully sync time : {0}.\n", obj);
}
try
{
using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime))
{
dataSync.TableSynched +=
(sender, e) =>
strInfo.AppendFormat
("{0} rows have been synchronized from {1} table in SYNC_REUTERS DB to {2} table in CnE DB.\n",
e.NumOfRowsSynched, e.Source, e.Dest);
dataSync.Init();
var sourceTable = settingManager.SourceTableName.Split(',');
dataSync.Sync(sourceTable);
}
strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count());
var merge = new MergeData();
merge.ExecuteMetals(conn, tran);
tran.Commit();
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
var connStr = ConfigurationManager.AppSettings["mergeData"];
var conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
strInfo.AppendFormat("Source [Email:{0}]\n", ConfigurationManager.AppSettings["CoffedSenderFilter"]);
strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr);
strInfo.AppendFormat("Destination Table:Cofeed\n" + "\r\n");
var lastSyncTime = Convert.ToDateTime("2016-09-03");
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
//ToGMT8
lastSyncTime = date.Value.AddHours(8);
}
}
try
{
StringBuilder sb = new StringBuilder();
#region 执行数据同步程序
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"];
string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"];
string attFetcherSavingPath = ConfigurationManager.AppSettings["CoffedSavingPath"];
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
string[] senders = ConfigurationManager.AppSettings["CoffedSenderFilter"].Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
var attFetcherTitleFilter = new TitleFilter();
var titleRegex = new Regex(ConfigurationManager.AppSettings["CoffedTitleFilter"], RegexOptions.Compiled);
attFetcherTitleFilter.SetRule(titleRegex);
attFetcher.AppendFilter(attFetcherTitleFilter);
attFetcher.Execute(lastSyncTime);
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
foreach (var tempattachname in attachmentFileNames)
{
CofeedManager manager = new CofeedManager();
manager.GetCellsByFirstSheet(tempattachname, sb);
}
strInfo.Append(sb);
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
#endregion
if (attachmentFileNames.Count > 0)
{
MergeData merge = new MergeData();
merge.Execute(conn, tran);
tran.Commit();
strInfo.Append("Execute Procedure : createMaxAgricultrueTable \r\n");
strInfo.Append("Insert Into Table : GDT_AgricultureMax \r\n");
}
else
{
strInfo.Append("No files found\r\n");
}
var settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
@"config\Ftp-Cofeed-data-sync.xml");
var settingManager = new FtpSyncXmlManager(File.ReadAllText(settingFilePath), lastSyncTime, startTime.AddHours(8));
settingManager.Init();
var ftpSync = new FtpSyncLoad(settingManager, strInfo);
ftpSync.Excute();
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OracleClient.OracleConnection orlConnection = null;
OracleClient.OracleTransaction orlTransaction = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Start a new transaction.
orlTransaction = orlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = orlTransaction;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
orlTransaction.Commit();
// Close the database connection.
orlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (orlTransaction != null)
{
orlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
protected override void ExecuteInternal(Quartz.JobExecutionContext context)
{
string connStr = ConfigurationManager.AppSettings["mergeData"].ToString();
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
strInfo.AppendFormat("Source [Email:{0}]\n", ConfigurationManager.AppSettings["CoffedProductSenderFilter"]);
strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr);
var lastSyncTime = Convert.ToDateTime("2016-11-14");
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value.AddMinutes(-10);
}
}
try
{
#region 执行数据同步程序
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"];
string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"];
string attFetcherSavingPath = ConfigurationManager.AppSettings["CoffedProductSavingPath"];
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
string[] senders = ConfigurationManager.AppSettings["CoffedProductSenderFilter"].Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
attFetcher.Execute(lastSyncTime);
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
// List<string> attachmentFileNames = Directory.GetFiles(@"C:\DataFeedApp\Scheduler\CnE\CoffedProductData").ToList<string>();
for (int i = attachmentFileNames.Count - 1; i >= 0; i--)
{
var tempattachname = attachmentFileNames[i];
if (tempattachname.IndexOf("Productinventory") != -1)
{
CofeedProductManager c = new CofeedProductManager();
c.GetCellsByFirstSheet(tempattachname, strInfo);
}
else if (tempattachname.IndexOf("week") != -1)
{
CoffedWeekManager c = new CoffedWeekManager();
c.GetCellsByFirstSheet(tempattachname, strInfo);
}
}
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
if (attachmentFileNames.Count > 0)
{
MergeData m = new MergeData();
m.ExecuteCoffedAndMetalMax(conn, tran);
strInfo.Append("Execute Procedure : CREATEMAXAGRICULTRUEOUTPUT\r\n");
strInfo.Append("Insert Into Table : GDT_AGRICULTRUEOUTPUTMAX, GDT_ENERGYYIELDMAX \r\n");
//生成MAX表；
//ExecuteCoffedAndMetalMax
tran.Commit();
}
else
{
strInfo.Append("No files found\r\n");
}
;
#endregion
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
}
public static void ExcuteMailSync(string jobType, DateTime syncStartTime, string attFetcherSavingPath, string senderFilter, string titleFilter, string userName, string password
, Action <SCHEDULERLOG> saveLog
, Action <List <string>, StringBuilder> syncCallback
, Action <OracleConnection, OracleTransaction> mergeCallback
)
{
string connStr = ConfigurationManager.AppSettings["mergeData"];
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
var lastSyncTime = syncStartTime;
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == jobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value;
}
}
try
{
#region 执行数据同步程序
strInfo.AppendFormat("Source [Type: Email , Sender: {0} ,Title {1} ]\n",
senderFilter, titleFilter);
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", "Oracle",
connStr);
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = userName;
string attFetcherPassWord = password;
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
/*Filter example. If there are more filters , please write like below.*/
string[] senders = senderFilter.Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
if (!string.IsNullOrEmpty(titleFilter))
{
var attFetcherTitleFilter = new TitleFilter();
var titleRegex = new Regex(titleFilter, RegexOptions.Compiled);
attFetcherTitleFilter.SetRule(titleRegex);
attFetcher.AppendFilter(attFetcherTitleFilter);
}
attFetcher.Execute(lastSyncTime);
/*Get the saved attachment names.*/
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
//var attachmentFileNames=new List<string>();
//attachmentFileNames.Add(@"C:\DataFeedApp\Scheduler\CnE\路透数据20161109.xls");
StringBuilder sb = new StringBuilder();
//var attachmentFileNames = Directory.GetFiles(@"C:\xx\troil").ToList<string>();
syncCallback(attachmentFileNames, sb);
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
if (attachmentFileNames.Count == 0)
{
strInfo.Append("No find files .\r\n");
}
#endregion
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo + sb.ToString();
saveLog(logEntity);
//merge data from temp to persistence
if (mergeCallback != null)
{
mergeCallback(conn, tran);
}
tran.Commit();
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
saveLog(logEntity);
}
finally
{
conn.Close();
conn.Dispose();
}
}