// *************************** IMPORT: SAMPLE ****************************** // ***************************************************************************** public static int InsertOrUpdateWQX_IMPORT_TEMP_SAMPLE(global::System.Int32? tEMP_SAMPLE_IDX, string uSER_ID, global::System.String oRG_ID, global::System.Int32? pROJECT_IDX, string pROJECT_ID, global::System.Int32? mONLOC_IDX, string mONLOC_ID, global::System.Int32? aCTIVITY_IDX, global::System.String aCTIVITY_ID, global::System.String aCT_TYPE, global::System.String aCT_MEDIA, global::System.String aCT_SUBMEDIA, global::System.DateTime? aCT_START_DT, global::System.DateTime? aCT_END_DT, global::System.String aCT_TIME_ZONE, global::System.String rELATIVE_DEPTH_NAME, global::System.String aCT_DEPTHHEIGHT_MSR, global::System.String aCT_DEPTHHEIGHT_MSR_UNIT, global::System.String tOP_DEPTHHEIGHT_MSR, global::System.String tOP_DEPTHHEIGHT_MSR_UNIT, global::System.String bOT_DEPTHHEIGHT_MSR, global::System.String bOT_DEPTHHEIGHT_MSR_UNIT, global::System.String dEPTH_REF_POINT, global::System.String aCT_COMMENT, global::System.String bIO_ASSEMBLAGE_SAMPLED, global::System.String bIO_DURATION_MSR, global::System.String bIO_DURATION_MSR_UNIT, global::System.String bIO_SAMP_COMPONENT, int? bIO_SAMP_COMPONENT_SEQ, global::System.String bIO_REACH_LEN_MSR, global::System.String bIO_REACH_LEN_MSR_UNIT, global::System.String bIO_REACH_WID_MSR, global::System.String bIO_REACH_WID_MSR_UNIT, int? bIO_PASS_COUNT, global::System.String bIO_NET_TYPE, global::System.String bIO_NET_AREA_MSR, global::System.String bIO_NET_AREA_MSR_UNIT, global::System.String bIO_NET_MESHSIZE_MSR, global::System.String bIO_MESHSIZE_MSR_UNIT, global::System.String bIO_BOAT_SPEED_MSR, global::System.String bIO_BOAT_SPEED_MSR_UNIT, global::System.String bIO_CURR_SPEED_MSR, global::System.String bIO_CURR_SPEED_MSR_UNIT, global::System.String bIO_TOXICITY_TEST_TYPE, int? sAMP_COLL_METHOD_IDX, global::System.String sAMP_COLL_METHOD_ID, global::System.String sAMP_COLL_METHOD_CTX, global::System.String sAMP_COLL_METHOD_NAME, global::System.String sAMP_COLL_EQUIP, global::System.String sAMP_COLL_EQUIP_COMMENT, int? sAMP_PREP_IDX, global::System.String sAMP_PREP_ID, global::System.String sAMP_PREP_CTX, global::System.String sAMP_PREP_NAME, global::System.String sAMP_PREP_CONT_TYPE, global::System.String sAMP_PREP_CONT_COLOR, global::System.String sAMP_PREP_CHEM_PRESERV, global::System.String sAMP_PREP_THERM_PRESERV, global::System.String sAMP_PREP_STORAGE_DESC, string sTATUS_CD, string sTATUS_DESC, bool BioIndicator, Boolean autoImportRefDataInd) { try { using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities()) { Boolean insInd = false; //******************* GET STARTING RECORD ************************************************* T_WQX_IMPORT_TEMP_SAMPLE a; if (tEMP_SAMPLE_IDX != null) //grab from IDX if given a = (from c in ctx.T_WQX_IMPORT_TEMP_SAMPLE where c.TEMP_SAMPLE_IDX == tEMP_SAMPLE_IDX select c).FirstOrDefault(); else //check if existing activity ID exists in the import { a = (from c in ctx.T_WQX_IMPORT_TEMP_SAMPLE where c.ACTIVITY_ID == aCTIVITY_ID && c.ORG_ID == oRG_ID select c).FirstOrDefault(); } //if can't find a match based on supplied IDX or ID, then create a new record if (a == null) { insInd = true; a = new T_WQX_IMPORT_TEMP_SAMPLE(); } //********************** END GET STARTING RECORD ************************************************ if (!string.IsNullOrEmpty(uSER_ID)) a.USER_ID = uSER_ID; if (!string.IsNullOrEmpty(oRG_ID)) a.ORG_ID = oRG_ID; //PROJECT HANDLING if (pROJECT_IDX == null && pROJECT_ID == null) { sTATUS_CD = "F"; sTATUS_DESC += "Project ID must be provided. "; } if (pROJECT_IDX != null) a.PROJECT_IDX = pROJECT_IDX; if (pROJECT_ID != null) { a.PROJECT_ID = pROJECT_ID.Trim().SubStringPlus(0, 35); T_WQX_PROJECT ptemp = db_WQX.GetWQX_PROJECT_ByIDString(pROJECT_ID, oRG_ID); if (ptemp == null) { sTATUS_CD = "F"; sTATUS_DESC += "Project ID does not exist. Create project first."; } else { a.PROJECT_IDX = ptemp.PROJECT_IDX; } } //MONITORING LOCATION HANDLING if (mONLOC_IDX == null && mONLOC_ID == null) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location ID must be provided. "; } if (mONLOC_IDX != null) a.MONLOC_IDX = mONLOC_IDX; if (mONLOC_ID != null) { a.MONLOC_ID = mONLOC_ID.Trim().SubStringPlus(0, 35); T_WQX_MONLOC mm = db_WQX.GetWQX_MONLOC_ByIDString(oRG_ID, mONLOC_ID); if (mm == null) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location ID does not exist. Import MonLocs first."; } else { a.MONLOC_IDX = mm.MONLOC_IDX; } } //ACTIVITY ID HANDLING if (aCTIVITY_IDX == null && aCTIVITY_ID == null) { sTATUS_CD = "F"; sTATUS_DESC += "Activity ID must be provided. "; } if (aCTIVITY_IDX != null) a.ACTIVITY_IDX = aCTIVITY_IDX; if (!string.IsNullOrEmpty(aCTIVITY_ID)) a.ACTIVITY_ID = aCTIVITY_ID.Trim().SubStringPlus(0, 35); //ACTIVITY TYPE HANDLING if (!string.IsNullOrEmpty(aCT_TYPE)) { a.ACT_TYPE = aCT_TYPE.SubStringPlus(0, 70) ?? ""; if (db_Ref.GetT_WQX_REF_DATA_ByKey("ActivityType", aCT_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Activity Type not valid. "; } } else { a.ACT_TYPE = ""; sTATUS_CD = "F"; sTATUS_DESC += "Activity Type is required."; } if (!string.IsNullOrEmpty(aCT_MEDIA)) { a.ACT_MEDIA = aCT_MEDIA.SubStringPlus(0, 20) ?? ""; if (db_Ref.GetT_WQX_REF_DATA_ByKey("ActivityMedia", aCT_MEDIA.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Activity Media not valid. "; } } else { a.ACT_MEDIA = ""; sTATUS_CD = "F"; sTATUS_DESC += "Activity Media is required."; } if (!string.IsNullOrEmpty(aCT_SUBMEDIA)) { a.ACT_SUBMEDIA = aCT_SUBMEDIA.SubStringPlus(0, 45); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ActivityMediaSubdivision", aCT_SUBMEDIA.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Activity Media Subdivision not valid. "; } } if (aCT_START_DT == null) { sTATUS_CD = "F"; sTATUS_DESC += "Activity Start Date must be provided. "; } else { //fix improperly formatted datetime if (aCT_START_DT.ConvertOrDefault<DateTime>().Year < 1900) { sTATUS_CD = "F"; sTATUS_DESC += "Activity Start Date is formatted incorrectly. "; } else a.ACT_START_DT = aCT_START_DT; } if (aCT_END_DT != null) { //fix improperly formatted datetime if (aCT_END_DT.ConvertOrDefault<DateTime>().Year < 1900) aCT_END_DT = null; a.ACT_END_DT = aCT_END_DT; } if (!string.IsNullOrEmpty(aCT_TIME_ZONE)) { a.ACT_TIME_ZONE = aCT_TIME_ZONE.Trim().SubStringPlus(0, 4); if (db_Ref.GetT_WQX_REF_DATA_ByKey("TimeZone", aCT_TIME_ZONE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "TimeZone not valid. "; } } else { //put in Timezone if missing a.ACT_TIME_ZONE = Utils.GetWQXTimeZoneByDate(a.ACT_START_DT.ConvertOrDefault<DateTime>()); } if (!string.IsNullOrEmpty(rELATIVE_DEPTH_NAME)) { a.RELATIVE_DEPTH_NAME = rELATIVE_DEPTH_NAME.Trim().SubStringPlus(0, 15); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ActivityRelativeDepth", rELATIVE_DEPTH_NAME.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Relative Depth Name not valid. "; } } if (!string.IsNullOrEmpty(aCT_DEPTHHEIGHT_MSR)) { a.ACT_DEPTHHEIGHT_MSR = aCT_DEPTHHEIGHT_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(aCT_DEPTHHEIGHT_MSR_UNIT)) { a.ACT_DEPTHHEIGHT_MSR_UNIT = aCT_DEPTHHEIGHT_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", aCT_DEPTHHEIGHT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Depth Measure Unit not valid. "; } } if (!string.IsNullOrEmpty(tOP_DEPTHHEIGHT_MSR)) { a.TOP_DEPTHHEIGHT_MSR = tOP_DEPTHHEIGHT_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(tOP_DEPTHHEIGHT_MSR_UNIT)) { a.TOP_DEPTHHEIGHT_MSR_UNIT = tOP_DEPTHHEIGHT_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", tOP_DEPTHHEIGHT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Top Depth Measure Unit not valid. "; } } if (!string.IsNullOrEmpty(bOT_DEPTHHEIGHT_MSR)) { a.BOT_DEPTHHEIGHT_MSR = bOT_DEPTHHEIGHT_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bOT_DEPTHHEIGHT_MSR_UNIT)) { a.BOT_DEPTHHEIGHT_MSR_UNIT = bOT_DEPTHHEIGHT_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bOT_DEPTHHEIGHT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bottom Depth Measure Unit not valid. "; } } if (!string.IsNullOrEmpty(dEPTH_REF_POINT)) { a.DEPTH_REF_POINT = dEPTH_REF_POINT.Trim().SubStringPlus(0, 125); } if (!string.IsNullOrEmpty(aCT_COMMENT)) { a.ACT_COMMENT = aCT_COMMENT.Trim().SubStringPlus(0, 4000); } //BIOLOGICAL MONITORING if (BioIndicator == true) { if (!string.IsNullOrEmpty(bIO_ASSEMBLAGE_SAMPLED)) { a.BIO_ASSEMBLAGE_SAMPLED = bIO_ASSEMBLAGE_SAMPLED.Trim().SubStringPlus(0, 50); if (db_Ref.GetT_WQX_REF_DATA_ByKey("Assemblage", bIO_ASSEMBLAGE_SAMPLED.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Assemblage not valid. "; } } if (!string.IsNullOrEmpty(bIO_DURATION_MSR)) { a.BIO_DURATION_MSR = bIO_DURATION_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_DURATION_MSR_UNIT)) { a.BIO_DURATION_MSR_UNIT = bIO_DURATION_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_DURATION_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Collection Duration Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_SAMP_COMPONENT)) { a.BIO_SAMP_COMPONENT = bIO_SAMP_COMPONENT.Trim().SubStringPlus(0, 15); } if (bIO_SAMP_COMPONENT_SEQ != null) a.BIO_SAMP_COMPONENT_SEQ = bIO_SAMP_COMPONENT_SEQ; if (!string.IsNullOrEmpty(bIO_REACH_LEN_MSR)) { a.BIO_REACH_LEN_MSR = bIO_REACH_LEN_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_REACH_LEN_MSR_UNIT)) { a.BIO_REACH_LEN_MSR_UNIT = bIO_REACH_LEN_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_REACH_LEN_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Reach Length Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_REACH_WID_MSR)) { a.BIO_REACH_WID_MSR = bIO_REACH_WID_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_REACH_WID_MSR_UNIT)) { a.BIO_REACH_WID_MSR_UNIT = bIO_REACH_WID_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_REACH_WID_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Reach Width Unit not valid. "; } } if (bIO_PASS_COUNT != null) a.BIO_PASS_COUNT = bIO_PASS_COUNT; if (!string.IsNullOrEmpty(bIO_NET_TYPE)) { a.BIO_NET_TYPE = bIO_NET_TYPE.Trim().SubStringPlus(0, 30); if (db_Ref.GetT_WQX_REF_DATA_ByKey("NetType", bIO_NET_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Net Type not valid. "; } } if (!string.IsNullOrEmpty(bIO_NET_AREA_MSR)) { a.BIO_NET_AREA_MSR = bIO_NET_AREA_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_NET_AREA_MSR_UNIT)) { a.BIO_NET_AREA_MSR_UNIT = bIO_NET_AREA_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_NET_AREA_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Net Area Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_NET_MESHSIZE_MSR)) { a.BIO_NET_MESHSIZE_MSR = bIO_NET_MESHSIZE_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_MESHSIZE_MSR_UNIT)) { a.BIO_MESHSIZE_MSR_UNIT = bIO_MESHSIZE_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_MESHSIZE_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Bio Net Mesh Size Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_BOAT_SPEED_MSR)) { a.BIO_BOAT_SPEED_MSR = bIO_BOAT_SPEED_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_BOAT_SPEED_MSR_UNIT)) { a.BIO_BOAT_SPEED_MSR_UNIT = bIO_BOAT_SPEED_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_BOAT_SPEED_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Boat Speed Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_CURR_SPEED_MSR)) { a.BIO_CURR_SPEED_MSR = bIO_CURR_SPEED_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(bIO_CURR_SPEED_MSR_UNIT)) { a.BIO_CURR_SPEED_MSR_UNIT = bIO_CURR_SPEED_MSR_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", bIO_CURR_SPEED_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Current Speed Unit not valid. "; } } if (!string.IsNullOrEmpty(bIO_TOXICITY_TEST_TYPE)) { a.BIO_TOXICITY_TEST_TYPE = bIO_TOXICITY_TEST_TYPE.Trim().SubStringPlus(0, 7); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ToxicityTestType", bIO_TOXICITY_TEST_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Toxicity Test Type not valid. "; } } } if (sAMP_COLL_METHOD_IDX != null) { a.SAMP_COLL_METHOD_IDX = sAMP_COLL_METHOD_IDX; //if IDX is populated but ID/Name/Ctx aren't then grab them T_WQX_REF_SAMP_COL_METHOD scm = db_Ref.GetT_WQX_REF_SAMP_COL_METHOD_ByIDX(a.SAMP_COLL_METHOD_IDX); if (scm != null) { a.SAMP_COLL_METHOD_ID = scm.SAMP_COLL_METHOD_ID; a.SAMP_COLL_METHOD_NAME = scm.SAMP_COLL_METHOD_NAME; a.SAMP_COLL_METHOD_CTX = scm.SAMP_COLL_METHOD_CTX; } } else { //set context to org id if none is provided if (!string.IsNullOrEmpty(sAMP_COLL_METHOD_ID) && string.IsNullOrEmpty(sAMP_COLL_METHOD_CTX)) sAMP_COLL_METHOD_CTX = oRG_ID; if (!string.IsNullOrEmpty(sAMP_COLL_METHOD_ID) && !string.IsNullOrEmpty(sAMP_COLL_METHOD_CTX)) { //lookup matching collection method IDX T_WQX_REF_SAMP_COL_METHOD scm = db_Ref.GetT_WQX_REF_SAMP_COL_METHOD_ByIDandContext(sAMP_COLL_METHOD_ID.Trim(), sAMP_COLL_METHOD_CTX.Trim()); if (scm != null) a.SAMP_COLL_METHOD_IDX = scm.SAMP_COLL_METHOD_IDX; else //no matching sample collection method lookup found { if (autoImportRefDataInd == true) { db_Ref.InsertOrUpdateT_WQX_REF_SAMP_COL_METHOD(null, sAMP_COLL_METHOD_ID.Trim(), sAMP_COLL_METHOD_CTX.Trim(), sAMP_COLL_METHOD_NAME.Trim(), "", true); } else { sTATUS_CD = "F"; sTATUS_DESC += "No matching Sample Collection Method found - please add it at the Reference Data screen first. "; } } //**************************************** a.SAMP_COLL_METHOD_ID = sAMP_COLL_METHOD_ID.Trim().SubStringPlus(0, 20); a.SAMP_COLL_METHOD_CTX = sAMP_COLL_METHOD_CTX.Trim().SubStringPlus(0, 120); if (!string.IsNullOrEmpty(sAMP_COLL_METHOD_NAME)) { a.SAMP_COLL_METHOD_NAME = sAMP_COLL_METHOD_NAME.Trim().SubStringPlus(0, 120); } } } if (a.SAMP_COLL_METHOD_IDX == null && a.ACT_TYPE.ToUpper().Contains("SAMPLE")) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Collection Method is required when Activity Type contains the term -Sample-. "; } if (!string.IsNullOrEmpty(sAMP_COLL_EQUIP)) { a.SAMP_COLL_EQUIP = sAMP_COLL_EQUIP.Trim().SubStringPlus(0, 40); if (db_Ref.GetT_WQX_REF_DATA_ByKey("SampleCollectionEquipment", sAMP_COLL_EQUIP.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Collection Equipment not valid. "; } } else { //special validation requiring sampling collection equipment if activity type contains "Sample" if (a.ACT_TYPE.ToUpper().Contains("SAMPLE")) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Collection Equipment is required when Activity Type contains the term -Sample-. "; } } if (!string.IsNullOrEmpty(sAMP_COLL_EQUIP_COMMENT)) { a.SAMP_COLL_EQUIP_COMMENT = sAMP_COLL_EQUIP_COMMENT.Trim().SubStringPlus(0, 4000); } if (sAMP_PREP_IDX != null) a.SAMP_PREP_IDX = sAMP_PREP_IDX; else { //set context to org id if none is provided if (!string.IsNullOrEmpty(sAMP_PREP_ID) && string.IsNullOrEmpty(sAMP_PREP_CTX)) sAMP_PREP_CTX = oRG_ID; if (!string.IsNullOrEmpty(sAMP_PREP_ID) && !string.IsNullOrEmpty(sAMP_PREP_CTX)) { //see if matching prep method exists T_WQX_REF_SAMP_PREP sp = db_Ref.GetT_WQX_REF_SAMP_PREP_ByIDandContext(sAMP_PREP_ID.Trim(), sAMP_PREP_CTX.Trim()); if (sp != null) a.SAMP_PREP_IDX = sp.SAMP_PREP_IDX; //**************************************** a.SAMP_PREP_ID = sAMP_PREP_ID.Trim().SubStringPlus(0, 20); a.SAMP_PREP_CTX = sAMP_PREP_CTX.Trim().SubStringPlus(0, 120); if (!string.IsNullOrEmpty(sAMP_PREP_NAME)) { a.SAMP_PREP_NAME = sAMP_PREP_NAME.Trim().SubStringPlus(0, 120); } } } if (!string.IsNullOrEmpty(sAMP_PREP_CONT_TYPE)) { a.SAMP_PREP_CONT_TYPE = sAMP_PREP_CONT_TYPE.Trim().SubStringPlus(0, 35); if (db_Ref.GetT_WQX_REF_DATA_ByKey("SampleContainerType", sAMP_PREP_CONT_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Container Type not valid. "; } } if (!string.IsNullOrEmpty(sAMP_PREP_CONT_COLOR)) { a.SAMP_PREP_CONT_COLOR = sAMP_PREP_CONT_COLOR.Trim().SubStringPlus(0, 15); if (db_Ref.GetT_WQX_REF_DATA_ByKey("SampleContainerColor", sAMP_PREP_CONT_COLOR.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Container Color not valid. "; } } if (!string.IsNullOrEmpty(sAMP_PREP_CHEM_PRESERV)) { a.SAMP_PREP_CHEM_PRESERV = sAMP_PREP_CHEM_PRESERV.Trim().SubStringPlus(0, 250); } if (!string.IsNullOrEmpty(sAMP_PREP_THERM_PRESERV)) { a.SAMP_PREP_THERM_PRESERV = sAMP_PREP_THERM_PRESERV.Trim().SubStringPlus(0, 25); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ThermalPreservativeUsed", sAMP_PREP_THERM_PRESERV.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Thermal Preservative Used not valid. "; } } if (!string.IsNullOrEmpty(sAMP_PREP_STORAGE_DESC)) { a.SAMP_PREP_STORAGE_DESC = sAMP_PREP_STORAGE_DESC.Trim().SubStringPlus(0, 250); } if (sTATUS_CD != null) a.IMPORT_STATUS_CD = sTATUS_CD; if (sTATUS_DESC != null) a.IMPORT_STATUS_DESC = sTATUS_DESC.SubStringPlus(0,100); if (insInd) //insert case ctx.AddToT_WQX_IMPORT_TEMP_SAMPLE(a); ctx.SaveChanges(); return a.TEMP_SAMPLE_IDX; } } catch (Exception ex) { sTATUS_CD = "F"; sTATUS_DESC += "Unspecified error"; return 0; } }
// *************************** IMPORT: PROJECT ****************************** // ***************************************************************************** public static int InsertOrUpdateWQX_IMPORT_TEMP_PROJECT(global::System.Int32? tEMP_PROJECT_IDX, string uSER_ID, global::System.Int32? pROJECT_IDX, global::System.String oRG_ID, global::System.String pROJECT_ID, global::System.String pROJECT_NAME, global::System.String pROJECT_DESC, global::System.String sAMP_DESIGN_TYPE_CD, global::System.Boolean? qAPP_APPROVAL_IND, global::System.String qAPP_APPROVAL_AGENCY, string sTATUS_CD, string sTATUS_DESC) { using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities()) { Boolean insInd = false; try { T_WQX_IMPORT_TEMP_PROJECT a = new T_WQX_IMPORT_TEMP_PROJECT(); if (tEMP_PROJECT_IDX != null) a = (from c in ctx.T_WQX_IMPORT_TEMP_PROJECT where c.TEMP_PROJECT_IDX == tEMP_PROJECT_IDX select c).FirstOrDefault(); else insInd = true; if (uSER_ID != null) { a.USER_ID = uSER_ID; if (uSER_ID.Length > 25) { sTATUS_CD = "F"; sTATUS_DESC += "User ID length exceeded. "; } } if (pROJECT_IDX != null) a.PROJECT_IDX = pROJECT_IDX; if (oRG_ID != null) a.ORG_ID = oRG_ID; if (pROJECT_ID != null) { a.PROJECT_ID = pROJECT_ID.SubStringPlus(0, 35).Trim(); if (pROJECT_ID.Length > 35) { sTATUS_CD = "F"; sTATUS_DESC += "Project ID length exceeded. "; } T_WQX_PROJECT ptemp = db_WQX.GetWQX_PROJECT_ByIDString(pROJECT_ID, oRG_ID); if (ptemp != null) { sTATUS_CD = "F"; sTATUS_DESC += "Project ID already exists. "; } } if (!string.IsNullOrEmpty(pROJECT_NAME)) { a.PROJECT_NAME = pROJECT_NAME.SubStringPlus(0, 120).Trim(); if (pROJECT_NAME.Length > 120) { sTATUS_CD = "F"; sTATUS_DESC += "Project Name length exceeded. "; } } if (!string.IsNullOrEmpty(pROJECT_DESC)) { a.PROJECT_DESC = pROJECT_DESC.SubStringPlus(0, 1999); if (pROJECT_DESC.Length > 1999) { sTATUS_CD = "F"; sTATUS_DESC += "Project Description length exceeded. "; } } if (!string.IsNullOrEmpty(sAMP_DESIGN_TYPE_CD)) { a.SAMP_DESIGN_TYPE_CD = sAMP_DESIGN_TYPE_CD.Trim().SubStringPlus(0, 20); if (sAMP_DESIGN_TYPE_CD.Length > 20) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Design Type Code length exceeded. "; } } if (qAPP_APPROVAL_IND != null) { a.QAPP_APPROVAL_IND = qAPP_APPROVAL_IND; } if (!string.IsNullOrEmpty(qAPP_APPROVAL_AGENCY)) { a.QAPP_APPROVAL_AGENCY = qAPP_APPROVAL_AGENCY.SubStringPlus(0, 50); if (qAPP_APPROVAL_AGENCY.Length > 50) { sTATUS_CD = "F"; sTATUS_DESC += "QAPP Approval Agency length exceeded. "; } } if (sTATUS_CD != null) a.IMPORT_STATUS_CD = sTATUS_CD; if (sTATUS_DESC != null) a.IMPORT_STATUS_DESC = sTATUS_DESC.SubStringPlus(0, 100); if (insInd) //insert case ctx.AddToT_WQX_IMPORT_TEMP_PROJECT(a); ctx.SaveChanges(); return a.TEMP_PROJECT_IDX; } catch (Exception ex) { sTATUS_CD = "F"; sTATUS_DESC += "Unspecified error. "; return 0; } } }
// *************************** IMPORT: RESULT ****************************** // ***************************************************************************** public static int InsertOrUpdateWQX_IMPORT_TEMP_RESULT(global::System.Int32? tEMP_RESULT_IDX, int tEMP_SAMPLE_IDX, global::System.Int32? rESULT_IDX, string dATA_LOGGER_LINE, string rESULT_DETECT_CONDITION, global::System.String cHAR_NAME, string mETHOD_SPECIATION_NAME, string rESULT_SAMP_FRACTION, global::System.String rESULT_MSR, global::System.String rESULT_MSR_UNIT, string rESULT_MSR_QUAL, string rESULT_STATUS, string sTATISTIC_BASE_CODE, string rESULT_VALUE_TYPE, string wEIGHT_BASIS, string tIME_BASIS, string tEMP_BASIS, string pARTICAL_BASIS, string pRECISION_VALUE, string bIAS_VALUE, string cONFIDENCE_INTERVAL_VALUE, string uP_CONFIDENCE_LIMIT, string lOW_CONFIDENCE_LIMIT, string rESULT_COMMENT, string dEPTH_HEIGHT_MSR, string dEPTH_HEIGHT_MSR_UNIT, string dEPTHALTITUDEREFPOINT, string bIO_INTENT_NAME, string bIO_INDIVIDUAL_ID, string bIO_SUBJECT_TAXONOMY, string bIO_UNIDENTIFIED_SPECIES_ID, string bIO_SAMPLE_TISSUE_ANATOMY, string gRP_SUMM_COUNT_WEIGHT_MSR, string gRP_SUMM_COUNT_WEIGHT_MSR_UNIT, string tAX_DTL_CELL_FORM, string tAX_DTL_CELL_SHAPE, string tAX_DTL_HABIT, string tAX_DTL_VOLTINISM, string tAX_DTL_POLL_TOLERANCE, string tAX_DTL_POLL_TOLERANCE_SCALE, string tAX_DTL_TROPHIC_LEVEL, string tAX_DTL_FUNC_FEEDING_GROUP1, string tAX_DTL_FUNC_FEEDING_GROUP2, string tAX_DTL_FUNC_FEEDING_GROUP3, string fREQ_CLASS_CODE, string fREQ_CLASS_UNIT, string fREQ_CLASS_UPPER, string fREQ_CLASS_LOWER, int? aNALYTIC_METHOD_IDX, string aNALYTIC_METHOD_ID, string aNALYTIC_METHOD_CTX, string aNALYTIC_METHOD_NAME, int? lAB_IDX, string lAB_NAME, DateTime? lAB_ANALYSIS_START_DT, DateTime? lAB_ANALYSIS_END_DT, string lAB_ANALYSIS_TIMEZONE, string rESULT_LAB_COMMENT_CODE, string mETHOD_DETECTION_LEVEL, string lAB_REPORTING_LEVEL, string pQL, string lOWER_QUANT_LIMIT, string uPPER_QUANT_LIMIT, string dETECTION_LIMIT_UNIT, int? lAB_SAMP_PREP_IDX, string lAB_SAMP_PREP_ID, string lAB_SAMP_PREP_CTX, DateTime? lAB_SAMP_PREP_START_DT, DateTime? lAB_SAMP_PREP_END_DT, string dILUTION_FACTOR, string sTATUS_CD, string sTATUS_DESC, bool BioIndicator, string orgID, Boolean autoImportRefDataInd) { using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities()) { Boolean insInd = false; try { T_WQX_IMPORT_TEMP_RESULT a = new T_WQX_IMPORT_TEMP_RESULT(); if (tEMP_RESULT_IDX != null) a = (from c in ctx.T_WQX_IMPORT_TEMP_RESULT where c.TEMP_RESULT_IDX == tEMP_RESULT_IDX select c).FirstOrDefault(); if (tEMP_RESULT_IDX == null) //insert case insInd = true; a.TEMP_SAMPLE_IDX = tEMP_SAMPLE_IDX; if (rESULT_IDX != null) a.RESULT_IDX = rESULT_IDX; if (!string.IsNullOrEmpty(dATA_LOGGER_LINE)) { a.DATA_LOGGER_LINE = dATA_LOGGER_LINE.Trim().SubStringPlus(0, 15); } if (rESULT_DETECT_CONDITION == "DNQ" || rESULT_MSR == "DNQ") { rESULT_DETECT_CONDITION = "Detected Not Quantified"; rESULT_MSR = "DNQ"; } if (rESULT_DETECT_CONDITION == "ND" || rESULT_MSR == "ND") { rESULT_DETECT_CONDITION = "Not Detected"; rESULT_MSR = "ND"; } if (rESULT_DETECT_CONDITION == "NR" || rESULT_MSR == "NR") { rESULT_DETECT_CONDITION = "Not Reported"; rESULT_MSR = "NR"; } if (rESULT_DETECT_CONDITION == "PAQL" || rESULT_MSR == "PAQL") { rESULT_DETECT_CONDITION = "Present Above Quantification Limit"; rESULT_MSR = "PAQL"; } if (rESULT_DETECT_CONDITION == "PBQL" || rESULT_MSR == "PBQL") { rESULT_DETECT_CONDITION = "Present Below Quantification Limit"; rESULT_MSR = "PBQL"; } if (!string.IsNullOrEmpty(rESULT_DETECT_CONDITION)) { a.RESULT_DETECT_CONDITION = rESULT_DETECT_CONDITION.Trim().SubStringPlus(0, 35); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultDetectionCondition", rESULT_DETECT_CONDITION.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Result Detection Condition not valid. "; } } if (!string.IsNullOrEmpty(cHAR_NAME)) { a.CHAR_NAME = cHAR_NAME.Trim().SubStringPlus(0, 120); if (db_Ref.GetT_WQX_REF_CHARACTERISTIC_ExistCheck(cHAR_NAME.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Characteristic Name not valid. "; } } if (!string.IsNullOrEmpty(mETHOD_SPECIATION_NAME)) { a.METHOD_SPECIATION_NAME = mETHOD_SPECIATION_NAME.Trim().SubStringPlus(0, 20); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MethodSpeciation", mETHOD_SPECIATION_NAME.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Method Speciation not valid. "; } } if (!string.IsNullOrEmpty(rESULT_SAMP_FRACTION)) { a.RESULT_SAMP_FRACTION = rESULT_SAMP_FRACTION.Trim().SubStringPlus(0, 25); if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultSampleFraction", rESULT_SAMP_FRACTION.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Result Sample Fraction not valid. "; } } else { if (db_Ref.GetT_WQX_REF_CHARACTERISTIC_SampFracReqCheck(cHAR_NAME.Trim()) == true) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Fraction must be reported."; } } if (!string.IsNullOrEmpty(rESULT_MSR)) { a.RESULT_MSR = rESULT_MSR.Trim().SubStringPlus(0, 60).Replace(",",""); } else { if ( string.IsNullOrEmpty(rESULT_DETECT_CONDITION) ) { sTATUS_CD = "F"; sTATUS_DESC += "Either Result Measure or Result Detection Condition must be reported."; } } if (!string.IsNullOrEmpty(rESULT_MSR_UNIT)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", rESULT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Measurement Unit not valid. "; } a.RESULT_MSR_UNIT = rESULT_MSR_UNIT.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(rESULT_MSR_QUAL)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultMeasureQualifier", rESULT_MSR_QUAL.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Measurement Qualifier not valid. "; } a.RESULT_MSR_QUAL = rESULT_MSR_QUAL.Trim().SubStringPlus(0, 5); } if (!string.IsNullOrEmpty(rESULT_STATUS)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultStatus", rESULT_STATUS.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Result Status not valid. "; } a.RESULT_STATUS = rESULT_STATUS.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(sTATISTIC_BASE_CODE)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("StatisticalBase", sTATISTIC_BASE_CODE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Statistic Base Code not valid. "; } a.STATISTIC_BASE_CODE = sTATISTIC_BASE_CODE.Trim().SubStringPlus(0, 25); } if (!string.IsNullOrEmpty(rESULT_VALUE_TYPE)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultValueType", rESULT_VALUE_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Result Value Type not valid. "; } a.RESULT_VALUE_TYPE = rESULT_VALUE_TYPE.Trim().SubStringPlus(0, 20); } if (!string.IsNullOrEmpty(wEIGHT_BASIS)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultWeightBasis", wEIGHT_BASIS.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Weight Basis not valid. "; } a.WEIGHT_BASIS = wEIGHT_BASIS.Trim().SubStringPlus(0, 15); } if (!string.IsNullOrEmpty(tIME_BASIS)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultTimeBasis", tIME_BASIS.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Time Basis not valid. "; } a.TIME_BASIS = tIME_BASIS.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(tEMP_BASIS)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("ResultTemperatureBasis", tEMP_BASIS.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Temp Basis not valid. "; } a.TEMP_BASIS = tEMP_BASIS.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(pARTICAL_BASIS)) a.PARTICLESIZE_BASIS = pARTICAL_BASIS.Trim().SubStringPlus(0, 40); if (!string.IsNullOrEmpty(pRECISION_VALUE)) a.PRECISION_VALUE = pRECISION_VALUE.Trim().SubStringPlus(0,60); if (!string.IsNullOrEmpty(bIAS_VALUE)) a.BIAS_VALUE = bIAS_VALUE.Trim().SubStringPlus(0,60); if (!string.IsNullOrEmpty(cONFIDENCE_INTERVAL_VALUE)) a.CONFIDENCE_INTERVAL_VALUE = cONFIDENCE_INTERVAL_VALUE.Trim().SubStringPlus(0,15); if (!string.IsNullOrEmpty(uP_CONFIDENCE_LIMIT)) a.UPPER_CONFIDENCE_LIMIT = uP_CONFIDENCE_LIMIT.Trim().SubStringPlus(0, 15); if (!string.IsNullOrEmpty(lOW_CONFIDENCE_LIMIT)) a.LOWER_CONFIDENCE_LIMIT = lOW_CONFIDENCE_LIMIT.Trim().SubStringPlus(0, 15); if (!string.IsNullOrEmpty(rESULT_COMMENT)) a.RESULT_COMMENT = rESULT_COMMENT.Trim().SubStringPlus(0,4000); if (!string.IsNullOrEmpty(dEPTH_HEIGHT_MSR)) a.DEPTH_HEIGHT_MSR = dEPTH_HEIGHT_MSR.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(dEPTH_HEIGHT_MSR_UNIT)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", dEPTH_HEIGHT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Result Depth Unit not valid. "; } a.DEPTH_HEIGHT_MSR_UNIT = dEPTH_HEIGHT_MSR_UNIT.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(dEPTHALTITUDEREFPOINT)) a.DEPTHALTITUDEREFPOINT = dEPTHALTITUDEREFPOINT.Trim().SubStringPlus(0, 125); if (BioIndicator == true) { if (!string.IsNullOrEmpty(bIO_INTENT_NAME)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("BiologicalIntent", bIO_INTENT_NAME.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Biological Intent not valid. "; } a.BIO_INTENT_NAME = bIO_INTENT_NAME.Trim().SubStringPlus(0, 35); if (string.IsNullOrEmpty(bIO_SUBJECT_TAXONOMY)) { sTATUS_CD = "F"; sTATUS_DESC += "Taxonomy must be reported when intent is reported. "; } } if (!string.IsNullOrEmpty(bIO_INDIVIDUAL_ID)) a.BIO_INDIVIDUAL_ID = bIO_INDIVIDUAL_ID.Trim().SubStringPlus(0, 4); if (!string.IsNullOrEmpty(bIO_SUBJECT_TAXONOMY)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("Taxon", bIO_SUBJECT_TAXONOMY.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Subject Taxonomy not valid. "; } a.BIO_SUBJECT_TAXONOMY = bIO_SUBJECT_TAXONOMY.Trim().SubStringPlus(0, 120); if (string.IsNullOrEmpty(bIO_INTENT_NAME)) { sTATUS_CD = "F"; sTATUS_DESC += "Biological intent must be reported when taxonomy is reported. "; } } if (!string.IsNullOrEmpty(bIO_UNIDENTIFIED_SPECIES_ID)) a.BIO_UNIDENTIFIED_SPECIES_ID = bIO_UNIDENTIFIED_SPECIES_ID.Trim().SubStringPlus(0, 120); if (!string.IsNullOrEmpty(bIO_SAMPLE_TISSUE_ANATOMY)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("SampleTissueAnatomy", bIO_SAMPLE_TISSUE_ANATOMY.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Sample Tissue Anatomy not valid. "; } a.BIO_SAMPLE_TISSUE_ANATOMY = bIO_SAMPLE_TISSUE_ANATOMY.Trim().SubStringPlus(0, 30); } if (!string.IsNullOrEmpty(gRP_SUMM_COUNT_WEIGHT_MSR)) a.GRP_SUMM_COUNT_WEIGHT_MSR = gRP_SUMM_COUNT_WEIGHT_MSR.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(gRP_SUMM_COUNT_WEIGHT_MSR_UNIT)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", gRP_SUMM_COUNT_WEIGHT_MSR_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Group Summary Unit not valid. "; } a.GRP_SUMM_COUNT_WEIGHT_MSR_UNIT = gRP_SUMM_COUNT_WEIGHT_MSR_UNIT.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(tAX_DTL_CELL_FORM)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("CellForm", tAX_DTL_CELL_FORM.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Cell Form not valid. "; } a.TAX_DTL_CELL_FORM = tAX_DTL_CELL_FORM.Trim().SubStringPlus(0, 11); } if (!string.IsNullOrEmpty(tAX_DTL_CELL_SHAPE)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("CellShape", tAX_DTL_CELL_SHAPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Cell Shape not valid. "; } a.TAX_DTL_CELL_SHAPE = tAX_DTL_CELL_SHAPE.Trim().SubStringPlus(0, 18); } if (!string.IsNullOrEmpty(tAX_DTL_HABIT)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("Habit", tAX_DTL_HABIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Habit not valid. "; } a.TAX_DTL_HABIT = tAX_DTL_HABIT.Trim().SubStringPlus(0, 15); } if (!string.IsNullOrEmpty(tAX_DTL_VOLTINISM)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("Voltinism", tAX_DTL_VOLTINISM.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Voltinism not valid. "; } a.TAX_DTL_VOLTINISM = tAX_DTL_VOLTINISM.Trim().SubStringPlus(0, 25); } if (!string.IsNullOrEmpty(tAX_DTL_POLL_TOLERANCE)) a.TAX_DTL_POLL_TOLERANCE = tAX_DTL_POLL_TOLERANCE.Trim().SubStringPlus(0, 4); if (!string.IsNullOrEmpty(tAX_DTL_POLL_TOLERANCE_SCALE)) a.TAX_DTL_POLL_TOLERANCE_SCALE = tAX_DTL_POLL_TOLERANCE_SCALE.Trim().SubStringPlus(0, 50); if (!string.IsNullOrEmpty(tAX_DTL_TROPHIC_LEVEL)) a.TAX_DTL_TROPHIC_LEVEL = tAX_DTL_TROPHIC_LEVEL.Trim().SubStringPlus(0, 4); if (!string.IsNullOrEmpty(tAX_DTL_FUNC_FEEDING_GROUP1)) a.TAX_DTL_FUNC_FEEDING_GROUP1 = tAX_DTL_FUNC_FEEDING_GROUP1.Trim().SubStringPlus(0, 6); if (!string.IsNullOrEmpty(tAX_DTL_FUNC_FEEDING_GROUP2)) a.TAX_DTL_FUNC_FEEDING_GROUP2 = tAX_DTL_FUNC_FEEDING_GROUP2.Trim().SubStringPlus(0, 6); if (!string.IsNullOrEmpty(tAX_DTL_FUNC_FEEDING_GROUP3)) a.TAX_DTL_FUNC_FEEDING_GROUP3 = tAX_DTL_FUNC_FEEDING_GROUP3.Trim().SubStringPlus(0, 6); if (!string.IsNullOrEmpty(fREQ_CLASS_CODE)) a.FREQ_CLASS_CODE = fREQ_CLASS_CODE.Trim().SubStringPlus(0, 50); if (!string.IsNullOrEmpty(fREQ_CLASS_UNIT)) a.FREQ_CLASS_UNIT = fREQ_CLASS_UNIT.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(fREQ_CLASS_CODE)) a.FREQ_CLASS_UPPER = fREQ_CLASS_UPPER.Trim().SubStringPlus(0, 8); if (!string.IsNullOrEmpty(fREQ_CLASS_CODE)) a.FREQ_CLASS_LOWER = fREQ_CLASS_LOWER.Trim().SubStringPlus(0, 8); } //analysis method //first populate the IDX if it is supplied if (aNALYTIC_METHOD_IDX != null) a.ANALYTIC_METHOD_IDX = aNALYTIC_METHOD_IDX; else { //if ID is supplied but Context is not, set context to org id if (!string.IsNullOrEmpty(aNALYTIC_METHOD_ID) && string.IsNullOrEmpty(aNALYTIC_METHOD_CTX)) aNALYTIC_METHOD_CTX = orgID; //if we now have values for the ID and context if (!string.IsNullOrEmpty(aNALYTIC_METHOD_ID) && !string.IsNullOrEmpty(aNALYTIC_METHOD_CTX)) { //see if matching collection method exists T_WQX_REF_ANAL_METHOD am = db_Ref.GetT_WQX_REF_ANAL_METHODByIDandContext(aNALYTIC_METHOD_ID.Trim(), aNALYTIC_METHOD_CTX.Trim()); if (am != null) a.ANALYTIC_METHOD_IDX = am.ANALYTIC_METHOD_IDX; else //no matching anal method lookup found { if (autoImportRefDataInd == true) { db_Ref.InsertOrUpdateT_WQX_REF_ANAL_METHOD(null, aNALYTIC_METHOD_ID.Trim(), aNALYTIC_METHOD_CTX.Trim(), aNALYTIC_METHOD_NAME.Trim(), "", true); } else { sTATUS_CD = "F"; sTATUS_DESC += "No matching Analysis Method found - please add it at the Reference Data screen first. "; } } //**************************************** a.ANALYTIC_METHOD_ID = aNALYTIC_METHOD_ID.Trim().SubStringPlus(0, 20); a.ANALYTIC_METHOD_CTX = aNALYTIC_METHOD_CTX.Trim().SubStringPlus(0, 120); if (!string.IsNullOrEmpty(aNALYTIC_METHOD_NAME)) a.ANALYTIC_METHOD_NAME = aNALYTIC_METHOD_NAME.Trim().SubStringPlus(0, 120); } else { //if IDX, ID, and Context not supplied, lookup the method from the default Org Char reference list T_WQX_REF_CHAR_ORG rco = db_Ref.GetT_WQX_REF_CHAR_ORGByName(orgID, cHAR_NAME.Trim().SubStringPlus(0, 120)); if (rco != null) { a.ANALYTIC_METHOD_IDX = rco.DEFAULT_ANAL_METHOD_IDX; if (rco.DEFAULT_ANAL_METHOD_IDX != null) { T_WQX_REF_ANAL_METHOD anal = db_Ref.GetT_WQX_REF_ANAL_METHODByIDX(rco.DEFAULT_ANAL_METHOD_IDX.ConvertOrDefault<int>()); if (anal != null) { a.ANALYTIC_METHOD_ID = anal.ANALYTIC_METHOD_ID; a.ANALYTIC_METHOD_NAME = anal.ANALYTIC_METHOD_NAME; a.ANALYTIC_METHOD_CTX = anal.ANALYTIC_METHOD_CTX; } } } } } //********** LABORATORY ********** if (lAB_IDX != null) a.LAB_IDX = lAB_IDX; else { if (!string.IsNullOrEmpty(lAB_NAME)) { a.LAB_NAME = lAB_NAME; //see if matching lab name exists for this org T_WQX_REF_LAB lab = db_Ref.GetT_WQX_REF_LAB_ByIDandContext(lAB_NAME, orgID); if (lab == null) { if (autoImportRefDataInd == true) { db_Ref.InsertOrUpdateT_WQX_REF_LAB(null, lAB_NAME.Trim(), null, null, orgID, true); } else { sTATUS_CD = "F"; sTATUS_DESC += "No matching Lab Name found - please add it at the Reference Data screen first. "; } } else a.LAB_IDX = lab.LAB_IDX; } } if (lAB_ANALYSIS_START_DT != null) { //fix improperly formatted datetime if (lAB_ANALYSIS_START_DT.ConvertOrDefault<DateTime>().Year < 1900) lAB_ANALYSIS_START_DT = null; a.LAB_ANALYSIS_START_DT = lAB_ANALYSIS_START_DT; } if (lAB_ANALYSIS_END_DT != null) { //fix improperly formatted datetime if (lAB_ANALYSIS_END_DT.ConvertOrDefault<DateTime>().Year < 1900) lAB_ANALYSIS_END_DT = null; a.LAB_ANALYSIS_END_DT = lAB_ANALYSIS_END_DT; } if (!string.IsNullOrEmpty(lAB_ANALYSIS_TIMEZONE)) { a.LAB_ANALYSIS_TIMEZONE = lAB_ANALYSIS_TIMEZONE.Trim().SubStringPlus(0, 4); if (db_Ref.GetT_WQX_REF_DATA_ByKey("TimeZone", lAB_ANALYSIS_TIMEZONE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "TimeZone not valid. "; } } else { //put in Timezone if missing if (lAB_ANALYSIS_START_DT != null || lAB_ANALYSIS_END_DT != null) a.LAB_ANALYSIS_TIMEZONE = Utils.GetWQXTimeZoneByDate(a.LAB_ANALYSIS_TIMEZONE.ConvertOrDefault<DateTime>()); } if (!string.IsNullOrEmpty(rESULT_LAB_COMMENT_CODE)) a.RESULT_LAB_COMMENT_CODE = rESULT_LAB_COMMENT_CODE.Trim().SubStringPlus(0, 3); if (!string.IsNullOrEmpty(mETHOD_DETECTION_LEVEL)) a.METHOD_DETECTION_LEVEL = mETHOD_DETECTION_LEVEL.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(lAB_REPORTING_LEVEL)) a.LAB_REPORTING_LEVEL = lAB_REPORTING_LEVEL.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(pQL)) a.PQL = pQL.Trim().SubStringPlus(0, 12); if (!string.IsNullOrEmpty(lOWER_QUANT_LIMIT)) a.LOWER_QUANT_LIMIT = lOWER_QUANT_LIMIT.Trim().SubStringPlus(0, 12); //if result is PBQL, but no value has been reported for MDL, LRL, PQL, or Lower Quant Limit, then grab from Org Char default if (rESULT_DETECT_CONDITION == "Present Below Quantification Limit" && string.IsNullOrEmpty(mETHOD_DETECTION_LEVEL) && string.IsNullOrEmpty(lAB_REPORTING_LEVEL) && string.IsNullOrEmpty(pQL) && string.IsNullOrEmpty(lOWER_QUANT_LIMIT)) { T_WQX_REF_CHAR_ORG rco = db_Ref.GetT_WQX_REF_CHAR_ORGByName(orgID, cHAR_NAME); if (rco != null) a.LOWER_QUANT_LIMIT = rco.DEFAULT_LOWER_QUANT_LIMIT; //if still null, then error if (a.LOWER_QUANT_LIMIT == null) { sTATUS_CD = "F"; sTATUS_DESC += "No Lower Quantification limit reported or default value specified. "; } } if (!string.IsNullOrEmpty(uPPER_QUANT_LIMIT)) a.UPPER_QUANT_LIMIT = uPPER_QUANT_LIMIT.Trim().SubStringPlus(0, 12); //if result is PAQL, but no value has been reported for MDL, LRL, PQL, or Lower Quant Limit, then grab from Org Char default if (rESULT_DETECT_CONDITION == "Present Above Quantification Limit" && string.IsNullOrEmpty(mETHOD_DETECTION_LEVEL) && string.IsNullOrEmpty(lAB_REPORTING_LEVEL) && string.IsNullOrEmpty(pQL) && string.IsNullOrEmpty(uPPER_QUANT_LIMIT)) { T_WQX_REF_CHAR_ORG rco = db_Ref.GetT_WQX_REF_CHAR_ORGByName(orgID, cHAR_NAME); if (rco != null) a.UPPER_QUANT_LIMIT = rco.DEFAULT_UPPER_QUANT_LIMIT; //if still null, then error if (a.UPPER_QUANT_LIMIT == null) { sTATUS_CD = "F"; sTATUS_DESC += "No Upper Quantification limit reported. "; } } if (!string.IsNullOrEmpty(dETECTION_LIMIT_UNIT)) { if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", dETECTION_LIMIT_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Detection Level Unit not valid. "; } a.DETECTION_LIMIT_UNIT = dETECTION_LIMIT_UNIT.Trim().SubStringPlus(0, 12); } //********** LAB SAMPLE PREP if (lAB_SAMP_PREP_IDX != null) a.LAB_SAMP_PREP_IDX = lAB_SAMP_PREP_IDX; else { if (!string.IsNullOrEmpty(lAB_SAMP_PREP_ID)) { //set context to org id if none is provided if (string.IsNullOrEmpty(lAB_SAMP_PREP_CTX)) lAB_SAMP_PREP_CTX = orgID; a.LAB_SAMP_PREP_ID = lAB_SAMP_PREP_ID.Trim().SubStringPlus(0, 20); a.LAB_SAMP_PREP_CTX = lAB_SAMP_PREP_CTX.Trim().SubStringPlus(0, 120); //see if matching lab prep method exists for this org T_WQX_REF_SAMP_PREP ppp = db_Ref.GetT_WQX_REF_SAMP_PREP_ByIDandContext(lAB_SAMP_PREP_ID, lAB_SAMP_PREP_CTX); if (ppp == null) //no match found { if (autoImportRefDataInd == true) { db_Ref.InsertOrUpdateT_WQX_REF_SAMP_PREP(null, lAB_SAMP_PREP_ID.Trim(), lAB_SAMP_PREP_CTX.Trim(), lAB_SAMP_PREP_ID.Trim(), "", true); } else { sTATUS_CD = "F"; sTATUS_DESC += "No matching Lab Sample Prep ID found - please add it at the Reference Data screen first. "; } } else //match found a.LAB_SAMP_PREP_IDX = ppp.SAMP_PREP_IDX; } } if (lAB_SAMP_PREP_START_DT != null) { //fix improperly formatted datetime if (lAB_SAMP_PREP_START_DT.ConvertOrDefault<DateTime>().Year < 1900) lAB_SAMP_PREP_START_DT = null; a.LAB_SAMP_PREP_START_DT = lAB_SAMP_PREP_START_DT; } if (lAB_SAMP_PREP_END_DT != null) { //fix improperly formatted datetime if (lAB_SAMP_PREP_END_DT.ConvertOrDefault<DateTime>().Year < 1900) lAB_SAMP_PREP_END_DT = null; a.LAB_SAMP_PREP_END_DT = lAB_SAMP_PREP_END_DT; } if (!string.IsNullOrEmpty(dILUTION_FACTOR)) a.DILUTION_FACTOR = dILUTION_FACTOR.Trim().SubStringPlus(0, 12); if (sTATUS_CD != null) a.IMPORT_STATUS_CD = sTATUS_CD; if (sTATUS_DESC != null) a.IMPORT_STATUS_DESC = sTATUS_DESC.SubStringPlus(0,100); if (insInd) //insert case ctx.AddToT_WQX_IMPORT_TEMP_RESULT(a); ctx.SaveChanges(); return a.TEMP_RESULT_IDX; } catch (Exception ex) { return 0; } } }
// *************************** IMPORT: MONLOC ****************************** // ***************************************************************************** public static int InsertOrUpdateWQX_IMPORT_TEMP_MONLOC(global::System.Int32? tEMP_MONLOC_IDX, string uSER_ID, global::System.Int32? mONLOC_IDX, global::System.String oRG_ID, global::System.String mONLOC_ID, global::System.String mONLOC_NAME, global::System.String mONLOC_TYPE, global::System.String mONLOC_DESC, global::System.String hUC_EIGHT, global::System.String HUC_TWELVE, global::System.String tRIBAL_LAND_IND, global::System.String tRIBAL_LAND_NAME, global::System.String lATITUDE_MSR, global::System.String lONGITUDE_MSR, global::System.Int32? sOURCE_MAP_SCALE, global::System.String hORIZ_ACCURACY, global::System.String hORIZ_ACCURACY_UNIT, global::System.String hORIZ_COLL_METHOD, global::System.String hORIZ_REF_DATUM, global::System.String vERT_MEASURE, global::System.String vERT_MEASURE_UNIT, global::System.String vERT_COLL_METHOD, global::System.String vERT_REF_DATUM, global::System.String cOUNTRY_CODE, global::System.String sTATE_CODE, global::System.String cOUNTY_CODE, global::System.String wELL_TYPE, global::System.String aQUIFER_NAME, global::System.String fORMATION_TYPE, global::System.String wELLHOLE_DEPTH_MSR, global::System.String wELLHOLE_DEPTH_MSR_UNIT, string sTATUS_CD, string sTATUS_DESC) { using (OpenEnvironmentEntities ctx = new OpenEnvironmentEntities()) { Boolean insInd = false; try { T_WQX_IMPORT_TEMP_MONLOC a = new T_WQX_IMPORT_TEMP_MONLOC(); if (tEMP_MONLOC_IDX != null) a = (from c in ctx.T_WQX_IMPORT_TEMP_MONLOC where c.TEMP_MONLOC_IDX == tEMP_MONLOC_IDX select c).FirstOrDefault(); else insInd = true; if (uSER_ID != null) { a.USER_ID = uSER_ID; if (uSER_ID.Length > 25) { sTATUS_CD = "F"; sTATUS_DESC += "User ID length exceeded. "; } } if (mONLOC_IDX != null) a.MONLOC_IDX = mONLOC_IDX; if (oRG_ID != null) a.ORG_ID = oRG_ID; if (mONLOC_ID != null) { a.MONLOC_ID = mONLOC_ID.SubStringPlus(0, 35).Trim(); if (mONLOC_ID.Length > 35) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location ID length exceeded. "; } T_WQX_MONLOC mtemp = db_WQX.GetWQX_MONLOC_ByIDString(oRG_ID, mONLOC_ID); if (mtemp != null) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location ID already exists. "; } } if (!string.IsNullOrEmpty(mONLOC_NAME)) { a.MONLOC_NAME = mONLOC_NAME.SubStringPlus(0, 255).Trim(); if (mONLOC_NAME.Length > 255) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location Name length exceeded. "; } } if (!string.IsNullOrEmpty(mONLOC_TYPE)) { a.MONLOC_TYPE = mONLOC_TYPE.SubStringPlus(0, 45); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MonitoringLocationType", mONLOC_TYPE.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location Type not valid. "; } } if (!string.IsNullOrEmpty(mONLOC_DESC)) { a.MONLOC_DESC = mONLOC_DESC.SubStringPlus(0, 1999); if (mONLOC_DESC.Length > 1999) { sTATUS_CD = "F"; sTATUS_DESC += "Monitoring Location Description length exceeded. "; } } if (!string.IsNullOrEmpty(hUC_EIGHT)) { a.HUC_EIGHT = hUC_EIGHT.Trim().SubStringPlus(0, 8); if (hUC_EIGHT.Length > 8) { sTATUS_CD = "F"; sTATUS_DESC += "HUC8 length exceeded. "; } } if (!string.IsNullOrEmpty(HUC_TWELVE)) { a.HUC_TWELVE = HUC_TWELVE.Trim().SubStringPlus(0, 12); if (HUC_TWELVE.Length > 12) { sTATUS_CD = "F"; sTATUS_DESC += "HUC12 length exceeded. "; } } if (!string.IsNullOrEmpty(tRIBAL_LAND_IND)) { if (tRIBAL_LAND_IND.ToUpper() == "TRUE") tRIBAL_LAND_IND = "Y"; if (tRIBAL_LAND_IND.ToUpper() == "FALSE") tRIBAL_LAND_IND = "N"; a.TRIBAL_LAND_IND = tRIBAL_LAND_IND.SubStringPlus(0, 1); if (tRIBAL_LAND_IND.Length > 1) { sTATUS_CD = "F"; sTATUS_DESC += "Tribal Land Indicator length exceeded. "; } } if (!string.IsNullOrEmpty(tRIBAL_LAND_NAME)) { a.TRIBAL_LAND_NAME = tRIBAL_LAND_NAME.SubStringPlus(0, 200); if (tRIBAL_LAND_NAME.Length > 200) { sTATUS_CD = "F"; sTATUS_DESC += "Tribal Land Name length exceeded. "; } } if (!string.IsNullOrEmpty(lATITUDE_MSR)) { a.LATITUDE_MSR = lATITUDE_MSR.SubStringPlus(0, 30); decimal iii = 0; if (Decimal.TryParse(lATITUDE_MSR, out iii) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Latitude is not decimal format. "; } } if (!string.IsNullOrEmpty(lONGITUDE_MSR)) { a.LONGITUDE_MSR = lONGITUDE_MSR.SubStringPlus(0, 30); decimal iii = 0; if (Decimal.TryParse(lONGITUDE_MSR, out iii) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Longitude is not decimal format. "; } } if (sOURCE_MAP_SCALE != null) { a.SOURCE_MAP_SCALE = sOURCE_MAP_SCALE; } if (!string.IsNullOrEmpty(hORIZ_COLL_METHOD)) { a.HORIZ_COLL_METHOD = hORIZ_COLL_METHOD.SubStringPlus(0, 150).Trim(); if (db_Ref.GetT_WQX_REF_DATA_ByKey("HorizontalCollectionMethod", hORIZ_COLL_METHOD.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Horizontal Collection Method not valid. "; } } if (!string.IsNullOrEmpty(hORIZ_REF_DATUM)) { a.HORIZ_REF_DATUM = hORIZ_REF_DATUM.Trim().SubStringPlus(0, 6); if (db_Ref.GetT_WQX_REF_DATA_ByKey("HorizontalCoordinateReferenceSystemDatum", hORIZ_REF_DATUM.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Horizontal Collection Datum not valid. "; } } if (!string.IsNullOrEmpty(vERT_MEASURE)) { a.VERT_MEASURE = vERT_MEASURE.Trim().SubStringPlus(0, 12); if (vERT_MEASURE.Length > 12) { sTATUS_CD = "F"; sTATUS_DESC += "Vertical Measure length exceeded. "; } } if (!string.IsNullOrEmpty(vERT_MEASURE_UNIT)) { a.VERT_MEASURE_UNIT = vERT_MEASURE_UNIT.Trim().SubStringPlus(0, 12); if (db_Ref.GetT_WQX_REF_DATA_ByKey("MeasureUnit", vERT_MEASURE_UNIT.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Vertical Measure Unit not valid. "; } } if (!string.IsNullOrEmpty(vERT_COLL_METHOD)) { a.VERT_COLL_METHOD = vERT_COLL_METHOD.Trim().SubStringPlus(0, 50); if (db_Ref.GetT_WQX_REF_DATA_ByKey("VerticalCollectionMethod", vERT_COLL_METHOD.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Vertical Collection Method not acceptable. "; } } if (!string.IsNullOrEmpty(vERT_REF_DATUM)) { a.VERT_REF_DATUM = vERT_REF_DATUM.Trim().SubStringPlus(0, 6); if (db_Ref.GetT_WQX_REF_DATA_ByKey("VerticalCoordinateReferenceSystemDatum", vERT_REF_DATUM.Trim()) == false) { sTATUS_CD = "F"; sTATUS_DESC += "Vertical Collection Datum not acceptable. "; } } if (!string.IsNullOrEmpty(cOUNTRY_CODE)) { //if there is a match of country NAME value to reference data text (in case user is importing country name instead of code) T_WQX_REF_DATA rd = db_Ref.GetT_WQX_REF_DATA_ByTextGetRow("Country", cOUNTRY_CODE); if (rd != null) a.COUNTRY_CODE = rd.VALUE.SubStringPlus(0, 2); else { a.COUNTRY_CODE = cOUNTRY_CODE.SubStringPlus(0, 2); if (cOUNTRY_CODE.Length > 2) { sTATUS_CD = "F"; sTATUS_DESC += "Country Code length exceeded. "; } } } if (!string.IsNullOrEmpty(sTATE_CODE)) { //if there is a match of state value to reference data text (in case user is importing state name instead of code) T_WQX_REF_DATA rd = db_Ref.GetT_WQX_REF_DATA_ByTextGetRow("State", sTATE_CODE); if (rd != null) a.STATE_CODE = rd.VALUE; else { a.STATE_CODE = sTATE_CODE.SubStringPlus(0, 2); if (sTATE_CODE.Length > 2) { sTATUS_CD = "F"; sTATUS_DESC += "State Code length exceeded. "; } } } if (!string.IsNullOrEmpty(cOUNTY_CODE)) { //if there is a match of county value to reference data text (in case user is importing county text instead of code) T_WQX_REF_COUNTY c = db_Ref.GetT_WQX_REF_COUNTY_ByCountyNameAndState(sTATE_CODE, cOUNTY_CODE); if (c != null) a.COUNTY_CODE = c.COUNTY_CODE; else { a.COUNTY_CODE = cOUNTY_CODE.SubStringPlus(0, 3); if (cOUNTY_CODE.Length > 3) { sTATUS_CD = "F"; sTATUS_DESC += "County Code length exceeded. "; } } } if (!string.IsNullOrEmpty(wELL_TYPE)) { a.WELL_TYPE = wELL_TYPE.Trim().SubStringPlus(0, 255); } if (!string.IsNullOrEmpty(aQUIFER_NAME)) { a.AQUIFER_NAME = aQUIFER_NAME.Trim().SubStringPlus(0, 120); } if (!string.IsNullOrEmpty(fORMATION_TYPE)) { a.FORMATION_TYPE = fORMATION_TYPE.Trim().SubStringPlus(0, 50); } if (!string.IsNullOrEmpty(wELLHOLE_DEPTH_MSR)) { a.WELLHOLE_DEPTH_MSR = wELLHOLE_DEPTH_MSR.Trim().SubStringPlus(0, 12); } if (!string.IsNullOrEmpty(wELLHOLE_DEPTH_MSR_UNIT)) { a.WELLHOLE_DEPTH_MSR_UNIT = wELLHOLE_DEPTH_MSR_UNIT.Trim().SubStringPlus(0, 12); } if (sTATUS_CD != null) a.IMPORT_STATUS_CD = sTATUS_CD; if (sTATUS_DESC != null) a.IMPORT_STATUS_DESC = sTATUS_DESC.SubStringPlus(0, 100); if (insInd) //insert case ctx.AddToT_WQX_IMPORT_TEMP_MONLOC(a); ctx.SaveChanges(); return a.TEMP_MONLOC_IDX; } catch (Exception ex) { return 0; } } }