New Arctos Report Template Request - UCM Herp Jar labels

ebraker commented 1 year ago

Description of the report - I'd like to move the UCM Herp templates over to the new tool. I technically have four labels that have slight variations on the same format: big, small, US (gets state + county) vs. not-US (gets Country + 1st administrative division). Possibly these could be coded to just a large and small format version (note that a lot of the longer place names are truncated on the small label versions...there's probably a more elegant way to do this with character limits but this is the system I was able to figure out). I don't think these would scale correctly if consolidated to a single label version - "big" I print 4x15 inch labels on thermal printer ribbon for half gallon and gallon jars, "small" labels are generally printed 4x8, 4x6, or 4x5 inches for smaller jar sizes. See attached pdf examples. Also, the vertical line is helpful for cutting labels since we print on a continuous roll.

Collection for which the report is needed - UCM:Herp

If this is a request to upgrade from an existing ColdFusion report in Arctos complete the following:

SQL used in ColdFusion report - copy the SQl from your old report and paste it here - begin and end it with ``` or it will be invisible

Report: UCM_Jar_consolidated_country_Large** Prefunction: catnum_to_list** '''select flat.scientific_name, flat.family, case when flat.country='Democratic Republic of the Congo' then 'Dem Rep Congo' when flat.country is NULL then 'no higher geography data' when flat.country='Something Else' then 'replacement string' else flat.country END country, case when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region','')) when flat.state_prov='Guangdong Province' then 'Guangdong' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)'''

2. Report: UCM_JAR_consolidated_country_SMALL Prefunction: catnum_to_list '''select flat.scientific_name, flat.family, case when flat.country='Democratic Republic of the Congo' then 'Dem. Rep. Congo' when flat.country='Papua New Guinea' then 'PNG' when flat.country is NULL then 'no higher geography data' when flat.country='Something Else' then 'replacement string' else flat.country END country, case when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region','')) when flat.state_prov='Chimbu Province' then 'Chimbu' when flat.state_prov='Gafsa Governorate' then 'Gafsa Gov.' when flat.state_prov='Guangdong Province' then 'Guangdong' when flat.state_prov='Stann Creek District' then 'Stann Creek Dist.' when flat.state_prov='Languedoc-Roussillon' then 'Languedoc-Rouss.' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol%' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin%' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)'''

3. Report: UCM_JAR_consolidated_state_LARGE Prefunction: catnum_to_list '''select flat.scientific_name, flat.family, flat.country, case when flat.state_prov is NULL then 'no higher geography data' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin-fixed, stored in 70% ethanol' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin%' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)'''

4. Report: UCM_JAR_consolidated_state_SMALL Prefunction: catnum_to_list '''select flat.scientific_name, flat.family, flat.country, case when flat.state_prov is NULL then 'no higher geography data' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, case when flat.county='Arapahoe County' then 'Arapahoe' when flat.county='Allegheny County' then 'Allegheny' when flat.county='Barnstable County' then 'Barnstable' when flat.county='Benton County' then 'Benton' when flat.county='Boulder County' then 'Boulder'
when flat.county='Bucks County' then 'Bucks' when flat.county='Carroll County' then 'Carroll' when flat.county='Catron County' then 'Catron' when flat.county='Clear Creek County' then 'Clear Creek' when flat.county='Cheboygan County' then 'Cheboygan' when flat.county='Cheyenne County' then 'Cheyenne' when flat.county='Chisago County' then 'Chisago' when flat.county='Crowley County' then 'Crowley' when flat.county='Delaware County' then 'Delaware' when flat.county='Dolores County' then 'Dolores' when flat.county='Douglas County' then 'Douglas' when flat.county='Durham County' then 'Durham' when flat.county='East Carroll Parish' then 'East Carroll' when flat.county='Fairfax County' then 'Fairfax' when flat.county='Garfield County' then 'Garfield' when flat.county='Gunnison County' then 'Gunnison' when flat.county='Hancock County' then 'Hancock' when flat.county='Harrison County' then 'Harrison' when flat.county='Hinsdale County' then 'Hinsdale' when flat.county='Huerfano County' then 'Huerfano' when flat.county='Imperial County' then 'Imperial' when flat.county='Jackson County' then 'Jackson' when flat.county='Jefferson County' then 'Jefferson' when flat.county='Kit Carson County' then 'Kit Carson' when flat.county='Lincoln County' then 'Lincoln' when flat.county='Los Angeles County' then 'Los Angeles' when flat.county='Mason County' then 'Mason' when flat.county='Middlesex County' then 'Middlesex' when flat.county='La Plata County' then 'La Plata' when flat.county='Las Animas County' then 'Las Animas' when flat.county='Montezuma County' then 'Montezuma' when flat.county='Montgomery County' then 'Montgomery' when flat.county='Montrose County' then 'Montrose' when flat.county='Prowers County' then 'Prowers' when flat.county='Rio Blanco County' then 'Rio Blanco' when flat.county='Riverside County' then 'Riverside' when flat.county='Sierra County' then 'Sierra' when flat.county='San Diego County' then 'San Diego' when flat.county='Santa Clara County' then 'Santa Clara' when flat.county='San Bernardino County' then 'San Bernardino' when flat.county='Socorro County' then 'Socorro' when flat.county='Washington County' then 'Washington' when flat.county='Something Else' then 'replacement string' else flat.county END county, flat.cat_num, case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol%' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin%' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)'''

Example labels: b_132.pdf b_3956.pdf s_2227.pdf s_504.pdf

Big 4x15 labels:

Small 4x6 labels:

Priority: Normal (as long as I can access the old reporter , I'm fine).

dustymc commented 1 year ago

@ebraker (1) is started as UCM_Jar_consolidated_country_Large

It should make the query, consolidate catnums, and dump the data. Please let me know if you need something different up to that point, and what we need to do to from there.

I think we should probably get one ironed out before looking at the others (and the first should copy to the rest with some minor adjustments), but I'm up for whatever.

I've been testing with https://arctos.database.museum/Reports/reporter.cfm?action=view&report_id=78&container_id=21513753

ebraker commented 1 year ago

Thanks @dustymc. The test container is not a great example since it contains an error that we have to fix (mixed taxa) and is not intended for US localities.

Though it does highlight the fact that our existing labels have some built in QC measures for mixed taxa and mixed higher geogs that are important to retain since they flag the jar as needing to be rehoused. This is how the label should look:

I used the UCM_Jar_consolidated_state_Large to generate this label so that county would be placed up with State/province.

If using the UCM_Jar_consolidated_country_Large label, you'll see that county gets bumped down to spec_loc because this format is intended to list Country state/province on the top line (seems to be a spacing issue as well):

This container is probably a better example for large country-level labels: https://arctos.database.museum/EditContainer.cfm?container_id=21513722

ebraker commented 1 year ago

@mkoo

mkoo commented 1 year ago

@lkvoong @dustymc This is going to require migrating over the previous prefunction (emily cites it above as Prefunction: catnum_to_list) which has the QC she mentions for dealing with mixed taxonomy etc. It also looks like all the labels use the same prefunction and SQL. Can a new SQL which incorporates that be posted or perhaps have a new function that can be called?

After that then it's HTML/CSS we can work on Thanks!

dustymc commented 1 year ago

@mkoo that functionality is in https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=78

ebraker commented 1 year ago

FYI I just got a generic email invite to an Adobe Cold Fusion summit for fall...seems like it isn't going away anytime soon?

mkoo commented 1 year ago

@mkoo that functionality is in https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=78

Cool-- thanks!

mkoo commented 1 year ago

FYI I just got a generic email invite to an Adobe Cold Fusion summit for fall...seems like it isn't going away anytime soon?

Yes, but our capacity to pay for the latest versions have gone away....

ebraker commented 7 months ago

@dustymc @mkoo @lkvoong I'm highlighting this help request again given that Coldfusion is going away

dustymc commented 7 months ago

Sorry, I think I'm lost on what's needed. https://arctos.database.museum/EditContainer.cfm?container_id=21513722 > print > UCM_Jar_consolidated_country_Large has "catnum_to_list" functionality

For clarity: Adobe isn't going away, ColdFusion isn't going away, cfreport was deprecated in ~2011 and fully desupported in ~2016. As far as I know, there is no environment that will run the cfreport/CFR reports in any way.

ebraker commented 7 months ago

@dustymc I have to use the legacy reporter to print any jar label... is access to the legacy reporter going away?

UCM_Jar_consolidated_country_Large
UCM_JAR_consolidated_country_SMALL
UCM_JAR_consolidated_state_LARGE
UCM_JAR_consolidated_state_SMALL
UCM_Mamm_consolidated_LARGE
UCM_Mamm_consolidated_MEDIUM
UCM_Mamm_single_label
UCM_Mamm_single_LARGE
UCM_TC_single_lot_label
UCM_cat_num_wet_tag
UCM_fish_single_lot_label
UCM_fish_single_lot_LARGE
UCM_Herps_single_label

dustymc commented 7 months ago

is access to the legacy reporter going away?

Yes, see https://github.com/ArctosDB/arctos/issues/7237, the legacy reporter runs cfr/cfreport.

I think my current question is whether UCM_Jar_consolidated_country_Large is producing the proper "consolidated" data (but @mkoo may already know that). If it is I can adapt it to whatever SQL is needed for the others, if it isn't then I need to know why so I can make it happy (and then adapt that SQL to the others).

ebraker commented 7 months ago

@dustymc yes, UCM_Jar_consolidated_country_Large is functioning correctly. But I also need help with migrating the label format to the new reporter for all of these...

ebraker commented 7 months ago

@dustymc @mkoo @lkvoong I still need help with these fairly urgently since I have someone who will be working half-time on jar inventory and will need to print thousands of labels. Or, if there is a way to get past the firewall to access the old reporter, I am all ears.

ebraker commented 7 months ago

Any way I can try to get permissions for the Coldfusion Server? I really need to print stuff...

Jegelewicz commented 7 months ago

@ebraker can you send me dimensions and a pdf example for the label you want to print?

ebraker commented 7 months ago

Thanks @Jegelewicz. I have two sizes:

small: 4x8 (includes all whitespace and line to indicate where to cut) s_1392.pdf
large: 4x15 (includes all whitespace and line to indicate where to cut) b_2312.pdf

All my reports and prefunctions that should be working correctly.

These are the most-needed reports: UCM_Jar_consolidated_country_Large UCM_JAR_consolidated_country_SMALL UCM_JAR_consolidated_state_LARGE UCM_JAR_consolidated_state_SMALL

ebraker commented 7 months ago

@Jegelewicz there are also a few additional example pdfs in my first comment on this thread

Jegelewicz commented 7 months ago

@ebraker one more question - what size paper are you printing on?

ebraker commented 7 months ago

@Jegelewicz I'm printing on 4" thermal paper (continuous roll)

ebraker commented 7 months ago

@Jegelewicz I also just emailed you the original cold fusion templates if those are helpful for layouts/dimensions

Jegelewicz commented 7 months ago

@dustymc when I try to create UCM_Jar_consolidated_country_Large by cloning Template of a Fluid label, Arctos is telling me it already exists, but I don't see it in the list....

dustymc commented 7 months ago

https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=78

Jegelewicz commented 7 months ago

I do not see that

Jegelewicz commented 7 months ago

Is the report private or something - it does not appear in the list for me.

dustymc commented 7 months ago

Shift-reload, almost got to be in your browser. (Chrome?)

https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=78

Jegelewicz commented 7 months ago

I am using Firefox - always do with Arctos, but it's there now - thanks

Jegelewicz commented 7 months ago

Why is this report so weird? I got in to start editing and now if I try to print to test stuff it isn't in the list....SUPER FRUSTRATING!!!!

OK - it's my fault. I need to be coming from containers - so I need to find a container I can test on....

Jegelewicz commented 7 months ago

@ebraker I don't have access to your collection - this makes it very difficult for me to do anything. You can revoke my access once these are done if you want, but I can't work on this without some data to test things.

ebraker commented 7 months ago

@Jegelewicz, now you should have access to the the ucm_herp collection. And sorry, failed to mention that I print these labels from containers (looking up the "barcode") to query each jar.

Jegelewicz commented 6 months ago

@ebraker the small country label is drafted. UCM_JAR_consolidated_country_SMALL

There needs to be some tweaking, but the basics are there. The only thing is - I cannot figure out how to make it print just one label. @dustymc can you help me figure that out - the only way it prints at all is with the first , but then it prints as many labels as lines in the results. Not sure how to stop that.

dustymc commented 6 months ago

I cannot figure out how to make it print just one label.

Feed it data that has one unique set of stuff-minus-catnum? I don't think that's the request, it looks to me like it's doing what it should be.

Jegelewicz commented 6 months ago

Feed it data that has one unique set of stuff-minus-catnum?

Yes - that is what I don't know how to do so I have it doing the only thing that I can get working in order to see what the label layout looks like. Can you help me to do that, or do I need someone else?

dustymc commented 6 months ago

I'm not sure we're talking about the same thing.

I think the label is working as it should. ( I think you don't? )

@ebraker can you confirm, or maybe provide an example of a homogenous container that should produce one label?

ebraker commented 6 months ago

Thanks @Jegelewicz!

I'm also seeing multiple labels for the same jar generated:

Some tweaks:

font: Arial
Have scientific name display on one line (no wrapping) and italicize
I'd like the printed date in a small font at the bottom of the label ("printed DD Month YYYY")
catalog number values should be bolded
jar number and preservation method values should be bolded and with no space between rows
The line that displays family and country values should use a larger font (same size as used with scientific name)
I include a vertical line in each template to indicate where to cut since our label roll is continuous. The overall size of the label also accounts for white space (especially important for the larger-sized label as it helps the label conform to the walls and stay standing up in a gallon jar)
I would move the locality text a bit further to the left...these locality descriptions happen to be short, but some are much longer and we have more wiggle room in the catalog number column to shrink vs wrap locality text.
FYI here is the same label printed in 2019 as an example (though I guess we didn't have as many specimens in the jar???):

Thanks for working on this!

Jegelewicz commented 6 months ago

[x] font: Arial
[x] Have scientific name display on one line (no wrapping) and italicize

It was already bold - does it need to be more bold (I don't know if that is possible?) added italics. I achieved the "no wrapping" by setting the columns to 80/20 - which still may not work for everything? Maybe there is a better solution, but it would take me some research.
[x] I'd like the printed date in a small font at the bottom of the label ("printed DD Month YYYY")

I don't know how to do this. @mkoo @dustymc has this been done for any of the other reports?
[x] catalog number values should be bolded
[x] jar number and preservation method values should be bolded and with no space between rows

these were already bolded - don't know what else to do about it?, removed the space
[x] The line that displays family and country values should use a larger font (same size as used with scientific name)
[x] I include a vertical line in each template to indicate where to cut since our label roll is continuous. The overall size of the label also accounts for white space (especially important for the larger-sized label as it helps the label conform to the walls and stay standing up in a gallon jar)

Maybe I have screwed this up? The dimensions of the label are 4x6 and your paper is 4" wide, so the need to cut would be at the bottom?**
[x] I would move the locality text a bit further to the left...these locality descriptions happen to be short, but some are much longer and we have more wiggle room in the catalog number column to shrink vs wrap locality text.

These are currently set to 50/50, what ratio would you like?

ebraker commented 6 months ago

@Jegelewicz Thank you!

Maybe I have screwed this up? The dimensions of the label are 4x6 and your paper is 4" wide, so the need to cut would be at the bottom?**

The thermal paper is a giant roll, 4" x 500 ft or so. The labels gets spit out sideways so the 4" dimension is actually the height.

These are currently set to 50/50, what ratio would you like?

Maybe 40/60?

dustymc commented 6 months ago

multiple labels for the same jar

If that's not what should happen when the data are heterogeneous, please spell out exactly what should happen for me, I'm lost.

more bold

see https://developer.mozilla.org/en-US/docs/Web/CSS/font-weight

printed DD Month YYYY

If you can do this with cfml,

printed #datefomat(now(),"DD mmmm YYYY")#

should do it.

ebraker commented 6 months ago

@dustymc When I go to Edit Container -> Print, then select my UCM_jar_consolidated_country__small report, this is what generates (multiple copies of the same label vs. a single label):

Jegelewicz commented 6 months ago

@dustymc I know what I need to fix the above, I just don't know how to get at it.

"q" has these data for everything in the jar and they should all be the same for every row (if they aren't, @ebraker has something in the wrong jar). So I need to get the unique values for each of those from "q", then I should be able to fix this so that it only prints one label. Clear as mud?

scientific_name, family, country, state_prov,

dustymc commented 6 months ago

@ebraker what container?

ebraker commented 6 months ago

@dustymc try UCM_JAR-3887

dustymc commented 6 months ago

Thanks, That's printing 4 labels because there are 4 locality variations (what the old pre-function did)

If that's not correct, what should it be doing?

Jegelewicz commented 6 months ago

@dustymc see https://github.com/ArctosDB/arctos/issues/5266#issuecomment-1942851082

dustymc commented 6 months ago

https://github.com/ArctosDB/arctos/issues/5266#issuecomment-1942851082

So just drop specloc?

ebraker commented 6 months ago

@dustymc I just need one label printed, not four versions of the same label. spec_loc is listed alongside each catalog number for every individual in the jar (grouping series/specimens from the same locality into one line):

Jegelewicz commented 6 months ago

What @ebraker said. These all might have individual spec_loc, but they should all share

scientific_name, family, country, state_prov,

the sql there now is getting the data needed for the individual spec_locs and summarizing it perfectly, so no need to change that, but from that, I only need the four things above ONCE.

dustymc commented 6 months ago

https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=78

Jegelewicz commented 6 months ago

Thanks!

Jegelewicz commented 6 months ago

so the 4" dimension is actually the height.

OK will change

ArctosDB / arctos

New Arctos Report Template Request - UCM Herp Jar labels #5266