// проверка выполненных процессов
public void CheckComplitedProcess()
{
string sql = String.Format("SELECT ID_PROCESS FROM PROCESSES AS T WHERE (T.DATEOFCOMPLETION IS NULL)",
_params.Department);
MySqlCommand commandMySQL = new MySqlCommand(sql, connectionMySQL);
using (MySqlDataReader readerMySQL = commandMySQL.ExecuteReader())
{
if (readerMySQL.HasRows)
{
while (readerMySQL.Read())
{
long id_processMySQL = readerMySQL.GetInt64(0);
//теперь по этому значению вытаскиваем данные из БД ПФР
OneProcess processMySQL = new OneProcess();
//и записываем в класс
processMySQL = GetProcessFromDB2(id_processMySQL);
// обновляем данные в БД ИнфоЦентра
if (processMySQL.Id_Process > 0)
{
UpdateMySQLProcess(processMySQL);
}
}
}
}
}
private void UpdateMySQLProcess(OneProcess pr)
{
string ss = pr.Dateofcomletion.ToString(DATEFORMAT);
string sql = String.Format(
"UPDATE PROCESSES SET DATEOFCOMPLETION = '{0}', ID_STATUS = {1}, ID_TYPE_PROCESS = {2} WHERE ID_PROCESS = {3}",
pr.Dateofcomletion.ToString(DATEFORMAT),
pr.id_Status,
pr.id_Type_process,
pr.Id_Process);
try
{
//
string conn = string.Format("Database={0};Data Source={1};User Id={2};Password={3}",
_params.msqlDatabase, _params.msqlHost, _params.msqlUser, _params.msqlPassword);
MySqlConnection newConnMySQL = new MySqlConnection(conn);
//
MySqlCommand commandMySQL = new MySqlCommand(sql, newConnMySQL);
commandMySQL.Connection.Open();
var i = commandMySQL.ExecuteNonQuery();
if (i > 0)
{
Log.wi(DateTime.Now, "Обновляем процесс в ИЦ ", pr.Id_Process.ToString());
}
}
catch (Exception e)
{
//Console.WriteLine(e);
}
}
private OneProcess GetProcessFromDB2(long idProcessMySql)
{
OneProcess process = new OneProcess();
try
{
string sql = String.Format("SELECT * FROM DB2ADMIN.PROCESSES AS T WHERE T.ID_PROCESS = {0}", idProcessMySql);
OleDbCommand commandDb2 = new OleDbCommand(sql, connectionDb2);
try
{
using (OleDbDataReader readerDb2 = commandDb2.ExecuteReader())
{
if (readerDb2.HasRows)
{
if (readerDb2.Read())
{
process.Id_Process = readerDb2.GetInt64(0);
process.Dateofcomming = readerDb2.GetDateTime(1);
if (!readerDb2.IsDBNull(2))
{
process.Dateofcomletion = readerDb2.GetDateTime(2);
process.id_Status = readerDb2.GetInt16(3);
process.id_Type_process = readerDb2.GetInt16(4);
process.id_Department = readerDb2.GetInt16(5);
}
else
{
process.Id_Process = 0;
}
}
}
}
}
catch (Exception e)
{
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(process);
}
public int InsertNewProcessFromDate(long lastDateIc, int iday)
{
int alladd = 0;
string nnn = "NULL";
string sql;
int allAdded = 0;
OneProcess oneProcess = new OneProcess();
DateTime dtfrom = new DateTime(lastDateIc);
DateTime dtto = dtfrom.AddDays(iday);
string sDateFrom = dtfrom.ToString("yyyy-MM-dd hh:mm:ss.fff");
string sDateTo = dtto.ToString("yyyy-MM-dd hh:mm:ss.fff");
if (connectionDb2 != null)
{
OleDbCommand commandDB2 = new OleDbCommand();
;
string commandString =
string.Format(
"SELECT * FROM DB2ADMIN.PROCESSES AS T WHERE (DATE(T.DATEOFCOMMING)>='{0}')AND(DATE(T.DATEOFCOMMING)<='{1}')AND(T.ID_DEPARTMENT={2})",
sDateFrom, sDateTo, _params.Department);
commandDB2.CommandText = commandString;
commandDB2.Connection = connectionDb2;
OleDbDataReader readerDB2 = commandDB2.ExecuteReader();
if (readerDB2.HasRows) // выбраны новые данные из БД ИЦ
{
while (readerDB2.Read())
{
try
{
oneProcess.Id_Process = readerDB2.GetInt64(0);
oneProcess.Dateofcomming = readerDB2.GetDateTime(1);
if (!readerDB2.IsDBNull(2))
{
oneProcess.Dateofcomletion = readerDB2.GetDateTime(2);
nnn = oneProcess.Dateofcomletion.ToString(DATEFORMAT);
}
else
{
nnn = "NULL";
}
if (!readerDB2.IsDBNull(3))
{
oneProcess.id_Status = readerDB2.GetInt32(3);
}
if (!readerDB2.IsDBNull(4))
{
oneProcess.id_Type_process = readerDB2.GetInt32(4);
}
oneProcess.id_Department = readerDB2.GetInt32(5);
}
catch (Exception e)
{
Log.we(DateTime.Now, "Добавление данных PROCESSES в ИнфоЦентр", e.Message);
}
if (nnn != "NULL")
{
sql = string.Format(
"INSERT INTO PROCESSES (ID_PROCESS, DATEOFCOMMING, DATEOFCOMPLETION, ID_STATUS, ID_TYPE_PROCESS, ID_DEPARTMENT)" +
"VALUES({0},'{1}','{2}',{3},{4},{5});", oneProcess.Id_Process,
oneProcess.Dateofcomming.ToString(DATEFORMAT), nnn,
oneProcess.id_Status, oneProcess.id_Type_process, oneProcess.id_Department);
}
else
{
//вариант запроса с пустым значением даты
sql = string.Format(
"INSERT INTO PROCESSES (ID_PROCESS, DATEOFCOMMING, DATEOFCOMPLETION, ID_STATUS, ID_TYPE_PROCESS, ID_DEPARTMENT)" +
"VALUES({0},'{1}',{2},{3},{4},{5});", oneProcess.Id_Process, oneProcess.Dateofcomming.ToString(DATEFORMAT),
nnn,
oneProcess.id_Status, oneProcess.id_Type_process, oneProcess.id_Department);
}
//подготовим вставку в БД Инфоцентра
MySqlCommand commandMySQL = new MySqlCommand(sql, connectionMySQL);
//добавляем запись
try
{
alladd = commandMySQL.ExecuteNonQuery();
allAdded += alladd;
}
catch (Exception e)
{
//Log.we(DateTime.Now, "Запись в БД Инфоцентр", e.Message);
}
} //цикл по найденым в ПФР
Log.wi(DateTime.Now, "Новые процессы", i: string.Format("Добавлено:{0}", allAdded));
// если все-таки ничего не добавлено, то счетчик даты увеличиваем на день
if (allAdded <= 0)
{
lastDateIc = IncrementDate(lastDateIc, iday);
SaveLastDate(lastDateIc, TableType.Process);
}
}
else /*в БД ИЦ ничего не нашлось*/
{
Log.wi(DateTime.Now, "Выборка данных из БД ПФР",
i: string.Format("За {0} + 1день ничего не нашлось", new DateTime(lastDateIc).ToString(DATEFORMAT)));
//если в БД ИЦ нет выбраны данные то счетчик даты увеличиваем на день, что-бы стартовать потом со след. даты
lastDateIc = IncrementDate(lastDateIc, iday);
SaveLastDate(lastDateIc, TableType.Process);
}
} //if connection
return(allAdded);
}