java Clean stuff.txt

java -jar dbe.jar squaredance.db &

java -jar makeweb.jar -map ../upload/
time java -jar copyfiles.jar . ../upload/

java -jar filebrowser.jar /home/sidney/home/upload/ //squaree8@squaredancingrochester.org/home1/squaree8/public_html

java -jar filebrowser.jar /home/sidney/home/upload/ //swm@glados.cs.rit.edu/home/fac/swm/upload

java -jar filebrowser1.jar /home/sidney/home/upload/ //squaree8@squaredancingrochester.org/home1/squaree8/public_html

java -jar filebrowser1.jar /home/sidney/home/upload/ //swm@glados.cs.rit.edu/home/fac/swm/upload

java -jar filebrowser.jar /home/sidney/home/upload/ /home/sidney/home/squaredance

java -jar filebrowser1.jar /home/sidney/home/squaredance/tmp&
java -jar filebrowser1.jar //sidney@server/home/sidney/home/squaredance/tmp&

java -jar filebrowser1.jar -ssh squaree8@squaredancingrochester.org

backup

java -jar filebrowser.jar /media/raid /media/sidney/USB1/&

java -jar filebrowser.jar /home/sidney/ /media/raid/sidney/&

Carl's Process additions:

java -jar filebrowser.jar C:/cygwin64/home/carlw/upload/ //squaree8@squaredancingrochester.org/home1/squaree8/public_html

java -jar filebrowser.jar C:/cygwin64/home/carlw/upload/ C:/cygwin64//home/carlw/squaredance

pdftk PYFSF.pdf PYFSB.pdf cat output PYFS.pdf

JPG CONVERSION

