public string GetDefectNoFromDB(KnownDefect knownDefect)
{
string query = "";
if (knownDefect.MasterTransNo == "" && knownDefect.TestTransNo == "" && knownDefect.SecId == "")
{
query = "select defect from VT_DEFECTS where PROJECT = :proj and Lower_Version = :lower_Version and Upper_Version = :upper_Version and Deviation_Column_Name = :deviation_Column_Name and Master_Value = :master_Value and Test_Value = :test_Value";
}
else
{
query = "select defect from VT_DEFECTS where PROJECT = :proj and Lower_Version = :lower_Version and Upper_Version = :upper_Version and Master_TransNo = :master_TransNo and Test_TransNo = :test_TransNo and SecId = :secId and Deviation_Column_Name = :deviation_Column_Name";
}
OracleCommand cmd = new OracleCommand(query, OracleConnection);
cmd.Parameters.Add(":proj", OracleDbType.Varchar2).Value = knownDefect.Project;
cmd.Parameters.Add(":lower_Version", OracleDbType.Double).Value = knownDefect.LowerVersion;
cmd.Parameters.Add(":upper_Version", OracleDbType.Double).Value = knownDefect.UpperVersion;
cmd.Parameters.Add(":master_TransNo", OracleDbType.Varchar2).Value = knownDefect.MasterTransNo;
cmd.Parameters.Add(":test_TransNo", OracleDbType.Varchar2).Value = knownDefect.TestTransNo;
cmd.Parameters.Add(":secId", OracleDbType.Varchar2).Value = knownDefect.SecId;
cmd.Parameters.Add(":deviation_Column_Name", OracleDbType.Varchar2).Value = knownDefect.DeviationColumnName;
cmd.Parameters.Add(":master_Value", OracleDbType.Varchar2).Value = knownDefect.MasterValue;
cmd.Parameters.Add(":test_Value", OracleDbType.Varchar2).Value = knownDefect.TestValue;
cmd.CommandType = CommandType.Text;
using (OracleDataReader dataAdapter = cmd.ExecuteReader()) {
while (dataAdapter.Read())
{
return(dataAdapter.GetString(0));
}
}
return("");
}
public void UpdateDefectsTable(KnownDefect knownDefect, string userId)
{
string query = "UPDATE VT_DEFECTS " +
"SET Defect = :defect, " +
"User_Id = :user_Id, " +
"Changed_date = :changed_date " +
"WHERE " +
"Project = :project and " +
"Lower_Version = :lower_Version and " +
"Upper_Version = :upper_Version and " +
"Master_TransNo = :master_TransNo and " +
"Test_TransNo = :test_TransNo and " +
"SecId = :secID and " +
"Deviation_Column_Name = :deviation_Column_Name";
OracleCommand cmd = new OracleCommand(query, OracleConnection);
cmd.Parameters.Add(":defect", OracleDbType.Varchar2).Value = knownDefect.DefectNo;
cmd.Parameters.Add(":user_Id", OracleDbType.Varchar2).Value = userId;
cmd.Parameters.Add(":changed_date", OracleDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add(":project", OracleDbType.Varchar2).Value = knownDefect.Project;
cmd.Parameters.Add(":lower_Version", OracleDbType.Double).Value = knownDefect.LowerVersion;
cmd.Parameters.Add(":upper_Version", OracleDbType.Double).Value = knownDefect.UpperVersion;
cmd.Parameters.Add(":master_TransNo", OracleDbType.Varchar2).Value = knownDefect.MasterTransNo;
cmd.Parameters.Add(":test_TransNo", OracleDbType.Varchar2).Value = knownDefect.TestTransNo;
cmd.Parameters.Add(":secId", OracleDbType.Varchar2).Value = knownDefect.SecId;
cmd.Parameters.Add(":deviation_Column_Name", OracleDbType.Varchar2).Value = knownDefect.DeviationColumnName;
cmd.ExecuteNonQuery();
}
public void InsertIntoDefectsTable(KnownDefect knownDefect, string userId)
{
string query = "INSERT INTO VT_DEFECTS(Project, Lower_Version, Upper_Version, Defect, Master_TransNo, Test_TransNo, SecId, Deviation_Column_Name, Master_Value, Test_Value, User_Id, Changed_date) " +
"VALUES(:project, :lower_Version, :upper_Version, :defect, :master_TransNo, :test_TransNo, :secId, :deviation_Column_Name, :master_Value, :test_Value, :user_Id, :changed_date)";
OracleCommand cmd = new OracleCommand(query, OracleConnection);
cmd.Parameters.Add(":project", OracleDbType.Varchar2).Value = knownDefect.Project;
cmd.Parameters.Add(":lower_Version", OracleDbType.Double).Value = knownDefect.LowerVersion;
cmd.Parameters.Add(":upper_Version", OracleDbType.Double).Value = knownDefect.UpperVersion;
cmd.Parameters.Add(":defect", OracleDbType.Varchar2).Value = knownDefect.DefectNo;
cmd.Parameters.Add(":master_TransNo", OracleDbType.Varchar2).Value = knownDefect.MasterTransNo;
cmd.Parameters.Add(":test_TransNo", OracleDbType.Varchar2).Value = knownDefect.TestTransNo;
cmd.Parameters.Add(":secId", OracleDbType.Varchar2).Value = knownDefect.SecId;
cmd.Parameters.Add(":deviation_Column_Name", OracleDbType.Varchar2).Value = knownDefect.DeviationColumnName;
cmd.Parameters.Add(":master_Value", OracleDbType.Varchar2).Value = knownDefect.MasterValue;
cmd.Parameters.Add(":test_Value", OracleDbType.Varchar2).Value = knownDefect.TestValue;
cmd.Parameters.Add(":user_Id", OracleDbType.Varchar2).Value = userId;
cmd.Parameters.Add(":changed_date", OracleDbType.TimeStamp).Value = DateTime.Now;
cmd.ExecuteNonQuery();
}
public void DeleteDefectsTable(KnownDefect knownDefect)
{
string query = "DELETE FROM VT_DEFECTS " +
"WHERE " +
"Project = :project and " +
"Lower_Version = :lower_Version and " +
"Upper_Version = :upper_Version and " +
"Master_TransNo = :master_TransNo and " +
"Test_TransNo = :test_TransNo and " +
"SecId = :secId and " +
"Deviation_Column_Name = :deviation_Column_Name";
OracleCommand cmd = new OracleCommand(query, OracleConnection);
cmd.Parameters.Add(":project", OracleDbType.Varchar2).Value = knownDefect.Project;
cmd.Parameters.Add(":lower_Version", OracleDbType.Double).Value = knownDefect.LowerVersion;
cmd.Parameters.Add(":upper_Version", OracleDbType.Double).Value = knownDefect.UpperVersion;
cmd.Parameters.Add(":master_TransNo", OracleDbType.Varchar2).Value = knownDefect.MasterTransNo;
cmd.Parameters.Add(":test_TransNo", OracleDbType.Varchar2).Value = knownDefect.TestTransNo;
cmd.Parameters.Add(":secId", OracleDbType.Varchar2).Value = knownDefect.SecId;
cmd.Parameters.Add(":deviation_Column_Name", OracleDbType.Varchar2).Value = knownDefect.DeviationColumnName;
cmd.ExecuteNonQuery();
}
public List <KnownDefect> GetDeviationsFromExcel(string path, string proj, List <double> upgrade)
{
var excelData = ReadExcel(path);
if (excelData.Count <= 1)
{
return(null);
}
var headers = excelData.First();
int mTransNoIndex = -1;
int tTransNoIndex = -1;
int secIdIndex = -1;
for (int i = 0; i < headers.Count; i++)
{
if (headers[i].ToLower().Contains("m_trans"))
{
mTransNoIndex = i;
}
else if (headers[i].ToLower().Contains("t_trans"))
{
tTransNoIndex = i;
}
else if (headers[i].ToLower().Contains("sec_id") || headers[i].ToLower().Contains("secid") || headers[i].ToLower().Contains("secshort"))
{
secIdIndex = i;
}
}
int deviationColumnNameIndx = headers.IndexOf("Column Name");
int masterValueIndx = headers.IndexOf("Master Value");
int testValueIndx = headers.IndexOf("Test Value");
if (deviationColumnNameIndx < 0 || masterValueIndx < 0 || testValueIndx < 0)
{
return(null);
}
List <KnownDefect> knownDefects = new List <KnownDefect>();
foreach (var item in excelData.Skip(1))
{
if (item[0] != "" && !IsKnownDefect(item[0]))
{
KnownDefect knownDefect = new KnownDefect(
project: proj,
lowerVersion: upgrade[0],
upperVersion: upgrade[1],
defectNo: item[0].Trim(),
masterTransNo: mTransNoIndex == -1 ? "" : item[mTransNoIndex],
testTransNo: tTransNoIndex == -1 ? "" : item[tTransNoIndex],
secId: secIdIndex == -1 ? "" : item[secIdIndex],
deviationColumnName: item[deviationColumnNameIndx],
masterValue: item[masterValueIndx],
testValue: item[testValueIndx]
);
if (!knownDefects.Contains(knownDefect))
{
knownDefects.Add(knownDefect);
}
}
}
return(knownDefects);
}
