Tau = 2 * Pi ; Needs [ DatabaseLink` ] ; conn = OpenSQLConnection [ JDBC [ SQLite , /home/hugo/.stupan/stupan.db ] ] ; DataForMonth [ start_ , end_ ] := SQLExecute [ conn , SELECT count(l.id), p.name FROM log l INNER JOIN products p ON p.id = l.product_id WHERE l.time BETWEEN `1` AND `2` GROUP BY p.id ORDER BY count(l.id) DESC , { DateString [ start , ISODate ] , DateString [ end , ISODate ] } ] ; BarForMonth [ start_ , end_ ] := Block [ { data = DataForMonth [ start , end ] , values , names } , values = data [ [ All , 1 ] ] ; names = data [ [ All , 2 ] ] ; BarChart [ { values } , ChartLabels { Placed [ names , Axis , Rotate [ # , Tau / 4 ] & ] } ] ] ;

Quantity [ 2 , Year ]

Manipulate [ BarForMonth [ start , end ] , { start , AbsoluteTime @ { 2017 , 1 } , AbsoluteTime @ { 2019 } } , { end , AbsoluteTime @ { 2017 } , AbsoluteTime @ { 2019 } } ]

BarForMonth [ { 2017 , 01 , 01 } , Today ]

stupan_2.gif

BarForMonth [ { 2017 , 1 , 1 } , Day: Wed 31 Oct 2018 ]

BarForMonth [ { 2017 , 1 , 1 } , Day: Wed 31 Oct 2018 ]

BarForMonth [ 9 ]

stupan_3.gif

Ovanstående är ett exempel på logg för Stupan under januari månad 2018.

Test med tider

stupan_4.gif

2018-10-30 13:10:46

Tue 30 Oct 2018 13:10:46 GMT+ 1.

stupan_5.gif

stupan_6.gif

AS CREATE VIEW money_change

SQLSelect [ conn , products , { name } , SortingColumns { SQLColumn [ name ] Ascending } ]

{ { 10 Trappistes Rochefort } , { 6 Trappistes Rochefort } , { 8 Trappistes Rochefort } , { Aecht Schlenkerla Rauchbier } , { Anchor Christmas Ale } , { Anchor Coffee Porter } , { Anchor Steam Beer } , { Bad Apple } , { Badgers First Call } , { Balder Pastersbier Walhöll } , { Ballast Point Sour Blackberry } , { Ballast point HW series } , { Banderos Chips } , { Bedrö Bitter } , { Bellman } , { Berliner Vice } , { Bernard Amber lager } , { Bernard Celebration } , { Biere nouvelle } , { Billig Sprit } , { Billys } , { Birra Nazi } , { Bitburger } , { Black Chocolate Stout } , { Blå Chimay } , { Borracho } , { Boulard Cidre Biologique } , { Breznak } , { Breznak Burk } , { Carnegie } , { Celebrator } , { Chips [100, 250) } , { Chips [250, 350) } , { Cider Rose } , { Cidre Biologique ca Ribaude } , { Cirrus the Cloudy Lager } , { Corona } , { Crowmoor Päron } , { Cuvee des Jacobins } , { Daim } , { Dale's Pale Ale } , { Double Chocolate } , { Duvel } , { Eko torr äppelcider } , { Electric Nurse APA } , { Erdinger Kristall } , { Erdinger Weißbier } , { Fat Tire } , { Finläsk } , { Flat Tire } , { Folkes Lager } , { Folkes röda IPA } , { Founders Curmudgeon } , { Founders Dirty Bastard } , { Franziskaner Weissbier } , { Fuller's past masters } , { Fullers ESB } , { Gato Negro } , { Geuze Boon Black Label } , { Ginger Joe } , { Gorbys } , { Goud Carolus } , { Gouden Carolus Christmas } , { Granny Mary's Red Ale } , { Grebbestads Julöl } , { Gueze Tilquin } , { Guiness } , { Göteborgs Christmas } , { Göteborgs nya starkpilsner } , { Hale to Nothing } , { Hanssens Oude Gueze } , { Holley American Style } , { Homework Series } , { Honey Dew } , { Häfvöl } , { Innis & Gunn } , { J.P.Chenet } , { Jenny Nyström Påsköl } , { Jutesäck } , { Kilkenny } , { Kona Big Wave } , { Kravall Draget } , { LEON } , { La Choulette Ambree } , { La Trappe Trappist Tripel } , { Leffe Blond } , { Leffe Brun Storflaska } , { Lindemans Cuvee Rene Oude Gueuze } , { Lindemans Äpple } , { London Pale Ale Meantime } , { London Porter } , { London Pride } , { Lustgårdens Cider Torr Äpple } , { Lättöl & Fulläsk } , { Magners } , { Mc Chouffe Brune } , { McGarg Granny Marry Red Lay } , { Medlem Halvår } , { Medlem Helår } , { Märke } , { N'Ice Chouffe } , { Nachos } , { Nebuchadnezzar } , { Newcastle Brown Ale } , { NääS Påsköl } , { Nötter } , { Oppigårds Amarillo } , { Oppigårds Dalalager } , { Oppigårds Golden Ale } , { Oppigårds Indian Tribute IPA } , { Oppigårds New Sweden IPA } , { Oppigårds Norn pale ale } , { Oppigårds Thurbo Double IPA } , { Pant rödback med flaskor } , { Pohjala Italobänger } , { Pohjala Jöuluöö } , { Popcorn } , { Poppels } , { Porter Warminski } , { Proper Job Cornish IPA } , { Rodenbach Alexander } , { Rodenbach Caractere Rouge } , { S:t Erik IPA } , { SIPA } , { Saison Dupont } , { Schneider Weisse } , { Ship full of ipa } , { Sierra Nevada Pale Ale } , { Silk Road Tie Guan Yin } , { Sitting Bulldog IPA } , { Skånsk Äppelcider } , { Sleepy Bulldog Pale Ale } , { Somersby Rose } , { Sotholmen EXTRA Stout } , { St Peters Cream Stout } , { Stallhagen Baltic Porter } , { Staropramen dark } , { Stone White Ghost } , { Stoodley Stout } , { SunRise saku } , { TexMex biljett } , { Timmermans Kriek Lambicus } , { Trappist Achel } , { Tropic Thunder } , { Twisted Thistle } , { Utskrift } , { Weihenstephaner } , { Weihenstephaner Kristall } , { Westmalle Trappist Dubbel } , { Wisby Kloster } , { Zeunerts Påskmust } , { Zlatopramen } , { Äppelcider } }

stupan_7.gif

The plot should have that the whole change is noted on the Y axis, and all travel along the X axis should be
perfectly horizontal. I have however not figured out how to do that yet.

PlotProducts [ { Breznak , Breznak Burk , Bitburger } , PlotRange { { DateObject [ { 2017 , 09 , 01 } ] , Today } , { Automatic , Automatic } } , ImageSize Large ]

stupan_8.gif

stupan_9.gif

stupan_10.gif

stupan_11.gif

stupan_12.gif

PlotProducts [ Flatten [ SQLSelect [ conn , products , { name } ] ] ]

stupan_13.gif

MoneyData [ id_ ] := Block [ { data , times , values } , data = SQLExecute [ conn , SELECT CURRENT_TIMESTAMP, amount / 100.0 FROM money WHERE id = `1` UNION ALL SELECT time, change / 100.0 FROM money_change WHERE account = `1` ORDER BY time DESC , { id } ] ; times = DateObject /@ data [ [ All , 1 ] ] ; values = Accumulate [ data [ [ All , 2 ] ] ] ; (* Transpose [ { times , values } ] *) TimeSeries [ values , { times } ] ]

MoneyAll summerar helt fel, och bör *inte* användas.

MoneyAll = Block [ { data , times , values } , data = SQLExecute [ conn , SELECT CURRENT_TIMESTAMP, sum(amount) / 100.0 FROM money UNION ALL SELECT time, change / 100.0 FROM money_change ORDER BY time DESC ] ; times = DateObject /@ data [ [ All , 1 ] ] ; values = Accumulate [ data [ [ All , 2 ] ] ] ; TimeSeries [ values , { times } ] ] ;

DateListStepPlot [ { MoneyData [ 1 ] , MoneyData [ 2 ] , MoneyData [ 3 ] , MoneyAll } , PlotLegends { Kassa , Valv , Fodringar , Totalt } , GridLines { Automatic , { 0 } } , PlotRange { { DateObject [ { 2018 , 03 , 01 } ] , Today } , Automatic } ]

stupan_14.gif

stupan_15.gif

DateListStepPlot [ MoneyAll ]

stupan_16.gif

DateListStepPlot [ { MoneyData [ 1 ] , MovingAverage [ MoneyData [ 1 ] , 10 ] } , PlotLegends { Valv , Moving Average } , GridLines { Automatic , { 0 } } , PlotRange { { DateObject [ { 2018 , 03 , 01 } ] , Today } , Automatic } ]

stupan_17.gif

Nu kollar vi på totalvärde
först behöver vi inventarievärde

ProjectedValue = SQLExecute [ conn , "

Created with the Wolfram Language