private static string GetDecryptPwd(string pwd)
{
string DecryptPwd = string.Empty;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select decrypt('" + pwd + "') from dual";
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader oreader = cmd.ExecuteReader();
if (oreader.HasRows)
{
while (oreader.Read())
{
DecryptPwd = Convert.ToString(oreader[0]);
}
}
oreader.Close();
cmd.Dispose();
}
return(DecryptPwd);
}
public static DataTable GetList(string strQry)
{
DataTable dtable = null;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand cmd = Conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
try
{
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
dtable = new DataTable();
da.Fill(dtable);
}
}
catch
{
throw;
}
}
}
return(dtable);
}
public static User GetItem(int dbID)
{
User ObjUser = null;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "Select * From UserProfile " +
"Where dbid = @dbid";
objCmd.Parameters.AddWithValue("@dbid", dbID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader objReader = objCmd.ExecuteReader();
if (objReader.Read())
{
ObjUser = FillDataRecord(objReader);
ObjUser.IsNew = false;
}
objReader.Close();
objReader.Dispose();
}
}
return(ObjUser);
}
private static string GetEncryptPwd(string pwd)
{
string EncryptPwd;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "encryptproc";
cmd.Parameters.Add("plain", SqlDbType.VarChar).Value = pwd;
cmd.Parameters.Add("mLogName", SqlDbType.VarChar, 15).Direction = ParameterDirection.Output;
try
{
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
cmd.ExecuteNonQuery();
EncryptPwd = Convert.ToString(cmd.Parameters["mLogName"].Value);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
Conn.Close();
}
return(EncryptPwd);
}
public static DataTable GetCompFinYear()
{
string strQry;
DataTable dTable = new DataTable();
try
{
strQry = "Select cf.dbid, Plant, YEAR(fromdate) fromdt, " +
" YEAR(todate) Todt " +
" FROM CompFinYr cf, SysCompany c " +
" WHERE cf.compDBID = c.CompanyID " +
" order by cf.dbid desc ";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strQry;
using (SqlDataAdapter da = new SqlDataAdapter(objCmd))
{
da.Fill(dTable);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dTable);
}
/// <summary>
/// Open an Appointment when a VisitorGP is Deleted.
/// </summary>
/// <param name="objVisitorGP"></param>
public static void ReOpenAppointment(string strApmtNo)
{
int result = 0;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
try
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "UPDATE APPOINTMENTMASTER " +
" SET APMTCLOSE = @value " +
" WHERE APPOINTMENTNO = @AppointmentNo ";
objCmd.Parameters.AddWithValue("@value", false);
objCmd.Parameters.AddWithValue("@AppointmentNo", strApmtNo);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
public static DataTable GetUserFormRights(long UserID, string strMenu)
{
//UserRightsList objList = null;
DataTable dTable = null;
string strSql = "Select * from USERRIGHTS " +
" WHERE USERID = @mUserID " +
" AND MENUID LIKE @mMenu " +
" ORDER BY MENUID ";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
objCmd.Parameters.AddWithValue("mUserID", UserID);
objCmd.Parameters.AddWithValue("mMenu", strMenu + "%");
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(objCmd))
{
dTable = new DataTable();
da.Fill(dTable);
}
}
}
return(dTable);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static UserList GetList(string strSelectQry)
{
UserList objList = null;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSelectQry;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader objReader = objCmd.ExecuteReader();
if (objReader.HasRows)
{
objList = new UserList();
while (objReader.Read())
{
objList.Add(FillDataRecord4List(objReader));
}
}
objReader.Close();
objReader.Dispose();
}
}
return(objList);
}
/// <summary>
/// Returns Registration No. For New Visitor
/// </summary>
/// <returns></returns>
public static int GetVRegNo()
{
int result = 0;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
SqlCommand ObjCmd = Conn.CreateCommand();
ObjCmd.CommandType = CommandType.Text;
ObjCmd.CommandText = "select Max(VISITORREGNO) VREGNO " +
" from visitormaster ";
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
object objTemp = ObjCmd.ExecuteScalar();
if (objTemp == null || objTemp == DBNull.Value)
{
result = 1;
}
else
{
result = Convert.ToInt32(objTemp);
result += 1;
}
ObjCmd.Dispose();
}
return(result);
}
/// <summary>
/// This method Saves Record into Database.
/// </summary>
/// <param name="objDept">Object containing Data values to be saved.</param>
/// <returns>Boolean value True if Record is saved successfully
/// otherwise returns False indicating Record is not saved.</returns>
public static bool Save(Department objDept, User objCurUser)
{
int result = 0;
UserCompany CurrentCompany = new UserCompany();
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
string strSaveQry;
if (objDept.IsNew)
{
strSaveQry = "INSERT INTO DEPTMAST(DBID, DEPTNAME, DESCRIPTION, ISACTIVE, " +
" ST_DATE, MODIFY_DATE, CRBY, MODBY, MACHINENAME) " +
"VALUES (@dbId, @DeptName, @Descr, @IsActive, " +
" @STDate, @ModifyDate, @CrBy, @ModBy, @MachineName)";
}
else
{
strSaveQry = "UPDATE DEPTMAST " +
"SET DEPTNAME = @DeptName, DESCRIPTION = @Descr, ISACTIVE = @IsActive, " +
"MODIFY_DATE = @ModifyDate, MODBY = @ModBy, MACHINENAME = @MachineName " +
"WHERE DBID = @dbId";
}
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSaveQry;
objCmd.Parameters.AddWithValue("@DeptName", objDept.DeptName);
objCmd.Parameters.AddWithValue("@Descr", objDept.Description);
objCmd.Parameters.AddWithValue("@IsActive", objDept.IsActive);
if (objDept.IsNew)
{
objCmd.Parameters.AddWithValue("@StDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@CrBy", objCurUser.LoginName);
objDept.DBID = General.GenerateDBID(Conn, "DEPTMAST");
}
objCmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@ModBy", objCurUser.LoginName);
objCmd.Parameters.AddWithValue("@MachineName", General.GetMachineName());
objCmd.Parameters.AddWithValue("@dbID", objDept.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result > 0);
}
/// <summary>
/// This method provides List of Visitor-GatePass available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Visitor GatePass Objects.</returns>
public static VisitorGatePassList GetList(string strWhere, DateTime vwDate, bool flgShowAll)
{
VisitorGatePassList objList = null;
string strSql = "Select * from VISITORGATEPASS ";
if (!flgShowAll)
{
strSql += " WHERE GATEDATE = @gateDate ";
if (strWhere != string.Empty)
{
strSql = strSql + " AND " + strWhere;
}
}
else
{
if (strWhere != string.Empty)
{
strSql = strSql + " WHERE " + strWhere;
}
}
strSql += " ORDER BY GATEDATE DESC, GATEPASSNO DESC";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
if (!flgShowAll)
{
objCmd.Parameters.AddWithValue("@gateDate", vwDate.ToString("dd-MMM-yy"));
}
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new VisitorGatePassList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
/// <summary>
/// This method Saves Record into Database.
/// </summary>
/// <param name="objDriver">Object containing Data values to be saved.</param>
/// <returns>Boolean value True if Record is saved successfully
/// otherwise returns False indicating Record is not saved.</returns>
public static bool Save(Driver objDriver, User objUser)
{
int result = 0;
UserCompany CurrentCompany = new UserCompany();
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
string strSaveQry;
if (objDriver.IsNew)
{
strSaveQry = "INSERT INTO DRIVERMASTER(DBID, NAME, LICENSENO, ISACTIVE, " +
" ST_DATE, MODIFY_DATE, CRBY, MODBY, MACHINENAME) " +
"VALUES (@dbId, @Name, @LicenseNo, @IsActive, " +
" @STDate, @ModifyDate, @CrBy, @ModBy, @MachineName)";
}
else
{
strSaveQry = "UPDATE DRIVERMASTER " +
"SET NAME = @Name, LICENSENO = @LicenseNo, ISACTIVE = @IsActive, " +
"MODIFY_DATE = @ModifyDate, MODBY = @ModBy, MACHINENAME = @MachineName " +
"WHERE DBID = @dbId";
}
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSaveQry;
objCmd.Parameters.AddWithValue("@Name", objDriver.Name);
objCmd.Parameters.AddWithValue("@LicenseNo", objDriver.LicenceNo);
objCmd.Parameters.AddWithValue("@IsActive", objDriver.IsActive);
if (objDriver.IsNew)
{
objCmd.Parameters.AddWithValue("@StDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@CrBy", objUser.LoginName);
objDriver.DBID = General.GenerateDBID(Conn, "DRIVERMASTER");
}
objCmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@ModBy", objUser.LoginName);
objCmd.Parameters.AddWithValue("@MachineName", General.GetMachineName());
objCmd.Parameters.AddWithValue("@dbID", objDriver.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result > 0);
}
/// <summary>
/// This method Saves Record into Database.
/// </summary>
/// <param name="objDept">Object containing Data values to be saved.</param>
/// <returns>Boolean value True if Record is saved successfully
/// otherwise returns False indicating Record is not saved.</returns>
public static bool Save(City objCity, User objUser)
{
int result = 0;
UserCompany CurrentCompany = new UserCompany();
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
string strSaveQry;
if (objCity.IsNew)
{
strSaveQry = "INSERT INTO CITYMASTER(DBID, CITY, " +
" ST_DATE, MODIFY_DATE, CRBY, MODBY, MACHINENAME) " +
"VALUES (@dbId, @City, " +
" @STDate, @ModifyDate, @CrBy, @ModBy, @MachineName)";
}
else
{
strSaveQry = "UPDATE CITYMASTER " +
"SET CITY = @CITY, MODIFY_DATE = @ModifyDate, " +
"MODBY = @ModBy, MACHINENAME = @MachineName " +
"WHERE DBID = @dbId";
}
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSaveQry;
objCmd.Parameters.AddWithValue("@CITY", objCity.mCity);
if (objCity.IsNew)
{
objCmd.Parameters.AddWithValue("@StDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@CrBy", objUser.LoginName);
objCity.DBID = General.GenerateDBID(Conn, "CITYMASTER");
}
objCmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now);
objCmd.Parameters.AddWithValue("@ModBy", objUser.LoginName);
objCmd.Parameters.AddWithValue("@MachineName", General.GetMachineName());
objCmd.Parameters.AddWithValue("@dbID", objCity.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result > 0);
}
/// <summary>
/// This method provides List of Visitors available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Visitor Objects.</returns>
public static VisitorList GetList(string strCompany, string strContactNo)
{
VisitorList objList = null;
string strSql = "Select * from VISITORMASTER ";
if (strCompany != string.Empty & strContactNo == string.Empty)
{
strSql += " WHERE COMPANY = '" + strCompany + "'";
}
else if (strCompany == string.Empty & strContactNo != string.Empty)
{
strSql += " WHERE CONTACTNO = '" + strContactNo + "'";
}
else if ((strCompany != string.Empty) & (strContactNo != string.Empty))
{
strSql += " WHERE COMPANY = '" + strCompany +
"' AND CONTACTNO = '" + strContactNo + "'";
}
strSql += " ORDER BY VISITORREGNO";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new VisitorList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
/// <summary>
/// This method provides List of Returnable DCs available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Returnable DC Objects.</returns>
public static ReturnableDCList GetList(string entryType, DateTime lvDate, bool flgShowAll)
{
ReturnableDCList objList = null;
string strSql = "SELECT * " +
" FROM ReturnableDC " +
" WHERE ENTRYTYPE = @entryType ";
if (!flgShowAll)
{
strSql += " AND ENTRYDATE = @entryDate ";
}
strSql += " ORDER BY ENTRYDATE DESC, ENTRYNO DESC";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
objCmd.Parameters.AddWithValue("@entryType", entryType);
if (!flgShowAll)
{
objCmd.Parameters.AddWithValue("@entryDate", lvDate.ToString("dd-MMM-yy"));
}
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new ReturnableDCList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
/// <summary>
/// This method provides List of Returnable DCs available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Returnable DC Objects.</returns>
public static ReturnableDCItemList GetList(long pEntryNo, DateTime pEntryDate, long pMasterDBID)
{
ReturnableDCItemList objList = null;
string strSql = "Select * from ReturnableDCDetail " +
" WHERE ENTRYNO = @entryNo " +
" AND ENTRYDATE = @entryDate " +
" AND MASTERDBID = @masterDBID " +
" ORDER BY DBID";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
objCmd.Parameters.AddWithValue("@entryNo", pEntryNo);
objCmd.Parameters.AddWithValue("@entryDate", pEntryDate);
objCmd.Parameters.AddWithValue("@masterDBID", pMasterDBID);
try
{
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new ReturnableDCItemList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
catch
{
throw;
}
}
}
return(objList);
}
/// <summary>
/// This method Checks whether Current Employee already exists in Database or not.
/// </summary>
/// <param name="objEmp">Object Containing New Data Values.</param>
/// <returns>Boolean value True if Current Record already exists
/// otherwise returns False indicating current Record Does not exist.</returns>
public static bool IsEmployeeExist(Employee objEmp)
{
bool IsRecordExist = false;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT DBID FROM EMPMAST " +
" WHERE FIRSTNAME = @mFName " +
" AND MIDDLENAME = @mMidName " +
" AND LASTNAME = @mLName " +
" AND DBID <> @dbID ";
objCmd.Parameters.AddWithValue("@mFName", objEmp.FirstName);
objCmd.Parameters.AddWithValue("@mMidName", objEmp.MiddleName);
objCmd.Parameters.AddWithValue("@mLName", objEmp.LastName);
objCmd.Parameters.AddWithValue("@dbID", objEmp.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader objReader = objCmd.ExecuteReader())
{
if (objReader.HasRows)
{
while (objReader.Read())
{
IsRecordExist = true;
}
}
else
{
IsRecordExist = false;
}
}
}
catch (ApplicationException ex)
{
throw new Exception(ex.Message);
}
}
return(IsRecordExist);
}
/// <summary>
/// This method Checks whether Current Dept already exists in Database or not.
/// </summary>
/// <param name="objAppoint">Object Containing New Data Values.</param>
/// <returns>Boolean value True if Current Record already exists
/// otherwise returns False indicating current Record Does not exist.</returns>
public static bool IsAppointExist(Appointment objAppoint)
{
bool IsRecordExist = false;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT DBID FROM APPOINTMENTMASTER " +
" WHERE NAME = @mName AND APPOINTMENTDATE = @mDate " +
" AND DBID <> @dbID ";
objCmd.Parameters.AddWithValue("@mName", objAppoint.Name);
objCmd.Parameters.AddWithValue("@mDate", objAppoint.AppointmentDate);
objCmd.Parameters.AddWithValue("@dbID", objAppoint.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader objReader = objCmd.ExecuteReader())
{
if (objReader.HasRows)
{
while (objReader.Read())
{
IsRecordExist = true;
}
}
else
{
IsRecordExist = false;
}
}
}
catch (ApplicationException ex)
{
throw new Exception(ex.Message);
}
}
return(IsRecordExist);
}
public static DataTable GetDCReport(string entryType, DateTime FromDate, DateTime ToDate, string partyName)
{
DataTable objList = null;
string strSql = "SELECT RETURNABLEDC.ENTRYNO, RETURNABLEDC.ENTRYDATE, RETURNABLEDC.ENTRYTYPE, " +
" RETURNABLEDC.PARTYNAME, RETURNABLEDC.DCNO, RETURNABLEDC.DCDATE, RETURNABLEDC.VINDATE, " +
" RETURNABLEDC.VINTIME, RETURNABLEDC.VOUTDATE, RETURNABLEDC.VOUTTIME, RETURNABLEDCDETAIL.ITEMCODE, " +
" RETURNABLEDCDETAIL.QTY, RETURNABLEDCDETAIL.UNIT " +
" FROM RETURNABLEDC INNER JOIN RETURNABLEDCDETAIL ON (RETURNABLEDC.ENTRYNO = RETURNABLEDCDETAIL.ENTRYNO) " +
" AND (RETURNABLEDC.ENTRYDATE = RETURNABLEDCDETAIL.ENTRYDATE) " +
" WHERE RETURNABLEDC.ENTRYTYPE = @entryType " +
" AND RETURNABLEDC.ENTRYDATE BETWEEN @FromDate and @ToDate ";
if (partyName != "ALL")
{
strSql += " AND RETURNABLEDC.PARTYNAME = @PartyName ";
}
strSql += " ORDER BY ENTRYDATE ASC, ENTRYNO ASC";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
objCmd.Parameters.AddWithValue("@entryType", entryType);
objCmd.Parameters.AddWithValue("@FromDate", FromDate);
objCmd.Parameters.AddWithValue("@ToDate", ToDate);
if (partyName != "ALL")
{
objCmd.Parameters.AddWithValue("@PartyName", partyName);
}
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataAdapter da = new SqlDataAdapter(objCmd);
da.Fill(objList);
}
}
return(objList);
}
private static int GenerateDBID()
{
int id = 0;
SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString());
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "Select Max(DBID) ID from UserProfile ";
try
{
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader objReader = objCmd.ExecuteReader();
if (objReader.HasRows)
{
while (objReader.Read())
{
id = Convert.ToInt32(objReader["id"]);
id += 1;
}
}
else
{
id = 1;
}
objReader.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
objCmd.Dispose();
Conn.Close();
Conn.Dispose();
}
return(id);
}
/// <summary>
/// This method Checks whether Current Dept already exists in Database or not.
/// </summary>
/// <param name="objVehicle">Object Containing New Data Values.</param>
/// <returns>Boolean value True if Current Record already exists
/// otherwise returns False indicating current Record Does not exist.</returns>
public static bool IsVehicleExist(Vehicle objVehicle)
{
bool IsRecordExist = false;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT DBID FROM VEHICLEMASTER " +
" WHERE DEPTNAME = @mVEHICLENO " +
" AND DBID <> @dbID ";
objCmd.Parameters.AddWithValue("@mVEHICLENO", objVehicle.VehicleNo);
objCmd.Parameters.AddWithValue("@dbID", objVehicle.Dbid);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader objReader = objCmd.ExecuteReader())
{
if (objReader.HasRows)
{
while (objReader.Read())
{
IsRecordExist = true;
}
}
else
{
IsRecordExist = false;
}
}
}
catch (ApplicationException ex)
{
throw new Exception(ex.Message);
}
}
return(IsRecordExist);
}
/// <summary>
/// This method Checks whether Current City already exists in Database or not.
/// </summary>
/// <param name="objCity">Object Containing New Data Values.</param>
/// <returns>Boolean value True if Current Record already exists
/// otherwise returns False indicating current Record Does not exist.</returns>
public static bool IsCityExist(City objCity)
{
bool IsRecordExist = false;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT DBID FROM CITYMASTER " +
" WHERE City = @mCITY " +
" AND DBID <> @dbID ";
objCmd.Parameters.AddWithValue("@mCITY", objCity.mCity);
objCmd.Parameters.AddWithValue("@dbID", objCity.DBID);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader objReader = objCmd.ExecuteReader())
{
if (objReader.HasRows)
{
while (objReader.Read())
{
IsRecordExist = true;
}
}
else
{
IsRecordExist = false;
}
}
}
catch (ApplicationException ex)
{
throw new Exception(ex.Message);
}
}
return(IsRecordExist);
}
/// <summary>
/// This method provides List of Vehicle available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Vehicle Objects.</returns>
public static VehicleList GetList(string strWhere, bool blnIsActive)
{
VehicleList objList = null;
string strSql = "Select * from VEHICLEMASTER " +
" WHERE ISACTIVE = @IsActive";
if (strWhere != string.Empty)
{
strSql = strSql + " AND " + strWhere;
}
strSql += " ORDER BY VEHICLENO";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
objCmd.Parameters.AddWithValue("@IsActive", blnIsActive);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new VehicleList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
/// <summary>
/// This method provides List of Employees available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of Employee Objects.</returns>
public static EmployeeList GetList(string strWhere)
{
EmployeeList objList = null;
string strSql = "SELECT A.*, B.DEPTNAME " +
" FROM EmpMast A, DEPTMAST B " +
" WHERE A.DEPT = B.DBID ";//(+)
if (strWhere != string.Empty)
{
strSql = strSql + " AND " + strWhere;
}
strSql += " ORDER BY DEPTNAME, INITIALS";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new EmployeeList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
public static bool Save(UserRights objUserRights)
{
int result = 0;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
string strSaveQry = "";
//if (objList.IsNew)
//{
strSaveQry = "INSERT INTO USERRIGHTS (USERID, MENUID, FORMNAME) " +
" VALUES (@UserID, @MenuID, @FormName) ";
//}
//else
//{
//strSaveQry = "UPDATE USERRIGHTS " +
// " SET USERID = @UserID, MENUID = @MenuID, FORMNAME = @FormName " +
// " WHERE DBID = @dbId";
//}
try
{
SqlCommand objCmd = Conn.CreateCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSaveQry;
objCmd.Parameters.AddWithValue("@UserID", objUserRights.UserID);
objCmd.Parameters.AddWithValue("@MenuID", objUserRights.MenuID);
objCmd.Parameters.AddWithValue("@FormName", objUserRights.FormName);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result > 0);
}
/// <summary>
/// This method provides List of Cities available in Database.
/// </summary>
/// <param name="strWhere">Specifies condition for retrieving records.</param>
/// <returns>Collection of City Objects.</returns>
public static CityList GetList(string strWhere)
{
CityList objList = null;
string strSql = "Select * from CITYMASTER ";
if (strWhere != string.Empty)
{
strSql = strSql + " WHERE " + strWhere;
}
strSql += " ORDER BY CITY";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strSql;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (SqlDataReader oReader = objCmd.ExecuteReader())
{
if (oReader.HasRows)
{
objList = new CityList();
while (oReader.Read())
{
objList.Add(FillDataRecord(oReader));
}
}
oReader.Close();
oReader.Dispose();
}
}
}
return(objList);
}
public static Int32 CheckLogin(string strUserName, string strPwd)
{
string strQry;
Int32 UserID = 0;
try
{
strQry = "SELECT DBID" +
" FROM USERPROFILE " +
" WHERE ISACTIVE = 1 " +
" AND LOGINNAME = @Login " +
" AND PASSWORD = @Pwd ";
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strQry;
objCmd.Parameters.AddWithValue("@Login", strUserName);
objCmd.Parameters.AddWithValue("@Pwd", strPwd);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
object objTemp = objCmd.ExecuteScalar();
if (objTemp != null || objTemp != DBNull.Value)
{
UserID = Convert.ToInt32(objTemp);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(UserID);
}
/// <summary>
/// This method retrieves "Appointment" Record, which is retrieved from Database.
/// </summary>
/// <param name="dbid">Unique ID value based on which Record will be fetched from Database.</param>
/// <returns>Object "Appointment" containing Data Values.</returns>
public static Appointment GetItem(int dbid)
{
Appointment objAppoint = null;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
try
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT AP.*, EMP.INITIALS, DEPT.DEPTNAME " +
" FROM APPOINTMENTMASTER AP, EMPMAST EMP, DEPTMAST DEPT " +
" WHERE AP.DBID = @DBID " +
" AND EMP.DBID = AP.EMPLOYEEID " +
" AND DEPT.DBID = EMP.DEPT ";
objCmd.Parameters.AddWithValue("@DBID", dbid);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader oReader = objCmd.ExecuteReader();
if (oReader.Read())
{
objAppoint = FillDataRecord(oReader);
objAppoint.IsNew = false;
}
oReader.Close();
oReader.Dispose();
}
catch
{
throw;
}
}
}
return(objAppoint);
}
/// <summary>
/// This Method Deletes the record from Database based on ID Specified.
/// </summary>
/// <param name="id">Unique ID value for Record.</param>
/// <returns>Boolean value True if record is Deleted successfully
/// otherwise returns False indicating record is not Deleted.</returns>
public static bool Delete(long id)
{
int result = 0;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
SqlCommand ObjDelCmd = Conn.CreateCommand();
ObjDelCmd.CommandType = CommandType.Text;
ObjDelCmd.CommandText = "DELETE FROM VISITORMASTER WHERE DBID = @dbID";
ObjDelCmd.Parameters.AddWithValue("@dbID", id);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
result = ObjDelCmd.ExecuteNonQuery();
ObjDelCmd.Dispose();
}
return(result > 0);
}
/// <summary>
/// This method retrieves "Employee" Record, from Database.
/// </summary>
/// <param name="dbid">Unique ID value based on which Record will be fetched.</param>
/// <returns>Object "Employee" containing Data Values.</returns>
public static Employee GetItem(long dbid)
{
Employee objEmp = null;
using (SqlConnection Conn = new SqlConnection(General.GetSQLConnectionString()))
{
using (SqlCommand objCmd = new SqlCommand())
{
try
{
objCmd.Connection = Conn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT a.*, b.DeptName " +
" FROM EMPMast a, DEPTMAST b" +
" WHERE a.dept = b.dbid " +
" and a.DBID = @mDBID";
objCmd.Parameters.AddWithValue("@mDBID", dbid);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataReader oReader = objCmd.ExecuteReader();
if (oReader.Read())
{
objEmp = FillDataRecord(oReader);
objEmp.IsNew = false;
}
oReader.Close();
oReader.Dispose();
}
catch
{
throw;
}
}
}
return(objEmp);
}