private List<Product> ImportProducts(string localFileName)
{
var result = new List<Product>();
try
{
var lines = System.IO.File.ReadAllLines(localFileName);
System.Text.RegularExpressions.Regex csvParser = new System.Text.RegularExpressions.Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
string[] headerColumns = csvParser.Split(lines.FirstOrDefault());
Dictionary<string, int> columnIndices = GetColumnIndices(headerColumns);
var imageIndices = columnIndices.Where(x => x.Key.Contains("Image")).Select(x => x.Value);
foreach (var line in lines.Skip(1))
{
String[] values = csvParser.Split(line);
// clean up the fields (remove " and leading spaces)
for (int i = 0; i < values.Length; i++)
{
values[i] = values[i].TrimStart(' ', '"');
values[i] = values[i].TrimEnd('"');
}
var product = new Product();
product.Stock_No = Convert.ToInt32(values[columnIndices["STOCK No"]]);
product.chassis_no_1 = values[columnIndices["CHASSIS No 1"]];
product.chassis_no_2 = values[columnIndices["CHASSIS No 2"]];
product.Maker = values[columnIndices["MARCA"]];
product.ProductType = values[columnIndices["TYPE"]];
product.Model = values[columnIndices["NAME OF CAR"]];
product.Grade = values[columnIndices["GRADE"]];
product.Year = values[columnIndices["YEAR"]] == ""? (int?)null : Convert.ToInt32(values[columnIndices["YEAR"]]);
product.Month = values[columnIndices["MONTH"]];
product.ETD = values[columnIndices["ETD"]];
product.Color = values[columnIndices["COLOR"]];
product.KM_ran = values[columnIndices["KM"]] == "" ? (int?)null : Convert.ToInt32(values[columnIndices["KM"]].Replace(",", ""));
product.Fuel = values[columnIndices["FUEL"]];
product.Gear_m_at = values[columnIndices["GEAR(A/T)"]];
product.CC = values[columnIndices["CC"]] == "" ? (int?)null : Convert.ToInt32(values[columnIndices["CC"]]);
product.Price = Convert.ToDecimal(values[columnIndices["SELLING PRICE"]].Replace(",",""));
product.Images = (from imageIndex in imageIndices where values[imageIndex] != "" select values[imageIndex]).ToArray();
product.Equipments = new Equipment();
product.Equipments.AC = values[columnIndices["EQUIPMENT3(A/C)"]] != "" ? true : false;
product.Equipments.PS = values[columnIndices["EQUIPMENT2(P/S)"]] != "" ? true : false;
product.Equipments.PW = values[columnIndices["EQUIPMENT1(P/W)"]] != "" ? true : false;
product.Equipments.WCAB = values[columnIndices["W CAB"]] != "" ? true : false;
product.Equipments.FourWheelDrive = values[columnIndices["4 WD"]] != "" ? true : false;
var doors = columnIndices.Where(x => x.Key.Contains("DOOR")).Where(y => values[y.Value] != "").Select(x => x.Key.Split(' ')[0]).FirstOrDefault();
product.NoOfDoors = doors == "" || doors == null ? (int?)null : Convert.ToInt32(doors);
result.Add(product);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.IO.File.Delete(localFileName);
}
return result;
}
public object GetProducts(int pageIndex = 0,int pageSize = 10)
{
try
{
AaauctionEntities db = new AaauctionEntities();
var products = db.vehicle_for_sale.Where(x => x.is_sold == false).OrderByDescending(x => x.last_modified_date).Select(x => x);
var productQuery = (from product in products.Skip(pageIndex * pageSize).Take(pageSize)
join model in db.models on product.model_id equals model.id
into models
from mod in models.DefaultIfEmpty()
join make in db.makes on mod.make_id equals make.makeid
into makes
from mak in makes.DefaultIfEmpty()
join img in db.images_on_vehicles_for_sale on product.stock_no equals img.stock_no
into images
from img in images.DefaultIfEmpty()
join feature in db.features_on_vehicles_for_sale on product.stock_no equals feature.stock_no
into featJoin
from fj in featJoin.DefaultIfEmpty()
join ft in db.vehicle_features
on fj.featureid equals ft.featureid
into featureJoin
from f in featureJoin.DefaultIfEmpty()
group new { Product = product, model = mod, make = mak, Image = img, Feature = f } by product.stock_no into grp
select grp
).ToList();
List<Product> productsFromDB = new List<Product>();
foreach (var item in productQuery)
{
var productItem = item.FirstOrDefault().Product;
var product = new Product();
product.Currency = "USD";
product.Stock_No = productItem.stock_no;
product.Year = productItem.year;
product.Month = productItem.month;
product.KM_ran = productItem.KM_ran;
//var model = db.models.Where(x => x.id == productItem.model_id).FirstOrDefault();
product.Model = item.FirstOrDefault().model.model_name;// model.model_name;
product.Maker = item.FirstOrDefault().make.make_name;//db.makes.Where(x => x.makeid == model.make_id).Select(y => y.make_name).FirstOrDefault();
product.Price = productItem.price.HasValue == true ? productItem.price.Value : 0;
product.CC = productItem.CC;
product.chassis_no_1 = productItem.chassis_no_1;
product.chassis_no_2 = productItem.chassis_no_2;
product.Color = productItem.color;
product.Fuel = productItem.fuel;
product.Gear_m_at = productItem.gear_at.HasValue == true && productItem.gear_at.Value == true ? "A/T" : "Manual";
product.LastModifiedDate = productItem.last_modified_date.HasValue == true ? productItem.last_modified_date.Value.ToShortDateString() : "";
product.Images = item.Where(fI => fI.Image != null).OrderBy(o => o.Image.imageid).GroupBy(x => x.Image.imageurl).Select(y => y.Max(z => z.Image.imageurl)).ToArray();
var features = item.Where(fI => fI.Feature != null).GroupBy(x => x.Feature.feature_name).Select(y => y.Key).ToList();
product.Equipments = new Equipment();
foreach (var feature in features)
{
switch (feature)
{
case "A/C":
product.Equipments.AC = true;
break;
case "PS":
product.Equipments.PS = true;
break;
case "PW":
product.Equipments.PW = true;
break;
case "4WD":
product.Equipments.FourWheelDrive = true;
break;
case "WCAB":
product.Equipments.WCAB = true;
break;
}
}
productsFromDB.Add(product);
}
ProductsResponse response = new ProductsResponse() { products = productsFromDB.ToArray(), count = products.Count() };
return Request.CreateResponse<ProductsResponse>(HttpStatusCode.OK, response);
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
}
}