-
-
Save bubnenkoff/7377f735671aacd624371a14151856f4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
insertOrUpdateXmlFiles( | |
{required String xmlName, required List<String?> purchaseNumAndDocDate, required FTPItemModel archiveItem}) async { | |
print("insertOrUpdateXmlFiles"); | |
int? region_id; | |
if(archiveItem.fz == 'fz44') { | |
region_id = listOfMaps44fz.singleWhere((element) => element['translite_name'] == archiveItem.regionName)['id']; | |
} | |
if(region_id == null) { | |
throw Exception("you are trying to isert UNKNOW region: ${archiveItem.regionName}"); | |
} | |
print('current region_id: $region_id'); | |
serviceStatus['currentTask'] = 'insertOrUpdateXmlFiles: $xmlName'; | |
// перед вставкой необходимо проверить, по имени определить action_type | |
// data_update или data_insert | |
print('insertOrUpdateXmlFiles'); | |
String? purchaseNumber = purchaseNumAndDocDate[0]; | |
String? docDate = purchaseNumAndDocDate[1]; | |
var data_update_list = ["prolong", "cancel"]; | |
String action_type; | |
bool isContains = data_update_list.any((e) => xmlName.toLowerCase().contains(e)); | |
if(isContains) { | |
action_type = 'data_update'; | |
// print('Action_type: data_update'); | |
} else { | |
action_type = 'data_insert'; | |
// print('Action_type: data_insert'); | |
} | |
String sql = """SELECT id, "docPublishDate", action_type FROM "xml_files" WHERE action_type = '$action_type' AND section_name = '${archiveItem.sectionName}' AND "purchaseNumber" = '$purchaseNumber' order by "docPublishDate" DESC Limit 1;"""; | |
// print(sql); | |
int max = 10; | |
int min = 1; | |
Random rnd = Random(); | |
int jobNumber = min + rnd.nextInt(max - min); // от 1 до 9 | |
try { | |
// print(sql); | |
print("aaa"); | |
List<List<dynamic>> result = await connection.query(sql).timeout(Duration(seconds: 120)); | |
print('bbb'); | |
if (result.isEmpty) { | |
// данных нет в БД и их нужно вставить | |
// result[0] содержит все такой ответ [0] потому что мы всегда по одному файла запрашиваем. | |
// xml_date - берем из даты архива | |
sql = """INSERT INTO xml_files("arch_name", "file_name", "region", "section_name", "xml_date", "purchaseNumber", "docPublishDate", "fz", "jobNumber", "action_type", "region_id") | |
VALUES ('${archiveItem.archiveName.replaceAll('.xml.zip', '')}', '$xmlName', '${archiveItem.regionName}', '${archiveItem.sectionName}', '${archiveItem.archDate}', '$purchaseNumber', '${docDate}', '${archiveItem.fz}', '$jobNumber', '$action_type', $region_id) ON CONFLICT ("file_name") DO NOTHING;"""; | |
// print(sql); | |
await connection.query(sql); | |
} else { | |
// если данные уже есть, то нужно понять старые данные в БД или новые и выставить exists_status "old" или пустой | |
int dbXmlId = result[0][0]; | |
DateTime dbXMLDocDate = result[0][1]; // тип вернется из БД как DateTime | |
if(dbXMLDocDate.isBefore(DateTime.parse(docDate!))) { | |
sql = """UPDATE xml_files SET "exists_status" = 'old' WHERE action_type = 'data_update' AND section_name = '${archiveItem.sectionName}' AND "purchaseNumber"='$purchaseNumber' AND "docPublishDate" <'$docDate' """; | |
// print(sql); | |
await connection.query(sql); | |
sql = """INSERT INTO xml_files("arch_name", "file_name", "region", "section_name", "xml_date", "purchaseNumber", "docPublishDate", "fz", "jobNumber", "action_type", region_id) | |
VALUES ('${archiveItem.archiveName.replaceAll('.xml.zip', '')}', '$xmlName', '${archiveItem.regionName}', '${archiveItem.sectionName}', '${archiveItem.archDate}', '$purchaseNumber', '$docDate', '${archiveItem.fz}', '$jobNumber', '$action_type', $region_id ) ON CONFLICT ("file_name") DO NOTHING;"""; | |
// print(sql2); | |
currentJob['filesInserted']++; // увеличим счетчик обработанных файлов | |
// print(sql); | |
await connection.query(sql); | |
} else { // сразу вставляем как old | |
sql = """INSERT INTO xml_files("arch_name", "file_name", "region", "section_name", "xml_date", "purchaseNumber", "docPublishDate", "fz", "jobNumber", "action_type", "exists_status", "region_id") | |
VALUES ('${archiveItem.archiveName.replaceAll('.xml.zip', '')}', '$xmlName', '${archiveItem.regionName}', '${archiveItem.sectionName}', '${archiveItem.archDate}', '$purchaseNumber', '$docDate', '${archiveItem.fz}', '$jobNumber', '$action_type', 'old', $region_id ) ON CONFLICT ("file_name") DO NOTHING;"""; | |
// print(sql); | |
await connection.query(sql); | |
} | |
serviceStatus['currentTask'] = ''; | |
} | |
} on PostgreSQLException catch (e) { | |
print('Exception during Insert in xml_files: $e'); | |
// exit(0); | |
} on SocketException catch(e) { // пытаемся багу поймать | |
print('SocketException during Insert in xml_files: $e'); | |
// exit(0); | |
} on Exception catch(e) { | |
print('BaseException during Insert in xml_files: $e'); | |
// exit(0); | |
} | |
catch(e) { | |
print("We should not reach this"); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment