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(); } }