//Работа с уже занесенной записью
/// <summary>
/// Конструктор для работы с уже занессеной записью(использовать например для удаления DataBlock
/// </summary>
/// <param name="connectionStringTMP">строка подключения</param>
/// <param name="dataBlockId">dataBlockId</param>
/// <param name="Current_Language">Язык</param>
public DataBlock(string connectionStringTMP, int dataBlockId, string Current_Language)
{
//тестовое подключение
sqlDb = new SQLDB(connectionStringTMP);
///////
SQLDB sqlDB = new SQLDB(connectionStringTMP);
int dataIdTemp = sqlDB.checkTableExistence("fn_data_block", "DATA_BLOCK_ID", dataBlockId);
if (dataIdTemp == 0)
throw (new Exception("There is no Data Block with id " + dataBlockId.ToString()));
connectionString = connectionStringTMP;
DATA_BLOCK_ID = dataBlockId;
DATA_BLOCK_ID_PREVIOUS = -1;
CurrentLanguage = Current_Language;
dataRecord = new DataRecords(connectionStringTMP, dataBlockId, Current_Language, sqlDb);
vehicleUnitInfo = new VehicleUnitInfo(connectionString, Current_Language, sqlDb);
cardUnitInfo = new CardUnitInfo(connectionString, Current_Language, sqlDb);
plfUnitInfo = new PLFUnitInfo(connectionString, Current_Language, sqlDb);
cardsTable = new CardsTable(connectionString, Current_Language, sqlDb);
organizationTable = new OrganizationTable(connectionString, Current_Language, sqlDb);
usersTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
stringTable = new StringTable(connectionString, CurrentLanguage);
vehiclesTables = new VehiclesTable(connectionString, CurrentLanguage, sqlDb);
criteriaTable = new CriteriaTable(connectionString, CurrentLanguage, sqlDb);
deviceTable = new DeviceTable(connectionString, CurrentLanguage, sqlDb);
//historyTable = new HistoryTable(connectionString, CurrentLanguage, sqlDb);
reportsTable = new ReportsTable(connectionString, CurrentLanguage, sqlDb);
invoiceTable = new InvoiceTable(connectionString, CurrentLanguage, sqlDb);
emailScheduleTable = new EmailScheduleTable(connectionString, CurrentLanguage, sqlDb);
remindTable = new RemindTable(connectionString, CurrentLanguage, sqlDb);
bannersTable = new BannersTable(connectionString, CurrentLanguage, sqlDb);
currentCardType = -1;
organizationID = 0;
}
/// <summary>
/// Загрузить файл
/// </summary>
/// <param name="dataTemp">Файл в битовом массиве</param>
/// <param name="fileName">Имя файла</param>
/// <param name="cardId">Id карты</param>
/// <returns>ID блока данных</returns>
private int AddData(byte[] dataTemp, string fileName, int cardId)
{
CRC32.Crc32 crc32 = new CRC32.Crc32();
//SQLDB sqlDB = new SQLDB(connectionString);
string bytesCount = dataTemp.Length.ToString();
crc32.ComputeHash(dataTemp);//Этот метод может иметь повторы при большом колличестве файлов. Если будут проблемы - заменить на md5
uint crc = crc32.CrcValue;
List<int> dataBlockCrcIds = new List<int>();
dataBlockCrcIds = GetDataBlockId_byCRC32(crc);
if (dataBlockCrcIds.Count > 0)
throw new Exception("Этот файл уже загружен!");
//sqlDB.OpenConnection();
int dataBlock_fileNameID = sqlDb.AddParam("DataBlock_FileName", 0, 255);
int dataBlock_bytesCount = sqlDb.AddParam("DataBlock_BytesCount", 0, 255);
int dataBlock_CRC32 = sqlDb.AddParam("DataBlock_CRC32", 0, 255);
//sqlDB.CloseConnection();
dataTemp = ZipBytes(dataTemp);//Зипуем файл для добавления в базу данных!
int dataIdTemp = sqlDb.AddDataBlock(dataTemp);
//sqlDB.OpenConnection();
sqlDb.AddDataRecord(fileName, dataIdTemp, dataBlock_fileNameID);
sqlDb.AddDataRecord(bytesCount, dataIdTemp, dataBlock_bytesCount);
sqlDb.AddDataRecord(crc.ToString(), dataIdTemp, dataBlock_CRC32);
if (cardId != -1)
sqlDb.SetDataBlock_CardId(dataIdTemp, cardId);
//sqlDB.CloseConnection();
if (dataIdTemp == -1)
{
throw (new Exception("Can't add data block from byte[]"));
}
else
{
DATA_BLOCK_ID_PREVIOUS = DATA_BLOCK_ID;
DATA_BLOCK_ID = dataIdTemp;
dataRecord = new DataRecords(connectionString, DATA_BLOCK_ID, CurrentLanguage, sqlDb);
return dataIdTemp;
}
}
public DataBlock(string connectionStringTMP, int dataBlockId, int dataRecordId, string Current_Language)
{
//тестовое подключение
sqlDb = new SQLDB(connectionStringTMP);
///////
connectionString = connectionStringTMP;
DATA_BLOCK_ID = dataBlockId;
DATA_BLOCK_ID_PREVIOUS = -1;
CurrentLanguage = Current_Language;
dataRecord = new DataRecords(connectionStringTMP, dataBlockId, dataRecordId, Current_Language, sqlDb);
vehicleUnitInfo = new VehicleUnitInfo(connectionString, Current_Language, sqlDb);
cardUnitInfo = new CardUnitInfo(connectionString, Current_Language, sqlDb);
plfUnitInfo = new PLFUnitInfo(connectionString, Current_Language, sqlDb);
cardsTable = new CardsTable(connectionString, Current_Language, sqlDb);
organizationTable = new OrganizationTable(connectionString, Current_Language, sqlDb);
usersTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
stringTable = new StringTable(connectionString, CurrentLanguage);
vehiclesTables = new VehiclesTable(connectionString, CurrentLanguage, sqlDb);
criteriaTable = new CriteriaTable(connectionString, CurrentLanguage, sqlDb);
deviceTable = new DeviceTable(connectionString, CurrentLanguage, sqlDb);
//historyTable = new HistoryTable(connectionString, CurrentLanguage, sqlDb);
reportsTable = new ReportsTable(connectionString, CurrentLanguage, sqlDb);
invoiceTable = new InvoiceTable(connectionString, CurrentLanguage, sqlDb);
emailScheduleTable = new EmailScheduleTable(connectionString, CurrentLanguage, sqlDb);
remindTable = new RemindTable(connectionString, CurrentLanguage, sqlDb);
bannersTable = new BannersTable(connectionString, CurrentLanguage, sqlDb);
currentCardType = -1;
organizationID = 0;
}
/// <summary>
/// Устанавливает, какой dataBlockId разбирать
/// </summary>
/// <param name="dataBlockId">dataBlockId</param>
public void SetDataBlockIdForParse(int dataBlockId)
{
DATA_BLOCK_ID = dataBlockId;
DATA_BLOCK_ID_PREVIOUS = -1;
dataRecord = new DataRecords(connectionString, dataBlockId, CurrentLanguage, sqlDb);
}
public void LoadAllInfo()
{
Console.WriteLine("Conerting starts");
DateTime startLoadTime = DateTime.Now;
PLFUnit.PLFUnitClass plf;
CARS cars = new CARS(fb_con);
cars.LoadAllCars();
DEPARTMENTS depar = new DEPARTMENTS(fb_con);
depar.LoadAllDepartments();
WORKERS workers = new WORKERS(fb_con);
workers.LoadAllWorkers();
DEVICES devices = new DEVICES(fb_con);
devices.LoadAllDevices();
READCYCLE readcycle = new READCYCLE(fb_con);
readcycle.LoadAllReadCycles();
DataBlock datablock = new DataBlock(connectionStringMysql, currentLanguage);
try
{
List<KeyValuePair<int, int>> orgOldNewIds = new List<KeyValuePair<int, int>>();
List<KeyValuePair<int, int>> workOldNewIds = new List<KeyValuePair<int, int>>();
List<KeyValuePair<int, int>> carsOldNewIds = new List<KeyValuePair<int, int>>();
List<KeyValuePair<READCYCLE.readcycle, int>> readCycleAndorgIdList = new List<KeyValuePair<READCYCLE.readcycle, int>>();
List<int> allIds = new List<int>();
int newDeviceId = -1;
int userInfoId;
KeyValuePair<int, int> tempOldNewIds;
List<int> addedWorkers = new List<int>();
datablock.OpenConnection();
datablock.OpenTransaction();
//Создание организация, работников и ТС
foreach (DEPARTMENTS.department department in depar.departments)
{
addedWorkers = new List<int>();
int orgId = datablock.organizationTable.AddNewOrganization(department.DEPARTMENT, 1, 1, 1);
tempOldNewIds = new KeyValuePair<int, int>(department.ID, orgId);
orgOldNewIds.Add(tempOldNewIds);
int carId = -1;
#region "cars"
IEnumerable<CARS.car> carsList =
from car in cars.carsArray
where car.DEPARTMENTID == department.ID
select car;
foreach (CARS.car car in carsList)
{
IEnumerable<int> scoreQuery =
from devId in readcycle.cycles
where devId.CARID == car.ID
select devId.DEVICEID;
allIds = scoreQuery.ToList();
if (allIds.Count > 0)
{
IEnumerable<DEVICES.device> oneDevice =
from devId in devices.devices
where devId.ID == allIds[0]
select devId;
if (oneDevice.ToList().Count > 0)
{
DEVICES.device NewDevice = oneDevice.ToList()[0];
newDeviceId = datablock.deviceTable.AddNewDevice(1, NewDevice.DEVICE, NewDevice.VERSION, DateTime.Now, 1, 23442324);
}
else
newDeviceId = datablock.deviceTable.AddNewDevice(1, "UndefinedDevice", "UndefinedVersion", DateTime.Now, 1, 23442324);
}
else
newDeviceId = datablock.deviceTable.AddNewDevice(1, "UndefinedDevice", "UndefinedVersion", DateTime.Now, 1, 23442324);
if (car.DEPARTMENTID == department.ID)
{
int cardId = datablock.cardsTable.CreateNewCard(car.REGNUMBER, "Unknown", datablock.cardsTable.vehicleCardTypeId, orgId, 0, "Created from FIREBIRD database", 0, 1);
carId = datablock.vehiclesTables.AddNewVehicle(car.REGNUMBER, car.CARMODEL, "Unknown", 1, newDeviceId, cardId, DateTime.Now, 1);
tempOldNewIds = new KeyValuePair<int, int>(car.ID, carId);
carsOldNewIds.Add(tempOldNewIds);
}
}
#endregion
#region "workers"
IEnumerable<WORKERS.worker> workersList =
from worker in workers.workers
where worker.DEPARTMENTID == department.ID
select worker;
foreach (WORKERS.worker workerForAdd in workersList)
{
int newWId = datablock.cardsTable.CreateNewCard(workerForAdd.FIRSTNAME + " " + workerForAdd.SURNAME, workerForAdd.WORKERINTID.ToString(), datablock.cardsTable.driversCardTypeId,
orgId, 0, "Created from firebird", 0, 1);
/*userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Patronimic);
datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.MIDDLENAME);
userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Name);
datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.FIRSTNAME);
userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Surname);
datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.SURNAME);
userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Birthday);
datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.BIRTHDAY.ToShortDateString());
userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_DriversCertificate);
datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.LICENCE);*/
addedWorkers.Add(workerForAdd.ID);
tempOldNewIds = new KeyValuePair<int, int>(workerForAdd.ID, newWId);
workOldNewIds.Add(tempOldNewIds);
}
#endregion
#region "set orgId to READCYCLES"
if(addedWorkers.Count>0)
for(int i=0;i<readcycle.cycles.Count;i++)
{
if(addedWorkers.Contains(readcycle.cycles[i].WORKER1ID) || addedWorkers.Contains(readcycle.cycles[i].WORKER2ID))
{
KeyValuePair<READCYCLE.readcycle, int> readCycleAndorgId = new KeyValuePair<READCYCLE.readcycle, int>(readcycle.cycles[i], orgId);
readCycleAndorgIdList.Add(readCycleAndorgId);
}
}
#endregion
}
Console.WriteLine("Added workers, departmens, cars");
if (readCycleAndorgIdList.Count != readcycle.cycles.Count)
{
if (false) //база фигово связана
throw new Exception("Куда-то пропала часть инфы");
}
//Загрузка файлов ПЛФ в ранее созданные организации.
foreach (KeyValuePair<READCYCLE.readcycle, int> readedcycle in readCycleAndorgIdList)
{
SQLDB sqlDb = datablock.sqlDb;
SQLDB_Records sqlDB_rec = new SQLDB_Records(connectionStringMysql, sqlDb.GETMYSQLCONNECTION());
ReflectObjectToTableClass reflectedItemsList;
byte[] bytes = Guid.NewGuid().ToByteArray();
int orgId = readedcycle.Value;
Type type = null;
object myParseObject = new object();
plf = new PLFUnitClass();
plf = LoadLogBook(readedcycle.Key);
plf.cardType = 2;
IEnumerable<string> selCar =
from car in cars.carsArray
where car.ID == readedcycle.Key.CARID
select car.REGNUMBER;
if(selCar.ToList().Count>0)
plf.VEHICLE = selCar.ToList()[0];
IEnumerable<string> selDeviceName =
from dev in devices.devices
where dev.ID == readedcycle.Key.DEVICEID
select dev.DEVICE;
if (selDeviceName.ToList().Count > 0)
plf.ID_DEVICE = selDeviceName.ToList()[0];
int driverId = -1;
foreach (KeyValuePair<int, int> oldNew in workOldNewIds)
{
if (oldNew.Key == readedcycle.Key.WORKER1ID)
driverId = oldNew.Value;
}
int dataBlockId = datablock.AddPlfTypeData(orgId, bytes,
"AutoGenerated " + plf.VEHICLE + " " + plf.START_PERIOD.ToString() + " - " + plf.END_PERIOD.ToString(),
driverId);
DataRecords dataRecord = new DataRecords(connectionString, dataBlockId, currentLanguage, sqlDb);
//////////////////////устанавливаем PLF карту нужного водителя. Незнаю почему именно здесь, но так получилось.
int plfDriversCardType = sqlDB_rec.Get_DataBlockCardType(dataBlockId);
sqlDb.SetDataBlock_CardId(dataBlockId, plfDriversCardType);
// sqlDB.OpenConnection();
int cardTypeParamId = sqlDb.AddParam("cardType", 0, 255);
//sqlDB.OpenConnection();
sqlDb.DeleteDataRecord(dataBlockId, cardTypeParamId);
// sqlDB.CloseConnection();
//////////////////////
type = plf.GetType();
myParseObject = plf;
//sqlDB.OpenConnection();
SetParseBDate(sqlDb, dataBlockId);
//sqlDB.CloseConnection();
List<ReflectObjectToTableClass> allRecordsToAdd = new List<ReflectObjectToTableClass>();
foreach (PropertyInfo pi in type.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
reflectedItemsList = new ReflectObjectToTableClass();
string fieldName = pi.Name;
object field2 = pi.GetValue(myParseObject, null);
if (field2 != null)
{
reflectedItemsList.ReflectObjectToTable(fieldName, field2);//Не удалять!
allRecordsToAdd.Add(AddRecords(reflectedItemsList, sqlDb));//не удалять
}
}
foreach (ReflectObjectToTableClass recordList in allRecordsToAdd)
{
dataRecord.AddDataArray(recordList.reflectedItemsList);
}
//sqlDB.OpenConnection();
SetParseEDate(sqlDb,dataBlockId);
sqlDb.SetDataBlockState(dataBlockId, 2);
int dataBlockParseRecords = sqlDb.SetDataBlockParseRecords(dataBlockId);
Console.WriteLine("" + dataBlockParseRecords.ToString()+" records added");
}
Console.WriteLine("\n\r" + "Время начала импорта " + startLoadTime.ToShortDateString() + " " + startLoadTime.ToShortTimeString());
Console.WriteLine("\n\r" + "Время окончания импорта " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
//throw new Exception();
datablock.CommitTransaction();
datablock.CloseConnection();
}
catch (Exception ex)
{
datablock.RollbackConnection();
datablock.CloseConnection();
Console.WriteLine(ex.Message);
}
}