module Buchhaltung.Importers
(
paypalImporter
, aqbankingImporter
, comdirectVisaImporter
, module Buchhaltung.Import
, getBayesFields
)
where
import Buchhaltung.Common
import Buchhaltung.Import
import Control.Arrow
import Control.Monad.RWS.Strict
import Data.Functor.Identity
import qualified Data.HashMap.Strict as HM
import Data.List
import qualified Data.ListLike as L
import qualified Data.ListLike.String as L
import qualified Data.Map.Strict as M
import Data.Maybe
import Data.Ord
import Data.String
import qualified Data.Text as T
import qualified Data.Text.IO as T
import qualified Data.Text.Lazy as TL
import Data.Time.Calendar
import Formatting (sformat, (%), shown)
import qualified Formatting.ShortFormatters as F
import Text.Parsec
import qualified Text.ParserCombinators.Parsec as C
import Text.Printf
headerInfo
:: MonadError Msg m =>
VersionedCSV a -> Maybe Version -> m (SFormat Version, CsvImport a)
headerInfo g v = do
(format, map) <- g
let convert rh = (cVersion rh <$ format, rh)
convert . cRaw <&> lookupErrM
(printf "Version is not defined for format '%s%'" $ fName format)
M.lookup (fromMaybe (fromDefaultVersion $ fVersion format) v) map
csvImport
:: (MonadError Msg m
,MonadReader (Options user config (a, Maybe Version)) m)
=> VersionedCSV a -> T.Text -> m [ImportedEntry]
csvImport versionedCsv csv = do
(env, version) <- reader oEnv
(form, g@CSV{cHeader=expected,
cDescription = desc,
cVersion = version }) <- headerInfo versionedCsv version
let toEntry x = ImportedEntry
{ ieT = genTrans date (vdate =<< cVDate g x)
(getCsvConcat desc x)
, ieSource = fromMapToSource form x
, iePostings = (AccountId (cBank g env $ x) (cAccount g env $ x)
, cAmount g x)
}
where
vdate vd = if date == vd then Nothing
else Just vd
date = cDate g x
(header, rows) = parseCsv (cSeparator g) . TL.fromStrict $ csv
if expected == header then
return $ fmap toEntry $ filter (cFilter g) rows
else throwError $ L.unlines
[sformat ("Headers do not match. Expected by format '"%
F.st%"' version '"%F.st%"':")
(fName form) version
,fshow expected
,"Given:"
,fshow header
]
aqbankingImporter :: Importer env
aqbankingImporter = Importer Nothing $ csvImport aqbankingImport
aqbankingImport :: VersionedCSV env
aqbankingImport = toVersionedCSV (SFormat "aqBanking" $ DefaultVersion "4")
[CSV
{ cFilter = const True
, cAmount = getCsvConcat [ "value_value"
, "value_currency"]
, cDate = readdate . getCsv "date"
, cVDate = Just . readdate . getCsv "valutadate"
, cBank = const $ getCsv "localBankCode"
, cAccount = const $ getCsv "localAccountNumber"
, cSeparator = ';'
, cVersion= "4"
, cHeader = ["transactionId"
,"localBankCode"
,"localAccountNumber"
,"remoteBankCode"
,"remoteAccountNumber"
,"date"
,"valutadate"
,"value_value"
,"value_currency"
,"localName"
,"remoteName"
,"remoteName1"
,"purpose"
,"purpose1"
,"purpose2"
,"purpose3"
,"purpose4"
,"purpose5"
,"purpose6"
,"purpose7"
,"purpose8"
,"purpose9"
,"purpose10"
,"purpose11"
,"category"
,"category1"
,"category2"
,"category3"
,"category4"
,"category5"
,"category6"
,"category7"]
, cDescription = desc
, cBayes = ["remoteBankCode","remoteAccountNumber"]
++ desc
}]
where desc = concatMap (\(f,i) -> (f <>) <$> "":i)
[ ("remoteName", ["1"])
, ("purpose", fshow <$> [1..11])
, ("category", fshow <$> [1..7])]
comdirectToAqbanking :: IO ()
comdirectToAqbanking = toAqbanking2 ';' T.getContents comdirect_header comdirect_mapping $ const True
comdirect_header :: [[Char]]
comdirect_header = ["Buchungstag","Wertstellung (Valuta)","Vorgang","Buchungstext","Umsatz in EUR"]
comdirect_header_visa :: [[Char]]
comdirect_header_visa = ["Buchungstag","Umsatztag","Vorgang","Referenz","Buchungstext","Umsatz in EUR"]
comdirect_mapping_visa :: [([Char], T.Text -> T.Text)]
comdirect_mapping_visa = [
("Referenz",const ""),
( "Referenz" , undefined),
( "Referenz" , const "Visa")
] ++ empty 2 ++ [
( "Buchungstag", fshow . readdate2),
( "Umsatztag", fshow. readdate2),
( "Umsatz in EUR", comma ),
( "Umsatz in EUR", const "EUR" ),
( "Umsatz in EUR", const "Johannes Gerer" ),
( "Buchungstext", id),
("Referenz",const ""),
( "Vorgang",id),
( "Referenz",id)] ++ empty (3214)
where empty x = take x $ repeat ( "Referenz" , const "")
comdirect_mapping :: [([Char], T.Text -> T.Text)]
comdirect_mapping = [
("Buchungstag",const ""),
( "Buchungstag" , undefined),
( "Buchungstag" , const "Visa")
] ++ empty 2 ++ [
( "Buchungstag", fshow . readdate2),
( "Wertstellung (Valuta)", fshow . readdate2),
( "Umsatz in EUR", const "WTF" ),
( "Umsatz in EUR", const "EUR" ),
( "Umsatz in EUR", const "Johannes Gerer" ),
( "Buchungstext", id),
( "Vorgang",id)] ++ empty (32102)
where empty x = take x $ repeat ( "Buchungstag" , const "")
ok :: (IsString b, Eq b, L.StringLike b) => b -> [b]
ok = L.takeWhile (/= fromString "")
. L.dropWhile (/= fromString "\"Buchungstag\";\"Umsatztag\";\"Vorgang\";\"Referenz\";\"Buchungstext\";\"Umsatz in EUR\";")
. L.lines
p :: ParsecT [Char] u Identity [Char]
p = do a <- C.manyTill C.anyChar (C.try $ C.string "\n\"Neu\";")
return a
hbci_sep = ';'
description
:: (L.ListLike c item, L.StringLike c, Show a, Eq a) =>
[a] -> [c] -> c
description cols r = L.unwords . filter (not . L.null) $ description_list csv_header
cols r
description_list :: (Show a, Eq a) => [a] -> [a] -> [b] -> [b]
description_list t cols r = map fst $ sorted r
where
desc_indices = map (idx t) cols
magic v k = do i <- elemIndices k desc_indices
return (v,i)
sorted r = sortBy (comparing snd) $ mconcat $ zipWith magic r [0..]
csv_header = undefined
comdirectVisaImporter :: Importer T.Text
comdirectVisaImporter = Importer windoof $ csvImport comdirectVisa
comdirectVisa = toVersionedCSV (SFormat "visa" $ DefaultVersion "manuell")
[CSV
{ cFilter =(/= "") . getCsv "Buchungstag"
, cAmount = textstrip . comma . (<> " EUR") . getCsv "Ausgang"
, cDate = parseDatum . getCsv "Buchungstag"
, cVDate = Just . parseDatum . getCsv "Valuta"
, cBank = const
, cAccount = const $ const "Visa"
, cSeparator = ','
, cHeader = ["Buchungstag"
,"Vorgang"
,"Buchungstext"
,"Ausgang"
,"Valuta"
,"Referenz"
,"Buchungstext2"
]
, cDescription = desc
, cBayes = desc
, cVersion = "manuell"
}
, CSV
{ cFilter =(/= "") . getCsv "Buchungstag"
, cAmount = comma . (<> " EUR") . getCsv "Umsatz in EUR"
, cDate = parseDatum . getCsv "Buchungstag"
, cVDate = Just . parseDatum . getCsv "Umsatztag"
, cBank = const
, cAccount = const $ const "Visa"
, cSeparator = ','
, cHeader = ["Buchungstag"
,"Umsatztag"
,"Vorgang"
,"Referenz"
,"Buchungstext"
,"Umsatz in EUR"]
, cDescription = desc2
, cBayes = desc2
, cVersion = "export"
}
]
where desc = ["Vorgang"
,"Buchungstext"
,"Buchungstext2"
]
desc2 = ["Vorgang"
,"Buchungstext"
]
paypalImporter :: Importer T.Text
paypalImporter = Importer windoof $ csvImport paypalImport
paypalImport :: VersionedCSV T.Text
paypalImport =
let base = CSV
{ cFilter = (/= "Storniert") . getCsv " Status"
, cAmount = comma . getCsvConcat [ " Netto"
, " Währung"]
, cDate = parseDatum . getCsv "Datum"
, cVDate = const Nothing
, cBank = const $ const "Paypal"
, cAccount = const
, cSeparator = ','
, cVersion = "undefined"
, cHeader = []
, cBayes = ["undefined"]
, cDescription = ["undefined"]
} in toVersionedCSV (SFormat "paypal" $ DefaultVersion "2016")
[base { cVersion = "2016"
, cHeader = ["Datum"
," Zeit"
," Zeitzone"
," Name"
," Typ"
," Status"
," W\228hrung"
," Brutto"
," Geb\252hr"
," Netto"
," Von E-Mail-Adresse"
," An E-Mail-Adresse"
," Transactionscode"
," Status der Gegenpartei"
," Adressstatus"
," Artikelbezeichnung"
," Artikelnummer"
," Betrag f\252r Versandkosten"
," Versicherungsbetrag"
," Umsatzsteuer"
," Option 1 - Name"
," Option 1 - Wert"
," Option 2 - Name"
," Option 2 - Wert"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Angebotsende"
," Vorgangs-Nr."
," Rechnungs-Nr."
," Txn-Referenzkennung"
," Rechnungsnummer"
," Individuelle Nummer"
," Belegnummer"
," Guthaben"
," Adresszeile 1"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer"
," "]
, cBayes = [" Name"
," An E-Mail-Adresse"
," Von E-Mail-Adresse"
," Artikelbezeichnung"
," Typ"
," Status"
," Käufer-ID"
, " Status der Gegenpartei"," Adressstatus"
, " Option 1 - Name"
, " Option 2 - Name"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Adresszeile 1"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer"
]
, cDescription = [" Name"
," Artikelbezeichnung"
," Typ"
," Zeit"]
}
,base { cVersion = "2014"
, cHeader = ["Datum"
," Zeit"
," Zeitzone"
," Name"
," Art"
," Status"
," W\228hrung"
," Brutto"
," Geb\252hr"
," Netto"
," Von E-Mail-Adresse"
," An E-Mail-Adresse"
," Transaktionscode"
," Status der Gegenpartei"
," Adressstatus"
," Verwendungszweck"
," Artikelnummer"
," Betrag f\252r Versandkosten"
," Versicherungsbetrag"
," Umsatzsteuer"
," Option 1 - Name"
," Option 1 - Wert"
," Option 2 - Name"
," Option 2 - Wert"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Angebotsende"
," Vorgangs-Nr."
," Rechnungs-Nr."
," Txn-Referenzkennung"
," Rechnungsnummer"
," Individuelle Nummer"
," Best\228tigungsnummer"
," Guthaben"
," Adresse"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer der Kontaktperson"
," "]
, cBayes = [" Name"
," An E-Mail-Adresse"
," Von E-Mail-Adresse"
," Verwendungszweck"
," Art"
," Status"
," Käufer-ID"
, " Status der Gegenpartei"," Adressstatus"
, " Option 1 - Name"
, " Option 2 - Name"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Adresse"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer der Kontaktperson"
]
, cDescription = [" Name"
," Verwendungszweck"
," Art"
," Zeit"]
}
, base { cVersion = "2013"
, cHeader = ["Datum"
," Zeit"
," Zeitzone"
," Name"
," Art"
," Status"
," W\228hrung"
," Brutto"
," Geb\252hr"
," Netto"
," Von E-Mail-Adresse"
," An E-Mail-Adresse"
," Transaktionscode"
," Status der Gegenpartei"
," Adressstatus"
," Verwendungszweck"
," Artikelnummer"
," Betrag f\252r Versandkosten"
," Versicherungsbetrag"
," Umsatzsteuer"
," Option 1 - Name"
," Option 1 - Wert"
," Option 2 - Name"
," Option 2 - Wert"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Angebotsende"
," Vorgangs-Nr."
," Rechnungs-Nr."
," Txn-Referenzkennung"
," Rechnungsnummer"
," Individuelle Nummer"
," Menge"
," Best\228tigungsnummer"
," Guthaben"
," Adresse"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer der Kontaktperson"
," "]
, cBayes = [" Name"
," An E-Mail-Adresse"
," Von E-Mail-Adresse"
," Verwendungszweck"
," Art"
," Status"
," Käufer-ID"
," Status der Gegenpartei"," Adressstatus"
," Option 1 - Name"
," Option 2 - Name"
," Auktions-Site"
," K\228ufer-ID"
," Artikel-URL"
," Adresse"
," Zus\228tzliche Angaben"
," Ort"
," Staat/Provinz/Region/Landkreis/Territorium/Pr\228fektur/Republik"
," PLZ"
," Land"
," Telefonnummer der Kontaktperson"
]
, cDescription = [" Name"
," Verwendungszweck"
," Art"
," Zeit"]
}]
show2 :: [[T.Text]] -> [T.Text]
show2 = map (T.intercalate ";" . map (\x -> "\"" <> (quote x) <> "\""))
where quote x = T.replace "\"" escapedDoubleQuotes x
hibiscusToAqbanking :: IO ()
hibiscusToAqbanking = toAqbanking ';'
(T.readFile "/home/data/finanzen/imported/hibiscus-export-20120930.csv")
hibicus_header hibiscus_mapping hibiscus_transf $ const True
toAqbankingPure
:: (Show a, Eq a) =>
Char
-> [a]
-> [a]
-> [T.Text -> T.Text]
-> ([T.Text] -> Bool)
-> T.Text
-> T.Text
toAqbankingPure sep header mapping transformation filtercond =
T.intercalate "\n" . show2 . (:) csv_header
. map appl . filter filtercond . drop 1 . (readcsv sep)
where appl = map (\(a,b) -> a b) . zip transformation . description_list header mapping
toAqbanking
:: (Show a, Eq a) =>
Char
-> IO T.Text
-> [a]
-> [a]
-> [T.Text -> T.Text]
-> ([T.Text] -> Bool)
-> IO ()
toAqbanking sep f header mapping transf filt =
T.putStr =<< toAqbankingPure sep header mapping transf filt <$> f
toAqbanking2Pure
:: (Show a, Eq a) =>
Char
-> [a]
-> [(a, T.Text -> T.Text)]
-> ([T.Text] -> Bool)
-> T.Text
-> T.Text
toAqbanking2Pure sep header mapping filt = toAqbankingPure sep header mapping' transf filt
where mapping' = map fst mapping
transf = map snd mapping
toAqbanking2 sep f header mapping filt =
T.writeFile "/tmp/new" =<< toAqbanking2Pure sep header mapping filt <$> f
hibicus_header :: [[Char]]
hibicus_header =["Kontonummer","BLZ","Konto","Gegenkonto","Gegenkonto BLZ","Gegenkonto Inhaber","Betrag","Valuta","Datum","Verwendungszweck","Verwendungszweck 2","Zwischensumme","Primanota","Kundenreferenz","Kategorie","Kommentar","Weitere Verwendungszwecke"]
hibiscus_mapping :: [[Char]]
hibiscus_mapping = ["Primanota","BLZ","Kontonummer","Gegenkonto BLZ","Gegenkonto","Datum","Valuta","Betrag" , "Betrag" , "Betrag" , "Gegenkonto Inhaber", "Betrag", "Verwendungszweck","Verwendungszweck 2","Kommentar","Weitere Verwendungszwecke"] ++ (take 16 $ repeat "Betrag")
hibiscus_transf :: [T.Text -> T.Text]
hibiscus_transf = [id, id , id , id , id , fshow . readdate2, fshow . readdate2,comma ,const "EUR", const "Johannes Gerer", id , const ""] <> (take 4 $ repeat id) <> (take 16 $ repeat $ const "") :: [T.Text -> T.Text]
comma :: T.Text -> T.Text
comma = T.replace "," "." . T.replace "." "" :: T.Text -> T.Text
readdate2 :: Stream t Data.Functor.Identity.Identity Char
=> t -> Date2
readdate2 s = either (error.show) id (parse p_date2 "date" s)
p_date2 :: Stream t Data.Functor.Identity.Identity Char
=> Parsec t () Date2
p_date2 = do d <- many1 digit
char '.'
m <- many1 digit
char '.'
y <- many1 digit
return (D y m d)
readdate :: T.Text -> Day
readdate s = either (error.show) id (parse p_date "date" s)
p_date :: Stream t Data.Functor.Identity.Identity Char
=> Parsec t () Day
p_date = do y <- many1 digit
char '/'
m <- many1 digit
char '/'
d <- many1 digit
return $ fromGregorian (read y) (read m) (read d)
instance Show Date2 where
show (D y m d) = concat [y, "/", m, "/", d]
data Date2 = D String String String
deriving (Eq)
toBayes
:: MonadError Msg m
=> VersionedCSV a
-> m (SFormat DefaultVersion, M.Map Version [T.Text])
toBayes = fmap (second $ fmap $ cBayes . cRaw)
defaultFields
:: MonadError Msg m =>
m (M.Map (SFormat ()) (M.Map Version [T.Text]))
defaultFields = fromListUnique . fmap (first $ (() <$))
=<< sequence [toBayes paypalImport, toBayes aqbankingImport]
getBayesFields
:: MonadError Msg m
=> Source -> m [T.Text]
getBayesFields source = do
fields <- lookupErrM "Version has to be configured" M.lookup
(fVersion format)
=<< lookupErrM "Format has to be configured" M.lookup
(() <$ format)
=<< defaultFields
return $ mapMaybe (flip M.lookup $ sStore source) fields
where format = sFormat source