public List <FrayteLogisticServiceItem> GetLogisticServiceItems(int Year)
{
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
var list = (from ls in dbContext.LogisticServices
join lsbrch in dbContext.LogisticServiceBaseRateCardHistories on ls.LogisticServiceId equals lsbrch.LogisticServiceId into leftJoin
from tempRate in leftJoin.DefaultIfEmpty()
where ls.OperationZoneId == OperationZone.OperationZoneId &&
ls.IsActive == true &&
tempRate.ReportYear == Year
select new FrayteLogisticServiceItem
{
LogisticServiceId = ls.LogisticServiceId,
LogisticCompany = ls.LogisticCompany,
LogisticCompanyDisplay = ls.LogisticCompanyDisplay,
LogisticType = ls.LogisticType,
LogisticTypeDisplay = ls.LogisticTypeDisplay,
RateType = ls.RateType,
RateTypeDisplay = ls.RateTypeDisplay,
IssueDate = ls.IssuedDate.Value,
ExpiryDate = ls.ExpiryDate.Value
}).ToList();
return(list);
}
public FryatePODResult ShipmentPDF_Report()
{
FrayteOperationZone foz = GetBusinessZoneID();
GeneratePDF_ShipmentReportDetail(foz.OperationZoneId);
string pdfFileName = HttpContext.Current.Server.MapPath("~/UploadFiles/PDFGenerator/") + filename;
FryatePODResult result = new FryatePODResult();
result.PDFPath = pdfFileName;
return(result);
}
public List <FrayteOperationZone> GetOperationZone()
{
List <FrayteOperationZone> _operation = new List <FrayteOperationZone>();
var operation = dbContext.OperationZones.ToList();
FrayteOperationZone fz;
foreach (var rr in operation)
{
fz = new FrayteOperationZone();
fz.OperationZoneId = rr.OperationZoneId;
fz.OperationZoneName = rr.Name;
_operation.Add(fz);
}
return(_operation);
}
public TimeZoneModal GetOperationTimezone()
{
FrayteOperationZone operationZone = UtilityRepository.GetOperationZone();
var timeZone = (from tz in dbContext.Timezones join
c in dbContext.Countries on tz.TimezoneId equals c.TimeZoneId
where c.CountryCode == operationZone.OperationZoneName
select new TimeZoneModal()
{
TimezoneId = tz.TimezoneId,
Name = tz.Name,
Offset = tz.Offset,
OffsetShort = tz.OffsetShort
}).FirstOrDefault();
return(timeZone);
}
public List <FrayteZone> GetZoneList()
{
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
//var list = dbContext.Zones.Where(p => p.OperationZoneId == OperationZone.OperationZoneId).ToList();
var list = (from ls in dbContext.LogisticServices
join lsz in dbContext.LogisticServiceZones on ls.LogisticServiceId equals lsz.LogisticServiceId
where ls.OperationZoneId == OperationZone.OperationZoneId
select new FrayteZone
{
ZoneId = lsz.LogisticServiceZoneId,
OperationZoneId = ls.OperationZoneId,
ZoneName = lsz.ZoneName,
ZoneDisplayName = lsz.ZoneDisplayName,
ZoneColor = lsz.ZoneColor,
LogisticType = ls.LogisticType,
CourierComapny = ls.LogisticCompany,
RateType = ls.RateType,
ModuleType = ls.ModuleType
}).ToList();
return(list);
}
public List <FrayteLogisticServiceItem> GetCustomerLogisticServiceItems(int UserId)
{
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
var list = (from cl in dbContext.CustomerLogistics
join ls in dbContext.LogisticServices on cl.LogisticServiceId equals ls.LogisticServiceId
where
cl.UserId == UserId &&
ls.IsActive == true
select new FrayteLogisticServiceItem
{
LogisticServiceId = ls.LogisticServiceId,
LogisticCompany = ls.LogisticCompany,
LogisticCompanyDisplay = ls.LogisticCompanyDisplay,
LogisticType = ls.LogisticType,
LogisticTypeDisplay = ls.LogisticTypeDisplay,
RateType = ls.RateType,
RateTypeDisplay = ls.RateTypeDisplay,
IssueDate = ls.IssuedDate.HasValue ? ls.IssuedDate.Value : DateTime.UtcNow,
ExpiryDate = ls.ExpiryDate.HasValue ? ls.ExpiryDate.Value : DateTime.UtcNow
}).OrderBy(p => p.LogisticCompany).ToList();
return(list);
}
public FrayteManifestName DownloadRateCardExcelTemplate(string CourierCompany, string LogisticType, string RateType)
{
FrayteManifestName file = new FrayteManifestName();
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
var zonelist = (from lsz in dbContext.LogisticServiceZones
join ls in dbContext.LogisticServices on lsz.LogisticServiceId equals ls.LogisticServiceId
where ls.OperationZoneId == OperationZone.OperationZoneId &&
ls.LogisticCompany == CourierCompany &&
ls.LogisticType == LogisticType &&
ls.RateType == RateType
select new FrayteBaseRateCardZone
{
LogisticZoneId = lsz.LogisticServiceZoneId,
LogisticZoneName = lsz.ZoneDisplayName
}).ToList();
var weightlist = (from lsw in dbContext.LogisticServiceWeights
join lsst in dbContext.LogisticServiceShipmentTypes on lsw.LogisticServiceShipmentTypeId equals lsst.LogisticServiceShipmentTypeId
join ls in dbContext.LogisticServices on lsst.LogisticServiceId equals ls.LogisticServiceId
where ls.OperationZoneId == OperationZone.OperationZoneId &&
ls.LogisticCompany == CourierCompany &&
ls.LogisticType == LogisticType &&
ls.RateType == RateType
select new FrayteBaseRateCardWeight
{
LogisticWeightId = lsw.LogisticServiceWeightId,
LogisticWeight = lsw.WeightToDisplay,
LogisticShipmentType = lsst.LogisticDescription
}).ToList();
Application ExcelApp = new Application();
Workbook ExcelWorkBook = null;
Worksheet ExcelWorkSheet = null;
//ExcelApp.Visible = true;
ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
try
{
int r = 1;
ExcelWorkBook.Worksheets.Add();
ExcelWorkSheet = ExcelWorkBook.Worksheets[1];
for (int i = 0; i < zonelist.Count; i++)
{
ExcelWorkSheet.Cells[r, i + 4] = zonelist[i].LogisticZoneId;
ExcelWorkSheet.Cells[r + 1, i + 4] = zonelist[i].LogisticZoneName;
}
r++;
for (int j = 0; j < weightlist.Count; j++)
{
ExcelWorkSheet.Cells[r + 1, 1] = weightlist[j].LogisticWeightId;
ExcelWorkSheet.Cells[r + 1, 2] = weightlist[j].LogisticShipmentType;
ExcelWorkSheet.Cells[r + 1, 3] = weightlist[j].LogisticWeight;
r++;
}
ExcelWorkBook.SaveAs(HostingEnvironment.MapPath("~/UploadFiles/BaseRateCardUpdate/" + CourierCompany + " " + LogisticType + " " + RateType));
ExcelWorkBook.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelWorkSheet);
Marshal.ReleaseComObject(ExcelWorkBook);
Marshal.ReleaseComObject(ExcelApp);
file.FileName = CourierCompany + " " + LogisticType + " " + RateType + ".xlsx";
file.FilePath = AppSettings.WebApiPath + "UploadFiles/BaseRateCardUpdate/" + CourierCompany + " " + LogisticType + " " + RateType + ".xlsx";
}
catch (Exception ex)
{
}
finally
{
foreach (Process process in Process.GetProcessesByName("Excel"))
{
process.Kill();
}
}
return(file);
}
public List <FrayteCustomerStaff> GetCustomerStaff(FrayteCustomerStaffTrack track)
{
List <FrayteCustomerStaff> staff = new List <FrayteCustomerStaff>();
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
int SkipRows = 0;
SkipRows = (track.CurrentPage - 1) * track.TakeRows;
if (track.RoleId == (int)FrayteUserRole.Admin || track.RoleId == (int)FrayteUserRole.Staff)
{
staff = (from u in dbContext.Users
join uaa in dbContext.UserAdditionals on u.UserId equals uaa.UserId
join ur in dbContext.UserRoles on u.UserId equals ur.UserId
join ua in dbContext.UserAddresses on ur.UserId equals ua.UserId
join c in dbContext.Countries on ua.CountryId equals c.CountryId
where u.IsActive == true &&
u.OperationZoneId == OperationZone.OperationZoneId &&
ur.RoleId == (int)FrayteUserRole.CustomerStaff
select new FrayteCustomerStaff
{
UserId = u.UserId,
ContactName = u.ContactName,
Email = u.Email,
PhoneNo = u.PhoneNumber,
TotalRows = 0,
Country = new FrayteCountryCode()
{
CountryId = c.CountryId,
Name = c.CountryName,
Code = c.CountryCode,
Code2 = c.CountryCode2,
CountryPhoneCode = c.CountryPhoneCode,
}
}).ToList();
int total = staff.Count();
staff = staff.OrderBy(p => p.UserId).Skip(SkipRows).Take(track.TakeRows).ToList();
staff.ForEach(p => p.TotalRows = total);
}
else if (track.RoleId == (int)FrayteUserRole.Customer)
{
staff = (from u in dbContext.Users
join ur in dbContext.UserRoles on u.UserId equals ur.UserId
join ua in dbContext.UserAddresses on ur.UserId equals ua.UserId
join c in dbContext.Countries on ua.CountryId equals c.CountryId
join cs in dbContext.CustomerStaffs on u.UserId equals cs.CustomerStaffId
where u.IsActive == true &&
u.OperationZoneId == OperationZone.OperationZoneId &&
ur.RoleId == (int)FrayteUserRole.CustomerStaff &&
cs.UserId == track.CreatedBy &&
cs.IsActive == true
select new FrayteCustomerStaff
{
UserId = u.UserId,
ContactName = u.ContactName,
Email = u.Email,
PhoneNo = u.PhoneNumber,
TotalRows = 0,
Country = new FrayteCountryCode()
{
CountryId = c.CountryId,
Name = c.CountryName,
Code = c.CountryCode,
Code2 = c.CountryCode2,
CountryPhoneCode = c.CountryPhoneCode,
}
}).ToList();
int total = staff.Count();
staff = staff.OrderBy(p => p.UserId).Skip(SkipRows).Take(track.TakeRows).ToList();
staff.ForEach(p => p.TotalRows = total);
}
return(staff);
}
public FrayteOperationZone GetBusinessOperationZoneId()
{
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
return(OperationZone);
}
public List <TradelaneGetShipmentModel> GetTradelaneShipments(TradelaneTrackDirectBooking track)
{
List <TradelaneGetShipmentModel> ShipmentList = new List <TradelaneGetShipmentModel>();
if (track != null)
{
FrayteOperationZone OperationZone = UtilityRepository.GetOperationZone();
int RoleId = dbContext.UserRoles.Where(p => p.UserId == (track.UserId > 0 ? track.UserId : track.CustomerId)).FirstOrDefault().RoleId;
int TotalRows = 0;
int SkipRows = 0;
SkipRows = (track.CurrentPage - 1) * track.TakeRows;
DateTime?fromdate;
DateTime?todate;
var Airline = "";
if (track.FromDate.HasValue)
{
fromdate = track.FromDate.Value;
}
else
{
fromdate = track.FromDate;
}
if (track.ToDate.HasValue)
{
todate = track.ToDate.Value;
}
else
{
todate = track.ToDate;
}
if (track.MAWB != null && track.MAWB != "")
{
var Result1 = GetMawbStr(track.MAWB);
track.MAWB = Result1.Item1;
Airline = Result1.Item2;
}
var Result = dbContext.spGet_TradelaneTrackAndTraceDetail(track.MAWB, fromdate, todate, track.ShipmentStatusId, track.FrayteNumber, SkipRows, track.TakeRows, track.CustomerId, track.UserId, OperationZone.OperationZoneId, RoleId, track.SpecialSearchId, Airline).ToList();
foreach (var res in Result)
{
var STCode = "";
TradelaneGetShipmentModel Shipment = new TradelaneGetShipmentModel();
Shipment.DepartureAirport = res.DepartureAirport;
Shipment.DestinationAirport = res.DestinationAirport;
Shipment.CustomerId = res.CustomerId;
Shipment.CreatedOn = res.CreatedOnUtc.Date;
Shipment.Customer = res.ContactName;
Shipment.IsAgentMAWBAllocated = res.IsAgentMAWBAllocated != null ? res.IsAgentMAWBAllocated.Value : false;
Shipment.MAWBAgentId = res.MAWBAgentId != null ? res.MAWBAgentId.Value : 0;
Shipment.TradelaneShipmentId = res.TradelaneShipmentId;
STCode = !string.IsNullOrEmpty(res.ShipmentHandlerMethodCode) ? " (" + res.ShipmentHandlerMethodCode + ')' : "";
Shipment.Status = res.StatusName;
Shipment.StatusDisplay = res.DisplayStatusName + STCode;
Shipment.MAWB = res.MAWB;
Shipment.TotalWeight = res.TotalWeight != null ? res.TotalWeight.Value : 0;
Shipment.TotalCarton = res.TotalCartons != null ? res.TotalCartons.Value : 0;
Shipment.TotalRows = res.TotalRows.Value;
ShipmentList.Add(Shipment);
}
return(ShipmentList.OrderBy(a => a.Status).ToList());
}
return(ShipmentList);
}
