Advertisement
Guest User

Untitled

a guest
Nov 12th, 2019
221
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Julia 6.13 KB | None | 0 0
  1. module S_Stock
  2.     export add, erase, Stock, show_db, cost, all_cost, erase_cost, sqcount, show_log
  3.  
  4.     include("My_Log.jl")
  5.     using .My_Log
  6.  
  7.     try
  8.         using SQLite
  9.         using DataFrames
  10.     catch
  11.         import Pkg
  12.         Pkg.add("SQLite")
  13.         Pkg.add("DataFrames")
  14.         using SQLite
  15.         using DataFrames
  16.     end
  17.  
  18.     mutable struct Stock
  19.         db
  20.         first
  21.         last
  22.         timer
  23.         function Stock(pass)
  24.             dbase = SQLite.DB(pass)
  25.             l = f = t = 0
  26.             if !("consignments" in SQLite.tables(dbase).name)
  27.                 query = "CREATE TABLE consignments (
  28.                                    con_id INTEGER PRIMARY KEY,
  29.                                    con_name TEXT NOT NULL,
  30.                                    con_cnt INTEGER NOT NULL,
  31.                                    con_cost INTEGER
  32.                                );"
  33.                 SQLite.Query(dbase, query)
  34.                 query = "CREATE TABLE logger (
  35.                                    time INTEGER PRIMARY KEY,
  36.                                    type TEXT NOT NULL,
  37.                                    con_name TEXT NOT NULL,
  38.                                    con_cnt INTEGER NOT NULL
  39.                                );"
  40.                 SQLite.Query(dbase, query)
  41.             else
  42.                 query = "SELECT
  43.                                MIN(con_id) AS first,
  44.                                MAX(con_id) AS last
  45.                                FROM consignments;"
  46.                 df = SQLite.Query(dbase, query) |> DataFrame
  47.                 f = df.first[1] === missing ? 0 : df.first[1]
  48.                 l = df.last[1] === missing ? 0 : df.last[1]
  49.  
  50.                 query = "SELECT MAX(time) AS time FROM logger;"
  51.                 df = SQLite.Query(dbase, query) |> DataFrame
  52.                 t = df.time[1] === missing ? 0 : df.time[1]
  53.             end
  54.             new(dbase, f, l, t)
  55.         end
  56.     end
  57.  
  58.     function sql_log(stock, type, name, cnt)
  59.         stock[].timer += 1
  60.         query = "INSERT INTO logger (time, type, con_name, con_cnt)
  61.                VALUES
  62.                (?, ?, ?, ?);"
  63.         SQLite.Query(stock[].db, query, values=[stock[].timer, type, name, cnt])
  64.     end
  65.  
  66.     function add(stock, name, cnt, cost)
  67.         Log("---Adding consignment...")
  68.         query = "INSERT INTO consignments (con_id, con_name, con_cnt, con_cost)
  69.                VALUES
  70.                (?, ?, ?, ?);"
  71.         stock[].last += 1
  72.         if stock[].first == 0
  73.             stock[].first = stock[].last
  74.         end
  75.         SQLite.Query(stock[].db, query, values=[stock[].last, name, cnt, cost])
  76.         sql_log(stock, "add", name, cnt)
  77.         Log("---Added.")
  78.     end
  79.  
  80.     function erase(stock, cnt)
  81.         Log("---Delete product...")
  82.         query = "SELECT con_cnt AS cnt, con_name AS name FROM consignments ORDER BY con_id"
  83.         df = SQLite.Query(stock[].db, query) |> DataFrame
  84.         for i in 1:length(df.cnt)
  85.             cur = df.cnt[i]
  86.             if cur > cnt
  87.                 cur -= cnt
  88.                 query = "UPDATE consignments
  89.                                SET con_cnt=?
  90.                                WHERE con_id=?;"
  91.                 SQLite.Query(stock[].db, query, values=[cur, stock[].first])
  92.                 sql_log(stock, "erase", df.name[i], cnt)
  93.                 break
  94.             else
  95.                 cnt -= cur
  96.                 query = "DELETE FROM consignments WHERE con_id=?;"
  97.                 SQLite.Query(stock[].db, query, values=[stock[].first])
  98.                 sql_log(stock, "erase", df.name[i], cur)
  99.                 stock[].first += 1
  100.                 if stock[].first > stock[].last
  101.                     stock[].first = stock[].last = 0
  102.                 end
  103.                 if cnt == 0
  104.                     break
  105.                 end
  106.             end
  107.         end
  108.         Log("---Deleted.")
  109.     end
  110.  
  111.     function show_db(stock)
  112.         Log("---Showing...")
  113.         query = "SELECT
  114.                con_name AS name,
  115.                SUM(con_cnt) AS cnt,
  116.                SUM(con_cost * con_cnt) AS summary_cost
  117.                FROM consignments
  118.                GROUP BY con_name
  119.                ORDER BY con_name;"
  120.         println(SQLite.Query(stock[].db, query) |> DataFrame)
  121.     end
  122.  
  123.     function cost(stock, name)
  124.         Log("---Counting cost...")
  125.         query = "SELECT SUM(con_cnt * con_cost) AS res
  126.                FROM consignments WHERE con_name = ?;"
  127.         df = SQLite.Query(stock[].db, query, values=[name]) |> DataFrame
  128.         println(df.res[1] === missing ? 0 : df.res[1])
  129.         Log("---Conuting success.")
  130.     end
  131.  
  132.     function all_cost(stock)
  133.         Log("---Counting cost...")
  134.         query = "SELECT SUM(con_cnt * con_cost) AS res
  135.                FROM consignments;"
  136.         df = SQLite.Query(stock[].db, query) |> DataFrame
  137.         println(df.res[1] === missing ? 0 : df.res[1])
  138.         Log("---Conuting success.")
  139.     end
  140.  
  141.     function erase_cost(stock, cnt)
  142.         Log("---Counting cost...")
  143.         query = "SELECT con_cnt AS cnt, con_cost AS cost
  144.                FROM consignments ORDER BY con_id;"
  145.         df = SQLite.Query(stock[].db, query) |> DataFrame
  146.         sum = 0
  147.         for i in 1:length(df.cnt)
  148.             if df.cnt[i] > cnt
  149.                 sum += cnt * df.cost[i]
  150.                 break
  151.             else
  152.                 sum += df.cnt[i] * df.cost[i]
  153.                 cnt -= df.cnt[i]
  154.                 if cnt == 0
  155.                     break
  156.                 end
  157.             end
  158.         end
  159.         println(sum)
  160.         Log("---Counting success.")
  161.     end
  162.  
  163.     function sqcount(stock, name)
  164.         Log("---Counting...")
  165.         query = "SELECT SUM(con_cnt) AS res FROM consignments WHERE con_name = ?;"
  166.         df = SQLite.Query(stock[].db, query, values=[name]) |> DataFrame
  167.         println(df.res[1] === missing ? 0 : df.res[1])
  168.         Log("---Counting success.")
  169.     end
  170.  
  171.     function show_log(stock)
  172.         Log("---Showing log...")
  173.         query = "SELECT type, con_name, con_cnt FROM logger ORDER BY time;"
  174.         println(SQLite.Query(stock[].db, query) |> DataFrame)
  175.     end
  176.  
  177. end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement