public JsonResult AddPrice(decimal id)
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdatePriceForListing(id, null, null, null, null, null, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
static void UploadExport()
{
FileInfo fi = null;
fi = new FileInfo(@"D:\CurrentDevelopment\Cresa\out.xlsx");
var stream = new MemoryStream(File.ReadAllBytes(fi.FullName));
OracleRepository repo = new OracleRepository();
var id = repo.CreateListingDocument("Excel", "EXPORT", 0, "admin", "Cresa - Industrial Market Availabilities.xls", stream);
Console.WriteLine("Create document with id={0}", id);
}
static void CreateExcel()
{
string type = "";
OracleRepository rep = new OracleRepository();
MemoryStream stream =(MemoryStream) rep.LoadListingDocument("701", out type);
Cresa.Services.Excel.ExcelReader reader = new Services.Excel.ExcelReader();
var template = reader.ReadExcel(stream);
var srv = new Cresa.Services.Excel.ListingExcelService("");
var excelBytes = srv.Export(template, "48013");
File.WriteAllBytes(@"D:\CurrentDevelopment\Cresa\out.xlsx", excelBytes);
}
public JsonResult UpdateNote(decimal id, int listing_id, string note)
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdateNoteForListing(listing_id, id, note, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult UpdateLoadingDock(decimal id, int listing_id, decimal? no_drive_in, decimal? size_drive_in)
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdateLoadingDockForListing(listing_id, id, no_drive_in, size_drive_in, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetListingBuilding(decimal id)
{
OracleRepository repo = new OracleRepository();
var query = String.Format(@"SELECT * FROM TBUILDING_LISTING WHERE ID = {0} ", id);
var result = repo.QueryAll(query).First();
return Json(result, JsonRequestBehavior.AllowGet);
}
public byte[] Export(List<ExcelData> datas, string csvIDsToExport)
{
OracleRepository repo = new OracleRepository();
foreach (var data in datas)
{
var listingData = repo.QueryAll(string.Format("SELECT * FROM TLISTING WHERE ID IN ({0}) AND TYPE= '{1}'", csvIDsToExport,data.SheetName));
var mapping = repo.QueryAll(String.Format("SELECT * FROM TEXPORT_LISTING WHERE TYPE ='{0}' ORDER BY ORDINAL", data.SheetName));
foreach (var listing in listingData)
{
List<string> row = new List<string>();
List<Dictionary<string, object>> officeData = null;
List<Dictionary<string, object>> rentData = null;
List<Dictionary<string, object>> loadingDockData = null;
List<Dictionary<string, object>> driveInData = null;
List<Dictionary<string, object>> brokerData = null;
List<Dictionary<string, object>> notesData = null;
List<Dictionary<string, object>> priceData = null;
List<Dictionary<string, object>> taxData = null;
decimal index = 0;
foreach (var map in mapping)
{
decimal id = (decimal)listing["ID"];
decimal ordinal = (decimal)map["ORDINAL"];
while (index < ordinal && index < 512)
{
row.Add("");
index++;
}
var colName = String.Format("{0}", map["COLUMN_NAME"]);
var tableName = String.Format("{0}", map["TABLE_NAME"]);
if (tableName == "TLISTING")
{
row.Add(String.Format("{0}", listing[colName]));
}
else if (tableName == "TOFFICE_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TOFFICE_LISTING", officeData,map));
}
else if (tableName == "TRENT_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TRENT_LISTING", rentData, map));
}
else if (tableName == "TLOADINGDOCK_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TLOADINGDOCK_LISTING", loadingDockData, map));
}
else if (tableName == "TDRIVE_IN_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TDRIVE_IN_LISTING", driveInData, map));
}
else if (tableName == "TBROKER_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TBROKER_LISTING", brokerData, map));
}
else if (tableName == "TNOTES_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TNOTES_LISTING", notesData, map));
}
else if (tableName == "TPRICE_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TPRICE_LISTING", priceData, map));
}
else if (tableName == "TTAX_LISTING")
{
row.AddRange(ExportEntity(id, colName, "TTAX_LISTING", taxData, map));
}
else
{
row.Add("");
}
index++;
}
officeData = null;
data.DataRows.Add(row);
}
}
ExcelWriter writer = new ExcelWriter();
return writer.GenerateExcel(datas);
}
public bool Import(ExcelData data, string user)
{
OracleRepository repo = new OracleRepository();
var listingMapping = GetMapping(Type, "TLISTING").ToList();
var officeMapping = GetMapping(Type, "TOFFICE_LISTING").ToList();
var rentMapping = GetMapping(Type, "TRENT_LISTING").ToList();
var loadingDockMapping = GetMapping(Type, "TLOADINGDOCK_LISTING").ToList(); ;
var driveInMapping = GetMapping(Type, "TDRIVE_IN_LISTING").ToList();
var brokerMapping = GetMapping(Type, "TBROKER_LISTING").ToList();
var notesMapping = GetMapping(Type, "TNOTES_LISTING").ToList();
var priceMapping = GetMapping(Type, "TPRICE_MAPPING").ToList();
var taxMapping = GetMapping(Type, "TTAX_LISTING").ToList();
for (int i=0;i<data.DataRows.Count();i++)
{
decimal? id = null;
decimal? listing_id= GetExistingEntity("TLISTING", null, listingMapping, data.DataRows[i], data.Headers);
if (listing_id==null)
{
listing_id=ProcessEntity(null, id, Type, listingMapping, "TLISTING", data.DataRows[i], data.Headers, user);
}
else
{
ProcessEntity(null, listing_id, Type, listingMapping, "TLISTING", data.DataRows[i], data.Headers, user);
}
var keysBySequence = GetMultipleMapping(officeMapping);
foreach (var office in keysBySequence.Keys)
{
id = GetExistingEntity("TOFFICE_LISTING", listing_id, keysBySequence[office], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[office], "TOFFICE_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(rentMapping);
foreach (var rent in keysBySequence.Keys)
{
id = GetExistingEntity("TRENT_LISTING", listing_id, keysBySequence[rent], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[rent], "TRENT_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(loadingDockMapping);
foreach (var loadingDock in keysBySequence.Keys)
{
id = GetExistingEntity("TLOADINGDOCK_LISTING", listing_id, keysBySequence[loadingDock], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[loadingDock], "TLOADINGDOCK_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(driveInMapping);
foreach (var driveIn in keysBySequence.Keys)
{
id = GetExistingEntity("TDRIVE_IN_LISTING", listing_id, keysBySequence[driveIn], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[driveIn], "TDRIVE_IN_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(brokerMapping);
foreach (var broker in keysBySequence.Keys)
{
id = GetExistingEntity("TBROKER_LISTING", listing_id, keysBySequence[broker], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[broker], "TBROKER_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(notesMapping);
foreach (var notes in keysBySequence.Keys)
{
id = GetExistingEntity("TNOTES_LISTING", listing_id, keysBySequence[notes], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[notes], "TNOTES_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(priceMapping);
foreach (var price in keysBySequence.Keys)
{
id = GetExistingEntity("TPRICE_LISTING", listing_id, keysBySequence[price], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[price], "TPRICE_LISTING", data.DataRows[i], data.Headers, user);
}
keysBySequence = GetMultipleMapping(taxMapping);
foreach (var tax in keysBySequence.Keys)
{
id = GetExistingEntity("TTAX_LISTING", listing_id, keysBySequence[tax], data.DataRows[i], data.Headers);
ProcessEntity(listing_id, id, null, keysBySequence[tax], "TTAX_LISTING", data.DataRows[i], data.Headers, user);
}
}
return false;
}
public List<string> ExportEntity(decimal listing_id, string colName, string tableName, List<Dictionary<string,object>> data, Dictionary<string, object> map)
{
List<string> result = new List<string>();
OracleRepository repo = new OracleRepository();
//check for other rows and sequence
if (data == null)
{
data= repo.QueryAll(String.Format("SELECT * FROM {0} Where LISTING_ID = {1}",tableName, listing_id)).ToList();
}
int sequence = 0;
if (map["SEQUENCE"] != DBNull.Value)
{
sequence = Convert.ToInt32(map["SEQUENCE"]);
}
if (data.Count() > 0 && data.Count() > sequence)
{
result.Add(String.Format("{0}", data[sequence][colName]));
}
else
{
result.Add("");
}
return result;
}
public JsonResult UpdateRent(decimal id,int listing_id,DateTime? effective_date, decimal? total_area, decimal? net_rent, decimal? op_costs )
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdateRentForListing(listing_id, id,effective_date, total_area,net_rent, op_costs, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetTaxForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "ID";
sortorder = "";
}
var query = BuildQuery(
String.Format(@"
SELECT ID, LISTING_ID, EFFECTIVE_DATE, NAME,AMOUNT, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM
TTAX_LISTING
WHERE LISTING_ID = {0}", id));
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var taxes = repo.Query(query, pagenum, pagesize);
if (taxes!=null && taxes.Count()>0)
{
foreach (var tax in taxes)
{
if (tax["EFFECTIVE_DATE"]!=DBNull.Value)
{
var effectiveDate = (DateTime?)tax["EFFECTIVE_DATE"];
if (effectiveDate != null)
{
tax["EFFECTIVE_DATE"] = effectiveDate.Value.Year.ToString();
}
}
}
}
var result = new
{
TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TTAX_LISTING WHERE LISTING_ID = {0}", id))),
Rows = taxes
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetRentsForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "ID";
sortorder = "";
}
var query = BuildQuery(
String.Format(@"
SELECT ID, LISTING_ID, EFFECTIVE_DATE, TOTAL_AREA,NET_RENT, OP_COSTS, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM
VRENT_LISTING
WHERE LISTING_ID = {0}", id));
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var rents = repo.Query(query, pagenum, pagesize);
var result = new
{
TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from VRENT_LISTING WHERE LISTING_ID = {0}", id))),
Rows = rents
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetOfficesForListingImageList(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
try
{
int nID = int.Parse(id);
var query = BuildQuery(
String.Format(@"
SELECT ID, LISTING_ID, OFFICE_PICTURE_NAME,DESCRIPTION, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TLISTING_PICTURES
WHERE LISTING_ID = {0} WHERE STATUS='A' ", nID));
OracleRepository repo = new OracleRepository();
var result = repo.Query(query, pagenum, pagesize);
return Json(result, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(null, JsonRequestBehavior.AllowGet);
}
}
public JsonResult GetOfficesForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
try
{
int nID = int.Parse(id);
var query = BuildQuery(
String.Format(@"SELECT ID, LISTING_ID, NAME,FLOOR_SIZE, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TOFFICE_LISTING WHERE LISTING_ID = {0}", nID));
OracleRepository repo = new OracleRepository();
var offs = repo.Query(query, pagenum, pagesize);
var result = new
{
TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TOFFICE_LISTING WHERE LISTING_ID = {0}", id))),
Rows = offs
};
return Json(result, JsonRequestBehavior.AllowGet);
}
catch(Exception ex)
{
return Json(null, JsonRequestBehavior.AllowGet);
}
}
public JsonResult GetLoadingDocksForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "ID";
sortorder = "";
}
var query = BuildQuery(
String.Format(@"
SELECT ID, LISTING_ID, NO_LOADING_DOCKS, LOADING_SIZE, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM
TLOADINGDOCK_LISTING
WHERE LISTING_ID = {0}", id));
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var loadingdock = repo.Query(query, pagenum, pagesize);
var result = new
{
TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id))),
Rows = loadingdock
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetListings(string sortdatafield, string sortorder, int pagesize, int pagenum)
{
Models.SummaryModel summary = new Models.SummaryModel();
var query = BuildQuery(@"
Select VLISTING.*
from VLISTING ");
if (String.IsNullOrEmpty(sortdatafield))
{
//sortdatafield = "c.sort_order asc,VBUILDING.building_name asc,VBUILDING_VACANCY.floor_numeric desc";
sortdatafield = "ID";
sortorder = "";
}
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var buildings = repo.Query(query, pagenum, pagesize);
var resultBuilding = repo.Query(BuildQuery(@"Select COUNT(VLISTING.ID) from VLISTING inner join (Select MAX(ID)as ID from VLISTING Group BY Address, CITY) t on t.ID = VLISTING.ID"));
var resultVac = repo.Query(BuildQuery(@"Select Count(ID) from VLISTING "));
//TODO get total acreage for land
var resultSq = repo.Query(BuildQuery(@"Select Sum(TOTAL_AREA) from VLISTING "));
if (resultBuilding != null) { summary.TotalBuilding = resultBuilding.ToString(); }
else { summary.TotalBuilding = "0"; }
if (resultVac != null) { summary.TotalVacancy = resultVac.ToString(); }
else { summary.TotalVacancy = "0"; }
if (resultSq != null) { summary.TotalSqFeet = String.Format("{0:n0}", resultSq); }
else { summary.TotalSqFeet = "0"; }
var result = new
{
TotalRows = repo.Total(BuildQuery("Select COUNT(*) from VLISTING")),
Rows = buildings,
Summary = summary
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult GetListingPicture(decimal id)
{
OracleRepository repo = new OracleRepository();
string filename;
Stream st = repo.GetBuildingPicture(id, out filename);
if (String.IsNullOrEmpty(filename))
{
filename = "unknown";
}
var cd = new System.Net.Mime.ContentDisposition
{
FileName = filename
};
Response.AppendHeader("Content-Disposition", cd.ToString());
st.Position = 0;
string mime = System.Web.MimeMapping.GetMimeMapping(filename);
return new FileStreamResult(st, mime);
}
public JsonResult UpdateOffice(decimal id, decimal listing_id, string name, decimal? floor_size)
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdateOfficeForListing(listing_id, id, name, floor_size, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult UpdatePrice(decimal id, int listing_id, DateTime? effective_date, DateTime? date_listed, decimal? new_price, decimal? old_price)
{
OracleRepository repo = new OracleRepository();
var result = repo.InsertOrUpdatePriceForListing(listing_id, id, effective_date, date_listed, new_price, old_price, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult ImportExcel( HttpPostedFileBase file)
{
OracleRepository repo = new OracleRepository();
object model;
// Check if the file is excel
if (file.ContentLength <= 0 )
{
model = (object)"You uploaded an empty file";
return Json(model);
}
if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
model = (object)"Please upload a valid excel file of version 2007 and above";
return Json(model);
}
var reader = new Cresa.Services.Excel.ExcelReader();
MemoryStream ms = new MemoryStream();
file.InputStream.CopyTo(ms);
var data= reader.ReadExcel(ms);
foreach(var sheet in data)
{
var srv = new Cresa.Services.Excel.ListingExcelService(sheet.SheetName);
if (srv.Import(sheet, HttpContext.User.Identity.Name))
{
model = (object)"Import complete";
return Json(model);
}
}
model= (object)"Unable to import file.";
return Json(model);
}
public JsonResult UpdateTax(decimal id, int listing_id, string effective_date, decimal? amount)
{
OracleRepository repo = new OracleRepository();
DateTime? dtEffectiveDate = null;
int year = 0;
if (!String.IsNullOrEmpty(effective_date) && effective_date.Length==4 && int.TryParse(effective_date,out year))
{
dtEffectiveDate = new DateTime(year, 1, 1);
}
var result = repo.InsertOrUpdateTaxForListing(listing_id, id, dtEffectiveDate,amount, HttpContext.User.Identity.Name);
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult ListingBuilding(decimal? id)
{
OracleRepository repo = new OracleRepository();
if (id== null)
{
id = repo.InsertOrUpdateEntity("TBUILDING_LISTING", new Dictionary<string, object>(), HttpContext.User.Identity.Name) as decimal?;
}
return View(id);
}
public IEnumerable<Dictionary<string, object>> GetMapping(string type, string table_name)
{
OracleRepository repo = new OracleRepository();
return repo.QueryAll(String.Format("SELECT * FROM TEXPORT_LISTING WHERE TYPE= '{0}' AND TABLE_NAME='{1}'", type, table_name));
}
public JsonResult SaveListing(decimal? id, string type,string subtype,string quadrant,
string district, string city,string unit_name,
string address,string zoning, string under_construction,
string building_type,
string total_building_size, decimal? landlord_contact_id,
string year_built,string land_acres, string smallest_divisible,
string land_serviced_type, string available,
string term,DateTime? leased_date, string largest_contiguous,
string ceiling_height, string mezzanine_yn,
string yard_area_yn, string site_size, string power,
string cranes, DateTime? sold_date, decimal? latitude, decimal? longitude,
string legal_description_block, string legal_description_lot,
string legal_description_plan)
{
OracleRepository repo = new OracleRepository();
id= repo.InsertOrUpdateListing(id, type, subtype, quadrant,
district, city, unit_name,
address, zoning, under_construction,
building_type,"",
total_building_size, landlord_contact_id,
year_built, land_acres, smallest_divisible,
land_serviced_type, available,
term, leased_date, largest_contiguous,
ceiling_height, mezzanine_yn,
yard_area_yn, site_size, power,
cranes, sold_date, User.Identity.Name,
legal_description_block, legal_description_lot, legal_description_plan);
if (id!=null && (latitude!=null ||longitude!=null))
{
repo.InsertListingLatLng(id.Value, latitude, longitude);
}
return Json(id, JsonRequestBehavior.AllowGet);
}
protected decimal? GetExistingEntity(string tableName, decimal? listing_id, List<Dictionary<string,object>> mapping, List<string> row, List<string> header)
{
OracleRepository repo = new OracleRepository();
if (mapping.Count() > 0)
{
var keys = mapping.Where(x => x.ContainsKey("IS_KEY") && x["IS_KEY"] != DBNull.Value && Convert.ToInt32(x["IS_KEY"]) == 1);
var colKeys = new Dictionary<string, object>();
if (listing_id!=null)
{
colKeys.Add("LISTING_ID", listing_id.Value);
}
foreach (var k in keys)
{
int parsedOrdinal = 0;
int useHeader = 0;
if (k["ORDINAL"]!=DBNull.Value && int.TryParse(k["ORDINAL"].ToString(), out parsedOrdinal ))
{
if (row.Count() > parsedOrdinal)
{
if (k["DATA_TYPE"]!=DBNull.Value && k["DATA_TYPE"].ToString().StartsWith("DATE"))
{
double parseOADate = 0;
if (!String.IsNullOrEmpty(row[parsedOrdinal].ToString()) && double.TryParse(row[parsedOrdinal].ToString(), out parseOADate))
{
colKeys.Add(k["COLUMN_NAME"].ToString(), DateTime.FromOADate(parseOADate));
}
else
{
if (k["DATA_TYPE"].ToString() != "DATE_ONLY")
{
colKeys.Add(k["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
}
else if (k["DATA_TYPE"] != DBNull.Value && k["DATA_TYPE"].ToString() == "NUMBER")
{
if (IsNumeric(row[parsedOrdinal]))
{
colKeys.Add(k["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
else
{
colKeys.Add(k["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
}
if (k["USE_HEADER_AS_NAME"]!=DBNull.Value && int.TryParse(k["USE_HEADER_AS_NAME"].ToString(), out useHeader))
{
if (useHeader ==1)
{
colKeys.Add("NAME", header[parsedOrdinal]);
}
}
}
return repo.GetEntityID(tableName, colKeys) as decimal?;
}
return null;
}
public JsonResult GetDriveInOrDockForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "ID";
sortorder = "";
}
var queryDriveIn = BuildQuery(
String.Format(@"
SELECT ID,LISTING_ID, 'DRIVE IN' as TYPE, NO_DRIVE_IN as AMOUNT, SIZE_DRIVE_IN as SIZE_OF , CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TDRIVE_IN_LISTING
WHERE LISTING_ID = {0}" , id));
var queryDock = BuildQuery(
String.Format(@"
SELECT ID,LISTING_ID, 'LOADING DOCK' as TYPE, NO_LOADING_DOCKS as AMOUNT, LOADING_SIZE as SIZE_OF , CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TLOADINGDOCK_LISTING
WHERE LISTING_ID = {0}", id));
queryDriveIn = String.Format("{0} order by {1} {2}", queryDriveIn, sortdatafield, sortorder);
queryDock = String.Format("{0} order by {1} {2}", queryDock, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var drivein = repo.Query(queryDriveIn, pagenum, pagesize);
var loading_dock = repo.Query(queryDock, pagenum, pagesize);
var all = new List<Dictionary<string, object>>();
all.AddRange(drivein);
all.AddRange(loading_dock);
var total = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TDRIVE_IN_LISTING WHERE LISTING_ID = {0}", id)));
total += repo.Total(BuildQuery(String.Format("Select COUNT(*) from TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id)));
var result = new
{
TotalRows =total ,
Rows = all
};
return Json(result, JsonRequestBehavior.AllowGet);
}
protected decimal? ProcessEntity(decimal? listing_id,decimal? id, string type, List<Dictionary<string,object>> mappings,
string tableName,List<String>row, List<string> header, string user)
{
OracleRepository repo = new OracleRepository();
var cols = new Dictionary<string, object>();
if (id != null)
{
cols.Add("ID", id);
}
if (listing_id!=null)
{
cols.Add("LISTING_ID", listing_id);
}
if (type!=null)
{
cols.Add("TYPE", type);
}
for (int i=0; i< mappings.Count(); i++)
{
try
{
int parsedOrdinal = 0;
int useHeader = 0;
if (mappings[i]["ORDINAL"] != DBNull.Value && int.TryParse(mappings[i]["ORDINAL"].ToString(), out parsedOrdinal))
{
if (row.Count() > parsedOrdinal)
{
if (mappings[i]["DATA_TYPE"] != DBNull.Value && mappings[i]["DATA_TYPE"].ToString().StartsWith ("DATE"))
{
double parseOADate = 0;
if (!String.IsNullOrEmpty(row[parsedOrdinal].ToString()) && double.TryParse(row[parsedOrdinal].ToString(), out parseOADate))
{
cols.Add(mappings[i]["COLUMN_NAME"].ToString(), DateTime.FromOADate(parseOADate));
}
else
{
if (mappings[i]["DATA_TYPE"].ToString()!="DATE_ONLY")
{
cols.Add(mappings[i]["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
}
else if (mappings[i]["DATA_TYPE"] != DBNull.Value && mappings[i]["DATA_TYPE"].ToString()=="NUMBER")
{
if (IsNumeric(row[parsedOrdinal]))
{
cols.Add(mappings[i]["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
else
{
cols.Add(mappings[i]["COLUMN_NAME"].ToString(), row[parsedOrdinal]);
}
}
}
if (mappings[i]["USE_HEADER_AS_NAME"] != DBNull.Value && int.TryParse(mappings[i]["USE_HEADER_AS_NAME"].ToString(), out useHeader))
{
if (useHeader == 1)
{
cols.Add("NAME", header[parsedOrdinal]);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Unable to processEntity (ListingExcelService.ProcessEntity): Reason: {0}", ex.Message);
}
}
var output = repo.InsertOrUpdateEntity(tableName, cols, user);
return output as decimal?;
}
public JsonResult GetListing(decimal id)
{
try
{
OracleRepository repo = new OracleRepository();
var query = String.Format(@"
SELECT VLISTING.*
FROM VLISTING
WHERE ID = {0} ", id);
var result = repo.QueryAll(query).First();
return Json(result, JsonRequestBehavior.AllowGet);
}
catch(Exception ex)
{
return Json("", JsonRequestBehavior.AllowGet);
}
}
public JsonResult UpdateDriveInOrDock(decimal id, int listing_id,string type, decimal? amount, decimal? size_of)
{
OracleRepository repo = new OracleRepository();
bool result = false;
if (type == "LOADING DOCK")
{
result = repo.InsertOrUpdateLoadingDockForListing(listing_id, id, amount, size_of, HttpContext.User.Identity.Name);
}
else
{
result = repo.InsertOrUpdateDriveInForListing(listing_id, id, amount, size_of, HttpContext.User.Identity.Name);
}
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetBuildingListingList(decimal id, string sortdatafield, string sortorder, int pagesize, int pagenum)
{
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "ID";
sortorder = "";
}
var query = BuildQuery(
String.Format(@"
SELECT *
FROM VLISTING
WHERE BUILDING_LISTING_ID = {0}", id));
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var broker = repo.Query(query, pagenum, pagesize);
var result = new
{
TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from VLISTING WHERE BUILDING_LISTING_ID = {0}", id))),
Rows = broker
};
return Json(result, JsonRequestBehavior.AllowGet);
}
