protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString["IDF"] != null) { IDF = Request.QueryString["IDF"]; } using (SqlConnection con = new SqlConnection()) { con.ConnectionString = ConfigurationManager.ConnectionStrings["PATENTCN"].ConnectionString; con.Open(); using (SqlCommand cmd1 = new SqlCommand()) { string sql1 = string.Empty; //sql1 = "select FileNo,Description,AmtInFC,AmtInINR,Amt_InCond,Format(DueDate_Restoration,'dd-MM-yyyy') as DueDate_Restoration,Format(Intimation_Date,'dd-MM-yyyy') as Intimation_Date,Format(Confirmation_Date,'dd-MM-yyyy') as Confirmation_Date,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate,Status from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "' and Status='" + ddlstatus.SelectedItem.ToString() + "'"; sql1 = "select FileNo,Title,FirstApplicant,Inventor1,Applcn_no,Filing_dt,Pat_no,Pat_dt,Status,Sub_Status,Industry1,Commercialized from PatDetails where FileNo='" + IDF.ToString() + "'"; cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr5; dr5 = cmd1.ExecuteReader(); DataTable dt1 = new DataTable(); dt1.Load(dr5); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); } } }
protected void ImageReportButn1_Click(object sender, ImageClickEventArgs e) { string sql1; using (SqlConnection con = new SqlConnection()) { con.ConnectionString = ConfigurationManager.ConnectionStrings["PATENTCN"].ConnectionString; con.Open(); using (SqlCommand cmd1 = new SqlCommand()) { if (ddlAgreeType.SelectedItem.ToString() == "" && drop1.SelectedValue.ToString() != "All") { sql1 = "select b.ContractNo,b.Event,b.Frequency, Format(ExecutionDate,'dd-MM-yyyy') as ExecutionDate ,b.DeclDueAmt,b.InvoiceRaised,b.RecieptNo,b.RecieptDate,b.AmountInRs,b.Status from agreement as a join Royality as b on a.ContractNo = b.ContractNo where b.ExecutionDate between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and b.Status='" + drop1.SelectedItem.ToString() + "'"; } else if (drop1.SelectedItem.ToString() == "All" && ddlAgreeType.SelectedValue.ToString() != "") { sql1 = "select b.ContractNo,b.Event,b.Frequency, Format(ExecutionDate,'dd-MM-yyyy') as ExecutionDate,b.DeclDueAmt,b.InvoiceRaised,b.RecieptNo,b.RecieptDate,b.AmountInRs,b.Status from agreement as a join Royality as b on a.ContractNo = b.ContractNo where b.ExecutionDate between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and a.AgreementType='" + ddlAgreeType.SelectedValue.ToString() + "'"; } else if (drop1.SelectedItem.ToString() == "All" && ddlAgreeType.SelectedValue.ToString() == "") { sql1 = "select b.ContractNo,b.Event,b.Frequency, Format(ExecutionDate,'dd-MM-yyyy') as ExecutionDate ,b.DeclDueAmt,b.InvoiceRaised,b.RecieptNo,b.RecieptDate,b.AmountInRs,b.Status from agreement as a join Royality as b on a.ContractNo = b.ContractNo where b.ExecutionDate between '" + txtFDate.Text + "' and '" + txtTDate.Text + "'"; } else { sql1 = "select b.ContractNo,b.Event,b.Frequency, Format(ExecutionDate,'dd-MM-yyyy') as ExecutionDate,b.DeclDueAmt,b.InvoiceRaised,b.RecieptNo,b.RecieptDate,b.AmountInRs,b.Status from agreement as a join Royality as b on a.ContractNo = b.ContractNo where b.ExecutionDate between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and b.Status='" + drop1.SelectedItem.ToString() + "' and a.AgreementType='" + ddlAgreeType.SelectedValue.ToString() + "'"; } cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr1; dr1 = cmd1.ExecuteReader(); DataTable dt1 = new DataTable(); dt1.Load(dr1); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); dr1.Close(); // lblmsg.Visible = true; } } }
protected void ImageReportButn1_Click(object sender, ImageClickEventArgs e) { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = ConfigurationManager.ConnectionStrings["PATENTCN"].ConnectionString; con.Open(); using (SqlCommand cmd1 = new SqlCommand()) { if (ddlAgreeType.SelectedItem.ToString() == "" && drop1.SelectedValue.ToString() != "All") { string sql1 = "select ContractNo,AgreementType,AgreementNo,Title,Scope,Party,CoordinatingPerson,Remark,Format(Request,'dd-MM-yyyy')as Request,TechTransfer,Status,Format(EffectiveDt,'dd-MM-yyyy') as EffectiveDt from Agreement where Request between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and Status='" + drop1.SelectedItem.ToString() + "'"; cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr1; dr1 = cmd1.ExecuteReader(); //if (dr1.Read()) //{ DataTable dt1 = new DataTable(); dt1.Load(dr1); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); dr1.Close(); //} //else //{ // lblmsg.Visible = true; //} } else if (drop1.SelectedItem.ToString() == "All" && ddlAgreeType.SelectedValue.ToString() != "") { string sql1 = "select ContractNo,AgreementType,AgreementNo,Title,Scope,Party,CoordinatingPerson,Remark,Format(Request,'dd-MM-yyyy') as Request,TechTransfer,Status,Format(EffectiveDt,'dd-MM-yyyy')as EffectiveDt from Agreement where Request between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and AgreementType='" + ddlAgreeType.SelectedValue.ToString() + "'"; cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr1; dr1 = cmd1.ExecuteReader(); //if (dr1.Read() == true) //{ DataTable dt1 = new DataTable(); dt1.Load(dr1); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); dr1.Close(); //} //else //{ // lblmsg.Visible = true; //} } else if (drop1.SelectedItem.ToString() == "All" && ddlAgreeType.SelectedValue.ToString() == "") { string sql1 = "select ContractNo,AgreementType,AgreementNo,Title,Scope,Party,CoordinatingPerson,Remark,Format(Request,'dd-MM-yyyy')as Request,TechTransfer,Status,Format(EffectiveDt,'dd-MM-yyyy')as EffectiveDt from Agreement where Request between '" + txtFDate.Text + "' and '" + txtTDate.Text + "'"; cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr1; dr1 = cmd1.ExecuteReader(); //if (dr1.Read() != true) //{ // lblmsg.Visible = true; //} //else //{ DataTable dt1 = new DataTable(); dt1.Load(dr1); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); dr1.Close(); //} } else { string sql1 = "select ContractNo,AgreementType,AgreementNo,Title,Scope,Party,CoordinatingPerson,Remark,Format(Request,'dd-MM-yyyy')as Request,TechTransfer,Status,Format(EffectiveDt,'dd-MM-yyyy') as EffectiveDt from Agreement where Request between '" + txtFDate.Text + "' and '" + txtTDate.Text + "' and Status='" + drop1.SelectedItem.ToString() + "' and AgreementType='" + ddlAgreeType.SelectedValue.ToString() + "'"; cmd1.CommandText = sql1; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr1; dr1 = cmd1.ExecuteReader(); //if (dr1.Read() == true) //{ DataTable dt1 = new DataTable(); dt1.Load(dr1); GvReportDate.DataSource = dt1; GvReportDate.DataBind(); dr1.Close(); //} //else //{ // lblmsg.Visible = true; //} } } } }
protected void ImageReportButn1_Click1(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = ConfigurationManager.ConnectionStrings["PATENTCN"].ConnectionString; con.Open(); using (SqlCommand cmd1 = new SqlCommand()) { string sql2 = string.Empty, sql3 = string.Empty, sql4 = string.Empty; string sql1 = "select FileNo,Description,Responsibility,Format(DueDate, 'dd-MM-yyyy') as DueDate,AmtInFC,AmtInINR,Status,Format(POPaymentDate, 'dd-MM-yyyy') as POPaymentDate from RenewalFollowup"; if (txtFDate.Text != "" && txtTDate.Text != "") { sql2 = " where DueDate between '" + txtFDate.Text + "' and '" + txtTDate.Text + "'"; } if (ddlResponsibility.SelectedItem.Text != "All") { sql2 += (sql2 != string.Empty)? " and " : " where "; sql2 += " Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "'"; } if (ddlstatus.SelectedItem.Text != "All") { sql3 = (sql2 != string.Empty) ? " and " : " where "; sql3 += "Status='" + ddlstatus.SelectedItem.ToString() + "' "; } if (ddlPhase.SelectedItem.Text != "Both") { sql4 = (sql2 != string.Empty || sql3 != string.Empty) ? " and " : " where "; sql4 += "Phase='" + ddlPhase.SelectedValue + "'"; } //if (ddlResponsibility.SelectedItem.Text != "All" && ddlstatus.SelectedItem.Text != "All") //{ // //sql1 = "select FileNo,Description,AmtInFC,AmtInINR,Amt_InCond,Format(DueDate_Restoration,'dd-MM-yyyy') as DueDate_Restoration,Format(Intimation_Date,'dd-MM-yyyy') as Intimation_Date,Format(Confirmation_Date,'dd-MM-yyyy') as Confirmation_Date,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate,Status from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "' and Status='" + ddlstatus.SelectedItem.ToString() + "'"; // sql1 = "select FileNo,Description,Responsibility,Format(DueDate,'dd-MM-yyyy') as DueDate,AmtInFC,AmtInINR,Status,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "' and Status='" + ddlstatus.SelectedItem.ToString() + "'"; //} //else if (ddlResponsibility.SelectedItem.Text == "All" && ddlstatus.SelectedItem.Text != "All") //{ // //sql1 = "select FileNo,Description,AmtInFC,AmtInINR,Amt_InCond,Format(DueDate_Restoration,'dd-MM-yyyy') as DueDate_Restoration,Format(Intimation_Date,'dd-MM-yyyy') as Intimation_Date,Format(Confirmation_Date,'dd-MM-yyyy') as Confirmation_Date,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate,Status from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Status='" + ddlstatus.SelectedItem.ToString() + "'"; // sql1 = "select FileNo,Description,Responsibility,Format(DueDate,'dd-MM-yyyy') as DueDate,AmtInFC,AmtInINR,Status,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Status='" + ddlstatus.SelectedItem.ToString() + "'"; //} //else if (ddlstatus.SelectedItem.Text == "All" && ddlResponsibility.SelectedItem.ToString() != "All") //{ // //sql1 = "select FileNo,Description,AmtInFC,AmtInINR,Amt_InCond,Format(DueDate_Restoration,'dd-MM-yyyy') as DueDate_Restoration,Format(Intimation_Date,'dd-MM-yyyy') as Intimation_Date,Format(Confirmation_Date,'dd-MM-yyyy') as Confirmation_Date,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate,Status from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "'"; // sql1 = "select FileNo,Description,Responsibility,Format(DueDate,'dd-MM-yyyy') as DueDate,AmtInFC,AmtInINR,Status,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "' and Responsibility='" + ddlResponsibility.SelectedItem.ToString() + "'"; //} //else //{ // //sql1 = "select FileNo,Description,AmtInFC,AmtInINR,Amt_InCond,Format(DueDate_Restoration,'dd-MM-yyyy') as DueDate_Restoration,Format(Intimation_Date,'dd-MM-yyyy') as Intimation_Date,Format(Confirmation_Date,'dd-MM-yyyy') as Confirmation_Date,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate,Status from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "'"; // sql1 = "select FileNo,Description,Responsibility,Format(DueDate,'dd-MM-yyyy') as DueDate,AmtInFC,AmtInINR,Status,Format(POPaymentDate,'dd-MM-yyyy') as POPaymentDate from RenewalFollowup where DueDate between '" + txtFDate.Text + "'and '" + txtTDate.Text + "'"; //} string sql = sql1 + sql2 + sql3 + sql4; cmd1.CommandText = sql; cmd1.CommandType = CommandType.Text; cmd1.Connection = con; SqlDataReader dr5; dr5 = cmd1.ExecuteReader(); DataTable dt1 = new DataTable(); dt1.Load(dr5); GvReportDate.Visible = true; GridReportAmount.Visible = false; GvReportDate.DataSource = dt1; GvReportDate.DataBind(); } } }