Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- module S_Stock
- export add, erase, Stock, show_db, cost, all_cost, erase_cost, sqcount, show_log
- include("My_Log.jl")
- using .My_Log
- try
- using SQLite
- using DataFrames
- catch
- import Pkg
- Pkg.add("SQLite")
- Pkg.add("DataFrames")
- using SQLite
- using DataFrames
- end
- mutable struct Stock
- db
- first
- last
- timer
- function Stock(pass)
- dbase = SQLite.DB(pass)
- l = f = t = 0
- if !("consignments" in SQLite.tables(dbase).name)
- query = "CREATE TABLE consignments (
- con_id INTEGER PRIMARY KEY,
- con_name TEXT NOT NULL,
- con_cnt INTEGER NOT NULL,
- con_cost INTEGER
- );"
- SQLite.Query(dbase, query)
- query = "CREATE TABLE logger (
- time INTEGER PRIMARY KEY,
- type TEXT NOT NULL,
- con_name TEXT NOT NULL,
- con_cnt INTEGER NOT NULL
- );"
- SQLite.Query(dbase, query)
- else
- query = "SELECT
- MIN(con_id) AS first,
- MAX(con_id) AS last
- FROM consignments;"
- df = SQLite.Query(dbase, query) |> DataFrame
- f = df.first[1] === missing ? 0 : df.first[1]
- l = df.last[1] === missing ? 0 : df.last[1]
- query = "SELECT MAX(time) AS time FROM logger;"
- df = SQLite.Query(dbase, query) |> DataFrame
- t = df.time[1] === missing ? 0 : df.time[1]
- end
- new(dbase, f, l, t)
- end
- end
- function sql_log(stock, type, name, cnt)
- stock[].timer += 1
- query = "INSERT INTO logger (time, type, con_name, con_cnt)
- VALUES
- (?, ?, ?, ?);"
- SQLite.Query(stock[].db, query, values=[stock[].timer, type, name, cnt])
- end
- function add(stock, name, cnt, cost)
- Log("---Adding consignment...")
- query = "INSERT INTO consignments (con_id, con_name, con_cnt, con_cost)
- VALUES
- (?, ?, ?, ?);"
- stock[].last += 1
- if stock[].first == 0
- stock[].first = stock[].last
- end
- SQLite.Query(stock[].db, query, values=[stock[].last, name, cnt, cost])
- sql_log(stock, "add", name, cnt)
- Log("---Added.")
- end
- function erase(stock, cnt)
- Log("---Delete product...")
- query = "SELECT con_cnt AS cnt, con_name AS name FROM consignments ORDER BY con_id"
- df = SQLite.Query(stock[].db, query) |> DataFrame
- for i in 1:length(df.cnt)
- cur = df.cnt[i]
- if cur > cnt
- cur -= cnt
- query = "UPDATE consignments
- SET con_cnt=?
- WHERE con_id=?;"
- SQLite.Query(stock[].db, query, values=[cur, stock[].first])
- sql_log(stock, "erase", df.name[i], cnt)
- break
- else
- cnt -= cur
- query = "DELETE FROM consignments WHERE con_id=?;"
- SQLite.Query(stock[].db, query, values=[stock[].first])
- sql_log(stock, "erase", df.name[i], cur)
- stock[].first += 1
- if stock[].first > stock[].last
- stock[].first = stock[].last = 0
- end
- if cnt == 0
- break
- end
- end
- end
- Log("---Deleted.")
- end
- function show_db(stock)
- Log("---Showing...")
- query = "SELECT
- con_name AS name,
- SUM(con_cnt) AS cnt,
- SUM(con_cost * con_cnt) AS summary_cost
- FROM consignments
- GROUP BY con_name
- ORDER BY con_name;"
- println(SQLite.Query(stock[].db, query) |> DataFrame)
- end
- function cost(stock, name)
- Log("---Counting cost...")
- query = "SELECT SUM(con_cnt * con_cost) AS res
- FROM consignments WHERE con_name = ?;"
- df = SQLite.Query(stock[].db, query, values=[name]) |> DataFrame
- println(df.res[1] === missing ? 0 : df.res[1])
- Log("---Conuting success.")
- end
- function all_cost(stock)
- Log("---Counting cost...")
- query = "SELECT SUM(con_cnt * con_cost) AS res
- FROM consignments;"
- df = SQLite.Query(stock[].db, query) |> DataFrame
- println(df.res[1] === missing ? 0 : df.res[1])
- Log("---Conuting success.")
- end
- function erase_cost(stock, cnt)
- Log("---Counting cost...")
- query = "SELECT con_cnt AS cnt, con_cost AS cost
- FROM consignments ORDER BY con_id;"
- df = SQLite.Query(stock[].db, query) |> DataFrame
- sum = 0
- for i in 1:length(df.cnt)
- if df.cnt[i] > cnt
- sum += cnt * df.cost[i]
- break
- else
- sum += df.cnt[i] * df.cost[i]
- cnt -= df.cnt[i]
- if cnt == 0
- break
- end
- end
- end
- println(sum)
- Log("---Counting success.")
- end
- function sqcount(stock, name)
- Log("---Counting...")
- query = "SELECT SUM(con_cnt) AS res FROM consignments WHERE con_name = ?;"
- df = SQLite.Query(stock[].db, query, values=[name]) |> DataFrame
- println(df.res[1] === missing ? 0 : df.res[1])
- Log("---Counting success.")
- end
- function show_log(stock)
- Log("---Showing log...")
- query = "SELECT type, con_name, con_cnt FROM logger ORDER BY time;"
- println(SQLite.Query(stock[].db, query) |> DataFrame)
- end
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement