public WellModel GetWellDetails(int RigId)
{
WellModel model = new WellModel();
try
{
string query = @"SELECT TOP 1 A.WellId
,A.WellCode
,A.WellName
,A.LocationId
,A.WellTypeId
,A.WellPlannedDate
,A.WellDepth
,A.RigId
,B.WellTypeName
,C.LocName
FROM Well A
INNER join WellType B on A.WellTypeId = B.WellTypeId
INNER JOIN Location C on C.LocId = A.LocationId
WHERE A.RigId = " + RigId.ToString();
fun.OpenConnection();
if (fun.getConnection().State == System.Data.ConnectionState.Open)
{
DataSet ds = fun.fillComboDataset(query);
if (ds.Tables.Count > 0)
{
foreach (DataRow item in ds.Tables[0].Rows)
{
model.LocationId = Convert.ToInt32(item["LocationId"].ToString());
model.LocName = item["LocName"].ToString();
model.RigId = Convert.ToInt32(item["RigId"].ToString());
model.WellCode = item["WellCode"].ToString();
model.WellDepth = Convert.ToDecimal(item["WellDepth"].ToString());
model.WellId = Convert.ToInt32(item["WellId"].ToString());
model.WellName = item["WellName"].ToString();
model.WellPlannedDate = Convert.ToDateTime(item["WellPlannedDate"].ToString());
model.WellTypeId = Convert.ToInt32(item["WellTypeId"].ToString());
model.WellTypeName = item["WellTypeName"].ToString();
}
}
}
else
{
throw new Exception("Please check network connection");
}
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
public WellModel GetWellDetails(int wellId)
{
WellModel model = new WellModel();
model.DrillingPlanDoc = new List <WellDrillingPlanModel>();
model.WellSections = new List <WellSectionModel>();
try
{
string query = String.Format(@"SELECT [WellId]
,[WellCode]
,[WellName]
,[LocationId]
,[WellTypeId]
,[WellPlannedDate]
,[WellDepth]
,[RigId]
,[PlannedDays]
FROM [Well] WHERE [WellId] = {0};
SELECT [WellDrillPlanId]
,[WellId]
,[WellDrillDoc]
,[WellDrilDocName]
,[WellDrillDocExt]
,[WellDrillDocUploadDate]
FROM [dbo].[WellDrillingPlan]WHERE [WellId] = {0};
select isnull(B.WellSecId,0)WellSecId,isnull(B.WellId,0)WellId,isnull(A.SecId,0)SecId,
A.SectionName, isnull(B.SecDepth,0)SecDepth
from SectionalDetails A
left join WellSections B on A.SecId = B.SecId and B.WellId = {0}", wellId);
DataSet ds = new DataSet();
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
ds = fun.fillComboDataset(query);
}
else
{
ds = temp.fillComboDataset(query);
}
if (ds.Tables.Count > 0)
{
foreach (DataRow item in ds.Tables[0].Rows)
{
model.LocationId = Convert.ToInt32(item["LocationId"].ToString());
model.RigId = Convert.ToInt32(item["RigId"].ToString());
model.WellCode = item["WellCode"].ToString();
model.WellDepth = Convert.ToDecimal(item["WellDepth"].ToString());
model.WellId = Convert.ToInt32(item["WellId"].ToString());
model.WellName = item["WellName"].ToString();
model.WellPlannedDate = Convert.ToDateTime(item["WellPlannedDate"].ToString());
model.WellTypeId = Convert.ToInt32(item["WellTypeId"].ToString());
model.PlannedDays = Convert.ToInt32(item["PlannedDays"].ToString());
}
foreach (DataRow item in ds.Tables[1].Rows)
{
model.DrillingPlanDoc.Add(new WellDrillingPlanModel()
{
WellDrilDocName = item["WellDrilDocName"].ToString(),
WellDrillDoc = (byte[])item["WellDrillDoc"],
WellDrillDocExt = item["WellDrillDocExt"].ToString(),
WellDrillPlanId = (int)item["WellDrillPlanId"],
WellId = (int)item["WellId"],
WellDrillDocUploadDate = (DateTime)item["WellDrillDocUploadDate"]
});
}
foreach (DataRow item in ds.Tables[2].Rows)
{
model.WellSections.Add(new WellSectionModel()
{
SecDepth = Convert.ToDecimal(item["SecDepth"].ToString()),
SecId = Convert.ToInt32(item["SecId"].ToString()),
SectionName = item["SectionName"].ToString(),
WellId = Convert.ToInt32(item["WellId"].ToString()),
WellSecId = Convert.ToInt32(item["WellSecId"].ToString())
});
}
}
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
public WellModel Update(WellModel model)
{
try
{
string query = @"Update Well set
WellCode = @WellCode
,WellName = @WellName
,LocationId = @LocationId
,WellTypeId = @WellTypeId
,WellPlannedDate = @WellPlannedDate
,WellDepth = @WellDepth
,RigId = @RigId
,PlannedDays = @PlannedDays
WHERE WellId = @WellId";
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@WellId", model.WellId));
param.Add(new SqlParameter("@WellCode", model.WellCode));
param.Add(new SqlParameter("@WellName", model.WellName));
param.Add(new SqlParameter("@LocationId", model.LocationId));
param.Add(new SqlParameter("@WellTypeId", model.WellTypeId));
param.Add(new SqlParameter("@WellPlannedDate", model.WellPlannedDate));
param.Add(new SqlParameter("@WellDepth", model.WellDepth));
param.Add(new SqlParameter("@RigId", model.RigId));
param.Add(new SqlParameter("@PlannedDays", model.PlannedDays));
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
fun.ExecuteQueryWithParameters(query, param);
if (model.WellId > 0)
{
foreach (WellDrillingPlanModel item in model.DrillingPlanDoc)
{
if (item.WellDrillPlanId == 0)
{
query = @"INSERT INTO WellDrillingPlan(
WellId
,WellDrillDoc
,WellDrilDocName
,WellDrillDocExt
,WellDrillDocUploadDate)
OUTPUT Inserted.WellDrillPlanId
VALUES(
@WellId
,@WellDrillDoc
,@WellDrilDocName
,@WellDrillDocExt
,@WellDrillDocUploadDate)";
item.WellId = model.WellId;
param = new List <SqlParameter>();
param.Add(new SqlParameter("@WellId", model.WellId));
param.Add(new SqlParameter("@WellDrillDoc", item.WellDrillDoc));
param.Add(new SqlParameter("@WellDrilDocName", item.WellDrilDocName));
param.Add(new SqlParameter("@WellDrillDocExt", item.WellDrillDocExt));
param.Add(new SqlParameter("@WellDrillDocUploadDate", DateTime.Now));
item.WellDrillPlanId = fun.ExecuteQueryWithParameters(query, param, "Yes");
}
}
foreach (var item in model.WellSections)
{
if (item.SecDepth > 0)
{
if (item.WellSecId == 0)
{
query = @"INSERT INTO WellSections(
WellId
,SecId
,SecDepth)
OUTPUT INSERTED.WellSecId
VALUES(
@WellId
,@SecId
,@SecDepth)
";
param = new List <SqlParameter>();
param.Add(new SqlParameter("@WellId", model.WellId));
param.Add(new SqlParameter("@SecId", item.SecId));
param.Add(new SqlParameter("@SecDepth", item.SecDepth));
item.WellSecId = fun.ExecuteQueryWithParameters(query, param, "Yes");
}
else
{
query = @"UPDATE WellSections SET SecDepth = @SecDepth
WHERE WellId = @WellId
AND SecId = @SecId
AND SecDepth = @SecDepth";
param = new List <SqlParameter>();
param.Add(new SqlParameter("@WellId", model.WellId));
param.Add(new SqlParameter("@SecId", item.SecId));
param.Add(new SqlParameter("@SecDepth", item.SecDepth));
fun.ExecuteQueryWithParameters(query, param);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(model);
}