public ActionResult Download(string date)
{
List <ReportPerMesin> result = new List <ReportPerMesin>();
DateTime selectedDate = Convert.ToDateTime(date);
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from daily in db.tt_Daily
join record in db.tm_Recorder on daily.Recorder_FK equals record.Recorder_PK
join trans in db.tt_Transaction on daily.Daily_PK equals trans.Daily_FK into gj
from subpet in gj.DefaultIfEmpty()
join mesin in db.tm_Mesin on subpet.Mesin_FK equals mesin.Mesin_PK
join kw in db.tm_KodeWarna on subpet.KodeWarna_FK equals kw.KodeWarna_PK
where daily.Status_FK == 1 && DbFunctions.TruncateTime(daily.Date) == selectedDate.Date
select new ReportPerMesin
{
NoMesin = mesin.KodeMesin,
KodeWarna = kw.KodeWarna,
HasilKain = db.tt_TransactionDetail.Where(t => t.Transaction_FK == subpet.Transaction_PK && t.Status_FK == 1 && subpet.Status_FK == 1).Sum(i => (Double?)i.HasilKain) ?? 0,
Penambahan = subpet.Status_FK == 1 ? subpet.Penambahan ?? 0 : 0,
TotalKain = db.tt_TransactionDetail.Where(t => t.Transaction_FK == subpet.Transaction_PK && t.Status_FK == 1 && subpet.Status_FK == 1).Sum(i => (Double?)i.HasilKain) + subpet.Penambahan ?? 0
}).GroupBy(l => new
{
l.NoMesin,
l.KodeWarna
})
.Select(cl => new ReportPerMesin
{
NoMesin = cl.FirstOrDefault().NoMesin,
KodeWarna = cl.FirstOrDefault().KodeWarna,
HasilKain = cl.Sum(x => x.HasilKain),
Penambahan = cl.Sum(x => x.Penambahan),
TotalKain = cl.Sum(x => x.TotalKain)
}).ToList();
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "Report");
IXLWorksheet ws = wb.Worksheet(1);
ws.Columns().AdjustToContents();
string myName = Server.UrlEncode("ReportPerMesin" + date + ".xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}
public ActionResult DownloadWaving(ReportProperty rp)
{
List <ReportListWaving> result = new List <ReportListWaving>();
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from td in db.tt_TransactionDetail
join t in db.tt_Transaction on td.Transaction_FK equals t.Transaction_PK
join mesin in db.tm_Mesin on t.Mesin_FK equals mesin.Mesin_PK
join statmesin in db.tm_StatusMesin on mesin.StatusMesin_FK equals statmesin.StatusMesin_PK
join daily in db.tt_Daily on t.Daily_FK equals daily.Daily_PK
join kodewarna in db.tm_KodeWarna on t.KodeWarna_FK equals kodewarna.KodeWarna_PK
join op in db.tm_Operator on td.Operator_FK equals op.Operator_PK
where daily.Status_FK == 1 && t.Status_FK == 1 && td.Status_FK == 1 && daily.Date >= rp.startdate && daily.Date <= rp.enddate
select new ReportListWaving
{
NoOperator = op.NoOperator.ToString(),
NIP = op.NIP,
Nama = op.Nama,
HasilKain = td.HasilKain,
Total = Math.Round(kodewarna.Pick * statmesin.Nilai * td.HasilKain, 2)
}).GroupBy(l => l.NoOperator)
.Select(cl => new ReportListWaving
{
NoOperator = cl.FirstOrDefault().NoOperator,
NIP = cl.FirstOrDefault().NIP,
Nama = cl.FirstOrDefault().Nama,
HasilKain = cl.Sum(x => x.HasilKain),
Total = cl.Sum(x => x.Total)
}).ToList();
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "GajiWaving");
IXLWorksheet ws = wb.Worksheet(1);
string myName = Server.UrlEncode("GajiWaving.xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}
public ActionResult DownloadInspect(ReportProperty rp)
{
List <ReportListWaving> result = new List <ReportListWaving>();
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from t in db.tt_Transaction
join daily in db.tt_Daily on t.Daily_FK equals daily.Daily_PK
join rc in db.tm_Recorder on daily.Recorder_FK equals rc.Recorder_PK
where daily.Status_FK == 1 && t.Status_FK == 1 && daily.Date >= rp.startdate && daily.Date <= rp.enddate
select new ReportListWaving
{
NoOperator = rc.NoRecorder.ToString(),
NIP = rc.NIP,
Nama = rc.Nama,
HasilKain = db.tt_TransactionDetail.Where(x => x.Transaction_FK == t.Transaction_PK).Sum(i => ((Double?)i.HasilKain) ?? 0) + (t.Penambahan ?? 0.0)
}).GroupBy(l => l.NoOperator)
.Select(cl => new ReportListWaving
{
NoOperator = cl.FirstOrDefault().NoOperator,
NIP = cl.FirstOrDefault().NIP,
Nama = cl.FirstOrDefault().Nama,
HasilKain = cl.Sum(x => x.HasilKain),
}).ToList();
foreach (var item in result)
{
item.Total = item.HasilKain * 25;
}
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "GajiInspect");
IXLWorksheet ws = wb.Worksheet(1);
string myName = Server.UrlEncode("GajiInspect.xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}
public CarMaxVehicle LoadDetailCar(XmlNode node)
{
var newVehicle = new CarMaxVehicle();
try
{
var apiUrlNode = node.SelectSingleNode("*[local-name()='links']/*[local-name()='resourcelink']/*[local-name()='href']");
if (apiUrlNode == null)
{
return(null);
}
newVehicle.CarMaxVehicleId = Convert.ToInt32(Path.GetFileName(apiUrlNode.InnerText));
newVehicle.Url = string.Format("https://www.carmax.com/cars/{0}", newVehicle.CarMaxVehicleId);
newVehicle.Year = Convert.ToInt32(node.SelectSingleNode("*[local-name()='year']").InnerText);
newVehicle.Make = node.SelectSingleNode("*[local-name()='make']").InnerText;
newVehicle.Model = node.SelectSingleNode("*[local-name()='model']").InnerText;
newVehicle.Price = Convert.ToInt32(node.SelectSingleNode("*[local-name()='price']").InnerText);
newVehicle.Miles = Convert.ToInt32(node.SelectSingleNode("*[local-name()='miles']").InnerText.Replace("K", "000"));
newVehicle.ExteriorColor = node.SelectSingleNode("*[local-name()='exteriorcolor']").InnerText;
newVehicle.InteriorColor = node.SelectSingleNode("*[local-name()='interiorcolor']").InnerText;
newVehicle.Used = node.SelectSingleNode("*[local-name()='isnew']").InnerText.Equals("false");
newVehicle.FullPhotos = node.SelectSingleNode("*[local-name()='photourl']").InnerText;
newVehicle.ThumbnailPhotos = newVehicle.FullPhotos;
newVehicle.Stock = node.SelectSingleNode("*[local-name()='stocknumber']").InnerText;
newVehicle.DriveTrain = node.SelectSingleNode("*[local-name()='drivetrain']").InnerText;
newVehicle.Transmission = node.SelectSingleNode("*[local-name()='transmission']").InnerText;
newVehicle.Vin = node.SelectSingleNode("*[local-name()='vin']").InnerText;
var carmaxStoreId = Convert.ToInt32(node.SelectSingleNode("*[local-name()='storeid']").InnerText);
newVehicle.StoreId = (new VINMarketSqlHelper()).GetId("SELECT StoreId FROM CarMaxStore WHERE CarMaxStoreId = " + carmaxStoreId);
newVehicle.UpdatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
newVehicle.CreatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
}
catch (Exception ex)
{
throw;
}
return(newVehicle);
}
private CarMaxStoreViewModel GetStoreDetail(XmlNode node)
{
try
{
var fullNameNode = node.SelectSingleNode(".//h4[@class='store-locator--result--name']/a");
var fullName = fullNameNode.InnerText.Trim();
var url = "https://www.carmax.com" + fullNameNode.Attributes["href"].Value;
var name = node.SelectSingleNode("./div[@class='store-locator--result--header']") != null?node.SelectSingleNode("./div[@class='store-locator--result--header']").InnerText.TrimEnd() : fullName;
var address = node.SelectSingleNode(".//*[@data-react='GetDirectionsButton']/*[@data-scope='store']/*[@data-prop='Street']").InnerText;
var city = node.SelectSingleNode(".//*[@data-react='GetDirectionsButton']/*[@data-scope='store']/*[@data-prop='City']").InnerText;
var stateStr = node.SelectSingleNode(".//*[@data-react='GetDirectionsButton']/*[@data-scope='store']/*[@data-prop='State']").InnerText;
var zip = Convert.ToInt32(node.SelectSingleNode(".//*[@data-react='GetDirectionsButton']/*[@data-scope='store']/*[@data-prop='ZipCode']").InnerText);
var phone = node.SelectSingleNode(".//span[@class='store-locator--result--phone unclickable']").InnerText.Trim();
var id = Convert.ToInt64(Path.GetFileName(url));
var newStore = new CarMaxStoreViewModel()
{
CarMaxStoreId = id,
Name = name,
FullName = fullName,
Url = url,
Address = address,
City = city,
State = stateStr,
ZipCode = zip,
Phone = phone,
CreatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now),
UpdatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now)
};
return(newStore);
}
catch (Exception ex)
{
throw;
}
}
public CarMaxVehicle LoadDetailCarNew(CarMaxVehicle newVehicle)
{
//imageUrl = https://img2.carmax.com/api/images/12702986
var content = WebHandler.DownloadContent("https://beta.carmax.com/cars/" + newVehicle.CarMaxVehicleId);
var xmlDocument = WebHandler.DownloadDocument(content);
var vinNode = xmlDocument.SelectSingleNode("//*[@class='card-grid-section--action-bar-item'][2]/span[2]");
if (vinNode == null)
{
return(null);
}
newVehicle.Vin = vinNode.InnerText.Trim();
var infoNodes = xmlDocument.SelectNodes("//div[@class='card--text-block-list--row']");
if (infoNodes != null)
{
foreach (XmlNode item in infoNodes)
{
if (item.ChildNodes[1] == null || item.ChildNodes[3] == null)
{
continue;
}
var text = item.ChildNodes[1].InnerText.Trim();
var value = item.ChildNodes[3].InnerText.Trim();
switch (text)
{
case "Drive":
newVehicle.DriveTrain = value;
break;
case "Transmission":
newVehicle.Transmission = value;
break;
case "Exterior":
newVehicle.ExteriorColor = value;
break;
case "Interior":
newVehicle.InteriorColor = value;
break;
}
}
}
var featureNodes = xmlDocument.SelectNodes("//ul/li[@class='card-grid-section--item-list-element']");
if (featureNodes != null)
{
var list = new List <string>();
foreach (XmlNode item in featureNodes)
{
list.Add(item.InnerText);
}
newVehicle.Features = string.Join(",", list);
}
var storeNode = xmlDocument.SelectSingleNode("//*[@id='qualifiers-global']");
if (storeNode != null)
{
var jsonObj = (JObject)JsonConvert.DeserializeObject(storeNode.Attributes["data-qualifiers"].Value);
var carmaxStoreId = Convert.ToInt32(((JValue)jsonObj["StoreId"]).Value);
newVehicle.StoreId = (new VINMarketSqlHelper()).GetId("SELECT StoreId FROM CarMaxStore WHERE CarMaxStoreId = " + carmaxStoreId);
}
var stockNode = xmlDocument.SelectSingleNode("//*[@class='card-grid-section--action-bar-item'][1]/span[2]");
if (stockNode != null)
{
newVehicle.Stock = stockNode.InnerText.Trim();
}
var mpgCityNode = xmlDocument.SelectSingleNode("//*[@class='mpg']/*[@class='mpg--value']");
if (mpgCityNode != null)
{
newVehicle.MPGCity = Convert.ToInt32(mpgCityNode.InnerText.Trim());
}
var mpgHighway = xmlDocument.SelectSingleNode("//*[@class='mpg'][2]/*[@class='mpg--value']");
if (mpgHighway != null)
{
newVehicle.MPGHighway = Convert.ToInt32(mpgHighway.InnerText.Trim());
}
var priceNode = xmlDocument.SelectSingleNode("//*[@class='price-mileage--price-container']/*[@class='price-mileage--value']");
if (priceNode != null)
{
newVehicle.Price = CommonHelper.RemoveSpecialCharactersAndReturnNumber(priceNode.InnerText.Trim());
}
var milesNode = xmlDocument.SelectSingleNode("//*[@class='price-mileage--mileage-container']/*[@class='price-mileage--value']");
if (milesNode != null)
{
newVehicle.Miles = Convert.ToInt32(milesNode.InnerText.Trim().ToLower().Replace("k", "000").Replace("<", "").Replace(">", ""));
}
newVehicle.Used = newVehicle.Miles > 1000;
newVehicle.UpdatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
newVehicle.CreatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
return(newVehicle);
}
public CarMaxVehicle LoadDetailCar(int year, string make, string model, string trim, long carId, string url)
{
var newVehicle = new CarMaxVehicle()
{
CarMaxVehicleId = carId, Year = year, Make = make, Model = model, Trim = trim, Url = url
};
try
{
var content = WebHandler.DownloadContent(url);
var xmlDocument = WebHandler.DownloadDocument(content);
var vinNode = xmlDocument.SelectSingleNode("//*[@class='vin']");
if (vinNode == null)
{
return(LoadDetailCarNew(newVehicle));
}
newVehicle.Vin = vinNode.ChildNodes[1].Value.Trim();
newVehicle.Price = CommonHelper.RemoveSpecialCharactersAndReturnNumber(xmlDocument.SelectSingleNode("//*[@class='sansser']/*").ChildNodes[0].Value.Trim());
//var certifiedNode = xmlDocument.SelectSingleNode("//*[@class='info-container cqc_tombstone_pos']/*[last()]/*/*/*/*/*[last()-1]/*[last()]");
//newVehicle.Certified = certifiedNode != null && certifiedNode.InnerText.Contains("CarMax Quality Certified");
var infoNodes = xmlDocument.SelectNodes("//*[@class='info-container cqc_tombstone_pos']/*[last()]/*/*/*/*[last()]/*");
if (infoNodes != null)
{
foreach (XmlNode item in infoNodes)
{
if (!(item.Name.Equals("li")) || item.ChildNodes[1] == null)
{
continue;
}
var value = item.ChildNodes[1].Value;
switch (item.FirstChild.InnerText)
{
case "Miles":
newVehicle.Miles = value.Equals("New") ? 0 : Convert.ToInt32(value.ToLower().Replace("k", "000").Replace("<", "").Replace(">", ""));
newVehicle.Used = !value.Equals("New");
break;
case "Drive":
newVehicle.DriveTrain = value;
break;
case "Transmission":
newVehicle.Transmission = value;
break;
case "Exterior":
newVehicle.ExteriorColor = value;
break;
case "Interior":
newVehicle.InteriorColor = value;
break;
case "EPA Mileage":
var tmp = value.Replace("MPG", "").Split('/');
newVehicle.MPGCity = value.Equals("Not Available") ? 0 : Convert.ToInt32(tmp[0]);
newVehicle.MPGHighway = value.Equals("Not Available") ? 0 : Convert.ToInt32(tmp[1]);
break;
case "Stock #":
newVehicle.Stock = value;
break;
case "Rating":
newVehicle.Rating = Convert.ToDecimal(item.ChildNodes[1].InnerText.Replace("(", "").Replace(")", ""));
break;
}
}
}
var storeNode = xmlDocument.SelectSingleNode("//*[@id='qualifiers-global']");
if (storeNode != null)
{
var jsonObj = (JObject)JsonConvert.DeserializeObject(storeNode.Attributes["data-qualifiers"].Value);
var carmaxStoreId = Convert.ToInt32(((JValue)jsonObj["StoreId"]).Value);
newVehicle.StoreId = (new VINMarketSqlHelper()).GetId("SELECT StoreId FROM CarMaxStore WHERE CarMaxStoreId = " + carmaxStoreId);
}
var features = xmlDocument.SelectNodes("//*[@class='features']/*[2]/*");
if (features != null)
{
var temp = (from XmlNode item in features select item.InnerText.Trim()).ToList();
newVehicle.Features = temp.Any() ? temp.Aggregate((a, b) => a + ',' + b) : string.Empty;
}
//var thumbnailImages = xmlDocument.SelectNodes("//*[@id='thumbs']/*");
//var fullImages = xmlDocument.SelectNodes("//*[@id='photos']/*");
//if (fullImages != null)
//{
// var temp = (from XmlNode item in fullImages select item.Attributes["src"].Value).ToList();
// newVehicle.FullPhotos = temp.Any() ? temp.Aggregate((a, b) => a + ',' + b) : string.Empty;
// newVehicle.ThumbnailPhotos = newVehicle.FullPhotos;
//}
newVehicle.UpdatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
newVehicle.CreatedDate = DataCommonHelper.GetChicagoDateTime(DateTime.Now);
}
catch (Exception ex)
{
_logging.Error("CARMAX - LoadDetailVehicle", ex);
}
return(newVehicle);
}
