// GET api/<controller>
public SelectItemsVM Get()
{
//Connet to db
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
SelectItemsVM locations = new SelectItemsVM();
locations.SelectItemsVMList = new List <SelectItemVM>();
// Get the customers
var oCust = from c in estEnt.Locations
orderby c.LocationName
select new { c.LocationName, c.LocationID };
locations.SelectItemsVMList.Add(new SelectItemVM {
Text = "-- Choose Location --", Value = "0"
});
foreach (var o in oCust)
{
locations.SelectItemsVMList.Add(new SelectItemVM {
Text = o.LocationName, Value = o.LocationID.ToString()
});
}
return(locations);
}
public IHttpActionResult GetCustomer(int id)
{
//Connet to db
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
var o = (from c in estEnt.Customers
where c.CustomerID == id
join b in estEnt.CustomerBalances on c.CustomerID equals b.CustomerID into joinedT
from b in joinedT.DefaultIfEmpty()
select new
{
City = c.City,
Email = c.Email,
FirstName = c.FirstName,
LastName = c.LastName,
Phone = c.Phone,
State = c.State,
Street1 = c.Street1,
Street2 = c.Street2,
Zip = c.Zip,
Amount = b.Balance == null ? 0 : (decimal)b.Balance
}).FirstOrDefault();
CustomerInfo oCustInfo = new CustomerInfo()
{
FirstName = o.FirstName, LastName = o.LastName, Street1 = o.Street1, Street2 = o.Street2, City = o.City, State = o.State, Zip = o.Zip, Phone = o.Phone, Email = o.Email, Balance = o.Amount
};
return(Ok(oCustInfo));
}
// GET api/<controller>
public SelectItemsVM GetAllCustomers()
{
//Connet to db
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
SelectItemsVM customers = new SelectItemsVM();
customers.SelectItemsVMList = new List <SelectItemVM>();
// Get the customers
var oCust = from c in estEnt.Customers
orderby c.LastName
select new { c.LastName, c.FirstName, c.CustomerID };
customers.SelectItemsVMList.Add(new SelectItemVM {
Text = "-- Choose Customer --", Value = "0"
});
foreach (var o in oCust)
{
customers.SelectItemsVMList.Add(new SelectItemVM {
Text = o.LastName + ", " + o.FirstName, Value = o.CustomerID.ToString()
});
}
return(customers);
}
public IHttpActionResult UpdateCustomerBalance(PurchaseData oPurch)
{//PurchaseData oPurch
//Connet to db
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
int newBalance = 0;
var oBal = (from b in estEnt.CustomerBalances
where b.CustomerID == oPurch.CustomerID
select b).FirstOrDefault();
if (oBal != null)
{
oBal.Balance += Convert.ToDecimal(oPurch.Amount);
newBalance = Convert.ToInt32(oBal.Balance);
oBal.DateUpdated = DateTime.Now;
// Insert Transaction
Transaction oTran = new Transaction()
{
AddedBy = "Admin", CustomerID = oPurch.CustomerID, Amount = oPurch.Amount, LocationID = oPurch.LocationID, TransactionDate = DateTime.Now
};
estEnt.Transactions.Add(oTran);
estEnt.SaveChanges();
}
else
{
CustomerBalance oCust = new CustomerBalance()
{
Balance = oPurch.Amount, CustomerID = oPurch.CustomerID, DateUpdated = DateTime.Now, UpdatedBy = "LR"
};
estEnt.CustomerBalances.Add(oCust);
Transaction oTran = new Transaction()
{
AddedBy = "Admin", CustomerID = oPurch.CustomerID, Amount = oPurch.Amount, LocationID = oPurch.LocationID, TransactionDate = DateTime.Now
};
estEnt.Transactions.Add(oTran);
newBalance = Convert.ToInt32(oPurch.Amount);
estEnt.SaveChanges();
}
return(Ok(newBalance));
}
// GET api/<controller>/5
public IHttpActionResult Get(int id)
{
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
CustomerDetail oCustInfo = new CustomerDetail();
// Get the customer info
var oC = (from c in estEnt.Customers
where c.CustomerID == id
join b in estEnt.CustomerBalances on c.CustomerID equals b.CustomerID into joinedT
from b in joinedT.DefaultIfEmpty()
select new
{
City = c.City,
Email = c.Email,
FirstName = c.FirstName,
LastName = c.LastName,
Phone = c.Phone,
State = c.State,
Street1 = c.Street1,
Street2 = c.Street2,
Zip = c.Zip,
Amount = b.Balance == null ? 0 : (decimal)b.Balance,
CustomerID = c.CustomerID
}).FirstOrDefault();
if (oC != null)
{
oCustInfo.City = oC.City;
oCustInfo.Email = oC.Email;
oCustInfo.FirstName = oC.FirstName;
oCustInfo.LastName = oC.LastName;
oCustInfo.Phone = oC.Phone;
oCustInfo.State = oC.State;
oCustInfo.Street1 = oC.Street1;
oCustInfo.Street2 = oC.Street2;
oCustInfo.Zip = oC.Zip;
}
return(Ok(oCustInfo));
}
// GET api/<controller>/5
public IHttpActionResult Get(int id)
{
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
LocationInfo oLocInfo = new LocationInfo();
var oLocs = (from l in estEnt.Locations
where l.LocationID == id
select new { l.LocationName, l.Street1, l.Street2, l.City, l.State, l.Zip }).FirstOrDefault();
if (oLocs != null)
{
oLocInfo.City = oLocs.City;
oLocInfo.LocationName = oLocs.LocationName;
oLocInfo.State = oLocs.State;
oLocInfo.Street1 = oLocs.Street1;
oLocInfo.Street2 = oLocs.Street2;
oLocInfo.Zip = oLocs.Zip;
}
return(Ok(oLocInfo));
}
// POST api/<controller>
public IHttpActionResult UpdateLocation(LocationInfo oLocInfo)
{
// Connect to db
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
int iReturnID = 0;
// Add a new location
if (oLocInfo.LocationID == 0)
{
Location oLoc = new Location()
{
City = oLocInfo.City, LocationName = oLocInfo.LocationName, State = oLocInfo.State, Street1 = oLocInfo.Street1, Street2 = oLocInfo.Street2, Zip = oLocInfo.Zip
};
estEnt.Locations.Add(oLoc);
estEnt.SaveChanges();
iReturnID = oLoc.LocationID;
}
else
{
// Update existing location
var oLoc = (from l in estEnt.Locations
where l.LocationID == oLocInfo.LocationID
select l).FirstOrDefault();
if (oLoc != null)
{
oLoc.City = oLocInfo.City;
oLoc.LocationName = oLocInfo.LocationName;
oLoc.State = oLocInfo.State;
oLoc.Street1 = oLocInfo.Street1;
oLoc.Street2 = oLocInfo.Street2;
oLoc.Zip = oLocInfo.Zip;
estEnt.SaveChanges();
iReturnID = oLoc.LocationID;
}
}
return(Ok(iReturnID));
}
// POST api/<controller>
public IHttpActionResult UpdateCustomerDetail(CustomerDetail oCustInfo)
{
EarthSkyTimeEntities1 estEntity = new EarthSkyTimeEntities1();
int iReturnID = 0;
if (oCustInfo.CustomerID == 0)
{
Customer oCust = new Customer()
{
AddedBy = "Admin",
City = oCustInfo.City,
FirstName = oCustInfo.FirstName,
LastName = oCustInfo.LastName,
Phone = oCustInfo.Phone,
State = oCustInfo.State,
Street1 = oCustInfo.Street1,
Street2 = oCustInfo.Street2,
Zip = oCustInfo.Zip,
Email = oCustInfo.Email
};
estEntity.Customers.Add(oCust);
estEntity.SaveChanges();
iReturnID = oCust.CustomerID;
// Setup their Balance information
CustomerBalance oBal = new CustomerBalance()
{
Balance = 0, CustomerID = oCust.CustomerID, DateUpdated = DateTime.Now, UpdatedBy = "Admin"
};
estEntity.CustomerBalances.Add(oBal);
estEntity.SaveChanges();
}
else
{
// Update the customer
var oC = (from c in estEntity.Customers
where c.CustomerID == oCustInfo.CustomerID
select c).FirstOrDefault();
if (oC != null)
{
oC.City = oCustInfo.City;
oC.FirstName = oCustInfo.FirstName;
oC.LastName = oCustInfo.LastName;
oC.Phone = oCustInfo.Phone;
oC.State = oCustInfo.State;
oC.Street1 = oCustInfo.Street1;
oC.Street2 = oCustInfo.Street2;
oC.Zip = oCustInfo.Zip;
oC.Email = oCustInfo.Email;
estEntity.SaveChanges();
iReturnID = oCustInfo.CustomerID;
}
}
return(Ok(iReturnID));
}
public CustomerTransactions Get(string dtTo, string dtFrom, string LocationID)
{
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
CustomerTransactions transactions = new CustomerTransactions();
transactions.CustomerTransactionList = new List <CustomerTransaction>();
// get all transactions
var oTrans = from t in estEnt.Transactions
join c in estEnt.Customers on t.CustomerID equals c.CustomerID
join l in estEnt.Locations on t.LocationID equals l.LocationID into joinedT
from l in joinedT.DefaultIfEmpty()
orderby t.TransactionDate descending
select new { t.Amount, t.TransactionDate, c.FirstName, c.LastName, l.LocationName, l.City, l.State, LocationID = l.LocationID == null ? 0 : l.LocationID };
if (dtFrom != "undefined" && dtFrom != "null")
{
DateTime dFrom = Convert.ToDateTime(dtFrom);
oTrans = oTrans.Where(m => m.TransactionDate >= dFrom);
}
if (dtTo != "undefined" && dtTo != "null")
{
DateTime dTo = Convert.ToDateTime(dtTo).AddDays(1);
oTrans = oTrans.Where(m => m.TransactionDate <= dTo);
}
if (LocationID != "undefined")
{
int iLocation = Convert.ToInt32(LocationID);
if (iLocation > 0)
{
oTrans = oTrans.Where(m => m.LocationID == iLocation);
}
}
foreach (var t1 in oTrans)
{
string sLoc = t1.LocationName + " - " + t1.City + ", " + t1.State;
if (t1.LocationName != null)
{
sLoc = t1.LocationName;
if (t1.City != "")
{
sLoc += " - " + t1.City;
}
if (t1.State != "")
{
sLoc += ", " + t1.State;
}
}
else
{
sLoc = "None";
}
transactions.CustomerTransactionList.Add(new CustomerTransaction {
Name = t1.LastName + ", " + t1.FirstName, Amount = Convert.ToDecimal(t1.Amount), Location = sLoc, DateUpdated = Convert.ToDateTime(t1.TransactionDate).ToShortDateString()
});
}
return(transactions);
}
public Balances ObtainBalances(string sortOrder = "")
{
EarthSkyTimeEntities1 estEnt = new EarthSkyTimeEntities1();
Balances balances = new Balances();
balances.BalanceList = new List <Balance>();
var oBal = from b in estEnt.CustomerBalances
join c in estEnt.Customers on b.CustomerID equals c.CustomerID
select new { c.FirstName, c.LastName, b.Balance, b.DateUpdated, c.Email, c.Phone };
if (sortOrder != "undefined")
{
if (sortOrder == "Balance Ascending")
{
oBal = oBal.OrderBy(m => m.Balance);
}
if (sortOrder == "Balance Descending")
{
oBal = oBal.OrderByDescending(m => m.Balance);
}
if (sortOrder == "Date Ascending")
{
oBal = oBal.OrderBy(m => m.DateUpdated);
}
if (sortOrder == "Date Descending")
{
oBal = oBal.OrderByDescending(m => m.DateUpdated);
}
if (sortOrder == "Name Ascending")
{
oBal = oBal.OrderBy(m => m.LastName); //.ThenBy(m => m.FirstName);
}
if (sortOrder == "Name Descending")
{
oBal = oBal.OrderByDescending(m => m.LastName); //.ThenByDescending(m => m.FirstName);
}
}
else
{
oBal = oBal.OrderByDescending(m => m.DateUpdated);
}
foreach (var b1 in oBal)
{
balances.BalanceList.Add(new Balance {
Name = b1.LastName + ", " + b1.FirstName, Amount = Convert.ToDecimal(b1.Balance), Email = b1.Email, DateUpdated = Convert.ToDateTime(b1.DateUpdated).ToShortDateString(), Phone = b1.Phone
});
}
return(balances);
}
