147 lines
6.6 KiB
Mathematica
147 lines
6.6 KiB
Mathematica
let
|
||
fP = (P_NAME as text, optional P_FORCED_TYPE as nullable text) => let
|
||
//Получаем параметр из таблицы с настройками
|
||
GET_PARAMETER = try
|
||
let
|
||
GetTable = Excel.CurrentWorkbook(){[Name="PARAMETERS_TABLE"]}[Content],
|
||
KeepNeeded = Table.SelectColumns(GetTable,{"ПАРАМЕТР", "ЗНАЧЕНИЕ", "ТИП"}),
|
||
SetTypes = Table.TransformColumnTypes(KeepNeeded,{{"ПАРАМЕТР", type text}, {"ЗНАЧЕНИЕ", type any}, {"ТИП", type text}}),
|
||
Filter = Table.SelectRows(SetTypes, each ([ПАРАМЕТР] = P_NAME)),
|
||
Value = Table.SingleRow(Filter)
|
||
in
|
||
Value otherwise null,
|
||
|
||
PATH_OVERRIDE = try if P_NAME = "PATH_OVERRIDE" then null else @fP("PATH_OVERRIDE") otherwise null,
|
||
|
||
P_TYPE = if GET_PARAMETER <> null then
|
||
if P_FORCED_TYPE = null then
|
||
GET_PARAMETER[ТИП]
|
||
else
|
||
P_FORCED_TYPE
|
||
else
|
||
null,
|
||
|
||
P_VALUE = if GET_PARAMETER <> null then
|
||
if PATH_OVERRIDE <> null and
|
||
List.Contains({"FILE", "EXCEL", "DIR", "PATH", "CSVL"}, P_TYPE) then
|
||
fReplaceFunction(GET_PARAMETER[ЗНАЧЕНИЕ], false, PATH_OVERRIDE )
|
||
else
|
||
GET_PARAMETER[ЗНАЧЕНИЕ]
|
||
else
|
||
null,
|
||
|
||
// Определяем функции
|
||
// БУЛЕВО
|
||
F_Boolean = (Input as any) => let
|
||
Result = Value.ReplaceType(Input, type logical)
|
||
in
|
||
Result,
|
||
|
||
// ЦЕЛОЕ ЧИСЛО
|
||
F_Integer = (Input as any) => let
|
||
Result = Value.ReplaceType(Input, Int64.Type)
|
||
in
|
||
Result,
|
||
|
||
// ЧИСЛО С ЗАПЯТОЙ
|
||
F_Float = (Input as any) => let
|
||
Result = Value.ReplaceType(Input, type number)
|
||
in
|
||
Result,
|
||
|
||
// СТРОКА
|
||
F_Text = (Input as any) => let
|
||
Result = Value.ReplaceType(Input, type text)
|
||
in
|
||
Result,
|
||
|
||
// ДАТА
|
||
F_Date = (Input as any) => let
|
||
Result = Value.ReplaceType(DateTime.Date(Input), type date)
|
||
in
|
||
Result,
|
||
|
||
// ДАТА И ВРЕМЯ
|
||
F_DateTime = (Input as any) => let
|
||
Result = Value.ReplaceType(Input, type datetime)
|
||
in
|
||
Result,
|
||
|
||
// СПИСОК В ФОРМАТЕ PQ
|
||
// Ввод через точку с запятой, напр.: 1;2;яблоко;-10
|
||
// Точка с запятой плюс пробел меняются на точку с запятой
|
||
F_List = (Input as any) => let
|
||
Result = Text.Split(Text.Replace(Text.From(Input), "; ", ";"), ";")
|
||
in
|
||
Result,
|
||
|
||
// ТАБЛИЦА В ФОРМАТЕ PQ
|
||
F_RcrdSet = (Input as any) => let
|
||
RemoveSpaces = Text.Replace(Text.Replace(Input, " ;", ";"), "; ", ";"),
|
||
SplitRows = Text.Split(RemoveSpaces, "};{"),
|
||
Replace = List.ReplaceValue(List.ReplaceValue(SplitRows,"}","",Replacer.ReplaceText),"{","",Replacer.ReplaceText),
|
||
Result = Table.FromList(Replace, Splitter.SplitTextByDelimiter(";"))
|
||
in
|
||
Result,
|
||
|
||
// СПИСОК ЗАМЕН
|
||
F_Symlink = (Input as any) => let
|
||
RemoveSpaces = Text.Replace(Text.Replace(Input, " ;", ";"), "; ", ";"),
|
||
SplitRows = Text.Split(RemoveSpaces, ";"),
|
||
Result = Table.FromList(SplitRows, Splitter.SplitTextByDelimiter(">>"))
|
||
in
|
||
Result,
|
||
|
||
// ФАЙЛ EXCEL
|
||
F_ExcelFile = (Input as any) => let
|
||
Result = Excel.Workbook(File.Contents(Input), null, true)
|
||
in
|
||
Result,
|
||
|
||
// СОДЕРЖИМОЕ ПАПКИ
|
||
F_DirContent = (Input as any) => let
|
||
Result = Folder.Files(Input)
|
||
in
|
||
Result,
|
||
|
||
// ЛОКАЛЬНАЯ ИМЕНОВАННАЯ ТАБЛИЦА
|
||
F_LocalTable = (Input as any) => let
|
||
Result = Excel.CurrentWorkbook(){[Name=Input]}[Content]
|
||
in
|
||
Result,
|
||
|
||
// CSV-ФАЙЛ В СТАНДАРТНОЙ ПАПКЕ
|
||
F_LocalCSV = (Input as any) => let
|
||
GetDoc = Csv.Document(File.Contents("A:\Aggregated CSV\" & Input & ".csv"), [Delimiter="#(tab)", Encoding=1251, QuoteStyle=QuoteStyle.None]),
|
||
Result = Table.PromoteHeaders(GetDoc, [PromoteAllScalars=true])
|
||
in
|
||
Result,
|
||
|
||
CaseValues = {
|
||
{ List.Contains({"BOOL" , "LOGICAL" }, P_TYPE), try F_Boolean (P_VALUE) otherwise null},
|
||
{ List.Contains({"INT" , "NUMBER" }, P_TYPE), try F_Integer (P_VALUE) otherwise null},
|
||
{ List.Contains({"FLOAT" , "DOUBLE" }, P_TYPE), try F_Float (P_VALUE) otherwise null},
|
||
{ List.Contains({"TEXT" , "STRING" }, P_TYPE), try F_Text (P_VALUE) otherwise null},
|
||
{ List.Contains({"DATE" }, P_TYPE), try F_Date (P_VALUE) otherwise null},
|
||
{ List.Contains({"DATETIME", "DTTM" }, P_TYPE), try F_DateTime (P_VALUE) otherwise null},
|
||
|
||
{ List.Contains({"LIST" , "PQLIST" }, P_TYPE), try F_List (P_VALUE) otherwise null},
|
||
{ List.Contains({"RCRDSET" , "PQTABLE" }, P_TYPE), try F_RcrdSet (P_VALUE) otherwise null},
|
||
{ List.Contains({"SYMLNK" , "SYMLINK" }, P_TYPE), try F_Symlink (P_VALUE) otherwise null},
|
||
|
||
{ List.Contains({"TABLE" }, P_TYPE), try F_LocalTable (P_VALUE) otherwise null},
|
||
|
||
{ List.Contains({"FILE" , "EXCEL" }, P_TYPE), try F_ExcelFile (P_VALUE) otherwise null},
|
||
{ List.Contains({"DIR" , "PATH" }, P_TYPE), try F_DirContent (P_VALUE) otherwise null},
|
||
{ List.Contains({"CSVL" }, P_TYPE), try F_LocalCSV (P_VALUE) otherwise null},
|
||
{ List.Contains({"CSVD" }, P_TYPE), try F_LocalCSV (P_VALUE) otherwise null}, // Сделать обработки папки с CSV
|
||
|
||
{ true , P_VALUE }
|
||
},
|
||
|
||
SelectCase = List.First(List.Select(CaseValues, each _{0} = true)){1}
|
||
|
||
in
|
||
SelectCase
|
||
in
|
||
fP |