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();
}
}
}