예제 #1
0
 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);
 }
예제 #2
0
        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);
        }
예제 #3
0
        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("&center={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;
        }
예제 #4
0
        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);
        }
예제 #5
0
 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);
 }
예제 #6
0
 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));
 }
예제 #7
0
 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);
     }
 }
예제 #8
0
        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);
            }
        }
예제 #9
0
        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;
        }
예제 #10
0
        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;
        }
예제 #11
0
        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("&center={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;
        }
예제 #12
0
        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;
        }
예제 #13
0
        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");
        }
예제 #14
0
 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);
     }
 }
예제 #15
0
 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);
 }
예제 #16
0
        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;
        }
예제 #17
0
 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);
 }
예제 #18
0
 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);
     }
 }
예제 #19
0
        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);
        }
예제 #20
0
        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;
        }
예제 #21
0
        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");
        }
예제 #22
0
        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);
        }
예제 #23
0
        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;
        }