public List<Saleswarehouse> MakeSalesWarehouse(string inputStartTime, string inputEndTime)
{
try
{
using (var db = new DatabaseEngine(_username, _password, _host, _port, _database))
{
var startTime = (double)db.ExecuteSQLScalar(string.Format("SELECT extract('epoch' from timestamp '{0} 00:00:00') as time;", inputStartTime));
var endTime = (double)db.ExecuteSQLScalar(string.Format("SELECT extract('epoch' from timestamp '{0} 23:59:59') as time;", inputEndTime));
var ticketsdata = db.GetTickets(Locations: null, starttime: startTime, endtime: endTime);
var salesdata = db.GetSales(Locations: null, starttime: startTime, endtime: endTime);
var discountsdata = db.GetDiscounts(Locations: null, starttime: startTime, endtime: endTime, deleted: 0);
var productcategories = db.GetProductCategories();
var Products = db.GetProducts();
var Vendors = db.GetVendors();
var Inventory = db.GetInventory();
var ticketitemcounts = from sale in salesdata
group sale by sale.Ticketid into x
select new
{
ticketid = x.Key,
itemcount = x.Count<Sales>()
};
var SalesWarehouseList = new List<Saleswarehouse>();
foreach (var sale in salesdata)
{
var row = new Saleswarehouse();
row.Datetime = sale.Datetime;
row.Location = sale.Location;
row.Refunded = sale.Refunded;
row.Id = sale.Id;
row.Ticketid = sale.Ticketid;
row.Productid = sale.Productid;
row.Weight = sale.Weight;
row.Manualweigh = sale.Manualweigh;
row.Ismedicated = sale.Ismedicated;
row.Price = sale.Price;
row.Taxpercentage = sale.Tax;
row.Employee = ticketsdata.Where<Tickets>(x => x.Id == row.Ticketid).Single<Tickets>().Userid;
row.Customerid = ticketsdata.Where<Tickets>(x => x.Id == row.Ticketid).Single<Tickets>().Customerid;
row.Inventoryid = sale.Inventoryid;
row.Uniqueloadid = sale.Id.ToString();
var salesdiscounts = discountsdata.Where<Discounts>(x => x.Ticketid == row.Ticketid);
foreach (var disc in salesdiscounts)
{
if (disc.Saleid == null)
{
if (disc.Istaxable == 1)
{
row.Disctaxable += disc.Amount.HasValue ? (double?)Math.Round(disc.Amount.Value, 2) : null;
}
else
{
row.Discnottaxable += disc.Amount.HasValue ? (double?)Math.Round(disc.Amount.Value, 2) : null;
}
}
else
{
if (disc.Istaxable == 1)
{
if (disc.Amount.HasValue)
{
row.Disctaxable += Math.Round(disc.Amount.Value / ticketitemcounts.Where(x => x.ticketid == row.Ticketid).Single().itemcount, 2);
}
else
{
row.Disctaxable += 0.00;
}
}
else
{
if (disc.Amount.HasValue)
{
row.Discnottaxable += Math.Round(disc.Amount.Value / ticketitemcounts.Where(x => x.ticketid == row.Ticketid).Single().itemcount, 2);
}
else
{
row.Discnottaxable += 0.00;
}
}
}
}
var priceValue = row.Price.HasValue ? row.Price.Value : 0.00;
var discNotTaxableValue = row.Discnottaxable.HasValue ? row.Discnottaxable.Value : 0.00;
var taxPercentageValue = row.Taxpercentage.HasValue ? row.Taxpercentage.Value : 0.00;
row.Taxdollars = Math.Round((priceValue - discNotTaxableValue) * taxPercentageValue, 2);
row.Productcategory = Products.Where(x => x.Id == row.Productid).Single().Productcategory;
row.Taxcategory = Products.Where(x => x.Id == row.Productid).Single().Taxcategory;
if (row.Inventoryid != null)
{
row.Vendorid = Inventory.Where(x => x.Id == row.Inventoryid).Single().Vendorid;
}
else
{
row.Vendorid = null;
}
row.Cost = double.Parse((string)db.ExecuteSQLScalar(string.Format("SELECT func_getcostperunit('{0}', 0) as cost;", row.Inventoryid))) * row.Weight.Value;
row.Date = Functions.ConvertFromUnixTimestamp(row.Datetime.Value).ToShortDateString();
row.Time = Functions.ConvertFromUnixTimestamp(row.Datetime.Value).ToShortTimeString();
SalesWarehouseList.Add(row);
}
return SalesWarehouseList;
}
}
catch (Exception e)
{
throw e;
}
}
private static void SyncFoundation()
{
var warehouse = new WarehouseEngine(programConfig.warehouse_username, programConfig.warehouse_password, programConfig.warehouse_host, programConfig.warehouse_port);
var database = new DatabaseEngine(programConfig.username, programConfig.password, programConfig.host, programConfig.port, programConfig.database);
using (var wh_session = warehouse.GetSession())
{
// products
var products = database.GetProducts();
foreach (var product in products)
{
var wh_product = (wh_products)product;
wh_session.Replicate(wh_product, replicationMode: NHibernate.ReplicationMode.Overwrite);
}
wh_session.Flush();
// product categories
var productCategories = database.GetProductCategories();
foreach (var category in productCategories)
{
var wh_productcategory = (wh_productcategory)category;
wh_session.Replicate(wh_productcategory, replicationMode: NHibernate.ReplicationMode.Overwrite);
}
wh_session.Flush();
// locations
var locations = database.GetLocations();
foreach (var location in locations)
{
var wh_location = (wh_location)location;
wh_session.Replicate(wh_location, replicationMode: NHibernate.ReplicationMode.Overwrite);
}
wh_session.Flush();
// vendors
var vendors = database.GetVendors();
foreach (var vendor in vendors)
{
var wh_vendor = (wh_Vendors)vendor;
wh_session.Replicate(wh_vendor, replicationMode: NHibernate.ReplicationMode.Overwrite);
}
wh_session.Flush();
// customers
var customers = database.GetCustomers();
foreach (var customer in customers)
{
var wh_customer = (wh_customers)customer;
wh_session.Replicate(wh_customer, replicationMode: NHibernate.ReplicationMode.Overwrite);
}
wh_session.Flush();
}
}
