/// <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); }