public JsonResult GetBuildingVacanciesByBuilding(decimal id)
{
OracleRepository repo = new OracleRepository();
var result = repo.QueryAll(String.Format("SELECT * FROM VBUILDING_VACANCY WHERE BUILDING_ID = {0}", id));
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetParentTemplateDocuments()
{
var query = BuildQuery(@"Select NAME, UPDATE_USER, UPDATE_DATE, CREATE_USER,
CREATE_DATE, ID, REVISION, PARENT_DOCUMENT_ID,
SEQUENCE_NO, TYPE
from TDOCUMENT
WHERE (TYPE = 'PARENT TEMPLATE') AND STATUS<>'D' ", true);
OracleRepository repo = new OracleRepository();
var documents = repo.QueryAll(query);
var result = new
{
Rows = documents
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public bool ProcessPicture(List<decimal> building_ids, Stream childFS, ref Novacode.DocX docx, string [] zoom, string []lat, string []lng)
{
try
{
OracleRepository repo = new OracleRepository();
var floors = repo.QueryAll(
String.Format(@" SELECT ID FROM TBUILDING_PICTURES WHERE BUILDING_ID ={0} ", 0));
string filename = "";
string floor_no = "";
foreach (var floor in floors)
{
using (var childDocx = Novacode.DocX.Load(childFS))
{
using (var picStream = repo.GetPicture((decimal)floor["ID"], out filename, out floor_no))
{
picStream.Seek(0, SeekOrigin.Begin);
Novacode.Image img = childDocx.AddImage(picStream); // Create image.
Novacode.Picture pic1 = img.CreatePicture(1, 1); // Create picture.
Novacode.Paragraph p = childDocx.InsertParagraph();
p.InsertPicture(pic1); // Insert picture into paragraph.
}
string markers = "";
string building_id_csv = "";
List<string> buildingList = new List<string>();
foreach (var bid in building_ids)
{
if (String.IsNullOrEmpty(building_id_csv))
{
building_id_csv = bid.ToString();
}
else
{
building_id_csv += "," + bid;
}
}
var latLongs = repo.QueryAll(
String.Format(@" SELECT TBUILDING.BUILDING_NAME, LATITUDE, LONGITUDE FROM TBUILDING_LAT_LONG
INNER JOIN TBUILDING ON TBUILDING_LAT_LONG.BUILDING_ID= TBUILDING.BUILDING_ID
WHERE TBUILDING_LAT_LONG.BUILDING_ID IN ({0}) ", building_id_csv));
int index = 0;
foreach (var latLong in latLongs)
{
index++;
markers += String.Format("&markers=label:{0}%7C{1},{2}", index, latLong["LATITUDE"], latLong["LONGITUDE"]);
buildingList.Add(index + "." + latLong["BUILDING_NAME"]);
}
string zoomAmt = "";
string center = "";
if (zoom.Count()>0)
{
int z = 14;
if (int.TryParse(zoom[0], out z))
{
zoomAmt = "&zoom=" + z;
}
}
if (lat.Count()>0 && lng.Count()>0)
{
double latAmt = 0;
double lngAmt = 0;
if (double.TryParse(lat[0], out latAmt) && double.TryParse(lng[0], out lngAmt))
{
center = String.Format("¢er={0},{1}", latAmt, lngAmt);
}
}
using (MemoryStream memStream = new MemoryStream())
{
WebRequest request = WebRequest.Create(
String.Format("https://maps.googleapis.com/maps/api/staticmap?format=jpg{0}{1}{2}&size=400x400&key=AIzaSyB64gJHrp5QzW2fNMVRvRygTm8wCuBIrnc", markers, zoomAmt, center));
// Get the response.
WebResponse response = request.GetResponse();
// Get the stream containing content returned by the server.
Stream dataStream = response.GetResponseStream();
using (Stream rStream = response.GetResponseStream())
{
byte[] buffer = new byte[1024];
int byteCount;
do
{
byteCount = rStream.Read(buffer, 0, buffer.Length);
memStream.Write(buffer, 0, byteCount);
} while (byteCount > 0);
}
double newHeight = 1;
using (var tempImg = System.Drawing.Image.FromStream(memStream))
{
newHeight = (tempImg.Height / tempImg.Width) * 400;
}
memStream.Seek(0, SeekOrigin.Begin);
try
{
Novacode.Image img = childDocx.AddImage(memStream); // Create image.
Novacode.Picture pic1 = img.CreatePicture((int)newHeight,400); // Create picture.
Novacode.Paragraph p = childDocx.InsertParagraph();
p.InsertPicture(pic1); // Insert picture into paragraph.
foreach (var s in buildingList)
{
childDocx.InsertParagraph(s);
}
}
catch (Exception ex)
{
Novacode.Paragraph p = childDocx.InsertParagraph(ex.Message + " " + ex.StackTrace);
}
docx.InsertSectionPageBreak();
docx.InsertDocument(childDocx);
}
}
}
}
catch { }
return true;
}
public JsonResult GetBuildingOnly(string id)
{
OracleRepository repo = new OracleRepository();
int nBuildingID = int.Parse(id);
var query = String.Format(@"
SELECT b.BUILDING_ID,
b.BUILDING_NAME,
b.CLASS_TYP,
b.LOCATION_TYP,
b.QRTRLY_RPT_FLAG,
b.ADDRESS1,
b.POSTAL_CODE,
b.YEAR_BUILT,
b.STOREYS,
b.NET_RENTABLE,
b.FLOOR_PLATE,
b.PARKING_RATIO_FORMATTED,
b.latest_op_cost_amt,
b.OWNER_NAME,
nvl(b.OWNER_CONTACT_ID, 0) OWNER_CONTACT_ID,
B.LANDLORD_NAME,
nvl(b.LANDLORD_CONTACT_ID,0) LANDLORD_CONTACT_ID,
b.CREATE_DATE,
b.CREATE_USER,
b.UPDATE_DATE,
b.UPDATE_USER,
t.LATITUDE,
t.LONGITUDE
FROM VBUILDING b,
TBUILDING_LAT_LONG t,
WHERE b.BUILDING_ID = {0} ", nBuildingID);
var result = repo.QueryAll(query).First();
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 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 GetActiveTemplates()
{
try
{
OracleRepository repo = new OracleRepository();
string qry = "SELECT ID, NAME FROM TDOCUMENT WHERE STATUS <> 'D' AND (TYPE = 'USER TEMPLATE' OR TYPE ='PARENT TEMPLATE') ";
var result = repo.QueryAll(qry);
return Json(new
{
Rows = result
}, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(ex.Message, JsonRequestBehavior.AllowGet);
}
}
public JsonResult GetAvailableOfficesForListing(decimal id)
{
try
{
var query =
String.Format(@"
SELECT ID, LISTING_ID, NAME,FLOOR_SIZE, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TOFFICE_LISTING WHERE LISTING_ID = {0} ORDER BY NAME",id);
OracleRepository repo = new OracleRepository();
var result = repo.QueryAll(query);
return Json(result, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(null, JsonRequestBehavior.AllowGet);
}
}
private XElement GenerateDataFromDataSource(List<Dictionary<string, object>> data,
ref Dictionary<string, Tuple<int,string, decimal>> buildingIndices,
ref Dictionary<string, Tuple<string, decimal>> floorplanIndices,
ref Dictionary<int, XElement> buildingxml)
{
var buildings = new XElement("BUILDINGS", new XElement("MAP", "<MAP_JG2016>"));
OracleRepository repo = new OracleRepository();
for (int i = 0; i < data.Count(); ++i)
{
buildingxml.Add(i,new XElement("BUILDINGS", new XElement("MAP", "<MAP_JG2016>")));
var building = new XElement("BUILDING");
foreach (var d in data[i])
{
building.Add(new XElement(d.Key, d.Value));
}
int index = buildingIndices.Count();
buildingIndices.Add("<BUILDING_IMAGE_" + index + ">", new Tuple<int, string, decimal>(index, String.Format("{0}", data[i]["BUILDING_NAME"]), (decimal)data[i]["BUILDING_ID"]));
building.Add(new XElement("INDEX_NUMERIC", index + 1));
building.Add(new XElement("INDEX", UnitMap[index + 1]));
building.Add(new XElement("IMAGE", "<BUILDING_IMAGE_" + index + ">"));
building.Add(new XElement("PARKING_LIST", ""));
building.Add(new XElement("COMMENT_LIST", ""));
if (data[i]["SIZE_SQ"] != null && data[i]["SIZE_SQ"] != DBNull.Value)
{
building.Add(new XElement("SIZE_SQ_FORMATTED", String.Format("{0:N0} sf", data[i]["SIZE_SQ"])));
}
else
{
building.Add(new XElement("SIZE_SQ_FORMATTED", ""));
}
if (data[i]["BASE_RENT"] != null && data[i]["BASE_RENT"] != DBNull.Value)
{
building.Add(new XElement("BASE_RENT_FORMATTED", String.Format("${0:N2} per sf", data[i]["BASE_RENT"])));
}
else
{
building.Add(new XElement("BASE_RENT_FORMATTED", ""));
}
if (data[i]["LATEST_OP_COST_AMT"] != null && data[i]["LATEST_OP_COST_AMT"] != DBNull.Value)
{
building.Add(new XElement("OP_COST_FORMATTED", String.Format("${0:N2} per sf (est {1})", data[i]["LATEST_OP_COST_AMT"], data[i]["OP_COST_YEAR"])));
}
else
{
building.Add(new XElement("OP_COST_FORMATTED", ""));
}
if (data[i]["TERM"] != null && data[i]["TERM"] != DBNull.Value)
{
double dblTerm = 0;
if (double.TryParse(String.Format("{0}", data[i]["TERM"]), out dblTerm))
{
building.Add(new XElement("TERM_FORMATTED", String.Format("{0} years", data[i]["TERM"])));
}
else
{
building.Add(new XElement("TERM_FORMATTED", String.Format("{0}", data[i]["TERM"])));
}
}
else
{
building.Add(new XElement("TERM_FORMATTED", ""));
}
if (data[i]["PARKING_RATIO"] != null && data[i]["PARKING_RATIO"] != DBNull.Value)
{
building.Add(new XElement("PARKING_RATIO", String.Format("1 stall per {0} sf", data[i]["PARKING_RATIO"])));
}
else
{
building.Add(new XElement("PARKING_RATIO", ""));
}
if (data[i]["NET_RENTABLE"] != null && data[i]["NET_RENTABLE"] != DBNull.Value)
{
building.Add(new XElement("NET_RENTABLE_FORMATTED", String.Format("{0:n0} sf", data[i]["NET_RENTABLE"])));
}
else
{
building.Add(new XElement("NET_RENTABLE_FORMATTED", ""));
}
if (data[i]["FLOOR_PLATE"] != null && data[i]["FLOOR_PLATE"] != DBNull.Value)
{
building.Add(new XElement("FLOOR_PLATE_FORMATTED", String.Format("{0:n0} sf", data[i]["FLOOR_PLATE"])));
}
else
{
building.Add(new XElement("FLOOR_PLATE_FORMATTED", ""));
}
decimal building_vacancy_id = (decimal)data[i]["BUILDING_VACANCY_ID"];
string qry = String.Format(@" SELECT ID,FLOOR_NO FROM TBUILDING_PICTURES WHERE BUILDING_VACANCY_ID = {0}", building_vacancy_id);
var floors = repo.QueryAll(qry);
int floorIndex = 0;
if (floors.Count()==0)
{
floorIndex = floorplanIndices.Count();
XElement floorXml = new XElement("FLOOR_DETAIL");
floorXml.Add(new XElement("FLOOR_NO", String.Format("{0}", "")));
floorXml.Add(new XElement("ID", String.Format("{0}", "")));
floorXml.Add(new XElement("IMAGE", ""));
building.Add(floorXml);
}
foreach (var floor in floors)
{
floorIndex = floorplanIndices.Count();
XElement floorXml = new XElement("FLOOR_DETAIL");
floorXml.Add(new XElement("FLOOR_NO", String.Format("{0}", floor["FLOOR_NO"])));
floorXml.Add(new XElement("ID", String.Format("{0}", floor["ID"])));
floorXml.Add(new XElement("IMAGE", String.Format("FLOORPLAN_IMAGE_{0}", floorIndex)));
floorplanIndices.Add(String.Format("FLOORPLAN_IMAGE_{0}", floorIndex), new Tuple<string, decimal>(String.Format("{0}", floor["FLOOR_NO"]), (decimal)floor["ID"]));
building.Add(floorXml);
}
var parkingList = repo.QueryAll(
String.Format(@"
SELECT
PARKING_ID,
BUILDING_ID,
PARKING_TYP,
NUM_STALLS,
COST_AMT,
NOTE,
INFO_AS_OF_DATE,
CREATE_DATE,
CREATE_USER,
UPDATE_DATE,
UPDATE_USER
FROM VPARKING WHERE BUILDING_ID={0} ", data[i]["BUILDING_ID"]));
string strParkingList = "";
if (parkingList.Count() ==0)
{
var parkingXml = new XElement("PARKING");
building.Add(parkingXml);
parkingXml.Add(new XElement("PARKING_ID", ""));
parkingXml.Add(new XElement("PARKING_FORMATTED", ""));
parkingXml.Add(new XElement("PARKING_TYP", ""));
parkingXml.Add(new XElement("NUM_STALLS", ""));
parkingXml.Add(new XElement("COST_AMT", ""));
parkingXml.Add(new XElement("NOTE", ""));
parkingXml.Add(new XElement("INFO_AS_OF_DATE", ""));
}
foreach (Dictionary<string, object> p in parkingList)
{
var parkingXml = new XElement("PARKING");
building.Add(parkingXml);
if (String.IsNullOrEmpty(String.Format("{0}", p["COST_AMT"])))
{
strParkingList = String.Format("{0}", p["PARKING_TYP"]);
}
else
{
strParkingList = String.Format("${0} per stall, per month \n {1} ", p["COST_AMT"], p["PARKING_TYP"]);
}
parkingXml.Add(new XElement("PARKING_ID", String.Format("{0}", p["PARKING_ID"])));
parkingXml.Add(new XElement("PARKING_FORMATTED", String.Format("{0}", strParkingList)));
parkingXml.Add(new XElement("PARKING_TYP", String.Format("{0}", p["PARKING_TYP"])));
parkingXml.Add(new XElement("NUM_STALLS", String.Format("{0}", p["NUM_STALLS"])));
parkingXml.Add(new XElement("COST_AMT", String.Format("{0}", p["COST_AMT"])));
parkingXml.Add(new XElement("NOTE", String.Format("{0}", p["NOTE"])));
parkingXml.Add(new XElement("INFO_AS_OF_DATE", String.Format("{0}", p["INFO_AS_OF_DATE"])));
}
var commentList = repo.QueryAll(
String.Format(@"
SELECT ATTR_NOTE FROM TBUILDING_ATTR WHERE BUILDING_ID={0} ", data[i]["BUILDING_ID"])) as List<Dictionary<string, object>>;
var commentVacList = repo.QueryAll(
String.Format(@"
SELECT VACANCY_ATTR_NOTE as ATTR_NOTE FROM TBUILDING_VACANCY_ATTR WHERE BUILDING_VACANCY_ID={0} ", data[i]["BUILDING_VACANCY_ID"]));
foreach(var cv in commentVacList)
{
commentList.Add(cv);
}
if (commentList.Count() == 0)
{
XElement commentListXml = new XElement("COMMENT");
building.Add(commentListXml);
commentListXml.Add(new XElement("ATTR_NOTE", ""));
}
foreach (Dictionary<string, object> p in commentList)
{
XElement commentListXml = new XElement("COMMENT");
building.Add(commentListXml);
commentListXml.Add(new XElement("ATTR_NOTE", String.Format("{0}", p["ATTR_NOTE"])));
}
buildingxml[i].Add(building);
buildings.Add(building);
}
return buildings;
}
private Models.MarketReportModel LoadMarketReport(string id)
{
Dictionary<string, Models.MarketReport> mrList = new Dictionary<string, Cresa.Models.MarketReport>();
try
{
ViewBag.ID = id;
OracleRepository repo = new OracleRepository();
var buildings = repo.QueryAll(String.Format(@"
Select VBUILDING.BUILDING_ID, VBUILDING.EXT_REF_ID, VBUILDING.BUILDING_NAME, VBUILDING.CLASS_TYP_ID, VBUILDING.CLASS_TYP,
VBUILDING.LOCATION_TYP_ID, VBUILDING.LOCATION_TYP, VBUILDING.QRTRLY_RPT_FLAG, VBUILDING.CONTACT_MECH_ID,
VBUILDING.ADDRESS1, VBUILDING.POSTAL_CODE, VBUILDING.YEAR_BUILT, VBUILDING.STOREYS, VBUILDING.NET_RENTABLE,
VBUILDING.FLOOR_PLATE, VBUILDING.PARKING_RATIO, VBUILDING.PARKING_RATIO_FORMATTED, VBUILDING.LATEST_OP_COST_AMT,
VBUILDING.OP_COST_YEAR, VBUILDING.LANDLORD_CONTACT_ID, VBUILDING.LANDLORD_NAME, VBUILDING.OWNER_CONTACT_ID,
VBUILDING.OWNER_NAME, VBUILDING.CREATE_DATE, VBUILDING.CREATE_USER, VBUILDING.UPDATE_DATE,
VBUILDING.UPDATE_USER, VBUILDING_VACANCY.BUILDING_VACANCY_ID, VBUILDING_VACANCY.LEASE_TYP_ID,
VBUILDING_VACANCY.LEASE_TYP_CODE, VBUILDING_VACANCY.LEASE_TYP, VBUILDING_VACANCY.SUITE_NO, VBUILDING_VACANCY.FLOOR,
VBUILDING_VACANCY.FLOOR_NUMERIC, VBUILDING_VACANCY.SIZE_SQ, VBUILDING_VACANCY.SMALLEST_DIV,
VBUILDING_VACANCY.INDUCEMENTS, VBUILDING_VACANCY.BASE_RENT, VBUILDING_VACANCY.TERM, VBUILDING_VACANCY.ARCHIVED_YN,
VBUILDING_VACANCY.LAST_ARCHIVED_DATE, VBUILDING_VACANCY.RETAIL_YN, VBUILDING_VACANCY.EXCLUSIVE_YN,
VBUILDING_VACANCY.VACANCY_DESC, VBUILDING_VACANCY.BROKER_NOTES, TBUILDING_LAT_LONG.LATITUDE, TBUILDING_LAT_LONG.LONGITUDE
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
left join TBUILDING_LAT_LONG on VBUILDING.BUILDING_ID = TBUILDING_LAT_LONG.BUILDING_ID
where BUILDING_VACANCY_ID In ({0})
Order By VBUILDING.BUILDING_NAME
", id));
bool showTitle = buildings.Count() > 1;
Dictionary<decimal, Models.BuildingLocation> buildingLocations = new Dictionary<decimal, Models.BuildingLocation>();
foreach (Dictionary<string, object> b in buildings)
{
string location = (string)b["LOCATION_TYP"];
if (mrList.ContainsKey(location))
{
mrList[location].Buildings.Add(b);
}
else
{
Models.MarketReport mr = new Cresa.Models.MarketReport();
mr.Location = location;
mr.Buildings.Add(b);
mrList.Add(location, mr);
}
if (!mrList[location].BuildingLocation.ContainsKey((decimal)b["BUILDING_ID"]))
{
mrList[location].BuildingLocation.Add((decimal)b["BUILDING_ID"], new Models.BuildingLocation()
{
BuildingName = String.Format("{0}", b["BUILDING_NAME"]),
Floorplans = new List<Models.Floorplan>(),
Lat = String.Format("{0}", b["LATITUDE"]),
Long = String.Format("{0}", b["LONGITUDE"])
});
}
List<Models.Floorplan> floors = new List<Models.Floorplan>();
var floorResult = repo.QueryAll(String.Format("SELECT ID, FLOOR_NO FROM TBUILDING_PICTURES WHERE BUILDING_VACANCY_ID = {0} ", b["BUILDING_VACANCY_ID"]));
foreach (var f in floorResult)
{
string fPlan = String.Format("{0}", b["FLOOR"]);
if (f["FLOOR_NO"]!=null && f["FLOOR_NO"] != System.DBNull.Value)
{
fPlan= String.Format("{0}({1})", b["FLOOR"], f["FLOOR_NO"]);
}
mrList[location].BuildingLocation[(decimal)b["BUILDING_ID"]].Floorplans.Add(new Models.Floorplan() { ID = f["ID"].ToString(), FloorNo = fPlan });
}
}
int i = 1;
foreach (var a in mrList.ToArray())
{
a.Value.SectionNo = i.ToString();
i++;
}
var result = new Models.MarketReportModel
{
showTitle = showTitle,
building = mrList.Values.ToArray()
};
return result;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(String.Format("Unable to load market report. Reason: {0}", ex.Message));
throw;
}
return null;
}
protected bool ProcessMap(Dictionary<string, Tuple<int,string,decimal>> buildings, WordprocessingDocument wordDoc, string [] zoom, string [] lat, string [] lng, List<SizeAttribute> sa )
{
try
{
OracleRepository repo = new OracleRepository();
string markers = "";
foreach(var b in buildings.Values)
{
var latLongs = repo.QueryAll(
String.Format(@" SELECT TBUILDING.BUILDING_NAME, LATITUDE, LONGITUDE FROM TBUILDING_LAT_LONG
INNER JOIN TBUILDING ON TBUILDING_LAT_LONG.BUILDING_ID= TBUILDING.BUILDING_ID
WHERE TBUILDING_LAT_LONG.BUILDING_ID = {0} ", b.Item3));
foreach (var latLong in latLongs)
{
markers += String.Format("&markers=label:{0}%7C{1},{2}", b.Item1 + 1, latLong["LATITUDE"], latLong["LONGITUDE"]);
}
}
string zoomAmt = "";
string center = "";
if (zoom.Count() > 0)
{
int z = 14;
if (int.TryParse(zoom[0], out z))
{
zoomAmt = "&zoom=" + z;
}
}
if (lat.Count() > 0 && lng.Count() > 0)
{
double latAmt = 0;
double lngAmt = 0;
if (double.TryParse(lat[0], out latAmt) && double.TryParse(lng[0], out lngAmt))
{
center = String.Format("¢er={0},{1}", latAmt, lngAmt);
}
}
using (MemoryStream memStream = new MemoryStream())
{
try
{
WebRequest request = WebRequest.Create(
String.Format("https://maps.googleapis.com/maps/api/staticmap?format=jpg{0}{1}{2}&size=400x400&scale=2&key=AIzaSyB64gJHrp5QzW2fNMVRvRygTm8wCuBIrnc", markers, zoomAmt, center));
// Get the response.
WebResponse response = request.GetResponse();
// Get the stream containing content returned by the server.
using (Stream rStream = response.GetResponseStream())
{
rStream.CopyTo(memStream);
}
memStream.Seek(0, SeekOrigin.Begin);
string imageid = "";
MainDocumentPart mainPart = wordDoc.MainDocumentPart;
ImagePart imagePart = mainPart.AddImagePart(ImagePartType.Jpeg);
imagePart.FeedData(memStream);
foreach (var txt in wordDoc.MainDocumentPart.Document.Body.Descendants<Text>().Where(x => x.Text == ("<MAP_JG2016>")))
{
try
{
long width = 3813048;
long height = 3813048;
foreach (var s in sa)
{
if (s.TransformedValue == txt.Text)
{
width = s.Width;
height = s.Height;
}
}
imageid = mainPart.GetIdOfPart(imagePart);
var el = AddImageToBody(imageid, width, height);
txt.Text = "";
txt.Parent.Append (el);
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Unable to add image: Error:{0} Stack Trace: {1}", ex.Message, ex.StackTrace);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Unable to add image: Error:{0} Stack Trace: {1}", ex.Message, ex.StackTrace);
}
}
}
catch { }
return true;
}
protected List<Tuple<WmlDocument, string>> LoadDocuments(int id)
{
OracleRepository repo = new OracleRepository();
var childDocs = repo.QueryAll(String.Format(@"
Select ID, PARENT_DOCUMENT_ID, TYPE
from TDOCUMENT
where PARENT_DOCUMENT_ID = {0} AND STATUS <>'D'
ORDER BY SEQUENCE_NO
", id));
List<Tuple<WmlDocument,string>> wmldocs = new List<Tuple<WmlDocument, string>>();
/* Tuple<string, string>[] docs = new Tuple<string, string>[]
{
new Tuple<string, string>(@"d:\CurrentDevelopment\Cresa\Cresa\templates\map.dotx","SUMMARY"),
new Tuple<string,string> (@"d:\CurrentDevelopment\Cresa\Cresa\templates\section2.dotx","DETAIL"),
new Tuple<string,string> (@"d:\CurrentDevelopment\Cresa\Cresa\templates\section2a.dotx","DETAIL")
};*/
string docType = "";
foreach (var d in childDocs)//docs /*
{
using (Stream fs = repo.LoadDocument(String.Format("{0}",d["ID"]), out docType))//new MemoryStream())//
{
/* using (FileStream fs1 = System.IO.File.OpenRead(d.Item1))
{
fs1.CopyTo(fs);
}*/
wmldocs.Add(new Tuple<WmlDocument, string>(new WmlDocument("template1.docx", fs as MemoryStream), String.Format("{0}", d["TYPE"])));//d.Item2/*
}
}
return wmldocs;
}
public ActionResult ApplyDocumentOpenXml(string id, string building_vacancy_ids, string[] zoom, string[] lat, string[] lng)
{
OracleRepository repo = new OracleRepository();
var buildings = repo.QueryAll(String.Format(@"
Select VBUILDING.BUILDING_ID, VBUILDING.EXT_REF_ID, VBUILDING.BUILDING_NAME, VBUILDING.CLASS_TYP_ID, VBUILDING.CLASS_TYP,
VBUILDING.LOCATION_TYP_ID, VBUILDING.LOCATION_TYP, VBUILDING.QRTRLY_RPT_FLAG, VBUILDING.CONTACT_MECH_ID,
VBUILDING.ADDRESS1, VBUILDING.POSTAL_CODE, VBUILDING.YEAR_BUILT, VBUILDING.STOREYS, VBUILDING.NET_RENTABLE,
VBUILDING.FLOOR_PLATE, VBUILDING.PARKING_RATIO, VBUILDING.PARKING_RATIO_FORMATTED, VBUILDING.LATEST_OP_COST_AMT,
VBUILDING.OP_COST_YEAR, VBUILDING.LANDLORD_CONTACT_ID, VBUILDING.LANDLORD_NAME, VBUILDING.OWNER_CONTACT_ID,
VBUILDING.OWNER_NAME, VBUILDING.CREATE_DATE, VBUILDING.CREATE_USER, VBUILDING.UPDATE_DATE,
VBUILDING.UPDATE_USER, VBUILDING_VACANCY.BUILDING_VACANCY_ID, VBUILDING_VACANCY.LEASE_TYP_ID,
VBUILDING_VACANCY.LEASE_TYP_CODE, VBUILDING_VACANCY.LEASE_TYP, VBUILDING_VACANCY.SUITE_NO, VBUILDING_VACANCY.FLOOR,
VBUILDING_VACANCY.FLOOR_NUMERIC, VBUILDING_VACANCY.SIZE_SQ, VBUILDING_VACANCY.SMALLEST_DIV,
VBUILDING_VACANCY.INDUCEMENTS, VBUILDING_VACANCY.BASE_RENT, VBUILDING_VACANCY.TERM, VBUILDING_VACANCY.ARCHIVED_YN,
VBUILDING_VACANCY.LAST_ARCHIVED_DATE, VBUILDING_VACANCY.RETAIL_YN, VBUILDING_VACANCY.EXCLUSIVE_YN,
VBUILDING_VACANCY.VACANCY_DESC, VBUILDING_VACANCY.BROKER_NOTES, TBUILDING_LAT_LONG.LATITUDE, TBUILDING_LAT_LONG.LONGITUDE
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
left join TBUILDING_LAT_LONG on VBUILDING.BUILDING_ID = TBUILDING_LAT_LONG.BUILDING_ID
where BUILDING_VACANCY_ID In ({0})
ORDER BY VBUILDING.BUILDING_NAME
", building_vacancy_ids));
Dictionary<string, Tuple<int, string,decimal>> buildingIndices = new Dictionary<string, Tuple<int, string, decimal>>();
Dictionary<string, Tuple<string, decimal>> floorplanIndices = new Dictionary<string, Tuple<string, decimal>>();
Dictionary<int, XElement> buildingXML = new Dictionary<int, XElement>();
Stream st = new MemoryStream();
string docType = "PARENT TEMPLATE";
List<SizeAttribute> sa = new List<SizeAttribute>();
string imageid = "";
using (Stream fs = repo.LoadDocument(id, out docType))//new MemoryStream())//
{
/* using (FileStream fs1 = System.IO.File.OpenRead(@"d:\CurrentDevelopment\Cresa\Cresa\templates\TOC.dotx"))
{
fs1.CopyTo(fs);
}*/
WmlDocument wmlDoc = new WmlDocument("template1.docx", fs as MemoryStream, true);
WmlDocument wmlAssembledDoc;
var xml = GenerateDataFromDataSource(buildings.ToList(),
ref buildingIndices, ref floorplanIndices,
ref buildingXML);
if (docType=="PARENT TEMPLATE")
{
List<Source> sources = new List<Source>();
sources.Add(new Source (wmlDoc,true));
List<Tuple<WmlDocument, string>> docs = new List<Tuple<WmlDocument,string>>();
docs = LoadDocuments(int.Parse(id));
int index = 0;
foreach (var b in buildings.ToList())
{
foreach (var d in docs)
{
if (index==0 || d.Item2=="DETAIL")
{
if (d.Item2 == "SUMMARY")
{
sources.Add(LoadDocumentToSource(d.Item1, xml,buildingIndices,zoom,lat,lng, sa));
}
else
{
sources.Add(LoadDocumentToSource(d.Item1, buildingXML[index],null, null,null,null, sa));
}
}
}
index++;
}
wmlAssembledDoc = DocumentBuilder.BuildDocument(sources);
}
else
{
bool templateError;
wmlAssembledDoc = DocumentAssembler.AssembleDocument(wmlDoc, xml, out templateError, sa);
}
OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wmlAssembledDoc);
streamDoc.GetModifiedDocument().WriteByteArray(st);
string filename = "";
using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(st, true))
{
int index = 0;
List<decimal> building_ids = new List<decimal>();
foreach (var building in buildings)
{
building_ids.Add((decimal)building["BUILDING_ID"]);
}
ProcessMap(buildingIndices, wordDoc, zoom, lat, lng, sa);
ProcessFloorplan(floorplanIndices, wordDoc, sa);
foreach (var b in buildingIndices.Keys)
{
using (var picStream = repo.GetBuildingPicture(buildingIndices[b].Item3, out filename))
{
picStream.Position = 0;
MainDocumentPart mainPart = wordDoc.MainDocumentPart;
ImagePart imagePart = mainPart.AddImagePart(ImagePartType.Jpeg);
imagePart.FeedData(picStream);
imageid = mainPart.GetIdOfPart(imagePart);
}
foreach (var txt in wordDoc.MainDocumentPart.Document.Body.Descendants<Text>().Where(x => x.Text == b))
{
try
{
long width = 1906524;
long height = 1906524;
foreach (var s in sa)
{
if (s.TransformedValue == txt.Text)
{
width = s.Width;
height = s.Height;
}
}
var el = AddImageToBody(imageid, width, height);
txt.Parent.AppendChild(el);
txt.Text = "";
}
catch { }
}
index++;
}
}
}
string ext = "doc";
var cd = new System.Net.Mime.ContentDisposition
{
FileName = "template." + ext,
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
st.Position = 0;
return new FileStreamResult(st, "application/msword");
}
public JsonResult GetVacancies(decimal id)
{
try
{
OracleRepository repo = new OracleRepository();
string qry = String.Format("SELECT * FROM VBUILDING_VACANCY WHERE BUILDING_ID ={0}", id);
var result = repo.QueryAll(qry);
List<string> allFloors = new List<string>();
foreach (var r in result)
{
allFloors.Add(String.Format("{0}", r["FLOOR_NO"]));
}
// string[] availableFloors = GetAvailableFloors(allFloors);
return Json(new
{
Rows= result
}, JsonRequestBehavior.AllowGet);
}
catch(Exception ex)
{
return Json(ex.Message, JsonRequestBehavior.AllowGet);
}
}
public JsonResult GetAllClasses()
{
OracleRepository repo = new OracleRepository();
var result = repo.QueryAll("SELECT TYP_ID, TYP_NAME FROM VCLASS_TYPE");
var lstresult = result.ToList();
var dict = new Dictionary<string, object>();
dict.Add("TYP_ID", 0);
dict.Add("TYP_NAME", "");
lstresult.Add(dict);
return Json(lstresult, JsonRequestBehavior.AllowGet);
}
public bool ProcessFloorplan(decimal building_id, decimal building_vacancy_id, string building_vacancy_floor, Stream childFS, ref Novacode.DocX docx)
{
try
{
OracleRepository repo = new OracleRepository();
string qry = String.Format(@" SELECT ID,FLOOR_NO FROM TBUILDING_PICTURES WHERE BUILDING_ID ={0} AND BUILDING_VACANCY_ID = {1}", building_id, building_vacancy_id);
var floors = repo.QueryAll(qry);
string filename = "";
string floor_no = "";
foreach (var floor in floors)
{
using (var childDocx = Novacode.DocX.Load(childFS))
{
using (var picStream = repo.GetPicture((decimal)floor["ID"], out filename, out floor_no))
{
int newHeight = 300;
using (var tempImg = System.Drawing.Bitmap.FromStream(picStream))
{
newHeight = (int)Math.Round(((double)tempImg.Height / (double)tempImg.Width) * 300);
using (System.Drawing.Bitmap bt = new System.Drawing.Bitmap(tempImg, 300, newHeight))
{
using (MemoryStream ms = new MemoryStream())
{
bt.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
ms.Seek(0, SeekOrigin.Begin);
Novacode.Image img = childDocx.AddImage(ms); // Create image.
Novacode.Picture pic1 = img.CreatePicture(); // Create picture.
Novacode.Paragraph p = childDocx.InsertParagraph();
p.InsertPicture(pic1); // Insert picture into paragraph.
}
}
}
}
string picDesc = "";
if (floor["FLOOR_NO"]!=null && floor["FLOOR_NO"] != System.DBNull.Value)
{
picDesc = String.Format("({0})", floor["FLOOR_NO"]);
}
childDocx.ReplaceText(String.Format("<{0}>", "FLOOR_NO"), String.Format("{0}{1}", building_vacancy_floor, picDesc));
docx.InsertSectionPageBreak();
docx.InsertDocument(childDocx);
}
}
}
catch { }
return true;
}
public JsonResult GetAllContacts()
{
OracleRepository repo = new OracleRepository();
var result = repo.QueryAll("SELECT CONTACT_ID, CONTACT_NAME FROM VCONTACT");
var lstresult = result.ToList();
var dict = new Dictionary<string, object>();
dict.Add("CONTACT_ID", 0);
dict.Add("CONTACT_NAME", "");
lstresult.Add(dict);
return Json(lstresult, JsonRequestBehavior.AllowGet);
}
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 GetBuilding(string id)
{
OracleRepository repo = new OracleRepository();
int nBuildingID = int.Parse(id);
var query = String.Format(@"
SELECT b.BUILDING_ID,
b.BUILDING_NAME,
b.CLASS_TYP,
b.CLASS_TYP_ID,
b.LOCATION_TYP,
b.QRTRLY_RPT_FLAG,
b.ADDRESS1,
b.POSTAL_CODE,
b.YEAR_BUILT,
b.STOREYS,
b.NET_RENTABLE,
b.FLOOR_PLATE,
b.PARKING_RATIO_FORMATTED,
b.latest_op_cost_amt,
op_cost_year latest_op_cost_year,
b.OWNER_NAME,
nvl(b.OWNER_CONTACT_ID, 0) OWNER_CONTACT_ID,
B.LANDLORD_NAME,
nvl(b.LANDLORD_CONTACT_ID,0) LANDLORD_CONTACT_ID,
nvl(total_vacancy_avail,0) total_vacancy_avail,
vp.headlease,vp.sublease,vp.ghost,vp.upcoming,
b.CREATE_DATE,
b.CREATE_USER,
b.UPDATE_DATE,
b.UPDATE_USER,
t.LATITUDE,
t.LONGITUDE
FROM VBUILDING b INNER JOIN (SELECT BUILDING_ID, SUM(SIZE_SQ) AS total_vacancy_avail
FROM VBUILDING_VACANCY
GROUP BY BUILDING_ID) va ON b.BUILDING_ID = va.BUILDING_ID INNER JOIN
VBUILDING_VACANCY_PIVOT vp ON b.BUILDING_ID = vp.BUILDING_ID LEFT OUTER JOIN
TBUILDING_LAT_LONG t ON t.BUILDING_ID = b.BUILDING_ID
WHERE
b.BUILDING_ID = {0} ", nBuildingID);
var result = repo.QueryAll(query).First();
return Json(result, JsonRequestBehavior.AllowGet);
}
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 ActionResult ApplyDocument(string id, string building_vacancy_ids, string[] zoom, string [] lat, string [] lng)
{
OracleRepository repo = new OracleRepository();
var buildings = repo.QueryAll(String.Format(@"
Select VBUILDING.BUILDING_ID, VBUILDING.EXT_REF_ID, VBUILDING.BUILDING_NAME, VBUILDING.CLASS_TYP_ID, VBUILDING.CLASS_TYP,
VBUILDING.LOCATION_TYP_ID, VBUILDING.LOCATION_TYP, VBUILDING.QRTRLY_RPT_FLAG, VBUILDING.CONTACT_MECH_ID,
VBUILDING.ADDRESS1, VBUILDING.POSTAL_CODE, VBUILDING.YEAR_BUILT, VBUILDING.STOREYS, VBUILDING.NET_RENTABLE,
VBUILDING.FLOOR_PLATE, VBUILDING.PARKING_RATIO, VBUILDING.PARKING_RATIO_FORMATTED, VBUILDING.LATEST_OP_COST_AMT,
VBUILDING.OP_COST_YEAR, VBUILDING.LANDLORD_CONTACT_ID, VBUILDING.LANDLORD_NAME, VBUILDING.OWNER_CONTACT_ID,
VBUILDING.OWNER_NAME, VBUILDING.CREATE_DATE, VBUILDING.CREATE_USER, VBUILDING.UPDATE_DATE,
VBUILDING.UPDATE_USER, VBUILDING_VACANCY.BUILDING_VACANCY_ID, VBUILDING_VACANCY.LEASE_TYP_ID,
VBUILDING_VACANCY.LEASE_TYP_CODE, VBUILDING_VACANCY.LEASE_TYP, VBUILDING_VACANCY.SUITE_NO, VBUILDING_VACANCY.FLOOR,
VBUILDING_VACANCY.FLOOR_NUMERIC, VBUILDING_VACANCY.SIZE_SQ, VBUILDING_VACANCY.SMALLEST_DIV,
VBUILDING_VACANCY.INDUCEMENTS, VBUILDING_VACANCY.BASE_RENT, VBUILDING_VACANCY.TERM, VBUILDING_VACANCY.ARCHIVED_YN,
VBUILDING_VACANCY.LAST_ARCHIVED_DATE, VBUILDING_VACANCY.RETAIL_YN, VBUILDING_VACANCY.EXCLUSIVE_YN,
VBUILDING_VACANCY.VACANCY_DESC, VBUILDING_VACANCY.BROKER_NOTES, TBUILDING_LAT_LONG.LATITUDE, TBUILDING_LAT_LONG.LONGITUDE
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
left join TBUILDING_LAT_LONG on VBUILDING.BUILDING_ID = TBUILDING_LAT_LONG.BUILDING_ID
where BUILDING_VACANCY_ID In ({0})
", building_vacancy_ids));
Stream st = new MemoryStream();
string docType = ""; ;
using (Stream fs = repo.LoadDocument(id, out docType))
{
var docx = Novacode.DocX.Load(fs);
if (docType== "USER TEMPLATE")
{
//TODO
}
else
{
Dictionary<string, int> tokenIndexes = new Dictionary<string, int>();
foreach (var k in buildings.First().Keys)
{
foreach (var p in docx.Paragraphs)
{
var paraIndex = p.FindAll(String.Format("<{0}>", k));
if (paraIndex.Count > 0)
{
tokenIndexes.Add(k, paraIndex.First());
}
}
}
foreach (Dictionary<string, object> b in buildings)
{
foreach (var token in tokenIndexes.Keys)
{
int paraIndex = tokenIndexes[token];
var para = docx.Paragraphs[paraIndex];
docx.ReplaceText(String.Format("<{0}>", token), "");
docx.InsertParagraph(String.Format("{0}", b[token]), false);
}
}
ProcessChildPage(id, buildings, zoom, lat, lng, ref docx);
}
docx.SaveAs(st);
}
var cd = new System.Net.Mime.ContentDisposition
{
FileName = "template.doc",
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
st.Position = 0;
return new FileStreamResult(st, "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
}
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 ProcessParkingList(decimal building_id, ref Novacode.DocX docx)
{
try
{
OracleRepository repo = new OracleRepository();
var parkingList = repo.QueryAll(
String.Format(@"
SELECT
PARKING_ID,
BUILDING_ID,
PARKING_TYP,
NUM_STALLS,
COST_AMT,
NOTE,
INFO_AS_OF_DATE,
CREATE_DATE,
CREATE_USER,
UPDATE_DATE,
UPDATE_USER
FROM VPARKING WHERE BUILDING_ID={0} ", building_id));
string strParkingList = "";
foreach (Dictionary<string, object> p in parkingList)
{
if (String.IsNullOrEmpty(String.Format("{0}", p["COST_AMT"])))
{
strParkingList = String.Format("{0}", p["PARKING_TYP"]);
}
else
{
strParkingList += String.Format("$ {0} per stall, \n {1} ", p["COST_AMT"], p["PARKING_TYP"]);
}
}
docx.ReplaceText("<PARKING_LIST>", strParkingList);
}
catch { }
return true;
}