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 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 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_Check_Duplication_AddDivision()
{
//close connection
SysCon.CloseConnection();
//open connection
SysCon.SystemConnect.Open();
string CheckDuplication = "Select Unit from tbl_Divisions where Unit ='" + txtUnit.Text + "'";
SqlCommand CheckRecordCommand = new SqlCommand(CheckDuplication, SysCon.SystemConnect);
SqlDataReader CReader = CheckRecordCommand.ExecuteReader();
if (CReader.Read())
{
MessageBox.Show("Division under the same service already exists!", "Division", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtDivisionName.Focus();
return;
}
else
{
if (txtDivisionCode.Text == "")
{
txtUnit.Text = "";
txtUnit.Text = cboServices.Text;
}
else
{
txtUnit.Text = "";
txtUnit.Text = cboServices.Text + '-' + txtDivisionCode.Text;
}
func_Add_Division();
}
CReader.Close();
CReader.Dispose();
}
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_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_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();
}
