internal DataSet Fetch_LoginDtl(string UserCode, string Password, ref string sReturnMsg)
{
IDBManager dbManager = CommonConnection.Connectionstring();
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(3);
dbManager.AddParameters(0, "LoginUserCode", UserCode, ParameterDirection.Input);
dbManager.AddParameters(1, "UserPassword", Password, ParameterDirection.Input);
dbManager.AddParameters(2, "@RetMsg", string.Empty, ParameterDirection.Output, 100);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Fetch_LoginDtl");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Fetch_LoginDtl");
}
if (dbManager.Parameters[2] != null)
{
sReturnMsg = dbManager.Parameters[2].Value.ToString();
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal DataSet Fetch_UserInfo(string Mode, string usercode)
{
IDBManager dbManager = CommonConnection.Connectionstring();
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(2);
dbManager.AddParameters(0, "Mode", Mode, ParameterDirection.Input);
dbManager.AddParameters(1, "UserCode", usercode, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Fetch_Users");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Fetch_Users");
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal int SendResetPswdlink(string UserCode, string UserEmail, string EmlTmpl, string token, DateTimeOffset tokenDt)
{
IDBManager dbManager = CommonConnection.Connectionstring();
int MsgId = 0;
DataSet ds = new DataSet();
try
{
const string dtFormat = "yyyy-MM-dd HH:mm:ss.fffffff zzz";
string dtString2 = tokenDt.ToString(dtFormat, CultureInfo.InvariantCulture);
dbManager.Open();
dbManager.CreateParameters(5);
dbManager.AddParameters(0, "UserCode", UserCode, ParameterDirection.Input);
dbManager.AddParameters(1, "UserEmail", UserEmail, ParameterDirection.Input);
dbManager.AddParameters(2, "EmlTmpl", EmlTmpl, ParameterDirection.Input);
dbManager.AddParameters(3, "token", token, ParameterDirection.Input);
dbManager.AddParameters(4, "tokenDt", dtString2, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "SendResetPswdlink");
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "SendResetPswdlink");
MsgId = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
return(MsgId);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal int Save_UserPassword(string UserEmail, string userCode, string UsrPwd, string strToken)
{
IDBManager dbManager = CommonConnection.Connectionstring();
int results = 0;
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(4);
dbManager.AddParameters(0, "UserEmail", UserEmail, ParameterDirection.Input);
dbManager.AddParameters(1, "userCode", userCode, ParameterDirection.Input);
dbManager.AddParameters(2, "UsrPwd", UsrPwd, ParameterDirection.Input);
dbManager.AddParameters(3, "strToken", strToken, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Save_userPassword");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Save_userPassword");
results = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
}
return(results);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal int Save_NewApps(int appid, string AppName, string AppURL, string Status, string mode)
{
IDBManager dbManager = CommonConnection.Connectionstring();
int results = 0;
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(5);
dbManager.AddParameters(0, "AppId", appid, ParameterDirection.Input);
dbManager.AddParameters(1, "AppName", AppName, ParameterDirection.Input);
dbManager.AddParameters(2, "AppURL", AppURL, ParameterDirection.Input);
dbManager.AddParameters(3, "Status", Status, ParameterDirection.Input);
dbManager.AddParameters(4, "mode", mode, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Save_Apps");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Save_Apps");
results = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
}
return(results);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal DataSet Open_LoginInfo(string UserCode, string Password, string param)
{
IDBManager dbManager = CommonConnection.Connectionstring();
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(3);
dbManager.AddParameters(0, "LoginUserCode", UserCode, ParameterDirection.Input);
dbManager.AddParameters(1, "UserPassword", Password, ParameterDirection.Input);
dbManager.AddParameters(2, "Param", param, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Fetch_LoginInfo");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Fetch_LoginInfo");
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal string Validate(string param, string Mode)
{
IDBManager dbManager = CommonConnection.Connectionstring();
string results = "";
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(2);
dbManager.AddParameters(0, "params", param, ParameterDirection.Input);
dbManager.AddParameters(1, "MODE", Mode, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Validations");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Validations");
results = Convert.ToString(ds.Tables[0].Rows[0][0]);
}
return(results);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal string Save_DocUpload(string sDocType, byte[] DocImgThumb, string Param1, string Param2,
string Param3, string Param4, string Param5, string sUserCode, string sFileName)
{
IDBManager dbManager = CommonConnection.Connectionstring();
try
{
string sReqNo = string.Empty;
DataSet ds = new DataSet();
dbManager.Open();
dbManager.CreateParameters(10);
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
dbManager.AddParameters(0, "@DocType", sDocType, ParameterDirection.Input);
if (DocImgThumb != null)
{
dbManager.AddParameters(1, "@DocImg", DocImgThumb, ParameterDirection.Input);
}
else
{
dbManager.AddParameters(1, "@DocImg", DBNull.Value, ParameterDirection.Input);
}
dbManager.AddParameters(2, "@UserCode", sUserCode, ParameterDirection.Input);
dbManager.AddParameters(3, "@FileName", sFileName, ParameterDirection.Input);
dbManager.AddParameters(4, "@Param1", Param1, ParameterDirection.Input);
dbManager.AddParameters(5, "@Param2", Param2, ParameterDirection.Input);
dbManager.AddParameters(6, "@Param3", Param3, ParameterDirection.Input);
dbManager.AddParameters(7, "@Param4", Param4, ParameterDirection.Input);
dbManager.AddParameters(8, "@Param5", Param5, ParameterDirection.Input);
dbManager.AddParameters(9, "@ReqNo", string.Empty, ParameterDirection.Output, 50);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "DataImport.GTK_Upld");
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "DataImport.GTK_Upld");
}
else
{
}
sReqNo = dbManager.Parameters[9].Value.ToString();
return(sReqNo);
}
catch (Exception ex)
{
throw ex;
//return null;
}
finally
{
dbManager.Dispose();
}
}
internal DataSet GetFilterConditions()
{
IDBManager dbManager = CommonConnection.Connectionstring();
try
{
dbManager.Open();
dbManager.CreateParameters(2);
DataSet dsFilterCond = new DataSet();
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
if (SessionObjects.obj.GlobalPropertiesObject.FteCode != null && SessionObjects.obj.GlobalPropertiesObject.FteCode != "")
{
dbManager.AddParameters(0, "@FTECODE", SessionObjects.obj.GlobalPropertiesObject.FteCode, ParameterDirection.Input, 10);
}
else
{
dbManager.AddParameters(0, "@FTECODE", DBNull.Value, ParameterDirection.Input, 10);
}
if (SessionObjects.obj.GlobalPropertiesObject.UserCode != null && SessionObjects.obj.GlobalPropertiesObject.UserCode != "")
{
dbManager.AddParameters(1, "@UserCode", SessionObjects.obj.GlobalPropertiesObject.UserCode, ParameterDirection.Input, 100);
}
else
{
dbManager.AddParameters(1, "@UserCode", DBNull.Value, ParameterDirection.Input, 100);
}
dsFilterCond = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "GetFilterConditions");
}
return(dsFilterCond);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
//This method will return the converted value by comparing the UOMs from DB.
public static double UomCnvVal(string FrUOM, string ToUOM, double FrQty, double ValFct, double WT, string WTUOM)
{
IDBManager dbManager = CommonConnection.Connectionstring();
try
{
double sRetVal = default(double);
dbManager.Open();
dbManager.CreateParameters(7);
DataSet dsVal = new DataSet();
//Asign values to properties
UomConversions cls = new UomConversions(FrUOM, ToUOM, FrQty, ValFct, WT, WTUOM);
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
dbManager.AddParameters(0, "@FrUOM", UomConversions.FrUOM, ParameterDirection.Input);
dbManager.AddParameters(1, "@ToUOM", UomConversions.ToUOM, ParameterDirection.Input);
dbManager.AddParameters(2, "@FrQty", UomConversions.FrQty, ParameterDirection.Input);
dbManager.AddParameters(3, "@ValFct", UomConversions.ValFct, ParameterDirection.Input);
dbManager.AddParameters(4, "@WT", UomConversions.WT, ParameterDirection.Input);
dbManager.AddParameters(5, "@WTUOM", UomConversions.WTUOM, ParameterDirection.Input);
dbManager.AddParameters(6, "@CnvQty", 0, ParameterDirection.Output);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "ObjUom_Convert");
dsVal = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "ObjUom_Convert");
}
if (dbManager.Parameters[6].Value.ToString() != "")
{
sRetVal = double.Parse(dbManager.Parameters[6].Value.ToString());
}
return(sRetVal);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal int Save_UserInfo(UserList user)
{
IDBManager dbManager = CommonConnection.Connectionstring();
int results = 0;
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(8);
dbManager.AddParameters(0, "UserFirstName", user.UserFirstName, ParameterDirection.Input);
dbManager.AddParameters(1, "UserMidName", user.UserMidName, ParameterDirection.Input);
dbManager.AddParameters(2, "UserLastName", user.UserLastName, ParameterDirection.Input);
dbManager.AddParameters(3, "Email", user.UserEmail, ParameterDirection.Input);
dbManager.AddParameters(4, "UserApps", user.UserApps, ParameterDirection.Input);
dbManager.AddParameters(5, "Mode", user.Mode, ParameterDirection.Input);
dbManager.AddParameters(6, "userCode", user.userCode, ParameterDirection.Input);
dbManager.AddParameters(7, "eParam", user.EParm, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Save_Users");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Save_Users");
results = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
}
return(results);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbManager.Dispose();
}
}
internal int Save_NewAppsx(string xml, string Mode)
{
IDBManager dbManager = CommonConnection.Connectionstring();
int results = 0;
DataSet ds = new DataSet();
try
{
dbManager.Open();
dbManager.CreateParameters(2);
dbManager.AddParameters(0, "xml", xml, ParameterDirection.Input);
dbManager.AddParameters(1, "Mode", Mode, ParameterDirection.Input);
string spCall = Helper.Ins.GetSPCall(dbManager.Parameters, "Save_AppsX");
if (ConfigurationSettings.AppSettings["DataB"] == "SQL")
{
ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "Save_AppsX");
results = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
}
}
catch (Exception ex)
{
}
return(results);
}
public DataSet FetchDataSet(string criteria)
{
DataSet ds = new DataSet();
string qryString = "SELECT * FROM viewFALIST ORDER BY FA";
_user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
_dbmgr = new DBManager(_user.plantDBStr);
_dbmgr.ConnectionString = _user.plantDBStr;
try
{
_dbmgr.Open();
ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_dbmgr.Dispose();
}
return ds;
}
protected void btnlogin_Click(object sender, EventArgs e)
{
int iRecordsFetched = 0;
int iTempGroupID = 0;
m_db = new DBManager(DataProvider.Oracle);
if (m_db == null) {
Response.Redirect("Error_page.aspx?ERROR=UNABLE_CREATE_DBOBJ");
}
///''''''''''''If user clicked on login button check for username,password and selecttype are filled or not if they are not empty then proceed
if (txtuser.Value.Length != 0 & txtpwd.Value.Length != 0) {
m_db.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
m_db.Open();
if (m_db.Connection == null) {
///''''''' If Failed to Eastablish database Connection Show Error Page to the User
// strstacktrace = HttpUtility.UrlEncode(m_db.Exception_str)
strstacktrace = "";
Response.Redirect("Error_page.aspx?ERROR=DATABASE_CONNECTION_FAILED&MESSAGE=" + strstacktrace);
}
///''''''''''' Form the query to get the login userdetails from tbl_usermaster
// strQry = "select tbl_usermaster.userid,tbl_usermaster.usertypeflag,tbl_usermaster.webaccountflag,usersmemberslink.memberid,upper(username) as username,grp.groupid,grp.groupname from tbl_usermaster inner join groupe as grp on tbl_usermaster.groupid=grp.groupid inner join usersmemberslink on tbl_usermaster.userid=usersmemberslink.userid where username='******' and password='******' and tbl_usermaster.activated=1 and tbl_usermaster.webaccountflag=1"
//strQry = "select usermaster.userid,usermaster.usertypeflag,usermaster.webaccountflag,upper(username) as username,grp.groupid,grp.groupname from usermaster inner join groupe as grp on usermaster.groupid=grp.groupid where username='******' and password='******' and usermaster.activated=1 and usermaster.webaccountflag=1";
//strQry = "select usermaster.userid,upper(username) as username from usermaster ";// where username='******' and password='******'";
strQry = string.Format("select users.userid,upper(username) as username,groupid from users where username='******' and userpassword='******'", txtuser.Value, txtpwd.Value);
objDataset = m_db.ExecuteDataSet(CommandType.Text, strQry);
if (objDataset == null) {
m_db.Close();
m_db.Dispose();
// strstacktrace = HttpUtility.UrlEncode(m_db.Exception_str)
Response.Redirect("Error_page.aspx?ERROR=RECORDSET_FETCH_FAILED&MESSAGE=" + strstacktrace);
}
///'''''''''''''' get the no of records that exsist for the framed query
iRecordsFetched = int.Parse(objDataset.Tables[0].Rows.Count.ToString());
///''''''''''''' if no records present give msg to the user alerting him that he is invalid user
if (iRecordsFetched == 0) {
txtuser.Focus();
} else {
///'''''''''''' if records present get all the userdetails and store them inside a session
CUSERDetails objUserDetails = new CUSERDetails();
objUserDetails.iUserID = Convert.ToInt32(objDataset.Tables[0].Rows[0]["userid"].ToString());
objUserDetails.strUserName = objDataset.Tables[0].Rows[0]["username"].ToString();
objUserDetails.iGroupID = Convert.ToInt32(objDataset.Tables[0].Rows[0]["groupid"]);
//objUserDetails.strGroupName = objDataset.Tables[0].Rows[0]["groupname"].ToString();
objUserDetails.iMemberID = "1";
//objDataset.Tables(0).Rows(0)("memberid").ToString()
//objUserDetails.iRooterFlag = Convert.ToInt32(objDataset.Tables[0].Rows[0]["usertypeflag"]);
//objUserDetails.iWebAccountFlag = Convert.ToInt32(objDataset.Tables[0].Rows[0]["webaccountflag"]);
//objUserDetails.iUserID = CType(objDataset.Tables(0).Rows(0)("userid").ToString(), Integer)
//objUserDetails.strUserName = objDataset.Tables(0).Rows(0)("username").ToString()
//objUserDetails.iGroupID = 4 ' CType(objDataset.Tables(0).Rows(0)("groupid"), Integer)
//objUserDetails.strGroupName = objDataset.Tables(0).Rows(0)("groupname").ToString()
//objUserDetails.iMemberID = 1 'objDataset.Tables(0).Rows(0)("memberid").ToString()
//objUserDetails.iRooterFlag = 1 'CType(objDataset.Tables(0).Rows(0)("usertypeflag"), Integer)
//objUserDetails.iWebAccountFlag = 1 'CType(objDataset.Tables(0).Rows(0)("webaccountflag"), Integer)
Session["sesuUserDetails"] = objUserDetails;
objDataset.Dispose();
// Free dataset
m_db.Close();
// Free SQL Connection
iTempGroupID = objUserDetails.iGroupID;
objUserDetails = null;
m_db = null;
//Response.Redirect("~/frmNewConnection.aspx");
Response.Redirect("SBMUpload.aspx");
}
}
}
public DataSet FetchDataSet(string criteria)
{
_user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
DataSet ds = new DataSet();
string qryString;
switch (_user.plant)
{
case "BLN":
case "TFAC":
qryString = "SELECT * FROM viewROUTELIST_FR ORDER BY NODE";
break;
default:
qryString = "SELECT * FROM viewROUTELIST ORDER BY NODE";
break;
}
_user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
_dbmgr = new DBManager(_user.plantDBStr);
_dbmgr.ConnectionString = _user.plantDBStr;
try
{
_dbmgr.Open();
ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_dbmgr.Dispose();
}
return ds;
}
/// <summary>
/// Fetches Dataset
/// </summary>
/// <param name="criteria">Prep_Only, Prep and Chkd, Show All</param>
/// <returns>Dataset</returns>
public DataSet FetchDataSet(string criteria)
{
DataSet ds = new DataSet();
string qryString;
switch (criteria)
{
case "Prep Only":
qryString = "SELECT * FROM viewCOMPLIST_LITE WHERE PREP_DATE IS NOT NULL AND CHKD_DATE IS NULL ORDER BY COMP";
break;
case "Prep and Chkd":
qryString = "SELECT * FROM viewCOMPLIST_LITE WHERE PREP_DATE IS NOT NULL AND CHKD_DATE IS NOT NULL ORDER BY COMP";
break;
case "Show All":
default:
qryString = "SELECT * FROM viewCOMPLIST_LITE ORDER BY COMP";
break;
}
// create database connection
_user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
_dbmgr = new DBManager(_user.plantDBStr);
_dbmgr.ConnectionString = _user.plantDBStr;
try
{
_dbmgr.Open();
ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_dbmgr.Dispose();
}
return ds;
}
