private void bindGrid()
{
BusinessObjects.Rates.Rate r1 = new BusinessObjects.Rates.Rate();
List<BusinessObjects.Rates.Rate> L = new List<BusinessObjects.Rates.Rate>();
r1.Shipper = ddContract.SelectedText;
r1.Origin = ddOrigin.SelectedText;
r1.Destination = ddDestination.SelectedText;
r1.Commodity = ddCommodity.SelectedText;
r1.ContainerType = ddCommodity.SelectedText;
r1.DollarValue = 456.00;
L.Add(r1);
GridView1.DataSource = L;
GridView1.DataBind();
}
public static List<BusinessObjects.Rates.Rate> GetRates(string origin, string destination, DateTime shipdate,
int? contractid, int? containertype, int? commodityType)
{
string sql = @"select contracts.ContractNumber,shppr.Name Shipper, orig.Name [from], dest.Name [to], cntr1.Name container,
zones.Name zone, map.Zipcode,coms1.Name commodity, rates1.BaseRate,rates1.EffectiveDate,rates1.ExpirationDate
from dbo.ShippingRates rates1
join dbo.ShippingContracts contracts on contracts.ContractID=rates1.ContractID
join dbo.Shippers shppr on shppr.ShipperID=contracts.ShipperID
join dbo.ShippingRoutes rout on rout.RouteID=rates1.RouteID
join dbo.PortsAndPlaces orig on orig.ID=rout.Origin
join dbo.PortsAndPlaces dest on dest.ID=rout.Destination
join dbo.ContainerTypes cntr1 on cntr1.ID = rates1.ContainerTypeID
left join dbo.CommodityTypes coms1 on coms1.CommodityTypeID=rates1.CommodityTypeID
left join dbo.ShippingZipcodeZones zones on zones.ZoneID=rates1.ZoneID
left join dbo.ShippingZoneMap map on map.ZoneID=zones.ZoneID";
string where = @" where (orig.Name=@origin or orig.Code=@origin or map.Zipcode=@origin)
and (dest.Name=@destination or dest.Code=@destination)
and (rates1.EffectiveDate <= @shipdt and rates1.ExpirationDate>=@shipdt)";
if (contractid.HasValue){where+=" and (rates1.ContractID=@contratid)";}
if (commodityType.HasValue) { where += @" and (coms1.CommodityTypeID=@commodity or coms1.CommodityTypeID is null)"; }
if (containertype.HasValue){where+=" and (cntr1.ID=@containertype)";}
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(sql + where + "order by commodity, container", Properties.Settings.Default.CompassSQL);
adp.SelectCommand.Parameters.Add(new SqlParameter("@origin", origin));
adp.SelectCommand.Parameters.Add(new SqlParameter("@destination", destination));
adp.SelectCommand.Parameters.Add(new SqlParameter("@shipdt", shipdate));
if (contractid.HasValue) { adp.SelectCommand.Parameters.Add(new SqlParameter("@contratid", contractid.Value)); }
if (commodityType.HasValue) { adp.SelectCommand.Parameters.Add(new SqlParameter("@commodity", commodityType.Value)); }
if (containertype.HasValue) { adp.SelectCommand.Parameters.Add(new SqlParameter("@containertype", containertype.Value)); }
adp.Fill(dt);
if (dt.Rows.Count == 0)
{//TODO: Ask if cross contracts are allowed?
sql= @"select distinct contracts1.ContractNumber,shppr1.Name Shipper, orig.Name [from],via.Name via, dest.Name [to],
cntr1.Name container, zones1.Name zone, map.Zipcode
,case when coms1.Name is null then coms2.Name else coms1.Name end Commodity
,rates1.BaseRate + rates2.BaseRate [BaseRate]
,case when rates1.EffectiveDate>rates2.EffectiveDate then rates1.EffectiveDate else rates2.EffectiveDate end EffectiveDate
,case when rates1.ExpirationDate>rates2.ExpirationDate then rates2.ExpirationDate else rates1.ExpirationDate end ExpirationDate
from dbo.ShippingRates rates1
join dbo.ShippingContracts contracts1 on contracts1.ContractID=rates1.ContractID
join dbo.Shippers shppr1 on shppr1.ShipperID=contracts1.ShipperID
join dbo.ShippingRoutes rout1 on rout1.RouteID=rates1.RouteID
join dbo.PortsAndPlaces orig on orig.ID=rout1.Origin
join dbo.ContainerTypes cntr1 on cntr1.ID = rates1.ContainerTypeID
join dbo.ShippingRoutes rout2 on rout2.Origin=rout1.Destination
join dbo.ShippingRates rates2 on rates2.RouteID=rout2.RouteID and (rates2.EffectiveDate <= @shipdt and rates2.ExpirationDate>=@shipdt)
join dbo.PortsAndPlaces via on via.ID=rout2.Origin
join dbo.PortsAndPlaces dest on dest.ID=rout2.Destination
join dbo.ContainerTypes cntr2 on cntr2.ID = rates2.ContainerTypeID and cntr1.ID=cntr2.ID
left join dbo.CommodityTypes coms1 on coms1.CommodityTypeID=rates1.CommodityTypeID
left join dbo.CommodityTypes coms2 on coms2.CommodityTypeID=rates2.CommodityTypeID
left join dbo.ShippingZipcodeZones zones1 on zones1.ZoneID=rates1.ZoneID
left join dbo.ShippingZoneMap map on map.ZoneID=zones1.ZoneID";
if (commodityType.HasValue) { where += " and (coms2.CommodityTypeID=@commodity or coms2.CommodityTypeID is null)";}
if (containertype.HasValue) {where += " and (cntr2.ID=@containertype)";}
adp.SelectCommand.CommandText = sql + where +"order by commodity, container";
adp.Fill(dt);
}
List<BusinessObjects.Rates.Rate> rts = new List<BusinessObjects.Rates.Rate>();
foreach (DataRow row in dt.Rows)
{
BusinessObjects.Rates.Rate r = new BusinessObjects.Rates.Rate();
r.Commodity = row["commodity"].ToString();
r.ContainerType = row["container"].ToString();
r.ContractNumber = row["ContractNumber"].ToString();
r.Destination = row["to"].ToString();
r.DollarValue = double.Parse( row["BaseRate"].ToString());
r.Origin = row["from"].ToString();
r.Shipper = row["Shipper"].ToString();
rts.Add(r);
}
return rts;
}
