private void func_Add_SupCat()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string NewSupCat = "Insert into tbl_Supplier_Category (Category_Name) Values('" + txtSupCat.Text + "')";
SqlCommand AddNewSupCat = new SqlCommand(NewSupCat, SysCon.SystemConnect);
AddNewSupCat.ExecuteNonQuery();
MessageBox.Show("New category has been successfully added!", "Add New Supplier Category", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Add New Supplier Category = ' + '" + txtSupCat.Text + "'+ ' ; Id = '+ '" + SupCatId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//Close Connection
SysCon.SystemConnect.Close();
func_Reset();
func_Load_All_SupCat();
}
private void func_Check_Duplication_UpdSupCat()
{
if (SupCatId != txtSupCat.Text)
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string CheckDuplication_SupCat = "Select Category_Name from tbl_Supplier_Category where Category_Name ='" + txtSupCat.Text + "'";
SqlCommand CheckDuplicateSCCommand = new SqlCommand(CheckDuplication_SupCat, SysCon.SystemConnect);
SqlDataReader SupCatReader = CheckDuplicateSCCommand.ExecuteReader();
if (SupCatReader.Read())
{
MessageBox.Show("Supplier Category already exists!", "Supplier Category", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtSupCat.Focus();
return;
}
else
{
func_Update_SOE();
}
SupCatReader.Close();
SupCatReader.Dispose();
}
else
{
MessageBox.Show("No changes has been made!");
func_Reset();
}
}
private void func_Check_Duplication_AddSupCat()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string CheckDuplication = "Select Category_Name from tbl_Supplier_Category where Category_Name ='" + txtSupCat.Text + "'";
SqlCommand CheckRecordCommand = new SqlCommand(CheckDuplication, SysCon.SystemConnect);
SqlDataReader CReader = CheckRecordCommand.ExecuteReader();
if (CReader.Read())
{
MessageBox.Show("Status already exists!", "New Category", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtSupCat.Focus();
return;
}
else
{
func_Add_SupCat();
}
CReader.Close();
CReader.Dispose();
}
private void func_Load_Services()
{
string Services = "Select pk_Service_Id,Service_Code from tbl_Services";
//Close existing connection
SysCon.CloseConnection();
//Open Connectiond
SysCon.SystemConnect.Open();
//Loading all categories
SqlCommand LoadServices = new SqlCommand(Services, SysCon.SystemConnect);
SqlDataReader ServicesReader = LoadServices.ExecuteReader();
while (ServicesReader.Read())
{
cboServices.Items.Add(ServicesReader.GetValue(1));
}
ServicesReader.Close();
ServicesReader.Dispose();
//Close connection
SysCon.SystemConnect.Close();
}
private void func_Load_All_Items()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllItems = "Select pk_Id, Category_Name,SubCategory_Name, Article_Name , Description, UOM, Unit_Cost, Serial_No,Old_Property_No from view_Inventory_Details ";
SqlDataAdapter AllItemssAdapter = new SqlDataAdapter(AllItems, SysCon.SystemConnect);
string srctbl = "view_Inventory_Details";
DataSet SubItemData = new DataSet();
AllItemssAdapter.Fill(SubItemData, srctbl);
dgvItems.DataSource = SubItemData.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[5].Visible = false;
//dgvItems.Columns[6].Visible = false;
//dgvItems.Columns[7].Visible = false;
//dgvItems.Columns[8].Visible = false;
// dgvItems.Columns[10].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
SysCon.SystemConnect.Close();
}
private void func_Load_All_Divisions()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllDivisions = "Select * from view_ServicesDivision";
SqlDataAdapter AllDivisionsAdapter = new SqlDataAdapter(AllDivisions, SysCon.SystemConnect);
string srctbl = "view_ServicesDivision";
DataSet DivisionsData = new DataSet();
AllDivisionsAdapter.Fill(DivisionsData, srctbl);
dgvDivision.DataSource = DivisionsData.Tables["view_ServicesDivision"];
dgvDivision.RowHeadersWidth = 5;
dgvDivision.Columns[0].Visible = false;
dgvDivision.Columns[1].Visible = false;
dgvDivision.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
SysCon.SystemConnect.Close();
}
private void func_Get_Service_Id()
{
//Close existing connection
SysCon.CloseConnection();
string Services = "Select pk_Service_Id from tbl_Services where Service_Code = '" + cboServices.Text + "'";
SysCon.SystemConnect.Open();
//Get ID from subcategory table
SqlCommand SelectServices_Id = new SqlCommand(Services, SysCon.SystemConnect);
SqlDataReader ServiceIdReader = SelectServices_Id.ExecuteReader();
ServiceIdReader.Read();
Service_Id = ServiceIdReader[0].ToString();
ServiceIdReader.Close();
//Close connection
SysCon.SystemConnect.Close();
//MessageBox.Show(Service_Id);
}
private void func_Delete_Division()
{
try
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute deletion
SqlCommand DeleteDivision = new SqlCommand();
DeleteDivision.CommandText = "Delete from tbl_Divisions where pk_Division_Id = '" + GlobalClass.GlobalDivisionId + "'";
DeleteDivision.CommandType = CommandType.Text;
DeleteDivision.Connection = SysCon.SystemConnect;
DeleteDivision.ExecuteNonQuery();
MessageBox.Show("Division has been successfully deleted!", "Delete Division", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Delete Division Name = ' + '" + txtDivisionName.Text + "' + ' ; Code = '+ '" + txtDivisionCode.Text + "' + ' ; Service = ' + '" + cboServices.Text + "' + ' ; Unit : ' + '" + txtUnit.Text + "' + ' ;Division Id = ' + '" + GlobalClass.GlobalDivisionId + "' )";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
}
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 Division", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void func_Add_Division()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string NewDivision = "Insert into tbl_Divisions (fk_Service_Id,Division_Name,Division_Code,Unit) Values('" + Service_Id +
"', '" + txtDivisionName.Text + "','" + txtDivisionCode.Text + "', '" + txtUnit.Text + "')";
SqlCommand AddNewDivision = new SqlCommand(NewDivision, SysCon.SystemConnect);
AddNewDivision.ExecuteNonQuery();
MessageBox.Show("New Division has been successfully added!", "Add Division", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Add New Division Name = ' + '" + txtDivisionName.Text + "'+ ' ; Code = '+ '" + txtDivisionCode.Text + "' + ' ; Unit ' + '" + txtUnit.Text + "' + 'Service_Id = ' + '" + Service_Id + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//Close Connection
SysCon.SystemConnect.Close();
func_Reset();
}
private void func_Load_All_SupCat()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllSupCat = "Select * from tbl_Supplier_Category";
SqlDataAdapter AllSCAdapter = new SqlDataAdapter(AllSupCat, SysCon.SystemConnect);
string srctbl = "tbl_Supplier_Category";
DataSet SupCatData = new DataSet();
AllSCAdapter.Fill(SupCatData, srctbl);
dgvSupCat.DataSource = SupCatData.Tables["tbl_Supplier_Category"];
dgvSupCat.RowHeadersWidth = 5;
dgvSupCat.Columns[0].Visible = false;
dgvSupCat.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
SysCon.SystemConnect.Close();
}
private void func_LoadEULData()
{
string EULData = "Select pk_EUL_Id,EUL_Name from tbl_Estimated_Useful_Life";
//Close existing connection
SysCon.CloseConnection();
//Open Connection
SysCon.SystemConnect.Open();
//Loading all EUL Data
SqlCommand LoadEULData = new SqlCommand(EULData, SysCon.SystemConnect);
SqlDataReader EULReader = LoadEULData.ExecuteReader();
while (EULReader.Read())
{
cboEUL.Items.Add(EULReader.GetValue(1));
}
EULReader.Close();
EULReader.Dispose();
//Close connection
SysCon.SystemConnect.Close();
}
private void func_Load_All_Items()
{
//Close current connection
SysCon.CloseConnection();
SysCon.SystemConnect.Open();
string AllExItems = "Select pk_Id,Category_Name,Subcategory_Name,Subcategory_Code,Article_Code,Article_Name,Description,UOM,Serial_No,Unit_Cost,EUL_Name,Date_Acquired,Depreciated_Cost,Book_Value,Old_Property_No,New_Property_No,Status,Document_No,[Accountable Officer],Designation,[End User],[End User Unit],Inventory_Date,Remarks,EOS from view_Inventory_Details where (STATUS != 'EXPIRED')";
//(Status != 'FOR ASSIGNMENT' AND Status!= 'EXPIRED'
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.Columns[0].Visible = false;
dgvItems.Columns[3].Visible = false;
dgvItems.Columns[4].Visible = false;
dgvItems.RowHeadersWidth = 5;
SysCon.SystemConnect.Close();
lblcount.Text = dgvItems.RowCount.ToString();
}
private void func_Retrieve_Division_Details()
{
string RetrieveDivision = "Select Service_Code,Division_Name,Division_Code, Unit from view_ServicesDivision where pk_Division_Id = ' " + GlobalClass.GlobalDivisionId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand DivisionFinder = new SqlCommand(RetrieveDivision, SysCon.SystemConnect);
SqlDataReader DivisionReader = DivisionFinder.ExecuteReader();
if (DivisionReader.Read())
{
//copy to variable for the purpose of updating comparison
//SubcategoryName = DivisionReader[1].ToString();
//SubcategoryCode = DivisionReader[2].ToString();
txtDivisionName.Text = DivisionReader[1].ToString();
txtDivisionCode.Text = DivisionReader[2].ToString();
txtUnit.Text = DivisionReader[3].ToString();
cboServices.Text = DivisionReader[0].ToString();
Unit = txtUnit.Text;
txtDivisionName.Focus();
}
cboServices.Focus();
btnSaveUpdate.Text = "Update";
btnDelete.Enabled = true;
}
private void frmDivisionList_Load(object sender, EventArgs e)
{
// Close current connection
SysCon.CloseConnection();
//Load categories data
func_Load_All_Divisions();
txtSearchDivision.Focus();
}
private void func_Check_Duplication_UpdDivision()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
if (Unit != txtUnit.Text)
{
string CheckDuplication_Code = "Select Unit from tbl_Divisions where Unit = '" + txtUnit.Text + "'";
SqlCommand CheckDuplicateCodeCommand = new SqlCommand(CheckDuplication_Code, SysCon.SystemConnect);
SqlDataReader CodeReader = CheckDuplicateCodeCommand.ExecuteReader();
if (CodeReader.Read())
{
MessageBox.Show("Division under the same service already exists!", "Division", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtDivisionCode.Focus();
return;
}
else
{
if (txtDivisionCode.Text == "")
{
txtUnit.Text = "";
txtUnit.Text = cboServices.Text;
}
else
{
txtUnit.Text = "";
txtUnit.Text = cboServices.Text + '-' + txtDivisionCode.Text;
}
func_Update_Division();
}
CodeReader.Close();
CodeReader.Dispose();
}
else
{
if (txtUnit.Text == "")
{
txtUnit.Text = "";
txtUnit.Text = cboServices.Text;
}
else
{
// txtUnit.Text = "";
txtUnit.Text = cboServices.Text + '-' + txtDivisionCode.Text;
}
func_Update_Division();
func_Reset();
}
}
private void func_Update_Data()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details Set IsSubscribed = @IsSubscribed, EOS = @EOS where pk_Id = @pk_Id";
int x = 0;
foreach (DataGridViewRow row in dgvItems.Rows)
{
//for generation of EOS Date
//Additional field to determine subscription end date , applicable for software subscribed only - value = Date of Acquisition + Estimated Useful Life
//* Get End Of Service value
//**get no. only from eul value
var EULife = dgvItems.Rows[x].Cells[10].Value.ToString();
var E_UL = String.Join("", EULife.Where(Char.IsDigit));
// Add EUL to Year of Acquisition
var DateOfAcq = dgvItems.Rows[x].Cells[11].Value.ToString();
DateTime AquisitionDate = DateTime.Parse(DateOfAcq);
DateTime EOS_Date = AquisitionDate.AddYears(Convert.ToInt32(E_UL));
//Update tbl_Inventory
//Update IsSubscribed and EOS_Date (Date of Acquisition + Estimated Useful Life) Values
using (SqlCommand cmdUpdate = new SqlCommand(UpdateRecord, SysCon.SystemConnect))
{
cmdUpdate.Parameters.Clear();
cmdUpdate.Parameters.AddWithValue("@IsSubscribed", SubsValue);
cmdUpdate.Parameters.AddWithValue("@EOS", EOS_Date);
cmdUpdate.Parameters.AddWithValue("@pk_Id", row.Cells[0].Value);
cmdUpdate.ExecuteNonQuery();
//Audit trail
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Item : ' + '" + row.Cells[6].Value + "' + ' ; Property No. : ' + '" + row.Cells[14].Value + "' + ' ; EOS :' + '" + EOS_Date + "' + '; Is Subscribed : ' + '" + SubsValue + "')";
SqlCommand AuditTrail1 = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail1.ExecuteNonQuery();
}
x++;
}
MessageBox.Show("Data has been successfully updated!", "Update Data", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset();
func_Load_All_Items();
}
private void func_Retrieve_SubItemDesc()
{
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())
{
txtSubItemDesc.Text = ItemReader[1].ToString();
txtUOM.Text = ItemReader[2].ToString();
}
}
private void func_Retrieve_ParentItem()
{
string RetrieveItem = "Select Description,Unit_Cost,Old_Property_No from view_Inventory_Details where pk_Id = ' " + GlobalClass.GlobalInvItemId + "'";
//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[0].ToString();
txtCost.Text = ItemReader[1].ToString();
txtOldPropertyNo.Text = ItemReader[2].ToString();
}
}
private void func_Retrieve_Article_Details()
{
string RetrieveArticle = "Select Article_Name 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())
{
txtArticle.Text = ArticleReader[0].ToString();
btnFindSubItem.Focus();
}
btnFindSubArticle.Focus();
}
private void func_Update_Data()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details Set Unit_Cost = @Unit_Cost 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("@Unit_Cost", txtUnitCost.Text);
cmdUpdate.Parameters.AddWithValue("@pk_Id", row.Cells[0].Value);
cmdUpdate.ExecuteNonQuery();
}
}
//Audit trail
string user = "******";
foreach (DataGridViewRow row in dgvItems.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", "Item Desciption : " + row.Cells[6].Value + " ; Unit Cost : " + txtUnitCost.Text + "'");
cmd.ExecuteNonQuery();
}
}
}
MessageBox.Show("Unit Cost value/s has been successfully updated!", "Batch Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset();
func_Load_All_Items();
}
private void btnClearPN_Click(object sender, EventArgs e)
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details set New_Property_No = @NewPN , Office = @Office 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("@NewPN", "");
cmdUpdate.Parameters.AddWithValue("@Office", "");
cmdUpdate.Parameters.AddWithValue("@pk_Id", row.Cells[0].Value);
cmdUpdate.ExecuteNonQuery();
}
}
MessageBox.Show("Done!");
}
private void func_Get_EUL_Id()
{
//Close existing connection
SysCon.CloseConnection();
string EUL = "Select pk_EUL_Id from tbl_Estimated_Useful_Life where EUL_Name = '" + cboEUL.Text + "'";
SysCon.SystemConnect.Open();
//Get ID from EUL table
SqlCommand SelectEUL_Id = new SqlCommand(EUL, SysCon.SystemConnect);
SqlDataReader EULIdReader = SelectEUL_Id.ExecuteReader();
EULIdReader.Read();
EUL_Id = EULIdReader[0].ToString();
EULIdReader.Close();
//Close connection
SysCon.SystemConnect.Close();
}
private void func_Update_SOE()
{
string UpdateRecord = "Update tbl_Supplier_Category Set Category_Name = '" + txtSupCat.Text + "' where pk_Sup_Category_Id = '" + SupCatId + "'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand UpdateSupcat = new SqlCommand();
UpdateSupcat.CommandType = CommandType.Text;
UpdateSupcat.CommandText = UpdateRecord;
UpdateSupcat.Connection = SysCon.SystemConnect;
UpdateSupcat.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Supplier Category = ' + '" + txtSupCat.Text + "'+ ' ; ID = '+ '" + SupCatId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//close connection
SysCon.SystemConnect.Close();
MessageBox.Show("Item has been successfully updated!", "Update Supplier Category", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtSearchSupCat.Text = "";
func_Reset();
func_Load_All_SupCat();
}
private void func_Delete_SupCat()
{
try
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute deletion
SqlCommand DeleteSupCat = new SqlCommand();
DeleteSupCat.CommandText = "Delete from tbl_Supplier_Category where pk_Sup_Category_Id = '" + SupCatId + "'";
DeleteSupCat.CommandType = CommandType.Text;
DeleteSupCat.Connection = SysCon.SystemConnect;
DeleteSupCat.ExecuteNonQuery();
MessageBox.Show("Supplier Category has been successfully deleted!", "Delete Supplier Category", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
txtSearchSupCat.Text = "";
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Delete Supplier Category = ' + '" + txtSupCat.Text + "'+ ' ; Supplier Category Id = '+ '" + SupCatId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
func_Load_All_SupCat();
}
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 Status Equipment", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void func_Update_Division()
{
string UpdateRecord = "Update tbl_Divisions Set fk_Service_Id = '" + Service_Id +
"',Division_Name = '" + txtDivisionName.Text + "', Division_Code ='" + txtDivisionCode.Text + "', Unit ='" + txtUnit.Text + "' where pk_Division_Id = '" + GlobalClass.GlobalDivisionId + "'";
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//execute query
SqlCommand UpdateDivision = new SqlCommand();
UpdateDivision.CommandType = CommandType.Text;
UpdateDivision.CommandText = UpdateRecord;
UpdateDivision.Connection = SysCon.SystemConnect;
UpdateDivision.ExecuteNonQuery();
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Division Name = ' + '" + txtDivisionName.Text + "'+ ' ; Code = '+ '" + txtDivisionCode.Text + "' +' ; Service Id: ' + '" + Service_Id + "'+ ' ;Unit :' + '" + txtUnit.Text + "' + ' ; Id = ' + '" + GlobalClass.GlobalDivisionId + "')";
SqlCommand AuditTrail = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail.ExecuteNonQuery();
//close connection
SysCon.SystemConnect.Close();
MessageBox.Show("Division has been successfully updated!", "Update Division", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset();
}
private void func_Check_Existence_in_tbl_Supplier()
{
string Check_Data = "Select fk_Sup_Category_Id from tbl_Supplier where fk_Sup_Category_Id = ' " + SupCatId + "' ";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand DataFinder = new SqlCommand(Check_Data, SysCon.SystemConnect);
SqlDataReader DataReader = DataFinder.ExecuteReader();
if (DataReader.Read())
{
MessageBox.Show("This data cannot be deleted due to some dependency issue. \nPlease contact the System Administrator for assistance.", "Delete Supplier Category", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
func_Delete_SupCat();
}
DataReader.Close();
DataReader.Dispose();
}
private void func_Save_Data()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
//Saving Item Details :
string NewComponent = "Insert into tbl_Sub_Items_Head Values (@fk_Inv_Id,@fk_Article_Id,@fk_Item_Code,@Serial_No,@Quantity,@Unit_Cost,@Total_Cost,@fk_EUL_Id,@Date_Acquired,@Remarks,@Stock_No,@IsSubscribed,@EOS)";
int x = 0;
foreach (DataGridViewRow row in dgvItems.Rows)
{
using (SqlCommand cmd = new SqlCommand(NewComponent, SysCon.SystemConnect))
{
cmd.Parameters.AddWithValue("@fk_Inv_Id", row.Cells[0].Value);
cmd.Parameters.AddWithValue("@fk_Article_Id", row.Cells[1].Value);
cmd.Parameters.AddWithValue("@fk_Item_Code", row.Cells[2].Value);
cmd.Parameters.AddWithValue("@Serial_No", row.Cells[9].Value);
cmd.Parameters.AddWithValue("@Quantity", row.Cells[10].Value);
cmd.Parameters.AddWithValue("@Unit_Cost", row.Cells[11].Value);
cmd.Parameters.AddWithValue("@Total_Cost", row.Cells[12].Value);
cmd.Parameters.AddWithValue("@fk_EUL_Id", row.Cells[3].Value);
cmd.Parameters.AddWithValue("@Date_Acquired", row.Cells[14].Value);
cmd.Parameters.AddWithValue("@Remarks", row.Cells[16].Value);
cmd.Parameters.AddWithValue("@Stock_No", row.Cells[15].Value);
cmd.Parameters.AddWithValue("@IsSubscribed", row.Cells[17].Value);
cmd.Parameters.AddWithValue("@EOS", row.Cells[18].Value);
string varId = dgvItems.Rows[x].Cells[0].Value.ToString();
varAId = dgvItems.Rows[x].Cells[1].Value.ToString();
string VarIid = dgvItems.Rows[x].Cells[2].Value.ToString();
string VarSerialNo = dgvItems.Rows[x].Cells[9].Value.ToString();
string VarUcost = dgvItems.Rows[x].Cells[11].Value.ToString();
string VarEULId = dgvItems.Rows[x].Cells[3].Value.ToString();
string EULife = dgvItems.Rows[x].Cells[13].Value.ToString();
VarDateAcq = dgvItems.Rows[x].Cells[14].Value.ToString();
string VarIsSubscribed = dgvItems.Rows[x].Cells[17].Value.ToString();
string VarEOS = dgvItems.Rows[x].Cells[18].Value.ToString();
//Compute Depreciation and Book Value
//Convert date of acquisition
DateTime dt_Acq = Convert.ToDateTime(VarDateAcq);
//Get the difference from date today and date acquired
int MonthsUsed = (DateTime.Now.Year - dt_Acq.Year) * 12 + DateTime.Now.Month - dt_Acq.Month;
//get no. only from eul value
var E_UL = String.Join("", EULife.Where(Char.IsDigit));
//multiply to 12 to get value in months
double ESL = (Convert.ToDouble(E_UL) * 12);
//compute salvage cost / 5% of the unit cost
double Salvage_Value = (Convert.ToDouble(VarUcost) * .05);
//compute depreciation expense
double Depreciation_Exp = ((Convert.ToDouble(VarUcost) - Salvage_Value) / ESL);
//convert from date to month
double YearUsed = (Convert.ToDouble(MonthsUsed) / 12);
//Compute accumulated depreciation
Acc_Depreciation = System.Math.Round((Convert.ToDouble(Depreciation_Exp) * YearUsed), 2);
// Acc_Depreciation = Acc_Depreciation.ToString();
//Compute Book Value . Acquisition Cost - Accumulated Depreciation
Book_Value = ((Convert.ToDouble(VarUcost) - Acc_Depreciation));
//Saving to Inventory Details
string qty = dgvItems.Rows[x].Cells[10].Value.ToString();
int rowcnt = Convert.ToInt32(qty);
//MessageBox.Show("Quantity "+rowcnt.ToString());
int ctr = 0;
x++;
//MessageBox.Show(x.ToString());
do
{
//MessageBox.Show("Counter "+ctr.ToString());
//Generate Property No
//Get equivalent UACS Code
string GetUACS = "Select Subcategory_Code from view_ArticleSubcat where pk_Article_Id = ' " + varAId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand CodeFinder = new SqlCommand(GetUACS, SysCon.SystemConnect);
SqlDataReader CodeReader = CodeFinder.ExecuteReader();
if (CodeReader.Read())
{
UACS_Code = CodeReader[0].ToString();
}
CodeReader.Close();
//Get Article Code
string GetArticleCode = "Select Article_Code from view_ArticleSubcat where pk_Article_Id = ' " + varAId + "'";
//close current connection
SysCon.CloseConnection();
//Open connection
SysCon.SystemConnect.Open();
SqlCommand ArticleCodeFinder = new SqlCommand(GetArticleCode, SysCon.SystemConnect);
SqlDataReader ArticleCodeReader = ArticleCodeFinder.ExecuteReader();
if (ArticleCodeReader.Read())
{
Article_Code = ArticleCodeReader[0].ToString();
}
CodeReader.Close();
//Get Year Acquired
lastTwoDigitsOfYear = VarDateAcq.Substring(8, 2);
Code = UACS_Code + '-' + Article_Code + lastTwoDigitsOfYear;
//Get Serial #
string strCount = "Select count(*) FROM view_Inventory_Details WHERE Old_Property_No like '%" + Article_Code + "%'";
//Close existing connection
SysCon.CloseConnection();
SqlCommand comd = new SqlCommand(strCount, SysCon.SystemConnect);
SysCon.SystemConnect.Open();
int count = Convert.ToInt32(comd.ExecuteScalar());
count = (Convert.ToInt32(count) + 1);
PropertyNo = Code + '-' + count.ToString("0000");
//End of code for generating Property No.
//MessageBox.Show("Property" +Property_No);
//Save to tbl_Inventory_Details
string NewInvItem = "Insert into tbl_Inventory_SubDetails (fk_Record_Id,fk_Article_Id,fk_Item_Code,Serial_No,Unit_Cost,fk_EUL_Id,Date_Acquired,Old_Property_No,fk_Accountable_Employee_Id,fk_End_User_Id,Depreciated_Cost,Book_Value,IsSubscribed,EOS,Status) Values('" + varId +
"', '" + varAId +
"', '" + VarIid +
"', '" + VarSerialNo +
"', '" + VarUcost +
"', '" + VarEULId +
"', '" + VarDateAcq +
"', '" + PropertyNo +
"', '" + GlobalClass.GlobalOICId +
"', '" + GlobalClass.GlobalOICId +
"', '" + Acc_Depreciation +
"', '" + Book_Value +
"', '" + VarIsSubscribed +
"', '" + VarEOS +
"', 'FOR ASSIGNMENT')";
SqlCommand AddNewInvItem = new SqlCommand(NewInvItem, SysCon.SystemConnect);
AddNewInvItem.ExecuteNonQuery();
ctr++;
}while (ctr != rowcnt);
cmd.ExecuteNonQuery();
}
}
//Close Connection
SysCon.SystemConnect.Close();
MessageBox.Show("New record has been successfully added!", "Add New Component record ", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Reset_All();
}
private void func_Update_Data()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string UpdateRecord = "Update tbl_Inventory_Details Set New_Property_No = @New_Property_No, Office = '" + cboOffice.Text + "' where pk_Id = @pk_Id";
int x = 0;
foreach (DataGridViewRow row in dgvItems.Rows)
{
// for generation of New Property No. //Get UACS, Article Code, Date of Acquisition
string Date = dgvItems.Rows[x].Cells[11].Value.ToString();
string lastTwoDigitsOfYear = Date.Substring(8, 2);
//string UACS = dgvItems.Rows[x].Cells[3].Value.ToString();
string ArtCode = dgvItems.Rows[x].Cells[4].Value.ToString();
New_Prop_Code = dgvItems.Rows[x].Cells[15].Value.ToString();
if (New_Prop_Code == "") //Check if there's already new property Code; skip generation of new property no. if True
{
{
//Get Serial #
string strCount = "Select count(*) FROM tbl_Inventory_Details WHERE New_Property_No like '%" + ArtCode + "%'";
//Close existing connection
SysCon.CloseConnection();
SqlCommand comd = new SqlCommand(strCount, SysCon.SystemConnect);
SysCon.SystemConnect.Open();
int count = Convert.ToInt32(comd.ExecuteScalar());
count = (Convert.ToInt32(count) + 1);
// New_Property_No = UACS + '-' + cboOffice.Text + '-' + ArtCode + lastTwoDigitsOfYear + '-' + count.ToString("0000");
New_Property_No = cboOffice.Text + '-' + ArtCode + lastTwoDigitsOfYear + '-' + count.ToString("0000");
//MessageBox.Show(new_code + '-' + count.ToString("0000"));
//End of code for generating Property No.
}
SysCon.CloseConnection();
// Open new connection
SysCon.SystemConnect.Open();
//Update tbl_Inventory
using (SqlCommand cmdUpdate = new SqlCommand(UpdateRecord, SysCon.SystemConnect))
{
cmdUpdate.Parameters.Clear();
cmdUpdate.Parameters.AddWithValue("@New_Property_No", New_Property_No);
cmdUpdate.Parameters.AddWithValue("@pk_Id", row.Cells[0].Value);
cmdUpdate.ExecuteNonQuery();
//Audit trail
//Insert Activity to audit trail
string user = "******" + GlobalClass.GlobalName +
"', '" + GlobalClass.GlobalUser +
"', '" + DateTime.Now.ToString() +
"', 'Update Item : ' + '" + row.Cells[6].Value + "' + ' ; Property No. : ' + '" + row.Cells[14].Value + "' + ' ; New Property No. :' + '" + New_Property_No + "' + '; Serial No. : ' + '" + row.Cells[8].Value + "')";
SqlCommand AuditTrail1 = new SqlCommand(user, SysCon.SystemConnect);
AuditTrail1.ExecuteNonQuery();
}
x++;
}
}
MessageBox.Show("New property no. has been successfully generated!", "Update Data", MessageBoxButtons.OK, MessageBoxIcon.Information);
func_Load_All_Items();
func_Reset();
}