//Query Daily Shipping public static List <DailyShippingDto> SearchDailyShipping(DateTime datetime) { var tomorrow = datetime.AddDays(1); var query = from inventory in db.Histories where inventory.ShippedDate >= datetime && inventory.ShippedDate < tomorrow orderby inventory.CreatedDate ascending, inventory.SalesOrder ascending group inventory by inventory.SalesOrder; var result = new List <DailyShippingDto>(); foreach (var inventory in query) { var record = inventory.FirstOrDefault(); Nullable <System.DateTime> CreatedDate = record.CreatedDate; string SO = record.SalesOrder; Dictionary <string, int> hash = new Dictionary <string, int>(); foreach (var item in inventory) { string ItemID = item.ModelNo; ModelInfo model = ModelInfoRepository.GetModelByNo(ItemID); string FG = model.Description; string Qty = inventory.Count().ToString(); string Pro = item.TrackingNo; string BillTo = item.BillTo; String CustPo = item.CustPoNo; string ShipVia = item.ShipVia; string ShipState = item.ShipState; string ShipCity = item.ShipCity; System.DateTime ShippedDate = item.ShippedDate; var shippingDto = new DailyShippingDto(); shippingDto.CreatedDate = CreatedDate; shippingDto.SO = SO; shippingDto.ItemID = item.ModelNo; //Match each SO Qty not each model Qty shippingDto.Qty = inventory.Count().ToString(); shippingDto.FG = FG; shippingDto.SN = item.SN; shippingDto.TrackingNo = Pro; shippingDto.ShipVia = ShipVia; shippingDto.BillTo = item.BillTo; shippingDto.CustPo = CustPo; shippingDto.ShipState = ShipState; shippingDto.ShipCity = ShipCity; shippingDto.ShippingDate = ShippedDate; result.Add(shippingDto); } var pallet = from Pallet in db.Pallets where Pallet.ShippedDate >= datetime && Pallet.SalesOrder.Equals(SO) select Pallet; foreach (var item in pallet) { var shippingDto2 = new DailyShippingDto(); shippingDto2.CreatedDate = item.CreatedDate; shippingDto2.SO = item.SalesOrder; shippingDto2.ItemID = item.ItemID; shippingDto2.FG = item.FG; shippingDto2.Qty = item.Qty.ToString(); shippingDto2.TrackingNo = item.TrackingNo; shippingDto2.BillTo = item.BillTo; shippingDto2.CustPo = item.CustPoNo; shippingDto2.ShipVia = item.ShipVia; shippingDto2.ShipState = item.ShipState; shippingDto2.ShipCity = item.ShipCity; shippingDto2.ShippingDate = item.ShippedDate; result.Add(shippingDto2); } } //List<DailyShippingDto> SortedList = result.OrderByDescending(o => o.CreatedDate).ToList(); return(result); }
//Get Model Data from PeachTree public static List <ModelInfo> getModelInfo() { List <ModelInfo> data = ModelInfoRepository.GetAllModel(); List <ModelInfo> result = null; if (data.Count == 0) { String connString = System.Configuration.ConfigurationManager.ConnectionStrings["PeachreeSNOConnectionString"].ToString(); DataTable dt = new DataTable(); OdbcConnection cn; //= new OdbcConnection(connString); dt = new DataTable(); //reset //Peachtree using (cn = new OdbcConnection(connString)) { string sqlCheck = @" SELECT ItemID, ItemDescription FROM LineItem WHERE (CHAR_LENGTH(ItemID) = 6) "; OdbcCommand sqlCmd = new OdbcCommand(sqlCheck, cn); // sqlCmd.Parameters.Add("?", OdbcType.VarChar).Value = (rec.ServiceId.ToString() + "-" + rec.OrderId.ToString()); OdbcDataAdapter adapter = new OdbcDataAdapter(sqlCmd); adapter.Fill(dt); } var models = new List <ModelInfo>(); foreach (DataRow row in dt.Rows) { var arr = row.ItemArray.Select(i => i.ToString()).ToArray(); var model = new ModelInfo(); model.ModelNo = arr[0]; model.Description = arr[1]; if (model.ModelNo.Equals("115816")) { model.Description = "INTEGRA T500"; } else if (model.ModelNo.Equals("450887")) { model.Description = "Treadmill Xterra TR150"; } models.Add(model); } result = ModelInfoRepository.InsertModel(models); cn.Close(); } else { result = ModelInfoRepository.GetAllModel(); } return(result); }