public SiteReportVM GetSiteReportSummary(Int64 SiteId)
{
SiteReportVM SiteReportvm = new SiteReportVM();
try
{
DataSet ds = sd.GetSiteTestSummary(SiteId);
DataTable dtTestSummary = ds.Tables[0];
DataTable dtConfigurationSettigs = ds.Tables[1];
DataTable dtPlots = ds.Tables[2];
List <DataRow> drSite = dtTestSummary.Select("SiteId='" + SiteId + "'").Distinct().ToList();
SitesVM loSite = new SitesVM();
#region SitesVM
loSite.SiteId = cnvtr.Int64(drSite[0]["SiteId"].ToString());
loSite.SiteCode = drSite[0]["Site"] is DBNull ? "" : drSite[0]["Site"].ToString();
loSite.Region = drSite[0]["Region"] is DBNull ? "" : drSite[0]["Region"].ToString();
loSite.Market = drSite[0]["City"] is DBNull ? "" : drSite[0]["City"].ToString();
loSite.Latitude = cnvtr.Double(drSite[0]["Latitude"].ToString());
loSite.Longitude = cnvtr.Double(drSite[0]["Longitude"].ToString());
loSite.ScheduledOn = drSite[0]["ScheduledOn"] is DBNull?Convert.ToDateTime("11/15/2016 00:00") : Convert.ToDateTime(drSite[0]["ScheduledOn"].ToString());
loSite.SubmittedOn = drSite[0]["SubmittedOn"] is DBNull?Convert.ToDateTime("11/15/2016 00:00") : Convert.ToDateTime(drSite[0]["SubmittedOn"].ToString());
loSite.CompletedOn = drSite[0]["CompletedOn"] is DBNull?Convert.ToDateTime("11/15/2016 00:00") : Convert.ToDateTime(drSite[0]["CompletedOn"].ToString());
loSite.ClientLogo = drSite[0]["ClientLogo"] is DBNull ? "" : drSite[0]["ClientLogo"].ToString();
loSite.VendorLogo = drSite[0]["VendorLogo"] is DBNull ? "" : drSite[0]["VendorLogo"].ToString();
#endregion
DataView viewBands = new DataView(dtTestSummary);
DataTable dtBands = viewBands.ToTable(true, "BandId", "Band", "NetworkMode", "Carrier", "Scope");
#region lstBands
List <BandVM> lstBands = new List <BandVM>();
BandVM loBand;
foreach (DataRow drBand in dtBands.Rows)
{
loBand = new BandVM();
loBand.BandId = drBand["BandId"] is DBNull ? 0 : Convert.ToInt32(drBand["BandId"]);
loBand.BandName = drBand["Band"] is DBNull ? "" : Convert.ToString(drBand["Band"]);
loBand.NetworkMode = drBand["NetworkMode"] is DBNull ? "" : Convert.ToString(drBand["NetworkMode"]);
loBand.Carrier = drBand["Carrier"] is DBNull ? "" : Convert.ToString(drBand["Carrier"]);
loBand.Scope = drBand["Scope"] is DBNull ? "" : Convert.ToString(drBand["Scope"]);
DataView viewSectors = new DataView(dtTestSummary, "BandId = '" + loBand.BandId + "'", "SectorId", DataViewRowState.CurrentRows);
DataTable dtSectors = viewBands.ToTable(true);
#region TestResult And lstsectors
List <SectorsVM> lstsectors = new List <SectorsVM>();
SectorsVM loSector;
TestResultVm TestResult;
foreach (DataRow drSector in dtSectors.Rows)
{
loSector = new SectorsVM();
loSector.SectorCode = drSector["Sector"] is DBNull ? "" : Convert.ToString(drSector["Sector"]);
loSector.PCI = drSector["PciId"] is DBNull ? 0 : Convert.ToInt32(drSector["PciId"]);
loSector.Azimuth = drSector["Azimuth"] is DBNull ? (float)0 : (float)Convert.ToDouble(drSector["Azimuth"]);
loSector.BeamWidth = drSector["BeamWidth"] is DBNull ? (float)0 : (float)Convert.ToDouble(drSector["BeamWidth"]);
TestResult = new TestResultVm();
TestResult.DownlinkAvgResult = cnvtr.Double(drSector["DownlinkAvgResult"].ToString());
TestResult.DownlinkMaxResult = cnvtr.Double(drSector["DownlinkMaxResult"].ToString());
TestResult.DownlinkMinResult = cnvtr.Double(drSector["DownlinkMinResult"].ToString());
TestResult.DownlinkRate = drSector["DownlinkRate"] is DBNull ? "" : Convert.ToString(drSector["DownlinkRate"]);
if (!string.IsNullOrEmpty(drSector["DownlinkStatus"].ToString()))
{
TestResult.DownlinkStatus = Convert.ToBoolean(drSector["DownlinkStatus"]);
}
if (!string.IsNullOrEmpty(drSector["FtpStatus"].ToString()))
{
TestResult.FtpStatus = cnvtr.Bool(drSector["FtpStatus"].ToString());
}
TestResult.GsmRssi = cnvtr.Int32(drSector["GsmRssi"].ToString());
TestResult.GsmRxQual = cnvtr.Int32(drSector["GsmRxQual"].ToString());
TestResult.LatencyRate = drSector["LatencyRate"] is DBNull ? "" : Convert.ToString(drSector["LatencyRate"]);
TestResult.LteCqi = cnvtr.Double(drSector["LteCqi"].ToString());
TestResult.LteRsnr = cnvtr.Int32(drSector["LteRsnr"].ToString());
TestResult.LteRsrp = cnvtr.Int32(drSector["LteRsrp"].ToString());
TestResult.LteRsrq = cnvtr.Int32(drSector["LteRsrq"].ToString());
TestResult.LteRssi = cnvtr.Int32(drSector["LteRssi"].ToString());
TestResult.PciId = drSector["DownlinkAvgResult"] is DBNull ? "" : Convert.ToString(drSector["PciId"]);
TestResult.PingAverageResult = cnvtr.Double(drSector["PingAverageResult"].ToString());
TestResult.PingHost = drSector["PingHost"] is DBNull ? "" : Convert.ToString(drSector["PingHost"]);
TestResult.PingIterations = drSector["PingIterations"] is DBNull ? "" : Convert.ToString(drSector["PingIterations"]);
TestResult.PingMaxResult = drSector["PingMaxResult"] is DBNull ? "" : Convert.ToString(drSector["PingMaxResult"]);
TestResult.PingMinResult = cnvtr.Double(drSector["PingMinResult"].ToString());
if (!string.IsNullOrEmpty(drSector["PingStatus"].ToString()))
{
TestResult.PingStatus = cnvtr.Bool(drSector["PingStatus"].ToString());
}
TestResult.UplinkMinResult = cnvtr.Double(drSector["UplinkMinResult"].ToString());
TestResult.UplinkRate = cnvtr.Double(drSector["UplinkRate"].ToString());
TestResult.WcdmaEcio = cnvtr.Double(drSector["WcdmaEcio"].ToString());
TestResult.WcdmaRscp = cnvtr.Int32(drSector["WcdmaRscp"].ToString());
TestResult.WcdmaRssi = cnvtr.Int32(drSector["WcdmaRssi"].ToString());
if (!string.IsNullOrEmpty(drSector["Ccwhandoverstatus"].ToString()))
{
TestResult.Ccwhandoverstatus = cnvtr.Bool(drSector["Ccwhandoverstatus"].ToString());
}
if (!string.IsNullOrEmpty(drSector["ConnectionSetupStatus"].ToString()))
{
TestResult.ConnectionSetupStatus = cnvtr.Bool(drSector["ConnectionSetupStatus"].ToString());
TestResult.MoStatus = cnvtr.Bool(drSector["MoStatus"].ToString());
}
TestResult.ConnectionSetupTime = drSector["ConnectionSetupTime"] is DBNull ? "" : Convert.ToString(drSector["ConnectionSetupTime"]);
if (!string.IsNullOrEmpty(drSector["CwHandoverStatus"].ToString()))
{
TestResult.CwHandoverStatus = cnvtr.Bool(drSector["CwHandoverStatus"].ToString());
}
TestResult.FtpDownlinkFile = drSector["FtpDownlinkFile"] is DBNull ? "" : Convert.ToString(drSector["FtpDownlinkFile"]);
TestResult.FtpServerIp = drSector["FtpServerIp"] is DBNull ? "" : Convert.ToString(drSector["FtpServerIp"]);
TestResult.FtpServerPath = drSector["FtpServerPath"] is DBNull ? "" : Convert.ToString(drSector["FtpServerPath"]);
TestResult.FtpServerPort = drSector["FtpServerPort"] is DBNull ? "" : Convert.ToString(drSector["FtpServerPort"]);
TestResult.MoMtCallDuration = drSector["MoMtCallDuration"] is DBNull ? "" : Convert.ToString(drSector["MoMtCallDuration"]);
TestResult.MoMtCallNo = drSector["MoMtCallNo"] is DBNull ? "" : Convert.ToString(drSector["MoMtCallNo"]);
if (!string.IsNullOrEmpty(drSector["MoStatus"].ToString()))
{
TestResult.MoStatus = cnvtr.Bool(drSector["MoStatus"].ToString());
}
if (!string.IsNullOrEmpty(drSector["MtStatus"].ToString()))
{
TestResult.MtStatus = cnvtr.Bool(drSector["MtStatus"].ToString());
}
TestResult.UplinkAvgResult = cnvtr.Double(drSector["UplinkAvgResult"].ToString());
TestResult.UplinkMaxResult = cnvtr.Double(drSector["UplinkMaxResult"].ToString());
if (!string.IsNullOrEmpty(drSector["UplinkStatus"].ToString()))
{
TestResult.UplinkStatus = cnvtr.Bool(drSector["UplinkStatus"].ToString());
}
TestResult.VMoMtCallDuration = drSector["VMoMtCallDuration"] is DBNull ? "" : Convert.ToString(drSector["VMoMtCallDuration"]);
TestResult.VMoMtCallno = drSector["VMoMtCallno"] is DBNull ? "" : Convert.ToString(drSector["VMoMtCallno"]);
if (!string.IsNullOrEmpty(drSector["VMoStatus"].ToString()))
{
TestResult.VMoStatus = cnvtr.Bool(drSector["VMoStatus"].ToString());
}
if (!string.IsNullOrEmpty(drSector["VMtStatus"].ToString()))
{
TestResult.VMtStatus = cnvtr.Bool(drSector["VMtStatus"].ToString());
}
TestResult.StationaryTestFilePath = drSector["StationaryTestFilePath"] is DBNull ? "" : Convert.ToString(drSector["StationaryTestFilePath"]);
TestResult.CwTestFilePath = drSector["CwTestFilePath"] is DBNull ? "" : Convert.ToString(drSector["CwTestFilePath"]);
TestResult.CcwTestFilePath = drSector["CcwTestFilePath"] is DBNull ? "" : Convert.ToString(drSector["CcwTestFilePath"]);
TestResult.OoklaDownLinkResult = cnvtr.Double(drSector["OoklaDownlinkResult"].ToString());
TestResult.OoklaUplinkResult = cnvtr.Double(drSector["OoklaUplinkResult"].ToString());
TestResult.Latitude = drSector["Latitude"] is DBNull ? "" : Convert.ToString(drSector["Latitude"]);
TestResult.Longitude = drSector["Longitude"] is DBNull ? "" : Convert.ToString(drSector["Longitude"]);
TestResult.TestLatitude = cnvtr.Double(drSector["TestLatitude"].ToString());
TestResult.TestLongitude = cnvtr.Double(drSector["TestLongitude"].ToString());
TestResult.Band = drSector["Band"].ToString();
TestResult.IRATHandover = cnvtr.Bool(drSector["IRATHandover"].ToString());
loSector.TestResult = TestResult;
lstsectors.Add(loSector);
}
loBand.Sectors = lstsectors;
#endregion
DataView vSectors = new DataView(dtTestSummary, "SiteId = '" + loSite.SiteId + "'", "SectorId", DataViewRowState.CurrentRows);
DataTable tblSectors = vSectors.ToTable(true);
DataView viewPlots = new DataView(dtPlots);
DataTable dtPlot = viewPlots.ToTable(true);
#region SiteReportPlotVM
List <SiteReportPlotVM> lstPlots = new List <SiteReportPlotVM>();
SiteReportPlotVM plot;
foreach (DataRow drPlot in dtPlot.Rows)
{
plot = new SiteReportPlotVM();
plot.Latitude = Convert.ToDouble(drPlot["Latitude"] is DBNull ? 0 : drPlot["Latitude"]);
plot.Longitude = Convert.ToDouble(drPlot["Longitude"] is DBNull ? 0 : drPlot["Longitude"]);
plot.PCI = drPlot["PciId"] is DBNull ? "" : drPlot["PciId"].ToString();
plot.MarkerImagePath = "https://upload.wikimedia.org/wikipedia/commons/thumb/0/0e/Ski_trail_rating_symbol-green_circle.svg/768px-Ski_trail_rating_symbol-green_circle.svg.png";
plot.siteLatitude = drSite[0]["Latitude"] is DBNull ? 0 : Convert.ToDouble(drSite[0]["Latitude"].ToString());
plot.siteLongitude = drSite[0]["Longitude"] is DBNull ? 0 : Convert.ToDouble(drSite[0]["Longitude"].ToString());
plot.IsHandover = Convert.ToBoolean(drPlot["IsHandover"].ToString());
ColorCollection cc = new ColorCollection();
#region LTE Color
DataRow[] drows = tblSectors.Select("PciId=" + "'" + plot.PCI + "'");
if (drows.Count() > 0)
{
int SelectedIndex = tblSectors.Rows.IndexOf(drows[0]);
plot.plotColor = cc.LTE_PciId(SelectedIndex);
}
if (loBand.NetworkMode.ToString() == "LTE")
{
int LteRsrp = drPlot["LteRsrp"] is DBNull ? 0 : Convert.ToInt32(drPlot["LteRsrp"]);
plot.rsrpColor = cc.LTERsrp(LteRsrp);
int LteRsrq = drPlot["LteRsrq"] is DBNull ? 0 : Convert.ToInt32(drPlot["LteRsrq"]);
plot.rsrqColor = cc.LTERsrq(LteRsrq);
int Ltesinr = drPlot["LteRsnr"] is DBNull ? 0 : Convert.ToInt32(drPlot["LteRsnr"]);
plot.rsnrColor = cc.LTEsinr(Ltesinr);
}
#endregion
#region WCDMA Color
if (loBand.NetworkMode.ToString() == "WCDMA")
{
int WcdmaEcio = drPlot["WcdmaEcio"] is DBNull ? 0 : Convert.ToInt32(drPlot["WcdmaEcio"]);
plot.rsrpColor = cc.WCDMAEcio(WcdmaEcio);
}
#endregion
#region GSM Color
if (loBand.NetworkMode.ToString() == "GSM")
{
int GsmRssi = drPlot["GsmRssi"] is DBNull ? 0 : Convert.ToInt32(drPlot["GsmRssi"]);
plot.rsrpColor = cc.GsmRssi(GsmRssi);
}
#endregion
lstPlots.Add(plot);
}
#endregion
loBand.PciPlot = lstPlots;
lstBands.Add(loBand);
}
#endregion
loSite.Bands = lstBands;
DataView view = new DataView(dtConfigurationSettigs);
DataTable distinctValues = view.ToTable(true, "TestCateoryId", "TestCategory");
List <TestCategoryVM> lstTestCategories = new List <TestCategoryVM>();
TestCategoryVM cat;
foreach (DataRow dr in distinctValues.Rows)
{
cat = new TestCategoryVM();
cat.TestCategoryId = cnvtr.Int64(dr["TestCateoryId"].ToString());
cat.TestCategoryName = dr["TestCategory"] is DBNull ? "" : dr["TestCategory"].ToString();
DataView v = new DataView(dtConfigurationSettigs, "TestCateoryId = '" + cat.TestCategoryId + "'", "TestTypeId", DataViewRowState.CurrentRows);
DataTable dtTypes = v.ToTable(true, "TestTypeId", "TestType");
List <TestTypeVM> lstTestTypes = new List <TestTypeVM>();
TestTypeVM type;
foreach (DataRow drtype in dtTypes.Rows)
{
type = new TestTypeVM();
type.TestTypeName = drtype["TestType"] is DBNull ? "" : drtype["TestType"].ToString();
type.TestTypeId = cnvtr.Int64(drtype["TestTypeId"].ToString());
DataView vkpis = new DataView(dtConfigurationSettigs, "TestTypeId = '" + type.TestTypeId + "'", "TestTypeId", DataViewRowState.CurrentRows);
DataTable dtKpi = vkpis.ToTable(true, "Kpi", "KpiValue");
List <TestKpiVM> lstKpi = new List <TestKpiVM>();
TestKpiVM kpi;
foreach (DataRow drkpi in dtKpi.Rows)
{
kpi = new TestKpiVM();
kpi.KpiName = drkpi["Kpi"] is DBNull ? "" : drkpi["Kpi"].ToString();
kpi.KpiValue = drkpi["KpiValue"] is DBNull ? "" : drkpi["KpiValue"].ToString();
lstKpi.Add(kpi);
}
type.TestKpi = lstKpi;
lstTestTypes.Add(type);
}
cat.TestTypes = lstTestTypes;
lstTestCategories.Add(cat);
}
SiteReportvm.Site = loSite;
SiteReportvm.TestCategories = lstTestCategories;
}
catch (Exception)
{
// throw;
}
return(SiteReportvm);
}