/* ==================================================================
   A transportation model in AMPL. 
   For further details see Fourer et al. pages 41=43.
   We will handle data differently, so ignore the discussion of data
   You will need to change the paths to the data in the table statements
   ================================================================== */

/* ------------------------------------------------------------------
   Sets index things. In the transportation model we have two sets of 
   interest: The set of origins or suppliers, which we will call ORIG 
   and the set of destinations or customers, which we will call DEST
   ------------------------------------------------------------------ */
set ORIG;
set DEST;

/* ------------------------------------------------------------------
   Parameters are place holders for known or calculated values. We are
   concerned with three kinds of data in the transportation model: The 
   available supply at each origin, which we will call supply; the 
   required demand at each destination, which we will call demand; and 
   the unit cost of transportation between each origin and destination, 
   which we will call cost. 

   Parameter definitions indicate how the parameters are related to or 
   indexed by the sets. There is a supply for each origin, a demand for 
   each destination and a cost for each (origin, destination) pair. 
   ------------------------------------------------------------------- */
param supply {ORIG};
param demand {DEST};
param cost   {ORIG, DEST};

table OriginTable IN "ODBC"
"D:\Personal\15057\TransportationData.mdb"
"Origins":
ORIG <- [Origin], supply~Supply;

read table OriginTable;

table DestinationTable IN "ODBC"
"D:\Personal\15057\TransportationData.mdb"
"Destinations":
DEST <- [Destination], demand~Demand;

read table DestinationTable;

table CostTable IN "ODBC"
"D:\Personal\15057\TransportationData.mdb"
"Cost":
[origin, destination], cost;

read table CostTable;

/* -------------------------------------------------------------------
   Variables are related to or indexed by sets as well. We have only one
   kind of variable in the transportation model: Trans, the quantity we 
   transport between each origin and destination. So, there is a Trans 
   variable for each (origin, destination) pair. These variables must be 
   non-negative
   -------------------------------------------------------------------- */
var Trans {ORIG, DEST} >= 0;

/* ---------------------------------------------------------------------
   With these pieces in place, we are ready to construct the model. We 
   start with the objective: minimize total_cost, which consists of all
   the transport costs incurred for shipments between all (origin,   
   destination) pairs.

   cost[o,d] is the unit cost to ship from origin o to destination d
   Trans[o,d] is the number of units we ship from o to d
   So, cost[o,d]*Trans[o,d] is the total cost we incur for shipments from
   o to d. 
   Summing these costs over all origins and destinations gives the total 
   cost.
   ---------------------------------------------------------------------- */
minimize Total_Cost: 
   sum{o in ORIG, d in DEST} cost[o,d]*Trans[o,d];

/* ----------------------------------------------------------------------
   Finally there are two kinds of constraints in the transportation model:
   Supply constraints that ensure we do not ship more from any origin than 
   is available there and Demand constraints that ensure we ship at least 
   as much to each destination as is required. 

   There is a Supply constraint for each origin. That's what 
   "s.t. Supply {o in ORIG}:" says. For each value of o, the constraint is
   sum{d in DEST} Trans[o,d] <= supply[o]
   There is a Demand constraint for each destination. That's what 
   "s.t. Demand {d in DEST}:" says. For each value of d, the constraint is
   sum{o in ORIG} Trans[o,d] >= demand[d]
   ----------------------------------------------------------------------- */
s.t. Supply {o in ORIG}:
   sum{d in DEST} Trans[o,d] <= supply[o];

s.t. Demand {d in DEST}:
   sum{o in ORIG} Trans[o,d] >= demand[d];


option solver cplex;
solve;

table TransOutTable OUT "ODBC"
"D:\Personal\15057\TransportationData.mdb"
"TransOut":
{origin in ORIG, destination in DEST: Trans[origin, destination] > 0} -> [origin, destination], Trans[origin, destination]~Trans;

write table TransOutTable;