public List <FrayteUnAssignedJob> GetUnAssignedJobs(TrackHSCodeJob obj)
{
List <FrayteUnAssignedJob> list = new List <FrayteUnAssignedJob>();
try
{
int SkipRows = 0;
SkipRows = (obj.CurrentPage - 1) * obj.TakeRows;
var OperationZone = UtilityRepository.GetOperationZone();
// To Do : Get ETD Date And Time in the list
var jobs = dbContext.TrackUnAssignedJob(obj.FromDate, obj.ToDate, SkipRows, obj.TakeRows, OperationZone.OperationZoneId).ToList();
var d = jobs.Where(p => p.EstimatedDateOfDelivery.HasValue).ToList();
if (jobs != null && jobs.Count > 0)
{
foreach (var data in jobs)
{
FrayteUnAssignedJob job = new FrayteUnAssignedJob();
job.CourierCompany = data.LogisticCompany;
job.CourierCompanyDisplay = data.LogisticCompanyDisplay;
job.Customer = data.ContactName;
job.DisplayStatus = data.StatusName;
job.ShippedFromCompany = data.FromCompany;
job.ShippedToCompany = data.ToCompany;
job.ShippingDate = data.CreatedOn;
job.TotalRows = data.TotalRows.HasValue ? data.TotalRows.Value : 0;
job.TrackingNo = data.TrackingNo;
job.EstimatedDateOfDeparture = data.EstimatedDateOfDelivery;
job.EstimatedTimeOfDeparture = UtilityRepository.GetTimeZoneTime(data.EstimatedTimeofDelivery);
job.EstimatedDateOfArrival = data.EstimatedDateofArrival;
job.EstimatedTimeOfArrival = UtilityRepository.GetTimeZoneTime(data.EstimatedTimeofArrival);
job.ShipmentId = data.DirectShipmentId;
job.ShipmentDescription = data.ContentDescription;
job.Reference1 = data.Reference1;
job.FrayteNumber = data.FrayteNumber;
job.FromCountry = data.FromCountry;
job.ToCountry = data.ToCountry;
list.Add(job);
}
}
// To Do : OrderBy on ETD date and then ETD Time
var collection = list.OrderByDescending(p => p.EstimatedDateOfDeparture).ThenByDescending(p => UtilityRepository.GetTimeFromString(p.EstimatedTimeOfDeparture)).ToList();
return(collection);
}
catch (Exception ex)
{
Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
return(list);
}
}
public List <FrayteCourier> GetUKCourier()
{
List <FrayteCourier> lstCourier = new List <FrayteCourier>();
var result = dbContext.Couriers.ToList().Skip(3);
foreach (Courier courier in result)
{
FrayteCourier frayteCourier = new FrayteCourier();
frayteCourier.CourierId = courier.CourierId;
frayteCourier.Name = courier.CourierName;
frayteCourier.DisplayName = courier.DisplayName;
frayteCourier.Website = courier.Website;
frayteCourier.CourierType = courier.ShipmentType;
frayteCourier.LatestBookingTime = UtilityRepository.GetTimeZoneTime(courier.LatestBookingTime);
lstCourier.Add(frayteCourier);
}
return(lstCourier);
}
public List <FrayteSystemAlert> GetSystemAlerts(TrackSystemAlert trackSystemAlert)
{
int SkipRows = 0;
SkipRows = (trackSystemAlert.CurrentPage - 1) * trackSystemAlert.TakeRows;
var result = dbContext.spGet_GetSystemAlerts(trackSystemAlert.OperationZoneId, trackSystemAlert.FromDate, trackSystemAlert.ToDate,
SkipRows, trackSystemAlert.TakeRows).ToList();
List <FrayteSystemAlert> list = new List <FrayteSystemAlert>();
if (result != null && result.Count > 0)
{
FrayteSystemAlert SystemAlertResult;
foreach (var data in result)
{
SystemAlertResult = new FrayteSystemAlert();
SystemAlertResult.SystemAlertId = data.SystemAlertId;
SystemAlertResult.OperationZoneId = data.OperationZoneId;
SystemAlertResult.Heading = data.Heading;
SystemAlertResult.Description = data.Description;
SystemAlertResult.FromDate = data.FromDate;
SystemAlertResult.FromTime = UtilityRepository.GetTimeZoneTime(data.FromTime);
SystemAlertResult.ToDate = data.ToDate;
SystemAlertResult.ToTime = UtilityRepository.GetTimeZoneTime(data.ToTime);
SystemAlertResult.IsActive = data.IsActive;
SystemAlertResult.TotalRows = data.TotalRows.Value;
SystemAlertResult.TimeZoneDetail = new TimeZoneModal();
SystemAlertResult.TimeZoneDetail.Name = data.TimeZoneName;
if (data.TimezoneId.HasValue)
{
SystemAlertResult.TimeZoneDetail.TimezoneId = data.TimezoneId.Value;
}
SystemAlertResult.TimeZoneDetail.Offset = data.TimeZoneOffset;
SystemAlertResult.TimeZoneDetail.OffsetShort = data.TimeZoneOffsetShort;
var TimeZoneInformation = TimeZoneInfo.FindSystemTimeZoneById(data.TimeZoneName);
SystemAlertResult.FromTime = UtilityRepository.UtcDateToOtherTimezone(data.FromDate, data.FromTime, TimeZoneInformation).Item2;
SystemAlertResult.FromDate = UtilityRepository.UtcDateToOtherTimezone(data.FromDate, data.FromTime, TimeZoneInformation).Item1;
SystemAlertResult.ToTime = UtilityRepository.UtcDateToOtherTimezone(data.ToDate, data.ToTime, TimeZoneInformation).Item2;
SystemAlertResult.ToDate = UtilityRepository.UtcDateToOtherTimezone(data.ToDate, data.ToTime, TimeZoneInformation).Item1;
list.Add(SystemAlertResult);
}
}
return(list);
}
private void GetAssociateUsersDetail(int agentId, FrayteAgent agentDetail)
{
List <FrayteAgentAssociatedUser> userAssociated = new List <FrayteAgentAssociatedUser>();
var users = dbContext.AgentAssociatedUsers.Where(p => p.AgentId == agentId);
foreach (var user in users)
{
FrayteAgentAssociatedUser newUser = new FrayteAgentAssociatedUser();
newUser.AgentAssociatedUserId = user.AgentAssociatedUserId;
newUser.AgentId = user.AgentId;
newUser.Name = user.Name;
newUser.UserType = user.UserType;
newUser.Email = user.Email;
newUser.TelephoneNo = user.TelephoneNo;
newUser.WorkingStartTime = UtilityRepository.GetTimeZoneTime(user.WorkingStartTime, agentDetail.Timezone.TimezoneId);
newUser.WorkingEndTime = UtilityRepository.GetTimeZoneTime(user.WorkingEndTime, agentDetail.Timezone.TimezoneId);
newUser.WorkingWeekDays = user.WorkingWeekDays;
userAssociated.Add(newUser);
}
agentDetail.AssociatedUsers = userAssociated;
}
public FrayteWarehouse GetWarehouseDetail(int?warehouseId)
{
FrayteWarehouse warehouseDetail = new FrayteWarehouse();
WorkingWeekDay workingDays = new WorkingWeekDay();
var warehouse = dbContext.Warehouses.Where(p => p.WarehouseId == warehouseId).FirstOrDefault();
if (warehouse != null)
{
warehouseDetail.WarehouseId = warehouse.WarehouseId;
warehouseDetail.Address = warehouse.Address;
warehouseDetail.Address2 = warehouse.Address2;
warehouseDetail.Address3 = warehouse.Address3;
warehouseDetail.City = warehouse.City;
warehouseDetail.State = warehouse.State;
warehouseDetail.Zip = warehouse.Zip;
warehouseDetail.WorkingWeekDay = new WorkingWeekDay();
if (warehouse.WorkingWeekDayId != null)
{
warehouseDetail.WorkingWeekDay.WorkingWeekDayId = warehouse.WorkingWeekDayId.Value;
}
if (warehouseDetail.WorkingWeekDay.WorkingWeekDayId > 0)
{
workingDays = dbContext.WorkingWeekDays.Find(warehouseDetail.WorkingWeekDay.WorkingWeekDayId);
}
if (workingDays != null)
{
warehouseDetail.WorkingWeekDay = workingDays;
}
warehouseDetail.Email = warehouse.Email;
warehouseDetail.TelephoneNo = warehouse.TelephoneNo;
warehouseDetail.MobileNo = warehouse.MobileNo;
warehouseDetail.Fax = warehouse.Fax;
warehouseDetail.WorkingStartTime = UtilityRepository.GetTimeZoneTime(warehouse.WorkingStartTime, warehouse.TimeZoneId);
warehouseDetail.WorkingEndTime = UtilityRepository.GetTimeZoneTime(warehouse.WorkingEndTime, warehouse.TimeZoneId);
warehouseDetail.Address3 = warehouse.Address3;
warehouseDetail.Address3 = warehouse.Address3;
warehouseDetail.Address3 = warehouse.Address3;
warehouseDetail.LocationMapImage = warehouse.LocationMapImage;
warehouseDetail.LocationName = warehouse.LocationName;
warehouseDetail.MapDetail = new GrayteGoogleMap();
warehouseDetail.MapDetail.latitude = warehouse.LocationLatitude.HasValue ? warehouse.LocationLatitude.Value : 0;
warehouseDetail.MapDetail.longitude = warehouse.LocationLongitude.HasValue ? warehouse.LocationLongitude.Value : 0;
warehouseDetail.Zoom = warehouse.LocationZoom.HasValue ? warehouse.LocationZoom.Value : 0;
warehouseDetail.MarkerDetail = new GrayteGoogleMap();
warehouseDetail.MarkerDetail.latitude = warehouse.MarkerLatitude.HasValue ? warehouse.MarkerLatitude.Value : 0;
warehouseDetail.MarkerDetail.longitude = warehouse.MarkerLongitude.HasValue ? warehouse.MarkerLongitude.Value : 0;
//Get Country Details
var country = dbContext.Countries.Where(p => p.CountryId == warehouse.CountryId).FirstOrDefault();
if (country != null)
{
warehouseDetail.Country = new FrayteCountryCode();
warehouseDetail.Country.CountryId = country.CountryId;
warehouseDetail.Country.Code = country.CountryCode;
warehouseDetail.Country.Code2 = country.CountryCode2;
warehouseDetail.Country.Name = country.CountryName;
}
else
{
warehouseDetail.Country = new FrayteCountryCode();
}
//Get TimeZone details
var timezone = dbContext.Timezones.Where(p => p.TimezoneId == warehouse.TimeZoneId).FirstOrDefault();
if (timezone != null)
{
warehouseDetail.Timezone = new TimeZoneModal();
warehouseDetail.Timezone.TimezoneId = timezone.TimezoneId;
warehouseDetail.Timezone.Name = timezone.Name;
warehouseDetail.Timezone.Offset = timezone.Offset;
warehouseDetail.Timezone.OffsetShort = timezone.OffsetShort;
}
else
{
warehouseDetail.Timezone = new TimeZoneModal();
}
//Get warehouse manager detail
var warehouseManager = dbContext.Users.Where(p => p.UserId == warehouse.ManagerId).FirstOrDefault();
if (warehouseManager != null)
{
warehouseDetail.Manager = new FrayteCustomerAssociatedUser();
warehouseDetail.Manager.UserId = warehouseManager.UserId;
warehouseDetail.Manager.AssociateType = FrayteAssociateType.Manager;
warehouseDetail.Manager.ContactName = warehouseManager.ContactName;
warehouseDetail.Manager.Email = warehouseManager.Email;
warehouseDetail.Manager.TelephoneNo = warehouseManager.TelephoneNo;
warehouseDetail.Manager.WorkingHours = UtilityRepository.GetWorkingHours(warehouseManager.WorkingStartTime, warehouseManager.WorkingEndTime);
}
else
{
warehouseDetail.Manager = new FrayteCustomerAssociatedUser();
}
}
return(warehouseDetail);
}
public List <FrayteWarehouse> GetAllWarehouseList()
{
List <FrayteWarehouse> lstFrayteWarehouse = new List <FrayteWarehouse>();
var lstWarehouse = (from w in dbContext.Warehouses
join c in dbContext.Countries on w.CountryId equals c.CountryId
join tz in dbContext.Timezones on w.TimeZoneId equals tz.TimezoneId
join u in dbContext.Users on w.ManagerId equals u.UserId into tempManager
from us in tempManager.DefaultIfEmpty()
select new
{
w.WarehouseId,
w.Address,
w.Address2,
w.Address3,
w.City,
w.State,
w.Zip,
w.CountryId,
c.CountryCode,
c.CountryName,
w.LocationName,
w.LocationLatitude,
w.LocationMapImage,
w.ManagerId,
ManagerName = us.ContactName,
ManagerEmail = us.Email,
ManagerTelephone = us.TelephoneNo,
ManagerStartTime = us.WorkingStartTime,
ManagerEndTime = us.WorkingEndTime,
w.Email,
w.TelephoneNo,
w.MobileNo,
w.Fax,
w.WorkingStartTime,
w.WorkingEndTime,
w.TimeZoneId,
TimeZoneName = tz.Name,
TimeZoneOffset = tz.Offset,
TimezoneOffSetShort = tz.OffsetShort
}).ToList();
if (lstWarehouse != null)
{
foreach (var w in lstWarehouse)
{
FrayteWarehouse frayteWarehouse = new FrayteWarehouse();
frayteWarehouse.WarehouseId = w.WarehouseId;
frayteWarehouse.Address = w.Address;
frayteWarehouse.Address2 = w.Address2;
frayteWarehouse.Address3 = w.Address3;
frayteWarehouse.City = w.City;
frayteWarehouse.State = w.State;
frayteWarehouse.Zip = w.Zip;
frayteWarehouse.Country = new FrayteCountryCode();
frayteWarehouse.Country.CountryId = w.CountryId;
frayteWarehouse.Country.Code = w.CountryCode;
frayteWarehouse.Country.Name = w.CountryName;
frayteWarehouse.LocationName = w.LocationName;
frayteWarehouse.LocationMapImage = w.LocationMapImage;
frayteWarehouse.Manager = new FrayteCustomerAssociatedUser();
frayteWarehouse.Manager.UserId = w.ManagerId.HasValue ? w.ManagerId.Value : 0;
if (frayteWarehouse.Manager.UserId > 0)
{
frayteWarehouse.Manager.ContactName = w.ManagerName;
frayteWarehouse.Manager.Email = w.ManagerEmail;
frayteWarehouse.Manager.TelephoneNo = w.ManagerTelephone;
frayteWarehouse.Manager.WorkingHours = UtilityRepository.GetWorkingHours(w.ManagerStartTime, w.ManagerEndTime);
}
frayteWarehouse.Email = w.Email;
frayteWarehouse.TelephoneNo = w.TelephoneNo;
frayteWarehouse.MobileNo = w.MobileNo;
frayteWarehouse.Fax = w.Fax;
frayteWarehouse.WorkingStartTime = UtilityRepository.GetTimeZoneTime(w.WorkingStartTime, w.TimeZoneName);
frayteWarehouse.WorkingEndTime = UtilityRepository.GetTimeZoneTime(w.WorkingEndTime, w.TimeZoneName);
frayteWarehouse.Timezone = new TimeZoneModal();
frayteWarehouse.Timezone.TimezoneId = w.TimeZoneId;
frayteWarehouse.Timezone.Name = w.TimeZoneName;
frayteWarehouse.Timezone.Offset = w.TimeZoneOffset;
frayteWarehouse.Timezone.OffsetShort = w.TimezoneOffSetShort;
lstFrayteWarehouse.Add(frayteWarehouse);
}
}
return(lstFrayteWarehouse);
}
public int HSCodeOutputPerPerHour(int userId)
{
List <HSCodeAvgOutput> list = new List <HSCodeAvgOutput>();
try
{
// get all operators of the manager
var operators = (from r in dbContext.Users
join u in dbContext.UserAdditionals on r.UserId equals u.UserId
join t in dbContext.Timezones on r.TimezoneId equals t.TimezoneId
where u.ManagerUserId == userId && r.IsActive == true
select new
{
OperatorId = r.UserId,
Name = r.ContactName,
WST = r.WorkingStartTime,
WET = r.WorkingEndTime,
TimeZone = new TimeZoneModal
{
Name = t.Name,
TimezoneId = t.TimezoneId,
Offset = t.Offset,
OffsetShort = t.OffsetShort
}
}
).ToList();
// Get avg jobs per hour or each operator
foreach (var data in operators)
{
HSCodeAvgOutput jobOutput = new HSCodeAvgOutput();
var mycount = (from cnt in dbContext.eCommerceShipmentDetails
join ec in dbContext.eCommerceShipments on cnt.eCommerceShipmentId equals ec.eCommerceShipmentId
where !string.IsNullOrEmpty(cnt.HSCode) && ec.AssignedTo == data.OperatorId && ec.MappedOn != null
group ec by ec.eCommerceShipmentId into g
select new
{
name = g.Key,
MappedOn = System.Data.Entity.DbFunctions.TruncateTime(g.FirstOrDefault().MappedOn.Value),
count = g.Count()
}
).ToList();
var count = mycount.GroupBy(x => x.MappedOn.Value.Date)
.Select(AS => new
{
name = AS.Key,
count = AS.Count()
}).ToList().Count;
var wst = UtilityRepository.GetFormattedTimeString(UtilityRepository.GetTimeZoneTime(data.WST, data.TimeZone.Name));
var wet = UtilityRepository.GetFormattedTimeString(UtilityRepository.GetTimeZoneTime(data.WET, data.TimeZone.Name));
DateTime dtFrom = DateTime.Parse(wst);
DateTime dtTo = DateTime.Parse(wet);
var timeDiffHrs = dtTo.Subtract(dtFrom).Hours;
var timeDiffMins = dtTo.Subtract(dtFrom).Minutes;
timeDiffHrs = timeDiffMins / 60 + timeDiffHrs;
var avgjobs = mycount.Count / timeDiffHrs * count;
jobOutput.AvgJobs = avgjobs;
jobOutput.Name = data.Name;
jobOutput.OperatorId = data.OperatorId;
list.Add(jobOutput);
}
int sum = 0;
foreach (var data in list)
{
sum += data.AvgJobs;
}
if (list.Count > 0)
{
sum = sum / list.Count;
}
else
{
sum = 0;
}
return(sum);
}
catch (Exception ex)
{
return(0);
}
}
public List <FrayteMappedJobs> GetAssignedJobs(TrackAssignedJob obj)
{
List <FrayteMappedJobs> list = new List <FrayteMappedJobs>();
try
{
var OperationZone = UtilityRepository.GetOperationZone();
int SkipRows = 0;
SkipRows = (obj.CurrentPage - 1) * obj.TakeRows;
var mappedJobs = dbContext.spGet_TrackAssignedJob(obj.FromDate, obj.ToDate, SkipRows, obj.TakeRows, OperationZone.OperationZoneId, obj.OperatorId, obj.DestinationCountry).ToList();
var idList = mappedJobs.Select(p => p.eCommerceShipmentId).ToList();
var detailList = (from eCD in dbContext.eCommerceShipmentDetails
where idList.Contains(eCD.eCommerceShipmentId) && (obj.AllShipments == true || string.IsNullOrEmpty(eCD.HSCode))
select new eCommercePackage
{
eCommerceShipmentDetailId = eCD.eCommerceShipmentDetailId,
eCommerceShhipmentId = eCD.eCommerceShipmentId,
Value = eCD.DeclaredValue.HasValue ? eCD.DeclaredValue.Value : 0,
CartoonValue = eCD.CartoonValue,
Weight = eCD.Weight,
Length = eCD.Length,
Height = eCD.Height,
Content = eCD.PiecesContent,
Width = eCD.Width,
HSCode = eCD.HSCode,
}).ToList();
list = mappedJobs.Select(group => new FrayteMappedJobs
{
ShipmentId = group.eCommerceShipmentId,
CourierCompany = group.LogisticCompany,
CourierCompanyDisplay = group.LogisticCompanyDisplay,
Customer = group.ContactName,
DisplayStatus = group.StatusName,
FrayteNumber = group.FrayteNumber,
FromCountry = group.FromCountry,
EstimatedDateOfDeparture = group.EstimatedDateOfDelivery,
EstimatedTimeOfDeparture = UtilityRepository.GetTimeZoneTime(group.EstimatedTimeofDelivery),
EstimatedDateOfArrival = group.EstimatedDateofArrival,
EstimatedTimeOfArrival = UtilityRepository.GetTimeZoneTime(group.EstimatedTimeofArrival),
ShippedFromCompany = group.FromCompany,
ToCountry = group.ToCountry,
ShippedToCompany = group.ToCompany,
ShipmentDescription = group.ContentDescription,
ShippingDate = group.CreatedOn,
Status = group.StatusName,
TotalRows = group.TotalRows.HasValue ? group.TotalRows.Value : 0,
Reference1 = group.Reference1,
TrackingNo = group.TrackingNo,
StaffAssigned = group.OperatorName,
StaffCompanyName = group.OperatorCompanyName,
StaffEmail = group.OperatorEmail,
Packages = detailList.Where(p => p.eCommerceShhipmentId == group.eCommerceShipmentId).Select(subGroup => new eCommercePackage
{
eCommerceShipmentDetailId = subGroup.eCommerceShipmentDetailId,
CartoonValue = subGroup.CartoonValue,
Content = subGroup.Content,
Height = subGroup.Height,
HSCode = subGroup.HSCode,
Length = subGroup.Length,
Value = subGroup.Value,
Width = subGroup.Width,
Weight = subGroup.Weight,
IsPrinted = string.IsNullOrEmpty(subGroup.HSCode) ? false : true
}).ToList()
}).OrderByDescending(p => p.EstimatedDateOfDeparture)
.ThenByDescending(p => UtilityRepository.GetTimeFromString(p.EstimatedTimeOfDeparture).HasValue ? UtilityRepository.GetTimeFromString(p.EstimatedTimeOfDeparture).Value : DateTime.UtcNow.TimeOfDay).ToList();
var data = list.Where(p => p.Packages.Count > 0).ToList();
return(data);
}
catch (Exception ex)
{
Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
return(list);
}
}
