public void InsertDefect(string Loginuser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp date1, String filenames, String filesize, Byte Attachments, String environment, String severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
// Date = CAST(Date AS FORMAT 'YYYY-MM-DD');
// TdTimestamp dt = new TdTimestamp(Date);
// string dts = dt.ToString("MM/dd/yyyy HH:mm:ss");
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddDefect = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect Values('" + DefectID + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + filenames + "','" + filesize + "',,'" + environment + "','" + severity + "','" + Projectname + "','" + Rootcause + "')", con);
int DefectIDD = (int)GetNextDefectID();
int histid = 1;
//DefectIDD = DefectIDD - 1;
Teradata.Client.Provider.TdCommand cmdAddDefectHistory = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect_History Values('"
+ DefectIDD + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + histid + "','" + Loginuser + "','" + environment + "','" + severity + "', '" + Projectname + "','" + Rootcause + "')", con);
int intLengt = 0;
byte[] objDat;
objDat = new byte[intLengt];
string DataOb = Convert.ToString(objDat);
// cmdAddDefect.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
// cmdAddDefectHistory.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddDefect.ExecuteNonQuery();
cmdAddDefectHistory.ExecuteNonQuery();
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
protected void BtnEnter_Click(object sender, EventArgs e)
{
if (TxtDefectName.Text == string.Empty || DDLStatus.SelectedIndex == -1 ||
DDlAssignedto.SelectedIndex == -1 || DDlCycle.SelectedIndex == -1 ||
DdlProject.SelectedIndex == -1 || DDlTester.SelectedIndex == -1 ||
/*TxtTcAssociated.Text == string.Empty || */ DDlModule.SelectedIndex == -1 ||
DDLProjectname.SelectedIndex == -1 || DDLEnvironment.SelectedIndex == -1 || DDLSeverity.SelectedIndex == -1
)
{
lbMessage.Visible = true;
lbMessage.Text = "Please enter data in the required fields";
}
else
{
try
{
defectid = dbObj.GetNextDefectID();
this.Session["DefectID11"] = defectid;
DateTime date = Convert.ToDateTime(txtDate.Text);
TdTimestamp CreateDate = new TdTimestamp(date);
TdTimestamp UpdateDate = new TdTimestamp(date);
//DateTime date1 = Convert.ToDateTime(date);
string filenames = "";
string filesize = "";
Byte Attachments;
Attachments = 0;
string LoginUser = Session["New"].ToString();
string Rootcause = "";
//Attachments = null;
dbObj.InsertDefect(LoginUser, defectid, TxtDefectName.Text, CreateDate, DDLStatus.SelectedValue, DDlCycle.SelectedValue, TxtSteps.Text, DdlProject.SelectedValue, DDlTester.SelectedValue, TxtTcAssociated.Text, DDlModule.SelectedValue, DDlAssignedto.SelectedValue, UpdateDate, filenames, filesize, Attachments, DDLEnvironment.SelectedValue, DDLSeverity.SelectedValue, DDLProjectname.SelectedValue, Rootcause);
lbMessage.Visible = true;
lbMessage.Text = "Defect Added Succesfully";
// string MessageBody="";
MailMessage mailMessage = new MailMessage();
mailMessage.To.Add("*****@*****.**");
mailMessage.From = new MailAddress("*****@*****.**");
mailMessage.Subject = "New Defect Added-Defect ID " + defectid + "";
mailMessage.Body = mailMessage.Body + "<p>A New Defect has been added by " + LoginUser + " </p>";
mailMessage.Body = mailMessage.Body + "<p><fieldset><legend><strong>Defect Details:-</strong></legend>";
mailMessage.Body = mailMessage.Body + "" + LBDefectID.Text + " :" + TxtDefectID.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBDefectName.Text + " :'" + TxtDefectName.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBCycle.Text + " :" + DDlCycle.SelectedItem.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LbModule.Text + " :" + DDlModule.SelectedItem.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBDate.Text + " :" + txtDate.Text + "<br/>";
// mailMessage.Body = mailMessage.Body + "" + lbupdatedate.Text + " :" + TxtLastupdated.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBStatus.Text + " :" + DDLStatus.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + lbAssignedto.Text + " :" + DDlAssignedto.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + lbProject.Text + " :" + DdlProject.SelectedItem.Text + "<br/>";
// mailMessage.Body = mailMessage.Body + "" + LBAttachments.Text + " :;" + GridViewAttachments. + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBTCAssociated.Text + " :" + TxtTcAssociated.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBSteps.Text + " :" + TxtSteps.Text + "<br/>";
mailMessage.IsBodyHtml = true;
SmtpClient smtpClient = new SmtpClient("azshspp04.caremarkrx.net");
smtpClient.Send(mailMessage);
// Response.Write("E-mail sent!");
Server.Transfer("HomePage.aspx", true);
}
catch (TdException ex)
{
Response.Write("Could not send the e-mail - error: " + ex.Message);
}
}
}
public void UpdateDefect(string Comments, string LoginUser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp sysdate, String filenames, String filesize, Byte Attachments, int histid, String environment, String Severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdUpdateDefect =
new Teradata.Client.Provider.TdCommand(@"Update coebatch.tbl_Defect set DefectName='" + DefectName + "' ,Status='" + Status + "',SCycle ='" + Cycle + "',Description='" + Description + "',Project='" + Project + "',Tester='" + Tester + "',TestcasesAssociated='" + TestcasesAssociated + "',Module='" + Module + "',AssignedTo='" + Assignedto + "',CloseDate='" + sysdate + "', Environment = '" + environment + "' , Severity = '" + Severity + "', Releases = '" + Projectname + "',Rootcause = '" + Rootcause + "' where DefectID= '" + DefectID + "'", con);
Teradata.Client.Provider.TdCommand cmdInsertComments =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Comments Values ('" + DefectID + "','" + Comments + "','" + sysdate + "', '" + LoginUser + "' )", con);
DateTime Datetimestamp = DateTime.Now;
Teradata.Client.Provider.TdCommand cmdUpdateDefectHistory =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Defect_History Values('" + DefectID + "','" + DefectName + "' ,'" + Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','" + sysdate + "','" + histid + "','" + LoginUser + "', '" + environment + "' , '" + Severity + "','" + Projectname + "','" + Rootcause + "') ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdUpdateDefect.ExecuteNonQuery();
cmdInsertComments.ExecuteNonQuery();
cmdUpdateDefectHistory.ExecuteNonQuery();
}
catch (TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
protected void BtnSaveEditted_Click(object sender, EventArgs e)
{
if (TxtDefectName.Text == string.Empty || DDLStatus1.SelectedIndex == -1 ||
DDlAssignedto.SelectedIndex == -1 || DDlCycle.SelectedIndex == -1 ||
DdlProject.SelectedIndex == -1 || DDlTester.SelectedIndex == -1 ||
/*TxtTcAssociated.Text == string.Empty ||*/ DDlModule.SelectedIndex == -1 ||
txtComments.Text == string.Empty || DDLSeverity.SelectedIndex == -1 || DDLEnvironment.SelectedIndex == -1 || DDLProjectname.SelectedIndex == -1 || TxtDefectName.Text == "")
{
lbMessage.Visible = true;
lbMessage.Text = "Please enter data in all the fields";
}
else
{
if ((DDLStatus1.SelectedValue == "Fixed" || DDLStatus1.SelectedValue == "Close") && DDLRootcause.SelectedIndex == 0)
{
lbMessage.Visible = true;
lbMessage.Text = "Please enter rootcause for the issue";
}
else
{
try
{
defectid1 = (int)Session["MySessionVar"];
DateTime Systimestamp = DateTime.Now;
string LoginUser = Session["New"].ToString();
string defectname = TxtDefectName.Text;
string status = DDLStatus1.SelectedValue.ToString();
DateTime Sysdate = DateTime.Now;
string date1 = txtDate.Text.ToString();
TxtLastupdated.Text = Sysdate.ToString("MM/dd/yyyy HH:mm:ss");
string Sysdate1 = TxtLastupdated.Text.ToString();
DateTime date = Convert.ToDateTime(date1);
DateTime date2 = Convert.ToDateTime(Sysdate1);
TdTimestamp Datefrom1 = new TdTimestamp(date);
TdTimestamp DateTo1 = new TdTimestamp(date2);
int histid = dbObj.GetHistoryid(defectid1);
string Comments = txtComments.Text;
statusmail = Session["Status"].ToString();
AssigntoMail = Session["Assignedto"].ToString();
string item = DDLStatus1.SelectedItem.ToString();
//defectid = dbObj.GetNextDefectID();
// DateTime date = Convert.ToDateTime (txtDate.Text);
string filenames = "";
string filesize = "";
Byte Attachments = 0;;
//AtC:\Documents and Settings\z123993\my documents\Downloads\Framework-4-0\FreeTextBox.dlltachments = 0;
//Attachments = null;
dbObj.UpdateDefect(Comments, LoginUser, defectid1, defectname, Datefrom1, status, DDlCycle.SelectedValue, TxtDescription.Text, DdlProject.SelectedValue, DDlTester.SelectedValue, TxtTcAssociated.Text, DDlModule.SelectedValue, DDlAssignedto.SelectedValue, DateTo1, filenames, filesize, Attachments, histid, DDLEnvironment.SelectedValue, DDLSeverity.SelectedValue, DDLProjectname.SelectedValue, DDLRootcause.SelectedValue);
// llbMessage.Visible = true;
if (statusmail != DDLStatus1.SelectedItem.Text || AssigntoMail != DDlAssignedto.SelectedItem.Text)
{
MailMessage mailMessage = new MailMessage();
mailMessage.To.Add("*****@*****.**");
mailMessage.From = new MailAddress("*****@*****.**");
mailMessage.Subject = "Defect ID " + defectid1 + " has been updated";
mailMessage.Body = mailMessage.Body + "<p><b>Defect has been updated by " + LoginUser + " </b></p>";
mailMessage.Body = mailMessage.Body + "<p><fieldset><legend><strong>Defect Details:-</strong></legend>";
mailMessage.Body = mailMessage.Body + "" + LBDefectID.Text + " :" + TxtDefectID.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBDefectName.Text + " :" + TxtDefectName.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBCycle.Text + " :" + DDlCycle.SelectedItem.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LbModule.Text + " :" + DDlModule.SelectedItem.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBDate.Text + " :" + txtDate.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + lbupdatedate.Text + " :" + TxtLastupdated.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "<b>" + LBStatus.Text + " :" + DDLStatus1.SelectedItem.Text + "</b><br/>";
mailMessage.Body = mailMessage.Body + "<b>" + lbAssignedto.Text + " :" + DDlAssignedto.Text + "</b><br/>";
mailMessage.Body = mailMessage.Body + "<b>" + LBComments.Text + " :" + txtComments.Text + "</b><br/>";
mailMessage.Body = mailMessage.Body + "" + lbProject.Text + " :" + DdlProject.SelectedItem.Text + "<br/>";
// mailMessage.Body = mailMessage.Body + "" + LBAttachments.Text + " :;" + GridViewAttachments. + "<br/>";
mailMessage.Body = mailMessage.Body + "" + LBTCAssociated.Text + " :" + TxtTcAssociated.Text + "<br/>";
mailMessage.Body = mailMessage.Body + "" + lbSteps.Text + " :" + TxtDescription.Text + "<br/>";
mailMessage.IsBodyHtml = true;
SmtpClient smtpClient = new SmtpClient("azshspp04.caremarkrx.net");
smtpClient.Send(mailMessage);
// Response.Write("E-mail sent!");
Server.Transfer("HomePage.aspx", true);
}
else
{
Server.Transfer("HomePage.aspx", true);
}
}
catch (TdException ex)
{
Response.Write("Could not send the e-mail - error: " + ex.Message);
}
}
}
}
protected void BtnSearch_Click(object sender, EventArgs e)
{
DateTime Sysdate = DateTime.Now;
fromdate = Calendarfrom.SelectedDate;
todate = CalendartoDate.SelectedDate;
int defect = 0;
if (fromdate > todate || fromdate > Sysdate || todate > Sysdate)
{
LbMessage.Visible = true;
LbMessage.Text = "Please enter the valid date range";
}
else
{
if
(DDLAssignedto.SelectedValue == "" && DDLEnvironment.SelectedValue == "" &&
DDLModule.SelectedValue == "" && DDLProject.SelectedValue == "" && DDLProjectname.SelectedValue == "" &&
DDlStatus.SelectedValue == "" &&
DDLTester.SelectedValue == "" && txtfromdate.Text == "" && txtTodate.Text == "" && TxtDefectname.Text == "" && DDLCycle.SelectedValue == "")
{
LbMessage.Visible = true;
LbMessage.Text = "Please select any of the search items";
}
else
{
try
{
string trteradataconnection = "Data Source='204.99.34.21';User ID='coebatch';Password='******';";
if (txtfromdate.Text != "" && txtTodate.Text != "")
{
Datefrom = Convert.ToDateTime(txtfromdate.Text);
DateTo = Convert.ToDateTime(txtTodate.Text);
Datefrom1 = new TdTimestamp(Datefrom);
DateTo1 = new TdTimestamp(DateTo);
}
else
{
DateTime timetora = DateTime.Now;
string text = timetora.ToString("MM/dd/yyyy HH:mm:ss");
DateTime Test1 = Convert.ToDateTime(text);
DateTo1 = new TdTimestamp(Test1);
}
Teradata.Client.Provider.TdConnection tereconnection = new Teradata.Client.Provider.TdConnection(trteradataconnection);
//TdCommand Teracomd = new TdCommand("select * from coebatch.tbl_Defect", tereconnection);
//TdDataAdapter AdvancedDefectSearch =
// new TdDataAdapter(@"Select DefectID,DefectName,Status,sDate,sCycle,Description,Project,Tester,TestcasesAssociated,Module,AssignedTo,Filenames,Filesize from coebatch.tbl_Defect where sDate between '" + fromdate + "' and '" + todate + "'", tereconnection);
TdCommand cmd = new TdCommand("COEBATCH.ADVANCEDDEFECTSEARCH1", tereconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("VDefectname", TdType.VarChar).Value = TxtDefectname.Text;
cmd.Parameters.Add("VStatus", TdType.VarChar).Value = DDlStatus.SelectedValue;
cmd.Parameters.Add("VAssignedto", TdType.VarChar).Value = DDLAssignedto.SelectedValue;
cmd.Parameters.Add("VTester", TdType.VarChar).Value = DDLTester.SelectedValue;
cmd.Parameters.Add("VOpendate", TdType.Timestamp).Value = Datefrom1;
cmd.Parameters.Add("VOpendate1", TdType.Timestamp).Value = DateTo1;
cmd.Parameters.Add("VProject", TdType.VarChar).Value = DDLProject.SelectedValue;
cmd.Parameters.Add("VModule", TdType.VarChar).Value = DDLModule.SelectedValue;
cmd.Parameters.Add("VEnvironment", TdType.VarChar).Value = DDLEnvironment.SelectedValue;
cmd.Parameters.Add("VRelease", TdType.VarChar).Value = DDLProjectname.SelectedValue;
cmd.Parameters.Add("VCycle", TdType.VarChar).Value = DDLCycle.SelectedValue;
//cmd.Parameters.Add("cur1", TdType.AnyType).Direction = ParameterDirection.Output;
tereconnection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
// defect = cmd.ExecuteNonQuery();
// TdDataAdapter AdvancedDefectSearch =
// new TdDataAdapter();
//AdvancedDefectSearch= new TdDataAdapter(cmd);
//// IDataSource dt1 = new IDataSource ;
//tereconnection.Open();
//DataSet ds = new DataSet();
//AdvancedDefectSearch.Fill(ds);
GridAdvancedSearch.DataSource = dt;
GridAdvancedSearch.DataBind();
//this.GridAdvancedSearch.DataSource = ds.Tables[0].DataSet;
//GridAdvancedSearch.DataKeyNames = new string[] { "Defectid" };
//this.GridAdvancedSearch.DataBind();
//this.GridAdvancedSearch.Visible = true;
if (GridAdvancedSearch.Rows.Count > 0)
{
rowcount = GridAdvancedSearch.Rows.Count;
BtnExport.Enabled = true;
}
else
{
BtnExport.Enabled = false;
}
}
catch (Teradata.Client.Provider.TdException ex)
{
//Throw the exception to calling environment
throw ex;
}
finally
{
//Close the Connection from the Database
}
//GridAdvancedSearch.DataSource = dt;
// AdvancedDefectSearch.Fill(dt);
}
}
}
