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