public static List <ClientDropDown> GetClientDropDown(out string message)
{
try
{
message = "";
using (var db = new QuickipediaEntities())
{
var query = from c in db.ClientProfile
orderby c.ClientName ascending
select new ClientDropDown
{
ID = c.ClientCode,
Value = c.ClientName
};
return(query.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <MotoRequestModel> GetDuplicate(MotoRequestModel _model, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.MotoRequest.Where(r => r.RecordLocator.ToLower() == _model.RecordLocator.ToLower() &&
r.PaxName.ToLower() == _model.PaxName.ToLower()).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from m in moto
join c in quicki on m.ClientCode equals c.ClientCode
join u in db.UserAccount on m.RequestedBy equals u.ID
orderby m.Date ascending
select new MotoRequestModel
{
ClientCode = m.ClientCode,
ClientName = c.ClientName,
AdminFee = m.AdminFee,
Amount = m.Amount,
ApprovalCode = m.ApprovalCode,
BCDFee = m.BCDFee,
Company = m.Company,
Currency = m.Currency,
Date = m.Date,
ID = m.ID,
Invoice = m.Invoice,
OptionTime = m.OptionTime,
Others = m.Others,
PaxName = m.PaxName.ToUpper(),
RecordLocator = m.RecordLocator,
Remarks = m.Remarks,
Status = m.Status,
Total = m.Total,
RequestedBy = m.RequestedBy,
ShowRequestedBy = u.FirstName + " " + u.LastName,
DeclinedReason = m.DeclinedReason,
ApprovedDate = m.ApprovedDate,
LogID = m.LogID
};
return(join.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static List <ApprovedReportModel> GetAllMotoReport(DateTime start, DateTime end, out string message)
{
try
{
message = "";
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.v_MotoRequest.Where(r => r.ApprovedDate >= start && r.ApprovedDate <= end).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from a in moto
join c in quicki on a.ClientCode equals c.ClientCode
join u in db.UserAccount on a.RequestedBy equals u.ID
join usr in db.UserAccount on a.ApprovedBy equals usr.ID into qUsr
from ap in qUsr.DefaultIfEmpty()
orderby a.Date ascending
select new ApprovedReportModel
{
ID = a.ID,
Date = a.Date,
FirstName = a.FirstName,
LastName = a.LastName,
ClientCode = a.ClientCode,
Company = a.Company,
PaxName = a.PaxName,
RecordLocator = a.RecordLocator,
Currency = a.Currency,
Amount = a.Amount,
Others = a.Others,
BCDFee = a.BCDFee,
AdminFee = a.AdminFee,
Total = a.Total,
OptionTime = a.OptionTime,
ApprovalCode = a.ApprovalCode,
Remarks = a.Remarks,
ApprovedDate = a.ApprovedDate,
ApprovedBy = ap.ID,
ShowApprovedBy = ap.FirstName + " " + ap.LastName,
ClientName = c.ClientName,
LogID = a.LogID
};
return(join.ToList());
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
public static double GetAdminFeeFromDB(MotoRequestModel _moto, out string message)
{
try
{
message = "";
double _airFare, _serviceFee, _others;
if (_moto.Amount == null)
{
_airFare = 0;
}
else
{
_airFare = double.Parse(_moto.Amount.ToString());
}
if (_moto.BCDFee == null)
{
_serviceFee = 0;
}
else
{
_serviceFee = double.Parse(_moto.BCDFee.ToString());
}
if (_moto.Others == null)
{
_others = 0;
}
else
{
_others = double.Parse(_moto.Others.ToString());
}
if (_moto.Currency == "" || _moto.Currency == null)
{
message = "Currency is required to auto compute admin fee";
return(0);
}
using (var db = new QuickipediaEntities())
{
var admFormula = db.EcardAdminFee.FirstOrDefault(r => r.ClientCode == _moto.ClientCode);
if (admFormula != null)
{
double total = 0;
if (_moto.Currency == "PHP")
{
if (admFormula.AirFareFlag)
{
total += _airFare;
}
if (admFormula.ServiceFeeFlag)
{
total += _serviceFee;
}
if (admFormula.OtherFeeFlag)
{
total += _others;
}
if (admFormula.Divide > 0)
{
total = total / double.Parse(admFormula.Divide.ToString());
}
if (admFormula.Multiply > 0)
{
total = total * double.Parse(admFormula.Multiply.ToString());
}
}
else
{
if (admFormula.AirFareUSD)
{
total += _airFare;
}
if (admFormula.ServiceFeeUSD)
{
total += _serviceFee;
}
if (admFormula.OthersUSD)
{
total += _others;
}
if (admFormula.DivideUSD > 0)
{
total = total / double.Parse(admFormula.DivideUSD.ToString());
}
if (admFormula.MultiplyUSD > 0)
{
total = total * double.Parse(admFormula.MultiplyUSD.ToString());
}
}
return(total);
}
else
{
message = "Admin Fee Profile not Found (You need to compute admin fee manually)";
return(0);
}
}
}
catch (Exception error)
{
message = error.Message;
return(0);
}
}
public static List <MotoRequestModel> Search(string search, out string message)
{
try
{
message = "";
var lowerSearch = search.ToLower();
using (var db = new eCardEntities())
{
var qDB = new QuickipediaEntities();
var moto = db.v_MotoRequest.Where(r => r.RecordLocator.ToLower().Contains(lowerSearch) ||
r.Remarks.ToLower().Contains(lowerSearch) || r.PaxName.ToLower().Contains(lowerSearch) ||
r.LogID.ToString().ToLower().Contains(lowerSearch)
).ToList();
var motoClientCode = moto.Select(r => r.ClientCode);
var quicki = qDB.ClientProfile.Where(r => motoClientCode.Contains(r.ClientCode)).ToList();
var join = from a in moto
join c in quicki on a.ClientCode equals c.ClientCode
join u in db.UserAccount on a.RequestedBy equals u.ID
join usr in db.UserAccount on a.ApprovedBy equals usr.ID into qUsr
from ap in qUsr.DefaultIfEmpty()
orderby a.Date ascending
select new MotoRequestModel
{
ID = a.ID,
Date = a.Date,
ClientCode = a.ClientCode,
Company = a.Company,
PaxName = a.PaxName,
RecordLocator = a.RecordLocator,
Currency = a.Currency,
Amount = a.Amount,
Others = a.Others,
BCDFee = a.BCDFee,
AdminFee = a.AdminFee,
Total = a.Total,
OptionTime = a.OptionTime,
ApprovalCode = a.ApprovalCode,
Remarks = a.Remarks,
ApprovedDate = a.ApprovedDate,
ApprovedBy = ap.ID,
ShowApprovedBy = ap.FirstName + " " + ap.LastName,
Status = a.Status,
ClientName = c.ClientName,
RequestedBy = a.RequestedBy,
ShowRequestedBy = u.FirstName + " " + u.LastName,
DeclinedReason = a.DeclinedReason,
LogID = a.LogID
};
var val = join.ToList();
if (val.Count() == 0)
{
message = "No record found . . .";
return(null);
}
else
{
return(val);
}
}
}
catch (Exception error)
{
message = error.Message;
return(null);
}
}
