private void AddUpdateData(string tableName, string columnName, TextBox tbName, string message, string id)
{
if (dataGridView_ListData.SelectedRows.Count <= 0)
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO " + tableName + " VALUES (@1, @2)", SQLCon.sqlConnection);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", tbName.Text.ToUpper());
SQLCon.sqlCommand.Parameters.AddWithValue("@2", 1);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("New " + message + " Added", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int tempId = int.Parse(dataGridView_ListData.SelectedRows[0].Cells[0].Value.ToString());
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("UPDATE " + tableName + " SET " + columnName + " = @1 WHERE " + id + " = @0", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@0", tempId);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", tbName.Text.ToUpper());
SQLCon.sqlCommand.ExecuteNonQuery();
MetroFramework.MetroMessageBox.Show(this, message + " Successfully Updated", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (tableName == "ServiceProvidedBies")
{
string tempName = dataGridView_ListData.SelectedRows[0].Cells[1].Value.ToString();
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("UPDATE ServiceRequestInfoes SET Techinicians = @1 WHERE Techinicians = @0", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@0", tempName);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", tbName.Text.ToUpper());
SQLCon.sqlCommand.ExecuteNonQuery();
}
}
ClearText();
}
private void SaveTypeOfService()
{
try
{
if (cb_TypeOfService.Text != "")
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO TypeOfServices VALUES(@1)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@1", cb_TypeOfService.Text);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("New Services added successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Please input Type of Service!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void bt_Save_Click(object sender, EventArgs e)
{
if (!(String.IsNullOrWhiteSpace(cb_SystemTitle.Text)))
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO SystemDevelopments VALUES(@USER_ID,@DateAccomplish,@Accomplishment,@Remarks,@WeeklyMonthly,@SystemTitle)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@USER_ID", WelcomeForm.AccountID);
SQLCon.sqlCommand.Parameters.AddWithValue("@Remarks", tb_Memo.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@Accomplishment", tb_Accomplishment.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@SystemTitle", cb_SystemTitle.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateAccomplish", dateTimePicker_Date.Value.Date);
SQLCon.sqlCommand.Parameters.AddWithValue("@WeeklyMonthly", 1);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("Save Successfully");
LoadWeeklyRpt();
}
else
{
MessageBox.Show("Don't leave blank System Title");
}
}
private string GetOthers(string count, string month, string year)
{
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT * from ServiceRequestInfoes where NOT TypeOfServiceProvided like '%DESKTOP%' and
NOT TypeOfServiceProvided like '%LAPTOP%' and
NOT TypeOfServiceProvided like '%PRINTER%' and
NOT TypeOfServiceProvided like '%NETWORK%' and
MONTH(DateAccomplished) = @2 and YEAR(DateAccomplished) = @3 ", SQLCon.sqlConnection);
int a = (Convert.ToDateTime(month + " 01, 1900").Month);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@2", a);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@3", Int32.Parse(year));
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
int i = 0;
foreach (DataRow row in SQLCon.dataTable.Rows)
{
i++;
}
string number;
number = i.ToString();
return(number);
}
public void LoadTypeOfService()
{
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT * FROM TypeOfServices", SQLCon.sqlConnection);
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_TypeOfService.DataSource = SQLCon.dataTable;
cb_TypeOfService.ValueMember = "TS_ID";
cb_TypeOfService.DisplayMember = "TypeOfServiceProvided";
cb_TypeOfService.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_TypeOfService.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_TypeOfService.SelectedIndex = -1;
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT * FROM ServiceProvidedBies", SQLCon.sqlConnection);
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
checkedComboBoxEdit1.Properties.DataSource = SQLCon.dataTable;
checkedComboBoxEdit1.Properties.ValueMember = "SP_ID";
checkedComboBoxEdit1.Properties.DisplayMember = "spName";
//checkedComboBoxEdit1.Properties.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
//checkedComboBoxEdit1.Properties.AutoCompleteSource = AutoCompleteSource.ListItems;
//checkedComboBoxEdit1.Properties.SelectedIndex = -1;
}
private void SaveRemark()
{
try
{
if (cb_Remarks.Text != "")
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO RemarkInfoes VALUES(@1)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@1", cb_Remarks.Text);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("New Remark added successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadRemark();
}
else
{
MessageBox.Show("Please don't leave it blank!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void MainForm_Load(object sender, EventArgs e)
{
SQLCon.DbCon();
user_AddRequest1.LoadTypeOfService();
user_AddRequest1.LoadTechnician();
user_AddRequest1.LoadOfficeDepartment();
}
private void CheckifExist(string columnName, string tableName, string input, TextBox tb, CancelEventArgs e)
{
int exist = 0;
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("SELECT ISNULL(" + columnName + ",0) FROM " + tableName + " WHERE (" + columnName + "= @service)", SQLCon.sqlConnection);
SQLCon.sqlCommand.Parameters.AddWithValue("@service", tb.Text);
SQLCon.sqlDataReader = SQLCon.sqlCommand.ExecuteReader();
if (SQLCon.sqlDataReader.Read())
{
exist = 1;
}
if (exist > 0)
{
e.Cancel = true;
errorProvider.SetError(tb, input + " name already exist");
}
else if (tb.Text == "")
{
e.Cancel = true;
errorProvider.SetError(tb, "Please input a " + input + "!");
}
else
{
e.Cancel = false;
errorProvider.SetError(tb, null);
}
}
private void LoadRequestList()
{
try
{
SQLCon.DbCon();
//SQLCon.sqlDataApater = new SqlDataAdapter("SELECT TypeOfServiceProvided AS [Type Of Service Provided], RequestedBy AS [Requested By], " +
// "NameOfOficce AS [Office], DateRequested AS [Date Requested], DateAccomplished AS [Date Accomplished], ServiceProvidedBy AS [Service Provided By] " +
// "FROM ServiceRequestInfoes AS T1, TypeOfServices AS T2" +
//" ORDER BY SR_ID DESC", SQLCon.sqlConnection);
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT T2.TypeOfServiceProvided AS [Type Of Service Provided], T1.RequestedBy AS [Requested By], " +
"T3.OfficeDepartmentName AS [Office], T1.DateRequested AS [Date Requested], T1.DateAccomplished AS [Date Accomplished], T4.spName AS [Service Provided By], T1.Status " +
"FROM ServiceRequestInfoes AS T1, TypeOfServices AS T2, OfficeDepartments AS T3, ServiceProvidedBies AS T4" +
" WHERE T1.TS_ID=T2.TS_ID " +
"AND T1.OD_ID=T3.OD_ID " +
"AND T1.SP_ID=T4.SP_ID " +
"AND T1.Status=1", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
gridControl_RequestList.DataSource = SQLCon.dataTable;
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void Bt_SaveUser_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(tb_Username.Text))
{
MessageBox.Show("Plesae Fill up the required fields");
}
else
{
if (String.IsNullOrEmpty(tempId_UserList))
{
if (tb_Password.Text == tb_ConfirmPassword.Text)
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO Accounts VALUES(@Username,@Password,@FirstName,@LastName,@AccessLevel,@IsActive,@IsLogon,@MiddleName)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@Username", tb_Username.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@Password", tb_Password.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@FirstName", tb_FirstName.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@MiddleName", tb_MiddleName.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@LastName", tb_LastName.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@AccessLevel", cb_AccessLevel.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@IsActive", 1);
SQLCon.sqlCommand.Parameters.AddWithValue("@IsLogon", 1);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("User added Successfully");
LoadUserList();
}
else
{
MessageBox.Show("Password did not match");
tb_Password.Clear();
tb_ConfirmPassword.Clear();
}
}
else
{
using (SrisContext ctx = new SrisContext())
{
int tempId = Convert.ToInt32(tempId_UserList);
Account userAccount = ctx.Account.Where(x => x.USER_ID == tempId).FirstOrDefault();
userAccount.Username = tb_Username.Text;
userAccount.Password = tb_Password.Text;
userAccount.FirstName = tb_FirstName.Text;
userAccount.MiddleName = tb_MiddleName.Text;
userAccount.LastName = tb_LastName.Text;
userAccount.AccessLevel = cb_AccessLevel.Text;
userAccount.IsActive = checkBox_Status.Checked;
ctx.SaveChanges();
MessageBox.Show("User update Successfully");
LoadUserList();
tempId_UserList = null;
dataGridView_UserList.ClearSelection();
}
}
}
ClearText();
}
private void LoadWeeklyRpt()
{
dataGridView_ListWeek.Refresh();
try
{
SQLCon.DbCon();
if (radioButton_Weekly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
SD_ID,
SystemTitle AS [System Title],
Accomplishment AS [Accomplishments],
DateAccomplishment AS [Date Accomplished],
Remarks AS [Remarks]
FROM
SystemDevelopments
WHERE
WeeklyMonthly = 1 AND
SystemTitle = @SystemTitle
", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@SystemTitle", cb_SystemTitle.Text);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListWeek.DataSource = SQLCon.dataTable;
DesignTable();
}
else if (radioButtonMonthly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
SD_ID,
SystemTitle AS [System Title],
Accomplishment AS [Accomplishments],
concat(YEAR(DateAccomplishment) ,' ', DATENAME(month, DateAccomplishment)) AS [Date Accomplished],
Remarks AS [Remarks]
FROM
SystemDevelopments
WHERE
WeeklyMonthly = 0
ORDER BY
DateAccomplishment ASC
", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListWeek.DataSource = SQLCon.dataTable;
DesignTable();
}
}
catch (Exception ex)
{
MessageBox.Show(ex + "");
}
}
private void LoadWeeklyRpt()
{
try
{
SQLCon.DbCon();
if (radioButton_Weekly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
SD_ID,
SystemTitle AS [System Title],
Accomplishment AS [Accomplishments],
DateAccomplishment AS [Date Accomplished],
Remarks AS [Remarks]
FROM
SystemDevelopments
WHERE
USER_ID = @1 AND
WeeklyMonthly = 1
", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@1", Convert.ToInt32(WelcomeForm.AccountID));
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListWeek.DataSource = SQLCon.dataTable;
DesignTable();
}
else if (radioButtonMonthly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
SD_ID,
SystemTitle AS [System Title],
Accomplishment AS [Accomplishments],
concat(YEAR(DateAccomplishment) ,' ', DATENAME(month, DateAccomplishment)) AS [Date Accomplished],
Remarks AS [Remarks]
FROM
SystemDevelopments
WHERE
USER_ID = @1 AND
WeeklyMonthly = 0
ORDER BY
DateAccomplishment ASC
", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@1", Convert.ToInt32(WelcomeForm.AccountID));
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListWeek.DataSource = SQLCon.dataTable;
DesignTable();
}
}
catch (Exception)
{
}
}
private void LoadListSystemTitle(ComboBox cb)
{
try
{
SQLCon.DbCon();
if (radioButton_Weekly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(@"
SELECT DISTINCT
SystemTitle
FROM
SystemDevelopments
WHERE
DATALENGTH(SystemTitle) > 0 AND
USER_ID = @1
ORDER BY
SystemTitle
", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@1", Convert.ToInt32(WelcomeForm.AccountID));
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb.DataSource = SQLCon.dataTable;
cb.DisplayMember = "SystemTitle";
cb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb.AutoCompleteSource = AutoCompleteSource.ListItems;
cb.SelectedIndex = -1;
}
else if (radioButtonMonthly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(@"
SELECT DISTINCT
SystemTitle
FROM
SystemDevelopments
WHERE
DATALENGTH(SystemTitle) > 0 AND
USER_ID = @1
ORDER BY
SystemTitle
", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@1", Convert.ToInt32(WelcomeForm.AccountID));
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb.DataSource = SQLCon.dataTable;
cb.DisplayMember = "SystemTitle";
cb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb.AutoCompleteSource = AutoCompleteSource.ListItems;
cb.SelectedIndex = -1;
}
}
catch (Exception)
{
throw;
}
}
public void LoadRequestList()
{
try
{
SQLCon.DbCon();
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
T1.SR_ID,
T1.TypeOfServiceProvided AS [Type Of Service Provided],
T1.RequestedBy AS [Requested By],
T1.OfficeDepartmentName AS [Office],
T1.DateRequested AS [Date Requested],
T1.DateAccomplished AS [Date Accomplished],
RemarkDeatails AS [Remarks],
T1.Techinicians AS [Technicians]
FROM
ServiceRequestInfoes AS T1
WHERE
T1.Status = 1 AND
(
T1.RequestedBy LIKE @1 OR
T1.OfficeDepartmentName LIKE @2
) ORDER BY DateEntered Desc", SQLCon.sqlConnection);
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@1", "%" + tb_Search.Text + "%");
SQLCon.sqlDataApater.SelectCommand.Parameters.AddWithValue("@2", "%" + tb_Search.Text + "%");
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListOfRequest.DataSource = SQLCon.dataTable;
dataGridView_ListOfRequest.Columns["SR_ID"].Visible = false;
dataGridView_ListOfRequest.Columns["Date Accomplished"].Visible = false;
DataGridViewDesign();
int i = 0;
foreach (DataGridViewRow row in dataGridView_ListOfRequest.Rows)
{
i++;
}
label_Counter.Text = i.ToString();
}
catch (Exception e)
{
MessageBox.Show("" + e);
}
}
private void LoginValidation()
{
Cursor.Current = Cursors.WaitCursor;
SQLCon.DbCon();
SQLCon.sql = "SELECT USER_ID, Username, Password, FirstName, LastName , AccessLevel, IsActive FROM Accounts WHERE Username=@1 AND Password=@2";
SQLCon.sqlCommand = new SqlCommand(SQLCon.sql, SQLCon.sqlConnection);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", tb_Username.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@2", tb_Password.Text);
SQLCon.sqlDataReader = SQLCon.sqlCommand.ExecuteReader();
while (SQLCon.sqlDataReader.Read())
{
WelcomeForm.AccountID = Convert.ToInt32((SQLCon.sqlDataReader["USER_ID"].ToString()));
WelcomeForm.Username = (SQLCon.sqlDataReader["Username"].ToString());
WelcomeForm.Password = (SQLCon.sqlDataReader["Password"].ToString());
WelcomeForm.FirstName = (SQLCon.sqlDataReader["FirstName"].ToString());
WelcomeForm.LastName = (SQLCon.sqlDataReader["LastName"].ToString());
accountValidation = (SQLCon.sqlDataReader["IsActive"].ToString());
role = (SQLCon.sqlDataReader["AccessLevel"].ToString());
}
if (WelcomeForm.Username == tb_Username.Text && WelcomeForm.Password == tb_Password.Text && accountValidation == "True")
{
if (role == "ADMIN")
{
MainForm.accessLevel = "ADMIN";
}
else if (role == "TECHNICIAN")
{
MainForm.accessLevel = "TECHNICIAN";
}
else if (role == "PROGRAMMER")
{
MainForm.accessLevel = "PROGRAMMER";
}
this.Close();
WelcomeForm welcomeForm = new WelcomeForm();
welcomeForm.ShowDialog();
}
else if (accountValidation == "False" && WelcomeForm.Username == tb_Username.Text && WelcomeForm.Password == tb_Password.Text)
{
MessageBox.Show("Your account has been suspended. Please contact the Administrator");
tb_Password.Clear();
tb_Username.Clear();
}
else
{
MessageBox.Show("Incorrect username or password");
tb_Password.Clear();
tb_Username.Clear();
}
Cursor.Current = Cursors.Default;
}
private void LoadListSystemTitle()
{
try
{
SQLCon.DbCon();
if (radioButton_Weekly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(@"
SELECT DISTINCT
SystemTitle
FROM
SystemDevelopments
WHERE
DATALENGTH(SystemTitle) > 0
ORDER BY
SystemTitle
", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_SystemTitle.DataSource = SQLCon.dataTable;
cb_SystemTitle.DisplayMember = "SystemTitle";
cb_SystemTitle.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_SystemTitle.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_SystemTitle.SelectedIndex = -1;
}
else if (radioButtonMonthly.Checked == true)
{
SQLCon.sqlDataApater = new SqlDataAdapter(@"
SELECT DISTINCT
SystemTitle
FROM
SystemDevelopments
WHERE
DATALENGTH(SystemTitle) > 0
ORDER BY
SystemTitle
", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_SystemTitle.DataSource = SQLCon.dataTable;
cb_SystemTitle.ValueMember = "TT_ID";
cb_SystemTitle.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_SystemTitle.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_SystemTitle.SelectedIndex = -1;
}
}
catch (Exception)
{
}
}
private void LoadRequestList()
{
try
{
SQLCon.DbCon();
//SQLCon.sqlDataApater = new SqlDataAdapter("SELECT TypeOfServiceProvided AS [Type Of Service Provided], RequestedBy AS [Requested By], " +
// "NameOfOficce AS [Office], DateRequested AS [Date Requested], DateAccomplished AS [Date Accomplished], ServiceProvidedBy AS [Service Provided By] " +
// "FROM ServiceRequestInfoes AS T1, TypeOfServices AS T2" +
//" ORDER BY SR_ID DESC", SQLCon.sqlConnection);
//SQLCon.sqlDataApater = new SqlDataAdapter("SELECT T2.TypeOfServiceProvided AS [Type Of Service Provided], T1.RequestedBy AS [Requested By], " +
// "T3.OfficeDepartmentName AS [Office], T1.DateRequested AS [Date Requested], T1.TimeLeft AS [Time Left] ,T1.DateAccomplished AS [Date Accomplished], T4.spName AS [Service Provided By], Remars AS [Remarks] ,Status [Status:] " +
// "FROM ServiceRequestInfoes AS T1, TypeOfServices AS T2, OfficeDepartments AS T3, ServiceProvidedBies AS T4, RemarkInfoes AS T5" +
//" WHERE T1.TS_ID=T2.TS_ID AND T1.OD_ID=T3.OD_ID AND T1.SP_ID=T4.SP_ID AND T1.Remark_ID=T5.Remark_ID", SQLCon.sqlConnection);
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT DateRequested FROM ServiceRequestInfoes", SQLCon.sqlConnection);
SQLCon.dataSet = new DataSet();
//SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataSet, "ServiceRequestInfoes");
DataTable dt = new DataTable();
dataGridView1.Columns.Add("newColumnName", "Column Name in Text");
dataGridView1.Rows.Add("asdas");
dt.Columns.Add(new DataColumn("colBestBefore", typeof(DateTime)));
dt.Columns.Add(new DataColumn("colStatus", typeof(string)));
dt.Columns["colStatus"].Expression = String.Format("IIF(colBestBefore < #{0}#, 'Ok','Not ok')", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
dt.Rows.Add(DateTime.Now.AddDays(-1));
dt.Rows.Add(DateTime.Now.AddDays(1));
dt.Rows.Add(DateTime.Now.AddDays(2));
dt.Rows.Add(DateTime.Now.AddDays(-2));
dataGridView1.DataSource = dt;
//dataGridView1.DataSource = SQLCon.dataSet.Tables["ServiceRequestInfoes"].DefaultView;
LoadTechnician();
LoadTypeOfService();
LoadOfficeDepartment();
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void SaveRequest(string message)
{
dateTimeRequested = dtp_Requested_Date.Value.Date + dtp_Requested_Time.Value.TimeOfDay;
TechnicianForm technicianForm = new TechnicianForm();
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand(@"INSERT INTO ServiceRequestInfoes (TypeOfServiceProvided, RequestedBy, OfficeDepartmentName, DateRequested, TimeLeft, DateAccomplished, Status, Techinicians, RemarkDeatails, DateEntered, AssignedTechnician) VALUES(
@TypeOfServiceProvided,
@RequestedBy,
@OfficeDepartmentName,
@DateRequested,
@TimeLeft,
@DateAccomplished,
@Status,
@Techinicians,
@RemarkDeatails,
@DateEntered,
@AssignedTechnician);SELECT SCOPE_IDENTITY();", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@TypeOfServiceProvided", cb_Service.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@RequestedBy", tb_RequestedBy.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@OfficeDepartmentName", cb_Office.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateRequested", dateTimeRequested);
SQLCon.sqlCommand.Parameters.AddWithValue("@TimeLeft", DBNull.Value); //Time Left Column
SQLCon.sqlCommand.Parameters.AddWithValue("@RemarkDeatails", cb_Remarks.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@Techinicians", tb_ServiceProvided.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@AssignedTechnician", tb_ToBeServiceProvided.Text);
if (radioButton_Yes.Checked == true)
{
SQLCon.sqlCommand.Parameters.AddWithValue("@Status", true);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateAccomplished", dtp_Accomplished.Value.Date);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateEntered", dtp_Accomplished.Value.Date);
}
else
{
SQLCon.sqlCommand.Parameters.AddWithValue("@Status", false);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateAccomplished", DBNull.Value);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateEntered", DBNull.Value);
}
SQLCon.sqlCommand.ExecuteNonQuery();
ClearTextbox();
LoadRequest();
PopulateComboBox_Infoes();
MetroFramework.MetroMessageBox.Show(this, message, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void ImportRemarks(FileInfo filename)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(filename))
{
ExcelWorksheet firstWorkSheet = excelPackage.Workbook.Worksheets[0];
ExcelWorksheet namedWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];
string rowValue = "";
var start = firstWorkSheet.Dimension.Start;
var end = firstWorkSheet.Dimension.End;
int rowStart = Convert.ToInt32(tb_Start.Text);
int rowEnd = Convert.ToInt32(tb_End.Text);
for (int row = rowStart; row <= rowEnd; row++)
{
for (int col = 8; col <= 8; col++)
{
object cellValue = firstWorkSheet.Cells[row, col].Text;
rowValue = cellValue.ToString();
}
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("SELECT Remars FROM RemarkInfoes WHERE Remars = @1", SQLCon.sqlConnection);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", rowValue);
SQLCon.sqlDataReader = SQLCon.sqlCommand.ExecuteReader();
string checkIfExist = "";
while (SQLCon.sqlDataReader.Read())
{
checkIfExist = (SQLCon.sqlDataReader["Remars"].ToString());
}
if (rowValue != checkIfExist && rowValue != "")
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand(@"INSERT INTO RemarkInfoes
VALUES(
@Remars,
@IsActive)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@Remars", rowValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@IsActive", 1);
SQLCon.sqlCommand.ExecuteNonQuery();
}
}
}
}
public void LoadTechnician()
{
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT * FROM ServiceProvidedBies", SQLCon.sqlConnection);
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_ServiceProvided.DataSource = SQLCon.dataTable;
cb_ServiceProvided.ValueMember = "SP_ID";
cb_ServiceProvided.DisplayMember = "spName";
cb_ServiceProvided.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_ServiceProvided.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_ServiceProvided.SelectedIndex = -1;
}
private void LoadRemark()
{
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT * FROM RemarkInfoes", SQLCon.sqlConnection);
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_Remarks.DataSource = SQLCon.dataTable;
cb_Remarks.ValueMember = "Remark_ID";
cb_Remarks.DisplayMember = "Remars";
cb_Remarks.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_Remarks.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_Remarks.SelectedIndex = -1;
}
public void PopulateComboBox(string tableName, string valueName, string displayName, ComboBox cb_Name)
{
SQLCon.DbCon();
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT DISTINCT " + displayName + " FROM " + tableName + " ORDER BY " + displayName, SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_Name.DataSource = SQLCon.dataTable;
// cb_Name.ValueMember = valueName;
cb_Name.DisplayMember = displayName;
cb_Name.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_Name.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_Name.SelectedIndex = -1;
}
private void DeleteUpdateDataList(string tableName, string columnName, string id, string tag)
{
if (dataGridView_ListData.SelectedRows.Count <= 0)
{
MessageBox.Show("Please Select " + tag + " ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int tempId = int.Parse(dataGridView_ListData.SelectedRows[0].Cells[0].Value.ToString());
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("UPDATE " + tableName + " SET IsActive = 0 WHERE " + id + " = @2", SQLCon.sqlConnection);
SQLCon.sqlCommand.Parameters.AddWithValue("@2", tempId);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("Deleted " + tag + " ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDataList(tableName, columnName, id, tag);
}
}
private void LoadDataList(string tableName, string columnName, string id, string tag)
{
SQLCon.DbCon();
string columnTableName = columnName;
columnTableName = Regex.Replace(columnTableName, "([a-z])([A-Z])", "$1 $2").Trim();
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT " + id + ", " + columnName + " AS [" + columnTableName + "] FROM " + tableName + " WHERE IsActive = 1", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListData.DataSource = SQLCon.dataTable;
dataGridView_ListData.Columns[0].Visible = false;
dataGridView_ListData.Columns[1].HeaderText = tag.ToUpper();
dataGridView_ListData.Tag = tag;
ClearText();
DataGridViewDesign();
dataGridView_ListData.ClearSelection();
}
private void SaveWeekly()
{
try
{
using (SrisContext ctx = new SrisContext())
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO SystemDevelopments VALUES(@USER_ID,@DateAccomplish,@Accomplishment,@Remarks,@WeeklyMonthly,@SystemTitle)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@USER_ID", WelcomeForm.AccountID);
SQLCon.sqlCommand.Parameters.AddWithValue("@Remarks", tb_Memo.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@Accomplishment", tb_Accomplishment.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@SystemTitle", cb_SystemTitle.Text);
if (radioButton_Weekly.Checked == true)
{
SQLCon.sqlCommand.Parameters.AddWithValue("@DateAccomplish", dateTimePicker_Date.Value.Date);
SQLCon.sqlCommand.Parameters.AddWithValue("@WeeklyMonthly", 1);
}
else
{
string month = cb_Month.Text.ToString();
string year = DateTime.Now.Year.ToString();
string dateInput = month + " 1, " + year;
var parsedDate = DateTime.Parse(dateInput);
SQLCon.sqlCommand.Parameters.AddWithValue("@DateAccomplish", Convert.ToDateTime(parsedDate));
SQLCon.sqlCommand.Parameters.AddWithValue("@WeeklyMonthly", 0);
}
}
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("Save Successfully");
ClearText();
LoadWeeklyRpt();
LoadListSystemTitle(cb_SystemTitle);
DesignTable();
}
catch (Exception)
{
throw;
}
}
private void bt_Add_Click(object sender, EventArgs e)
{
int tempID = tempIdRequest;
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand(
@"UPDATE ServiceRequestInfoes SET
TypeOfServiceProvided = @1,
RequestedBy = @2,
OfficeDepartmentName = @3,
DateRequested = @4,
TimeLeft = @5,
DateAccomplished = @6,
RemarkDeatails = @7,
Status = @8,
Techinicians = @9,
DateEntered = @11
WHERE
SR_ID=@10"
, SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@0", tempID);
SQLCon.sqlCommand.Parameters.AddWithValue("@1", comboBox1.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@2", tb_RequestedBy.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@3", cb_Office.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@4", dtp_Requested.Value);
SQLCon.sqlCommand.Parameters.AddWithValue("@5", DBNull.Value); //Time Left Column
SQLCon.sqlCommand.Parameters.AddWithValue("@6", dtp_Accomplished.Value.Date);
SQLCon.sqlCommand.Parameters.AddWithValue("@7", cb_Remarks.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@9", tb_ServiceProvided.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@10", tempID);
SQLCon.sqlCommand.Parameters.AddWithValue("@8", 1);
SQLCon.sqlCommand.Parameters.AddWithValue("@11", DateTime.Now);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("Update Successfully", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
public void LoadRequestList()
{
try
{
SQLCon.DbCon();
SQLCon.sqlDataApater = new SqlDataAdapter(
@"SELECT
T1.II_ID,
T1.RequestingOffice AS [Requesting Office],
T1.RequestedBy AS [Requested By],
T1.ReceiveBy AS [Receive By],
T1.Position AS [Position],
T1.ReceiveDate AS [Receive Date],
T1.TypeOfUnit AS [Type Of Unit],
T1.ComplaintOnUnit AS [Complaint On Unit],
T1.PropertyNumber AS [Property No]
FROM
InspectionInfoes AS T1
", SQLCon.sqlConnection);
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
dataGridView_ListOfInspection.DataSource = SQLCon.dataTable;
dataGridView_ListOfInspection.Columns["II_ID"].Visible = false;
dataGridView_ListOfInspection.Columns["Position"].Visible = false;
DataGridViewDesign();
int i = 0;
foreach (DataGridViewRow row in dataGridView_ListOfInspection.Rows)
{
i++;
}
label_Counter.Text = i.ToString();
}
catch (Exception e)
{
MessageBox.Show("" + e);
}
}
public void LoadOfficeDepartment()
{
try
{
SQLCon.sqlDataApater = new SqlDataAdapter("SELECT * FROM OfficeDepartments", SQLCon.sqlConnection);
SQLCon.DbCon();
SQLCon.dataTable = new DataTable();
SQLCon.sqlDataApater.Fill(SQLCon.dataTable);
cb_OfficeDepartment.DataSource = SQLCon.dataTable;
cb_OfficeDepartment.ValueMember = "OD_ID";
cb_OfficeDepartment.DisplayMember = "OfficeDepartmentName";
cb_OfficeDepartment.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
cb_OfficeDepartment.AutoCompleteSource = AutoCompleteSource.ListItems;
cb_OfficeDepartment.SelectedIndex = -1;
}
catch (Exception)
{
}
}
private void bt_Restore_Click(object sender, EventArgs e)
{
OpenFileDialog opd = new OpenFileDialog();
opd.Filter = "Back up (*.bak)|*.bak";
opd.RestoreDirectory = true;
if (opd.ShowDialog() == DialogResult.OK)
{
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand(@"USE MASTER RESTORE DATABASE SrisDb FROM DISK = '" + opd.FileName + "' WITH REPLACE", SQLCon.sqlConnection);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("Restore Successfully");
}
}
private void SaveRequest()
{
try
{
if (cb_ServiceProvided.SelectedIndex != -1 && cb_OfficeDepartment.SelectedIndex != -1 && cb_TypeOfService.SelectedIndex != -1 && tb_RequestedBy.Text != "")
{
DateTime dateTime1 = DateTime.Now;
DateTime dateTime2 = DateTime.Now.AddDays(4);
var hours = (dateTime2 - dateTime1).TotalHours;
SQLCon.DbCon();
SQLCon.sqlCommand = new SqlCommand("INSERT INTO ServiceRequestInfoes VALUES(@1, @2, @3, @4, @5, @6, @7, @8, @9)", SQLCon.sqlConnection);
SQLCon.sqlCommand.CommandType = CommandType.Text;
SQLCon.sqlCommand.Parameters.AddWithValue("@1", cb_TypeOfService.SelectedValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@2", tb_RequestedBy.Text);
SQLCon.sqlCommand.Parameters.AddWithValue("@3", cb_OfficeDepartment.SelectedValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@4", dtp_Requested.EditValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@5", hours);
SQLCon.sqlCommand.Parameters.AddWithValue("@6", DBNull.Value);
SQLCon.sqlCommand.Parameters.AddWithValue("@7", cb_ServiceProvided.SelectedValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@8", cb_Remarks.SelectedValue);
SQLCon.sqlCommand.Parameters.AddWithValue("@9", 0);
SQLCon.sqlCommand.ExecuteNonQuery();
MessageBox.Show("New request added successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearText();
LoadRequestList();
}
else
{
MessageBox.Show("Please input all the required fields!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
