protected void lnkAddWaterAnalysis_OnCommand(object sender, CommandEventArgs e)
{
//add water analysis
//get data on form
WaterAnalysis wat = new WaterAnalysis();
wat.ClearIron = Convert.ToDecimal((fviewWaterAnalysis.FindControl("txtClearIron") as TextBox).Text);
wat.Hardness = Convert.ToDecimal((fviewWaterAnalysis.FindControl("txtHardness") as TextBox).Text);
wat.HydrogenSulfide = Convert.ToDecimal((fviewWaterAnalysis.FindControl("txtHydrogenSulfide") as TextBox).Text);
wat.PH_Acid = Convert.ToDecimal((fviewWaterAnalysis.FindControl("txtPh") as TextBox).Text);
wat.TDS = Convert.ToDecimal((fviewWaterAnalysis.FindControl("txtTDS") as TextBox).Text);
wat.AccountID = _accountID;
//save
wat.ReportID = WaterAnalysisManager.Save(wat);
//clear the fields
(fviewWaterAnalysis.FindControl("txtClearIron") as TextBox).Text = "";
(fviewWaterAnalysis.FindControl("txtHardness") as TextBox).Text = "";
(fviewWaterAnalysis.FindControl("txtHydrogenSulfide") as TextBox).Text = "";
(fviewWaterAnalysis.FindControl("txtPh") as TextBox).Text = "";
(fviewWaterAnalysis.FindControl("txtTDS") as TextBox).Text = "";
//display in gridview
gviewWaterAnalysis.DataSource = WaterAnalysisManager.GetList(_accountID);
gviewWaterAnalysis.DataBind();
}
static void WriteWaterAnalysis(WaterAnalysis analysis, ISheet sheet, int rowIndex, ICellStyle cellDateStyle)
{
var row = CreateAnalysisRow(analysis, sheet, rowIndex, cellDateStyle);
int i = 2;
foreach (var p in WaterAnalysis.DoubleProperties.Keys.ToList())
{
row.CreateCell(i, CellType.Numeric).SetCellValue((double)Interaction.CallByName(analysis, WaterAnalysis.DoubleProperties[p].Name, CallType.Get));
i += 1;
}
}
public static int Save(WaterAnalysis waterAnalysis)
{
int result;
MyDBConnection myConn = new MyDBConnection();
SqlConnection conn = new SqlConnection();
try
{
conn = myConn.OpenDB();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.InsertUpdateWaterAnalysis";
if (waterAnalysis.ReportID == -1)
{
cmd.Parameters.Add("@ReportID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("@ReportID", SqlDbType.Int).Value = waterAnalysis.ReportID;
}
cmd.Parameters.Add("@Hardness", SqlDbType.Decimal).Value = waterAnalysis.Hardness;
cmd.Parameters.Add("@ClearIron", SqlDbType.Decimal).Value = waterAnalysis.ClearIron;
cmd.Parameters.Add("@PH_Acid", SqlDbType.Decimal).Value = waterAnalysis.PH_Acid;
cmd.Parameters.Add("@HydrogenSulfide", SqlDbType.Decimal).Value = waterAnalysis.HydrogenSulfide;
cmd.Parameters.Add("@TDS", SqlDbType.Decimal).Value = waterAnalysis.TDS;
cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = waterAnalysis.AccountID;
cmd.Parameters.Add("@Notes", SqlDbType.VarChar).Value = waterAnalysis.Notes;
cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = waterAnalysis.CreatedDate;
cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = waterAnalysis.ModifiedDate;
cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = waterAnalysis.CreatedBy;
cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = waterAnalysis.ModifiedBy;
DbParameter returnValue = cmd.CreateParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue);
cmd.ExecuteNonQuery();
result = Convert.ToInt32(returnValue.Value);
}
finally
{
myConn.CloseDB(conn);
}
return(result);
}
static void WriteWaterAnalysisHeader(ISheet sheet)
{
var header = sheet.CreateRow(0);
header.CreateCell(0).SetCellValue(WaterAnalysis.GetDisplayName(nameof(WaterAnalysis.WellName)));
header.CreateCell(1).SetCellValue(WaterAnalysis.GetDisplayName(nameof(WaterAnalysis.Date)));
int i = 2;
foreach (var p in WaterAnalysis.DoubleProperties.Keys.ToList())
{
header.CreateCell(i).SetCellValue(p);
i += 1;
}
}
public static List <WaterAnalysis> ReadWaterAnalysis(IWorkbook workbook, int sheetIndex, IProgress <int> progress)
{
var sheet = workbook.GetSheetAt(sheetIndex);
IRow row;
List <WaterAnalysis> analyses = new List <WaterAnalysis>();
int indexError = 1;
CreateRejectedRows(sheet.GetRow(0));
try
{
var maxCount = sheet.LastRowNum;
for (int i = 1; i < maxCount + 1; i++)
{
indexError = i;
row = sheet.GetRow(i);
var analysis = new WaterAnalysis
{
Well = GetWell(ReadCellAsString(row, 0)?.ToUpper(CultureInfo.InvariantCulture)),
Date = ParseStringDate(ReadCellAsDateString(row, 1))
};
int j = 2;
foreach (var p in WaterAnalysis.DoubleProperties.Keys.ToList())
{
Interaction.CallByName(analysis, WaterAnalysis.DoubleProperties[p].Name, CallType.Set, ReadCellAsDouble(row, j));
j += 1;
}
if (analysis.Well != null)
{
analyses.Add(analysis);
}
else
{
RejectedRows.Add(CreateRejectedRow(row, j, i, RejectedReasons.WellNotFound));
}
progress?.Report(i / maxCount * 100);
}
}
catch (Exception ex)
{
throw new Exception("Error leyendo la fila " + indexError.ToString(), ex);
}
return(analyses);
}
/// <summary>
/// helper method
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private static WaterAnalysis FillDataRecord(IDataRecord dr)
{
WaterAnalysis waterAnalysis = new WaterAnalysis();
waterAnalysis.ReportID = dr.GetInt32(dr.GetOrdinal("ReportID"));
waterAnalysis.Hardness = dr.GetDecimal(dr.GetOrdinal("Hardness"));
waterAnalysis.ClearIron = dr.GetDecimal(dr.GetOrdinal("ClearIron"));
waterAnalysis.PH_Acid = dr.GetDecimal(dr.GetOrdinal("PH_Acid"));
waterAnalysis.HydrogenSulfide = dr.GetDecimal(dr.GetOrdinal("HydrogenSulfide"));
waterAnalysis.TDS = dr.GetDecimal(dr.GetOrdinal("TDS"));
waterAnalysis.Notes = dr.GetString(dr.GetOrdinal("Notes"));
waterAnalysis.AccountID = dr.GetInt32(dr.GetOrdinal("AccountID"));
waterAnalysis.CreatedDate = dr.GetDateTime(dr.GetOrdinal("CreatedDate"));
waterAnalysis.ModifiedDate = dr.GetDateTime(dr.GetOrdinal("ModifiedDate"));
waterAnalysis.CreatedBy = dr.GetString(dr.GetOrdinal("CreatedBy"));
waterAnalysis.ModifiedBy = dr.GetString(dr.GetOrdinal("ModifiedBy"));
return(waterAnalysis);
}
public static WaterAnalysis GetWaterAnalysisByReportID(int id)
{
WaterAnalysis waterAnalysis = new WaterAnalysis();
MyDBConnection myConn = new MyDBConnection();
SqlConnection conn = new SqlConnection();
SqlDataReader dr;
SqlCommand cmd = null;
string sql = "Select * from AquaOne.dbo.WaterAnalysis where ReportID = @ReportID";
// Open the connection
conn = myConn.OpenDB();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@ReportID", SqlDbType.Int).Value = id;
dr = cmd.ExecuteReader();
if (dr.Read())
{
waterAnalysis = FillDataRecord(dr);
}
cmd.Dispose();
//close the connection
myConn.CloseDB(conn);
return(waterAnalysis);
}
public static int Save(WaterAnalysis waterAnalysis)
{
return(WaterAnalysisDB.Save(waterAnalysis));
}
