public static List <string> SearchFor(string pre, int option)
{
List <string> results = new List <string>();
switch (option)
{
case 1:
using (GiffiDBEntities dc = new GiffiDBEntities())
{
if (pre.Equals("*") || pre.Equals("."))
{
results = (from c in dc.Companies
select c.CompanyName).ToList();
}
else
{
results = (from c in dc.Companies
where c.CompanyName.StartsWith(pre)
select c.CompanyName).ToList();
}
}
break;
default:
break;
}
return(results);
}
public static string GetCarrierNameByCarrierId(int carrierId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from c in dc.Companies
where c.Id == carrierId
select c.CompanyName);
return(results.FirstOrDefault());
}
}
internal static string GetCompanyCodeById(int id)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from c in dc.Companies
where c.Id == id
select c.Code);
return(results.FirstOrDefault());
}
}
internal static Company GetCompanyById(int companyId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var company = (from c in dc.Companies
where c.Id == companyId
select c).First();
return(company);
}
}
public static int GetCompanyIdFromCode(string carrierCode)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var allCompanyId = (from c in dc.Companies
where c.Code.Equals(carrierCode, StringComparison.InvariantCultureIgnoreCase)
select c.Id).Distinct().ToList();
return(allCompanyId.First());
}
}
public static int GetBookingIdFromGiffiId(double giffiRef)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = from bf in dc.BookingReferences
where bf.GiffiId == giffiRef
select bf.BookingId;
return((results.Any()) ? (int)results.First() : -1);
}
}
public static List <Company> GetCompanyByType(CompanyType type)
{
List <Company> cp = new List <Company>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
cp = (from c in dc.Companies
where c.CompanyType.Equals(type.ToString(), StringComparison.InvariantCultureIgnoreCase)
select c).ToList();
}
return(cp);
}
private void PopulateData()
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from c in dc.Companies
orderby c.CompanyType descending, c.CompanyName
select new { c.Id, c.CompanyName, c.Code, c.FederalNumber, c.CompanyType, c.Address, c.City, c.State, c.ZipCode, c.Phone }).ToList();
gvMgmt.DataSource = results;
gvMgmt.DataBind();
}
}
internal static Booking GetBookingFromGiffiId(double giffiRef)
{
int bookingId = GetBookingIdFromGiffiId(giffiRef);
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = from b in dc.Bookings
where b.Id == bookingId
select b;
return((results.Any()) ? results.First() : null);
}
}
private Dictionary <int, string> GetVendorInfo()
{
List <string> carrierNames = new List <string>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from c in dc.Companies
where c.CompanyType.Equals("Vendor", StringComparison.InvariantCultureIgnoreCase)
select new { c.Id, c.Code });
return(results.ToDictionary(a => a.Id, a => a.Code));
}
}
protected void btnSelectedSearch_Click(object sender, EventArgs e)
{
int option = int.Parse(ddlSearchOption.SelectedValue.ToString());
string searchPhase = txtSearchBox.Text.Trim();
string giffiRefNo = searchPhase;
using (GiffiDBEntities dc = new GiffiDBEntities())
{
switch (option)
{
case 1:
if (!dc.BookingViews.Any() && !dc.BookingViews.Any(x => x.GiffiId.Value.ToString().Trim().Equals(searchPhase)))
{
this.AlertMessage(GetType(), string.Format("SYSTEM ERROR!!! INVALID GiffRef#{0}", searchPhase));
return;
}
PopulateBookingData(giffiRefNo);
break;
case 2: //ShipperRef
double?dGiffiRef = dc.BookingViews.Where(x => x.ShipperRefNo.Equals(searchPhase, StringComparison.InvariantCultureIgnoreCase)).Select(x => x.GiffiId).FirstOrDefault();
if (!dGiffiRef.HasValue)
{
this.AlertMessage(GetType(), string.Format("SYSTEM ERROR!!! INVALID GiffRef#{0}", searchPhase));
return;
}
PopulateBookingData(dGiffiRef.Value.ToString());
break;
case 3: //CarrierRef
double?dGiffiRef2 = dc.BookingViews.Where(x => x.CarrierRefNo.Equals(searchPhase, StringComparison.InvariantCultureIgnoreCase)).Select(x => x.GiffiId).FirstOrDefault();
if (!dGiffiRef2.HasValue)
{
this.AlertMessage(GetType(), string.Format("SYSTEM ERROR!!! INVALID GiffRef#{0}", searchPhase));
return;
}
PopulateBookingData(dGiffiRef2.Value.ToString());
break;
default:
break;
}
}
}
private Dictionary <int, string> GetAcctCode()
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from a in dc.AccountingCodes
select new { a.Id, a.Name });
if (results == null || !results.Any())
{
return(null);
}
return(results.ToDictionary(a => a.Id, a => string.Format("{0}, {1}", a.Id, a.Name)));
}
}
private Dictionary <string, string> GetAcctCodeMapIdWithName()
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from a in dc.AccountingCodes
select new { a.Id, a.MapId, a.Name });
if (results == null || !results.Any())
{
return(null);
}
return(results.ToDictionary(a => string.Join(",", a.Id, a.MapId), a => a.Name));
}
}
protected void gvIndex_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string userName = this.Page.User.Identity.Name;
gvIndex.PageIndex = e.NewPageIndex;
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from d in dc.BookingViews
where d.CreatedBy.Equals(userName, StringComparison.InvariantCultureIgnoreCase)
orderby d.ModifiedTime descending
select d).ToList();
gvIndex.DataSource = results;
gvIndex.DataBind();
}
}
public static Booking GetBookingInfo(int bookingId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.Bookings
where b.Id == bookingId
select b);
if (results == null || results.Count() == 0)
{
return(null);
}
return(results.First());
}
}
private void PopulateBookingData(string giffiReNo)
{
int bookingId = DataUtil.GetBookingIdFromGiffiId(double.Parse(giffiReNo));
lblGiffiRef.Text = giffiReNo;
hfSearchBookingId.Value = bookingId.ToString();
hfBOLId.Value = "-1";
using (GiffiDBEntities dc = new GiffiDBEntities())
{
BookingView view = dc.BookingViews.ToList <BookingView>().Where(x => x.BookingId == bookingId).FirstOrDefault();
if (view != null)
{
Company c = DataUtil.GetBillToCompany(double.Parse(giffiReNo)); //using
string cityZip = string.Join(", ", c.City.Trim(), c.State.Trim(), c.ZipCode.Trim());
string cpInfo = string.Join("</br>", c.CompanyName, c.Address, cityZip, c.Country);
lblShipper.Text = cpInfo;
lblCarrierRef.Text = view.CarrierRefNo;
}
Container cont = dc.Containers.Where(x => x.BookingId == bookingId).FirstOrDefault();
if (cont != null)
{
txtContainerNo.Text = cont.ContainerNo;
txtSEALNo.Text = cont.SealNo;
}
if (dc.BillOfLandings.Any(x => x.BookingId == bookingId))
{
var bol = dc.BillOfLandings.Where(x => x.BookingId == bookingId).FirstOrDefault();
hfBOLId.Value = bol.Id.ToString();
txtConsignee.Text = bol.ConsigneeAddress;
txtConsigneeRef.Text = bol.ConsigneeRef;
txtBLNo.Text = bol.BOLRef.ToString();
txtNotify.Text = bol.NotifyAddress;
txtDateOfIssue.Text = (bol.DateOfIssue.HasValue) ? bol.DateOfIssue.Value.ToString("d") : "";
txtPlaceOfIssue.Text = bol.PlaceOfIssue;
txtPlaceOfDelivery.Text = bol.PlaceOfDelivery;
txtPlaceOfReceipt.Text = bol.PlaceOfReceipt;
txtSupplier.Text = bol.SupplierAddress;
txtWarehouse.Text = bol.WarehouseAddress;
txtNotes.Text = bol.Notes;
}
}
}
public static List <string> SearchBy(string pre, int option)
{
var regex = new Regex(pre, RegexOptions.IgnoreCase);
List <string> results = new List <string>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
switch (option)
{
case 1:
results = DataUtil.SearchBookingReferenceFor(pre);
break;
case 2:
if (pre.Equals("*") || pre.Equals("."))
{
results = dc.BookingViews.ToList <BookingView>().Select(x => x.CarrierRefNo.TrimEnd()).ToList();
}
else
{
results = dc.BookingViews.ToList <BookingView>().Where(x => x.CarrierRefNo.StartsWith(pre)).Select(x => x.CarrierRefNo.TrimEnd()).ToList();
}
break;
case 3:
if (pre.Equals("*") || pre.Equals("."))
{
results = dc.BookingViews.ToList <BookingView>().Select(x => x.ShipperRefNo.TrimEnd()).ToList();
}
else
{
results = dc.BookingViews.ToList <BookingView>().Where(x => x.ShipperRefNo.StartsWith(pre)).Select(x => x.ShipperRefNo.TrimEnd()).ToList();
}
break;
case 4:
//TODO
break;
default:
results = DataUtil.SearchBookingReferenceFor(pre);
break;
}
}
return(results);
}
private List <Freight> GetFreights(double giffiRef)
{
int bookingId = DataUtil.GetBookingIdFromGiffiId(giffiRef);
if (bookingId < Constants.InitBookingId)
{
return(null);
}
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from f in dc.Freights
where f.BookingId == bookingId
select f);
return((results.Any()) ? results.ToList() : null);
}
}
public static string GetCompanyAsText(int compId)
{
if (compId <= 0)
{
return(string.Empty);
}
using (GiffiDBEntities dc = new GiffiDBEntities())
{
Company cp = (from c in dc.Companies
where c.Id == compId
select c).FirstOrDefault();
string cityZip = string.Join(", ", cp.City.Trim(), cp.State.Trim(), cp.ZipCode.Trim());
string cpInfo = string.Join("\n", cp.CompanyName, cp.Address, cityZip, cp.Country);
return(cpInfo);
}
}
private decimal GetNETTotal(int bookingId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.BillingItems
where b.BookingId == bookingId
select new { b.BillingAmount, b.PayoutAmount });
if (results == null || !results.Any())
{
return(0);
}
decimal billing = results.AsEnumerable().Sum(a => a.BillingAmount);
decimal payout = results.AsEnumerable().Sum(a => a.PayoutAmount);
return(Math.Round(billing - payout, 2, MidpointRounding.AwayFromZero));
}
}
public static List <string> SearchFor(string pre, int option)
{
List <string> allCompanyName = new List <string>();
ComanyType companyType;
using (GiffiDBEntities dc = new GiffiDBEntities())
{
switch (option)
{
case 3:
companyType = ComanyType.Consignee;
break;
case 4:
companyType = ComanyType.Supplier;
break;
case 5:
companyType = ComanyType.Warehouse;
break;
default:
companyType = ComanyType.Consignee;
break;
}
if (pre.Equals("*") || pre.Equals("."))
{
allCompanyName = (from c in dc.Companies
where c.CompanyType.Equals(companyType.ToString())
select c.CompanyName).Distinct().ToList();
}
else
{
allCompanyName = (from c in dc.Companies
where c.CompanyType.Equals(companyType.ToString()) && c.CompanyName.StartsWith(pre)
select c.CompanyName).Distinct().ToList();
}
}
return(allCompanyName);
}
private decimal GetPayoutTotal(int bookingId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.BillingItems
where b.BookingId == bookingId
select new { b.PayoutAmount });
if (results == null || !results.Any())
{
return(0);
}
decimal payout = results.AsEnumerable().Sum(a => a.PayoutAmount);
return(payout);
}
}
private Tuple <decimal, decimal> GetTotal(int bookingId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.BillingItems
where b.BookingId == bookingId
select new { b.BillingAmount, b.PayoutAmount });
if (results == null || !results.Any())
{
return(null);
}
decimal billing = results.AsEnumerable().Sum(a => a.BillingAmount);
decimal payout = results.AsEnumerable().Sum(a => a.PayoutAmount);
return(new Tuple <decimal, decimal> (billing, payout));
}
}
public static List <string> SearchBookingReferenceFor(string pre)
{
List <string> results = new List <string>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
List <double> giffiIds = (from c in dc.BookingReferences select c.GiffiId).ToList();
List <string> listGiffiIds = giffiIds.Select(d => string.Format("{0:0.##}", d)).ToList();
if (pre.Equals("*") || pre.Equals("."))
{
results = listGiffiIds;
}
else
{
results = listGiffiIds.Where(s => s.StartsWith(pre)).ToList();
}
}
return(results);
}
private Company GetCompanyInfo(int bookingId)
{
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.Bookings
where b.Id == bookingId
select b.BillToId);
if (results == null || results.Count() == 0)
{
return(null);
}
int billToId = results.First();
var cp = (from c in dc.Companies
where c.Id == billToId
select c);
return(cp.First());
}
}
private decimal GetInvoiceTotal(int bookingId)
{
decimal billing = 0;
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.BillingItems
where b.BookingId == bookingId
select new { b.BillingAmount });
if (results == null || !results.Any())
{
return(billing);
}
billing = results.AsEnumerable().Sum(a => a.BillingAmount);
return(billing);
}
}
protected void SelectedSearch_Click(object sender, EventArgs e)
{
int option = int.Parse(DropDownList1.SelectedValue.ToString());
string searchPhase = txtSearchBox.Text;
var regex = new Regex(searchPhase, RegexOptions.IgnoreCase);
using (GiffiDBEntities dc = new GiffiDBEntities())
{
switch (option)
{
case 1:
if (searchPhase.Equals(".") || searchPhase.Equals("*"))
{
gvIndex.DataSource = dc.BookingViews.ToList <BookingView>();
}
else
{
var results = dc.BookingViews.ToList <BookingView>().Where(x => regex.IsMatch(x.GiffiId.Value.ToString())).ToList();
gvIndex.DataSource = results;
}
break;
case 2:
var shipperList = dc.BookingViews.ToList <BookingView>().Where(x => regex.IsMatch(x.ShipperRefNo)).ToList();
gvIndex.DataSource = shipperList;
break;
case 3:
var carrierList = dc.BookingViews.ToList <BookingView>().Where(x => regex.IsMatch(x.CarrierRefNo)).ToList();
gvIndex.DataSource = carrierList;
break;
default:
gvIndex.DataSource = dc.BookingViews.ToList <BookingView>();
break;
}
gvIndex.DataBind();
}
}
public static List <string> GetCompanyName(string pre)
{
List <string> allCompanyName = new List <string>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
if (pre.Equals("*") || pre.Equals("."))
{
allCompanyName = (from c in dc.Companies
where (c.CompanyType.Equals("Customer") || c.CompanyType.Equals("Both"))
select c.CompanyName).Distinct().ToList();
}
else
{
allCompanyName = (from c in dc.Companies
where c.CompanyName.StartsWith(pre)
select c.CompanyName).Distinct().ToList();
}
}
return(allCompanyName);
}
public static List <string> GetCarrierCode(string pre)
{
List <string> carrierNames = new List <string>();
using (GiffiDBEntities dc = new GiffiDBEntities())
{
if (pre.Equals("*") || pre.Equals("."))
{
carrierNames = (from c in dc.Companies
where c.CompanyType.Equals("Vendor", StringComparison.InvariantCultureIgnoreCase)
select c.Code).Distinct().ToList();
}
else
{
carrierNames = (from c in dc.Companies
where c.Code.StartsWith(pre) && c.CompanyType.Equals("Vendor", StringComparison.InvariantCultureIgnoreCase)
select c.Code).Distinct().ToList();
}
}
return(carrierNames);
}
public static Company GetBillToCompany(double giffiRef)
{
int bookingId = DataUtil.GetBookingIdFromGiffiId(giffiRef);
using (GiffiDBEntities dc = new GiffiDBEntities())
{
var results = (from b in dc.Bookings
where b.Id == bookingId
select b.BillToId);
if (results == null || results.Count() == 0)
{
return(null);
}
int billToId = results.First();
var cp = (from c in dc.Companies
where c.Id == billToId
select c);
return(cp.First());
}
}