public List <CarrierIdentifier> GetCarrierIdentifiers(string carrierIdentifierQuery, SqlConnection cnn_sql4, string assignmentId) { List <CarrierIdentifier> carrierIdentifierList = new List <CarrierIdentifier>(); try { SqlCommand commandCarrierIdentifier = new SqlCommand(carrierIdentifierQuery, cnn_sql4); commandCarrierIdentifier.Parameters.Add(new SqlParameter("assignmentId", assignmentId)); SqlDataReader getCarrierIdentifier = commandCarrierIdentifier.ExecuteReader(); if (getCarrierIdentifier.HasRows) { while (getCarrierIdentifier.Read()) { CarrierIdentifier carrierIdentifier = new CarrierIdentifier(); carrierIdentifier.type = getCarrierIdentifier["certification"].ToString(); carrierIdentifier.value = getCarrierIdentifier["certification_number"].ToString(); carrierIdentifierList.Add(carrierIdentifier); } } else { } getCarrierIdentifier.Close(); } catch (ArgumentOutOfRangeException e) { Console.WriteLine(e.Message); } return(carrierIdentifierList); }
/// <inheritdoc/> public string ToDelimitedString() { CultureInfo culture = CultureInfo.CurrentCulture; return(string.Format( culture, StringHelper.StringFormatSequence(0, 50, Configuration.FieldSeparator), Id, ExternalAccessionIdentifier?.ToDelimitedString(), AccessionIdentifier?.ToDelimitedString(), ContainerIdentifier?.ToDelimitedString(), PrimaryParentContainerIdentifier?.ToDelimitedString(), EquipmentContainerIdentifier?.ToDelimitedString(), SpecimenSource, RegistrationDateTime.HasValue ? RegistrationDateTime.Value.ToString(Consts.DateTimeFormatPrecisionSecond, culture) : null, ContainerStatus?.ToDelimitedString(), CarrierType?.ToDelimitedString(), CarrierIdentifier?.ToDelimitedString(), PositionInCarrier?.ToDelimitedString(), TrayTypeSac?.ToDelimitedString(), TrayIdentifier?.ToDelimitedString(), PositionInTray?.ToDelimitedString(), Location != null ? string.Join(Configuration.FieldRepeatSeparator, Location.Select(x => x.ToDelimitedString())) : null, ContainerHeight.HasValue ? ContainerHeight.Value.ToString(Consts.NumericFormat, culture) : null, ContainerDiameter.HasValue ? ContainerDiameter.Value.ToString(Consts.NumericFormat, culture) : null, BarrierDelta.HasValue ? BarrierDelta.Value.ToString(Consts.NumericFormat, culture) : null, BottomDelta.HasValue ? BottomDelta.Value.ToString(Consts.NumericFormat, culture) : null, ContainerHeightDiameterDeltaUnits?.ToDelimitedString(), ContainerVolume.HasValue ? ContainerVolume.Value.ToString(Consts.NumericFormat, culture) : null, AvailableSpecimenVolume.HasValue ? AvailableSpecimenVolume.Value.ToString(Consts.NumericFormat, culture) : null, InitialSpecimenVolume.HasValue ? InitialSpecimenVolume.Value.ToString(Consts.NumericFormat, culture) : null, VolumeUnits?.ToDelimitedString(), SeparatorType?.ToDelimitedString(), CapType?.ToDelimitedString(), Additive != null ? string.Join(Configuration.FieldRepeatSeparator, Additive.Select(x => x.ToDelimitedString())) : null, SpecimenComponent?.ToDelimitedString(), DilutionFactor?.ToDelimitedString(), Treatment?.ToDelimitedString(), Temperature?.ToDelimitedString(), HemolysisIndex.HasValue ? HemolysisIndex.Value.ToString(Consts.NumericFormat, culture) : null, HemolysisIndexUnits?.ToDelimitedString(), LipemiaIndex.HasValue ? LipemiaIndex.Value.ToString(Consts.NumericFormat, culture) : null, LipemiaIndexUnits?.ToDelimitedString(), IcterusIndex.HasValue ? IcterusIndex.Value.ToString(Consts.NumericFormat, culture) : null, IcterusIndexUnits?.ToDelimitedString(), FibrinIndex.HasValue ? FibrinIndex.Value.ToString(Consts.NumericFormat, culture) : null, FibrinIndexUnits?.ToDelimitedString(), SystemInducedContaminants != null ? string.Join(Configuration.FieldRepeatSeparator, SystemInducedContaminants.Select(x => x.ToDelimitedString())) : null, DrugInterference != null ? string.Join(Configuration.FieldRepeatSeparator, DrugInterference.Select(x => x.ToDelimitedString())) : null, ArtificialBlood?.ToDelimitedString(), SpecialHandlingCode != null ? string.Join(Configuration.FieldRepeatSeparator, SpecialHandlingCode.Select(x => x.ToDelimitedString())) : null, OtherEnvironmentalFactors != null ? string.Join(Configuration.FieldRepeatSeparator, OtherEnvironmentalFactors.Select(x => x.ToDelimitedString())) : null, ContainerLength?.ToDelimitedString(), ContainerWidth?.ToDelimitedString(), ContainerForm?.ToDelimitedString(), ContainerMaterial?.ToDelimitedString(), ContainerCommonName?.ToDelimitedString() ).TrimEnd(Configuration.FieldSeparator.ToCharArray())); }
static public CarrierIdentifier formatCarrierIdentifierType(CarrierIdentifier carrierIdentifier) { if (carrierIdentifier.type == "MC#") { carrierIdentifier.type = "MC_NUMBER"; } if (carrierIdentifier.type == "DOT NUMBER") { carrierIdentifier.type = "DOT_NUMBER"; } return(carrierIdentifier); }
// string testQuery= "select * from probills"; public async Task Execute(IJobExecutionContext context) { // job1 , check if there is any new Leg need to be tracked. // better to put this job into a function // question 1, how can we make the exsitELDList list to be reuseable... List <ReferenceTruck> exsitingTrackingList = new List <ReferenceTruck>(); List <ReferenceTruck> newTrackingList = new List <ReferenceTruck>(); List <ReferenceTruck> LegForKraftList = new List <ReferenceTruck>(); try { //using (SqlConnection con_sqlsrv01 = new SqlConnection(_con_sqlsrv01)) //{ // con_sqlsrv01.Open(); // SqlCommand command = new SqlCommand(exsitELDQuery, con_sqlsrv01); // SqlDataReader dataReader = command.ExecuteReader(); // if (!dataReader.HasRows) // { // Console.WriteLine("no info found in existELD"); // } // else // { // // List<ReferenceELD> exsitELDList= new List<ReferenceELD>(); // while (dataReader.Read()) // { // ReferenceELD exsitELD = new ReferenceELD(); // exsitELD.probill = (string)dataReader.GetValue(0); // exsitELD.ELDNum = (string)dataReader.GetValue(1); // exsitELDList.Add(exsitELD); // } // } //} using (SqlConnection cnn_sql4 = new SqlConnection(_con_sql4)) { cnn_sql4.Open(); SqlCommand command = new SqlCommand(isNewTrackingQuery, cnn_sql4); SqlDataReader dataReader = command.ExecuteReader(); if (!dataReader.HasRows) { Console.WriteLine("no info found in existELD"); } else { // List<ReferenceELD> exsitELDList= new List<ReferenceELD>(); while (dataReader.Read()) { ReferenceTruck newELD = new ReferenceTruck(); newELD.probill = (string)dataReader.GetValue(0); newELD.truckId = (string)dataReader.GetValue(1); newTrackingList.Add(newELD); } dataReader.Close(); foreach (ReferenceTruck r in newTrackingList) { if (!exsitingTrackingList.Contains(r)) { SqlCommand commandCheckIfKraft = new SqlCommand(isDeliverOrPickUpByKraft, cnn_sql4); commandCheckIfKraft.Parameters.Add(new SqlParameter("probillNum", r.probill)); commandCheckIfKraft.Parameters.Add(new SqlParameter("trackId", r.truckId)); SqlDataReader dataReaderKraft = commandCheckIfKraft.ExecuteReader(CommandBehavior.SingleRow); if (dataReaderKraft.Read()) { string departStartDate = dataReaderKraft["picked_up_on"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate(dataReaderKraft["pick_up_date"].ToString(), true, 0) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["picked_up_on"].ToString(), true, 0); string departStartTime = dataReaderKraft["picked_up_at"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate(dataReaderKraft["pick_up_time"].ToString(), false, 0) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["picked_up_at"].ToString(), false, 0); string departEndtime = dataReaderKraft["picked_up_at"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate((dataReaderKraft["pick_up_time"].ToString()), false, 120) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["picked_up_at"].ToString(), false, 120); string arrivalStartDate = dataReaderKraft["delivered_on"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate(dataReaderKraft["delivery_date"].ToString(), true, 0) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["delivered_on"].ToString(), true, 0); string arrivalStartTime = dataReaderKraft["delivered_at"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate(dataReaderKraft["delivery_time"].ToString(), false, 0) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["delivered_at"].ToString(), false, 0); string arrivalEndtime = dataReaderKraft["delivered_at"].ToString().Length == 0 ? HelperFunction.HelperFunction.formatDate((dataReaderKraft["delivery_time"].ToString()), false, 120) : HelperFunction.HelperFunction.formatDate(dataReaderKraft["delivered_at"].ToString(), false, 120); string departLocationId = dataReaderKraft["pick_up"].ToString(); string destinationLocationId = dataReaderKraft["deliver"].ToString(); string billOfLading = dataReaderKraft["bill_of_lading"].ToString(); string assignmentId = dataReaderKraft["assignment_id"].ToString(); string conpanyInfoQuery = "select [company_name] ,[postal],[street_number],[street_name], [city],[state],[country]from companies where company_id =@companyId"; string carrierIdentifierQuery = "select distinct r.certification , r.certification_number from [resource_certification] as r" + " join[carrier_assignments] as c on r.resource_id = c.carrier_id " + " where(certification = 'DOT Number' or certification = 'MC#' or certification = 'SCAC') and c.[number]=@assignmentId order by [certification]"; string shipmentIdentifierQuery = "select reference_id from reference_numbers where pro =@probillNum"; dataReaderKraft.Close(); //start point: generate json RootObject jsonProLeg = new RootObject(); // get shipmentIdentifiers // if bill_of_lading is NULL ShipmentIdentifier shipmentIdentifier = new ShipmentIdentifier(); if (billOfLading.Length == 0) {// shipmentIdentifier will be the first po # shipmentIdentifier = GetShiomentIdentifier(shipmentIdentifierQuery, cnn_sql4, r.probill); } else { shipmentIdentifier.type = "BILL_OF_LADING"; shipmentIdentifier.value = billOfLading; } // get depart location information ShipmentStop depart = new ShipmentStop(); depart.stopNumber = 1; depart.appointmentWindow.startDateTime = departStartDate + "T" + departStartTime; depart.appointmentWindow.endDateTime = departStartDate + "T" + departEndtime; depart.location = GetLocation(conpanyInfoQuery, cnn_sql4, departLocationId); // get depart location information ShipmentStop destination = new ShipmentStop(); destination.stopNumber = 2; destination.appointmentWindow.startDateTime = arrivalStartDate + "T" + arrivalStartTime; destination.appointmentWindow.endDateTime = arrivalStartDate + "T" + arrivalEndtime; destination.location = GetLocation(conpanyInfoQuery, cnn_sql4, destinationLocationId); // get carrierldentifier List <CarrierIdentifier> carrierIdentifierList = GetCarrierIdentifiers(carrierIdentifierQuery, cnn_sql4, assignmentId); CarrierIdentifier carrierIdentifier = new CarrierIdentifier(); if (carrierIdentifierList.Count == 3) { carrierIdentifier = HelperFunction.HelperFunction.formatCarrierIdentifierType(carrierIdentifierList.ElementAt(1)); } else { carrierIdentifier = HelperFunction.HelperFunction.formatCarrierIdentifierType(carrierIdentifierList.ElementAt(0)); } //get equipmentIdentifier EquipmentIdentifier equipmentIdentifier = new EquipmentIdentifier(); equipmentIdentifier.type = "VEHICLE_ID"; equipmentIdentifier.value = r.truckId; // integrate all the infomations jsonProLeg.carrierIdentifier = carrierIdentifier; jsonProLeg.shipmentIdentifiers.Add(shipmentIdentifier); jsonProLeg.shipmentStops.Add(depart); jsonProLeg.shipmentStops.Add(destination); jsonProLeg.equipmentIdentifiers.Add(equipmentIdentifier); var json = JsonConvert.SerializeObject(jsonProLeg); RestClient restClient = new RestClient("https://test-v2.p-44.com/api/v4/tl/shipments"); // RestClient restClient = new RestClient("https://cloud.p-44.com/api/v4/tl/shipments"); RestRequest restRequest = new RestRequest(Method.POST); string base64Auth = HelperFunction.HelperFunction.Base64Encode(ConfigStrings.auth); //string base64Auth = HelperFunction.HelperFunction.Base64Encode("project44!"); restRequest.AddHeader("Authorization:Basic", base64Auth); // restRequest.AddHeader("Password", HelperFunction.HelperFunction.Base64Encode("project44!")); restRequest.AddParameter("application/json", json, ParameterType.RequestBody); IRestResponse restResponse = restClient.Execute(restRequest); // Console.WriteLine(t.Result); // Console.ReadLine(); // end point..... var test = "test"; } else { dataReaderKraft.Close(); } } } } } } catch (InvalidOperationException e) { Console.WriteLine("------------can not connect to database-----------------."); Console.WriteLine(e.Message); Console.ReadKey(); } }