public string saveArea(mdlArea areainfo)
{
string result = "";
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID FROM AREAS WHERE AREA=@AREA";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
result = "Area already exist";
}
else
{
dr.Dispose();
cmd.Dispose();
strSQL = @"INSERT INTO AREAS(AREA,ACTIVE,ENCODEDBY,MODIFIEDBY,DATEMODIFIED,DATEENCODED,AREATYPE)
VALUES(@AREA,@ACTIVE,@ENCODEDBY,@MODIFIEDBY,GETDATE(),GETDATE(),@AREATYPE);SELECT SCOPE_IDENTITY()";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA;
cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = areainfo.ACTIVE;
cmd.Parameters.Add("ENCODEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("AREATYPE", SqlDbType.VarChar).Value = areainfo.AREATYPE;
var id = cmd.ExecuteScalar();
result = id.ToString();
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(result);
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getAreas()
{
List <mdlArea> model = new List <mdlArea>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT dbo.areas.ID, dbo.areas.AREA, Case When dbo.areas.ACTIVE = 'A' Then 'Active' Else 'Inactive' End AS ACTIVE , dbo.areatype.AREATYPE, dbo.areas.MAP
FROM dbo.areas LEFT OUTER JOIN dbo.areatype ON dbo.areas.AREATYPE = dbo.areatype.ID
ORDER BY dbo.areas.AREA";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
mdlArea list = new mdlArea();
while (dr.Read())
{
list = new mdlArea();
list.ID = dr["ID"].ToString();
list.AREA = dr["AREA"].ToString();
list.AREATYPE = dr["AREATYPE"].ToString();
list.ACTIVE = dr["ACTIVE"].ToString();
list.MAP = dr["MAP"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getAreaInfo(string id)
{
mdlArea model = new mdlArea();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID,AREA,AREATYPE,ACTIVE FROM AREAS WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = id;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
model.ID = dr["ID"].ToString();
model.AREATYPE = dr["AREATYPE"].ToString();
model.AREA = dr["AREA"].ToString();
model.ACTIVE = dr["ACTIVE"].ToString();
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getAreaType()
{
List <mdlArea> model = new List <mdlArea>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID,AREATYPE FROM AREATYPE WHERE ACTIVE='A' ";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
mdlArea list = new mdlArea();
while (dr.Read())
{
list = new mdlArea();
list.ID = dr["ID"].ToString();
list.AREATYPE = dr["AREATYPE"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public string updateArea(mdlArea areainfo)
{
string result = "";
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID FROM AREAS WHERE AREA=@AREA";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["ID"].ToString() != areainfo.ID)
{
result = "Area already exist";
}
}
dr.Dispose();
cmd.Dispose();
if (result == "")
{
strSQL = @"UPDATE AREAS SET AREA=@AREA,AREATYPE=@AREATYPE,ACTIVE=@ACTIVE,MODIFIEDBY=@MODIFIEDBY,DATEMODIFIED=GETDATE()
WHERE ID=@ID";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA;
cmd.Parameters.Add("AREATYPE", SqlDbType.VarChar).Value = areainfo.AREATYPE;
cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = areainfo.ACTIVE;
cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = areainfo.ID;
cmd.ExecuteNonQuery();
dr.Dispose();
cmd.Dispose();
result = "Area successfully updated";
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(result);
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
dbconn.closeConnection();
}
}
