/// <summary>
/// Update AccountId column in any table
/// </summary>
/// <param name="accountId">AccountId</param>
/// <param name="obj">Table Name which want to update</param>
/// <param name="objId">record Id ..Table PK value</param>
/// <param name="pkName">Table PK column name</param>
public static void AddAccountIdToObj(string accountId, string obj, int objId, string pkName, string accountIdColumnName = "")
{
EasyFreightEntities db = new EasyFreightEntities();
StringBuilder query = new StringBuilder();
query.Append("Update ");
query.Append(obj);
if (string.IsNullOrEmpty(accountIdColumnName))
{
query.Append(" Set AccountId = '");
}
else
{
query.Append(" Set " + accountIdColumnName + " = '");
}
query.Append(accountId);
query.Append("' Where ");
query.Append(pkName);
query.Append(" = ");
query.Append(objId);
db.Database.ExecuteSqlCommand(query.ToString());
}
public static CompanySetupVm GetCompanySetup()
{
CompanySetupVm compSetVm = new CompanySetupVm();
EasyFreightEntities db = new EasyFreightEntities();
CompanySetup compSetupDb = db.CompanySetups.FirstOrDefault();
CompanySetupContact compSetupContactDb = db.CompanySetupContacts.FirstOrDefault();
// SystemSetup sysSetupDb = db.SystemSetups.FirstOrDefault();
if (compSetupDb != null)
{
Mapper.CreateMap <CompanySetup, CompanySetupVm>();
Mapper.Map(compSetupDb, compSetVm);
}
if (compSetupContactDb != null)
{
Mapper.CreateMap <CompanySetupContact, CompanySetupVm>();
Mapper.Map(compSetupContactDb, compSetVm);
}
//if (sysSetupDb != null)
//{
// Mapper.CreateMap<SystemSetup, CompanySetupVm>();
// Mapper.Map(sysSetupDb, compSetVm);
//}
return(compSetVm);
}
internal static object DeleteVessel(int vesselId)
{
EasyFreightEntities db = new EasyFreightEntities();
string isDeleted = "true";
Vessel vesselDb = db.Vessels.Where(x => x.VesselId == vesselId).FirstOrDefault();
db.Vessels.Remove(vesselDb);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
if ((ex.InnerException).InnerException.Message.Contains("DELETE statement conflicted"))
{
isDeleted = "false. Can't delete this row as it is linked with some operation orders";
}
else
{
isDeleted = "false" + ex.Message;
}
}
return(isDeleted);
}
public static string AddEditArea(Area areaDb)
{
string isSaved = "true";
EasyFreightEntities db = new EasyFreightEntities();
if (areaDb.AreaId == 0)
{
db.Areas.Add(areaDb);
}
else
{
db.Areas.Attach(areaDb);
db.Entry(areaDb).State = System.Data.Entity.EntityState.Modified;
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
isSaved = "false" + ex.Message;
}
return(isSaved);
}
internal static List <Vessel> GetVesselList()
{
EasyFreightEntities db = new EasyFreightEntities();
List <Vessel> vesselList = db.Vessels.ToList();
return(vesselList);
}
internal static string AddEditVessel(int vesselId, string vesselName)
{
string isSaved = "true";
EasyFreightEntities db = new EasyFreightEntities();
Vessel vesselDb;
if (vesselId == 0)
{
vesselDb = new Vessel();
}
else
{
vesselDb = db.Vessels.Where(x => x.VesselId == vesselId).FirstOrDefault();
}
vesselDb.VesselName = vesselName;
if (vesselId == 0)
{
db.Vessels.Add(vesselDb);
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
isSaved = "false" + ex.Message;
}
return(isSaved);
}
public static List <IncotermLib> GetIncotermList()
{
EasyFreightEntities db = new EasyFreightEntities();
List <IncotermLib> incotermLibList = db.IncotermLibs.ToList();
return(incotermLibList);
}
public static List <PackageType> GetPackageTypesList()
{
EasyFreightEntities db = new EasyFreightEntities();
List <PackageType> packageTypeList = db.PackageTypes.ToList();
return(packageTypeList);
}
public static List <ContainerType> GetContainersTypeList()
{
EasyFreightEntities db = new EasyFreightEntities();
List <ContainerType> containersTypeList = db.ContainerTypes.ToList();
return(containersTypeList);
}
public static List <Country> GetCountriesList()
{
EasyFreightEntities db = new EasyFreightEntities();
List <Country> countriesList = db.Countries.ToList();
return(countriesList);
}
public static int AddConsigneeQuick(string code, string name)
{
Consignee consigneeObj = new Consignee();
consigneeObj.ConsigneeCode = code;
consigneeObj.ConsigneeNameEn = name;
EasyFreightEntities db = new EasyFreightEntities();
string isSaved = "true";
db.Consignees.Add(consigneeObj);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
isSaved = "false " + e.Message;
}
catch (Exception e)
{
isSaved = "false " + e.InnerException.InnerException;
}
return(consigneeObj.ConsigneeId);
}
public static StaticText GetStaticTextById(int staticTextId)
{
EasyFreightEntities db = new EasyFreightEntities();
StaticText staticText = db.StaticTexts.Where(x => x.StaticTextId == staticTextId).FirstOrDefault();
return(staticText);
}
public static int AddShipperQuick(string code, string name)
{
Shipper shipperObj = new Shipper();
shipperObj.ShipperCode = code;
shipperObj.ShipperNameEn = name;
EasyFreightEntities db = new EasyFreightEntities();
string isSaved = "true";
db.Shippers.Add(shipperObj);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
isSaved = "false " + e.Message;
}
catch (Exception e)
{
isSaved = "false " + e.InnerException.InnerException;
}
return(shipperObj.ShipperId);
}
public static AgentVm GetAgentInfo(int id)
{
AgentVm carrVmObj = new AgentVm();
if (id == 0)
{
ContactPersonVm contactVm = new ContactPersonVm();
carrVmObj.ContactPersons.Add(contactVm);
}
else
{
EasyFreightEntities db = new EasyFreightEntities();
Agent carrDbObj = db.Agents.Include("AgentContacts")
.Where(x => x.AgentId == id).FirstOrDefault();
Mapper.CreateMap <Agent, AgentVm>().IgnoreAllNonExisting();
Mapper.Map(carrDbObj, carrVmObj);
Mapper.CreateMap <AgentContact, ContactPersonVm>().IgnoreAllNonExisting()
.ForMember(x => x.FkValue, opts => opts.MapFrom(scr => scr.AgentId));
ContactPersonVm contactVm;
foreach (var item in carrDbObj.AgentContacts)
{
contactVm = new ContactPersonVm();
Mapper.Map(item, contactVm);
carrVmObj.ContactPersons.Add(contactVm);
}
}
return(carrVmObj);
}
/// <summary>
/// Get Contractor List By City
/// </summary>
/// <param name="langCode">en is default.. send "ar" to get Arabic data </param>
/// <param name="fromCityID">From CityId </param>
/// <param name="toCityId">To CityId</param>
/// <returns>Dictionary Contractor Id, Contractor Name</returns>
public static Dictionary <int, string> GetContractorListByCity(string langCode = "en", int fromCityID = 0, int toCityId = 0)
{
EasyFreightEntities db = new EasyFreightEntities();
Dictionary <int, string> ContractorList = new Dictionary <int, string>();
var contractorListDb = db.ContractorRates.Where(x => (x.FromCityId == fromCityID && x.ToCityId == toCityId) ||
x.ToCityId == fromCityID && x.FromCityId == toCityId).Select
(x => new { x.ContractorId, x.Contractor.ContractorNameEn, x.Contractor.ContractorNameAr }).Distinct().ToList();
if (langCode == "en")
{
ContractorList = contractorListDb
.OrderBy(x => x.ContractorNameEn)
.ToDictionary(x => x.ContractorId, x => x.ContractorNameEn);
}
else
{
ContractorList = contractorListDb
.OrderBy(x => x.ContractorNameAr)
.ToDictionary(x => x.ContractorId, x => x.ContractorNameAr);
}
return(ContractorList);
}
public static List <TruckingCostVm> GetTruckingCostByTruckingOrderId(int truckingOrderId)
{
List <TruckingCostVm> truckingCostvmList = new List <TruckingCostVm>();
EasyFreightEntities db = new EasyFreightEntities();
// Mapper.CreateMap<TruckingOrderCost, TruckingCostVm>().IgnoreAllNonExisting();
TruckingCostVm truckingCostObj;
//var truckingCostList = db.TruckingOrderCosts
// .Include("TruckingCostLib").Where(x => x.TruckingOrderId == truckingOrderId).ToList();
var truckingCostList2 = from c in db.TruckingOrderCosts
join o in db.Currencies on c.CurrencyId equals o.CurrencyId
where c.TruckingOrderId == truckingOrderId
select new
{
ContractorId = c.ContractorId,
CurrencyId = o.CurrencyId,
CurrencySign = o.CurrencySign,
TruckingCostId = c.TruckingCostId,
TruckingCostName = c.TruckingCostLib.TruckingCostName,
TruckingCostNet = c.TruckingCostNet,
TruckingCostSelling = c.TruckingCostSelling,
TruckingOrderCostId = c.TruckingOrderCostId,
TruckingOrderId = c.TruckingOrderId,
};
foreach (var item in truckingCostList2)
{
truckingCostObj = new TruckingCostVm();
truckingCostObj.ContractorId = item.ContractorId;
truckingCostObj.CurrencyId = item.CurrencyId;
truckingCostObj.CurrencySign = item.CurrencySign;
truckingCostObj.TruckingCostId = item.TruckingCostId;
truckingCostObj.TruckingCostName = item.TruckingCostName;
truckingCostObj.TruckingCostNet = item.TruckingCostNet;
truckingCostObj.TruckingCostSelling = item.TruckingCostSelling;
truckingCostObj.TruckingOrderCostId = item.TruckingOrderCostId;
truckingCostObj.TruckingOrderId = item.TruckingOrderId;
truckingCostvmList.Add(truckingCostObj);
//try
//{
// truckingCostObj = new TruckingCostVm();
// Mapper.Map(item, truckingCostObj);
// truckingCostvmList.Add(truckingCostObj);
//}
//catch { }
}
//try
//{
// Mapper.CreateMap<TruckingOrderCost, TruckingCostVm>().IgnoreAllNonExisting();
// Mapper.Map(truckingCostList2, truckingCostvmList);
//}
//catch
//{ }
return(truckingCostvmList);
}
public static string ChangeStatus(int truckingOrderId, byte statusId)
{
string isSaved = "true";
EasyFreightEntities db = new EasyFreightEntities();
TruckingOrder orderDb;
orderDb = db.TruckingOrders.Where(x => x.TruckingOrderId == truckingOrderId).FirstOrDefault();
if (statusId == 4) //cancel from HB will delete the record
{
db.TruckingOrders.Remove(orderDb);
}
else
{
orderDb.StatusId = statusId;
}
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
isSaved = "false " + e.Message;
}
catch (Exception e)
{
isSaved = "false " + e.Message;
}
return(isSaved);
}
private static bool CheckIfActiveRate(CarrierRateVm carrRateVm)
{
bool isActive = false;
int carrId, fromPortId, toPortId, containerTypeId, carrRateId;
carrId = carrRateVm.CarrierId;
fromPortId = carrRateVm.FromPortId;
toPortId = carrRateVm.ToPortId;
containerTypeId = carrRateVm.ContainerTypeId;
carrRateId = carrRateVm.CarrierRateId;
EasyFreightEntities db = new EasyFreightEntities();
if (carrRateId == 0)
{
isActive = db.CarrierRates.Any(x => x.CarrierId == carrId &&
x.ContainerTypeId == containerTypeId && x.FromPortId == fromPortId &&
x.ToPortId == toPortId &&
x.IsValid == true);
}
else
{
isActive = db.CarrierRates.Any(x => x.CarrierId == carrId &&
x.ContainerTypeId == containerTypeId && x.FromPortId == fromPortId &&
x.ToPortId == toPortId && x.CarrierRateId != carrRateId &&
x.IsValid == true);
}
return(isActive);
}
public static List <AgentNoteVm> GetAgentNoteList(int operId)
{
AccountingEntities db = new AccountingEntities();
List <AgentNoteVm> agentNoteList = new List <AgentNoteVm>();
var agNoteDb = db.AgentNotes.Where(x => x.OperationId == operId).ToList();
Mapper.CreateMap <AgentNote, AgentNoteVm>()
.ForMember(x => x.AgentNoteDetails, y => y.Ignore())
.IgnoreAllNonExisting();
Mapper.Map(agNoteDb, agentNoteList);
//Get agent Name
List <int> agentIds = agentNoteList.Select(x => x.AgentId).ToList();
EasyFreightEntities db1 = new EasyFreightEntities();
var agentInfo = db1.Agents.Where(x => agentIds.Contains(x.AgentId))
.Select(x => new { x.AgentId, x.AgentNameEn }).ToList();
// update to show status 24/01/2017
var invStatusLib = db.InvStatusLibs.Select(a => a).ToList();
foreach (var item in agentNoteList)
{
item.AgentName = agentInfo.Where(x => x.AgentId == item.AgentId).FirstOrDefault().AgentNameEn;
// update to show status 24/01/2017
item.InvStatusName = invStatusLib.Where(x => x.InvStatusId == item.InvStatusId)
.FirstOrDefault().InvStatusNameEn;
if (item.AgentNoteType == 1) //Debit .. will collect
{
item.InvStatusName = item.InvStatusName.Replace("Paid", "Collected");
}
}
return(agentNoteList);
}
private static bool CheckIfActiveRate(ContractorRateVm contractRateVm)
{
bool isActive = false;
int contractId, containerTypeId, contractRateId;
int? fromAreaId, toAreaId;
contractId = contractRateVm.ContractorId;
fromAreaId = contractRateVm.FromAreaId;
toAreaId = contractRateVm.ToAreaId;
containerTypeId = contractRateVm.ContainerTypeId;
contractRateId = contractRateVm.ContractorRateId;
EasyFreightEntities db = new EasyFreightEntities();
if (contractRateId == 0)
{
isActive = db.ContractorRates.Any(x => x.ContractorId == contractId &&
x.ContainerTypeId == containerTypeId && x.FromAreaId == fromAreaId &&
x.ToAreaId == toAreaId &&
x.IsValid == true);
}
else
{
isActive = db.ContractorRates.Any(x => x.ContractorId == contractId &&
x.ContainerTypeId == containerTypeId && x.FromAreaId == fromAreaId &&
x.ToAreaId == toAreaId && x.ContractorRateId != contractRateId &&
x.IsValid == true);
}
return(isActive);
}
/// <summary>
/// Get valid contractors rates between two areas
/// </summary>
/// <param name="fromAreaId">From Area Id</param>
/// <param name="toAreaId">To Area Id</param>
/// <returns>ContractorRateVm List</returns>
public static List <ContractorRateVm> GetContractorRatesForArea(int fromCityId, int toCityId)
{
EasyFreightEntities db = new EasyFreightEntities();
List <ContractorRateVm> contractRateVmList = new List <ContractorRateVm>();
var dbList = db.ContractorRates.Include("ContainerType").Include("Contractor").Include("Currency")
.Where(x => (x.FromCityId == fromCityId && x.ToCityId == toCityId) ||
(x.ToCityId == fromCityId && x.FromCityId == toCityId) && x.IsValid == true)
.OrderBy(x => x.ContainerTypeId).ThenByDescending(x => x.CostAmount)
.ToList();
Mapper.CreateMap <ContractorRate, ContractorRateVm>().IgnoreAllNonExisting()
.ForMember(x => x.ContainerTypeName, c => c.MapFrom(d => d.ContainerType.ContainerTypeName))
.ForMember(x => x.ContractorName, c => c.MapFrom(d => d.Contractor.ContractorNameEn))
.ForMember(x => x.CurrencySign, c => c.MapFrom(d => d.Currency.CurrencySign))
.ForMember(x => x.FromAreaName, c => c.MapFrom(d => d.Area.AreaNameEn))
.ForMember(x => x.ToAreaName, c => c.MapFrom(d => d.Area1.AreaNameEn))
.ForMember(x => x.FromCityName, c => c.MapFrom(d => d.City.CityNameEn))
.ForMember(x => x.ToCityName, c => c.MapFrom(d => d.City1.CityNameEn))
;
Mapper.CreateMap <Contractor, ContractorVm>().IgnoreAllNonExisting();
Mapper.Map(dbList, contractRateVmList);
return(contractRateVmList);
}
internal static string DeleteTruckingCost(int id)
{
EasyFreightEntities db = new EasyFreightEntities();
string isDeleted = "true";
var costObj = db.TruckingCostLibs.Where(x => x.TruckingCostId == id).FirstOrDefault();
db.TruckingCostLibs.Remove(costObj);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
if ((ex.InnerException).InnerException.Message.Contains("DELETE statement conflicted"))
{
isDeleted = "false. Can't delete this row as it is linked with some operation orders";
}
else
{
isDeleted = "false" + ex.Message;
}
}
return(isDeleted);
}
internal static string AddEditTruckCost(int costlId, string costNameEn, string costNameAr)
{
string isSaved = "true";
EasyFreightEntities db = new EasyFreightEntities();
TruckingCostLib costDb;
if (costlId == 0)
{
costDb = new TruckingCostLib();
}
else
{
costDb = db.TruckingCostLibs.Where(x => x.TruckingCostId == costlId).FirstOrDefault();
}
costDb.TruckingCostName = costNameEn;
costDb.TruckingCostNameAr = costNameAr;
if (costlId == 0)
{
db.TruckingCostLibs.Add(costDb);
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
isSaved = "false" + ex.Message;
}
return(isSaved);
}
internal static List <TruckingCostLib> GetTruckingCostLib()
{
EasyFreightEntities db = new EasyFreightEntities();
List <TruckingCostLib> costLib = db.TruckingCostLibs.ToList();
return(costLib);
}
/// <summary>
/// Get Contractor List By Area
/// </summary>
/// <param name="langCode">en is default.. send "ar" to get Arabic data </param>
/// <param name="fromAreaId">From AreaId </param>
/// <param name="toAreaId">To AreaId</param>
/// <returns>Dictionary Contractor Id, Contractor Name</returns>
public static Dictionary <int, string> GetContractorListByArea(string langCode = "en", int fromAreaID = 0, int toAreaId = 0)
{
EasyFreightEntities db = new EasyFreightEntities();
Dictionary <int, string> ContractorList = new Dictionary <int, string>();
var contractorListDb = db.ContractorRates.Where(x => (x.FromAreaId == fromAreaID && x.ToAreaId == toAreaId) ||
x.ToAreaId == fromAreaID && x.FromAreaId == toAreaId).Select
(x => new { x.ContractorId, x.Contractor.ContractorNameEn, x.Contractor.ContractorNameAr }).Distinct().ToList();
//var contractorListDb = db.Contractors.Include("ContractorRates").Where(x=> x.ContractorRates.FirstOrDefault().FromAreaId == fromAreaID
// && x.ContractorRates.FirstOrDefault().ToAreaId == toAreaId)
// .ToList();
if (langCode == "en")
{
ContractorList = contractorListDb
.OrderBy(x => x.ContractorNameEn)
.ToDictionary(x => x.ContractorId, x => x.ContractorNameEn);
}
else
{
ContractorList = contractorListDb
.OrderBy(x => x.ContractorNameAr)
.ToDictionary(x => x.ContractorId, x => x.ContractorNameAr);
}
return(ContractorList);
}
//public static OperationStatisticVm GetOperationStatistics(System.Web.Mvc.FormCollection form)
//{
//OperationsEntities db5 = new OperationsEntities();
//var operationList = db5.OperationViews.Where(f => f.OrderFrom == orderFrom &&
// (f.OperationDate >= fromDate && f.OperationDate <= toDate))
// .Select(x => new
// {
// x.OperationId,
// x.CarrierType,
// x.QuoteId,
// x.CreateDate,
// x.OperationCode,
// x.ShipperNameEn,
// x.ConsigneeNameEn,
// x.CarrierNameEn,
// x.FromPort,
// x.ToPort,
// x.DateOfDeparture,
// x.StatusName,
// x.StatusId,
// x.IsConsolidation,
// x.OrderFrom,
// x.AgentNameEn
// })
// .ToList();
// OperationsEntities db = new OperationsEntities();
// OperationStatisticVm operationStatistic = new OperationStatisticVm() ;
// int orderfrom = string.IsNullOrEmpty(form["OrderFrom"])? 1:int.Parse(form["OrderFrom"]);
// DateTime fromDate = string.IsNullOrEmpty(form["FromDate"]) ? DateTime.Now.AddDays(-7) : DateTime.Parse(form["FromDate"]);
// DateTime toDate = string.IsNullOrEmpty(form["ToDate"]) ? DateTime.Now.AddDays(-7) : DateTime.Parse(form["ToDate"]);
// operationStatistic = GetOperationStatistics(fromDate, toDate, orderfrom);
// return operationStatistic;
//}
internal static string DeleteOperation(int operationId)
{
string isClosed = "true";
OperationsEntities db = new OperationsEntities();
int statusId = db.Operations.Where(d => d.OperationId == operationId).FirstOrDefault().StatusId;
if (statusId > 2)
{
isClosed = "false Cannot delete this operation it may be Closed or Invoice Issued ";
return(isClosed);
}
// delete all HB under this operation
var hbList = db.HouseBills.Where(x => x.OperationId == operationId);// && (x.StatusId == 1 || x.StatusId == 2));
foreach (var item in hbList)
{
item.StatusId = 4; //canceled
}
// delete all Custom Clearance under this operation
var ccList = db.CustomClearanceOrders.Where(x => x.OperationId == operationId);// && (x.StatusId == 1 || x.StatusId == 2));
foreach (var item in ccList)
{
item.StatusId = 4; //canceled
}
// Delete all Trucking orders
EasyFreightEntities dbE = new EasyFreightEntities();
var truckList = dbE.TruckingOrders.Where(x => x.OperationId == operationId);// && (x.StatusId == 1 || x.StatusId == 2));
foreach (var item in truckList)
{
item.StatusId = 4; //canceled
}
var operation = db.Operations.Where(x => x.OperationId == operationId).FirstOrDefault();
operation.StatusId = 4;
try
{
db.SaveChanges();
dbE.SaveChanges();
}
catch (DbEntityValidationException e)
{
isClosed = "false " + e.Message;
}
catch (Exception e)
{
isClosed = "false " + e.InnerException;
}
return(isClosed);
}
public static List <AgentVm> GetAgentListByCountry(int countryId = 0)
{
List <AgentVm> agentList = new List <AgentVm>();
EasyFreightEntities db = new EasyFreightEntities();
var carrDbList = db.Agents.Where(c => c.CountryId == (countryId > 0 ? countryId : c.CountryId)).ToList();
Mapper.CreateMap <Agent, AgentVm>().IgnoreAllNonExisting();
Mapper.Map(carrDbList, agentList);
return(agentList);
}
public static List <AgentVm> GetAgentList()
{
List <AgentVm> agentList = new List <AgentVm>();
EasyFreightEntities db = new EasyFreightEntities();
var carrDbList = db.Agents.ToList();
Mapper.CreateMap <Agent, AgentVm>().IgnoreAllNonExisting();
Mapper.Map(carrDbList, agentList);
return(agentList);
}
public static List <CarrierVm> GetCarrierList()
{
List <CarrierVm> carrList = new List <CarrierVm>();
EasyFreightEntities db = new EasyFreightEntities();
var carrDbList = db.Carriers.ToList();
Mapper.CreateMap <Carrier, CarrierVm>().IgnoreAllNonExisting();
Mapper.Map(carrDbList, carrList);
return(carrList);
}
public static List <ShipperVm> GetShipperList()
{
List <ShipperVm> shipperList = new List <ShipperVm>();
EasyFreightEntities db = new EasyFreightEntities();
var carrDbList = db.Shippers.ToList();
Mapper.CreateMap <Shipper, ShipperVm>().IgnoreAllNonExisting();
Mapper.Map(carrDbList, shipperList);
return(shipperList);
}