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(); }