public DataSet GetApplicationStatus()
{
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@UserName", SqlDbType.VarChar);
p[0].Value = _UserName;
return(SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spGetApplicationStatus", p));
}
public DataSet GetApplicationLeaveDetails()
{
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@ApplicationNo", SqlDbType.Int);
p[0].Value = _ApplicationNo;
return(SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spGetApplicationLeave", p));
}
public DataSet GetCity()
{
string strSql = string.Empty;
strSql = "select d.CityId,d.CityName,d.CityDescription,st.StateName,c.CountryName,s.StatusName from tbl_CityDetails d,tbl_Country c,tbl_State st,tbl_Status s where d.StatusId=s.StatusId and d.StateId=st.StateId and d.CountryId=c.CountryId";
if (_CityName != "")
{
strSql += " and CityName like '%" + _CityName + "%'";
}
if (_CountryName != "select" && _CountryName != null)
{
strSql += " and CountryName='" + _CountryName + "'";
}
if (_StateName != "select" && _StateName != null)
{
strSql += " and StateName='" + _StateName + "'";
}
if (Sort_On != "")
{
strSql = strSql + " ORDER BY " + Sort_On;
}
else
{
strSql = strSql + " Order By CityName";
}
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public int AddEmployeeDetails()
{
SqlParameter[] p = new SqlParameter[13];
p[0] = new SqlParameter("@DeptId", this._DeptId);
p[0].DbType = DbType.Int32;
p[1] = new SqlParameter("@ContactNo", this._ContactNo);
p[1].DbType = DbType.String;
p[2] = new SqlParameter("@Address", this._Address);
p[2].DbType = DbType.String;
p[3] = new SqlParameter("@CityId", this._CityId);
p[3].DbType = DbType.Int32;
p[4] = new SqlParameter("@StateId", this._StateId);
p[4].DbType = DbType.Int32;
p[5] = new SqlParameter("@CountryId", this._CountryId);
p[5].DbType = DbType.Int32;
p[6] = new SqlParameter("@EmailId", this._EmailId);
p[6].DbType = DbType.String;
p[7] = new SqlParameter("@EmpName", this._EmpName);
p[7].DbType = DbType.String;
p[8] = new SqlParameter("@output", SqlDbType.Int);
p[8].Direction = ParameterDirection.Output;
p[9] = new SqlParameter("@UserName", this._UserName);
p[9].DbType = DbType.String;
p[10] = new SqlParameter("@Password", this._Password);
p[10].DbType = DbType.String;
p[11] = new SqlParameter("@DesigName", SqlDbType.VarChar);
p[11].Value = _Designation;
p[12] = new SqlParameter("@Role", SqlDbType.VarChar);
p[12].Value = _Role;
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spAddEmployee", p);
return((int)p[8].Value);
}
public DataSet GetState()
{
string strSql = string.Empty;
strSql = "select st.StateId,st.StateName,st.StateDescription,c.CountryName,s.StatusName from tbl_Status s, tbl_State st,tbl_Country c where st.CountryId=c.CountryId and s.StatusId=st.StatusId";
if (_StateName != "")
{
strSql += " and StateName like '%" + _StateName + "%'";
}
if (_CountryName != "select" && _CountryName != null)
{
strSql = strSql + " and CountryName='" + _CountryName + "'";
}
//else
//{
// strSql = strSql + " and CountryName='" + _CountryName + "'";
//}
if (Sort_On != "")
{
strSql = strSql + " ORDER BY " + Sort_On;
}
else
{
strSql = strSql + " Order By StateName";
}
DataSet dsTemp;
//dsTemp = objDbConnector.GetDataSet(strSql);
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public void UpdateEmployeeDetails()
{
SqlParameter[] p = new SqlParameter[10];
p[0] = new SqlParameter("@DeptId", this._DeptId);
p[0].DbType = DbType.Int32;
p[1] = new SqlParameter("@ContactNo", this._ContactNo);
p[1].DbType = DbType.String;
p[2] = new SqlParameter("@Address", this._Address);
p[2].DbType = DbType.String;
p[3] = new SqlParameter("@CityId", this._CityId);
p[3].DbType = DbType.Int32;
p[4] = new SqlParameter("@StateId", this._StateId);
p[4].DbType = DbType.Int32;
p[5] = new SqlParameter("@CountryId", this._CountryId);
p[5].DbType = DbType.Int32;
p[6] = new SqlParameter("@EmailId", this._EmailId);
p[6].DbType = DbType.String;
p[7] = new SqlParameter("@UserName", this._UserName);
p[7].DbType = DbType.String;
p[8] = new SqlParameter("@Desig", this._Designation);
p[8].DbType = DbType.String;
p[9] = new SqlParameter("@EmpName", this._EmpName);
p[9].DbType = DbType.String;
// p[8] = new SqlParameter("@StatusId", this._StatusId);
// p[8].DbType = DbType.Int32;
//p[9] = new SqlParameter("@output", SqlDbType.Int);
//p[9].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spUpdataEmployee", p);
}
public DataSet GetEmployeeOnProject()
{
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@ProjectId", SqlDbType.Int);
p[0].Value = _projId;
return(SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spGetEmployeeOnProject", p));
}
//jan 04
public DataSet GetApplicationData()
{
string strSql = string.Empty;
strSql = "select * from tbl_ApplicationData a,tbl_Status s,tbl_LeaveType l where a.ApplicationStatusId=s.StatusId and a.LeaveTypeId=l.LeaveTypeId ";
if (!string.IsNullOrEmpty(_UserName))
{
strSql += " and a.UserName like '%" + _UserName + "%'";
}
if (_LeaveTypeId != 0)
{
strSql += " and a.LeaveTypeId=" + _LeaveTypeId;
}
if (!string.IsNullOrEmpty(Sort_On))
{
strSql = strSql + " ORDER BY " + Sort_On;
}
else
{
strSql = strSql + " Order By a.UserName";
}
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public void GetStateDetails()
{
string strSql = string.Empty;
strSql += "select st.StateId,st.StateName,st.StateDescription,c.CountryName,s.StatusName from tbl_Status s, tbl_State st,tbl_Country c where st.CountryId=c.CountryId and s.StatusId=st.StatusId";
strSql += " and StateId=" + _StateId;
DataSet dsTemp;
// dsTemp = objDbConnector.GetDataSet(strSql);
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
DataRowCollection drc = dsTemp.Tables[0].Rows;
if (drc.Count > 0)
{
_StateName = drc[0]["StateName"].ToString();
_StateDescr = drc[0]["StateDescription"].ToString();
_CountryName = drc[0]["CountryName"].ToString();
_StatusName = drc[0]["StatusName"].ToString();
}
else
{
_StateName = "";
_StateDescr = "";
_CountryName = "";
_StatusName = "";
}
}
public void DeleteCountry(string CountryId)
{
string strSql = string.Empty;
strSql += "delete from tbl_Country";
strSql += " where CountryId in('" + CountryId.Replace(",", "','") + "')";
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public void DeleteState(string StateId)
{
string strSql = string.Empty;
strSql += "delete from tbl_State";
strSql += " where StateId in('" + StateId.Replace(",", "','") + "')";
//objDbConnector.runSQL(strSql);
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public void UpdateApplicationData()
{
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@StausId", SqlDbType.Int);
p[0].Value = _AppStatusId;
p[1] = new SqlParameter("@ApplicationNo", SqlDbType.Int);
p[1].Value = _ApplicationNo;
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spUpdateApplicationData", p);
}
public void AddStatus()
{
string strSql = string.Empty;
strSql += "Insert into tbl_Status(StatusName,StatusDescription)";
strSql += " values('" + _StatusName + "',";
strSql += " '" + _StatusDescr + "')";
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public void AddCountry()
{
string strSql = string.Empty;
strSql += "Insert into tbl_Country(CountryName,CountryDescription,StatusId)";
strSql += " values('" + _CountryName + "',";
strSql += " '" + _CountryDescr + "'," + _StatusId + ")";
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public DataSet GetStatusName()
{
string strSql = string.Empty;
strSql += "select StatusName,StatusId from tbl_Status";
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public void AddState()
{
string strSql = string.Empty;
strSql += "Insert into tbl_State(StateName,StateDescription,CountryId,StatusId)";
strSql += " values('" + _StateName + "',";
strSql += " '" + _StateDescr + "'," + _CountryId + "," + _StatusId + ")";
//objDbConnector.runSQL(strSql);
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public void UpdateStatus()
{
string strSql = string.Empty;
strSql += "Update tbl_Status set";
strSql += " StatusName='" + _StatusName + "',";
strSql += " StatusDescription='" + _StatusDescr + "'";
strSql += " where StatusId=" + _StatusId;
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public DataSet GetDesignation()
{
string strSql = string.Empty;
strSql += "select * from tbl_Designation";
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public DataSet GetCountryName()
{
string strSql = string.Empty;
strSql += "select CountryName,CountryId from tbl_Country";
DataSet dsTemp;
//ds = objDbConnector.GetDataSet(strSql);
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public int AdminChangeOldPassword()
{
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@AdminLoginId", SqlDbType.VarChar);
p[0].Value = _AdminLoginId;
p[1] = new SqlParameter("@Password", SqlDbType.VarChar);
p[1].Value = _Password;
int i = SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spAdminChangePassword", p);
return(i);
}
public DataSet GetAdminLoginDetails()
{
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@AdminLoginId", SqlDbType.VarChar);
p[0].Value = _AdminLoginId;
p[1] = new SqlParameter("@Password", SqlDbType.VarChar);
p[1].Value = _Password;
DataSet tempds = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spAdminLogin", p);
return(tempds);
}
public void UpdateState()
{
string strSql = string.Empty;
strSql += "Update tbl_State set";
strSql += " StateName='" + _StateName + "',";
strSql += " StateDescription='" + _StateDescr + "',";
strSql += " CountryId=" + _CountryId + ",";
strSql += " StatusId=" + _StatusId;
strSql += " where StateId=" + _StateId;
//objDbConnector.runSQL(strSql);
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
}
public DataSet GetStateName()
{
string strSql = string.Empty;
strSql += "select StateName,StateId from tbl_State";
if (_CountryId != 0)
{
strSql += " where CountryId=" + _CountryId;
}
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public DataSet GetCityName()
{
string strSql = string.Empty;
strSql += "select CityName,CityId from tbl_CityDetails";
if (_StateId != 0)
{
strSql += " where StateId=" + _StateId;
}
DataSet dsTemp;
dsTemp = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.Text, strSql);
return(dsTemp);
}
public int GetBalanceDays()
{
SqlParameter[] p = new SqlParameter[3];
p[0] = new SqlParameter("@LeaveTypeId", SqlDbType.Int);
p[0].Value = _LeaveTypeId;
p[1] = new SqlParameter("@UserName", SqlDbType.VarChar);
p[1].Value = _UserName;
p[2] = new SqlParameter("@Balanceleaves", SqlDbType.Int);
p[2].Direction = ParameterDirection.Output;
SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spGetBalanceDays", p);
//return i;
return((int)p[2].Value);
}
public DataSet GetEmployeeLoginDetails()
{
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@UserLoginId", SqlDbType.VarChar);
p[0].Value = this._EmployeeLoginId;
p[1] = new SqlParameter("@Password", SqlDbType.VarChar);
p[1].Value = this.Password;
DataSet dsEmployeeLoginDetails = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spEmployeeLoginDetails", p);
return(dsEmployeeLoginDetails);
}
public void AddLeaveDetails()
{
SqlParameter[] p = new SqlParameter[5];
p[0] = new SqlParameter("@UserName", SqlDbType.VarChar);
p[0].Value = _UserName;
p[1] = new SqlParameter("@ApplicationNo", SqlDbType.Int);
p[1].Value = _ApplicationNo;
p[2] = new SqlParameter("@AppStatusId", SqlDbType.Int);
p[2].Value = _AppStatusId;
p[3] = new SqlParameter("@BalanceLeaves", SqlDbType.Int);
p[3].Value = _BalanceLeaves;
p[4] = new SqlParameter("@LeaveTypeId", SqlDbType.Int);
p[4].Value = _LeaveTypeId;
SqlHelper.ExecuteNonQuery(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spAddLeaveDetails", p);
}
public bool CheckAppNoInLeaveDetails()
{
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@ApplicationNo", SqlDbType.Int);
p[0].Value = _ApplicationNo;
DataSet ds = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spCheckAppNoInLeaveDetails", p);
if (ds.Tables[0].Rows.Count > 0)
{
return(false);
}
else
{
return(true);
}
}
public bool CheckAdminOldPassword()
{
SqlParameter[] p = new SqlParameter[2];
p[0] = new SqlParameter("@AdminLoginId", SqlDbType.VarChar);
p[0].Value = _AdminLoginId;
p[1] = new SqlParameter("@Password", SqlDbType.VarChar);
p[1].Value = _OldPassword;
DataSet tempds = SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spCheckAdminOldPassword", p);
DataRowCollection drc = tempds.Tables[0].Rows;
if (drc.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
//Jan 04 11.09am
public void GetTotalBalanceDays()
{
SqlParameter[] p = new SqlParameter[5];
p[0] = new SqlParameter("@UserName", SqlDbType.VarChar);
p[0].Value = _UserName;
p[1] = new SqlParameter("@CasualBalanceLeaves", SqlDbType.Int);
p[1].Direction = ParameterDirection.Output;
p[2] = new SqlParameter("@EarnBalanceLeaves", SqlDbType.Int);
p[2].Direction = ParameterDirection.Output;
p[3] = new SqlParameter("@HalfPaidBalanceLeaves", SqlDbType.Int);
p[3].Direction = ParameterDirection.Output;
p[4] = new SqlParameter("@MedicalBalanceLeaves", SqlDbType.Int);
p[4].Direction = ParameterDirection.Output;
SqlHelper.ExecuteDataset(ClsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spGetTotalBalanceDays", p);
_CasualBalanceLeaves = (int)p[1].Value;
_EarnBalanceLeaves = (int)p[2].Value;
_HalfPaidBalanceLeaves = (int)p[3].Value;
_MedicalBalanceLeaves = (int)p[4].Value;
}