maysam · February 15, 2025 20:21
diff --git a/yuh.rb b/yuh.rb
 # frozen_string_literal: true

 require 'sinatra'
 require 'sinatra/reloader' if development?
 require 'sqlite3'
 require 'pdf-reader'

 set :environment, :development

 DB_FILE = 'transactions.db'

 def get_pdf_files
  Dir.glob('*.pdf')
 end

 # Setup database
 def setup_database
  db = SQLite3::Database.new(DB_FILE)
  db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS transactions (
      id INTEGER PRIMARY KEY,
      date TEXT,
      type TEXT,
      ref TEXT,
      stock TEXT,
      symbol TEXT,
      quantity INTEGER,
      price REAL,
      amount REAL
    );
  SQL
  db.close
 end

 # Parse PDF and extract transactions
 def parse_pdf
  transactions = {}
  get_pdf_files.each do |pdf_file|
    reader = PDF::Reader.new(pdf_file)

    reader.pages.each do |page|
      text = page.text
      lines = text.lines.map(&:strip).filter { |a| a.to_s > '' }
      lines.each_with_index do |line, index|
        next if index < 2 
        unless index > 1 && line.match?(/Quantity/) && lines[index - 1].match?(/(.*) \(.+\)/) && lines[index - 2].match?(/Buy|Sell/)
          next
        end

        quantity = line.match(/Quantity: (\d+.?\d*)/)[1].to_f
        stock = lines[index - 1].match(/(.*) \((.+)\)/)[1].strip
        symbol = lines[index - 1].match(/(.*) \((.+)\)/)[2].strip
        parts = lines[index - 2].split
        type = parts[1]
        quantity = type == 'Buy' ? quantity : -quantity
        price = parts[3].tr("’", '').tr("'", '').to_f
        ref = parts[2]
        amount = type == 'Buy' ? -price : price
        date = parts[-2] # Extract the date

        transactions[ref] = [date, type, ref, stock, symbol, quantity, price, amount]
      end
    end
  end
  transactions
 end

 # Insert transactions into SQLite3
 def insert_transactions
  db = SQLite3::Database.new(DB_FILE)
  transactions = parse_pdf

  db.execute('DELETE FROM transactions')
  transactions.each do |ref, t|
    result = db.execute('SELECT * FROM transactions where ref = ?', ref)
    if result.length.positive?
      pp t
      pp result
      # update
      db.execute(
        'UPDATE transactions SET date = ?, type = ?, ref = ?, stock = ?, symbol = ?, quantity = ?, price = ?, amount = ? WHERE ref = ?', t, ref
      )
    else
      db.execute(
        'INSERT INTO transactions (date, type, ref, stock, symbol, quantity, price, amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', t
      )
    end
  end

  db.close
 end

 # Setup database and insert transactions
 setup_database
 insert_transactions

 # Sinatra Web Interface
 get '/' do
  db = SQLite3::Database.new(DB_FILE)
  @transactions = db.execute('SELECT stock, symbol, sum(amount) as profit, sum(quantity) as quantity, count(*) as c FROM transactions group BY stock order by profit desc')
  db.close
  erb :index
 end

 get '/transactions' do
  db = SQLite3::Database.new(DB_FILE)
  @transactions = db.execute('SELECT * FROM transactions order by stock desc')
  db.close
  erb :transactions
 end

 __END__

 @@layout
 <!DOCTYPE html>
 <html>
 <head>
  <title>Stock Transactions</title>
  <style>
    body { font-family: Arial, sans-serif; margin: 0; padding: 0; }
    .container { width: 90%; margin: 0 auto; padding: 20px; }
    table { width: 100%; border-collapse: collapse; margin-top: 20px; }
    th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
    th { background-color: #f4f4f4; color: #333; }
    tr:nth-child(even) { background-color: #f9f9f9; }
    tr:hover { background-color: #f1f1f1; }
    h1 { color: #333; text-align: center; margin-top: 20px; }
  </style>
 </head>
 <body>
  <div class="container">
    <h1>Stock Transactions</h1>
    <%= yield %>
  </div>
 </body>
 </html>

 @@index
 <table>
  <tr>
    <th>Stock</th>
    <th>Symbol</th>
    <th>Profit</th>
    <th>Quantity</th>
    <th>Number of transactions</th>
  </tr>
  <% @transactions.each do |t| %>
    <tr>
      <td><%= t[0] %></td>
      <td><%= t[1] %></td>
      <td style="color: <%= t[2] < 0 ? 'red' : 'green' %>"><%= t[2].round(2) %></td>
      <td style="color: <%= t[3] < 0 ? 'red' : 'green' %>"><%= t[3] %></td>
      <td><%= t[4] %></td>
    </tr>
  <% end %>
 </table>

 @@transactions
 <table>
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Type</th>
  <th>Reference</th>
    <th>Stock</th>
    <th>Symbol</th>
    <th>Quantity</th>
    <th>Price</th>
    <th>Amount</th>
  </tr>
  <% @transactions.each do |t| %>
    <tr>
      <td><%= t[0] %></td>
      <td><%= t[1] %></td>
      <td><%= t[2] %></td>
      <td><%= t[3] %></td>
      <td><%= t[4] %></td>
      <td><%= t[5] %></td>
      <td style="color: <%= t[6] < 0 ? 'red' : 'green' %>"><%= t[6] %></td>
      <td style="color: <%= t[7] < 0 ? 'red' : 'green' %>"><%= t[7].round(2) %></td>
      <td style="color: <%= t[8] < 0 ? 'red' : 'green' %>"><%= t[8].round(2) %></td>
    </tr>
  <% end %>
 </table>
	# frozen_string_literal: true

	require 'sinatra'
	require 'sinatra/reloader' if development?
	require 'sqlite3'
	require 'pdf-reader'

	set :environment, :development

	DB_FILE = 'transactions.db'

	def get_pdf_files
	Dir.glob('*.pdf')
	end

	# Setup database
	def setup_database
	db = SQLite3::Database.new(DB_FILE)
	db.execute <<-SQL
	CREATE TABLE IF NOT EXISTS transactions (
	id INTEGER PRIMARY KEY,
	date TEXT,
	type TEXT,
	ref TEXT,
	stock TEXT,
	symbol TEXT,
	quantity INTEGER,
	price REAL,
	amount REAL
	);
	SQL
	db.close
	end

	# Parse PDF and extract transactions
	def parse_pdf
	transactions = {}
	get_pdf_files.each do \|pdf_file\|
	reader = PDF::Reader.new(pdf_file)

	reader.pages.each do \|page\|
	text = page.text
	lines = text.lines.map(&:strip).filter { \|a\| a.to_s > '' }
	lines.each_with_index do \|line, index\|
	next if index < 2
	unless index > 1 && line.match?(/Quantity/) && lines[index - 1].match?(/(.*) \(.+\)/) && lines[index - 2].match?(/Buy\|Sell/)
	next
	end

	quantity = line.match(/Quantity: (\d+.?\d*)/)[1].to_f
	stock = lines[index - 1].match(/(.*) \((.+)\)/)[1].strip
	symbol = lines[index - 1].match(/(.*) \((.+)\)/)[2].strip
	parts = lines[index - 2].split
	type = parts[1]
	quantity = type == 'Buy' ? quantity : -quantity
	price = parts[3].tr("’", '').tr("'", '').to_f
	ref = parts[2]
	amount = type == 'Buy' ? -price : price
	date = parts[-2] # Extract the date

	transactions[ref] = [date, type, ref, stock, symbol, quantity, price, amount]
	end
	end
	end
	transactions
	end

	# Insert transactions into SQLite3
	def insert_transactions
	db = SQLite3::Database.new(DB_FILE)
	transactions = parse_pdf

	db.execute('DELETE FROM transactions')
	transactions.each do \|ref, t\|
	result = db.execute('SELECT * FROM transactions where ref = ?', ref)
	if result.length.positive?
	pp t
	pp result
	# update
	db.execute(
	'UPDATE transactions SET date = ?, type = ?, ref = ?, stock = ?, symbol = ?, quantity = ?, price = ?, amount = ? WHERE ref = ?', t, ref
	)
	else
	db.execute(
	'INSERT INTO transactions (date, type, ref, stock, symbol, quantity, price, amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', t
	)
	end
	end

	db.close
	end

	# Setup database and insert transactions
	setup_database
	insert_transactions

	# Sinatra Web Interface
	get '/' do
	db = SQLite3::Database.new(DB_FILE)
	@transactions = db.execute('SELECT stock, symbol, sum(amount) as profit, sum(quantity) as quantity, count(*) as c FROM transactions group BY stock order by profit desc')
	db.close
	erb :index
	end

	get '/transactions' do
	db = SQLite3::Database.new(DB_FILE)
	@transactions = db.execute('SELECT * FROM transactions order by stock desc')
	db.close
	erb :transactions
	end

	__END__

	@@layout
	<!DOCTYPE html>
	<html>
	<head>
	<title>Stock Transactions</title>
	<style>
	body { font-family: Arial, sans-serif; margin: 0; padding: 0; }
	.container { width: 90%; margin: 0 auto; padding: 20px; }
	table { width: 100%; border-collapse: collapse; margin-top: 20px; }
	th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
	th { background-color: #f4f4f4; color: #333; }
	tr:nth-child(even) { background-color: #f9f9f9; }
	tr:hover { background-color: #f1f1f1; }
	h1 { color: #333; text-align: center; margin-top: 20px; }
	</style>
	</head>
	<body>
	<div class="container">
	<h1>Stock Transactions</h1>
	<%= yield %>
	</div>
	</body>
	</html>

	@@index
	<table>
	<tr>
	<th>Stock</th>
	<th>Symbol</th>
	<th>Profit</th>
	<th>Quantity</th>
	<th>Number of transactions</th>
	</tr>
	<% @transactions.each do \|t\| %>
	<tr>
	<td><%= t[0] %></td>
	<td><%= t[1] %></td>
	<td style="color: <%= t[2] < 0 ? 'red' : 'green' %>"><%= t[2].round(2) %></td>
	<td style="color: <%= t[3] < 0 ? 'red' : 'green' %>"><%= t[3] %></td>
	<td><%= t[4] %></td>
	</tr>
	<% end %>
	</table>

	@@transactions
	<table>
	<tr>
	<th>ID</th>
	<th>Date</th>
	<th>Type</th>
	<th>Reference</th>
	<th>Stock</th>
	<th>Symbol</th>
	<th>Quantity</th>
	<th>Price</th>
	<th>Amount</th>
	</tr>
	<% @transactions.each do \|t\| %>
	<tr>
	<td><%= t[0] %></td>
	<td><%= t[1] %></td>
	<td><%= t[2] %></td>
	<td><%= t[3] %></td>
	<td><%= t[4] %></td>
	<td><%= t[5] %></td>
	<td style="color: <%= t[6] < 0 ? 'red' : 'green' %>"><%= t[6] %></td>
	<td style="color: <%= t[7] < 0 ? 'red' : 'green' %>"><%= t[7].round(2) %></td>
	<td style="color: <%= t[8] < 0 ? 'red' : 'green' %>"><%= t[8].round(2) %></td>
	</tr>
	<% end %>
	</table>