Exporting stock orders

[cybersiddhu]

The export by default should contain the following fields ...

• email of ordering user.
• Date of order
• Strain/Plasmid/Bacterial Id of order. Do not exporting the primary key value of database table.

And any other useful metadata that might be present.

[biodavidjm]

Step 1. SQL statements

• Stock Orders for Plasmids:

"StockOrder_ID", "OrderDate", "PlasmidID", "PlasmidName", "ColleagueID","FirstName", "LastName", "email" 

select 
SO.STOCK_ORDER_ID ORDER_ID, 
SO.ORDER_DATE,
PLASMID.ID,
PLASMID.NAME,
COLLEAGUE.COLLEAGUE_NO,
COLLEAGUE.FIRST_NAME,
COLLEAGUE.LAST_NAME,
EMAIL.EMAIL
from CGM_DDB.PLASMID
JOIN CGM_DDB.STOCK_ITEM_ORDER SIO ON
(
  PLASMID.ID=SIO.ITEM_ID
  AND
  PLASMID.NAME=SIO.ITEM
)
join CGM_DDB.STOCK_ORDER SO on SIO.ORDER_ID=SO.STOCK_ORDER_ID
left join CGM_DDB.COLLEAGUE on COLLEAGUE.COLLEAGUE_NO=SO.COLLEAGUE_ID
left join CGM_DDB.COLL_EMAIL COE on COE.COLLEAGUE_NO=COLLEAGUE.COLLEAGUE_NO
left join CGM_DDB.EMAIL on EMAIL.EMAIL_NO=COE.EMAIL_NO

To Explore: Does the "plasmid" table have a dbxref_id? How can be connected to cgm_chado.dbxref?

• Stock Orders for Strains:

"StockOrder_ID", "OrderDate", "StrainID", "StrainName", "ColleagueID","FirstName", "LastName", "email" 

select 
SO.STOCK_ORDER_ID ORDER_ID, 
SO.ORDER_DATE,
SC.ID ID,
SC.STRAIN_NAME NAME,
COLLEAGUE.COLLEAGUE_NO,
COLLEAGUE.FIRST_NAME,
COLLEAGUE.LAST_NAME,
EMAIL.EMAIL
from CGM_DDB.STOCK_CENTER SC
JOIN CGM_DDB.STOCK_ITEM_ORDER SIO ON
(
  SC.ID=SIO.ITEM_ID
  AND
  SC.STRAIN_NAME=SIO.ITEM
)
join CGM_DDB.STOCK_ORDER SO on SIO.ORDER_ID=SO.STOCK_ORDER_ID
left join CGM_DDB.COLLEAGUE on COLLEAGUE.COLLEAGUE_NO=SO.COLLEAGUE_ID
left join CGM_DDB.COLL_EMAIL COE on COE.COLLEAGUE_NO=COLLEAGUE.COLLEAGUE_NO
left join CGM_DDB.EMAIL on EMAIL.EMAIL_NO=COE.EMAIL_NO

Step 2. Script to dump the data to a file

As a preliminary step and in order to understand some of the libraries used in Modware-Loader, I have written a temporal script to export the data to a file. The script can be found here. The final goal is to make this part of Modware-Loader

Step 3. Integration in Modware-Loader

On branch export-stock-orders

1. The first attempt was to create a module stockorders using a statement that would get all the data. The first working version has been committed (472d9f69774dfdc5158c4b65cb1a42516b31684b)

perl -Ilib bin/modware-export stockorders -c config-legacy.yaml -o stock_center_orders.csv

1. After analysis of the tables related to the stocks order, it is concluded that both strains and plasmids should be dumped independently with the SQL statements above. Two additional modules are created: stockordersplasmids.pm and stockordersstrains.pm

These modules do not block execution, but need some work following Sidd's feedback:

• not written object oriented style and show improper use of language construct.
• You have to call method not subroutine, so call them in object context. $self variable should always be the object, it is being thoroughly molested in the code. Needless to say, you have to change that to proper perl convention.
• If something is null/blank/empty or has no value, just check them and return. You don't need so many lines of code to do that or fill it up with fake value. Secondly you need to learn how to write perl way of checking empty value. Perl has lot of subtleties in this regard, so please spend some time in learning them.
• The lack of idiomatic perl code always has a side effect of using extra parenthesis. If dealt properly, those will go away resulting in cleaner code. Anyway, you really need to spend (a lot i think) time to learn when and why parenthesis is used.

Refactoring on export-stock-orders-pro