private void func_Update_Inventory_Date()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details Set Inventory_Date = @Inventory_Date,Tag = @Tag where pk_Id = @pk_Id";
foreach (DataGridViewRow row in dgvItems.Rows)
{
using (SqlCommand cmdUpdate = new SqlCommand(UpdateRecord, SysCon.SystemConnect))
{
cmdUpdate.Parameters.Clear();
cmdUpdate.Parameters.AddWithValue("@Inventory_Date", dtInvDate.Text);
cmdUpdate.Parameters.AddWithValue("@Tag", "Verified");
cmdUpdate.Parameters.AddWithValue("@pk_Id", row.Cells[0].Value);
cmdUpdate.ExecuteNonQuery();
}
}
MessageBox.Show("Inventory Date has been updated!", "Inventory", MessageBoxButtons.OK, MessageBoxIcon.Information);
//close connection
SysCon.SystemConnect.Close();
if (txtSearch.Text == "")
{
MessageBox.Show("Please indicate what items to print. ");
}
else if (cboSearchCriteria.Text == "End User")
{
func_Print_Tag_Per_User();
}
else if (cboSearchCriteria.Text == "Item Description")
{
func_Print_Tag_Per_Item();
}
else if (cboSearchCriteria.Text == "Service")
{
func_Print_Tag_Per_Service();
}
}
private void func_Retrieve_Witness()
{
string RetrieveOIC = "Select * from view_EmployeeDivision where pk_Employee_Id = ' " + Chairman_Id + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand OICFinder = new SqlCommand(RetrieveOIC, SysCon.SystemConnect);
SqlDataReader OICReader = OICFinder.ExecuteReader();
if (OICReader.Read())
{
txtChairman.Text = OICReader[2].ToString();
}
}
private void func_Retrieve_Supplier()
{
string RetrieveSupplier = "Select * from view_CatSupplier where pk_Supplier_Id = ' " + GlobalClass.GlobalSupplierId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand SupplierFinder = new SqlCommand(RetrieveSupplier, SysCon.SystemConnect);
SqlDataReader SupplierReader = SupplierFinder.ExecuteReader();
if (SupplierReader.Read())
{
txtSupplier.Text = SupplierReader[3].ToString();
}
btnFindSupplier.Focus();
}
private void func_Retrieve_ItemDesc()
{
string RetrieveItem = "Select * from tbl_Items_Head where pk_Item_Code = ' " + GlobalClass.GlobalItemId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ItemFinder = new SqlCommand(RetrieveItem, SysCon.SystemConnect);
SqlDataReader ItemReader = ItemFinder.ExecuteReader();
if (ItemReader.Read())
{
txtItemDesc.Text = ItemReader[1].ToString();
txtUOM.Text = ItemReader[2].ToString();
}
}
private void func_Retrieve_AOfficer()
{
string RetrieveOfficer = "Select * from view_EmployeeDivision where pk_Employee_Id = ' " + GlobalClass.GlobalOfficerId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand OfficerFinder = new SqlCommand(RetrieveOfficer, SysCon.SystemConnect);
SqlDataReader OfficerReader = OfficerFinder.ExecuteReader();
if (OfficerReader.Read())
{
txtAccountable.Text = OfficerReader[2].ToString();
}
OfficerReader.Close(); OfficerReader.Dispose();
}
private void func_Retrieve_Items_Details()
{
string RetrieveItems = "Select pk_Id,Category_Name,Subcategory_Name,Article_Name, Description,UOM,Serial_No,Unit_Cost,Supplier_Name,EUL_Name,Date_Acquired,Warranty_Validity,Old_Property_No,Location_Name,Remarks,fk_EUL_Id,IsSubscribed from view_Received_Items where pk_Id = ' " + GlobalClass.GlobalRecItemId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ItemFinder = new SqlCommand(RetrieveItems, SysCon.SystemConnect);
SqlDataReader ItemReader = ItemFinder.ExecuteReader();
if (ItemReader.Read())
{
txtCategory.Text = ItemReader[1].ToString();
txtSubcat.Text = ItemReader[2].ToString();
txtArticle.Text = ItemReader[3].ToString();
txtItemDesc.Text = ItemReader[4].ToString();
txtUOM.Text = ItemReader[5].ToString();
txtSerialNo.Text = ItemReader[6].ToString();
txtUnitCost.Text = ItemReader[7].ToString();
txtSupplier.Text = ItemReader[8].ToString();
dtDateAcquired.Text = ItemReader[10].ToString();
txtWarrantyVaildity.Text = ItemReader[11].ToString();
txtPropertyNo.Text = ItemReader[12].ToString();
txtRemarks.Text = ItemReader[14].ToString();
EUL_Id = ItemReader[15].ToString();
if (ItemReader[16].ToString() == "1")
{
chkIsSubscribed.CheckState = CheckState.Checked;
}
EULName = ItemReader[9].ToString();
LocationName = ItemReader[13].ToString();
cboEUL.Text = EULName;
cboLocation.Text = LocationName;
PropertyNo = txtPropertyNo.Text;
SerialNo = txtSerialNo.Text;
}
txtSerialNo.Focus();
}
private void func_Save_Repair_Record()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string NewRecord = "Insert into tbl_Repair_Record (fk_Inv_Id,Date_Repaired,Status_Of_Item,Reference_No,Remarks) Values('" + Inv_Id +
"', '" + dtDateReturn.Text +
"', '" + cboStatus.Text +
"', '" + txtRefNo.Text +
"', '" + txtRemarks.Text + "')";
SqlCommand AddNewItem = new SqlCommand(NewRecord, SysCon.SystemConnect);
AddNewItem.ExecuteNonQuery();
//Save to Item History Table
string ItemHistory = "Insert into tbl_Item_History (fk_Inv_Id,Date,Document_No,fk_End_User_Id,Status,Remarks) Values ('" + Inv_Id +
"', '" + dtDateReturn.Text +
"', '" + txtRefNo.Text +
"', '" + EmpId +
"', '" + cboStatus.Text +
"', '" + txtRemarks.Text + "')";
SqlCommand AddToHistory = new SqlCommand(ItemHistory, SysCon.SystemConnect);
AddToHistory.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'New Repair Record = ' + '" + txtItemDesc.Text + "'+ ' ; Property No = '+ '" + txtPropertyNo.Text + "' + ' ; Date Returned = '+ '" + dtDateReturn.Text + "' + ' ; Status = '+ '" + cboStatus.Text + "'+ ' ; Reference No. = '+ '" + txtRefNo.Text + "' + ' ; Remarks = '+ '" + txtRemarks.Text + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//Close Connection
SysCon.SystemConnect.Close();
}
private void func_Generate_Document_No()
{
int count = 0;
string YearToday = DateTime.Now.Year.ToString();
string MonthToday = DateTime.Now.ToString("MM");
string YearMonth = YearToday + "-" + MonthToday;
string strCount = "Select Series FROM tbl_Serial where Doc_Type = 'RS'";
//Close existing connection
SysCon.CloseConnection();
SqlCommand comd = new SqlCommand(strCount, SysCon.SystemConnect);
SysCon.SystemConnect.Open();
count = Convert.ToInt32(comd.ExecuteScalar());
count = (Convert.ToInt32(count) + 1);
DocNo = "RS" + "-" + YearMonth + "-" + count.ToString("0000");
// txtDocNo.Text = Document_No;
//Update tbl_Serial
string UpdateSerial = "Update tbl_Serial Set Series = '" + count + "' where Doc_Type = 'RS'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand cmdUpdateSerial = new SqlCommand();
cmdUpdateSerial.CommandType = CommandType.Text;
cmdUpdateSerial.CommandText = UpdateSerial;
cmdUpdateSerial.Connection = SysCon.SystemConnect;
cmdUpdateSerial.ExecuteNonQuery();
//close connection
SysCon.CloseConnection();
}
private void func_Retrieve_User()
{
string RetrieveUser = "******" + GlobalClass.GlobalEmployeeId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand UserFinder = new SqlCommand(RetrieveUser, SysCon.SystemConnect);
SqlDataReader UserReader = UserFinder.ExecuteReader();
if (UserReader.Read())
{
txtEndUser.Text = UserReader[2].ToString();
}
UserReader.Close();
UserReader.Dispose();
}
private void func_Check_Serial_Dup()
{
string CheckSerialNo = "Select * from tbl_Inventory_Details where Serial_No = '" + txtSerialNo.Text + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand SerialNoFinder = new SqlCommand(CheckSerialNo, SysCon.SystemConnect);
SqlDataReader SerialNoReader = SerialNoFinder.ExecuteReader();
if (SerialNoReader.Read())
{
MessageBox.Show("Serial No. has already been used. Please check your data. ");
txtSerialNo.Focus();
}
else
{
if (btnSaveUpdate.Text == "Save")
{
func_Check_Duplication_PropertyNo();
}
else if (btnSaveUpdate.Text == "Update")
{
if (txtPropertyNo.Text != PropertyNo)
{
func_Check_Duplication_PropertyNo();
}
else
{
//Compute Depreciation Cost and Book Value
func_Compute_Accumulated_Depreciation();
//Update data
func_Update_Data();
}
}
}
SerialNoReader.Close();
SerialNoReader.Dispose();
SysCon.SystemConnect.Close();
}
private void func_Retrieve_OIC()
{
string RetrieveOIC = "Select * from view_EmployeeDivision where pk_Employee_Id = ' " + GlobalClass.GlobalOICId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand OICFinder = new SqlCommand(RetrieveOIC, SysCon.SystemConnect);
SqlDataReader OICReader = OICFinder.ExecuteReader();
if (OICReader.Read())
{
txtReceivingEmployee.Text = OICReader[2].ToString();
}
OICReader.Close();
OICReader.Dispose();
}
private void func_Update_Receiving_Head()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
// string UpdateRecord = "Update tbl_Inventory_Details Set Status = @Status,Remarks = @Remarks where Property_No = @Property_No";
string UpdateRecord = "Update tbl_Receiving_Items_Head Set Remarks = @Remarks where IAR_No = @IAR_No";
foreach (DataGridViewRow row in dgvItems2.Rows)
{
using (SqlCommand cmdUpdate = new SqlCommand(UpdateRecord, SysCon.SystemConnect))
{
cmdUpdate.Parameters.Clear();
cmdUpdate.Parameters.AddWithValue("@Remarks", "Item Description : " + row.Cells[3].Value + " ; Reason for cancellation of Property No. : " + txtRemarks.Text + " ; Property No. : " + row.Cells[2].Value);
cmdUpdate.Parameters.AddWithValue("@IAR_No", row.Cells[1].Value);
cmdUpdate.ExecuteNonQuery();
}
}
}
private void func_Retrieve_Subcategory_Details()
{
string RetrieveSubcategory = "Select Category_Name,Subcategory_Name from view_CatSubcat where pk_Subcategory_Id = ' " + GlobalClass.GlobalSubcategoryId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand SubcategoryFinder = new SqlCommand(RetrieveSubcategory, SysCon.SystemConnect);
SqlDataReader SubcategoryReader = SubcategoryFinder.ExecuteReader();
if (SubcategoryReader.Read())
{
txtSubCatName.Text = SubcategoryReader[1].ToString();
txtCategoryName.Text = SubcategoryReader[0].ToString();
txtArticleName.Focus();
}
}
private void func_Retrieve_Item()
{
string RetrieveItems = "Select pk_Id,Description,New_Property_No,Serial_No from view_Inventory_Details where pk_Id = ' " + GlobalClass.GlobalInvItemId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ItemFinder = new SqlCommand(RetrieveItems, SysCon.SystemConnect);
SqlDataReader ItemReader = ItemFinder.ExecuteReader();
if (ItemReader.Read())
{
InvId = ItemReader[0].ToString();
txtDescription.Text = ItemReader[1].ToString();
txtPropertyNo.Text = ItemReader[2].ToString();
txtSerialNo.Text = ItemReader[3].ToString();
}
}
private void func_Retrieve_Service_Details()
{
string RetrieveService = "Select Service_Code from tbl_Services where pk_Service_Id = ' " + GlobalClass.GlobalServiceId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ServiceFinder = new SqlCommand(RetrieveService, SysCon.SystemConnect);
SqlDataReader ServiceReader = ServiceFinder.ExecuteReader();
if (ServiceReader.Read())
{
txtSearch.Text = ServiceReader[0].ToString();
}
func_Load_Inv_Items_Per_Service();
lblcount.Text = dgvItems.RowCount.ToString();
}
private void func_Retrieve_Employee_Details()
{
string RetrieveEmployee = "Select Full_Name from view_EmployeeDivision where pk_Employee_Id = ' " + GlobalClass.GlobalEmployeeId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand EmployeeFinder = new SqlCommand(RetrieveEmployee, SysCon.SystemConnect);
SqlDataReader EmployeeReader = EmployeeFinder.ExecuteReader();
if (EmployeeReader.Read())
{
txtSearch.Text = EmployeeReader[0].ToString();
}
func_Load_Inv_Items_Per_User();
lblcount.Text = dgvItems.RowCount.ToString();
}
private void func_Update_Status()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details Set Status = @Status,Remarks = @Remarks,fk_Accountable_Employee_Id =@fk_Accountable_Employee_Id,fk_End_User_Id=@fk_End_User_Id where Old_Property_No = @Property_No";
foreach (DataGridViewRow row in dgvItems2.Rows)
{
using (SqlCommand cmdUpdate = new SqlCommand(UpdateRecord, SysCon.SystemConnect))
{
cmdUpdate.Parameters.Clear();
cmdUpdate.Parameters.AddWithValue("@Status", "CANCELLED PROPERTY NO.");
cmdUpdate.Parameters.AddWithValue("@Remarks", txtRemarks.Text);
cmdUpdate.Parameters.AddWithValue("@Property_No", row.Cells[2].Value);
cmdUpdate.Parameters.AddWithValue("@fk_Accountable_Employee_Id", " ");
cmdUpdate.Parameters.AddWithValue("@fk_End_User_Id", " ");
cmdUpdate.ExecuteNonQuery();
}
}
//Audit trail
string user = "******";
foreach (DataGridViewRow row in dgvItems2.Rows)
{
using (SqlCommand cmd = new SqlCommand(user, SysCon.SystemConnect))
{
{
cmd.Parameters.AddWithValue("@Full_Name", GlobalClass.GlobalName);
cmd.Parameters.AddWithValue("@User_Name", GlobalClass.GlobalUser);
cmd.Parameters.AddWithValue("@Date_Time", DateTime.Now.ToString());
cmd.Parameters.AddWithValue("@Activity", "Cancel Property No. : " + row.Cells[2].Value + " ; Item Desciption : " + row.Cells[3].Value + " ; Reason for Cancellation : " + txtRemarks.Text + " ; Reference Number : " + row.Cells[1].Value);
cmd.ExecuteNonQuery();
}
}
}
MessageBox.Show("Property No/s. has been successfully cancelled!", "Property No Cancellation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void func_Check_Duplication_PropertyNo()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string CheckDuplication = "Select Old_Property_No from tbl_Inventory_Details where Old_Property_No = '" + txtPropertyNo.Text + "'";
SqlCommand CheckRecordCommand = new SqlCommand(CheckDuplication, SysCon.SystemConnect);
SqlDataReader CReader = CheckRecordCommand.ExecuteReader();
if (CReader.Read()) //if Property# was found
{
MessageBox.Show("Property Number already exists!", "Adding Existing Item", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtPropertyNo.Focus();
return;
}
else
{
if (btnSaveUpdate.Text == "Save")
{
//Compute Depreciation Cost and Book Value
func_Compute_Accumulated_Depreciation();
//Save data
func_Save_Data();
}
else
{
//Compute Depreciation Cost and Book Value
func_Compute_Accumulated_Depreciation();
//Update Data
func_Update_Data();
}
}
CReader.Close();
CReader.Dispose();
}
private void func_Update_Item()
{
string UpdateRecord = "Update tbl_Items_Head Set Description = '" + txtItemDescription.Text +
"',UOM = '" + cboUOM.Text + "' where pk_Item_Code = '" + ItemCode + "'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand UpdateItems = new SqlCommand();
UpdateItems.CommandType = CommandType.Text;
UpdateItems.CommandText = UpdateRecord;
UpdateItems.Connection = SysCon.SystemConnect;
UpdateItems.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Item description = ' + '" + txtItemDescription.Text + "'+ ' ; UOM = '+ '" + cboUOM.Text + "' + ' ; Item Code = ' + '" + ItemCode + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//close connection
SysCon.SystemConnect.Close();
MessageBox.Show("Item has been successfully updated!", "Update Item", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset();
txtItemDescription.Focus();
txtSearchItems.Text = "";
func_Load_All_Items();
}
private void func_Retrieve_Item()
{
string RetrieveItems = "Select Description from view_Existing_Items_Details where pk_Id = ' " + GlobalClass.GlobalInvItemId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ItemFinder = new SqlCommand(RetrieveItems, SysCon.SystemConnect);
SqlDataReader ItemReader = ItemFinder.ExecuteReader();
if (ItemReader.Read())
{
txtSearch.Text = ItemReader[0].ToString();
}
ItemReader.Close();
ItemReader.Dispose();
func_Load_Item_History();
lblcount.Text = dgvItems.RowCount.ToString();
}
private void func_Retrieve_Article_Details()
{
string RetrieveArticle = "Select * from view_ArticleSubcat where pk_Article_Id = ' " + GlobalClass.GlobalArticleId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ArticleFinder = new SqlCommand(RetrieveArticle, SysCon.SystemConnect);
SqlDataReader ArticleReader = ArticleFinder.ExecuteReader();
if (ArticleReader.Read())
{
txtCategory.Text = ArticleReader[2].ToString();
txtSubcat.Text = ArticleReader[3].ToString();
txtArticle.Text = ArticleReader[5].ToString();
btnFindItemDesc.Focus();
}
btnFindArticle.Focus();
}
private void func_Retrieve_Query_Result()
{
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
string RetrieveData;
if (cboSearchCriteria.Text == "Document No.")
{
RetrieveData = "Select Distinct Document_No,[Accountable Officer],Date_Issued from view_Inventory_Details where Document_No LIKE '%" + txtSearch.Text + "%'and (Document_No LIKE '%" + DocType1 + "%' or Document_No LIKE '%" + DocType2 + "%') ";
SqlDataAdapter AllItemsAdapter = new SqlDataAdapter(RetrieveData, SysCon.SystemConnect);
string srctbl = "view_Inventory_Details";
DataSet ItemsData = new DataSet();
AllItemsAdapter.Fill(ItemsData, srctbl);
dgvItems.DataSource = ItemsData.Tables["view_Inventory_Details"];
dgvItems.RowHeadersWidth = 5;
dgvItems.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
if (cboSearchCriteria.Text == "Accountable Officer")
{
RetrieveData = "Select Distinct Document_No,[Accountable Officer],Date_Issued from view_Inventory_Details where [Accountable Officer] LIKE '%" + txtSearch.Text + "%'and (Document_No LIKE '%" + DocType1 + "%' or Document_No LIKE '%" + DocType2 + "%') ";
SqlDataAdapter AllItemsAdapter = new SqlDataAdapter(RetrieveData, SysCon.SystemConnect);
string srctbl = "view_Inventory_Details";
DataSet ItemsData = new DataSet();
AllItemsAdapter.Fill(ItemsData, srctbl);
dgvItems.DataSource = ItemsData.Tables["view_Inventory_Details"];
dgvItems.RowHeadersWidth = 5;
dgvItems.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
SysCon.SystemConnect.Close();
lblcount.Text = dgvItems.RowCount.ToString();
}
private void func_Load_All_Items()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllExItems = "Select * from view_Inventory_Details where Status = 'Assigned' and fk_End_User_Id = '" + GlobalClass.GlobalEmployeeId + "'";
SqlDataAdapter AllItemsAdapter = new SqlDataAdapter(AllExItems, SysCon.SystemConnect);
string srctbl = "view_Inventory_Details";
DataSet ItemsData = new DataSet();
AllItemsAdapter.Fill(ItemsData, srctbl);
dgvItems.DataSource = ItemsData.Tables["view_Inventory_Details"];
dgvItems.RowHeadersWidth = 5;
dgvItems.Columns[0].Visible = false;
dgvItems.Columns[1].Visible = false;
dgvItems.Columns[2].Visible = false;
dgvItems.Columns[3].Visible = false;
dgvItems.Columns[4].Visible = false;
dgvItems.Columns[22].Visible = false;
dgvItems.Columns[26].Visible = false;
dgvItems.Columns[29].Visible = false;
dgvItems.Columns[30].Visible = false;
dgvItems.Columns[32].Visible = false;
dgvItems.Columns[35].Visible = false;
dgvItems.Columns[36].Visible = false;
dgvItems.Columns[37].Visible = false;
//dgvItems.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
SysCon.SystemConnect.Close();
}
private void func_Retrieve_Employee_Details()
{
string RetrieveEmployee = "Select * from view_EmployeeDivision where pk_Employee_Id = ' " + GlobalClass.GlobalEmployeeId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand EmployeeFinder = new SqlCommand(RetrieveEmployee, SysCon.SystemConnect);
SqlDataReader EmployeeReader = EmployeeFinder.ExecuteReader();
if (EmployeeReader.Read())
{
txtFirstName.Text = EmployeeReader[6].ToString();
txtMiddleName.Text = EmployeeReader[7].ToString();
txtLastName.Text = EmployeeReader[8].ToString();
cboUnit.Text = EmployeeReader[5].ToString();
}
cboUserType.Focus();
}
private void func_Load_All_Items()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllInvtems = "Select * from view_Inventory_Details";
SqlDataAdapter AllItemsAdapter = new SqlDataAdapter(AllInvtems, SysCon.SystemConnect);
string srctbl = "view_Inventory_Details";
DataSet ItemsData = new DataSet();
AllItemsAdapter.Fill(ItemsData, srctbl);
dgvItems.DataSource = ItemsData.Tables["view_Inventory_Details"];
dgvItems.RowHeadersWidth = 5;
dgvItems.Columns[0].Visible = false;
dgvItems.Columns[1].Visible = false;
dgvItems.Columns[2].Visible = false;
dgvItems.Columns[3].Visible = false;
dgvItems.Columns[4].Visible = false;
dgvItems.Columns[22].Visible = false;
dgvItems.Columns[26].Visible = false;
dgvItems.Columns[30].Visible = false;
dgvItems.Columns[31].Visible = false;
dgvItems.Columns[32].Visible = false;
dgvItems.Columns[32].Visible = false;
dgvItems.Columns[38].Visible = false;
dgvItems.Columns[39].Visible = false;
//dgvItems.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
SysCon.SystemConnect.Close();
}
private void func_Check_Duplication_UpdEmployee()
{
string MidI = txtMiddleName.Text;
MidI = MidI.Substring(0, 1);
txtFullName.Text = txtFirstName.Text + ' ' + MidI + '.' + ' ' + txtLastName.Text;
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
if (EmployeeName != txtFullName.Text)
{
string CheckDuplication = "Select Full_Name from tbl_BOI_Employees where Full_Name = '" + txtFullName.Text + "'";
SqlCommand CheckDuplicateCommand = new SqlCommand(CheckDuplication, SysCon.SystemConnect);
SqlDataReader EmployeeReader = CheckDuplicateCommand.ExecuteReader();
if (EmployeeReader.Read())
{
MessageBox.Show("Employee name already exists!", "BOI Employee", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtFullName.Focus();
return;
}
else
{
func_Update_Employee();
}
EmployeeReader.Close();
EmployeeReader.Dispose();
}
else
{
func_Update_Employee();
//func_Reset();
}
}
private void func_Retrieve_Users()
{
string RetrieveUsers = "Select * from tbl_System_Users where pk_User_Id = ' " + GlobalClass.GlobalUsersId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand UsersFinder = new SqlCommand(RetrieveUsers, SysCon.SystemConnect);
SqlDataReader UsersReader = UsersFinder.ExecuteReader();
if (UsersReader.Read())
{
txtFirstName.Text = UsersReader[1].ToString();
txtMiddleName.Text = UsersReader[2].ToString();
txtLastName.Text = UsersReader[3].ToString();
Full_Name = UsersReader[4].ToString();
txtUserName.Text = UsersReader[6].ToString();
// textBox1.Text = UsersReader[7].ToString();
pword = UsersReader[7].ToString();
cboUnit.Text = UsersReader[5].ToString();
cboUserType.Text = UsersReader[8].ToString();
txtRemarks.Text = UsersReader[9].ToString();
}
UsersReader.Close();
UsersReader.Dispose();
txtPassword.Enabled = true;
func_Decrypt_Password();
UserName = txtUserName.Text;
// txtPassword.Text = pword;
}
private void func_Delete_MOA()
{
try
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute deletion
SqlCommand DeleteMOA = new SqlCommand();
DeleteMOA.CommandText = "Delete from tbl_Mode_of_Acquisition where pk_MOA_Id = '" + MOAId + "'";
DeleteMOA.CommandType = CommandType.Text;
DeleteMOA.Connection = SysCon.SystemConnect;
DeleteMOA.ExecuteNonQuery();
MessageBox.Show("Record has been successfully deleted!", "Delete Mode of Acquisition", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
txtSearchMOA.Text = "";
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Delete Mode of Acquisition = ' + '" + txtMOA.Text + "'+ ' ; Record Id = '+ '" + MOAId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
func_Load_All_MOA();
}
catch (System.Data.SqlClient.SqlException)
{
MessageBox.Show("This data cannot be deleted due to some dependency issue. \nPlease contact the System Administrator for assistance.", "Delete Mode of Acquisition", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void func_Update_MOA()
{
string UpdateRecord = "Update tbl_Mode_of_Acquisition Set Mode_of_Acquisition = '" + txtMOA.Text + "' where pk_MOA_Id = '" + MOAId + "'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand UpdateMOA = new SqlCommand();
UpdateMOA.CommandType = CommandType.Text;
UpdateMOA.CommandText = UpdateRecord;
UpdateMOA.Connection = SysCon.SystemConnect;
UpdateMOA.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Mode of Acquisition description = ' + '" + txtMOA.Text + "'+ ' ; ID = '+ '" + MOAId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//close connection
SysCon.SystemConnect.Close();
MessageBox.Show("Record has been successfully updated!", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtSearchMOA.Text = "";
func_Reset();
func_Load_All_MOA();
}
private void func_Update_EUL()
{
string UpdateRecord = "Update tbl_Estimated_Useful_Life Set EUL_Name = '" + txtESLDesc.Text + "' where pk_EUL_Id = '" + ESLId + "'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand UpdateItems = new SqlCommand();
UpdateItems.CommandType = CommandType.Text;
UpdateItems.CommandText = UpdateRecord;
UpdateItems.Connection = SysCon.SystemConnect;
UpdateItems.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update EUL description = ' + '" + txtESLDesc.Text + "'+ ' ; ID = '+ '" + ESLId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//close connection
SysCon.SystemConnect.Close();
MessageBox.Show("Item has been successfully updated!", "Update Item", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset();
func_Load_All_ESL();
}
