/// <summary>
/// Description: Method to load data based on selected vessel and date range
/// </summary>
private void LoadData()
{
try
{
hdnVessel_IDs.Value = null;
string Qtr = "";
string sStartDate = txtStartDate.Text;
string sEndDate = txtEndDate.Text;
if (sStartDate != "" && sEndDate != "")
{
DateTime StartDate = Convert.ToDateTime(UDFLib.ConvertToDefaultDt(txtStartDate.Text)); //ConvertToDefaultDt() method is used to change the user selected date format to the default format, the format in which date is saved in database.
DateTime EndDate = Convert.ToDateTime(UDFLib.ConvertToDefaultDt(txtEndDate.Text));
//hdnLastMonth.Value = lastMonth;
DataSet ds = objKPI.Get_Vetting_KPI_ByCompany(Qtr, 0, StartDate, EndDate);
DataTable dt = ds.Tables[0];
string[] Pkey_cols = new string[] { "KPI_ID" };
string[] Hide_cols = new string[] { "ID", "Qtr_Start", "Qtr_End", "Code" };
DataTable dt1 = objKPI.PivotTable("Qtr", "KPI_Value", "", Pkey_cols, Hide_cols, dt);
gvCompanyKPI.DataSource = dt1;
gvCompanyKPI.DataBind();
IntializeControls();
string jsMethodName = null;
jsMethodName = "$(document).ready(function () {searchInitialize();});";
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "uniqueKey", jsMethodName, true);
}
}
catch (Exception ex)
{
UDFLib.WriteExceptionLog(ex);
}
}
/// <summary>
/// Event to export the tabular data to excel sheet
/// Created By: Krishnapriya
/// Created On: 07-MAR-2017
/// </summary>
protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=CrewRetentionDetails.xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
string Year = hdnYear.Value;
string VesselID = hdnVessel.Value;
string VettingTypeID = hdnVettingType.Value;
string ObvTypeID = hdnObvType.Value;
string CategoryID = hdnCategory.Value;
string FleetID = hdnFleet.Value;
//Fetch Observation by Vessel Count
DataTable dtRawdata;
dtRawdata = objVTReports.GetObservationsByVesselCnt(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols = new string[] { "Year" };
string[] Hide_cols = new string[] { "ID", "Year" };
DataTable dt = objKPI.PivotTable("Vessel", "Rec_Count", "", Pkey_cols, Hide_cols, dtRawdata);
string[] columnNames = dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Fleet Count
DataTable dtRawdata1;
dtRawdata1 = objVTReports.GetObservationsByFleetCnt(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols1 = new string[] { "Year" };
string[] Hide_cols1 = new string[] { "FleetCode" };
DataTable dt1 = objKPI.PivotTable("FleetName", "Rec_Count", "", Pkey_cols1, Hide_cols1, dtRawdata1);
string[] columnNames1 = dt1.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Vessel for Categories
DataTable dtRawdata2;
dtRawdata2 = objVTReports.GetVesselObservationCntByCategory(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols2 = new string[] { "ID" };
string[] Hide_cols2 = new string[] { "ID" };
DataTable dt2 = objKPI.PivotTable("CategoryName", "Rec_Count", "", Pkey_cols2, Hide_cols2, dtRawdata2);
string[] columnNames2 = dt2.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Fleet for Categories
DataTable dtRawdata3;
dtRawdata3 = objVTReports.GetFleetObservationCntByCategory(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols3 = new string[] { "FleetCode" };
string[] Hide_cols3 = new string[] { "FleetCode" };
DataTable dt3 = objKPI.PivotTable("CategoryName", "Rec_Count", "", Pkey_cols3, Hide_cols3, dtRawdata3);
string[] columnNames3 = dt3.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Observation by Vessel Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='10'><h3>Observations by Vessel</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Fleet Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='10'><h3>Observations by Fleet</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames1[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt1.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames1[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Vessel for Categories
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='10'><h3>Observations by Vessel</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames2[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt2.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames2[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
////Observation by Fleet for Categories
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='10'><h3>Observations by Fleet</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames3.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames3[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt3.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames3.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames3[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.End();
//GridViewExportUtil.ExportToExcel(dt1, columnNames, columnNames, "Observation_by_Vessel", "Observation by Vessel");
}
catch (Exception ex)
{
UDFLib.WriteExceptionLog(ex);
}
}
/// <summary>
/// Event to export the tabular data to excel sheet
/// Created By: Krishnapriya
/// Created On: 07-MAR-2017
/// </summary>
protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=ObservationByCategory.xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
string Year = hdnYear.Value;
string [] yearArr = Year.Split(',');
Array.Reverse(yearArr);
string VesselID = hdnVessel.Value;
string VettingTypeID = hdnVettingType.Value;
string ObvTypeID = hdnObvType.Value;
string CategoryID = hdnCategory.Value;
string FleetID = hdnFleet.Value;
//int columnCnt, columnCnt1, columnCnt2, columnCnt3;
int[] columnCnt = new int[4];
//Fetch Observation by Vessel Count
DataTable dtRawdata;
dtRawdata = objVTReports.GetObservationByCategoryCount(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols = new string[] { "Year" };
string[] Hide_cols = new string[] { "CategoryID", "ID", "Year" };
DataTable dt = objKPI.PivotTable("CategoryName", "Rec_Count", "", Pkey_cols, Hide_cols, dtRawdata);
columnCnt[0] = dt.Columns.Count;
string[] columnNames = dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Fleet Count
DataTable dtRawdata1;
DataTable dt1 = new DataTable();
dtRawdata1 = objVTReports.GetMultipleYearCategoryCount(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols1 = new string[] { "Year" };
string[] Hide_cols1 = new string[] { "ID" };
dt1 = objKPI.PivotTable("Category_Name", "Rec_Count", "", Pkey_cols, Hide_cols, dtRawdata1);
dt1.Columns.Add("Year", typeof(System.String)).SetOrdinal(0);
for (int j = 0; j < dt1.Rows.Count; j++)
{
for (int i = 0; i < yearArr.Length; i++)
{
if (i == j)
{
dt1.Rows[j]["Year"] = yearArr[i];
}
}
}
columnCnt[1] = dt1.Columns.Count;
string[] columnNames1 = dt1.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Vessel for Categories
DataTable dtRawdata2;
dtRawdata2 = objVTReports.GetVesselObservationCntByCategory(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols2 = new string[] { "ID" };
string[] Hide_cols2 = new string[] { "ID", "Category_ID" };
DataTable dt2 = objKPI.PivotTable("CategoryName", "Rec_Count", "", Pkey_cols2, Hide_cols2, dtRawdata2);
columnCnt[2] = dt2.Columns.Count;
string[] columnNames2 = dt2.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Fleet for Categories
DataTable dtRawdata3;
dtRawdata3 = objVTReports.GetFleetObservationCntByCategory(VesselID, Year, VettingTypeID, CategoryID, ObvTypeID, FleetID).Tables[0];
string[] Pkey_cols3 = new string[] { "FleetCode" };
string[] Hide_cols3 = new string[] { "FleetCode", "ID" };
DataTable dt3 = objKPI.PivotTable("CategoryName", "Rec_Count", "", Pkey_cols3, Hide_cols3, dtRawdata3);
columnCnt[3] = dt3.Columns.Count;
string[] columnNames3 = dt3.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
int maxColumnCnt = columnCnt.Max();
//Observation by Vessel Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[0] + "'><h3>Observations by Category</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Fleet Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[1] + "'><h3>Avg. Observation per Vetting</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames1[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt1.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames1[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
////Observation by Fleet for Categories
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[2] + "'><h3>Categories by Fleet</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames3.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames3[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt3.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames3.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames3[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Vessel for Categories
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[3] + "'><h3>Categories by Vessel</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames2[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt2.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames2[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.End();
//GridViewExportUtil.ExportToExcel(dt1, columnNames, columnNames, "Observation_by_Vessel", "Observation by Vessel");
}
catch (Exception ex)
{
UDFLib.WriteExceptionLog(ex);
}
}
/// <summary>
/// Event to export the tabular data to excel sheet
/// Created By: Harshal
/// Created On: 17-MAR-2017
/// </summary>
protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=ObservationbyOilMajor.xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
string Year = hdnYear.Value;
string VesselID = hdnVessel.Value;
string CategoryID = hdnCategory.Value;
string VettingTypeID = hdnVettingType.Value;
string ObvTypeID = hdnObvType.Value;
string FleetID = hdnFleet.Value;
string OilID = hdnOilMajor.Value;
int[] columnCnt = new int[4];
//Fetch Observation by OilMajorCount Yearwise
DataTable dtRawdata;
dtRawdata = objVTReports.GetVesselwiseOilMajors(Year, VesselID, CategoryID, VettingTypeID, ObvTypeID, FleetID,OilID).Tables[0];
string[] Pkey_cols = new string[] { "Year" };
string[] Hide_cols = new string[] { "ID", "Year" };
DataTable dt = objKPI.PivotTable("OilMajorName", "Rec_Count", "", Pkey_cols, Hide_cols, dtRawdata);
columnCnt[0] = dt.Columns.Count;
string[] columnNames = dt.Columns.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by OilMajorName Count yearwise
DataTable dtRawdata1;
dtRawdata1 = objVTReports.GetOilMajorCountYearwise(Year, VesselID, CategoryID, VettingTypeID, ObvTypeID, FleetID,OilID).Tables[0];
string[] Pkey_cols1 = new string[] { "OilMajorID" };
string[] Hide_cols1 = new string[] { "OilMajorID" };
DataTable dt1 = objKPI.PivotTable("Year", "Rec_Count", "", Pkey_cols1, Hide_cols1, dtRawdata1);
columnCnt[1] = dt1.Columns.Count;
string[] columnNames1 = dt1.Columns.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
int maxColumnCnt = columnCnt.Max();
//Observation by Oil Major Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + maxColumnCnt + "'><h3>Observations by Oil Major</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Oil MajorName TABLE YearWise
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[1] + "'><h3>Observations by Oil Major</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames1[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt1.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames1[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.End();
GridViewExportUtil.ExportToExcel(dt1, columnNames, columnNames, "Observation_by_OilMajor", "Observation by OilMajor");
}
catch (Exception ex)
{
UDFLib.WriteExceptionLog(ex);
}
}
/// <summary>
/// Based on search criteria , method will search the different PMS KPIvalues and pivot the Monthwise values into columns
/// </summary>
private void BindKPI()
{
try
{
string sStartDate = txtStartDate.Text;
string sEndDate = txtEndDate.Text;
if (sStartDate != "" && sEndDate != "")
{
DateTime StartDate = Convert.ToDateTime(UDFLib.ConvertToDefaultDt(txtStartDate.Text)); //ConvertToDefaultDt() method is used to change the user selected date format to the default format, the format in which date is saved in database.
DateTime EndDate = Convert.ToDateTime(UDFLib.ConvertToDefaultDt(txtEndDate.Text));
DateTime prevMonthLastDate = EndDate.AddDays(-EndDate.Day);
string lastMonth = String.Format("{0:y}", prevMonthLastDate);
hdnLastMonth.Value = lastMonth;
DataSet ds = objKPI.Get_PM_Overdue((DataTable)Session["Vessel_Id"], StartDate, EndDate, null);
DataTable dt = ds.Tables[0];
string[] Pkey_cols = new string[] { "VesselID", "KPI_ID" };
string[] Hide_cols = new string[] { "ID", "KPI_ID", "Effective_Date" };
DataTable dt1 = objKPI.PivotTable("MonthYear", "Value", "", Pkey_cols, Hide_cols, dt);
DataView dtVesselIDs = new DataView(dt1);
DataTable distinctValues = dtVesselIDs.ToTable(true, "VesselID");
string Vessel_Ids = "";
foreach (DataRow dr in distinctValues.Rows)
{
Vessel_Ids = Vessel_Ids + "," + dr["VesselID"].ToString();
}
Vessel_Ids = Vessel_Ids.Trim(',');
hdnVessel_IDs.Value = Vessel_Ids;
if (StartDate <= EndDate)
{
if ((EndDate - StartDate).TotalDays >= 28 || EndDate.Month != StartDate.Month)
{
if (dt1.Rows.Count > 0)
{
gvPMSOverdue.DataSource = dt1;
gvPMSOverdue.DataBind();
gvPMSOverdue.Visible = true;
string jsMethodName = null;
jsMethodName = "$(document).ready(function () {showChart('" + Vessel_Ids + "','" + Convert.ToDateTime(UDFLib.ConvertToDefaultDt(txtStartDate.Text)).ToString("yyyy-MM-dd") + "','" + lastMonth + "' ) });";
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "uniqueKey", jsMethodName, true);
}
else
{
gvPMSOverdue.Visible = false;
string msg2 = String.Format("showAlert('No record available! ')");
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "uniqueKey", msg2, true);
}
}
else
{
gvPMSOverdue.Visible = false;
string msg2 = String.Format("showAlert('No record available between this date range! ')");
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "uniqueKey", msg2, true);
}
}
else
{
gvPMSOverdue.Visible = false;
string msg2 = String.Format("showAlert('Start Date should not be greater than End Date ')");
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "uniqueKey", msg2, true);
}
}
}
catch (Exception ex)
{
UDFLib.WriteExceptionLog(ex);
}
}
/// <summary>
/// Event to export the tabular data to excel sheet
/// Created By: Harshal
/// Created On: 18-MAR-2017
/// </summary>
protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=ObservationByRiskLevel.xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
string Year = hdnYear.Value;
string VesselID = hdnVessel.Value;
string FleetID = hdnFleet.Value;
string RiskLevel = hdnRisk.Value;
string VettingTypeID = hdnVettingType.Value;
string ObvTypeID = hdnObvType.Value;
string CategoryID = hdnCategory.Value;
//int columnCnt, columnCnt1, columnCnt2, columnCnt3;
int[] columnCnt = new int[4];
//Fetch Observation by Risk Level with count grid1
DataTable dtRawdata;
dtRawdata = objVTReports.GetRiskLevelObservation(Year, VesselID, FleetID, RiskLevel, VettingTypeID, ObvTypeID, CategoryID).Tables[0];
string[] Pkey_cols = new string[] { "Year" };
string[] Hide_cols = new string[] { "ID", "Year" };
DataTable dt = objKPI.PivotTable("Risk_Level", "Rec_Count", "", Pkey_cols, Hide_cols, dtRawdata);
columnCnt[0] = dt.Columns.Count;
string[] columnNames = dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Risk Level Count for Vessels with yearwise grid2
DataTable dtRawdata1;
dtRawdata1 = objVTReports.GetRiskLevelObservationgrid(Year, VesselID, FleetID, RiskLevel, VettingTypeID, ObvTypeID, CategoryID).Tables[0];
string[] Pkey_cols1 = new string[] { "Vessel_ID" };
string[] Hide_cols1 = new string[] { "Vessel_ID" };
DataTable dt1 = objKPI.PivotTable("Year", "Rec_Count", "", Pkey_cols1, Hide_cols1, dtRawdata1);
columnCnt[1] = dt1.Columns.Count;
string[] columnNames1 = dt1.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//Fetch Observation by Risk Level Count for Vessel With yearwise grid3
DataTable dtRawdata2;
dtRawdata2 = objVTReports.GetRiskLevelObservationgrid(Year, VesselID, FleetID, RiskLevel, VettingTypeID, ObvTypeID, CategoryID).Tables[0];
string[] Pkey_cols2 = new string[] { "Vessel_ID" };
string[] Hide_cols2 = new string[] { "Vessel_ID" };
DataTable dt2 = objKPI.PivotTable("Year", "Rec_Count", "", Pkey_cols2, Hide_cols2, dtRawdata2);
columnCnt[2] = dt2.Columns.Count;
string[] columnNames2 = dt2.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
int maxColumnCnt = columnCnt.Max();
//Observation by Risk Level1 for grid1 Count TABLE
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + maxColumnCnt + "'><h3>Observations by Risk Level</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames.Length; i++)
{
HttpContext.Current.Response.Write("<td>");
HttpContext.Current.Response.Write(dr[columnNames[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Risk Level with vessels for grid2 TABLE YearWise
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[1] + "'><h3>High Risk by Vessel</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames1[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt1.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames1.Length; i++)
{
HttpContext.Current.Response.Write("<td style='text-align:center;'>");
HttpContext.Current.Response.Write(dr[columnNames1[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
//Observation by Risk Level with Fleets TABLE YearWise
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='0' width='100%'>");
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='text-align:center;' colspan='" + columnCnt[2] + "'><h3>Medium Risk by Vessel</h3></td>");
HttpContext.Current.Response.Write("</tr>");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='2' width='100%'>");
HttpContext.Current.Response.Write("<tr style='background-color: #F2F2F2;'>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td width='20%'>");
HttpContext.Current.Response.Write("<b>" + columnNames2[i] + "</b>");
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
foreach (DataRow dr in dt2.Rows)
{
HttpContext.Current.Response.Write("<tr>");
for (int i = 0; i < columnNames2.Length; i++)
{
HttpContext.Current.Response.Write("<td style='text-align:center;'>");
HttpContext.Current.Response.Write(dr[columnNames2[i]]);
HttpContext.Current.Response.Write("</td>");
}
HttpContext.Current.Response.Write("</tr>");
}
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.Write("</TABLE>");
HttpContext.Current.Response.Write("<br />");
HttpContext.Current.Response.End();
GridViewExportUtil.ExportToExcel(dt2, columnNames, columnNames, "Observation_by_RiskLevel", "Observation by RiskLevel");
}