using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
public static string cnst =@"Data Source=DESKTOP-PQAMMG9\SQLSERVER2008;Initial Catalog=PropretyTest;Integrated Security=True";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillPropType();
}
}
void FillPropType()
{
SqlConnection con = new SqlConnection(cnst);
SqlCommand cm = new SqlCommand("select * from TblPropType", con);
con.Open();
SqlDataReader dr = cm.ExecuteReader();
int i = 1;
DDLPropType.Items.Clear();
DDLPropType.Items.Add("select Prop Type");
if (dr.HasRows)
{
while (dr.Read())
{
DDLPropType.Items.Add(dr["PropTypeName"].ToString());
DDLPropType.Items[i].Value = dr["PropTypeId"].ToString();
i++;
}
}
}
void FillCircleName(int PropTypeId)
{
SqlConnection con = new SqlConnection(cnst);
SqlCommand cm = new SqlCommand("Select CircleId, CircleName from TblCircle where PropTypeId =" + PropTypeId, con);
con.Open();
SqlDataReader dr = cm.ExecuteReader();
int i=1;
DdlCircle.Items.Clear();
DdlCircle.Items.Add("select Circle");
if (dr.HasRows)
{
while (dr.Read())
{
DdlCircle.Items.Add(dr["CircleName"].ToString());
DdlCircle.Items[i].Value = dr["CircleId"].ToString();
i++;
}
}
}
protected void DDLPropType_SelectedIndexChanged(object sender, EventArgs e)
{
int PropTypeId = int.Parse(DDLPropType.SelectedValue);
FillCircleName(PropTypeId);
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
if (!checkRegsiterCount())
{
SqlConnection con = new SqlConnection(cnst);
string SQL = "Insert into tblStampDutyDetails(Pname,Gender,PropTypeId,CircleId,DateofRegister,Area,PropValue,StampDuty,TotalAmt) values(@Pname,@Gender,@PropTypeId,@CircleId,@DateofRegister,@Area,@PropValue,@StampDuty,@TotalAmt)";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.Add("@Pname", SqlDbType.VarChar).Value = TxtClientName.Text;
cmd.Parameters.Add("@Gender", SqlDbType.VarChar).Value = (RbtMale.Checked) ? "Male" : "Female";
cmd.Parameters.Add("@PropTypeId", SqlDbType.Int).Value = DDLPropType.SelectedValue;
cmd.Parameters.Add("@CircleId", SqlDbType.Int).Value = DdlCircle.SelectedValue;
cmd.Parameters.Add("@DateofRegister", SqlDbType.DateTime).Value = DateTime.Now.ToString("MM/dd/yyyy");
cmd.Parameters.Add("@Area", SqlDbType.VarChar).Value = TxtArea.Text;
cmd.Parameters.Add("@PropValue", SqlDbType.VarChar).Value = LblPropertyValue.Text;
cmd.Parameters.Add("@StampDuty", SqlDbType.VarChar).Value = LblStampDuty.Text;
cmd.Parameters.Add("@TotalAmt", SqlDbType.VarChar).Value = LbltotalAmt.Text;
cmd.Connection = con;
try
{
con.Open();
object obj = cmd.ExecuteScalar();
ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Data Stored successfully');", true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
else
ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('No more registeration allowed');", true);
}
int GetRate(int PropTypeId, string CircleName)
{
int rate = 0;
SqlConnection con = new SqlConnection(cnst);
SqlCommand cm = new SqlCommand("Select Rate from TblCircle where PropTypeId =" + PropTypeId +" and CircleName ='"+CircleName+"'", con);
con.Open();
SqlDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
rate = int.Parse(dr["Rate"].ToString());
}
return rate;
}
int GetCountRegister(int PropTypeId, int CircleId)
{
int cnt = 0;
SqlConnection con = new SqlConnection(cnst);
SqlCommand cm = new SqlCommand("Select PropTypeId from TblStampDutyDetails where PropTypeId =" + PropTypeId + " and CircleId=" + CircleId, con);
con.Open();
SqlDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
cnt++;
}
}
return cnt;
}
bool checkRegsiterCount()
{
bool found;
int NoofPlots = GetNoofPlots(int.Parse(DDLPropType.SelectedValue), int.Parse(DdlCircle.SelectedValue));
int totalRegister = GetCountRegister(int.Parse(DDLPropType.SelectedValue), int.Parse(DdlCircle.SelectedValue));
if (NoofPlots == totalRegister)
found = true;
else
found= false;
return found;
}
int GetNoofPlots(int PropTypeId, int CircleId)
{
int cnt = 0;
SqlConnection con = new SqlConnection(cnst);
SqlCommand cm = new SqlCommand("Select NoofPlots from TblCircle where PropTypeId =" + PropTypeId + " and CircleId=" + CircleId, con);
con.Open();
SqlDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
cnt = int.Parse(dr[0].ToString());
}
return cnt;
}
protected void TxtArea_TextChanged(object sender, EventArgs e)
{
long propValue = long.Parse(TxtArea.Text) * long.Parse(LblRate.Text);
LblPropertyValue.Text = propValue.ToString();
long StampDuty = propValue * 5 / 100;
LblStampDuty.Text = StampDuty.ToString();
long TotalAmount = propValue + StampDuty;
LbltotalAmt.Text = TotalAmount.ToString();
}
protected void DdlCircle_SelectedIndexChanged(object sender, EventArgs e)
{
int PropTypeId= int.Parse( DDLPropType.SelectedValue);
string CircleName = DdlCircle.SelectedItem.Text;
LblRate.Text = GetRate(PropTypeId, CircleName).ToString();
}
}
protected void BtnReport_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(cnst);
string SQL = "Select StampDutyId, Pname,Gender,PropTypeName,PropValue,StampDuty,TotalAmt from tblStampDutyDetails inner join tblPropType on tblStampDutyDetails.PropTypeId =TblPropType.PropTypeId where DateofRegister between '" + DateTime.Parse(TxtDateFrom.Text).ToString("dd/MMM/yy") + "' and '" + DateTime.Parse(TxtDateTo.Text).ToString("dd/MMM/yy") + "'";
SqlDataAdapter da = new SqlDataAdapter(SQL, con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void BtnView_Click(object sender, EventArgs e)
{
char[] sp = { '-' };
string[] mm = DropDownList1.Text.Split(sp);
string Month = mm[0];
string YY = mm[1];
SqlConnection con = new SqlConnection(cnst);
string SQL = "SELECT TblPropType.PropTypeName, COUNT(TblPropType.PropTypeName) AS TotProperty, SUM(TblStampDutyDetails.StampDuty) AS TotStampDuty FROM TblStampDutyDetails INNER JOIN TblPropType ON TblStampDutyDetails.PropTypeId = TblPropType.PropTypeId WHERE (MONTH(TblStampDutyDetails.DateofRegister) = " + Month + ") AND (YEAR(TblStampDutyDetails.DateofRegister) =" + YY + ") GROUP BY TblPropType.PropTypeName";
SqlDataAdapter da = new SqlDataAdapter(SQL, con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView2.DataSource = dt;
GridView2.DataBind();
}
