public Grade GetGradeByName(ClipperData clipperData, int?idLoadPort)
{
if (string.IsNullOrWhiteSpace(clipperData.Grade) && string.IsNullOrWhiteSpace(clipperData.Charter_Grade))
{
return(null);
}
string stemGrade = !string.IsNullOrWhiteSpace(clipperData.Grade)
? clipperData.Grade
: clipperData.Charter_Grade;
var mappedGrade = string.Empty;
mappedGrade = GetTargoValue("Grade", stemGrade).ToLower();
if (mappedGrade?.ToLower() == "exception")
{
mappedGrade = ResolveGrade(stemGrade, clipperData.LoadPort);
}
return(GradesList.Any(x => x.Name.ToLower() == mappedGrade.ToLower())
? GradesList.FirstOrDefault(x => x.Name.ToLower() == mappedGrade.ToLower())
: !string.IsNullOrWhiteSpace(mappedGrade)
? AddGrade(mappedGrade.ToUpper())
: null);
}
public int?GetUnitOfMeasureId(ClipperData clipperData)
{
switch (clipperData.Type)
{
case "measuresGlobalCrudeEntity":
return(UnitOfMeasuresList.FirstOrDefault(u => u.Name.ToLower().Contains("bbl"))?.Id);
default:
return(UnitOfMeasuresList.FirstOrDefault(u => u.Name.ToLower().Contains("bbl"))?.Id);
}
}
public int?GetCounterpartId(ClipperData clipperData)
{
if (string.IsNullOrWhiteSpace(clipperData.LoadOwner) || clipperData.LoadOwner == "UNKNOWN")
{
return(null);
}
var name = clipperData.LoadOwner;
var result = CounterpartsList.Any(x => x.Name.ToLower() == name.ToLower())
? CounterpartsList.FirstOrDefault(x => x.Name.ToLower() == name.ToLower())?.Id
: AddCounterpart(name);
if (result == 0)
{
return(null);
}
return(result);
}
public int?GetLocationId(ClipperData clipperData, bool isLoad)
{
var point = isLoad ? GetTargoValue("Location", clipperData.LoadPoint)
: GetTargoValue("Location", clipperData.OffTakePoint);
var port = isLoad ? GetTargoValue("Location", clipperData.LoadPort)
: GetTargoValue("Location", clipperData.OffTakePort);
if (string.IsNullOrWhiteSpace(point) && string.IsNullOrWhiteSpace(port))
{
return(null);
}
int?terminalId;
int?portId;
if (port?.ToLower() == "exception")
{
var area = isLoad
? GetTargoValue("Location", clipperData.LoadArea)
: GetTargoValue("Location", clipperData.OffTakeArea);
port = ResolveLocation(point, port, area).ToLower();
terminalId = !string.IsNullOrWhiteSpace(point) ? GetTargoLocationId(point, 2)?.Id : null;
var targoPort = GetTargoLocationId(port, 1);
portId = null;
if (targoPort?.Id == null)
{
if (!string.IsNullOrWhiteSpace(port))
{
portId = AddPort(port, 1, _portUnallocated);
}
}
}
else
{
terminalId = isLoad ? clipperData.LoadPointId : clipperData.OfftakePointId;
var idParent = terminalId.HasValue ? GetPortParent(terminalId.Value) : null;
if (idParent != null)
{
portId = idParent;
}
else
{
portId = GetTargoLocationId(port, 1)?.Id;
if (portId == null)
{
if (!string.IsNullOrWhiteSpace(port))
{
portId = AddPort(port, 1, _portUnallocated);
}
}
}
}
if (terminalId == null)
{
if (!string.IsNullOrWhiteSpace(point))
{
terminalId = AddPort(point, 2, portId);
}
}
return(terminalId ?? portId);
}
public async Task <ClipperCleanerData> CleanStem(ClipperData clipperData)
{
var theloadDate = TryGetDate(clipperData.LoadDate);
var idLoadLocation = GetLocationId(clipperData, true);
var loadPort = idLoadLocation != null?GetPortParent((int)idLoadLocation) : null;
var grade = GetGradeByName(clipperData, loadPort);
var idDischargeLocation = GetLocationId(clipperData, false) ??
GetLocationIdByRegion(
!string.IsNullOrWhiteSpace(clipperData.OffTakeArea)
? clipperData.OffTakeArea
: !string.IsNullOrWhiteSpace(clipperData.OffTakeRegion)
? clipperData.OffTakeRegion
: null,
clipperData.OffTakeRegion);
var loadDate = theloadDate;
var dischargeDate = TryGetDate(clipperData.OffTakeDate);
var dischargePort = idDischargeLocation != null?GetPortParent((int)idDischargeLocation) : null;
var quantity = clipperData.BblsNominal;
var idUom = GetUnitOfMeasureId(clipperData);
var idEquity = GetCounterpartId(clipperData);
var vessel = GetVesselFromImoString(clipperData.Imo.ToString());
var idCharterer = GetChartererIdFromName(clipperData.Consignee);
var idSource = _sourceClipper;
var route = GetRoute(clipperData.Route);
var speed = clipperData.Speed;
var draught = clipperData.Draught;
var fixDate = TryGetDate(clipperData.Fix_Date);
var laycan = TryGetDate(clipperData.Laycan);
var loadDay = theloadDate?.Day ?? laycan?.Day ?? 0;
var loadMonth = theloadDate?.Month ?? laycan?.Month ?? 0;
var loadYear = theloadDate?.Year ?? laycan?.Year ?? 0;
var cargoId = $"{(grade.Name.Length > 2 ? grade.Name.Substring(0, 3) : grade.Name.Substring(0, 2))}_{loadYear}{loadMonth % 100:00}{loadDay % 100:00}_CLPR";
var chartererInfo = !string.IsNullOrEmpty(clipperData.Fix_Date) && theloadDate == null;
var idClipperCharterer = !string.IsNullOrWhiteSpace(clipperData.Charterer)
? GetChartererIdFromName(clipperData.Charterer)
: null;
//var thisVoyageTime = dischargeDate?.Subtract(theloadDate.Value).Days ?? -1;
//if (thisVoyageTime != -1)
//{
// if((loadPort != null || idLoadLocation != null) && (dischargePort != null || idDischargeLocation != null))
// AddVoyageTime(loadPort ?? (idLoadLocation ?? -1), dischargePort ?? (idDischargeLocation ?? -1), route, vessel.ShipClass, loadMonth,
// thisVoyageTime);
//}
var clipperCleanerData = new ClipperCleanerData();
clipperCleanerData.DateNum = clipperData.DateNum;
clipperCleanerData.RowNum = clipperData.RowNum;
clipperCleanerData.StatNum = clipperData.StatNum;
clipperCleanerData.CargoId = cargoId;
clipperCleanerData.ChartererInfo = chartererInfo;
clipperCleanerData.ClipperDataRowVersion = clipperData.ClipperDataRowVersion;
clipperCleanerData.CreationDate = DateTime.Now;
clipperCleanerData.CreationName = "Clipper";
clipperCleanerData.DischargeDate = dischargeDate;
clipperCleanerData.Draught = draught;
clipperCleanerData.EndLaycan = laycan ?? loadDate;
clipperCleanerData.FixDate = fixDate;
clipperCleanerData.IdCharterer = idCharterer;
clipperCleanerData.IdClipperCharterer = idClipperCharterer;
clipperCleanerData.IdDestination = idDischargeLocation;
clipperCleanerData.IdDisport = dischargePort;
clipperCleanerData.IdEquity = idEquity;
clipperCleanerData.IdGrade = grade.Id;
clipperCleanerData.IdLoadLocation = idLoadLocation;
clipperCleanerData.IdLoadPort = loadPort;
clipperCleanerData.IdShippingRoute = route;
clipperCleanerData.IdShip = vessel?.Id;
clipperCleanerData.IdUnitOfMeasure = idUom;
clipperCleanerData.LoadDate = loadDate;
clipperCleanerData.Month = loadMonth;
clipperCleanerData.Quantity = quantity;
clipperCleanerData.Source = idSource;
clipperCleanerData.Speed = speed;
clipperCleanerData.StartLaycan = laycan ?? loadDate;
clipperCleanerData.Year = loadYear;
if (++_processedCount % 1000 == 0)
{
Debug.WriteLine($"Processed {_processedCount} rows");
}
return(clipperCleanerData);
}
