/// <summary>
/// To Save Excel Data
/// </summary>
/// <param name="list1"></param>
/// <param name="uID"></param>
/// <returns></returns>
public string SaveExcelData(UploadPAPCoordinatesList list1, string uID)
{
string result = string.Empty;
OracleConnection myConnection;
OracleCommand myCommand;
//myConnection = new OracleConnection(AppConfiguration.ConnectionString);
////del
//if (list1.Count > 0)
//{
// myCommand = new OracleCommand("USP_TRN_DEL_PAP_COORDINATES", myConnection);
// myCommand.Connection = myConnection;
// myCommand.CommandType = CommandType.StoredProcedure;
// myCommand.Parameters.Add("HHID_", list1[0].HHID);
// myConnection.Open();
// myCommand.ExecuteNonQuery();
// myCommand.Dispose();
// myConnection.Close();
// myConnection.Dispose();
//}
//end
//insert
myConnection = new OracleConnection(AppConfiguration.ConnectionString);
myCommand = new OracleCommand("USP_TRN_INS_PAP_COORDINATES", myConnection);
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("HHID_", "");
myCommand.Parameters.Add("ROW_X_", "");
myCommand.Parameters.Add("ROW_Y_", "");
myCommand.Parameters.Add("ROW_LATITUDE_", "");
myCommand.Parameters.Add("ROW_LONGITUDE_", "");
myCommand.Parameters.Add("WL_X_", "");
myCommand.Parameters.Add("WL_Y_", "");
myCommand.Parameters.Add("WL_LATITUDE_", "");
myCommand.Parameters.Add("WL_LONGITUDE_", "");
myCommand.Parameters.Add("USERID_", "");
myCommand.Parameters.Add("ERRORMESSAGE_", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
myConnection.Open();
for (int i = 0; i < list1.Count; i++)
{
myCommand.Parameters["HHID_"].Value = list1[i].HHID;
myCommand.Parameters["ROW_X_"].Value = list1[i].ROW_X;
myCommand.Parameters["ROW_Y_"].Value = list1[i].ROW_Y;
myCommand.Parameters["ROW_LATITUDE_"].Value = list1[i].ROW_LATITUDE;
myCommand.Parameters["ROW_LONGITUDE_"].Value = list1[i].ROW_LONGITUDE;
myCommand.Parameters["WL_X_"].Value = list1[i].WL_X;
myCommand.Parameters["WL_Y_"].Value = list1[i].WL_Y;
myCommand.Parameters["WL_LATITUDE_"].Value = list1[i].WL_LATITUDE;
myCommand.Parameters["WL_LONGITUDE_"].Value = list1[i].WL_LONGITUDE;
myCommand.Parameters["USERID_"].Value = uID;
myCommand.ExecuteNonQuery();
}
myConnection.Close();
return(list1.Count.ToString());
}
/// <summary>
/// To Save Excel Data For All Paps
/// </summary>
/// <param name="List1"></param>
/// <param name="uID"></param>
/// <returns></returns>
public string SaveExcelDataForAllPaps(UploadPAPCoordinatesList List1, string uID)
{
UploadPAPCoordinatesDAL objUploadPAPCoordinatesDAL = new UploadPAPCoordinatesDAL();
return(objUploadPAPCoordinatesDAL.SaveExcelDataForAllPaps(List1, uID));
}
/// <summary>
/// Set the PAp coordinates to Map
/// </summary>
public string GetPap()
{
int HHID = 0;
if (Request.QueryString["HHID"] != null)
{
HHID = Convert.ToInt32(Request.QueryString["HHID"].ToString());
}
UploadPAPCoordinatesBLL objUploadPAPCoordinatesBLL = new UploadPAPCoordinatesBLL();
UploadPAPCoordinatesList objUploadPAPCoordinatesList = objUploadPAPCoordinatesBLL.GetAllPapCoordinatesData(HHID, Convert.ToInt32(Session["PROJECT_ID"]));
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int a = 0;
string s1 = "";
int ROWCount = 0;
for (int i = 0; i < objUploadPAPCoordinatesList.Count; i++)
{
UploadPAPCoordinatesBO objUploadPAPCoordinatesBO = objUploadPAPCoordinatesList[i];
if (objUploadPAPCoordinatesBO.ROW_LATITUDE != null && objUploadPAPCoordinatesBO.ROW_LATITUDE.Trim() != "" &&
objUploadPAPCoordinatesBO.ROW_LONGITUDE != null && objUploadPAPCoordinatesBO.ROW_LONGITUDE.Trim() != "")
{
ROWCount++;
if (a == 0)
{
s1 = string.Format("'{0},{1},{2}'", objUploadPAPCoordinatesBO.ROW_LATITUDE, objUploadPAPCoordinatesBO.ROW_LONGITUDE, objUploadPAPCoordinatesBO.Papname);
a++;
}
sb.Append(string.Format("'{0},{1},{2}'", objUploadPAPCoordinatesBO.ROW_LATITUDE, objUploadPAPCoordinatesBO.ROW_LONGITUDE, objUploadPAPCoordinatesBO.Papname));
sb.Append(";");
}
if (objUploadPAPCoordinatesList.Count == i + 1 && ROWCount > 0)
{
sb.Append(s1);
sb.Append(";");
}
}
if (ROWCount == 0)
{
for (int i = 0; i < objUploadPAPCoordinatesList.Count; i++)
{
UploadPAPCoordinatesBO objUploadPAPCoordinatesBO = objUploadPAPCoordinatesList[i];
if (objUploadPAPCoordinatesBO.WL_LATITUDE != null && objUploadPAPCoordinatesBO.WL_LATITUDE.Trim() != "" &&
objUploadPAPCoordinatesBO.WL_LONGITUDE != null && objUploadPAPCoordinatesBO.WL_LONGITUDE.Trim() != "")
{
ROWCount++;
if (a == 0)
{
s1 = string.Format("'{0},{1},{2}'", objUploadPAPCoordinatesBO.WL_LATITUDE, objUploadPAPCoordinatesBO.WL_LONGITUDE, objUploadPAPCoordinatesBO.Papname);
a++;
}
sb.Append(string.Format("'{0},{1},{2}'", objUploadPAPCoordinatesBO.ROW_LATITUDE, objUploadPAPCoordinatesBO.ROW_LONGITUDE, objUploadPAPCoordinatesBO.Papname));
sb.Append(";");
}
if (objUploadPAPCoordinatesList.Count == i + 1)
{
sb.Append(s1);
sb.Append(";");
}
}
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
//System.Text.StringBuilder sb1 = new System.Text.StringBuilder();
//sb1.Append(string.Format("'{0},{1},{2}'", "0.438583", "33.171651", "E"));
//sb1.Append(";");
//sb1.Append(string.Format("'{0},{1},{2}'", "0.43588", "33.172681", "B"));
//sb1.Append(";");
//sb1.Append(string.Format("'{0},{1},{2}'", "0.437553", "33.177788", "C"));
//sb1.Append(";");
//sb1.Append(string.Format("'{0},{1},{2}'", "0.441888", "33.176286", "D"));
//sb1.Append(";");
//sb1.Append(string.Format("'{0},{1},{2}'", "0.438583", "33.171651", "E"));
//sb1.Append(";");
//if (sb1.Length > 0) sb1.Remove(sb1.Length - 1, 1);
return(sb.ToString());
}
/// <summary>
/// To load co ordinates
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnLoadCordinate_Click(object sender, EventArgs e)
{
UploadPAPCoordinatesList list1 = new UploadPAPCoordinatesList();
UploadPAPCoordinatesBO objBo;
for (int i = 0; i < GrdPAPCoordinates.Rows.Count; i++)
{
objBo = new UploadPAPCoordinatesBO();
objBo.HHID = Convert.ToInt32(Request.QueryString["HHID"].ToString());
if (GrdPAPCoordinates.Rows[i].Cells[2].Text != " ")
{
objBo.ROW_X = GrdPAPCoordinates.Rows[i].Cells[2].Text;
}
if (GrdPAPCoordinates.Rows[i].Cells[3].Text != " ")
{
objBo.ROW_Y = GrdPAPCoordinates.Rows[i].Cells[3].Text;
}
TextBox txtlat = (TextBox)GrdPAPCoordinates.Rows[i].FindControl("txtLATITUDE");
TextBox txtlon = (TextBox)GrdPAPCoordinates.Rows[i].FindControl("txtLONGITUDE");
if (txtlat.Text != " ")
{
objBo.ROW_LATITUDE = txtlat.Text;
}
if (txtlon.Text != " ")
{
objBo.ROW_LONGITUDE = txtlon.Text;
}
if (GrdPAPCoordinates.Rows[i].Cells[6].Text != " ")
{
objBo.WL_X = GrdPAPCoordinates.Rows[i].Cells[6].Text;
}
if (GrdPAPCoordinates.Rows[i].Cells[7].Text != " ")
{
objBo.WL_Y = GrdPAPCoordinates.Rows[i].Cells[7].Text;
}
TextBox txtlat1 = (TextBox)GrdPAPCoordinates.Rows[i].FindControl("txtWLLATITUDE");
TextBox txtlon1 = (TextBox)GrdPAPCoordinates.Rows[i].FindControl("txtWLLONGITUDE");
if (txtlat1.Text != " ")
{
objBo.WL_LATITUDE = txtlat1.Text;
}
if (txtlon1.Text != " ")
{
objBo.WL_LONGITUDE = txtlon1.Text;
}
list1.Add(objBo);
}
UploadPAPCoordinatesBLL objUploadPAPCoordinatesBLL = new UploadPAPCoordinatesBLL();
string message = "";
string uID = Session["USER_ID"].ToString();
string Count = objUploadPAPCoordinatesBLL.SaveExcelData(list1, Session["USER_ID"].ToString());
if (!string.IsNullOrEmpty(Count))
{
message = Count + " - Coordinates(s) were added successfully";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Added", "alert('" + message + "');", true);
}
pnlRouteCoordinatesDetails.Visible = true;
pnlFileUpload.Visible = false;
btn_ImportExcel.Visible = true;
BindGrid();
EnableSaveCancel(false);
}
public UploadPAPCoordinatesBO GetPapCoordinatesDataByID(int ID)
{
OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString);
OracleCommand cmd;
string proc = "USP_TRN_GET_PAP_COORDINATESID";
cmd = new OracleCommand(proc, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID_", ID);
cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Connection.Open();
OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
UploadPAPCoordinatesBO UploadPAPCoordinatesBOobj = null;
UploadPAPCoordinatesList UploadPAPCoordinatesListobj = new UploadPAPCoordinatesList();
UploadPAPCoordinatesBOobj = new UploadPAPCoordinatesBO();
while (dr.Read())
{
UploadPAPCoordinatesBOobj = new UploadPAPCoordinatesBO();
if (!dr.IsDBNull(dr.GetOrdinal("ID")))
{
UploadPAPCoordinatesBOobj.Id = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("ID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("HHID")))
{
UploadPAPCoordinatesBOobj.HHID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("HHID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROW_X")))
{
UploadPAPCoordinatesBOobj.ROW_X = Convert.ToString(dr.GetValue(dr.GetOrdinal("ROW_X")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROW_Y")))
{
UploadPAPCoordinatesBOobj.ROW_Y = Convert.ToString(dr.GetValue(dr.GetOrdinal("ROW_Y")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROW_LATITUDE")))
{
UploadPAPCoordinatesBOobj.ROW_LATITUDE = Convert.ToString(dr.GetValue(dr.GetOrdinal("ROW_LATITUDE")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROW_LONGITUDE")))
{
UploadPAPCoordinatesBOobj.ROW_LONGITUDE = Convert.ToString(dr.GetValue(dr.GetOrdinal("ROW_LONGITUDE")));
}
if (!dr.IsDBNull(dr.GetOrdinal("WL_X")))
{
UploadPAPCoordinatesBOobj.WL_X = Convert.ToString(dr.GetValue(dr.GetOrdinal("WL_X")));
}
if (!dr.IsDBNull(dr.GetOrdinal("WL_Y")))
{
UploadPAPCoordinatesBOobj.WL_Y = Convert.ToString(dr.GetValue(dr.GetOrdinal("WL_Y")));
}
if (!dr.IsDBNull(dr.GetOrdinal("WL_LATITUDE")))
{
UploadPAPCoordinatesBOobj.WL_LATITUDE = Convert.ToString(dr.GetValue(dr.GetOrdinal("WL_LATITUDE")));
}
if (!dr.IsDBNull(dr.GetOrdinal("WL_LONGITUDE")))
{
UploadPAPCoordinatesBOobj.WL_LONGITUDE = Convert.ToString(dr.GetValue(dr.GetOrdinal("WL_LONGITUDE")));
}
}
dr.Close();
return(UploadPAPCoordinatesBOobj);
}
