private static FullArticleInfo GetArticleInfoFromTable(DataTable articleData)
{
FullArticleInfo articleInfo = null;
try
{
if (articleData != null && articleData.Rows.Count > 0)
{
articleInfo = new FullArticleInfo();
articleInfo.ArticleType = articleData.Rows[0]["DOC_TYPE"].ToString();
articleInfo.JournalDOI = articleData.Rows[0]["DOI"].ToString();
articleInfo.JournalName = articleData.Rows[0]["JOURNAL_NAME"].ToString();
articleInfo.JournalStartPage = articleData.Rows[0]["START_PAGE"].ToString();
articleInfo.JournalEndPage = articleData.Rows[0]["END_PAGE"].ToString();
int journalYear = 0;
int.TryParse(articleData.Rows[0]["JOURNAL_YEAR"].ToString(), out journalYear);
articleInfo.JournalYear = journalYear;
}
}
catch (Exception ex)
{
ErrorHandling.WriteErrorLog(ex.ToString());
}
return(articleInfo);
}
private static FullArticleInfo GetFullArticleDetailsOnShipmentRefID(int shipmentRefID)
{
FullArticleInfo objFullArtInfo = null;
try
{
if (shipmentRefID > 0)
{
objFullArtInfo = new FullArticleInfo();
objFullArtInfo.ShipmentRefID = shipmentRefID;
DataTable dtArticleInfo = ReactionCurationDB.SaveFullArticleInfo(objFullArtInfo);
if (dtArticleInfo != null && dtArticleInfo.Rows.Count > 0)
{
objFullArtInfo.ArticleType = dtArticleInfo.Rows[0]["REFERENCE_TYPE"].ToString();
objFullArtInfo.JournalDOI = dtArticleInfo.Rows[0]["DOI"].ToString();
objFullArtInfo.JournalName = dtArticleInfo.Rows[0]["JOURNAL_NAME"].ToString();
objFullArtInfo.JournalRefNo = dtArticleInfo.Rows[0]["REFERENCE_NAME"].ToString();
objFullArtInfo.JournalVolume = dtArticleInfo.Rows[0]["VOLUME"].ToString();
int journalYear = 0;
int.TryParse(dtArticleInfo.Rows[0]["JOURNAL_YEAR"].ToString(), out journalYear);
objFullArtInfo.JournalYear = journalYear;
objFullArtInfo.JournalStartPage = dtArticleInfo.Rows[0]["START_PAGE"].ToString();
objFullArtInfo.JournalEndPage = dtArticleInfo.Rows[0]["END_PAGE"].ToString();
}
}
}
catch (Exception ex)
{
ErrorHandling.WriteErrorLog(ex.ToString());
}
return(objFullArtInfo);
}
public static DataTable SaveFullArticleInfo(FullArticleInfo articleInfo)
{
string strTemp = String.Empty;
DataTable dtFullArticleInfo = null;
try
{
using (OracleConnection oraCon = ConnectionDB.GetOracleConnection())
{
using (OracleCommand oraCmd = new OracleCommand())
{
oraCmd.Connection = oraCon;
oraCmd.CommandText = "SHIPMENT_MANAGEMENT.UPDATE_BIBLIOGRAPHY_INFO";
oraCmd.CommandType = CommandType.StoredProcedure;
oraCmd.Parameters.Add("PIN_SHIPMENT_REF_ID", OracleDbType.Int32).Value = articleInfo.ShipmentRefID;
oraCmd.Parameters.Add("PIC_JOURNAL_NAME", OracleDbType.Varchar2).Value = articleInfo.JournalName;
oraCmd.Parameters.Add("PIN_JOURNAL_YEAR", OracleDbType.Int32).Value = articleInfo.JournalYear;
oraCmd.Parameters.Add("PIC_VOLUME", OracleDbType.Varchar2).Value = articleInfo.JournalVolume;
oraCmd.Parameters.Add("PIC_ISSUE", OracleDbType.Varchar2).Value = "";
oraCmd.Parameters.Add("PIC_START_PAGE", OracleDbType.Varchar2).Value = articleInfo.JournalStartPage;
oraCmd.Parameters.Add("PIC_END_PAGE", OracleDbType.Varchar2).Value = articleInfo.JournalEndPage;
oraCmd.Parameters.Add("PIC_DOI", OracleDbType.Varchar2).Value = articleInfo.JournalDOI;
oraCmd.Parameters.Add("PORC_SHIPMENT_REFERENCES", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
dtFullArticleInfo = new DataTable();
using (OracleDataAdapter dataAdapter = new OracleDataAdapter(oraCmd))
{
dataAdapter.Fill(dtFullArticleInfo);
}
}
}
}
catch (Exception)
{
throw;
}
return(dtFullArticleInfo);
}
/// <summary>
/// Export Reactions for client delivery
/// </summary>
/// <param name="RefIDsList"></param>
/// <param name="shipmentYear"></param>
/// <param name="outputPath"></param>
/// <param name="mdlStartNo"></param>
/// <param name="deliveryDtls"></param>
/// <param name="delivSovCats"></param>
/// <returns></returns>
public static bool ExportReactionsForClientDelivery(List <int> RefIDsList, string shipmentYear, string outputPath, int mdlStartNo, out Delivery deliveryDtls, out List <DeliverySolvCats> delivSovCatsList)
{
bool blStatus = false;
Delivery objDelivery = null;
List <DeliverySolvCats> lstDeliverySovCats = null;
try
{
int mdlNo = mdlStartNo;//70000000;
List <string> lstUniqSolvInchi = new List <string>();
//Define Delivery class
objDelivery = new Delivery();
objDelivery.DeliveryDate = DateTime.Now;
objDelivery.MDLStartNo = mdlStartNo;
objDelivery.DeliveryRefCount = RefIDsList.Count;
List <int> lstDelRefs = new List <int>();
List <int> lstDelRefMDLStNo = new List <int>();
List <int> lstDelRefMDLEndNo = new List <int>();
int deliveredRxnCnt = 0;
lstDeliverySovCats = new List <DeliverySolvCats>();
foreach (int shipRefID in RefIDsList)
{
DataSet dsRxns = ReactionCurationDB.GetReactionsForExportOnDocID(shipRefID);
DataTable dtRxnRefs = null;
DataTable dtCrossRefs = null;
//Automated Export
DataSet dsRxn_CrossRefs = ReactionCurationDB.GetRxnAndCrossReferencesOnShipmentRefID(shipRefID);
if (dsRxn_CrossRefs != null)
{
if (dsRxn_CrossRefs.Tables.Count == 2)
{
dtCrossRefs = dsRxn_CrossRefs.Tables[0];
dtRxnRefs = dsRxn_CrossRefs.Tables[1];
}
}
if (dsRxns != null)
{
if (dsRxns.Tables.Count == 8)
{
DataTable dtDocMaster = dsRxns.Tables[0];
DataTable dtReactions = dsRxns.Tables[1];
//Manual Export
//DataTable dtRxnRefs = dsRxns.Tables[2];
//DataTable dtCrossRefs = dsRxns.Tables[3];
DataTable dtRxnSteps = dsRxns.Tables[4];
DataTable dtConditions = dsRxns.Tables[5];
DataTable dtParticipants = dsRxns.Tables[6];
DataTable dtProducts = dsRxns.Tables[7];
// FullArticleInfo articleInfo = GetArticleInfoFromTable(dtDocMaster);
FullArticleInfo articleInfo = GetFullArticleDetailsOnShipmentRefID(Convert.ToInt32(dtDocMaster.Rows[0]["SHIPMENT_REF_ID"].ToString()));
string shipementRefName = dtDocMaster.Rows[0]["REFERENCE_NAME"].ToString();
List <ReactionInfo> lstRxnInfo = new List <ReactionInfo>();
int reactionID = 0;
//Add Reference Name and MDL Start No
lstDelRefs.Add(shipRefID);
lstDelRefMDLStNo.Add(mdlNo);
//Unique ID for reaction (serial number); format: RXCInnnnnnnn;n=numerical range for backfiles is 70000001...89999999.
//Feedback on 28th Oct 2014
//General: Please assign MDLnumbers according to the year, i.e. issues from 1981 should have MDLnumbers RXCI81nnnnnn, to avoid duplicates
foreach (DataRow drow in dtReactions.Rows)
{
reactionID = Convert.ToInt32(drow["REACTION_ID"]);
ReactionInfo rxnInfo = new ReactionInfo();
rxnInfo.ShipmentRefID = shipRefID;
rxnInfo.ReactionID = reactionID;
rxnInfo.SysNo = dtDocMaster.Rows[0]["SYS_NO"].ToString();
rxnInfo.SysText = dtDocMaster.Rows[0]["SYS_TEXT"].ToString();
rxnInfo.RxnMDLNo = "RXCI" + shipmentYear + mdlNo.ToString("000000");//RXCI81nnnnnn
rxnInfo.ReactionScheme = drow["REACTION_SCHEME"];
rxnInfo.ReactionSNo = Convert.ToInt32(drow["REACTION_SNO"]);
rxnInfo.RxnComments = drow["RXN_COMMENTS"].ToString();
rxnInfo.RxnRef = GetRxnRefInfoFromTableOnReactionID(dtRxnRefs, reactionID);
rxnInfo.RxnCrossRef = GetCrossRefInfoFromTableOnReactionID(dtCrossRefs, reactionID);
rxnInfo.RxnProducts = GetProductsFromTableOnReactionID(dtProducts, reactionID);
rxnInfo.RxnSteps = GetStepsInfoFromTableOnReactionID(dtRxnSteps, dtParticipants, dtConditions, reactionID);
//Add to list
lstRxnInfo.Add(rxnInfo);
//increment MDL Numer
mdlNo++;
//Count delivery Rxns
deliveredRxnCnt++;
}
//Add Reference MDL End No
lstDelRefMDLEndNo.Add(mdlNo);
if (lstRxnInfo != null)
{
DeliverySolvCats refSovCats = new DeliverySolvCats();
if (ReactionExport.ExportToRDFile(lstRxnInfo, articleInfo, shipementRefName, outputPath, ref lstUniqSolvInchi, ref refSovCats))
{
blStatus = true;
if (refSovCats.RefNewSolvents != null)
{
refSovCats.ShipmentRefID = shipRefID;
//Add Refs and SolvCats to List
lstDeliverySovCats.Add(refSovCats);
}
}
}
}
}
}
//Delivery references and Mdl Nos
objDelivery.DeliveryRefsList = lstDelRefs;
objDelivery.RefMdlStartNoList = lstDelRefMDLStNo;
objDelivery.RefMdlEndNoList = lstDelRefMDLEndNo;
objDelivery.DeliveryRxnCount = deliveredRxnCnt;
objDelivery.MDLEndNo = (mdlStartNo + deliveredRxnCnt) - 1;
}
catch (Exception ex)
{
ErrorHandling.WriteErrorLog(ex.ToString());
}
deliveryDtls = objDelivery;
delivSovCatsList = lstDeliverySovCats;
return(blStatus);
}
