電子書籍購入一覧作成(kobo honto dmm) 

# 複数のところで買ってたらわからなくなったよ
という事で、一覧を作成することにしました。
kobo honto dmmのPCアプリは、SQLiteで書籍を管理しているので
その中から、必要な情報を引っ張り出してくることにする。

### 手順
- 各アプリを起動して、書籍情報を最新の状態にする。
- 使用している、SQLiteのDBを調べる。
  kobo  C:\Users\xxxxxx\AppData\Local\Kobo\Kobo Desktop Edition\Kobo.sqlite
  honto C:\Users\xxxxx\AppData\\Local\\DNP\honto\\BookShelf.db
  dmm   C:\Users\xxxxx\AppData\Roaming\DMM\DMMbookviewer2\dmmbookshelf.sqlite3
- SQLiteを使えるようにして、対応するSQLを実行する。
  お好みの言語等(python)で実行してください。
  [A5:SQL Mk-2](https://a5m2.mmatsubara.com/)などで実行して、そのままCSV等に出力できます。

 SQL文は、自分用にNotionでインポート用に作ってあります。
 レイアウトギャラリー表示で使えるようにしてます。 
 お好みでSQL文を変更してください。不要な項目の削除、ソート順など。
 項目によって、カンマが含まれるものがある為、注意が必要。
 出力内容  書名,仮名,表紙,著者,ReadLink,eShop,販売ページ,カテゴリ,出版社,説明,ID,ISBN,購入日
 ※出力されない項目もあります。

##### kobo用 
```kobo.sql
SELECT
    quote(Title) Title
    , "" kana   
    , "" Cover 
    , quote(Attribution) author
    , "" ReadLink
    , "kobo" eShop
    , "https://books.rakuten.co.jp/rk/" || REPLACE(CrossRevisionId,'-','') || "/"   ShopURL
    , "" category_name
    , quote(Publisher) Publisher
    , quote(Description) Description
    , REPLACE(CrossRevisionId,'-','')   product_Id 
    , ISBN                                 
    , "" purchaseDate 
 FROM
    "content" 
WHERE
ContentType = 6 and
Accessibility = 1 and
___UserID <> "removed"
 
ORDER BY
--    ContentID",    TitleKana
    TitleKana, ContentID
--    ___SyncTime desc,  TitleKana, ContentID 
```
 ##### honto用 
```honto.sql
SELECT
     BM.bookTitle     Title
    ,BM.bookTitleKana kana
    ,BC.thumbnailUrl  Cover
    ,BM.displayAuthors2 author
    ,""  ReadLink
    ,"honto" eShop
    ,ifnull(BM.purchaseUrl, 'https://honto.jp/ebook/pd_' || replace(substr(BC.thumbnailUrl,35,9),'/','') || '.html')  ShopURL   
    ,BM.ph2_category category_name
    ,BM.publisher     Publisher 
    ,""   Description 
    ,BC.bookId product_Id       -- bookId
    ,BM.isbn ISBN
    ,BC.purchaseDate             
FROM
    BOOK_CONTENT_TBL AS BC,
    BOOK_COMMON_TBL AS BM

WHERE
    BC.bookId = BM.bookId 
    and BC.purchaseFlag =1
    and BC.downloadLimitDate="" 
    and BC.userid <>""
    
ORDER BY
    bookTitlekana asc, purchaseDate desc,    userId asc
--  purchaseDate desc, bookTitlekana asc,    userId asc
```
 ##### dmm用
 ※category_idによって、ShopURLがへ変わる可能性があります
```dmm.sql
SELECT
    title 'Title'
    , title_ruby   kana
    , image_src      Cover
    , main_author_name          author
    , "" ReadLink
    ,"dmm" eShop
    , case
         when category_id = '1' then   "https://book.dmm.co.jp/detail/" || product_Id  || "/"  
         when category_id = '2' then  
              case when series_id = '-1' then "https://book.dmm.com/detail/" || product_Id  || "/"  
               else "https://book.dmm.com/product/" || series_id || "/"  || product_Id  || "/"  
              end
         when category_id = '7' then   "https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=" || product_Id  || "/"  
              
      end as ShopURL
    , category_name   category_name
    , ""  "Publisher"  
    ,"" Description
    , product_Id   
    , "" ISBN 
    , purchase_date 'purchaseDate'                          -- purchase_date

FROM
    my_library 
WHERE
    category_id IS NOT  -1 
ORDER BY
--    purchase_date  desc    , product_Id    , sp_number
---      purchase_date, title_ruby   , product_Id    , sp_number
      title_ruby,purchase_date, product_Id    , sp_number

```

 ##### おまけ 
 powershell用 タブ区切りで出力
 ※powershellでSQLiteが使えるようにしておく。 
 
```booklist.ps1
#PowerShell -ExecutionPolicy RemoteSigned ./booklist.ps1
using namespace System.Windows.Forms
Import-Module "./System.Data.SQLite.dll"

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
[Application]::EnableVisualStyles()

$hontoDB = "C:\Users\xxxx\AppData\Local\DNP\honto\BookShelf.db"
$koboDB  = "C:\Users\xxxx\AppData\Local\Kobo\Kobo Desktop Edition\Kobo.sqlite"
$dmmDB   = "C:\Users\xxxx\AppData\Roaming\DMM\DMMbookviewer2\dmmbookshelf.sqlite3"

#--honto--------------------------------------------------
#書名,仮名,表紙,著者,(ReadLink),honto,販売ページ,カテゴリ,出版社,(説明),ID,ISBN,購入日L
$hontoSQL = "SELECT BM.bookTitle Title, BM.bookTitleKana kana, BC.thumbnailUrl Cover, BM.displayAuthors2 author
   ,'' AS ReadLink,'honto' eShop,ifnull(BM.purchaseUrl, 'https://honto.jp/ebook/pd_' || replace(substr(BC.thumbnailUrl,35,9),'/','') || '.html')  ShopURL
   ,BM.ph2_category category_name, BM.publisher Publisher, '' Description, BC.bookId product_Id, BM.isbn ISBN, BC.purchaseDate
  FROM BOOK_CONTENT_TBL AS BC, BOOK_COMMON_TBL AS BM
  WHERE BC.bookId = BM.bookId and BC.purchaseFlag =1 and BC.downloadLimitDate='' and BC.userid <>''
  ORDER BY bookTitlekana asc, purchaseDate desc,  userId asc
  --    purchaseDate desc, bookTitlekana asc, userId asc"

#--kobo--------------------------------------------------
#書名,(仮名),(表紙),著者,(ReadLink),kobo,販売ページ,(カテゴリ),出版社,説明,ID,ISBN,(購入日)　--kobo
$koboSQL = "SELECT
    quote(Title) Title                                  -- Title
    , '' kana   
    , '' Cover 
    , quote(Attribution) author
    , '' ReadLink
    , 'kobo' eShop
    , 'https://books.rakuten.co.jp/rk/' || REPLACE(CrossRevisionId,'-','') || '/'   ShopURL
    , '' category_name
    , quote(Publisher) Publisher                            -- Publisher
    , quote(Description) Description
    ,  REPLACE(CrossRevisionId,'-','')   product_Id 
    , ISBN                                      -- ISBN
    , DateLastRead  -- 購入日がないので最後に読んだ日
 FROM
    'content' 
WHERE
ContentType = 6 and
Accessibility = 1 and
___UserID <> 'removed'
 
ORDER BY
--    ContentID,    TitleKana
    TitleKana, ContentID
--    ___SyncTime desc,    TitleKana, ContentID "

#--dmm--------------------------------------------------
#書名,仮名,表紙,著者,(ReadLink),dmm,販売ページ,カテゴリ,(出版社),(説明),ID,(ISBN),購入日　--dmm
$dmmSQL = "SELECT
    title 'Title'
    , title_ruby   kana
    , image_src      Cover
    , main_author_name          author
    , "" ReadLink
    ,"dmm" eShop
    , case
         when category_id = '1' then   "https://book.dmm.co.jp/detail/" || product_Id  || "/"  
         when category_id = '2' then  
              case when series_id = '-1' then "https://book.dmm.com/detail/" || product_Id  || "/"  
               else "https://book.dmm.com/product/" || series_id || "/"  || product_Id  || "/"  
              end
         when category_id = '7' then   "https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=" || product_Id  || "/"  
              
      end as ShopURL
    , category_name   category_name
    , ""  "Publisher"  
    ,"" Description
    , product_Id   
    , "" ISBN 
    , purchase_date 'purchaseDate'                          -- purchase_date

FROM
    my_library 
WHERE
    category_id IS NOT  -1 
ORDER BY
--    purchase_date  desc    , product_Id    , sp_number
---      purchase_date, title_ruby   , product_Id    , sp_number
      title_ruby,purchase_date, product_Id    , sp_number

##-------------------------------------------------------------------------
# フォーム
$frame = New-Object Form -Property @{
    Text            = '購入一覧作成'
    Size            = New-Object Drawing.Size(300, 250)
    MaximizeBox     = $false
    FormBorderStyle = 'FixedDialog'
    Font            = New-Object Drawing.Font('Meiryo UI', 8.5)
    StartPosition = 'CenterScreen'
}
$Button1 = New-Object Button -Property @{
    Text        = '実行'
    Location    = New-Object Drawing.Point(40, 150)
    Size        = New-Object Drawing.Size(200, 30)
    DialogResult = "OK"
}
   
# ラジオボタン定義
$RadioButton1 = New-Object RadioButton -Property @{
    Size        = New-Object Drawing.Size(60, 17)
    Text        = 'honto'
    Location    = New-Object Drawing.Point(20, 25)
    Checked     = $True
}

$RadioButton2 = New-Object RadioButton -Property @{
    Size        = New-Object Drawing.Size(60, 17)
    Text        = 'kobo'
    Location    = New-Object Drawing.Point(20, 55)
}
$RadioButton3 = New-Object RadioButton -Property @{
    Size        = New-Object Drawing.Size(60, 17)
    Text        = 'dmmm'
    Location    = New-Object Drawing.Point(20, 85)
}
$GroupBox1 = New-Object GroupBox -Property @{
    Text        = '電子書店'
    Location    = New-Object Drawing.Point(20, 10)
    Size            = New-Object Drawing.Size(120, 120)
}

$GroupBox1.Controls.AddRange(@($RadioButton1, $RadioButton2, $RadioButton3))
$frame.Controls.Add($GroupBox1)
$frame.Controls.Add($Button1)
$result = $frame.ShowDialog()
if ($result -ne "OK") { exit }

if ($RadioButton1.Checked){
    $dbFile=$hontoDB
    $textSQL=$hontoSQL
    $out_file="honto.txt"
}
if ($RadioButton2.Checked){
    $dbFile=$koboDB
    $textSQL=$koboSQL
    $out_file="kobo.txt"
}
if ($RadioButton3.Checked){
    $dbFile=$dmmDB
    $textSQL=$dmmSQL
    $out_file="dmm.txt"
}
Out-File $out_file

#--------------------------------------------------------------------------
$connectionString = "Data Source=$dbFile"
$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText=$textSQL
$reader = $command.ExecuteReader()

while ($reader.Read())
{
  $FC = $reader.FieldCount -1
  $csv=$reader[0..$FC] -join "`t"
  Write-Output $csv
  Write-Output $csv | Out-File -Append $out_file
}
$connection.Close()

```