private List <CrewImmigration> ProcessCrewImmigration(DataSet ds) { List <CrewImmigration> immigration = new List <CrewImmigration>(); try { string user_id = "", jde_id = ""; DataRow dr; if (ds.Tables[7].Rows.Count > 0) { dr = ds.Tables[7].Rows[0]; user_id = dr["user_id"].ToString(); jde_id = dr["jde_id"].ToString(); } immigration = (from a in ds.Tables[0].AsEnumerable() select new CrewImmigration { CrewVericationID = GlobalCode.Field2Long(a["colCrewVericationIDBigint"]), SeaparerID = GlobalCode.Field2Long(a["IDNumber"]), FirstName = GlobalCode.Field2String(a["FirstName"]), LastName = GlobalCode.Field2String(a["LastName"]), LOEControlNumber = GlobalCode.Field2String(a["LOEControlNumber"]), Nationality = GlobalCode.Field2String(a["NationalityName"]), ContactNo = GlobalCode.Field2String(a["ContactNo"]), EmailAdd = GlobalCode.Field2String(a["EmailAdd"]), PassportNo = GlobalCode.Field2String(a["PassportNo"]), PassportExpiredate = GlobalCode.Field2DateTimeNull(a["ExpirationDate"]) == null ? null : GlobalCode.Field2DateTime(a["ExpirationDate"]).ToString("MM/dd/yyyy"), PassportIssuedate = GlobalCode.Field2String(a["IssueDate"]), Vessel = GlobalCode.Field2String(a["Ship"]), Brand = GlobalCode.Field2String(a["BrandName"]), SignOnDate = GlobalCode.Field2DateTime(a["SignOnDateAdj"]), Seaport = GlobalCode.Field2String(a["PortName"]), Rank = GlobalCode.Field2String(a["PositionName"]), NewHire = GlobalCode.Field2Bool(a["NewHire"]), Joindate = GlobalCode.Field2DateTime(a["SignOnDateAdj"]), JoinPort = GlobalCode.Field2String(a["SeaportCode"]), JoinCity = GlobalCode.Field2String(a["AirportCode"]), DateHired = GlobalCode.Field2DateTimeNull(a["DateHired"]), Reason = GlobalCode.Field2Int(a["colReasonInt"]), IsFraudulentDoc = GlobalCode.Field2Bool(a["colIsFraudulentDocBit"]), IsPriorImmigIssues = GlobalCode.Field2Bool(a["colIsPriorImmigIssuesBit"]), IsPriorConDep = GlobalCode.Field2Bool(a["colIsPriorConDepBit"]), IsOther = GlobalCode.Field2Bool(a["colIsOtherBit"]), OtherDetail = GlobalCode.Field2String(a["colOtherDetailVarchar"]), IsApproved = GlobalCode.Field2Bool(a["colIsApprovedBit"]), DateOfBirth = GlobalCode.Field2DateTimeNull(a["DateOfBirth"]), UserName = GlobalCode.Field2String(a["UserName"]), ProcessDate = GlobalCode.Field2DateTimeNull(a["ProcessDate"]), ImmigrationAirTransaction = (from n in ds.Tables[1].AsEnumerable() select new ImmigrationAirTransaction { SeqNo = GlobalCode.Field2Int(n["colSeqNoInt"]), AirLine = GlobalCode.Field2String(n["AirlineName"]), DepartureDateTime = GlobalCode.Field2DateTime(n["DepartureDateTime"]), ArrivalDateTime = GlobalCode.Field2DateTime(n["ArrivalDateTime"]), DepartureAirportLocationCode = GlobalCode.Field2String(n["DepartureAirportCode"]), ArrivalAirportLocationCode = GlobalCode.Field2String(n["ArrivalAirportCode"]), }).ToList(), ImmigrationHotelBooking = (from i in ds.Tables[2].AsEnumerable() select new ImmigrationHotelBooking { BranchName = GlobalCode.Field2String(i["BranchName"]), TimeSpanStartDate = GlobalCode.Field2DateTime(i["CheckInDate"]), TimeSpanStartTime = GlobalCode.Field2DateTime(i["CheckInTime"]), TimeSpanEndDate = GlobalCode.Field2DateTime(i["CheckOutDate"]), TimeSpanEndTime = GlobalCode.Field2DateTime(i["CheckOutTime"]), TimeSpanDurationInt = GlobalCode.Field2Int(i["TimeSpanDuration"]), RoomType = GlobalCode.Field2String(i["RoomType"]), ForeColor = GlobalCode.Field2String(i["coldForeColorVarchar"]), ColorCode = GlobalCode.Field2String(i["colColorCodevarchar"]), }).ToList(), ImmigrationTransportion = (from e in ds.Tables[3].AsEnumerable() select new ImmigrationTransportion { VehicleVendorName = GlobalCode.Field2String(e["Transportation"]), RouteFrom = GlobalCode.Field2String(e["RouteFrom"]), RouteTo = GlobalCode.Field2String(e["RouteTo"]), PickUpDate = GlobalCode.Field2DateTime(e["colPickUpDate"]), PickUpTime = GlobalCode.Field2DateTime(e["colPickUpTime"]), ForeColor = GlobalCode.Field2String(e["coldForeColorVarchar"]), ColorCode = GlobalCode.Field2String(e["colColorCodevarchar"]), }).ToList(), ImmigrationEmploymentHistory = (from b in ds.Tables[4].AsEnumerable() select new ImmigrationEmploymentHistory { CrewVericationID = GlobalCode.Field2Long(b["colCrewVericationIDBigint"]), SeaparerID = GlobalCode.Field2Long(b["IDNumber"]), FirstName = GlobalCode.Field2String(b["FirstName"]), LastName = GlobalCode.Field2String(b["LastName"]), LOEControlNumber = GlobalCode.Field2String(b["LOEControlNumber"]), Nationality = GlobalCode.Field2String(b["NationalityName"]), ContactNo = GlobalCode.Field2String(b["ContactNo"]), EmailAdd = GlobalCode.Field2String(b["EmailAdd"]), PassportNo = GlobalCode.Field2String(b["PassportNo"]), PassportExpiredate = GlobalCode.Field2DateTime(b["ExpirationDate"]).ToString("MM/dd/yyyy"), PassportIssuedate = GlobalCode.Field2String(b["IssueDate"]), Vessel = GlobalCode.Field2String(b["Ship"]), Brand = GlobalCode.Field2String(b["BrandName"]), SignOnDate = GlobalCode.Field2DateTime(b["SignOnDate"]), Seaport = GlobalCode.Field2String(b["PortName"]), NewHire = GlobalCode.Field2Bool(b["NewHire"]), Joindate = GlobalCode.Field2DateTime(b["SignOnDate"]), JoinPort = GlobalCode.Field2String(b["SeaportCode"]), JoinCity = GlobalCode.Field2String(b["AirportCode"]), DateHired = GlobalCode.Field2DateTime(b["DateHired"]), Reason = GlobalCode.Field2Int(b["colReasonInt"]), IsFraudulentDoc = GlobalCode.Field2Bool(b["colIsFraudulentDocBit"]), IsPriorImmigIssues = GlobalCode.Field2Bool(b["colIsPriorImmigIssuesBit"]), IsPriorConDep = GlobalCode.Field2Bool(b["colIsPriorConDepBit"]), IsOther = GlobalCode.Field2Bool(b["colIsOtherBit"]), OtherDetail = GlobalCode.Field2String(b["colOtherDetailVarchar"]), IsApproved = GlobalCode.Field2Bool(b["colIsApprovedBit"]), DateOfBirth = GlobalCode.Field2DateTime(b["DateOfBirth"]), ShipID = GlobalCode.Field2Int(b["ShipID"]), Ship = GlobalCode.Field2String(b["Ship"]), RankID = GlobalCode.Field2Int(b["RankID"]), Rank = GlobalCode.Field2String(b["PositionName"]), ColorCode = GlobalCode.Field2String(b["ColorCode"]), ForeColor = GlobalCode.Field2String(b["ForeColor"]), }).ToList(), SeafarerImage = (from k in ds.Tables[5].AsEnumerable() where GlobalCode.Field2Long(k["SeafarerID"]) == GlobalCode.Field2Long(a["IDNumber"]) select new SeafarerImage { SeaparerID = GlobalCode.Field2Int(k["SeafarerID"]), Image = GlobalCode.Field2PictureByte(k["PictureImage"]), ImageType = GlobalCode.Field2String(k["PictureType"]), }).ToList(), Parent = (from p in ds.Tables[6].AsEnumerable() select new EmployeeParent { EmployeeID = GlobalCode.Field2Long(p["EmployeeID"]), FatherName = GlobalCode.Field2String(p["FatherName"]), MotherName = GlobalCode.Field2String(p["MotherName"]), }).ToList(), CtracDetail = new CtracDetail { user_id = user_id, jde_id = jde_id } }).ToList(); } catch (Exception ex) { throw ex; } return(immigration); }