[scale images to 500 px width]
for i in *.jpg; do gimp -i -b "(scale-width \"$i\" 500)" -b "(gimp-quit 0)"; done;
----------------------------------------------
[make 500 px width jpgs from pdfs (for articles and dance flyers)]
for i in *.pdf; do convert -thumbnail x500 ${i}[0] -flatten ${i/.pdf}.jpg; done;
----------------------------------------------
[make 100 px width jpgs from pdfs (for "icons")]
for i in *.pdf; do convert -thumbnail x100 ${i}[0] -flatten ${i/.pdf}.jpg; done;
----------------------------------------------
[change case from PDF to pdf (may not work on windows)]
for i in *.PDF; do touch $i; mv $i ${i/.PDF}.pdf; done
----------------------------------------------
[change case from JPG to jpg (may not work on windows)]
for i in *.JPG; do touch $i; mv $i ${i/.JPG}.jpg; done
----------------------------------------------
for i in *.pdf; do mv $i CWA-${i:4:4}-${i:8:2}-${i:10:10}; done;
----------------------------------------------
[for scanning old promenader issues]
for i in *.PDF; do convert -density 400 $i -type bilevel -threshold 75% -compress fax ${i/.PDF}.pdf; done
------------------------------------------------------------------
[for scanning old promenader issues]
for i in ???????????.PDF;\
do (/usr/local/bin/convert -density 400 $i -threshold 75% -type Bilevel -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done

[for scanning old promenader issues]
for i in ???????.PDF;\
do (/usr/local/bin/convert -density 400 $i -threshold 60% -type Bilevel -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done
------------------------------------------------------------------
[for scanning old promenader issues]
for i in *.PDF;\
do (/usr/local/bin/convert -density 400 $i -threshold 55% -type Bilevel -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done
------------------------------------------------------------------
[for scanning old promenader issues]
for i in ???????????.PDF;\
do (convert -density 400 $i -threshold 75% -type Bilevel -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done

[for scanning old promenader issues]
for i in ???????.PDF;\
do (convert -density 400 $i -threshold 60% -type Bilevel -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done
------------------------------------------------------------------
[for scanning old promenader issues]
for i in ???????????.PDF;\
do (convert -density 400 $i -type Bilevel -threshold 75% -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done

[for scanning old promenader issues]
for i in ???????.PDF;\
do (convert -density 400 $i -type Bilevel -threshold 60% -compress Fax ${i/.PDF}.pdf;\
evince ${i/.PDF}.pdf)&\
done
------------------------------------------------------------------
[for scanning old promenader issues]
for i in *_???.pdf;\
do (convert -density 400 $i -type bilevel -threshold 55% -compress fax $i;\
evince $i)&\
done
------------------------------------------------------------------
[for scanning old promenader issues]
convert -density 400 x_011.pdf -type bilevel -threshold 55% -compress fax x_011.pdf // for pink flyers
----------------------------------------------
[delete all club news from sandbox]
delete from clubnewsproof
----------------------------------------------
[delete all articles from sandbox]
delete from articleproof
----------------------------------------------
(save time by doing club news with articles)

replace two spaces with one space

replace all <space><lf> with <lf>
replace all <lf><space> with <lf>
replace all <lf> with <lf><lf>
replace all <lf><lf><lf> with <lf><lf>

replace & with &amp;

set line lengths

find funny characters - replace all - with -

replace smart quotes with markup  - do &lsquo; &rsquo; &ldquo; &rdquo;

replace ' and " appropriately

&mdash; for sentence hyphens (no spaces either side)

&ndash; for times etc. 2:00-3:00 or 2-5 or 2015-2016

&nbsp;&hellip; <space> for sentence ellipses

spell check

<span style="color:red">

<ol style="list-style-type:lower-alpha">

====================================
gimp script:

;;; gimp -i -b '(scale-width "x.jpg" 500)' -b '(gimp-quit 0)'

(define (scale-width filename new-width)
  ;(set! new-width (string->number new-width))
  ;(print "HI")
  (let* ((image (car(gimp-file-load RUN-NONINTERACTIVE filename filename)))
         (drawable (car (gimp-image-get-active-layer image)))
         (width (car (gimp-drawable-width drawable)))
         (height (car (gimp-drawable-height drawable))))
    ;(print 1)
    ;(print width)
    ;(print height)
    (gimp-image-scale image new-width (/ (* height new-width) width))
    ;(print 2)
    (gimp-file-save RUN-NONINTERACTIVE image drawable filename filename)
    ;(print 3)
    (gimp-image-delete image)
    ;(print 4)
    ;(+ "xxx" "yyy")
    ))

====================================
----------------------------


====================================
====================================


WITH RECURSIVE
  cnt(x) AS (VALUES('2018-01-05') UNION ALL SELECT date(x, '+7 day') FROM cnt WHERE x<'2019-01-01')
SELECT x FROM cnt;

WITH RECURSIVE
  cnt(x) AS (VALUES('2018-01-05') UNION ALL SELECT date(x, '+7 day') FROM cnt WHERE x<'2019-01-01')
SELECT x as date, 63 as venue, '1:30' as time, 'BM' as flags FROM cnt;

create temp table x as
WITH RECURSIVE
  cnt(x) AS (VALUES('2018-01-05') UNION ALL SELECT date(x, '+7 day') FROM cnt WHERE x<'2019-01-01')
SELECT x as date, 63 as venue, '1:30' as time, 'BM' as flags FROM cnt;

WITH RECURSIVE
  cnt(x, c) AS (VALUES('2018-01-05', 0) UNION ALL SELECT date(x, '+7 day'), c+1 FROM cnt WHERE x<'2019-01-01')
SELECT x as date, 63 as venue, '1:30' as time, 'BM' as flags, c FROM cnt;

WITH RECURSIVE
  cnt(x) AS (VALUES(5) UNION ALL SELECT 7 FROM cnt limit 10)
SELECT x FROM cnt;

select * from monthname join weekday

CREATE temp TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;

with t0(x, y) as (values (1, 'aaa'), (2, 'ccc'), (3, 'bbb')) SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x

with d(month) as (select distinct substr(date, 1, 7) as month from event
  order by substr(date, 1, 7)),
    dd(month, sum) as (select month, row_number() over (order by month)
      as sum from d order by month)
  select * from dd


SELECT
  first_value(date) over (order by date),
  lag(date, 1, (select first_value(date) over (order by date) from event)) OVER (
    ORDER BY date
  ) as prev,
  lead(date) OVER (
    ORDER BY date
  ) as next
FROM event;

SELECT
  date as curr,
  lag(date, 1, (select first_value(date) over (order by date) from event)) OVER (
    ORDER BY date
  ) as prev,
  lead(date) OVER (
    ORDER BY date
  ) as next
FROM event;

SELECT
  date as curr,
  lag(date, 1, (select first_value(date) over (order by date) from event)) OVER (
    ORDER BY date
  ) as prev,
  lead(date, 1, (select first_value(date) over (order by date desc) from event)) OVER (
    ORDER BY date
  ) as next
FROM event;

with d(month) as (select distinct substr(date, 1, 7) as month from event
  order by substr(date, 1, 7)),
    dd(month, sum) as (select month, row_number() over (order by month)
      as sum from d order by month)
  select *, (select(min(month)) from dd), (select(max(month)) from dd) from dd

--------------------------------
create temp table ddd as

with d(month) as (select distinct substr(date, 1, 7) as month from event
  order by substr(date, 1, 7)),
    dd(month, sum) as (select month, row_number() over (order by month)
      as sum from d order by month)
SELECT
  month as curr,
  lag(month, 1, (select min(month) from dd)) OVER (
    ORDER BY month
  ) as prev,
  lead(month, 1, (select max(month) from dd)) OVER (
    ORDER BY month
  ) as next,
  sum
FROM dd;
--------------------------------
create temp table ddd as

with d(month) as (select distinct substr(date, 1, 7) as month from event order by substr(date, 1, 7)),
    dd(month, sum) as (select month, row_number() over (order by month) as sum from d order by month),
    minmax(minimum, maximum) as (select min(month), max(month) from dd)
SELECT
  month as curr,
  lag(month, 1, (select minimum from minmax)) OVER (
    ORDER BY month
  ) as prev,
  lead(month, 1, (select maximum from minmax)) OVER (
    ORDER BY month
  ) as next,
  sum, (select maximum from minmax), (select minimum from minmax)
FROM dd;
--------------------------------
create temp table ddd as

with d(month) as (select distinct substr(date, 1, 7) as month from event order by substr(date, 1, 7)),
    dd(month) as (select month from d order by month),
    minmax(minimum, maximum) as (select min(month), max(month) from dd)
SELECT
  lag(month, 1, (select minimum from minmax)) OVER (
    ORDER BY month
  ) as prev,
  month as curr,
  lead(month, 1, (select maximum from minmax)) OVER (
    ORDER BY month
  ) as next
FROM dd;
--------------------------------


insert into event(date, venue, time, flags) select * from x

drop table x

====================================
====================================

Promenader Publication Process

CLUB NEWS

Collect all club news text into an emacs document. Separate paragraphs with a single blank line.

Normalize line lengths for convenience.

Substitute for & first.

Substitute for dumb quotes, smart quotes, ..., bullet, <, >

Remove previous items from last time from table clubnewsproof.

Copy and paste into table clubnewsproof the various fields. You have to look up the clubid in table club.

Make the web site: java -jar makeweb -map ../upload

Copy to upload area: java -jar copyfiles.jar . ../upload

repeat to get fullsitemap correct

Make the web site: java -jar makeweb -map ../upload

Copy to upload area: java -jar copyfiles.jar . ../upload

Post to the web and let the editor know. DO NOT UPDATE ANYTHING IN ./clubs/Cloverleafs

FLYERS

convert using following command on linux:

for i in *.pdf; do convert -thumbnail x500 ${i}[0] -flatten ${i/.pdf}.jpg; done;

Then update classifieds table with:

startdate: date flyer should appear in ads and flyers
sortdate: date used to sort order of flyers - generally event date
enddate: date to stop displaying - usually day after event

dates for paid flyers that are not event related are start of month to end of month.

ARTICLES

use gimp and set width of pictures to 500 if bigger

change issue date in table "content" promenader.date

----------------------------------------

RELEASE PROCESS

[Copy all new articles from articleproof to article. (Note that
articlecontent does not change.)]

insert into article select * from articleproof order by date, rank,
articleid

[delete articles from table article that are not to be published]

[Copy all club news from clubnewsproof to clubnews.]

insert into clubnews select * from clubnewsproof

[Update table content, line promenader/date to reflect current issue
number. There are 4 issues a year. Increment the roman numeral in
WINTER. Don't forget to update the year and month.]

[Update two links in content/newdancer/body to point to current
articles and club news.]

Update DOR flyer url in content/federation/body

[Compressed (pdf) files of the promenader issue go in the directory
"compressed" with the name
Articles/ClubNews/FlyerThumbnails-xxxx-xx.pdf]

[Modify table singlepage, line printpdfs.html/content by adding links
to the new files. (copy-paste the top entry and edit appropriately.)]


----------------------------------
select tbl_name, name, sql from main.sqlite_master where type = 'trigger' order by tbl_name, name
----------------------------------
DELETE FROM cache WHERE id IN
(SELECT cache.id FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL);

DELETE FROM cache WHERE id NOT IN (SELECT id from main);

DELETE FROM FirstTable WHERE firstTableId NOT IN (SELECT SecondTable.firstTableId FROM SecondTable LEFT JOIN FirstTable ON FirstTable.firstTableId=SecondTable.firstTableId)

DELETE FROM mytab       
WHERE EXISTS (SELECT *
              FROM deltab
              WHERE deltab.ID = mytab.ID)

delete from eventpdf where event not in (select eventid from event)
delete from eventcaller where eventid not in (select eventid from event)
delete from eventcuer where eventid not in (select eventid from event)
delete from eventclub where eventid not in (select eventid from event)
delete from specialdances where eventid not in (select eventid from event)

select event  from eventpdf where event not in (select eventid from event)
select eventid from eventcaller where eventid not in (select eventid from event)
select eventid from eventcuer where eventid not in (select eventid from event)
select eventid from eventclub where eventid not in (select eventid from event)
select eventid from specialdances where eventid not in (select eventid from event)

select * from clubofficer join person on clubofficer.person = person.personid  order by club,position