private int GetMaxLineNr()
{
int MaxID = 1;
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetMaxID = sqlClient.Query("SELECT MAX(ID) FROM LINE_SERVICE_DESCRIPTION", "LSD");
if (dataSetMaxID != null && dataSetMaxID.Tables.Count > 0 && dataSetMaxID.Tables[0].Rows.Count > 0)
{
if (dataSetMaxID.Tables[0].Rows[0][0].ToString() != "")
{
MaxID = Convert.ToInt16(dataSetMaxID.Tables[0].Rows[0][0].ToString()) + 1;
}
}
Cursor.Current = Cursors.Default;
return(MaxID);
}
private void GetLineNrData()
{
Cursor.Current = Cursors.WaitCursor;
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetLineNr = sqlClient.Query("SELECT * FROM LINE_SERVICE_DESCRIPTION", "LSD");
if (dataSetLineNr != null && dataSetLineNr.Tables.Count > 0 && dataSetLineNr.Tables[0].Rows.Count > 0)
{
dgViewLNR.DataSource = dataSetLineNr.Tables[0];
}
Cursor.Current = Cursors.Default;
}
private void GetPIDTagMappingData()
{
try
{
Cursor.Current = Cursors.WaitCursor;
dgTagMappingList.Columns.Clear();
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetLineNr = sqlClient.Query("SELECT * FROM PID_TAG_MAPPING ORDER BY ID", "DF");
if (dataSetLineNr != null && dataSetLineNr.Tables.Count > 0 && dataSetLineNr.Tables[0].Rows.Count > 0)
{
dgTagMappingList.DataSource = dataSetLineNr.Tables[0];
}
dgTagMappingList.Columns[0].Width = 100;
dgTagMappingList.Columns[1].Width = 250;
dgTagMappingList.Columns[2].Width = 250;
Cursor.Current = Cursors.Default;
}
catch
{
Cursor.Current = Cursors.Default;
}
}
private bool ValidateLogin(string uName, string pwd)
{
string test = CConf.GetString("DBConnectionString");
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetUserID = sqlClient.Query("SELECT FirstName,LastName,Role FROM Users where UserName ='******' and Password ='******'", "DF");
if (dataSetUserID != null && dataSetUserID.Tables.Count > 0 && dataSetUserID.Tables[0].Rows.Count > 0)
{
Globals.UserFirstName = dataSetUserID.Tables[0].Rows[0][0].ToString();
Globals.UserLastName = dataSetUserID.Tables[0].Rows[0][1].ToString();
Globals.UserID = uName;
Globals.Role = dataSetUserID.Tables[0].Rows[0][2].ToString();
return(true);
}
else
{
return(false);
}
}
private bool CheckUserNameExists(string uName)
{
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetUserID = sqlClient.Query("SELECT ID FROM Users where UserName ='******'", "DF");
if (dataSetUserID != null && dataSetUserID.Tables.Count > 0 && dataSetUserID.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void GetLegendData()
{
Cursor.Current = Cursors.WaitCursor;
dgDiscFilterList.Columns.Clear();
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetLineNr = sqlClient.Query("SELECT * FROM LEGEND WHERE LegendType ='" + cboDiscipline.Text + "'", "DF");
if (dataSetLineNr != null && dataSetLineNr.Tables.Count > 0 && dataSetLineNr.Tables[0].Rows.Count > 0)
{
dgDiscFilterList.DataSource = dataSetLineNr.Tables[0];
}
Cursor.Current = Cursors.Default;
}
private int GetProjectID(string strpName)
{
int PID = 1;
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProjectID = sqlClient.Query("SELECT ProjectID from PROJECT where ProjectName ='" + strpName + "'", "DF");
if (dataSetProjectID != null && dataSetProjectID.Tables.Count > 0 && dataSetProjectID.Tables[0].Rows.Count > 0)
{
if (dataSetProjectID.Tables[0].Rows[0][0].ToString() != "")
{
PID = Convert.ToInt16(dataSetProjectID.Tables[0].Rows[0][0].ToString());
}
}
Cursor.Current = Cursors.Default;
return(PID);
}
private string GetProjectName(string PID)
{
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProjectID = sqlClient.Query("SELECT ProjectName from PROJECT where ProjectID ='" + PID + "'", "DF");
if (dataSetProjectID != null && dataSetProjectID.Tables.Count > 0 && dataSetProjectID.Tables[0].Rows.Count > 0)
{
if (dataSetProjectID.Tables[0].Rows[0][0].ToString() != "")
{
return(dataSetProjectID.Tables[0].Rows[0][0].ToString());
}
}
Cursor.Current = Cursors.Default;
return(null);
}
private void GetUserDetails(string uName)
{
ClearTextBoxes(this);
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProj = sqlClient.Query("Select * FROM Users where UserName ='******'", "USER");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
txtFirstName.Text = dataSetProj.Tables[0].Rows[0][1].ToString().Trim();
txtLastName.Text = dataSetProj.Tables[0].Rows[0][2].ToString().Trim();
txtUserID.Text = dataSetProj.Tables[0].Rows[0][3].ToString().Trim();
txtPassword.Enabled = false;
if (dataSetProj.Tables[0].Rows[0][5].ToString().Trim() == "Admin")
{
optAdmin.Checked = true;
}
else
{
optEngineer.Checked = true;
}
if (dataSetProj.Tables[0].Rows[0][6].ToString().Trim() == "Active")
{
optActive.Checked = true;
}
else
{
optInactive.Checked = true;
}
}
}
private void GetUserProjectDetails(string uName)
{
lbAssignedProj.Items.Clear();
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProj = sqlClient.Query("Select * FROM USER_PROJECTS where UserID ='" + uName + "'", "USER");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dataSetProj.Tables[0].Rows.Count; i++)
{
lbAssignedProj.Items.Add(GetProjectName(dataSetProj.Tables[0].Rows[i][2].ToString()));
}
}
}
private void PopulateUnAssignedProjectList(string UID)
{
lbNewProject.Items.Clear();
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
//DataSet dataSetProj = sqlClient.Query("SELECT PROJECT.*FROM((SELECT Projectid FROM PROJECT) EXCEPT(SELECT ProjectID FROM USER_PROJECTS where UserID = '" + UID + "' and Status = 'Inactive')) t1_not_in_t2 JOIN PROJECT ON t1_not_in_t2.ProjectID = PROJECT.ProjectID", "PROJECT");
DataSet dataSetProj = sqlClient.Query("SELECT PROJECT.*FROM((SELECT Projectid FROM PROJECT) EXCEPT(SELECT ProjectID FROM USER_PROJECTS where UserID = '" + UID + "')) t1_not_in_t2 JOIN PROJECT ON t1_not_in_t2.ProjectID = PROJECT.ProjectID", "PROJECT");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dataSetProj.Tables[0].Rows.Count; i++)
{
lbNewProject.Items.Add(dataSetProj.Tables[0].Rows[i][1].ToString());
}
}
}
private void PopulateUsersList(int parentId, string pName)
{
lvUser.Items.Clear();
ListViewItem item;
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProj = sqlClient.Query("Select FirstName, LastName, UserName FROM Users where FirstName like '%" + txtSearch.Text + "%' or LastName like '%" + txtSearch.Text + "%' order by ID", "USERS");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dataSetProj.Tables[0].Rows.Count; i++)
{
item = lvUser.Items.Add(dataSetProj.Tables[0].Rows[i][0].ToString() + " " + dataSetProj.Tables[0].Rows[i][1].ToString(), 0);
item.ToolTipText = String.Format("UserName: {0}", dataSetProj.Tables[0].Rows[i][2].ToString());
item.Tag = dataSetProj.Tables[0].Rows[i][2].ToString();
}
}
}
private void GetVendorListData()
{
Cursor.Current = Cursors.WaitCursor;
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetLineNr = sqlClient.Query("SELECT * FROM VENDOR_LIST", "VL");
if (dataSetLineNr != null && dataSetLineNr.Tables.Count > 0 && dataSetLineNr.Tables[0].Rows.Count > 0)
{
dgViewVL.DataSource = dataSetLineNr.Tables[0];
}
Cursor.Current = Cursors.Default;
}
private void GetProjectDetails(string pName)
{
ClearTextBoxes(this);
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProj = sqlClient.Query("Select * FROM Project where ProjectName ='" + pName + "'", "PROJECT");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
txtPID.Text = dataSetProj.Tables[0].Rows[0][0].ToString().Trim();
txtPName.Text = dataSetProj.Tables[0].Rows[0][1].ToString().Trim();
txtPDetails.Text = dataSetProj.Tables[0].Rows[0][2].ToString().Trim();
txtSDetails.Text = dataSetProj.Tables[0].Rows[0][3].ToString().Trim();
}
}
private void PopulateProjectList()
{
lvProj.Items.Clear();
sqlClient = new WatcherSqlClient(CConf.GetString("DBHost"), CConf.GetString("DBName"), CConf.GetBoolean("DBTrustedConnection"), CConf.GetString("DBLogin"), CConf.GetString("DBPassword"), CConf.GetString("DBConnectionString"));
sqlClient.OpenConnection();
DataSet dataSetProj = sqlClient.Query("Select ProjectName FROM Project where ProjectName like '%" + txtSearch.Text + "%' order by ProjectID", "PROJECT");
if (dataSetProj != null && dataSetProj.Tables.Count > 0 && dataSetProj.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dataSetProj.Tables[0].Rows.Count; i++)
{
lvProj.Items.Add(dataSetProj.Tables[0].Rows[i][0].ToString(), 0);
}
}
//var colored = false;
//foreach (ListViewItem item in lvProj.Items)
//{
// item.BackColor = colored ? Color.LightBlue : Color.White;
// colored = !colored;
//}
}